CREATE OR REPLACE PROCEDURE WEB_PAGE_EVENT_DAILY_INSERT_PROC(
    file_name STRING, 
    p_date DATE, 
    geo_table_name STRING
)
RETURNS STRING
LANGUAGE SQL
AS 
$$
DECLARE 
    row_count INT;
    start_time TIMESTAMP;
    query_sql string;
BEGIN
    start_time := CURRENT_TIMESTAMP();
    query_sql := 'INSERT INTO WEB_PAGE_EVENT(
        USER_UID,
        EVENT_TYPE,
        PAGE_URL,
        ELEMENT,
        ELEMENT_INDEX,
        SECONDARY_ELEMENT,
        SECONDARY_ELEMENT_INDEX,
        ENTITY_ID,
        ENTITY_TYPE,
        P_HOUR,
        SESSION_ID,
        METHOD,
        USER_AGENT,
        LOCALE,
        TIMEZONE,
        CLIENT_IP,
        SECONDARY_ENTITY_ID,
        SECONDARY_ENTITY_TYPE,
        SECONDARY_SESSION_ID,
        SECONDARY_SESSION_TYPE,
        PROPERTIES,
        IS_MOBILE,
        HASHED_USER_AGENT_ID,
        REGION_NAME,
        CLIENT_TIMESTAMP,
        SERVER_TIMESTAMP,
        SERVER_PROCESS_TIMESTAMP,
        COUNTRY,
        CITY,
        P_DATE
    )
    with event_data as (
            select 
                *
            from ' || :file_name || ' (file_format => ''BASIC_JSON_LOADER'', pattern => (''.*''))
    )
    select
        event_data.$1:properties.userId as USER_UID,
        event_data.$1:properties.eventType as event_type,
        event_data.$1:context.page.path as page_url,
        event_data.$1:properties.element as element,
        event_data.$1:properties.elementIndex as event_index,
        event_data.$1:properties.secondaryElement as secondary_element,
        event_data.$1:properties.secondaryElementIndex as secondary_element_index,
        event_data.$1:properties.entityId as entity_id,
        event_data.$1:properties.entityType as entity_type,
        HOUR(IFF(event_data.$1:server_timestamp is not null, (event_data.$1:server_timestamp)::TIMESTAMP, TO_TIMESTAMP(event_data.$1:request_time::string, ''DD/MON/YYYY:HH24:MI:SS TZHTZM''))) as p_hour,
        IFNULL(event_data.$1:properties.sessionId, '''') as SESSION_ID,
        IFNULL(event_data.$1:properties.method, '''') as METHOD,
        event_data.$1:context.userAgent as user_agent,
        event_data.$1:context.locale as locale,
        event_data.$1:context.timezone as timezone,
        IFNULL(event_data.$1:client_ip, '''') as client_ip,
        event_data.$1:properties.secondaryEntityId as secondary_entity_id,
        event_data.$1:properties.secondaryEntityType as secondary_entity_type,
        event_data.$1:properties.secondarySessionId as secondary_session_id,
        event_data.$1:properties.secondarySessionType as secondary_session_type,
        event_data.$1:properties.extraProperties as properties,
        IS_USER_AGENT_MOBILE(event_data.$1:context.userAgent) as is_mobile,
        GET_MD5_ID(event_data.$1:context.userAgent) as HASHED_USER_AGENT_ID,
        IFNULL(event_data.$1:domain_name, '''') as REGION_NAME,
        TO_TIMESTAMP(event_data.$1:timestamp) as client_timestamp,
        IFF(event_data.$1:request_time is null, (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,
        DATE(IFF(event_data.$1:server_timestamp is not null, (event_data.$1:server_timestamp)::TIMESTAMP, TO_TIMESTAMP(event_data.$1:request_time::string, ''DD/MON/YYYY:HH24:MI:SS TZHTZM''))) as p_date
    from event_data;
    ';
    EXECUTE IMMEDIATE :query_sql;

    -- Call stored procedure to create a temporary Geo Info Table
    CALL CREATE_GEO_INFO_TEMP_TABLE_PROC(:geo_table_name, 
        (SELECT ARRAY_AGG(DISTINCT client_ip) 
         FROM WEB_PAGE_EVENT 
         WHERE DATE(p_date) = :p_date)
    );

    MERGE INTO WEB_PAGE_EVENT AS target
    USING (
        SELECT "ip" AS ip, "country" AS country, "city" AS city FROM IDENTIFIER(:geo_table_name)
    ) as source
    ON (target.client_ip = source.ip AND target.p_date = :p_date)
    WHEN MATCHED THEN
        UPDATE SET 
            target.country = source.country,
            target.city = source.city;

    -- Drop the temporary geo table after merge
    DROP TABLE IDENTIFIER(:geo_table_name);

     -- Count number of rows for the given p_date
     SELECT COUNT(*) 
        INTO :row_count 
    FROM WEB_PAGE_EVENT 
    WHERE p_date = :p_date;

    -- -- If row count is 0, send an alert to Slack
    IF (:row_count = 0) THEN
        select post_to_slack('#data-alerts', 'WEB_PAGE_EVENT_daily_have 0 data');
    END IF;
    call TASK_MONITOR_INSERT_PROC('WEB_PAGE_EVENT_DAILY_INSERT', :start_time, :row_count, 'SUCCESS', 'DAILY');
    RETURN :p_date;
END;
$$;
