Dvdrental -

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;

-- 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(); $$ ); dvdrental