diff options
| -rw-r--r-- | postgres/init.sql | 18 | ||||
| -rw-r--r-- | pydis_site/apps/api/models/bot/metricity.py | 39 | ||||
| -rw-r--r-- | pydis_site/apps/api/viewsets/bot/user.py | 15 |
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) |