CREATE TABLE IF NOT EXISTS `usuarios` (
  `usuario` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `passw` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `rol` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`usuario`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;


INSERT INTO `usuarios` (`usuario`, `passw`, `rol`) VALUES
	('admin', 'admin', 'admin');


CREATE TABLE IF NOT EXISTS `pedidos` (
  `id` varchar(255) NOT NULL,
  `start_datetime` datetime NOT NULL,
  `end_datetime` datetime NOT NULL,
  `cliente_final` varchar(255) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;


CREATE TABLE IF NOT EXISTS `monta_med` (
  `id` varchar(255) NOT NULL,
  `start_datetime` datetime NOT NULL,
  `end_datetime` datetime NOT NULL,
  `montador` varchar(255) NOT NULL,
  `material` varchar(255) NOT NULL,
  `npedido` varchar(255) NOT NULL,
  `poblacion` varchar(255) NOT NULL,
  `codigo_postal` varchar(255) NOT NULL,
  `direccion` varchar(255) NOT NULL,
  `tlf` varchar(255) NOT NULL,
  `cliente_final` varchar(255) NOT NULL,
  `medidor` varchar(255) NOT NULL,
  `fecha_med` varchar(255) NOT NULL,
  `estado` varchar(255) NOT NULL,
  `entrega` varchar(255) NOT NULL,
  `empresa` varchar(255) NOT NULL,
  `color` varchar(255) NOT NULL,
  PRIMARY KEY (`id`,`color`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;



CREATE TABLE IF NOT EXISTS `montadores` (
  `nombre` varchar(255) NOT NULL,
  `color` varchar(255) NOT NULL,
  PRIMARY KEY (`nombre`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;



CREATE TABLE IF NOT EXISTS `medidores` (
  `nombre` varchar(255) NOT NULL,
  `color` varchar(255) NOT NULL,
  PRIMARY KEY (`nombre`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;



CREATE TABLE IF NOT EXISTS `montajes` (
  `id` varchar(255) NOT NULL,
  `start_datetime` datetime NOT NULL,
  `end_datetime` datetime NOT NULL,
  `montador` varchar(255) NOT NULL,
  `material` varchar(255) NOT NULL,
  `npedido` varchar(255) NOT NULL,
  `poblacion` varchar(255) NOT NULL,
  `codigo_postal` varchar(255) NOT NULL,
  `direccion` varchar(255) NOT NULL,
  `tlf` varchar(255) NOT NULL,
  `cliente_final` varchar(255) NOT NULL,
  `medidor` varchar(255) NOT NULL,
  `fecha_med` varchar(255) NOT NULL,
  `estado` varchar(255) NOT NULL,
  `entrega` varchar(255) NOT NULL,
  `empresa` varchar(255) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `montador` (`montador`) USING BTREE,
  CONSTRAINT `montajes_ibfk_1` FOREIGN KEY (`montador`) REFERENCES `montadores` (`nombre`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;


CREATE TABLE IF NOT EXISTS `mediciones` (
  `id` varchar(255) NOT NULL,
  `start_datetime` datetime NOT NULL,
  `end_datetime` datetime NOT NULL,
  `montador` varchar(255) NOT NULL,
  `material` varchar(255) NOT NULL,
  `npedido` varchar(255) NOT NULL,
  `poblacion` varchar(255) NOT NULL,
  `codigo_postal` varchar(255) NOT NULL,
  `direccion` varchar(255) NOT NULL,
  `tlf` varchar(255) NOT NULL,
  `cliente_final` varchar(255) NOT NULL,
  `medidor` varchar(255) NOT NULL,
  `fecha_med` varchar(255) NOT NULL,
  `estado` varchar(255) NOT NULL,
  `entrega` varchar(255) NOT NULL,
  `empresa` varchar(255) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `FK_mediciones_medidores` (`montador`) USING BTREE,
  CONSTRAINT `mediciones_ibfk_1` FOREIGN KEY (`montador`) REFERENCES `medidores` (`nombre`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;


CREATE TABLE IF NOT EXISTS `estados_pedidos` (
  `nombre` varchar(255) NOT NULL,
  `color` varchar(255) NOT NULL,
  PRIMARY KEY (`nombre`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;



CREATE TABLE IF NOT EXISTS `estados_plan` (
  `nombre` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_spanish_ci NOT NULL,
  `numero` int(11) NOT NULL,
  PRIMARY KEY (`numero`) USING BTREE,
  UNIQUE KEY `nombre` (`nombre`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;


INSERT INTO `estados_plan` (`nombre`, `numero`) VALUES
	('Acabados', 5),
	('Almacen', 3),
	('CNC', 4),
	('Diseno', 2),
	('Inicial', 1),
	('Montaje', 6);

CREATE TABLE IF NOT EXISTS `plan_montaje` (
  `id` varchar(255) NOT NULL,
  `fecha` varchar(255) NOT NULL,
  `cliente_final` varchar(255) NOT NULL,
  `estado` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_spanish_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `FK_plan_montaje_estados_plan` (`estado`),
  CONSTRAINT `FK_plan_montaje_estados_plan` FOREIGN KEY (`estado`) REFERENCES `estados_plan` (`nombre`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;



CREATE TABLE IF NOT EXISTS `actualiza_plan` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_pedido` varchar(255) NOT NULL,
  `fecha` varchar(255) NOT NULL,
  `estado` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_spanish_ci NOT NULL,
  `usuario` varchar(50) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `FK_actualiza_plan_plan_montaje` (`id_pedido`),
  KEY `FK_actualiza_plan_estados_plan` (`estado`),
  CONSTRAINT `FK_actualiza_plan_estados_plan` FOREIGN KEY (`estado`) REFERENCES `estados_plan` (`nombre`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_actualiza_plan_plan_montaje` FOREIGN KEY (`id_pedido`) REFERENCES `plan_montaje` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=387 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;