-- MySQL Script generated by MySQL Workbench
-- 08/10/16 16:31:17
-- Model: New Model    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 vacationplaner
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema vacationplaner
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `vacationplaner` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `vacationplaner` ;

-- -----------------------------------------------------
-- Table `vacationplaner`.`vacationGroup`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `vacationplaner`.`vacationGroup` ;

CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationGroup` (
  `groupID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `groupName` VARCHAR(45) NULL COMMENT '',
  PRIMARY KEY (`groupID`)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vacationplaner`.`vacationUser`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `vacationplaner`.`vacationUser` ;

CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationUser` (
  `userID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `userLogin` VARCHAR(45) NOT NULL COMMENT '',
  `userPassword` VARCHAR(60) NULL COMMENT '',
  `userNameFirst` VARCHAR(45) NOT NULL COMMENT '',
  `userNameLast` VARCHAR(45) NOT NULL COMMENT '',
  `userMailAddress` VARCHAR(45) NOT NULL COMMENT '',
  `userVacationDaysTotal` INT NOT NULL COMMENT '',
  `groupID` INT NOT NULL COMMENT '',
  PRIMARY KEY (`userID`)  COMMENT '',
  CONSTRAINT `fk_user_group`
    FOREIGN KEY (`groupID`)
    REFERENCES `vacationplaner`.`vacationGroup` (`groupID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE UNIQUE INDEX `userID_UNIQUE` ON `vacationplaner`.`vacationUser` (`userID` ASC)  COMMENT '';

CREATE INDEX `fk_user_group_idx` ON `vacationplaner`.`vacationUser` (`groupID` ASC)  COMMENT '';


-- -----------------------------------------------------
-- Table `vacationplaner`.`vacationRequest`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `vacationplaner`.`vacationRequest` ;

CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationRequest` (
  `vacationRequestID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `userID` INT NOT NULL COMMENT '',
  `vacationRequestDateFrom` DATE NOT NULL COMMENT '',
  `vacationRequestDateTo` DATE NOT NULL COMMENT '',
  `vacationRequestTotalDays` INT NOT NULL COMMENT '',
  `vacationRequestDescription` VARCHAR(45) NULL COMMENT '',
  `vacationRequestStatus` VARCHAR(45) NOT NULL COMMENT '',
  `vacationRequestRequestDate` DATETIME NOT NULL COMMENT '',
  `vacationRequestStatusUpdateDate` DATE NULL COMMENT '',
  `vacationRequestStatusUpdateReason` VARCHAR(255) NULL COMMENT '',
  PRIMARY KEY (`vacationRequestID`)  COMMENT '',
  CONSTRAINT `fk_request_user`
    FOREIGN KEY (`userID`)
    REFERENCES `vacationplaner`.`vacationUser` (`userID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `x_idx` ON `vacationplaner`.`vacationRequest` (`userID` ASC)  COMMENT '';


-- -----------------------------------------------------
-- Table `vacationplaner`.`vacationDepartment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `vacationplaner`.`vacationDepartment` ;

CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationDepartment` (
  `departmentID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `departmentName` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL COMMENT '',
  `departmentColor` VARCHAR(45) NOT NULL COMMENT '',
  `departmentMinStock` INT NOT NULL COMMENT '',
  PRIMARY KEY (`departmentID`)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vacationplaner`.`statutoryHolidays`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `vacationplaner`.`statutoryHolidays` ;

CREATE TABLE IF NOT EXISTS `vacationplaner`.`statutoryHolidays` (
  `statutoryHolidaysID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `statutoryHolidaysName` VARCHAR(45) NOT NULL COMMENT '',
  `statutoryHolidaysDate` DATE NOT NULL COMMENT '',
  PRIMARY KEY (`statutoryHolidaysID`)  COMMENT '')
ENGINE = InnoDB;

CREATE UNIQUE INDEX `statutoryHolidaysName_UNIQUE` ON `vacationplaner`.`statutoryHolidays` (`statutoryHolidaysName` ASC)  COMMENT '';


-- -----------------------------------------------------
-- Table `vacationplaner`.`schoolHolidays`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `vacationplaner`.`schoolHolidays` ;

CREATE TABLE IF NOT EXISTS `vacationplaner`.`schoolHolidays` (
  `schoolHolidaysID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `schoolHolidaysName` VARCHAR(45) NOT NULL COMMENT '',
  `schoolHolidaysDateFrom` DATE NOT NULL COMMENT '',
  `schoolHolidaysDateTo` DATE NOT NULL COMMENT '',
  PRIMARY KEY (`schoolHolidaysID`)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vacationplaner`.`vacationUserAccountHistory`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `vacationplaner`.`vacationUserAccountHistory` ;

CREATE TABLE IF NOT EXISTS `vacationplaner`.`vacationUserAccountHistory` (
  `userAccountHistoryID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `userAccountID` INT NULL COMMENT '',
  `userID` INT NOT NULL COMMENT '',
  `userVacationDaysLeft` INT NOT NULL COMMENT '',
  `userVacationDaysUnconfirmed` INT NOT NULL COMMENT '',
  `userVacationLastUpdated` DATETIME NOT NULL COMMENT '',
  PRIMARY KEY (`userAccountHistoryID`)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vacationplaner`.`deputyOfDepartment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `vacationplaner`.`deputyOfDepartment` ;

CREATE TABLE IF NOT EXISTS `vacationplaner`.`deputyOfDepartment` (
  `deputyOfDepartmentID` INT NOT NULL COMMENT '',
  PRIMARY KEY (`deputyOfDepartmentID`)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vacationplaner`.`userAccount`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `vacationplaner`.`userAccount` ;

CREATE TABLE IF NOT EXISTS `vacationplaner`.`userAccount` (
  `userAccountID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `userAccountDaysLeft` INT NOT NULL COMMENT '',
  `userAccountDaysUnconfirmed` INT NOT NULL COMMENT '',
  `userAccountLastUpdated` DATETIME NOT NULL COMMENT '',
  `userID` INT NOT NULL COMMENT '',
  PRIMARY KEY (`userAccountID`)  COMMENT '',
  CONSTRAINT `fk_userAccount_vacationUser1`
    FOREIGN KEY (`userID`)
    REFERENCES `vacationplaner`.`vacationUser` (`userID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE UNIQUE INDEX `userAccountID_UNIQUE` ON `vacationplaner`.`userAccount` (`userAccountID` ASC)  COMMENT '';

CREATE INDEX `fk_userAccount_vacationUser1_idx` ON `vacationplaner`.`userAccount` (`userID` ASC)  COMMENT '';


-- -----------------------------------------------------
-- Table `vacationplaner`.`responsibleOfDepartment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `vacationplaner`.`responsibleOfDepartment` ;

CREATE TABLE IF NOT EXISTS `vacationplaner`.`responsibleOfDepartment` (
  `responsibleOfDepartmentID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  PRIMARY KEY (`responsibleOfDepartmentID`)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vacationplaner`.`userDepartment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `vacationplaner`.`userDepartment` ;

CREATE TABLE IF NOT EXISTS `vacationplaner`.`userDepartment` (
  `userDepartmentID` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `departmentID` INT NOT NULL COMMENT '',
  `responsibleOfDepartmentID` INT NULL COMMENT '',
  `deputyOfDepartment` INT NULL COMMENT '',
  `userID` INT NOT NULL COMMENT '',
  PRIMARY KEY (`userDepartmentID`)  COMMENT '',
  CONSTRAINT `fk_userDepartment_vacationUser1`
    FOREIGN KEY (`userID`)
    REFERENCES `vacationplaner`.`vacationUser` (`userID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_userDepartment_department`
    FOREIGN KEY (`departmentID`)
    REFERENCES `vacationplaner`.`vacationDepartment` (`departmentID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_userDepartment_deputy`
    FOREIGN KEY (`deputyOfDepartment`)
    REFERENCES `vacationplaner`.`deputyOfDepartment` (`deputyOfDepartmentID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_userDepartment_responsible`
    FOREIGN KEY (`responsibleOfDepartmentID`)
    REFERENCES `vacationplaner`.`responsibleOfDepartment` (`responsibleOfDepartmentID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE UNIQUE INDEX `departmentID_UNIQUE` ON `vacationplaner`.`userDepartment` (`departmentID` ASC)  COMMENT '';

CREATE INDEX `fk_userDepartment_vacationUser1_idx` ON `vacationplaner`.`userDepartment` (`userID` ASC)  COMMENT '';

CREATE INDEX `fk_userDepartment_deputy_idx` ON `vacationplaner`.`userDepartment` (`deputyOfDepartment` ASC)  COMMENT '';

CREATE INDEX `fk_userDepartment_responsible_idx` ON `vacationplaner`.`userDepartment` (`responsibleOfDepartmentID` ASC)  COMMENT '';

USE `vacationplaner`;

DELIMITER $$

USE `vacationplaner`$$
DROP TRIGGER IF EXISTS `vacationplaner`.`vacationUser_BEFORE_UPDATE` $$
USE `vacationplaner`$$
CREATE DEFINER = CURRENT_USER TRIGGER `vacationplaner`.`vacationUser_BEFORE_UPDATE` BEFORE UPDATE ON `vacationUser` FOR EACH ROW
BEGIN
    INSERT INTO vacationUserHistory SELECT * FROM vacationUser WHERE userID = NEW.userID;
END
;$$


DELIMITER ;

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

-- -----------------------------------------------------
-- Data for table `vacationplaner`.`vacationGroup`
-- -----------------------------------------------------
START TRANSACTION;
USE `vacationplaner`;
INSERT INTO `vacationplaner`.`vacationGroup` (`groupID`, `groupName`) VALUES (DEFAULT, 'Admin');
INSERT INTO `vacationplaner`.`vacationGroup` (`groupID`, `groupName`) VALUES (DEFAULT, 'Urlaubs_Verantwortlicher');
INSERT INTO `vacationplaner`.`vacationGroup` (`groupID`, `groupName`) VALUES (DEFAULT, 'User');

COMMIT;


-- -----------------------------------------------------
-- Data for table `vacationplaner`.`vacationDepartment`
-- -----------------------------------------------------
START TRANSACTION;
USE `vacationplaner`;
INSERT INTO `vacationplaner`.`vacationDepartment` (`departmentID`, `departmentName`, `departmentColor`, `departmentMinStock`) VALUES (1, 'Geschäftsführung', '#1AE5E9', 1);
INSERT INTO `vacationplaner`.`vacationDepartment` (`departmentID`, `departmentName`, `departmentColor`, `departmentMinStock`) VALUES (2, 'Service', '#EE0BC4', 3);
INSERT INTO `vacationplaner`.`vacationDepartment` (`departmentID`, `departmentName`, `departmentColor`, `departmentMinStock`) VALUES (3, 'Vertrieb', '#E9F01D', 1);
INSERT INTO `vacationplaner`.`vacationDepartment` (`departmentID`, `departmentName`, `departmentColor`, `departmentMinStock`) VALUES (4, 'Entwicklung', '#2213F5', 0);

COMMIT;


-- -----------------------------------------------------
-- Data for table `vacationplaner`.`statutoryHolidays`
-- -----------------------------------------------------
START TRANSACTION;
USE `vacationplaner`;
INSERT INTO `vacationplaner`.`statutoryHolidays` (`statutoryHolidaysID`, `statutoryHolidaysName`, `statutoryHolidaysDate`) VALUES (1, 'Neujahrstag', '2016-01-01');

COMMIT;


-- -----------------------------------------------------
-- Data for table `vacationplaner`.`schoolHolidays`
-- -----------------------------------------------------
START TRANSACTION;
USE `vacationplaner`;
INSERT INTO `vacationplaner`.`schoolHolidays` (`schoolHolidaysID`, `schoolHolidaysName`, `schoolHolidaysDateFrom`, `schoolHolidaysDateTo`) VALUES (1, 'Summer', '2016-07-11', '2016-08-23');

COMMIT;