SELECT CONCAT("ALTER TABLE IDN_OIDC_PROPERTY DROP FOREIGN KEY ", CONSTRAINT_NAME)
INTO @sqlst
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'IDN_OIDC_PROPERTY'
AND REFERENCED_TABLE_NAME = 'IDN_OAUTH_CONSUMER_APPS';

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

SELECT CONCAT("ALTER TABLE IDN_OAUTH2_CIBA_AUTH_CODE DROP FOREIGN KEY ", CONSTRAINT_NAME)
INTO @sqlst
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'IDN_OAUTH2_CIBA_AUTH_CODE'
AND REFERENCED_TABLE_NAME = 'IDN_OAUTH_CONSUMER_APPS';

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

DROP INDEX IDX_IOP_CK ON IDN_OIDC_PROPERTY;

ALTER TABLE IDN_OAUTH_CONSUMER_APPS DROP KEY CONSUMER_KEY_CONSTRAINT;

ALTER TABLE IDN_OAUTH_CONSUMER_APPS ADD CONSTRAINT CONSUMER_KEY_CONSTRAINT UNIQUE (TENANT_ID, CONSUMER_KEY);

ALTER TABLE IDN_OIDC_PROPERTY ADD CONSTRAINT FK_TENANT_CONSUMER_KEY_IOP_IOCA FOREIGN KEY (TENANT_ID, CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(TENANT_ID, CONSUMER_KEY) ON DELETE CASCADE;

ALTER TABLE IDN_OAUTH2_CIBA_AUTH_CODE ADD CONSTRAINT FK_TENANT_CONSUMER_KEY_IOCAC_IOCA FOREIGN KEY (TENANT_ID, CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(TENANT_ID, CONSUMER_KEY) ON DELETE CASCADE;

CREATE INDEX IDX_IOP_CK ON IDN_OIDC_PROPERTY(TENANT_ID, CONSUMER_KEY);
