/**
 * 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_DATA_RESTORE_SP IS


rowCount INT;
CURRENT_SCHEMA VARCHAR(100);
-- ------------------------------------------
-- CONFIGURABLE ATTRIBUTES
-- ------------------------------------------
enableLog BOOLEAN := TRUE; -- ENABLE LOGGING [DEFAULT : TRUE]
logLevel VARCHAR(10):= 'TRACE'; -- SET LOG LEVELS : TRACE


BEGIN

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_DATA_RESTORE_SP');
    IF (rowCount = 1) then
    EXECUTE IMMEDIATE 'DROP TABLE LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_DATA_RESTORE_SP';
    COMMIT;
    END if;
    EXECUTE IMMEDIATE 'CREATE TABLE LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_DATA_RESTORE_SP (TIMESTAMP VARCHAR(250) , LOG VARCHAR(250)) NOLOGGING';
    COMMIT;
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_DATA_RESTORE_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''WSO2_OB_EVENT_NOTIFICATION_CLEANUP_DATA_RESTORE_SP STARTED .... !'')';
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_DATA_RESTORE_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_DATA_RESTORE_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''USING SCHEMA :'||CURRENT_SCHEMA||''')';
    COMMIT;
END IF;


-- ---------------------

SELECT COUNT(1) INTO rowCount FROM ALL_TABLES where OWNER = CURRENT_SCHEMA AND TABLE_NAME IN ('OB_NOTIFICATION');
IF (rowCount = 1)
THEN
    IF (enableLog AND logLevel IN ('TRACE')) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_DATA_RESTORE_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION STARTED ON OB_NOTIFICATION TABLE !'')';
    COMMIT;
    END IF;
    EXECUTE IMMEDIATE 'INSERT INTO OB_NOTIFICATION SELECT A.* FROM BAK_OB_NOTIFICATION A LEFT JOIN OB_NOTIFICATION B ON A.NOTIFICATION_ID = B.NOTIFICATION_ID WHERE B.NOTIFICATION_ID IS NULL';
    rowCount:=  sql%Rowcount;
    IF (enableLog ) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_DATA_RESTORE_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION COMPLETED ON OB_NOTIFICATION WITH '||rowCount||''')';
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_DATA_RESTORE_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
    COMMIT;
    END IF;
END IF;

-- ---------------------

SELECT COUNT(1) INTO rowCount FROM ALL_TABLES where OWNER = CURRENT_SCHEMA AND TABLE_NAME IN ('OB_NOTIFICATION_EVENT');
IF (rowCount = 1)
THEN
    IF (enableLog AND logLevel IN ('TRACE')) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_DATA_RESTORE_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION STARTED ON OB_NOTIFICATION_EVENT TABLE !'')';
    COMMIT;
    END IF;
    EXECUTE IMMEDIATE 'INSERT INTO OB_NOTIFICATION_EVENT SELECT A.* FROM BAK_OB_NOTIFICATION_EVENT A LEFT JOIN OB_NOTIFICATION_EVENT B ON A.NOTIFICATION_ID = B.NOTIFICATION_ID WHERE B.NOTIFICATION_ID IS NULL';
    rowCount:=  sql%Rowcount;
    IF (enableLog ) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_DATA_RESTORE_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION COMPLETED ON OB_NOTIFICATION_EVENT WITH '||rowCount||''')';
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_DATA_RESTORE_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
    COMMIT;
    END IF;
END IF;

-- ---------------------

SELECT COUNT(1) INTO rowCount FROM ALL_TABLES where OWNER = CURRENT_SCHEMA AND TABLE_NAME IN ('OB_NOTIFICATION_ERROR');
IF (rowCount = 1)
THEN
    IF (enableLog AND logLevel IN ('TRACE')) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_DATA_RESTORE_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION STARTED ON OB_NOTIFICATION_ERROR TABLE !'')';
    COMMIT;
    END IF;
    EXECUTE IMMEDIATE 'INSERT INTO OB_NOTIFICATION_ERROR SELECT A.* FROM BAK_OB_NOTIFICATION_ERROR A LEFT JOIN OB_NOTIFICATION_ERROR B ON A.NOTIFICATION_ID = B.NOTIFICATION_ID WHERE B.NOTIFICATION_ID IS NULL';
    rowCount:=  sql%Rowcount;
    IF (enableLog ) THEN
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_DATA_RESTORE_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),''CLEANUP DATA RESTORATION COMPLETED ON OB_NOTIFICATION_ERROR WITH '||rowCount||''')';
    EXECUTE IMMEDIATE 'INSERT INTO LOG_WSO2_OB_EVENT_NOTIFICATION_CLEANUP_DATA_RESTORE_SP (TIMESTAMP,LOG) VALUES (TO_CHAR( SYSTIMESTAMP, ''DD.MM.YYYY HH24:MI:SS:FF4''),'' '')';
    COMMIT;
    END IF;
END IF;

-- ---------------------

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

END;
