ALTER TABLE IDN_OAUTH2_SCOPE MODIFY SCOPE_ID INTEGER NOT NULL AUTO_INCREMENT;
ALTER TABLE IDN_OAUTH2_SCOPE ALTER COLUMN NAME RENAME TO DISPLAY_NAME;
ALTER TABLE IDN_OAUTH2_SCOPE ALTER COLUMN SCOPE_KEY RENAME TO NAME;
ALTER TABLE IDN_OAUTH2_SCOPE MODIFY NAME VARCHAR(255) NOT NULL;
ALTER TABLE IDN_OAUTH2_SCOPE MODIFY DISPLAY_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);

CREATE ALIAS IF NOT EXISTS DROP_FK AS $$ void executeSql(Connection conn, String sql) throws SQLException { conn.createStatement().executeUpdate(sql); } $$;

CALL DROP_FK('ALTER TABLE IDN_OAUTH2_RESOURCE_SCOPE DROP CONSTRAINT ' || (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE TABLE_NAME = 'IDN_OAUTH2_RESOURCE_SCOPE' AND COLUMN_LIST  = 'SCOPE_ID'));

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;
