IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_USER]') AND TYPE IN (N'U'))
	begin
		DECLARE @ConstraintName0 nvarchar(200)
		SELECT @ConstraintName0 = CONSTRAINT_NAME  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='UM_USER' AND COLUMN_NAME='UM_USER_ID'
		IF @ConstraintName0 IS NOT NULL
		begin
			EXEC('ALTER TABLE UM_USER DROP CONSTRAINT ' + @ConstraintName0)
		end
		ALTER TABLE UM_USER ADD UNIQUE(UM_USER_ID)
		DECLARE @ConstraintName1 nvarchar(200)
		SELECT @ConstraintName1 = CONSTRAINT_NAME  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='UM_USER' AND COLUMN_NAME='UM_USER_NAME'
		IF @ConstraintName1 IS NULL
		begin
			ALTER TABLE UM_USER ADD UNIQUE(UM_USER_NAME, UM_TENANT_ID)
		end
	end

CREATE UNIQUE INDEX INDEX_UM_USERNAME_UM_TENANT_ID ON UM_USER(UM_USER_NAME, UM_TENANT_ID);

ALTER TABLE UM_TENANT ADD UM_ORG_UUID VARCHAR(36) DEFAULT NULL;

------------------------ ORGANIZATION MANAGEMENT TABLES -------------------------

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_ORG]') AND TYPE IN (N'U'))
CREATE TABLE UM_ORG (
    UM_ID VARCHAR(36) NOT NULL,
    UM_ORG_NAME VARCHAR(255) NOT NULL,
    UM_ORG_DESCRIPTION VARCHAR(1024),
    UM_CREATED_TIME DATETIME NOT NULL,
    UM_LAST_MODIFIED DATETIME NOT NULL,
    UM_STATUS VARCHAR(255) DEFAULT 'ACTIVE' NOT NULL,
    UM_PARENT_ID VARCHAR(36),
    UM_ORG_TYPE VARCHAR(100) NOT NULL,
    PRIMARY KEY (UM_ID),
    FOREIGN KEY (UM_PARENT_ID) REFERENCES UM_ORG(UM_ID)
);

IF NOT EXISTS (SELECT * FROM UM_ORG WHERE UM_ID = '10084a8d-113f-4211-a0d5-efe36b082211')
BEGIN
    INSERT INTO UM_ORG (UM_ID, UM_ORG_NAME, UM_ORG_DESCRIPTION, UM_CREATED_TIME, UM_LAST_MODIFIED, UM_STATUS, UM_ORG_TYPE)
    VALUES ('10084a8d-113f-4211-a0d5-efe36b082211', 'Super', 'This is the super organization.', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'ACTIVE', 'TENANT')
END;

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_ORG_ATTRIBUTE]') AND TYPE IN (N'U'))
CREATE TABLE UM_ORG_ATTRIBUTE (
    UM_ID INTEGER IDENTITY(1,1) NOT NULL,
    UM_ORG_ID VARCHAR(36) NOT NULL,
    UM_ATTRIBUTE_KEY VARCHAR(255) NOT NULL,
    UM_ATTRIBUTE_VALUE VARCHAR(512),
    PRIMARY KEY (UM_ID),
    UNIQUE (UM_ORG_ID, UM_ATTRIBUTE_KEY),
    FOREIGN KEY (UM_ORG_ID) REFERENCES UM_ORG(UM_ID) ON DELETE CASCADE
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_ORG_ROLE]') AND TYPE IN (N'U'))
CREATE TABLE UM_ORG_ROLE (
    UM_ROLE_ID VARCHAR(255) NOT NULL,
    UM_ROLE_NAME VARCHAR(255) NOT NULL,
    UM_ORG_ID VARCHAR(36) NOT NULL,
    PRIMARY KEY(UM_ROLE_ID),
    CONSTRAINT FK_UM_ORG_ROLE_UM_ORG FOREIGN KEY (UM_ORG_ID) REFERENCES UM_ORG (UM_ID) ON DELETE CASCADE
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_ORG_PERMISSION]') AND TYPE IN (N'U'))
CREATE TABLE UM_ORG_PERMISSION(
    UM_ID INTEGER IDENTITY(1,1) NOT NULL,
    UM_RESOURCE_ID VARCHAR(255) NOT NULL,
    UM_ACTION VARCHAR(255) NOT NULL,
    UM_TENANT_ID INTEGER DEFAULT 0,
    PRIMARY KEY(UM_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_ORG_ROLE_USER]') AND TYPE IN (N'U'))
CREATE TABLE UM_ORG_ROLE_USER (
    UM_USER_ID VARCHAR(255) NOT NULL,
    UM_ROLE_ID VARCHAR(255) NOT NULL,
    CONSTRAINT FK_UM_ORG_ROLE_USER_UM_ORG_ROLE FOREIGN KEY (UM_ROLE_ID) REFERENCES UM_ORG_ROLE(UM_ROLE_ID) ON DELETE CASCADE
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_ORG_ROLE_GROUP]') AND TYPE IN (N'U'))
CREATE TABLE UM_ORG_ROLE_GROUP(
    UM_GROUP_ID VARCHAR(255) NOT NULL,
    UM_ROLE_ID VARCHAR(255) NOT NULL,
    CONSTRAINT FK_UM_ORG_ROLE_GROUP_UM_ORG_ROLE FOREIGN KEY (UM_ROLE_ID) REFERENCES UM_ORG_ROLE(UM_ROLE_ID) ON DELETE CASCADE
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_ORG_ROLE_PERMISSION]') AND TYPE IN (N'U'))
CREATE TABLE UM_ORG_ROLE_PERMISSION(
    UM_PERMISSION_ID INTEGER NOT NULL,
    UM_ROLE_ID VARCHAR(255) NOT NULL,
    CONSTRAINT FK_UM_ORG_ROLE_PERMISSION_UM_ORG_ROLE FOREIGN KEY (UM_ROLE_ID) REFERENCES UM_ORG_ROLE(UM_ROLE_ID) ON DELETE CASCADE,
    CONSTRAINT FK_UM_ORG_ROLE_PERMISSION_UM_ORG_PERMISSION FOREIGN KEY (UM_PERMISSION_ID) REFERENCES UM_ORG_PERMISSION(UM_ID) ON DELETE CASCADE
);

IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UM_ORG_HIERARCHY]') AND TYPE IN (N'U'))
CREATE TABLE UM_ORG_HIERARCHY (
    UM_PARENT_ID VARCHAR(36) NOT NULL,
    UM_ID VARCHAR(36) NOT NULL,
    DEPTH INTEGER,
    PRIMARY KEY (UM_PARENT_ID, UM_ID),
    FOREIGN KEY (UM_PARENT_ID) REFERENCES UM_ORG(UM_ID),
    FOREIGN KEY (UM_ID) REFERENCES UM_ORG(UM_ID)
);

IF NOT EXISTS (SELECT * FROM UM_ORG_HIERARCHY WHERE UM_PARENT_ID = '10084a8d-113f-4211-a0d5-efe36b082211' AND UM_ID = '10084a8d-113f-4211-a0d5-efe36b082211')
BEGIN
    INSERT INTO UM_ORG_HIERARCHY (UM_PARENT_ID, UM_ID, DEPTH)
    VALUES ('10084a8d-113f-4211-a0d5-efe36b082211', '10084a8d-113f-4211-a0d5-efe36b082211', 0)
END;

-- Trigger UM_ORG_HIERARCHY and UM_ORG delete by UM_ID on UM_ORG deletion by UM_ID --
CREATE TRIGGER UM_ORG_DELETE_TRIGGER ON UM_ORG INSTEAD OF DELETE AS
BEGIN
    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;

-------------------------GROUP ID DOMAIN MAPPER TABLES ----------------------------

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_GROUP_UUID_DOMAIN_MAPPER]') AND TYPE IN (N'U'))
CREATE TABLE UM_GROUP_UUID_DOMAIN_MAPPER (
    UM_ID INTEGER IDENTITY(1, 1),
    UM_GROUP_ID VARCHAR(255) NOT NULL,
    UM_DOMAIN_ID INTEGER NOT NULL,
    UM_TENANT_ID INTEGER DEFAULT 0,
    PRIMARY KEY (UM_ID),
    UNIQUE (UM_GROUP_ID),
    FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE
);

CREATE INDEX GRP_UUID_DM_GRP_ID_TID ON UM_GROUP_UUID_DOMAIN_MAPPER(UM_GROUP_ID, UM_TENANT_ID);
