-- 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,
  `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;

TRUNCATE `almacen`;
INSERT INTO `almacen` (`id`, `ubigeo`, `departamento`, `provincia`, `latitud`, `longitud`, `esPrincipal`, `region_fk`) VALUES
(1,	'10201',	'Amazonas',	'Bagua',	-5.63906152,	-78.53166353,	0,	3),
(2,	'40101',	'Arequipa',	'Arequipa',	-16.39881421,	-71.537019649,	1,	1),
(3,	'50901',	'Ayacucho',	'Sucre',	-14.01125749,	-73.83884087,	0,	2),
(4,	'130101',	'La Libertad',	'Trujillo',	-8.11176389,	-79.02868652,	1,	1),
(5,	'150101',	'Lima',	'Lima',	-12.04591952,	-77.03049615,	1,	1),
(6,	'230101',	'Tacna',	'Tacna',	-18.0137008,	-70.2507964,	0,	1);

DROP TABLE IF EXISTS `averia`;
CREATE TABLE `averia` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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;

TRUNCATE `averia`;

DROP TABLE IF EXISTS `camion`;
CREATE TABLE `camion` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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;

TRUNCATE `camion`;
INSERT INTO `camion` (`id`, `placa`, `kilometraje`, `estado`, `tipoCamion_fk`) VALUES
(1,	'ETALLIC',	69420,	1,	1);

DROP TABLE IF EXISTS `cliente`;
CREATE TABLE `cliente` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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;

TRUNCATE `cliente`;
INSERT INTO `cliente` (`id`, `nombreCompleto`, `dni`, `correo`, `telefono`) VALUES
(1,	'EmpresaAAA',	'1234567890',	'contacto@empresaAAA.com',	'123456789'),
(2,	'EmpresaBBB',	'1234567890',	'contacto@empresaBBB.com',	'123456789');

DROP TABLE IF EXISTS `pedido`;
CREATE TABLE `pedido` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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;

TRUNCATE `pedido`;
INSERT INTO `pedido` (`id`, `fechaPedido`, `cantidad`, `plazoEntrega`, `estado`, `codigoPedido`, `almacen_fk`, `cliente_fk`, `usuario_fk`, `PTG_fk`) VALUES
(2,	'2022-01-01 00:00:00',	10,	1,	0,	'20220101_000001',	3,	1,	4,	NULL);

DROP TABLE IF EXISTS `PTG`;
CREATE TABLE `PTG` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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;

TRUNCATE `PTG`;
INSERT INTO `PTG` (`id`, `fechaGenerado`, `esSimulacion`, `usuario_fk`) VALUES
(1,	'2022-06-01 05:10:47',	1,	1);

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

TRUNCATE `region`;
INSERT INTO `region` (`id`, `nombre`) VALUES
(1,	'costa'),
(2,	'sierra'),
(3,	'selva');

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

TRUNCATE `rol`;
INSERT INTO `rol` (`id`, `nombre`) VALUES
(1,	'superuser');

DROP TABLE IF EXISTS `ruta`;
CREATE TABLE `ruta` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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;

TRUNCATE `ruta`;
INSERT INTO `ruta` (`id`, `fechaInicio`, `fechaFin`, `PTG_fk`, `camion_fk`) VALUES
(1,	'2022-01-01 00:00:00',	'2022-01-02 00:00:00',	1,	1);

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

TRUNCATE `tipoAveria`;

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

TRUNCATE `tipoCamion`;
INSERT INTO `tipoCamion` (`id`, `nombre`, `capacidad`) VALUES
(1,	'tipoA',	90),
(2,	'tipoB',	45),
(3,	'tipoC',	30);

DROP TABLE IF EXISTS `tramo`;
CREATE TABLE `tramo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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;

TRUNCATE `tramo`;
INSERT INTO `tramo` (`id`, `distancia`, `estaBloqueado`, `almacen1_fk`, `almacen2_fk`) VALUES
(1,	10,	0,	2,	5),
(2,	10,	0,	3,	5),
(3,	10,	0,	4,	5),
(4,	10,	0,	6,	5),
(5,	20,	0,	1,	4),
(6,	20,	0,	4,	1),
(7,	10,	0,	5,	2),
(8,	10,	0,	5,	3),
(9,	10,	0,	5,	4),
(10,	10,	0,	5,	6);

DROP TABLE IF EXISTS `tramo_ruta`;
CREATE TABLE `tramo_ruta` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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;

TRUNCATE `tramo_ruta`;
INSERT INTO `tramo_ruta` (`id`, `orden`, `horaSalidaEst`, `horaLlegadaEst`, `horaSalidaReal`, `horaLlegadaReal`, `paquetesEntregar`, `ruta_fk`, `tramo_fk`) VALUES
(1,	1,	'2022-01-01 10:00:00',	'2022-01-01 15:00:00',	NULL,	NULL,	0,	1,	9),
(2,	2,	'2022-01-01 16:00:00',	'2022-01-01 21:00:00',	NULL,	NULL,	10,	1,	6);

DROP TABLE IF EXISTS `usuario`;
CREATE TABLE `usuario` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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;

TRUNCATE `usuario`;
INSERT INTO `usuario` (`id`, `usuario`, `clave`, `nombre`, `apellido`, `dni`, `correo`, `creador_fk`, `rol_fk`) VALUES
(1,	'gabs',	'*6AB7931FB2D104A478932D23AF4C7700D1A0C61A',	'Gabriela',	'Hernandez',	'12345678',	'gabs@pucp.edu.pe',	NULL,	1),
(4,	'mitsuo',	'*D279131EA232308E083B3EA192B74A0C15EA8701',	'Mitsuo',	'Tokumori',	'12345678',	'mitsuo@pucp.edu.pe',	1,	1);

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`));

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`));

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`));

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`));

-- 2022-06-01 05:35:28