DROP PROCEDURE IF EXISTS `WSO2_CONFIRMATION_CODE_CLEANUP_RESTORE`;
DELIMITER $$
CREATE PROCEDURE `WSO2_CONFIRMATION_CODE_CLEANUP_RESTORE`()
BEGIN
    -- ------------------------------------------
    -- DECLARE VARIABLES
    -- ------------------------------------------
    DECLARE rowCount INT;
    DECLARE enableLog BOOLEAN;

    -- ------------------------------------------
    -- CONFIGURABLE ATTRIBUTES
    -- ------------------------------------------
    SET enableLog = FALSE; -- ENABLE LOGGING [DEFAULT : FALSE]

    IF (enableLog) THEN
        SELECT  'CLEANUP DATA RESTORATION STARTED...!' AS 'INFO LOG';
    END IF;

    SELECT COUNT(1) INTO rowcount FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA IN (SELECT DATABASE())  AND TABLE_NAME IN ('IDN_RECOVERY_FLOW_DATA');
    IF (rowcount = 1)
    THEN
        INSERT INTO IDN_RECOVERY_FLOW_DATA SELECT A.* FROM BAK_IDN_RECOVERY_FLOW_DATA A LEFT JOIN IDN_RECOVERY_FLOW_DATA B ON A.RECOVERY_FLOW_ID = B.RECOVERY_FLOW_ID WHERE B.RECOVERY_FLOW_ID IS NULL;
        SELECT row_count() INTO rowCount;
        IF (enableLog) THEN
            SELECT 'CLEANUP DATA RESTORATION COMPLETED ON IDN_RECOVERY_FLOW_DATA WITH %' + rowCount AS 'INFO LOG';
        END IF;
    END IF;

    SELECT COUNT(1) INTO rowcount  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA IN (SELECT DATABASE())  AND TABLE_NAME IN ('IDN_RECOVERY_DATA');
    IF (rowcount = 1)
    THEN
        INSERT INTO IDN_RECOVERY_DATA SELECT A.* FROM BAK_IDN_RECOVERY_DATA A LEFT JOIN IDN_RECOVERY_DATA B ON A.CODE = B.CODE WHERE B.CODE IS NULL;
        SELECT row_count() INTO rowCount;
        IF (enableLog) THEN
            SELECT 'CLEANUP DATA RESTORATION COMPLETED ON IDN_RECOVERY_DATA WITH %' + rowCount AS 'INFO LOG';
        END IF;
    END IF;

    IF (enableLog) THEN
        SELECT  'CLEANUP DATA RESTORATION COMPLETED...!';
    END IF;

END$$

DELIMITER ;
