CREATE OR REPLACE TASK SUNO_PROD.PROD.AGG_PLAY_INFO_HOURLY_UPSERT
    WAREHOUSE = SUNO_PROD_AGG_HOURLY_LARGE
    AFTER SUNO_PROD.PROD.FACT_PLAY_HOURLY_UPSERT
    AS
    DECLARE
        p_date DATE;
        p_hour NUMBER;
        row_count NUMBER;
        start_time TIMESTAMP;
    BEGIN
        start_time := CURRENT_TIMESTAMP();
        -- Run with a 2-hour delay to ensure fact_play data is settled
        p_date := DATE(SYSDATE() - INTERVAL '2 HOUR');
        p_hour := HOUR(SYSDATE() - INTERVAL '2 HOUR');
        CALL SUNO_PROD.PROD.AGG_PLAY_INFO_HOURLY_UPSERT_PROC(:p_date, :p_hour, :p_date, :p_hour);

        SELECT COUNT(*) INTO :row_count
        FROM suno_prod.prod.agg_play_info_hourly
        WHERE p_date = :p_date AND p_hour = :p_hour;

        CALL TASK_MONITOR_INSERT_PROC('AGG_PLAY_INFO_HOURLY_UPSERT', :start_time, :row_count, 'SUCCESS', 'HOURLY');
    END;
