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 (
        query_date,
        value_date,
        is_report_day,
        current_cutoff_date,
        current_effective_date,
        last_cutoff_date
    )
    WITH 
    CTE_PARAM AS (
        SELECT :currentCustomDate AS CustomCurrentDate
    ),

    CTE_DateList AS (
        -- Generate 60 dates ending at CustomCurrentDate
        SELECT 
            SEQ4() AS seq,
            DATEADD(DAY, -SEQ4(), (SELECT CustomCurrentDate FROM CTE_PARAM)) AS AsOfDate
        FROM TABLE(GENERATOR(ROWCOUNT => 60))
    ),

    CTE_Holidays AS (
        SELECT HOLIDAY_DT
        FROM PIPELINE.HK_FA_CITI_CUSTOM_COMM_CALENDAR
    ),

    CTE_BaseCalendar AS (
        SELECT 
            AsOfDate,
            CASE 
                WHEN DAYOFWEEK(AsOfDate) IN (0, 6) 
                     OR EXISTS (SELECT 1 FROM CTE_Holidays WHERE HOLIDAY_DT = AsOfDate)
                THEN 0 ELSE 1
            END AS IsWorkingDay
        FROM CTE_DateList
    ),

    CTE_WorkCalendar AS (
        SELECT 
            AsOfDate,
            IsWorkingDay,
            SUM(CASE WHEN IsWorkingDay = 1 THEN 1 ELSE 0 END) 
                OVER (ORDER BY AsOfDate) AS WorkdaySeq
        FROM CTE_BaseCalendar
    ),

    CTE_AccountingPeriodFiltered AS (
        SELECT *
        FROM PIPELINE.HK_FA_CITI_COMM_ACTG_PERIOD
        WHERE efctv_dt BETWEEN DATEADD(YEAR, -1, :currentCustomDate) AND :currentCustomDate
    ),

    AccountingPeriodWithPrev AS (
        SELECT 
            curr.efctv_dt AS current_effective_date,
            curr.cutoff_dt AS current_cutoff_date,
            prev.cutoff_dt AS last_cutoff_date,
            ROW_NUMBER() OVER (PARTITION BY curr.efctv_dt ORDER BY prev.efctv_dt DESC) AS rn
        FROM CTE_AccountingPeriodFiltered curr
        LEFT JOIN CTE_AccountingPeriodFiltered prev
            ON prev.efctv_dt < curr.efctv_dt
    ),

    AccountingPeriod AS (
        SELECT 
            current_effective_date,
            current_cutoff_date,
            last_cutoff_date
        FROM AccountingPeriodWithPrev
        QUALIFY rn = 1
    ),

    QueryDateCTE AS (
        -- Get T-1 date from CustomCurrentDate
        SELECT *
        FROM CTE_WorkCalendar
        WHERE AsOfDate = DATEADD(DAY, -1, :currentCustomDate)
    ),

    ValueDateCTE AS (
        -- Get latest working day before QueryDate (T-2)
        SELECT *
        FROM CTE_WorkCalendar
        WHERE AsOfDate < (SELECT AsOfDate FROM QueryDateCTE)
        AND IsWorkingDay = 1
        QUALIFY ROW_NUMBER() OVER (ORDER BY AsOfDate DESC) = 1
    ),

    FirstWorkingDayAfterCutoff AS (
        SELECT 
            ap.current_cutoff_date,
            MIN(wc.AsOfDate) AS first_report_day
        FROM AccountingPeriod ap
        JOIN CTE_WorkCalendar wc
            ON wc.IsWorkingDay = 1
            AND wc.AsOfDate > ap.current_cutoff_date
        GROUP BY ap.current_cutoff_date
    ),

    FinalResult AS (
        SELECT
            qd.AsOfDate AS query_date,
            vd.AsOfDate AS value_date,
            CASE 
                WHEN vd.AsOfDate = ap.current_cutoff_date 
                     AND vd.IsWorkingDay = 1
                     AND qd.AsOfDate = fr.first_report_day
                THEN 1
                ELSE 0
            END AS is_report_day,
            ap.current_cutoff_date,
            ap.current_effective_date,
            ap.last_cutoff_date,
            ROW_NUMBER() OVER (ORDER BY qd.AsOfDate DESC) AS rn
        FROM QueryDateCTE qd
        JOIN ValueDateCTE vd ON 1=1
        JOIN AccountingPeriod ap 
            ON ap.last_cutoff_date < vd.AsOfDate
            AND ap.current_cutoff_date >= vd.AsOfDate
        JOIN FirstWorkingDayAfterCutoff fr
            ON fr.current_cutoff_date = ap.current_cutoff_date
    )

    -- Return only the current report row
    SELECT 
        query_date, 
        value_date, 
        is_report_day, 
        current_cutoff_date, 
        current_effective_date, 
        last_cutoff_date
    FROM FinalResult
    WHERE is_report_day = 1
    QUALIFY rn = 1;

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