# EJERCICIOS evaluación contínua #2
# La entrega debe ser en este formato: renting_cars2_tu_nombre_tu_apellido.sql
# Por ejemplo: renting_cars2_Robin_Hood.sql
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.25 puntos
# Correctamente resuelto: 0.50 puntos
USE renting_cars2;

# 01. Reforma la vista facturación para incluir :
-- el nombre del país, 
-- la marca, 
-- el modelo y 
-- los concesionarios de cada vehículo
DROP VIEW IF EXISTS facturacion;

CREATE VIEW facturacion as
SELECT c.nombre_cliente, c.apellido_cliente, p.nombre_pais, m.marca, m.nombre_modelo, co.nombre_concesionario,
fecha_recogida "recogida", ifnull(fecha_devolucion, "pendiente") "devolucion",
datediff(ifnull(fecha_devolucion, curdate()) + 1, fecha_recogida) "dias",
v.precio_dia,
(datediff(ifnull(fecha_devolucion, curdate()) + 1, fecha_recogida) * precio_dia) "importe"
FROM clientes c
JOIN alquileres a
ON c.id_cliente = a.id_cliente
JOIN vehiculos v
ON v.id_vehiculo = a.id_vehiculo
JOIN modelos m
ON v.id_modelo = m.id_modelo
JOIN paises p
ON p.id_pais = c.id_pais
JOIN concesionarios co
ON co.id_concesionario = v.id_concesionario
Order by recogida;

SELECT * FROM facturacion;
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.25 puntos
# Correctamente resuelto: 0.50 puntos


# PROCEDIMIENTOS ALMACENADOS

# 02. Crea un SP (llamado cars_no_rent) para mostrar los modelos que no se han alquilado nunca
DROP PROCEDURE IF EXISTS cars_no_rent;

DELIMITER //
	CREATE PROCEDURE cars_no_rent()
		COMMENT "Mostrar los modelos no alquilados"
 BEGIN
    SELECT m.marca 
	FROM modelos m
	LEFT JOIN vehiculos v
	ON m.id_modelo=v.id_modelo
	LEFT JOIN alquileres a
	ON a.id_vehiculo=v.id_vehiculo
	WHERE a.id_vehiculo IS NULL;
 END//
DELIMITER ;
CALL cars_no_rent();

# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.25 puntos
# Correctamente resuelto: 0.50 puntos


# 03. Crea un SP (llamado list_fact_by_year) que devuelva por cada año la facturación anual
-- Habrá por tanto dos columnas en la salida: "año" y "facturación anual" 
DROP PROCEDURE IF EXISTS list_fact_by_year;
DELIMITER //
CREATE PROCEDURE list_fact_by_year()
BEGIN
   SELECT 
   YEAR(a.fecha_recogida) AS año,
   SUM(v.precio_dia * DATEDIFF(IFNULL(a.fecha_devolucion, CURDATE()), a.fecha_recogida)) AS 'facturación anual'
   FROM alquileres a
   JOIN vehiculos v ON a.id_vehiculo = v.id_vehiculo
   GROUP BY año
   ORDER BY año;
END //
DELIMITER ;

CALL list_fact_by_year();
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.25 puntos
# Correctamente resuelto: 0.50 puntos


# 04. Crea un SP (llamado fact_by_year) que devuelva la facturación anual según el año que se indique
-- Habrá por tanto una única respuesta: "facturación anual" 

DROP PROCEDURE IF EXISTS fact_by_year;
DELIMITER //
CREATE PROCEDURE fact_by_year(in billing_year int)
COMMENT "Devuelve la facturación anual según el año que se indique"
	BEGIN
	  SELECT 
	  SUM(DATEDIFF(IFNULL(fecha_devolucion,curdate())+1, fecha_recogida)*v.precio_dia) "facturación anual"
	  FROM alquileres a
	  JOIN vehiculos v
	  ON a.id_vehiculo =v.id_vehiculo
	  WHERE YEAR(a.fecha_recogida)=billing_year
        ;
	END//
DELIMITER ;
CALL fact_by_year(2024);

# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.25 puntos
# Correctamente resuelto: 0.50 puntos


# 05. Crea un SP (llamado facturacion_pais) para saber la facturación total de un pais en concreto
-- Recibirá como parametro de entrada el nombre del pais y devolverá la cantidad de dinero facturada
DROP PROCEDURE IF EXISTS facturacion_pais
 
DELIMITER //
CREATE PROCEDURE facturacion_pais(in country varchar(30))
COMMENT "saber la facturación total de un pais en concreto"
BEGIN
	SELECT 
	SUM(DATEDIFF(IFNULL(fecha_devolucion, curdate())+1, fecha_recogida)* v.precio_dia)  "cantidad facturada"
	FROM  paises p
	JOIN clientes c
	ON p.id_pais= c.id_pais
	JOIN alquileres a
	ON c.id_cliente=a.id_cliente
	JOIN vehiculos v
	ON a.id_vehiculo=v.id_vehiculo
	WHERE p.nombre_pais= country
        ;
END//

DELIMITER ;
CALL facturacion_pais("italia");

# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.50 puntos
# Correctamente resuelto: 1.00 puntos 


# 06. Crea un SP (llamado update_units_cars) para añadir vehículos a la flota, con estos parámetros:
-- marca y nombre del modelo,
-- unidades a añadir
-- Ejecútalo así:
-- CALL insert_cars("Fiat", "Panda", 3); # Y como había 2, ahora deben ser 5
DROP PROCEDURE IF EXISTS update_units_cars;
DELIMITER //
CREATE PROCEDURE update_units_cars(
    IN input_marca VARCHAR(20),
    IN input_modelo VARCHAR(20),
    IN unidades_a_agregar INT
)
BEGIN
    -- Declaracion de variables
    DECLARE modelo_id INT;
    DECLARE vehiculo_existe INT;
    
    -- Verificar si el modelo existe en la tabla modelos
    SELECT id_modelo INTO modelo_id
    FROM modelos
    WHERE marca = input_marca
	AND nombre_modelo = input_modelo
    LIMIT 1;

    -- Si el modelo no existe, mostramos un mensaje de error
  IF modelo_id IS NULL THEN
	  SIGNAL SQLSTATE '45000' 
	  SET MESSAGE_TEXT = 'El modelo no existe en la tabla modelos.';
    ELSE
        -- Verificar si el modelo tiene vehículos registrados en la tabla vehiculos
	SELECT COUNT(*) INTO vehiculo_existe
	FROM vehiculos
	WHERE id_modelo = modelo_id;
	 -- Si no existe en vehiculos, mostramos un mensaje de error
	IF vehiculo_existe = 0 THEN
		SIGNAL SQLSTATE '45000' 
		SET MESSAGE_TEXT = 'No hay vehículos registrados para este modelo en la tabla vehiculos.';
	ELSE
    -- Si existe, actualizar las unidades en vehiculos
		UPDATE vehiculos 
		SET unidades = unidades + unidades_a_agregar
		WHERE id_modelo = modelo_id;

	-- Mensaje de éxito
		SELECT CONCAT('Se han agregado ', unidades_a_agregar, ' unidades al modelo ', input_marca, ' ', input_modelo) AS mensaje;
	END IF;
  END IF;
END //
DELIMITER ;
CALL update_units_cars("Fiat", "Panda", 3);

# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.50 puntos
# Correctamente resuelto: 1.00 puntos 


# 07. Crea un SP (llamado car_rent) para alquilar un vehículo, con estos parámetros:
-- nombre y apellido del cliente
-- marca y nombre del modelo
-- fecha de inicio y fin de alquiler
-- Si no hay una unidad disponible debe aparecer el mensaje:

DROP PROCEDURE IF EXISTS car_rent;
DELIMITER //

CREATE PROCEDURE car_rent(
    IN nombre_in VARCHAR(30),
    IN apellido_in VARCHAR(30),
	IN marca_in VARCHAR(30),
	IN modelo_in VARCHAR(30),
    IN fecha_incio_in date,
    IN fecha_fin_in date
)
COMMENT 'alquilar un vehículo'
BEGIN    
  DECLARE var_id_cliente INT;
  DECLARE var_id_modelo INT;
  DECLARE var_id_vehiculo INT;
  DECLARE var_unidades_disp INT;
    
-- Encontrar ID del cliente en el nombre y apellido
    SELECT id_cliente INTO var_id_cliente
    FROM clientes
    WHERE nombre_cliente = nombre_in AND apellido_cliente = apellido_in
    LIMIT 1;
-- Encontrar el ID modelo en la marca y el nombre del modelo
    SELECT id_modelo INTO var_id_modelo
    FROM modelos
    WHERE marca = marca_in AND nombre_modelo = modelo_in
    LIMIT 1;
-- Encontrar un vehículo disponible en el ID del modelo
    SELECT id_vehiculo, unidades INTO var_id_vehiculo, var_unidades_disp
    FROM vehiculos
    WHERE id_modelo = var_id_modelo AND unidades > 0
    LIMIT 1;
-- Comprobar si hay una unidad disponible
    IF var_unidades_disp > 0 THEN
-- Insertar el registro de alquiler en la tabla `alquileres`
	   INSERT INTO alquileres (id_cliente, id_vehiculo, fecha_recogida, fecha_devolucion)
	   VALUES (var_id_cliente, var_id_vehiculo, fecha_inicio_in, fecha_fin_in);
-- Disminuir las unidades del vehículo
	   UPDATE vehiculos
	   SET unidades = unidades - 1
	   WHERE id_vehiculo = var_id_vehiculo;

     ELSE
-- Generar un error si no hay unidades disponibles 
	   SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No hay unidades disponibles para el vehículo solicitado';
    END IF;
  
END//

DELIMITER ;
-- Llamar al procedimiento almacenado
CALL car_rent('Carlos', 'Almendarez','Nissan', 'Juke','2024-10-05','2024-10-10');
-- alquiler

# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.50 puntos
# Correctamente resuelto: 1.00 puntos 


# FUNCIONES

# 08. Crea una función (llamada country) que, dado el nombre de un país
-- devuelva su id de la tabla paises
use renting_cars2;
DROP FUNCTION IF exists country;
DELIMITER //
CREATE FUNCTION country(input_nombre_pais VARCHAR(50)) RETURNS INT DETERMINISTIC
BEGIN
    -- Declaramos una variable para almacenar el id del país
    DECLARE id_pais INT;

    -- Buscamos el id del país según el nombre del país
    SELECT p.id_pais INTO id_pais
    FROM paises p
    WHERE p.nombre_pais = input_nombre_pais
    LIMIT 1;

    -- Retornamos el id del país
    RETURN id_pais;
END //
DELIMITER ;

SELECT country('Italia');
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.25 puntos
# Correctamente resuelto: 0.50 puntos 


# 09. Crea una función (llamada fact_total_cliente) que, dado el nombre y apellido de un cliente,
-- devuelva su facturación total (incluso si es 0);
DROP FUNCTION IF exists  fact_total_cliente;
DELIMITER //
CREATE FUNCTION fact_total_cliente(input_nombre_cliente VARCHAR(25), input_apellido_cliente VARCHAR(50)) RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
    -- Declaramos una variable para almacenar la facturación total
    DECLARE total_facturacion DECIMAL(10,2) DEFAULT 0;

    -- Calculamos la facturación total para el cliente dado
    SELECT IFNULL(SUM(DATEDIFF(IFNULL(a.fecha_devolucion, CURDATE()), a.fecha_recogida) * v.precio_dia), 0)
    INTO total_facturacion
    FROM clientes c
    JOIN alquileres a 
    ON c.id_cliente = a.id_cliente
    JOIN vehiculos v 
    ON a.id_vehiculo = v.id_vehiculo
    WHERE c.nombre_cliente = input_nombre_cliente AND c.apellido_cliente = input_apellido_cliente;

    -- Retornamos la facturación total
    RETURN total_facturacion;
END //
DELIMITER ;

SELECT fact_total_cliente('Bill', 'Gates');
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.25 puntos
# Correctamente resuelto: 0.50 puntos 


# 10. Crea una función (llamada total_fact_year) que, dado un año, 
-- devuelva la facturación total de ese año;
DROP FUNCTION IF exists total_fact_year;
DELIMITER //
CREATE FUNCTION total_fact_year(input_year INT) RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
    -- Declaramos una variable para almacenar la facturación total
    DECLARE total_facturacion DECIMAL(10,2) DEFAULT 0;

    -- Calculamos la facturación total para el año dado
    SELECT IFNULL(SUM(DATEDIFF(IFNULL(a.fecha_devolucion, CURDATE()), a.fecha_recogida) * v.precio_dia), 0)
    INTO total_facturacion
    FROM alquileres a
    JOIN vehiculos v ON a.id_vehiculo = v.id_vehiculo
    WHERE YEAR(a.fecha_recogida) = input_year;

    -- Retornamos la facturación total
    RETURN total_facturacion;
END //
DELIMITER ;

SELECT total_fact_year(2023) AS "Facturación";
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.25 puntos
# Correctamente resuelto: 0.50 puntos 


# 11. Crea una función (llamada fact_model) que, dados la marca y el nombre del modelo
-- devuelva la facturación total de ese modelo, incluso si es 0;
DROP FUNCTION IF exists fact_model;
DELIMITER //
CREATE FUNCTION fact_model(input_marca VARCHAR(20), input_modelo VARCHAR(20)) RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
	-- Declaramos una variable para almacenar la facturación total de la marca y modelo
	DECLARE facturacion_modelo DECIMAL(10,2) DEFAULT 0;
    
    -- Calculamos facturación total para la marca y el modelo
    SELECT IFNULL(SUM(DATEDIFF(IFNULL(a.fecha_devolucion, CURDATE()), a.fecha_recogida) * v.precio_dia), 0)
    INTO facturacion_modelo
    FROM modelos m
    JOIN vehiculos v
    ON v.id_modelo = m.id_modelo
    JOIN alquileres a
    ON a.id_vehiculo = v.id_vehiculo
    WHERE m.marca = input_marca and m.nombre_modelo = input_modelo
    GROUP BY m.marca;
    
    -- Retornamos la facturación total para la marca y el modelo
    RETURN facturacion_modelo;
END //
DELIMITER ;

SELECT fact_model("Nissan","Primastar") as "Facturación modelo";
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.50 puntos
# Correctamente resuelto: 1.00 puntos 


# 12. Crea una función (llamada units_conc) que, dado el nombre del concesionario
-- devuelva la cantidad de unidades que tiene ese concesionario
-- o bien cero si ese concesionario no está en la BD;

# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.50 puntos
# Correctamente resuelto: 1.00 puntos 

# TRIGGERS

# 13. Crea un trigger que, en caso de que se anule un alquiler, actualice el stock actual.
use renting_cars2;

DROP TRIGGER IF EXISTS after_delete_alquileres;
DELIMITER //
CREATE TRIGGER after_delete_alquileres
AFTER DELETE ON alquileres
FOR EACH ROW
BEGIN
	-- Actualizamos el stock (incrementamos las unidades disponibles)
	UPDATE vehiculos 
    SET unidades = unidades + 1 
    WHERE id_vehiculo = OLD.id_vehiculo;
END//
DELIMITER ;

DELETE FROM alquileres WHERE id_alquiler = 5;
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.50 puntos
# Correctamente resuelto: 1.00 puntos 

# 14. Crea una tabla de nombre incidencias. Tendrá 4 columnas:
-- marca
-- modelo
-- incidencia (varchar 100)
-- fecha_incidencia (por defecto pondrá la fecha, hora, minutos y segundos actuales)
-- Modifica el trigger check_disponibilidad_vehiculos para que, en caso de no disponer
-- de stock, se inserte este mensaje en esa el campo incidencia:
-- "Falló el stock"
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.50 puntos
# Correctamente resuelto: 1.00 puntos