tablas_agencia_transportes.sql

-- Creacion de la base de datos
CREATE DATABASE agencia_tranporte;
USE agencia_tranporte;

-- Tablas agencia de transportes 

-- tabla clientes

CREATE TABLE clientes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(15) NOT NULL,
    apellido1 VARCHAR(15),
    apellido2 VARCHAR(15),
    direccion VARCHAR(20) NOT NULL,
    poblacion VARCHAR(15) NOT NULL,
    provincia VARCHAR(15) DEFAULT('granada'),
    codigo_postal VARCHAR(10),
    telefono VARCHAR(9),
    email VARCHAR(30),
    fecha_alta DATETIME DEFAULT(CURRENT_DATE)
);

-- tabla entregas

CREATE TABLE entregas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    destinatario VARCHAR(50) NOT NULL,
    direccion VARCHAR(20) NOT NULL,
    poblacion VARCHAR(15) NOT NULL,
    provincia VARCHAR(15) NOT NULL,
    codigo_postal VARCHAR(10),
    telefono VARCHAR(9),
    fecha_salida DATETIME DEFAULT(CURRENT_DATE),
    fecha_entrega DATETIME,
    id_cliente INT,
    id_estado INT,
    id_repartidor INT,
    id_vehiculo INT,
    FOREIGN KEY (id_cliente) REFERENCES clientes(id),
    FOREIGN KEY (id_estado) REFERENCES estado(id),
    FOREIGN KEY (id_repartidor) REFERENCES repartidores(id),
    FOREIGN KEY (id_vehiculo) REFERENCES vehiculos(id)
);

-- tabla salidas (igual que entregas)

CREATE TABLE salidas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    remitente VARCHAR(50) NOT NULL,
    direccion VARCHAR(20) NOT NULL,
    poblacion VARCHAR(15) NOT NULL,
    provincia VARCHAR(15) NOT NULL,
    codigo_postal VARCHAR(10),
    telefono VARCHAR(9),
    fecha_salida DATETIME DEFAULT(CURRENT_DATE),
    fecha_entrega DATETIME,
    id_cliente INT,
    id_estado INT,
    id_repartidor INT,
    id_vehiculo INT,
    FOREIGN KEY (id_cliente) REFERENCES clientes(id),
    FOREIGN KEY (id_estado) REFERENCES estado(id),
    FOREIGN KEY (id_repartidor) REFERENCES repartidores(id),
    FOREIGN KEY (id_vehiculo) REFERENCES vehiculos(id)
);

-- tabla estados

CREATE TABLE estados (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(20) NOT NULL CHECK (nombre IN ('en transito', 'entregado', 'incidencia'))
);

-- tabla incidencias (puedes asociar a entrega o salida)

CREATE TABLE incidencias (
    id INT PRIMARY KEY AUTO_INCREMENT,
    descripcion VARCHAR(100) NOT NULL,
    id_entrega INT,
    id_salida INT,
    FOREIGN KEY (id_entrega) REFERENCES entregas(id),
    FOREIGN KEY (id_salida) REFERENCES salidas(id)
);

-- tabla repartidores

CREATE TABLE repartidores(
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(30) NOT NULL,
    numero_rep INT
);

-- tabla vehiculos

CREATE TABLE vehiculos(
    id INT PRIMARY KEY AUTO_INCREMENT,
    matricula VARCHAR(8) NOT NULL,
    marca VARCHAR(15)
);

-- tabla intermedia (relacion muchos a muchos) repartidor y vehiculos

CREATE TABLE repartidor_vehiculo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_repartidor INT NOT NULL,
    id_vehiculo INT NOT NULL,
    fecha_asignacion DATETIME DEFAULT CURRENT_DATE,
    FOREIGN KEY (id_repartidor) REFERENCES repartidores(id),
    FOREIGN KEY (id_vehiculo) REFERENCES vehiculos(id)
);

-- Vistas

CREATE VIEW vista_entregas_detalladas AS
SELECT 
    e.id AS id_entrega,
    e.destinatario,
    e.direccion,
    e.poblacion,
    e.provincia,
    e.codigo_postal,
    e.telefono,
    e.fecha_salida,
    e.fecha_entrega,
    c.nombre AS nombre_cliente,
    c.apellido1,
    c.apellido2,
    est.nombre AS estado,
    r.nombre AS repartidor,
    v.matricula AS vehiculo
FROM entregas e
LEFT JOIN clientes c ON e.id_cliente = c.id
LEFT JOIN estados est ON e.id_estado = est.id
LEFT JOIN repartidores r ON e.id_repartidor = r.id
LEFT JOIN vehiculos v ON e.id_vehiculo = v.id;

CREATE VIEW vista_salidas_detalladas AS
SELECT 
    s.id AS id_salida,
    s.remitente,
    s.direccion,
    s.poblacion,
    s.provincia,
    s.codigo_postal,
    s.telefono,
    s.fecha_salida,
    s.fecha_entrega,
    c.nombre AS nombre_cliente,
    c.apellido1,
    c.apellido2,
    est.nombre AS estado,
    r.nombre AS repartidor,
    v.matricula AS vehiculo
FROM salidas s
LEFT JOIN clientes c ON s.id_cliente = c.id
LEFT JOIN estados est ON s.id_estado = est.id
LEFT JOIN repartidores r ON s.id_repartidor = r.id
LEFT JOIN vehiculos v ON s.id_vehiculo = v.id;

CREATE VIEW vista_incidencias AS
SELECT 
    i.id AS id_incidencia,
    i.descripcion,
    e.destinatario AS entrega_destinatario,
    s.remitente AS salida_remitente,
    i.id_entrega,
    i.id_salida
FROM incidencias i
LEFT JOIN entregas e ON i.id_entrega = e.id
LEFT JOIN salidas s ON i.id_salida = s.id;

CREATE VIEW vista_repartidor_vehiculo AS
SELECT 
    rv.id,
    r.nombre AS repartidor,
    v.matricula AS vehiculo,
    rv.fecha_asignacion
FROM repartidor_vehiculo rv
LEFT JOIN repartidores r ON rv.id_repartidor = r.id
LEFT JOIN vehiculos v ON rv.id_vehiculo = v.id;

CREATE VIEW vista_entregas_incidencia AS
SELECT 
    e.*,
    c.nombre AS nombre_cliente,
    c.apellido1,
    c.apellido2
FROM entregas e
LEFT JOIN clientes c ON e.id_cliente = c.id
LEFT JOIN estados est ON e.id_estado = est.id
WHERE est.nombre = 'incidencia';

-- Procedimientos

DELIMITER //
CREATE PROCEDURE nueva_entrega(
    IN destinatario VARCHAR(50),
    IN direccion VARCHAR(20),
    IN poblacion VARCHAR(15),
    IN provincia VARCHAR(15),
    IN codigo_postal VARCHAR(10),
    IN telefono VARCHAR(9),
    IN id_cliente INT,
    IN id_estado INT,
    IN id_repartidor INT,
    IN id_vehiculo INT
)
BEGIN
    INSERT INTO entregas (destinatario, direccion, poblacion, provincia, codigo_postal, telefono, id_cliente, id_estado, id_repartidor, id_vehiculo)
    VALUES (destinatario, direccion, poblacion, provincia, codigo_postal, telefono, id_cliente, id_estado, id_repartidor, id_vehiculo);
    SELECT LAST_INSERT_ID() AS nueva_entrega_id;
END;
//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE cambiar_estado_entrega(
    IN entrega_id INT,
    IN nuevo_estado_id INT
)
BEGIN
    UPDATE entregas SET id_estado = nuevo_estado_id WHERE id = entrega_id;
END;
//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE registrar_incidencia(
    IN descripcion VARCHAR(100),
    IN id_entrega INT,
    IN id_salida INT
)
BEGIN
    INSERT INTO incidencias (descripcion, id_entrega, id_salida)
    VALUES (descripcion, id_entrega, id_salida);
END;
//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE entregas_por_repartidor(
    IN repartidor_id INT,
    IN fecha_inicio DATE,
    IN fecha_fin DATE
)
BEGIN
    SELECT * FROM entregas
    WHERE id_repartidor = repartidor_id
      AND fecha_salida BETWEEN fecha_inicio AND fecha_fin;
END;
//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE asignar_vehiculo(
    IN repartidor_id INT,
    IN vehiculo_id INT,
    IN fecha DATETIME
)
BEGIN
    INSERT INTO repartidor_vehiculo (id_repartidor, id_vehiculo, fecha_asignacion)
    VALUES (repartidor_id, vehiculo_id, fecha);
END;
//
DELIMITER ;

-- Disparadores

DELIMITER //
CREATE TRIGGER set_estado_incidencia_entrega
AFTER INSERT ON incidencias
FOR EACH ROW
BEGIN
    IF NEW.id_entrega IS NOT NULL THEN
        UPDATE entregas
        SET id_estado = (SELECT id FROM estados WHERE nombre = 'incidencia' LIMIT 1)
        WHERE id = NEW.id_entrega;
    END IF;
END;
//
DELIMITER ;

DELIMITER //
CREATE TRIGGER set_estado_incidencia_salida
AFTER INSERT ON incidencias
FOR EACH ROW
BEGIN
    IF NEW.id_salida IS NOT NULL THEN
        UPDATE salidas
        SET id_estado = (SELECT id FROM estados WHERE nombre = 'incidencia' LIMIT 1)
        WHERE id = NEW.id_salida;
    END IF;
END;
//
DELIMITER ;

DELIMITER //
CREATE TRIGGER set_fecha_entrega
AFTER UPDATE ON entregas
FOR EACH ROW
BEGIN
    IF NEW.id_estado = (SELECT id FROM estados WHERE nombre = 'entregado' LIMIT 1)
       AND (OLD.id_estado <> NEW.id_estado OR OLD.fecha_entrega IS NULL) THEN
        UPDATE entregas
        SET fecha_entrega = CURRENT_TIMESTAMP
        WHERE id = NEW.id;
    END IF;
END;
//
DELIMITER ;