INSERT INTO SUNO_PROD.PROD.ML_SONG_SUMMARY_INFO_V1 (
    song_id,
    web_total_play_cnt_0,
    web_total_play_duration_0,
    web_total_play_cnt_5,
    web_total_play_duration_5,
    web_non_creator_listen_duration_0,
    web_non_creator_listen_duration_5,
    ios_total_play_cnt_0,
    ios_total_play_duration_0,
    ios_total_play_cnt_5,
    ios_total_play_duration_5,
    ios_non_creator_listen_duration_0,
    ios_non_creator_listen_duration_5,
    android_total_play_cnt_0,
    android_total_play_duration_0,
    android_total_play_cnt_5,
    android_total_play_duration_5,
    android_non_creator_listen_duration_0,
    android_non_creator_listen_duration_5,
    sum_total_play_cnt_0,
    sum_total_play_duration_0,
    sum_total_play_cnt_5,
    sum_total_play_duration_5,
    sum_non_creator_listen_duration_0,
    sum_non_creator_listen_duration_5,
    p_date,
    p_hour
)

WITH

-- Aggregate data by platform groups and thresholds
aggregated AS (
    SELECT
        clip_id AS song_id,
        CASE
            WHEN platform IN ('ios', 'ipados') THEN 'ios'
            ELSE platform
        END AS platform,
        play_duration_threshold,
        SUM(play_cnt) as play_cnt,
        SUM(play_duration_sec) as play_duration_sec,
        SUM(CASE WHEN NOT is_user_song_owner THEN play_duration_sec ELSE 0 END) as non_creator_duration
    FROM suno_prod.prod.agg_play_info_hourly
    WHERE 1=1
        -- Only include data up to (but not including) the current backfill hour
        AND (p_date < DATE(CURRENT_TIMESTAMP() - INTERVAL '2 HOUR')
             OR (p_date = DATE(CURRENT_TIMESTAMP() - INTERVAL '2 HOUR')
                 AND p_hour < HOUR(CURRENT_TIMESTAMP() - INTERVAL '2 HOUR')))
        AND play_duration_sec > 0
    GROUP BY clip_id, platform, play_duration_threshold
)

SELECT
    song_id,

    -- Web metrics
    SUM(CASE WHEN platform = 'web' AND play_duration_threshold = 0 THEN play_cnt ELSE 0 END) AS web_total_play_cnt_0,
    SUM(CASE WHEN platform = 'web' AND play_duration_threshold = 0 THEN play_duration_sec ELSE 0 END) AS web_total_play_duration_0,
    SUM(CASE WHEN platform = 'web' AND play_duration_threshold = 5 THEN play_cnt ELSE 0 END) AS web_total_play_cnt_5,
    SUM(CASE WHEN platform = 'web' AND play_duration_threshold = 5 THEN play_duration_sec ELSE 0 END) AS web_total_play_duration_5,
    SUM(CASE WHEN platform = 'web' AND play_duration_threshold = 0 THEN non_creator_duration ELSE 0 END) AS web_non_creator_listen_duration_0,
    SUM(CASE WHEN platform = 'web' AND play_duration_threshold = 5 THEN non_creator_duration ELSE 0 END) AS web_non_creator_listen_duration_5,

    -- iOS metrics
    SUM(CASE WHEN platform = 'ios' AND play_duration_threshold = 0 THEN play_cnt ELSE 0 END) AS ios_total_play_cnt_0,
    SUM(CASE WHEN platform = 'ios' AND play_duration_threshold = 0 THEN play_duration_sec ELSE 0 END) AS ios_total_play_duration_0,
    SUM(CASE WHEN platform = 'ios' AND play_duration_threshold = 5 THEN play_cnt ELSE 0 END) AS ios_total_play_cnt_5,
    SUM(CASE WHEN platform = 'ios' AND play_duration_threshold = 5 THEN play_duration_sec ELSE 0 END) AS ios_total_play_duration_5,
    SUM(CASE WHEN platform = 'ios' AND play_duration_threshold = 0 THEN non_creator_duration ELSE 0 END) AS ios_non_creator_listen_duration_0,
    SUM(CASE WHEN platform = 'ios' AND play_duration_threshold = 5 THEN non_creator_duration ELSE 0 END) AS ios_non_creator_listen_duration_5,

    -- Android metrics
    SUM(CASE WHEN platform = 'android' AND play_duration_threshold = 0 THEN play_cnt ELSE 0 END) AS android_total_play_cnt_0,
    SUM(CASE WHEN platform = 'android' AND play_duration_threshold = 0 THEN play_duration_sec ELSE 0 END) AS android_total_play_duration_0,
    SUM(CASE WHEN platform = 'android' AND play_duration_threshold = 5 THEN play_cnt ELSE 0 END) AS android_total_play_cnt_5,
    SUM(CASE WHEN platform = 'android' AND play_duration_threshold = 5 THEN play_duration_sec ELSE 0 END) AS android_total_play_duration_5,
    SUM(CASE WHEN platform = 'android' AND play_duration_threshold = 0 THEN non_creator_duration ELSE 0 END) AS android_non_creator_listen_duration_0,
    SUM(CASE WHEN platform = 'android' AND play_duration_threshold = 5 THEN non_creator_duration ELSE 0 END) AS android_non_creator_listen_duration_5,

    -- Total metrics
    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 THEN non_creator_duration ELSE 0 END) AS sum_non_creator_listen_duration_0,
    SUM(CASE WHEN play_duration_threshold = 5 THEN non_creator_duration ELSE 0 END) AS sum_non_creator_listen_duration_5,
    -- Set to next p_date and p_hour
    -- Then on subsequent runs, the procedure will pull this data and increment the p_date and p_hour
    DATE(CURRENT_TIMESTAMP() - INTERVAL '2 HOUR') AS p_date,
    HOUR(CURRENT_TIMESTAMP() - INTERVAL '2 HOUR') AS p_hour
FROM aggregated
GROUP BY song_id;
