from django.db import migrations, models, connection
from django.utils.text import slugify


def backfill_slugs(apps, schema_editor):
    Product = apps.get_model('products', 'Product')
    products_without_slug = Product.objects.filter(slug='').select_related('vendor')
    for product in products_without_slug:
        base_slug = f"{slugify(product.name)}-{slugify(product.vendor.shop_name)}"
        if not base_slug or base_slug == '-':
            base_slug = f"product-{product.pk}"
        slug = base_slug
        counter = 1
        while Product.objects.filter(slug=slug).exclude(pk=product.pk).exists():
            slug = f"{base_slug}-{counter}"
            counter += 1
        product.slug = slug
        product.save(update_fields=['slug'])


def column_exists(table, column):
    """Check if a column already exists in the database table."""
    with connection.cursor() as cursor:
        columns = [
            info.name
            for info in connection.introspection.get_table_description(cursor, table)
        ]
    return column in columns


def drop_leftover_slug_indexes(apps, schema_editor):
    """Drop any slug-related constraints and indexes left behind by failed migration attempts."""
    if connection.vendor != 'postgresql':
        return
    with connection.cursor() as cursor:
        # Drop constraints FIRST (they own the indexes)
        cursor.execute(
            "SELECT conname FROM pg_constraint "
            "WHERE conrelid = 'products'::regclass AND conname LIKE '%%slug%%'"
        )
        for (constraint_name,) in cursor.fetchall():
            cursor.execute(
                f'ALTER TABLE "products" DROP CONSTRAINT IF EXISTS "{constraint_name}"'
            )

        # Then drop any remaining standalone indexes
        cursor.execute(
            "SELECT indexname FROM pg_indexes "
            "WHERE tablename = 'products' AND indexname LIKE '%%slug%%'"
        )
        for (index_name,) in cursor.fetchall():
            cursor.execute(f'DROP INDEX IF EXISTS "{index_name}"')


class AddSlugIfNotExists(migrations.operations.base.Operation):
    """Add the slug column only if it doesn't already exist (handles partial migration)."""
    reversible = True
    reduces_to_sql = False

    def state_forwards(self, app_label, state):
        migrations.AddField(
            model_name="product",
            name="slug",
            field=models.SlugField(blank=True, max_length=300, default=''),
        ).state_forwards(app_label, state)

    def database_forwards(self, app_label, schema_editor, from_state, to_state):
        if not column_exists('products', 'slug'):
            migrations.AddField(
                model_name="product",
                name="slug",
                field=models.SlugField(blank=True, max_length=300, default=''),
            ).database_forwards(app_label, schema_editor, from_state, to_state)

    def database_backwards(self, app_label, schema_editor, from_state, to_state):
        migrations.RemoveField(
            model_name="product",
            name="slug",
        ).database_backwards(app_label, schema_editor, from_state, to_state)

    def describe(self):
        return "Add slug field to Product if it does not already exist"


class Migration(migrations.Migration):
    # Run each operation in its own transaction so that the backfill
    # commits before AlterField tries to add the unique index.
    # Without this, PostgreSQL raises "pending trigger events".
    atomic = False

    dependencies = [
        ("products", "0007_productdislike"),
    ]

    operations = [
        # Step 1: Add slug column (skip if it already exists from a failed prior run)
        AddSlugIfNotExists(),
        # Step 2: Backfill slugs for any products with empty slug
        migrations.RunPython(backfill_slugs, migrations.RunPython.noop),
        # Step 3: Drop any leftover indexes from failed prior migration attempts
        migrations.RunPython(drop_leftover_slug_indexes, migrations.RunPython.noop),
        # Step 4: Add the unique constraint (all rows now have distinct slugs)
        migrations.AlterField(
            model_name="product",
            name="slug",
            field=models.SlugField(blank=True, max_length=300, unique=True),
        ),
    ]