Untitled

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

CREATE OR REPLACE PROCEDURE generate_TBSummary(currentCustomDate DATE)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
    QueryDate DATE;
    ValueDate DATE;
    IsReportDay INT;
    CurrentCutoffDate DATE;
    CurrentEffectiveDate DATE;
    LastCutoffDate DATE;
BEGIN
    -- Step 1: Prepare WorkCalendar
    CREATE OR REPLACE TEMP TABLE temp_calendar AS
    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
    )
    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;

    -- Step 2: Derive QD and VD from temp_calendar
    SELECT as_of_date, workday_seq
    INTO QueryDate, ValueDate
    FROM (
        SELECT
            QD.as_of_date AS as_of_date,
            VD.as_of_date AS value_as_of_date,
            QD.workday_seq,
            VD.workday_seq
        FROM temp_calendar QD
        JOIN temp_calendar VD
            ON VD.is_working_day = 1
           AND VD.workday_seq = QD.workday_seq - 1
        WHERE QD.as_of_date = DATEADD(DAY, -1, :currentCustomDate)
    ) AS result;

    -- Step 3: AccountingPeriod match
    SELECT
        CASE 
            WHEN :ValueDate = cutoff_dt THEN 1 ELSE 0 
        END,
        cutoff_dt,
        efctv_dt,
        last_cutoff_dt
    INTO IsReportDay, CurrentCutoffDate, CurrentEffectiveDate, LastCutoffDate
    FROM (
        SELECT
            curr.efctv_dt,
            curr.cutoff_dt,
            prev.cutoff_dt AS last_cutoff_dt
        FROM PIPELINE.HK_FA_CITI_COMM_ACTG_PERIOD curr
        LEFT JOIN PIPELINE.HK_FA_CITI_COMM_ACTG_PERIOD prev
            ON prev.efctv_dt < curr.efctv_dt
        WHERE curr.efctv_dt BETWEEN DATEADD(YEAR, -1, :currentCustomDate) AND :currentCustomDate
          AND prev.cutoff_dt < :ValueDate
          AND curr.cutoff_dt >= :ValueDate
        QUALIFY ROW_NUMBER() OVER (PARTITION BY curr.efctv_dt ORDER BY prev.efctv_dt DESC) = 1
    ) AS AP;

    -- Step 4: Insert final result
    TRUNCATE TABLE PUB.TBSummary;

    INSERT INTO PUB.TBSummary (
        QueryDate,
        ValueDate,
        IsReportDay,
        CurrentCutoffDate,
        CurrentEffectiveDate,
        LastCutoffDate
    )
    VALUES (
        :QueryDate,
        :ValueDate,
        :IsReportDay,
        :CurrentCutoffDate,
        :CurrentEffectiveDate,
        :LastCutoffDate
    );

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