Changes between Initial Version and Version 1 of UseCase02

Timestamp:

01/23/26 21:37:15 (2 weeks ago)

Author:

231035

Comment:

--

UseCase02

@@ +1 @@
+= 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;
+}}}
+
+
+