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 COLUMN TENANT_ID INTEGER NOT NULL DEFAULT -1;

DO $$ DECLARE con_name varchar(200); BEGIN SELECT 'ALTER TABLE IDN_OIDC_JTI DROP CONSTRAINT ' || tc .constraint_name || ';' INTO con_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name WHERE constraint_type = 'PRIMARY KEY' AND tc.table_name = 'idn_oidc_jti'; EXECUTE con_name; END $$;

ALTER TABLE IDN_OIDC_JTI ADD 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 COLUMN KEY_ID VARCHAR(255) NULL;

ALTER TABLE IDN_SECRET ALTER COLUMN SECRET_NAME TYPE VARCHAR(1023);

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_OAUTH2_AUTHZ_CODE_SCOPE ALTER COLUMN SCOPE TYPE VARCHAR(255);

ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN_SCOPE ALTER COLUMN TOKEN_SCOPE TYPE VARCHAR(255);


ALTER TABLE IDN_FED_AUTH_SESSION_MAPPING ADD COLUMN IDP_ID INTEGER NOT NULL DEFAULT 0;

ALTER TABLE IDN_FED_AUTH_SESSION_MAPPING ADD FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE;

DO $$ DECLARE con_name VARCHAR(200); BEGIN SELECT constraint_name INTO con_name from information_schema.constraint_column_usage where table_name = 'idn_fed_auth_session_mapping' AND column_name IN ('idp_session_id', 'tenant_id') GROUP BY constraint_name HAVING COUNT(DISTINCT column_name) = 2; IF con_name IS NOT NULL THEN EXECUTE 'ALTER TABLE IDN_FED_AUTH_SESSION_MAPPING DROP CONSTRAINT IF EXISTS '|| con_name || ';'; END IF; end; $$;

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


DROP TABLE IF EXISTS IDP_GROUP;
DROP SEQUENCE IF EXISTS IDP_GROUP_SEQ;
CREATE SEQUENCE IDP_GROUP_SEQ;
CREATE TABLE IDP_GROUP (
	ID INTEGER DEFAULT NEXTVAL('IDP_GROUP_SEQ'),
	IDP_ID INTEGER,
	TENANT_ID INTEGER,
	GROUP_NAME VARCHAR(255),
	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');

DROP TABLE IF EXISTS IDVP;
DROP SEQUENCE IF EXISTS IDVP_SEQ;
CREATE SEQUENCE IDVP_SEQ;
CREATE TABLE IDVP (
	ID INTEGER NOT NULL DEFAULT NEXTVAL('IDVP_SEQ'),
	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)
);

DROP TABLE IF EXISTS IDVP_CLAIM_MAPPING;
DROP SEQUENCE IF EXISTS IDVP_CLAIM_MAPPING_SEQ;
CREATE SEQUENCE IDVP_CLAIM_MAPPING_SEQ;
CREATE TABLE IDVP_CLAIM_MAPPING (
  ID INTEGER NOT NULL DEFAULT NEXTVAL('IDVP_CLAIM_MAPPING_SEQ'),
  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
);

DROP TABLE IF EXISTS IDVP_CONFIG;
DROP SEQUENCE IF EXISTS IDVP_CONFIG_SEQ;
CREATE SEQUENCE IDVP_CONFIG_SEQ;
CREATE TABLE IDVP_CONFIG (
	ID INTEGER NOT NULL DEFAULT NEXTVAL('IDVP_CONFIG_SEQ'),
	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
);

DROP TABLE IF EXISTS IDV_CLAIM;
DROP SEQUENCE IF EXISTS IDV_CLAIM_SEQ;
CREATE SEQUENCE IDV_CLAIM_SEQ;
CREATE TABLE IDV_CLAIM (
	ID INTEGER NOT NULL DEFAULT NEXTVAL('IDV_CLAIM_SEQ'),
	UUID CHAR(36) NOT NULL,
	USER_ID VARCHAR(255) NOT NULL,
	CLAIM_URI VARCHAR(255),
	IDVP_ID CHAR(36) NOT NULL,
	TENANT_ID INTEGER,
	IS_VERIFIED CHAR(1) NOT NULL DEFAULT '0',
	METADATA BYTEA,
	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);

DROP TABLE IF EXISTS IDN_RECOVERY_FLOW_DATA;
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 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  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;

DROP TABLE IF EXISTS API_RESOURCE;
DROP SEQUENCE IF EXISTS API_RESOURCE_SEQ;
CREATE SEQUENCE API_RESOURCE_SEQ;
CREATE TABLE API_RESOURCE (
    ID CHAR(36) NOT NULL PRIMARY KEY,
    CURSOR_KEY SERIAL,
    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
);

DROP TABLE IF EXISTS API_RESOURCE_PROPERTY;
DROP SEQUENCE IF EXISTS API_RESOURCE_PROPERTY_SEQ;
CREATE SEQUENCE API_RESOURCE_PROPERTY_SEQ;
CREATE TABLE API_RESOURCE_PROPERTY (
    ID INTEGER DEFAULT NEXTVAL('API_RESOURCE_PROPERTY_SEQ'),
    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
);

DROP TABLE IF EXISTS SCOPE;
DROP SEQUENCE IF EXISTS SCOPE_SEQ;
CREATE SEQUENCE SCOPE_SEQ;
CREATE TABLE SCOPE (
    ID CHAR(36) NOT NULL PRIMARY KEY,
    CURSOR_KEY SERIAL,
    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);

DROP TABLE IF EXISTS AUTHORIZED_API;
DROP SEQUENCE IF EXISTS AUTHORIZED_API_SEQ;
CREATE SEQUENCE AUTHORIZED_API_SEQ;
CREATE TABLE AUTHORIZED_API (
    APP_ID CHAR(36) NOT NULL,
    API_ID CHAR(36) NOT NULL,
    POLICY_ID VARCHAR(255) NOT NULL,
    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
);

DROP TABLE IF EXISTS AUTHORIZED_SCOPE;
DROP SEQUENCE IF EXISTS AUTHORIZED_SCOPE_SEQ;
CREATE SEQUENCE AUTHORIZED_SCOPE_SEQ;
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)
);

DROP TABLE IF EXISTS IDN_OAUTH_PAR;
CREATE TABLE IDN_OAUTH_PAR (
    REQ_URI_REF VARCHAR(255) PRIMARY KEY,
    CLIENT_ID VARCHAR(255) NOT NULL,
    SCHEDULED_EXPIRY BIGINT NOT NULL,
    PARAMETERS TEXT
);

ALTER TABLE IF EXISTS IDN_OAUTH2_DEVICE_FLOW ADD COLUMN IF NOT EXISTS SUBJECT_IDENTIFIER VARCHAR(255);

DROP TABLE IF EXISTS IDN_ORG_USER_INVITATION;
DROP SEQUENCE IF EXISTS IDN_ORG_USER_INVITATION_SEQ;
CREATE SEQUENCE IDN_ORG_USER_INVITATION_SEQ;
CREATE TABLE IDN_ORG_USER_INVITATION (
	ID INTEGER NOT NULL DEFAULT NEXTVAL('IDN_ORG_USER_INVITATION_SEQ'),
	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,
	PRIMARY KEY (INVITATION_ID)
);

DROP TABLE IF EXISTS IDN_ORG_USER_INVITE_ASSIGNMENT;
DROP SEQUENCE IF EXISTS IDN_ORG_USER_INVITE_ASSIGNMENT_SEQ;
CREATE SEQUENCE IDN_ORG_USER_INVITE_ASSIGNMENT_SEQ;
CREATE TABLE IDN_ORG_USER_INVITE_ASSIGNMENT (
	ID INTEGER NOT NULL DEFAULT NEXTVAL('IDN_ORG_USER_INVITE_ASSIGNMENT_SEQ'),
	INVITATION_ID VARCHAR(40) NOT NULL,
  	ASSIGNMENT_ID VARCHAR(255) NOT NULL,
  	ASSIGNMENT_TYPE VARCHAR(255) NOT NULL,
  	PRIMARY KEY (INVITATION_ID, ASSIGNMENT_ID, ASSIGNMENT_TYPE),
	FOREIGN KEY (INVITATION_ID) REFERENCES IDN_ORG_USER_INVITATION(INVITATION_ID) ON DELETE CASCADE
);

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

DO $$ DECLARE con_name VARCHAR(200); BEGIN SELECT constraint_name INTO con_name from information_schema.constraint_column_usage where table_name = 'idn_scim_group' AND column_name IN ('tenant_id', 'role_name', 'attr_name') GROUP BY constraint_name HAVING COUNT(DISTINCT column_name) = 3; IF con_name IS NOT NULL THEN EXECUTE 'ALTER TABLE IDN_SCIM_GROUP DROP CONSTRAINT IF EXISTS '|| con_name || ';'; END IF; end; $$;

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

DROP TABLE IF EXISTS APP_ROLE_ASSOCIATION;
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
);

DROP TABLE IF EXISTS ROLE_SCOPE;
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 COLUMN AUTHORIZED_ORGANIZATION varchar(36) NOT NULL DEFAULT 'NONE',
    DROP CONSTRAINT 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);

ALTER TABLE IF EXISTS SP_SHARED_APP ADD COLUMN IF NOT EXISTS SHARE_WITH_ALL_CHILDREN BOOLEAN DEFAULT FALSE;

