CREATE OR REPLACE PROCEDURE PROD.BZ_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 bz_hourly_task 
    as 
    select "name" as name from table(result_scan(last_query_id())) where "name" like '%HOURLY%' and "name" like '%BZ_%' and "state" = 'started';
    SELECT ARRAY_AGG(name)
    INTO :failed_tasks
    FROM (
        WITH tasks AS (
            SELECT UPPER(name) AS name 
            FROM bz_hourly_task
            where name not in (
                'BZ_TASK_MONITOR_HOURLY_JOB',
                'TASK_MONITOR_HOURLY_JOB'
            )
        ),
        task_history AS (
            SELECT UPPER(name) AS name
            FROM bz_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 bz_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 bz_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';

    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 BZ_TASK_MONITOR_HOURLY_JOB_PROC');
        RAISE;
END;
$$;
