-- Web audio player actions tracking user interactions with the audio player
-- Clustered by partition date and hour for optimal query performance
CREATE TRANSIENT TABLE IF NOT EXISTS {web_audio_player_actions_table_name} CLUSTER BY (p_date, p_hour)(
	-- Auto-incrementing ID
	ID NUMBER(38,0) AUTOINCREMENT START 1 INCREMENT 1 NOORDER COMMENT 'Auto-incrementing unique identifier',
	
	-- Event details
	EVENT_NAME VARCHAR(255) COMMENT 'Name of the audio player event',
	SONG_SESSION_ID VARCHAR(255) COMMENT 'Unique identifier for this song play session',
	ACTION_NAME VARCHAR(255) COMMENT 'Specific action taken (play, pause, skip, etc.)',
	ACTION_INDEX NUMBER(38,0) COMMENT 'Sequential index of action in session',
	
	-- Song information
	SONG_ID VARCHAR(255) COMMENT 'Unique identifier for the song',
	SONG_LENGTH NUMBER(38,0) COMMENT 'Total length of the song in seconds',
	HAS_CLIP BOOLEAN COMMENT 'Whether song has a clip version',
	CLIP_LENGTH FLOAT COMMENT 'Length of clip if available',
	IS_USER_SONG_OWNER BOOLEAN COMMENT 'Whether user owns this song',
	
	-- Playback state
	IS_PLAYING BOOLEAN COMMENT 'Whether audio is currently playing',
	START_TIME FLOAT COMMENT 'Start time of playback in seconds',
	END_TIME FLOAT COMMENT 'End time of playback in seconds',
	PLAY_DURATION FLOAT COMMENT 'Duration of play in seconds',
	IS_AUDIO_ELEMENT_NULL BOOLEAN COMMENT 'Whether audio element is null',
	AUDIO_ELEMENT_CURRENT_TIME NUMBER(38,0) COMMENT 'Current time of audio element',
	
	-- Player settings
	VOLUME NUMBER(38,0) COMMENT 'Volume level (0-100)',
	IS_AUTO_PLAY_ON BOOLEAN COMMENT 'Whether autoplay is enabled',
	IS_REPEAT_ON BOOLEAN COMMENT 'Whether repeat is enabled',
	IS_SHUFFLE_ON BOOLEAN COMMENT 'Whether shuffle is enabled',
	IS_SONG_AUTOPLAY_QUEUE BOOLEAN COMMENT 'Whether song is in autoplay queue',
	
	-- User information
	USER_ID VARCHAR(255) COMMENT 'User identifier',
	SESSION_ID VARCHAR(16777216) COMMENT 'User session identifier',
	BACKEND_SESSION_ID VARCHAR(255) COMMENT 'Backend session identifier',
	ANONYMOUS_ID VARCHAR(255) COMMENT 'Anonymous user identifier',
	
	-- Context information
	PREVIOUS_SONG_SESSION_ID VARCHAR(255) COMMENT 'Previous song session ID',
	CLICK_SOURCE_URL VARCHAR(16777216) COMMENT 'URL source of the click',
	CONTEXT_ID VARCHAR(16777216) COMMENT 'Context identifier',
	CONTEXT_TYPE VARCHAR(16777216) COMMENT 'Type of context',
	STUDIO_PROJECT_SESSION_ID VARCHAR(255) COMMENT 'Studio project session identifier',
	
	-- Device and location info
	IS_MOBILE BOOLEAN COMMENT 'Whether user is on mobile device',
	PLATFORM VARCHAR(16777216) COMMENT 'Platform (web, mobile web, etc.)',
	USER_AGENT VARCHAR(16777216) COMMENT 'Browser user agent string',
	HASHED_USER_AGENT_ID VARCHAR(16777216) COMMENT 'Hashed user agent identifier',
	LOCALE VARCHAR(255) COMMENT 'User locale/language',
	TIMEZONE VARCHAR(255) COMMENT 'User timezone',
	CLIENT_IP VARCHAR(255) COMMENT 'User IP address',
	COUNTRY VARCHAR(255) COMMENT 'User country',
	CITY VARCHAR(255) COMMENT 'User city',
	REGION_NAME VARCHAR(255) COMMENT 'User region/state',
	
	-- Timestamps
	CLIENT_TIMESTAMP TIMESTAMP_TZ(9) COMMENT 'When event occurred on client side',
	SERVER_TIMESTAMP TIMESTAMP_TZ(9) COMMENT 'When event was received by server',
	SERVER_PROCESS_TIMESTAMP TIMESTAMP_TZ(9) COMMENT 'When event was processed by server',
	
	-- Partitioning columns
	P_DATE DATE COMMENT 'Partition date for table optimization',
	P_HOUR NUMBER(38,0) COMMENT 'Partition hour for table optimization',
	
	-- Additional metadata
	METADATA VARIANT COMMENT 'Additional event metadata in JSON format'
) COMMENT='Table contains web audio player action events for user interactions with the audio player';

