CREATE OR REPLACE PROCEDURE ML_SONG_SUMMARY_INFO_UPSERT_PROC(p_date VARCHAR, p_hour VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
    prev_p_date VARCHAR;
    prev_p_hour VARCHAR;
BEGIN
    prev_p_date := DATE(DATEADD(HOUR, -1, CREATE_HOUR_TS(:p_date, :p_hour)));
    prev_p_hour := HOUR(DATEADD(HOUR, -1, CREATE_HOUR_TS(:p_date, :p_hour)));

    -- Insert aggregated data into ML_SONG_SUMMARY_INFO
    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
    -- Get new data from agg_play_info_hourly_v1
    new_data AS (
        SELECT
            clip_id AS song_id,
            platform,
            play_duration_threshold,
            play_cnt,
            play_duration_sec,
            is_user_song_owner
        FROM suno_prod.prod.agg_play_info_hourly
        WHERE 1=1
            AND p_date = :p_date
            AND p_hour = :p_hour
            AND play_duration_sec > 0
    ),

    -- Get existing data from ml_song_summary_info
    existing_data AS (
        SELECT
            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
        FROM suno_prod.prod.ml_song_summary_info_v1
        WHERE 1=1
            AND p_date = :prev_p_date
            AND p_hour = :prev_p_hour
    ),

    -- Aggregate new data by platform groups and thresholds
    aggregated_new AS (
        SELECT
            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 new_data
        GROUP BY song_id, platform, play_duration_threshold
    ),

    -- Pivot the aggregated data using conditional aggregation
    pivoted_new AS (
        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
        FROM aggregated_new
        GROUP BY song_id
    ),

    -- Combine new and existing data
    combined_data AS (
        SELECT * FROM pivoted_new
        UNION ALL
        SELECT
            song_id,
            COALESCE(web_total_play_cnt_0, 0),
            COALESCE(web_total_play_duration_0, 0),
            COALESCE(web_total_play_cnt_5, 0),
            COALESCE(web_total_play_duration_5, 0),
            COALESCE(web_non_creator_listen_duration_0, 0),
            COALESCE(web_non_creator_listen_duration_5, 0),
            COALESCE(ios_total_play_cnt_0, 0),
            COALESCE(ios_total_play_duration_0, 0),
            COALESCE(ios_total_play_cnt_5, 0),
            COALESCE(ios_total_play_duration_5, 0),
            COALESCE(ios_non_creator_listen_duration_0, 0),
            COALESCE(ios_non_creator_listen_duration_5, 0),
            COALESCE(android_total_play_cnt_0, 0),
            COALESCE(android_total_play_duration_0, 0),
            COALESCE(android_total_play_cnt_5, 0),
            COALESCE(android_total_play_duration_5, 0),
            COALESCE(android_non_creator_listen_duration_0, 0),
            COALESCE(android_non_creator_listen_duration_5, 0),
            COALESCE(sum_total_play_cnt_0, 0),
            COALESCE(sum_total_play_duration_0, 0),
            COALESCE(sum_total_play_cnt_5, 0),
            COALESCE(sum_total_play_duration_5, 0),
            COALESCE(sum_non_creator_listen_duration_0, 0),
            COALESCE(sum_non_creator_listen_duration_5, 0)
        FROM existing_data
    )

    -- Final aggregation
    SELECT
        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(web_total_play_cnt_5) AS web_total_play_cnt_5,
        SUM(web_total_play_duration_5) AS web_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(ios_total_play_cnt_0) AS ios_total_play_cnt_0,
        SUM(ios_total_play_duration_0) AS ios_total_play_duration_0,
        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(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(android_total_play_cnt_0) AS android_total_play_cnt_0,
        SUM(android_total_play_duration_0) AS android_total_play_duration_0,
        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(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(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(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,
        -- 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
        :p_date AS p_date,
        :p_hour AS p_hour
    FROM combined_data
    GROUP BY song_id;
END;
$$;
