CREATE OR REPLACE FUNCTION cleanup_events_tasks_proc()
 RETURNS VOID
 LANGUAGE plpgsql
 VOLATILE
 AS
 $$
 
 
 
 
 DECLARE
    batchsize_event INTEGER;
    batchsize_task INTEGER;
    
    eventCleanupEnabled VARCHAR(5);
    eventMaxAge INTEGER;
    eventCleanupStart TIMESTAMP;
    taskCleanupEnabled VARCHAR(5);
    taskMaxAge INTEGER;
    taskCleanupStart TIMESTAMP;
    
    eventcount INTEGER;
    taskcount INTEGER;
    event_partition VARCHAR(300);
    event_statistics VARCHAR(300);
    partition_number INTEGER;
    
    baseUTCTime TIMESTAMP WITH TIME ZONE := timezone('UTC', clock_timestamp());
    startTime TIMESTAMP WITH TIME ZONE := CLOCK_TIMESTAMP();
    vpxd_date TIMESTAMP WITH TIME ZONE;
    part int;
    array_deleted int[];
    array_exclude int[];
 
    err_log char(3);
    errnum  INTEGER;
    errmsg  VARCHAR(1000);
    retention_log INTEGER;
 
 BEGIN
 
    errnum := 0;
    
    BEGIN
       SELECT COALESCE(value,'OFF') INTO STRICT err_log
       FROM VPX_PARAMETER WHERE name = 'DBProc.Log.Debug.Info';
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          err_log := 'OFF';
    END;
    BEGIN
       SELECT  to_number(COALESCE(value,'0'), '999999999')  INTO STRICT retention_log
       FROM VPX_PARAMETER WHERE name = 'DBProc.Log.Event.Purge';
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          retention_log := 0;
    END;
 
    DELETE FROM vpx_proc_log
    WHERE event_dt <  timezone('UTC', clock_timestamp()) - (retention_log || ' days')::interval
    AND PROC_NAME = 'CLEANUP_EVENTS_TASKS_PROC';
 
    IF err_log = 'ON' THEN
        INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
             VALUES ('CLEANUP_EVENTS_TASKS_PROC','START', TIMEZONE('UTC',clock_timestamp()), 0, 'Start procedure');
    END IF;
 
    
    BEGIN
       SELECT COALESCE(value,'false') INTO STRICT eventCleanupEnabled
       FROM VPX_PARAMETER WHERE name = 'event.maxAgeEnabled';
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          eventCleanupEnabled := 'false';
    END;
    BEGIN
       SELECT to_number(COALESCE(value,'-1'), '999999999') INTO STRICT eventMaxAge
       FROM VPX_PARAMETER WHERE name='event.maxAge';
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          eventMaxAge := -1;
    END;
    BEGIN
       SELECT to_number(COALESCE(value,'2000'), '999999999') INTO STRICT  batchsize_event
       FROM VPX_PARAMETER WHERE name='event.batchsize';
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          batchsize_event := 2000;
    END;
 
 
    BEGIN
       SELECT COALESCE(MAX(START_DATE), timezone('UTC', clock_timestamp())) INTO STRICT vpxd_date
       FROM VPX_EVENT_PARTITION_LOOKUP;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          vpxd_date := timezone('UTC', clock_timestamp()) ;
    END;
 
    eventCleanupStart := vpxd_date - (eventMaxAge || ' days')::interval;
 
    
    BEGIN
       SELECT COALESCE(value,'false') INTO STRICT taskCleanupEnabled
       FROM VPX_PARAMETER WHERE name = 'task.maxAgeEnabled';
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          taskCleanupEnabled := 'false';
    END;
    BEGIN
       SELECT to_number(COALESCE(value,'-1'), '999999999') INTO STRICT taskMaxAge
       FROM VPX_PARAMETER WHERE name='task.maxAge';
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          taskMaxAge := -1;
    END;
    BEGIN
       SELECT to_number(COALESCE(value,'2000'), '999999999') INTO STRICT batchsize_task
       FROM VPX_PARAMETER WHERE name='task.batchsize';
     EXCEPTION
       WHEN NO_DATA_FOUND THEN
          batchsize_task := 2000;
    END;
 
    taskCleanupStart := baseUTCTime - (taskMaxAge || ' days')::interval;
 
    
    UPDATE VPX_TASK
     SET COMPLETE_STATE = 'error'
    WHERE COMPLETE_TIME IS NULL AND
          COMPLETE_STATE='running' AND
           QUEUE_TIME < (date_trunc('day',baseUTCTime) - INTERVAL '30 days');
 
 
   
   LOOP
       
       taskcount := 0;
       IF (lower(taskCleanupEnabled) = 'true' AND taskMaxAge > 0) THEN
          
          DELETE FROM VPX_TASK t
          WHERE task_id IN (
                           SELECT t1.task_id
                           FROM    vpx_task t1
                           WHERE   t1.queue_time < taskCleanupStart
                           and lower(t1.complete_state) in ('error', 'success')
                           ORDER BY t1.queue_time ASC
                           LIMIT   batchsize_task)
             AND NOT EXISTS (
                             SELECT  1
                             FROM    vpx_task t2
                             WHERE   t2.parent_task_id = t.task_id);
 
         GET DIAGNOSTICS taskcount = ROW_COUNT;
       END IF;
 
       
       IF (taskcount < batchsize_task) THEN
          
          
          EXIT;
       END IF;
       
       IF (clock_timestamp() > startTime + '30 minutes'::interval) THEN
          
          
          
          
          EXIT;
       END IF;
    END LOOP;
 
    IF err_log = 'ON' THEN
        INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
             VALUES ('CLEANUP_EVENTS_TASKS_PROC','EXEC', TIMEZONE('UTC',clock_timestamp()), 0, 'End cleanup task');
    END IF;
 
 
 
 
 
   
   IF (lower(eventCleanupEnabled) = 'true' AND eventMaxAge > 0 ) THEN
      BEGIN
 
         
         eventCleanupStart := cast(eventCleanupStart AS DATE);
         
         
         
         with part_del as (
             DELETE FROM VPX_EVENT_PARTITION_LOOKUP
             WHERE START_DATE < eventCleanupStart
             RETURNING partition_index)
         SELECT array_agg(partition_index)
         FROM part_del
         INTO array_deleted;
 
         
         
         
         
         SELECT array_agg(partition_index)
         INTO array_exclude
         FROM VPX_EVENT_PARTITION_LOOKUP;
 
         
         
 
         IF array_length(array_exclude, 1) > 0 THEN
            FOREACH part IN ARRAY array_exclude LOOP
               array_deleted := array_remove(array_deleted, part);
            END LOOP;
 
            
            
            
            IF array_length(array_deleted, 1) > 0 THEN
               FOREACH part IN ARRAY array_deleted
               LOOP
 
                   event_partition = 'TRUNCATE TABLE VPX_EVENT_' || CAST(part AS TEXT) || ' CASCADE ';
                   EXECUTE event_partition;
                   event_partition = 'TRUNCATE TABLE VPX_EVENT_ARG_' || CAST(part AS TEXT) || ' CASCADE ';
                   EXECUTE event_partition;
                   event_statistics = 'ANALYZE VPX_EVENT_' || CAST(part AS TEXT);
                   EXECUTE event_statistics;
                   event_statistics = 'ANALYZE VPX_EVENT_ARG_' || CAST(part AS TEXT);
                   EXECUTE event_statistics;
               END LOOP;
            END IF;
       END IF;
 
 
       
       LOOP
          
          eventcount := 0;
          
          DELETE FROM VPX_ENTITY_LAST_EVENT
          WHERE LAST_EVENT_ID NOT IN (
                             SELECT  event_id
                             FROM    VPXV_EVENT_ALL
                             LIMIT   batchsize_event);
                             
          GET DIAGNOSTICS eventcount = ROW_COUNT;
 
          
          IF (eventcount < batchsize_event)  THEN
             
             
             EXIT;
          END IF;
          
          IF (clock_timestamp() > startTime + '1 hour'::interval) THEN
             
             
             
             
             EXIT;
          END IF;
       END LOOP;
 
    EXCEPTION
       WHEN OTHERS THEN
          errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
          IF err_log = 'ON' THEN
              INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                VALUES ('CLEANUP_EVENTS_TASKS_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -5, SUBSTR(errmsg, 1, 1000));
          END IF;
    END;
    END IF;
 
  IF err_log = 'ON' THEN
     INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
          VALUES ('CLEANUP_EVENTS_TASKS_PROC','END', TIMEZONE('UTC',clock_timestamp()), 0, 'End procedure');
  END IF;
 
 EXECUTE 'ANALYZE VPX_TASK' ;
 EXECUTE 'ANALYZE VPX_ENTITY_LAST_EVENT' ;
 
 END
 $$
 ;