CREATE OR REPLACE TEMPORARY TABLE {{ dedup_table_name }} AS (
    SELECT * FROM (
        SELECT
            *
            , element_id AS hook_id
            , TRY_PARSE_JSON(context):hookSessionId::VARCHAR AS hook_session_id
            , TRY_PARSE_JSON(context):previousHookSessionId::VARCHAR AS previous_hook_session_id
            , TRY_PARSE_JSON(context):startTime::DOUBLE AS start_time
            , TRY_PARSE_JSON(context):endTime::DOUBLE AS end_time
            , TRY_PARSE_JSON(context):playDuration::DOUBLE AS play_duration_seconds
            , TRY_PARSE_JSON(context):totalPlayDuration::DOUBLE AS total_play_duration_seconds
            , TRY_PARSE_JSON(context):isUserHookOwner::BOOLEAN AS is_user_hook_owner
            , TRY_PARSE_JSON(context):volume::DOUBLE AS volume
            , TRY_PARSE_JSON(context):recommendationItemId::VARCHAR AS recommendation_item_id
            , TRY_PARSE_JSON(context):contextType::VARCHAR AS context_type
            , TRY_PARSE_JSON(context):contextId::VARCHAR AS context_id
            , TRY_PARSE_JSON(context):previousClipSessionId::VARCHAR AS previous_clip_session_id
            , ROW_NUMBER() OVER (
                PARTITION BY
                    user_id
                    , anonymous_id
                    , session_id
                    , element_id
                    , category
                    , action_name
                    , action_type
                    , ARRAY_SORT(TRY_PARSE_JSON(context)) -- Sort the array to deduplicate
                    , FLOOR(EXTRACT(epoch_millisecond FROM client_timestamp::TIMESTAMP_NTZ(9)) / 200) * 200 -- 200ms precision
                ORDER BY server_timestamp ASC
            ) AS rn
        FROM {{ ios_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 os_name = 'iOS'
            AND source = 'hooks_player'
            AND action_name IN (
                'PlayNewHook',
                'PlayHook',
                'PauseHook',
                'OpenDeeplinkPauseHook',
                'OpenDeeplinkPlayHook',
                'AutoRepeatPlayHook',
                'ScrollUpPauseHook',
                'ScrollUpPlayNewHook',
                'ScrollDownPauseHook',
                'ScrollDownPlayNewHook',
                'TapCreateHookPauseHook',
                'CloseCreateHookPlayHook',
                'TapCreateSongPauseHook',
                'CloseCreateSongPlayHook',
                'TapTabPauseHook',
                'TapTabPlayHook',
                'TapProfilePauseHook',
                'TapBackPauseHook',
                'TapBackPlayHook',
                'TapSongPillPauseHook',
                'CloseOmniPlayerPlayHook',
                'BackgroundAppPauseHook',
                'ForegroundAppPlayHook',
                'TapCoverPauseHook',
                'CloseCoverPlayHook',
                'TapExtendPauseHook',
                'CloseExtendPlayHook'
            )
            AND context IS NOT NULL
            AND user_id IS NOT NULL
    ) WHERE rn = 1
)
