create or replace task RECS_BACKEND_EVENT_HOURLY_INSERT
	warehouse=SUNO_PROD_X_SMAL
	schedule='USING CRON 10 * * * * UTC'
	as DECLARE
        stage_path string;
        p_date date;
        p_hour number;
        query_sql string;
        row_count number;
        start_time TIMESTAMP;
        BEGIN
            start_time := CURRENT_TIMESTAMP();
            stage_path := CONCAT('@SUNO_RECS_EVENTS/1/', YEAR(SYSDATE() - INTERVAL '1 HOUR'), '/', LPAD(MONTH(SYSDATE() - INTERVAL '1 HOUR'), 2, '0'), '/', 
                    LPAD(DAY(SYSDATE() - INTERVAL '1 HOUR'), 2, '0'), '/', 
                    LPAD(HOUR(SYSDATE() - INTERVAL '1 HOUR'), 2, '0'));
            p_date := Date(SYSDATE() - INTERVAL '1 hour');
            p_hour := HOUR(SYSDATE() - INTERVAL '1 hour');
            query_sql := 'INSERT INTO RECS_BACKEND_EVENT
            with event_data as (
                    select 
                        *,
                    from ' || :stage_path || ' (file_format => ''BASIC_JSON_LOADER'', pattern => (''.*''))
            )
            select
                event_data.$1:name as NAME,
                event_data.$1:timestamp::TIMESTAMP_TZ as TIMESTAMP,
                event_data.$1:source as SOURCE,
                event_data.$1:user_id::string as USER_ID,
                IFF(event_data.$1:session_id=''None'', null, event_data.$1:session_id) AS SESSION_ID,
                event_data.$1:properties as PROPERTIES,
                event_data.$1:request_id::string as REQUEST_ID,
                event_data.$1:_datadog as DATADOG,
                event_data.$1:processed_timestamp::TIMESTAMP_TZ as PROCESSED_TIMESTAMP,
                event_data.$1:processing_source::string as PROCESSING_SOURCE,
                DATE(CONVERT_TIMEZONE(''UTC'', event_data.$1:timestamp::TIMESTAMP_TZ)) as P_DATE,
                DATE_PART(''HOUR'', CONVERT_TIMEZONE(''UTC'', event_data.$1:timestamp::TIMESTAMP_TZ)) as P_HOUR
            from event_data;
            ';
            execute immediate :query_sql;
            select count(*) into :row_count 
            from RECS_BACKEND_EVENT
            where p_date = :p_date and p_hour = :p_hour;
            call TASK_MONITOR_INSERT_PROC('RECS_BACKEND_EVENT_HOURLY_INSERT', :start_time, :row_count, 'SUCCESS', 'HOURLY');
        END;


