-- Insert web user event data from S3 stage into WEB_USER_EVENT table
-- This query reads JSON files from the specified file path and inserts them into the table
INSERT INTO {web_user_event_table_name}(
    USER_UID,
    SESSION_ID,
    EVENT_NAME,
    FEATURE_SESSION_TYPE,
    FEATURE_SESSION_ID,
    COMPONENT_CONTEXT,
    ACTION_NAME,
    PRINCIPAL_OBJECT_TYPE,
    PRINCIPAL_OBJECT_VALUE,
    CONTEXT,
    PAGE_URL,
    USER_AGENT,
    LOCALE,
    TIMEZONE,
    CLIENT_TIMESTAMP,
    SERVER_TIMESTAMP,
    SERVER_PROCESS_TIMESTAMP,
    CLIENT_IP,
    COUNTRY,
    CITY,
    REGION_NAME,
    HASHED_USER_AGENT_ID,
    PLATFORM,
    IS_MOBILE,
    P_DATE,
    P_HOUR,
    TRANSACTION_ID,
    ANONYMOUS_ID,
    BACKEND_SESSION_ID,
    BROWSER_CONTEXT
)
WITH event_data AS (
    SELECT 
        *
    FROM {file_path} (file_format => 'BASIC_JSON_LOADER', pattern => ('.*'))
)
SELECT
    IFNULL(event_data.$1:userId, event_data.$1:properties.userId) AS USER_UID,
    event_data.$1:properties.sessionId AS SESSION_ID,
    event_data.$1:event AS EVENT_NAME,
    event_data.$1:properties.featureSessionType AS FEATURE_SESSION_TYPE,
    event_data.$1:properties.featureSessionId AS FEATURE_SESSION_ID,
    event_data.$1:properties.componentContext AS COMPONENT_CONTEXT,
    event_data.$1:properties.actionName AS ACTION_NAME,
    event_data.$1:properties.principalObjectType AS PRINCIPAL_OBJECT_TYPE,
    event_data.$1:properties.principalObjectValue AS PRINCIPAL_OBJECT_VALUE,
    PARSE_JSON(event_data.$1:properties.context) AS CONTEXT,
    event_data.$1:context.page.path AS PAGE_URL,
    event_data.$1:context.userAgent AS USER_AGENT,
    event_data.$1:context.locale AS LOCALE,
    event_data.$1:context.timezone AS TIMEZONE,
    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,
    IFNULL(event_data.$1:client_ip, '') AS CLIENT_IP,
    '' AS COUNTRY,
    '' AS CITY,
    IFNULL(event_data.$1:domain_name, '') AS REGION_NAME,
    GET_MD5_ID(event_data.$1:context.userAgent) AS HASHED_USER_AGENT_ID,
    IFNULL(event_data.$1:context.platform, '') AS PLATFORM,
    IS_USER_AGENT_MOBILE(event_data.$1:context.userAgent) AS IS_MOBILE,
    '{partition_date}'::DATE AS P_DATE,
    {partition_hour} AS P_HOUR,
    event_data.$1:properties.transactionUuid AS TRANSACTION_ID,
    event_data.$1:anonymous_id AS ANONYMOUS_ID,
    event_data.$1:backend_session_id AS BACKEND_SESSION_ID,
    event_data.$1:context AS BROWSER_CONTEXT
FROM event_data;
