DECLARE
   -- ------------------------------------------
   -- DECLARE VARIABLES
   -- ------------------------------------------
   rowCount INT   := 0;
   -- ------------------------------------------
   -- CONFIGURABLE ATTRIBUTES
   -- ------------------------------------------
   enableLog BOOLEAN    := TRUE; -- ENABLE LOGGING [DEFAULT : TRUE]
BEGIN
   IF (enableLog)
   THEN
       DBMS_OUTPUT.PUT_LINE('SAML IDP METADATA CLEANUP DATA RESTORATION STARTED...!');
   END IF;
   -- Check if REG_RESOURCE table exists
   SELECT COUNT(1) INTO rowCount FROM ALL_TABLES WHERE TABLE_NAME = 'REG_RESOURCE';
   IF (rowCount = 1)
   THEN
       -- Set transaction isolation level
       EXECUTE IMMEDIATE 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE';
       -- Insert data into REG_RESOURCE if not already present
       EXECUTE IMMEDIATE 'ALTER TRIGGER REG_RESOURCE_TRIGGER DISABLE';
       INSERT INTO REG_RESOURCE (REG_PATH_ID, REG_NAME, REG_VERSION, REG_MEDIA_TYPE, REG_CREATOR, REG_CREATED_TIME,
        REG_LAST_UPDATOR, REG_LAST_UPDATED_TIME, REG_DESCRIPTION, REG_CONTENT_ID, REG_TENANT_ID, REG_UUID)
       SELECT A.REG_PATH_ID, A.REG_NAME, A.REG_VERSION, A.REG_MEDIA_TYPE, A.REG_CREATOR, A.REG_CREATED_TIME,
        A.REG_LAST_UPDATOR, A.REG_LAST_UPDATED_TIME, A.REG_DESCRIPTION, A.REG_CONTENT_ID, A.REG_TENANT_ID, A.REG_UUID
        FROM BAK_REG_RESOURCE A
        LEFT JOIN REG_RESOURCE B ON A.REG_VERSION = B.REG_VERSION AND A.REG_TENANT_ID = B.REG_TENANT_ID
        WHERE B.REG_VERSION IS NULL;

       -- Get the row count after the insert
       rowCount := SQL%ROWCOUNT;

       EXECUTE IMMEDIATE 'ALTER TRIGGER REG_RESOURCE_TRIGGER ENABLE';
       -- Insert data into REG_CONTENT if not already present
       EXECUTE IMMEDIATE 'ALTER TRIGGER REG_CONTENT_TRIGGER DISABLE';
       INSERT INTO REG_CONTENT (REG_CONTENT_ID, REG_CONTENT_DATA, REG_TENANT_ID)
       SELECT A.REG_CONTENT_ID, A.REG_CONTENT_DATA, A.REG_TENANT_ID
       FROM BAK_REG_CONTENT A
       LEFT JOIN REG_CONTENT B ON A.REG_CONTENT_ID = B.REG_CONTENT_ID
       WHERE B.REG_CONTENT_ID IS NULL;
       EXECUTE IMMEDIATE 'ALTER TRIGGER REG_CONTENT_TRIGGER ENABLE';
       -- Log the number of rows inserted
       IF (enableLog)
       THEN
           DBMS_OUTPUT.PUT_LINE('CLEANUP DATA RESTORATION COMPLETED ON REG_RESOURCE WITH ' || rowCount);
       END IF;
       -- Commit the changes
       COMMIT;
   END IF;
   IF (enableLog)
   THEN
       DBMS_OUTPUT.PUT_LINE('CLEANUP DATA RESTORATION COMPLETED...!');
   END IF;
EXCEPTION
   WHEN OTHERS THEN
       ROLLBACK;
       DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED: ' || SQLERRM);
END;
/
