Use-case 0004 - Create a Booking

Initiating actor: Pet Owner

Other actors: Pet Sitter

Description: 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 (e.g., sitter_filip) and clicks "Request Booking".
2. System provides the specific services that this sitter offers to display in the booking form:

   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 service, and submits.
4. System creates the booking with a 'PENDING' status and attaches the requested service simultaneously:

   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;