CREATE OR REPLACE FUNCTION cleanup_events_tasks_proc()
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
AS
$$
-- ==================================================================
/**
  Purpose:

    This procedure cleans up events and tasks older than a configured
    maximum age, if the corresponding enabling flags are set to true.

    The cleanup is performed in a loop, 1000 events and tasks at a
    time, in order to avoid a huge hit on the database the first time
    this feature is enabled.

*/
--
-- ==================================================================
DECLARE
   batchsize_event INTEGER;
   batchsize_task INTEGER;
   -- maxage is how many days worth of stuff we keep
   eventCleanupEnabled VARCHAR(5);
   eventMaxAge INTEGER;
   eventCleanupStart TIMESTAMP;
   taskCleanupEnabled VARCHAR(5);
   taskMaxAge INTEGER;
   taskCleanupStart TIMESTAMP;
   -- delete counters for the loops
   eventcount INTEGER;
   taskcount INTEGER;
   event_partition VARCHAR(300);
   event_statistics VARCHAR(300);
   partition_number INTEGER;
   -- time when we started work
   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;
   -- Get database login parameters
   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;

   -- Get Event aging parameters
   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;

   -- Get Task aging parameters
   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;

   /* Bug# 891802 - Delete Orphaned Task Rows
      Set complete_state to error */
   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');

--======= Clean up TASK  ====================
  -- Clean up tasks
  LOOP
      -- Clean up Tasks by queue time
      taskcount := 0;
      IF (lower(taskCleanupEnabled) = 'true' AND taskMaxAge > 0) THEN
         -- delete first 'batchsize' tasks
         IF (clock_timestamp() > startTime + '30 minutes'::interval) THEN
             --wrap up for now.
             -- This is supposed to only happen the first time we turn this feature on,
             -- with a large backlog of events and tasks to be deleted. This
             -- gives the system a chance to catch its breath.
             RAISE LOG 'Termination task clean up after 30 minutes';
             EXIT;

         ELSE
             RAISE LOG 'Proceeding with one batch of tasks cleanup';
           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')
                          AND NOT EXISTS (
                              SELECT  1
                              FROM    VPX_TASK t2
                              WHERE   t2.parent_task_id = t1.task_id)
                          ORDER BY t1.queue_time ASC
                          LIMIT   batchsize_task);

           GET DIAGNOSTICS taskcount = ROW_COUNT;
           RAISE LOG '% rows has been deleted', taskcount;
           IF taskcount = 0 THEN
              RAISE LOG 'Cleaning tasks completed';
              --wrap up for now since no more tasks are available for cleanup
              EXIT;
           END IF;
         END IF;
      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;

--=================================================


--======= Clean up EVENTS  ========================
  --Clean up partitions
  IF (lower(eventCleanupEnabled) = 'true' AND eventMaxAge > 0 ) THEN
     BEGIN

        -- Clean up from VPX_EVENT_partitions
        eventCleanupStart := cast(eventCleanupStart AS DATE);
        -- delete old partitions
        -- use RETURNING to keep track of which
        -- were deleted
        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;

        -- store all partition indexes which are
        -- existing after we've deleted the old ones
        -- this is necessary when a partition is still
        -- active, i.e. retention period larger than 90 days
        SELECT array_agg(partition_index)
        INTO array_exclude
        FROM VPX_EVENT_PARTITION_LOOKUP;

        -- delete from array_deleted all partitions which
        -- we shouldn't truncate

        IF array_length(array_exclude, 1) > 0 THEN
           FOREACH part IN ARRAY array_exclude LOOP
              array_deleted := array_remove(array_deleted, part);
           END LOOP;

           -- truncate partitions
           -- the main idea is not to use lookup table
           -- while we truncate + analyze the partitions
           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;


      -- Clean up last events
      LOOP
         -- Clean up last events by creation time
         eventcount := 0;
         -- delete first 'batchsize' events
         DELETE FROM VPX_ENTITY_LAST_EVENT
         WHERE LAST_EVENT_ID NOT IN (
                            SELECT  event_id
                            FROM    VPXV_EVENT_ALL
                            LIMIT   batchsize_event);
                            --TODO: Remove one of the above query
         GET DIAGNOSTICS eventcount = ROW_COUNT;

         -- Exit if we're all done.
         IF (eventcount < batchsize_event)  THEN
            -- If both delete counts are less than the batch size, they
            -- must be the last batch, if at all enabled.
            EXIT;
         END IF;
         -- Or if we've been working for over an hour:
         IF (clock_timestamp() > startTime + '1 hour'::interval) THEN
            -- wrap up for now.
            -- This will only happen the first time we turn this feature on,
            -- with a large backlog of events and tasks to be deleted. This
            -- gives the system a chance to catch its breath.
            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;
--======= Clean up EVENTS  ========================
 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;


-- Lock and Analyze the VPX_TASK table.  TODO: Switch to SKIP_LOCKED (bug# 2412723) in Postgres 12 version
BEGIN
   LOCK TABLE VPX_TASK IN SHARE UPDATE EXCLUSIVE MODE NOWAIT;
   ANALYZE VPX_TASK;
EXCEPTION
   WHEN OTHERS THEN
      -- Ignore all exceptions. Just skip analyze.
      RAISE LOG 'Cannot acquire lock on VPX_TASK. Skip analyze';
END;

-- Lock and Analyze the VPX_ENTITY_LAST_EVENT table.  TODO: Switch to SKIP_LOCKED (bug# 2412723) in Postgres 12 version
BEGIN
   LOCK TABLE VPX_ENTITY_LAST_EVENT IN SHARE UPDATE EXCLUSIVE MODE NOWAIT;
   ANALYZE VPX_ENTITY_LAST_EVENT;
EXCEPTION
   WHEN OTHERS THEN
      -- Ignore all exceptions. Just skip analyze.
      RAISE LOG 'Cannot acquire lock on VPX_ENTITY_LAST_EVENT. Skip analyze';
END;

END
$$
;