Resolve conflicts

1 files changed, 51 insertions, 2 deletions

diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py
index 25b42fa2..cae630f1 100644
--- a/pydis_site/apps/api/models/bot/metricity.py
+++ b/pydis_site/apps/api/models/bot/metricity.py
@@ -1,5 +1,12 @@
 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."""
@@ -21,7 +28,8 @@ class Metricity:
 
     def user(self, user_id: str) -> dict:
         """Query a user's data."""
-        columns = ["verified_at"]
+        # TODO: Swap this back to some sort of verified at date
+        columns = ["joined_at"]
         query = f"SELECT {','.join(columns)} FROM users WHERE id = '%s'"
         self.cursor.execute(query, [user_id])
         values = self.cursor.fetchone()
@@ -33,7 +41,48 @@ 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'", [user_id])
+        self.cursor.execute(
+            """
+            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()
+
+        if not values:
+            raise NotFound()
+
+        return values[0]
+
+    def total_message_blocks(self, user_id: str) -> int:
+        """
+        Query number of 10 minute blocks during which the user has been active.
+
+        This metric prevents users from spamming to achieve the message total threshold.
+        """
+        self.cursor.execute(
+            """
+            SELECT
+                COUNT(*)
+            FROM (
+                SELECT
+                    (floor((extract('epoch' from created_at) / %s )) * %s) AS interval
+                FROM messages
+                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, EXCLUDE_CHANNELS]
+        )
         values = self.cursor.fetchone()
 
         if not values: