= Stored Functions and Procedures =

== Description ==

This section contains reusable SQL functions and procedures used for calculations, validation, and reporting.

== 1. calculate_wedding_total_cost() ==

=== Description ===

Calculates the total cost of a wedding using venue, photographer, and band bookings.

=== SQL Code ===

<pre>
CREATE OR REPLACE FUNCTION calculate_wedding_total_cost(p_wedding_id INTEGER)
RETURNS NUMERIC AS $$
DECLARE
    total_cost NUMERIC;
BEGIN
    SELECT
        COALESCE(SUM(vb.price), 0)
    INTO total_cost
    FROM wedding w
    LEFT JOIN venue_booking vb ON w.wedding_id = vb.wedding_id
    WHERE w.wedding_id = p_wedding_id;

    RETURN total_cost;
END;
$$ LANGUAGE plpgsql;
</pre>

== 2. is_venue_available() ==

=== Description ===

Checks whether a venue is available for a selected date and time interval.

=== SQL Code ===

<pre>
CREATE OR REPLACE FUNCTION is_venue_available(
    p_venue_id INTEGER,
    p_date DATE,
    p_start TIME,
    p_end TIME
)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN NOT EXISTS (
        SELECT 1
        FROM venue_booking
        WHERE venue_id = p_venue_id
          AND "date" = p_date
          AND (
                p_start < end_time
            AND p_end > start_time
          )
    );
END;
$$ LANGUAGE plpgsql;
</pre>

== 3. is_photographer_available() ==

=== Description ===

Checks whether a photographer is available for a selected date and time interval.

=== SQL Code ===

<pre>
CREATE OR REPLACE FUNCTION is_photographer_available(
    p_photographer_id INTEGER,
    p_date DATE,
    p_start TIME,
    p_end TIME
)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN NOT EXISTS (
        SELECT 1
        FROM photographer_booking
        WHERE photographer_id = p_photographer_id
          AND "date" = p_date
          AND (
                p_start < end_time
            AND p_end > start_time
          )
    );
END;
$$ LANGUAGE plpgsql;
</pre>

== 4. generate_rsvp_summary() ==

=== Description ===

Returns RSVP statistics for a selected event.

=== SQL Code ===

<pre>
CREATE OR REPLACE PROCEDURE generate_rsvp_summary(p_event_id INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'Accepted: %',
    (
        SELECT COUNT(*)
        FROM event_rsvp
        WHERE event_id = p_event_id
          AND status = 'accepted'
    );
END;
$$;
</pre>