-- Step 1b: Deduplicate web audio player actions (play events)
-- Creates temporary table for deduplicated WEB_AUDIO_PLAYER_ACTIONS data

CREATE OR REPLACE TEMPORARY TABLE {{ web_audio_player_actions_dedup_table_name }} AS (
    SELECT * FROM (
        SELECT
            *
            , ROW_NUMBER() OVER (
                PARTITION BY
                    song_session_id
                    , user_id
                    , start_time
                    , end_time
                    , clip_length
                    , song_id
                    , play_duration
                    , action_index
                    , country
                    , anonymous_id
                    , is_user_song_owner
                    , session_id
                    , FLOOR(EXTRACT(epoch_millisecond FROM client_timestamp::TIMESTAMP_NTZ(9)) / 200) * 200 -- 200ms precision
                ORDER BY server_timestamp ASC
            ) AS rn
        FROM
            WEB_AUDIO_PLAYER_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 }})
                )
            )
            -- EditV3 and Studio both fire events for context_type = 'studio_new' as well as context_type = 'studio_new_timeline'.
            -- For fact_play, we want EditV3 to keep studio_new events since edit mode is one track, and exclude studio_new_timeline events since it is a duplicate.
            -- But for Studio, we want to keep studio_new_timeline events since it emits one event over the studio project session, and exclude studio_new events since these will fire one per clip.
            -- Per-clip studio data is modeled in fact_studio_play.
            -- In other words: For EditV3, exclude studio_new_timeline events. For Studio, exclude studio_new events.
            AND NOT (context_type = 'studio_new_timeline' AND SPLIT(click_source_url, '/')[1] = 'edit')
            AND NOT (context_type = 'studio_new' AND SPLIT(click_source_url, '/')[1] = 'studio')
    ) WHERE rn = 1
);
