-- ----------------------------------------------------------------------
-- SQL create script
-- ----------------------------------------------------------------------

DROP DATABASE IF EXISTS `JobTrak2Data`;

CREATE DATABASE `JobTrak2Data`
  CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `JobTrak2Data`.`CLIENT` (
  `CLIENT_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `CLIENT_NAME` VARCHAR(50) NULL,
  `DEACTIVE` TINYINT(1) NOT NULL,
  PRIMARY KEY (`CLIENT_ID`),
  INDEX `CLIENT_ID` (`CLIENT_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`CODE_COST_CENTRE` (
  `COST_CENTRE_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `COST_CENTRE_NAME` VARCHAR(50) NULL,
  `COST_CENTRE_CODE` VARCHAR(50) NULL,
  `DEACTIVE` TINYINT(1) NOT NULL,
  PRIMARY KEY (`COST_CENTRE_ID`),
  INDEX `COST_CENTER_CODE` (`COST_CENTRE_NAME`),
  INDEX `COST_CENTRE_CODE` (`COST_CENTRE_CODE`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`CODE_DOCUMENT_TYPE` (
  `DOCUMENT_TYPE_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `DOCUMENT_TYPE` VARCHAR(50) NULL,
  `DOCUMENT_TYPE_DESC` LONGTEXT NULL,
  `DEACTIVE` TINYINT(1) NOT NULL,
  PRIMARY KEY (`DOCUMENT_TYPE_ID`),
  INDEX `CODE_DOCUMENT_TYPEDOCUMENT_TYPE` (`DOCUMENT_TYPE`),
  INDEX `DOCUMENT_TYPE_ID` (`DOCUMENT_TYPE_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`CODE_GROUP` (
  `GROUP_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `GROUP_NAME` VARCHAR(50) NULL,
  PRIMARY KEY (`GROUP_ID`),
  INDEX `GROUP_ID` (`GROUP_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`CODE_PROJECT_CATEGORY` (
  `PROJECT_CATEGORY_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `CODE_PROJECT_CATEGORY` VARCHAR(50) NULL,
  `CODE_PROJECT_CATEGORY_DESC` LONGTEXT NULL,
  `DEACTIVE` TINYINT(1) NOT NULL,
  PRIMARY KEY (`PROJECT_CATEGORY_ID`),
  INDEX `CODE_PROJECT_CATEGORY` (`CODE_PROJECT_CATEGORY`),
  INDEX `PROJECT_category_ID` (`PROJECT_CATEGORY_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`CODE_PROJECT_STATUS` (
  `PROJECT_STATUS_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `PROJECT_STATUS_NAME` VARCHAR(50) NULL,
  `PROJECT_STATUS_DESC` LONGTEXT NULL,
  `DEACTIVE` TINYINT(1) NOT NULL,
  PRIMARY KEY (`PROJECT_STATUS_ID`),
  INDEX `PROJECT_STATUS_ID` (`PROJECT_STATUS_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`CODE_ROLE` (
  `ROLE_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `ROLE_NAME` VARCHAR(50) NULL,
  `ROLE_DESC` LONGTEXT NULL,
  `DEACTIVE` TINYINT(1) NOT NULL,
  PRIMARY KEY (`ROLE_ID`),
  INDEX `ROLE_ID` (`ROLE_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`CODE_SUPPORTING_DATA_TYPE` (
  `SUPPORTING_DATA_TYPE_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `SUPPORTING_DATA_TYPE` VARCHAR(50) NULL,
  `SUPPORTING_DATA_TYPE_DESC` LONGTEXT NULL,
  `DEACTIVE` TINYINT(1) NOT NULL,
  PRIMARY KEY (`SUPPORTING_DATA_TYPE_ID`),
  INDEX `CODE_SUPPORTING_DATA_TYPESUPPORTING_DATA_TYPE` (`SUPPORTING_DATA_TYPE`),
  INDEX `SUPPORTING_DATA_TYPE_ID` (`SUPPORTING_DATA_TYPE_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`CODE_TASK_TYPE` (
  `TASK_TYPE_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `TASK_TYPE_NAME` VARCHAR(50) NULL,
  `TASK_TYPE_DESC` LONGTEXT NULL,
  `DEACTIVE` TINYINT(1) NOT NULL,
  PRIMARY KEY (`TASK_TYPE_ID`),
  INDEX `TASK_TYPE_ID` (`TASK_TYPE_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`CODE_USER_TYPE` (
  `USER_TYPE_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `USER_TYPE` VARCHAR(50) NULL,
  `USER_TYPE_DESC` LONGTEXT NULL,
  `DEACTIVE` TINYINT(1) NOT NULL,
  PRIMARY KEY (`USER_TYPE_ID`),
  INDEX `USER_TYPE_ID` (`USER_TYPE_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`DAY_SUMMARY` (
  `USER_ID` INTEGER(10) NULL,
  `WORK_DAY` DATETIME NULL,
  `START_TIME` DOUBLE(15, 5) NULL,
  `END_TIME` DOUBLE(15, 5) NULL,
  `BREAKS` DOUBLE(15, 5) NULL,
  `ON_CALL` TINYINT(1) NOT NULL,
  PRIMARY KEY (`USER_ID`, `WORK_DAY`),
  INDEX `USER_ID` (`USER_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`PROJECT` (
  `PROJECT_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `PROJECT_NAME` VARCHAR(100) NULL,
  `PROJECT_STATUS` INTEGER(10) NULL,
  `PROJECT_CLIENT` INTEGER(10) NULL,
  `PROJECT_COST_CENTRE` INTEGER(10) NULL,
  `PROJECT_DESCRIPTION` LONGTEXT NULL,
  `DATE_CREATED` DATETIME NULL,
  `DATE_REQUIRED` DATETIME NULL,
  `DATE_COMPLETED` DATETIME NULL,
  `PROJECT_FOLDER` LONGTEXT NULL,
  `PROJECT_CATAGORY_ID` INTEGER(10) NULL,
  PRIMARY KEY (`PROJECT_ID`),
  INDEX `{0F807EE8-E149-450E-A284-BEE27AD46016}` (`PROJECT_COST_CENTRE`),
  INDEX `{A5D66869-473C-4164-83C3-AF1D1FBB2E04}` (`PROJECT_CLIENT`),
  INDEX `{DFCA9C76-78B3-42D8-AE57-3568186DC0FB}` (`PROJECT_STATUS`),
  INDEX `{FC35E407-3071-4FE3-8DD0-A743E8225CDA}` (`PROJECT_CATAGORY_ID`),
  INDEX `PROJECT_CATAGORY_ID` (`PROJECT_CATAGORY_ID`),
  INDEX `PROJECT_ID` (`PROJECT_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`REPORT` (
  `ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `REPORT_TITLE` VARCHAR(150) NULL,
  `REPORT_NAME` VARCHAR(100) NULL,
  `REPORT_DESC` LONGTEXT NULL,
  `DATE_RANGE` TINYINT(1) NOT NULL,
  `FROM_DATE` VARCHAR(50) NULL,
  `TO_DATE` VARCHAR(50) NULL,
  `STAFF_MEMBER` TINYINT(1) NOT NULL,
  `STAFF_MEMBER_FIELD` VARCHAR(50) NULL,
  `PROJECT` TINYINT(1) NOT NULL,
  `PROJECT_FIELD` VARCHAR(50) NULL,
  `TASK` TINYINT(1) NOT NULL,
  `TASK_FIELD` VARCHAR(50) NULL,
  `COSTCENTER` TINYINT(1) NOT NULL,
  `COSTCENTER_FIELD` VARCHAR(50) NULL,
  `SQL` VARCHAR(255) NULL,
  `OPENARGS` VARCHAR(255) NULL,
  PRIMARY KEY (`ID`),
  INDEX `ID` (`ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`SCOPE_CHANGE` (
  `CHANGE_DATE` DATETIME NULL,
  `TASK_OR_PROJECT` VARCHAR(1) NULL,
  `TABLE_ID` INTEGER(10) NULL,
  `FIELD` VARCHAR(255) NULL,
  `OLD_VALUE` VARCHAR(50) NULL,
  `NEW_VALUE` VARCHAR(50) NULL,
  `REASON` LONGTEXT NULL,
  PRIMARY KEY (`CHANGE_DATE`, `TASK_OR_PROJECT`, `TABLE_ID`),
  INDEX `TABLE_ID` (`TABLE_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`TASK` (
  `TASK_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `PROJECT_ID` INTEGER(10) NULL,
  `TASK_TYPE` INTEGER(10) NULL,
  `TASK_NAME` VARCHAR(150) NULL,
  `TASK_DESC` LONGTEXT NULL,
  `TASK_CLIENT` INTEGER(10) NULL,
  `TASK_COST_CENTRE` INTEGER(10) NULL,
  `TASK_DATE_REQUESTED` DATETIME NULL,
  `TASK_DATE_STARTED` DATETIME NULL,
  `TASK_DATE_COMPLETED` DATETIME NULL,
  `TASK_DATE_REQUIRED` DATETIME NULL,
  `TASK_TIME_ESTIMATE` INTEGER(10) NULL,
  `SCHEDULE` VARCHAR(50) NULL,
  `TASK_ESTIMATED_COST` DECIMAL(19, 4) NULL,
  PRIMARY KEY (`TASK_ID`),
  INDEX `{336CED07-732C-4497-A392-F07ECE7BA2F8}` (`TASK_COST_CENTRE`),
  INDEX `{71B58F71-3884-478B-A436-5815C99043B4}` (`TASK_CLIENT`),
  INDEX `CODE_TASK_TYPETASK` (`TASK_TYPE`),
  INDEX `PROJECT_ID` (`PROJECT_ID`),
  INDEX `PROJECTTASK` (`PROJECT_ID`),
  INDEX `TASK_ID` (`TASK_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`TASK_DIARY` (
  `TASK_ID` INTEGER(10) NULL,
  `USER_ID` INTEGER(10) NULL,
  `WORK_DAY` DATETIME NULL,
  `DIARY` LONGTEXT NULL,
  PRIMARY KEY (`TASK_ID`, `USER_ID`, `WORK_DAY`),
  INDEX `TASKTASK_DIARY` (`TASK_ID`),
  INDEX `USER_TASKTASK_DIARY` (`TASK_ID`),
  INDEX `USER_TASKTASK_DIARY` (`USER_ID`),
  INDEX `WORK_DAY` (`WORK_DAY`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`TASK_DOCUMENT` (
  `DOCUMENT_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `TASK_ID` INTEGER(10) NULL,
  `DOCUMENT_LOCATION` LONGTEXT NULL,
  `DOCUMENT_TYPE` INTEGER(10) NULL,
  `INLCUDE` TINYINT(1) NOT NULL,
  PRIMARY KEY (`DOCUMENT_ID`),
  INDEX `{6B7A1091-A46F-4161-8D28-E6BFB4168FFE}` (`DOCUMENT_TYPE`),
  INDEX `OCUMENT_ID` (`DOCUMENT_ID`),
  INDEX `TASK_ID` (`TASK_ID`),
  INDEX `TASKTASK_DOCUMENT` (`TASK_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`TASK_SUPPORTING_DATA` (
  `SUPPORTING_DATA__ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `TASK_ID` INTEGER(10) NULL,
  `SUPPORTING_DATA_TYPE` INTEGER(10) NULL,
  `SUPPORTING_DATA_TITLE` VARCHAR(150) NULL,
  `SUPPORTING_DATA_TEXT` LONGTEXT NULL,
  `AUTHOR` INTEGER(10) NULL,
  PRIMARY KEY (`SUPPORTING_DATA__ID`),
  INDEX `{BA37FA4F-07A9-467F-9FCD-4AB92840B0EA}` (`SUPPORTING_DATA_TYPE`),
  INDEX `SUPPORT_ID` (`SUPPORTING_DATA__ID`),
  INDEX `TASK_ID` (`TASK_ID`),
  INDEX `TASKTASK_SUPPORTING_DATA` (`TASK_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`TASK_TEMPLATE` (
  `TASK_TEMPLATE_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `TASK_TEMPLATE_NAME` VARCHAR(50) NULL,
  `USER_ID` INTEGER(10) NULL,
  `PROJECT_ID` INTEGER(10) NULL,
  `TASK_TYPE` INTEGER(10) NULL,
  `TASK_NAME` VARCHAR(50) NULL,
  `TASK_DESC` LONGTEXT NULL,
  `TASK_CLIENT` INTEGER(10) NULL,
  `TASK_COST_CENTRE` INTEGER(10) NULL,
  `DEFAULT_TIME` INTEGER(10) NULL,
  PRIMARY KEY (`TASK_TEMPLATE_ID`),
  INDEX `PROJECT_ID` (`PROJECT_ID`),
  INDEX `TASK_TEMPLATE_ID` (`TASK_TEMPLATE_ID`),
  INDEX `USER_ID` (`USER_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`TASK_TEST_CASE` (
  `TEST_CASE_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `TASK_ID` INTEGER(10) NULL,
  `MODULE` VARCHAR(50) NULL,
  `FUNCTION` VARCHAR(50) NULL,
  `ENVIRONMENT` VARCHAR(5) NULL,
  PRIMARY KEY (`TEST_CASE_ID`),
  INDEX `JOB_ID` (`TEST_CASE_ID`),
  INDEX `JOE_ID` (`TASK_ID`),
  INDEX `TASKTASK_TEST_CASE` (`TASK_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`TASK_TEST_CASE_ITEM` (
  `TEST_CASE_ITEM_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `TEST_CASE_ID` INTEGER(10) NULL,
  `REQUIREMENT` LONGTEXT NULL,
  `SETUP` LONGTEXT NULL,
  `RESULT` LONGTEXT NULL,
  `INCLUDE` TINYINT(1) NOT NULL,
  PRIMARY KEY (`TEST_CASE_ITEM_ID`),
  INDEX `TASK_TEST_CASETASK_TEST_CASE_ITEM` (`TEST_CASE_ID`),
  INDEX `TEST_CASE_ID` (`TEST_CASE_ID`),
  INDEX `TEST_CASE_ITEM_ID` (`TEST_CASE_ITEM_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`TASK_TIME` (
  `TASK_TIME_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `TASK_ID` INTEGER(10) NULL,
  `USER_ID` INTEGER(10) NULL,
  `WORK_DAY` DATETIME NULL,
  `CLIENT` INTEGER(10) NULL,
  `COST_CENTRE` INTEGER(10) NULL,
  `TASK_TIME` INTEGER(10) NULL,
  `OVER_TIME` TINYINT(1) NOT NULL,
  PRIMARY KEY (`TASK_TIME_ID`),
  INDEX `{FDF6109A-F141-4090-887E-698AAC7CE73B}` (`USER_ID`),
  INDEX `{FDF6109A-F141-4090-887E-698AAC7CE73B}` (`WORK_DAY`),
  INDEX `CLIENTTASK_TIME` (`CLIENT`),
  INDEX `CODE_COST_CENTRETASK_TIME` (`COST_CENTRE`),
  INDEX `TASK_ID` (`TASK_ID`),
  INDEX `TASK_TIME` (`TASK_TIME`),
  INDEX `TASK_TIME_ID` (`TASK_TIME_ID`),
  INDEX `TASKTASK_TIME` (`TASK_ID`),
  INDEX `USER_ID` (`USER_ID`),
  INDEX `USER_TASKTASK_TIME` (`TASK_ID`),
  INDEX `USER_TASKTASK_TIME` (`USER_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`USER_DETAILS` (
  `USER_ID` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(50) NULL,
  `LOGIN` VARCHAR(15) NULL,
  `DEACTIVE` TINYINT(1) NOT NULL,
  `RATE` DECIMAL(19, 4) NULL,
  `USER_TYPE_ID` INTEGER(10) NULL,
  `EMPLOYEE_NUMBER` VARCHAR(50) NULL,
  PRIMARY KEY (`USER_ID`),
  INDEX `{E8650CA3-D7BF-4BD3-92A0-F3A45EB69D79}` (`USER_TYPE_ID`),
  INDEX `STAFF_ID` (`USER_ID`),
  INDEX `USER_TYPE_ID` (`USER_TYPE_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`USER_PROJECT` (
  `PROJECT_ID` INTEGER(10) NULL,
  `USER_ID` INTEGER(10) NULL,
  `ROLE_ID` INTEGER(10) NULL,
  `DEACTIVE` TINYINT(1) NOT NULL,
  PRIMARY KEY (`PROJECT_ID`, `USER_ID`),
  INDEX `{CBD842D6-CE89-498A-9973-00E439753DC1}` (`USER_ID`),
  INDEX `PROJECT_ID` (`PROJECT_ID`),
  INDEX `PROJECTUSER_PROJECT` (`PROJECT_ID`),
  INDEX `ROLE_ID` (`ROLE_ID`)
)
ENGINE = INNODB;

CREATE TABLE `JobTrak2Data`.`USER_TASK` (
  `TASK_ID` INTEGER(10) NULL,
  `USER_ID` INTEGER(10) NULL,
  `DEACTIVE` TINYINT(1) NOT NULL,
  PRIMARY KEY (`TASK_ID`, `USER_ID`),
  INDEX `{B55EDAE7-0857-42EC-B2D0-58B35D3E1D40}` (`USER_ID`),
  INDEX `PROJECT_ID` (`TASK_ID`),
  INDEX `TASKUSER_TASK` (`TASK_ID`)
)
ENGINE = INNODB;