-- create table UM_RESOURCE_SHARING_POLICY
IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_RESOURCE_SHARING_POLICY]') AND TYPE IN (N'U'))
CREATE TABLE UM_RESOURCE_SHARING_POLICY (
    UM_ID INTEGER IDENTITY(1, 1) NOT NULL,
    UM_RESOURCE_TYPE VARCHAR(255) NOT NULL,
    UM_RESOURCE_ID VARCHAR(36) NOT NULL,
    UM_INITIATING_ORG_ID VARCHAR(36) NOT NULL,
    UM_POLICY_HOLDING_ORG_ID VARCHAR(36) NOT NULL,
    UM_SHARING_POLICY VARCHAR(255) NOT NULL,
    PRIMARY KEY (UM_ID),
    FOREIGN KEY (UM_INITIATING_ORG_ID) REFERENCES UM_ORG (UM_ID),
    FOREIGN KEY (UM_POLICY_HOLDING_ORG_ID) REFERENCES UM_ORG (UM_ID)
);

IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'IDX_POLICY_HOLDING_ORG_ID')
DROP INDEX UM_RESOURCE_SHARING_POLICY.IDX_POLICY_HOLDING_ORG_ID;
CREATE INDEX IDX_POLICY_HOLDING_ORG_ID ON UM_RESOURCE_SHARING_POLICY (UM_POLICY_HOLDING_ORG_ID);

-- create table UM_SHARED_RESOURCE_ATTRIBUTES
IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_SHARED_RESOURCE_ATTRIBUTES]') AND TYPE IN (N'U'))
CREATE TABLE UM_SHARED_RESOURCE_ATTRIBUTES (
    UM_ID INTEGER IDENTITY(1, 1) NOT NULL,
    UM_RESOURCE_SHARING_POLICY_ID INTEGER NOT NULL,
    UM_SHARED_ATTRIBUTE_TYPE VARCHAR(255) NOT NULL,
    UM_SHARED_ATTRIBUTE_ID VARCHAR(36) NOT NULL,
    PRIMARY KEY (UM_ID),
    FOREIGN KEY (UM_RESOURCE_SHARING_POLICY_ID) REFERENCES UM_RESOURCE_SHARING_POLICY (UM_ID) ON DELETE CASCADE
);

IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'IDX_RESOURCE_SHARING_POLICY_ID')
DROP INDEX UM_SHARED_RESOURCE_ATTRIBUTES.IDX_RESOURCE_SHARING_POLICY_ID;
CREATE INDEX IDX_RESOURCE_SHARING_POLICY_ID ON UM_SHARED_RESOURCE_ATTRIBUTES (UM_RESOURCE_SHARING_POLICY_ID);

-- drop previous trigger for UM_ORG deletion and alter the new deletions as well
DROP TRIGGER IF EXISTS UM_ORG_DELETE_TRIGGER;

GO

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) DELETE FROM UM_RESOURCE_SHARING_POLICY WHERE UM_INITIATING_ORG_ID IN (SELECT UM_ID FROM deleted) DELETE FROM UM_RESOURCE_SHARING_POLICY WHERE UM_POLICY_HOLDING_ORG_ID IN (SELECT UM_ID FROM deleted) END;

 -- Create table UM_HYBRID_USER_ROLE_RESTRICTED_EDIT_PERMISSIONS
IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_HYBRID_USER_ROLE_RESTRICTED_EDIT_PERMISSIONS]') AND TYPE IN (N'U'))
CREATE TABLE UM_HYBRID_USER_ROLE_RESTRICTED_EDIT_PERMISSIONS (
    UM_ID INTEGER IDENTITY(1, 1) NOT NULL,
    UM_HYBRID_USER_ROLE_ID INTEGER NOT NULL,
    UM_HYBRID_USER_ROLE_TENANT_ID INTEGER NOT NULL,
    UM_EDIT_OPERATION VARCHAR(255) NOT NULL,
    UM_PERMITTED_ORG_ID VARCHAR(36) NOT NULL,
    PRIMARY KEY (UM_ID),
    FOREIGN KEY (UM_HYBRID_USER_ROLE_ID, UM_HYBRID_USER_ROLE_TENANT_ID)
        REFERENCES UM_HYBRID_USER_ROLE(UM_ID, UM_TENANT_ID)
        ON DELETE CASCADE
);

-- Alter table to add shared type and edit restriction
ALTER TABLE UM_ORG_USER_ASSOCIATION
ADD UM_SHARED_TYPE VARCHAR(255) NOT NULL DEFAULT 'NOT SPECIFIED';

-- Alter table to add unique auto-incrementing UM_ID
ALTER TABLE UM_ORG_USER_ASSOCIATION
ADD UM_ID INT IDENTITY(1,1) UNIQUE;
