From c9c4b5f7b886482680d3b27e6a7b305b3bc42fc8 Mon Sep 17 00:00:00 2001
From: Joe Banks <joseph@josephbanks.me>
Date: Wed, 28 Oct 2020 20:53:03 +0000
Subject: Add new column to Postgres init

---
 postgres/init.sql | 9 ++++++---
 1 file changed, 6 insertions(+), 3 deletions(-)

(limited to 'postgres/init.sql')

diff --git a/postgres/init.sql b/postgres/init.sql
index 922ce1ad..75a9154c 100644
--- a/postgres/init.sql
+++ b/postgres/init.sql
@@ -16,15 +16,18 @@ INSERT INTO users VALUES (
 CREATE TABLE messages (
     id varchar,
     author_id varchar references users(id),
-    primary key(id)
+    primary key(id),
+    is_deleted boolean
 );
 
 INSERT INTO messages VALUES(
     0,
-    0
+    0,
+    false
 );
 
 INSERT INTO messages VALUES(
     1,
-    0
+    0,
+    false
 );
-- 
cgit v1.2.3


From 6804f4f0f19d7c0047870b7da4b79ab099140b98 Mon Sep 17 00:00:00 2001
From: Joe Banks <joseph@josephbanks.me>
Date: Sun, 1 Nov 2020 18:41:51 +0000
Subject: Add created_at to metricity database bootstrap

---
 postgres/init.sql | 11 +++++++----
 1 file changed, 7 insertions(+), 4 deletions(-)

(limited to 'postgres/init.sql')

diff --git a/postgres/init.sql b/postgres/init.sql
index 75a9154c..40538492 100644
--- a/postgres/init.sql
+++ b/postgres/init.sql
@@ -16,18 +16,21 @@ INSERT INTO users VALUES (
 CREATE TABLE messages (
     id varchar,
     author_id varchar references users(id),
-    primary key(id),
-    is_deleted boolean
+    is_deleted boolean,
+    created_at timestamp,
+    primary key(id)
 );
 
 INSERT INTO messages VALUES(
     0,
     0,
-    false
+    false,
+    now()
 );
 
 INSERT INTO messages VALUES(
     1,
     0,
-    false
+    false,
+    now() + INTERVAL '10 minutes'
 );
-- 
cgit v1.2.3


From 23bf022cf8fae66225e829c4a0abbccb2940edb7 Mon Sep 17 00:00:00 2001
From: Joe Banks <joseph@josephbanks.me>
Date: Mon, 2 Nov 2020 18:25:27 +0000
Subject: Exclude bot commands and seasonalbot commands from voice gate

---
 postgres/init.sql                           |  7 +++++--
 pydis_site/apps/api/models/bot/metricity.py | 20 +++++++++++++++++---
 2 files changed, 22 insertions(+), 5 deletions(-)

(limited to 'postgres/init.sql')

diff --git a/postgres/init.sql b/postgres/init.sql
index 40538492..c77fec9e 100644
--- a/postgres/init.sql
+++ b/postgres/init.sql
@@ -18,6 +18,7 @@ CREATE TABLE messages (
     author_id varchar references users(id),
     is_deleted boolean,
     created_at timestamp,
+    channel_id varchar,
     primary key(id)
 );
 
@@ -25,12 +26,14 @@ INSERT INTO messages VALUES(
     0,
     0,
     false,
-    now()
+    now(),
+    '267659945086812160'
 );
 
 INSERT INTO messages VALUES(
     1,
     0,
     false,
-    now() + INTERVAL '10 minutes'
+    now() + INTERVAL '10 minutes,',
+    '1234'
 );
diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py
index 379b0757..29d03d8b 100644
--- a/pydis_site/apps/api/models/bot/metricity.py
+++ b/pydis_site/apps/api/models/bot/metricity.py
@@ -2,6 +2,11 @@ from django.db import connections
 
 BLOCK_INTERVAL = 10 * 60  # 10 minute blocks
 
+EXCLUDE_CHANNELS = [
+    "267659945086812160",  # Bot commands
+    "607247579608121354"  # SeasonalBot commands
+]
+
 
 class NotFound(Exception):
     """Raised when an entity cannot be found."""
@@ -36,8 +41,16 @@ class Metricity:
     def total_messages(self, user_id: str) -> int:
         """Query total number of messages for a user."""
         self.cursor.execute(
-            "SELECT COUNT(*) FROM messages WHERE author_id = '%s' AND NOT is_deleted",
-            [user_id]
+            """
+            SELECT
+              COUNT(*)
+            FROM messages
+            WHERE
+              author_id = '%s'
+              AND NOT is_deleted
+              AND NOT %s::varchar[] @> ARRAY[channel_id]
+            """,
+            [user_id, EXCLUDE_CHANNELS]
         )
         values = self.cursor.fetchone()
 
@@ -63,10 +76,11 @@ class Metricity:
                 WHERE
                     author_id='%s'
                     AND NOT is_deleted
+                    AND NOT %s::varchar[] @> ARRAY[channel_id]
                 GROUP BY interval
             ) block_query;
             """,
-            [BLOCK_INTERVAL, BLOCK_INTERVAL, user_id]
+            [BLOCK_INTERVAL, BLOCK_INTERVAL, user_id, EXCLUDE_CHANNELS]
         )
         values = self.cursor.fetchone()
 
-- 
cgit v1.2.3


From eff717bb38e5b556751ba4f23a53cc6662baa7dd Mon Sep 17 00:00:00 2001
From: Joe Banks <joseph@josephbanks.me>
Date: Sat, 19 Dec 2020 03:43:23 +0000
Subject: Update verified_at fields to joined_at fields

---
 postgres/init.sql                        |  2 +-
 pydis_site/apps/api/tests/test_users.py  | 10 +++++-----
 pydis_site/apps/api/viewsets/bot/user.py |  2 +-
 3 files changed, 7 insertions(+), 7 deletions(-)

(limited to 'postgres/init.sql')

diff --git a/postgres/init.sql b/postgres/init.sql
index c77fec9e..740063e7 100644
--- a/postgres/init.sql
+++ b/postgres/init.sql
@@ -4,7 +4,7 @@ CREATE DATABASE metricity;
 
 CREATE TABLE users (
     id varchar,
-    verified_at timestamp,
+    joined_at timestamp,
     primary key(id)
 );
 
diff --git a/pydis_site/apps/api/tests/test_users.py b/pydis_site/apps/api/tests/test_users.py
index c422f895..69bbfefc 100644
--- a/pydis_site/apps/api/tests/test_users.py
+++ b/pydis_site/apps/api/tests/test_users.py
@@ -407,10 +407,10 @@ class UserMetricityTests(APISubdomainTestCase):
 
     def test_get_metricity_data(self):
         # Given
-        verified_at = "foo"
+        joined_at = "foo"
         total_messages = 1
         total_blocks = 1
-        self.mock_metricity_user(verified_at, total_messages, total_blocks)
+        self.mock_metricity_user(joined_at, total_messages, total_blocks)
 
         # When
         url = reverse('bot:user-metricity-data', args=[0], host='api')
@@ -419,7 +419,7 @@ class UserMetricityTests(APISubdomainTestCase):
         # Then
         self.assertEqual(response.status_code, 200)
         self.assertEqual(response.json(), {
-            "verified_at": verified_at,
+            "joined_at": joined_at,
             "total_messages": total_messages,
             "voice_banned": False,
             "activity_blocks": total_blocks
@@ -455,12 +455,12 @@ class UserMetricityTests(APISubdomainTestCase):
                     self.assertEqual(response.status_code, 200)
                     self.assertEqual(response.json()["voice_banned"], case["voice_banned"])
 
-    def mock_metricity_user(self, verified_at, total_messages, total_blocks):
+    def mock_metricity_user(self, joined_at, total_messages, total_blocks):
         patcher = patch("pydis_site.apps.api.viewsets.bot.user.Metricity")
         self.metricity = patcher.start()
         self.addCleanup(patcher.stop)
         self.metricity = self.metricity.return_value.__enter__.return_value
-        self.metricity.user.return_value = dict(verified_at=verified_at)
+        self.metricity.user.return_value = dict(joined_at=joined_at)
         self.metricity.total_messages.return_value = total_messages
         self.metricity.total_message_blocks.return_value = total_blocks
 
diff --git a/pydis_site/apps/api/viewsets/bot/user.py b/pydis_site/apps/api/viewsets/bot/user.py
index 79f90163..829e2694 100644
--- a/pydis_site/apps/api/viewsets/bot/user.py
+++ b/pydis_site/apps/api/viewsets/bot/user.py
@@ -109,7 +109,7 @@ class UserViewSet(ModelViewSet):
 
     #### Response format
     >>> {
-    ...    "verified_at": "2020-10-06T21:54:23.540766",
+    ...    "joined_at": "2020-10-06T21:54:23.540766",
     ...    "total_messages": 2,
     ...    "voice_banned": False,
     ...    "activity_blocks": 1
-- 
cgit v1.2.3

