ALTER TABLE IDN_BASE_TABLE CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_CONFIG_TYPE CONVERT TO CHARACTER SET latin1;

INSERT INTO IDN_CONFIG_TYPE (ID, NAME, DESCRIPTION) VALUES
('8469a176-3e6c-438a-ba01-71e9077072fa', 'APPLICATION_BRANDING_PREFERENCES', 'A resource type to keep the application branding preferences'),
('f4e83b8a-d1c4-a0d6-03a7-d48e268c60c5', 'PK_JWT_CONFIGURATION', 'A resource type to keep the tenant private key jwt configuration.'),
('9ec61e9d-f0e6-4952-9a09-ab842aeb2db2', 'ATTRIBUTE_CONFIGURATION', 'A resource type to store attribute related configurations.'),
('132b0ee6-43e0-462d-8b4b-15b68109d71d', 'ORGANIZATION_CONFIGURATION', 'A resource type to keep the organization configurations.'),
('1fc809a0-dc0d-4cb2-82f3-58934d389236', 'CUSTOM_TEXT', 'A resource type to keep the tenant custom text preferences.'),
('3e5b1f91-72d8-4fbc-94d1-1b9a4f8c3b07', 'IMPERSONATION_CONFIGURATION', 'A resource type to keep the tenant impersonation preferences.');

ALTER TABLE IDN_OIDC_JTI ADD TENANT_ID INTEGER NOT NULL DEFAULT -1;

ALTER TABLE IDN_OIDC_JTI DROP PRIMARY KEY;

ALTER TABLE IDN_OIDC_JTI ADD PRIMARY KEY (JWT_ID, TENANT_ID);

ALTER TABLE IDN_OIDC_JTI CONVERT TO CHARACTER SET latin1;

INSERT INTO IDN_SECRET_TYPE (ID, NAME, DESCRIPTION) VALUES ('c508ca28-60c0-4493-a758-77e4173ffdb9', 'IDP_SECRET_PROPERTIES', 'Secret type to uniquely identify secrets relevant to identity providers');

ALTER TABLE IDN_SECRET
    ADD COLUMN KEY_ID VARCHAR(255) NULL,
    CONVERT TO CHARACTER SET latin1,
    MODIFY SECRET_NAME VARCHAR(1023) NOT NULL;

CREATE INDEX IDN_SECRET_TYPE_ID ON IDN_SECRET (TYPE_ID);

INSERT INTO IDN_SECRET_TYPE (ID, NAME, DESCRIPTION) VALUES ('29d0c37d-139a-4b1e-a343-7b8d26f0a2a9', 'ANDROID_ATTESTATION_CREDENTIALS', 'Secret type to uniquely identify secrets relevant to android client attestation credentials');

ALTER TABLE IDN_AUTH_USER CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_AUTH_USER_SESSION_MAPPING CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_AUTH_SESSION_APP_INFO CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_AUTH_SESSION_META_DATA CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OAUTH_CONSUMER_APPS CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OAUTH2_AUTHZ_CODE_SCOPE
    CONVERT TO CHARACTER SET latin1,
    MODIFY SCOPE VARCHAR(255);

ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN_SCOPE
    CONVERT TO CHARACTER SET latin1,
    MODIFY TOKEN_SCOPE VARCHAR(255);

ALTER TABLE IDN_OAUTH2_TOKEN_BINDING CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OAUTH2_SCOPE CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OAUTH2_SCOPE_BINDING CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OAUTH2_SCOPE_VALIDATORS CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OAUTH2_RESOURCE_SCOPE CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OAUTH1A_REQUEST_TOKEN CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OAUTH1A_ACCESS_TOKEN CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN_AUDIT CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OAUTH2_AUTHORIZATION_CODE CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OAUTH2_DEVICE_FLOW CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OAUTH2_DEVICE_FLOW_SCOPES CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_FED_AUTH_SESSION_MAPPING
	ADD COLUMN IDP_ID INTEGER NOT NULL DEFAULT 0,
	ADD CONSTRAINT IDP_ID_FOREIGN_CONSTRAINT FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE,
	DROP INDEX IDP_SESSION_ID,
	CONVERT TO CHARACTER SET latin1,
	ADD CONSTRAINT IDP_SESSION_ID UNIQUE (IDP_SESSION_ID, TENANT_ID, IDP_ID);

ALTER TABLE IDN_SCIM_GROUP CONVERT TO CHARACTER SET latin1;

ALTER TABLE SP_APP CONVERT TO CHARACTER SET latin1;

ALTER TABLE SP_METADATA CONVERT TO CHARACTER SET latin1;

ALTER TABLE SP_INBOUND_AUTH CONVERT TO CHARACTER SET latin1;

ALTER TABLE SP_AUTH_STEP CONVERT TO CHARACTER SET latin1;

ALTER TABLE SP_FEDERATED_IDP CONVERT TO CHARACTER SET latin1;

ALTER TABLE SP_CLAIM_DIALECT CONVERT TO CHARACTER SET latin1;

ALTER TABLE SP_CLAIM_MAPPING CONVERT TO CHARACTER SET latin1;

ALTER TABLE SP_ROLE_MAPPING CONVERT TO CHARACTER SET latin1;

ALTER TABLE SP_REQ_PATH_AUTHENTICATOR CONVERT TO CHARACTER SET latin1;

ALTER TABLE SP_PROVISIONING_CONNECTOR CONVERT TO CHARACTER SET latin1;

ALTER TABLE SP_AUTH_SCRIPT CONVERT TO CHARACTER SET latin1;

ALTER TABLE SP_TEMPLATE CONVERT TO CHARACTER SET latin1;

CREATE TABLE IF NOT EXISTS IDP_GROUP (
			ID INTEGER AUTO_INCREMENT NOT NULL,
			IDP_ID INTEGER NOT NULL,
			TENANT_ID INTEGER NOT NULL,
			GROUP_NAME VARCHAR(255) NOT NULL,
			UUID CHAR(36) NOT NULL,
			PRIMARY KEY (ID),
			UNIQUE (IDP_ID, GROUP_NAME),
			UNIQUE (UUID),
			FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
)DEFAULT CHARACTER SET latin1 ENGINE INNODB;

INSERT INTO IDN_SECRET_TYPE (ID, NAME, DESCRIPTION) VALUES ('433df096-62b7-4a36-b3eb-1bed9150ed35', 'IDVP_SECRET_PROPERTIES', 'Secret type to uniquely identify secrets relevant to identity verification providers');

ALTER TABLE IDN_OPENID_REMEMBER_ME CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OPENID_USER_RPS CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OPENID_ASSOCIATIONS CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_STS_STORE CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_IDENTITY_USER_DATA CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_IDENTITY_META_DATA CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_THRIFT_SESSION CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_AUTH_SESSION_STORE CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_AUTH_TEMP_SESSION_STORE CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_AUTH_WAIT_STATUS CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDP CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDP_ROLE CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDP_ROLE_MAPPING CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDP_CLAIM CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDP_CLAIM_MAPPING CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDP_AUTHENTICATOR CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDP_METADATA CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDP_AUTHENTICATOR_PROPERTY CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDP_PROVISIONING_CONFIG CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDP_PROV_CONFIG_PROPERTY CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDP_PROVISIONING_ENTITY CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDP_LOCAL_CLAIM CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_ASSOCIATED_ID CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_USER_ACCOUNT_ASSOCIATION CONVERT TO CHARACTER SET latin1;

ALTER TABLE FIDO_DEVICE_STORE CONVERT TO CHARACTER SET latin1;

ALTER TABLE FIDO2_DEVICE_STORE CONVERT TO CHARACTER SET latin1;

ALTER TABLE WF_REQUEST CONVERT TO CHARACTER SET latin1;

ALTER TABLE WF_BPS_PROFILE CONVERT TO CHARACTER SET latin1;

ALTER TABLE WF_WORKFLOW CONVERT TO CHARACTER SET latin1;

ALTER TABLE WF_WORKFLOW_ASSOCIATION CONVERT TO CHARACTER SET latin1;

ALTER TABLE WF_WORKFLOW_CONFIG_PARAM CONVERT TO CHARACTER SET latin1;

ALTER TABLE WF_REQUEST_ENTITY_RELATIONSHIP CONVERT TO CHARACTER SET latin1;

ALTER TABLE WF_WORKFLOW_REQUEST_RELATION CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_RECOVERY_DATA CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_PASSWORD_HISTORY_DATA CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_CLAIM_DIALECT CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_CLAIM CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_CLAIM_MAPPED_ATTRIBUTE CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_CLAIM_PROPERTY CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_CLAIM_MAPPING CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_SAML2_ASSERTION_STORE CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_SAML2_ARTIFACT_STORE CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OIDC_PROPERTY CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OIDC_REQ_OBJECT_REFERENCE CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OIDC_REQ_OBJECT_CLAIMS CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OIDC_REQ_OBJ_CLAIM_VALUES CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_CERTIFICATE CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OIDC_SCOPE_CLAIM_MAPPING CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_FUNCTION_LIBRARY CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OAUTH2_CIBA_AUTH_CODE CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OAUTH2_CIBA_REQUEST_SCOPES CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_CONFIG_RESOURCE CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_CONFIG_ATTRIBUTE CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_CONFIG_FILE CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_REMOTE_FETCH_CONFIG CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_REMOTE_FETCH_REVISIONS CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_USER_FUNCTIONALITY_MAPPING CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_USER_FUNCTIONALITY_PROPERTY CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_CORS_ORIGIN CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_CORS_ASSOCIATION CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OAUTH2_USER_CONSENT CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_OAUTH2_USER_CONSENTED_SCOPES CONVERT TO CHARACTER SET latin1;

ALTER TABLE IDN_SECRET_TYPE CONVERT TO CHARACTER SET latin1;

ALTER TABLE SP_SHARED_APP CONVERT TO CHARACTER SET latin1;

CREATE TABLE IF NOT EXISTS IDVP (
	ID          INTEGER NOT NULL AUTO_INCREMENT,
	UUID        CHAR(36) NOT NULL,
	TENANT_ID   INTEGER,
	IDVP_TYPE   VARCHAR(254),
	NAME        VARCHAR(254),
	DESCRIPTION VARCHAR(1024),
	IS_ENABLED  CHAR(1) NOT NULL DEFAULT '1',
	PRIMARY KEY (ID),
	UNIQUE (TENANT_ID, NAME),
    UNIQUE (UUID)
)DEFAULT CHARACTER SET latin1 ENGINE INNODB;

CREATE TABLE IF NOT EXISTS IDVP_CLAIM_MAPPING (
  ID            INTEGER NOT NULL AUTO_INCREMENT,
  IDVP_ID       INTEGER,
  TENANT_ID     INTEGER,
  CLAIM         VARCHAR(254),
  LOCAL_CLAIM   VARCHAR(254),
  PRIMARY KEY (ID),
  UNIQUE (IDVP_ID, CLAIM),
  FOREIGN KEY (IDVP_ID) REFERENCES IDVP(ID) ON DELETE CASCADE
)DEFAULT CHARACTER SET latin1 ENGINE INNODB;

CREATE TABLE IF NOT EXISTS IDVP_CONFIG (
	ID              INTEGER NOT NULL AUTO_INCREMENT,
	IDVP_ID         INTEGER,
	TENANT_ID       INTEGER,
	PROPERTY_KEY    VARCHAR(254) NOT NULL,
  	PROPERTY_VALUE  VARCHAR(1024),
  	IS_SECRET       CHAR(1) NOT NULL DEFAULT '0',
	PRIMARY KEY (ID),
	UNIQUE (IDVP_ID, PROPERTY_KEY, TENANT_ID),
	FOREIGN KEY (IDVP_ID) REFERENCES IDVP(ID) ON DELETE CASCADE
)DEFAULT CHARACTER SET latin1 ENGINE INNODB;

CREATE TABLE IF NOT EXISTS IDV_CLAIM (
	ID          INTEGER NOT NULL AUTO_INCREMENT,
	UUID        CHAR(36) NOT NULL,
	USER_ID     VARCHAR(254) NOT NULL,
	CLAIM_URI   VARCHAR(254),
	IDVP_ID     CHAR(36) NOT NULL,
	TENANT_ID   INTEGER,
	IS_VERIFIED CHAR(1) NOT NULL DEFAULT '0',
	METADATA    BLOB,
	PRIMARY KEY (ID),
	UNIQUE (CLAIM_URI, TENANT_ID, USER_ID, IDVP_ID),
    UNIQUE (UUID),
    FOREIGN KEY (IDVP_ID) REFERENCES IDVP(UUID) ON DELETE CASCADE
)DEFAULT CHARACTER SET latin1 ENGINE INNODB;

CREATE INDEX IDX_TBR_TS ON IDN_OAUTH2_ACCESS_TOKEN(TOKEN_BINDING_REF, TOKEN_STATE);

CREATE INDEX IDX_CLAIM_TI_CU ON IDN_CLAIM (TENANT_ID, CLAIM_URI);

CREATE INDEX IDX_AUTH_PROP_AUTH_ID ON IDP_AUTHENTICATOR_PROPERTY (AUTHENTICATOR_ID);

CREATE INDEX IDX_CON_FILE_RES_ID ON IDN_CONFIG_FILE (RESOURCE_ID);

CREATE TABLE IF NOT EXISTS IDN_RECOVERY_FLOW_DATA (
  RECOVERY_FLOW_ID VARCHAR(255) NOT NULL,
  CODE VARCHAR(255),
  FAILED_ATTEMPTS INTEGER DEFAULT 0 NOT NULL,
  RESEND_COUNT INTEGER DEFAULT 0 NOT NULL,
  TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(RECOVERY_FLOW_ID)
)DEFAULT CHARACTER SET latin1 ENGINE INNODB;

ALTER TABLE IDN_RECOVERY_DATA ADD RECOVERY_FLOW_ID VARCHAR(255);

ALTER TABLE IDN_RECOVERY_DATA ADD FOREIGN KEY (RECOVERY_FLOW_ID) REFERENCES IDN_RECOVERY_FLOW_DATA(RECOVERY_FLOW_ID) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS API_RESOURCE (
    ID CHAR(36) NOT NULL PRIMARY KEY,
    CURSOR_KEY INTEGER NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(255) NOT NULL,
    IDENTIFIER VARCHAR(255) NOT NULL,
    TENANT_ID INT,
    DESCRIPTION VARCHAR(255),
    TYPE VARCHAR(255) NOT NULL,
    REQUIRES_AUTHORIZATION BOOLEAN NOT NULL,
    UNIQUE KEY (CURSOR_KEY)
)DEFAULT CHARACTER SET latin1 ENGINE INNODB;

CREATE TABLE IF NOT EXISTS API_RESOURCE_PROPERTY (
    ID INTEGER AUTO_INCREMENT,
    API_ID CHAR(36) NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    VALUE VARCHAR(255) NOT NULL,
    PRIMARY KEY (ID),
    CONSTRAINT API_RESOURCE_PROPERTY_CONSTRAINT UNIQUE (API_ID, NAME),
    FOREIGN KEY (API_ID) REFERENCES API_RESOURCE(ID) ON DELETE CASCADE
)DEFAULT CHARACTER SET latin1 ENGINE INNODB;

CREATE TABLE IF NOT EXISTS SCOPE (
    ID CHAR(36) NOT NULL PRIMARY KEY,
    CURSOR_KEY INTEGER NOT NULL AUTO_INCREMENT,
    API_ID CHAR(36) NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    DISPLAY_NAME VARCHAR(255) NOT NULL,
    TENANT_ID INT,
    DESCRIPTION VARCHAR(300),
    UNIQUE KEY (CURSOR_KEY),
    FOREIGN KEY (API_ID) REFERENCES API_RESOURCE(ID) ON DELETE CASCADE
)DEFAULT CHARACTER SET latin1 ENGINE INNODB;

CREATE INDEX API_ID_NAME_INDEX ON SCOPE (API_ID, NAME);

CREATE TABLE IF NOT EXISTS AUTHORIZED_API (
    APP_ID CHAR(36) NOT NULL,
    API_ID CHAR(36) NOT NULL,
    POLICY_ID VARCHAR(255) NOT NULL,
    CONSTRAINT PK_APP_API PRIMARY KEY (APP_ID, API_ID),
    FOREIGN KEY (API_ID) REFERENCES API_RESOURCE(ID) ON DELETE CASCADE,
    FOREIGN KEY (APP_ID) REFERENCES SP_APP(UUID) ON DELETE CASCADE
)DEFAULT CHARACTER SET latin1 ENGINE INNODB;

CREATE TABLE IF NOT EXISTS AUTHORIZED_SCOPE (
    APP_ID CHAR(36) NOT NULL,
    API_ID CHAR(36) NOT NULL,
    SCOPE_ID CHAR(36) NOT NULL,
    CONSTRAINT PK_APP_API_SCOPE PRIMARY KEY (APP_ID, API_ID, SCOPE_ID),
    FOREIGN KEY (API_ID) REFERENCES API_RESOURCE(ID),
    FOREIGN KEY (SCOPE_ID) REFERENCES SCOPE(ID) ON DELETE CASCADE,
    FOREIGN KEY (APP_ID) REFERENCES SP_APP(UUID),
    FOREIGN KEY (APP_ID, API_ID) REFERENCES AUTHORIZED_API(APP_ID, API_ID) ON DELETE CASCADE,
    CONSTRAINT AUTHORIZED_SCOPE_UNIQUE UNIQUE (APP_ID, SCOPE_ID)
)DEFAULT CHARACTER SET latin1 ENGINE INNODB;

CREATE TABLE IDN_ORG_USER_INVITATION (
	ID INTEGER NOT NULL AUTO_INCREMENT,
	INVITATION_ID VARCHAR(40) NOT NULL,
	CONFIRMATION_CODE VARCHAR(40) NOT NULL,
	USER_NAME VARCHAR(255) NOT NULL,
	DOMAIN_NAME VARCHAR(255) NOT NULL,
	EMAIL VARCHAR(255) NOT NULL,
	USER_ORG_ID VARCHAR(255) NOT NULL,
	INVITED_ORG_ID VARCHAR(255) NOT NULL,
	USER_REDIRECT_URL VARCHAR(1024) NOT NULL,
	STATUS VARCHAR(10) NOT NULL,
	CREATED_AT TIMESTAMP NOT NULL,
	EXPIRED_AT TIMESTAMP NOT NULL,
	UNIQUE KEY (ID),
	PRIMARY KEY (INVITATION_ID)
)DEFAULT CHARACTER SET latin1 ENGINE INNODB;

CREATE TABLE IDN_ORG_USER_INVITE_ASSIGNMENT (
	ID INTEGER NOT NULL AUTO_INCREMENT,
	INVITATION_ID VARCHAR(40) NOT NULL,
  	ASSIGNMENT_ID VARCHAR(255) NOT NULL,
  	ASSIGNMENT_TYPE VARCHAR(255) NOT NULL,
  	UNIQUE KEY (ID),
	PRIMARY KEY (INVITATION_ID, ASSIGNMENT_ID, ASSIGNMENT_TYPE),
	FOREIGN KEY (INVITATION_ID) REFERENCES IDN_ORG_USER_INVITATION(INVITATION_ID) ON DELETE CASCADE
)DEFAULT CHARACTER SET latin1 ENGINE INNODB;

DROP PROCEDURE IF EXISTS ALTER_IDN_OAUTH2_DEVICE_FLOW;

DELIMITER $$
CREATE PROCEDURE ALTER_IDN_OAUTH2_DEVICE_FLOW()
BEGIN
     IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='IDN_OAUTH2_DEVICE_FLOW' AND COLUMN_NAME='SUBJECT_IDENTIFIER') THEN
            ALTER TABLE `IDN_OAUTH2_DEVICE_FLOW` ADD COLUMN `SUBJECT_IDENTIFIER` VARCHAR(255);
     END IF;
END $$
DELIMITER ;

CALL ALTER_IDN_OAUTH2_DEVICE_FLOW();

DROP PROCEDURE IF EXISTS ALTER_IDN_OAUTH2_DEVICE_FLOW;

CREATE TABLE IF NOT EXISTS APP_ROLE_ASSOCIATION (
    APP_ID CHAR(36) NOT NULL,
    ROLE_ID VARCHAR(255) NOT NULL,
    PRIMARY KEY (APP_ID, ROLE_ID),
    FOREIGN KEY (APP_ID) REFERENCES SP_APP(UUID) ON DELETE CASCADE
)DEFAULT CHARACTER SET latin1 ENGINE INNODB;

CREATE TABLE IF NOT EXISTS ROLE_SCOPE (
    ROLE_ID VARCHAR(255) NOT NULL,
    SCOPE_ID CHAR(36) NOT NULL,
    PRIMARY KEY (ROLE_ID, SCOPE_ID),
    FOREIGN KEY (SCOPE_ID) REFERENCES SCOPE(ID) ON DELETE CASCADE
)DEFAULT CHARACTER SET latin1 ENGINE INNODB;

ALTER TABLE IDN_SCIM_GROUP ADD COLUMN AUDIENCE_REF_ID INTEGER NOT NULL DEFAULT -1;

ALTER TABLE IDN_SCIM_GROUP DROP KEY TENANT_ID;

ALTER TABLE IDN_SCIM_GROUP ADD CONSTRAINT TENANT_ID UNIQUE KEY (TENANT_ID,ROLE_NAME,ATTR_NAME,AUDIENCE_REF_ID);

CREATE TABLE IF NOT EXISTS IDN_OAUTH_PAR (
    REQ_URI_REF VARCHAR(255) PRIMARY KEY,
    CLIENT_ID VARCHAR(255) NOT NULL,
    SCHEDULED_EXPIRY BIGINT NOT NULL,
    PARAMETERS MEDIUMTEXT
)DEFAULT CHARACTER SET latin1 ENGINE INNODB;

ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN
    ADD COLUMN AUTHORIZED_ORGANIZATION varchar(36) NOT NULL DEFAULT 'NONE',
    DROP KEY CON_APP_KEY,
    ADD CONSTRAINT CON_APP_KEY UNIQUE (CONSUMER_KEY_ID,AUTHZ_USER,TENANT_ID,USER_DOMAIN,USER_TYPE,TOKEN_SCOPE_HASH,TOKEN_STATE,TOKEN_STATE_ID,IDP_ID,TOKEN_BINDING_REF,AUTHORIZED_ORGANIZATION);

DROP PROCEDURE IF EXISTS ALTER_SP_SHARED_APP;

DELIMITER $$
CREATE PROCEDURE ALTER_SP_SHARED_APP()
BEGIN
     IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='SP_SHARED_APP' AND COLUMN_NAME='SHARE_WITH_ALL_CHILDREN' and TABLE_SCHEMA = (SELECT DATABASE())) THEN
            ALTER TABLE `SP_SHARED_APP` ADD COLUMN `SHARE_WITH_ALL_CHILDREN` BOOLEAN DEFAULT FALSE;
     END IF;
END $$
DELIMITER ;

CALL ALTER_SP_SHARED_APP();

DROP PROCEDURE IF EXISTS ALTER_SP_SHARED_APP;
