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 `logpro_base` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE `logpro_base`;
-- -----------------------------------------------------
-- Table `logpro_base`.`puertos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro_base`.`puertos`
(
  `id`          VARCHAR(128) NOT NULL,
  `nombre`      VARCHAR(30)  NOT NULL,
  `coordenadas` VARCHAR(100) NULL,
  PRIMARY KEY (`id`)
  )
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro_base`.`puertos_estados`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro_base`.`puertos_estados`
(
  `id`                    INT UNSIGNED                       NOT NULL AUTO_INCREMENT,
  `puerto_id`             VARCHAR(128)                       NOT NULL,
  `consulta_fecha`        DATE                               NOT NULL,
  `consulta_hora`         TIME                               NOT NULL,
  `estado`                VARCHAR(200)                       NOT NULL,
  `fecha_hora_local`      TIMESTAMP                          NOT NULL,
  `condicion`             VARCHAR(50)                        NOT NULL,
  `viento`                VARCHAR(200)                       NOT NULL,
  `mar`                   VARCHAR(100)                       NOT NULL,
  `informacion_adicional` TEXT                               NULL,
  `tag`                   ENUM ('verde', 'amarillo', 'rojo') NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_estados_puertos` (`puerto_id` ASC),
  CONSTRAINT `fk_estados_puertos`
  FOREIGN KEY (`puerto_id`)
  REFERENCES `logpro`.`externos_puertos` (`id`)
  ON DELETE RESTRICT
  ON UPDATE CASCADE
  )
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro_base`.`puertos_alertas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro_base`.`puertos_alertas`
(
  `id`              INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `creacion`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `usuario_id`      INT(2) UNSIGNED NULL,
  `usuario_sistema` VARCHAR(40)     NOT NULL,
  `usuario_email`   VARCHAR(255)    NOT NULL,
  `usuario_nombre`  VARCHAR(255)    NOT NULL,
  `puerto_id`       VARCHAR(100)    NOT NULL,
  `sistema`         VARCHAR(40)     NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_alertas_puertos puertos` (`puerto_id` ASC),
  CONSTRAINT `fk_alertas_puertos_puertos`
    FOREIGN KEY (`puerto_id`)
    REFERENCES `logpro`.`externos_puertos` (`id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
  )
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8
  PACK_KEYS = 0
  ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `logpro_base`.`pos_proveedores_accesos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro_base`.`pos_proveedores_accesos`
(
  `id` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `sistema` VARCHAR(20) NOT NULL,
  `nombre` VARCHAR(50) NOT NULL,
  `usuario` VARCHAR(40) NULL,
  `clave` VARCHAR(40) NULL,
  `data_access_content` TEXT NULL,
  `data_access_expire` DATE NULL,
  `estado` ENUM('HABILITADO', 'DESHABILITADO') NOT NULL DEFAULT 'HABILITADO',
  PRIMARY KEY (`id`)
  )
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
  -- -----------------------------------------------------
-- Table `logpro_base`.`pos_grupos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro_base`.`pos_grupos`
(
  `id`INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(40) NOT NULL,
  `color` VARCHAR(20) NOT NULL DEFAULT 'arrow-blue',
  `mostrar` ENUM('SI','NO') NOT NULL DEFAULT 'SI',
  `sistema` VARCHAR(40) NOT NULL,
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro_base`.`pos_assets`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro_base`.`pos_assets`
(
  `id` VARCHAR(40)  NOT NULL ,
  `creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `grupo_id` INT(3) UNSIGNED NOT NULL DEFAULT '1',
  `activo_tipo` ENUM('NAVE', 'CAMION') NOT NULL DEFAULT 'NAVE',
  `activo_nombre` VARCHAR(40) NOT NULL,
  `activo_sistema` VARCHAR(40) NOT NULL,
  `posicionador_codigo` VARCHAR(40) NOT NULL,
  `posicionador_acceso_id` INT(3) UNSIGNED NOT NULL,
  `estado` ENUM('HABILITADO', 'DESHABILITADO') NOT NULL DEFAULT 'HABILITADO',
  `last_position_latitud` VARCHAR(40) NULL,
  `last_position_longitud` VARCHAR(40) NULL,
  `last_position_timestamp` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_assets_proveedores_accesos` (`posicionador_acceso_id` ASC),
  INDEX `fk_assets_grupos` (`grupo_id` ASC),
  CONSTRAINT `fk_assets_proveedores_accesos`
    FOREIGN KEY (`posicionador_acceso_id`)
    REFERENCES `logpro_base`.`pos_proveedores_accesos` (`id`)
      ON DELETE RESTRICT
      ON UPDATE CASCADE,
  CONSTRAINT `fk_assets_grupos`
    FOREIGN KEY (`grupo_id`)
    REFERENCES `logpro_base`.`pos_grupos` (`id`)
      ON DELETE RESTRICT
      ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
  -- -----------------------------------------------------
  -- Table `logpro_base`.`pos_positions`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `logpro_base`.`pos_positions`
(
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `asset_id` VARCHAR(40) NOT NULL,
  `latitud` VARCHAR(20) NOT NULL,
  `longitud` VARCHAR(20) NOT NULL,
  `timestamp` TIMESTAMP NOT NULL,
  `data_raw` TEXT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_assets_positions_assets` (`asset_id` ASC),
  CONSTRAINT `fk_assets_positions_assets`
    FOREIGN KEY (`asset_id`)
    REFERENCES `logpro_base`.`pos_assets` (`id`)
      ON DELETE RESTRICT
      ON UPDATE CASCADE
  )
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro_base`.`geo_barrios`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro_base`.`geo_barrios`
(
  `id`               INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre`           VARCHAR(5)      NOT NULL,
  `data_coordenadas` TEXT            NULL,
  `lugar`            INT(3) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `logpro`.`responsables_transportes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro_base`.`responsables_transportes`
(
  `rut`        VARCHAR(12)     NOT NULL,
  `nombre`     VARCHAR(200)    NOT NULL,
  PRIMARY KEY (`rut`)
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
  -- -----------------------------------------------------
-- Table `logpro`.`responsables_transportes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `logpro_base`.`responsables_transportes_empresas`
(
  `id`                          VARCHAR(20)     NOT NULL,
  `rut`                         VARCHAR(12)     NOT NULL,
  `sistema`                     VARCHAR(20)     NOT NULL,
  `empresa_id`                  INT(3) UNSIGNED NOT NULL,
  `empresa_razon_social`        VARCHAR(200)    NOT NULL,
  `telefono`                    VARCHAR(20)     NULL,
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- VISTA RESPONSABLES TRANSPORTES
-- -----------------------------------------------------
-- Table `logpro`.`vista_responsables_transportes`
-- -----------------------------------------------------
CREATE VIEW `logpro_base`.`vista_responsables_transportes` AS
SELECT
	rte.id,
  rte.rut,
  rt.nombre,
  rte.sistema,
  rte.empresa_id,
  rte.empresa_razon_social,
  rte.telefono
FROM responsables_transportes rt
JOIN responsables_transportes_empresas rte ON rte.rut = rt.rut;
-- ------------------------------------------------------