Untitled

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

CREATE OR REPLACE PROCEDURE generate_TBSummary(currentCustomDate DATE)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    INSERT INTO PUB.TBSummary ()
    WITH date_series AS (
        -- Generate a series of dates
        SELECT
            SEQ4() AS day_seq,
            DATEADD(DAY, -SEQ4(), currentCustomDate) AS as_of_date
        FROM
            TABLE(GENERATOR(ROWCOUNT => 60))
    ),
    holiday_check AS (
        -- Mark weekends and holidays as non-working days
        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
        FROM
            date_series
    ),
    BaseCalendar AS (
        -- Final selection
        SELECT
            day_seq + 1 AS day_seq,
            as_of_date,
            is_working_day
        FROM
            holiday_check
    ),
    ascending_dates AS (
        -- Step 1: Sort dates in ascending order to calculate workday sequence correctly
        SELECT
            *,
            ROW_NUMBER() OVER (
                ORDER BY
                    as_of_date ASC
            ) AS ascending_day_num
        FROM
            BaseCalendar
    ),
    seq_raw AS (
        -- Step 2: Calculate raw sequence only for working days
        SELECT
            *,
            SUM(
                CASE
                    WHEN is_working_day = 1 THEN 1
                    ELSE 0
                END
            ) OVER (
                ORDER BY
                    as_of_date ASC
            ) AS raw_workday_seq
        FROM
            ascending_dates
    ),
    fill_seq AS (
        -- Step 3: For non-working days, use previous row's sequence (carry forward)
        SELECT
            *,
            CASE
                WHEN is_working_day = 1 THEN raw_workday_seq
                ELSE NULL
            END AS workday_seq_with_nulls
        FROM
            seq_raw
    ),
    WorkCalendar AS (
        -- Step 4: Fill in the sequence for non-working days
        SELECT
            *,
            LAST_VALUE(workday_seq_with_nulls IGNORE NULLS) OVER (
                ORDER BY
                    as_of_date ASC ROWS BETWEEN UNBOUNDED PRECEDING
                    AND CURRENT ROW
            ) AS workday_seq
        FROM
            fill_seq
    )
    -- Use WorkCalendar logic for further processing or final output
    SELECT
        day_seq,
        as_of_date,
        is_working_day,
        workday_seq
    FROM
        WorkCalendar;

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