ALTER TABLE IDN_OAUTH2_SCOPE MODIFY SCOPE_ID INTEGER NOT NULL AUTO_INCREMENT;
ALTER TABLE IDN_OAUTH2_SCOPE CHANGE COLUMN `NAME` `DISPLAY_NAME` VARCHAR(255) NOT NULL;
ALTER TABLE IDN_OAUTH2_SCOPE CHANGE COLUMN `SCOPE_KEY` `NAME` VARCHAR(255) NOT NULL;
ALTER TABLE IDN_OAUTH2_SCOPE DROP COLUMN ROLES;
UPDATE IDN_OAUTH2_SCOPE SET TENANT_ID = -1 WHERE TENANT_ID = 0;
ALTER TABLE IDN_OAUTH2_SCOPE MODIFY TENANT_ID INTEGER NOT NULL DEFAULT -1;
-- CREATE UNIQUE INDEX SCOPE_INDEX ON IDN_OAUTH2_SCOPE (NAME, TENANT_ID);

SELECT CONCAT("ALTER TABLE IDN_OAUTH2_RESOURCE_SCOPE DROP FOREIGN KEY ",CONSTRAINT_NAME)
INTO @sqlst
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = "IDN_OAUTH2_RESOURCE_SCOPE"
AND REFERENCED_TABLE_NAME = "IDN_OAUTH2_SCOPE" AND REFERENCED_COLUMN_NAME = "SCOPE_ID" ;

PREPARE stmt FROM @sqlst;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @sqlstr = NULL;

ALTER TABLE IDN_OAUTH2_RESOURCE_SCOPE MODIFY SCOPE_ID INTEGER NOT NULL;
ALTER TABLE IDN_OAUTH2_RESOURCE_SCOPE ADD FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE(SCOPE_ID) ON DELETE CASCADE;
