CREATE TRANSIENT TABLE IF NOT EXISTS {{ hook_session_table_name }} (
    -- Primary key and identifiers
    id VARCHAR(255) NOT NULL PRIMARY KEY UNIQUE COMMENT 'Hook session ID. NOTE: In some cases, this is not unique because of logging cases where the same hook session ID is used for multiple hook plays. Always join with hook session ID as well as hook ID'
    , hook_id VARCHAR(255) COMMENT 'Hook ID'
    , hook_play_id VARCHAR(255) NOT NULL REFERENCES {{ fact_hook_play_table_name }}(id) COMMENT 'Unique ID for this hook play - hash of user_uid, hook_id, and hook_session_id'
    , user_id INTEGER COMMENT 'User ID'
    , user_uid VARCHAR(255) COMMENT 'User UID'
    , clip_id VARCHAR(255) COMMENT 'Clip ID'

    -- Playback info
    , session_start_ts_utc TIMESTAMP_NTZ(9) COMMENT 'UTC timestamp of first audio event in song session, based on client timestamp on playback events'
    , session_end_ts_utc TIMESTAMP_NTZ(9) COMMENT 'UTC timestamp of last audio event in song session, based on client timestamp on playback events'
    , hook_session_duration_seconds FLOAT COMMENT 'Elapsed time of hook session in seconds, based on client timestamp on playback events (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%'
    , repeat_count INTEGER COMMENT 'Number of times the hook was auto-repeated at the end of the hook'
    , is_long_watch BOOLEAN COMMENT 'Whether the hook session was a long watch (i.e. hook_session_duration_seconds >= 5 seconds)'

    -- Like/unlike counts
    , like_count INTEGER COMMENT 'Number of likes for this hook for this hook session (source: backend event)'
    , undo_like_count INTEGER COMMENT 'Number of undo likes for this hook for this hook session (source: backend event)'
    , dislike_count INTEGER COMMENT 'Number of dislikes for this hook for this hook session (source: backend event)'
    , undo_dislike_count INTEGER COMMENT 'Number of undo dislikes for this hook for this hook session (source: backend event)'

    -- Comment counts
    , comment_open_tap_count INTEGER COMMENT 'Number of comment tapped for this hook for this hook session (source: frontend event)'
    , comment_begin_write_count INTEGER COMMENT 'Number of comment begin write for this hook for this hook session (source: frontend event)'
    , comment_count INTEGER COMMENT 'Number of comments for this hook for this hook session (source: backend event)'

    -- Share counts
    , share_tap_count INTEGER COMMENT 'Number of share button taps for this hook for this hook session (source: frontend event)'
    , share_count INTEGER COMMENT 'Number of shares for this hook for this hook session (source: backend event)'

    -- Profile and follow/unfollow counts
    , profile_tap_count INTEGER COMMENT 'Number of profile taps for this hook for this hook session (source: frontend event)'
    , follow_count INTEGER COMMENT 'Number of follows for this hook for this hook session (source: backend event)'
    , unfollow_count INTEGER COMMENT 'Number of unfollows for this hook for this hook session (source: backend event)'

    -- Playlist event counts
    , playlist_add_tap_count INTEGER COMMENT 'Number of playlist add button taps for this hook for this hook session (source: frontend event)'
    , playlist_add_count INTEGER COMMENT 'Number of add to playlists for this hook for this hook session (source: backend event)'

    -- Create hook counts
    , create_hook_tap_count INTEGER COMMENT 'Number of create hook taps for this hook for this hook session (source: frontend event)'
    , create_hook_submit_count INTEGER COMMENT 'Number of create hook submits for this hook for this hook session (source: backend event)'
    , create_hook_success_count INTEGER COMMENT 'Number of create hook successes for this hook for this hook session (source: backend event)'
    , remix_tap_count INTEGER COMMENT 'Number of remix taps for this hook for this hook session (source: frontend event)'

    -- Misc button taps & event fire counts
    , song_pill_tap_count INTEGER COMMENT 'Number of song pill taps for this hook for this hook session (source: frontend event)'
    , go_to_full_song_tap_count INTEGER COMMENT 'Number of Go to Full Song taps (via three-dot menu) for this hook for this hook session (source: frontend event)'
    , song_title_tap_count INTEGER COMMENT 'Number of song title taps for this hook for this hook session (source: frontend event)'

    -- Report/hide creator counts
    , report_count INTEGER COMMENT 'Number of reports for this hook for this hook session (source: backend event)'
    , hide_creator_count INTEGER COMMENT 'Number of hide creators for this hook for this hook session (source: backend event)'

    -- Context
    , 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 session'
    , session_id VARCHAR(255) COMMENT 'Unique session ID'
    , platform VARCHAR COMMENT 'Platform of the hook session (e.g. web, ios, android)'
    , app_version VARCHAR COMMENT 'App version for mobile apps. NULL for web'
    , is_mobile BOOLEAN COMMENT 'Whether the hook session was on a mobile device'
    , country VARCHAR COMMENT 'Country of the hook session'
    , city VARCHAR COMMENT 'City of the hook session'

    -- Partition fields
    , p_date DATE COMMENT 'Date of the hook session'
    , p_hour INTEGER COMMENT 'Hour of the hook session'
) CLUSTER BY (p_date, p_hour)
