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
/

ALTER TABLE IDN_OIDC_JTI DROP PRIMARY KEY
/

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 ALTER COLUMN SECRET_NAME SET DATA TYPE VARCHAR(1023)
/
ALTER TABLE IDN_SECRET ADD COLUMN KEY_ID VARCHAR(255) 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_OAUTH2_AUTHZ_CODE_SCOPE ALTER COLUMN SCOPE SET DATA TYPE VARCHAR(255)
/
ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN_SCOPE ALTER COLUMN TOKEN_SCOPE SET DATA 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 CONSTRAINT IDP_ID_CONSTRAINT FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
/
BEGIN
    DECLARE const_name0 VARCHAR(128);
    DECLARE STMT VARCHAR(200);
    SELECT  k.CONSTNAME
    INTO const_name0
    FROM SYSCAT.KEYCOLUSE k JOIN SYSCAT.TABCONST t ON k.CONSTNAME = t.CONSTNAME AND k.TABSCHEMA = t.TABSCHEMA
    WHERE t.TABNAME = 'IDN_FED_AUTH_SESSION_MAPPING' AND t.TYPE='U' AND k.COLNAME = 'IDP_SESSION_ID';
    
    IF const_name0 IS NOT NULL THEN
        SET STMT = 'ALTER TABLE IDN_FED_AUTH_SESSION_MAPPING DROP UNIQUE ' || const_name0;
        EXECUTE IMMEDIATE STMT;
    END IF;
END
/
ALTER TABLE IDN_FED_AUTH_SESSION_MAPPING ADD CONSTRAINT IDN_FED_AUTH_SESSION_MAPPING_U1 UNIQUE(IDP_SESSION_ID, TENANT_ID, IDP_ID)
/
CREATE TABLE IDP_GROUP (
	ID INTEGER 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)
/
CREATE SEQUENCE IDP_GROUP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDP_GROUP_TRIG NO CASCADE
    BEFORE INSERT
    ON IDP_GROUP
    REFERENCING NEW AS NEW
    FOR EACH ROW MODE DB2SQL
        BEGIN ATOMIC
            SET (NEW.ID) = (NEXTVAL FOR IDP_GROUP_SEQ);
        END
/
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')
/
CREATE TABLE IDVP (
    ID          INTEGER NOT NULL,
    UUID        CHAR(36) NOT NULL,
    TENANT_ID   INTEGER NOT NULL,
    IDVP_TYPE   VARCHAR(254),
    NAME        VARCHAR(254) NOT NULL,
    DESCRIPTION VARCHAR(254),
    IS_ENABLED  CHAR(1) DEFAULT '1',
    PRIMARY KEY (ID),
    UNIQUE (TENANT_ID, NAME),
    UNIQUE (UUID)
)
/
CREATE SEQUENCE IDVP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDVP_TRIG NO CASCADE
            BEFORE INSERT
            ON IDVP
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
            BEGIN ATOMIC
                SET (NEW.ID) = (NEXTVAL FOR IDVP_SEQ);
            END
/
CREATE TABLE IDVP_CLAIM_MAPPING (
  ID            INTEGER NOT NULL,
  IDVP_ID       INTEGER NOT NULL,
  TENANT_ID     INTEGER NOT NULL,
  CLAIM         VARCHAR(254) NOT NULL,
  LOCAL_CLAIM   VARCHAR(254),
  PRIMARY KEY (ID),
  UNIQUE (IDVP_ID, CLAIM),
  FOREIGN KEY (IDVP_ID) REFERENCES IDVP(ID) ON DELETE CASCADE
)
/
CREATE SEQUENCE IDVP_CLAIM_MAPPING_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDVP_CLAIM_MAPPING_TRIG NO CASCADE
            BEFORE INSERT
            ON IDVP_CLAIM_MAPPING
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
            BEGIN ATOMIC
                SET (NEW.ID) = (NEXTVAL FOR IDVP_CLAIM_MAPPING_SEQ);
            END
/
CREATE TABLE IDVP_CONFIG (
    ID              INTEGER NOT NULL,
    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
)
/
CREATE SEQUENCE IDVP_CONFIG_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDVP_CONFIG_TRIG NO CASCADE
            BEFORE INSERT
            ON IDVP_CONFIG
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
            BEGIN ATOMIC
                SET (NEW.ID) = (NEXTVAL FOR IDVP_CONFIG_SEQ);
            END
/
CREATE TABLE IDV_CLAIM (
    ID          INTEGER NOT NULL,
    UUID        CHAR(36) NOT NULL,
    USER_ID     VARCHAR(255) NOT NULL,
    CLAIM_URI   VARCHAR(255) NOT NULL,
    IDVP_ID     CHAR(36) NOT NULL,
    TENANT_ID   INTEGER NOT NULL,
    IS_VERIFIED CHAR(1) 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
)
/
CREATE SEQUENCE IDV_CLAIM_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDV_CLAIM_TRIG NO CASCADE
            BEFORE INSERT
            ON IDV_CLAIM
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
            BEGIN ATOMIC
                SET (NEW.ID) = (NEXTVAL FOR IDV_CLAIM_SEQ);
            END
/
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 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
/
CREATE TABLE API_RESOURCE (
	ID CHAR(36) PRIMARY KEY NOT NULL,
    CURSOR_KEY INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    IDENTIFIER VARCHAR(255) NOT NULL,
    TENANT_ID INT,
    DESCRIPTION VARCHAR(255),
    TYPE VARCHAR(255) NOT NULL,
    REQUIRES_AUTHORIZATION CHAR(1) NOT NULL
)
/
CREATE SEQUENCE API_RESOURCE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER API_RESOURCE_TRIG NO CASCADE
            BEFORE INSERT
            ON API_RESOURCE
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
            BEGIN ATOMIC
                SET (NEW.CURSOR_KEY) = (NEXTVAL FOR API_RESOURCE_SEQ);
            END
/
CREATE TABLE API_RESOURCE_PROPERTY (
    ID INTEGER NOT NULL,
    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
)
/
CREATE SEQUENCE API_RESOURCE_PROPERTY_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER API_RESOURCE_PROPERTY_TRIG NO CASCADE
            BEFORE INSERT
            ON API_RESOURCE_PROPERTY
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR API_RESOURCE_PROPERTY_SEQ);
                END
/
CREATE TABLE SCOPE (
    ID CHAR(36) NOT NULL PRIMARY KEY,
    CURSOR_KEY INTEGER NOT NULL,
    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 SEQUENCE SCOPE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER SCOPE_TRIG NO CASCADE
            BEFORE INSERT
            ON SCOPE
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
            BEGIN ATOMIC
                SET (NEW.CURSOR_KEY) = (NEXTVAL FOR SCOPE_SEQ);
            END
/
CREATE INDEX API_ID_NAME_INDEX ON SCOPE (API_ID, NAME)
/
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
)
/
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),
    CONSTRAINT AUTHORIZED_SCOPE_UNIQUE UNIQUE (APP_ID, SCOPE_ID)
)
/
CREATE TRIGGER BEFORE_AUTHORIZED_API_DELETE
            BEFORE DELETE
            ON AUTHORIZED_API
            REFERENCING OLD AS OLD
            FOR EACH ROW MODE DB2SQL
            BEGIN
            DELETE FROM AUTHORIZED_SCOPE WHERE APP_ID = OLD.APP_ID AND API_ID = OLD.API_ID;
            END
/

CREATE TRIGGER BEFORE_SP_APP_DELETE
            BEFORE DELETE ON SP_APP
            REFERENCING OLD AS OLD
            FOR EACH ROW MODE DB2SQL
            BEGIN
            DELETE FROM AUTHORIZED_SCOPE WHERE APP_ID = OLD.UUID;
            END
/
CREATE TABLE IDN_OAUTH_PAR (
    REQ_URI_REF VARCHAR(255) PRIMARY KEY NOT NULL,
    CLIENT_ID VARCHAR(255) NOT NULL,
    SCHEDULED_EXPIRY BIGINT NOT NULL,
    PARAMETERS CLOB(1M)
)
/

CREATE OR REPLACE PROCEDURE ALTER_IDN_OAUTH2_DEVICE_FLOW
BEGIN ATOMIC
    IF EXISTS(SELECT * FROM SYSCAT.TABLES WHERE TABNAME='IDN_OAUTH2_DEVICE_FLOW')
	THEN
	    IF NOT EXISTS(SELECT * FROM SYSCAT.COLUMNS WHERE TABNAME='IDN_OAUTH2_DEVICE_FLOW' AND COLNAME='SUBJECT_IDENTIFIER')
	    THEN
	        EXECUTE IMMEDIATE 'ALTER TABLE IDN_OAUTH2_DEVICE_FLOW ADD COLUMN SUBJECT_IDENTIFIER VARCHAR(255)';
	    END IF;
    END IF;
END
/
CALL ALTER_IDN_OAUTH2_DEVICE_FLOW
/
DROP PROCEDURE ALTER_IDN_OAUTH2_DEVICE_FLOW
/
CREATE TABLE IDN_ORG_USER_INVITATION (
	ID INTEGER 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 TIMESTAMP NOT NULL,
	EXPIRED_AT TIMESTAMP NOT NULL,
	PRIMARY KEY (INVITATION_ID)
)
/
CREATE SEQUENCE IDN_ORG_USER_INVITATION_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_ORG_USER_INVITATION_TRIG NO CASCADE
            BEFORE INSERT
            ON IDN_ORG_USER_INVITATION
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
            BEGIN ATOMIC
                SET (NEW.ID) = (NEXTVAL FOR IDN_ORG_USER_INVITATION_SEQ);
            END
/
CREATE TABLE IDN_ORG_USER_INVITE_ASSIGNMENT (
	ID INTEGER NOT NULL,
	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
)
/
CREATE SEQUENCE IDN_ORG_USER_INVITE_ASSIGNMENT_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_ORG_USER_INVITE_ASSIGNMENT_TRIG
            BEFORE INSERT
            ON IDN_ORG_USER_INVITE_ASSIGNMENT
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
            BEGIN ATOMIC
                SET (NEW.ID) = (NEXTVAL FOR IDN_ORG_USER_INVITE_ASSIGNMENT_SEQ);
            END
/

ALTER TABLE IDN_SCIM_GROUP ADD COLUMN AUDIENCE_REF_ID INTEGER DEFAULT -1 NOT NULL
/
BEGIN
    DECLARE const_name0 VARCHAR(128);
    DECLARE STMT VARCHAR(200);
    SELECT  k.CONSTNAME
    INTO const_name0
    FROM SYSCAT.KEYCOLUSE k JOIN SYSCAT.TABCONST t ON k.CONSTNAME = t.CONSTNAME AND k.TABSCHEMA = t.TABSCHEMA
    WHERE t.TABNAME = 'IDN_SCIM_GROUP' AND t.TYPE='U' AND k.COLNAME = 'ROLE_NAME';
    
    IF const_name0 IS NOT NULL THEN
        SET STMT = 'ALTER TABLE IDN_SCIM_GROUP DROP UNIQUE ' || const_name0;
        EXECUTE IMMEDIATE STMT;
    END IF;
END
/
ALTER TABLE IDN_SCIM_GROUP ADD CONSTRAINT IDN_SCIM_GROUP_U1 UNIQUE(TENANT_ID, ROLE_NAME, ATTR_NAME, AUDIENCE_REF_ID)
/
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
)/

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

CREATE OR REPLACE PROCEDURE ALTER_SP_SHARED_APP
BEGIN ATOMIC
    IF EXISTS(SELECT * FROM SYSCAT.TABLES WHERE TABNAME='SP_SHARED_APP')
	THEN
	    IF NOT EXISTS(SELECT * FROM SYSCAT.COLUMNS WHERE TABNAME='SP_SHARED_APP' AND COLNAME='SHARE_WITH_ALL_CHILDREN')
	    THEN
	        EXECUTE IMMEDIATE 'ALTER TABLE SP_SHARED_APP ADD COLUMN SHARE_WITH_ALL_CHILDREN BOOLEAN DEFAULT FALSE';
	    END IF;
    END IF;
END
/
CALL ALTER_SP_SHARED_APP
/
DROP PROCEDURE ALTER_SP_SHARED_APP
/
