-- Adminer 4.7.9 MySQL dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

SET NAMES utf8mb4;

DROP TABLE IF EXISTS `almacen`;
CREATE TABLE `almacen` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activo` int(11) NOT NULL,
  `ubigeo` varchar(10) NOT NULL,
  `departamento` varchar(100) NOT NULL,
  `provincia` varchar(100) NOT NULL,
  `latitud` double NOT NULL,
  `longitud` double NOT NULL,
  `esPrincipal` int(11) NOT NULL,
  `region_fk` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `region_fk` (`region_fk`),
  CONSTRAINT `almacen_ibfk_1` FOREIGN KEY (`region_fk`) REFERENCES `region` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `averia`;
CREATE TABLE `averia` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activo` int(11) NOT NULL,
  `descripcion` varchar(1000) NOT NULL,
  `fechaRegistro` datetime NOT NULL,
  `camion_fk` int(11) NOT NULL,
  `tipoAveria_fk` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `camion_fk` (`camion_fk`),
  KEY `tipoAveria_fk` (`tipoAveria_fk`),
  CONSTRAINT `averia_ibfk_1` FOREIGN KEY (`camion_fk`) REFERENCES `camion` (`id`),
  CONSTRAINT `averia_ibfk_2` FOREIGN KEY (`tipoAveria_fk`) REFERENCES `tipoAveria` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `camion`;
CREATE TABLE `camion` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activo` int(11) NOT NULL,
  `placa` varchar(10) NOT NULL,
  `kilometraje` double NOT NULL,
  `estado` int(11) NOT NULL,
  `tipoCamion_fk` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tipoCamion_fk` (`tipoCamion_fk`),
  CONSTRAINT `camion_ibfk_1` FOREIGN KEY (`tipoCamion_fk`) REFERENCES `tipoCamion` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `cliente`;
CREATE TABLE `cliente` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activo` int(11) NOT NULL,
  `nombreCompleto` varchar(100) NOT NULL,
  `dni` varchar(10) NOT NULL,
  `correo` varchar(100) NOT NULL,
  `telefono` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `pedido`;
CREATE TABLE `pedido` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activo` int(11) NOT NULL,
  `fechaPedido` datetime NOT NULL,
  `cantidad` int(11) NOT NULL,
  `plazoEntrega` int(11) NOT NULL,
  `estado` int(11) NOT NULL,
  `codigoPedido` varchar(100) DEFAULT NULL,
  `almacen_fk` int(11) NOT NULL,
  `cliente_fk` int(11) NOT NULL,
  `usuario_fk` int(11) NOT NULL,
  `PTG_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `usuario_fk` (`usuario_fk`),
  KEY `PTG_fk` (`PTG_fk`),
  KEY `cliente_fk` (`cliente_fk`),
  KEY `almacen_fk` (`almacen_fk`),
  CONSTRAINT `pedido_ibfk_1` FOREIGN KEY (`usuario_fk`) REFERENCES `usuario` (`id`),
  CONSTRAINT `pedido_ibfk_2` FOREIGN KEY (`PTG_fk`) REFERENCES `PTG` (`id`),
  CONSTRAINT `pedido_ibfk_3` FOREIGN KEY (`cliente_fk`) REFERENCES `cliente` (`id`),
  CONSTRAINT `pedido_ibfk_4` FOREIGN KEY (`almacen_fk`) REFERENCES `almacen` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `PTG`;
CREATE TABLE `PTG` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activo` int(11) NOT NULL,
  `fechaGenerado` datetime NOT NULL,
  `esSimulacion` int(11) NOT NULL,
  `usuario_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `usuario_fk` (`usuario_fk`),
  CONSTRAINT `PTG_ibfk_1` FOREIGN KEY (`usuario_fk`) REFERENCES `usuario` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `region`;
CREATE TABLE `region` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activo` int(11) NOT NULL,
  `nombre` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `rol`;
CREATE TABLE `rol` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activo` int(11) NOT NULL,
  `nombre` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `ruta`;
CREATE TABLE `ruta` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activo` int(11) NOT NULL,
  `fechaInicio` datetime NOT NULL,
  `fechaFin` datetime NOT NULL,
  `PTG_fk` int(11) NOT NULL,
  `camion_fk` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `PTG_fk` (`PTG_fk`),
  KEY `camion_fk` (`camion_fk`),
  CONSTRAINT `ruta_ibfk_1` FOREIGN KEY (`PTG_fk`) REFERENCES `PTG` (`id`),
  CONSTRAINT `ruta_ibfk_2` FOREIGN KEY (`camion_fk`) REFERENCES `camion` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `tipoAveria`;
CREATE TABLE `tipoAveria` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activo` int(11) NOT NULL,
  `nombre` int(11) NOT NULL,
  `duracion` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `tipoCamion`;
CREATE TABLE `tipoCamion` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activo` int(11) NOT NULL,
  `nombre` varchar(100) NOT NULL,
  `capacidad` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `tramo`;
CREATE TABLE `tramo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activo` int(11) NOT NULL,
  `distancia` double NOT NULL,
  `estaBloqueado` int(11) NOT NULL,
  `almacen1_fk` int(11) NOT NULL,
  `almacen2_fk` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `almacen1_fk` (`almacen1_fk`),
  KEY `almacen2_fk` (`almacen2_fk`),
  CONSTRAINT `tramo_ibfk_1` FOREIGN KEY (`almacen1_fk`) REFERENCES `almacen` (`id`),
  CONSTRAINT `tramo_ibfk_2` FOREIGN KEY (`almacen2_fk`) REFERENCES `almacen` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `tramo_ruta`;
CREATE TABLE `tramo_ruta` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activo` int(11) NOT NULL,
  `orden` int(11) NOT NULL,
  `horaSalidaEst` datetime NOT NULL,
  `horaLlegadaEst` datetime NOT NULL,
  `horaSalidaReal` datetime DEFAULT NULL,
  `horaLlegadaReal` datetime DEFAULT NULL,
  `paquetesEntregar` int(11) NOT NULL,
  `ruta_fk` int(11) NOT NULL,
  `tramo_fk` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ruta_fk` (`ruta_fk`),
  KEY `tramo_fk` (`tramo_fk`),
  CONSTRAINT `tramo_ruta_ibfk_1` FOREIGN KEY (`ruta_fk`) REFERENCES `ruta` (`id`),
  CONSTRAINT `tramo_ruta_ibfk_2` FOREIGN KEY (`tramo_fk`) REFERENCES `tramo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `usuario`;
CREATE TABLE `usuario` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activo` int(11) NOT NULL,
  `usuario` varchar(100) NOT NULL,
  `clave` varchar(100) NOT NULL,
  `nombre` varchar(100) NOT NULL,
  `apellido` varchar(100) NOT NULL,
  `dni` varchar(10) NOT NULL,
  `correo` varchar(100) NOT NULL,
  `creador_fk` int(11) DEFAULT NULL,
  `rol_fk` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `creador_fk` (`creador_fk`),
  KEY `rol_fk` (`rol_fk`),
  CONSTRAINT `usuario_ibfk_2` FOREIGN KEY (`creador_fk`) REFERENCES `usuario` (`id`),
  CONSTRAINT `usuario_ibfk_3` FOREIGN KEY (`rol_fk`) REFERENCES `usuario` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP VIEW IF EXISTS `v_almacen`;
CREATE TABLE `v_almacen` (`ubigeo` varchar(10), `departamento` varchar(100), `provincia` varchar(100), `latitud` double, `longitud` double, `esPrincipal` int(11), `region` varchar(100));


DROP VIEW IF EXISTS `v_camion`;
CREATE TABLE `v_camion` (`id` int(11), `placa` varchar(10), `kilometraje` double, `estado` int(11), `tipo` varchar(100), `capacidad` int(11));


DROP VIEW IF EXISTS `v_pedido`;
CREATE TABLE `v_pedido` (`cliente` varchar(100), `RUC` varchar(10), `ubigeo` varchar(10), `provincia` varchar(100), `cantidad` int(11), `fechaPedido` datetime, `plazoEntrega` int(11), `estado` int(11), `URL_pedido` varchar(100), `registrado por` varchar(100));


DROP VIEW IF EXISTS `v_usuario`;
CREATE TABLE `v_usuario` (`usuario` varchar(100), `nombre` varchar(100), `apellido` varchar(100), `dni` varchar(10), `correo` varchar(100), `registered by` varchar(100), `rol` varchar(100));


DROP TABLE IF EXISTS `v_almacen`;
CREATE ALGORITHM=UNDEFINED DEFINER=`gabs`@`%.%.%.%` SQL SECURITY DEFINER VIEW `v_almacen` AS select `a`.`ubigeo` AS `ubigeo`,`a`.`departamento` AS `departamento`,`a`.`provincia` AS `provincia`,`a`.`latitud` AS `latitud`,`a`.`longitud` AS `longitud`,`a`.`esPrincipal` AS `esPrincipal`,`r`.`nombre` AS `region` from (`almacen` `a` join `region` `r` on(`a`.`region_fk` = `r`.`id`)) where a.activo = 1;

DROP TABLE IF EXISTS `v_camion`;
CREATE ALGORITHM=UNDEFINED DEFINER=`gabs`@`%.%.%.%` SQL SECURITY DEFINER VIEW `v_camion` AS select `c`.`id` AS `id`,`c`.`placa` AS `placa`,`c`.`kilometraje` AS `kilometraje`,`c`.`estado` AS `estado`,`tc`.`nombre` AS `tipo`,`tc`.`capacidad` AS `capacidad` from (`camion` `c` join `tipoCamion` `tc` on(`c`.`tipoCamion_fk` = `tc`.`id`)) where c.activo = 1;

DROP TABLE IF EXISTS `v_pedido`;
CREATE ALGORITHM=UNDEFINED DEFINER=`gabs`@`%.%.%.%` SQL SECURITY DEFINER VIEW `v_pedido` AS select `cli`.`nombreCompleto` AS `cliente`,`cli`.`dni` AS `RUC`,`alm`.`ubigeo` AS `ubigeo`,`alm`.`provincia` AS `provincia`,`ped`.`cantidad` AS `cantidad`,`ped`.`fechaPedido` AS `fechaPedido`,`ped`.`plazoEntrega` AS `plazoEntrega`,`ped`.`estado` AS `estado`,`ped`.`codigoPedido` AS `URL_pedido`,`usu`.`usuario` AS `registrado por` from (((`pedido` `ped` join `cliente` `cli` on(`ped`.`cliente_fk` = `cli`.`id`)) join `usuario` `usu` on(`ped`.`usuario_fk` = `usu`.`id`)) join `almacen` `alm` on(`ped`.`almacen_fk` = `alm`.`id`)) where ped.activo = 1;

DROP TABLE IF EXISTS `v_usuario`;
CREATE ALGORITHM=UNDEFINED DEFINER=`gabs`@`%.%.%.%` SQL SECURITY DEFINER VIEW `v_usuario` AS select `u`.`usuario` AS `usuario`,`u`.`nombre` AS `nombre`,`u`.`apellido` AS `apellido`,`u`.`dni` AS `dni`,`u`.`correo` AS `correo`,`u2`.`nombre` AS `registered by`,`r`.`nombre` AS `rol` from ((`usuario` `u` left join `usuario` `u2` on(`u`.`creador_fk` = `u2`.`id`)) join `rol` `r` on(`u`.`rol_fk` = `r`.`id`)) where u.activo = 1;

-- 2022-06-01 06:30:20