= Budget vs Actual Expenditure Analysis =

== Objective ==

This report compares the planned wedding budget with the actual costs of booked services.

It includes venue costs, photographer costs, and band costs. The report helps identify remaining budget, total spending, and budget variance.

== SQL Query Implementation ==

{{{
#!sql
SELECT
    w.wedding_id,
    u.first_name || ' ' || u.last_name AS organizer_name,
    w."date" AS wedding_date,
    w.budget AS budgeted_amount,

    COALESCE(SUM(DISTINCT vb.price), 0) AS venue_cost,

    COALESCE(SUM(
        EXTRACT(EPOCH FROM (pb.end_time - pb.start_time)) / 3600
        * p.price_per_hour
    ), 0) AS photographer_cost,

    COALESCE(SUM(
        EXTRACT(EPOCH FROM (bb.end_time - bb.start_time)) / 3600
        * b.price_per_hour
    ), 0) AS band_cost,

    COALESCE(SUM(DISTINCT vb.price), 0)
    + COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time)) / 3600 * p.price_per_hour), 0)
    + COALESCE(SUM(EXTRACT(EPOCH FROM (bb.end_time - bb.start_time)) / 3600 * b.price_per_hour), 0)
    AS total_actual_cost,

    w.budget -
    (
        COALESCE(SUM(DISTINCT vb.price), 0)
        + COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time)) / 3600 * p.price_per_hour), 0)
        + COALESCE(SUM(EXTRACT(EPOCH FROM (bb.end_time - bb.start_time)) / 3600 * b.price_per_hour), 0)
    ) AS remaining_budget

FROM wedding w
LEFT JOIN "user" u ON w.user_id = u.user_id
LEFT JOIN venue_booking vb ON w.wedding_id = vb.wedding_id
LEFT JOIN photographer_booking pb ON w.wedding_id = pb.wedding_id
LEFT JOIN photographer p ON pb.photographer_id = p.photographer_id
LEFT JOIN band_booking bb ON w.wedding_id = bb.wedding_id
LEFT JOIN band b ON bb.band_id = b.band_id
GROUP BY w.wedding_id, u.first_name, u.last_name, w."date", w.budget
ORDER BY w.wedding_id;
}}}

== Query Complexity Analysis ==

* Join count: 7 tables
* Aggregate functions: SUM, COALESCE, EXTRACT
* Temporal calculation: booking duration is converted from time interval to hours
* Grouping: results are grouped per wedding

== Relational Algebra Expression ==

{{{
π wedding_id, organizer_name, wedding_date, budget, venue_cost, photographer_cost, band_cost, total_actual_cost
(
  γ wedding_id;
    SUM(venue_booking.price),
    SUM((photographer_booking.end_time - photographer_booking.start_time) * photographer.price_per_hour),
    SUM((band_booking.end_time - band_booking.start_time) * band.price_per_hour)
  (
    Wedding ⟕ User ⟕ Venue_Booking ⟕ Photographer_Booking ⟕ Photographer ⟕ Band_Booking ⟕ Band
  )
)
}}}

== Stored Procedure ==

{{{
#!sql
CREATE OR REPLACE PROCEDURE budget_variance_report(
    IN p_wedding_id INT DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_record RECORD;
    v_venue_cost NUMERIC;
    v_photographer_cost NUMERIC;
    v_band_cost NUMERIC;
    v_total_cost NUMERIC;
BEGIN
    CREATE TEMP TABLE budget_variance_results (
        wedding_id INTEGER,
        organizer_name VARCHAR,
        wedding_date DATE,
        budgeted_amount NUMERIC,
        venue_cost NUMERIC,
        photographer_cost NUMERIC,
        band_cost NUMERIC,
        total_actual_cost NUMERIC,
        remaining_budget NUMERIC
    );

    FOR v_record IN
        SELECT w.wedding_id, u.first_name, u.last_name, w."date", w.budget
        FROM wedding w
        LEFT JOIN "user" u ON w.user_id = u.user_id
        WHERE p_wedding_id IS NULL OR w.wedding_id = p_wedding_id
    LOOP
        SELECT COALESCE(SUM(price), 0)
        INTO v_venue_cost
        FROM venue_booking
        WHERE wedding_id = v_record.wedding_id;

        SELECT COALESCE(SUM(EXTRACT(EPOCH FROM (pb.end_time - pb.start_time)) / 3600 * p.price_per_hour), 0)
        INTO v_photographer_cost
        FROM photographer_booking pb
        JOIN photographer p ON pb.photographer_id = p.photographer_id
        WHERE pb.wedding_id = v_record.wedding_id;

        SELECT COALESCE(SUM(EXTRACT(EPOCH FROM (bb.end_time - bb.start_time)) / 3600 * b.price_per_hour), 0)
        INTO v_band_cost
        FROM band_booking bb
        JOIN band b ON bb.band_id = b.band_id
        WHERE bb.wedding_id = v_record.wedding_id;

        v_total_cost := v_venue_cost + v_photographer_cost + v_band_cost;

        INSERT INTO budget_variance_results
        VALUES (
            v_record.wedding_id,
            v_record.first_name || ' ' || v_record.last_name,
            v_record."date",
            v_record.budget,
            v_venue_cost,
            v_photographer_cost,
            v_band_cost,
            v_total_cost,
            v_record.budget - v_total_cost
        );
    END LOOP;

    RAISE NOTICE 'Budget variance report generated.';
END;
$$;
}}}

== Purpose ==

This report supports financial planning by showing whether a wedding is within its planned budget or exceeding it.