UseCase11 - Leave a VetClinic review

Initiating actor: Client

Description

A logged-in client can submit a review about a vet clinic in the system. A client can review a vet clinic only if the client has at least one completed (DONE) appointment at that clinic for at least one of their pets(he is the current owner).

Scenario

1. Client opens the clinics page.

   SELECT clinic_id, name, city, address
   FROM vet_clinics
   WHERE name = 'Happy Paws Clinic';
   

2. Clicks the "Leave review" button.
3. Client enters rating, comment and submits.

   BEGIN;
   
   WITH new_review AS (
     INSERT INTO reviews (reviewer_id, rating, comment, created_at)
     VALUES (
       (SELECT user_id FROM users WHERE username = 'client.viktor'),
       5,
       'Excellent clinic, very professional staff.',
       NOW()
     )
     RETURNING review_id
   )
   INSERT INTO clinic_reviews (review_id, target_clinic_id)
   SELECT
     nr.review_id,
     (SELECT clinic_id FROM vet_clinics WHERE name = 'Happy Paws Clinic')
   FROM new_review nr;
   
   COMMIT;