-- 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 validation_manager
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `validation_manager` ;

-- -----------------------------------------------------
-- Schema validation_manager
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `validation_manager` DEFAULT CHARACTER SET latin1 ;
USE `validation_manager` ;

-- -----------------------------------------------------
-- Table `validation_manager`.`test_project`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`test_project` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`test_project` (
  `id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `notes` TEXT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
COMMENT = 'In some scenarios this is considered a validation project.';


-- -----------------------------------------------------
-- Table `validation_manager`.`test_plan`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`test_plan` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`test_plan` (
  `id` INT UNSIGNED NOT NULL,
  `test_project_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `notes` TEXT NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `is_open` TINYINT(1) NOT NULL DEFAULT 1,
  `regression_test_plan_id` INT UNSIGNED NULL,
  `regression_test_plan_test_project_id` INT NULL,
  PRIMARY KEY (`id`, `test_project_id`),
  INDEX `fk_test_plan_test_project_idx` (`test_project_id` ASC),
  INDEX `fk_test_plan_test_plan1_idx` (`regression_test_plan_id` ASC, `regression_test_plan_test_project_id` ASC),
  CONSTRAINT `fk_test_plan_test_project`
    FOREIGN KEY (`test_project_id`)
    REFERENCES `validation_manager`.`test_project` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_test_plan_test_plan1`
    FOREIGN KEY (`regression_test_plan_id` , `regression_test_plan_test_project_id`)
    REFERENCES `validation_manager`.`test_plan` (`id` , `test_project_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'In some scenarios this is considered a validation plan.';


-- -----------------------------------------------------
-- Table `validation_manager`.`test_case`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`test_case` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`test_case` (
  `id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL DEFAULT '\"TODO\"',
  `summary` BLOB NULL,
  `creation_date` DATETIME NOT NULL,
  `active` TINYINT(1) NULL DEFAULT 1,
  `is_open` TINYINT(1) NULL DEFAULT 1,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`user_status`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`user_status` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`user_status` (
  `id` INT NOT NULL,
  `status` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NULL,
  UNIQUE INDEX `unique` (`status` ASC),
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`vm_user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`vm_user` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`vm_user` (
  `id` INT NOT NULL,
  `username` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `first_name` VARCHAR(255) NOT NULL,
  `last_name` VARCHAR(255) NOT NULL,
  `locale` VARCHAR(255) NOT NULL DEFAULT 'en',
  `user_status_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_vm_user_user_status1_idx` (`user_status_id` ASC),
  CONSTRAINT `fk_vm_user_user_status1`
    FOREIGN KEY (`user_status_id`)
    REFERENCES `validation_manager`.`user_status` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`role` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`role` (
  `id` INT NOT NULL,
  `role_name` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`user_right`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`user_right` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`user_right` (
  `id` INT NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`role_has_right`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`role_has_right` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`role_has_right` (
  `role_id` INT NOT NULL,
  `right_id` INT NOT NULL,
  PRIMARY KEY (`role_id`, `right_id`),
  INDEX `fk_role_has_right_role1_idx` (`role_id` ASC),
  INDEX `fk_role_has_right_right1_idx` (`right_id` ASC),
  CONSTRAINT `fk_role_has_right_role1`
    FOREIGN KEY (`role_id`)
    REFERENCES `validation_manager`.`role` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_role_has_right_right1`
    FOREIGN KEY (`right_id`)
    REFERENCES `validation_manager`.`user_right` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`user_has_role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`user_has_role` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`user_has_role` (
  `user_id` INT NOT NULL,
  `role_id` INT NOT NULL,
  PRIMARY KEY (`user_id`, `role_id`),
  INDEX `fk_user_has_role_user1_idx` (`user_id` ASC),
  INDEX `fk_user_has_role_role1_idx` (`role_id` ASC),
  CONSTRAINT `fk_user_has_role_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `validation_manager`.`vm_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_has_role_role1`
    FOREIGN KEY (`role_id`)
    REFERENCES `validation_manager`.`role` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`user_test_plan_role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`user_test_plan_role` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`user_test_plan_role` (
  `test_plan_id` INT UNSIGNED NOT NULL,
  `test_plan_test_project_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `role_id` INT NOT NULL,
  PRIMARY KEY (`test_plan_id`, `test_plan_test_project_id`, `user_id`, `role_id`),
  INDEX `fk_test_plan_has_user_test_plan1_idx` (`test_plan_id` ASC, `test_plan_test_project_id` ASC),
  INDEX `fk_test_plan_has_user_user1_idx` (`user_id` ASC),
  INDEX `fk_user_test_plan_role_role1_idx` (`role_id` ASC),
  CONSTRAINT `fk_test_plan_has_user_test_plan1`
    FOREIGN KEY (`test_plan_id` , `test_plan_test_project_id`)
    REFERENCES `validation_manager`.`test_plan` (`id` , `test_project_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_test_plan_has_user_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `validation_manager`.`vm_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_test_plan_role_role1`
    FOREIGN KEY (`role_id`)
    REFERENCES `validation_manager`.`role` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`user_test_project_role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`user_test_project_role` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`user_test_project_role` (
  `test_project_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `role_id` INT NOT NULL,
  PRIMARY KEY (`test_project_id`, `user_id`, `role_id`),
  INDEX `fk_test_project_has_user_test_project1_idx` (`test_project_id` ASC),
  INDEX `fk_test_project_has_user_user1_idx` (`user_id` ASC),
  INDEX `fk_user_test_project_role_role1_idx` (`role_id` ASC),
  CONSTRAINT `fk_test_project_has_user_test_project1`
    FOREIGN KEY (`test_project_id`)
    REFERENCES `validation_manager`.`test_project` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_test_project_has_user_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `validation_manager`.`vm_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_test_project_role_role1`
    FOREIGN KEY (`role_id`)
    REFERENCES `validation_manager`.`role` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`assigment_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`assigment_type` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`assigment_type` (
  `id` INT NOT NULL,
  `fk_table` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`assignment_status`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`assignment_status` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`assignment_status` (
  `id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`user_assigment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`user_assigment` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`user_assigment` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `assigner_id` INT NOT NULL,
  `assigment_type_id` INT NOT NULL,
  `assignment_status_id` INT NOT NULL,
  `assignee_id` INT NOT NULL,
  `deadline` DATETIME NULL,
  `creation_time` DATETIME NOT NULL,
  PRIMARY KEY (`id`, `assigner_id`, `assigment_type_id`, `assignment_status_id`),
  INDEX `fk_user_assigment_user1_idx` (`assigner_id` ASC),
  INDEX `fk_user_assigment_user2_idx` (`assignee_id` ASC),
  INDEX `fk_user_assigment_assigment_type1_idx` (`assigment_type_id` ASC),
  INDEX `fk_user_assigment_assignment_status1_idx` (`assignment_status_id` ASC),
  CONSTRAINT `fk_user_assigment_user1`
    FOREIGN KEY (`assigner_id`)
    REFERENCES `validation_manager`.`vm_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_assigment_user2`
    FOREIGN KEY (`assignee_id`)
    REFERENCES `validation_manager`.`vm_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_assigment_assigment_type1`
    FOREIGN KEY (`assigment_type_id`)
    REFERENCES `validation_manager`.`assigment_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_assigment_assignment_status1`
    FOREIGN KEY (`assignment_status_id`)
    REFERENCES `validation_manager`.`assignment_status` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`step`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`step` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`step` (
  `id` INT NOT NULL,
  `test_case_id` INT UNSIGNED NOT NULL,
  `step_sequence` INT NOT NULL DEFAULT 1,
  `text` LONGBLOB NOT NULL,
  `expected_result` LONGBLOB NULL,
  `notes` TEXT NULL,
  PRIMARY KEY (`id`, `test_case_id`),
  INDEX `fk_step_test_case1_idx` (`test_case_id` ASC),
  CONSTRAINT `fk_step_test_case1`
    FOREIGN KEY (`test_case_id`)
    REFERENCES `validation_manager`.`test_case` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`requirement_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`requirement_type` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`requirement_type` (
  `id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NULL,
  `level` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`requirement_status`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`requirement_status` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`requirement_status` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `status` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`project`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`project` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`project` (
  `id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `notes` TEXT NULL,
  `parent_project_id` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_project_project1_idx` (`parent_project_id` ASC),
  CONSTRAINT `fk_project_project1`
    FOREIGN KEY (`parent_project_id`)
    REFERENCES `validation_manager`.`project` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`spec_level`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`spec_level` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`spec_level` (
  `id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`requirement_spec`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`requirement_spec` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`requirement_spec` (
  `id` INT NOT NULL,
  `project_id` INT NOT NULL,
  `spec_level_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `modificationDate` DATETIME NOT NULL,
  PRIMARY KEY (`id`, `project_id`, `spec_level_id`),
  INDEX `fk_requirement_spec_product1_idx` (`project_id` ASC),
  UNIQUE INDEX `product-spec` (`project_id` ASC, `name` ASC),
  INDEX `fk_requirement_spec_spec_level1_idx` (`spec_level_id` ASC),
  CONSTRAINT `fk_requirement_spec_product1`
    FOREIGN KEY (`project_id`)
    REFERENCES `validation_manager`.`project` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_requirement_spec_spec_level1`
    FOREIGN KEY (`spec_level_id`)
    REFERENCES `validation_manager`.`spec_level` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`requirement_spec_node`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`requirement_spec_node` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`requirement_spec_node` (
  `id` INT NOT NULL,
  `requirement_spec_id` INT NOT NULL,
  `requirement_spec_project_id` INT NOT NULL,
  `requirement_spec_spec_level_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `scope` TEXT NULL,
  `requirement_spec_node_id` INT NULL,
  `requirement_spec_node_requirement_spec_id` INT NULL,
  `requirement_spec_node_requirement_spec_project_id` INT NULL,
  `requirement_spec_node_requirement_spec_spec_level_id` INT NULL,
  PRIMARY KEY (`id`, `requirement_spec_id`, `requirement_spec_project_id`, `requirement_spec_spec_level_id`),
  INDEX `fk_requirement_spec_node_requirement_spec1_idx` (`requirement_spec_id` ASC, `requirement_spec_project_id` ASC, `requirement_spec_spec_level_id` ASC),
  INDEX `fk_requirement_spec_node_requirement_spec_node1_idx` (`requirement_spec_node_id` ASC, `requirement_spec_node_requirement_spec_id` ASC, `requirement_spec_node_requirement_spec_project_id` ASC, `requirement_spec_node_requirement_spec_spec_level_id` ASC),
  CONSTRAINT `fk_requirement_spec_node_requirement_spec1`
    FOREIGN KEY (`requirement_spec_id` , `requirement_spec_project_id` , `requirement_spec_spec_level_id`)
    REFERENCES `validation_manager`.`requirement_spec` (`id` , `project_id` , `spec_level_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_requirement_spec_node_requirement_spec_node1`
    FOREIGN KEY (`requirement_spec_node_id` , `requirement_spec_node_requirement_spec_id` , `requirement_spec_node_requirement_spec_project_id` , `requirement_spec_node_requirement_spec_spec_level_id`)
    REFERENCES `validation_manager`.`requirement_spec_node` (`id` , `requirement_spec_id` , `requirement_spec_project_id` , `requirement_spec_spec_level_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`requirement`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`requirement` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`requirement` (
  `id` INT NOT NULL,
  `requirement_type_id` INT NOT NULL,
  `unique_id` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  `notes` TEXT NULL,
  `requirement_status_id` INT NOT NULL,
  `requirement_spec_node_id` INT NOT NULL,
  `requirement_spec_node_requirement_spec_id` INT NOT NULL,
  `requirement_spec_node_requirement_spec_project_id` INT NOT NULL,
  `requirement_spec_node_requirement_spec_spec_level_id` INT NOT NULL,
  `parent_requirement_id` INT NULL,
  INDEX `fk_requirement_requirement_type1_idx` (`requirement_type_id` ASC),
  PRIMARY KEY (`id`),
  INDEX `fk_requirement_requirement_status1_idx` (`requirement_status_id` ASC),
  INDEX `fk_requirement_requirement_spec_node1_idx` (`requirement_spec_node_id` ASC, `requirement_spec_node_requirement_spec_id` ASC, `requirement_spec_node_requirement_spec_project_id` ASC, `requirement_spec_node_requirement_spec_spec_level_id` ASC),
  INDEX `fk_requirement_requirement1_idx` (`parent_requirement_id` ASC),
  CONSTRAINT `fk_requirement_requirement_type1`
    FOREIGN KEY (`requirement_type_id`)
    REFERENCES `validation_manager`.`requirement_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_requirement_requirement_status1`
    FOREIGN KEY (`requirement_status_id`)
    REFERENCES `validation_manager`.`requirement_status` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_requirement_requirement_spec_node1`
    FOREIGN KEY (`requirement_spec_node_id` , `requirement_spec_node_requirement_spec_id` , `requirement_spec_node_requirement_spec_project_id` , `requirement_spec_node_requirement_spec_spec_level_id`)
    REFERENCES `validation_manager`.`requirement_spec_node` (`id` , `requirement_spec_id` , `requirement_spec_project_id` , `requirement_spec_spec_level_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_requirement_requirement1`
    FOREIGN KEY (`parent_requirement_id`)
    REFERENCES `validation_manager`.`requirement` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`project_has_test_project`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`project_has_test_project` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`project_has_test_project` (
  `project_id` INT NOT NULL,
  `test_project_id` INT NOT NULL,
  PRIMARY KEY (`project_id`, `test_project_id`),
  INDEX `fk_product_has_test_project_product1_idx` (`project_id` ASC),
  INDEX `fk_product_has_test_project_test_project1_idx` (`test_project_id` ASC),
  CONSTRAINT `fk_product_has_test_project_product1`
    FOREIGN KEY (`project_id`)
    REFERENCES `validation_manager`.`project` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_product_has_test_project_test_project1`
    FOREIGN KEY (`test_project_id`)
    REFERENCES `validation_manager`.`test_project` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`user_modified_record`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`user_modified_record` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`user_modified_record` (
  `user_id` INT NOT NULL,
  `record_id` INT NOT NULL,
  `modified_date` DATETIME NOT NULL,
  `reason` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`record_id`, `user_id`),
  INDEX `fk_user_modified_record_user1_idx` (`user_id` ASC),
  CONSTRAINT `fk_user_modified_record_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `validation_manager`.`vm_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`test_case_execution`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`test_case_execution` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`test_case_execution` (
  `id` INT NOT NULL,
  `scope` LONGTEXT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `conclusion` LONGTEXT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`investigation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`investigation` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`investigation` (
  `id` INT NOT NULL,
  `description` TEXT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`user_has_investigation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`user_has_investigation` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`user_has_investigation` (
  `user_id` INT NOT NULL,
  `investigation_id` INT NOT NULL,
  `start_date` DATETIME NULL,
  `close_date` DATETIME NULL,
  PRIMARY KEY (`user_id`, `investigation_id`),
  INDEX `fk_user_has_investigation_user1_idx` (`user_id` ASC),
  INDEX `fk_user_has_investigation_investigation1_idx` (`investigation_id` ASC),
  CONSTRAINT `fk_user_has_investigation_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `validation_manager`.`vm_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_has_investigation_investigation1`
    FOREIGN KEY (`investigation_id`)
    REFERENCES `validation_manager`.`investigation` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`root_cause_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`root_cause_type` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`root_cause_type` (
  `id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`root_cause`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`root_cause` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`root_cause` (
  `id` INT NOT NULL,
  `root_cause_type_id` INT NOT NULL,
  `details` TEXT NOT NULL,
  PRIMARY KEY (`id`, `root_cause_type_id`),
  INDEX `fk_root_cause_root_cause_type1_idx` (`root_cause_type_id` ASC),
  CONSTRAINT `fk_root_cause_root_cause_type1`
    FOREIGN KEY (`root_cause_type_id`)
    REFERENCES `validation_manager`.`root_cause_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`corrective_action`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`corrective_action` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`corrective_action` (
  `id` INT NOT NULL,
  `details` TEXT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`user_has_corrective_action`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`user_has_corrective_action` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`user_has_corrective_action` (
  `user_id` INT NOT NULL,
  `corrective_action_id` INT NOT NULL,
  PRIMARY KEY (`user_id`, `corrective_action_id`),
  INDEX `fk_user_has_corrective_action_user1_idx` (`user_id` ASC),
  INDEX `fk_user_has_corrective_action_corrective_action1_idx` (`corrective_action_id` ASC),
  CONSTRAINT `fk_user_has_corrective_action_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `validation_manager`.`vm_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_has_corrective_action_corrective_action1`
    FOREIGN KEY (`corrective_action_id`)
    REFERENCES `validation_manager`.`corrective_action` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`exception_has_root_cause`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`exception_has_root_cause` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`exception_has_root_cause` (
  `exception_id` INT NOT NULL,
  `exception_reporter_id` INT NOT NULL,
  `root_cause_id` INT NOT NULL,
  `root_cause_root_cause_type_id` INT NOT NULL,
  PRIMARY KEY (`exception_id`, `exception_reporter_id`, `root_cause_id`, `root_cause_root_cause_type_id`),
  INDEX `fk_exception_has_root_cause_root_cause1_idx` (`root_cause_id` ASC, `root_cause_root_cause_type_id` ASC),
  CONSTRAINT `fk_exception_has_root_cause_root_cause1`
    FOREIGN KEY (`root_cause_id` , `root_cause_root_cause_type_id`)
    REFERENCES `validation_manager`.`root_cause` (`id` , `root_cause_type_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`exception_has_investigation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`exception_has_investigation` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`exception_has_investigation` (
  `exception_id` INT NOT NULL,
  `exception_reporter_id` INT NOT NULL,
  `investigation_id` INT NOT NULL,
  PRIMARY KEY (`exception_id`, `exception_reporter_id`, `investigation_id`),
  INDEX `fk_exception_has_investigation_investigation1_idx` (`investigation_id` ASC),
  CONSTRAINT `fk_exception_has_investigation_investigation1`
    FOREIGN KEY (`investigation_id`)
    REFERENCES `validation_manager`.`investigation` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`exception_has_corrective_action`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`exception_has_corrective_action` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`exception_has_corrective_action` (
  `exception_id` INT NOT NULL,
  `exception_reporter_id` INT NOT NULL,
  `corrective_action_id` INT NOT NULL,
  PRIMARY KEY (`exception_id`, `exception_reporter_id`, `corrective_action_id`),
  INDEX `fk_exception_has_corrective_action_corrective_action1_idx` (`corrective_action_id` ASC),
  CONSTRAINT `fk_exception_has_corrective_action_corrective_action1`
    FOREIGN KEY (`corrective_action_id`)
    REFERENCES `validation_manager`.`corrective_action` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`vm_id`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`vm_id` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`vm_id` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `table_name` VARCHAR(100) NOT NULL,
  `last_id` INT NOT NULL DEFAULT 1000,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `Unique` (`table_name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`vm_setting`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`vm_setting` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`vm_setting` (
  `id` INT NOT NULL,
  `setting` VARCHAR(255) NOT NULL,
  `bool_val` TINYINT(1) NULL,
  `int_val` INT NULL,
  `long_val` MEDIUMTEXT NULL,
  `string_val` TEXT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `unique` (`setting` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`FMEA`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`FMEA` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`FMEA` (
  `id` INT NOT NULL,
  `parent` INT NULL,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC),
  INDEX `fk_FMEA_FMEA1_idx` (`parent` ASC),
  CONSTRAINT `fk_FMEA_FMEA1`
    FOREIGN KEY (`parent`)
    REFERENCES `validation_manager`.`FMEA` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`risk_item`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`risk_item` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`risk_item` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `FMEA_id` INT NOT NULL,
  `sequence` INT NOT NULL,
  `version` INT NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`, `FMEA_id`),
  INDEX `fk_risk_item_FMEA1_idx` (`FMEA_id` ASC),
  CONSTRAINT `fk_risk_item_FMEA1`
    FOREIGN KEY (`FMEA_id`)
    REFERENCES `validation_manager`.`FMEA` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`risk_category`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`risk_category` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`risk_category` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `minimum` INT NOT NULL,
  `maximum` INT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`hazard`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`hazard` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`hazard` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`failure_mode`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`failure_mode` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`failure_mode` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`cause`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`cause` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`cause` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`risk_item_has_hazard`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`risk_item_has_hazard` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`risk_item_has_hazard` (
  `risk_item_id` INT NOT NULL,
  `risk_item_FMEA_id` INT NOT NULL,
  `hazard_id` INT NOT NULL,
  PRIMARY KEY (`risk_item_id`, `risk_item_FMEA_id`, `hazard_id`),
  INDEX `fk_risk_item_has_hazard_hazard1_idx` (`hazard_id` ASC),
  INDEX `fk_risk_item_has_hazard_risk_item1_idx` (`risk_item_id` ASC, `risk_item_FMEA_id` ASC),
  CONSTRAINT `fk_risk_item_has_hazard_risk_item1`
    FOREIGN KEY (`risk_item_id` , `risk_item_FMEA_id`)
    REFERENCES `validation_manager`.`risk_item` (`id` , `FMEA_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_risk_item_has_hazard_hazard1`
    FOREIGN KEY (`hazard_id`)
    REFERENCES `validation_manager`.`hazard` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`risk_item_has_failure_mode`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`risk_item_has_failure_mode` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`risk_item_has_failure_mode` (
  `risk_item_id` INT NOT NULL,
  `risk_item_FMEA_id` INT NOT NULL,
  `failure_mode_id` INT NOT NULL,
  PRIMARY KEY (`risk_item_id`, `risk_item_FMEA_id`, `failure_mode_id`),
  INDEX `fk_risk_item_has_failure_mode_failure_mode1_idx` (`failure_mode_id` ASC),
  INDEX `fk_risk_item_has_failure_mode_risk_item1_idx` (`risk_item_id` ASC, `risk_item_FMEA_id` ASC),
  CONSTRAINT `fk_risk_item_has_failure_mode_risk_item1`
    FOREIGN KEY (`risk_item_id` , `risk_item_FMEA_id`)
    REFERENCES `validation_manager`.`risk_item` (`id` , `FMEA_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_risk_item_has_failure_mode_failure_mode1`
    FOREIGN KEY (`failure_mode_id`)
    REFERENCES `validation_manager`.`failure_mode` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`risk_item_has_cause`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`risk_item_has_cause` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`risk_item_has_cause` (
  `risk_item_id` INT NOT NULL,
  `risk_item_FMEA_id` INT NOT NULL,
  `cause_id` INT NOT NULL,
  PRIMARY KEY (`risk_item_id`, `risk_item_FMEA_id`, `cause_id`),
  INDEX `fk_risk_item_has_cause_cause1_idx` (`cause_id` ASC),
  INDEX `fk_risk_item_has_cause_risk_item1_idx` (`risk_item_id` ASC, `risk_item_FMEA_id` ASC),
  CONSTRAINT `fk_risk_item_has_cause_risk_item1`
    FOREIGN KEY (`risk_item_id` , `risk_item_FMEA_id`)
    REFERENCES `validation_manager`.`risk_item` (`id` , `FMEA_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_risk_item_has_cause_cause1`
    FOREIGN KEY (`cause_id`)
    REFERENCES `validation_manager`.`cause` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`risk_control_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`risk_control_type` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`risk_control_type` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`risk_control`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`risk_control` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`risk_control` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `risk_control_type_id` INT NOT NULL,
  PRIMARY KEY (`id`, `risk_control_type_id`),
  INDEX `fk_risk_control_risk_control_type1_idx` (`risk_control_type_id` ASC),
  CONSTRAINT `fk_risk_control_risk_control_type1`
    FOREIGN KEY (`risk_control_type_id`)
    REFERENCES `validation_manager`.`risk_control_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`risk_control_has_test_case`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`risk_control_has_test_case` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`risk_control_has_test_case` (
  `risk_control_id` INT NOT NULL,
  `risk_control_risk_control_type_id` INT NOT NULL,
  `test_case_id` INT UNSIGNED NOT NULL,
  `test_case_test_id` INT NOT NULL,
  PRIMARY KEY (`risk_control_id`, `risk_control_risk_control_type_id`, `test_case_id`, `test_case_test_id`),
  INDEX `fk_risk_control_has_test_case_test_case1_idx` (`test_case_id` ASC, `test_case_test_id` ASC),
  INDEX `fk_risk_control_has_test_case_risk_control1_idx` (`risk_control_id` ASC, `risk_control_risk_control_type_id` ASC),
  CONSTRAINT `fk_risk_control_has_test_case_risk_control1`
    FOREIGN KEY (`risk_control_id` , `risk_control_risk_control_type_id`)
    REFERENCES `validation_manager`.`risk_control` (`id` , `risk_control_type_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_risk_control_has_test_case_test_case1`
    FOREIGN KEY (`test_case_id`)
    REFERENCES `validation_manager`.`test_case` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`risk_control_has_residual_risk_item`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`risk_control_has_residual_risk_item` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`risk_control_has_residual_risk_item` (
  `risk_control_id` INT NOT NULL,
  `risk_control_risk_control_type_id` INT NOT NULL,
  `risk_item_id` INT NOT NULL,
  `risk_item_FMEA_id` INT NOT NULL,
  PRIMARY KEY (`risk_control_id`, `risk_control_risk_control_type_id`, `risk_item_id`, `risk_item_FMEA_id`),
  INDEX `fk_risk_control_has_risk_item_risk_item1_idx` (`risk_item_id` ASC, `risk_item_FMEA_id` ASC),
  INDEX `fk_risk_control_has_risk_item_risk_control1_idx` (`risk_control_id` ASC, `risk_control_risk_control_type_id` ASC),
  CONSTRAINT `fk_risk_control_has_risk_item_risk_control1`
    FOREIGN KEY (`risk_control_id` , `risk_control_risk_control_type_id`)
    REFERENCES `validation_manager`.`risk_control` (`id` , `risk_control_type_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_risk_control_has_risk_item_risk_item1`
    FOREIGN KEY (`risk_item_id` , `risk_item_FMEA_id`)
    REFERENCES `validation_manager`.`risk_item` (`id` , `FMEA_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`FMEA_has_risk_category`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`FMEA_has_risk_category` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`FMEA_has_risk_category` (
  `FMEA_id` INT NOT NULL,
  `risk_category_id` INT NOT NULL,
  PRIMARY KEY (`FMEA_id`, `risk_category_id`),
  INDEX `fk_FMEA_has_risk_category_risk_category1_idx` (`risk_category_id` ASC),
  INDEX `fk_FMEA_has_risk_category_FMEA1_idx` (`FMEA_id` ASC),
  CONSTRAINT `fk_FMEA_has_risk_category_FMEA1`
    FOREIGN KEY (`FMEA_id`)
    REFERENCES `validation_manager`.`FMEA` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_FMEA_has_risk_category_risk_category1`
    FOREIGN KEY (`risk_category_id`)
    REFERENCES `validation_manager`.`risk_category` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`risk_item_has_risk_category`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`risk_item_has_risk_category` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`risk_item_has_risk_category` (
  `risk_item_id` INT NOT NULL,
  `risk_item_FMEA_id` INT NOT NULL,
  `risk_category_id` INT NOT NULL,
  `value` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`risk_item_id`, `risk_item_FMEA_id`, `risk_category_id`),
  INDEX `fk_risk_item_has_risk_category_risk_category1_idx` (`risk_category_id` ASC),
  INDEX `fk_risk_item_has_risk_category_risk_item1_idx` (`risk_item_id` ASC, `risk_item_FMEA_id` ASC),
  CONSTRAINT `fk_risk_item_has_risk_category_risk_item1`
    FOREIGN KEY (`risk_item_id` , `risk_item_FMEA_id`)
    REFERENCES `validation_manager`.`risk_item` (`id` , `FMEA_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_risk_item_has_risk_category_risk_category1`
    FOREIGN KEY (`risk_category_id`)
    REFERENCES `validation_manager`.`risk_category` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`attachment_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`attachment_type` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`attachment_type` (
  `id` INT(11) NOT NULL,
  `description` TEXT NULL,
  `TYPE` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `validation_manager`.`attachment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`attachment` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`attachment` (
  `id` INT(11) NOT NULL,
  `attachment_type_id` INT(11) NOT NULL,
  `file` LONGBLOB NULL,
  `string_value` VARCHAR(255) NULL,
  `TEXT_VALUE` LONGTEXT NULL DEFAULT NULL,
  `file_name` VARCHAR(255) NULL,
  PRIMARY KEY (`id`, `attachment_type_id`),
  INDEX `fk_attachment_attachment_type1_idx` (`attachment_type_id` ASC),
  CONSTRAINT `fk_attachment_attachment_type1`
    FOREIGN KEY (`attachment_type_id`)
    REFERENCES `validation_manager`.`attachment_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK_attachment_ATTACHMENT_TYPE_ID`
    FOREIGN KEY (`attachment_type_id`)
    REFERENCES `validation_manager`.`attachment_type` (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `validation_manager`.`risk_control_has_requirement`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`risk_control_has_requirement` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`risk_control_has_requirement` (
  `risk_control_id` INT NOT NULL,
  `risk_control_risk_control_type_id` INT NOT NULL,
  `requirement_id` INT NOT NULL,
  `requirement_major_version` INT NOT NULL,
  `requirement_mid_version` INT NOT NULL,
  `requirement_minor_version` INT NOT NULL,
  PRIMARY KEY (`risk_control_id`, `risk_control_risk_control_type_id`, `requirement_id`, `requirement_major_version`, `requirement_mid_version`, `requirement_minor_version`),
  INDEX `fk_risk_control_has_requirement_risk_control1_idx` (`risk_control_id` ASC, `risk_control_risk_control_type_id` ASC),
  INDEX `fk_risk_control_has_requirement_requirement1_idx` (`requirement_id` ASC, `requirement_major_version` ASC, `requirement_mid_version` ASC, `requirement_minor_version` ASC),
  CONSTRAINT `fk_risk_control_has_requirement_risk_control1`
    FOREIGN KEY (`risk_control_id` , `risk_control_risk_control_type_id`)
    REFERENCES `validation_manager`.`risk_control` (`id` , `risk_control_type_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_risk_control_has_requirement_requirement1`
    FOREIGN KEY (`requirement_id`)
    REFERENCES `validation_manager`.`requirement` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`risk_item_has_risk_control`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`risk_item_has_risk_control` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`risk_item_has_risk_control` (
  `risk_item_id` INT NOT NULL,
  `risk_item_FMEA_id` INT NOT NULL,
  `risk_control_id` INT NOT NULL,
  `risk_control_risk_control_type_id` INT NOT NULL,
  PRIMARY KEY (`risk_item_id`, `risk_item_FMEA_id`, `risk_control_id`, `risk_control_risk_control_type_id`),
  INDEX `fk_risk_item_has_risk_control_risk_control1_idx` (`risk_control_id` ASC, `risk_control_risk_control_type_id` ASC),
  INDEX `fk_risk_item_has_risk_control_risk_item1_idx` (`risk_item_id` ASC, `risk_item_FMEA_id` ASC),
  CONSTRAINT `fk_risk_item_has_risk_control_risk_item1`
    FOREIGN KEY (`risk_item_id` , `risk_item_FMEA_id`)
    REFERENCES `validation_manager`.`risk_item` (`id` , `FMEA_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_risk_item_has_risk_control_risk_control1`
    FOREIGN KEY (`risk_control_id` , `risk_control_risk_control_type_id`)
    REFERENCES `validation_manager`.`risk_control` (`id` , `risk_control_type_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`root_cause_has_user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`root_cause_has_user` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`root_cause_has_user` (
  `root_cause_id` INT NOT NULL,
  `root_cause_root_cause_type_id` INT NOT NULL,
  `vm_user_id` INT NOT NULL,
  PRIMARY KEY (`root_cause_id`, `root_cause_root_cause_type_id`, `vm_user_id`),
  INDEX `fk_root_cause_has_vm_user_vm_user1_idx` (`vm_user_id` ASC),
  INDEX `fk_root_cause_has_vm_user_root_cause1_idx` (`root_cause_id` ASC, `root_cause_root_cause_type_id` ASC),
  CONSTRAINT `fk_root_cause_has_vm_user_root_cause1`
    FOREIGN KEY (`root_cause_id` , `root_cause_root_cause_type_id`)
    REFERENCES `validation_manager`.`root_cause` (`id` , `root_cause_type_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_root_cause_has_vm_user_vm_user1`
    FOREIGN KEY (`vm_user_id`)
    REFERENCES `validation_manager`.`vm_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`test_plan_has_test_case`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`test_plan_has_test_case` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`test_plan_has_test_case` (
  `test_plan_id` INT UNSIGNED NOT NULL,
  `test_plan_test_project_id` INT NOT NULL,
  `test_case_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`test_plan_id`, `test_plan_test_project_id`, `test_case_id`),
  INDEX `fk_test_plan_has_test_case_test_case1_idx` (`test_case_id` ASC),
  INDEX `fk_test_plan_has_test_case_test_plan1_idx` (`test_plan_id` ASC, `test_plan_test_project_id` ASC),
  CONSTRAINT `fk_test_plan_has_test_case_test_plan1`
    FOREIGN KEY (`test_plan_id` , `test_plan_test_project_id`)
    REFERENCES `validation_manager`.`test_plan` (`id` , `test_project_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_test_plan_has_test_case_test_case1`
    FOREIGN KEY (`test_case_id`)
    REFERENCES `validation_manager`.`test_case` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`step_has_requirement`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`step_has_requirement` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`step_has_requirement` (
  `step_id` INT NOT NULL,
  `step_test_case_id` INT UNSIGNED NOT NULL,
  `requirement_id` INT NOT NULL,
  PRIMARY KEY (`step_id`, `step_test_case_id`, `requirement_id`),
  INDEX `fk_step_has_requirement_requirement1_idx` (`requirement_id` ASC),
  INDEX `fk_step_has_requirement_step1_idx` (`step_id` ASC, `step_test_case_id` ASC),
  CONSTRAINT `fk_step_has_requirement_step1`
    FOREIGN KEY (`step_id` , `step_test_case_id`)
    REFERENCES `validation_manager`.`step` (`id` , `test_case_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_step_has_requirement_requirement1`
    FOREIGN KEY (`requirement_id`)
    REFERENCES `validation_manager`.`requirement` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`execution_result`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`execution_result` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`execution_result` (
  `id` INT NOT NULL,
  `result_name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `result_name_UNIQUE` (`result_name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`review_result`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`review_result` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`review_result` (
  `id` INT NOT NULL,
  `review_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `review_name_UNIQUE` (`review_name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`history`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`history` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`history` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `version_major` INT NOT NULL DEFAULT 0,
  `version_mid` INT NOT NULL DEFAULT 0,
  `version_minor` INT NOT NULL DEFAULT 1,
  `reason` LONGTEXT NOT NULL,
  `modification_time` DATETIME NOT NULL,
  `modifier_id` INT NOT NULL,
  `requirement_id` INT NULL,
  `step_id` INT NOT NULL,
  `step_test_case_id` INT UNSIGNED NOT NULL,
  `vm_setting_id` INT NOT NULL,
  `project_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_history_vm_user1_idx` (`modifier_id` ASC),
  INDEX `fk_history_requirement1_idx` (`requirement_id` ASC),
  INDEX `fk_history_step1_idx` (`step_id` ASC, `step_test_case_id` ASC),
  INDEX `fk_history_vm_setting1_idx` (`vm_setting_id` ASC),
  INDEX `fk_history_project1_idx` (`project_id` ASC),
  CONSTRAINT `fk_history_vm_user1`
    FOREIGN KEY (`modifier_id`)
    REFERENCES `validation_manager`.`vm_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_history_requirement1`
    FOREIGN KEY (`requirement_id`)
    REFERENCES `validation_manager`.`requirement` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_history_step1`
    FOREIGN KEY (`step_id` , `step_test_case_id`)
    REFERENCES `validation_manager`.`step` (`id` , `test_case_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_history_vm_setting1`
    FOREIGN KEY (`vm_setting_id`)
    REFERENCES `validation_manager`.`vm_setting` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_history_project1`
    FOREIGN KEY (`project_id`)
    REFERENCES `validation_manager`.`project` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`execution_step`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`execution_step` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`execution_step` (
  `test_case_execution_id` INT NOT NULL,
  `step_id` INT NOT NULL,
  `step_test_case_id` INT UNSIGNED NOT NULL,
  `assignee` INT NULL,
  `assigner` INT NULL,
  `reviewer` INT NULL,
  `execution_time` DOUBLE NULL,
  `comment` LONGTEXT NULL,
  `execution_start` DATETIME NULL,
  `execution_end` DATETIME NULL,
  `assigned_time` DATETIME NULL,
  `result_id` INT NULL,
  `locked` TINYINT(1) NOT NULL DEFAULT 0,
  `reviewed` TINYINT(1) NOT NULL DEFAULT 0,
  `review_result_id` INT NULL,
  `review_date` DATETIME NULL,
  `step_history` INT NOT NULL,
  PRIMARY KEY (`test_case_execution_id`, `step_id`, `step_test_case_id`),
  INDEX `fk_test_case_execution_has_step_step1_idx` (`step_id` ASC, `step_test_case_id` ASC),
  INDEX `fk_test_case_execution_has_step_test_case_execution1_idx` (`test_case_execution_id` ASC),
  INDEX `fk_execution_step_vm_user1_idx` (`assignee` ASC),
  INDEX `fk_execution_step_execution_result1_idx` (`result_id` ASC),
  INDEX `fk_execution_step_vm_user2_idx` (`assigner` ASC),
  INDEX `fk_execution_step_review_result1_idx` (`review_result_id` ASC),
  INDEX `fk_execution_step_vm_user3_idx` (`reviewer` ASC),
  INDEX `fk_execution_step_history1_idx` (`step_history` ASC),
  CONSTRAINT `fk_test_case_execution_has_step_test_case_execution1`
    FOREIGN KEY (`test_case_execution_id`)
    REFERENCES `validation_manager`.`test_case_execution` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_test_case_execution_has_step_step1`
    FOREIGN KEY (`step_id` , `step_test_case_id`)
    REFERENCES `validation_manager`.`step` (`id` , `test_case_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_execution_step_vm_user1`
    FOREIGN KEY (`assignee`)
    REFERENCES `validation_manager`.`vm_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_execution_step_execution_result1`
    FOREIGN KEY (`result_id`)
    REFERENCES `validation_manager`.`execution_result` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_execution_step_vm_user2`
    FOREIGN KEY (`assigner`)
    REFERENCES `validation_manager`.`vm_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_execution_step_review_result1`
    FOREIGN KEY (`review_result_id`)
    REFERENCES `validation_manager`.`review_result` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_execution_step_vm_user3`
    FOREIGN KEY (`reviewer`)
    REFERENCES `validation_manager`.`vm_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_execution_step_history1`
    FOREIGN KEY (`step_history`)
    REFERENCES `validation_manager`.`history` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`execution_step_has_attachment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`execution_step_has_attachment` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`execution_step_has_attachment` (
  `execution_step_test_case_execution_id` INT NOT NULL,
  `execution_step_step_id` INT NOT NULL,
  `execution_step_step_test_case_id` INT UNSIGNED NOT NULL,
  `attachment_id` INT(11) NOT NULL,
  `attachment_attachment_type_id` INT(11) NOT NULL,
  `creation_time` DATETIME NOT NULL,
  PRIMARY KEY (`execution_step_test_case_execution_id`, `execution_step_step_id`, `execution_step_step_test_case_id`, `attachment_id`, `attachment_attachment_type_id`),
  INDEX `fk_execution_step_has_attachment_attachment1_idx` (`attachment_id` ASC, `attachment_attachment_type_id` ASC),
  INDEX `fk_execution_step_has_attachment_execution_step1_idx` (`execution_step_test_case_execution_id` ASC, `execution_step_step_id` ASC, `execution_step_step_test_case_id` ASC),
  CONSTRAINT `fk_execution_step_has_attachment_execution_step1`
    FOREIGN KEY (`execution_step_test_case_execution_id` , `execution_step_step_id` , `execution_step_step_test_case_id`)
    REFERENCES `validation_manager`.`execution_step` (`test_case_execution_id` , `step_id` , `step_test_case_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_execution_step_has_attachment_attachment1`
    FOREIGN KEY (`attachment_id` , `attachment_attachment_type_id`)
    REFERENCES `validation_manager`.`attachment` (`id` , `attachment_type_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`issue_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`issue_type` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`issue_type` (
  `id` INT NOT NULL,
  `type_name` VARCHAR(45) NOT NULL,
  `description` LONGTEXT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `type_name_UNIQUE` (`type_name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`issue_resolution`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`issue_resolution` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`issue_resolution` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`issue`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`issue` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`issue` (
  `id` INT NOT NULL,
  `issue_type_id` INT NOT NULL,
  `issue_resolution_id` INT NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` LONGTEXT NOT NULL,
  `creationTime` DATETIME NOT NULL,
  PRIMARY KEY (`id`, `issue_type_id`),
  INDEX `fk_issue_issue_type1_idx` (`issue_type_id` ASC),
  INDEX `fk_issue_issue_resolution1_idx` (`issue_resolution_id` ASC),
  CONSTRAINT `fk_issue_issue_type1`
    FOREIGN KEY (`issue_type_id`)
    REFERENCES `validation_manager`.`issue_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_issue_issue_resolution1`
    FOREIGN KEY (`issue_resolution_id`)
    REFERENCES `validation_manager`.`issue_resolution` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`execution_step_has_issue`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`execution_step_has_issue` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`execution_step_has_issue` (
  `execution_step_test_case_execution_id` INT NOT NULL,
  `execution_step_step_id` INT NOT NULL,
  `execution_step_step_test_case_id` INT UNSIGNED NOT NULL,
  `issue_id` INT NOT NULL,
  `issue_issue_type_id` INT NOT NULL,
  PRIMARY KEY (`execution_step_test_case_execution_id`, `execution_step_step_id`, `execution_step_step_test_case_id`, `issue_id`, `issue_issue_type_id`),
  INDEX `fk_execution_step_has_issue_issue1_idx` (`issue_id` ASC, `issue_issue_type_id` ASC),
  INDEX `fk_execution_step_has_issue_execution_step1_idx` (`execution_step_test_case_execution_id` ASC, `execution_step_step_id` ASC, `execution_step_step_test_case_id` ASC),
  CONSTRAINT `fk_execution_step_has_issue_execution_step1`
    FOREIGN KEY (`execution_step_test_case_execution_id` , `execution_step_step_id` , `execution_step_step_test_case_id`)
    REFERENCES `validation_manager`.`execution_step` (`test_case_execution_id` , `step_id` , `step_test_case_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_execution_step_has_issue_issue1`
    FOREIGN KEY (`issue_id` , `issue_issue_type_id`)
    REFERENCES `validation_manager`.`issue` (`id` , `issue_type_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`vm_user_has_execution_step_has_issue`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`vm_user_has_execution_step_has_issue` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`vm_user_has_execution_step_has_issue` (
  `vm_user_id` INT NOT NULL,
  `execution_step_has_issue_execution_step_test_case_execution_id` INT NOT NULL,
  `execution_step_has_issue_execution_step_step_id` INT NOT NULL,
  `execution_step_has_issue_execution_step_step_test_case_id` INT UNSIGNED NOT NULL,
  `execution_step_has_issue_issue_id` INT NOT NULL,
  `execution_step_has_issue_issue_issue_type_id` INT NOT NULL,
  PRIMARY KEY (`vm_user_id`, `execution_step_has_issue_execution_step_test_case_execution_id`, `execution_step_has_issue_execution_step_step_id`, `execution_step_has_issue_execution_step_step_test_case_id`, `execution_step_has_issue_issue_id`, `execution_step_has_issue_issue_issue_type_id`),
  INDEX `fk_vm_user_has_execution_step_has_issue_execution_step_has__idx` (`execution_step_has_issue_execution_step_test_case_execution_id` ASC, `execution_step_has_issue_execution_step_step_id` ASC, `execution_step_has_issue_execution_step_step_test_case_id` ASC, `execution_step_has_issue_issue_id` ASC, `execution_step_has_issue_issue_issue_type_id` ASC),
  INDEX `fk_vm_user_has_execution_step_has_issue_vm_user1_idx` (`vm_user_id` ASC),
  CONSTRAINT `fk_vm_user_has_execution_step_has_issue_vm_user1`
    FOREIGN KEY (`vm_user_id`)
    REFERENCES `validation_manager`.`vm_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_vm_user_has_execution_step_has_issue_execution_step_has_is1`
    FOREIGN KEY (`execution_step_has_issue_execution_step_test_case_execution_id` , `execution_step_has_issue_execution_step_step_id` , `execution_step_has_issue_execution_step_step_test_case_id` , `execution_step_has_issue_issue_id` , `execution_step_has_issue_issue_issue_type_id`)
    REFERENCES `validation_manager`.`execution_step_has_issue` (`execution_step_test_case_execution_id` , `execution_step_step_id` , `execution_step_step_test_case_id` , `issue_id` , `issue_issue_type_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`baseline`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`baseline` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`baseline` (
  `id` INT NOT NULL,
  `creation_date` DATETIME NOT NULL,
  `baseline_name` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `requirement_spec_id` INT NOT NULL,
  `requirement_spec_project_id` INT NOT NULL,
  `requirement_spec_spec_level_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_baseline_requirement_spec1_idx` (`requirement_spec_id` ASC, `requirement_spec_project_id` ASC, `requirement_spec_spec_level_id` ASC),
  CONSTRAINT `fk_baseline_requirement_spec1`
    FOREIGN KEY (`requirement_spec_id` , `requirement_spec_project_id` , `requirement_spec_spec_level_id`)
    REFERENCES `validation_manager`.`requirement_spec` (`id` , `project_id` , `spec_level_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`field_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`field_type` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`field_type` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `type_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `type_name_UNIQUE` (`type_name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`history_field`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`history_field` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`history_field` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `field_type_id` INT NOT NULL,
  `history_id` INT NOT NULL,
  `field_name` VARCHAR(45) NOT NULL,
  `field_value` LONGTEXT NOT NULL,
  PRIMARY KEY (`id`, `field_type_id`, `history_id`),
  INDEX `fk_history_field_field_type1_idx` (`field_type_id` ASC),
  INDEX `fk_history_field_history1_idx` (`history_id` ASC),
  CONSTRAINT `fk_history_field_field_type1`
    FOREIGN KEY (`field_type_id`)
    REFERENCES `validation_manager`.`field_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_history_field_history1`
    FOREIGN KEY (`history_id`)
    REFERENCES `validation_manager`.`history` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`execution_step_has_vm_user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`execution_step_has_vm_user` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`execution_step_has_vm_user` (
  `execution_step_test_case_execution_id` INT NOT NULL,
  `execution_step_step_id` INT NOT NULL,
  `execution_step_step_test_case_id` INT UNSIGNED NOT NULL,
  `vm_user_id` INT NOT NULL,
  `vm_user_execution_step_test_case_execution_id` INT NOT NULL,
  `vm_user_execution_step_step_id` INT NOT NULL,
  `vm_user_execution_step_step_test_case_id` INT UNSIGNED NOT NULL,
  `role_id` INT NOT NULL,
  PRIMARY KEY (`execution_step_test_case_execution_id`, `execution_step_step_id`, `execution_step_step_test_case_id`, `vm_user_id`, `vm_user_execution_step_test_case_execution_id`, `vm_user_execution_step_step_id`, `vm_user_execution_step_step_test_case_id`, `role_id`),
  INDEX `fk_execution_step_has_vm_user_vm_user1_idx` (`vm_user_id` ASC, `vm_user_execution_step_test_case_execution_id` ASC, `vm_user_execution_step_step_id` ASC, `vm_user_execution_step_step_test_case_id` ASC),
  INDEX `fk_execution_step_has_vm_user_execution_step1_idx` (`execution_step_test_case_execution_id` ASC, `execution_step_step_id` ASC, `execution_step_step_test_case_id` ASC),
  INDEX `fk_execution_step_has_vm_user_role1_idx` (`role_id` ASC),
  CONSTRAINT `fk_execution_step_has_vm_user_execution_step1`
    FOREIGN KEY (`execution_step_test_case_execution_id` , `execution_step_step_id` , `execution_step_step_test_case_id`)
    REFERENCES `validation_manager`.`execution_step` (`test_case_execution_id` , `step_id` , `step_test_case_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_execution_step_has_vm_user_vm_user1`
    FOREIGN KEY (`vm_user_id`)
    REFERENCES `validation_manager`.`vm_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_execution_step_has_vm_user_role1`
    FOREIGN KEY (`role_id`)
    REFERENCES `validation_manager`.`role` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`baseline_has_history`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`baseline_has_history` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`baseline_has_history` (
  `baseline_id` INT NOT NULL,
  `history_id` INT NOT NULL,
  PRIMARY KEY (`baseline_id`, `history_id`),
  INDEX `fk_baseline_has_history_history1_idx` (`history_id` ASC),
  INDEX `fk_baseline_has_history_baseline1_idx` (`baseline_id` ASC),
  CONSTRAINT `fk_baseline_has_history_baseline1`
    FOREIGN KEY (`baseline_id`)
    REFERENCES `validation_manager`.`baseline` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_baseline_has_history_history1`
    FOREIGN KEY (`history_id`)
    REFERENCES `validation_manager`.`history` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`execution_step_has_history`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`execution_step_has_history` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`execution_step_has_history` (
  `execution_step_test_case_execution_id` INT NOT NULL,
  `execution_step_step_id` INT NOT NULL,
  `execution_step_step_test_case_id` INT UNSIGNED NOT NULL,
  `history_id` INT NOT NULL,
  PRIMARY KEY (`execution_step_test_case_execution_id`, `execution_step_step_id`, `execution_step_step_test_case_id`, `history_id`),
  INDEX `fk_execution_step_has_history_history1_idx` (`history_id` ASC),
  INDEX `fk_execution_step_has_history_execution_step1_idx` (`execution_step_test_case_execution_id` ASC, `execution_step_step_id` ASC, `execution_step_step_test_case_id` ASC),
  CONSTRAINT `fk_execution_step_has_history_execution_step1`
    FOREIGN KEY (`execution_step_test_case_execution_id` , `execution_step_step_id` , `execution_step_step_test_case_id`)
    REFERENCES `validation_manager`.`execution_step` (`test_case_execution_id` , `step_id` , `step_test_case_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_execution_step_has_history_history1`
    FOREIGN KEY (`history_id`)
    REFERENCES `validation_manager`.`history` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`notification_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`notification_type` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`notification_type` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `type_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `type_name_UNIQUE` (`type_name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `validation_manager`.`notification`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `validation_manager`.`notification` ;

CREATE TABLE IF NOT EXISTS `validation_manager`.`notification` (
  `id` INT NOT NULL,
  `notification_type_id` INT NOT NULL,
  `target_user` INT NOT NULL,
  `author` INT NOT NULL,
  `content` LONGTEXT NOT NULL,
  `creation_date` DATETIME NOT NULL,
  `acknowledge_date` DATETIME NULL,
  PRIMARY KEY (`id`, `notification_type_id`),
  INDEX `fk_notification_notification_type1_idx` (`notification_type_id` ASC),
  INDEX `fk_notification_vm_user1_idx` (`target_user` ASC),
  INDEX `fk_notification_vm_user2_idx` (`author` ASC),
  CONSTRAINT `fk_notification_notification_type1`
    FOREIGN KEY (`notification_type_id`)
    REFERENCES `validation_manager`.`notification_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_notification_vm_user1`
    FOREIGN KEY (`target_user`)
    REFERENCES `validation_manager`.`vm_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_notification_vm_user2`
    FOREIGN KEY (`author`)
    REFERENCES `validation_manager`.`vm_user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
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 `validation_manager`.`user_status`
-- -----------------------------------------------------
START TRANSACTION;
USE `validation_manager`;
INSERT INTO `validation_manager`.`user_status` (`id`, `status`, `description`) VALUES (1, 'user.status.active', 'user.status.active.desc');
INSERT INTO `validation_manager`.`user_status` (`id`, `status`, `description`) VALUES (2, 'user.status.inactive', 'user.status.inactive.desc');
INSERT INTO `validation_manager`.`user_status` (`id`, `status`, `description`) VALUES (3, 'user.status.locked', 'user.status.locked.desc');
INSERT INTO `validation_manager`.`user_status` (`id`, `status`, `description`) VALUES (4, 'user.status.aged', 'user.status.aged.desc');

COMMIT;


-- -----------------------------------------------------
-- Data for table `validation_manager`.`vm_user`
-- -----------------------------------------------------
START TRANSACTION;
USE `validation_manager`;
INSERT INTO `validation_manager`.`vm_user` (`id`, `username`, `password`, `email`, `first_name`, `last_name`, `locale`, `user_status_id`) VALUES (1, 'admin', '21232f297a57a5a743894a0e4a801fc3', '', 'System', 'Administrator', 'en', 1);

COMMIT;


-- -----------------------------------------------------
-- Data for table `validation_manager`.`role`
-- -----------------------------------------------------
START TRANSACTION;
USE `validation_manager`;
INSERT INTO `validation_manager`.`role` (`id`, `role_name`, `description`) VALUES (1, 'reserved1', 'reserver.desc');
INSERT INTO `validation_manager`.`role` (`id`, `role_name`, `description`) VALUES (2, 'reserved2', 'reserved2.desc');
INSERT INTO `validation_manager`.`role` (`id`, `role_name`, `description`) VALUES (3, 'none', 'none.desc');
INSERT INTO `validation_manager`.`role` (`id`, `role_name`, `description`) VALUES (4, 'test.designer', 'test.designer.desc');
INSERT INTO `validation_manager`.`role` (`id`, `role_name`, `description`) VALUES (5, 'guest', 'guest.desc');
INSERT INTO `validation_manager`.`role` (`id`, `role_name`, `description`) VALUES (6, 'senior.tester', 'senior.tester.desc');
INSERT INTO `validation_manager`.`role` (`id`, `role_name`, `description`) VALUES (7, 'tester', 'tester.desc');
INSERT INTO `validation_manager`.`role` (`id`, `role_name`, `description`) VALUES (8, 'admin', 'admin.desc');
INSERT INTO `validation_manager`.`role` (`id`, `role_name`, `description`) VALUES (9, 'leader', 'leader.desc');
INSERT INTO `validation_manager`.`role` (`id`, `role_name`, `description`) VALUES (10, 'quality', 'quality.desc');

COMMIT;


-- -----------------------------------------------------
-- Data for table `validation_manager`.`user_right`
-- -----------------------------------------------------
START TRANSACTION;
USE `validation_manager`;
INSERT INTO `validation_manager`.`user_right` (`id`, `description`) VALUES (1, 'testplan.execute');
INSERT INTO `validation_manager`.`user_right` (`id`, `description`) VALUES (2, 'testplan.create.build');
INSERT INTO `validation_manager`.`user_right` (`id`, `description`) VALUES (3, 'testplan.metrics');
INSERT INTO `validation_manager`.`user_right` (`id`, `description`) VALUES (4, 'testplan.planning');
INSERT INTO `validation_manager`.`user_right` (`id`, `description`) VALUES (5, 'testplan.user_role.assigment');
INSERT INTO `validation_manager`.`user_right` (`id`, `description`) VALUES (6, 'testcase.view');
INSERT INTO `validation_manager`.`user_right` (`id`, `description`) VALUES (7, 'testcase.modify');
INSERT INTO `validation_manager`.`user_right` (`id`, `description`) VALUES (8, 'requirement.view');
INSERT INTO `validation_manager`.`user_right` (`id`, `description`) VALUES (9, 'requirement.modify');
INSERT INTO `validation_manager`.`user_right` (`id`, `description`) VALUES (10, 'product.modify');
INSERT INTO `validation_manager`.`user_right` (`id`, `description`) VALUES (11, 'manage.users');
INSERT INTO `validation_manager`.`user_right` (`id`, `description`) VALUES (12, 'testplan.create');
INSERT INTO `validation_manager`.`user_right` (`id`, `description`) VALUES (13, 'events.view');
INSERT INTO `validation_manager`.`user_right` (`id`, `description`) VALUES (14, 'events.management');
INSERT INTO `validation_manager`.`user_right` (`id`, `description`) VALUES (15, 'system.configuration');

COMMIT;


-- -----------------------------------------------------
-- Data for table `validation_manager`.`role_has_right`
-- -----------------------------------------------------
START TRANSACTION;
USE `validation_manager`;
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (8, 1);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (8, 2);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (8, 3);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (8, 4);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (8, 5);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (8, 6);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (8, 7);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (8, 8);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (8, 9);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (8, 10);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (8, 11);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (8, 12);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (8, 13);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (8, 14);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (8, 15);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (5, 3);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (5, 6);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (4, 3);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (4, 6);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (4, 7);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (4, 8);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (4, 9);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (7, 1);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (7, 3);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (7, 6);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (6, 1);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (6, 2);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (6, 3);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (6, 6);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (6, 7);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (6, 9);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (9, 1);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (9, 2);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (9, 3);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (9, 4);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (9, 5);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (9, 6);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (9, 7);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (9, 8);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (9, 9);
INSERT INTO `validation_manager`.`role_has_right` (`role_id`, `right_id`) VALUES (9, 12);

COMMIT;


-- -----------------------------------------------------
-- Data for table `validation_manager`.`user_has_role`
-- -----------------------------------------------------
START TRANSACTION;
USE `validation_manager`;
INSERT INTO `validation_manager`.`user_has_role` (`user_id`, `role_id`) VALUES (1, 8);

COMMIT;


-- -----------------------------------------------------
-- Data for table `validation_manager`.`assigment_type`
-- -----------------------------------------------------
START TRANSACTION;
USE `validation_manager`;
INSERT INTO `validation_manager`.`assigment_type` (`id`, `fk_table`, `description`) VALUES (1, 'test_plan_has_test', 'testcase.execution');
INSERT INTO `validation_manager`.`assigment_type` (`id`, `fk_table`, `description`) VALUES (2, 'test_case', 'testcase.review');

COMMIT;


-- -----------------------------------------------------
-- Data for table `validation_manager`.`assignment_status`
-- -----------------------------------------------------
START TRANSACTION;
USE `validation_manager`;
INSERT INTO `validation_manager`.`assignment_status` (`id`, `name`, `description`) VALUES (1, 'assignment.status.open', 'assignment.status.open.desc');
INSERT INTO `validation_manager`.`assignment_status` (`id`, `name`, `description`) VALUES (2, 'assignment.status.closed', 'assignment.status.closed.desc');
INSERT INTO `validation_manager`.`assignment_status` (`id`, `name`, `description`) VALUES (3, 'assignment.status.completed', 'assignment.status.completed.desc');
INSERT INTO `validation_manager`.`assignment_status` (`id`, `name`, `description`) VALUES (4, 'assignment.status..todo.urgent', 'assignment.status.todo.urgent.desc');
INSERT INTO `validation_manager`.`assignment_status` (`id`, `name`, `description`) VALUES (5, 'assignment.status.todo', 'assignment.status.todo.desc');

COMMIT;


-- -----------------------------------------------------
-- Data for table `validation_manager`.`requirement_type`
-- -----------------------------------------------------
START TRANSACTION;
USE `validation_manager`;
INSERT INTO `validation_manager`.`requirement_type` (`id`, `name`, `description`, `level`) VALUES (1, 'HW', 'Hardware', DEFAULT);
INSERT INTO `validation_manager`.`requirement_type` (`id`, `name`, `description`, `level`) VALUES (2, 'SW', 'Software', DEFAULT);
INSERT INTO `validation_manager`.`requirement_type` (`id`, `name`, `description`, `level`) VALUES (3, 'Labeling', 'Labeling requirements', DEFAULT);
INSERT INTO `validation_manager`.`requirement_type` (`id`, `name`, `description`, `level`) VALUES (4, 'Standards', 'Testing related to applicable standards', DEFAULT);

COMMIT;


-- -----------------------------------------------------
-- Data for table `validation_manager`.`requirement_status`
-- -----------------------------------------------------
START TRANSACTION;
USE `validation_manager`;
INSERT INTO `validation_manager`.`requirement_status` (`id`, `status`) VALUES (1, 'general.open');
INSERT INTO `validation_manager`.`requirement_status` (`id`, `status`) VALUES (2, 'general.approved');
INSERT INTO `validation_manager`.`requirement_status` (`id`, `status`) VALUES (3, 'general.obsolete');
INSERT INTO `validation_manager`.`requirement_status` (`id`, `status`) VALUES (4, 'general.rejected');

COMMIT;


-- -----------------------------------------------------
-- Data for table `validation_manager`.`spec_level`
-- -----------------------------------------------------
START TRANSACTION;
USE `validation_manager`;
INSERT INTO `validation_manager`.`spec_level` (`id`, `name`, `description`) VALUES (1, 'User Need', 'Higher level of requirements.');
INSERT INTO `validation_manager`.`spec_level` (`id`, `name`, `description`) VALUES (2, 'Requirements', 'Detailed version of the User Needs. Usually the lowest level of requirement for non-software projects.');
INSERT INTO `validation_manager`.`spec_level` (`id`, `name`, `description`) VALUES (3, 'System Design', 'Systems design is the phase where system engineers analyze and understand the business of the proposed system by studying the user requirements document.');
INSERT INTO `validation_manager`.`spec_level` (`id`, `name`, `description`) VALUES (4, 'Architecture Design', 'The baseline in selecting the architecture is that it should realize all which typically consists of the list of modules, brief functionality of each module, their interface relationships, dependencies, database tables, architecture diagrams, technology details etc.');
INSERT INTO `validation_manager`.`spec_level` (`id`, `name`, `description`) VALUES (5, 'Module Design', 'The designed system is broken up into smaller units or modules and each of them is explained so that the programmer can start coding directly. The low level design document or program specifications will contain a detailed functional logic of the module, in pseudocode.');

COMMIT;


-- -----------------------------------------------------
-- Data for table `validation_manager`.`vm_setting`
-- -----------------------------------------------------
START TRANSACTION;
USE `validation_manager`;
INSERT INTO `validation_manager`.`vm_setting` (`id`, `setting`, `bool_val`, `int_val`, `long_val`, `string_val`) VALUES (4, 'password.aging', 0, 90, '0', NULL);
INSERT INTO `validation_manager`.`vm_setting` (`id`, `setting`, `bool_val`, `int_val`, `long_val`, `string_val`) VALUES (5, 'password.attempts', 0, 3, '0', NULL);
INSERT INTO `validation_manager`.`vm_setting` (`id`, `setting`, `bool_val`, `int_val`, `long_val`, `string_val`) VALUES (6, 'password.unusable_period', 0, 365, '0', NULL);
INSERT INTO `validation_manager`.`vm_setting` (`id`, `setting`, `bool_val`, `int_val`, `long_val`, `string_val`) VALUES (1, 'version.high', 0, 0, '0', NULL);
INSERT INTO `validation_manager`.`vm_setting` (`id`, `setting`, `bool_val`, `int_val`, `long_val`, `string_val`) VALUES (2, 'version.mid', 0, 0, '0', NULL);
INSERT INTO `validation_manager`.`vm_setting` (`id`, `setting`, `bool_val`, `int_val`, `long_val`, `string_val`) VALUES (3, 'version.low', 0, 1, '0', NULL);
INSERT INTO `validation_manager`.`vm_setting` (`id`, `setting`, `bool_val`, `int_val`, `long_val`, `string_val`) VALUES (7, 'version.postfix', 0, 0, '0', 'Alpha');

COMMIT;


-- -----------------------------------------------------
-- Data for table `validation_manager`.`execution_result`
-- -----------------------------------------------------
START TRANSACTION;
USE `validation_manager`;
INSERT INTO `validation_manager`.`execution_result` (`id`, `result_name`) VALUES (1, 'result.pass');
INSERT INTO `validation_manager`.`execution_result` (`id`, `result_name`) VALUES (2, 'result.fail');
INSERT INTO `validation_manager`.`execution_result` (`id`, `result_name`) VALUES (3, 'result.blocked');
INSERT INTO `validation_manager`.`execution_result` (`id`, `result_name`) VALUES (4, 'result.pending');

COMMIT;


-- -----------------------------------------------------
-- Data for table `validation_manager`.`review_result`
-- -----------------------------------------------------
START TRANSACTION;
USE `validation_manager`;
INSERT INTO `validation_manager`.`review_result` (`id`, `review_name`) VALUES (1, 'review.pass');
INSERT INTO `validation_manager`.`review_result` (`id`, `review_name`) VALUES (2, 'review.fail');
INSERT INTO `validation_manager`.`review_result` (`id`, `review_name`) VALUES (3, 'review.pending');

COMMIT;


-- -----------------------------------------------------
-- Data for table `validation_manager`.`issue_type`
-- -----------------------------------------------------
START TRANSACTION;
USE `validation_manager`;
INSERT INTO `validation_manager`.`issue_type` (`id`, `type_name`, `description`) VALUES (1, 'bug.name', 'bug.desc');
INSERT INTO `validation_manager`.`issue_type` (`id`, `type_name`, `description`) VALUES (2, 'observation.name', 'observation.desc');
INSERT INTO `validation_manager`.`issue_type` (`id`, `type_name`, `description`) VALUES (3, 'step.issue.name', 'step.issue.desc');

COMMIT;


-- -----------------------------------------------------
-- Data for table `validation_manager`.`issue_resolution`
-- -----------------------------------------------------
START TRANSACTION;
USE `validation_manager`;
INSERT INTO `validation_manager`.`issue_resolution` (`id`, `name`) VALUES (1, 'issue.invalid');
INSERT INTO `validation_manager`.`issue_resolution` (`id`, `name`) VALUES (2, 'issue.not.reproduceable');
INSERT INTO `validation_manager`.`issue_resolution` (`id`, `name`) VALUES (3, 'issue.fixed');

COMMIT;


-- -----------------------------------------------------
-- Data for table `validation_manager`.`field_type`
-- -----------------------------------------------------
START TRANSACTION;
USE `validation_manager`;
INSERT INTO `validation_manager`.`field_type` (`id`, `type_name`) VALUES (1, 'String');
INSERT INTO `validation_manager`.`field_type` (`id`, `type_name`) VALUES (2, 'Integer');
INSERT INTO `validation_manager`.`field_type` (`id`, `type_name`) VALUES (3, 'Blob');
INSERT INTO `validation_manager`.`field_type` (`id`, `type_name`) VALUES (4, 'Text');

COMMIT;