Added metricity db connection and user bot API

4 files changed, 85 insertions, 1 deletions

diff --git a/docker-compose.yml b/docker-compose.yml
index 73d2ff85..7287d8d5 100644
--- a/docker-compose.yml
+++ b/docker-compose.yml
@@ -18,6 +18,8 @@ services:
       POSTGRES_DB: pysite
       POSTGRES_PASSWORD: pysite
       POSTGRES_USER: pysite
+    volumes:
+      - ./postgres/init.sql:/docker-entrypoint-initdb.d/init.sql
 
   web:
     build:
@@ -40,6 +42,7 @@ services:
       - staticfiles:/var/www/static
     environment:
       DATABASE_URL: postgres://pysite:pysite@postgres:5432/pysite
+      METRICITY_DB_URL: postgres://pysite:pysite@postgres:5432/metricity
       SECRET_KEY: suitable-for-development-only
       STATIC_ROOT: /var/www/static
 
diff --git a/postgres/init.sql b/postgres/init.sql
new file mode 100644
index 00000000..fd29ddbc
--- /dev/null
+++ b/postgres/init.sql
@@ -0,0 +1,34 @@
+CREATE DATABASE metricity;
+
+\c metricity;
+
+CREATE TABLE users (
+    id varchar(255),
+    name varchar(255) not null,
+    avatar_hash varchar(255),
+    joined_at timestamp not null,
+    created_at timestamp not null,
+    is_staff boolean not null,
+    opt_out boolean default false,
+    bot boolean default false,
+    is_guild boolean default true,
+    is_verified boolean default false,
+    public_flags text default '{}',
+    verified_at timestamp,
+    primary key(id)
+);
+
+INSERT INTO users VALUES (
+    0,
+    'foo',
+    'bar',
+    current_timestamp,
+    current_timestamp,
+    false,
+    false,
+    false,
+    true,
+    false,
+    '{}',
+    NULL
+);
diff --git a/pydis_site/apps/api/viewsets/bot/user.py b/pydis_site/apps/api/viewsets/bot/user.py
index 9571b3d7..0eeacbb3 100644
--- a/pydis_site/apps/api/viewsets/bot/user.py
+++ b/pydis_site/apps/api/viewsets/bot/user.py
@@ -1,3 +1,10 @@
+import json
+
+from django.db import connections
+from rest_framework import status
+from rest_framework.decorators import action
+from rest_framework.request import Request
+from rest_framework.response import Response
 from rest_framework.viewsets import ModelViewSet
 from rest_framework_bulk import BulkCreateModelMixin
 
@@ -53,6 +60,29 @@ class UserViewSet(BulkCreateModelMixin, ModelViewSet):
     - 200: returned on success
     - 404: if a user with the given `snowflake` could not be found
 
+    ### GET /bot/users/<snowflake:int>/metricity_data
+    Gets metricity data for a single user by ID.
+
+    #### Response format
+    >>> {
+    ...    "id": "0",
+    ...    "name": "foo",
+    ...    "avatar_hash": "bar",
+    ...    "joined_at": "2020-10-06T18:17:30.101677",
+    ...    "created_at": "2020-10-06T18:17:30.101677",
+    ...    "is_staff": False,
+    ...    "opt_out": False,
+    ...    "bot": False,
+    ...    "is_guild": True,
+    ...    "is_verified": False,
+    ...    "public_flags": {},
+    ...    "verified_at": null
+    ...}
+
+    #### Status codes
+    - 200: returned on success
+    - 404: if a user with the given `snowflake` could not be found
+
     ### POST /bot/users
     Adds a single or multiple new users.
     The roles attached to the user(s) must be roles known by the site.
@@ -115,7 +145,23 @@ class UserViewSet(BulkCreateModelMixin, ModelViewSet):
     #### Status codes
     - 204: returned on success
     - 404: if a user with the given `snowflake` does not exist
+
+
     """
 
     serializer_class = UserSerializer
     queryset = User.objects
+
+    @action(detail=True)
+    def metricity_data(self, request: Request, pk: str = None) -> Response:
+        """Request handler for metricity_data endpoint."""
+        user = self.get_object()
+        column_keys = ["id", "name", "avatar_hash", "joined_at", "created_at", "is_staff",
+                       "opt_out", "bot", "is_guild", "is_verified", "public_flags", "verified_at"]
+        with connections['metricity'].cursor() as cursor:
+            query = f"SELECT {','.join(column_keys)} FROM users WHERE id = '%s'"
+            cursor.execute(query, [user.id])
+            values = cursor.fetchone()
+            data = dict(zip(column_keys, values))
+            data["public_flags"] = json.loads(data["public_flags"])
+            return Response(data, status=status.HTTP_200_OK)
diff --git a/pydis_site/settings.py b/pydis_site/settings.py
index 3769fa25..2e78e458 100644
--- a/pydis_site/settings.py
+++ b/pydis_site/settings.py
@@ -172,7 +172,8 @@ WSGI_APPLICATION = 'pydis_site.wsgi.application'
 # https://docs.djangoproject.com/en/2.1/ref/settings/#databases
 
 DATABASES = {
-    'default': env.db()
+    'default': env.db(),
+    'metricity': env.db('METRICITY_DB_URL'),
 }
 
 # Password validation