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


def drop_legacy_unique_if_exists(apps, schema_editor):
    """
    MySQL/MariaDB: drop legacy UNIQUE(item_id, structure_id) if it exists.
    Some installations may not have it -> do nothing.
    """
    table = "markettracker_trackeditem"
    cols = ("item_id", "structure_id")

    with schema_editor.connection.cursor() as cursor:
        cursor.execute(
            """
            SELECT INDEX_NAME
            FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = %s
              AND NON_UNIQUE = 0
            GROUP BY INDEX_NAME
            HAVING GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) = %s
            LIMIT 1
            """,
            [table, ",".join(cols)],
        )
        row = cursor.fetchone()
        if not row:
            return
        idx_name = row[0]
        cursor.execute(f"ALTER TABLE `{table}` DROP INDEX `{idx_name}`;")


def ensure_location_column(apps, schema_editor):
    """
    Ensure location_id column exists on markettracker_trackeditem.
    If it already exists -> noop (fixes Duplicate column).
    If missing -> add it as nullable BIGINT to allow data backfill.
    """
    table = "markettracker_trackeditem"

    with schema_editor.connection.cursor() as cursor:
        cursor.execute(
            """
            SELECT COUNT(*)
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = %s
              AND COLUMN_NAME = 'location_id'
            """,
            [table],
        )
        exists = int(cursor.fetchone()[0] or 0) > 0

        if not exists:
            cursor.execute(f"ALTER TABLE `{table}` ADD COLUMN `location_id` bigint NULL;")

        # Ensure index exists (optional but good)
        cursor.execute(
            """
            SELECT COUNT(*)
            FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = %s
              AND INDEX_NAME = 'mt_trackeditem_location_id_idx'
            """,
            [table],
        )
        idx_exists = int(cursor.fetchone()[0] or 0) > 0
        if not idx_exists:
            cursor.execute(f"CREATE INDEX `mt_trackeditem_location_id_idx` ON `{table}` (`location_id`);")

        # Ensure FK exists (best-effort)
        # Some installs may already have FK under another name -> then we do nothing.
        cursor.execute(
            """
            SELECT COUNT(*)
            FROM information_schema.KEY_COLUMN_USAGE
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = %s
              AND COLUMN_NAME = 'location_id'
              AND REFERENCED_TABLE_NAME IS NOT NULL
            """,
            [table],
        )
        fk_exists = int(cursor.fetchone()[0] or 0) > 0
        if not fk_exists:
            # create a deterministic FK name
            cursor.execute(
                f"""
                ALTER TABLE `{table}`
                ADD CONSTRAINT `mt_trackeditem_location_fk`
                FOREIGN KEY (`location_id`)
                REFERENCES `markettracker_trackedlocation` (`id`)
                ON DELETE CASCADE
                """
            )


def ensure_new_unique_if_missing(apps, schema_editor):
    """
    Ensure UNIQUE(item_id, location_id) exists on markettracker_trackeditem.
    Works across installs where Django state differs.
    """
    table = "markettracker_trackeditem"
    cols = ("item_id", "location_id")

    with schema_editor.connection.cursor() as cursor:
        # do we already have a UNIQUE index exactly on (item_id, location_id)?
        cursor.execute(
            """
            SELECT COUNT(*)
            FROM (
                SELECT INDEX_NAME
                FROM information_schema.STATISTICS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = %s
                  AND NON_UNIQUE = 0
                GROUP BY INDEX_NAME
                HAVING GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) = %s
            ) t
            """,
            [table, ",".join(cols)],
        )
        exists = int(cursor.fetchone()[0] or 0) > 0
        if exists:
            return

        # create one with deterministic name
        cursor.execute(
            f"CREATE UNIQUE INDEX `mt_trackeditem_item_location_uniq` ON `{table}` (`item_id`, `location_id`);"
        )


def forwards(apps, schema_editor):
    TrackedLocation = apps.get_model("markettracker", "TrackedLocation")
    table = "markettracker_trackeditem"

    default_loc = TrackedLocation.objects.filter(is_default=True, is_active=True).first()
    if not default_loc:
        default_loc = TrackedLocation.objects.filter(is_active=True).first()
    if not default_loc:
        return

    default_id = int(default_loc.pk)

    with schema_editor.connection.cursor() as cursor:
        # does legacy structure_id exist?
        cursor.execute(
            """
            SELECT COUNT(*)
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = %s
              AND COLUMN_NAME = 'structure_id'
            """,
            [table],
        )
        has_structure_id = int(cursor.fetchone()[0] or 0) > 0

        # does location_id exist? (should after ensure_location_column)
        cursor.execute(
            """
            SELECT COUNT(*)
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = %s
              AND COLUMN_NAME = 'location_id'
            """,
            [table],
        )
        has_location_id = int(cursor.fetchone()[0] or 0) > 0
        if not has_location_id:
            # safety: if ensure_location_column didn't run for some reason
            cursor.execute(f"ALTER TABLE `{table}` ADD COLUMN `location_id` bigint NULL;")

        if has_structure_id:
            # copy per-row mapping where possible
            cursor.execute(
                f"""
                UPDATE `{table}`
                SET `location_id` = COALESCE(`location_id`, `structure_id`, %s)
                """,
                [default_id],
            )
        else:
            # no legacy mapping -> just set defaults where missing
            cursor.execute(
                f"""
                UPDATE `{table}`
                SET `location_id` = COALESCE(`location_id`, %s)
                """,
                [default_id],
            )



def backwards(apps, schema_editor):
    # no reliable rollback
    return



class Migration(migrations.Migration):

    dependencies = [
        ("markettracker", "0011_trackedlocation_rename_and_seed"),
    ]

    operations = [
        # A) usuń legacy unique (safe)
        migrations.RunPython(drop_legacy_unique_if_exists, migrations.RunPython.noop),

        # B) ensure DB column + FK (safe)
        migrations.RunPython(ensure_location_column, migrations.RunPython.noop),

        # C) update Django state (no DB changes here)
        migrations.SeparateDatabaseAndState(
            database_operations=[],
            state_operations=[
                migrations.AddField(
                    model_name="trackeditem",
                    name="location",
                    field=models.ForeignKey(
                        blank=True,
                        null=True,
                        on_delete=django.db.models.deletion.CASCADE,
                        related_name="tracked_items",
                        to="markettracker.trackedlocation",
                    ),
                ),
            ],
        ),

        # D) backfill (SQL-only)
        migrations.RunPython(forwards, migrations.RunPython.noop),

        # E) make non-null at ORM/state level
        migrations.AlterField(
            model_name="trackeditem",
            name="location",
            field=models.ForeignKey(
                on_delete=django.db.models.deletion.CASCADE,
                related_name="tracked_items",
                to="markettracker.trackedlocation",
            ),
        ),

        # F) ensure new unique exists (safe)
        migrations.RunPython(ensure_new_unique_if_missing, migrations.RunPython.noop),
    ]

