"""Remodel ShopSource from memberaudit onto corptools.

- Adds a corptools CharacterAudit FK and remaps existing rows by matching the
  legacy memberaudit Character's EveCharacter.
- Swaps the memberaudit Location FK for a plain BigInteger ``location_id`` while
  PRESERVING the underlying ``location_id`` column values (the old FK column was
  already named ``location_id`` and held the EVE location id, since a memberaudit
  Location's pk IS the eve location id). The FK constraint/index is dropped so
  memberaudit can be uninstalled later, but the column data survives.
"""

from django.db import migrations, models
import django.db.models.deletion


def remap(apps, schema_editor):
    ShopSource = apps.get_model("shop", "ShopSource")
    CharacterAudit = apps.get_model("corptools", "CharacterAudit")
    try:
        from memberaudit.models import Character
    except ImportError:
        return  # fresh install: no legacy memberaudit sources to remap
    for src in ShopSource.objects.all():
        ma = (
            Character.objects.filter(pk=src.character_id)
            .select_related("eve_character")
            .first()
        )
        # Match by the EVE character id (an int) rather than passing the
        # memberaudit EveCharacter instance: historical models live in a
        # separate app registry and reject cross-registry instances.
        audit = (
            CharacterAudit.objects.filter(
                character__character_id=ma.eve_character.character_id
            ).first()
            if ma
            else None
        )
        if audit is None:
            src.delete()
            continue
        src.character_audit_id = audit.pk
        src.save(update_fields=["character_audit"])


def noop(apps, schema_editor):
    pass


def drop_location_fk(apps, schema_editor):
    """Drop the legacy memberaudit Location FK constraint (and its index) while
    keeping the ``location_id`` column and all its values. No-op on fresh installs
    where the constraint was never created."""
    conn = schema_editor.connection
    with conn.cursor() as c:
        c.execute(
            """
            SELECT constraint_name
            FROM information_schema.key_column_usage
            WHERE table_schema = DATABASE()
              AND table_name = 'shop_shopsource'
              AND column_name = 'location_id'
              AND referenced_table_name = 'memberaudit_location'
            """
        )
        row = c.fetchone()
        if not row:
            return  # fresh install: no legacy FK to drop
        constraint = row[0]
        c.execute(
            f"ALTER TABLE `shop_shopsource` DROP FOREIGN KEY `{constraint}`"
        )
        # The FK's backing index shares the constraint name; drop it if present.
        c.execute(
            """
            SELECT 1 FROM information_schema.statistics
            WHERE table_schema = DATABASE()
              AND table_name = 'shop_shopsource'
              AND index_name = %s
            """,
            [constraint],
        )
        if c.fetchone():
            c.execute(f"ALTER TABLE `shop_shopsource` DROP INDEX `{constraint}`")


class Migration(migrations.Migration):

    dependencies = [
        ("shop", "0020_orderline_label"),
        ("corptools", "__first__"),
    ]

    operations = [
        # --- character FK -> corptools CharacterAudit ------------------------
        migrations.AddField(
            model_name="shopsource",
            name="character_audit",
            field=models.ForeignKey(
                null=True,
                on_delete=django.db.models.deletion.CASCADE,
                related_name="+",
                to="corptools.characteraudit",
            ),
        ),
        migrations.RunPython(remap, noop),
        migrations.RemoveField(
            model_name="shopsource",
            name="character",
        ),
        migrations.AlterField(
            model_name="shopsource",
            name="character_audit",
            field=models.ForeignKey(
                on_delete=django.db.models.deletion.CASCADE,
                related_name="+",
                to="corptools.characteraudit",
            ),
        ),
        # --- location FK -> plain BigInteger, preserving column values -------
        # The old `location` FK already lives in the `location_id` column and
        # holds the EVE location id (a memberaudit Location's pk IS the eve
        # location id). The new plain BigInteger `location_id` field uses the
        # SAME column. So we only need to (a) drop the memberaudit FK constraint
        # at the DB level and (b) swap the field in Django's state -- the column
        # and its data are left untouched.
        migrations.SeparateDatabaseAndState(
            database_operations=[
                migrations.RunPython(drop_location_fk, noop),
            ],
            state_operations=[
                migrations.RemoveField(
                    model_name="shopsource",
                    name="location",
                ),
                migrations.AddField(
                    model_name="shopsource",
                    name="location_id",
                    field=models.PositiveBigIntegerField(default=0),
                    preserve_default=False,
                ),
            ],
        ),
    ]
