# 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

CREATE OR REPLACE VIEW facturacion AS
SELECT 
    a.id_alquiler,
    c.nombre_cliente,
    c.apellido_cliente,
    p.nombre_pais,
    m.marca,
    m.nombre_modelo,
    con.nombre_concesionario,
    v.precio_dia * DATEDIFF(a.fecha_devolucion, a.fecha_recogida) AS total_facturacion
FROM alquileres a
JOIN clientes c ON a.id_cliente = c.id_cliente
JOIN paises p ON c.id_pais = p.id_pais
JOIN vehiculos v ON a.id_vehiculo = v.id_vehiculo
JOIN modelos m ON v.id_modelo = m.id_modelo
JOIN concesionarios con ON v.id_concesionario = con.id_concesionario;

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

DELIMITER //

CREATE PROCEDURE cars_no_rent()
BEGIN
    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_alquiler IS NULL;
END //

DELIMITER ;

CALL cars_no_rent();

# 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

DELIMITER //

CREATE PROCEDURE list_fact_by_year()
BEGIN
    SELECT 
        YEAR(a.fecha_recogida) AS año, 
        SUM(v.precio_dia * DATEDIFF(a.fecha_devolucion, a.fecha_recogida)) AS facturacion_anual
    FROM alquileres a
    JOIN vehiculos v ON a.id_vehiculo = v.id_vehiculo
    GROUP BY YEAR(a.fecha_recogida);
END //

DELIMITER ;

CALL list_fact_by_year();

# 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

DELIMITER //

CREATE PROCEDURE fact_by_year(IN input_year INT)
BEGIN
    SELECT 
        SUM(v.precio_dia * DATEDIFF(a.fecha_devolucion, a.fecha_recogida)) AS facturacion_anual
    FROM alquileres a
    JOIN vehiculos v ON a.id_vehiculo = v.id_vehiculo
    WHERE YEAR(a.fecha_recogida) = input_year;
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 

DELIMITER //

CREATE PROCEDURE facturacion_pais(IN pais_nombre VARCHAR(50))
BEGIN
    SELECT 
        SUM(v.precio_dia * DATEDIFF(a.fecha_devolucion, a.fecha_recogida)) AS facturacion_total
    FROM alquileres a
    JOIN clientes c ON a.id_cliente = c.id_cliente
    JOIN paises p ON c.id_pais = p.id_pais
    JOIN vehiculos v ON a.id_vehiculo = v.id_vehiculo
    WHERE p.nombre_pais = pais_nombre;
END //

DELIMITER ;

CALL facturacion_pais('España');

# 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 

DELIMITER //

CREATE PROCEDURE update_units_cars(IN marca_input VARCHAR(50), IN modelo_input VARCHAR(50), IN new_units INT)
BEGIN
    UPDATE vehiculos v
    JOIN modelos m ON v.id_modelo = m.id_modelo
    SET v.unidades = v.unidades + new_units
    WHERE m.marca = marca_input AND m.nombre_modelo = modelo_input;
END //

DELIMITER ;

CALL update_units_cars('Fiat', 'Panda', 3);

SELECT unidades FROM vehiculos v
JOIN modelos m ON v.id_modelo = m.id_modelo
WHERE m.marca = 'Fiat' AND m.nombre_modelo = 'Panda';

# 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 

DELIMITER //

CREATE PROCEDURE car_rent(
    IN cliente_nombre VARCHAR(50), 
    IN cliente_apellido VARCHAR(50), 
    IN marca_input VARCHAR(50), 
    IN modelo_input VARCHAR(50), 
    IN fecha_inicio DATE, 
    IN fecha_fin DATE)
BEGIN
    DECLARE unidades_disponibles INT;
    
    SELECT v.unidades INTO unidades_disponibles
    FROM vehiculos v
    JOIN modelos m ON v.id_modelo = m.id_modelo
    WHERE m.marca = marca_input AND m.nombre_modelo = modelo_input
    LIMIT 1;
    
    IF unidades_disponibles > 0 THEN
        INSERT INTO alquileres (id_cliente, id_vehiculo, fecha_recogida, fecha_devolucion)
        SELECT c.id_cliente, v.id_vehiculo, fecha_inicio, fecha_fin
        FROM clientes c, vehiculos v
        JOIN modelos m ON v.id_modelo = m.id_modelo
        WHERE c.nombre_cliente = cliente_nombre 
        AND c.apellido_cliente = cliente_apellido
        AND m.marca = marca_input
        AND m.nombre_modelo = modelo_input
        LIMIT 1;
        
        UPDATE vehiculos v
        JOIN modelos m ON v.id_modelo = m.id_modelo
        SET v.unidades = v.unidades - 1
        WHERE m.marca = marca_input AND m.nombre_modelo = modelo_input;
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No units available';
    END IF;
END //

DELIMITER ;

CALL car_rent('Bill', 'Gates', 'Nissan', 'Juke', '2024-10-01', '2024-10-10');

SELECT * FROM alquileres;

# 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 

DELIMITER //

CREATE FUNCTION country(pais_nombre VARCHAR(50)) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE pais_id INT;
    SELECT id_pais INTO pais_id FROM paises WHERE nombre_pais = pais_nombre;
    RETURN pais_id;
END //

DELIMITER ;

SELECT country('España');

# 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 

DELIMITER //

CREATE FUNCTION fact_total_cliente(nombre_cliente VARCHAR(50), apellido_cliente VARCHAR(50)) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE total_facturacion DECIMAL(10,2);
    SELECT SUM(v.precio_dia * DATEDIFF(a.fecha_devolucion, a.fecha_recogida)) INTO total_facturacion
    FROM alquileres a
    JOIN clientes c ON a.id_cliente = c.id_cliente
    JOIN vehiculos v ON a.id_vehiculo = v.id_vehiculo
    WHERE c.nombre_cliente = nombre_cliente AND c.apellido_cliente = apellido_cliente;
    RETURN IFNULL(total_facturacion, 0);
END //

DELIMITER ;

SELECT fact_total_cliente('Bill', 'Gates');


# 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 

DELIMITER //

CREATE FUNCTION total_fact_year(input_year INT) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE total_facturacion DECIMAL(10,2);
    SELECT SUM(v.precio_dia * DATEDIFF(a.fecha_devolucion, a.fecha_recogida)) INTO total_facturacion
    FROM alquileres a
    JOIN vehiculos v ON a.id_vehiculo = v.id_vehiculo
    WHERE YEAR(a.fecha_recogida) = input_year;
    RETURN IFNULL(total_facturacion, 0);
END //

DELIMITER ;

SELECT total_fact_year(2024);

# 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 

DELIMITER //

CREATE FUNCTION fact_model(marca_input VARCHAR(50), modelo_input VARCHAR(50)) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE total_facturacion DECIMAL(10,2);
    SELECT SUM(v.precio_dia * DATEDIFF(a.fecha_devolucion, a.fecha_recogida)) INTO total_facturacion
    FROM alquileres a
    JOIN vehiculos v ON a.id_vehiculo = v.id_vehiculo
    JOIN modelos m ON v.id_modelo = m.id_modelo
    WHERE m.marca = marca_input AND m.nombre_modelo = modelo_input;
    RETURN IFNULL(total_facturacion, 0);
END //

DELIMITER ;

SELECT fact_model('Fiat', 'Panda');

# 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

DELIMITER //

CREATE FUNCTION units_conc(nombre_concesionario VARCHAR(50)) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE total_units INT;
    SELECT SUM(v.unidades) INTO total_units
    FROM vehiculos v
    JOIN concesionarios con ON v.id_concesionario = con.id_concesionario
    WHERE con.nombre_concesionario = nombre_concesionario;
    RETURN IFNULL(total_units, 0);
END //

DELIMITER ;

SELECT units_conc('VW Motors');

# 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 update_stock_on_cancel AFTER DELETE ON alquileres
FOR EACH ROW
BEGIN
    UPDATE vehiculos
    SET unidades = unidades + 1
    WHERE id_vehiculo = OLD.id_vehiculo;
END //

DELIMITER ;

DELETE FROM alquileres WHERE id_alquiler = 1;

SELECT unidades FROM vehiculos;

# 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