Merge branch main into dewikification

7 files changed, 192 insertions, 71 deletions

diff --git a/pydis_site/apps/api/models/__init__.py b/pydis_site/apps/api/models/__init__.py
index e3f928e1..fd5bf220 100644
--- a/pydis_site/apps/api/models/__init__.py
+++ b/pydis_site/apps/api/models/__init__.py
@@ -8,10 +8,10 @@ from .bot import (
     Message,
     MessageDeletionContext,
     Nomination,
+    NominationEntry,
     OffensiveMessage,
     OffTopicChannelName,
     Reminder,
     Role,
     User
 )
-from .log_entry import LogEntry
diff --git a/pydis_site/apps/api/models/bot/__init__.py b/pydis_site/apps/api/models/bot/__init__.py
index 1673b434..ac864de3 100644
--- a/pydis_site/apps/api/models/bot/__init__.py
+++ b/pydis_site/apps/api/models/bot/__init__.py
@@ -6,7 +6,7 @@ from .documentation_link import DocumentationLink
 from .infraction import Infraction
 from .message import Message
 from .message_deletion_context import MessageDeletionContext
-from .nomination import Nomination
+from .nomination import Nomination, NominationEntry
 from .off_topic_channel_name import OffTopicChannelName
 from .offensive_message import OffensiveMessage
 from .reminder import Reminder
diff --git a/pydis_site/apps/api/models/bot/documentation_link.py b/pydis_site/apps/api/models/bot/documentation_link.py
index 2a0ce751..3dcc71fc 100644
--- a/pydis_site/apps/api/models/bot/documentation_link.py
+++ b/pydis_site/apps/api/models/bot/documentation_link.py
@@ -1,7 +1,20 @@
+from django.core.exceptions import ValidationError
+from django.core.validators import RegexValidator
 from django.db import models
 
 from pydis_site.apps.api.models.mixins import ModelReprMixin
 
+package_name_validator = RegexValidator(
+    regex=r"^[a-z0-9_]+$",
+    message="Package names can only consist of lowercase a-z letters, digits, and underscores."
+)
+
+
+def ends_with_slash_validator(string: str) -> None:
+    """Raise a ValidationError if `string` does not end with a slash."""
+    if not string.endswith("/"):
+        raise ValidationError("The entered URL must end with a slash.")
+
 
 class DocumentationLink(ModelReprMixin, models.Model):
     """A documentation link used by the `!docs` command of the bot."""
@@ -9,13 +22,15 @@ class DocumentationLink(ModelReprMixin, models.Model):
     package = models.CharField(
         primary_key=True,
         max_length=50,
+        validators=(package_name_validator,),
         help_text="The Python package name that this documentation link belongs to."
     )
     base_url = models.URLField(
         help_text=(
             "The base URL from which documentation will be available for this project. "
             "Used to generate links to various symbols within this package."
-        )
+        ),
+        validators=(ends_with_slash_validator,)
     )
     inventory_url = models.URLField(
         help_text="The URL at which the Sphinx inventory is available for this package."
diff --git a/pydis_site/apps/api/models/bot/infraction.py b/pydis_site/apps/api/models/bot/infraction.py
index 7660cbba..60c1e8dd 100644
--- a/pydis_site/apps/api/models/bot/infraction.py
+++ b/pydis_site/apps/api/models/bot/infraction.py
@@ -15,7 +15,8 @@ class Infraction(ModelReprMixin, models.Model):
         ("mute", "Mute"),
         ("kick", "Kick"),
         ("ban", "Ban"),
-        ("superstar", "Superstar")
+        ("superstar", "Superstar"),
+        ("voice_ban", "Voice Ban"),
     )
     inserted_at = models.DateTimeField(
         default=timezone.now,
diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py
new file mode 100644
index 00000000..5daa5c66
--- /dev/null
+++ b/pydis_site/apps/api/models/bot/metricity.py
@@ -0,0 +1,132 @@
+from typing import List, Tuple
+
+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."""
+
+    pass
+
+
+class Metricity:
+    """Abstraction for a connection to the metricity database."""
+
+    def __init__(self):
+        self.cursor = connections['metricity'].cursor()
+
+    def __enter__(self):
+        return self
+
+    def __exit__(self, *_):
+        self.cursor.close()
+
+    def user(self, user_id: str) -> dict:
+        """Query a user's data."""
+        # 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()
+
+        if not values:
+            raise NotFound()
+
+        return dict(zip(columns, values))
+
+    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
+              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:
+            raise NotFound()
+
+        return values[0]
+
+    def top_channel_activity(self, user_id: str) -> List[Tuple[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'
+                    WHEN channels.name ILIKE '%%voice%%' THEN 'voice chats'
+                    ELSE channels.name
+                END,
+                COUNT(1)
+            FROM
+                messages
+                LEFT JOIN channels ON channels.id = messages.channel_id
+            WHERE
+                author_id = '%s' AND NOT messages.is_deleted
+            GROUP BY
+                1
+            ORDER BY
+                2 DESC
+            LIMIT
+                3;
+            """,
+            [user_id]
+        )
+
+        values = self.cursor.fetchall()
+
+        if not values:
+            raise NotFound()
+
+        return values
diff --git a/pydis_site/apps/api/models/bot/nomination.py b/pydis_site/apps/api/models/bot/nomination.py
index 11b9e36e..221d8534 100644
--- a/pydis_site/apps/api/models/bot/nomination.py
+++ b/pydis_site/apps/api/models/bot/nomination.py
@@ -5,23 +5,12 @@ from pydis_site.apps.api.models.mixins import ModelReprMixin
 
 
 class Nomination(ModelReprMixin, models.Model):
-    """A helper nomination created by staff."""
+    """A general helper nomination information created by staff."""
 
     active = models.BooleanField(
         default=True,
         help_text="Whether this nomination is still relevant."
     )
-    actor = models.ForeignKey(
-        User,
-        on_delete=models.CASCADE,
-        help_text="The staff member that nominated this user.",
-        related_name='nomination_set'
-    )
-    reason = models.TextField(
-        help_text="Why this user was nominated.",
-        null=True,
-        blank=True
-    )
     user = models.ForeignKey(
         User,
         on_delete=models.CASCADE,
@@ -42,6 +31,10 @@ class Nomination(ModelReprMixin, models.Model):
         help_text="When the nomination was ended.",
         null=True
     )
+    reviewed = models.BooleanField(
+        default=False,
+        help_text="Whether a review was made."
+    )
 
     def __str__(self):
         """Representation that makes the target and state of the nomination immediately evident."""
@@ -52,3 +45,38 @@ class Nomination(ModelReprMixin, models.Model):
         """Set the ordering of nominations to most recent first."""
 
         ordering = ("-inserted_at",)
+
+
+class NominationEntry(ModelReprMixin, models.Model):
+    """A nomination entry created by a single staff member."""
+
+    nomination = models.ForeignKey(
+        Nomination,
+        on_delete=models.CASCADE,
+        help_text="The nomination this entry belongs to.",
+        related_name="entries"
+    )
+    actor = models.ForeignKey(
+        User,
+        on_delete=models.CASCADE,
+        help_text="The staff member that nominated this user.",
+        related_name='nomination_set'
+    )
+    reason = models.TextField(
+        help_text="Why the actor nominated this user.",
+        default="",
+        blank=True
+    )
+    inserted_at = models.DateTimeField(
+        auto_now_add=True,
+        help_text="The creation date of this nomination entry."
+    )
+
+    class Meta:
+        """Meta options for NominationEntry model."""
+
+        verbose_name_plural = "nomination entries"
+
+        # Set default ordering here to latest first
+        # so we don't need to define it everywhere
+        ordering = ("-inserted_at",)
diff --git a/pydis_site/apps/api/models/log_entry.py b/pydis_site/apps/api/models/log_entry.py
deleted file mode 100644
index 752cd2ca..00000000
--- a/pydis_site/apps/api/models/log_entry.py
+++ /dev/null
@@ -1,55 +0,0 @@
-from django.db import models
-from django.utils import timezone
-
-from pydis_site.apps.api.models.mixins import ModelReprMixin
-
-
-class LogEntry(ModelReprMixin, models.Model):
-    """A log entry generated by one of the PyDis applications."""
-
-    application = models.CharField(
-        max_length=20,
-        help_text="The application that generated this log entry.",
-        choices=(
-            ('bot', 'Bot'),
-            ('seasonalbot', 'Seasonalbot'),
-            ('site', 'Website')
-        )
-    )
-    logger_name = models.CharField(
-        max_length=100,
-        help_text="The name of the logger that generated this log entry."
-    )
-    timestamp = models.DateTimeField(
-        default=timezone.now,
-        help_text="The date and time when this entry was created."
-    )
-    level = models.CharField(
-        max_length=8,  # 'critical'
-        choices=(
-            ('debug', 'Debug'),
-            ('info', 'Info'),
-            ('warning', 'Warning'),
-            ('error', 'Error'),
-            ('critical', 'Critical')
-        ),
-        help_text=(
-            "The logger level at which this entry was emitted. The levels "
-            "correspond to the Python `logging` levels."
-        )
-    )
-    module = models.CharField(
-        max_length=100,
-        help_text="The fully qualified path of the module generating this log line."
-    )
-    line = models.PositiveSmallIntegerField(
-        help_text="The line at which the log line was emitted."
-    )
-    message = models.TextField(
-        help_text="The textual content of the log line."
-    )
-
-    class Meta:
-        """Customizes the default generated plural name to valid English."""
-
-        verbose_name_plural = 'Log entries'