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';
-- -----------------------------------------------------
USE `logpro`;
-- ----------------------------------------------------
-- Table `logpro`.`trabajadores`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`trabajadores` (
  `id`               VARCHAR(20)                 NOT NULL,
  `creacion`         TIMESTAMP                   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `creador_id`       INT(3) UNSIGNED             NOT NULL,
  `nacionalidad`     VARCHAR(30)                 NOT NULL,
  `contrato`         ENUM ('INTERNO', 'EXTERNO') NOT NULL DEFAULT 'INTERNO',
  `nombre`           VARCHAR(100)                NOT NULL,
  `dni`              VARCHAR(20)                 NOT NULL,
  `fecha_nacimiento` DATE                        NOT NULL,
  `sexo`             ENUM ('M', 'F')             NOT NULL,
  `activo`           ENUM ('SI', 'NO')           NOT NULL DEFAULT 'SI',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `unique_trabajador` (`dni` ASC),
  INDEX `fk_trabajadores_creadores` (`creador_id` ASC),
  CONSTRAINT `fk_trabajadores_creadores`
  FOREIGN KEY (`creador_id`)
  REFERENCES `logpro`.`sistema_usuarios` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8
  COLLATE = utf8_general_ci
  PACK_KEYS = 0
  ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `logpro`.`accidentes_clasificaciones`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `logpro`.`accidentes_clasificaciones` (
  `id`                INT(2) UNSIGNED   NOT NULL AUTO_INCREMENT,
  `nombre`            VARCHAR(100)      NOT NULL,
  `reinicia_contador` ENUM ('SI', 'NO') NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`accidentes_partes_lesionadas`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `logpro`.`accidentes_partes_lesionadas` (
  `id`     INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(100)    NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`accidentes_naturaleza_lesiones`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `logpro`.`accidentes_naturaleza_lesiones` (
  `id`     INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(100)    NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`accidentes_tipos_lesiones`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `logpro`.`accidentes_tipos_lesiones` (
  `id`     INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(100)    NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`accidentes_agentes`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `logpro`.`accidentes_agentes` (
  `id`     INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(100)    NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`accidentes_causas_inmediatas_actos`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `logpro`.`accidentes_causas_inmediatas_actos` (
  `id`     INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(100)    NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`accidentes_causas_inmediatas_condiciones`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `logpro`.`accidentes_causas_inmediatas_condiciones` (
  `id`     INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(100)    NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`accidentes_causas_basicas_personales`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `logpro`.`accidentes_causas_basicas_personales` (
  `id`       INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `causa_id` INT(2) UNSIGNED NULL,
  `nombre`   VARCHAR(100)    NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_accidentes_causas_basicas_personales_causas` (`causa_id` ASC),
  CONSTRAINT `fk_accidentes_causas_basicas_personales_causas`
  FOREIGN KEY (`causa_id`)
  REFERENCES `logpro`.`accidentes_causas_basicas_personales` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`accidentes_causas_basicas_trabajo`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `logpro`.`accidentes_causas_basicas_trabajo` (
  `id`     INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(100)    NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`accidentes_informes`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `logpro`.`accidentes_informes` (
  `id`                                 INT(2) UNSIGNED                          NOT NULL AUTO_INCREMENT,
  `creacion`                           TIMESTAMP                                NOT NULL DEFAULT CURRENT_TIMESTAMP
  ON UPDATE CURRENT_TIMESTAMP,
  `creador_id`                         INT(3) UNSIGNED                          NOT NULL,
  `tipo_contrato`                      ENUM ('PROPIO', 'SUBCONTRATO')           NOT NULL,
  `unidad_id`                          VARCHAR(40)                              NULL,
  `trabajador_id`                      VARCHAR(30)                              NULL,
  `lugar_accidente`                    VARCHAR(200)                             NOT NULL,
  `fecha`                              DATE                                     NOT NULL,
  `hora`                               TIME                                     NOT NULL,
  `jefe_directo`                       VARCHAR(50)                              NOT NULL,
  `afectado_nombre`                    VARCHAR(100)                             NOT NULL,
  `afectado_rut`                       VARCHAR(9)                               NOT NULL,
  `afectado_edad`                      INT(2) UNSIGNED                          NOT NULL,
  `afectado_cargo`                     VARCHAR(50)                              NOT NULL,
  `afectado_afp`                       VARCHAR(30)                              NOT NULL,
  `afectado_isapre`                    VARCHAR(30)                              NOT NULL,
  `afectado_anos_puesto_actual`        VARCHAR(20)                              NOT NULL,
  `horas_trabajadas_previo_accidente`  VARCHAR(10)                              NOT NULL,
  `clasificacion_id`                   INT(2) UNSIGNED                          NOT NULL,
  `origen_identificado_listado`        ENUM ('SI', 'NO')                        NOT NULL,
  `descripcion_danos_materiales`       TEXT                                     NULL,
  `descripcion_accidente`              TEXT                                     NULL,
  `supervision_presente`               ENUM ('SI', 'NO', 'OTRAS LABORES FAENA') NOT NULL,
  `partes_lesionadas`                  VARCHAR(30)                              NULL,
  `naturalezas_lesiones`               VARCHAR(30)                              NULL,
  `tipos_lesiones`                     VARCHAR(30)                              NULL,
  `agentes`                            VARCHAR(30)                              NULL,
  `causas_inmediatas_actos`            VARCHAR(30)                              NULL,
  `causas_inmediatas_condiciones`      VARCHAR(30)                              NULL,
  `causas_basicas_personales`          VARCHAR(30)                              NULL,
  `causas_basicas_trabajo`             VARCHAR(30)                              NULL,
  `evaluacion_probabilidad_ocurrencia` ENUM ('FRECUENTE', 'OCASIONAL', 'RARO')  NOT NULL,
  `evaluacion_consecuencias`           ENUM ('GRAVE', 'SERIA', 'LEVE')          NOT NULL,
  `investigado_por`                    INT(3) UNSIGNED                          NULL,
  `investigado_fecha`                  DATE                                     NULL,
  `revisado_por`                       INT(3) UNSIGNED                          NULL,
  `revisado_fecha`                     DATE                                     NULL,
  `aprobado_por`                       INT(3) UNSIGNED                          NULL,
  `aprobado_fecha`                     DATE                                     NULL,
  `estado`                             ENUM ('EN CURSO', 'CERRADO')             NOT NULL DEFAULT 'EN CURSO',
  PRIMARY KEY (`id`),
  INDEX `fk_accidentes_informes_creadores` (`creador_id` ASC),
  INDEX `fk_accidentes_informes_unidades` (`unidad_id` ASC),
  INDEX `fk_accidentes_informes_clasificaciones` (`clasificacion_id` ASC),
  INDEX `fk_accidentes_informes_investigadores` (`investigado_por` ASC),
  INDEX `fk_accidentes_informes_revisores` (`revisado_por` ASC),
  INDEX `fk_accidentes_informes_aprobadores` (`aprobado_por` ASC),
  CONSTRAINT `fk_accidentes_informes_creadores`
  FOREIGN KEY (`creador_id`)
  REFERENCES `logpro`.`sistema_usuarios` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_accidentes_informes_unidades`
  FOREIGN KEY (`unidad_id`)
  REFERENCES `logpro`.`control_unidades` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_accidentes_informes_clasificaciones`
  FOREIGN KEY (`clasificacion_id`)
  REFERENCES `logpro`.`accidentes_clasificaciones` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_accidentes_informes_investigadores`
  FOREIGN KEY (`investigado_por`)
  REFERENCES `logpro`.`sistema_usuarios` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_accidentes_informes_revisores`
  FOREIGN KEY (`revisado_por`)
  REFERENCES `logpro`.`sistema_usuarios` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_accidentes_informes_aprobadores`
  FOREIGN KEY (`aprobado_por`)
  REFERENCES `logpro`.`sistema_usuarios` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `logpro`.`accidentes_testigos`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `logpro`.`accidentes_testigos` (
  `id`   INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `accidente_id` INT(3) UNSIGNED NOT NULL,
  `nombre` VARCHAR(100) NOT NULL,
  `rut` VARCHAR(100) NOT NULL,
  `cargo` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_accidentes_testigos_accidentes` (`accidente_id` ASC),
  CONSTRAINT `fk_accidentes_testigos_accidentes`
  FOREIGN KEY (`accidente_id`)
  REFERENCES `logpro`.`accidentes_informes` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `logpro`.`accidentes_medidas_correctivas`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `logpro`.`accidentes_medidas_correctivas` (
  `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `accidente_id` INT(3) UNSIGNED NOT NULL,
  `descripcion` TEXT NOT NULL,
  `responsable` VARCHAR(100) NOT NULL,
  `fecha_cumplimiento` DATE NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_accidentes_medidas_correctivas_accidentes` (`accidente_id` ASC),
  CONSTRAINT `fk_accidentes_medidas_correctivas_accidentes`
  FOREIGN KEY (`accidente_id`)
  REFERENCES `logpro`.`accidentes_informes` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `logpro`.`accidentes_archivos`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `logpro`.`accidentes_archivos` (
  `id`           INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `creacion`     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `creador_id`   INT(3) UNSIGNED NOT NULL,
  `accidente_id` INT(3) UNSIGNED NOT NULL,
  `archivo`      VARCHAR(200)    NOT NULL,
  `nombre`       VARCHAR(100)    NOT NULL,
  `peso`         INT UNSIGNED    NULL,
  `mime`         VARCHAR(20)     NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_accidentes_archivos_creadores` (`creador_id` ASC),
  INDEX `fk_accidentes_archivos_accidentes` (`accidente_id` ASC),
  CONSTRAINT `fk_accidentes_archivos_accidentes`
  FOREIGN KEY (`accidente_id`)
  REFERENCES `logpro`.`accidentes_informes` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_accidentes_archivos_creadores`
  FOREIGN KEY (`creador_id`)
  REFERENCES `logpro`.`sistema_usuarios` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `logpro`.`accidentes_declaraciones`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `logpro`.`accidentes_declaraciones` (
  `id`           INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `accidente_id` INT(3) UNSIGNED NOT NULL,
  `nombre`       VARCHAR(100)    NOT NULL,
  `declaracion`  TEXT            NOT NULL,
  `fecha`        DATE            NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_accidentes_declaraciones_accidentes` (`accidente_id` ASC),
  CONSTRAINT `fk_accidentes_declaraciones_accidentes`
  FOREIGN KEY (`accidente_id`)
  REFERENCES `logpro`.`accidentes_informes` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;