INSERT INTO {{ stg_hook_session_segment_events_table_name }} (
    user_id
    , user_uid
    , hook_id
    , hook_session_id
    , recommendation_item_id
    , session_id
    , event_timestamp
    , event_type
    , event_count
    , platform
    , p_date
    , p_hour
)

WITH frontend_events_raw AS (
    -- Frontend events: taps and clicks across platforms (Android, iOS, Web)

    -- Comment tapped (Android)
    SELECT
        user_uid
        , hook_id
        , NULL AS hook_session_id  -- Future: when mobile logs hook_session_id directly
        , recommendation_item_id  -- Already extracted in staging table
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'comment_open_tap' AS event_type
        , 1 AS event_count
        , CASE WHEN os_name = 'iPadOS' THEN 'ios' ELSE LOWER(os_name) END AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__hook_comment_tapped_android
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- Comment tapped (iOS)
    SELECT
        user_uid
        , hook_id
        , NULL AS hook_session_id  -- Future: when mobile logs hook_session_id directly
        , recommendation_item_id  -- Already extracted in staging table
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'comment_open_tap' AS event_type
        , 1 AS event_count
        , CASE WHEN os_name = 'iPadOS' THEN 'ios' ELSE LOWER(os_name) END AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__hook_comment_tapped_ios
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- Comment begin write (Android)
    SELECT
        user_uid
        , hook_id
        , NULL AS hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'comment_begin_write' AS event_type
        , 1 AS event_count
        , CASE WHEN os_name = 'iPadOS' THEN 'ios' ELSE LOWER(os_name) END AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__hook_comment_begin_write_android
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- TODO: Add comment_begin_write for iOS when available

    -- Add to playlist tapped (Android)
    SELECT
        user_uid
        , hook_id
        , NULL AS hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'playlist_add_tap' AS event_type
        , 1 AS event_count
        , CASE WHEN os_name = 'iPadOS' THEN 'ios' ELSE LOWER(os_name) END AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__add_hook_song_to_playlist_tapped_android
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- Add to playlist tapped (iOS)
    SELECT
        user_uid
        , hook_id
        , NULL AS hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'playlist_add_tap' AS event_type
        , 1 AS event_count
        , CASE WHEN os_name = 'iPadOS' THEN 'ios' ELSE LOWER(os_name) END AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__add_hook_song_to_playlist_tapped_ios
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- Add to playlist clicked (Web)
    SELECT
        user_uid
        , hook_id
        , hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'playlist_add_tap' AS event_type
        , 1 AS event_count
        , 'web' AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__add_hook_song_to_playlist_clicked_web
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- Share tapped (Android)
    SELECT
        user_uid
        , hook_id
        , NULL AS hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'share_tap' AS event_type
        , 1 AS event_count
        , CASE WHEN os_name = 'iPadOS' THEN 'ios' ELSE LOWER(os_name) END AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__hook_share_tapped_android
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- Share tapped (iOS)
    SELECT
        user_uid
        , hook_id
        , NULL AS hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'share_tap' AS event_type
        , 1 AS event_count
        , CASE WHEN os_name = 'iPadOS' THEN 'ios' ELSE LOWER(os_name) END AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__hook_share_tapped_ios
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- Share clicked (Web)
    SELECT
        user_uid
        , hook_id
        , hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'share_tap' AS event_type
        , 1 AS event_count
        , 'web' AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__hook_share_clicked_web
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- Song pill tapped (Android)
    SELECT
        user_uid
        , hook_id
        , NULL AS hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'song_pill_tap' AS event_type
        , 1 AS event_count
        , CASE WHEN os_name = 'iPadOS' THEN 'ios' ELSE LOWER(os_name) END AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__hook_song_pill_tapped_android
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- Song pill tapped (iOS)
    SELECT
        user_uid
        , hook_id
        , NULL AS hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'song_pill_tap' AS event_type
        , 1 AS event_count
        , CASE WHEN os_name = 'iPadOS' THEN 'ios' ELSE LOWER(os_name) END AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__hook_song_pill_tapped_ios
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- Go to full song tapped (Android)
    SELECT
        user_uid
        , hook_id
        , NULL AS hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'go_to_full_song_tap' AS event_type
        , 1 AS event_count
        , CASE WHEN os_name = 'iPadOS' THEN 'ios' ELSE LOWER(os_name) END AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__hook_go_to_full_song_tapped_android
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- Go to full song tapped (iOS)
    SELECT
        user_uid
        , hook_id
        , NULL AS hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'go_to_full_song_tap' AS event_type
        , 1 AS event_count
        , CASE WHEN os_name = 'iPadOS' THEN 'ios' ELSE LOWER(os_name) END AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__hook_go_to_full_song_tapped_ios
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- Remix tapped (Android)
    SELECT
        user_uid
        , hook_id
        , NULL AS hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'remix_tap' AS event_type
        , 1 AS event_count
        , CASE WHEN os_name = 'iPadOS' THEN 'ios' ELSE LOWER(os_name) END AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__remix_hook_song_tapped_android
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- Remix tapped (iOS)
    SELECT
        user_uid
        , hook_id
        , NULL AS hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'remix_tap' AS event_type
        , 1 AS event_count
        , CASE WHEN os_name = 'iPadOS' THEN 'ios' ELSE LOWER(os_name) END AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__remix_hook_song_tapped_ios
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- Remix clicked (Web)
    SELECT
        user_uid
        , hook_id
        , hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'remix_tap' AS event_type
        , 1 AS event_count
        , 'web' AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__remix_hook_song_clicked_web
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- Profile tapped: hook_creator_clicked on Web
    SELECT
        user_uid
        , hook_id
        , hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'profile_tap' AS event_type
        , 1 AS event_count
        , 'web' AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__hook_creator_clicked_web
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- TODO: Add profile_tap for Android when available
    -- TODO: Add profile_tap for iOS when available

    -- Song title tapped: hook_song_title_clicked on Web only
    SELECT
        user_uid
        , hook_id
        , hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'song_title_tap' AS event_type
        , 1 AS event_count
        , 'web' AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__hook_song_title_clicked_web
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- NOTE: song_title_tap doesn't exist on iOS/Android

    -- Create hook tapped: create_hook_clicked on Web
    SELECT
        user_uid
        , hook_id
        , hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'create_hook_tap' AS event_type
        , 1 AS event_count
        , 'web' AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__create_hook_clicked_web
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    UNION ALL

    -- Create hook tapped (iOS)
    SELECT
        user_uid
        , hook_id
        , NULL AS hook_session_id
        , recommendation_item_id
        , session_id
        , server_timestamp::TIMESTAMP_NTZ AS event_timestamp
        , 'create_hook_tap' AS event_type
        , 1 AS event_count
        , CASE WHEN os_name = 'iPadOS' THEN 'ios' ELSE LOWER(os_name) END AS platform
        , p_date
        , p_hour
    FROM suno_prod.{{ dbt_stg_schema }}.stg_segment_events__create_hook_tapped_ios
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour <  {{ buffered_partition_end_hour }},
            (
                (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }})
                OR
                (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}')
                OR
                (p_date = '{{ buffered_partition_end_date }}' AND p_hour <  {{ buffered_partition_end_hour }})
            )
        )

    -- TODO: Add create_hook_tap for Android when available
),

-- Mobile events with temporal join (Android + iOS)
mobile_events AS (
    SELECT
        fer.user_uid,  -- Already varchar from staging model
        fer.hook_id,
        -- Future-proof: use direct hook_session_id if available, else recommendation_item_id join
        CASE
            WHEN fer.hook_session_id IS NOT NULL
            THEN fer.hook_session_id
            ELSE hp.hook_session_id
        END AS hook_session_id,
        fer.recommendation_item_id,
        fer.session_id,
        fer.event_timestamp,
        fer.event_type,
        fer.event_count,
        fer.platform,
        fer.p_date,
        fer.p_hour
    FROM frontend_events_raw fer
    LEFT JOIN {{ fact_hook_play_table_name }} hp ON (
        -- Join on recommendation_item_id (much cleaner than temporal join)
        fer.recommendation_item_id = hp.recommendation_item_id
        AND fer.user_uid = hp.user_uid  -- Both are varchar already
        AND fer.hook_id = hp.hook_id
        -- Only do recommendation_item_id join when direct hook_session_id is not available
        AND fer.hook_session_id IS NULL
        -- Only for mobile platforms (web already has correct hook_session_id)
        AND fer.platform IN ('android', 'ios', 'ipados')
        -- Filter to partition window to avoid full table scan
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            hp.p_date = '{{ buffered_partition_start_date }}' AND hp.p_hour >= {{ buffered_partition_start_hour }} AND hp.p_hour <  {{ buffered_partition_end_hour }},
            (
                (hp.p_date = '{{ buffered_partition_start_date }}' AND hp.p_hour >= {{ buffered_partition_start_hour }})
                OR
                (hp.p_date > '{{ buffered_partition_start_date }}' AND hp.p_date < '{{ buffered_partition_end_date }}')
                OR
                (hp.p_date = '{{ buffered_partition_end_date }}' AND hp.p_hour <  {{ buffered_partition_end_hour }})
            )
        )
    )
    -- Only include mobile events that have successful recommendation_item_id join
    WHERE fer.platform IN ('android', 'ios', 'ipados')
        AND (fer.hook_session_id IS NOT NULL OR hp.hook_session_id IS NOT NULL)
    -- TODO: Remove recommendation_item_id join logic once hook_session_id is logged directly in mobile events
    -- Use ROW_NUMBER to handle multiple hook sessions per recommendation_item_id (pick first temporally)
    QUALIFY CASE
        WHEN fer.hook_session_id IS NOT NULL THEN 1
        ELSE ROW_NUMBER() OVER (
            PARTITION BY fer.recommendation_item_id, fer.user_uid, fer.hook_id
            ORDER BY hp.play_start_ts_utc
        )
    END = 1
),

-- Web events (already have correct hook_session_id from context:hookSessionId)
web_events AS (
    SELECT
        user_uid
        , hook_id
        , hook_session_id  -- Web events already correctly extract context:hookSessionId
        , recommendation_item_id
        , session_id
        , event_timestamp
        , event_type
        , event_count
        , platform
        , p_date
        , p_hour
    FROM frontend_events_raw
    WHERE platform = 'web'
        AND hook_session_id IS NOT NULL  -- Web events should always have hook_session_id
),

-- Combined frontend events with hook_session_id populated
all_frontend_events AS (
    SELECT * FROM mobile_events
    UNION ALL
    SELECT * FROM web_events
),

-- Join with fact_hook_play to get user_id for all events
final_events AS (
    SELECT
        hp.user_id
        , fe.user_uid
        , fe.hook_id
        , fe.hook_session_id
        , fe.recommendation_item_id
        , fe.session_id
        , fe.event_timestamp
        , fe.event_type
        , fe.event_count
        , fe.platform
        , fe.p_date
        , fe.p_hour
    FROM all_frontend_events fe
    LEFT JOIN {{ fact_hook_play_table_name }} hp ON (
        fe.hook_session_id = hp.hook_session_id
        AND fe.hook_id = hp.hook_id
        AND fe.user_uid = hp.user_uid
        -- Filter to partition window to avoid full table scan
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            hp.p_date = '{{ buffered_partition_start_date }}' AND hp.p_hour >= {{ buffered_partition_start_hour }} AND hp.p_hour <  {{ buffered_partition_end_hour }},
            (
                (hp.p_date = '{{ buffered_partition_start_date }}' AND hp.p_hour >= {{ buffered_partition_start_hour }})
                OR
                (hp.p_date > '{{ buffered_partition_start_date }}' AND hp.p_date < '{{ buffered_partition_end_date }}')
                OR
                (hp.p_date = '{{ buffered_partition_end_date }}' AND hp.p_hour <  {{ buffered_partition_end_hour }})
            )
        )
    )
    WHERE hp.user_id IS NOT NULL  -- Only include events that successfully joined
)

-- Insert processed events into staging table
SELECT
    user_id
    , user_uid
    , hook_id
    , hook_session_id
    , recommendation_item_id
    , session_id
    , event_timestamp
    , event_type
    , event_count
    , platform
    , p_date
    , p_hour
FROM final_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_uid IS NOT NULL;
