Changes between Version 2 and Version 3 of UseCase04

Timestamp:

05/17/26 23:28:59 (8 days ago)

Author:

181201

Comment:

--

UseCase04

@@ -6 +6 @@
 
 '''Description:'''
-A Pet Owner finds a Pet Sitter they like and requests a booking. The system creates the booking and links the selected pets using a secure database transaction.
+A Pet Owner finds a Pet Sitter they like and requests a booking. They must select the specific service they want. The system creates the booking and links the requested services using a secure database transaction.
 
 '''Scenario:'''
- 1. Pet Owner views the profile of a sitter and clicks "Book Now".
- 2. Owner fills out the dates, location, selects the pets, and submits.
- 3. System creates the booking with a 'Pending' status:
+ 1. Pet Owner views the profile of a sitter (e.g., `sitter_filip`) and clicks "Book Now".
+ 2. System provides the specific services that this sitter offers to display in the booking form:
 {{{
 #!sql
-INSERT INTO bookings (status, date_from, date_to, address, owner_id, sitter_id)
-VALUES (
-    'Pending', '2026-06-10', '2026-06-12', 'ul. Partizanska br. 10',
-    (SELECT user_id FROM users WHERE username = 'owner_bojan'),
-    (SELECT user_id FROM users WHERE username = 'sitter_filip')
-);
+SELECT s.service_id, s.type, s.description 
+FROM services s
+JOIN sitter_services ss ON s.service_id = ss.service_id
+WHERE ss.sitter_id = (SELECT user_id FROM users WHERE username = 'sitter_filip');
 }}}
+ 3. Owner fills out the dates, location, selects the pets, and submits.
+ 4. System creates the booking with a 'Pending' status and attaches the requested service simultaneously:
+{{{
+#!sql
+BEGIN;
+
+WITH new_booking AS (
+    INSERT INTO bookings (status, date_from, date_to, address, owner_id, sitter_id)
+    VALUES (
+        'Pending', '2026-06-10', '2026-06-12', 'ul. Partizanska br. 10',
+        (SELECT user_id FROM users WHERE username = 'owner_bojan'),
+        (SELECT user_id FROM users WHERE username = 'sitter_filip')
+    )
+    RETURNING booking_id
+)
+INSERT INTO booking_services (booking_id, service_id)
+SELECT nb.booking_id, (SELECT service_id FROM services WHERE type = 'Dog Walking')
+FROM new_booking nb;
+
+COMMIT;
+}}}