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;

DECLARE @COMMAND1 NVARCHAR(200);SELECT @COMMAND1='ALTER TABLE IDN_OIDC_JTI DROP CONSTRAINT ' + NAME + ';' FROM   sys.key_constraints WHERE  [type] = 'PK' AND [parent_object_id] = Object_id('dbo.IDN_OIDC_JTI');EXEC (@COMMAND1);

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

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 KEY_ID VARCHAR(255) NULL;

ALTER TABLE IDN_SECRET ALTER COLUMN 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');

DECLARE @COMMAND2 NVARCHAR(200);SET @COMMAND2=(SELECT 'ALTER TABLE IDN_OAUTH2_AUTHZ_CODE_SCOPE DROP CONSTRAINT ' + A.CONSTRAINT_NAME + ';' FROM (SELECT * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME='IDN_OAUTH2_AUTHZ_CODE_SCOPE' AND COLUMN_NAME='SCOPE') A INNER JOIN (SELECT * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME='IDN_OAUTH2_AUTHZ_CODE_SCOPE' AND COLUMN_NAME='CODE_ID') B ON A.CONSTRAINT_NAME=B.CONSTRAINT_NAME);EXEC (@COMMAND2);

ALTER TABLE IDN_OAUTH2_AUTHZ_CODE_SCOPE ALTER COLUMN SCOPE VARCHAR(255) NOT NULL;

ALTER TABLE IDN_OAUTH2_AUTHZ_CODE_SCOPE ALTER COLUMN CODE_ID VARCHAR(255) NOT NULL;

ALTER TABLE IDN_OAUTH2_AUTHZ_CODE_SCOPE ADD CONSTRAINT CODE_ID_SCOPE_KEY PRIMARY KEY (CODE_ID, SCOPE);

DECLARE @COMMAND3 NVARCHAR(200);SET @COMMAND3=(SELECT 'ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN_SCOPE DROP CONSTRAINT ' + A.CONSTRAINT_NAME + ';' FROM (SELECT * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME='IDN_OAUTH2_ACCESS_TOKEN_SCOPE' AND COLUMN_NAME='TOKEN_ID') A INNER JOIN (SELECT * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME='IDN_OAUTH2_ACCESS_TOKEN_SCOPE' AND COLUMN_NAME='TOKEN_SCOPE') B ON A.CONSTRAINT_NAME=B.CONSTRAINT_NAME);EXEC (@COMMAND3);

ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN_SCOPE ALTER COLUMN TOKEN_SCOPE VARCHAR(255) NOT NULL;

ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN_SCOPE ALTER COLUMN TOKEN_ID VARCHAR (255) NOT NULL;

ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN_SCOPE ADD CONSTRAINT TOKEN_ID_SCOPE_KEY PRIMARY KEY (TOKEN_ID, TOKEN_SCOPE);

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'IDN_FED_AUTH_SESSION_MAPPING' AND COLUMN_NAME = 'IDP_ID')

DECLARE @COMMAND4 NVARCHAR(200);SELECT @COMMAND4='ALTER TABLE IDN_FED_AUTH_SESSION_MAPPING DROP CONSTRAINT ' + A.CONSTRAINT_NAME + ';' FROM (SELECT * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME='IDN_FED_AUTH_SESSION_MAPPING' AND COLUMN_NAME='IDP_SESSION_ID') A INNER JOIN (SELECT * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME='IDN_FED_AUTH_SESSION_MAPPING' AND COLUMN_NAME='TENANT_ID') B ON A.CONSTRAINT_NAME=B.CONSTRAINT_NAME;EXEC (@COMMAND4);

ALTER TABLE IDN_FED_AUTH_SESSION_MAPPING ADD IDP_ID INTEGER NOT NULL DEFAULT 1;

ALTER TABLE IDN_FED_AUTH_SESSION_MAPPING ADD CONSTRAINT SESSION_TENANT_ID_CONSTRAINT UNIQUE (IDP_SESSION_ID, TENANT_ID, IDP_ID);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDP_GROUP]') AND TYPE IN (N'U'))
CREATE TABLE IDP_GROUP (
  ID INTEGER IDENTITY 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
);

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');

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDVP]') AND TYPE IN (N'U'))
CREATE TABLE IDVP (
	ID              INTEGER NOT NULL IDENTITY,
	UUID            CHAR(36) NOT NULL,
	TENANT_ID       INTEGER NOT NULL,
	IDVP_TYPE       VARCHAR(254),
	NAME            VARCHAR(254),
	DESCRIPTION     VARCHAR(1024),
	IS_ENABLED      CHAR(1) NOT NULL DEFAULT '0',
	PRIMARY KEY (ID),
	UNIQUE (TENANT_ID, NAME),
    UNIQUE (UUID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDVP_CLAIM_MAPPING]') AND TYPE IN (N'U'))
CREATE TABLE IDVP_CLAIM_MAPPING (
    ID          INTEGER NOT NULL IDENTITY,
    IDVP_ID     INTEGER NOT NULL,
    TENANT_ID   INTEGER NOT NULL,
    CLAIM       VARCHAR(254),
    LOCAL_CLAIM VARCHAR(254),
    PRIMARY KEY (ID),
    UNIQUE (IDVP_ID, CLAIM, TENANT_ID),
    FOREIGN KEY (IDVP_ID) REFERENCES IDVP(ID) ON DELETE CASCADE
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDVP_CONFIG]') AND TYPE IN (N'U'))
CREATE TABLE IDVP_CONFIG (
	ID              INTEGER NOT NULL IDENTITY,
	IDVP_ID         INTEGER NOT NULL,
	TENANT_ID       INTEGER NOT NULL,
	PROPERTY_KEY    VARCHAR(254) NOT NULL,
  	PROPERTY_VALUE  VARCHAR(1024),
  	IS_SECRET       CHAR (1) DEFAULT '0',
	PRIMARY KEY (ID),
	UNIQUE (IDVP_ID, PROPERTY_KEY, TENANT_ID),
	FOREIGN KEY (IDVP_ID) REFERENCES IDVP(ID) ON DELETE CASCADE
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDV_CLAIM]') AND TYPE IN (N'U'))
CREATE TABLE IDV_CLAIM (
	ID          INTEGER NOT NULL IDENTITY,
	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    VARBINARY(MAX),
	PRIMARY KEY (ID),
	UNIQUE (CLAIM_URI, TENANT_ID, USER_ID, IDVP_ID),
    UNIQUE (UUID),
    FOREIGN KEY (IDVP_ID) REFERENCES IDVP(UUID) ON DELETE CASCADE
);

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);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_RECOVERY_FLOW_DATA]') AND TYPE IN (N'U'))
CREATE TABLE 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 DATETIME NOT NULL,
  PRIMARY KEY(RECOVERY_FLOW_ID)
);

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;

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[API_RESOURCE]') AND TYPE IN (N'U'))
CREATE TABLE API_RESOURCE (
    ID CHAR(36) NOT NULL PRIMARY KEY,
    CURSOR_KEY INTEGER NOT NULL IDENTITY,
    NAME VARCHAR(255) NOT NULL,
    IDENTIFIER VARCHAR(255) NOT NULL,
    TENANT_ID INT,
    DESCRIPTION VARCHAR(255),
    TYPE VARCHAR(255) NOT NULL,
    REQUIRES_AUTHORIZATION BIT NOT NULL
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[API_RESOURCE_PROPERTY]') AND TYPE IN (N'U'))
CREATE TABLE API_RESOURCE_PROPERTY (
    ID INTEGER IDENTITY,
    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
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[SCOPE]') AND TYPE IN (N'U'))
CREATE TABLE SCOPE (
    ID CHAR(36) NOT NULL PRIMARY KEY,
    CURSOR_KEY INTEGER NOT NULL IDENTITY,
    API_ID CHAR(36) NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    DISPLAY_NAME VARCHAR(255) NOT NULL,
    TENANT_ID INT,
    DESCRIPTION VARCHAR(300),
    FOREIGN KEY (API_ID) REFERENCES API_RESOURCE(ID) ON DELETE CASCADE
);

CREATE INDEX API_ID_NAME_INDEX ON SCOPE (API_ID, NAME);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AUTHORIZED_API]') AND TYPE IN (N'U'))
CREATE TABLE 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),
    FOREIGN KEY (APP_ID) REFERENCES SP_APP(UUID) ON DELETE CASCADE
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AUTHORIZED_SCOPE]') AND TYPE IN (N'U'))
CREATE TABLE 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)
);

-- Trigger AUTHORIZED_API delete by API_ID on API_RESOURCE deletion by ID --
CREATE TRIGGER API_RESOURCE_DELETE_TRIGGER ON API_RESOURCE INSTEAD OF DELETE AS BEGIN DELETE FROM AUTHORIZED_API WHERE API_ID IN (SELECT ID FROM DELETED) DELETE FROM API_RESOURCE WHERE ID IN (SELECT ID FROM deleted) END;

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH_PAR]') AND TYPE IN (N'U'))
CREATE TABLE IDN_OAUTH_PAR (
    REQ_URI_REF VARCHAR(255) NOT NULL,
    CLIENT_ID VARCHAR(255) NOT NULL,
    SCHEDULED_EXPIRY BIGINT NOT NULL,
    PARAMETERS TEXT,
    PRIMARY KEY (REQ_URI_REF)
);

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'IDN_OAUTH2_DEVICE_FLOW' AND COLUMN_NAME = 'SUBJECT_IDENTIFIER')
ALTER TABLE IDN_OAUTH2_DEVICE_FLOW ADD SUBJECT_IDENTIFIER VARCHAR(255);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_ORG_USER_INVITATION]') AND TYPE IN (N'U'))
CREATE TABLE IDN_ORG_USER_INVITATION (
	ID INTEGER IDENTITY(1,1) NOT NULL,
	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 DATETIME NOT NULL,
	EXPIRED_AT DATETIME NOT NULL,
	CONSTRAINT PK_ORG_USER_INVITATION PRIMARY KEY(INVITATION_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_ORG_USER_INVITE_ASSIGNMENT]') AND TYPE IN (N'U'))
CREATE TABLE IDN_ORG_USER_INVITE_ASSIGNMENT (
	ID INTEGER IDENTITY(1,1) NOT NULL,
	INVITATION_ID VARCHAR(40) NOT NULL,
  	ASSIGNMENT_ID VARCHAR(255) NOT NULL,
  	ASSIGNMENT_TYPE VARCHAR(255) NOT NULL,
  	CONSTRAINT PK_ORG_USER_INVITE_ASSIGNMENT PRIMARY KEY(INVITATION_ID, ASSIGNMENT_ID, ASSIGNMENT_TYPE),
	CONSTRAINT FK_ORG_USER_ROLE_ORG_USER_INVITATION FOREIGN KEY(INVITATION_ID) REFERENCES IDN_ORG_USER_INVITATION(INVITATION_ID) ON DELETE CASCADE
);

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'IDN_SCIM_GROUP' AND COLUMN_NAME = 'AUDIENCE_REF_ID')

DECLARE @COMMAND5 NVARCHAR(200);SELECT @COMMAND5='ALTER TABLE IDN_SCIM_GROUP DROP CONSTRAINT ' + A.CONSTRAINT_NAME + ';' FROM (SELECT * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME='IDN_SCIM_GROUP' AND COLUMN_NAME='ROLE_NAME') A INNER JOIN (SELECT * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME='IDN_SCIM_GROUP' AND COLUMN_NAME='TENANT_ID') B ON A.CONSTRAINT_NAME=B.CONSTRAINT_NAME;EXEC (@COMMAND5);

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

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

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[APP_ROLE_ASSOCIATION]') AND TYPE IN (N'U'))
CREATE TABLE 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
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[ROLE_SCOPE]') AND TYPE IN (N'U'))
CREATE TABLE 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
);

ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN ADD AUTHORIZED_ORGANIZATION varchar(36) NOT NULL DEFAULT('NONE');
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,TOKEN_BINDING_REF,AUTHORIZED_ORGANIZATION);

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SP_SHARED_APP' AND COLUMN_NAME = 'SHARE_WITH_ALL_CHILDREN')
ALTER TABLE SP_SHARED_APP ADD SHARE_WITH_ALL_CHILDREN BIT DEFAULT 0;
