= P3 – Application Scenarios for Database Access (SQL)

== Description
This phase demonstrates typical application scenarios for accessing the Wedding Planner database using SQL queries.
The scenarios reflect real tasks such as creating weddings, managing events and guests, tracking attendance, and handling venue bookings.

== Assumptions
 * Primary keys are generated by the system (or assigned manually for the examples).
 * table_number in Attendance is optional because not all events require seating arrangements.
 * Status values are stored as text (e.g., 'planned', 'confirmed', 'cancelled', 'attending').

== Scenario 1: Register a new user
Create a new user who will manage weddings in the system.

{{{
INSERT INTO User (user_id, first_name, last_name, email, phone_number, gender, birthday)
VALUES (10, 'Jana', 'Trpkovska', 'jana@mail.com', '070123456', 'F', '2002-05-10');
}}}

== Scenario 2: Create a wedding for a user
A user creates a wedding record.

{{{
INSERT INTO Wedding (wedding_id, date, budget, notes, user_id)
VALUES (1, '2026-06-20', 15000, 'Summer wedding', 10);
}}}

== Scenario 3: Add events for a wedding
Create wedding-related events such as ceremony and reception.

{{{
INSERT INTO Event (event_id, event_type, date, start_time, end_time, status, wedding_id)
VALUES
(1, 'Ceremony', '2026-06-20', '12:00:00', '13:00:00', 'planned', 1),
(2, 'Reception', '2026-06-20', '15:00:00', '21:00:00', 'planned', 1);
}}}

== Scenario 4: Add guests to a wedding
Add invited guests to the wedding.

{{{
INSERT INTO Guest (guest_id, first_name, last_name, email, wedding_id)
VALUES
(101, 'Ana', 'Petrova', 'ana.p@mail.com', 1),
(102, 'Marko', 'Iliev', 'marko.i@mail.com', 1),
(103, 'Elena', 'Stojanova', NULL, 1);
}}}

== Scenario 5: Record attendance for an event
Track guest attendance for a specific event (e.g., reception). table_number is optional.

{{{
INSERT INTO Attendance (attendance_id, status, role, table_number, guest_id, event_id)
VALUES
(1, 'attending', 'guest', 5, 101, 2),
(2, 'attending', 'best_man', 1, 102, 2),
(3, 'pending', 'guest', NULL, 103, 2);
}}}

== Scenario 6: List all weddings for a specific user
Retrieve weddings managed by a given user.

{{{
SELECT w.wedding_id, w.date, w.budget, w.notes
FROM Wedding w
WHERE w.user_id = 10
ORDER BY w.date;
}}}

== Scenario 7: List all events for a wedding
Show all events for a selected wedding.

{{{
SELECT e.event_id, e.event_type, e.date, e.start_time, e.end_time, e.status
FROM Event e
WHERE e.wedding_id = 1
ORDER BY e.date, e.start_time;
}}}

== Scenario 8: Show guest list for a wedding
Retrieve all guests invited to a wedding.

{{{
SELECT g.guest_id, g.first_name, g.last_name, g.email
FROM Guest g
WHERE g.wedding_id = 1
ORDER BY g.last_name, g.first_name;
}}}

== Scenario 9: Attendance overview for an event (JOIN)
List guests with their attendance status and seating (if available) for a specific event.

{{{
SELECT g.first_name, g.last_name, a.status, a.role, a.table_number
FROM Attendance a
JOIN Guest g ON a.guest_id = g.guest_id
WHERE a.event_id = 2
ORDER BY a.role, g.last_name;
}}}

== Scenario 10: Update an event status
Example: the ceremony is confirmed.

{{{
UPDATE Event
SET status = 'confirmed'
WHERE event_id = 1;
}}}

== Scenario 11: Add venue types and venues
Define venue types and store available venues.

{{{
INSERT INTO Venue_Type (type_id, type_name)
VALUES
(1, 'Restaurant'),
(2, 'Hotel'),
(3, 'Event Hall');
}}}

{{{
INSERT INTO Venue (venue_id, name, location, city, address, capacity, menu, phone_number, price_per_guest)
VALUES
(1, 'Royal Hall', 'Center', 'Skopje', 'Main Street 10', 250, 'Standard Menu', '021234567', 25.00),
(2, 'Lakeview Hotel', 'Lake Area', 'Ohrid', 'Lake Road 5', 180, NULL, '046111222', 30.00);
}}}

== Scenario 12: Book a venue for a wedding (Venue_booking)
Create a booking record for a specific venue and time interval.

{{{
INSERT INTO Venue_booking (booking_id, date, start_time, end_time, status, price, wedding_id, venue_id)
VALUES (1, '2026-06-20', '15:00:00', '21:00:00', 'confirmed', 4500.00, 1, 1);
}}}

== Scenario 13: Venue booking overview for a wedding (JOIN)
Show booking details including venue name.

{{{
SELECT v.name, vb.date, vb.start_time, vb.end_time, vb.status, vb.price
FROM Venue_booking vb
JOIN Venue v ON vb.venue_id = v.venue_id
WHERE vb.wedding_id = 1
ORDER BY vb.date, vb.start_time;
}}}

== Scenario 14: Count guests per wedding (aggregate query)
Useful for planning and estimating costs.

{{{
SELECT w.wedding_id, COUNT(g.guest_id) AS total_guests
FROM Wedding w
LEFT JOIN Guest g ON w.wedding_id = g.wedding_id
GROUP BY w.wedding_id
ORDER BY w.wedding_id;
}}}

== Scenario 15: Estimated venue cost based on guest count
Estimate total cost = price_per_guest * number_of_guests (for a selected venue and wedding).

{{{
SELECT w.wedding_id,
       v.name AS venue_name,
       v.price_per_guest,
       COUNT(g.guest_id) AS guest_count,
       (v.price_per_guest * COUNT(g.guest_id)) AS estimated_total_cost
FROM Wedding w
JOIN Guest g ON w.wedding_id = g.wedding_id
JOIN Venue v ON v.venue_id = 1
WHERE w.wedding_id = 1
GROUP BY w.wedding_id, v.name, v.price_per_guest;
}}}

== Conclusion
These SQL scenarios demonstrate how the database supports the main wedding planning operations:
user and wedding management, event scheduling, guest tracking, attendance recording, and venue bookings.