create database parcialBDD2Abr25;
use parcialBDD2Abr25;


-- Tabla de Clientes
CREATE TABLE Clientes (
    cliente_id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    telefono VARCHAR(15),
    fecha_registro DATE NOT NULL,
    tipo_cliente ENUM('Individual', 'Corporativo') NOT NULL
);

-- Tabla de Destinos
CREATE TABLE Destinos (
    destino_id INT AUTO_INCREMENT PRIMARY KEY,
    nombre_destino VARCHAR(100) NOT NULL,
    pais VARCHAR(100) NOT NULL,
    tipo ENUM('Playa', 'Montaña', 'Ciudad', 'Aventura', 'Cultural') NOT NULL,
    temporada_recomendada ENUM('Verano', 'Invierno', 'Todo el año'),
    precio_base DECIMAL(10,2) NOT NULL
);

-- Tabla de Paquetes
CREATE TABLE Paquetes (
    paquete_id INT AUTO_INCREMENT PRIMARY KEY,
    nombre_paquete VARCHAR(100) NOT NULL,
    destino_id INT NOT NULL,
    duracion_dias INT NOT NULL,
    precio_total DECIMAL(10,2) NOT NULL,
    detalles TEXT,
    FOREIGN KEY (destino_id) REFERENCES Destinos(destino_id)
);

-- Tabla de Reservas
CREATE TABLE Reservas (
    reserva_id INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT NOT NULL,
    paquete_id INT NOT NULL,
    fecha_reserva DATE NOT NULL,
    fecha_viaje DATE NOT NULL,
    estado ENUM('Confirmada', 'Pendiente', 'Cancelada') NOT NULL DEFAULT 'Pendiente',
    promocion_id INT, -- Nueva relación con Promociones
    FOREIGN KEY (cliente_id) REFERENCES Clientes(cliente_id),
    FOREIGN KEY (paquete_id) REFERENCES Paquetes(paquete_id),
    FOREIGN KEY (promocion_id) REFERENCES Promociones(promocion_id) ON DELETE SET NULL ON UPDATE CASCADE
);

-- Tabla de Promociones
CREATE TABLE Promociones (
    promocion_id INT AUTO_INCREMENT PRIMARY KEY,
    nombre_promocion VARCHAR(100) NOT NULL,
    descripcion TEXT,
    descuento DECIMAL(5,2) NOT NULL,
    fecha_inicio DATE NOT NULL,
    fecha_fin DATE NOT NULL,
    condiciones TEXT
);

-- Nueva Tabla de LogErrores
CREATE TABLE LogErrores (
    error_id INT AUTO_INCREMENT PRIMARY KEY,
    fecha_error DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    descripcion_error TEXT NOT NULL,
    cliente_id INT, -- Nueva relación con Clientes para identificar quién causó el error
    FOREIGN KEY (cliente_id) REFERENCES Clientes(cliente_id)
);

INSERT INTO Clientes (nombre, email, telefono, fecha_registro, tipo_cliente)
VALUES 
('Juan Pérez', 'juan.perez@example.com', '123456789', '2025-01-15', 'Individual'),
('Ana Gómez', 'ana.gomez@example.com', '987654321', '2025-01-20', 'Corporativo'),
('Carlos Ruiz', 'carlos.ruiz@example.com', '456789123', '2025-02-01', 'Individual');

INSERT INTO Destinos (nombre_destino, pais, tipo, temporada_recomendada, precio_base)
VALUES 
('Playa del Carmen', 'México', 'Playa', 'Verano', 1500.00),
('Bariloche', 'Argentina', 'Montaña', 'Invierno', 2000.00),
('París', 'Francia', 'Ciudad', 'Todo el año', 3000.00),
('Machu Picchu', 'Perú', 'Cultural', 'Todo el año', 2500.00);

INSERT INTO Paquetes (nombre_paquete, destino_id, duracion_dias, precio_total, detalles)
VALUES 
('Escapada a la playa', 1, 7, 1700.00, 'Incluye hotel y vuelos. Actividades acuáticas.'),
('Aventura en la montaña', 2, 5, 2200.00, 'Incluye excursiones y hospedaje en cabaña.'),
('Romántico en París', 3, 3, 3200.00, 'Incluye paseo por el Sena y cena gourmet.'),
('Descubriendo Machu Picchu', 4, 4, 2600.00, 'Incluye guías turísticos y transporte.');

INSERT INTO Promociones (nombre_promocion, descripcion, descuento, fecha_inicio, fecha_fin, condiciones)
VALUES 
('Verano Playero', 'Promoción especial para destinos de playa.', 10.00, '2025-06-01', '2025-08-31', 'Válido solo para paquetes de más de 5 días.'),
('Invierno Aventurero', 'Promoción para destinos de montaña.', 15.00, '2025-06-01', '2025-09-30', 'Aplicable solo a reservas confirmadas antes del 1 de julio.'),
('Romance en París', 'Promoción para paquetes románticos.', 20.00, '2025-02-01', '2025-03-31', 'Solo válido para parejas.');

INSERT INTO Reservas (cliente_id, paquete_id, fecha_reserva, fecha_viaje, estado, promocion_id)
VALUES 
(1, 1, '2025-04-01', '2025-04-08', 'Confirmada', 1),
(2, 2, '2025-04-05', '2025-04-10', 'Pendiente', 2),
(3, 3, '2025-04-10', '2025-04-13', 'Confirmada', NULL);
-- Nuevas reservas para Destinos
INSERT INTO Reservas (cliente_id, paquete_id, fecha_reserva, fecha_viaje, estado, promocion_id)
VALUES 
(1, 1, '2025-04-02', '2025-04-09', 'Confirmada', 1), -- Playa del Carmen
(2, 1, '2025-04-03', '2025-04-10', 'Confirmada', 1), -- Playa del Carmen
(3, 1, '2025-04-04', '2025-04-11', 'Confirmada', 1), -- Playa del Carmen
(1, 2, '2025-04-06', '2025-04-12', 'Pendiente', 2),  -- Bariloche
(2, 2, '2025-04-07', '2025-04-13', 'Pendiente', 2),  -- Bariloche
(3, 2, '2025-04-08', '2025-04-14', 'Confirmada', NULL), -- Bariloche
(1, 3, '2025-04-15', '2025-04-18', 'Confirmada', 3), -- París
(2, 3, '2025-04-16', '2025-04-19', 'Confirmada', 3), -- París
(3, 3, '2025-04-17', '2025-04-20', 'Confirmada', 3); -- París





-- ejer 1
DELIMITER //
CREATE FUNCTION calcular_descuento(precio_original DECIMAL(10, 2), porcentaje_descuento DECIMAL(5, 2))
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
    -- Validar si el porcentaje de descuento está fuera del rango permitido
    IF porcentaje_descuento < 0 OR porcentaje_descuento > 100 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'El porcentaje de descuento debe estar entre 0 y 100.';
    END IF;
    
    -- Calcular el precio final después de aplicar el descuento
    RETURN precio_original - (precio_original * (porcentaje_descuento / 100));
END;//
DELIMITER ;

select version();
select calcular_descuento(100.00,20.00);


-- ejercicio 2: vista que utiliza esta funcion (no es lo pedido)
DELIMITER $$
CREATE FUNCTION total_reservas_por_cliente(fecha_inicio DATE, fecha_fin DATE)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE total INT;
    IF fecha_inicio > fecha_fin THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Error: La fecha de inicio no puede ser mayor que la fecha de fin.';
    END IF;
    -- calcular el total de reservas en el rango de fechas
    SELECT COUNT(*) INTO total
    FROM Reservas
    WHERE fecha_reserva BETWEEN fecha_inicio AND fecha_fin;
    RETURN total;
END$$
DELIMITER ;

-- vista que utiliza la función total_reservas_por_cliente
CREATE VIEW ReservasPorCliente AS
SELECT 
    cliente_id,
    nombre AS nombre_cliente,
    email AS email_cliente,
    tipo_cliente,
    total_reservas_por_cliente(CURRENT_DATE - INTERVAL 30 DAY, CURRENT_DATE) AS total_reservas_ultimos_30_dias
FROM 
    Clientes;

select * from ReservasPorCliente;



-- opcion con subquerys
CREATE or replace VIEW ReservasPorCliente AS
SELECT 
    c.cliente_id,
    c.nombre AS nombre_cliente,
    COUNT(r.reserva_id) AS cantidadReservas,
    (SELECT fecha_reserva -- levantamos la ultima fecha de reserva, con subq y order by
     FROM Reservas 
     WHERE cliente_id = c.cliente_id 
     ORDER BY fecha_reserva DESC 
     LIMIT 1) AS fechaUltimaReserva
FROM 
    Clientes c
INNER JOIN Reservas r ON c.cliente_id = r.cliente_id
GROUP BY c.cliente_id, c.nombre;

select * from ReservasPorCliente;


-- version con MAX (la opcion mas sencilla)
CREATE VIEW ReservasPorCliente AS
SELECT 
    c.cliente_id,
    c.nombre AS nombre_cliente,
    COUNT(r.reserva_id) AS cantidadReservas,
    MAX(r.fecha_reserva) AS fechaUltimaReserva
FROM 
    Clientes c INNER JOIN Reservas r ON c.cliente_id = r.cliente_id
GROUP BY 
	c.cliente_id, c.nombre;

select * from ReservasPorCliente;




-- ejercicio 3: sp que registra reserva con manejo de exception y transaccion
DELIMITER $$
CREATE PROCEDURE registrar_reserva(
    IN p_cliente_id INT,
    IN p_paquete_id INT,
    IN p_fecha_viaje DATE
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        INSERT INTO LogErrores (descripcion_error)
        VALUES ('Ocurrio un error al intentar registrar una nueva reserva. Verifique los datos.');
    END;
    -- Validación previa antes de iniciar la transacción
    IF NOT EXISTS (SELECT 1 FROM Paquetes WHERE paquete_id = p_paquete_id) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Error: El paquete especificado no existe.'; -- no verifico el id de cliente
    END IF;    
    
    
    
    START TRANSACTION;
    -- insertar la reserva solo si la validacion esta ok
    INSERT INTO Reservas (cliente_id, paquete_id, fecha_reserva, fecha_viaje, estado)
    VALUES (p_cliente_id, p_paquete_id, CURRENT_DATE, p_fecha_viaje, 'Pendiente');
    COMMIT;
END$$
DELIMITER ;


-- ejercicio 4: trigger
DELIMITER $$
CREATE TRIGGER validar_fecha_reserva
BEFORE INSERT ON Reservas
FOR EACH ROW
BEGIN
    IF NEW.fecha_viaje < NEW.fecha_reserva THEN
        -- registrar el error
        INSERT INTO LogErrores (descripcion_error)
        VALUES (CONCAT('Error en reserva: La fecha del viaje (', NEW.fecha_viaje, ') es anterior a la fecha de reserva (', NEW.fecha_reserva, ').'));
        -- lanzar un error 
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Error: Fecha de viaje no puede ser anterior a la fecha de reserva.';
    END IF;
END$$
DELIMITER ;


-- ejercicio 5: cte
WITH DestinosPopulares AS (
    SELECT 
        d.destino_id,
        d.nombre_destino,
        COUNT(r.reserva_id) AS total_reservas
    FROM Destinos d 
    JOIN paquetes p ON d.destino_id = p.destino_id
    join reservas r on p.paquete_id = r.paquete_id
    GROUP BY 
        d.destino_id, d.nombre_destino
    HAVING total_reservas >=4
)
SELECT 
    dp.destino_id,
    dp.nombre_destino,
    dp.total_reservas
FROM 
    DestinosPopulares dp;
    
/* ------------------------------------------------------------------------------*/