-- Step 2: Create ordered actions with play duration calculation for iOS legacy
-- Uses LEAD window function to track next actions and calculate play durations
-- Note: iOS legacy does not support unique play duration segments

-- Create ordered actions with next action tracking
CREATE OR REPLACE TEMPORARY TABLE {{ ios_legacy_ordered_actions_table_name }} AS (
    SELECT
        user_id AS user_uid
        , clip_id
        , client_timestamp::TIMESTAMP_NTZ(9) AS client_timestamp
        , server_timestamp::TIMESTAMP_NTZ(9) AS server_timestamp
        , action_name
        , is_playing
        , is_user_song_owner
        , is_streaming
        , LOWER(os_name) AS platform
        , app_version
        , NULL AS country
        , NULL AS city
        , LEAD(action_name) OVER (
            PARTITION BY user_id
            ORDER BY client_timestamp
        ) AS next_action_name
        , LEAD(clip_id) OVER (
            PARTITION BY user_id
            ORDER BY client_timestamp
        ) AS next_clip_id
        , LEAD(client_timestamp) OVER (
            PARTITION BY user_id
            ORDER BY client_timestamp
        ) AS next_client_timestamp
        , LEAD(is_playing) OVER (
            PARTITION BY user_id
            ORDER BY client_timestamp
        ) AS next_is_playing
    FROM
        {{ ios_legacy_audio_actions_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
);
