CREATE OR REPLACE TEMPORARY TABLE {{ dedup_table_name }} AS (
    SELECT * FROM (
        SELECT
            *
            , context:hookId::VARCHAR AS hook_id
            , context:hookSessionId::VARCHAR AS hook_session_id
            , context:previousHookSessionId::VARCHAR AS previous_hook_session_id
            , context:startTime::FLOAT AS start_time
            , context:endTime::FLOAT AS end_time
            , context:playDuration::FLOAT AS play_duration_seconds
            , context:isUserHookOwner::BOOLEAN AS is_user_hook_owner
            , context:recommendationItemId::VARCHAR AS recommendation_item_id
            , NULL AS previous_clip_session_id
            , NULL AS context_type
            , NULL AS context_id
            , ROW_NUMBER() OVER (PARTITION BY
                user_uid
                , session_id
                , anonymous_id
                , action_name
                , feature_session_type
                , feature_session_id
                , ARRAY_SORT(TRY_PARSE_JSON(context)) -- Sort the array to deduplicate
                , page_url
                , platform
                , is_mobile
                , FLOOR(EXTRACT(epoch_millisecond FROM client_timestamp::TIMESTAMP_NTZ(9)) / 200) * 200 -- 200ms precision
                ORDER BY server_timestamp ASC
            ) AS rn
        FROM {{ web_hook_event_table_name }}
        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 }})
                )
            )
            AND action_name IN (
                'PlayNewHookPauseHook',
                'PlayNewHook',
                'PlayHook',
                'PauseHook',
                'AutoRepeatPauseHook',
                'AutoRepeatPlayHook',
                'SeekHookPauseHook',
                'SeekHookPlayHook',
                'ScrollUpPauseHook',
                'ScrollUpPlayNewHook',
                'ScrollDownPauseHook',
                'ScrollDownPlayNewHook',
                'NavigatePauseHook',
                'BackgroundPauseHook',
                'ForegroundPlayHook',
                'HookPauseClose', -- Legacy event name
                'ClosePauseHook'
            )
    ) WHERE rn = 1
)
