-- Step 3: Create iOS song sessions from deduplicated omniplayer events
-- Aggregates events into play sessions
-- IMPORTANT: Uses HAVING clause to exclude single PlayNewSong events

CREATE OR REPLACE TEMPORARY TABLE {{ ios_play_sessions_table_name }} AS (
    SELECT
        -- Primary key and identifiers
        user_id AS user_uid
        , MODE(element_id) AS clip_id
        , TRY_PARSE_JSON(context):songSessionId::VARCHAR AS song_session_id

        -- Playback
        , MIN(client_timestamp::TIMESTAMP_NTZ(9)) AS play_start_ts_utc
        , MAX(client_timestamp::TIMESTAMP_NTZ(9)) AS play_end_ts_utc
        , DATEDIFF('second', MIN(client_timestamp::TIMESTAMP_NTZ(9)), MAX(client_timestamp::TIMESTAMP_NTZ(9))) AS song_session_duration_seconds
        , CASE
            -- For iOS app versions 1.23.0 to 1.29.0, the play duration was recorded as an updating value, so we use the last value to measure the play duration
            WHEN SPLIT(MIN_BY(app_version, client_timestamp::TIMESTAMP_NTZ(9)), '.')[1]::INTEGER = 1
                AND SPLIT(MIN_BY(app_version, client_timestamp::TIMESTAMP_NTZ(9)), '.')[2]::INTEGER BETWEEN 23 AND 29
                THEN GREATEST(0, MAX_BY(TRY_PARSE_JSON(context):playDuration::DOUBLE, client_timestamp::TIMESTAMP_NTZ(9)))
            -- For all other app versions, we use the sum of the play durations
            ELSE SUM(GREATEST(0, TRY_PARSE_JSON(context):playDuration::DOUBLE))
        END AS play_duration_seconds -- Exclude negative play durations
        , CASE
            WHEN MIN_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) = 'PlayNewSong' THEN 'click_song'
            WHEN MIN_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) = 'ForwardPlayNewSong' THEN 'click_forward'
            WHEN MIN_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) = 'BackwardPlayNewSong' THEN 'click_backward'
            WHEN MIN_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) = 'BackwardRepeatSong' THEN 'click_backward_repeat'
            WHEN MIN_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) IN ('AutoRepeatPlayNewSong', 'AutoPlayNewSong') THEN 'auto_play'
            WHEN MIN_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) = 'AutoRepeatPlaySong' THEN 'auto_repeat'
            ELSE MIN_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9))
        END AS play_start_reason
        , CASE
            WHEN MAX_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) = 'SongEnd' THEN 'song_end'
            WHEN MAX_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) = 'PauseSong' THEN 'click_pause'
            WHEN MAX_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) = 'PlayNewSongPauseSong' THEN 'click_new_song'
            WHEN MAX_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) = 'BackwardPauseSong' THEN 'click_backward'
            WHEN MAX_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) = 'ForwardPausePreSong' THEN 'click_forward'
            ELSE MAX_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9))
        END AS play_end_reason
        , (
            -- If the user seeked or backwards repeated at any point, then the playback is not continuous
            NOT BOOLOR_AGG(
                action_name IN (
                    'SeekProgressBarPauseSong', 'SeekProgressBarPlaySong',
                    'SeekProgressBarPauseSongInMediaSession', 'SeekProgressBarPlaySongInMediaSession',
                    'BackwardRepeatSong'
                )
            )
            -- If the user paused *and* resumed/repeated, then the playback is not continuous
            AND NOT (
                BOOLOR_AGG(action_name = 'PauseSong')
                AND BOOLOR_AGG(action_name IN ('PlaySong', 'BackwardRepeatSong'))
            )
        ) AS is_play_continuous
        , MIN_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) IN (
            'PlayNewSong', 'ForwardPlayNewSong', 'BackwardPlayNewSong', 'BackwardRepeatSong'
        ) AS is_manually_started
        , ARRAY_SLICE(ARRAY_AGG(
            OBJECT_CONSTRUCT(
                'action_name', action_name,
                'client_timestamp', client_timestamp::TIMESTAMP_NTZ(9),
                'server_timestamp', server_timestamp::TIMESTAMP_NTZ(9),
                'start_time', TRY_PARSE_JSON(context):startTime::DOUBLE,
                'end_time', TRY_PARSE_JSON(context):endTime::DOUBLE,
                'play_duration_seconds', TRY_PARSE_JSON(context):playDuration::DOUBLE
            )
        ) WITHIN GROUP (ORDER BY client_timestamp::TIMESTAMP_NTZ(9)), 0, 30) AS audio_player_events

        -- Clip info
        , ANY_VALUE(TRY_PARSE_JSON(context):isUserSongOwner::BOOLEAN) AS is_user_song_owner

        -- Context info
        , MIN_BY(TRY_PARSE_JSON(context):previousSongSessionId::VARCHAR, client_timestamp::TIMESTAMP_NTZ(9)) AS previous_song_session_id
        , ANY_VALUE(session_id) AS session_id
        , LOWER(MIN_BY(os_name, client_timestamp::TIMESTAMP_NTZ(9))) AS platform
        , MIN_BY(app_version, client_timestamp::TIMESTAMP_NTZ(9)) AS app_version
        , MIN_BY(country, client_timestamp::TIMESTAMP_NTZ(9)) AS country
        , MIN_BY(city, client_timestamp::TIMESTAMP_NTZ(9)) AS city
        , DATE(MIN_BY(server_timestamp::TIMESTAMP_NTZ(9), client_timestamp::TIMESTAMP_NTZ(9))) AS p_date
        , HOUR(MIN_BY(server_timestamp::TIMESTAMP_NTZ(9), client_timestamp::TIMESTAMP_NTZ(9))) AS p_hour
    FROM
        {{ ios_omniplayer_events_dedup_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 user_id IS NOT NULL
        AND song_session_id IS NOT NULL
    GROUP BY
        user_id
        , song_session_id
    HAVING
        NOT (COUNT(*) = 1 AND MIN_BY(action_name, client_timestamp::TIMESTAMP_NTZ(9)) = 'PlayNewSong') -- Exclude song sessions that are a single PlayNewSong event https://linear.app/sunomusic/issue/IOS-1411/check-for-double-counting-of-play-count-due-to-double-firing
);
