CREATE OR REPLACE TEMPORARY TABLE {{ unique_segments_table_name }} AS (
    -- This table computes the unique play segments for each user, clip, and studio project session.
    -- It deduplicates overlapping play segments and calculates the total unique play duration.
    WITH

    sorted_play_segments AS (
        SELECT
            user_id AS user_uid
            , song_id AS clip_id
            , studio_project_session_id
            , start_time
            , end_time
            , play_duration AS play_duration_seconds
            , MIN(start_time) OVER (
                PARTITION BY studio_project_session_id, song_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 studio_project_session_id, song_id
                ORDER BY start_time, end_time
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ) AS cumulative_max_end_time
        FROM {{ dedup_table_name }}
        WHERE 1=1
            AND user_id IS NOT NULL
            AND song_id IS NOT NULL
            AND studio_project_session_id IS NOT NULL
            AND start_time IS NOT NULL
            AND end_time IS NOT NULL
            AND play_duration_seconds BETWEEN 0 AND 1 * 60 * 60 -- Exclude play segments that are longer than 1 hour
            AND start_time < end_time --
            AND start_time >= 0
            AND end_time >= 0
        ORDER BY
            studio_project_session_id
            , song_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
            , studio_project_session_id
            , start_time
            , end_time
            , CASE
                WHEN start_time > LAG(end_time, 1, -1) OVER (
                    PARTITION BY user_uid, clip_id, studio_project_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
            , studio_project_session_id
            , start_time
            , end_time
            , SUM(is_new_group) OVER (
                PARTITION BY user_uid, clip_id, studio_project_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
            , studio_project_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
            , studio_project_session_id
            , group_id
    )

    SELECT
        user_uid
        , clip_id
        , studio_project_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
        , studio_project_session_id
);

