Work — Dvdrental

// Calculate late fee const fee = await pool.query( SELECT calculate_late_fee($1) AS fee , [rentalId]);

I'll help you develop a feature for the classic PostgreSQL sample database. This is a well-known database schema for a movie rental store. Feature: "Late Return Penalty Calculator & Customer Alert System" This feature identifies overdue rentals, calculates late fees, and sends alerts to customers. 1. Database Schema Overview (Key Tables) -- Main tables involved: -- rental (rental_id, customer_id, inventory_id, rental_date, return_date) -- customer (customer_id, first_name, last_name, email, address_id) -- inventory (inventory_id, film_id, store_id) -- film (film_id, title, rental_rate) -- payment (payment_id, customer_id, amount, payment_date) 2. Feature Components A. Identify Overdue Rentals CREATE OR REPLACE VIEW overdue_rentals AS SELECT r.rental_id, c.customer_id, c.first_name, c.last_name, c.email, f.title AS film_title, r.rental_date, r.return_date, CURRENT_DATE - r.rental_date::DATE AS days_rented, f.rental_duration, CASE WHEN r.return_date IS NULL AND (CURRENT_DATE - r.rental_date::DATE) > f.rental_duration THEN (CURRENT_DATE - r.rental_date::DATE) - f.rental_duration ELSE 0 END AS days_overdue 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 WHERE r.return_date IS NULL AND (CURRENT_DATE - r.rental_date::DATE) > f.rental_duration; B. Calculate Late Fees CREATE OR REPLACE FUNCTION calculate_late_fee( p_rental_id INTEGER ) RETURNS NUMERIC AS $$ DECLARE v_days_overdue INTEGER; v_late_fee NUMERIC; v_film_title TEXT; BEGIN -- Calculate days overdue SELECT GREATEST(0, (CURRENT_DATE - r.rental_date::DATE) - f.rental_duration), f.title INTO v_days_overdue, v_film_title 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 = p_rental_id AND r.return_date IS NULL; -- Late fee: $0.50 per day overdue v_late_fee := v_days_overdue * 0.50; dvdrental

// Update return date await pool.query( UPDATE rental SET return_date = NOW() WHERE rental_id = $1 , [rentalId]); // Calculate late fee const fee = await pool

-- Generate customer alerts for today SELECT * FROM generate_late_return_alerts(); f.title AS film_title