-- Step 3: Assign play durations based on action name
-- Creates actions with play durations table from ordered actions
-- Calculates play duration for each audio event based on next action patterns

CREATE OR REPLACE TEMPORARY TABLE {{ ios_legacy_play_durations_table_name }} AS (
    SELECT
        *
        -- Calculate play duration for this audio event
        , CASE
            ---------- NO PLAY DURATION ----------
            -- If this is the last audio event for the user (i.e. the next action is null), assign no play duration
            WHEN next_action_name IS NULL THEN 0
            -- If the next action happened more than 10 minutes after this action, assign no play duration.
            -- This is only an issue in iOS legacy, where we do not have song sessions and use consecutive events across time to determine song sessions
            WHEN DATEDIFF('minute', client_timestamp, next_client_timestamp) > 10 THEN 0
            ---------- SAME SONG ----------
            -- If the next action is a pause on the same song as the current action,
            -- then assign a play duration based on timestamp differences between this action and the next action
            WHEN next_clip_id = clip_id AND next_action_name = 'PauseSongTapped'
                THEN DATEDIFF('second', client_timestamp, next_client_timestamp)
            -- If the next action is a seek, restart, forward/backward, end of queue, or autoplay next song,
            -- then assign a play duration based on timestamp differences between this action and the next action
            WHEN (
                next_clip_id = clip_id
                AND next_is_playing
                AND next_action_name IN (
                    'SeekFinished'
                    , 'RestartSongTapped'
                    , 'AutoplayNextSong'
                    , 'PlayPreviousSong'
                    , 'PlayNextSong'
                    , 'EndOfQueue'
                )
            ) THEN DATEDIFF('second', client_timestamp, next_client_timestamp)
            ---------- DIFFERENT SONG ----------
            -- If the next action is to play a new song and the current action is playing a song,
            -- then assign a play duration based on timestamp differences between this action and the next action
            WHEN next_action_name = 'PlayNewSong' AND action_name IN ('PlayNewSong', 'PlaySongTapped')
                THEN DATEDIFF('second', client_timestamp, next_client_timestamp)
            -- Otherwise, no play duration
            ELSE 0
        END AS play_duration_seconds
        -- Song session index: incremented for events that start a new song session, partitioned by user
        , SUM(CASE WHEN action_name = 'PlayNewSong' THEN 1 ELSE 0 END)
            OVER (PARTITION BY user_uid ORDER BY client_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
            AS song_session_index
    FROM
        {{ ios_legacy_ordered_actions_table_name }}
);
