CREATE OR REPLACE TASK FACT_PLAY_HOURLY_UPSERT
    WAREHOUSE = FACT_PLAY_HOURLY_LARGE
    -- removing dim_user_insert as dependency
    -- sensor in dagster triggers this task after materialization of latest dim_user partition
AS
    DECLARE
        p_date DATE;
        p_hour NUMBER;
        row_count NUMBER;
        start_time TIMESTAMP;
    BEGIN
        start_time := CURRENT_TIMESTAMP();
        p_date := DATE(SYSDATE() - INTERVAL '1 HOUR');
        p_hour := HOUR(SYSDATE() - INTERVAL '1 HOUR');

        -- fact_play procedures take in start/end date/hour, and 1 hour buffer is applied inside the procedure
        CALL FACT_PLAY_WEB_UPSERT_PROC(:p_date, :p_hour, :p_date, :p_hour);
        CALL FACT_PLAY_IOS_UPSERT_PROC(:p_date, :p_hour, :p_date, :p_hour);
        CALL FACT_PLAY_IOS_LEGACY_UPSERT_PROC(:p_date, :p_hour, :p_date, :p_hour);
        CALL FACT_PLAY_ANDROID_UPSERT_PROC(:p_date, :p_hour, :p_date, :p_hour);

        SELECT COUNT(*) INTO :row_count
        FROM FACT_PLAY
        WHERE 1=1
            AND p_date = :p_date
            AND p_hour = :p_hour;
        CALL TASK_MONITOR_INSERT_PROC('FACT_PLAY_HOURLY_UPSERT', :start_time, :row_count, 'SUCCESS', 'HOURLY');

        -- removing data validation below from snowflake due to migration to dagster checks
        -- task still needs to run until fact_play is migrated to dagster
        
        --data validation
        -- CALL FACT_PLAY_HOURLY_INSERT_DATA_VALIDATION_PROC(:p_date, :p_hour);
    END;
