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';
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `control_frigo` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
USE `control_frigo`;
-- -----------------------------------------------------
-- Table `control_frigo`.`aux_regiones`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`aux_regiones` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`aux_provincias`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`aux_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 `control_frigo`.`aux_regiones` (`id` )
        ON DELETE RESTRICT  
        ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`aux_comunas`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`aux_comunas` (
    `id` INT(2) 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 `control_frigo`.`aux_provincias` (`id` )
        ON DELETE RESTRICT  
        ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`andenes`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`andenes` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL,
    `lugar` INT(3) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`procedencia_transportes`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`procedencia_transportes` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`destinos_transportes`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`destinos_transportes` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`procedencia_carga`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`procedencia_carga` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`destinos_carga`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`destinos_carga` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`tipos_caja`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`tipos_caja` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL,
    `peso` DECIMAL(4,2) UNSIGNED NOT NULL,
    `unidades_por_pallet` INT(3) UNSIGNED NOT NULL,
    `horas_por_pallet` DECIMAL(2,1) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`controles_carga`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`controles_carga` (
    `id` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL,
    `estado` ENUM('ACTIVO', 'DESVINCULADO') NOT NULL DEFAULT 'ACTIVO',
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`jefes_de_turno`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`jefes_de_turno` (
    `id` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL,
    `estado` ENUM('ACTIVO', 'DESVINCULADO') NOT NULL DEFAULT 'ACTIVO',
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`supervisores`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`supervisores` (
    `id` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL,
    `estado` ENUM('ACTIVO', 'DESVINCULADO') NOT NULL DEFAULT 'ACTIVO',
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`contratistas`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`contratistas` (
    `id` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL,
    `tons_mensuales` INT(5) UNSIGNED NOT NULL DEFAULT 0,
    `estado` ENUM('ACTIVO', 'DESVINCULADO') NOT NULL DEFAULT 'ACTIVO',
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`horquilleros`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`horquilleros` (
    `id` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL,
    `estado` ENUM('ACTIVO', 'DESVINCULADO') NOT NULL DEFAULT 'ACTIVO',
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`movilizadores`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`movilizadores` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    `contratista_id` INT(3) UNSIGNED NOT NULL,
    `nombre` VARCHAR(200) NOT NULL,
    `estado` ENUM('ACTIVO', 'DESVINCULADO') NOT NULL DEFAULT 'ACTIVO',
    INDEX `fk_movilizadores_contratistas` (`contratista_id` ASC) ,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_movilizadores_contratistas`
        FOREIGN KEY (`contratista_id` )
        REFERENCES `control_frigo`.`contratistas` (`id` )
        ON DELETE RESTRICT  
        ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`proveedores`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`proveedores` (
    `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,
    `vigente` ENUM ('SI', 'NO') NOT NULL DEFAULT 'SI',
    PRIMARY KEY (`id`),
    UNIQUE `unique_rut_proveedor` (`rut`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`clientes`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`clientes` (
    `id` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
    `asignado_a` INT(2) UNSIGNED NULL,
    `rut` VARCHAR(9) NOT NULL,
    `razon_social` VARCHAR(100) NOT NULL,
    `nombre_fantasia` VARCHAR(50) NOT NULL,
    `cobro_porteos` ENUM('SI', 'NO') NOT NULL DEFAULT 'SI',
    `tons_contratadas` INT UNSIGNED NOT NULL,
    `max_diario_faenas` INT(2) UNSIGNED NOT NULL,
    `max_diario_tons` INT(3) UNSIGNED NOT NULL,
    `copias_notificaciones` TEXT NULL,
    `dia_facturacion` INT(2) NOT NULL DEFAULT '25',
    `vigente` ('SI', 'NO') NOT NULL DEFAULT 'SI',
    PRIMARY KEY (`id`),
    UNIQUE `unique_rut_cliente` (`rut`),
    INDEX `fk_clientes_controles_carga` (`asignado_a` ASC) ,
    CONSTRAINT `fk_clientes_controles_carga`
        FOREIGN KEY (`asignado_a` )
        REFERENCES `control_frigo`.`controles_carga` (`id` )
        ON DELETE RESTRICT  
        ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`contactos`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`contactos` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    `cliente_id` INT(3) UNSIGNED NOT NULL ,
    `tipo` ENUM('GERENCIA', 'OPERACIONES') NOT NULL,
    `nombre` VARCHAR(100) NOT NULL ,
    `cargo` VARCHAR(40) NOT NULL,
    `telefono` VARCHAR(50) NOT NULL ,
    `comuna` VARCHAR(50) NOT NULL,
    `mail` VARCHAR(100) NOT NULL ,
    `acceso_remoto` ENUM('SI', 'NO') NOT NULL DEFAULT 'NO',
    `clave` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`) ,
    INDEX `fk_contactos_clientes` (`cliente_id` ASC) ,
    UNIQUE `unique_email_contacto` (`mail`) ,
    CONSTRAINT `fk_contactos_clientes`
        FOREIGN KEY (`cliente_id` )
        REFERENCES `control_frigo`.`clientes` (`id` )
        ON DELETE RESTRICT  
        ON UPDATE CASCADE    
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `control_frigo`.`faenas_archivos_categorias`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`faenas_fotografias_categorias` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`faenas_tipos`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`faenas_tipos` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL,
    `direccion` ENUM('IN','OUT') NOT NULL,
    `requiere_anden` ENUM('SI','NO') NOT NULL DEFAULT 'NO',
    `requiere_cliente` ENUM('SI','NO') NOT NULL DEFAULT 'SI',
    `extra` ENUM('SI', 'NO') NOT NULL DEFAULT 'SI',
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`faenas_causales_retrasos`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`faenas_causales_retraso` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL,
    `responsable` ENUM('CLIENTE', 'EMPRESA') NOT NULL,
    `cobra_tiempo` ENUM('SI', 'NO') NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`faenas_causales_suspensiones`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`faenas_causales_suspensiones` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL,
    `responsable` ENUM('CLIENTE', 'EMPRESA') NOT NULL,
    `cobra_tiempo` ENUM('SI', 'NO') NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `control_frigo`.`contenedores_tipos`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`contenedores_tipos` (
  `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(200) NOT NULL,
  `capacidad` INT(6) NOT NULL,
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;