CREATE TRANSIENT TABLE IF NOT EXISTS {{ fact_hook_play_table_name }} (
    -- Primary key and identifiers
    id VARCHAR(255) NOT NULL PRIMARY KEY UNIQUE COMMENT 'Unique ID for this hook play - hash of user_uid, hook_id, and hook_session_id. For legacy iOS, the hash is of user_uid, hook_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'
    , hook_id VARCHAR(255) COMMENT 'Hook ID'
    , hook_session_id VARCHAR(255) COMMENT 'Unique hook session ID'
    , clip_id VARCHAR(255) COMMENT 'Clip ID'

    -- 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'
    , hook_session_duration_seconds FLOAT COMMENT 'Elapsed time of hook 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 hook played in hook session (i.e. sum of play_duration in the hook session). Capped at 3 hours.'
    , unique_play_duration_seconds FLOAT COMMENT 'Unique seconds of the hook played in hook session, capped at hook_duration_seconds (i.e. deduplicated if multiple play events in the hook session played the same part of the hook). Capped at 3 hours.'
    , unique_play_percent FLOAT COMMENT 'Percent of hook played, considering only unique seconds(i.e. unique_play_duration_seconds / hook_duration_seconds). Capped at 100%'
    , hook_start_reason VARCHAR COMMENT 'Reason for hook start'
    , hook_end_reason VARCHAR COMMENT 'Reason for hook end'
    , is_play_continuous BOOLEAN COMMENT 'Whether the playback was continuous (i.e. False if the user paused and resumed or seeked'
    , repeat_count INTEGER COMMENT 'Number of times the hook was auto-repeated at the end of the hook'
    , hook_player_events VARIANT COMMENT 'All audio events as JSON (up to the first 50 events). Contains event name, client/server timestamp, start/end time, and play duration.'
    , is_high_intent_listening VARIANT COMMENT 'Whether the play was during high intent listening as JSON (boolean by high intent listening definition version)'

    -- Hook info
    , hook_duration_seconds FLOAT COMMENT 'Duration of hook in seconds. Fallback to dim_clip if frontend events had zero 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_song_opened BOOLEAN COMMENT 'Whether the song was opened from a Hooks page'

    -- Context info
    , previous_hook_session_id VARCHAR COMMENT 'Previous hook session ID'
    , previous_clip_session_id VARCHAR COMMENT 'Previous clip session ID'
    , context_type VARCHAR COMMENT 'Context type of the hook session (e.g. profile, hook, feed)'
    , context_id VARCHAR COMMENT 'Context ID of the hook session (e.g. user handle for profile context)'
    , recommendation_item_id VARCHAR COMMENT 'Recommendation item ID for this hook play session'
    , hook_player_metadata VARIANT COMMENT 'Hook player metadata at the start of the hook session (e.g. volume, isMuted, autoplay, repeat, shuffle)'

    -- Device metadata
    , session_id VARCHAR(255) COMMENT 'Unique 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 'Date of the play'
    , p_hour INTEGER COMMENT 'Hour of the play'
) CLUSTER BY (p_date, p_hour)
COMMENT = 'Fact table for hook plays. One row per hook session, where each hook session is a consecutive sequence of hook play events for one user and one hook';
