= Stored Functions and Procedures =

== Description ==

This section documents advanced PostgreSQL PL/pgSQL functions and procedures implemented for analytical processing, booking validation, overlap prevention, and RSVP reporting.

The implementation demonstrates reusable database-side logic for operational validation and analytical reporting.

== 1. calculate_wedding_total_cost() ==

=== Description ===

Calculates the total wedding cost by aggregating:
* venue booking expenses
* photographer booking expenses
* band booking expenses

=== SQL Code ===

{{{
#!sql
CREATE OR REPLACE FUNCTION calculate_wedding_total_cost(p_wedding_id INTEGER)
RETURNS NUMERIC AS $$

DECLARE

    venue_cost NUMERIC := 0;
    photographer_cost NUMERIC := 0;
    band_cost NUMERIC := 0;

BEGIN

    SELECT COALESCE(SUM(price), 0)
    INTO venue_cost
    FROM venue_booking
    WHERE wedding_id = p_wedding_id
      AND status = 'confirmed';

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

    INTO photographer_cost

    FROM photographer_booking pb

    JOIN photographer p
        ON pb.photographer_id = p.photographer_id

    WHERE pb.wedding_id = p_wedding_id
      AND pb.status = 'confirmed';

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

    INTO band_cost

    FROM band_booking bb

    JOIN band b
        ON bb.band_id = b.band_id

    WHERE bb.wedding_id = p_wedding_id
      AND bb.status = 'confirmed';

    RETURN venue_cost + photographer_cost + band_cost;

END;
$$ LANGUAGE plpgsql;
}}}

== 2. is_venue_available() ==

=== Description ===

Checks whether a venue is available for a requested time interval.

The implementation explicitly validates multiple overlap scenarios using separate interval comparison conditions.

The validation detects:
* intervals starting inside existing intervals
* intervals ending inside existing intervals
* intervals completely containing existing intervals
* intervals fully contained inside existing intervals
* identical intervals
* partial overlaps

Boundary-touching intervals are treated as valid non-overlapping intervals.

=== SQL Code ===

{{{
#!sql
CREATE OR REPLACE FUNCTION is_venue_available(

    p_venue_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 venue_booking vb

        WHERE vb.venue_id = p_venue_id
          AND vb.status <> 'cancelled'

          AND (

                ((p_date + p_start) >= (vb."date" + vb.start_time)
                 AND (p_date + p_start) < (vb."date" + vb.end_time))

                OR

                ((p_date + p_end) > (vb."date" + vb.start_time)
                 AND (p_date + p_end) <= (vb."date" + vb.end_time))

                OR

                ((p_date + p_start) <= (vb."date" + vb.start_time)
                 AND (p_date + p_end) >= (vb."date" + vb.end_time))

                OR

                ((p_date + p_start) >= (vb."date" + vb.start_time)
                 AND (p_date + p_end) <= (vb."date" + vb.end_time))
          )
    );

END;
$$ LANGUAGE plpgsql;
}}}

== 3. is_photographer_available() ==

=== Description ===

Checks whether a photographer is available for a requested booking interval.

The function validates all important overlap scenarios and prevents conflicting reservations.

=== SQL Code ===

{{{
#!sql
CREATE OR REPLACE FUNCTION is_photographer_available(

    p_photographer_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 photographer_booking pb

        WHERE pb.photographer_id = p_photographer_id
          AND pb.status <> 'cancelled'

          AND (

                ((p_date + p_start) >= (pb."date" + pb.start_time)
                 AND (p_date + p_start) < (pb."date" + pb.end_time))

                OR

                ((p_date + p_end) > (pb."date" + pb.start_time)
                 AND (p_date + p_end) <= (pb."date" + pb.end_time))

                OR

                ((p_date + p_start) <= (pb."date" + pb.start_time)
                 AND (p_date + p_end) >= (pb."date" + pb.end_time))

                OR

                ((p_date + p_start) >= (pb."date" + pb.start_time)
                 AND (p_date + p_end) <= (pb."date" + pb.end_time))
          )
    );

END;
$$ LANGUAGE plpgsql;
}}}

== 4. is_band_available() ==

=== Description ===

Checks whether a music band is available for a requested booking interval.

The implementation prevents all major overlap conflict scenarios.

=== 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.start_time)
                 AND (p_date + p_start) < (bb."date" + bb.end_time))

                OR

                ((p_date + p_end) > (bb."date" + bb.start_time)
                 AND (p_date + p_end) <= (bb."date" + bb.end_time))

                OR

                ((p_date + p_start) <= (bb."date" + bb.start_time)
                 AND (p_date + p_end) >= (bb."date" + bb.end_time))

                OR

                ((p_date + p_start) >= (bb."date" + bb.start_time)
                 AND (p_date + p_end) <= (bb."date" + bb.end_time))
          )
    );

END;
$$ LANGUAGE plpgsql;
}}}

== 5. generate_rsvp_summary() ==

=== Description ===

Generates RSVP statistics for a selected event.

The procedure reports:
* accepted RSVPs
* declined RSVPs
* pending RSVPs

=== SQL Code ===

{{{
#!sql
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'
    );

    RAISE NOTICE 'Declined: %',

    (
        SELECT COUNT(*)

        FROM event_rsvp

        WHERE event_id = p_event_id
          AND status = 'declined'
    );

    RAISE NOTICE 'Pending: %',

    (
        SELECT COUNT(*)

        FROM event_rsvp

        WHERE event_id = p_event_id
          AND status = 'pending'
    );

END;
$$;
}}}

== Summary ==

The implemented functions and procedures provide:
* reusable analytical logic
* overlap conflict prevention
* interval validation
* vendor availability checking
* RSVP reporting
* financial calculation functionality

The implementation demonstrates advanced procedural programming capabilities inside PostgreSQL using PL/pgSQL.