-- Step 3: Aggregate play data by user and song session for iOS legacy
-- Creates song sessions from actions with play durations
-- IMPORTANT: Uses HAVING to filter sessions that start with PlayNewSong and have positive duration

CREATE OR REPLACE TEMPORARY TABLE {{ ios_legacy_play_sessions_table_name }} AS (
    SELECT
        apd.user_uid
        , apd.clip_id
        , du.user_id
        , apd.song_session_index
        , SUM(GREATEST(0, apd.play_duration_seconds)) AS play_duration_seconds -- Exclude negative play durations
        , BOOLOR_AGG(apd.is_user_song_owner) AS is_user_song_owner
        , BOOLOR_AGG(apd.is_streaming) AS is_play_during_generation
        , ANY_VALUE(apd.platform) AS platform
        , ANY_VALUE(apd.app_version) AS app_version
        , ANY_VALUE(apd.country) AS country
        , ANY_VALUE(apd.city) AS city
        , MIN(apd.client_timestamp) AS play_start_ts_utc
        , MAX(apd.client_timestamp) AS play_end_ts_utc
        , DATEDIFF('second', MIN(apd.client_timestamp), MAX(apd.client_timestamp)) AS song_session_duration_seconds
        , CASE
            WHEN MIN_BY(apd.action_name, apd.client_timestamp) = 'PlayNewSong' THEN 'click_song'
            ELSE MIN_BY(apd.action_name, apd.client_timestamp)
        END AS play_start_reason
        , CASE
            WHEN MAX_BY(apd.action_name, apd.client_timestamp) = 'PauseSongTapped' THEN 'click_pause'
            WHEN MAX_BY(apd.action_name, apd.client_timestamp) = 'AutoplayNextSong' THEN 'auto_play'
            WHEN MAX_BY(apd.action_name, apd.client_timestamp) = 'EndOfQueue' THEN 'end_of_queue'
            WHEN MAX_BY(apd.action_name, apd.client_timestamp) = 'PlayPreviousSong' THEN 'click_backward'
            WHEN MAX_BY(apd.action_name, apd.client_timestamp) = 'PlayNextSong' THEN 'click_forward'
            ELSE MAX_BY(apd.action_name, apd.client_timestamp)
        END AS play_end_reason
        , (
            -- If the user seeked or restarted at any point, then the playback is not continuous
            NOT BOOLOR_AGG(
                apd.action_name IN (
                    'SeekFinished'
                    , 'RestartSongTapped'
                )
            )
            -- If the user paused *and* resumed, then the playback is not continuous
            AND NOT (
                BOOLOR_AGG(apd.action_name = 'PauseSongTapped')
                AND BOOLOR_AGG(apd.action_name IN ('PlaySongTapped', 'RestartSongTapped'))
            )
        ) AS is_play_continuous
        , ARRAY_SLICE(ARRAY_AGG(
            OBJECT_CONSTRUCT(
                'action_name', apd.action_name,
                'client_timestamp', apd.client_timestamp,
                'server_timestamp', apd.server_timestamp,
                'is_playing', apd.is_playing,
                'play_duration_seconds', apd.play_duration_seconds
            )
        ) WITHIN GROUP (ORDER BY apd.client_timestamp), 0, 30) AS audio_player_events
        , DATE(MIN_BY(apd.server_timestamp, apd.client_timestamp)) AS p_date
        , HOUR(MIN_BY(apd.server_timestamp, apd.client_timestamp)) AS p_hour
    FROM
        {{ ios_legacy_play_durations_table_name }} apd
    LEFT JOIN
        DIM_USER du
        ON apd.user_uid = du.user_uid
    GROUP BY
        apd.user_uid
        , apd.clip_id
        , du.user_id
        , apd.song_session_index
    HAVING
        MIN_BY(apd.action_name, apd.client_timestamp) = 'PlayNewSong'
        AND SUM(apd.play_duration_seconds) > 0
);
