-- Insert app audio player actions from S3 stage into APP_AUDIO_ACTIONS table
-- This query reads JSON files from the specified file path and inserts them into the table
INSERT INTO {app_audio_actions_table_name}(
    EVENT_NAME,
    CLIP_ID,
    ACTION_NAME,
    USER_ID,
    ANONYMOUS_ID,
    PLAYLIST_ID,
    CLIENT_TIMESTAMP,
    IS_USER_SONG_OWNER,
    IS_PUBLIC,
    IS_LIKED,
    IS_DISLIKED,
    SHARE_TYPE,
    IS_PLAYING,
    IS_STREAMING,
    ELAPSED_TIME,
    TOTAL_TIME,
    FROM_COMMAND_CENTER,
    OMNIPLAYER_IS_EXPANDED,
    SERVER_TIMESTAMP,
    USER_AGENT,
    LOCALE,
    TIMEZONE,
    P_DATE,
    P_HOUR,
    CLIENT_IP,
    APP_NAMESPACE,
    APP_VERSION,
    OS_NAME,
    OS_VERSION
)
WITH total_data AS (
    SELECT * FROM {file_path} (file_format => 'BASIC_JSON_LOADER', pattern => ('.*'))
)
SELECT
    total_data.$1:event AS event_name,
    total_data.$1:properties.clipId AS clip_id,
    total_data.$1:properties.actionName AS action_name,
    total_data.$1:properties.userId AS user_id,
    total_data.$1:properties.anonymousId AS anonymous_id,
    IFNULL(total_data.$1:properties.playlistID, '') AS playlist_id,
    total_data.$1:timestamp AS client_timestamp,
    total_data.$1:properties.isUserSongOwner AS is_user_song_owner,
    total_data.$1:properties.isPublic AS is_public,
    total_data.$1:properties.isLiked AS is_liked,
    total_data.$1:properties.isDisliked AS is_disliked,
    IFNULL(total_data.$1:properties.shareType, '') AS share_type,
    total_data.$1:properties.isPlaying AS is_playing,
    total_data.$1:properties.isStreaming AS is_streaming,
    total_data.$1:properties.elapsedTime AS elapsed_time,
    total_data.$1:properties.totalTime AS total_time,
    total_data.$1:properties.fromCommandCenter AS from_command_center,
    total_data.$1:properties.omniplayerIsExpanded AS omniplayer_is_expanded,
    IFNULL(total_data.$1:server_timestamp, total_data.$1:timestamp) AS server_timestamp,
    total_data.$1:context.userAgent AS user_agent,
    total_data.$1:context.locale AS locale,
    total_data.$1:context.timezone AS timezone,
    '{partition_date}'::DATE AS p_date,
    {partition_hour} AS p_hour,
    IFNULL(total_data.$1:client_ip, '') AS client_ip,
    total_data.$1:context.app.namespace AS app_namespace,
    total_data.$1:context.app.version AS app_version,
    total_data.$1:context.os.name AS os_name,
    total_data.$1:context.os.version AS os_version
FROM total_data;
