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 UM_ORG_DISCOVERY (
            UM_ID INTEGER,
            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_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER UM_ORG_DISCOVERY_TRIG
            BEFORE INSERT
            ON UM_ORG_DISCOVERY
            REFERENCING NEW AS NEW
            FOR EACH ROW
              BEGIN
                SELECT UM_ORG_DISCOVERY_SEQ.nextval INTO :NEW.UM_ID FROM dual;
              END;
/
ALTER TABLE UM_ORG_ROLE_USER ADD (UM_USER_RESIDENT_ORG_ID VARCHAR2(36))
/

CREATE TABLE 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 INTEGER 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 START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER UM_HYBRID_ROLE_AUDIENCE_TRIGGER
                    BEFORE INSERT
                    ON UM_HYBRID_ROLE_AUDIENCE
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT UM_HYBRID_ROLE_AUDIENCE_SEQUENCE.nextval INTO :NEW.UM_ID FROM dual;
              END;
/

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

ALTER TABLE UM_HYBRID_ROLE ADD UM_AUDIENCE_REF_ID INTEGER DEFAULT -1 NOT NULL
/
ALTER TABLE UM_HYBRID_ROLE ADD UM_UUID VARCHAR(36)
/
DECLARE
    v_constraint_name VARCHAR2(255);
BEGIN
    SELECT constraint_name INTO v_constraint_name
    FROM user_constraints
    WHERE table_name = 'UM_HYBRID_ROLE'
    AND constraint_type = 'U'
    AND constraint_name = (
        SELECT constraint_name
        FROM user_cons_columns
        WHERE table_name = 'UM_HYBRID_ROLE'
        AND column_name IN ('UM_ROLE_NAME', 'UM_TENANT_ID')
        GROUP BY constraint_name
        HAVING COUNT(*) = 2
    )
    AND rownum = 1;

    EXECUTE IMMEDIATE 'ALTER TABLE UM_HYBRID_ROLE DROP CONSTRAINT ' || v_constraint_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        NULL; -- Constraint does not exist, do nothing
END;
/

ALTER TABLE UM_HYBRID_ROLE ADD 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 INTEGER 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 START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER UM_SHARED_ROLE_TRIGGER
                    BEFORE INSERT
                    ON UM_SHARED_ROLE
                    REFERENCING NEW AS NEW
                    FOR EACH ROW
                    BEGIN
                    SELECT UM_SHARED_ROLE_SEQUENCE.nextval INTO :NEW.UM_ID FROM dual;
              END;
/

