create or replace task APP_GENERAL_DAILY_INSERT
	warehouse=SUNO_PROD_MOBILE_DAILY_X_SMALL
	schedule='USING CRON 0 3 * * * UTC'
	as DECLARE
        start_time TIMESTAMP;
        p_date date;
        p_hour string;
        stage_path string;
        row_count number;
        sql string;
        BEGIN
            start_time := CURRENT_TIMESTAMP();
            p_date := DATE(SYSDATE() - INTERVAL '1 DAY');
            p_hour := HOUR(SYSDATE() - INTERVAL '1 DAY');
            stage_path := CONCAT('@SUNO_S3_EVENTS/App-General-Event/0/', YEAR(SYSDATE() - INTERVAL '1 DAY'), '/', MONTH(SYSDATE() - INTERVAL '1 DAY'), '/', DAY(SYSDATE() - INTERVAL '1 DAY'));
            sql := 'INSERT INTO APP_GENERAL(
                EVENT_NAME,
                ACTION_NAME,
                USER_ID,
                ANONYMOUS_ID,
                DESCRIPTION,
	            CLIENT_TIMESTAMP,
	            SERVER_TIMESTAMP,
	            USER_AGENT,
	            LOCALE,
	            TIMEZONE,
	            P_HOUR,
	            CLIENT_IP,
                APP_NAMESPACE,
                APP_VERSION,
                OS_NAME,
                OS_VERSION,
                P_DATE
            )	
            with total_data as (
                select * from ' || :stage_path || ' (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,
                total_data.$1:properties.anonymousId as anonymous_id,
                total_data.$1:properties.description as description,
                total_data.$1:timestamp as client_timestamp,
                IFNULL(total_data.$1:server_timestamp, total_data.$1:timestamp) as server_timestamp,
                total_data.$1:context.userAgent as user_agent,
                total_data.$1:context.locale as locale,
                total_data.$1:context.timezone as timezone,
                HOUR(IFNULL(total_data.$1:server_timestamp, total_data.$1:timestamp)::TIMESTAMP) as p_hour,
                IFNULL(total_data.$1:client_ip, '''') as client_ip,
                total_data.$1:context.app.namespace as app_namespace,
                total_data.$1:context.app.version as app_version,
                total_data.$1:context.os.name as os_name,
                total_data.$1:context.os.version as os_version,
                DATE(IFNULL(total_data.$1:server_timestamp, total_data.$1:timestamp)::TIMESTAMP) as p_date
            from total_data;
            ';
            execute immediate :sql;
            SELECT COUNT(*) 
                INTO :row_count 
            FROM APP_GENERAL
            WHERE p_date = :p_date;
            call TASK_MONITOR_INSERT_PROC('APP_GENERAL_DAILY_INSERT', :start_time, :row_count, 'SUCCESS', 'DAILY');
        END;