CREATE INDEX REG_RESC_PROP_BY_PROP_ID_TI ON REG_RESOURCE_PROPERTY(REG_TENANT_ID,REG_PROPERTY_ID)
/
CREATE INDEX INDEX_UM_TENANT_ORG_UUID ON UM_TENANT(UM_ORG_UUID)
/
CREATE INDEX INDEX_ROLE_PERMSN_TI_RN ON UM_ROLE_PERMISSION(UM_TENANT_ID,UM_ROLE_NAME)
/
CREATE TABLE IF NOT EXISTS UM_ORG_DISCOVERY (
            UM_ID INTEGER NOT NULL,
            UM_ORG_ID VARCHAR(36) NOT NULL,
            UM_ROOT_ORG_ID VARCHAR(36) NOT NULL,
            UM_DISCOVERY_TYPE VARCHAR(255) NOT NULL,
            UM_DISCOVERY_VALUE VARCHAR(255) NOT NULL,
            PRIMARY KEY (UM_ID),
            UNIQUE (UM_ROOT_ORG_ID, UM_DISCOVERY_TYPE, UM_DISCOVERY_VALUE),
            FOREIGN KEY (UM_ROOT_ORG_ID) REFERENCES UM_ORG(UM_ID) ON DELETE CASCADE,
            FOREIGN KEY (UM_ORG_ID) REFERENCES UM_ORG(UM_ID) ON DELETE CASCADE
)/

CREATE SEQUENCE UM_ORG_DISCOVERY_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER UM_ORG_DISCOVERY_TRIG NO CASCADE BEFORE INSERT ON UM_ORG_DISCOVERY
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    SET (NEW.UM_ID)
       = (NEXTVAL FOR UM_ORG_DISCOVERY_SEQUENCE);END/

ALTER TABLE UM_ORG_ROLE_USER ADD COLUMN UM_USER_RESIDENT_ORG_ID VARCHAR(36)
/

ALTER TABLE UM_ROLE ADD COLUMN UM_ROLE_UUID VARCHAR(255) NOT NULL DEFAULT -1
/
ALTER TABLE UM_ROLE ADD COLUMN UM_CREATED_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
/
ALTER TABLE UM_ROLE ADD COLUMN UM_LAST_MODIFIED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
/

CREATE TABLE IF NOT EXISTS UM_ORG_USER_ASSOCIATION (
	UM_USER_ID VARCHAR(255) NOT NULL,
	UM_ORG_ID VARCHAR(36) NOT NULL,
	UM_ASSOCIATED_USER_ID VARCHAR(255) NOT NULL,
	UM_ASSOCIATED_ORG_ID VARCHAR(36) NOT NULL,
	PRIMARY KEY (UM_USER_ID, UM_ORG_ID, UM_ASSOCIATED_USER_ID, UM_ASSOCIATED_ORG_ID)
)/

CREATE TABLE UM_HYBRID_ROLE_AUDIENCE(
            UM_ID DECIMAL(31,0) NOT NULL,
            UM_AUDIENCE VARCHAR(255) NOT NULL,
            UM_AUDIENCE_ID VARCHAR(255) NOT NULL,
            UNIQUE (UM_AUDIENCE, UM_AUDIENCE_ID),
            PRIMARY KEY (UM_ID)
)
/
CREATE SEQUENCE UM_HYBRID_ROLE_AUDIENCE_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER UM_HYBRID_ROLE_AUDIENCE_TR1 NO CASCADE BEFORE INSERT ON UM_HYBRID_ROLE_AUDIENCE
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    SET (NEW.UM_ID)
       = (NEXTVAL FOR UM_HYBRID_ROLE_AUDIENCE_SEQUENCE);END/

ALTER TABLE UM_HYBRID_ROLE ADD UM_AUDIENCE_REF_ID DECIMAL(31,0) DEFAULT -1 NOT NULL
/
ALTER TABLE UM_HYBRID_ROLE ADD UM_UUID VARCHAR(36)
/
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 = 'UM_HYBRID_ROLE' AND t.TYPE='U' AND k.COLNAME = 'UM_ROLE_NAME';
    
    IF const_name0 IS NOT NULL THEN
        SET STMT = 'ALTER TABLE UM_HYBRID_ROLE DROP UNIQUE ' || const_name0;
        EXECUTE IMMEDIATE STMT;
    END IF;
END
/
ALTER TABLE UM_HYBRID_ROLE ADD CONSTRAINT UM_HYBRID_ROLE_U1 UNIQUE(UM_ROLE_NAME,UM_TENANT_ID,UM_AUDIENCE_REF_ID)
/

CREATE TABLE UM_IDP_GROUP_ROLE(
            UM_ROLE_ID INTEGER NOT NULL,
            UM_GROUP_ID VARCHAR(36) NOT NULL,
            UM_TENANT_ID INTEGER NOT NULL,
            PRIMARY KEY (UM_ROLE_ID, UM_GROUP_ID, UM_TENANT_ID),
            FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID) ON DELETE CASCADE
)/

CREATE TABLE UM_SHARED_ROLE(
            UM_ID DECIMAL(31,0) NOT NULL,
            UM_SHARED_ROLE_ID INTEGER NOT NULL,
            UM_MAIN_ROLE_ID INTEGER NOT NULL,
            UM_SHARED_ROLE_TENANT_ID INTEGER NOT NULL,
            UM_MAIN_ROLE_TENANT_ID INTEGER NOT NULL,
            PRIMARY KEY (UM_ID),
            UNIQUE (UM_SHARED_ROLE_ID, UM_MAIN_ROLE_ID, UM_MAIN_ROLE_TENANT_ID),
            FOREIGN KEY (UM_SHARED_ROLE_ID, UM_SHARED_ROLE_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
            FOREIGN KEY (UM_MAIN_ROLE_ID, UM_MAIN_ROLE_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID) ON DELETE CASCADE
)/

CREATE SEQUENCE UM_SHARED_ROLE_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER UM_SHARED_ROLE_TR1 NO CASCADE BEFORE INSERT ON UM_SHARED_ROLE
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    SET (NEW.UM_ID)
       = (NEXTVAL FOR UM_SHARED_ROLE_SEQUENCE);END/

