USE sakila;

-- =====================================================
-- HANDS-ON 1: Create a view that shows customer rental 
-- information with customer names and film titles
-- =====================================================

CREATE VIEW customer_rental_info AS
SELECT 
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    f.title AS film_title,
    r.rental_date,
    r.return_date
FROM rental r
JOIN customer c ON r.customer_id = c.customer_id
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film f ON i.film_id = f.film_id;

-- Test it:
SELECT * FROM customer_rental_info LIMIT 10;

-- =====================================================
-- HANDS-ON 2: Create a view showing top-earning films
-- =====================================================

CREATE VIEW film_revenue AS
SELECT 
    f.title,
    c.name AS category,
    COUNT(r.rental_id) AS rental_count,
    SUM(p.amount) AS total_revenue
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON i.inventory_id = r.inventory_id
JOIN payment p ON r.rental_id = p.rental_id
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON fc.category_id = c.category_id
GROUP BY f.film_id, f.title, c.name
ORDER BY total_revenue DESC;

-- Test it:
SELECT * FROM film_revenue LIMIT 5;

-- =====================================================
-- HANDS-ON 3: Create a function to calculate late fees
-- =====================================================

DELIMITER $$
CREATE FUNCTION calculate_late_fee(rental_id_input INT)
RETURNS DECIMAL(5,2)
DETERMINISTIC
READS SQL DATA
BEGIN
	DECLARE days_late INT DEFAULT 0;
	DECLARE late_fee DECIMAL(5,2) DEFAULT 0.00;
	DECLARE rental_date_var DATETIME;
	DECLARE return_date_var DATETIME;
	DECLARE allowed_days INT;
    
	-- Get rental info
	SELECT r.rental_date, r.return_date, f.rental_duration
	INTO rental_date_var, return_date_var, allowed_days
	FROM rental r
	JOIN inventory i ON r.inventory_id = i.inventory_id
	JOIN film f ON i.film_id = f.film_id
	WHERE r.rental_id = rental_id_input;

	-- Calculate if late
	IF return_date_var IS NOT NULL THEN
		SET days_late = DATEDIFF(return_date_var, rental_date_var) - allowed_days;
		IF days_late > 0 THEN
			SET late_fee = days_late * 1.50;
		END IF;
	END IF;

	RETURN late_fee;
END$$
DELIMITER ;

-- Test it:
SELECT rental_id, calculate_late_fee(rental_id) AS late_fee
FROM rental
WHERE return_date IS NOT NULL
LIMIT 10;

-- =====================================================
-- HANDS-ON 4: Create a procedure to rent a film to 
-- a customer
-- =====================================================

DELIMITER $$
CREATE PROCEDURE rent_film(
    IN p_customer_id INT,
    IN p_inventory_id INT,
    IN p_staff_id INT
)
BEGIN
    DECLARE film_available INT DEFAULT 0;
    DECLARE result_message VARCHAR(100);
    
    -- Check if film is available (not currently rented)
    SELECT COUNT(*) INTO film_available
    FROM inventory i
    LEFT JOIN rental r ON i.inventory_id = r.inventory_id 
        AND r.return_date IS NULL
    WHERE i.inventory_id = p_inventory_id
        AND r.rental_id IS NULL;
    
    IF film_available > 0 THEN
        -- Insert rental
        INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id)
        VALUES (NOW(), p_inventory_id, p_customer_id, p_staff_id);
        
        SET result_message = 'Rental successful!';
    ELSE
        SET result_message = 'Error: Film not available';
    END IF;
    
    SELECT result_message AS message;
END$$
DELIMITER ;

-- Test it:
CALL rent_film(2, 10, 1);

-- =====================================================
-- HANDS-ON 5: Create a trigger to automatically update 
-- customer's last_update timestamp
-- =====================================================

DELIMITER $$
CREATE TRIGGER customer_last_update_trigger
BEFORE UPDATE ON customer
FOR EACH ROW
BEGIN
	SET NEW.last_update = NOW();
END$$
DELIMITER ;

-- Test it:
UPDATE customer
SET email = 'newemail@example.com'
WHERE customer_id = 1;

SELECT customer_id, email, last_update
FROM customer
WHERE customer_id = 1;

-- =====================================================
-- HANDS-ON 6: Create a procedure that uses a cursor 
-- to give loyalty points to top customers
-- =====================================================

-- Step 1: Add column (if not exists)
ALTER TABLE customer ADD COLUMN loyalty_points INT DEFAULT 0;

-- Step 2: Create procedure with cursor
DELIMITER $$
CREATE PROCEDURE award_loyalty_points()
BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE cust_id INT;
	DECLARE total_spent DECIMAL(10,2);
	-- Declare cursor
	DECLARE customer_cursor CURSOR FOR
		SELECT customer_id, SUM(amount) AS total
		FROM payment
		GROUP BY customer_id
		HAVING total > 150;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

	OPEN customer_cursor;

	read_loop: LOOP
		FETCH customer_cursor INTO cust_id, total_spent;
		IF done THEN
			LEAVE read_loop;
		END IF;
		
		-- Award points
		UPDATE customer 
		SET loyalty_points = loyalty_points + 100
		WHERE customer_id = cust_id;
	END LOOP;

	CLOSE customer_cursor;

	SELECT 'Loyalty points awarded!' AS message;
END$$
DELIMITER ;

-- Test it:
CALL award_loyalty_points();

-- Check results
SELECT customer_id,
CONCAT(first_name, ' ', last_name) AS name,
loyalty_points
FROM customer
WHERE loyalty_points > 0;