-- Insert ML song summary info data by aggregating play information
-- This query inserts aggregated play metrics for songs on an hourly basis

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 (
    -- Current hour's play data from agg_play_info_hourly_v0
    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 = 'ios' THEN play_cnt ELSE 0 END) AS ios_total_play_cnt_0,
        SUM(CASE WHEN play_duration_threshold = 0 AND platform = 'ios' 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 = 'ios' THEN play_cnt ELSE 0 END) AS ios_total_play_cnt_5,
        SUM(CASE WHEN play_duration_threshold = 5 AND platform = 'ios' 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 = 'ios' 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 = 'ios' 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 = '{p_date}' AND p_hour = {p_hour} AND play_duration_sec > 0
    GROUP BY clip_id
    
    UNION ALL
    
    -- Previous hour's cumulative data from ml_song_summary_info
    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_p_date}' AND p_hour = {pre_p_hour}
)
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,
    '{p_date}' AS p_date,
    {p_hour} AS p_hour
FROM a
GROUP BY song_id;

