INSERT ALL 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')
INTO IDN_CONFIG_TYPE (ID, NAME, DESCRIPTION) VALUES
('f4e83b8a-d1c4-a0d6-03a7-d48e268c60c5', 'PK_JWT_CONFIGURATION', 'A resource type to keep the tenant private key jwt configuration.')
INTO IDN_CONFIG_TYPE (ID, NAME, DESCRIPTION) VALUES
('9ec61e9d-f0e6-4952-9a09-ab842aeb2db2', 'ATTRIBUTE_CONFIGURATION', 'A resource type to store attribute related configurations.')
INTO IDN_CONFIG_TYPE (ID, NAME, DESCRIPTION) VALUES
('132b0ee6-43e0-462d-8b4b-15b68109d71d', 'ORGANIZATION_CONFIGURATION', 'A resource type to keep the organization configurations.')
INTO IDN_CONFIG_TYPE (ID, NAME, DESCRIPTION) VALUES
('1fc809a0-dc0d-4cb2-82f3-58934d389236', 'CUSTOM_TEXT', 'A resource type to keep the tenant custom text preferences.')
INTO IDN_CONFIG_TYPE (ID, NAME, DESCRIPTION) VALUES
('3e5b1f91-72d8-4fbc-94d1-1b9a4f8c3b07', 'IMPERSONATION_CONFIGURATION', 'A resource type to keep the tenant impersonation preferences.')
SELECT 1 FROM dual
/

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

ALTER TABLE IDN_OIDC_JTI DROP PRIMARY KEY
/

ALTER TABLE IDN_OIDC_JTI  ADD CONSTRAINT PK_IDN_OIDC_JTI 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 MODIFY SECRET_NAME VARCHAR2(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 MODIFY "SCOPE" VARCHAR2(255)
/
ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN_SCOPE MODIFY TOKEN_SCOPE VARCHAR2(255)
/

ALTER TABLE IDN_FED_AUTH_SESSION_MAPPING ADD COLUMN IDP_ID INTEGER DEFAULT 0
/
ALTER TABLE IDN_FED_AUTH_SESSION_MAPPING ADD CONSTRAINT IDP_ID_CONSTRAINT FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
/
ALTER TABLE IDN_FED_AUTH_SESSION_MAPPING DROP UNIQUE (IDP_SESSION_ID, TENANT_ID)
/
ALTER TABLE IDN_FED_AUTH_SESSION_MAPPING ADD 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 CACHE 20
/
CREATE OR REPLACE TRIGGER IDP_GROUP_TRIG
    BEFORE INSERT
    ON IDP_GROUP
    REFERENCING NEW AS NEW
    FOR EACH ROW
        BEGIN
            SELECT IDP_GROUP_SEQ.nextval INTO :NEW.ID FROM dual;
        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,
    IDVP_TYPE   VARCHAR(254),
    NAME        VARCHAR(254),
    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 CACHE 20
/
CREATE OR REPLACE TRIGGER IDVP_TRIG
            BEFORE INSERT
            ON IDVP
            REFERENCING NEW AS NEW
            FOR EACH ROW
            BEGIN
                SELECT IDVP_SEQ.nextval INTO :NEW.ID FROM dual;
            END;
/
CREATE TABLE IDVP_CLAIM_MAPPING (
  ID            INTEGER NOT NULL,
  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
)
/
CREATE SEQUENCE IDVP_CLAIM_MAPPING_SEQ START WITH 1 INCREMENT BY 1 CACHE 20
/
CREATE OR REPLACE TRIGGER IDVP_CLAIM_MAPPING_TRIG
            BEFORE INSERT
            ON IDVP_CLAIM_MAPPING
            REFERENCING NEW AS NEW
            FOR EACH ROW
            BEGIN
                SELECT IDVP_CLAIM_MAPPING_SEQ.nextval INTO :NEW.ID FROM dual;
            END;
/
CREATE TABLE IDVP_CONFIG (
    ID              INTEGER NOT NULL,
    IDVP_ID         INTEGER,
    TENANT_ID       INTEGER,
    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 CACHE 20
/
CREATE OR REPLACE TRIGGER IDVP_CONFIG_TRIG
            BEFORE INSERT
            ON IDVP_CONFIG
            REFERENCING NEW AS NEW
            FOR EACH ROW
            BEGIN
                SELECT IDVP_CONFIG_SEQ.nextval INTO :NEW.ID FROM dual;
            END;
/
CREATE TABLE IDV_CLAIM (
    ID          INTEGER NOT NULL,
    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) 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 CACHE 20
/
CREATE OR REPLACE TRIGGER IDV_CLAIM_TRIG
            BEFORE INSERT
            ON IDV_CLAIM
            REFERENCING NEW AS NEW
            FOR EACH ROW
            BEGIN
                SELECT IDV_CLAIM_SEQ.nextval INTO :NEW.ID FROM dual;
            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 VARCHAR2(255) NOT NULL,
    CODE VARCHAR2(255),
    FAILED_ATTEMPTS INTEGER DEFAULT 0 NOT NULL,
    RESEND_COUNT INTEGER DEFAULT 0 NOT NULL,
    TIME_CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY(RECOVERY_FLOW_ID)
)
/
ALTER TABLE IDN_RECOVERY_DATA ADD RECOVERY_FLOW_ID VARCHAR2(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) NOT NULL PRIMARY KEY,
    CURSOR_KEY INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    IDENTIFIER VARCHAR(255) NOT NULL,
    TENANT_ID INT NOT NULL,
    DESCRIPTION VARCHAR(255),
    TYPE VARCHAR(255) NOT NULL,
    REQUIRES_AUTHORIZATION BOOLEAN NOT NULL,
    CONSTRAINT IDENTIFIER_UNIQUE UNIQUE (IDENTIFIER, TENANT_ID)
)
/
CREATE SEQUENCE API_RESOURCE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER API_RESOURCE_SEQ
            BEFORE INSERT
            ON API_RESOURCE
            REFERENCING NEW AS NEW
            FOR EACH ROW
            BEGIN
                SELECT API_RESOURCE.nextval INTO :NEW.CURSOR_KEY FROM dual;
            END;
/
CREATE TABLE API_RESOURCE_PROPERTY (
    ID INTEGER,
    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 CACHE 20
/
CREATE OR REPLACE TRIGGER API_RESOURCE_PROPERTY_TRIG
            BEFORE INSERT
            ON API_RESOURCE_PROPERTY
            REFERENCING NEW AS NEW
            FOR EACH ROW
               BEGIN
                   SELECT API_RESOURCE_PROPERTY_SEQ.nextval INTO :NEW.ID FROM dual;
               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 NOT NULL,
    DESCRIPTION VARCHAR(300),
    FOREIGN KEY (API_ID) REFERENCES API_RESOURCE(ID) ON DELETE CASCADE,
    CONSTRAINT SCOPE_UNIQUE UNIQUE (NAME, TENANT_ID)
)
/
CREATE SEQUENCE SCOPE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER SCOPE_SEQ
            BEFORE INSERT
            ON SCOPE
            REFERENCING NEW AS NEW
            FOR EACH ROW
            BEGIN
                SELECT SCOPE.nextval INTO :NEW.CURSOR_KEY FROM dual;
            END;
/
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) ON DELETE CASCADE,
    FOREIGN KEY (APP_ID) REFERENCES SP_APP(UUID) ON DELETE CASCADE
)
/
CREATE TABLE IF NOT EXISTS AUTHORIZED_SCOPE (
    APP_ID CHAR(36) NOT NULL,
    API_ID CHAR(36) NOT NULL,
    SCOPE_NAME VARCHAR(255) NOT NULL,
    TENANT_ID INT NOT NULL,
    CONSTRAINT PK_APP_API_SCOPE PRIMARY KEY (APP_ID, API_ID, SCOPE_NAME),
    FOREIGN KEY (API_ID) REFERENCES API_RESOURCE(ID),
    FOREIGN KEY (SCOPE_NAME, TENANT_ID) REFERENCES SCOPE(NAME, TENANT_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_NAME)
)
/
CREATE TABLE IDN_OAUTH_PAR (
    REQ_URI_REF VARCHAR2(255) PRIMARY KEY,
    CLIENT_ID VARCHAR2(255) NOT NULL,
    SCHEDULED_EXPIRY NUMBER(22,0) NOT NULL,
    PARAMETERS CLOB
)
/

BEGIN
    EXECUTE IMMEDIATE 'DROP PROCEDURE ALTER_IDN_OAUTH2_DEVICE_FLOW';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -4043 THEN
            RAISE;
        END IF;
END;

CREATE OR REPLACE PROCEDURE ALTER_IDN_OAUTH2_DEVICE_FLOW AS
  v_column_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO v_column_count
  FROM ALL_TAB_COLUMNS
  WHERE TABLE_NAME = 'IDN_OAUTH2_DEVICE_FLOW'
    AND COLUMN_NAME = 'SUBJECT_IDENTIFIER';

  IF v_column_count = 0 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE IDN_OAUTH2_DEVICE_FLOW ADD (SUBJECT_IDENTIFIER VARCHAR2(255))';
  END IF;
END ALTER_IDN_OAUTH2_DEVICE_FLOW;
/

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 OR REPLACE TRIGGER IDN_ORG_USER_INVITATION_TRIG
            BEFORE INSERT
            ON IDN_ORG_USER_INVITATION
            REFERENCING NEW AS NEW
            FOR EACH ROW
            BEGIN
                SELECT IDN_ORG_USER_INVITATION_SEQ.nextval INTO :NEW.ID FROM dual;
            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 OR REPLACE TRIGGER IDN_ORG_USER_INVITE_ASSIGNMENT_TRIG
            BEFORE INSERT
            ON IDN_ORG_USER_INVITE_ASSIGNMENT
            REFERENCING NEW AS NEW
            FOR EACH ROW
            BEGIN
                SELECT IDN_ORG_USER_INVITE_ASSIGNMENT_SEQ.nextval INTO :NEW.ID FROM dual;
            END;
/
ALTER TABLE IDN_SCIM_GROUP ADD AUDIENCE_REF_ID INTEGER DEFAULT -1 NOT NULL
/
ALTER TABLE IDN_SCIM_GROUP DROP UNIQUE (TENANT_ID, ROLE_NAME, ATTR_NAME)
/
ALTER TABLE IDN_SCIM_GROUP ADD 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_NAME VARCHAR(255) NOT NULL,
  TENANT_ID INT NOT NULL,
  PRIMARY KEY (ROLE_ID, SCOPE_NAME, TENANT_ID),
  FOREIGN KEY (SCOPE_NAME, TENANT_ID) REFERENCES SCOPE(NAME, TENANT_ID) ON DELETE CASCADE
)
/

CREATE OR REPLACE PROCEDURE ALTER_SP_SHARED_APP AS
  v_column_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO v_column_count
  FROM ALL_TAB_COLUMNS
  WHERE TABLE_NAME = 'SP_SHARED_APP'
    AND COLUMN_NAME = 'SHARE_WITH_ALL_CHILDREN';

  IF v_column_count = 0 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE SP_SHARED_APP ADD (SHARE_WITH_ALL_CHILDREN NUMBER(1,0) DEFAULT 0)';
  END IF;
END ALTER_SP_SHARED_APP;
/
CALL ALTER_SP_SHARED_APP()
/
DROP PROCEDURE ALTER_SP_SHARED_APP
/
