-- Step 2: Calculate unique play segments for Android
-- Handles overlapping play segments using window functions and grouping logic

CREATE OR REPLACE TEMPORARY TABLE {{ android_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
                , element_id AS clip_id
                , TRY_PARSE_JSON(context):songSessionId::VARCHAR AS song_session_id
                , TRY_PARSE_JSON(context):startTime::DOUBLE AS start_time
                , TRY_PARSE_JSON(context):endTime::DOUBLE AS end_time
                , TRY_PARSE_JSON(context):playDuration::DOUBLE 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
                {{ android_audio_player_events_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
                AND element_id IS NOT NULL
                AND TRY_PARSE_JSON(context):songSessionId::VARCHAR IS NOT NULL
                AND TRY_PARSE_JSON(context):startTime::DOUBLE IS NOT NULL
                AND TRY_PARSE_JSON(context):endTime::DOUBLE IS NOT NULL
                AND TRY_PARSE_JSON(context):playDuration::DOUBLE BETWEEN 0 AND 1 * 60 * 60 -- Exclude play segments that are longer than 1 hour
                AND TRY_PARSE_JSON(context):startTime::DOUBLE < TRY_PARSE_JSON(context):endTime::DOUBLE -- start time should be before end time
                AND TRY_PARSE_JSON(context):startTime::DOUBLE >= 0  -- Filter out negative start times
                AND TRY_PARSE_JSON(context):endTime::DOUBLE >= 0    -- Filter out negative end times
            ORDER BY
                TRY_PARSE_JSON(context):songSessionId::VARCHAR
                , TRY_PARSE_JSON(context):startTime::DOUBLE
                , TRY_PARSE_JSON(context):endTime::DOUBLE
        )

        -- 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
);
