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';

CREATE SCHEMA IF NOT EXISTS `client` DEFAULT CHARACTER SET utf8 ;
USE `client` ;

-- -----------------------------------------------------
-- Table `client`.`job_description`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`job_description` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `description` TEXT NULL,
  `type` ENUM('primary','supplementary') NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `client`.`employee`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`employee` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `employee_num` VARCHAR(45) NULL,
  `email` VARCHAR(128) NULL,
  `user_id` INT(11) NULL,
  `job_description_id` INT(11) NULL,
  `main_area_id` INT(11) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_employee_job_description1_idx` (`job_description_id` ASC),
  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC),
  INDEX `fk_employee_area1_idx` (`main_area_id` ASC),
  CONSTRAINT `fk_employee_job_description1`
    FOREIGN KEY (`job_description_id`)
    REFERENCES `client`.`job_description` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_employee_area1`
    FOREIGN KEY (`main_area_id`)
    REFERENCES `client`.`area` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = 'The employee has a related User, but this table belongs to the multicompany database: company_user.';


-- -----------------------------------------------------
-- Table `client`.`area`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`area` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `parent_id` INT(11) NULL,
  `principal_id` INT(11) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC),
  INDEX `fk_department_employee1_idx` (`principal_id` ASC),
  INDEX `fk_area_area1_idx` (`parent_id` ASC),
  CONSTRAINT `fk_department_employee1`
    FOREIGN KEY (`principal_id`)
    REFERENCES `client`.`employee` (`id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `fk_area_area1`
    FOREIGN KEY (`parent_id`)
    REFERENCES `client`.`area` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = 'Area is a department or an area within another area.';


-- -----------------------------------------------------
-- Table `client`.`procedure`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`procedure` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `area_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC),
  INDEX `fk_procedure_area1_idx` (`area_id` ASC),
  CONSTRAINT `fk_procedure_area1`
    FOREIGN KEY (`area_id`)
    REFERENCES `client`.`area` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `client`.`training`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`training` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `description` TEXT NULL,
  `type` ENUM('Internal','External') NULL,
  `validity` INT NULL COMMENT 'Vigencia del adiestramiento en meses',
  `cost` FLOAT NULL COMMENT 'Estimated cost',
  `duration` FLOAT NULL COMMENT 'Training duration in hours',
  `sop_id` INT(11) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC),
  INDEX `fk_training_sop1_idx` (`sop_id` ASC),
  UNIQUE INDEX `sop_id_UNIQUE` (`sop_id` ASC),
  CONSTRAINT `fk_training_sop1`
    FOREIGN KEY (`sop_id`)
    REFERENCES `client`.`procedure` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `client`.`assigned_training`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`assigned_training` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `training_id` INT(11) NOT NULL,
  `job_description_id` INT(11) NOT NULL,
  `name` VARCHAR(45) NULL,
  `optional` BIT(1) NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `fk_assigned_training_training1_idx` (`training_id` ASC),
  INDEX `fk_assigned_training_job_description1_idx` (`job_description_id` ASC),
  CONSTRAINT `fk_assigned_training_training1`
    FOREIGN KEY (`training_id`)
    REFERENCES `client`.`training` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_assigned_training_job_description1`
    FOREIGN KEY (`job_description_id`)
    REFERENCES `client`.`job_description` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = 'This table indicates what trainings have been assigned to a Job Description';


-- -----------------------------------------------------
-- Table `client`.`audit`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`audit` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `date` DATE NULL,
  `status` ENUM('new','started','closed') NULL DEFAULT 'new',
  `created_at` DATETIME NULL DEFAULT NULL,
  `updated_at` DATETIME NULL DEFAULT NULL,
  `conforms_total` INT(11) NULL DEFAULT NULL,
  `nonconforms_total` INT(11) NULL DEFAULT NULL,
  `major_nonconforms_total` INT(11) NULL DEFAULT NULL,
  `conforms_percentage` INT(11) NULL DEFAULT NULL,
  `nonconforms_percentage` INT(11) NULL DEFAULT NULL,
  `major_nonconforms_percentage` INT(11) NULL DEFAULT NULL,
  `created_by` INT(11) NULL DEFAULT NULL,
  `updated_by` INT(11) NULL DEFAULT NULL,
  `area_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_audit_area1_idx` (`area_id` ASC),
  CONSTRAINT `fk_audit_area1`
    FOREIGN KEY (`area_id`)
    REFERENCES `client`.`area` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `client`.`standard`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`standard` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `description` VARCHAR(255) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `client`.`standard_section`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`standard_section` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `no` INT NULL,
  `description` TEXT NULL,
  `standard_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_standard_section_standard1_idx` (`standard_id` ASC),
  CONSTRAINT `fk_standard_section_standard1`
    FOREIGN KEY (`standard_id`)
    REFERENCES `client`.`standard` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 8
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `client`.`audit_question`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`audit_question` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  `description` TEXT NULL,
  `guides` TEXT NULL DEFAULT NULL,
  `section_id` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_audit_question_section1_idx` (`section_id` ASC),
  CONSTRAINT `fk_audit_question_section1`
    FOREIGN KEY (`section_id`)
    REFERENCES `client`.`standard_section` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 44
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `client`.`migration`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`migration` (
  `version` VARCHAR(180) NOT NULL,
  `apply_time` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`version`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `client`.`capa_section`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`capa_section` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `description` TEXT NULL,
  `prepared_by` INT(11) NULL DEFAULT NULL,
  `prepared_at` DATETIME NULL DEFAULT NULL,
  `capa_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_capa_section_capa1_idx` (`capa_id` ASC),
  CONSTRAINT `fk_capa_section_capa1`
    FOREIGN KEY (`capa_id`)
    REFERENCES `client`.`capa` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `client`.`capa`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`capa` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `origin` ENUM('Auditoria Interna','Auditoria Externa','Reclamacion Interna','Reclamacion Externa','Otro') NULL,
  `date` DATE NULL,
  `description` TEXT NULL,
  `created_at` DATETIME NULL DEFAULT NULL,
  `updated_at` DATETIME NULL DEFAULT NULL,
  `closed_at` DATETIME NULL DEFAULT NULL,
  `area_id` INT(11) NULL,
  `description_section_id` INT(11) NULL,
  `causes_section_id` INT(11) NULL,
  `short_term_correction_section_id` INT(11) NULL,
  `investigation_section_id` INT(11) NULL,
  `conclusion_section_id` INT(11) NULL,
  `corrective_action_section_id` INT(11) NULL,
  `preventive_action_section_id` INT(11) NULL,
  `final_revision_section_id` INT(11) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_capa_capa_section1_idx` (`description_section_id` ASC),
  INDEX `fk_capa_capa_section2_idx` (`causes_section_id` ASC),
  INDEX `fk_capa_capa_section3_idx` (`short_term_correction_section_id` ASC),
  INDEX `fk_capa_capa_section4_idx` (`investigation_section_id` ASC),
  INDEX `fk_capa_capa_section5_idx` (`conclusion_section_id` ASC),
  INDEX `fk_capa_capa_section6_idx` (`corrective_action_section_id` ASC),
  INDEX `fk_capa_capa_section7_idx` (`preventive_action_section_id` ASC),
  INDEX `fk_capa_capa_section8_idx` (`final_revision_section_id` ASC),
  INDEX `fk_capa_area1_idx` (`area_id` ASC),
  CONSTRAINT `fk_capa_capa_section1`
    FOREIGN KEY (`description_section_id`)
    REFERENCES `client`.`capa_section` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_capa_capa_section2`
    FOREIGN KEY (`causes_section_id`)
    REFERENCES `client`.`capa_section` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_capa_capa_section3`
    FOREIGN KEY (`short_term_correction_section_id`)
    REFERENCES `client`.`capa_section` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_capa_capa_section4`
    FOREIGN KEY (`investigation_section_id`)
    REFERENCES `client`.`capa_section` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_capa_capa_section5`
    FOREIGN KEY (`conclusion_section_id`)
    REFERENCES `client`.`capa_section` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_capa_capa_section6`
    FOREIGN KEY (`corrective_action_section_id`)
    REFERENCES `client`.`capa_section` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_capa_capa_section7`
    FOREIGN KEY (`preventive_action_section_id`)
    REFERENCES `client`.`capa_section` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_capa_capa_section8`
    FOREIGN KEY (`final_revision_section_id`)
    REFERENCES `client`.`capa_section` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_capa_area1`
    FOREIGN KEY (`area_id`)
    REFERENCES `client`.`area` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `client`.`observation`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`observation` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  `type` ENUM('Major','Minor','Recommendation') NULL,
  `reference_documents` TEXT NULL DEFAULT NULL,
  `observation` TEXT NULL DEFAULT NULL,
  `corrective_action` TEXT NULL DEFAULT NULL,
  `answer` TEXT NULL DEFAULT NULL,
  `created_at` DATETIME NULL DEFAULT NULL,
  `updated_at` DATETIME NULL DEFAULT NULL,
  `created_by` INT(11) NULL DEFAULT NULL,
  `updated_by` INT(11) NULL DEFAULT NULL,
  `audit_question_id` INT(11) NULL,
  `capa_id` INT(11) NULL,
  `audit_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_audit_reponse_audit1_idx` (`audit_id` ASC),
  INDEX `fk_audit_reponse_audit_question1_idx` (`audit_question_id` ASC),
  INDEX `fk_observation_capa1_idx` (`capa_id` ASC),
  UNIQUE INDEX `capa_id_UNIQUE` (`capa_id` ASC),
  CONSTRAINT `fk_observation_audit1`
    FOREIGN KEY (`audit_id`)
    REFERENCES `client`.`audit` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_observation_audit_question1`
    FOREIGN KEY (`audit_question_id`)
    REFERENCES `client`.`audit_question` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_observation_capa1`
    FOREIGN KEY (`capa_id`)
    REFERENCES `client`.`capa` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 20
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `client`.`catalog`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`catalog` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(127) NULL DEFAULT NULL,
  `description` TEXT NULL DEFAULT NULL,
  `spec` TEXT NULL DEFAULT NULL,
  `status` SMALLINT(6) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL,
  `updated_at` TIMESTAMP NULL,
  `created_by` VARCHAR(255) NULL DEFAULT NULL,
  `updated_by` VARCHAR(255) NULL DEFAULT NULL,
  `document_name_prefix` VARCHAR(127) NULL DEFAULT NULL,
  `area_id` INT(11) NULL DEFAULT NULL,
  `version` VARCHAR(127) NULL DEFAULT NULL,
  `generated_at` TIMESTAMP NULL,
  `is_approval_workflow` TINYINT(1) NULL DEFAULT NULL,
  `is_approval_type` VARCHAR(255) NULL DEFAULT NULL,
  `is_approval_id` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `client`.`auditor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`auditor` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `created_at` DATETIME NULL,
  `expires_at` DATETIME NULL,
  `employee_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_auditor_employee1_idx` (`employee_id` ASC),
  CONSTRAINT `fk_auditor_employee1`
    FOREIGN KEY (`employee_id`)
    REFERENCES `client`.`employee` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `client`.`procedure_section`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`procedure_section` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `contents` TEXT NULL,
  `procedure_id` INT(11) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_procedure_section_procedure1_idx` (`procedure_id` ASC),
  CONSTRAINT `fk_procedure_section_procedure1`
    FOREIGN KEY (`procedure_id`)
    REFERENCES `client`.`procedure` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `client`.`training_instance`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`training_instance` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `training_id` INT(11) NOT NULL,
  `name` VARCHAR(45) NULL,
  `description` TEXT NULL,
  `date` DATE NULL,
  `location` VARCHAR(45) NULL,
  `trainer_name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_training_instance_training1_idx` (`training_id` ASC),
  CONSTRAINT `fk_training_instance_training1`
    FOREIGN KEY (`training_id`)
    REFERENCES `client`.`training` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `client`.`trainee`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`trainee` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `employee_id` INT(11) NOT NULL,
  `training_id` INT(11) NOT NULL,
  `name` VARCHAR(45) NULL,
  `date` DATE NULL,
  `location` VARCHAR(45) NULL,
  `trainer_name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_training_has_employee_employee1_idx` (`employee_id` ASC),
  INDEX `fk_training_has_employee_training1_idx` (`training_id` ASC),
  CONSTRAINT `fk_training_has_employee_training1`
    FOREIGN KEY (`training_id`)
    REFERENCES `client`.`training_instance` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_training_has_employee_employee1`
    FOREIGN KEY (`employee_id`)
    REFERENCES `client`.`employee` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `client`.`form`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`form` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `procedure_id` INT(11) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_form_procedure1_idx` (`procedure_id` ASC),
  CONSTRAINT `fk_form_procedure1`
    FOREIGN KEY (`procedure_id`)
    REFERENCES `client`.`procedure` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `client`.`input`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`input` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `type` VARCHAR(45) NULL,
  `datatype` ENUM('text','number','boolean') NULL,
  `required` BIT(1) NULL,
  `input_id` INT NULL,
  `form_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_input_input1_idx` (`input_id` ASC),
  INDEX `fk_input_form1_idx` (`form_id` ASC),
  CONSTRAINT `fk_input_input1`
    FOREIGN KEY (`input_id`)
    REFERENCES `client`.`input` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_input_form1`
    FOREIGN KEY (`form_id`)
    REFERENCES `client`.`form` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `client`.`area_assigned`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`area_assigned` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `employee_id` INT(11) NOT NULL,
  `area_id` INT(11) NOT NULL,
  `assigned_at` DATE NULL,
  `unassigned_at` DATE NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_employee_has_area_area1_idx` (`area_id` ASC),
  INDEX `fk_employee_has_area_employee1_idx` (`employee_id` ASC),
  CONSTRAINT `fk_employee_has_area_employee1`
    FOREIGN KEY (`employee_id`)
    REFERENCES `client`.`employee` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_employee_has_area_area1`
    FOREIGN KEY (`area_id`)
    REFERENCES `client`.`area` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `client`.`training_kit`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`training_kit` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `parent_id` INT(11) NOT NULL,
  `child_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_training_kit_training1_idx` (`parent_id` ASC),
  INDEX `fk_training_kit_training2_idx` (`child_id` ASC),
  CONSTRAINT `fk_training_kit_training1`
    FOREIGN KEY (`parent_id`)
    REFERENCES `client`.`training` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_training_kit_training2`
    FOREIGN KEY (`child_id`)
    REFERENCES `client`.`training` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `client`.`area_nesting`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`area_nesting` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `parent_id` INT(11) NOT NULL,
  `child_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_area_nesting_area1_idx` (`parent_id` ASC),
  INDEX `fk_area_nesting_area2_idx` (`child_id` ASC),
  CONSTRAINT `fk_area_nesting_area1`
    FOREIGN KEY (`parent_id`)
    REFERENCES `client`.`area` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_area_nesting_area2`
    FOREIGN KEY (`child_id`)
    REFERENCES `client`.`area` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `client`.`assigned_auditor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`assigned_auditor` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `audit_id` INT(11) NOT NULL,
  `auditor_id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_audit_auditor_audit1_idx` (`audit_id` ASC),
  INDEX `fk_audit_auditor_auditor1_idx` (`auditor_id` ASC),
  CONSTRAINT `fk_audit_auditor_audit1`
    FOREIGN KEY (`audit_id`)
    REFERENCES `client`.`audit` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_audit_auditor_auditor1`
    FOREIGN KEY (`auditor_id`)
    REFERENCES `client`.`auditor` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `client`.`upload`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`upload` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `description` TEXT NULL,
  `type` VARCHAR(45) NULL,
  `size` INT NULL,
  `contents` MEDIUMBLOB NULL,
  `observation_id` INT(11) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_upload_observation1_idx` (`observation_id` ASC),
  CONSTRAINT `fk_upload_observation1`
    FOREIGN KEY (`observation_id`)
    REFERENCES `client`.`observation` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `client`.`action_item`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`action_item` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  `description` TEXT NULL,
  `status` ENUM('Open','Closed') NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  `closed_at` DATETIME NULL,
  `created_by` INT NULL,
  `updated_by` INT NULL,
  `area_id` INT(11) NULL,
  `owner_id` INT(11) NULL,
  `capa_id` INT(11) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_action_item_area1_idx` (`area_id` ASC),
  INDEX `fk_action_item_employee1_idx` (`owner_id` ASC),
  INDEX `fk_action_item_capa1_idx` (`capa_id` ASC),
  CONSTRAINT `fk_action_item_area1`
    FOREIGN KEY (`area_id`)
    REFERENCES `client`.`area` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_action_item_employee1`
    FOREIGN KEY (`owner_id`)
    REFERENCES `client`.`employee` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_action_item_capa1`
    FOREIGN KEY (`capa_id`)
    REFERENCES `client`.`capa` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `client`.`assigned_job_description`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`assigned_job_description` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `employee_id` INT(11) NOT NULL,
  `job_description_id` INT(11) NOT NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  `expires_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_employee_job_description_employee1_idx` (`employee_id` ASC),
  INDEX `fk_employee_job_description_job_description1_idx` (`job_description_id` ASC),
  CONSTRAINT `fk_assigned_job_description_employee1`
    FOREIGN KEY (`employee_id`)
    REFERENCES `client`.`employee` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_assigned_job_description_job_description1`
    FOREIGN KEY (`job_description_id`)
    REFERENCES `client`.`job_description` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `client`.`training_upload`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client`.`training_upload` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `description` TEXT NULL,
  `type` VARCHAR(45) NULL,
  `size` INT NULL,
  `contents` MEDIUMBLOB NULL,
  `training_instance_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_training_upload_training_instance1_idx` (`training_instance_id` ASC),
  CONSTRAINT `fk_training_upload_training_instance1`
    FOREIGN KEY (`training_instance_id`)
    REFERENCES `client`.`training_instance` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


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