# Generated by Django 2.2.20 on 2021-04-20 20:28
from urllib.parse import parse_qs, urlparse

from django.conf import settings
from django.core.exceptions import ValidationError
from django.db import connections, migrations
from django.db.models import Count

from brouwers.forum_tools.forms.fields import TopicIDField

POST_QUERY = (
    f"SELECT topic_id FROM {settings.PHPBB_TABLE_PREFIX}posts WHERE post_id = '%s'"
)

MAX_TOPIC_ID = 100000

BASE_URL = "https://modelbrouwers.nl"


def from_url_to_topic_id(apps, schema_editor):
    Project = apps.get_model("awards", "Project")
    field = TopicIDField()

    mysql_connection = connections["mysql"]

    for project in Project.objects.all():
        try:
            project.topic = field.to_python(project.url)
        except ValidationError:
            # check if there's a `?p` querystring -> points to a post
            querystring = parse_qs(urlparse(project.url).query)
            if "p" in querystring:
                post_id = int(querystring["p"][0])
                with mysql_connection.cursor() as cursor:
                    cursor.execute(POST_QUERY, [post_id])
                    topic_id = cursor.fetchone()[0]
                project.topic_id = topic_id
            else:
                print(f"Invalid topic URL: {project.url}")
                continue
        if project.topic_id and project.topic_id > MAX_TOPIC_ID:
            print(f"Suspicious topic ID, skipping: {project.url}")
            continue

        project.save()

    # clean up duplicates that snuck in because of with/without www. URL
    dupes = (
        Project.objects.values("category", "topic_id")
        .annotate(cnt=Count("id"))
        .filter(cnt__gt=1)
        .order_by()
    )
    Project.objects.filter(
        votes=0, topic_id__in=dupes.values_list("topic_id", flat=True)
    ).delete()


def from_id_to_topic_url(apps, schema_editor):
    Project = apps.get_model("awards", "Project")
    for project in Project.objects.exclude(topic_id__isnull=True):
        project.url = f"{BASE_URL}{project.topic.get_absolute_url()}"
        project.save()


class Migration(migrations.Migration):

    dependencies = [
        ("awards", "0006_project_topic"),
    ]

    operations = [
        migrations.RunPython(from_url_to_topic_id, from_id_to_topic_url),
    ]
