DECLARE
	con_name0    VARCHAR2(100);
	con_name1    VARCHAR2(100);
	command      VARCHAR2(200);
	databasename VARCHAR2(128);

BEGIN
	SELECT sys_context('userenv', 'current_schema') INTO databasename FROM dual;
	
	BEGIN
		SELECT a.constraint_name
		INTO con_name0
		FROM all_cons_columns a
		JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name
		WHERE c.constraint_type = 'U' AND a.table_name = 'UM_USER' AND UPPER(a.OWNER) = UPPER(databasename) AND a.column_name='UM_USER_ID';
		
		IF con_name0 IS NOT NULL THEN
			command := 'ALTER TABLE UM_USER DROP CONSTRAINT ' || con_name0;
			dbms_output.Put_line(command);
			EXECUTE IMMEDIATE command;
		END IF;
		
		EXCEPTION WHEN NO_DATA_FOUND THEN
			dbms_output.Put_line('Unique key not found');
	END;
	
	BEGIN
		SELECT a.constraint_name
		INTO con_name1
		FROM all_cons_columns a
		JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name
		WHERE c.constraint_type = 'U' AND a.table_name = 'UM_USER' AND UPPER(a.OWNER) = UPPER(databasename) AND a.column_name='UM_USER_NAME';
		
		IF TRIM(con_name1) IS NOT NULL THEN
			dbms_output.Put_line('Unique key (UM_USER_NAME,UM_TENANT_ID) is already exists');
		END IF;
		
		EXCEPTION WHEN NO_DATA_FOUND THEN
			EXECUTE IMMEDIATE 'ALTER TABLE UM_USER ADD UNIQUE(UM_USER_NAME,UM_TENANT_ID)';
	END;
END;
/

ALTER TABLE UM_USER ADD UNIQUE(UM_USER_ID)
/

CREATE OR REPLACE PROCEDURE add_index_if_not_exists (query IN VARCHAR2)
  IS
BEGIN
  execute immediate query;
  dbms_output.put_line(query);
exception WHEN OTHERS THEN
  dbms_output.put_line('Skipped');
END;
/

CALL add_index_if_not_exists('CREATE UNIQUE INDEX INDEX_UM_USERNAME_UM_TENANT_ID ON UM_USER(UM_USER_NAME, UM_TENANT_ID)')
/

DROP PROCEDURE add_index_if_not_exists
/

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

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

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 TIMESTAMP NOT NULL,
    UM_LAST_MODIFIED TIMESTAMP 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) ON DELETE CASCADE
)
/

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

CREATE TABLE UM_ORG_ATTRIBUTE (
    UM_ID INTEGER,
    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
)
/

CREATE SEQUENCE UM_ORG_ATTRIBUTE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/

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

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
)
/

CREATE TABLE UM_ORG_PERMISSION(
    UM_ID INTEGER,
    UM_RESOURCE_ID VARCHAR(255) NOT NULL,
    UM_ACTION VARCHAR(255) NOT NULL,
    UM_TENANT_ID INTEGER DEFAULT 0,
    PRIMARY KEY (UM_ID)
)
/

CREATE SEQUENCE UM_ORG_PERMISSION_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/

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

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
)
/

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
)
/

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
)
/

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) ON DELETE CASCADE,
    FOREIGN KEY (UM_ID) REFERENCES UM_ORG(UM_ID) ON DELETE CASCADE
)
/

INSERT INTO UM_ORG_HIERARCHY (UM_PARENT_ID, UM_ID, DEPTH)
SELECT '10084a8d-113f-4211-a0d5-efe36b082211', '10084a8d-113f-4211-a0d5-efe36b082211', 0 FROM DUAL
WHERE NOT EXISTS (SELECT * FROM UM_ORG_HIERARCHY WHERE UM_PARENT_ID = '10084a8d-113f-4211-a0d5-efe36b082211' AND UM_ID = '10084a8d-113f-4211-a0d5-efe36b082211')
/

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

CREATE TABLE UM_GROUP_UUID_DOMAIN_MAPPER (
    UM_ID INTEGER,
    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 SEQUENCE UM_GROUP_UUID_DOMAIN_MAPPER_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE

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

CREATE INDEX UUID_GRP_UID_TID ON UM_GROUP_UUID_DOMAIN_MAPPER(UM_GROUP_ID, UM_TENANT_ID)
/
