CREATE TRANSIENT TABLE IF NOT EXISTS {{ fact_play_table_name }} (
    -- Primary key and identifiers
    id VARCHAR(255) NOT NULL PRIMARY KEY UNIQUE COMMENT 'Unique ID for this clip play - hash of user_uid, clip_id, and song_session_id. For legacy iOS, the hash is of user_uid, clip_id, and server timestamp of the first audio event in the song session.'
    , user_id INTEGER COMMENT 'User ID'
    , user_uid VARCHAR(255) COMMENT 'User UID'
    , clip_id VARCHAR(255) COMMENT 'Clip ID'
    , song_session_id VARCHAR(255) COMMENT 'Unique song session ID. NULL for legacy iOS.'

    -- Playback
    , play_start_ts_utc TIMESTAMP_NTZ(9) COMMENT 'UTC timestamp of first audio event in song session, based on client timestamp'
    , play_end_ts_utc TIMESTAMP_NTZ(9) COMMENT 'UTC timestamp of last audio event in song session, based on client timestamp'
    , song_session_duration_seconds FLOAT COMMENT 'Elapsed time of song session in seconds, based on client timestamp (i.e. play_end_ts_utc - play_start_ts_utc). Capped at 6 hours.'
    , play_duration_seconds FLOAT COMMENT 'Total number of seconds of the song played in song session (i.e. sum of play_duration in the song session). Capped at 3 hours.'
    , unique_play_duration_seconds FLOAT COMMENT 'Unique seconds of the song played in song session, capped at clip_duration_seconds (i.e. deduplicated if multiple play events in the song session played the same part of the clip). Capped at 3 hours.'
    , unique_play_percent FLOAT COMMENT 'Percent of clip played, considering only unique seconds(i.e. unique_play_duration_seconds / clip_duration_seconds). Capped at 100%.'
    , play_start_reason VARCHAR COMMENT 'Reason for play start. Can be click_song, click_forward, click_backward, click_backward_repeat, click_new_song, auto_play, auto_repeat, or song_end; falls back onto the first action_name.'
    , play_end_reason VARCHAR COMMENT 'Reason for play end. Can be song_end, click_pause, click_new_song, click_forward, or click_backward; falls back onto the last action_name.'
    , is_play_continuous BOOLEAN COMMENT 'Whether the playback was continuous (i.e. False if the user paused and resumed, seeked, or clicked backward to repeat the same song)'
    , is_manually_started BOOLEAN COMMENT 'Whether the play was manually started by the user by clicking play on a song, or forward/backward on audio player'
    , is_high_intent_listening VARIANT COMMENT 'Whether the play was during high intent listening as JSON (boolean by high intent listening definition version)'
    , audio_player_events VARIANT COMMENT 'All audio events as JSON (up to the first 30 events). Contains event name, client/server timestamp, start/end time, and play duration.'

    -- Clip info
    , clip_duration_seconds FLOAT 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'
    , is_play_during_generation BOOLEAN COMMENT 'Whether the play was while the song was streaming mid-generation. Inferred from clip_duration_seconds = 0'
    , clip_created_at TIMESTAMP_NTZ(9) COMMENT 'Timestamp when the clip was created'
    , model VARCHAR COMMENT 'Raw name of model used to generate the clip'
    , model_family VARCHAR COMMENT 'Model family used to generate the clip'

    -- Context info
    , previous_song_session_id VARCHAR COMMENT 'Previous song session ID'
    , play_page_type VARCHAR COMMENT 'Type of page where play was initiated (home, song page, playlist page, search, profile, etc)'
    , play_context_type VARCHAR COMMENT 'Type of context where play was initiated (song, playlist, etc)'
    , play_context_id VARCHAR COMMENT 'ID of the context where play was initiated (song_id, playlist_id, etc)'
    , play_context_metadata VARIANT COMMENT 'Additional context metadata as JSON (e.g., position in carousel, playlist track number, etc). For now this is empty.'
    , origin_context_id VARCHAR COMMENT 'Inferred origin context ID, inferred when play was initiated from a song/artist page. This is the context ID of the feed item that led the user to the song/artist page.'
    , origin_item_type VARCHAR COMMENT 'Inferred item that user clicked on to find the play context, when play was initiated from a song/artist page. Only populated for song sessions that start from a song/artist page.'
    , origin_song_id VARCHAR COMMENT 'Inferred song ID of the song that the user interacted with to find the song/artist page. Only populated for song sessions that start from a song/artist page.'
    , origin_item_metadata VARIANT COMMENT 'Inferred origin metadata, including index, comment count, like count, and play count. Only populated for song sessions that start from a song/artist page.'
    , browser_history VARIANT COMMENT 'Recent browser history as JSON (URL paths and timestamps)'
    , is_song_autoplay_queue BOOLEAN COMMENT 'Whether the song was auto-queued by autoplay settings'
    , audio_player_metadata VARIANT COMMENT 'Audio player metadata at the start of the song session (e.g. volume, autoplay, repeat, shuffle)'

    -- Device metadata
    , session_id VARCHAR COMMENT 'User session ID'
    , platform VARCHAR COMMENT 'Platform (web, ios, ipados, android)'
    , app_version VARCHAR 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 COMMENT 'Country of play'
    , city VARCHAR COMMENT 'City of play'

    -- Partition fields
    , p_date DATE COMMENT 'Partition date, based on server timestamp'
    , p_hour INTEGER COMMENT 'Partition hour, based on server timestamp'
) CLUSTER BY (p_date, p_hour)
COMMENT = 'Fact table for clip plays. One row per song session, where each song session is a consecutive sequence of audio events for one user and one clip played';
