CREATE OR REPLACE PROCEDURE WEB_ARTIST_ACTIONS_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_ARTIST_ACTIONS(
        EVENT_NAME,
        ACTION_NAME,
        USER_ID,
        IS_MOBILE,
        CLICK_SOURCE_URL,
        FOLLOWEE_ID,
        USER_AGENT,
        LOCALE,
        TIMEZONE,
        P_HOUR,
        CLIENT_IP,
        SESSION_ID,
        HASHED_USER_AGENT_ID,
        REGION_NAME,
        CLIENT_TIMESTAMP,
        SERVER_TIMESTAMP,
        SERVER_PROCESS_TIMESTAMP,
        COUNTRY,
        CITY,
        P_DATE
    )	
    WITH total_data AS (
        SELECT 
            * 
        FROM ' || :file_name || ' (FILE_FORMAT => ''BASIC_JSON_LOADER'', PATTERN => (''.*''))
    )
    SELECT
        total_data.$1:event AS event_name,
        total_data.$1:properties.actionName AS action_name,
        total_data.$1:properties.userId AS user_id,
        IS_USER_AGENT_MOBILE(total_data.$1:context.userAgent) AS is_mobile,
        total_data.$1:properties.clickSourceUrl AS click_source_url,
        total_data.$1:properties.followeeId AS followee_id,
        total_data.$1:context.userAgent AS user_agent,
        total_data.$1:context.locale AS locale,
        total_data.$1:context.timezone AS timezone,
        HOUR(
            IFF(total_data.$1:server_timestamp IS not NULL, 
                (total_data.$1:server_timestamp)::TIMESTAMP, 
                TO_TIMESTAMP(total_data.$1:request_time::STRING, ''DD/MON/YYYY:HH24:MI:SS TZHTZM'')
            )
        ) AS p_hour,
        COALESCE(total_data.$1:client_ip, '''') AS client_ip,
        COALESCE(total_data.$1:properties.sessionId, '''') AS session_id,
        GET_MD5_ID(total_data.$1:context.userAgent) AS hashed_user_agent_id,
        COALESCE(total_data.$1:domain_name, '''') AS region_name,
        TO_TIMESTAMP(total_data.$1:timestamp) AS client_timestamp,
        IFF(total_data.$1:request_time IS NULL, 
            (total_data.$1:server_timestamp)::TIMESTAMP, 
            TO_TIMESTAMP(total_data.$1:request_time::STRING, ''DD/MON/YYYY:HH24:MI:SS TZHTZM'')
        ) AS server_timestamp,
        TO_TIMESTAMP(total_data.$1:server_timestamp) AS server_process_timestamp,
        '''' AS country,
        '''' AS city,
        DATE(
            IFF(total_data.$1:server_timestamp IS not NULL, 
                (total_data.$1:server_timestamp)::TIMESTAMP, 
                TO_TIMESTAMP(total_data.$1:request_time::STRING, ''DD/MON/YYYY:HH24:MI:SS TZHTZM'')
            )
        ) AS p_date
    FROM total_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_ARTIST_ACTIONS 
         WHERE DATE(p_date) = :p_date)
    );

    MERGE INTO WEB_ARTIST_ACTIONS 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_ARTIST_ACTIONS 
    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_ARTIST_ACTIONS_daily_have 0 data');
    END IF;

    call TASK_MONITOR_INSERT_PROC('WEB_ARTIST_ACTIONS_DAILY_INSERT', :start_time, :row_count, 'SUCCESS', 'DAILY');
    
    RETURN 'Data Inserted';
END;
$$;
