UseCase07 - Create an Appointment

Initiating actor: Owner

Description

An owner schedules an appointment for one of their pets at a vet clinic they choose. The owner selects one of their animals, selects a clinic and submits the appointment request. The system then confirms the appointment has been created.

Scenario

1. Owner opens a specific vet clinic.

   SELECT
     clinic_id,
     name,
     city,
     address,
     email,
     phone
   FROM vet_clinics
   WHERE clinic_id = (SELECT clinic_id FROM vet_clinics WHERE name = 'Happy Paws Clinic');
   

2. Owner clicks "Create an appointment"(In the form there are all the animals of that owner, so he can choose witch animal is the appointment for).

   SELECT
     a.animal_id,
     a.name,
     a.species,
     a.breed
   FROM animals a
   WHERE a.owner_id = (SELECT user_id FROM users WHERE username = 'client.mila')
   ORDER BY a.name;
   

3. Owner fills in the form.
4. Owner submits the form.

   BEGIN;
   SELECT 1
   FROM appointments ap
   WHERE ap.clinic_id = (SELECT clinic_id FROM vet_clinics WHERE name = 'Happy Paws Clinic')
     AND ap.date_time = (NOW() + INTERVAL '3 days')
     AND ap.status = 'CONFIRMED'
   FOR UPDATE;
   
   INSERT INTO appointments (
     clinic_id,
     animal_id,
     responsible_owner_id,
     status,
     date_time,
     notes
   )
   VALUES (
     (SELECT clinic_id FROM vet_clinics WHERE name = 'Happy Paws Clinic'),
     (SELECT animal_id
      FROM animals
      WHERE name = 'Max'
        AND owner_id = (SELECT user_id FROM users WHERE username = 'client.mila')
      LIMIT 1),
     (SELECT user_id FROM users WHERE username = 'client.mila'),
     'CONFIRMED',
     (NOW() + INTERVAL '3 days'),
     'Regular check-up'
   )
   RETURNING appointment_id;
   
   COMMIT;