-- Insert web audio player actions data from S3 stage into WEB_AUDIO_PLAYER_ACTIONS table
-- This query reads JSON files from the specified file path and inserts them into the table
INSERT INTO {web_audio_player_actions_table_name}(
    EVENT_NAME,
    SONG_SESSION_ID,
    IS_MOBILE,
    HAS_CLIP,
    SONG_ID,
    START_TIME,
    END_TIME,
    IS_PLAYING,
    PLAY_DURATION,
    ACTION_NAME,
    IS_USER_SONG_OWNER,
    VOLUME,
    CLICK_SOURCE_URL,
    IS_AUTO_PLAY_ON,
    IS_REPEAT_ON,
    USER_ID,
    PREVIOUS_SONG_SESSION_ID,
    ACTION_INDEX,
    USER_AGENT,
    LOCALE,
    TIMEZONE,
    P_HOUR,
    CLIENT_IP,
    IS_AUDIO_ELEMENT_NULL,
    PLATFORM,
    CLIP_LENGTH,
    SESSION_ID,
    HASHED_USER_AGENT_ID,
    REGION_NAME,
    CLIENT_TIMESTAMP,
    SERVER_TIMESTAMP,
    SERVER_PROCESS_TIMESTAMP,
    COUNTRY,
    CITY,
    CONTEXT_ID,
    CONTEXT_TYPE,
    SONG_LENGTH,
    BACKEND_SESSION_ID,
    ANONYMOUS_ID,
    P_DATE,
    METADATA,
    IS_SHUFFLE_ON,
    IS_SONG_AUTOPLAY_QUEUE,
    STUDIO_PROJECT_SESSION_ID
)
WITH event_data AS (
    SELECT 
        *
    FROM {file_path} (file_format => 'BASIC_JSON_LOADER', pattern => ('.*'))
)
SELECT
    event_data.$1:event AS EVENT_NAME,
    event_data.$1:properties.songSessionId AS SONG_SESSION_ID,
    IS_USER_AGENT_MOBILE(event_data.$1:context.userAgent) AS IS_MOBILE,
    TRY_CAST(event_data.$1:properties.hasClip::STRING AS BOOLEAN) AS HAS_CLIP,
    event_data.$1:properties.songId AS SONG_ID,
    TRY_CAST(event_data.$1:properties.startTime::STRING AS NUMBER) AS START_TIME,
    TRY_CAST(event_data.$1:properties.endTime::STRING AS NUMBER) AS END_TIME,
    TRY_CAST(event_data.$1:properties.isPlaying::STRING AS BOOLEAN) AS IS_PLAYING,
    TRY_CAST(event_data.$1:properties.playDuration::STRING AS NUMBER) AS PLAY_DURATION,
    event_data.$1:properties.actionName AS ACTION_NAME,
    TRY_CAST(event_data.$1:properties.isUserSongOwner::STRING AS BOOLEAN) AS IS_USER_SONG_OWNER,
    TRY_CAST(event_data.$1:properties.volume::STRING AS NUMBER) AS VOLUME,
    event_data.$1:properties.clickSourceUrl AS CLICK_SOURCE_URL,
    TRY_CAST(event_data.$1:properties.isAutoPlayOn::STRING AS BOOLEAN) AS IS_AUTO_PLAY_ON,
    TRY_CAST(event_data.$1:properties.isRepeatOn::STRING AS BOOLEAN) AS IS_REPEAT_ON,
    IFNULL(event_data.$1:userId, event_data.$1:properties.userId) AS USER_ID,
    event_data.$1:properties.previousSongSessionId AS PREVIOUS_SONG_SESSION_ID,
    TRY_CAST(event_data.$1:properties.actionIndex::STRING AS NUMBER) AS ACTION_INDEX,
    event_data.$1:context.userAgent AS USER_AGENT,
    event_data.$1:context.locale AS LOCALE,
    event_data.$1:context.timezone AS TIMEZONE,
    {partition_hour} AS P_HOUR,
    IFNULL(event_data.$1:client_ip, '') AS CLIENT_IP,
    TRY_CAST(event_data.$1:properties.isAudioElementNull::STRING AS BOOLEAN) AS IS_AUDIO_ELEMENT_NULL,
    IFNULL(event_data.$1:context.platform, '') AS PLATFORM,
    event_data.$1:properties.clipLength AS CLIP_LENGTH,
    event_data.$1:properties.sessionId AS SESSION_ID,
    GET_MD5_ID(event_data.$1:context.userAgent) AS HASHED_USER_AGENT_ID,
    IFNULL(event_data.$1:domain_name, '') AS REGION_NAME,
    TRY_TO_TIMESTAMP(event_data.$1:timestamp::string) AS CLIENT_TIMESTAMP,
    IFF(event_data.$1:request_time IS NULL OR event_data.$1:request_time::string = '',
        (event_data.$1:server_timestamp)::TIMESTAMP,
        TO_TIMESTAMP(event_data.$1:request_time::string, 'DD/MON/YYYY:HH24:MI:SS TZHTZM')) AS SERVER_TIMESTAMP,
    TO_TIMESTAMP(event_data.$1:server_timestamp) AS SERVER_PROCESS_TIMESTAMP,
    '' AS COUNTRY,
    '' AS CITY,
    event_data.$1:properties.contextId AS CONTEXT_ID,
    event_data.$1:properties.contextType AS CONTEXT_TYPE,
    TRY_CAST(event_data.$1:properties.songLength::STRING AS NUMBER) AS SONG_LENGTH,
    event_data.$1:backend_session_id AS BACKEND_SESSION_ID,
    event_data.$1:anonymous_id AS ANONYMOUS_ID,
    '{partition_date}'::DATE AS P_DATE,
    event_data.$1:properties AS METADATA,
    TRY_CAST(event_data.$1:properties.isShuffleOn::STRING AS BOOLEAN) AS IS_SHUFFLE_ON,
    TRY_CAST(event_data.$1:properties.isSongAutoplayQueue::STRING AS BOOLEAN) AS IS_SONG_AUTOPLAY_QUEUE,
    event_data.$1:properties.studioProjectSessionId AS STUDIO_PROJECT_SESSION_ID
FROM event_data;

