= Venue Capacity Utilization Analysis =

== 2. Scenario Overview ==

This scenario analyzes venue occupancy and attendance utilization for weddings organized inside the system.

The analysis combines:
* venue capacity information
* wedding bookings
* guest attendance records
* event participation statistics

The generated report provides operational insights regarding:
* occupancy percentage
* attendance efficiency
* available seating capacity
* utilization classification

== 2.1 Objective ==

Analyze the relationship between confirmed guest attendance and venue capacity constraints.

The report determines:
* actual occupancy rate
* available seating capacity
* venue utilization level
* attendance efficiency

This analysis combines attendance records, venue specifications, and booking confirmations to establish operational efficiency indicators.

== 2.2 SQL Query Implementation ==

=== SQL Code ===

{{{
#!sql
SELECT
    v.venue_id,
    v.name AS venue_name,
    v.capacity AS venue_capacity,

    w.wedding_id,

    u.first_name || ' ' || u.last_name
        AS organizer_name,

    w.date AS wedding_date,

    COUNT(DISTINCT a.guest_id)
        AS confirmed_attendees,

    COUNT(
        DISTINCT CASE
            WHEN a.status = 'ATTENDED'
            THEN a.guest_id
        END
    ) AS actual_attendance,

    v.capacity - COUNT(DISTINCT a.guest_id)
        AS available_seats,

    ROUND(
        (
            CAST(COUNT(DISTINCT a.guest_id) AS NUMERIC)
            / v.capacity
        ) * 100,
        2
    ) AS occupancy_rate_percent,

    CASE
        WHEN COUNT(DISTINCT a.guest_id) > v.capacity
            THEN 'EXCEEDED'

        WHEN COUNT(DISTINCT a.guest_id)
            >= (v.capacity * 0.9)
            THEN 'HIGH'

        WHEN COUNT(DISTINCT a.guest_id)
            >= (v.capacity * 0.6)
            THEN 'MODERATE'

        ELSE 'LOW'
    END AS utilization_category,

    vb.status AS booking_status,
    vb.date AS booking_date

FROM venue v

INNER JOIN venue_booking vb
    ON v.venue_id = vb.venue_id

INNER JOIN wedding w
    ON vb.wedding_id = w.wedding_id

INNER JOIN "user" u
    ON w.user_id = u.user_id

LEFT JOIN event e
    ON w.wedding_id = e.wedding_id

LEFT JOIN attendance a
    ON e.event_id = a.event_id
    AND a.status IN ('ATTENDED', 'CONFIRMED')

GROUP BY
    v.venue_id,
    v.name,
    v.capacity,
    w.wedding_id,
    u.first_name,
    u.last_name,
    w.date,
    vb.status,
    vb.date

HAVING COUNT(DISTINCT a.guest_id) > 0

ORDER BY
    v.venue_id,
    w.wedding_id;
}}}

== 2.3 Query Complexity Analysis ==

* Join Count:
  * 6 tables

* Join Types:
  * INNER JOIN
  * LEFT JOIN

* Aggregate Functions:
  * COUNT(DISTINCT ...)
  * ROUND()
  * CASE

* Filtering Logic:
  * HAVING clause after aggregation

* Conditional Categorization:
  * LOW
  * MODERATE
  * HIGH
  * EXCEEDED

== 2.4 Relational Algebra Expression ==

{{{
π(
    v.venue_id,
    v.name,
    v.capacity,
    w.wedding_id,
    u.fname,
    u.lname,
    w.date,
    COUNT(a.guest_id),
    v.capacity - COUNT(a.guest_id),
    (COUNT(a.guest_id) / v.capacity) * 100
)

(
    σ(COUNT(guest_id) > 0)

    (
        γ(
            venue_id,
            wedding_id,
            COUNT(DISTINCT a.guest_id)
        )

        (
            (((Venue ⟕ Venue_Booking)
            ⟕ Wedding)
            ⟕ User)
            ⟕ Event)
            ⟕ Attendance
        )
)
}}}

=== Notation ===

* π = Projection
* σ = Selection
* γ = Grouping and aggregation
* ⟕ = Join operation
* COUNT(DISTINCT ...) = Distinct aggregation

=== Interpretation ===

The expression combines venue, booking, wedding, event, and attendance relations in order to calculate venue occupancy metrics.

Aggregation is applied after filtering attendance data, enabling utilization analysis for each wedding event.

== 2.5 PostgreSQL Stored Procedure ==

=== SQL Code ===

{{{
#!sql
CREATE OR REPLACE PROCEDURE
venue_capacity_utilization_report(

    IN p_venue_id INT DEFAULT NULL,

    IN p_min_occupancy_percent NUMERIC DEFAULT 0,

    IN p_max_occupancy_percent NUMERIC DEFAULT 100
)

LANGUAGE plpgsql

AS $$

DECLARE

    v_record RECORD;

    v_confirmed_count INTEGER;

    v_actual_count INTEGER;

    v_occupancy_rate NUMERIC;

    v_utilization_category VARCHAR;

    v_capacity INTEGER;

BEGIN

    CREATE TEMP TABLE capacity_utilization_results (

        venue_id INTEGER,

        venue_name VARCHAR,

        venue_capacity INTEGER,

        wedding_id INTEGER,

        organizer_name VARCHAR,

        wedding_date DATE,

        confirmed_attendees INTEGER,

        actual_attendance INTEGER,

        available_seats INTEGER,

        occupancy_rate_percent NUMERIC,

        utilization_category VARCHAR,

        booking_status VARCHAR,

        booking_date DATE
    );

    FOR v_record IN

        SELECT DISTINCT
            v.venue_id,
            v.name,
            v.capacity,
            w.wedding_id,
            u.first_name,
            u.last_name,
            w.date,
            vb.status,
            vb.date

        FROM venue v

        INNER JOIN venue_booking vb
            ON v.venue_id = vb.venue_id

        INNER JOIN wedding w
            ON vb.wedding_id = w.wedding_id

        INNER JOIN "user" u
            ON w.user_id = u.user_id

        WHERE
            (
                p_venue_id IS NULL
                OR v.venue_id = p_venue_id
            )

    LOOP

        SELECT COUNT(DISTINCT a.guest_id)

        INTO v_confirmed_count

        FROM event e

        LEFT JOIN attendance a
            ON e.event_id = a.event_id
            AND a.status = 'CONFIRMED'

        WHERE e.wedding_id = v_record.wedding_id;

        SELECT COUNT(DISTINCT a.guest_id)

        INTO v_actual_count

        FROM event e

        LEFT JOIN attendance a
            ON e.event_id = a.event_id
            AND a.status = 'ATTENDED'

        WHERE e.wedding_id = v_record.wedding_id;

        v_confirmed_count :=
            COALESCE(v_confirmed_count, 0);

        v_actual_count :=
            COALESCE(v_actual_count, 0);

        v_capacity := v_record.capacity;

        IF v_capacity > 0 THEN

            v_occupancy_rate :=
                ROUND(
                    (
                        CAST(v_confirmed_count AS NUMERIC)
                        / v_capacity
                    ) * 100,
                    2
                );

        ELSE

            v_occupancy_rate := 0;

        END IF;

        IF v_confirmed_count > v_capacity THEN

            v_utilization_category := 'EXCEEDED';

        ELSIF v_occupancy_rate >= 90 THEN

            v_utilization_category := 'HIGH';

        ELSIF v_occupancy_rate >= 60 THEN

            v_utilization_category := 'MODERATE';

        ELSE

            v_utilization_category := 'LOW';

        END IF;

        IF v_occupancy_rate BETWEEN
            p_min_occupancy_percent
            AND
            p_max_occupancy_percent

        THEN

            INSERT INTO capacity_utilization_results

            VALUES (

                v_record.venue_id,

                v_record.name,

                v_record.capacity,

                v_record.wedding_id,

                v_record.first_name || ' ' ||
                v_record.last_name,

                v_record.date,

                v_confirmed_count,

                v_actual_count,

                v_capacity - v_confirmed_count,

                v_occupancy_rate,

                v_utilization_category,

                v_record.status,

                v_record.date
            );

        END IF;

    END LOOP;

    RAISE NOTICE
        'Venue Capacity Utilization Report Generated - % rows processed',

        (
            SELECT COUNT(*)
            FROM capacity_utilization_results
        );

END;
$$;
}}}

== 2.6 Procedure Characteristics ==

* Input Parameters:
  * venue ID
  * minimum occupancy percentage
  * maximum occupancy percentage

* Attendance Analysis:
  * confirmed attendance counting
  * actual attendance counting

* Occupancy Classification:
  * LOW
  * MODERATE
  * HIGH
  * EXCEEDED

* NULL Handling:
  * COALESCE() usage

* Filtering Logic:
  * occupancy percentage range filtering

== 2.7 Proof of Execution with Sample Data ==

=== Sample Data Insertion ===

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

INSERT INTO wedding (
    date,
    budget,
    user_id
)
VALUES (
    '2024-07-20',
    12000.00,
    2
);

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

=== Query Execution Result ===

{{{
 venue_id | venue_name    | venue_capacity
----------+---------------+----------------
 2        | Golden Palace | 150
}}}

=== Calculation Verification ===

* Confirmed Attendees:
  * guests marked as CONFIRMED

* Actual Attendance:
  * guests marked as ATTENDED

* Available Seats:
  * venue capacity minus confirmed attendees

* Occupancy Rate:
  * percentage of occupied seats

* Utilization Category:
  * calculated based on occupancy thresholds

== Summary ==

This scenario demonstrates operational venue analysis using:
* multi-table joins
* attendance aggregation
* occupancy calculations
* utilization categorization
* PostgreSQL stored procedures
* analytical reporting techniques