-- Step 4: Insert iOS legacy play sessions into staging table
-- Joins sessions with dimension tables and calculates final metrics
-- Note: Legacy data does not support unique play duration or song session IDs

INSERT INTO {{ ios_legacy_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
)
SELECT
    MD5_TO_UUID(CONCAT(ss.user_uid, ss.clip_id, ss.play_start_ts_utc)) AS id
    , ss.user_id
    , ss.user_uid
    , ss.clip_id
    , NULL AS song_session_id -- Legacy data does not have song session IDs

    -- 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(LEAST(ss.play_duration_seconds, 2 * dc.duration_sec) * 10) / 10, 3 * 60 * 60) AS play_duration_seconds -- Cap at 2x clip duration and 3 hours max
    -- Legacy data does not support unique play duration
    , NULL AS unique_play_duration_seconds
    , NULL AS unique_play_percent
    -- If the previous song session ended recently (within 10 seconds) and ended due to AutoplayNextSong, then the play_start_reason is 'auto_play'
    , CASE
        WHEN
            DATEDIFF('second', LAG(ss.play_end_ts_utc) OVER (PARTITION BY ss.user_uid ORDER BY ss.song_session_index), ss.play_start_ts_utc) <= 10
            AND LAG(ss.play_end_reason) OVER (PARTITION BY ss.user_uid ORDER BY ss.song_session_index) = 'auto_play'
            THEN 'auto_play'
        ELSE ss.play_start_reason
    END AS play_start_reason
    , CASE
        -- If the next song session starts soon (within 10 seconds) and started due to previous/next, then the play_end_reason is based on those actions
        WHEN
            DATEDIFF('second', ss.play_end_ts_utc, LEAD(ss.play_start_ts_utc) OVER (PARTITION BY ss.user_uid ORDER BY ss.song_session_index)) <= 10
            AND LEAD(ss.play_start_reason) OVER (PARTITION BY ss.user_uid ORDER BY ss.song_session_index) IN ('click_backward', 'click_forward')
            THEN LEAD(ss.play_start_reason) OVER (PARTITION BY ss.user_uid ORDER BY ss.song_session_index)
        -- If the next song session starts soon (within 10 seconds) and started due to PlayNewSong, then the play_end_reason is 'click_new_song'
        WHEN
            DATEDIFF('second', ss.play_end_ts_utc, LEAD(ss.play_start_ts_utc) OVER (PARTITION BY ss.user_uid ORDER BY ss.song_session_index)) <= 10
            AND LEAD(ss.play_start_reason) OVER (PARTITION BY ss.user_uid ORDER BY ss.song_session_index) = 'PlayNewSong'
            THEN 'click_new_song'
        -- Otherwise, use the play_end_reason
        ELSE ss.play_end_reason
    END AS play_end_reason
    , ss.is_play_continuous
    , NULL AS 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 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  -- 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(dc.duration_sec, 0) AS clip_duration_seconds
    , ss.is_user_song_owner
    , ss.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 (not available in legacy data)
    , NULL AS previous_song_session_id
    , NULL AS play_page_type
    , NULL AS play_context_type
    , NULL AS play_context_id
    , NULL AS play_context_metadata
    , NULL AS origin_context_id
    , NULL AS origin_item_type
    , NULL AS origin_song_id
    , NULL AS origin_item_metadata
    , NULL AS browser_history
    , NULL AS is_song_autoplay_queue
    , NULL AS audio_player_metadata

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

    -- Partition fields
    , ss.p_date
    , ss.p_hour
FROM
    {{ ios_legacy_play_sessions_table_name }} ss
LEFT JOIN
    DIM_CLIP dc
    ON ss.clip_id = dc.clip_id
WHERE 1=1
    AND ss.user_uid IS NOT NULL
    AND ss.clip_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 }})
        )
    );
