USE ${database};

CREATE TABLE CMS_USERS (
	USER_ID NVARCHAR(36)  NOT NULL,
	USER_NAME NVARCHAR(64)  NOT NULL,
	USER_PASSWORD NVARCHAR(32)  NOT NULL,
	USER_DESCRIPTION NVARCHAR(255) NOT NULL,
	USER_FIRSTNAME NVARCHAR(50) NOT NULL,
	USER_LASTNAME NVARCHAR(50) NOT NULL,
	USER_EMAIL NVARCHAR(100) NOT NULL,
	USER_LASTLOGIN BIGINT NOT NULL,
	USER_FLAGS INT NOT NULL,
	USER_INFO IMAGE,
	USER_ADDRESS NVARCHAR(100) NOT NULL,
	USER_TYPE INT NOT NULL,
	PRIMARY KEY	(USER_ID), 
	UNIQUE (USER_NAME)
);

CREATE TABLE CMS_GROUPS (
	GROUP_ID NVARCHAR(36)  NOT NULL,
	PARENT_GROUP_ID NVARCHAR(36)  NOT NULL,
	GROUP_NAME NVARCHAR(64)  NOT NULL,
	GROUP_DESCRIPTION NVARCHAR(255) NOT NULL,
	GROUP_FLAGS INT NOT NULL,
	PRIMARY KEY (GROUP_ID),
	UNIQUE (GROUP_NAME)
);



CREATE TABLE CMS_GROUPUSERS (
	GROUP_ID NVARCHAR(36)  NOT NULL,
	USER_ID NVARCHAR(36)  NOT NULL,
	GROUPUSER_FLAGS INT NOT NULL,
	PRIMARY KEY (GROUP_ID,USER_ID),
	FOREIGN KEY (GROUP_ID) references CMS_GROUPS(GROUP_ID),
	FOREIGN KEY (USER_ID) references CMS_USERS(USER_ID)
);

CREATE TABLE CMS_PROJECTS (
	PROJECT_ID INT NOT NULL,
	PROJECT_NAME NVARCHAR(64)  NOT NULL,
	PROJECT_DESCRIPTION NVARCHAR(255) NOT NULL,
	PROJECT_FLAGS INT NOT NULL,
	PROJECT_TYPE INT NOT NULL,
	USER_ID NVARCHAR(36)  NOT NULL,
	GROUP_ID NVARCHAR(36)  NOT NULL, 
	MANAGERGROUP_ID NVARCHAR(36)  NOT NULL,
	TASK_ID INT NOT NULL,
	DATE_CREATED BIGINT NOT NULL,
	PRIMARY KEY (PROJECT_ID), 
	UNIQUE (PROJECT_NAME, DATE_CREATED),
	FOREIGN KEY (GROUP_ID) REFERENCES CMS_GROUPS(GROUP_ID) ,
	FOREIGN KEY (MANAGERGROUP_ID) REFERENCES CMS_GROUPS(GROUP_ID),
	FOREIGN KEY (USER_ID) references CMS_USERS(USER_ID)
);

CREATE TABLE CMS_BACKUP_PROJECTS (
	PROJECT_ID INT NOT NULL,
	PROJECT_NAME NVARCHAR(64)  NOT NULL,
	PROJECT_DESCRIPTION NVARCHAR(255) NOT NULL,
	PROJECT_TYPE INT NOT NULL,
	USER_ID NVARCHAR(36)  NOT NULL,
	GROUP_ID NVARCHAR(36)  NOT NULL,
	MANAGERGROUP_ID NVARCHAR(36)  NOT NULL,
	TASK_ID INT NOT NULL,
	DATE_CREATED BIGINT NOT NULL,	
	PUBLISH_TAG INT NOT NULL,
	PROJECT_PUBLISHDATE DATETIME,
	PROJECT_PUBLISHED_BY NVARCHAR(36)  NOT NULL,
	PROJECT_PUBLISHED_BY_NAME NVARCHAR(167),
	USER_NAME NVARCHAR(167),
	GROUP_NAME NVARCHAR(64) ,
	MANAGERGROUP_NAME NVARCHAR(64) ,	
	PRIMARY KEY (PUBLISH_TAG)
);

CREATE TABLE CMS_PROJECTRESOURCES (
	PROJECT_ID INT NOT NULL,
	RESOURCE_PATH nvarchar(440) NOT NULL,
	PRIMARY KEY (PROJECT_ID, RESOURCE_PATH)
);

CREATE TABLE CMS_BACKUP_PROJECTRESOURCES (
	PUBLISH_TAG INT NOT NULL,
	PROJECT_ID INT NOT NULL,
	RESOURCE_PATH NVARCHAR(440) NOT NULL,
	PRIMARY KEY (PUBLISH_TAG, PROJECT_ID, RESOURCE_PATH)
);

CREATE TABLE CMS_OFFLINE_PROPERTYDEF (
	PROPERTYDEF_ID NVARCHAR(36)  NOT NULL, 
	PROPERTYDEF_NAME NVARCHAR(128)  NOT NULL,
	PRIMARY KEY (PROPERTYDEF_ID), 
	UNIQUE (PROPERTYDEF_ID),
	UNIQUE (PROPERTYDEF_NAME)
);
                           
CREATE TABLE CMS_ONLINE_PROPERTYDEF (
	PROPERTYDEF_ID NVARCHAR(36)  NOT NULL, 
	PROPERTYDEF_NAME NVARCHAR(128)  NOT NULL,
	PRIMARY KEY (PROPERTYDEF_ID), 
	UNIQUE (PROPERTYDEF_ID),
	UNIQUE (PROPERTYDEF_NAME)	
);
                                        
CREATE TABLE CMS_BACKUP_PROPERTYDEF (
	PROPERTYDEF_ID NVARCHAR(36)  NOT NULL, 
	PROPERTYDEF_NAME NVARCHAR(128)  NOT NULL,
	PRIMARY KEY (PROPERTYDEF_ID), 
	UNIQUE (PROPERTYDEF_ID),
	UNIQUE (PROPERTYDEF_NAME)	
);

CREATE TABLE CMS_OFFLINE_PROPERTIES (
	PROPERTY_ID NVARCHAR(36)  NOT NULL,
	PROPERTYDEF_ID NVARCHAR(36)  NOT NULL,
	PROPERTY_MAPPING_ID NVARCHAR(36)  NOT NULL,
	PROPERTY_MAPPING_TYPE INT NOT NULL,
	PROPERTY_VALUE NTEXT NOT NULL,
	PRIMARY KEY (PROPERTY_ID)
);
                                         
CREATE TABLE CMS_ONLINE_PROPERTIES (
	PROPERTY_ID NVARCHAR(36)  NOT NULL,
	PROPERTYDEF_ID NVARCHAR(36)  NOT NULL,
	PROPERTY_MAPPING_ID NVARCHAR(36)  NOT NULL,
	PROPERTY_MAPPING_TYPE INT NOT NULL,
	PROPERTY_VALUE NTEXT NOT NULL,
	PRIMARY KEY(PROPERTY_ID)
);
                                                                              
CREATE TABLE CMS_BACKUP_PROPERTIES (
	BACKUP_ID NVARCHAR(36)  NOT NULL,
	PROPERTY_ID NVARCHAR(36)  NOT NULL,
	PROPERTYDEF_ID NVARCHAR(36)  NOT NULL,
	PROPERTY_MAPPING_ID NVARCHAR(36)  NOT NULL,
	PROPERTY_MAPPING_TYPE INT NOT NULL,
	PROPERTY_VALUE NTEXT NOT NULL,
	PUBLISH_TAG INT,
	VERSION_ID	INT NOT NULL,
	PRIMARY KEY(PROPERTY_ID)
);
	
CREATE TABLE CMS_SYSTEMID (
	TABLE_KEY NVARCHAR(255) NOT NULL,
	ID INT NOT NULL,
	PRIMARY KEY(TABLE_KEY)
);
                                                                                 
CREATE TABLE CMS_TASK (
	AUTOFINISH INT,
	ENDTIME DATETIME,
	ESCALATIONTYPEREF INT,
	ID INT NOT NULL,
	INITIATORUSERREF NVARCHAR(36) ,
	MILESTONEREF INT,
	NAME NVARCHAR(254),
	ORIGINALUSERREF NVARCHAR(36) ,
	AGENTUSERREF NVARCHAR(36) ,
	PARENT INT,
	PERCENTAGE NVARCHAR(50),
	PERMISSION NVARCHAR(50),
	PRIORITYREF INT DEFAULT '2',
	ROLEREF NVARCHAR(36) ,
	ROOT INT,
	STARTTIME DATETIME,
	STATE INT,
	TASKTYPEREF INT,
	TIMEOUT DATETIME,
	WAKEUPTIME DATETIME,
	HTMLLINK NVARCHAR(254),
	ESTIMATETIME INT DEFAULT '86400',
	PRIMARY KEY (ID)
);
                                                                                 
CREATE TABLE CMS_TASKTYPE (
	AUTOFINISH INT,
	ESCALATIONTYPEREF INT,
	HTMLLINK NVARCHAR(254),
	ID INT NOT NULL,
	NAME NVARCHAR(50),
	PERMISSION NVARCHAR(50),
	PRIORITYREF INT,
	ROLEREF NVARCHAR(36) ,
	PRIMARY KEY (ID)
);
                                                                                 
CREATE TABLE CMS_TASKLOG (
	COMENT NTEXT,
	EXTERNALUSERNAME NVARCHAR(254),
	ID INT NOT NULL,
	STARTTIME DATETIME,
	TASKREF INT,
	USERREF NVARCHAR(36)  NOT NULL,
	TYPE INT DEFAULT '0',
	PRIMARY KEY (ID)
);
                                         
CREATE TABLE CMS_TASKPAR (
	ID INT NOT NULL ,
	PARNAME NVARCHAR(50),
	PARVALUE NVARCHAR(50),
	REF INT,
	PRIMARY KEY (ID)
);

CREATE TABLE CMS_ONLINE_ACCESSCONTROL (
	RESOURCE_ID NVARCHAR(36)  NOT NULL,
	PRINCIPAL_ID NVARCHAR(36)  NOT NULL,
	ACCESS_ALLOWED INT,
	ACCESS_DENIED INT,
	ACCESS_FLAGS INT,
	PRIMARY KEY (RESOURCE_ID, PRINCIPAL_ID),
);

CREATE TABLE CMS_OFFLINE_ACCESSCONTROL (
	RESOURCE_ID NVARCHAR(36)  NOT NULL,
	PRINCIPAL_ID NVARCHAR(36)  NOT NULL,
	ACCESS_ALLOWED INT,
	ACCESS_DENIED INT,
	ACCESS_FLAGS INT,
	PRIMARY KEY (RESOURCE_ID, PRINCIPAL_ID)
);

CREATE TABLE CMS_PUBLISH_HISTORY (
	HISTORY_ID NVARCHAR(36)  NOT NULL,
	PUBLISH_TAG INT NOT NULL,
	STRUCTURE_ID NVARCHAR(36)  NOT NULL,
	RESOURCE_ID NVARCHAR(36)  NOT NULL,
	RESOURCE_PATH NVARCHAR(440) NOT NULL,
	RESOURCE_STATE INT NOT NULL,
	RESOURCE_TYPE INT NOT NULL,
	SIBLING_COUNT INT NOT NULL,
	PRIMARY KEY (HISTORY_ID, PUBLISH_TAG, STRUCTURE_ID, RESOURCE_ID)
);

CREATE TABLE CMS_STATICEXPORT_LINKS (
	LINK_ID NVARCHAR(36)  NOT NULL,
	LINK_RFS_PATH NVARCHAR(440) NOT NULL,
	LINK_TYPE INT NOT NULL,
	LINK_PARAMETER TEXT,
	LINK_TIMESTAMP BIGINT,	
	PRIMARY KEY (LINK_ID)	
);

CREATE TABLE CMS_OFFLINE_STRUCTURE (
	STRUCTURE_ID NVARCHAR(36)  NOT NULL,
	RESOURCE_ID NVARCHAR(36)  NOT NULL,
	PARENT_ID NVARCHAR(36)  NOT NULL,
	RESOURCE_PATH NVARCHAR(440) NOT NULL,
	STRUCTURE_STATE INT NOT NULL,
	DATE_RELEASED BIGINT NOT NULL,
	DATE_EXPIRED BIGINT NOT NULL,
	PRIMARY KEY (STRUCTURE_ID)
);