CREATE OR REPLACE PROCEDURE ml_recs_web_song_feed_insert_procedure(
    p_date VARCHAR
)
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
    query_sql STRING;
BEGIN    
    query_sql := '
        INSERT INTO ml_recs_web_song_feed (
            user_id,
            user_uid,
            clip_id,
            section_id,
            p_date,
            song_feed_item_seen_cnt,
            song_feed_item_title_clicked_cnt,
            song_feed_item_artist_clicked_cnt,
            song_feed_item_like_clicked_cnt,
            song_feed_item_play_clicked_cnt,
            song_feed_item_comments_clicked_cnt,
            total_action_cnt,
            total_play_cnt,
            play_duration_gt_5_sec_play_cnt,
            play_duration_gt_50_percent_clip_length_play_cnt,
            play_duration_gt_90_percent_clip_length_play_cnt,
            end_due_to_click_forward_play_cnt
        )
        WITH a AS (
            SELECT 
                user_id,
                user_uid,
                section_id,
                clip_id,
                p_date,
                SUM(CASE WHEN action_name = ''SongFeedItemSeen'' THEN action_cnt ELSE 0 END)           AS song_feed_item_seen_cnt,
                SUM(CASE WHEN action_name = ''SongFeedItemTitleClicked'' THEN action_cnt ELSE 0 END)  AS song_feed_item_title_clicked_cnt,
                SUM(CASE WHEN action_name = ''SongFeedItemArtistClicked'' THEN action_cnt ELSE 0 END) AS song_feed_item_artist_clicked_cnt,
                SUM(CASE WHEN action_name = ''SongFeedItemLikeClicked'' THEN action_cnt ELSE 0 END)   AS song_feed_item_like_clicked_cnt,
                SUM(CASE WHEN action_name = ''SongFeedItemPlayClicked'' THEN action_cnt ELSE 0 END)   AS song_feed_item_play_clicked_cnt,
                SUM(CASE WHEN action_name = ''SongFeedItemCommentsClicked'' THEN action_cnt ELSE 0 END) AS song_feed_item_comments_clicked_cnt,
                SUM(action_cnt)                                                                        AS total_action_cnt
            FROM ml_recs_web_song_feed_engagement
            WHERE p_date = ''' || p_date || '''
            GROUP BY user_id, user_uid, section_id, clip_id, p_date
        ), 
        b AS (
            SELECT 
                user_id,
                user_uid,
                clip_id,
                origin_context_id                                                                      AS section_id,
                p_date,
                COUNT(*)                                                                               AS total_play_cnt,
                SUM(CASE WHEN play_duration_seconds > 5 THEN 1 ELSE 0 END)                             AS play_duration_gt_5_sec_play_cnt,
                SUM(CASE WHEN unique_play_percent > 0.5 THEN 1 ELSE 0 END)                             AS play_duration_gt_50_percent_clip_length_play_cnt, 
                SUM(CASE WHEN unique_play_percent > 0.9 THEN 1 ELSE 0 END)                             AS play_duration_gt_90_percent_clip_length_play_cnt,
                SUM(CASE WHEN play_end_reason IN (''click_forward'') THEN 1 ELSE 0 END)                AS end_due_to_click_forward_play_cnt
            FROM fact_play 
            WHERE p_date = ''' || p_date || '''
                AND platform = ''web'' 
                AND user_uid IS NOT NULL 
                AND (play_context_type = ''featured_feed'' or origin_context_id IN (''featured_feed_following'', ''featured_feed_trending'', ''featured_feed_for_you''))
            GROUP BY user_id, user_uid, clip_id, origin_context_id, p_date
        )
        SELECT 
            a.user_id,
            a.user_uid,
            a.clip_id,
            a.section_id,
            a.p_date,
            -- Engagement metrics from table a
            a.song_feed_item_seen_cnt,
            a.song_feed_item_title_clicked_cnt,
            a.song_feed_item_artist_clicked_cnt,
            a.song_feed_item_like_clicked_cnt,
            a.song_feed_item_play_clicked_cnt,
            a.song_feed_item_comments_clicked_cnt,
            a.total_action_cnt,
            -- Play metrics from table b (with COALESCE for NULLs)
            COALESCE(b.total_play_cnt, 0)                                                              AS total_play_cnt,
            COALESCE(b.play_duration_gt_5_sec_play_cnt, 0)                                             AS play_duration_gt_5_sec_play_cnt,
            COALESCE(b.play_duration_gt_50_percent_clip_length_play_cnt, 0)                            AS play_duration_gt_50_percent_clip_length_play_cnt,
            COALESCE(b.play_duration_gt_90_percent_clip_length_play_cnt, 0)                            AS play_duration_gt_90_percent_clip_length_play_cnt,
            COALESCE(b.end_due_to_click_forward_play_cnt, 0)                                           AS end_due_to_click_forward_play_cnt
        FROM a
        LEFT JOIN b 
            ON a.user_id = b.user_id 
            AND a.user_uid = b.user_uid 
            AND a.clip_id = b.clip_id 
            AND a.section_id = b.section_id 
            AND a.p_date = b.p_date
    ';
            
    EXECUTE IMMEDIATE :query_sql;
    
    RETURN SQLROWCOUNT;
END;
$$;