INSERT INTO {{ fact_hook_play_table_name }} (
    -- Primary key and identifiers
    id
    , user_id
    , user_uid
    , hook_id
    , hook_session_id
    , clip_id
    -- Playback
    , play_start_ts_utc
    , play_end_ts_utc
    , hook_session_duration_seconds
    , play_duration_seconds
    , unique_play_duration_seconds
    , unique_play_percent
    , hook_start_reason
    , hook_end_reason
    , is_play_continuous
    , repeat_count
    , hook_player_events
    , is_high_intent_listening
    -- Hook info
    , hook_duration_seconds
    -- Clip info
    , clip_duration_seconds
    , is_user_song_owner
    -- Context info
    , previous_hook_session_id
    , previous_clip_session_id
    , context_type
    , context_id
    , recommendation_item_id
    , hook_player_metadata
    -- Device metadata
    , session_id
    , platform
    , app_version
    , is_mobile
    , country
    , city
    -- Partition fields
    , p_date
    , p_hour
)

SELECT
    -- Primary key and identifiers
    MD5_TO_UUID(CONCAT(hs.user_uid, hs.hook_id, hs.hook_session_id)) AS id
    , du.user_id
    , hs.user_uid
    , hs.hook_id
    , hs.hook_session_id
    , dh.original_clip AS clip_id

    -- Playback
    , hs.play_start_ts_utc
    , hs.play_end_ts_utc
    , LEAST(hs.hook_session_duration_seconds, 6 * 60 * 60) AS hook_session_duration_seconds
    , LEAST(CEIL(hs.play_duration_seconds * 10) / 10, 3 * 60 * 60) AS play_duration_seconds
    , COALESCE(CEIL(ups.unique_play_duration_seconds * 10) / 10, 0) AS unique_play_duration_seconds
    , LEAST(DIV0(CEIL(ups.unique_play_duration_seconds * 10) / 10, dh.video_duration), 1) AS unique_play_percent
    , hs.hook_start_reason
    , hs.hook_end_reason
    , hs.is_play_continuous
    , hs.repeat_count
    , hs.hook_player_events

    -- HIL Info
    , OBJECT_CONSTRUCT(
        -- For HIL v1, a hook play is considered high-intent if (1) the user is not the owner of the hook, or (2) the user is the owner of the hook but listened 2+ hours after creation.
        'v1',
        CASE
            WHEN NOT hs.is_user_hook_owner THEN TRUE  -- Not their hook = always high intent
            WHEN hs.is_user_hook_owner AND
                 COALESCE(
                     hs.hook_player_events[0]:server_timestamp::TIMESTAMP_NTZ(9),
                     CREATE_HOUR_TS(hs.p_date::STRING, hs.p_hour)
                 ) >= dh.created_at + INTERVAL '2 HOUR'
            THEN TRUE  -- Own hook & listened 2+ hours after creation = high intent
            ELSE FALSE  -- Own hook & listened within 2 hours of creation = not high intent
            END,
        -- For HIL v2, a hook play is considered high-intent if the hook play duration is greater than 5 seconds AND it meets HIL v1 criteria.
        'v2',
        CASE
            WHEN play_duration_seconds < 5 THEN FALSE -- Hook play duration is less than 5 seconds = not high intent
            WHEN NOT hs.is_user_hook_owner THEN TRUE -- 5+ seconds & not own hook = always high intent
            WHEN hs.is_user_hook_owner AND
                 COALESCE(
                     hs.hook_player_events[0]:server_timestamp::TIMESTAMP_NTZ(9),
                     CREATE_HOUR_TS(hs.p_date::STRING, hs.p_hour)
                 ) >= dh.created_at + INTERVAL '2 HOUR'
            THEN TRUE -- 5+ seconds & own hook & listened 2+ hours after creation = high intent
            ELSE FALSE -- 5+ seconds & own hook & listened within 2 hours of creation = not high intent
            END
    ) AS is_high_intent_listening

    -- Hook
    , dh.video_duration AS hook_duration_seconds

    -- Clip
    , dc.duration_sec AS clip_duration_seconds
    , COALESCE(hs.is_user_hook_owner, dh.user_id = du.user_id, FALSE) AS is_user_song_owner

    -- Context Info
    , hs.previous_hook_session_id AS previous_hook_session_id
    , hs.previous_clip_session_id AS previous_clip_session_id
    , hs.context_type
    , hs.context_id
    , hs.recommendation_item_id
    , hs.hook_player_metadata

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

    -- Partition fields
    , hs.p_date
    , hs.p_hour
FROM {{ sessions_table_name }} hs
LEFT JOIN {{ unique_segments_table_name }} ups
    ON ups.user_uid = hs.user_uid
    AND ups.hook_session_id = hs.hook_session_id
    AND ups.hook_id = hs.hook_id
-- TODO: Build and replace with dim_hook
LEFT JOIN dim_hook dh ON dh.id = hs.hook_id
LEFT JOIN dim_clip dc ON dc.clip_id = dh.original_clip
LEFT JOIN dim_user du ON du.user_uid = hs.user_uid
WHERE 1=1
    AND IFF(
        '{{ partition_start_date }}' = '{{ partition_end_date }}',
        hs.p_date = '{{ partition_start_date }}' AND hs.p_hour >= {{ partition_start_hour }} AND hs.p_hour < {{ partition_end_hour }},
        (
            (hs.p_date = '{{ partition_start_date }}' AND hs.p_hour >= {{ partition_start_hour }}) OR
            (hs.p_date > '{{ partition_start_date }}' AND hs.p_date < '{{ partition_end_date }}') OR
            (hs.p_date = '{{ partition_end_date }}' AND hs.p_hour < {{ partition_end_hour }})
        )
    )
    AND hs.user_uid IS NOT NULL
    AND hs.hook_id IS NOT NULL
    AND hs.hook_session_id IS NOT NULL
    AND hs.play_duration_seconds > 0
    AND hs.play_duration_seconds <= 3 * 60 * 60
