CREATE INDEX REG_RESC_PROP_BY_PROP_ID_TI ON REG_RESOURCE_PROPERTY(REG_TENANT_ID,REG_PROPERTY_ID);

CREATE INDEX INDEX_ROLE_PERMSN_TI_RN ON UM_ROLE_PERMISSION(UM_TENANT_ID,UM_ROLE_NAME);

CREATE INDEX INDEX_UM_TENANT_ORG_UUID ON UM_TENANT(UM_ORG_UUID);

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_ORG_DISCOVERY]') AND TYPE IN (N'U'))
CREATE TABLE UM_ORG_DISCOVERY (
            UM_ID INTEGER IDENTITY(1,1) 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),
            FOREIGN KEY (UM_ORG_ID) REFERENCES UM_ORG(UM_ID)
);

DROP TRIGGER UM_ORG_DELETE_TRIGGER;

CREATE TRIGGER UM_ORG_DELETE_TRIGGER ON UM_ORG INSTEAD OF DELETE AS BEGIN DELETE FROM UM_ORG_DISCOVERY WHERE UM_ROOT_ORG_ID IN (SELECT UM_ID FROM deleted) DELETE FROM UM_ORG_DISCOVERY WHERE UM_ORG_ID IN (SELECT UM_ID FROM deleted) DELETE FROM UM_ORG_HIERARCHY WHERE UM_PARENT_ID IN (SELECT UM_ID FROM deleted) DELETE FROM UM_ORG_HIERARCHY WHERE UM_ID IN (SELECT UM_ID FROM deleted) DELETE FROM UM_ORG WHERE UM_PARENT_ID IN (SELECT UM_ID FROM deleted) DELETE FROM UM_ORG WHERE UM_ID IN (SELECT UM_ID FROM deleted) END;

ALTER TABLE UM_ORG_ROLE_USER ADD UM_USER_RESIDENT_ORG_ID VARCHAR(36);

ALTER TABLE UM_ROLE ADD UM_ROLE_UUID VARCHAR(255) DEFAULT -1 NOT NULL;

ALTER TABLE UM_ROLE ADD UM_CREATED_TIME DATETIME2 DEFAULT SYSDATETIME() NOT NULL;

ALTER TABLE UM_ROLE ADD UM_LAST_MODIFIED DATETIME2 DEFAULT SYSDATETIME() NOT NULL;

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_ORG_USER_ASSOCIATION]') AND TYPE IN (N'U'))
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,
    CONSTRAINT PK_ORG_USER_ASSOC PRIMARY KEY (UM_USER_ID, UM_ORG_ID, UM_ASSOCIATED_USER_ID, UM_ASSOCIATED_ORG_ID)
);

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_HYBRID_ROLE_AUDIENCE]') AND TYPE IN (N'U'))
CREATE TABLE UM_HYBRID_ROLE_AUDIENCE(
            UM_ID INTEGER IDENTITY(1, 1),
            UM_AUDIENCE VARCHAR(255) NOT NULL,
            UM_AUDIENCE_ID VARCHAR(255) NOT NULL,
            UNIQUE (UM_AUDIENCE, UM_AUDIENCE_ID),
            PRIMARY KEY (UM_ID)
);

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'UM_HYBRID_ROLE' AND COLUMN_NAME = 'UM_AUDIENCE_REF_ID')
BEGIN
    DECLARE @COMMAND NVARCHAR(200)
    SELECT @COMMAND='ALTER TABLE UM_HYBRID_ROLE DROP CONSTRAINT ' + A.CONSTRAINT_NAME + ';' FROM (SELECT * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME='UM_HYBRID_ROLE' AND COLUMN_NAME='UM_ROLE_NAME') A INNER JOIN (SELECT * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME='UM_HYBRID_ROLE' AND COLUMN_NAME='UM_TENANT_ID') B ON A.CONSTRAINT_NAME=B.CONSTRAINT_NAME
    EXEC (@COMMAND)
    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)
    ALTER TABLE UM_HYBRID_ROLE ADD CONSTRAINT UM_HYBRID_ROLE_CONSTRAINT UNIQUE (UM_ROLE_NAME, UM_TENANT_ID, UM_AUDIENCE_REF_ID)
END

IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_IDP_GROUP_ROLE]') AND TYPE IN (N'U'))
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
);

IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_SHARED_ROLE]') AND TYPE IN (N'U'))
CREATE TABLE UM_SHARED_ROLE(
            UM_ID INTEGER IDENTITY(1,1) 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),
            FOREIGN KEY (UM_MAIN_ROLE_ID, UM_MAIN_ROLE_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID)
);

CREATE TRIGGER UM_HYBRID_ROLE_TRIGGER ON UM_HYBRID_ROLE INSTEAD OF DELETE AS BEGIN DELETE FROM UM_SHARED_ROLE WHERE UM_MAIN_ROLE_ID IN (SELECT UM_ID FROM deleted) AND UM_MAIN_ROLE_TENANT_ID IN (SELECT UM_TENANT_ID FROM deleted) DELETE FROM UM_SHARED_ROLE WHERE UM_SHARED_ROLE_ID IN (SELECT UM_ID FROM deleted) AND UM_SHARED_ROLE_TENANT_ID IN (SELECT UM_TENANT_ID FROM deleted) DELETE FROM UM_HYBRID_ROLE WHERE UM_ID IN (SELECT UM_ID FROM deleted) AND UM_TENANT_ID IN (SELECT UM_TENANT_ID FROM deleted) END;
