-- MySQL Script generated by MySQL Workbench
-- Wed 08 Nov 2017 01:13:22 AM MST
-- Model: TaskFloor Database    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema taskfloor
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema taskfloor
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `taskfloor` DEFAULT CHARACTER SET utf8 ;
USE `taskfloor` ;

-- -----------------------------------------------------
-- Table `taskfloor`.`tasks`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `taskfloor`.`tasks` ;

CREATE TABLE IF NOT EXISTS `taskfloor`.`tasks` (
  `taskid` INT NOT NULL AUTO_INCREMENT,
  `tasktitle` VARCHAR(255) NOT NULL,
  `taskdesc` MEDIUMTEXT NOT NULL,
  `taskassignedon` DATETIME NULL,
  `taskdueby` DATETIME NULL,
  `tasknotes` TEXT NULL,
  `taskcreatoruid` INT NOT NULL,
  `deleted` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`taskid`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `taskfloor`.`taskstatus`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `taskfloor`.`taskstatus` ;

CREATE TABLE IF NOT EXISTS `taskfloor`.`taskstatus` (
  `statusid` INT NOT NULL,
  `status` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`statusid`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `taskfloor`.`assigned_tasks`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `taskfloor`.`assigned_tasks` ;

CREATE TABLE IF NOT EXISTS `taskfloor`.`assigned_tasks` (
  `taskid` INT NOT NULL,
  `userid` INT NOT NULL,
  `starttime` DATETIME NULL,
  `endtime` DATETIME NULL,
  `statusid` INT NOT NULL,
  PRIMARY KEY (`taskid`, `userid`),
  INDEX `fk_tasks_has_users_tasks1_idx` (`taskid` ASC),
  INDEX `fk_assigned_tasks_taskstatus1_idx` (`statusid` ASC),
  CONSTRAINT `fk_tasks_has_users_tasks1`
    FOREIGN KEY (`taskid`)
    REFERENCES `taskfloor`.`tasks` (`taskid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_assigned_tasks_taskstatus1`
    FOREIGN KEY (`statusid`)
    REFERENCES `taskfloor`.`taskstatus` (`statusid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `taskfloor`.`messages`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `taskfloor`.`messages` ;

CREATE TABLE IF NOT EXISTS `taskfloor`.`messages` (
  `messageid` INT NOT NULL AUTO_INCREMENT,
  `messagetext` TEXT NOT NULL,
  `messagedate` DATETIME NOT NULL,
  `to` INT NULL,
  `from` INT NOT NULL,
  `deleted` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`messageid`),
  UNIQUE INDEX `messageid_UNIQUE` (`messageid` ASC))
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `taskfloor`.`taskstatus`
-- -----------------------------------------------------
START TRANSACTION;
USE `taskfloor`;
INSERT INTO `taskfloor`.`taskstatus` (`statusid`, `status`) VALUES (0, 'Not started');
INSERT INTO `taskfloor`.`taskstatus` (`statusid`, `status`) VALUES (1, 'Working');
INSERT INTO `taskfloor`.`taskstatus` (`statusid`, `status`) VALUES (2, 'Finished');
INSERT INTO `taskfloor`.`taskstatus` (`statusid`, `status`) VALUES (3, 'Paused, not complete');
INSERT INTO `taskfloor`.`taskstatus` (`statusid`, `status`) VALUES (4, 'Problem encountered, cannot continue');

COMMIT;