-- NOTE: This procedure assumes that the SAML IDP metadata is stored under the path
-- '/_system/governance/repository/identity/provider/saml' in the registry and only two corresponding RESOURCE entries
-- (one for the collection and one for the resource object) and one CONTENT entry exist for each SAML IDP metadata file.

--<![[Start of Procedure]]>--
DECLARE
   batchCount INT := 1;
   chunkCount INT := 1;
   rowCount INT   := 0;

   batchSize INT        := 10000;
   chunkSize INT        := 500000;
   enableLog BOOLEAN    := TRUE;
   backupTables BOOLEAN := FALSE;
BEGIN
   BEGIN
       EXECUTE IMMEDIATE 'DROP TABLE RootPathIdList';
   EXCEPTION WHEN OTHERS THEN NULL;
   END;
   EXECUTE IMMEDIATE 'CREATE TABLE RootPathIdList (
       ROOT_PATH_ID INT
   )';
   BEGIN
       EXECUTE IMMEDIATE 'DROP TABLE PathIdList';
   EXCEPTION WHEN OTHERS THEN NULL;
   END;
   EXECUTE IMMEDIATE 'CREATE TABLE PathIdList (
       REG_PATH_ID INT
   )';

   EXECUTE IMMEDIATE 'INSERT INTO RootPathIdList (ROOT_PATH_ID)
       SELECT REG_PATH_ID FROM REG_PATH WHERE REG_PATH_VALUE = ''/_system/governance/repository/identity/provider/saml''';
   EXECUTE IMMEDIATE 'INSERT INTO PathIdList (REG_PATH_ID)
       SELECT REG_PATH_ID FROM REG_PATH WHERE REG_PATH_PARENT_ID IN (SELECT ROOT_PATH_ID FROM RootPathIdList)';

   IF enableLog THEN
       DBMS_OUTPUT.PUT_LINE('SAML IDP METADATA CLEANUP STARTED...');
   END IF;

   IF backupTables THEN
       IF enableLog THEN
           DBMS_OUTPUT.PUT_LINE('BACKING UP TABLES...');
       END IF;

       BEGIN
           EXECUTE IMMEDIATE 'DROP TABLE BAK_REG_RESOURCE';
           EXECUTE IMMEDIATE 'DROP TABLE BAK_REG_CONTENT';
       EXCEPTION WHEN OTHERS THEN NULL;
       END;

       EXECUTE IMMEDIATE 'CREATE TABLE BAK_REG_RESOURCE AS
           SELECT * FROM REG_RESOURCE WHERE REG_PATH_ID IN (SELECT REG_PATH_ID FROM PathIdList)';

       BEGIN
           EXECUTE IMMEDIATE 'DROP TABLE ContentIdList';
       EXCEPTION WHEN OTHERS THEN NULL;
       END;
       EXECUTE IMMEDIATE 'CREATE TABLE ContentIdList (REG_CONTENT_ID INT)';
       EXECUTE IMMEDIATE 'INSERT INTO ContentIdList (REG_CONTENT_ID)
           SELECT DISTINCT REG_CONTENT_ID FROM BAK_REG_RESOURCE WHERE REG_CONTENT_ID IS NOT NULL';
       EXECUTE IMMEDIATE 'CREATE TABLE BAK_REG_CONTENT AS
           SELECT * FROM REG_CONTENT WHERE REG_CONTENT_ID IN (SELECT REG_CONTENT_ID FROM ContentIdList)';
       IF enableLog THEN
           DBMS_OUTPUT.PUT_LINE('TABLE BACKUP COMPLETED.');
       END IF;
   END IF;

   WHILE chunkCount > 0 LOOP

       BEGIN
           EXECUTE IMMEDIATE 'DROP TABLE REG_RESOURCE_CHUNK_TMP';
           EXECUTE IMMEDIATE 'DROP TABLE REG_CONTENT_CHUNK_TMP';
       EXCEPTION WHEN OTHERS THEN NULL;
       END;

       EXECUTE IMMEDIATE 'CREATE TABLE REG_RESOURCE_CHUNK_TMP (
           REG_VERSION INT, REG_TENANT_ID INT, REG_CONTENT_ID INT
       )';
       EXECUTE IMMEDIATE 'CREATE TABLE REG_CONTENT_CHUNK_TMP (
           REG_CONTENT_ID INT
       )';

       EXECUTE IMMEDIATE 'INSERT INTO REG_RESOURCE_CHUNK_TMP (REG_VERSION, REG_TENANT_ID, REG_CONTENT_ID)
           SELECT REG_VERSION, REG_TENANT_ID, REG_CONTENT_ID FROM REG_RESOURCE
           WHERE REG_PATH_ID IN (SELECT REG_PATH_ID FROM PathIdList)
           AND ROWNUM <= ' || chunkSize;
       chunkCount := SQL%ROWCOUNT;
       EXECUTE IMMEDIATE 'INSERT INTO REG_CONTENT_CHUNK_TMP(REG_CONTENT_ID)
           SELECT REG_CONTENT_ID FROM REG_RESOURCE_CHUNK_TMP WHERE REG_CONTENT_ID IS NOT NULL';

       IF chunkCount = 0 THEN
           EXIT;
       END IF;

       batchCount := 1;
       WHILE batchCount > 0 LOOP
           BEGIN
               EXECUTE IMMEDIATE 'DROP TABLE REG_RESOURCE_BATCH_TMP';
               EXECUTE IMMEDIATE 'DROP TABLE REG_CONTENT_BATCH_TMP';
           EXCEPTION WHEN OTHERS THEN NULL;
           END;

           EXECUTE IMMEDIATE 'CREATE TABLE REG_RESOURCE_BATCH_TMP (
               REG_VERSION INT, REG_TENANT_ID INT, REG_CONTENT_ID INT
           )';
           EXECUTE IMMEDIATE 'CREATE TABLE REG_CONTENT_BATCH_TMP (
               REG_CONTENT_ID INT
           )';

           EXECUTE IMMEDIATE 'INSERT INTO REG_RESOURCE_BATCH_TMP (REG_VERSION, REG_TENANT_ID, REG_CONTENT_ID)
               SELECT REG_VERSION, REG_TENANT_ID, REG_CONTENT_ID FROM REG_RESOURCE_CHUNK_TMP WHERE ROWNUM <= ' || batchSize;
           batchCount := SQL%ROWCOUNT;
           EXECUTE IMMEDIATE 'INSERT INTO REG_CONTENT_BATCH_TMP(REG_CONTENT_ID)
               SELECT REG_CONTENT_ID FROM REG_RESOURCE_BATCH_TMP WHERE REG_CONTENT_ID IS NOT NULL';

           IF batchCount = 0 THEN
               EXIT;
           END IF;

           EXECUTE IMMEDIATE 'DELETE FROM REG_RESOURCE WHERE (REG_VERSION, REG_TENANT_ID) IN
               (SELECT REG_VERSION, REG_TENANT_ID FROM REG_RESOURCE_BATCH_TMP)';
           rowCount := SQL%ROWCOUNT;
           EXECUTE IMMEDIATE 'DELETE FROM REG_CONTENT WHERE REG_CONTENT_ID IN
               (SELECT REG_CONTENT_ID FROM REG_CONTENT_BATCH_TMP)';

           IF enableLog THEN
               DBMS_OUTPUT.PUT_LINE('BATCH DELETE COMPLETED. DELETED ROWS: ' || rowCount);
           END IF;

           EXECUTE IMMEDIATE 'DELETE FROM REG_RESOURCE_CHUNK_TMP WHERE (REG_VERSION, REG_TENANT_ID) IN
               (SELECT REG_VERSION, REG_TENANT_ID FROM REG_RESOURCE_BATCH_TMP)';
       END LOOP;
   END LOOP;

   BEGIN
       EXECUTE IMMEDIATE 'DROP TABLE REG_RESOURCE_BATCH_TMP';
       EXECUTE IMMEDIATE 'DROP TABLE REG_CONTENT_BATCH_TMP';
       EXECUTE IMMEDIATE 'DROP TABLE REG_RESOURCE_CHUNK_TMP';
       EXECUTE IMMEDIATE 'DROP TABLE REG_CONTENT_CHUNK_TMP';
       EXECUTE IMMEDIATE 'DROP TABLE RootPathIdList';
       EXECUTE IMMEDIATE 'DROP TABLE PathIdList';
       EXECUTE IMMEDIATE 'DROP TABLE ContentIdList';
   EXCEPTION WHEN OTHERS THEN NULL;
   END;
   IF enableLog THEN
       DBMS_OUTPUT.PUT_LINE('CLEANUP COMPLETED SUCCESSFULLY.');
   END IF;
EXCEPTION
   WHEN OTHERS THEN
       ROLLBACK;
       BEGIN
       EXECUTE IMMEDIATE 'DROP TABLE REG_RESOURCE_BATCH_TMP';
       EXECUTE IMMEDIATE 'DROP TABLE REG_CONTENT_BATCH_TMP';
       EXECUTE IMMEDIATE 'DROP TABLE REG_RESOURCE_CHUNK_TMP';
       EXECUTE IMMEDIATE 'DROP TABLE REG_CONTENT_CHUNK_TMP';
       EXECUTE IMMEDIATE 'DROP TABLE RootPathIdList';
       EXECUTE IMMEDIATE 'DROP TABLE PathIdList';
       EXECUTE IMMEDIATE 'DROP TABLE ContentIdList';
	    EXCEPTION WHEN OTHERS THEN NULL;
	    END;
       DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED: ' || SQLERRM);
END;
/
--<![[End of Procedure]]>--
