-- Step 1: Deduplicate Android audio player events
-- Creates temporary table with deduplicated APP_EVENT data for Android audio_player

CREATE OR REPLACE TEMPORARY TABLE {{ android_audio_player_events_dedup_table_name }} AS (
    SELECT * FROM (
        SELECT
            *
            , ROW_NUMBER() OVER (
                PARTITION BY
                    user_id
                    , anonymous_id
                    , session_id
                    , TRY_PARSE_JSON(context):songSessionId::VARCHAR
                    , 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
            APP_EVENT
        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 category = 'audio_player'
            AND context IS NOT NULL
            AND user_id IS NOT NULL
            AND TRY_PARSE_JSON(context):songSessionId::VARCHAR IS NOT NULL
    ) WHERE rn = 1
);
