CREATE TABLE IF NOT EXISTS REG_CLUSTER_LOCK (
             REG_LOCK_NAME VARCHAR (20),
             REG_LOCK_STATUS VARCHAR (20),
             REG_LOCKED_TIME TIMESTAMP,
             REG_TENANT_ID INTEGER DEFAULT 0,
             PRIMARY KEY (REG_LOCK_NAME)
)ENGINE NDB;

CREATE TABLE IF NOT EXISTS REG_LOG (
             REG_LOG_ID INTEGER AUTO_INCREMENT,
             REG_PATH VARCHAR (750),
             REG_USER_ID VARCHAR (31) NOT NULL,
             REG_LOGGED_TIME TIMESTAMP NOT NULL,
             REG_ACTION INTEGER NOT NULL,
             REG_ACTION_DATA VARCHAR (500),
             REG_TENANT_ID INTEGER DEFAULT 0,
             PRIMARY KEY (REG_LOG_ID, REG_TENANT_ID)
)ENGINE NDB;

-- The REG_PATH_VALUE should be less than 767 bytes, and hence was fixed at 750.
-- See CARBON-5917.

CREATE TABLE IF NOT EXISTS REG_PATH(
             REG_PATH_ID INTEGER NOT NULL AUTO_INCREMENT,
             REG_PATH_VALUE VARCHAR(750) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
             REG_PATH_PARENT_ID INTEGER,
             REG_TENANT_ID INTEGER DEFAULT 0,
             CONSTRAINT PK_REG_PATH PRIMARY KEY(REG_PATH_ID, REG_TENANT_ID)
)ENGINE NDB;


CREATE TABLE IF NOT EXISTS REG_CONTENT (
             REG_CONTENT_ID INTEGER NOT NULL AUTO_INCREMENT,
             REG_CONTENT_DATA LONGBLOB,
             REG_TENANT_ID INTEGER DEFAULT 0,
             CONSTRAINT PK_REG_CONTENT PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
)ENGINE NDB;

CREATE TABLE IF NOT EXISTS REG_CONTENT_HISTORY (
             REG_CONTENT_ID INTEGER NOT NULL,
             REG_CONTENT_DATA LONGBLOB,
             REG_DELETED   SMALLINT,
             REG_TENANT_ID INTEGER DEFAULT 0,
             CONSTRAINT PK_REG_CONTENT_HISTORY PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
)ENGINE NDB;

CREATE TABLE IF NOT EXISTS REG_RESOURCE (
            REG_PATH_ID         INTEGER NOT NULL,
            REG_NAME            VARCHAR(256) CHARACTER SET latin1 COLLATE latin1_bin,
            REG_VERSION         INTEGER NOT NULL AUTO_INCREMENT,
            REG_MEDIA_TYPE      VARCHAR(500),
            REG_CREATOR         VARCHAR(31) NOT NULL,
            REG_CREATED_TIME    TIMESTAMP NOT NULL,
            REG_LAST_UPDATOR    VARCHAR(31),
            REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL,
            REG_DESCRIPTION     VARCHAR(1000),
            REG_CONTENT_ID      INTEGER,
            REG_TENANT_ID INTEGER DEFAULT 0,
            REG_UUID VARCHAR(100) NOT NULL,
            CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
)ENGINE NDB;


CREATE TABLE IF NOT EXISTS REG_RESOURCE_HISTORY (
            REG_PATH_ID         INTEGER NOT NULL,
            REG_NAME            VARCHAR(256),
            REG_VERSION         INTEGER NOT NULL,
            REG_MEDIA_TYPE      VARCHAR(500),
            REG_CREATOR         VARCHAR(31) NOT NULL,
            REG_CREATED_TIME    TIMESTAMP NOT NULL,
            REG_LAST_UPDATOR    VARCHAR(31),
            REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL,
            REG_DESCRIPTION     VARCHAR(1000),
            REG_CONTENT_ID      INTEGER,
            REG_DELETED         SMALLINT,
            REG_TENANT_ID INTEGER DEFAULT 0,
            REG_UUID VARCHAR(100) NOT NULL,
            CONSTRAINT PK_REG_RESOURCE_HISTORY PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
)ENGINE NDB;


CREATE TABLE IF NOT EXISTS REG_COMMENT (
            REG_ID        INTEGER NOT NULL AUTO_INCREMENT,
            REG_COMMENT_TEXT      VARCHAR(500) NOT NULL,
            REG_USER_ID           VARCHAR(31) NOT NULL,
            REG_COMMENTED_TIME    TIMESTAMP NOT NULL,
            REG_TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT PK_REG_COMMENT PRIMARY KEY(REG_ID, REG_TENANT_ID)
)ENGINE NDB;

CREATE TABLE IF NOT EXISTS REG_RESOURCE_COMMENT (
            REG_COMMENT_ID          INTEGER NOT NULL,
            REG_VERSION             INTEGER,
            REG_PATH_ID             INTEGER,
            REG_RESOURCE_NAME       VARCHAR(256),
            REG_TENANT_ID INTEGER DEFAULT 0
)ENGINE NDB;


CREATE TABLE IF NOT EXISTS REG_RATING (
            REG_ID     INTEGER NOT NULL AUTO_INCREMENT,
            REG_RATING        INTEGER NOT NULL,
            REG_USER_ID       VARCHAR(31) NOT NULL,
            REG_RATED_TIME    TIMESTAMP NOT NULL,
            REG_TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT PK_REG_RATING PRIMARY KEY(REG_ID, REG_TENANT_ID)
)ENGINE NDB;

CREATE TABLE IF NOT EXISTS REG_RESOURCE_RATING (
            REG_RATING_ID           INTEGER NOT NULL,
            REG_VERSION             INTEGER,
            REG_PATH_ID             INTEGER,
            REG_RESOURCE_NAME       VARCHAR(256),
            REG_TENANT_ID INTEGER DEFAULT 0
)ENGINE NDB;



CREATE TABLE IF NOT EXISTS REG_TAG (
            REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
            REG_TAG_NAME       VARCHAR(500) NOT NULL,
            REG_USER_ID        VARCHAR(31) NOT NULL,
            REG_TAGGED_TIME    TIMESTAMP NOT NULL,
            REG_TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT PK_REG_TAG PRIMARY KEY(REG_ID, REG_TENANT_ID)
)ENGINE NDB;

CREATE TABLE IF NOT EXISTS REG_RESOURCE_TAG (
            REG_TAG_ID              INTEGER NOT NULL,
            REG_VERSION             INTEGER,
            REG_PATH_ID             INTEGER,
            REG_RESOURCE_NAME       VARCHAR(256),
            REG_TENANT_ID INTEGER DEFAULT 0
)ENGINE NDB;


CREATE TABLE IF NOT EXISTS REG_PROPERTY (
            REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
            REG_NAME       VARCHAR(100) NOT NULL,
            REG_VALUE        VARCHAR(1000),
            REG_TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT PK_REG_PROPERTY PRIMARY KEY(REG_ID, REG_TENANT_ID)
)ENGINE NDB;

CREATE TABLE IF NOT EXISTS REG_RESOURCE_PROPERTY (
            REG_PROPERTY_ID         INTEGER NOT NULL,
            REG_VERSION             INTEGER,
            REG_PATH_ID             INTEGER,
            REG_RESOURCE_NAME       VARCHAR(256),
            REG_TENANT_ID INTEGER DEFAULT 0
)ENGINE NDB;


-- CREATE TABLE IF NOT EXISTS REG_ASSOCIATIONS (
-- SRC_PATH_ID     INTEGER,
-- SRC_RESOURCE_NAME    VARCHAR(256),
-- SRC_VERSION     INTEGER,
-- TGT_PATH_ID     INTEGER,
-- TGT_RESOURCE_NAME    VARCHAR(256),
-- TGT_VERSION     INTEGER
-- )ENGINE NDB;
-- 



CREATE TABLE IF NOT EXISTS REG_ASSOCIATION (
            REG_ASSOCIATION_ID INTEGER AUTO_INCREMENT,
            REG_SOURCEPATH VARCHAR (750) NOT NULL,
            REG_TARGETPATH VARCHAR (750) NOT NULL,
            REG_ASSOCIATION_TYPE VARCHAR (2000) NOT NULL,
            REG_TENANT_ID INTEGER DEFAULT 0,
            PRIMARY KEY (REG_ASSOCIATION_ID, REG_TENANT_ID)
)ENGINE NDB;

CREATE TABLE IF NOT EXISTS REG_SNAPSHOT (
            REG_SNAPSHOT_ID     INTEGER NOT NULL AUTO_INCREMENT,
            REG_PATH_ID            INTEGER NOT NULL,
            REG_RESOURCE_NAME      VARCHAR(255),
            REG_RESOURCE_VIDS     LONGBLOB NOT NULL,
            REG_TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT PK_REG_SNAPSHOT PRIMARY KEY(REG_SNAPSHOT_ID, REG_TENANT_ID)
)ENGINE NDB;




-- ################################
-- USER MANAGER TABLES
-- ################################

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 NDB;


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 NDB; 

CREATE TABLE UM_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 NDB;

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, 
             PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB; 


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, 
             PRIMARY KEY (UM_ID, UM_TENANT_ID) 
)ENGINE NDB; 

-- 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, 
             PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB;

-- 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), 
             PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB; 


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, 
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB; 



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 NDB; 

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 VARCHAR(255), 
            UM_REG_EX VARCHAR(255), 
            UM_SUPPORTED SMALLINT, 
            UM_REQUIRED SMALLINT, 
            UM_DISPLAY_ORDER INTEGER,
            UM_TENANT_ID INTEGER DEFAULT 0, 
            UNIQUE(UM_DIALECT_ID, UM_CLAIM_URI, UM_TENANT_ID), 
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB; 

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, 
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB; 
    
CREATE TABLE 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, 
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB; 

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

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,
            UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID),
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB;

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 NDB;

CREATE TABLE UM_CUSTOM_USERSTORE(
            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
            UM_USERSTORE_PROPERTY VARCHAR(255) NOT NULL,
            UM_USERSTORE_VALUE VARCHAR(500) NOT NULL,
            UM_TENANT_ID INTEGER DEFAULT 0,
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB;
