create rays.sql

--ray distance between line segments
--this is split into a couple interim tables as steps
--because the optimizer would run slowly
--yes, there may be ways to combine this and force optimizer order with HASH functions

CREATE OR REPLACE VIEW RAY_SPACING_1_RAY_RESULTS AS

WITH INIT_TBL AS (
SELECT 
ID, FTP_X, FTP_Y, LTP_X, LTP_Y
FROM INIT_TBL_0
GROUP BY ID
HAVING ID IS NOT NULL
)

, INIT_TBL_CLEAN AS (
SELECT *
, CASE WHEN LTP_Y > FTP_Y THEN 'TOE UP'
ELSE 'TOE DOWN' END AS TOE_DIRECTION
FROM INIT_TBL
WHERE FTP_X IS NOT NULL
AND FTP_Y IS NOT NULL
AND LTP_X IS NOT NULL
AND LTP_Y IS NOT NULL
)

, ALL_TOE_DOWN AS (
SELECT ID
, CASE WHEN TOE_DIRECTION = 'TOE UP' THEN LTP_X ELSE FTP_X END AS FTP_X
, CASE WHEN TOE_DIRECTION = 'TOE UP' THEN LTP_Y ELSE FTP_Y END AS FTP_Y
, CASE WHEN TOE_DIRECTION = 'TOE UP' THEN FTP_X ELSE LTP_X END AS LTP_X
, CASE WHEN TOE_DIRECTION = 'TOE UP' THEN FTP_Y ELSE LTP_Y END AS LTP_Y
FROM INIT_TBL_CLEAN
)

--check
/*SELECT *
FROM ALL_TOE_DOWN
WHERE TOE_DIRECTION = 'TOE UP'*/

--fix wells that are perfectly vertical or horizontal
, PERTURBED_HORIZ_OR_VERT AS (
SELECT ID, FTP_X, FTP_Y
, CASE WHEN LTP_X = FTP_X THEN LTP_X + 0.2 ELSE LTP_X END AS LTP_X
, CASE WHEN LTP_Y = FTP_Y THEN LTP_Y - 0.2 ELSE LTP_Y END AS LTP_Y
FROM ALL_TOE_DOWN
)

, WELL_POINTS_AND_SLOPES AS (
SELECT *
, (FTP_X + LTP_X)/2 AS MID_PT_X 
, (FTP_Y + LTP_Y)/2 AS MID_PT_Y
, (FTP_X + MID_PT_X)/2 AS P25_PT_X 
, (FTP_Y + MID_PT_Y)/2 AS P25_PT_Y
, (LTP_X + MID_PT_X)/2 AS P75_PT_X 
, (LTP_Y + MID_PT_Y)/2 AS P75_PT_Y
, (LTP_Y - FTP_Y)/(LTP_X - FTP_X) AS WELL_SLOPE
, -1 / WELL_SLOPE as RAY_SLOPE
, CASE WHEN FTP_X < LTP_X THEN FTP_X ELSE LTP_X END AS MIN_X
, CASE WHEN FTP_X > LTP_X THEN FTP_X ELSE LTP_X END AS MAX_X
, CASE WHEN FTP_Y < LTP_Y THEN FTP_Y ELSE LTP_Y END AS MIN_Y
, CASE WHEN FTP_Y > LTP_Y THEN FTP_Y ELSE LTP_Y END AS MAX_Y
FROM PERTURBED_HORIZ_OR_VERT
)

--course filtering of offset wells
, INIT_NEARBY_WELLS AS (
SELECT TGT.*
, OFFS.ID AS OFFSET_ID
, OFFS.FTP_X AS OFFSET_FTP_X, OFFS.FTP_Y AS OFFSET_FTP_Y
, OFFS.LTP_X AS OFFSET_LTP_X, OFFS.LTP_Y AS OFFSET_LTP_Y
, OFFS.WELL_SLOPE AS OFFSET_WELL_SLOPE
, OFFS.MIN_X AS OFFSET_MIN_X, OFFS.MIN_Y AS OFFSET_MIN_Y
, OFFS.MAX_X AS OFFSET_MAX_X, OFFS.MAX_Y AS OFFSET_MAX_Y
FROM WELL_POINTS_AND_SLOPES TGT
CROSS JOIN WELL_POINTS_AND_SLOPES OFFS
WHERE OFFS.MAX_X >= TGT.MIN_X - 10000
AND OFFS.MIN_X <= TGT.MAX_X + 10000
AND OFFS.MAX_Y >= TGT.MIN_Y - 10000
AND OFFS.MIN_Y <= TGT.MAX_Y + 10000
AND TGT.ID != OFFS.ID
)

, INIT_INTERSECTION_PTS AS (
SELECT *
, (RAY_SLOPE * FTP_X - OFFSET_WELL_SLOPE * OFFSET_FTP_X + OFFSET_FTP_Y - FTP_Y)/(RAY_SLOPE - OFFSET_WELL_SLOPE) AS INTERSECT_FTP_X
, RAY_SLOPE * (INTERSECT_FTP_X - FTP_X) + FTP_Y AS INTERSECT_FTP_Y
, (RAY_SLOPE * P25_PT_X - OFFSET_WELL_SLOPE * OFFSET_FTP_X + OFFSET_FTP_Y - P25_PT_Y)/(RAY_SLOPE - OFFSET_WELL_SLOPE) AS INTERSECT_P25_PT_X
, RAY_SLOPE * (INTERSECT_P25_PT_X - P25_PT_X) + P25_PT_Y AS INTERSECT_P25_PT_Y
, (RAY_SLOPE * MID_PT_X - OFFSET_WELL_SLOPE * OFFSET_FTP_X + OFFSET_FTP_Y - MID_PT_Y)/(RAY_SLOPE - OFFSET_WELL_SLOPE) AS INTERSECT_MID_PT_X
, RAY_SLOPE * (INTERSECT_MID_PT_X - MID_PT_X) + MID_PT_Y AS INTERSECT_MID_PT_Y
, (RAY_SLOPE * P75_PT_X - OFFSET_WELL_SLOPE * OFFSET_FTP_X + OFFSET_FTP_Y - P75_PT_Y)/(RAY_SLOPE - OFFSET_WELL_SLOPE) AS INTERSECT_P75_PT_X
, RAY_SLOPE * (INTERSECT_P75_PT_X - P75_PT_X) + P75_PT_Y AS INTERSECT_P75_PT_Y
, (RAY_SLOPE * LTP_X - OFFSET_WELL_SLOPE * OFFSET_FTP_X + OFFSET_FTP_Y - LTP_Y)/(RAY_SLOPE - OFFSET_WELL_SLOPE) AS INTERSECT_LTP_X
, RAY_SLOPE * (INTERSECT_LTP_X - LTP_X) + LTP_Y AS INTERSECT_LTP_Y
FROM INIT_NEARBY_WELLS
)

--if intersect not occur on offset well, set to null
, INTERSECTS_ON_OFFSET_WELLS AS (
SELECT ID, FTP_X, FTP_Y, P25_PT_X, P25_PT_Y
, MID_PT_X, MID_PT_Y, P75_PT_X, P75_PT_Y, LTP_X, LTP_Y
, WELL_SLOPE, RAY_SLOPE, MIN_X, MIN_Y, MAX_X, MAX_Y
, OFFSET_ID, OFFSET_FTP_X, OFFSET_FTP_Y, OFFSET_LTP_X, OFFSET_LTP_Y
, OFFSET_WELL_SLOPE, OFFSET_MIN_X, OFFSET_MIN_Y, OFFSET_MAX_X, OFFSET_MAX_Y
--only need to check x or y, don't need 4 conditional checks to ensure intersection point is on offset stick
, CASE WHEN INTERSECT_FTP_X >= OFFSET_MIN_X AND INTERSECT_FTP_X <= OFFSET_MAX_X THEN INTERSECT_FTP_X ELSE NULL END AS INTERSECT_FTP_X
, CASE WHEN INTERSECT_FTP_X >= OFFSET_MIN_X AND INTERSECT_FTP_X <= OFFSET_MAX_X THEN INTERSECT_FTP_Y ELSE NULL END AS INTERSECT_FTP_Y
, CASE WHEN INTERSECT_P25_PT_X >= OFFSET_MIN_X AND INTERSECT_P25_PT_X <= OFFSET_MAX_X THEN INTERSECT_P25_PT_X ELSE NULL END AS INTERSECT_P25_PT_X
, CASE WHEN INTERSECT_P25_PT_X >= OFFSET_MIN_X AND INTERSECT_P25_PT_X <= OFFSET_MAX_X THEN INTERSECT_P25_PT_Y ELSE NULL END AS INTERSECT_P25_PT_Y
, CASE WHEN INTERSECT_MID_PT_X >= OFFSET_MIN_X AND INTERSECT_MID_PT_X <= OFFSET_MAX_X THEN INTERSECT_MID_PT_X ELSE NULL END AS INTERSECT_MID_PT_X
, CASE WHEN INTERSECT_MID_PT_X >= OFFSET_MIN_X AND INTERSECT_MID_PT_X <= OFFSET_MAX_X THEN INTERSECT_MID_PT_Y ELSE NULL END AS INTERSECT_MID_PT_Y
, CASE WHEN INTERSECT_P75_PT_X >= OFFSET_MIN_X AND INTERSECT_P75_PT_X <= OFFSET_MAX_X THEN INTERSECT_P75_PT_X ELSE NULL END AS INTERSECT_P75_PT_X
, CASE WHEN INTERSECT_P75_PT_X >= OFFSET_MIN_X AND INTERSECT_P75_PT_X <= OFFSET_MAX_X THEN INTERSECT_P75_PT_Y ELSE NULL END AS INTERSECT_P75_PT_Y
, CASE WHEN INTERSECT_LTP_X >= OFFSET_MIN_X AND INTERSECT_LTP_X <= OFFSET_MAX_X THEN INTERSECT_LTP_X ELSE NULL END AS INTERSECT_LTP_X
, CASE WHEN INTERSECT_LTP_X >= OFFSET_MIN_X AND INTERSECT_LTP_X <= OFFSET_MAX_X THEN INTERSECT_LTP_Y ELSE NULL END AS INTERSECT_LTP_Y
FROM INIT_INTERSECTION_PTS
)

, DISTANCE_AND_SIDE AS (
SELECT *
, SQRT(POWER(FTP_X - INTERSECT_FTP_X,2) + POWER(FTP_Y - INTERSECT_FTP_Y,2)) AS FTP_DISTANCE
, SQRT(POWER(P25_PT_X - INTERSECT_P25_PT_X,2) + POWER(P25_PT_Y - INTERSECT_P25_PT_Y,2)) AS P25_PT_DISTANCE
, SQRT(POWER(MID_PT_X - INTERSECT_MID_PT_X,2) + POWER(MID_PT_Y - INTERSECT_MID_PT_Y,2)) AS MID_PT_DISTANCE
, SQRT(POWER(P75_PT_X - INTERSECT_P75_PT_X,2) + POWER(P75_PT_Y - INTERSECT_P75_PT_Y,2)) AS P75_PT_DISTANCE
, SQRT(POWER(LTP_X - INTERSECT_LTP_X,2) + POWER(LTP_Y - INTERSECT_LTP_Y,2)) AS LTP_DISTANCE

, CASE WHEN INTERSECT_FTP_X <= FTP_X THEN 'WEST' 
   WHEN INTERSECT_FTP_X IS NULL THEN NULL ELSE 'EAST' END AS FTP_SIDE
, CASE WHEN INTERSECT_P25_PT_X <= P25_PT_X THEN 'WEST' 
   WHEN INTERSECT_P25_PT_X IS NULL THEN NULL ELSE 'EAST' END AS P25_PT_SIDE
, CASE WHEN INTERSECT_MID_PT_X <= MID_PT_X THEN 'WEST' 
   WHEN INTERSECT_MID_PT_X IS NULL THEN NULL ELSE 'EAST' END AS MID_PT_SIDE
, CASE WHEN INTERSECT_P75_PT_X <= P75_PT_X THEN 'WEST' 
   WHEN INTERSECT_P75_PT_X IS NULL THEN NULL ELSE 'EAST' END AS P75_PT_SIDE
, CASE WHEN INTERSECT_LTP_X <= LTP_X THEN 'WEST' 
   WHEN INTERSECT_LTP_X IS NULL THEN NULL ELSE 'EAST' END AS LTP_SIDE
FROM INTERSECTS_ON_OFFSET_WELLS
WHERE INTERSECT_FTP_X IS NOT NULL
OR INTERSECT_P25_PT_X IS NOT NULL
OR INTERSECT_MID_PT_X IS NOT NULL
OR INTERSECT_P75_PT_X IS NOT NULL
OR INTERSECT_LTP_X IS NOT NULL
)

SELECT *
FROM DISTANCE_AND_SIDE
;

CREATE OR REPLACE VIEW RAY_SPACING_2_RAY_TALL AS 

WITH TALL_PIVOT AS (
SELECT ID, OFFSET_ID, 'FTP' AS TYPE_INTERSECT
  , INTERSECT_FTP_X AS INTERSECT_X, INTERSECT_FTP_Y AS INTERSECT_Y, 
  FTP_DISTANCE AS INTERSECT_DISTANCE, FTP_SIDE AS INTERSECT_SIDE
FROM RAY_SPACING_1_RAY_RESULTS
UNION ALL
SELECT ID, OFFSET_ID, 'P25_PT' AS TYPE_INTERSECT
  , INTERSECT_P25_PT_X AS INTERSECT_X, INTERSECT_P25_PT_Y AS INTERSECT_Y, 
  P25_PT_DISTANCE AS INTERSECT_DISTANCE, P25_PT_SIDE AS INTERSECT_SIDE
FROM RAY_SPACING_1_RAY_RESULTS
UNION ALL -- UNION ALL is faster than UNION because it doesn't remove dup rows
SELECT ID, OFFSET_ID, 'MID_PT' AS TYPE_INTERSECT
  , INTERSECT_MID_PT_X AS INTERSECT_X, INTERSECT_MID_PT_Y AS INTERSECT_Y, 
  MID_PT_DISTANCE AS INTERSECT_DISTANCE, MID_PT_SIDE AS INTERSECT_SIDE
FROM RAY_SPACING_1_RAY_RESULTS
UNION ALL
SELECT ID, OFFSET_ID, 'P75_PT' AS TYPE_INTERSECT
  , INTERSECT_P75_PT_X AS INTERSECT_X, INTERSECT_P75_PT_Y AS INTERSECT_Y, 
  P75_PT_DISTANCE AS INTERSECT_DISTANCE, P75_PT_SIDE AS INTERSECT_SIDE
FROM RAY_SPACING_1_RAY_RESULTS
UNION ALL
SELECT ID, OFFSET_ID, 'LTP' AS TYPE_INTERSECT
  , INTERSECT_LTP_X AS INTERSECT_X, INTERSECT_LTP_Y AS INTERSECT_Y, 
  LTP_DISTANCE AS INTERSECT_DISTANCE, LTP_SIDE AS INTERSECT_SIDE
FROM RAY_SPACING_1_RAY_RESULTS
)

, NO_NULLS AS (
SELECT *
FROM TALL_PIVOT
WHERE INTERSECT_X IS NOT NULL 
)

, INITIAL_TALL AS (
SELECT *
FROM NO_NULLS
)

, POD_TARGETS AS (
SELECT DISTINCT ID, TARGET
FROM WELL_STICKS
WHERE ROW_EXPIRY_DATE > CURRENT_DATE AND ROW_DELETED_DATE IS NULL OR ROW_DELETED_DATE > CURRENT_DATE
)

, ONLINE_DATES_TBL AS (
SELECT DISTINCT ID, ON_PRODUCTION_DATE::DATE AS ON_PRODUCTION_DATE
FROM  BI.WELL
WHERE 
ON_PRODUCTION_DATE IS NOT NULL
AND ID IS NOT NULL
)

, FORMATION_TARGET_IP_DATE_JOINED AS (
SELECT RAY.*
, TARGET_WELL.TARGET AS TARGET
, OFFS.TARGET AS OFFSET_TARGET
, TARG_FORM_TBL.FORMATION AS FORMATION
, OFFS_FORM_TBL.FORMATION AS OFFSET_FORMATION
, TGT_ON_DATES.ON_PRODUCTION_DATE AS ON_PRODUCTION_DATE
, OFFS_ON_DATES.ON_PRODUCTION_DATE AS OFFSET_ON_PRODUCTION_DATE 
FROM INITIAL_TALL RAY
 LEFT JOIN POD_TARGETS TARGET_WELL
ON RAY.ID = TARGET_WELL.ID
LEFT JOIN POD_TARGETS OFFS
ON RAY.OFFSET_ID = OFFS.ID
LEFT JOIN TARGET_VS_FORMATION_1 TARG_FORM_TBL
ON TARGET_WELL.TARGET = TARG_FORM_TBL.TARGET
LEFT JOIN TARGET_VS_FORMATION_1 OFFS_FORM_TBL
ON OFFS.TARGET = OFFS_FORM_TBL.TARGET
LEFT JOIN ONLINE_DATES_TBL TGT_ON_DATES
ON RAY.ID = TGT_ON_DATES.ID
LEFT JOIN ONLINE_DATES_TBL OFFS_ON_DATES
ON RAY.ID = OFFS_ON_DATES.ID 
)

SELECT * EXCLUDE (ON_PRODUCTION_DATE, OFFSET_ON_PRODUCTION_DATE)
, CASE WHEN ON_PRODUCTION_DATE IS NULL THEN DATE('2053-01-01') ELSE ON_PRODUCTION_DATE END AS ON_PRODUCTION_DATE
, CASE WHEN OFFSET_ON_PRODUCTION_DATE IS NULL THEN DATE('2050-01-01') ELSE OFFSET_ON_PRODUCTION_DATE END AS OFFSET_ON_PRODUCTION_DATE
FROM FORMATION_TARGET_IP_DATE_JOINED
;