create or replace task APP_ONBOARDING_ACTIONS_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-Onboarding-Action-Event/0/', YEAR(SYSDATE() - INTERVAL '1 DAY'), '/', MONTH(SYSDATE() - INTERVAL '1 DAY'), '/', DAY(SYSDATE() - INTERVAL '1 DAY'));
        sql := 'INSERT INTO APP_ONBOARDING_ACTIONS(
            EVENT_NAME,
            ACTION_NAME,
            USER_ID,
            ANONYMOUS_ID,
            CLIENT_TIMESTAMP,
            SERVER_TIMESTAMP,
            USER_AGENT,
            LOCALE,
            TIMEZONE,
            P_DATE,
            P_HOUR,
            CLIENT_IP,
            APP_NAMESPACE,
            APP_VERSION,
            OS_NAME,
            OS_VERSION
        )	
        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: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,
            DATE(SYSDATE() - INTERVAL ''1 DAY'') as p_date,
            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
        from total_data;
        ';
        execute immediate :sql;
        SELECT COUNT(*) 
            INTO :row_count 
        FROM APP_ONBOARDING_ACTIONS
        WHERE p_date = :p_date;
        call TASK_MONITOR_INSERT_PROC('APP_ONBOARDING_ACTIONS_DAILY_INSERT', :start_time, :row_count, 'SUCCESS', 'DAILY');
END;