-- Step 2: Calculate unique play segments
-- Handles overlapping play segments by detecting subsumption and grouping
-- This logic is ported from suno-etl procedure_web.sql lines 104-223

CREATE OR REPLACE TEMPORARY TABLE {{ web_unique_play_segments_table_name }} AS (
    WITH

    -- Sort play segments by start time and end time, and get cumulative min start time and max end time in the song session
    sorted_play_segments AS (
        SELECT
            user_id AS user_uid
            , song_id AS clip_id
            , song_session_id
            , start_time
            , end_time
            , play_duration AS play_duration_seconds
            , MIN(start_time) OVER (
                PARTITION BY song_session_id
                ORDER BY start_time, end_time
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ) AS cumulative_min_start_time
            , MAX(end_time) OVER (
                PARTITION BY song_session_id
                ORDER BY start_time, end_time
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ) AS cumulative_max_end_time
        FROM
            {{ web_audio_player_actions_dedup_table_name }}
        WHERE 1=1
            AND user_id IS NOT NULL
            AND (has_clip OR song_id IS NOT NULL)
            AND start_time IS NOT NULL
            AND end_time IS NOT NULL
            AND play_duration BETWEEN 0 AND 1 * 60 * 60 -- Exclude play segments that are longer than 1 hour
            AND start_time < end_time -- start time should be before end time
            AND start_time >= 0  -- Filter out negative start times
            AND end_time >= 0    -- Filter out negative end times
        ORDER BY
            song_session_id
            , start_time
            , end_time
    )

    -- Filter out play segments that are subsumed by previous play segments
    , non_subsumed_play_segments AS (
        SELECT
            user_uid
            , clip_id
            , song_session_id
            , start_time
            , end_time
            , CASE
                WHEN start_time > LAG(end_time, 1, -1) OVER (
                    PARTITION BY user_uid, clip_id, song_session_id
                    ORDER BY start_time, end_time
                ) THEN 1
                ELSE 0
            END AS is_new_group
        FROM
            sorted_play_segments
        WHERE 1=1
            AND (
                cumulative_min_start_time IS NULL  -- First row in session
                OR start_time < cumulative_min_start_time  -- Starts before any previous range
                OR end_time > cumulative_max_end_time      -- Ends after any previous range
            )
    )

    -- Assign group IDs
    , grouped_play_segments AS (
        SELECT
            user_uid
            , clip_id
            , song_session_id
            , start_time
            , end_time
            , SUM(is_new_group) OVER (
                PARTITION BY user_uid, clip_id, song_session_id
                ORDER BY start_time, end_time
            ) AS group_id
        FROM
            non_subsumed_play_segments
    )

    -- Summarize play groups
    , grouped_play_segments_summary AS (
        SELECT
            user_uid
            , clip_id
            , song_session_id
            , group_id
            , MIN(start_time) AS grouped_segment_start_time
            , MAX(end_time) AS grouped_segment_end_time
        FROM
            grouped_play_segments
        GROUP BY
            user_uid
            , clip_id
            , song_session_id
            , group_id
    )

    SELECT
        user_uid
        , clip_id
        , song_session_id
        , SUM(grouped_segment_end_time - grouped_segment_start_time) AS unique_play_duration_seconds
    FROM
        grouped_play_segments_summary
    GROUP BY
        user_uid
        , clip_id
        , song_session_id
);
