CREATE OR REPLACE PROCEDURE PROD.TASK_MONITOR_DAILY_JOB_PROC()
RETURNS STRING
LANGUAGE SQL
AS 
$$
DECLARE 
    failed_tasks ARRAY;
    long_running_tasks ARRAY;
    alert_message STRING;
BEGIN
    -- Step 1: Get failed tasks into an array
    SELECT ARRAY_AGG(name)
    INTO :failed_tasks
    FROM (
        WITH tasks AS (
            SELECT UPPER(value) AS name 
            FROM TABLE(FLATTEN(INPUT => ARRAY_CONSTRUCT(
                -- features
                'FEATURE_TOP_PLAY_CLIPS_WEEKLY_DAILY_INSERT',
                -- web
                'WEB_PLAYLIST_ACTIONS_DAILY_INSERT',
                'WEB_PAGE_EVENT_DAILY_INSERT',
                'WEB_GENERAL_EVENT_MADLIB_DAILY_INSERT',
                'WEB_AUDIO_CREATION_DAILY_INSERT',
                'WEB_AUDIO_ACTIONS_DAILY_INSERT',
                'WEB_ARTIST_ACTIONS_DAILY_INSERT',
                -- mobile
                'APP_EVENT_METADATA_DAILY_UPSERT',
                'APP_GENERAL_DAILY_INSERT',
                'APP_ONBOARDING_ACTIONS_DAILY_INSERT',
                -- appsflyer
                'AF_CLICKS_DAILY_INSERT',
                'AF_COHORT_RETARGETING_DAILY_INSERT',
                'AF_COHORT_RETARGETING_TIMEZONE_VERSIONED_DAILY_INSERT',
                'AF_COHORT_RETARGETING_VERSIONED_DAILY_INSERT',
                'AF_COHORT_UNIFIED_DAILY_INSERT',
                'AF_COHORT_UNIFIED_TIMEZONE_VERSIONED_DAILY_INSERT',
                'AF_COHORT_UNIFIED_VERSIONED_DAILY_INSERT',
                'AF_COHORT_USER_ACQUISITION_DAILY_INSERT',
                'AF_COHORT_USER_ACQUISITION_TIMEZONE_VERSIONED_DAILY_INSERT',
                'AF_COHORT_USER_ACQUISITION_VERSIONED_DAILY_INSERT',
                'AF_CONVERSIONS_RETARGETING_DAILY_INSERT',
                'AF_INAPPS_DAILY_INSERT',
                'AF_INAPPS_RETARGETING_DAILY_INSERT',
                'AF_INSTALLS_DAILY_INSERT',
                'AF_SESSIONS_DAILY_INSERT',
                'AF_SESSIONS_RETARGETING_DAILY_INSERT',
                'AF_SKAD_INAPPS_DAILY_INSERT',
                'AF_SKAD_INSTALLS_DAILY_INSERT',
                'AF_SKAD_POSTBACKS_DAILY_INSERT',
                'AF_SKAD_POSTBACKS_COPY_DAILY_INSERT',
                'AF_SKAD_REDOWNLOADS_DAILY_INSERT',
                'AF_SKAD_SRN_ADVERTISING_DIMENSIONS_DAILY_INSERT',
                'AF_SSOT_RETARGETING_DAILY_INSERT',
                'AF_SSOT_UNIFIED_DAILY_INSERT',
                'AF_SSOT_USER_ACQUISITION_DAILY_INSERT',
                'AF_WEBSITE_EVENTS_DAILY_INSERT',
                'AF_WEBSITE_VISITS_DAILY_INSERT',
                -- other
                'USER_AGENT_METADATA_DAILY_INSERT',
                'REVENUECAT_TRANSACTION_DATA_DAILY_INSERT',
                'CLIP_REC_QUALITY_SCORE_DAILY_INSERT'
            )))
        ),
        task_history AS (
            SELECT UPPER(name) AS name
            FROM task_monitor
            WHERE status = 'SUCCESS' 
            AND DATE(START_TIME) = Date(CURRENT_DATE())
        )
        -- Find failed tasks
        SELECT d.name
        FROM tasks d
        LEFT JOIN task_history t ON d.name = t.name
        WHERE t.name IS NULL
    );

    -- TODO adding data affected is 0 tasks

    -- 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 day: ' || 
            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 > 3600 
    AND DATE(START_TIME) = DATEADD(day, -1, CURRENT_DATE())
    AND TYPE = 'DAILY';

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

    RETURN '✅ Daily Task Monitoring Completed';

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