-- Step 1a: Deduplicate web user events (webpage click events)
-- Creates temporary table for deduplicated WEB_USER_EVENT data used for origin tracking

CREATE OR REPLACE TEMPORARY TABLE {{ web_user_event_dedup_table_name }} AS (
    SELECT * FROM (
        SELECT
            *
            , ROW_NUMBER() OVER (
                PARTITION BY
                    user_uid
                    , session_id
                    , anonymous_id
                    , action_name
                    , principal_object_value
                    , principal_object_type
                    , feature_session_type
                    , feature_session_id
                    , component_context
                    , ARRAY_SORT(TRY_PARSE_JSON(context)) -- Sort the array to deduplicate
                    , page_url
                    , platform
                    , is_mobile
                    , FLOOR(EXTRACT(epoch_millisecond FROM client_timestamp::TIMESTAMP_NTZ(9)) / 200) * 200 -- 200ms precision
                ORDER BY server_timestamp ASC
            ) AS rn
        FROM
            WEB_USER_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 action_name IN (
                -- Home feed
                'SongFeedItemTitleClicked', 'SongFeedItemArtistClicked',
                'SongCardTitleClicked', 'SongCardArtistClicked',
                -- Library and Create
                'SongRowTitleClicked', 'PlaylistCardTitleClicked'
            )
    ) WHERE rn = 1
);
