Untitled

USE SCHEMA {{ SNOWFLAKE_DATABASE_NAME }}.{{ EXTERNAL_TABLES_SCHEMA }};

CREATE OR REPLACE PROCEDURE generate_TBSummary(currentCustomDate DATE)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    -- Truncate the TBSummary table before inserting new data
    TRUNCATE TABLE PUB.TBSummary;

    INSERT INTO PUB.TBSummary (
        QueryDate,
        ValueDate,
        IsReportDay,
        CurrentCutoffDate,
        CurrentEffectiveDate,
        LastCutoffDate
    )
    WITH 
    date_series AS (
        SELECT
            SEQ4() + 1 AS day_seq,
            DATEADD(DAY, -SEQ4(), :currentCustomDate) AS as_of_date
        FROM TABLE(GENERATOR(ROWCOUNT => 60))
    ),
    holiday_check AS (
        SELECT
            day_seq,
            as_of_date,
            CASE
                WHEN DAYOFWEEK(as_of_date) IN (0, 6)
                  OR EXISTS (
                      SELECT 1
                      FROM PIPELINE.HK_FA_CITI_CUSTOM_COMM_CALENDAR
                      WHERE HOLIDAY_DT = as_of_date
                  ) THEN 0
                ELSE 1
            END AS is_working_day,
            SUM(
                CASE
                    WHEN DAYOFWEEK(as_of_date) NOT IN (0, 6)
                      AND NOT EXISTS (
                          SELECT 1
                          FROM PIPELINE.HK_FA_CITI_CUSTOM_COMM_CALENDAR
                          WHERE HOLIDAY_DT = as_of_date
                      ) THEN 1
                    ELSE 0
                END
            ) OVER (ORDER BY as_of_date ASC) AS raw_workday_seq
        FROM date_series
    ),
    WorkCalendar AS (
        SELECT
            day_seq,
            as_of_date,
            is_working_day,
            LAST_VALUE(
                CASE WHEN is_working_day = 1 THEN raw_workday_seq ELSE NULL END
            ) IGNORE NULLS OVER (
                ORDER BY as_of_date ASC 
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS workday_seq
        FROM holiday_check
    ),
    ValueDateMap AS (
        SELECT wc1.as_of_date AS QueryDate,
               MAX(wc2.as_of_date) AS ValueDate
        FROM WorkCalendar wc1
        LEFT JOIN WorkCalendar wc2
          ON wc2.as_of_date < wc1.as_of_date
         AND wc2.is_working_day = 1
        GROUP BY wc1.as_of_date
    ),
    FilteredCurrent AS (
        SELECT *
        FROM PIPELINE.HK_FA_CITI_COMM_ACTG_PERIOD
        WHERE efctv_dt BETWEEN DATEADD(YEAR, -1, :currentCustomDate) AND :currentCustomDate
    ),
    FilteredAll AS (
        SELECT *
        FROM PIPELINE.HK_FA_CITI_COMM_ACTG_PERIOD
        WHERE efctv_dt < :currentCustomDate
    ),
    AccountingPeriod AS (
        SELECT
            curr.efctv_dt,
            curr.cutoff_dt,
            prev.cutoff_dt AS last_cutoff_dt,
            ROW_NUMBER() OVER (
                PARTITION BY curr.efctv_dt 
                ORDER BY prev.efctv_dt DESC
            ) AS rn
        FROM FilteredCurrent curr
        LEFT JOIN FilteredAll prev
            ON prev.efctv_dt < curr.efctv_dt
    ),
    FinalView AS (
        SELECT
            wc.as_of_date AS QueryDate,
            vdm.ValueDate,
            wc.is_working_day AS IsReportDay,
            ap.cutoff_dt AS CurrentCutoffDate,
            ap.efctv_dt AS CurrentEffectiveDate,
            ap.last_cutoff_dt AS LastCutoffDate
        FROM WorkCalendar wc
        LEFT JOIN ValueDateMap vdm
            ON wc.as_of_date = vdm.QueryDate
        LEFT JOIN AccountingPeriod ap
            ON ap.efctv_dt <= wc.as_of_date
           AND ap.rn = 1
    )
    SELECT *
    FROM FinalView;

    RETURN 'Procedure executed successfully';
END;
$$;