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.

3 files changed, 71 insertions, 1 deletions

diff --git a/postgres/init.sql b/postgres/init.sql
index 740063e7..ae86fca0 100644
--- a/postgres/init.sql
+++ b/postgres/init.sql
@@ -13,12 +13,28 @@ INSERT INTO users VALUES (
     current_timestamp
 );
 
+CREATE TABLE channels (
+    id varchar,
+    name varchar,
+    primary key(id)
+);
+
+INSERT INTO channels VALUES(
+    '267659945086812160',
+    'python-general'
+);
+
+INSERT INTO channels VALUES(
+    '1234',
+    'zebra'
+);
+
 CREATE TABLE messages (
     id varchar,
     author_id varchar references users(id),
     is_deleted boolean,
     created_at timestamp,
-    channel_id varchar,
+    channel_id varchar references channels(id),
     primary key(id)
 );
 
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)