CREATE TRANSIENT TABLE IF NOT EXISTS {{ fact_studio_play_table_name }} (
    -- Primary key and identifiers
    id VARCHAR(255) PRIMARY KEY UNIQUE COMMENT 'Unique ID for this clip play during this project session. This is the hash of user_uid, clip_id, and studio_project_session_id.'
    , user_id INTEGER COMMENT 'User ID'
    , user_uid VARCHAR(255) COMMENT 'User UID'
    , clip_id VARCHAR(255) COMMENT 'Clip ID'
    , studio_project_session_id VARCHAR(255) COMMENT 'Unique studio project session ID'

    -- Playback
    , play_start_ts_utc TIMESTAMP_NTZ(9) COMMENT 'UTC timestamp of first audio event in studio project session for this clip, based on client timestamp'
    , play_end_ts_utc TIMESTAMP_NTZ(9) COMMENT 'UTC timestamp of last audio event in studio project session for this clip, based on client timestamp'
    , studio_project_session_duration_seconds NUMBER COMMENT 'Time elapsed in the studio project session between the first/last play events for this clip only, capped at 3 hours.'
    , play_duration_seconds NUMBER COMMENT 'Total number of seconds this clip was playing in the studio project session'
    , unique_play_duration_seconds NUMBER COMMENT 'Unique seconds of this clip played in the studio project session'
    , unique_play_percent FLOAT COMMENT 'Percent of clip played, considering only unique seconds (i.e. unique_play_duration_seconds / studio_project_clip_duration_seconds)'
    , play_start_reason VARCHAR(255) COMMENT 'Reason for play start'
    , play_end_reason VARCHAR(255) COMMENT 'Reason for play end'

    -- Clip info
    , clip_duration_seconds NUMBER COMMENT 'Duration of clip in seconds. Fallback to dim_clip if frontend events had zero duration.'
    , is_user_song_owner BOOLEAN COMMENT 'Whether the user is the owner of the song'

    -- Context info
    , play_page_type VARCHAR(255) COMMENT 'Type of page where play was initiated'
    , play_track_index INTEGER COMMENT 'Track index of this clip in the studio project. Not currently populated'
    , audio_player_events VARIANT COMMENT 'All audio events as JSON (up to the first 200 events). Contains event name, client/server timestamp, start/end time, and play duration.'
    , audio_player_metadata VARIANT COMMENT 'Additional metadata as JSON (e.g., position in studio project, etc). For now this is empty.'

    -- Device metadata
    , session_id VARCHAR(255) COMMENT 'Unique session ID'
    , platform VARCHAR(255) COMMENT 'Platform'
    , app_version VARCHAR(255) COMMENT 'App version for mobile apps. NULL for web'
    , is_mobile BOOLEAN COMMENT 'For web, whether the play session was on a mobile device. NULL for iOS and Android apps'
    , country VARCHAR(255) COMMENT 'Country of play'
    , city VARCHAR(255) COMMENT 'City of play'

    -- Partition columns
    , p_date DATE COMMENT 'Partition date of session start time, based on server timestamp'
    , p_hour INTEGER COMMENT 'Partition hour of session start time, based on server timestamp'
) CLUSTER BY (p_date, p_hour)
COMMENT = 'Fact table for studio plays. One row per studio play session x clip, as users can play multiple clips simultaneously in a single studio play session.';

