ALTER TABLE UM_HYBRID_USER_ROLE ADD CONSTRAINT UM_HYBRID_USER_ROLE_F1 FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE
/
ALTER TABLE UM_HYBRID_GROUP_ROLE ADD CONSTRAINT UM_HYBRID_GROUP_ROLE_F1 FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE
/

CREATE TABLE UM_RESOURCE_SHARING_POLICY (
    UM_ID DECIMAL(31,0) 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) ON DELETE CASCADE,
    FOREIGN KEY (UM_POLICY_HOLDING_ORG_ID) REFERENCES UM_ORG(UM_ID) ON DELETE CASCADE
)/

CREATE SEQUENCE UM_RESOURCE_SHARING_POLICY_SEQ AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER UM_RESOURCE_SHARING_POLICY_TRG NO CASCADE BEFORE INSERT ON UM_RESOURCE_SHARING_POLICY
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    SET (NEW.UM_ID)
        = (NEXTVAL FOR UM_RESOURCE_SHARING_POLICY_SEQ);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 DECIMAL(31,0) NOT NULL,
    UM_RESOURCE_SHARING_POLICY_ID DECIMAL(31,0) 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 SEQUENCE UM_SHARED_RESOURCE_ATTRIBUTES_SEQ AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER UM_SHARED_RESOURCE_ATTRIBUTES_TRG NO CASCADE BEFORE INSERT ON UM_SHARED_RESOURCE_ATTRIBUTES
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    SET (NEW.UM_ID)
        = (NEXTVAL FOR UM_SHARED_RESOURCE_ATTRIBUTES_SEQ);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 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
)
/

-- Create sequence for UM_HYBRID_USER_ROLE_RESTRICTED_EDIT_PERMISSIONS
CREATE SEQUENCE UM_HYBRID_USER_ROLE_RESTRICTED_EDIT_PERMISSIONS_SEQ
    INCREMENT BY 1
    START WITH 1
    NOCACHE
/
-- Create trigger for auto-incrementing UM_ID
CREATE TRIGGER UM_HYBRID_USER_ROLE_RESTRICTED_EDIT_PERMISSIONS_TRIG
    NO CASCADE BEFORE INSERT
    ON UM_HYBRID_USER_ROLE_RESTRICTED_EDIT_PERMISSIONS
    REFERENCING NEW AS NEW
    FOR EACH ROW MODE DB2SQL
        BEGIN ATOMIC
            SET (NEW.UM_ID) = (NEXTVAL FOR UM_HYBRID_USER_ROLE_RESTRICTED_EDIT_PERMISSIONS_SEQ);
        END
/
-- Alter table to add shared type
ALTER TABLE UM_ORG_USER_ASSOCIATION
ADD COLUMN 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 COLUMN UM_ID INTEGER
/

-- Create sequence for UM_ORG_USER_ASSOCIATION UM_ID
CREATE SEQUENCE UM_ORG_USER_ASSOCIATION_SEQ
    INCREMENT BY 1
    START WITH 1
    NOCACHE
/

-- Create trigger for auto-incrementing UM_ID in UM_ORG_USER_ASSOCIATION
CREATE TRIGGER UM_ORG_USER_ASSOCIATION_TRIG
    NO CASCADE BEFORE INSERT
    ON UM_ORG_USER_ASSOCIATION
    REFERENCING NEW AS NEW
    FOR EACH ROW MODE DB2SQL
        BEGIN ATOMIC
            SET (NEW.UM_ID) = (NEXTVAL FOR UM_ORG_USER_ASSOCIATION_SEQ);
        END
/
