CREATE TABLE UM_TENANT
(
    UM_ID           INTEGER      NOT NULL AUTO_INCREMENT,
    UM_DOMAIN_NAME  VARCHAR(255) NOT NULL,
    UM_EMAIL        VARCHAR(255),
    UM_ACTIVE       BOOLEAN DEFAULT FALSE,
    UM_CREATED_DATE TIMESTAMP    NOT NULL,
    UM_USER_CONFIG  LONGBLOB,
    PRIMARY KEY (UM_ID),
    UNIQUE (UM_DOMAIN_NAME)
) ENGINE INNODB;

CREATE TABLE UM_DOMAIN
(
    UM_DOMAIN_ID   INTEGER      NOT NULL AUTO_INCREMENT,
    UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
    UM_TENANT_ID   INTEGER DEFAULT 0,
    PRIMARY KEY (UM_DOMAIN_ID, UM_TENANT_ID),
    UNIQUE (UM_DOMAIN_NAME, UM_TENANT_ID)
) ENGINE INNODB;

CREATE UNIQUE INDEX INDEX_UM_TENANT_UM_DOMAIN_NAME
    ON UM_TENANT (UM_DOMAIN_NAME);

CREATE TABLE UM_USER
(
    UM_ID             INTEGER      NOT NULL AUTO_INCREMENT,
    UM_USER_NAME      VARCHAR(255) NOT NULL,
    UM_USER_PASSWORD  VARCHAR(255) NOT NULL,
    UM_SALT_VALUE     VARCHAR(31),
    UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE,
    UM_CHANGED_TIME   TIMESTAMP    NOT NULL,
    UM_TENANT_ID      INTEGER DEFAULT 0,
    PRIMARY KEY (UM_ID, UM_TENANT_ID),
    UNIQUE (UM_USER_NAME, UM_TENANT_ID)
) ENGINE INNODB;

CREATE TABLE UM_SYSTEM_USER
(
    UM_ID             INTEGER      NOT NULL AUTO_INCREMENT,
    UM_USER_NAME      VARCHAR(255) NOT NULL,
    UM_USER_PASSWORD  VARCHAR(255) NOT NULL,
    UM_SALT_VALUE     VARCHAR(31),
    UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE,
    UM_CHANGED_TIME   TIMESTAMP    NOT NULL,
    UM_TENANT_ID      INTEGER DEFAULT 0,
    PRIMARY KEY (UM_ID, UM_TENANT_ID),
    UNIQUE (UM_USER_NAME, UM_TENANT_ID)
) ENGINE INNODB;

CREATE TABLE UM_ROLE
(
    UM_ID          INTEGER      NOT NULL AUTO_INCREMENT,
    UM_ROLE_NAME   VARCHAR(255) NOT NULL,
    UM_TENANT_ID   INTEGER DEFAULT 0,
    UM_SHARED_ROLE BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (UM_ID, UM_TENANT_ID),
    UNIQUE (UM_ROLE_NAME, UM_TENANT_ID)
) ENGINE INNODB;


CREATE TABLE UM_MODULE
(
    UM_ID          INTEGER NOT NULL AUTO_INCREMENT,
    UM_MODULE_NAME VARCHAR(100),
    UNIQUE (UM_MODULE_NAME),
    PRIMARY KEY (UM_ID)
) ENGINE INNODB;

CREATE TABLE UM_MODULE_ACTIONS
(
    UM_ACTION    VARCHAR(255) NOT NULL,
    UM_MODULE_ID INTEGER      NOT NULL,
    PRIMARY KEY (UM_ACTION, UM_MODULE_ID),
    FOREIGN KEY (UM_MODULE_ID) REFERENCES UM_MODULE (UM_ID) ON DELETE CASCADE
) ENGINE INNODB;

CREATE TABLE UM_PERMISSION
(
    UM_ID          INTEGER      NOT NULL AUTO_INCREMENT,
    UM_RESOURCE_ID VARCHAR(255) NOT NULL,
    UM_ACTION      VARCHAR(255) NOT NULL,
    UM_TENANT_ID   INTEGER DEFAULT 0,
    UM_MODULE_ID   INTEGER DEFAULT 0,
    UNIQUE (UM_RESOURCE_ID, UM_ACTION, UM_TENANT_ID),
    PRIMARY KEY (UM_ID, UM_TENANT_ID)
) ENGINE INNODB;

CREATE INDEX INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION ON UM_PERMISSION (UM_RESOURCE_ID, UM_ACTION, UM_TENANT_ID);

CREATE TABLE UM_ROLE_PERMISSION
(
    UM_ID            INTEGER      NOT NULL AUTO_INCREMENT,
    UM_PERMISSION_ID INTEGER      NOT NULL,
    UM_ROLE_NAME     VARCHAR(255) NOT NULL,
    UM_IS_ALLOWED    SMALLINT     NOT NULL,
    UM_TENANT_ID     INTEGER DEFAULT 0,
    UM_DOMAIN_ID     INTEGER,
    UNIQUE (UM_PERMISSION_ID, UM_ROLE_NAME, UM_TENANT_ID, UM_DOMAIN_ID),
    FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION (UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
    FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN (UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
    PRIMARY KEY (UM_ID, UM_TENANT_ID)
) ENGINE INNODB;

-- REMOVED UNIQUE (UM_PERMISSION_ID, UM_ROLE_ID)
CREATE TABLE UM_USER_PERMISSION
(
    UM_ID            INTEGER      NOT NULL AUTO_INCREMENT,
    UM_PERMISSION_ID INTEGER      NOT NULL,
    UM_USER_NAME     VARCHAR(255) NOT NULL,
    UM_IS_ALLOWED    SMALLINT     NOT NULL,
    UM_TENANT_ID     INTEGER DEFAULT 0,
    FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION (UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
    PRIMARY KEY (UM_ID, UM_TENANT_ID)
) ENGINE INNODB;

-- REMOVED UNIQUE (UM_PERMISSION_ID, UM_USER_ID)
CREATE TABLE UM_USER_ROLE
(
    UM_ID        INTEGER NOT NULL AUTO_INCREMENT,
    UM_ROLE_ID   INTEGER NOT NULL,
    UM_USER_ID   INTEGER NOT NULL,
    UM_TENANT_ID INTEGER DEFAULT 0,
    UNIQUE (UM_USER_ID, UM_ROLE_ID, UM_TENANT_ID),
    FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_ROLE (UM_ID, UM_TENANT_ID),
    FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER (UM_ID, UM_TENANT_ID),
    PRIMARY KEY (UM_ID, UM_TENANT_ID)
) ENGINE INNODB;

CREATE TABLE UM_SHARED_USER_ROLE
(
    UM_ROLE_ID        INTEGER NOT NULL,
    UM_USER_ID        INTEGER NOT NULL,
    UM_USER_TENANT_ID INTEGER NOT NULL,
    UM_ROLE_TENANT_ID INTEGER NOT NULL,
    UNIQUE (UM_USER_ID, UM_ROLE_ID, UM_USER_TENANT_ID, UM_ROLE_TENANT_ID),
    FOREIGN KEY (UM_ROLE_ID, UM_ROLE_TENANT_ID) REFERENCES UM_ROLE (UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
    FOREIGN KEY (UM_USER_ID, UM_USER_TENANT_ID) REFERENCES UM_USER (UM_ID, UM_TENANT_ID) ON DELETE CASCADE
) ENGINE INNODB;

CREATE TABLE UM_ACCOUNT_MAPPING
(
    UM_ID                INTEGER      NOT NULL AUTO_INCREMENT,
    UM_USER_NAME         VARCHAR(255) NOT NULL,
    UM_TENANT_ID         INTEGER      NOT NULL,
    UM_USER_STORE_DOMAIN VARCHAR(100),
    UM_ACC_LINK_ID       INTEGER      NOT NULL,
    UNIQUE (UM_USER_NAME, UM_TENANT_ID, UM_USER_STORE_DOMAIN, UM_ACC_LINK_ID),
    FOREIGN KEY (UM_TENANT_ID) REFERENCES UM_TENANT (UM_ID) ON DELETE CASCADE,
    PRIMARY KEY (UM_ID)
) ENGINE INNODB;


CREATE TABLE UM_USER_ATTRIBUTE
(
    UM_ID         INTEGER      NOT NULL AUTO_INCREMENT,
    UM_ATTR_NAME  VARCHAR(255) NOT NULL,
    UM_ATTR_VALUE VARCHAR(1024),
    UM_PROFILE_ID VARCHAR(255),
    UM_USER_ID    INTEGER,
    UM_TENANT_ID  INTEGER DEFAULT 0,
    FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER (UM_ID, UM_TENANT_ID),
    PRIMARY KEY (UM_ID, UM_TENANT_ID)
) ENGINE INNODB;

CREATE INDEX UM_USER_ID_INDEX ON UM_USER_ATTRIBUTE (UM_USER_ID);

CREATE TABLE UM_DIALECT
(
    UM_ID          INTEGER      NOT NULL AUTO_INCREMENT,
    UM_DIALECT_URI VARCHAR(255) NOT NULL,
    UM_TENANT_ID   INTEGER DEFAULT 0,
    UNIQUE (UM_DIALECT_URI, UM_TENANT_ID),
    PRIMARY KEY (UM_ID, UM_TENANT_ID)
) ENGINE INNODB;

CREATE TABLE UM_CLAIM
(
    UM_ID                      INTEGER      NOT NULL AUTO_INCREMENT,
    UM_DIALECT_ID              INTEGER      NOT NULL,
    UM_CLAIM_URI               VARCHAR(255) NOT NULL,
    UM_DISPLAY_TAG             VARCHAR(255),
    UM_DESCRIPTION             VARCHAR(255),
    UM_MAPPED_ATTRIBUTE_DOMAIN VARCHAR(255),
    UM_MAPPED_ATTRIBUTE        VARCHAR(255),
    UM_REG_EX                  VARCHAR(255),
    UM_SUPPORTED               SMALLINT,
    UM_REQUIRED                SMALLINT,
    UM_DISPLAY_ORDER           INTEGER,
    UM_CHECKED_ATTRIBUTE       SMALLINT,
    UM_READ_ONLY               SMALLINT,
    UM_TENANT_ID               INTEGER DEFAULT 0,
    UNIQUE (UM_DIALECT_ID, UM_CLAIM_URI, UM_TENANT_ID, UM_MAPPED_ATTRIBUTE_DOMAIN),
    FOREIGN KEY (UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT (UM_ID, UM_TENANT_ID),
    PRIMARY KEY (UM_ID, UM_TENANT_ID)
) ENGINE INNODB;


CREATE TABLE UM_PROFILE_CONFIG
(
    UM_ID           INTEGER NOT NULL AUTO_INCREMENT,
    UM_DIALECT_ID   INTEGER NOT NULL,
    UM_PROFILE_NAME VARCHAR(255),
    UM_TENANT_ID    INTEGER DEFAULT 0,
    FOREIGN KEY (UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT (UM_ID, UM_TENANT_ID),
    PRIMARY KEY (UM_ID, UM_TENANT_ID)
) ENGINE INNODB;

CREATE TABLE IF NOT EXISTS UM_CLAIM_BEHAVIOR
(
    UM_ID         INTEGER NOT NULL AUTO_INCREMENT,
    UM_PROFILE_ID INTEGER,
    UM_CLAIM_ID   INTEGER,
    UM_BEHAVIOUR  SMALLINT,
    UM_TENANT_ID  INTEGER DEFAULT 0,
    FOREIGN KEY (UM_PROFILE_ID, UM_TENANT_ID) REFERENCES UM_PROFILE_CONFIG (UM_ID, UM_TENANT_ID),
    FOREIGN KEY (UM_CLAIM_ID, UM_TENANT_ID) REFERENCES UM_CLAIM (UM_ID, UM_TENANT_ID),
    PRIMARY KEY (UM_ID, UM_TENANT_ID)
) ENGINE INNODB;

CREATE TABLE UM_HYBRID_ROLE
(
    UM_ID        INTEGER      NOT NULL AUTO_INCREMENT,
    UM_ROLE_NAME VARCHAR(255) NOT NULL,
    UM_TENANT_ID INTEGER DEFAULT 0,
    PRIMARY KEY (UM_ID, UM_TENANT_ID),
    UNIQUE (UM_ROLE_NAME, UM_TENANT_ID)
) ENGINE INNODB;

CREATE INDEX UM_ROLE_NAME_IND ON UM_HYBRID_ROLE (UM_ROLE_NAME);

CREATE TABLE UM_HYBRID_USER_ROLE
(
    UM_ID        INTEGER NOT NULL AUTO_INCREMENT,
    UM_USER_NAME VARCHAR(255),
    UM_ROLE_ID   INTEGER NOT NULL,
    UM_TENANT_ID INTEGER DEFAULT 0,
    UM_DOMAIN_ID INTEGER,
    UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID, UM_DOMAIN_ID),
    FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_HYBRID_ROLE (UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
    FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN (UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
    PRIMARY KEY (UM_ID, UM_TENANT_ID)
) ENGINE INNODB;

CREATE TABLE UM_SYSTEM_ROLE
(
    UM_ID        INTEGER      NOT NULL AUTO_INCREMENT,
    UM_ROLE_NAME VARCHAR(255) NOT NULL,
    UM_TENANT_ID INTEGER DEFAULT 0,
    PRIMARY KEY (UM_ID, UM_TENANT_ID),
    UNIQUE (UM_ROLE_NAME, UM_TENANT_ID)
) ENGINE INNODB;

CREATE INDEX SYSTEM_ROLE_IND_BY_RN_TI ON UM_SYSTEM_ROLE (UM_ROLE_NAME, UM_TENANT_ID);

CREATE TABLE UM_SYSTEM_USER_ROLE
(
    UM_ID        INTEGER NOT NULL AUTO_INCREMENT,
    UM_USER_NAME VARCHAR(255),
    UM_ROLE_ID   INTEGER NOT NULL,
    UM_TENANT_ID INTEGER DEFAULT 0,
    UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID),
    FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_SYSTEM_ROLE (UM_ID, UM_TENANT_ID),
    PRIMARY KEY (UM_ID, UM_TENANT_ID)
) ENGINE INNODB;


CREATE TABLE UM_HYBRID_REMEMBER_ME
(
    UM_ID           INTEGER      NOT NULL AUTO_INCREMENT,
    UM_USER_NAME    VARCHAR(255) NOT NULL,
    UM_COOKIE_VALUE VARCHAR(1024),
    UM_CREATED_TIME TIMESTAMP,
    UM_TENANT_ID    INTEGER DEFAULT 0,
    PRIMARY KEY (UM_ID, UM_TENANT_ID)
) ENGINE INNODB;
