CREATE OR REPLACE PROCEDURE WEB_PLAYLIST_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_PLAYLIST_ACTIONS(
        EVENT_NAME,
        ACTION_NAME,
        IS_MOBILE,
        USER_ID,
        CLICK_SOURCE_URL,
        PLAYLIST_ID,
        IS_USER_PLAYLIST_OWNER,
        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,
        IS_USER_AGENT_MOBILE(total_data.$1:context.userAgent) as is_mobile,
        total_data.$1:properties.userId as user_id,
        total_data.$1:properties.clickSourceUrl as click_source_url,
        total_data.$1:properties.playlistID as playlist_id,
        total_data.$1:properties.isUserPlaylistOwner as is_user_playlist_owner,
        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,
        IFNULL(total_data.$1:client_ip, '''') as client_ip,
        IFNULL(total_data.$1:properties.sessionId, '''') as SESSION_ID,
        GET_MD5_ID(total_data.$1:context.userAgent) as HASHED_USER_AGENT_ID,
        IFNULL(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_PLAYLIST_ACTIONS 
         WHERE DATE(p_date) = :p_date)
    );

    MERGE INTO WEB_PLAYLIST_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_PLAYLIST_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_PLAYLIST_ACTIONS_daily_have 0 data');
    END IF;
    call TASK_MONITOR_INSERT_PROC('WEB_PLAYLIST_ACTIONS_DAILY_INSERT', :start_time, :row_count, 'SUCCESS', 'DAILY');
    RETURN 'Data Inserted';
END;
$$;
