CREATE TABLE UM_RESOURCE_SHARING_POLICY (
    UM_ID INTEGER 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),
    CONSTRAINT FK_INITIATING_ORG FOREIGN KEY (UM_INITIATING_ORG_ID) REFERENCES UM_ORG (UM_ID) ON DELETE CASCADE,
    CONSTRAINT FK_POLICY_HOLDING_ORG FOREIGN KEY (UM_POLICY_HOLDING_ORG_ID) REFERENCES UM_ORG (UM_ID) ON DELETE CASCADE
)
/

CREATE SEQUENCE UM_RESOURCE_SHARING_POLICY_SEQ  START WITH 1  INCREMENT BY 1 CACHE 20
/

CREATE OR REPLACE TRIGGER UM_RESOURCE_SHARING_POLICY_TRIGGER
        BEFORE INSERT
        ON UM_RESOURCE_SHARING_POLICY
        REFERENCING NEW AS NEW
        FOR EACH ROW
            BEGIN
                SELECT UM_RESOURCE_SHARING_POLICY_SEQ.nextval INTO :NEW.UM_ID FROM dual;
            END;
/

CREATE INDEX IDX_POLICY_HOLDING_ORG_ID ON UM_RESOURCE_SHARING_POLICY (UM_POLICY_HOLDING_ORG_ID)
/

CREATE TABLE UM_SHARED_RESOURCE_ATTRIBUTES (
    UM_ID INTEGER 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),
    CONSTRAINT FK_RESOURCE_POLICY FOREIGN KEY (UM_RESOURCE_SHARING_POLICY_ID)
        REFERENCES UM_RESOURCE_SHARING_POLICY (UM_ID) ON DELETE CASCADE
)
/

CREATE SEQUENCE UM_SHARED_RESOURCE_ATTRIBUTES_SEQ  START WITH 1  INCREMENT BY 1 CACHE 20
/

CREATE OR REPLACE TRIGGER UM_SHARED_RESOURCE_ATTRIBUTES_TRIGGER
        BEFORE INSERT
        ON UM_SHARED_RESOURCE_ATTRIBUTES
        REFERENCING NEW AS NEW
        FOR EACH ROW
            BEGIN
                SELECT UM_SHARED_RESOURCE_ATTRIBUTES_SEQ.nextval INTO :NEW.UM_ID FROM dual;
            END;
/

CREATE INDEX 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 UM_HYBRID_USER_ROLE_RESTRICTED_EDIT_PERMISSIONS (
    UM_ID INTEGER NOT NULL,
    UM_HYBRID_USER_ROLE_ID INTEGER NOT NULL,
    UM_HYBRID_USER_ROLE_TENANT_ID INTEGER NOT NULL,
    UM_EDIT_OPERATION VARCHAR2(255) NOT NULL,
    UM_PERMITTED_ORG_ID VARCHAR2(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
)
/;

-- Create sequence for auto-incrementing UM_ID
CREATE SEQUENCE UM_HYBRID_USER_ROLE_RESTRICTED_EDIT_PERMISSIONS_SEQ START WITH 1 INCREMENT BY 1 CACHE 20
/

-- Create trigger for auto-incrementing UM_ID
CREATE OR REPLACE TRIGGER UM_HYBRID_USER_ROLE_RESTRICTED_EDIT_PERMISSIONS_TRG
BEFORE INSERT ON UM_HYBRID_USER_ROLE_RESTRICTED_EDIT_PERMISSIONS
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
    SELECT UM_HYBRID_USER_ROLE_RESTRICTED_EDIT_PERMISSIONS_SEQ.NEXTVAL INTO :NEW.UM_ID FROM DUAL;
END;
/

-- Alter table to add shared type
ALTER TABLE UM_ORG_USER_ASSOCIATION
ADD UM_SHARED_TYPE VARCHAR2(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 UNIQUE
/

-- Create sequence for auto-incrementing UM_ID
CREATE SEQUENCE UM_ORG_USER_ASSOCIATION_SEQ START WITH 1 INCREMENT BY 1 CACHE 20
/

-- Create trigger for auto-incrementing UM_ID
CREATE OR REPLACE TRIGGER UM_ORG_USER_ASSOCIATION_TRG
BEFORE INSERT ON UM_ORG_USER_ASSOCIATION
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
    SELECT UM_ORG_USER_ASSOCIATION_SEQ.NEXTVAL INTO :NEW.UM_ID FROM DUAL;
END;
/
