CREATE OR REPLACE TEMPORARY TABLE {{ stg_studio_play_table_name }} AS (
    SELECT
        -- Primary key and identifiers
        MD5_TO_UUID(CONCAT(sps.user_uid, sps.clip_id, sps.studio_project_session_id)) AS id
        , du.user_id
        , sps.user_uid
        , sps.clip_id
        , sps.studio_project_session_id

        -- Playback
        , sps.play_start_ts_utc
        , sps.play_end_ts_utc
        , LEAST(CEIL(sps.studio_project_session_duration_seconds * 10) / 10, 3 * 60 * 60) AS studio_project_session_duration_seconds -- Cap at 3 hours
        , LEAST(CEIL(sps.play_duration_seconds * 10) / 10, 3 * 60 * 60) AS play_duration_seconds -- Cap at 3 hours
        , COALESCE(CEIL(ups.unique_play_duration_seconds * 10) / 10, 0) AS unique_play_duration_seconds
        , LEAST(DIV0(CEIL(ups.unique_play_duration_seconds * 10) / 10, COALESCE(sps.clip_duration_seconds, dc.duration_sec)), 1) AS unique_play_percent
        , sps.play_start_reason
        , sps.play_end_reason

        -- Clip info
        , COALESCE(sps.clip_duration_seconds, dc.duration_sec) AS clip_duration_seconds
        , sps.is_user_song_owner

        -- Context info
        , sps.play_page_type
        , NULL AS play_track_index
        , sps.audio_player_events
        , sps.audio_player_metadata

        -- Device metadata
        , sps.session_id
        , sps.platform
        , NULL AS app_version
        , sps.is_mobile
        , sps.country
        , sps.city

        -- Partition columns
        , sps.p_date
        , sps.p_hour
    FROM
        {{ temp_studio_project_clip_sessions_table_name }} sps
    LEFT JOIN
        {{ unique_segments_table_name }} ups
        ON ups.user_uid = sps.user_uid
        AND ups.studio_project_session_id = sps.studio_project_session_id
        AND ups.clip_id = sps.clip_id
    LEFT JOIN
        dim_user du
        ON du.user_uid = sps.user_uid
    LEFT JOIN
        dim_clip dc
        ON dc.clip_id = sps.clip_id
    WHERE 1=1
        AND IFF(
            '{{ partition_start_date }}' = '{{ partition_end_date }}',
            sps.p_date = '{{ partition_start_date }}' AND sps.p_hour >= {{ partition_start_hour }} AND sps.p_hour < {{ partition_end_hour }},
            (
                (sps.p_date = '{{ partition_start_date }}' AND sps.p_hour >= {{ partition_start_hour }}) OR
                (sps.p_date > '{{ partition_start_date }}' AND sps.p_date < '{{ partition_end_date }}') OR
                (sps.p_date = '{{ partition_end_date }}' AND sps.p_hour < {{ partition_end_hour }})
            )
        )
        AND sps.user_uid IS NOT NULL
        AND sps.clip_id IS NOT NULL
        AND sps.studio_project_session_id IS NOT NULL

        -- Only include records with actual play time (cap at 3 hours)
        AND sps.play_duration_seconds > 0
        AND sps.play_duration_seconds <= 3 * 60 * 60
);

