3 files changed, 124 insertions, 2 deletions
diff --git a/pydis_site/apps/api/migrations/0047_active_infractions_migration.py b/pydis_site/apps/api/migrations/0047_active_infractions_migration.py
new file mode 100644
index 00000000..9ac791dc
--- /dev/null
+++ b/pydis_site/apps/api/migrations/0047_active_infractions_migration.py
@@ -0,0 +1,105 @@
+# Generated by Django 2.2.6 on 2019-10-07 15:59
+
+from django.db import migrations
+from django.db.models import Count, Prefetch, QuerySet
+
+
+class ExpirationWrapper:
+    """Wraps an expiration date to properly compare permanent and temporary infractions."""
+
+    def __init__(self, infraction):
+        self.expiration_date = infraction.expires_at
+
+    def __lt__(self, other):
+        """An `expiration_date` is considered smaller when it comes earlier than the `other`."""
+        if self.expiration_date is None:
+            # A permanent infraction can never end sooner than another infraction
+            return False
+        elif other.expiration_date is None:
+            # If `self` is temporary, but `other` is permanent, `self` is smaller
+            return True
+        else:
+            return self.expiration_date < other.expiration_date
+
+    def __eq__(self, other):
+        """If both expiration dates are permanent they're equal, otherwise compare dates."""
+        if self.expiration_date is None and other.expiration_date is None:
+            return True
+        elif self.expiration_date is None or other.expiration_date is None:
+            return False
+        else:
+            return self.expiration_date == other.expiration_date
+
+
+def migrate_inactive_types_to_inactive(apps, schema_editor):
+    """Migrates infractions of non-active types to inactive."""
+    infraction_model = apps.get_model('api', 'Infraction')
+    infraction_model.objects.filter(type__in=('note', 'warning', 'kick')).update(active=False)
+
+
+def get_query(user_model, infraction_model, infr_type: str) -> QuerySet:
+    """
+    Creates QuerySet to fetch users with multiple active infractions of the given `type`.
+
+    The QuerySet will prefetch the infractions and attach them as an `.infractions` attribute to the
+    `User` instances.
+    """
+    active_infractions = infraction_model.objects.filter(type=infr_type, active=True)
+
+    # Build an SQL query by chaining methods together
+
+    # Get users with active infraction(s) of the provided `infr_type`
+    query = user_model.objects.filter(
+        infractions_received__type=infr_type, infractions_received__active=True
+    )
+
+    # Prefetch their active received infractions of `infr_type` and attach `.infractions` attribute
+    query = query.prefetch_related(
+        Prefetch('infractions_received', queryset=active_infractions, to_attr='infractions')
+    )
+
+    # Count and only include them if they have at least 2 active infractions of the `type`
+    query = query.annotate(num_infractions=Count('infractions_received'))
+    query = query.filter(num_infractions__gte=2)
+
+    # Make sure we return each individual only once
+    query = query.distinct()
+
+    return query
+
+
+def migrate_multiple_active_infractions_per_user_to_one(apps, schema_editor):
+    """
+    Make sure a user only has one active infraction of a given "active" infraction type.
+
+    If a user has multiple active infraction, we keep the one with longest expiration date active
+    and migrate the others to inactive.
+    """
+    infraction_model = apps.get_model('api', 'Infraction')
+    user_model = apps.get_model('api', 'User')
+
+    for infraction_type in ('ban', 'mute', 'superstar', 'watch'):
+        query = get_query(user_model, infraction_model, infraction_type)
+        for user in query:
+            infractions = sorted(user.infractions, key=ExpirationWrapper, reverse=True)
+            for infraction in infractions[1:]:
+                infraction.active = False
+                infraction.save()
+
+
+def reverse_migration(apps, schema_editor):
+    """There's no need to do anything special to reverse these migrations."""
+    return
+
+
+class Migration(migrations.Migration):
+    """Data migration to get the database consistent with the new infraction validation rules."""
+
+    dependencies = [
+        ('api', '0046_reminder_jump_url'),
+    ]
+
+    operations = [
+        migrations.RunPython(migrate_inactive_types_to_inactive, reverse_migration),
+        migrations.RunPython(migrate_multiple_active_infractions_per_user_to_one, reverse_migration)
+    ]
diff --git a/pydis_site/apps/api/migrations/0048_add_infractions_unique_constraints_active.py b/pydis_site/apps/api/migrations/0048_add_infractions_unique_constraints_active.py
new file mode 100644
index 00000000..4ea1fb90
--- /dev/null
+++ b/pydis_site/apps/api/migrations/0048_add_infractions_unique_constraints_active.py
@@ -0,0 +1,17 @@
+# Generated by Django 2.2.6 on 2019-10-07 18:27
+
+from django.db import migrations, models
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ('api', '0047_active_infractions_migration'),
+    ]
+
+    operations = [
+        migrations.AddConstraint(
+            model_name='infraction',
+            constraint=models.UniqueConstraint(condition=models.Q(active=True), fields=('user', 'type'), name='unique_active_infraction_per_type_per_user'),
+        ),
+    ]
diff --git a/pydis_site/apps/api/migrations/0047_deletedmessage_attachments.py b/pydis_site/apps/api/migrations/0049_deletedmessage_attachments.py
index a738faff..9cbde96d 100644
--- a/pydis_site/apps/api/migrations/0047_deletedmessage_attachments.py
+++ b/pydis_site/apps/api/migrations/0049_deletedmessage_attachments.py
@@ -7,14 +7,14 @@ from django.db import migrations, models
 class Migration(migrations.Migration):
 
     dependencies = [
-        ('api', '0046_reminder_jump_url'),
+        ('api', '0048_add_infractions_unique_constraints_active'),
     ]
 
     operations = [
         migrations.AddField(
             model_name='deletedmessage',
             name='attachments',
-            field=django.contrib.postgres.fields.ArrayField(base_field=models.URLField(max_length=512), default=[], help_text='Attachments attached to this message.', size=None),
+            field=django.contrib.postgres.fields.ArrayField(base_field=models.URLField(max_length=512), default=[], blank=True, help_text='Attachments attached to this message.', size=None),
             preserve_default=False,
         ),
     ]