UseCase02- Manages Clients

Initiating actor: Admin

Description

The Admin moderates Petify by blocking a client account when the client posts disrespectful/abusive reviews, or when the client receives extremely bad reviews that indicate harmful or unacceptable behavior. The Admin reviews evidence (review comments, ratings) and then blocks the client. The system records the decision (who/when/why), marks the user as blocked, and creates a notification for the blocked user.

Scenario

1. Admin goes to the reviews page that are grouped by client.

   SELECT
     u.user_id,
     u.username,
     u.email,
     u.is_blocked,
     u.blocked_reason,
     u.blocked_at,
     u.blocked_by,
   
     COUNT(ur.review_id) AS received_review_count,
     COALESCE(AVG(r.rating), 0)::numeric(10,2) AS avg_received_rating,
   
     (SELECT COUNT(*)
      FROM reviews rw
      WHERE rw.reviewer_id = u.user_id) AS written_review_count
   
   FROM users u
   JOIN clients c ON c.user_id = u.user_id
   LEFT JOIN user_reviews ur ON ur.target_user_id = u.user_id
   LEFT JOIN reviews r ON r.review_id = ur.review_id
   GROUP BY
     u.user_id, u.username, u.email,
     u.is_blocked, u.blocked_reason, u.blocked_at, u.blocked_by
   ORDER BY
     (COUNT(ur.review_id) = 0) ASC,
     avg_received_rating ASC,
     received_review_count DESC,
     u.username;
   
   

2. Admin clicks on a client so he can see all the reviews for them.

   SELECT
     r.review_id,
     r.reviewer_id,
     ru.username AS reviewer_username,
     r.rating,
     r.comment,
     r.created_at
   FROM user_reviews ur
   JOIN reviews r ON r.review_id = ur.review_id
   JOIN users ru ON ru.user_id = r.reviewer_id
   WHERE ur.target_user_id = $1        
   ORDER BY r.created_at DESC;
   

3. Admin clicks "Block client" and enters the reason.

   BEGIN;
   
   SELECT u.user_id, u.email, u.is_blocked
   FROM users u
   JOIN clients c ON c.user_id = u.user_id
   WHERE u.user_id = $1
   FOR UPDATE;
   
   UPDATE users
   SET
     is_blocked = TRUE,
     blocked_reason = $2,       
     blocked_by     = $4,     
     blocked_at     = NOW()
   WHERE user_id = $1;
   
   INSERT INTO notifications (user_id, type, message, is_read, created_at)
   VALUES (
     $1,
     'ACCOUNT_BLOCKED',
     CONCAT('Your account has been blocked. Reason: ', $2, '. ', COALESCE($3, '')),
     FALSE,
     NOW()
   );
   
   COMMIT;
   

4. The system sends and email to the client.

   SELECT
     email,
     username,
     blocked_reason,
     blocked_at
   FROM users
   WHERE user_id = $1;