# Generated by Django 4.2.26 on 2025-12-03 19:37

from django.db import migrations, models
from django.apps import apps
from django.contrib.contenttypes.models import ContentType

def backfill_media_sqlite(apps, schema_editor):
    """
    Backfill media tables into ScrobblableItem for SQLite.
    Safely updates M2M and FK relationships.
    """
    db = schema_editor.connection
    if db.vendor != "sqlite":
        raise RuntimeError("This migration is only for SQLite.")

    cursor = db.cursor()
    models_to_migrate = [
        ('books', 'Book', 'book_id'),
        ('books', 'Paper', 'paper_id'),
        ('music', 'Track', 'track_id'),
        ('videos', 'Video', 'video_id'),
        # Add other media tables here...
    ]

    ScrobblableItem = apps.get_model("scrobbles", "ScrobblableItem")

    for app_label, model_name, old_id_field in models_to_migrate:
        Model = apps.get_model(app_label, model_name)
        table = Model._meta.db_table

        print(f"\nMigrating {app_label}.{model_name}...")

        # --- Step 0: Temporarily rename all M2M tables that reference this table ---
        for m2m in Model._meta.many_to_many:
            m2m_table = m2m.remote_field.through._meta.db_table
            backup_table = f"_backup_{m2m_table}"
            print(f"  Backing up M2M table {m2m_table} -> {backup_table}")
            cursor.execute(f"ALTER TABLE {m2m_table} RENAME TO {backup_table}")

        # --- Step 1: Select all rows from original table ---
        columns = [f.name for f in Model._meta.local_fields]
        select_cols = ", ".join(columns)
        cursor.execute(f"SELECT {select_cols} FROM {table}")
        rows = cursor.fetchall()
        col_index = {name: i for i, name in enumerate(columns)}

        # --- Step 2: Create ScrobblableItem and update FK ---
        for row in rows:
            old_id = row[col_index['id']]
            base = ScrobblableItem.objects.create(
                uuid=row[col_index.get('uuid')],
                title=row[col_index.get('title')] or "",
                description=row[col_index.get('description')] or "",
                base_run_time_seconds=row[col_index.get('base_run_time_seconds')],
            )

            # Preserve created/modified if present
            for fld in ('created', 'modified'):
                if fld in col_index:
                    setattr(base, fld, row[col_index[fld]])
            base.save(update_fields=['created', 'modified'] if 'created' in col_index else None)

            # Update the media table to point to the new PK
            cursor.execute(
                f"UPDATE {table} SET scrobblableitem_ptr_id = ? WHERE id = ?",
                [base.id, old_id]
            )

            # Update scrobbles table FK
            scrobble_table = "scrobbles_scrobble"
            cursor.execute(
                f"UPDATE {scrobble_table} SET item_id = ? WHERE {old_id_field} = ?",
                [base.id, old_id]
            )

        # --- Step 3: Restore M2M tables pointing to new IDs ---
        for m2m in Model._meta.many_to_many:
            old_m2m_table = f"_backup_{m2m.remote_field.through._meta.db_table}"
            new_m2m_table = m2m.remote_field.through._meta.db_table
            print(f"  Restoring M2M table {new_m2m_table} from {old_m2m_table}")

            # Recreate the M2M table
            columns = [f.name for f in m2m.remote_field.through._meta.local_fields]
            cols_str = ", ".join(columns)
            cursor.execute(f"INSERT INTO {new_m2m_table} ({cols_str}) "
                           f"SELECT {cols_str} FROM {old_m2m_table} backup "
                           f"JOIN {table} media ON backup.{old_id_field} = media.id")

            # Drop backup
            cursor.execute(f"DROP TABLE {old_m2m_table}")

        print(f"Finished migrating {app_label}.{model_name}")

def backfill_media(apps, schema_editor):
    if schema_editor.connection.vendor == "sqlite":
        backfill_media_sqlite(apps, schema_editor)
    else:
        # Postgres-safe logic can go here
        pass


class Migration(migrations.Migration):

    dependencies = [
        ('scrobbles', '0073_alter_scrobble_options_scrobble_item_and_more'),
    ]

    operations = [
        migrations.RunPython(backfill_media, reverse_code=migrations.RunPython.noop),
        migrations.AlterField(
            model_name='scrobble',
            name='item',
            field=models.ForeignKey(null=True, on_delete=models.deletion.CASCADE, related_name='scrobble_set', to='scrobbles.scrobblableitem'),
        ),
    ]
