CREATE OR REPLACE PROCEDURE WEB_AUDIO_CREATION_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_AUDIO_CREATION(
        EVENT_NAME,
        IS_MOBILE,
        CONTINUE_CLIP_ID,
        REUSE_PROMPT_CLIP_ID,
        NEW_CLIP_IDS,
        ACTION_NAME,
        CLICK_SOURCE_URL,
        USER_ID,
        IS_CUSTOM_MODE,
        HAS_LYRICS,
        HAS_STYLE,
        HAS_TITLE,
        HAS_DESCRIPTION,
        IS_INSTRUMENTAL_MODE,
        IS_UPLOADED,
        UPLOAD_TYPE,
        MODEL_VERSION,
        RECOMMEND_STYLE,
        IS_GENERATE_LYIRCS_USED,
        IS_RECOMMEND_STYLE_USED,
        IS_CONTINUE_CLIP_PUBLIC,
        IS_REUSE_PROMPT_CLIP_PUBLIC,
        USER_AGENT,
        LOCALE,
        TIMEZONE,
        P_HOUR,
        CLIENT_IP,
        SESSION_ID,
        PROPERTIES,
        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,
        IS_USER_AGENT_MOBILE(total_data.$1:context.userAgent) as is_mobile,
        total_data.$1:properties.continueClipId as continue_clip_id,
        total_data.$1:properties.reusePromptClipId as reuse_prompt_clip_id,
        total_data.$1:properties.newClipIds as new_clip_ids,
        total_data.$1:properties.actionName as action_name,
        total_data.$1:properties.clickSourceUrl as click_source_url,
        total_data.$1:properties.userId as user_id,
        total_data.$1:properties.isCustomMode as is_custom_mode,
        total_data.$1:properties.hasLyrics as has_lyrics,
        total_data.$1:properties.hasStyle as has_style,
        total_data.$1:properties.hasTitle as has_title,
        total_data.$1:properties.hasDescription as has_description,
        case
            WHEN total_data.$1:properties.isInstrumentalMode IN (''true'', ''false'') then total_data.$1:properties.isInstrumentalMode
            ELSE ''false''
        end as is_instrumental_mode,
        total_data.$1:properties.isUploaded as is_uploaded,
        total_data.$1:properties.uploadType as upload_type,
        total_data.$1:properties.modelVersion as model_version,
        total_data.$1:properties.recommendStyle as recommend_style,
        total_data.$1:properties.isGenerateLyircsUsed as is_generate_lyircs_used,
        total_data.$1:properties.isRecommendStyleUsed as is_recommend_style_used,
        total_data.$1:properties.isContinueClipPublic as is_continue_clip_public,
        total_data.$1:properties.isReusePromptClipPublic as is_reuse_prompt_clip_public,
        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,
        $1:properties as PROPERTIES,
        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_AUDIO_CREATION 
         WHERE DATE(p_date) = :p_date)
    );

    MERGE INTO WEB_AUDIO_CREATION 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_AUDIO_CREATION 
    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_AUDIO_CREATION_daily_have 0 data');
    END IF;

    call TASK_MONITOR_INSERT_PROC('WEB_AUDIO_CREATION_DAILY_INSERT', :start_time, :row_count, 'SUCCESS', 'DAILY');

    RETURN 'Data Inserted';
END;
$$;
