Untitled

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

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

    -- Insert the report
    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
    ),
    AccountingPeriod AS (
        WITH 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
        ),
        JoinedWithPrevious 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
        )
        SELECT efctv_dt, cutoff_dt, last_cutoff_dt
        FROM JoinedWithPrevious
        QUALIFY rn = 1
    ),
    ReportView AS (
        SELECT
            QD.as_of_date AS QueryDate,
            VD.as_of_date AS ValueDate,
            CASE 
                WHEN VD.as_of_date = AP.cutoff_dt AND VD.is_working_day = 1 THEN 1
                ELSE 0
            END AS IsReportDay,
            AP.cutoff_dt AS CurrentCutoffDate,
            AP.efctv_dt AS CurrentEffectiveDate,
            AP.last_cutoff_dt AS LastCutoffDate
        FROM WorkCalendar QD
        JOIN WorkCalendar VD 
            ON VD.workday_seq = QD.workday_seq - 1 
            AND VD.is_working_day = 1
        JOIN AccountingPeriod AP 
            ON AP.last_cutoff_dt < VD.as_of_date 
            AND AP.cutoff_dt >= VD.as_of_date
        WHERE QD.as_of_date = DATEADD(DAY, -1, :currentCustomDate)
    )

    SELECT * FROM ReportView;

    RETURN 'TBSummary updated successfully';
END;
$$;