= Testing and Validation =

== Description ==

This section contains SQL scenarios used for testing trigger behavior and advanced database validation.

The tests validate booking conflict prevention, RSVP consistency validation, and availability checking functionality implemented during the advanced database development phase.

The overlap validation tests explicitly cover:
* partial overlap at the beginning
* partial overlap at the end
* complete overlap
* fully contained intervals
* non-overlapping intervals before existing bookings
* non-overlapping intervals after existing bookings

== 1. Venue Booking Conflict Test ==

=== SQL Code ===

{{{
#!sql
INSERT INTO venue_booking(
    "date",
    start_time,
    end_time,
    status,
    price,
    venue_id,
    wedding_id
)
VALUES (
    '2026-06-20',
    '18:00',
    '22:00',
    'confirmed',
    5000,
    1,
    1
);
}}}

=== Expected Result ===

Creates the initial booking interval used for overlap testing.

== 2. Venue Overlap Validation Cases ==

=== SQL Code ===

{{{
#!sql
-- Overlap case 1
SELECT is_venue_available(
    1,
    '2026-06-20',
    '19:00',
    '23:00'
);

-- Overlap case 2
SELECT is_venue_available(
    1,
    '2026-06-20',
    '16:00',
    '19:00'
);

-- Overlap case 3
SELECT is_venue_available(
    1,
    '2026-06-20',
    '17:00',
    '23:00'
);

-- Overlap case 4
SELECT is_venue_available(
    1,
    '2026-06-20',
    '19:00',
    '21:00'
);

-- Non-overlap case 1
SELECT is_venue_available(
    1,
    '2026-06-20',
    '14:00',
    '17:00'
);

-- Non-overlap case 2
SELECT is_venue_available(
    1,
    '2026-06-20',
    '22:00',
    '23:00'
);
}}}

=== Expected Result ===

The first four cases return FALSE because the intervals overlap with the existing booking.

The last two cases return TRUE because the intervals do not overlap.

== 3. RSVP Validation Test ==

=== SQL Code ===

{{{
#!sql
INSERT INTO attendance(
    status,
    table_number,
    role,
    guest_id,
    event_id
)
VALUES (
    'attending',
    3,
    'Guest',
    1,
    1
);
}}}

=== Expected Result ===

Guests who declined RSVP cannot be marked as attending.

== 4. Photographer Availability Function Test ==

=== SQL Code ===

{{{
#!sql
SELECT is_photographer_available(
    1,
    '2026-06-20',
    '18:00',
    '22:00'
);
}}}

=== Expected Result ===

Returns FALSE if the photographer already has a conflicting booking.

== 5. Band Availability Function Test ==

=== SQL Code ===

{{{
#!sql
SELECT is_band_available(
    1,
    '2026-06-20',
    '18:00',
    '22:00'
);
}}}

=== Expected Result ===

Returns FALSE if the band already has a conflicting booking.

== 6. Photographer Overlap Validation Test ==

=== SQL Code ===

{{{
#!sql
SELECT is_photographer_available(
    1,
    '2026-06-20',
    '19:00',
    '21:00'
);
}}}

=== Expected Result ===

Returns FALSE because the requested interval overlaps with an existing photographer booking.

== 7. Band Overlap Validation Test ==

=== SQL Code ===

{{{
#!sql
SELECT is_band_available(
    1,
    '2026-06-20',
    '17:00',
    '23:00'
);
}}}

=== Expected Result ===

Returns FALSE because the requested interval overlaps with an existing band booking.

== 8. Wedding Financial Summary View Test ==

=== SQL Code ===

{{{
#!sql
SELECT *
FROM vw_wedding_financial_summary;
}}}

=== Expected Result ===

Displays wedding budget information, total booking expenses, and remaining budget calculations.

== 9. RSVP Overview View Test ==

=== SQL Code ===

{{{
#!sql
SELECT *
FROM vw_rsvp_overview;
}}}

=== Expected Result ===

Displays grouped RSVP statistics for guests and events.

== 10. Vendor Booking Overview View Test ==

=== SQL Code ===

{{{
#!sql
SELECT *
FROM vw_vendor_booking_overview;
}}}

=== Expected Result ===

Displays venue, photographer, and band booking information for weddings.

== 11. Upcoming Weddings View Test ==

=== SQL Code ===

{{{
#!sql
SELECT *
FROM vw_upcoming_weddings;
}}}

=== Expected Result ===

Displays weddings scheduled for future dates only.

== 12. RSVP Summary Procedure Test ==

=== SQL Code ===

{{{
#!sql
CALL generate_rsvp_summary(1);
}}}

=== Expected Result ===

Displays NOTICE messages containing accepted, declined, and pending RSVP counts for the selected event.

== 13. Constraint Validation Test ==

=== SQL Code ===

{{{
#!sql
INSERT INTO event_rsvp(
    guest_id,
    event_id,
    status
)
VALUES (
    1,
    1,
    'invalid_status'
);
}}}

=== Expected Result ===

The CHECK constraint rejects invalid RSVP status values.

== Summary ==

The testing scenarios confirm that the implemented trigger functions, SQL functions, views, and constraints operate correctly and enforce the required business rules inside the PostgreSQL database layer.

The validation process demonstrates successful overlap prevention, integrity enforcement, analytical reporting, and advanced interval validation functionality for the Wedding Planner Management System.