Dvdrental May 2026

-- Process payment for customer ID 5 CALL process_late_fee_payment(5, 15.00); // GET /api/rentals/overdue app.get('/api/rentals/overdue', async (req, res) => const result = await pool.query(` SELECT * FROM overdue_rentals ORDER BY days_overdue DESC `); res.json(result.rows); ); // POST /api/rentals/:rentalId/return app.post('/api/rentals/:rentalId/return', async (req, res) => const rentalId = req.params;

res.json( message: 'Rental returned', late_fee: fee.rows[0].fee ); ); Overdue Rentals View: dvdrental

// Calculate late fee const fee = await pool.query( SELECT calculate_late_fee($1) AS fee , [rentalId]); -- Process payment for customer ID 5 CALL

RETURN v_late_fee; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION generate_late_return_alerts() RETURNS TABLE( customer_name TEXT, email TEXT, overdue_films TEXT, total_late_fee NUMERIC, alert_message TEXT ) AS $$ BEGIN RETURN QUERY WITH overdue_summary AS ( SELECT c.customer_id, c.first_name || ' ' || c.last_name AS full_name, c.email, STRING_AGG(f.title, ', ' ORDER BY f.title) AS overdue_titles, COUNT(r.rental_id) AS overdue_count, SUM(GREATEST(0, (CURRENT_DATE - r.rental_date::DATE) - f.rental_duration) * 0.50) AS total_fee 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 GROUP BY c.customer_id, c.first_name, c.last_name, c.email ) SELECT os.full_name, os.email, os.overdue_titles, os.total_fee, CONCAT( 'Dear ', os.full_name, ', ', 'You have ', os.overdue_count, ' overdue rental(s): ', os.overdue_titles, '. ', 'Late fee: $', ROUND(os.total_fee, 2), '. ', 'Please return immediately to avoid additional charges.' ) AS alert_message FROM overdue_summary os; END; $$ LANGUAGE plpgsql; D. Process Late Fee Payment CREATE OR REPLACE PROCEDURE process_late_fee_payment( p_customer_id INTEGER, p_amount NUMERIC ) LANGUAGE plpgsql AS $$ DECLARE v_total_owed NUMERIC; BEGIN -- Calculate total late fees for customer SELECT COALESCE(SUM(GREATEST(0, (CURRENT_DATE - r.rental_date::DATE) - f.rental_duration) * 0.50), 0) INTO v_total_owed 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.customer_id = p_customer_id AND r.return_date IS NULL AND (CURRENT_DATE - r.rental_date::DATE) > f.rental_duration; IF p_amount >= v_total_owed THEN -- Insert payment record INSERT INTO payment ( customer_id, staff_id, rental_id, amount, payment_date ) SELECT p_customer_id, 1, -- Default staff ID NULL, v_total_owed, NOW() WHERE v_total_owed > 0; RAISE NOTICE 'Payment of $% processed for customer %. Late fees cleared.', v_total_owed, p_customer_id; ELSE RAISE EXCEPTION 'Insufficient payment. Amount due: $%', v_total_owed; END IF; END; $$; E. Scheduled Job (PostgreSQL pg_cron) -- Send daily late fee alerts (requires pg_cron extension) SELECT cron.schedule( 'late-fee-alerts', -- job name '0 9 * * *', -- every day at 9 AM $$ SELECT generate_late_return_alerts(); $$ ); 3. Usage Examples -- View all overdue rentals SELECT * FROM overdue_rentals; -- Check late fee for a specific rental SELECT calculate_late_fee(12345); Process Late Fee Payment CREATE OR REPLACE PROCEDURE

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;

-- Generate customer alerts for today SELECT * FROM generate_late_return_alerts();

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