CREATE OR REPLACE TASK SUNO_PROD.PROD.CLIP_INITIAL_METRICS_HOURLY_UPSERT
    WAREHOUSE = SUNO_PROD_LARGE
    AFTER SUNO_PROD.PROD.FACT_PLAY_HOURLY_UPSERT
    AS
    DECLARE
        p_date VARCHAR;
        p_hour NUMBER;
        row_count NUMBER;
        start_time TIMESTAMP;
    BEGIN
        start_time := CURRENT_TIMESTAMP();
        -- SLA on fact_play is 1 hour, and bk_nonrealtime_event is 1 hour
        -- Need 2-hour window from clip creation, so wait for 2 hours of data to be available
        -- E.g. at hour 21, fact_play and bk_nonrealtime_event have data up to the end of hour 20
        -- This task will analyze clips created during hour 18, and be able to collect data up to the end of hour 20 (18:00:00 - 20:59:59)
        p_date := DATE(SYSDATE() - INTERVAL '1 HOUR' - INTERVAL '2 HOUR');
        p_hour := HOUR(SYSDATE() - INTERVAL '1 HOUR' - INTERVAL '2 HOUR');
        CALL CLIP_INITIAL_METRICS_HOURLY_UPSERT_PROC(:p_date, :p_hour);

        SELECT COUNT(*) INTO :row_count
        FROM suno_prod.prod.clip_initial_metrics
        WHERE p_date = :p_date AND p_hour = :p_hour;
        CALL TASK_MONITOR_INSERT_PROC('CLIP_INITIAL_METRICS_HOURLY_UPSERT', :start_time, :row_count, 'SUCCESS', 'HOURLY');
    END;
