CREATE OR REPLACE TEMPORARY TABLE {{ dedup_table_name }} AS (
    SELECT * FROM (
        SELECT
            *
            , user_id AS user_uid
            , 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
            -- Before 1.26.0, the play duration was recorded in milliseconds
            , CASE
                WHEN SPLIT(app_version, '.')[0]::INTEGER > 1 THEN TRY_PARSE_JSON(context):playDuration::DOUBLE
                WHEN SPLIT(app_version, '.')[0]::INTEGER = 1 AND SPLIT(app_version, '.')[1]::INTEGER >= 26 THEN TRY_PARSE_JSON(context):playDuration::DOUBLE
                WHEN SPLIT(app_version, '.')[0]::INTEGER = 1 AND SPLIT(app_version, '.')[1]::INTEGER < 26 AND TRY_PARSE_JSON(context):playDuration::DOUBLE < 100 THEN TRY_PARSE_JSON(context):playDuration::DOUBLE
                ELSE TRY_PARSE_JSON(context):playDuration::DOUBLE / 1000
              END AS play_duration_seconds
            , TRY_PARSE_JSON(context):totalPlayDuration::DOUBLE AS total_play_duration_seconds -- Currently not populated on Android
            , 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
            , TRY_PARSE_JSON(context):isMuted::BOOLEAN AS is_muted
            , TRY_PARSE_JSON(context):clickSourceUrl::VARCHAR AS click_source_url
            , 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 {{ android_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 = 'Android'
            AND action_name IN (
                -- New PascalCase events
                'PlayNewHook',
                'PauseHook',
                'PlayHook',
                'AutoRepeatPauseHook',
                'AutoRepeatPlayHook',
                'ScrollUpPauseHook',
                'ScrollDownPauseHook',
                'ScrollUpPlayNewHook',
                'ScrollDownPlayNewHook',
                'TapSongPillPauseHook',
                'CloseClipPlayerPlayHook',
                -- Legacy camelCase events for backward compatibility
                'playNewHook',
                'pauseHook',
                'playHook',
                'autoRepeatPauseHook',
                'autoRepeatPlayHook',
                'scrollUpPauseHook',
                'scrollDownPauseHook',
                'scrollUpPlayNewHook',
                'scrollDownPlayNewHook',
                'openOmniplayerPauseHook',
                'closeOmniplayerStartHook'
            )
            AND context IS NOT NULL
            AND user_id IS NOT NULL
    ) WHERE rn = 1
)
