= Budget vs Actual Expenditure Analysis =

== 1. Scenario Overview ==

This scenario performs financial analysis by comparing planned wedding budgets against actual vendor expenditures.

The report aggregates costs from:
* venue bookings
* photographer services
* band entertainment

The analysis enables:
* budget tracking
* financial reconciliation
* identification of budget overruns
* expenditure monitoring
* variance calculation

== 1.1 Objective ==

Perform comprehensive financial analysis comparing budgeted wedding costs against actual vendor expenditures.

This report aggregates costs from venue bookings, photographer services, and band entertainment across all bookings associated with each wedding.

The analysis enables identification of budget overruns, cost variances, and financial reconciliation at the wedding level.

== 1.2 SQL Query Implementation ==

=== SQL Code ===

{{{
#!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(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(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(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,

    ROUND((
        (
            w.budget - (
                COALESCE(SUM(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)
            )
        ) / w.budget
    ) * 100, 2) AS budget_variance_percent

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;
}}}

== 1.3 Query Complexity Analysis ==

* Join Count: 7 tables
* Aggregate Functions: SUM(), COALESCE(), EXTRACT(), ROUND()
* Grouping Columns: wedding_id, organizer name, wedding date, and budget
* Temporal Calculation: EXTRACT(EPOCH FROM ...) converts time intervals into billable hours
* Financial Metrics: total cost, remaining budget, and variance percentage

== 1.4 Relational Algebra Expression ==

{{{
π(w.wedding_id, u.fname, u.lname, w.date, w.budget,
  SUM(vb.price),
  SUM((pb.end - pb.start) * p.rate),
  SUM((bb.end - bb.start) * b.rate))

(
    γ(
        wedding_id,
        SUM(venue_cost),
        SUM(photo_cost),
        SUM(band_cost)
    )

    (
        ρ(
            vb.price → venue_cost,
            (pb.end - pb.start) * p.price_per_hour → photo_cost,
            (bb.end - bb.start) * b.price_per_hour → band_cost
        )

        (
            (((Wedding ⟕ User)
            ⟕ Venue_Booking)
            ⟕ Photographer_Booking)
            ⟕ Photographer)
            ⟕ Band_Booking)
            ⟕ Band
    )
)
}}}

=== Notation ===

* π = Projection (SELECT clause)
* γ = Grouping and aggregation (GROUP BY)
* ⟕ = Left outer join (LEFT JOIN)
* ρ = Rename operation (AS)
* × = Cartesian product

=== Interpretation ===

The expression chains seven relations through left outer joins to preserve all weddings regardless of booking status.

The grouping operation aggregates cost components by wedding_id, enabling dimensional financial analysis of expenditures.

== 1.5 PostgreSQL Stored Procedure ==

=== SQL Code ===

{{{
#!sql
CREATE OR REPLACE PROCEDURE budget_variance_report(
    IN p_wedding_id INT DEFAULT NULL,
    IN p_start_date DATE DEFAULT '2020-01-01',
    IN p_end_date DATE DEFAULT '2099-12-31'
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_record RECORD;
    v_venue_cost NUMERIC;
    v_photographer_cost NUMERIC;
    v_band_cost NUMERIC;
    v_total_cost NUMERIC;
    v_remaining NUMERIC;
    v_variance 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,
        variance_percent 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)
            AND w.date BETWEEN p_start_date AND p_end_date

    LOOP

        SELECT COALESCE(SUM(vb.price), 0)
        INTO v_venue_cost
        FROM venue_booking vb
        WHERE vb.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

        LEFT 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

        LEFT 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;

        v_remaining := v_record.budget - v_total_cost;

        v_variance := ROUND(
            (v_remaining / v_record.budget) * 100,
            2
        );

        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_remaining,
            v_variance
        );

    END LOOP;

    RAISE NOTICE
        'Budget Variance Report Generated - % rows',
        (SELECT COUNT(*) FROM budget_variance_results);

END;
$$;
}}}

== 1.6 Procedure Characteristics ==

* Input Parameters:
  * wedding ID (optional)
  * start date
  * end date

* Iteration Logic:
  * cursor-based iteration through weddings

* Financial Calculations:
  * isolated cost calculations for each vendor category

* Error Handling:
  * RAISE NOTICE execution logging

* Temporary Storage:
  * results stored in temporary session-scoped table

== 1.7 Proof of Execution with Sample Data ==

=== Sample Data Insertion ===

{{{
#!sql
INSERT INTO "user" (
    first_name,
    last_name,
    email,
    phone_number
)
VALUES (
    'Марко',
    'Стојановски',
    'marko.stojanovski@email.com',
    '+38970111222'
);

INSERT INTO wedding (
    date,
    budget,
    user_id
)
VALUES (
    '2024-06-15',
    8500.00,
    1
);

INSERT INTO venue_type(type_name)
VALUES ('Wedding Hall');

INSERT INTO venue (
    name,
    location,
    city,
    address,
    capacity,
    price_per_guest,
    type_id
)
VALUES (
    'Golden Palace',
    'Centar',
    'Skopje',
    'Ilindenska 15',
    200,
    45.00,
    1
);

INSERT INTO venue_booking (
    date,
    start_time,
    end_time,
    status,
    price,
    venue_id,
    wedding_id
)
VALUES (
    '2024-06-15',
    '17:00:00',
    '23:00:00',
    'CONFIRMED',
    3600.00,
    1,
    1
);

INSERT INTO photographer (
    name,
    email,
    phone_number,
    price_per_hour
)
VALUES (
    'Foto Studio Aurora',
    'aurora@studio.mk',
    '+38970222333',
    150.00
);

INSERT INTO photographer_booking (
    date,
    start_time,
    end_time,
    status,
    photographer_id,
    wedding_id
)
VALUES (
    '2024-06-15',
    '16:00:00',
    '22:00:00',
    'CONFIRMED',
    1,
    1
);

INSERT INTO band (
    band_name,
    genre,
    equipment,
    phone_number,
    price_per_hour
)
VALUES (
    'Balkan Harmony',
    'Pop/Folk',
    'Full sound system',
    '+38970333444',
    200.00
);

INSERT INTO band_booking (
    date,
    start_time,
    end_time,
    status,
    band_id,
    wedding_id
)
VALUES (
    '2024-06-15',
    '17:30:00',
    '23:00:00',
    'CONFIRMED',
    1,
    1
);
}}}

=== Query Execution Result ===

{{{
 wedding_id | organizer_name   | wedding_date | budgeted_amount | venue_cost | photographer_cost | band_cost | total_actual_cost | remaining_budget | budget_variance_percent
------------+------------------+--------------+-----------------+------------+-------------------+-----------+-------------------+------------------+-------------------------
 1          | Марко Стојановски | 2024-06-15   | 8500.00         | 3600.00    | 900.00            | 1100.00   | 5600.00           | 2900.00          | 34.12
}}}

=== Calculation Verification ===

* Venue Cost: 3600.00
* Photographer Cost: 6 hours × 150.00 = 900.00
* Band Cost: 5.5 hours × 200.00 = 1100.00
* Total Actual Cost: 3600.00 + 900.00 + 1100.00 = 5600.00
* Remaining Budget: 8500.00 - 5600.00 = 2900.00
* Variance Percentage: (2900.00 / 8500.00) × 100 = 34.12%

== Summary ==

This scenario demonstrates advanced financial analysis using:
* multi-table joins
* aggregate calculations
* temporal SQL calculations
* stored procedures
* relational algebra representation
* reusable analytical reporting logic