# 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


# 01. Reforma la vista facturación para incluir :
-- el nombre del país, 
-- la marca, 
-- el modelo y 
-- los concesionarios de cada vehículo
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.25 puntos
# Correctamente resuelto: 0.50 puntos
use renting_cars2;
DROP VIEW IF EXISTS facturacion;
CREATE VIEW facturacion as
SELECT c.nombre_cliente, c.apellido_cliente, p.nombre_pais as "pais", m.marca, 
m.nombre_modelo as "modelo", co.nombre_concesionario as "concesionario",
fecha_recogida "recogida", ifnull(fecha_devolucion, "pendiente") "devolucion",
datediff(ifnull(fecha_devolucion, curdate()) + 1, fecha_recogida) "dias",
v.precio_dia as "precio x 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;

# PROCEDIMIENTOS ALMACENADOS

# 02. Crea un SP (llamado cars_no_rent) para mostrar los modelos que no se han alquilado nunca
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.25 puntos
# Correctamente resuelto: 0.50 puntos

 use renting_cars2;
DROP PROCEDURE IF EXISTS cars_no_rent
DELIMITER //
CREATE PROCEDURE cars_no_rent()
COMMENT "Modelos que no se han alquilado nunca"
BEGIN
SELECT nombre_modelo as "modelo"
FROM modelos m
JOIN vehiculos v
ON v.id_modelo = m.id_modelo
LEFT JOIN alquileres a
ON v.id_vehiculo = a.id_vehiculo
WHERE a.id_vehiculo is null;
END//
DELIMITER ;
CALL car_no_rent();

#pruebas

SELECT m.nombre_modelo
FROM modelos m
LEFT JOIN vehiculos v ON m.id_modelo = v.id_modelo
LEFT JOIN alquileres a ON v.id_vehiculo = a.id_vehiculo
WHERE a.id_vehiculo IS NULL;
SELECT m.nombre_modelo
FROM alquileres a
LEFT JOIN vehiculos v
ON v.id_vehiculo = a.id_vehiculo
JOIN modelos m
ON m.id_modelo = v.id_modelo
where a.id_vehiculo is null


LEFT JOIN 
SELECT v.id_vehiculo, a.fecha_recogida
FROM vehiculos v
JOIN alquileres a
ON v.id_vehiculo = a.id_vehiculo
where 
SELECT * FROM vehiculos
SELECT nombre_modelo as "modelo"
FROM modelos m
WHERE v.id_vehiculo != a.id_vehiculo;

SELECT m.nombre_modelo
FROM modelos m
LEFT JOIN vehiculos v ON m.id_modelo = v.id_modelo
LEFT JOIN alquileres a ON v.id_vehiculo = a.id_vehiculo
WHERE a.id_vehiculo IS NULL;

SELECT 
FROM
JOIN
ON 
WHERE
# 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" 
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.25 puntos
# Correctamente resuelto: 0.50 puntos
DROP PROCEDURE IF EXISTS list_fact_by_year
DELIMITER //
CREATE PROCEDURE list_fact_by_year()
COMMENT "Facturacion x año"
BEGIN
SELECT YEAR(fecha_recogida) AS año, 
           SUM(DATEDIFF(fecha_devolucion, fecha_recogida) * v.precio_dia) AS facturacion_anual
    FROM alquileres a
    JOIN vehiculos v
    ON v.id_vehiculo = a.id_vehiculo
    GROUP BY YEAR(fecha_recogida)
    ORDER BY año;
END//
DELIMITER ;
#pruebas

SELECT YEAR(fecha_recogida) AS año, 
           SUM(DATEDIFF(fecha_devolucion, fecha_recogida) * v.precio_dia) AS facturacion_anual
    FROM alquileres a
    JOIN vehiculos v
    ON v.id_vehiculo = a.id_vehiculo
    GROUP BY YEAR(fecha_recogida)
    ORDER BY año;
    
# 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" 
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.25 puntos
# Correctamente resuelto: 0.50 puntos
DROP PROCEDURE IF EXISTS fact_by_year;
DELIMITER //
CREATE PROCEDURE fact_by_year (in año_fact INT)
BEGIN
SELECT YEAR(fecha_recogida) AS año, 
           SUM(DATEDIFF(fecha_devolucion, fecha_recogida) * v.precio_dia) AS "facturacion anual"
    FROM alquileres a
    JOIN vehiculos v
    ON v.id_vehiculo = a.id_vehiculo
    WHERE YEAR (fecha_devolucion) = año_fact
    GROUP BY YEAR(fecha_recogida);
    END //
    DELIMITER ;
    CALL fact_by_year(2024)
    

# 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
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.50 puntos
# Correctamente resuelto: 1.00 puntos 
DROP PROCEDURE IF EXISTS facturacion_pais;
DELIMITER //
CREATE PROCEDURE facturacion_pais (in pais VARCHAR(10))
BEGIN
SELECT nombre_pais, 
           SUM(DATEDIFF(fecha_devolucion, fecha_recogida) * v.precio_dia) AS "facturacion"
    FROM alquileres a
    JOIN clientes c
    ON c.id_cliente = a.id_cliente
    JOIN paises p
    ON p.id_pais = c.id_pais
    JOIN vehiculos v
    ON a.id_vehiculo = v.id_vehiculo
    WHERE p.nombre_pais = pais
    GROUP BY p.nombre_pais;
END // 
DELIMITER ;
CALL facturacion_pais("italia")
# 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
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.50 puntos
# Correctamente resuelto: 1.00 puntos 

DROP PROCEDURE IF EXISTS insert_cars;
DELIMITER //
CREATE PROCEDURE insert_cars(in marca_nuevo varchar(30), in modelo_nuevo varchar(30), in unidades_nuevo INT)
BEGIN
UPDATE vehiculos v
JOIN modelos m ON v.id_modelo = m.id_modelo
SET unidades = unidades + unidades_nuevo
WHERE m.nombre_modelo = modelo_nuevo
AND m.marca = marca_nuevo;
END //
DELIMITER ;
CALL insert_cars ("chevrolet", "captiva", 3);

#pruebas

SELECT v.unidades, m.nombre_modelo,m.marca
FROM vehiculos v
JOIN modelos m ON v.id_modelo = m.id_modelo
WHERE m.marca = "Fiat"
# 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:
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.50 puntos
# Correctamente resuelto: 1.00 puntos 
DROP PROCEDURE IF EXISTS car_rent
DELIMITER //
CREATE PROCEDURE car_rent(in nombre_cn varchar (40), in apellido_cn varchar(30), in marca_cn varchar(15),
in modelo_cn varchar(20), in recogida_cn DATE, in devolucion_cn DATE)
BEGIN
SELECT m.nombre_modelo FROM modelos m
JOIN vehiculos v ON v.id_modelo = m.id_modelo
JOIN alquileres a ON a.id_vehiculo = v.id_vehiculo
JOIn clientes c ON c.id_cliente = a.id_cliente
WHERE marca_cn = nombre_modelo,
IF v.unidades = NULL  THEN "no hay stock";
ELSE
UPDATE clientes c
SET c.nombre_cliente = nombre_cn, c.apellido_cliente = apellido_cn, 
a.fecha_recogida = recogida_cn, a.fecha_devolucion = devolucion_cn;
END IF
END // 
DELIMITER ;
CALL car_rent("Pablo", "Perez", "chevrolet", "captiva", 2024-09-17, 2024-11-24)


#pruebas 

DECLARE unidades_disponibles INT;

    -- Verificar si hay unidades disponibles del vehículo especificado
    SELECT v.unidades INTO unidades_disponibles
    FROM vehiculos v
    JOIN modelos m ON v.id_modelo = m.id_modelo
    WHERE m.marca = marca_cn AND m.nombre_modelo = modelo_cn;

    -- Si no hay unidades disponibles, devolver un mensaje
    IF unidades_disponibles IS NULL OR unidades_disponibles = 0 THEN
        SELECT 'No hay stock disponible' AS mensaje;
    ELSE
        -- Registrar el alquiler en la tabla alquileres
        INSERT INTO alquileres (id_cliente, id_vehiculo, fecha_recogida, fecha_devolucion)
        SELECT c.id_cliente, v.id_vehiculo, recogida_cn, devolucion_cn
        FROM clientes c
        JOIN vehiculos v ON v.id_modelo = (SELECT id_modelo FROM modelos WHERE marca = marca_cn AND nombre_modelo = modelo_cn)
        WHERE c.nombre_cliente = nombre_cn AND c.apellido_cliente = apellido_cn;
        
        -- Restar una unidad del vehículo alquilado
        UPDATE vehiculos v
        JOIN modelos m ON v.id_modelo = m.id_modelo
        SET v.unidades = v.unidades - 1
        WHERE m.marca = marca_cn AND m.nombre_modelo = modelo_cn;

        SELECT 'Alquiler registrado exitosamente' AS mensaje;
    END IF;
    
    
    
# FUNCIONES

# 08. Crea una función (llamada country) que, dado el nombre de un país
-- devuelva su id de la tabla paises
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.25 puntos
# Correctamente resuelto: 0.50 puntos 

DROP FUNCTION IF EXISTS country
DELIMITER //
CREATE FUNCTION country (pais VARCHAR(15))RETURNS INT READS SQL DATA
BEGIN
DECLARE id_pais INT;
SELECT p.id_pais INTO id_pais
FROM paises p
WHERE p.nombre_pais = pais;
RETURN id_pais;
END//
DELIMITER ;
SELECT country("Italia")

# pruebas

# 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);
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.25 puntos
# Correctamente resuelto: 0.50 puntos 
DROP FUNCTION IF EXISTS fact_total_cliente;

DELIMITER //

CREATE FUNCTION fact_total_cliente (nombre_c VARCHAR(30), apellido_c VARCHAR(30)) RETURNS FLOAT READS SQL DATA
BEGIN
    DECLARE facturacion_total FLOAT;

    SELECT SUM(DATEDIFF(fecha_devolucion, fecha_recogida) * v.precio_dia)
    INTO facturacion_total
    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 = nombre_c 
    AND c.apellido_cliente = apellido_c;

    IF facturacion_total IS NULL THEN
        SET facturacion_total = 0;
    END IF;

    RETURN facturacion_total;
END //

DELIMITER ;

SELECT fact_total_cliente('Jeff', 'Bezos');

# 10. Crea una función (llamada total_fact_year) que, dado un año, 
-- devuelva la facturación total de ese año;
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.25 puntos
# Correctamente resuelto: 0.50 puntos 

DROP FUNCTION IF EXISTS total_fact_year;

DELIMITER //

CREATE FUNCTION total_fact_year (año_fact YEAR) RETURNS FLOAT READS SQL DATA
BEGIN
    DECLARE facturacion_total FLOAT;

    SELECT SUM(DATEDIFF(a.fecha_devolucion, a.fecha_recogida) * v.precio_dia)
    INTO facturacion_total
    FROM alquileres a
    JOIN vehiculos v ON a.id_vehiculo = v.id_vehiculo
   WHERE year (a.fecha_recogida) = año_fact;

    IF facturacion_total IS NULL THEN
        SET facturacion_total = 0;
    END IF;

    RETURN facturacion_total;
END //

DELIMITER ;

SELECT total_fact_year(2022);
# 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;
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.50 puntos
# Correctamente resuelto: 1.00 puntos 

DROP FUNCTION IF EXISTS fact_model;

DELIMITER //

CREATE FUNCTION fact_model (marca_f VARCHAR(30), modelo_f VARCHAR(30)) RETURNS FLOAT READS SQL DATA
BEGIN
    DECLARE facturacion_total FLOAT;

    SELECT SUM(DATEDIFF(a.fecha_devolucion, a.fecha_recogida) * v.precio_dia)
    INTO facturacion_total
    FROM alquileres a 
    JOIN vehiculos v ON a.id_vehiculo = v.id_vehiculo
    JOIN modelos m ON m.id_modelo = v.id_modelo
    WHERE m.marca = marca_f
    AND m.nombre_modelo = modelo_f;

    IF facturacion_total IS NULL THEN
        SET facturacion_total = 0;
    END IF;

    RETURN facturacion_total;
END //

DELIMITER ;

SELECT fact_model('chevrolet', 'captiva');

# 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 

DROP FUNCTION IF EXISTS units_conc;

DELIMITER //

CREATE FUNCTION units_conc (nombre_conc VARCHAR(30)) RETURNS INT READS SQL DATA
BEGIN
    DECLARE unidades_conc INT;

    SELECT SUM(v.unidades)
    INTO unidades_conc
    FROM vehiculos v
    JOIN concesionarios c ON c.id_concesionario = v.id_concesionario
    WHERE c.nombre_concesionario = nombre_conc;

    IF unidades_conc IS NULL THEN
        SET unidades_conc = 0;
    END IF;


    RETURN unidades_conc;
END //

DELIMITER ;

SELECT units_conc('BCN autos');


-- Pruebas
SELECT v.unidades, c.nombre_concesionario
FROM concesionarios c
JOIN vehiculos v ON v.id_concesionario = c.id_concesionario
# TRIGGERS

# 13. Crea un trigger que, en caso de que se anule un alquiler, actualice el stock actual.
# No resuelto o mal resuelto: 0 puntos
# Resuelto con errores leves: 0.50 puntos
# Correctamente resuelto: 1.00 puntos 

DELIMITER //

CREATE TRIGGER actualizar_stock_alquiler_anulado
AFTER DELETE ON alquileres
FOR EACH ROW
BEGIN
    UPDATE vehiculos
    SET unidades = unidades + 1
    WHERE id_vehiculo = OLD.id_vehiculo;
END //
DELIMITER ;
# 14. Crea una tabla de nombre incidencias. Tendrá dos columnas:
-- marca
-- modelo
-- incidencia (varchar 100)
-- fecha_incidencia (por defecto pondrá la fecha, hora, minutos y segundos actuales)
-- Modifica el trigger check_unidades_before_insert 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