-- Step 4: Insert web play sessions into staging table
-- Joins sessions with dimension tables and calculates final metrics
-- Includes origin tracking logic from WEB_USER_EVENT for song feed item clicks

INSERT INTO {{ web_stg_play_table_name }} (
    -- Primary key and identifiers
    id
    , user_id
    , user_uid
    , clip_id
    , song_session_id
    -- Playback
    , play_start_ts_utc
    , play_end_ts_utc
    , song_session_duration_seconds
    , play_duration_seconds
    , unique_play_duration_seconds
    , unique_play_percent
    , play_start_reason
    , play_end_reason
    , is_play_continuous
    , is_manually_started
    , is_high_intent_listening
    , audio_player_events
    -- Clip info
    , clip_duration_seconds
    , is_user_song_owner
    , is_play_during_generation
    , clip_created_at
    , model
    , model_family
    -- Context info
    , previous_song_session_id
    , play_page_type
    , play_context_type
    , play_context_id
    , play_context_metadata
    , origin_context_id
    , origin_item_type
    , origin_song_id
    , origin_item_metadata
    , browser_history
    , is_song_autoplay_queue
    , audio_player_metadata
    -- Device metadata
    , session_id
    , platform
    , app_version
    , is_mobile
    , country
    , city
    -- Partition fields
    , p_date
    , p_hour
)
WITH

song_feed_item_clicked AS (
    SELECT
        user_uid
        , session_id
        , client_timestamp
        , CASE
            WHEN action_name = 'SongFeedItemTitleClicked' THEN 'song_feed_item_title'
            WHEN action_name = 'SongFeedItemArtistClicked' THEN 'song_feed_item_artist'
            WHEN action_name = 'SongCardTitleClicked' THEN 'song_card_title'
            WHEN action_name = 'SongCardArtistClicked' THEN 'song_card_artist'
            WHEN action_name = 'SongRowTitleClicked' AND component_context = 'library' THEN 'library_song_row_title'
            WHEN action_name = 'SongRowTitleClicked' AND component_context = 'create' THEN 'create_song_row_title'
            WHEN action_name = 'PlaylistCardTitleClicked' THEN 'playlist_card_title'
            ELSE NULL
        END AS clicked_on_item_type
        , principal_object_value AS clicked_on_song_id
        , '/' || principal_object_type || '/' || principal_object_value AS click_url
        , PARSE_JSON(context):sectionId::VARCHAR AS context_id
        , PARSE_JSON(context):sectionTitle::VARCHAR AS context_title
        , OBJECT_CONSTRUCT(
            'context_index', PARSE_JSON(context):index::INTEGER,
            'song_comment_count', PARSE_JSON(context):commentCount,
            'song_like_count', PARSE_JSON(context):likeCount,
            'song_play_count', PARSE_JSON(context):playCount
        ) AS clicked_on_item_metadata
    FROM
        {{ web_user_event_dedup_table_name }}
    WHERE 1=1
        AND action_name IN (
        'SongFeedItemTitleClicked', 'SongFeedItemArtistClicked',
        'SongCardTitleClicked', 'SongCardArtistClicked',
        'SongRowTitleClicked', 'PlaylistCardTitleClicked'
    )
)

, last_song_feed_click AS (
    SELECT
        ss.song_session_id
        , ss.session_id
        , MAX_BY(sfic.context_id, sfic.client_timestamp::TIMESTAMP_NTZ(9)) AS origin_context_id
        , MAX_BY(sfic.context_title, sfic.client_timestamp::TIMESTAMP_NTZ(9)) AS origin_context_title
        , MAX_BY(sfic.click_url, sfic.client_timestamp::TIMESTAMP_NTZ(9)) AS origin_click_url
        , MAX_BY(sfic.clicked_on_item_type, sfic.client_timestamp::TIMESTAMP_NTZ(9)) AS clicked_on_item_type
        , MAX_BY(sfic.clicked_on_song_id, sfic.client_timestamp::TIMESTAMP_NTZ(9)) AS clicked_on_song_id
        , MAX_BY(sfic.clicked_on_item_metadata, sfic.client_timestamp::TIMESTAMP_NTZ(9)) AS clicked_on_item_metadata
    FROM
        {{ web_play_sessions_table_name}} ss
    LEFT JOIN
        song_feed_item_clicked sfic
        ON sfic.session_id = ss.session_id
        AND sfic.client_timestamp::TIMESTAMP_NTZ(9) BETWEEN ss.play_start_ts_utc - INTERVAL '1 minute' AND ss.play_start_ts_utc
    WHERE 1=1
        AND ss.play_page_type IN ('artist', 'song')
        AND CASE
            WHEN ss.play_page_type = 'song' THEN sfic.click_url = ss.play_context_metadata:click_source_url::VARCHAR
            ELSE TRUE
        END
    GROUP BY
        ss.song_session_id
        , ss.session_id
)

SELECT
    MD5_TO_UUID(CONCAT(ss.user_uid, ss.clip_id, ss.song_session_id)) AS id
    , du.user_id
    , ss.user_uid
    , ss.clip_id
    , ss.song_session_id

    -- Playback
    , ss.play_start_ts_utc
    , ss.play_end_ts_utc
    , LEAST(ss.song_session_duration_seconds, 6 * 60 * 60) AS song_session_duration_seconds -- Cap at 6 hours
    , LEAST(CEIL(ss.play_duration_seconds * 10) / 10, 3 * 60 * 60) AS play_duration_seconds -- Cap at 3 hours
    , COALESCE(CEIL(ups.unique_play_duration_seconds * 10) / 10, 0) AS unique_play_duration_seconds
    , CASE
        WHEN COALESCE(ss.clip_duration_seconds, dc.duration_sec) = 0 THEN NULL
        ELSE LEAST(DIV0(CEIL(ups.unique_play_duration_seconds * 10) / 10, COALESCE(ss.clip_duration_seconds, dc.duration_sec)), 1)
    END AS unique_play_percent
    , ss.play_start_reason
    , ss.play_end_reason
    , ss.is_play_continuous
    , ss.is_manually_started
    , OBJECT_CONSTRUCT(
        -- For HIL v1, a play is considered high-intent if (1) the user is not the owner of the song, or (2) the user is the owner of the song but listened 2+ hours after creation.
        'v1',
        CASE
            WHEN NOT ss.is_user_song_owner THEN TRUE  -- Not their song = always high intent
            WHEN ss.is_user_song_owner AND
                 COALESCE(
                     ss.audio_player_events[0]:server_timestamp::TIMESTAMP_NTZ(9),
                     CREATE_HOUR_TS(ss.p_date::STRING, ss.p_hour)
                 ) >= dc.created_at + INTERVAL '2 HOUR'
            THEN TRUE  -- Own song & listened 2+ hours after creation = high intent
            ELSE FALSE  -- Own song & listened within 2 hours of creation = not high intent
            END,
        -- For HIL v2, a play is considered high-intent if the play duration is greater than 5 seconds AND it meets HIL v1 criteria.
        'v2',
        CASE
            WHEN ss.play_duration_seconds < 5 THEN FALSE -- Play duration is less than 5 seconds = not high intent
            WHEN NOT ss.is_user_song_owner THEN TRUE -- 5+ seconds & not own song = always high intent
            WHEN ss.is_user_song_owner AND
                 COALESCE(
                     ss.audio_player_events[0]:server_timestamp::TIMESTAMP_NTZ(9),
                     CREATE_HOUR_TS(ss.p_date::STRING, ss.p_hour)
                 ) >= dc.created_at + INTERVAL '2 HOUR'
            THEN TRUE -- 5+ seconds & own song & listened 2+ hours after creation = high intent
            ELSE FALSE -- 5+ seconds & own song & listened within 2 hours of creation = not high intent
            END
    ) AS is_high_intent_listening
    , ss.audio_player_events

    -- Clip info
    , COALESCE(ss.clip_duration_seconds, dc.duration_sec) AS clip_duration_seconds
    , ss.is_user_song_owner
    , ss.clip_duration_seconds = 0 AS is_play_during_generation
    , dc.created_at::TIMESTAMP_NTZ(9) AS clip_created_at
    , dc.model_name AS model
    , CASE
        WHEN dc.model_name LIKE '%v2%' THEN 'v2'
        WHEN dc.model_name LIKE '%v3p5%' OR dc.model_name LIKE '%v3-5%' THEN 'v3.5'
        WHEN dc.model_name LIKE '%v3%' THEN 'v3'
        WHEN dc.model_name LIKE '%v4%' OR dc.model_name LIKE '%chirp-up%' THEN 'v4'
        WHEN dc.model_name LIKE '%auk-turbo%' THEN 'v4.5-all'
        WHEN dc.model_name LIKE '%auk%' OR dc.model_name LIKE '%ahi%' THEN 'v4.5'
        WHEN dc.model_name like '%bluejay%' OR dc.model_name LIKE '%bass%' THEN 'v4.5+'
        WHEN dc.model_name LIKE '%crow%' OR dc.model_name LIKE '%carp%' THEN 'v5'
        WHEN dc.model_name LIKE '%stem%' THEN 'stem'
        WHEN dc.model_name = '' THEN 'no_model'
        ELSE dc.model_name
    END AS model_family

    -- Context info
    , ss.previous_song_session_id
    , ss.play_page_type
    , ss.play_context_type
    , ss.play_context_id
    , ss.play_context_metadata
    , lsfc.origin_context_id AS origin_context_id
    , lsfc.clicked_on_item_type AS origin_item_type
    , lsfc.clicked_on_song_id AS origin_song_id
    , lsfc.clicked_on_item_metadata AS origin_item_metadata
    , ss.browser_history
    , ss.is_song_autoplay_queue
    , ss.audio_player_metadata

    -- Device metadata
    , ss.session_id
    , 'web' AS platform
    , NULL AS app_version
    , ss.is_mobile
    , ss.country
    , ss.city

    -- Partition fields
    , ss.p_date
    , ss.p_hour
FROM
    {{ web_play_sessions_table_name }} ss
LEFT JOIN
    DIM_USER du
    ON ss.user_uid = du.user_uid
LEFT JOIN
    DIM_CLIP dc
    ON ss.clip_id = dc.clip_id
LEFT JOIN
    {{ web_unique_play_segments_table_name }} ups
    ON ups.user_uid = ss.user_uid
    AND ups.clip_id = ss.clip_id
    AND ups.song_session_id = ss.song_session_id
LEFT JOIN
    last_song_feed_click lsfc
    ON lsfc.song_session_id = ss.song_session_id
    AND lsfc.session_id = ss.session_id
WHERE 1=1
    AND ss.user_uid IS NOT NULL
    AND ss.clip_id IS NOT NULL
    AND ss.song_session_id IS NOT NULL

    -- Only include records with actual play time (cap at 3 hours)
    AND ss.play_duration_seconds > 0
    AND ss.play_duration_seconds <= 3 * 60 * 60

    -- Partition filtering
    AND IFF(
        '{{ partition_start_date }}' = '{{ partition_end_date }}',
        ss.p_date = '{{ partition_start_date }}' AND ss.p_hour >= {{ partition_start_hour }} AND ss.p_hour < {{ partition_end_hour }},
        (
            (ss.p_date = '{{ partition_start_date }}' AND ss.p_hour >= {{ partition_start_hour }}) OR
            (ss.p_date > '{{ partition_start_date }}' AND ss.p_date < '{{ partition_end_date }}') OR
            (ss.p_date = '{{ partition_end_date }}' AND ss.p_hour < {{ partition_end_hour }})
        )
    );
