create or replace task SUNO_PROD.PROD.ML_SONG_SUMMARY_INFO_HOURLY_INSERT
	warehouse=SUNO_PROD_ML_HOURLY_LARGE
	after SUNO_PROD.PROD.AGG_PLAY_INFO_HOURLY_V0_INSERT
    as DECLARE
        PDATE string;
        PHOUR string;
        PRE_PDATE string;
        PRE_PHOUR string;
        THRESHOLD_day number;
        THRESHOLD_hour number;
        THRESHOLD_min number;
        THRESHOLD_second number;
        start_time timestamp;
        row_count number;
    BEGIN
        -- todo: remove this after the ios play duration issue is fixed
        start_time := CURRENT_TIMESTAMP() - INTERVAL '20 minutes';
        -- Set the variables
        SELECT
            p_date,
            p_hour,
            pre_p_date,
            pre_p_hour
        INTO :PDATE, :PHOUR, :PRE_PDATE, :PRE_PHOUR
        FROM temp_agg_play_info;
        INSERT INTO ML_SONG_SUMMARY_INFO (
            song_id,
            web_total_play_cnt_0,
            web_total_play_duration_0,
            android_total_play_cnt_0,
            android_total_play_duration_0,
            ios_total_play_cnt_0,
            ios_total_play_duration_0,
            web_total_play_cnt_5,
            web_total_play_duration_5,
            android_total_play_cnt_5,
            android_total_play_duration_5,
            ios_total_play_cnt_5,
            ios_total_play_duration_5,
            sum_total_play_cnt_0,
            sum_total_play_duration_0,
            sum_total_play_cnt_5,
            sum_total_play_duration_5,
            web_non_creator_listen_duration_0,
            web_non_creator_listen_duration_5,
            android_non_creator_listen_duration_0,
            android_non_creator_listen_duration_5,
            ios_non_creator_listen_duration_0,
            ios_non_creator_listen_duration_5,
            sum_non_creator_listen_duration_0,
            sum_non_creator_listen_duration_5,
            p_date,
            p_hour
        )
        with a as (
            SELECT
                COALESCE(clip_id, 'UNKNOWN') AS song_id,
                SUM(CASE WHEN play_duration_threshold = 0 AND platform = 'web' THEN play_cnt ELSE 0 END) AS web_total_play_cnt_0,
                SUM(CASE WHEN play_duration_threshold = 0 AND platform = 'web' THEN play_duration_sec ELSE 0 END) AS web_total_play_duration_0,
                SUM(CASE WHEN play_duration_threshold = 0 AND platform = 'android' THEN play_cnt ELSE 0 END) AS android_total_play_cnt_0,
                SUM(CASE WHEN play_duration_threshold = 0 AND platform = 'android' THEN play_duration_sec ELSE 0 END) AS android_total_play_duration_0,
                SUM(CASE WHEN play_duration_threshold = 0 AND platform in ('ios', 'ipados') THEN play_cnt ELSE 0 END) AS ios_total_play_cnt_0,
                SUM(CASE WHEN play_duration_threshold = 0 AND platform in ('ios', 'ipados') THEN play_duration_sec ELSE 0 END) AS ios_total_play_duration_0,
                SUM(CASE WHEN play_duration_threshold = 5 AND platform = 'web' THEN play_cnt ELSE 0 END) AS web_total_play_cnt_5,
                SUM(CASE WHEN play_duration_threshold = 5 AND platform = 'web' THEN play_duration_sec ELSE 0 END) AS web_total_play_duration_5,
                SUM(CASE WHEN play_duration_threshold = 5 AND platform = 'android' THEN play_cnt ELSE 0 END) AS android_total_play_cnt_5,
                SUM(CASE WHEN play_duration_threshold = 5 AND platform = 'android' THEN play_duration_sec ELSE 0 END) AS android_total_play_duration_5,
                SUM(CASE WHEN play_duration_threshold = 5 AND platform in ('ios', 'ipados') THEN play_cnt ELSE 0 END) AS ios_total_play_cnt_5,
                SUM(CASE WHEN play_duration_threshold = 5 AND platform in ('ios', 'ipados') THEN play_duration_sec ELSE 0 END) AS ios_total_play_duration_5,
                SUM(CASE WHEN play_duration_threshold = 0 THEN play_cnt ELSE 0 END) AS sum_total_play_cnt_0,
                SUM(CASE WHEN play_duration_threshold = 0 THEN play_duration_sec ELSE 0 END) AS sum_total_play_duration_0,
                SUM(CASE WHEN play_duration_threshold = 5 THEN play_cnt ELSE 0 END) AS sum_total_play_cnt_5,
                SUM(CASE WHEN play_duration_threshold = 5 THEN play_duration_sec ELSE 0 END) AS sum_total_play_duration_5,
                SUM(CASE WHEN play_duration_threshold = 0 AND is_user_song_owner != true AND platform = 'web' THEN play_duration_sec ELSE 0 END) AS web_non_creator_listen_duration_0,
                SUM(CASE WHEN play_duration_threshold = 5 AND is_user_song_owner != true AND platform = 'web' THEN play_duration_sec ELSE 0 END) AS web_non_creator_listen_duration_5,
                SUM(CASE WHEN play_duration_threshold = 0 AND is_user_song_owner != true AND platform = 'android' THEN play_duration_sec ELSE 0 END) AS android_non_creator_listen_duration_0,
                SUM(CASE WHEN play_duration_threshold = 5 AND is_user_song_owner != true AND platform = 'android' THEN play_duration_sec ELSE 0 END) AS android_non_creator_listen_duration_5,
                SUM(CASE WHEN play_duration_threshold = 0 AND is_user_song_owner != true AND platform in ('ios', 'ipados') THEN play_duration_sec ELSE 0 END) AS ios_non_creator_listen_duration_0,
                SUM(CASE WHEN play_duration_threshold = 5 AND is_user_song_owner != true AND platform in ('ios', 'ipados') THEN play_duration_sec ELSE 0 END) AS ios_non_creator_listen_duration_5,
                SUM(CASE WHEN is_user_song_owner != true AND play_duration_threshold = 0 THEN play_duration_sec ELSE 0 END) AS sum_non_creator_listen_duration_0,
                SUM(CASE WHEN is_user_song_owner != true AND play_duration_threshold = 5 THEN play_duration_sec ELSE 0 END) AS sum_non_creator_listen_duration_5
            FROM agg_play_info_hourly_v0
            WHERE p_date = :PRE_PDATE AND p_hour = :PRE_PHOUR and play_duration_sec > 0
            GROUP BY clip_id
            union all
            select
                COALESCE(song_id, 'UNKNOWN') AS song_id,
                COALESCE(web_total_play_cnt_0, 0)  AS web_total_play_cnt_0,
                COALESCE(web_total_play_duration_0, 0) AS web_total_play_duration_0,
                COALESCE(android_total_play_cnt_0, 0) AS android_total_play_cnt_0,
                COALESCE(android_total_play_duration_0, 0) AS android_total_play_duration_0,
                COALESCE(ios_total_play_cnt_0, 0) AS ios_total_play_cnt_0,
                COALESCE(ios_total_play_duration_0, 0) AS ios_total_play_duration_0,
                COALESCE(web_total_play_cnt_5, 0) AS web_total_play_cnt_5,
                COALESCE(web_total_play_duration_5, 0) AS web_total_play_duration_5,
                COALESCE(android_total_play_cnt_5, 0) AS android_total_play_cnt_5,
                COALESCE(android_total_play_duration_5, 0) AS android_total_play_duration_5,
                COALESCE(ios_total_play_cnt_5, 0) AS ios_total_play_cnt_5,
                COALESCE(ios_total_play_duration_5, 0) AS ios_total_play_duration_5,
                COALESCE(sum_total_play_cnt_0, 0) AS sum_total_play_cnt_0,
                COALESCE(sum_total_play_duration_0, 0) AS sum_total_play_duration_0,
                COALESCE(sum_total_play_cnt_5, 0) AS sum_total_play_cnt_5,
                COALESCE(sum_total_play_duration_5, 0) AS sum_total_play_duration_5,
                COALESCE(web_non_creator_listen_duration_0, 0) AS web_non_creator_listen_duration_0,
                COALESCE(web_non_creator_listen_duration_5, 0) AS web_non_creator_listen_duration_5,
                COALESCE(android_non_creator_listen_duration_0, 0) AS android_non_creator_listen_duration_0,
                COALESCE(android_non_creator_listen_duration_5, 0) AS android_non_creator_listen_duration_5,
                COALESCE(ios_non_creator_listen_duration_0, 0) AS ios_non_creator_listen_duration_0,
                COALESCE(ios_non_creator_listen_duration_5, 0) AS ios_non_creator_listen_duration_5,
                COALESCE(sum_non_creator_listen_duration_0, 0) AS sum_non_creator_listen_duration_0,
                COALESCE(sum_non_creator_listen_duration_5, 0) AS sum_non_creator_listen_duration_5
            FROM ml_song_summary_info
            WHERE p_date = :PRE_PDATE AND p_hour = :PRE_PHOUR
        )
        select
            COALESCE(song_id, 'UNKNOWN') AS song_id,
            SUM(web_total_play_cnt_0) AS web_total_play_cnt_0,
            SUM(web_total_play_duration_0) AS web_total_play_duration_0,
            SUM(android_total_play_cnt_0) AS android_total_play_cnt_0,
            SUM(android_total_play_duration_0) AS android_total_play_duration_0,
            SUM(ios_total_play_cnt_0) AS ios_total_play_cnt_0,
            SUM(ios_total_play_duration_0) AS ios_total_play_duration_0,
            SUM(web_total_play_cnt_5) AS web_total_play_cnt_5,
            SUM(web_total_play_duration_5) AS web_total_play_duration_5,
            SUM(android_total_play_cnt_5) AS android_total_play_cnt_5,
            SUM(android_total_play_duration_5) AS android_total_play_duration_5,
            SUM(ios_total_play_cnt_5) AS ios_total_play_cnt_5,
            SUM(ios_total_play_duration_5) AS ios_total_play_duration_5,
            SUM(sum_total_play_cnt_0) AS sum_total_play_cnt_0,
            SUM(sum_total_play_duration_0) AS sum_total_play_duration_0,
            SUM(sum_total_play_cnt_5) AS sum_total_play_cnt_5,
            SUM(sum_total_play_duration_5) AS sum_total_play_duration_5,
            SUM(web_non_creator_listen_duration_0) AS web_non_creator_listen_duration_0,
            SUM(web_non_creator_listen_duration_5) AS web_non_creator_listen_duration_5,
            SUM(android_non_creator_listen_duration_0) AS android_non_creator_listen_duration_0,
            SUM(android_non_creator_listen_duration_5) AS android_non_creator_listen_duration_5,
            SUM(ios_non_creator_listen_duration_0) AS ios_non_creator_listen_duration_0,
            SUM(ios_non_creator_listen_duration_5) AS ios_non_creator_listen_duration_5,
            SUM(sum_non_creator_listen_duration_0) AS sum_non_creator_listen_duration_0,
            SUM(sum_non_creator_listen_duration_5) AS sum_non_creator_listen_duration_5,
            :PDATE AS p_date,
            :PHOUR AS p_hour
        from a
        group by song_id;

        -- call procedure ML_SONG_SUMMARY_INFO_VALIDATION
        call ML_SONG_SUMMARY_INFO_VALIDATION(:PDATE, :PHOUR, :PRE_PDATE, :PRE_PHOUR);

        select count(*) into :row_count from ML_SONG_SUMMARY_INFO where p_date = :PDATE and p_hour = :PHOUR;
        call TASK_MONITOR_INSERT_PROC('ML_SONG_SUMMARY_INFO_HOURLY_INSERT', :start_time, :row_count, 'SUCCESS', 'HOURLY');
    END;
