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 up to the 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
    ),

    QueryDates AS (
        SELECT * FROM CTE_WorkCalendar
    ),

    ValueDates AS (
        SELECT 
            q.AsOfDate AS query_date,
            q.WorkdaySeq,
            q.IsWorkingDay AS query_is_working_day,
            vd.AsOfDate AS value_date,
            vd.IsWorkingDay AS value_is_working_day
        FROM QueryDates q
        LEFT JOIN LATERAL (
            SELECT *
            FROM CTE_WorkCalendar
            WHERE AsOfDate < q.AsOfDate AND IsWorkingDay = 1
            QUALIFY ROW_NUMBER() OVER (ORDER BY AsOfDate DESC) = 1
        ) vd ON TRUE
    ),

    FinalResult AS (
        SELECT
            v.query_date,
            v.value_date,
            CASE 
                WHEN v.value_date = ap.current_cutoff_date AND v.value_is_working_day = 1 THEN 1
                ELSE 0
            END AS is_report_day,
            ap.current_cutoff_date,
            ap.current_effective_date,
            ap.last_cutoff_date
        FROM ValueDates v
        LEFT JOIN AccountingPeriod ap 
            ON ap.last_cutoff_date < v.value_date
            AND ap.current_cutoff_date >= v.value_date
    )

    SELECT 
        query_date, 
        value_date, 
        is_report_day, 
        current_cutoff_date, 
        current_effective_date, 
        last_cutoff_date
    FROM FinalResult
    ORDER BY query_date;

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