CREATE TABLE IF NOT EXISTS UM_RESOURCE_SHARING_POLICY (
            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
            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) ON DELETE CASCADE,
            FOREIGN KEY (UM_POLICY_HOLDING_ORG_ID) REFERENCES UM_ORG(UM_ID) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS IDX_POLICY_HOLDING_ORG_ID ON UM_RESOURCE_SHARING_POLICY (UM_POLICY_HOLDING_ORG_ID);

CREATE TABLE IF NOT EXISTS UM_SHARED_RESOURCE_ATTRIBUTES (
            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
            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
);

CREATE INDEX IF NOT EXISTS IDX_RESOURCE_SHARING_POLICY_ID ON UM_SHARED_RESOURCE_ATTRIBUTES (UM_RESOURCE_SHARING_POLICY_ID);

-- Create table UM_HYBRID_USER_ROLE_RESTRICTED_EDIT_PERMISSIONS
CREATE TABLE IF NOT EXISTS UM_HYBRID_USER_ROLE_RESTRICTED_EDIT_PERMISSIONS (
    UM_ID INTEGER AUTO_INCREMENT 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
ALTER TABLE UM_ORG_USER_ASSOCIATION
    ADD UM_SHARED_TYPE VARCHAR(255) DEFAULT 'NOT SPECIFIED' NOT NULL;

-- Alter table to add unique auto-incrementing UM_ID
ALTER TABLE UM_ORG_USER_ASSOCIATION
    ADD UM_ID INTEGER AUTO_INCREMENT UNIQUE;
