-- Step 1: Deduplicate iOS legacy audio player events
-- Creates temporary table with deduplicated APP_AUDIO_ACTIONS data for iOS

CREATE OR REPLACE TEMPORARY TABLE {{ ios_legacy_audio_actions_dedup_table_name }} AS (
    SELECT * FROM (
        SELECT
            *
            , ROW_NUMBER() OVER (
                PARTITION BY
                    user_id
                    , clip_id
                    , action_name
                    , FLOOR(EXTRACT(epoch_millisecond FROM client_timestamp::TIMESTAMP_NTZ(9)) / 200) * 200 -- 200ms precision
                    , is_playing
                    , is_user_song_owner
                    , os_name
                ORDER BY server_timestamp ASC
            ) AS rn
        FROM
            APP_AUDIO_ACTIONS
        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 user_id IS NOT NULL
            AND LOWER(os_name) IN ('ios', 'ipados')
            AND action_name IN (
                'PlayNewSong'
                , 'PlaySongTapped'
                , 'PauseSongTapped'
                , 'RestartSongTapped'
                , 'PlayNextSong'
                , 'PlayPreviousSong'
                , 'AutoplayNextSong'
                , 'SeekFinished'
                , 'EndOfQueue'
            )
    ) WHERE rn = 1
);
