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

DROP TABLE IF EXISTS UM_ORG_DISCOVERY;
DROP SEQUENCE IF EXISTS UM_ORG_DISCOVERY_PK_SEQ;
CREATE SEQUENCE UM_ORG_DISCOVERY_PK_SEQ;
CREATE TABLE UM_ORG_DISCOVERY (
            UM_ID INTEGER DEFAULT NEXTVAL('UM_ORG_DISCOVERY_PK_SEQ'),
            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
);

ALTER TABLE UM_ORG_ROLE_USER ADD COLUMN UM_USER_RESIDENT_ORG_ID VARCHAR(36);

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

DROP TABLE IF EXISTS UM_HYBRID_ROLE_AUDIENCE;
DROP SEQUENCE IF EXISTS UM_HYBRID_ROLE_AUDIENCE_PK_SEQ;
CREATE SEQUENCE UM_HYBRID_ROLE_AUDIENCE_PK_SEQ;
CREATE TABLE UM_HYBRID_ROLE_AUDIENCE(
            UM_ID INTEGER DEFAULT NEXTVAL('UM_HYBRID_ROLE_AUDIENCE_PK_SEQ'),
            UM_AUDIENCE VARCHAR(255) NOT NULL,
            UM_AUDIENCE_ID VARCHAR(255) NOT NULL,
            UNIQUE (UM_AUDIENCE, UM_AUDIENCE_ID),
            PRIMARY KEY (UM_ID)
);

ALTER TABLE UM_HYBRID_ROLE ADD COLUMN UM_AUDIENCE_REF_ID INTEGER DEFAULT -1 NOT NULL;
ALTER TABLE UM_HYBRID_ROLE ADD COLUMN UM_UUID 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;

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

ALTER TABLE UM_HYBRID_ROLE ADD UNIQUE(UM_ROLE_NAME, UM_TENANT_ID, UM_AUDIENCE_REF_ID);

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

DROP TABLE IF EXISTS UM_SHARED_ROLE;
DROP SEQUENCE IF EXISTS UM_SHARED_ROLE_PK_SEQ;
CREATE SEQUENCE UM_SHARED_ROLE_PK_SEQ;
CREATE TABLE UM_SHARED_ROLE(
            UM_ID INTEGER DEFAULT NEXTVAL('UM_SHARED_ROLE_PK_SEQ'),
            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
);
