/**
 * Copyright (c) 2023, WSO2 LLC. (https://www.wso2.com).
 *
 * WSO2 LLC. licenses this file to you under the Apache License,
 * Version 2.0 (the "License"); you may not use this file except
 * in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
 CREATE OR REPLACE PROCEDURE WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (
    eventStatuses_in IN VARCHAR2,
    clientIds_in IN VARCHAR2,
    purgeEventsOlderThanXNumberOfDays_in IN NUMBER,
    lastUpdatedTime_in IN NUMBER,
    purgeNonExistingResourceIds_in IN BOOLEAN,
    backupTables_in IN BOOLEAN,
    enableAudit_in IN BOOLEAN,
    enableStsGthrn_in IN BOOLEAN,
    enableRebuildIndexes_in IN BOOLEAN
) IS

-- ------------------------------------------
-- VARIABLE DECLARATION
-- ------------------------------------------

systime TIMESTAMP := systimestamp;
utcTime TIMESTAMP := sys_extract_utc(systimestamp);
deleteCount INT := 0;
chunkCount INT := 0;
batchCount INT := 0;
ROWCOUNT INT := 0;
cleaupCount INT := 0;
CURRENT_SCHEMA VARCHAR(100);
backupTable VARCHAR(100);
cursorTable VARCHAR(100);

CURSOR backupTablesCursor is
SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = CURRENT_SCHEMA AND
TABLE_NAME IN ('OB_NOTIFICATION','OB_NOTIFICATION_EVENT','OB_NOTIFICATION_ERROR');

-- ------------------------------------------
-- CONFIGURABLE ATTRIBUTES
-- ------------------------------------------

batchSize INT := 10000; -- BATCH WISE DELETE [DEFAULT : 10000]
chunkSize INT := 500000; -- CHUNK WISE DELETE FOR LARGE TABLES [DEFAULT : 500000]
checkCount INT := 100; -- SET CHECK COUNT FOR FINISH CLEANUP SCRIPT (CLEANUP ELIGIBLE EVENT NOTIFICATION COUNT SHOULD BE HIGHER THAN checkCount TO CONTINUE) [DEFAULT : 100]
sleepTime FLOAT := 2;  -- SET SLEEP TIME FOR AVOID TABLE LOCKS     [DEFAULT : 2]
enableLog BOOLEAN := TRUE ; -- ENABLE LOGGING [DEFAULT : TRUE]
logLevel VARCHAR(10) := 'TRACE'; -- SET LOG LEVELS : TRACE , DEBUG

purgeNonExistingResourceIds INT := 2;
backupTables BOOLEAN := TRUE;
enableAudit BOOLEAN := FALSE;
enableStsGthrn BOOLEAN := FALSE;
enableRebuildIndexes BOOLEAN := FALSE;

-- Event Notification Data Purging Configs
eventStatuses VARCHAR(1024) := '';
clientIds VARCHAR(4096) := '';
olderThanTimePeriodForPurging NUMBER := 60 * 60 * 24 * 365;
lastUpdatedTime NUMBER := EXTRACT(DAY FROM(utcTime - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) * 86400 + to_number(TO_CHAR(utcTime, 'SSSSS')) - olderThanTimePeriodForPurging;

BEGIN

backupTables:= backupTables_in;  -- SET IF EVENT NOTIFICATION TABLES NEEDS TO BACKUP BEFORE DELETE, WILL DROP THE PREVIOUS BACKUP TABLES IN NEXT ITERATION
enableAudit := enableAudit_in; -- SET TRUE FOR  KEEP TRACK OF ALL THE DELETED EVENT NOTIFICATIONS USING A TABLE [# IF YOU ENABLE THIS TABLE BACKUP WILL FORCEFULLY SET TO TRUE]
enableStsGthrn := enableStsGthrn_in; -- SET TRUE FOR GATHER SCHEMA LEVEL STATS TO IMPROVE QUERY PERFORMANCE
enableRebuildIndexes := enableRebuildIndexes_in; -- SET TRUE FOR REBUILD INDEXES TO IMPROVE QUERY PERFORMANCE

-- Event Notification Data Purging Configs
eventStatuses := eventStatuses_in;         -- SET EVENT_NOTIFICATION_STATUSES WHICH SHOULD BE ELIGIBLE FOR PURGING. (Ex : 'ACK,ERR')
clientIds := clientIds_in;                 -- SET CLIENT_IDS WHICH SHOULD BE ELIGIBLE FOR PURGING. (LEAVE AS EMPTY TO SKIP)

CASE WHEN purgeNonExistingResourceIds_in = TRUE
    THEN purgeNonExistingResourceIds := 1;  -- SET 1 (TRUE) FOR PURGE UNTRACEABLE NOTIFICATION EVENTS. [DEFAULT : FALSE]
    ELSE purgeNonExistingResourceIds := 2;
END CASE;

CASE WHEN purgeEventsOlderThanXNumberOfDays_in IS NOT NULL
    THEN olderThanTimePeriodForPurging := 60 * 60 * 24 * purgeEventsOlderThanXNumberOfDays_in;  -- SET TIME PERIOD (SECONDS) TO DELETE EVENT NOTIFICATION OLDER THAN N DAYS. (DEFAULT 365 DAYS) (CHECK BELOW FOR FOR INFO.)
    ELSE olderThanTimePeriodForPurging := 60 * 60 * 24 * 365;
END CASE;

CASE WHEN lastUpdatedTime_in IS NOT NULL
    THEN lastUpdatedTime := lastUpdatedTime_in;   -- SET LAST_UPDATED_TIME FOR PURGING, (IF EVENT NOTIFICATION'S UPDATED TIME IS OLDER THAN THIS VALUE THEN IT'S ELIGIBLE FOR PURGING, CHECK BELOW FOR FOR INFO.)
    ELSE lastUpdatedTime := EXTRACT(DAY FROM(utcTime - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) * 86400 + to_number(TO_CHAR(utcTime, 'SSSSS')) - olderThanTimePeriodForPurging;
END CASE;

-- HERE IF WE WISH TO PURGE EVENT NOTIFICATION WITH LAST UPDATED_TIME OLDER THAN 31 DAYS (1 MONTH), WE CAN CONFIGURE olderThanTimePeriodForPurging = 60 * 60 * 24 * 31
-- THIS VALUE IS IN SECONDS (60 (1 MINUTE) * 60 (1 HOUR) * 24 (24 HOURS = 1 DAY) * 31 (31 DAYS = 1 MONTH))
-- OR ELSE WE CAN SET THE INPUT PARAMETER purgeEventsOlderThanXNumberOfDays = 31 , FOR PURGE EVENT NOTIFICATION WITH LAST UPDATED_TIME OLDER THAN 31 DAYS.
-- IF WE WISH TO CONFIGURE EXACT TIMESTAMP OF THE LAST UPDATED_TIME RATHER THAN A TIME PERIOD, WE CAN IGNORE CONFIGURING olderThanTimePeriodForPurging, purgeEventsOlderThanXNumberOfDays
-- AND ONLY CONFIGURE lastUpdatedTime WITH EXACT UNIX TIMESTAMP.
-- EX : `SET lastUpdatedTime = 1660737878;`

-- ------------------------------------------------------
-- CREATING LOG TABLE FOR DELETING EVENT NOTIFICATIONS
-- ------------------------------------------------------

SELECT SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMA' ) INTO CURRENT_SCHEMA FROM DUAL;

IF (enableLog)
THEN
SELECT COUNT(*) INTO ROWCOUNT from ALL_TABLES where OWNER = CURRENT_SCHEMA AND table_name = upper('LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP');
    IF (ROWCOUNT = 1) then
    EXECUTE IMMEDIATE 'DROP TABLE LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP';
    COMMIT;
    END if;
EXECUTE IMMEDIATE 'CREATE TABLE LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP VARCHAR(250) , LOG VARCHAR(250)) NOLOGGING';
COMMIT;
EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP STARTED .... !'')';
EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''USING SCHEMA :'||CURRENT_SCHEMA||''')';
COMMIT;
END IF;


IF (enableAudit)
THEN
backupTables := TRUE;    -- BACKUP TABLES IS REQUIRED BE TRUE, HENCE THE AUDIT IS ENABLED.
END IF;

-- ------------------------------------------------------
-- BACKUP TABLES
-- ------------------------------------------------------


IF (backupTables)
THEN
      IF (enableLog)
      THEN
          EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''TABLE BACKUP STARTED ... !'')';
          COMMIT;
      END IF;

      FOR cursorTable IN backupTablesCursor
      LOOP

      SELECT REPLACE(''||cursorTable.TABLE_NAME||'','OB_','BAK_OB_') INTO backupTable FROM DUAL;

      IF (enableLog AND logLevel IN ('TRACE'))
      THEN
          EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''BACKING UP '||cursorTable.TABLE_NAME||' INTO '||backupTable||' STARTED '')';
          COMMIT;
      END IF;

      SELECT COUNT(*) INTO ROWCOUNT from ALL_TABLES where OWNER = CURRENT_SCHEMA AND table_name = upper(backupTable);
      IF (ROWCOUNT = 1)
      THEN
          EXECUTE IMMEDIATE 'DROP TABLE '||backupTable;
          COMMIT;
      END if;

      EXECUTE IMMEDIATE 'CREATE TABLE '||backupTable||' AS (SELECT * FROM '||cursorTable.TABLE_NAME||')';
      ROWCOUNT:= sql%rowcount;
      COMMIT;

      IF (enableLog  AND logLevel IN ('TRACE','DEBUG') )
      THEN
          EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''BACKING UP '||cursorTable.TABLE_NAME||' COMPLETED WITH : '||ROWCOUNT||''')';
          COMMIT;
      END IF;

      END LOOP;
      IF (enableLog)
      THEN
          EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
          COMMIT;
      END IF;
END IF;


-- ------------------------------------------------------
-- CREATING AUDIT TABLES FOR EVENT NOTIFICATION DELETION FOR THE FIRST TIME RUN
-- ------------------------------------------------------
IF (enableAudit)
THEN

    SELECT count(1) into ROWCOUNT FROM ALL_TABLES where OWNER = CURRENT_SCHEMA AND table_name = 'AUDITLOG_OB_EVENT_NOTIFICATION_CLEANUP';
    IF (ROWCOUNT =0 )
    THEN
        IF (enableLog  AND logLevel IN ('TRACE') )
        THEN
            EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CREATING AUDIT TABLE AUDITLOG_OB_EVENT_NOTIFICATION_CLEANUP .. ! '')';
            COMMIT;
        END IF;
        EXECUTE IMMEDIATE 'CREATE TABLE AUDITLOG_OB_EVENT_NOTIFICATION_CLEANUP as (SELECT * FROM OB_NOTIFICATION WHERE 1 = 2)';
        EXECUTE IMMEDIATE 'ALTER TABLE AUDITLOG_OB_EVENT_NOTIFICATION_CLEANUP ADD AUDIT_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP';
        COMMIT;
    ELSE
        IF (enableLog  AND logLevel IN ('TRACE') )
        THEN
            EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''USING AUDIT TABLE AUDITLOG_OB_EVENT_NOTIFICATION_CLEANUP'')';
            COMMIT;
        END IF;
    END IF;


    IF (enableLog  AND logLevel IN ('TRACE'))
    THEN
        EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
        COMMIT;
    END IF;

END IF;


---- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
---- CALCULATING EVENT NOTIFICATION COUNTS IN OB_NOTIFICATION
---- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
IF (enableLog)
THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CALCULATING EVENT NOTIFICATION COUNTS IN OB_NOTIFICATION TABLE .... !'')';
    COMMIT;

    IF (enableLog AND logLevel IN ('DEBUG','TRACE'))
    THEN
        SELECT COUNT(1) INTO ROWCOUNT FROM OB_NOTIFICATION;
        EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''TOTAL EVENT NOTIFICATIONS ON OB_NOTIFICATION TABLE BEFORE DELETE : '||ROWCOUNT||''')';
        COMMIT;
    END IF;

    IF (enableLog AND logLevel IN ('TRACE'))
    THEN
        SELECT COUNT(1) INTO cleaupCount FROM OB_NOTIFICATION A WHERE (INSTR(LOWER(','||eventStatuses||','), ','||LOWER(A.STATUS)||',') > 0) AND (clientIds IS NULL OR INSTR(LOWER(','||clientIds||','), ','||LOWER(A.CLIENT_ID)||',') > 0) AND  EXTRACT(DAY FROM(sys_extract_utc(A.updated_timestamp) - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) * 86400 + to_number(TO_CHAR(sys_extract_utc(A.updated_timestamp), 'SSSSS')) < lastUpdatedTime OR A.RESOURCE_ID IN (SELECT B.RESOURCE_ID FROM OB_NOTIFICATION B LEFT JOIN OB_CONSENT C ON B.RESOURCE_ID = C.CONSENT_ID WHERE C.CONSENT_ID IS NULL and 1 = purgeNonExistingResourceIds);
        EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''TOTAL EVENT NOTIFICATIONS SHOULD BE DELETED FROM OB_NOTIFICATION : '||cleaupCount||''')';
        EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''NOTE: ACTUAL DELETION WILL HAPPEN ONLY WHEN DELETE COUNT IS LARGER THAN CHECKCOUNT .... !'')';
        COMMIT;
    END IF;

    IF (enableLog AND logLevel IN ('TRACE'))
    THEN
        ROWCOUNT := (ROWCOUNT - cleaupCount);
        EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''TOTAL EVENT NOTIFICATIONS SHOULD BE RETAIN IN OB_NOTIFICATION : '||ROWCOUNT||''')';
        COMMIT;
    END IF;
END IF;

-- ------------------------------------------------------
-- BATCH DELETE EVENT NOTIFICATION DATA
-- ------------------------------------------------------
IF (enableLog)
THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''EVENT NOTIFICATION PURGING STARTED ... ! '')';
    COMMIT;
END IF;

LOOP
      SELECT COUNT(*) INTO ROWCOUNT from ALL_TABLES where OWNER = CURRENT_SCHEMA AND table_name = upper('CHUNK_OB_EVENT_NOTIFICATION');
      IF (ROWCOUNT = 1) then
          EXECUTE IMMEDIATE 'DROP TABLE CHUNK_OB_EVENT_NOTIFICATION';
          COMMIT;
      END if;

      EXECUTE IMMEDIATE 'CREATE TABLE CHUNK_OB_EVENT_NOTIFICATION (NOTIFICATION_ID VARCHAR(255),CONSTRAINT CHUNK_EVENT_NOTIFICATION_PRI PRIMARY KEY (NOTIFICATION_ID)) NOLOGGING';
      COMMIT;
      EXECUTE IMMEDIATE q'!INSERT /*+ APPEND */ INTO CHUNK_OB_EVENT_NOTIFICATION (NOTIFICATION_ID) SELECT A.NOTIFICATION_ID FROM OB_NOTIFICATION A WHERE rownum <= :chunkSize AND (
              (INSTR( LOWER( ','||:eventStatuses||','), LOWER( ','||A.STATUS||',') ) > 0) AND
              (:clientIds IS NULL OR INSTR( LOWER( ','||:clientIds||','), LOWER(','||A.CLIENT_ID||',') ) > 0) AND
              EXTRACT(DAY FROM(sys_extract_utc(A.UPDATED_TIMESTAMP) - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) * 86400 + to_number(TO_CHAR(sys_extract_utc(A.UPDATED_TIMESTAMP), 'SSSSS')) < :lastUpdatedTime) OR
              A.RESOURCE_ID IN (SELECT B.RESOURCE_ID FROM OB_NOTIFICATION B LEFT JOIN OB_CONSENT C ON B.RESOURCE_ID = C.CONSENT_ID WHERE C.CONSENT_ID IS NULL and 1 = :purgeNonExistingResourceIds)!'
              using chunkSize, eventStatuses, clientIds, clientIds, lastUpdatedTime, purgeNonExistingResourceIds;
      chunkCount:=  sql%Rowcount;
      COMMIT;

      EXIT WHEN chunkCount < checkCount ;

      IF (enableLog AND logLevel IN ('TRACE'))
      THEN
          EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CHUNK TABLE CHUNK_OB_EVENT_NOTIFICATION CREATED WITH : '||chunkCount||''')';
          COMMIT;
      END IF;

      IF (enableAudit)
      THEN
          EXECUTE IMMEDIATE 'INSERT INTO AUDITLOG_OB_EVENT_NOTIFICATION_CLEANUP SELECT OBN.*, CURRENT_TIMESTAMP FROM OB_NOTIFICATION OBN , CHUNK_OB_EVENT_NOTIFICATION CHK WHERE OBN.NOTIFICATION_ID = CHK.NOTIFICATION_ID';
          COMMIT;
      END IF;

      LOOP
          SELECT COUNT(*) INTO ROWCOUNT from ALL_TABLES where OWNER = CURRENT_SCHEMA AND table_name = upper('BATCH_OB_EVENT_NOTIFICATION');
          IF (ROWCOUNT = 1) then
              EXECUTE IMMEDIATE 'DROP TABLE BATCH_OB_EVENT_NOTIFICATION';
              COMMIT;
          END IF;

          EXECUTE IMMEDIATE 'CREATE TABLE BATCH_OB_EVENT_NOTIFICATION (NOTIFICATION_ID VARCHAR(255),CONSTRAINT BATCH_OB_EVENT_NOTIFICATION_PRI PRIMARY KEY (NOTIFICATION_ID)) NOLOGGING';
          COMMIT;

          EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ INTO BATCH_OB_EVENT_NOTIFICATION (NOTIFICATION_ID) SELECT NOTIFICATION_ID FROM CHUNK_OB_EVENT_NOTIFICATION WHERE rownum <= '||batchSize||'';
          batchCount:= sql%rowcount;
          COMMIT;

          EXIT WHEN batchCount = 0 ;

          IF (enableLog AND logLevel IN ('TRACE'))
              THEN
              EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''BATCH DELETE START ON EVENT NOTIFICATION TABLES WITH BATCH_COUNT : '||batchCount||''')';
              COMMIT;
          END IF;

          -- ------------------------------------------------------
          -- BATCH DELETE OB_NOTIFICATION_EVENT
          -- ------------------------------------------------------
          IF ((batchCount > 0))
          THEN
              EXECUTE IMMEDIATE 'DELETE OB_NOTIFICATION_EVENT where NOTIFICATION_ID in (select NOTIFICATION_ID from  BATCH_OB_EVENT_NOTIFICATION)';
              deleteCount:= sql%rowcount;
          COMMIT;
          END IF;

          IF (enableLog)
          THEN
              EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''BATCH DELETE FINISHED ON OB_NOTIFICATION_EVENT WITH : '||deleteCount||''')';
              COMMIT;
          END IF;

          -- ------------------------------------------------------
          -- BATCH DELETE OB_NOTIFICATION_ERROR
          -- ------------------------------------------------------
          IF ((batchCount > 0))
          THEN
              EXECUTE IMMEDIATE 'DELETE OB_NOTIFICATION_ERROR where NOTIFICATION_ID in (select NOTIFICATION_ID from  BATCH_OB_EVENT_NOTIFICATION)';
              deleteCount:= sql%rowcount;
          COMMIT;
          END IF;

          IF (enableLog)
          THEN
              EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''BATCH DELETE FINISHED ON OB_NOTIFICATION_ERROR WITH : '||deleteCount||''')';
              COMMIT;
          END IF;

          -- ------------------------------------------------------
          -- BATCH DELETE OB_NOTIFICATION
          -- ------------------------------------------------------
          IF ((batchCount > 0))
          THEN
              EXECUTE IMMEDIATE 'DELETE OB_NOTIFICATION where NOTIFICATION_ID in (select NOTIFICATION_ID from  BATCH_OB_EVENT_NOTIFICATION)';
              deleteCount:= sql%rowcount;
          COMMIT;
          END IF;

          IF (enableLog)
          THEN
              EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''BATCH DELETE FINISHED ON OB_NOTIFICATION WITH : '||deleteCount||''')';
              COMMIT;
          END IF;


          EXECUTE IMMEDIATE 'DELETE CHUNK_OB_EVENT_NOTIFICATION WHERE NOTIFICATION_ID in (select NOTIFICATION_ID from BATCH_OB_EVENT_NOTIFICATION)';
          COMMIT;

          IF (enableLog AND logLevel IN ('TRACE'))
          THEN
              EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''DELETED BATCH ON CHUNK_OB_EVENT_NOTIFICATION !'')';
              COMMIT;
          END IF;

          EXIT WHEN deleteCount = 0 ;

          IF ((deleteCount > 0))
          THEN
              IF (enableLog AND logLevel IN ('TRACE'))
              THEN
              EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''SLEEPING ...'')';
              COMMIT;
              END IF;
          DBMS_LOCK.SLEEP(sleepTime);
          END IF;
      END LOOP;
END LOOP;

IF (enableLog)
THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''BATCH DELETE ON EVENT NOTIFICATION DATA COMPLETED .... !'')';
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
END IF;
COMMIT;


IF (enableLog AND logLevel IN ('DEBUG','TRACE'))
THEN
    SELECT COUNT(1) INTO ROWCOUNT FROM OB_NOTIFICATION;
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''TOTAL EVENT NOTIFICATIONS ON OB_NOTIFICATION TABLE AFTER DELETE :'||ROWCOUNT||''')';
    COMMIT;

    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
    COMMIT;
END IF;


-- ------------------------------------------------------
-- REBUILDING INDEXES
-- ------------------------------------------------------

IF(enableRebuildIndexes)
THEN
      FOR cursorTable IN backupTablesCursor
      LOOP
            FOR INDEX_ENTRY IN (SELECT INDEX_NAME FROM ALL_INDEXES WHERE  TABLE_NAME=''||cursorTable.TABLE_NAME||'' AND INDEX_TYPE='NORMAL' AND OWNER = CURRENT_SCHEMA)
            LOOP
                IF (enableLog AND logLevel IN ('DEBUG','TRACE'))
                THEN
                EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''REBUILDING INDEXES ON '||cursorTable.TABLE_NAME||' TABLE : '||INDEX_ENTRY.INDEX_NAME||''')';
                COMMIT;
                END IF;
                EXECUTE IMMEDIATE 'ALTER INDEX ' || INDEX_ENTRY.INDEX_NAME || ' REBUILD';
                COMMIT;
            END LOOP;
      END LOOP;

      IF (enableLog AND logLevel IN ('DEBUG','TRACE'))
      THEN
      EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
      END IF;
      COMMIT;
END IF;


-- ------------------------------------------------------
-- STATS GATHERING FOR OPTIMUM PERFORMANCE
-- ------------------------------------------------------

IF(enableStsGthrn)
THEN
    IF (enableLog AND logLevel IN ('DEBUG','TRACE'))
    THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''SCHEMA LEVEL STATS GATHERING JOB STARTED.'')';
    COMMIT;
    END IF;

    BEGIN
    dbms_stats.gather_schema_stats(CURRENT_SCHEMA,DBMS_STATS.AUTO_SAMPLE_SIZE);
    END;

    IF (enableLog AND logLevel IN ('DEBUG','TRACE'))
    THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''SCHEMA LEVEL STATS GATHERING JOB COMPLETED.'')';
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
    COMMIT;
    END IF;
END IF;

IF (enableLog AND logLevel IN ('DEBUG','TRACE'))
THEN
EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''WSO2_OB_EVENT_NOTIFICATION_CLEANUP_SP COMPLETED .... !'')';
COMMIT;
END IF;

END;
