INSERT INTO {{ hook_session_table_name }} (
    -- Primary key and identifiers
    id
    , hook_id
    , hook_play_id
    , user_id
    , user_uid
    , clip_id

    -- Playback info
    , session_start_ts_utc
    , session_end_ts_utc
    , hook_session_duration_seconds
    , play_duration_seconds
    , unique_play_duration_seconds
    , unique_play_percent
    , is_long_watch
    , repeat_count

    -- Like/unlike counts
    , like_count
    , undo_like_count
    , dislike_count
    , undo_dislike_count

    -- Comment counts
    , comment_open_tap_count
    , comment_begin_write_count
    , comment_count

    -- Share counts
    , share_tap_count
    , share_count

    -- Profile and follow/unfollow counts
    , profile_tap_count
    , follow_count
    , unfollow_count

    -- Playlist event counts
    , playlist_add_tap_count
    , playlist_add_count

    -- Create hook counts
    , create_hook_tap_count
    , create_hook_submit_count
    , create_hook_success_count
    , remix_tap_count

    -- Misc button taps & event fire counts
    , song_pill_tap_count
    , go_to_full_song_tap_count
    , song_title_tap_count

    -- Report/hide creator counts
    , report_count
    , hide_creator_count

    -- Context
    , context_type
    , context_id
    , recommendation_item_id
    , session_id
    , platform
    , app_version
    , is_mobile
    , country
    , city

    -- Partition fields
    , p_date
    , p_hour
)

WITH backend_events AS (
    -- Backend events: likes, dislikes, comments, follows, playlist adds, reports
    SELECT
        user_id
        , user_uid
        , hook_id
        , hook_session_id
        , event_timestamp
        , event_type
        , event_count
        , platform
        , p_date
        , p_hour
    FROM {{ stg_hook_session_backend_events_agg_table_name }}
    WHERE 1=1
        AND IFF(
            '{{ partition_start_date }}' = '{{ partition_end_date }}',
            -- Partition hours are within the same day
            p_date = '{{ partition_start_date }}' AND p_hour >= {{ partition_start_hour }} AND p_hour < {{ partition_end_hour }},
            -- Partition hours are across multiple days
            (
                (p_date = '{{ partition_start_date }}' AND p_hour >= {{ partition_start_hour }})
                OR
                (p_date > '{{ partition_start_date }}' AND p_date < '{{ partition_end_date }}')
                OR
                (p_date = '{{ partition_end_date }}' AND p_hour < {{ partition_end_hour }})
            )
        )
),

segment_events AS (
    -- Segment/frontend events: taps and clicks across platforms (Android, iOS, Web)
    SELECT
        user_id
        , user_uid
        , hook_id
        , hook_session_id
        , event_timestamp
        , event_type
        , event_count
        , platform
        , p_date
        , p_hour
    FROM {{ stg_hook_session_segment_events_table_name }}
    WHERE 1=1
        AND IFF(
            '{{ partition_start_date }}' = '{{ partition_end_date }}',
            -- Partition hours are within the same day
            p_date = '{{ partition_start_date }}' AND p_hour >= {{ partition_start_hour }} AND p_hour < {{ partition_end_hour }},
            -- Partition hours are across multiple days
            (
                (p_date = '{{ partition_start_date }}' AND p_hour >= {{ partition_start_hour }})
                OR
                (p_date > '{{ partition_start_date }}' AND p_date < '{{ partition_end_date }}')
                OR
                (p_date = '{{ partition_end_date }}' AND p_hour < {{ partition_end_hour }})
            )
        )
),

all_events AS (
    SELECT * FROM backend_events
    UNION ALL
    SELECT * FROM segment_events
),

event_aggregates AS (
    SELECT
        hook_session_id
        , hook_id
        , user_id
        , user_uid

        -- Like/unlike counts
        , SUM(CASE WHEN event_type = 'like' THEN event_count ELSE 0 END) AS like_count
        , SUM(CASE WHEN event_type = 'undo_like' THEN event_count ELSE 0 END) AS undo_like_count
        , SUM(CASE WHEN event_type = 'dislike' THEN event_count ELSE 0 END) AS dislike_count
        , SUM(CASE WHEN event_type = 'undo_dislike' THEN event_count ELSE 0 END) AS undo_dislike_count

        -- Comment counts
        , SUM(CASE WHEN event_type = 'comment_open_tap' THEN event_count ELSE 0 END) AS comment_open_tap_count
        , SUM(CASE WHEN event_type = 'comment_begin_write' THEN event_count ELSE 0 END) AS comment_begin_write_count
        , SUM(CASE WHEN event_type = 'comment' THEN event_count ELSE 0 END) AS comment_count

        -- Share counts
        , SUM(CASE WHEN event_type = 'share_tap' THEN event_count ELSE 0 END) AS share_tap_count
        , SUM(CASE WHEN event_type = 'share' THEN event_count ELSE 0 END) AS share_count

        -- Profile and follow/unfollow counts
        , SUM(CASE WHEN event_type = 'profile_tap' THEN event_count ELSE 0 END) AS profile_tap_count
        , SUM(CASE WHEN event_type = 'follow' THEN event_count ELSE 0 END) AS follow_count
        , SUM(CASE WHEN event_type = 'unfollow' THEN event_count ELSE 0 END) AS unfollow_count

        -- Playlist event counts
        , SUM(CASE WHEN event_type = 'playlist_add_tap' THEN event_count ELSE 0 END) AS playlist_add_tap_count
        , SUM(CASE WHEN event_type = 'playlist_add' THEN event_count ELSE 0 END) AS playlist_add_count

        -- Create hook counts
        , SUM(CASE WHEN event_type = 'create_hook_tap' THEN event_count ELSE 0 END) AS create_hook_tap_count
        , SUM(CASE WHEN event_type = 'create_hook_submit' THEN event_count ELSE 0 END) AS create_hook_submit_count
        , SUM(CASE WHEN event_type = 'create_hook_success' THEN event_count ELSE 0 END) AS create_hook_success_count
        , SUM(CASE WHEN event_type = 'remix_tap' THEN event_count ELSE 0 END) AS remix_tap_count

        -- Misc button taps & event fire counts
        , SUM(CASE WHEN event_type = 'song_pill_tap' THEN event_count ELSE 0 END) AS song_pill_tap_count
        , SUM(CASE WHEN event_type = 'go_to_full_song_tap' THEN event_count ELSE 0 END) AS go_to_full_song_tap_count
        , SUM(CASE WHEN event_type = 'song_title_tap' THEN event_count ELSE 0 END) AS song_title_tap_count

        -- Report/hide creator counts
        , SUM(CASE WHEN event_type = 'report' THEN event_count ELSE 0 END) AS report_count
        , SUM(CASE WHEN event_type = 'hide_creator' THEN event_count ELSE 0 END) AS hide_creator_count

    FROM all_events
    WHERE hook_session_id IS NOT NULL  -- Only include events that successfully got a hook_session_id
      AND hook_id IS NOT NULL
      AND user_id IS NOT NULL
      AND user_uid IS NOT NULL
    GROUP BY
        hook_session_id
        , hook_id
        , user_id
        , user_uid
),

final_result AS (
    SELECT
        -- Primary key and identifiers
        hp.hook_session_id AS id
        , hp.hook_id
        , hp.id AS hook_play_id
        , hp.user_id
        , hp.user_uid
        , hp.clip_id

        -- Playback info from fact_hook_play
        , hp.play_start_ts_utc AS session_start_ts_utc
        , hp.play_end_ts_utc AS session_end_ts_utc
        , hp.hook_session_duration_seconds
        , hp.play_duration_seconds
        , hp.unique_play_duration_seconds
        , hp.unique_play_percent
        , hp.play_duration_seconds >= 5 AS is_long_watch
        , hp.repeat_count

        -- Like/unlike counts (using COALESCE to handle NULL for sessions with no events)
        , COALESCE(ea.like_count, 0) AS like_count
        , COALESCE(ea.undo_like_count, 0) AS undo_like_count
        , COALESCE(ea.dislike_count, 0) AS dislike_count
        , COALESCE(ea.undo_dislike_count, 0) AS undo_dislike_count

        -- Comment counts
        , COALESCE(ea.comment_open_tap_count, 0) AS comment_open_tap_count
        , COALESCE(ea.comment_begin_write_count, 0) AS comment_begin_write_count
        , COALESCE(ea.comment_count, 0) AS comment_count

        -- Share counts
        , COALESCE(ea.share_tap_count, 0) AS share_tap_count
        , COALESCE(ea.share_count, 0) AS share_count

        -- Profile and follow/unfollow counts
        , COALESCE(ea.profile_tap_count, 0) AS profile_tap_count
        , COALESCE(ea.follow_count, 0) AS follow_count
        , COALESCE(ea.unfollow_count, 0) AS unfollow_count

        -- Playlist event counts
        , COALESCE(ea.playlist_add_tap_count, 0) AS playlist_add_tap_count
        , COALESCE(ea.playlist_add_count, 0) AS playlist_add_count

        -- Create hook counts
        , COALESCE(ea.create_hook_tap_count, 0) AS create_hook_tap_count
        , COALESCE(ea.create_hook_submit_count, 0) AS create_hook_submit_count
        , COALESCE(ea.create_hook_success_count, 0) AS create_hook_success_count
        , COALESCE(ea.remix_tap_count, 0) AS remix_tap_count

        -- Misc button taps & event fire counts
        , COALESCE(ea.song_pill_tap_count, 0) AS song_pill_tap_count
        , COALESCE(ea.go_to_full_song_tap_count, 0) AS go_to_full_song_tap_count
        , COALESCE(ea.song_title_tap_count, 0) AS song_title_tap_count

        -- Report/hide creator counts
        , COALESCE(ea.report_count, 0) AS report_count
        , COALESCE(ea.hide_creator_count, 0) AS hide_creator_count

        -- Context from hook_play
        , hp.context_type
        , hp.context_id
        , hp.recommendation_item_id
        , hp.session_id
        , hp.platform
        , hp.app_version
        , hp.is_mobile
        , hp.country
        , hp.city

        -- Partition fields
        , hp.p_date
        , hp.p_hour

    FROM {{ fact_hook_play_table_name }} hp
    LEFT JOIN event_aggregates ea
        ON hp.hook_session_id = ea.hook_session_id
        AND hp.hook_id = ea.hook_id
        AND hp.user_id = ea.user_id
        AND hp.user_uid = ea.user_uid
    WHERE 1=1
        AND IFF(
            '{{ partition_start_date }}' = '{{ partition_end_date }}',
            -- Partition hours are within the same day
            hp.p_date = '{{ partition_start_date }}' AND hp.p_hour >= {{ partition_start_hour }} AND hp.p_hour < {{ partition_end_hour }},
            -- Partition hours are across multiple days
            (
                (hp.p_date = '{{ partition_start_date }}' AND hp.p_hour >= {{ partition_start_hour }})
                OR
                (hp.p_date > '{{ partition_start_date }}' AND hp.p_date < '{{ partition_end_date }}')
                OR
                (hp.p_date = '{{ partition_end_date }}' AND hp.p_hour < {{ partition_end_hour }})
            )
        )
)

SELECT
    -- Primary key and identifiers
    id
    , hook_id
    , hook_play_id
    , user_id
    , user_uid
    , clip_id

    -- Playback info
    , session_start_ts_utc
    , session_end_ts_utc
    , hook_session_duration_seconds
    , play_duration_seconds
    , unique_play_duration_seconds
    , unique_play_percent
    , is_long_watch
    , repeat_count

    -- Like/unlike counts
    , like_count
    , undo_like_count
    , dislike_count
    , undo_dislike_count

    -- Comment counts
    , comment_open_tap_count
    , comment_begin_write_count
    , comment_count

    -- Share counts
    , share_tap_count
    , share_count

    -- Profile and follow/unfollow counts
    , profile_tap_count
    , follow_count
    , unfollow_count

    -- Playlist event counts
    , playlist_add_tap_count
    , playlist_add_count

    -- Create hook counts
    , create_hook_tap_count
    , create_hook_submit_count
    , create_hook_success_count
    , remix_tap_count

    -- Misc button taps & event fire counts
    , song_pill_tap_count
    , go_to_full_song_tap_count
    , song_title_tap_count

    -- Report/hide creator counts
    , report_count
    , hide_creator_count

    -- Context
    , context_type
    , context_id
    , recommendation_item_id
    , session_id
    , platform
    , app_version
    , is_mobile
    , country
    , city

    -- Partition fields
    , p_date
    , p_hour
FROM final_result
