ALTER TABLE IDN_SAML2_ASSERTION_STORE ADD ASSERTION VARBINARY(MAX);

ALTER TABLE IDN_OAUTH_CONSUMER_APPS ALTER COLUMN CALLBACK_URL VARCHAR(2048);

ALTER TABLE IDN_OAUTH1A_REQUEST_TOKEN ALTER COLUMN CALLBACK_URL VARCHAR(2048);

ALTER TABLE IDN_OAUTH2_AUTHORIZATION_CODE ALTER COLUMN CALLBACK_URL VARCHAR(2048);

IF NOT EXISTS (SELECT * FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH2_AUTHORIZATION_CODE]') AND name = 'IDP_ID') BEGIN ALTER TABLE IDN_OAUTH2_AUTHORIZATION_CODE ADD IDP_ID INTEGER NOT NULL CONSTRAINT IDP_ID_DEFAULT DEFAULT (-1); ALTER TABLE IDN_OAUTH2_AUTHORIZATION_CODE DROP IDP_ID_DEFAULT; END IF NOT EXISTS (SELECT * FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH2_ACCESS_TOKEN]') AND name = 'IDP_ID') BEGIN ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN ADD IDP_ID INTEGER NOT NULL CONSTRAINT IDP_ID_DEFAULT DEFAULT (-1); ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN DROP IDP_ID_DEFAULT; END IF NOT EXISTS (SELECT * FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH2_ACCESS_TOKEN_AUDIT]') AND name = 'IDP_ID') BEGIN ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN_AUDIT ADD IDP_ID INTEGER NOT NULL CONSTRAINT IDP_ID_DEFAULT DEFAULT (-1); ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN_AUDIT DROP IDP_ID_DEFAULT; END

ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN DROP CONSTRAINT CON_APP_KEY;

ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN 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);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_AUTH_USER]') AND TYPE IN (N'U'))
CREATE TABLE IDN_AUTH_USER (
	USER_ID VARCHAR(255) NOT NULL,
	USER_NAME VARCHAR(255) NOT NULL,
	TENANT_ID INTEGER NOT NULL,
	DOMAIN_NAME VARCHAR(255) NOT NULL,
	IDP_ID INTEGER NOT NULL,
	PRIMARY KEY (USER_ID),
	CONSTRAINT USER_STORE_CONSTRAINT UNIQUE (USER_NAME, TENANT_ID, DOMAIN_NAME, IDP_ID));

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_AUTH_USER_SESSION_MAPPING]') AND TYPE IN (N'U'))
CREATE TABLE IDN_AUTH_USER_SESSION_MAPPING (
	USER_ID VARCHAR(255) NOT NULL,
	SESSION_ID VARCHAR(255) NOT NULL,
	CONSTRAINT USER_SESSION_STORE_CONSTRAINT UNIQUE (USER_ID, SESSION_ID));

IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_USER_ID' and object_id = OBJECT_ID('IDN_AUTH_USER_SESSION_MAPPING'))
CREATE INDEX IDX_USER_ID ON IDN_AUTH_USER_SESSION_MAPPING (USER_ID);

IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_SESSION_ID' and object_id = OBJECT_ID('IDN_AUTH_USER_SESSION_MAPPING'))
CREATE INDEX IDX_SESSION_ID ON IDN_AUTH_USER_SESSION_MAPPING (SESSION_ID);

IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_OCA_UM_TID_UD_APN' and object_id = OBJECT_ID('IDN_OAUTH_CONSUMER_APPS'))
CREATE INDEX IDX_OCA_UM_TID_UD_APN ON IDN_OAUTH_CONSUMER_APPS(USERNAME,TENANT_ID,USER_DOMAIN, APP_NAME);

IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_SPI_APP' and object_id = OBJECT_ID('SP_INBOUND_AUTH'))
CREATE INDEX IDX_SPI_APP ON SP_INBOUND_AUTH(APP_ID);

IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_IOP_TID_CK' and object_id = OBJECT_ID('IDN_OIDC_PROPERTY'))
CREATE INDEX IDX_IOP_TID_CK ON IDN_OIDC_PROPERTY(TENANT_ID,CONSUMER_KEY);

-- IDN_OAUTH2_ACCESS_TOKEN --
IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_AT_AU_TID_UD_TS_CKID' and object_id = OBJECT_ID('IDN_OAUTH2_ACCESS_TOKEN'))
CREATE INDEX IDX_AT_AU_TID_UD_TS_CKID ON IDN_OAUTH2_ACCESS_TOKEN(AUTHZ_USER, TENANT_ID, USER_DOMAIN, TOKEN_STATE, CONSUMER_KEY_ID);

IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_AT_AT' and object_id = OBJECT_ID('IDN_OAUTH2_ACCESS_TOKEN'))
CREATE INDEX IDX_AT_AT ON IDN_OAUTH2_ACCESS_TOKEN(ACCESS_TOKEN);

IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_AT_AU_CKID_TS_UT' and object_id = OBJECT_ID('IDN_OAUTH2_ACCESS_TOKEN'))
CREATE INDEX IDX_AT_AU_CKID_TS_UT ON IDN_OAUTH2_ACCESS_TOKEN(AUTHZ_USER, CONSUMER_KEY_ID, TOKEN_STATE, USER_TYPE);

IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_AT_RTH' and object_id = OBJECT_ID('IDN_OAUTH2_ACCESS_TOKEN'))
CREATE INDEX IDX_AT_RTH ON IDN_OAUTH2_ACCESS_TOKEN(REFRESH_TOKEN_HASH);

IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_AT_RT' and object_id = OBJECT_ID('IDN_OAUTH2_ACCESS_TOKEN'))
CREATE INDEX IDX_AT_RT ON IDN_OAUTH2_ACCESS_TOKEN(REFRESH_TOKEN);

-- IDN_OAUTH2_AUTHORIZATION_CODE --
IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_AC_CKID' and object_id = OBJECT_ID('IDN_OAUTH2_AUTHORIZATION_CODE'))
CREATE INDEX IDX_AC_CKID ON IDN_OAUTH2_AUTHORIZATION_CODE(CONSUMER_KEY_ID);

IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_AC_TID' and object_id = OBJECT_ID('IDN_OAUTH2_AUTHORIZATION_CODE'))
CREATE INDEX IDX_AC_TID ON IDN_OAUTH2_AUTHORIZATION_CODE(TOKEN_ID);

IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_AC_AC_CKID' and object_id = OBJECT_ID('IDN_OAUTH2_AUTHORIZATION_CODE'))
CREATE INDEX IDX_AC_AC_CKID ON IDN_OAUTH2_AUTHORIZATION_CODE(AUTHORIZATION_CODE, CONSUMER_KEY_ID);

-- IDN_OAUTH2_SCOPE --
IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_SC_TID' and object_id = OBJECT_ID('IDN_OAUTH2_SCOPE'))
CREATE INDEX IDX_SC_TID ON IDN_OAUTH2_SCOPE(TENANT_ID);

IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_SC_N_TID' and object_id = OBJECT_ID('IDN_OAUTH2_SCOPE'))
CREATE INDEX IDX_SC_N_TID ON IDN_OAUTH2_SCOPE(NAME, TENANT_ID);

-- IDN_OAUTH2_SCOPE_BINDING --
IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_SB_SCPID' and object_id = OBJECT_ID('IDN_OAUTH2_SCOPE_BINDING'))
CREATE INDEX IDX_SB_SCPID ON IDN_OAUTH2_SCOPE_BINDING(SCOPE_ID);

-- IDN_OIDC_REQ_OBJECT_REFERENCE --
IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_OROR_TID' and object_id = OBJECT_ID('IDN_OIDC_REQ_OBJECT_REFERENCE'))
CREATE INDEX IDX_OROR_TID ON IDN_OIDC_REQ_OBJECT_REFERENCE(TOKEN_ID);

-- IDN_OAUTH2_ACCESS_TOKEN_SCOPE --
IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_ATS_TID' and object_id = OBJECT_ID('IDN_OAUTH2_ACCESS_TOKEN_SCOPE'))
CREATE INDEX IDX_ATS_TID ON IDN_OAUTH2_ACCESS_TOKEN_SCOPE(TOKEN_ID);

-- IDN_AUTH_USER --
IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_AUTH_USER_UN_TID_DN' and object_id = OBJECT_ID('IDN_AUTH_USER'))
CREATE INDEX IDX_AUTH_USER_UN_TID_DN ON IDN_AUTH_USER (USER_NAME, TENANT_ID, DOMAIN_NAME);

IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_AUTH_USER_DN_TOD' and object_id = OBJECT_ID('IDN_AUTH_USER'))
CREATE INDEX IDX_AUTH_USER_DN_TOD ON IDN_AUTH_USER (DOMAIN_NAME, TENANT_ID);
