--------------------------------------------------------
--  DDL for View NLCORP_SECURITY_POLICY_EXCEPTIONS_STATUS_OVER_TIME_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OTHQ_OWN"."NLCORP_SECURITY_POLICY_EXCEPTIONS_STATUS_OVER_TIME_VW" ("ID", "THE_MONTH", "SPE_STATUS") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  WITH 
months AS (
  SELECT ADD_MONTHS(DATE '2023-09-01', LEVEL - 1) AS the_month
  FROM dual
  CONNECT BY LEVEL <= MONTHS_BETWEEN(TRUNC(SYSDATE, 'MM'), DATE '2022-01-01') + 1
),
spe_ranges AS (SELECT
    l.ID,
    TRUNC(l.METADATA_REQUEST_DATE_UTC, 'MM') AS START_MONTH,
    CASE
        WHEN TRUNC(SYSDATE) >= TRUNC(l.METADATA_EXCEPTION_EXPIRATION_DATE_UTC)
        THEN TRUNC(METADATA_EXCEPTION_EXPIRATION_DATE_UTC,'MM')
        
        WHEN l.STATUS IN ('Closed','Rejected') THEN (
            SELECT TRUNC(METADATA_DATE_UTC, 'MM')
            FROM SRC_NLCORP_SECURITY_POLICY_EXCEPTIONS_METRICS_TB c
            WHERE c.ID = l.ID
              AND c.FIELD = 'Status'
              AND c.NEW_VALUE IN ('Closed','Rejected')
            ORDER BY c.HISTORY_ID DESC
            FETCH FIRST 1 ROW ONLY
        )
        ELSE NULL
    END AS CLOSE_MONTH
FROM (
    SELECT *
    FROM (
        SELECT t.*,
               ROW_NUMBER() OVER (PARTITION BY ID ORDER BY HISTORY_ID DESC) AS rn
        FROM SRC_NLCORP_SECURITY_POLICY_EXCEPTIONS_METRICS_TB t
    ) 
    WHERE rn = 1
) l
)
SELECT
  r.ID,
  m.the_month,
  CASE
    WHEN m.the_month = r.start_month THEN 'New'
    WHEN r.close_month IS NOT NULL AND m.the_month >= r.close_month THEN 'Closed'
    WHEN m.the_month > r.start_month AND (r.close_month IS NULL OR m.the_month < r.close_month) THEN 'Open'
    ELSE NULL
  END AS spe_status
FROM spe_ranges  r
JOIN months m 
  ON m.the_month >= r.start_month
 AND (
      r.close_month IS NULL OR m.the_month <= r.close_month 
     )
WHERE 
  (m.the_month = r.start_month OR
   (r.close_month IS NOT NULL AND m.the_month >= r.close_month) OR
   (m.the_month > r.start_month AND (r.close_month IS NULL OR m.the_month < r.close_month)))
;
