-- 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

DELIMITER $$
CREATE PROCEDURE WSO2_SAML_IDP_METADATA_CLEANUP()
BEGIN

    DECLARE batchSize INT;
    DECLARE chunkSize INT;
    DECLARE batchCount INT;
    DECLARE chunkCount INT;
    DECLARE rowCount INT;
    DECLARE enableLog BOOLEAN;
    DECLARE backupTables BOOLEAN;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        IF (enableLog)
        THEN
            SELECT 'ERROR OCCURRED: TRANSACTION ROLLED BACK' AS 'INFO LOG';
        END IF;
    END;

    SET batchSize    = 10000;
    SET chunkSize    = 500000;
    SET enableLog    = TRUE;
    SET backupTables = FALSE;

    SET rowCount = 0;
    SET batchCount = 1;
    SET chunkCount = 1;

    DROP TEMPORARY TABLE IF EXISTS LOGGER;
    CREATE TEMPORARY TABLE LOGGER (MESSAGE TEXT);

    IF (enableLog)
    THEN
        INSERT INTO LOGGER VALUES ('WSO2_SAML_IDP_METADATA_CLEANUP() STARTED...!');
    END IF;

    DROP TEMPORARY TABLE IF EXISTS RootPathIdList;
    CREATE TEMPORARY TABLE RootPathIdList (ROOT_PATH_ID INT);
    INSERT INTO RootPathIdList (ROOT_PATH_ID) SELECT REG_PATH_ID FROM REG_PATH WHERE REG_PATH_VALUE = '/_system/governance/repository/identity/provider/saml';

    DROP TEMPORARY TABLE IF EXISTS PathIdList;
    CREATE TEMPORARY TABLE PathIdList (REG_PATH_ID INT);
    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 (backupTables)
    THEN
        IF (enableLog)
        THEN
            INSERT INTO LOGGER VALUES ('TABLE BACKUP STARTED ... !');
        END IF;

        IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'BAK_REG_RESOURCE' and TABLE_SCHEMA in (SELECT DATABASE())))
        THEN
            IF (enableLog)
            THEN
                INSERT INTO LOGGER VALUES ('DELETING OLD BACKUP...');
            END IF;
            DROP TABLE BAK_REG_RESOURCE;
            DROP TABLE BAK_REG_CONTENT;
        END IF;

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

        DROP TEMPORARY TABLE IF EXISTS ContentIdList;
        CREATE TEMPORARY TABLE ContentIdList (REG_CONTENT_ID INT);
        INSERT INTO ContentIdList (REG_CONTENT_ID) SELECT DISTINCT REG_CONTENT_ID FROM BAK_REG_RESOURCE WHERE
         REG_CONTENT_ID IS NOT NULL;
        CREATE TABLE BAK_REG_CONTENT AS SELECT * FROM REG_CONTENT WHERE REG_CONTENT_ID IN (SELECT REG_CONTENT_ID FROM ContentIdList);
    END IF;

    START TRANSACTION;

    CONFIRMATION_CHUNK_LOOP : WHILE (chunkCount > 0)
    DO
        DROP TABLE IF EXISTS REG_RESOURCE_CHUNK_TMP;
        DROP TABLE IF EXISTS REG_CONTENT_CHUNK_TMP;

        CREATE TABLE REG_RESOURCE_CHUNK_TMP AS SELECT REG_VERSION, REG_TENANT_ID, REG_CONTENT_ID FROM REG_RESOURCE WHERE
         REG_PATH_ID IN (SELECT REG_PATH_ID FROM PathIdList) LIMIT chunkSize;
        SELECT row_count() INTO chunkCount;
        CREATE TABLE REG_CONTENT_CHUNK_TMP AS SELECT REG_CONTENT_ID FROM REG_RESOURCE_CHUNK_TMP WHERE REG_CONTENT_ID IS NOT NULL;

        IF (chunkCount = 0)
        THEN
            LEAVE CONFIRMATION_CHUNK_LOOP;
        END IF;

        CREATE INDEX REG_RESOURCE_CHUNK_TMP on REG_RESOURCE_CHUNK_TMP (REG_VERSION, REG_TENANT_ID, REG_CONTENT_ID);
        CREATE INDEX REG_CONTENT_CHUNK_TMP on REG_CONTENT_CHUNK_TMP (REG_CONTENT_ID);

        IF (enableLog)
        THEN
            INSERT INTO LOGGER VALUES ('CREATED REG_RESOURCE_CHUNK_TMP...');
        END IF;

        SET batchCount = 1;
        CONFIRMATION_BATCH_LOOP : WHILE (batchCount > 0)
        DO
            DROP TABLE IF EXISTS REG_RESOURCE_BATCH_TMP;
            DROP TABLE IF EXISTS REG_CONTENT_BATCH_TMP;

            CREATE TABLE REG_RESOURCE_BATCH_TMP AS SELECT REG_VERSION, REG_TENANT_ID, REG_CONTENT_ID FROM REG_RESOURCE_CHUNK_TMP LIMIT batchSize;
            SELECT row_count() INTO batchCount;
            CREATE TABLE REG_CONTENT_BATCH_TMP AS SELECT REG_CONTENT_ID FROM REG_RESOURCE_BATCH_TMP WHERE REG_CONTENT_ID IS NOT NULL;

            IF (batchCount = 0)
            THEN
                LEAVE CONFIRMATION_BATCH_LOOP;
            END IF;

            CREATE INDEX REG_RESOURCE_BATCH_TMP on REG_RESOURCE_BATCH_TMP (REG_VERSION, REG_TENANT_ID);
            CREATE INDEX REG_CONTENT_BATCH_TMP on REG_CONTENT_BATCH_TMP (REG_CONTENT_ID);

            IF (enableLog)
            THEN
                INSERT INTO LOGGER VALUES ('CREATED REG_RESOURCE_BATCH_TMP...');
            END IF;

            IF (enableLog)
            THEN
                INSERT INTO LOGGER VALUES ('BATCH DELETE STARTED ON REG_RESOURCE...');
            END IF;

            DELETE r FROM REG_RESOURCE r INNER JOIN REG_RESOURCE_BATCH_TMP tmp ON r.REG_VERSION = tmp.REG_VERSION
             AND r.REG_TENANT_ID = tmp.REG_TENANT_ID;
            SELECT row_count() INTO rowCount;
            DELETE FROM REG_CONTENT WHERE REG_CONTENT_ID IN (SELECT REG_CONTENT_ID FROM REG_CONTENT_BATCH_TMP);

            IF (enableLog)
            THEN
                INSERT INTO LOGGER VALUES (CONCAT('BATCH DELETE FINISHED ON REG_RESOURCE : ', rowCount));
            END IF;

            DELETE r FROM REG_RESOURCE_CHUNK_TMP r INNER JOIN  REG_RESOURCE_BATCH_TMP tmp ON
             r.REG_VERSION = tmp.REG_VERSION AND r.REG_TENANT_ID = tmp.REG_TENANT_ID;
        END WHILE;
    END WHILE;

    DROP TABLE IF EXISTS REG_RESOURCE_BATCH_TMP;
    DROP TABLE IF EXISTS REG_CONTENT_BATCH_TMP;
    DROP TABLE IF EXISTS REG_RESOURCE_CHUNK_TMP;
    DROP TABLE IF EXISTS REG_CONTENT_CHUNK_TMP;

    COMMIT;

    IF (enableLog)
    THEN
        INSERT INTO LOGGER VALUES ('CLEANUP COMPLETED...!');
    END IF;
    SELECT * FROM LOGGER;

END $$
DELIMITER ;
-- End of Procedure

CALL WSO2_SAML_IDP_METADATA_CLEANUP();
DROP PROCEDURE IF EXISTS WSO2_SAML_IDP_METADATA_CLEANUP;
