-- ============================================================ -- HOJA DE RUTA - Esquema MariaDB/MySQL -- Versión 1.0 -- ============================================================ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ------------------------------------------------------------ -- Empresas / clientes -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS empresas ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(150) NOT NULL, cif VARCHAR(20) DEFAULT NULL, direccion VARCHAR(255) DEFAULT NULL, ciudad VARCHAR(100) DEFAULT NULL, cp VARCHAR(10) DEFAULT NULL, pais VARCHAR(80) DEFAULT 'España', telefono VARCHAR(30) DEFAULT NULL, email VARCHAR(150) DEFAULT NULL, notas TEXT DEFAULT NULL, activa TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_empresas_cif (cif) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- Conductores -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS conductores ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(100) NOT NULL, apellidos VARCHAR(150) NOT NULL, dni VARCHAR(20) DEFAULT NULL, carnet VARCHAR(20) DEFAULT NULL, -- nº carnet conducir categoria_carnet VARCHAR(20) DEFAULT NULL, -- CE, C, B, etc. caducidad_carnet DATE DEFAULT NULL, telefono VARCHAR(30) DEFAULT NULL, email VARCHAR(150) DEFAULT NULL, empresa_id INT UNSIGNED DEFAULT NULL, activo TINYINT(1) NOT NULL DEFAULT 1, notas TEXT DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_conductores_dni (dni), CONSTRAINT fk_conductores_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- Vehículos -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS vehiculos ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, matricula VARCHAR(20) NOT NULL, tipo ENUM('camion','furgon','trailer','cisterna','frigorifico','otro') NOT NULL DEFAULT 'camion', marca VARCHAR(80) DEFAULT NULL, modelo VARCHAR(80) DEFAULT NULL, anio SMALLINT DEFAULT NULL, tara_kg INT DEFAULT NULL, mma_kg INT DEFAULT NULL, -- masa máxima autorizada altura_m DECIMAL(4,2) DEFAULT NULL, largo_m DECIMAL(5,2) DEFAULT NULL, empresa_id INT UNSIGNED DEFAULT NULL, activo TINYINT(1) NOT NULL DEFAULT 1, notas TEXT DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_vehiculos_matricula (matricula), CONSTRAINT fk_vehiculos_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- POI / Paradas (catálogo reutilizable) -- Deduplicación por lat+lng redondeados a 5 decimales -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS poi ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(200) NOT NULL, tipo ENUM('origen','destino','cliente','parking_hgv','gasolinera','almacen','frontera','otro') NOT NULL DEFAULT 'otro', direccion VARCHAR(255) DEFAULT NULL, ciudad VARCHAR(100) DEFAULT NULL, cp VARCHAR(10) DEFAULT NULL, pais VARCHAR(80) DEFAULT 'España', lat DECIMAL(9,6) DEFAULT NULL, lng DECIMAL(9,6) DEFAULT NULL, lat5 DECIMAL(7,5) GENERATED ALWAYS AS (ROUND(lat, 5)) STORED, lng5 DECIMAL(7,5) GENERATED ALWAYS AS (ROUND(lng, 5)) STORED, osm_id BIGINT DEFAULT NULL, -- referencia OSM si viene de Overpass empresa_id INT UNSIGNED DEFAULT NULL, -- cliente asociado al POI si aplica notas TEXT DEFAULT NULL, activo TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_poi_latlng (lat5, lng5), CONSTRAINT fk_poi_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- Hojas de ruta (cabecera) -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS rutas ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, referencia VARCHAR(50) NOT NULL, -- ej: RUT-2026-0001 fecha_salida DATE NOT NULL, hora_salida TIME NOT NULL DEFAULT '08:00:00', conductor_id INT UNSIGNED NOT NULL, vehiculo_id INT UNSIGNED NOT NULL, empresa_id INT UNSIGNED DEFAULT NULL, -- empresa que contrata estado ENUM('borrador','confirmada','en_curso','completada','cancelada') NOT NULL DEFAULT 'borrador', km_inicio INT DEFAULT NULL, km_fin INT DEFAULT NULL, distancia_total_km DECIMAL(8,2) DEFAULT NULL, -- calculada por ORS duracion_total_min INT DEFAULT NULL, -- calculada por ORS notas_generales TEXT DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_rutas_referencia (referencia), CONSTRAINT fk_rutas_conductor FOREIGN KEY (conductor_id) REFERENCES conductores(id), CONSTRAINT fk_rutas_vehiculo FOREIGN KEY (vehiculo_id) REFERENCES vehiculos(id), CONSTRAINT fk_rutas_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- Tramos de cada ruta (paradas ordenadas) -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS ruta_paradas ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ruta_id INT UNSIGNED NOT NULL, orden TINYINT UNSIGNED NOT NULL, -- 0 = origen, 1,2,3... = paradas, N = destino final poi_id INT UNSIGNED NOT NULL, hora_llegada_plan TIME DEFAULT NULL, -- hora prevista llegada hora_salida_plan TIME DEFAULT NULL, -- hora prevista salida hora_llegada_real TIME DEFAULT NULL, hora_salida_real TIME DEFAULT NULL, duracion_parada_min INT DEFAULT NULL, -- tiempo de parada en minutos distancia_tramo_km DECIMAL(8,2) DEFAULT NULL, -- km desde parada anterior (ORS, editable) duracion_tramo_min INT DEFAULT NULL, -- tiempo conducción desde anterior (ORS, editable) distancia_edit TINYINT(1) NOT NULL DEFAULT 0, -- 1 si fue editado manualmente duracion_edit TINYINT(1) NOT NULL DEFAULT 0, parking_sugerido_id INT UNSIGNED DEFAULT NULL, -- POI de tipo parking_hgv sugerido notas TEXT DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_ruta_parada_orden (ruta_id, orden), CONSTRAINT fk_rp_ruta FOREIGN KEY (ruta_id) REFERENCES rutas(id) ON DELETE CASCADE, CONSTRAINT fk_rp_poi FOREIGN KEY (poi_id) REFERENCES poi(id), CONSTRAINT fk_rp_parking FOREIGN KEY (parking_sugerido_id) REFERENCES poi(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- Pausas reglamentarias por ruta (Regl. CE 561/2006) -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS ruta_pausas ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ruta_id INT UNSIGNED NOT NULL, orden TINYINT UNSIGNED NOT NULL, tipo ENUM('pausa_45','pausa_split_15','pausa_split_30','descanso','otra') NOT NULL DEFAULT 'pausa_45', hora_inicio TIME DEFAULT NULL, duracion_min INT NOT NULL DEFAULT 45, poi_id INT UNSIGNED DEFAULT NULL, -- parking donde se hace la pausa notas TEXT DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_pausas_ruta FOREIGN KEY (ruta_id) REFERENCES rutas(id) ON DELETE CASCADE, CONSTRAINT fk_pausas_poi FOREIGN KEY (poi_id) REFERENCES poi(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- Secuencia para referencias automáticas -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS secuencias ( nombre VARCHAR(50) PRIMARY KEY, valor INT UNSIGNED NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT IGNORE INTO secuencias (nombre, valor) VALUES ('rutas', 0); SET FOREIGN_KEY_CHECKS = 1;