CREATE OR REPLACE PROCEDURE PROD.TASK_MONITOR_HOURLY_JOB_PROC()
RETURNS STRING
LANGUAGE SQL
AS 
$$
DECLARE 
    failed_tasks ARRAY;
    long_running_tasks ARRAY;
    alert_message STRING;
    zero_row_tasks ARRAY;
BEGIN
    -- Step 1: Get failed tasks into an array
    show tasks in database SUNO_PROD;
    create or replace transient table hourly_task 
    as 
    select "name" as name from table(result_scan(last_query_id())) where "name" like '%HOURLY%' and "name" not like '%BZ_%' and "state" = 'started';
    SELECT ARRAY_AGG(name)
    INTO :failed_tasks
    FROM (
        WITH tasks AS (
            SELECT UPPER(name) AS name 
            FROM hourly_task
            where name not in (
                'TASK_MONITOR_HOURLY_JOB'
            )
        ),
        task_history AS (
            SELECT UPPER(name) AS name
            FROM task_monitor
            WHERE status = 'SUCCESS' 
            AND DATE(START_TIME) = DATE(SYSDATE() - INTERVAL '1 HOUR')
            AND HOUR(START_TIME) = HOUR(SYSDATE() - INTERVAL '1 HOUR')
        )
        -- Find failed tasks
        SELECT d.name
        FROM tasks d
        LEFT JOIN task_history t ON d.name = t.name
        WHERE t.name IS NULL
    );

    -- Step 2: Send alert to Slack if any tasks failed
    IF (ARRAY_SIZE(:failed_tasks) > 0) THEN
        alert_message := '🚨 ALERT: The following tasks failed in the past hour: ' || 
            ARRAY_TO_STRING(:failed_tasks, ', ');
        
        select post_to_slack('#data-alerts', :alert_message);
    END IF;

    SELECT ARRAY_AGG(UPPER(name))
    INTO :long_running_tasks
    FROM task_monitor
    WHERE duration > 2400 
    AND DATE(START_TIME) = DATE(SYSDATE() - INTERVAL '1 HOUR')
    AND HOUR(START_TIME) = HOUR(SYSDATE() - INTERVAL '1 HOUR')
    AND TYPE = 'HOURLY'
    and name not in (
        'RDS_CLIP_HOURLY_UPDATE'
    );

    IF (ARRAY_SIZE(:long_running_tasks) > 0) THEN
        alert_message := '🚨 ALERT: The following tasks run more than 40 minutes in the past hour: ' || 
            ARRAY_TO_STRING(:long_running_tasks, ', ');
        
        select post_to_slack('#data-alerts', :alert_message);
    END IF;

    -- Step 3: Send alert to Slack if any tasks affected row is 0 
    SELECT ARRAY_AGG(UPPER(name))
        INTO :zero_row_tasks
    FROM task_monitor
    WHERE row_affected = 0
    AND DATE(START_TIME) = DATE(SYSDATE() - INTERVAL '1 HOUR')
    AND HOUR(START_TIME) = HOUR(SYSDATE() - INTERVAL '1 HOUR')
    AND TYPE = 'HOURLY'
    AND name not in (
        'RDS_COMMENT_BAN_HOURLY_UPSERT',
        'GLUE_RDS_TO_S3_BOTS_COMMENTREPORT_HOURLY_UPSERT',
        'RDS_COMMENT_REPORT_HOURLY_UPSERT',
        'GLUE_RDS_TO_S3_BOTS_COMMENTBAN_HOURLY_UPSERT',
        'AF_SKAD_SRN_ADVERTISING_DIMENSIONS_DAILY_INSERT',
        'RDS_CONTEST_CLIP_HOURLY_UPSERT',
        'GLUE_RDS_TO_S3_BOTS_CONTESTCLIP_HOURLY_UPSERT',
        'RDS_USER_COMMENT_BAN_HOURLY_UPSERT',
        'GLUE_RDS_TO_ S3_BOTS_USERCOMMENTBAN_HOURLY_UPSERT',
        'GLUE_RDS_TO_S3_LABEL_MAKER_TASKS_HOURLY_UPDATED',
        'GLUE_RDS_TO_S3_LABEL_MAKER_PROJECTS_HOURLY_UPDATED',
        'GLUE_RDS_TO_S3_LABEL_MAKER_ANNOTATIONS_HOURLY_UPDATED',
        'RDS_LABEL_MAKER_TASKS_HOURLY_UPSERT',
        'RDS_LABEL_MAKER_ANNOTATIONS_HOURLY_UPSERT',
        'RDS_LABEL_MAKER_PROJECTS_HOURLY_UPSERT',
        'GLUE_SNOWFLAKE_TO_RDS_AGG_USER_TO_USER_ENGAGEMENT_WEEKLY_DIRECT',
        'RDS_EXPPREVIEWCLIP_HOURLY_UPSERT_PROC',
        'GLUE_RDS_TO_S3_GROWTH_EXPPREVIEWCLIP_HOURLY_UPSERT',
        'RDS_SURVEY_QUESTIONS_HOURLY_UPSERT',
        'GLUE_RDS_TO_S3_SURVEY_QUESTIONS_HOURLY_UPSERT',
        'GLUE_RDS_TO_S3_GROWTH_PREVIEWCLIP_HOURLY_UPSERT',
        'RDS_PREVIEWCLIP_HOURLY_UPSERT_PROC',
        'WEB_HOOK_EVENT_HOURLY_INSERT',
        'RDS_HOOK_COMMENT_HOURLY_UPSERT', 
        'GLUE_RDS_TO_S3_RDS_HOOK_COMMENT_REPORT_HOURLY_UPSERT', 
        'GLUE_RDS_TO_S3_RDS_HOOK_REPORT_HOURLY_UPSERT', 
        'WEB_AUDIO_PLAYER_ACTIONS_HOURLY_INSERT', 
        'GLUE_RDS_TO_S3_RDS_HOOK_CREATOR_LABEL_HOURLY_UPSERT', 
        'GLUE_RDS_TO_S3_RDS_HOOK_COMMENT_REACTION_HOURLY_UPSERT', 
        'GLUE_RDS_TO_S3_RDS_CLIP_PRIMARY_HOOK_HOURLY_UPSERT', 
        'RDS_HOOK_COMMENT_REPORT_HOURLY_UPSERT', 
        'RDS_HOOK_CREATOR_LABEL_HOURLY_UPSERT', 
        'RDS_CLIP_PRIMARY_HOOK_HOURLY_UPSERT', 
        'WEB_AUDIO_PLAYER_ACTIONS_HOURLY_INSERT',
        'GLUE_RDS_TO_S3_RDS_HOOK_COMMENT_HOURLY_UPSERT', 
        'RDS_HOOK_REPORT_HOURLY_UPSERT', 
        'RDS_HOOK_COMMENT_REACTION_HOURLY_UPSERT',
        'RDS_VIDEO_SPRITE_HOURLY_UPSERT', 
        'GLUE_RDS_TO_S3_RDS_VIDEO_SPRITE_HOURLY_UPSERT',
        'GLUE_RDS_TO_S3_RDS_HOOK_REACTION_HOURLY_UPSERT',
        'RDS_HOOK_REACTION_HOURLY_UPSERT',
        'RDS_DISCOUNT_OFFER_HOURLY_UPSERT',
        'RDS_USAGE_PLAN_DISCOUNT_OFFER_HOURLY_UPSERT',
        'GLUE_RDS_TO_S3_BILLING_DISCOUNTOFFER_HOURLY_UPSERT',
        'GLUE_RDS_TO_S3_BILLING_USAGEPLANDISCOUNTOFFER_HOURLY_UPSERT',
        'GLUE_RDS_TO_S3_SURVEY_RESPONSES_HOURLY_UPSERT',
        'GLUE_RDS_TO_S3_BOTS_STUDIOWAITLIST_HOURLY_UPSERT',
        'RDS_SURVEY_RESPONSES_HOURLY_UPSERT',
        'RDS_STUDIO_WAITLIST_HOURLY_UPSERT',
        'GLUE_RDS_TO_S3_RDS_HOOK_MANUAL_QUALITY_LABEL_HOURLY_UPSERT', 
        'RDS_HOOK_MANUAL_QUALITY_LABEL_HOURLY_UPSERT',
        'GLUE_DDB_TO_S3_ORPHEUS_HOURLY_UPSERT',
        'DDB_ORPHEUS_HOURLY_UPSERT',
        'FACT_PLAY_HOURLY_UPSERT'
    );

    If (ARRAY_SIZE(:zero_row_tasks) > 0) THEN
        alert_message := '🚨 ALERT: The following tasks affected 0 rows in the past hour: ' || 
            ARRAY_TO_STRING(:zero_row_tasks, ', ');
        
        select post_to_slack('#data-alerts', :alert_message);
    END IF;

    RETURN '✅ Hourly Task Monitoring Completed';

EXCEPTION
    WHEN OTHER THEN
        select post_to_slack('#data-alerts', 
            'Error in TASK_MONITOR_HOURLY_JOB_PROC');
        RAISE;
END;
$$;
