Add route to get a member's data for helper review

Added route for getting a user's join date, total messages, and top 3 channels by activity.

This information will be used to auto-review nominees.

2 files changed, 54 insertions, 0 deletions

diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py
index cae630f1..af5e1f3b 100644
--- a/pydis_site/apps/api/models/bot/metricity.py
+++ b/pydis_site/apps/api/models/bot/metricity.py
@@ -89,3 +89,42 @@ class Metricity:
             raise NotFound()
 
         return values[0]
+
+    def top_channel_activity(self, user_id: str) -> int:
+        """
+        Query the top three channels in which the user is most active.
+
+        Help channels are grouped under "the help channels",
+        and off-topic channels are grouped under "off-topic".
+        """
+        self.cursor.execute(
+            """
+            SELECT
+                CASE
+                    WHEN channels.name ILIKE 'help-%%' THEN 'the help channels'
+                    WHEN channels.name ILIKE 'ot%%' THEN 'off-topic'
+                    ELSE channels.name
+                END,
+                COUNT(1)
+            FROM
+                messages
+                LEFT JOIN channels ON channels.id = messages.channel_id
+            WHERE
+                author_id = '%s'
+            GROUP BY
+                1
+            ORDER BY
+                2 DESC
+            LIMIT
+                3;
+            """,
+            [user_id]
+        )
+
+        values = self.cursor.fetchall()
+        print(values)
+
+        if not values:
+            raise NotFound()
+
+        return values
diff --git a/pydis_site/apps/api/viewsets/bot/user.py b/pydis_site/apps/api/viewsets/bot/user.py
index 829e2694..5e1f8775 100644
--- a/pydis_site/apps/api/viewsets/bot/user.py
+++ b/pydis_site/apps/api/viewsets/bot/user.py
@@ -262,3 +262,18 @@ class UserViewSet(ModelViewSet):
             except NotFound:
                 return Response(dict(detail="User not found in metricity"),
                                 status=status.HTTP_404_NOT_FOUND)
+
+    @action(detail=True)
+    def metricity_review_data(self, request: Request, pk: str = None) -> Response:
+        """Request handler for metricity_review_data endpoint."""
+        user = self.get_object()
+
+        with Metricity() as metricity:
+            try:
+                data = metricity.user(user.id)
+                data["total_messages"] = metricity.total_messages(user.id)
+                data["top_channel_activity"] = metricity.top_channel_activity(user.id)
+                return Response(data, status=status.HTTP_200_OK)
+            except NotFound:
+                return Response(dict(detail="User not found in metricity"),
+                                status=status.HTTP_404_NOT_FOUND)