= 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>

This function does not only compare equal dates. It compares complete timestamp intervals using:

{{{
#!sql
(new_start < existing_end) AND (new_end > existing_start)
}}}

This condition detects all possible overlap cases:
* the new interval starts inside an existing interval
* the new interval ends inside an existing interval
* the new interval completely contains an existing interval
* the existing interval completely contains the new interval
* both intervals are exactly the same
* intervals that only touch at the boundary are not treated as overlapping

== 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. is_band_available() ==

=== Description ===

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

This function uses full timestamp interval comparison by combining the booking date with start and end time. This avoids incorrect availability results when intervals overlap in different ways.

=== SQL Code ===

{{{
#!sql
CREATE OR REPLACE FUNCTION is_band_available(
    p_band_id INTEGER,
    p_date DATE,
    p_start TIME,
    p_end TIME
)
RETURNS BOOLEAN AS $$
BEGIN
    IF p_end <= p_start THEN
        RAISE EXCEPTION 'Invalid interval: end time must be after start time.';
    END IF;

    RETURN NOT EXISTS (
        SELECT 1
        FROM band_booking bb
        WHERE bb.band_id = p_band_id
          AND bb.status <> 'cancelled'
          AND (
                (p_date + p_start) < (bb."date" + bb.end_time)
            AND (p_date + p_end)   > (bb."date" + bb.start_time)
          )
    );
END;
$$ LANGUAGE plpgsql;
}}}

== 5. 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>