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`.`empresas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`empresas`
(
  `id`                   INT(3) UNSIGNED   NOT NULL AUTO_INCREMENT,
  `rut`                  VARCHAR(9)        NOT NULL,
  `razon_social`         VARCHAR(100)      NOT NULL,
  `nombre_fantasia`      VARCHAR(50)       NOT NULL,
  `direccion`            VARCHAR(100)      NULL,
  `telefono`             VARCHAR(30)       NOT NULL,
  `ciudad`               VARCHAR(30)       NOT NULL,
  `comuna`               VARCHAR(30)       NOT NULL,
  `giro`                 VARCHAR(200)      NOT NULL,
  `mail`                 VARCHAR(100)      NOT NULL,
  `web`                  VARCHAR(100)      NOT NULL,
  `fax`                  VARCHAR(30)       NOT NULL,
  `propietario`          ENUM ('SI', 'NO') NOT NULL DEFAULT 'NO',
  `entrega_combustible`  ENUM ('SI', 'NO') NOT NULL DEFAULT 'NO',
  `proveedor_transporte` ENUM ('SI', 'NO') NOT NULL DEFAULT 'NO',
  `mostrar`              ENUM ('SI', 'NO') NOT NULL DEFAULT 'NO',
  PRIMARY KEY (`id`),
  UNIQUE `unique_empresa` (`rut`)
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8
  COLLATE = utf8_general_ci
  PACK_KEYS = 0
  ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `logpro`.`etiquetas_archivos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`etiquetas_archivos`
(
  `id`         INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `creacion`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `creador_id` INT(2) UNSIGNED NOT NULL,
  `nombre`     VARCHAR(200)    NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_etiquetas_archivos_usuarios` (`creador_id` ASC),
  CONSTRAINT `fk_etiquetas_archivos_usuarios`
  FOREIGN KEY (`creador_id`)
  REFERENCES `logpro`.`sistema_usuarios` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`nacionalidades`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`nacionalidades`
(
  `id`     INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(200)    NOT NULL,
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`regiones`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`regiones`
(
  `id`     INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(200)    NOT NULL,
  `numero` VARCHAR(4)      NOT NULL,
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`provincias`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`provincias`
(
  `id`        INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `region_id` INT(3) UNSIGNED NOT NULL,
  `nombre`    VARCHAR(200)    NOT NULL,
  INDEX `fk_provincias_regiones` (`region_id` ASC),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_provincias_regiones`
  FOREIGN KEY (`region_id`)
  REFERENCES `logpro`.`regiones` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`comunas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`comunas`
(
  `id`           INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `provincia_id` INT(3) UNSIGNED NOT NULL,
  `nombre`       VARCHAR(200)    NOT NULL,
  INDEX `fk_comunas_provincias` (`provincia_id` ASC),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_comunas_provincias`
  FOREIGN KEY (`provincia_id`)
  REFERENCES `logpro`.`provincias` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`zonas_comerciales`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`zonas_comerciales`
(
  `id`        INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre`    VARCHAR(5)      NOT NULL,
  `ubicacion` VARCHAR(200)    NOT NULL,
  `lugar`     INT(3) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `logpro`.`contab_proveedores_tipos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`contab_proveedores_tipos`
(
  `id`         INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `creacion`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `creador_id` INT(2) UNSIGNED NOT NULL,
  `nombre`     VARCHAR(200)    NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_contrab_proveedores_tipos_creadores` (`creador_id` ASC),
  CONSTRAINT `fk_contrab_proveedores_tipos_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`.`contab_proveedores`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`contab_proveedores`
(
  `id`              INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `creacion`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `creador_id`      INT(2) UNSIGNED NOT NULL,
  `tipo_id`         INT(2) UNSIGNED NOT NULL,
  `razon_social`    VARCHAR(100)    NOT NULL,
  `nombre_fantasia` VARCHAR(30)     NOT NULL,
  `rut`             VARCHAR(12)     NOT NULL,
  `giro`            VARCHAR(100)    NULL,
  `direccion`       VARCHAR(100)    NULL,
  `telefono`        VARCHAR(30)     NULL,
  `url`             TEXT            NULL,
  PRIMARY KEY (`id`),
  UNIQUE `unique_proveedor` (`rut`),
  INDEX `fk_contab_proveedores_creadores` (`creador_id` ASC),
  INDEX `fk_contab_proveedores_tipos` (`tipo_id` ASC),
  CONSTRAINT `fk_contab_proveedores_creadores`
  FOREIGN KEY (`creador_id`)
  REFERENCES `logpro`.`sistema_usuarios` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_contab_proveedores_tipos`
  FOREIGN KEY (`tipo_id`)
  REFERENCES `logpro`.`contab_proveedores` (`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`.`contab_cuentas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`contab_cuentas`
(
  `id`         VARCHAR(20)     NOT NULL,
  `creacion`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
  ON UPDATE CURRENT_TIMESTAMP,
  `creador_id` INT(2) UNSIGNED NOT NULL,
  `cuenta_id`  VARCHAR(20)     NULL,
  `nombre`     VARCHAR(100)    NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_contab_cuentas_creadores` (`creador_id` ASC),
  INDEX `fk_contab_cuentas_cuentas` (`cuenta_id` ASC),
  CONSTRAINT `fk_contab_cuentas_creadores`
  FOREIGN KEY (`creador_id`)
  REFERENCES `logpro`.`sistema_usuarios` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_contab_cuentas_cuentas`
  FOREIGN KEY (`cuenta_id`)
  REFERENCES `logpro`.`contab_cuentas` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`contab_centros_costo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`contab_centros_costo`
(
  `id`         VARCHAR(20)     NOT NULL,
  `creacion`   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `creador_id` INT(2) UNSIGNED NOT NULL,
  `centro_id`  VARCHAR(20)     NULL,
  `unidad_id`  VARCHAR(20)     NULL,
  `nombre`     VARCHAR(100)    NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_contab_centros_costo_creadores` (`creador_id` ASC),
  INDEX `fk_contab_centros_costo_centros` (`centro_id` ASC),
  INDEX `fk_contab_centros_costo_unidades` (`unidad_id` ASC),
  CONSTRAINT `fk_contab_centros_costo_creadores`
    FOREIGN KEY (`creador_id`)
      REFERENCES `logpro`.`sistema_usuarios` (`id`)
      ON DELETE RESTRICT
      ON UPDATE CASCADE,
  CONSTRAINT `fk_contab_centros_costo_centros`
    FOREIGN KEY (`centro_id`)
      REFERENCES `logpro`.`contab_centros_costo` (`id`)
      ON DELETE RESTRICT
      ON UPDATE CASCADE,
  CONSTRAINT `fk_contab_centros_costo_unidades`
    FOREIGN KEY (`unidad_id`)
      REFERENCES `logpro`.`control_unidades` (`id`)
      ON DELETE RESTRICT
      ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`enlaces_utiles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`enlaces_utiles`
(
  `id`         INT(2) UNSIGNED   NOT NULL AUTO_INCREMENT,
  `referencia` VARCHAR(200)      NOT NULL,
  `url`        TEXT              NOT NULL,
  `widget`     ENUM ('NO', 'SI') NOT NULL DEFAULT 'NO',
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`encuestas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`encuestas`
(
  `id`          INT(3) UNSIGNED   NOT NULL AUTO_INCREMENT,
  `creacion`    TIMESTAMP         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `creador_id`  INT(3) UNSIGNED   NOT NULL,
  `nombre`      VARCHAR(50)       NOT NULL,
  `descripcion` VARCHAR(250)      NULL,
  `activa`      ENUM ('SI', 'NO') NOT NULL DEFAULT 'NO',
  PRIMARY KEY (`id`),
  INDEX `fk_encuestas_creadores` (`creador_id` ASC),
  CONSTRAINT `fk_encuestas_creadores`
    FOREIGN KEY (`creador_id`)
      REFERENCES `logpro`.`sistema_usuarios` (`id`)
      ON DELETE RESTRICT
      ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`encuestas_preguntas_grupos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`encuestas_preguntas_grupos`
(
  `id`          INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
  `creacion`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
  ON UPDATE CURRENT_TIMESTAMP,
  `creador_id`  INT(3) UNSIGNED NOT NULL,
  `encuesta_id` INT(3) UNSIGNED NOT NULL,
  `nombre`      VARCHAR(50)     NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_encuestas_preguntas_grupos_creadores` (`creador_id` ASC),
  INDEX `fk_encuestas_preguntas_grupos_encuestas` (`encuesta_id` ASC),
  CONSTRAINT `fk_encuestas_preguntas_grupos_creadores`
  FOREIGN KEY (`creador_id`)
  REFERENCES `logpro`.`sistema_usuarios` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_encuestas_preguntas_grupos_encuestas`
  FOREIGN KEY (`encuesta_id`)
  REFERENCES `logpro`.`encuestas` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`encuestas_preguntas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`encuestas_preguntas`
(
  `id`            INT(6) UNSIGNED   NOT NULL AUTO_INCREMENT,
  `creacion`      TIMESTAMP         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `creador_id`    INT(3) UNSIGNED   NOT NULL,
  `encuesta_id`   INT(3) UNSIGNED   NOT NULL,
  `grupo_id`      INT(2) UNSIGNED   NOT NULL,
  `enunciado`     VARCHAR(50)       NOT NULL,
  `tipo`          VARCHAR(20)       NOT NULL,
  `configuracion` TEXT              NOT NULL,
  `opcional`      ENUM ('SI', 'NO') NOT NULL DEFAULT 'NO',
  PRIMARY KEY (`id`),
  INDEX `fk_encuestas_preguntas_creadores` (`creador_id` ASC),
  INDEX `fk_encuestas_preguntas_grupos` (`grupo_id` ASC),
  CONSTRAINT `fk_encuestas_preguntas_creadores`
    FOREIGN KEY (`creador_id`)
      REFERENCES `logpro`.`sistema_usuarios` (`id`)
      ON DELETE RESTRICT
      ON UPDATE CASCADE,
  CONSTRAINT `fk_encuestas_preguntas_grupos`
  FOREIGN KEY (`grupo_id`)
  REFERENCES `logpro`.`encuestas_preguntas_grupos` (`id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`archivos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`archivos`
(
  `id`          VARCHAR(40)            NOT NULL,
  `creacion`    TIMESTAMP              NOT NULL DEFAULT CURRENT_TIMESTAMP
  ON UPDATE CURRENT_TIMESTAMP,
  `creador_id`  INT(3)        UNSIGNED NOT NULL,
  `bucket`      VARCHAR(100)           NOT NULL,
  `storageRef`  VARCHAR(255)           NOT NULL,
  `descargaUrl` TEXT                   NOT NULL,
  `nombre`      VARCHAR(100)           NOT NULL,
  `peso`        VARCHAR(20)            NOT NULL,
  `mime`        VARCHAR(100)           NOT NULL,
  `hash`        VARCHAR(50)            NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_archivos_usuarios` (`creador_id` ASC),
  CONSTRAINT `fk_archivos_usuarios`
  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`.`archivos_log`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro`.`archivos_log`
(
  `id`             INT UNSIGNED                            NOT NULL AUTO_INCREMENT,
  `creacion`       TIMESTAMP                               NOT NULL DEFAULT CURRENT_TIMESTAMP
  ON UPDATE CURRENT_TIMESTAMP,
  `usuario_id`     INT(3)        UNSIGNED                  NOT NULL,
  `usuario_nombre` VARCHAR(200)                            NOT NULL,
  `archivo_id`     VARCHAR(40)                             NOT NULL,
  `archivo_ref`    VARCHAR(255)                            NOT NULL,
  `request_ip`     VARCHAR(100)                            NOT NULL,
  `request_url`    VARCHAR(200)                            NOT NULL,
  `request_agent`  VARCHAR(100)                            NOT NULL,
  `operacion`      ENUM ('CREAR', 'DESCARGAR', 'ELIMINAR') NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `index_descargas_usuarios` (`usuario_id` ASC),
  INDEX `index_descargas_archivos` (`archivo_id` ASC)
)
  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;