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

def disable_fk_checks(cursor):
    cursor.execute("PRAGMA foreign_keys = OFF;")

def enable_fk_checks(cursor):
    cursor.execute("PRAGMA foreign_keys = ON;")

M2M_TABLES = {
    "beers": [("beers_beer_beerstyles", "beer_id")],
    "music": [("music_track_albums", "track_id")],
    "boardgames": [
        ("boardgames_boardgame_publishers", "board_game_id"),
        ("boardgames_boardgame_designers", "board_game_id"),
    ],
    "videogames": [
        ("videogames_videogame_platforms", "video_game_id")
    ],
}

def get_table_columns(cursor, table_name, vendor):
    """
    Returns a set of real column names for the given table.
    Supports PostgreSQL and SQLite.
    """
    if vendor == "sqlite":
        cursor.execute(f"PRAGMA table_info({table_name})")
        return {row[1] for row in cursor.fetchall()}

    elif vendor == "postgresql":
        cursor.execute("""
            SELECT column_name
            FROM information_schema.columns
            WHERE table_name = %s
        """, [table_name])
        return {row[0] for row in cursor.fetchall()}

    else:
        raise RuntimeError(f"Unsupported DB vendor: {vendor}")


def create_and_copy_data(apps, schema_editor, lookup_keys, old_to_new_mappings):
    app_label, model_name, model_id_field = lookup_keys

    db = schema_editor.connection
    vendor = db.vendor  # "sqlite", "postgresql"

    cursor = db.cursor()

    Model = apps.get_model(app_label, model_name)
    ScrobblableItem = apps.get_model("scrobbles", "ScrobblableItem")

    table = Model._meta.db_table
    scrobble_table = "scrobbles_scrobble"

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

    # -----------------------
    # 1. Detect columns
    # -----------------------
    existing_cols = get_table_columns(cursor, table, vendor)

    # fields we *try* to pull if present
    candidate_fields = ["id", "uuid", "title", "description", "base_run_time_seconds", "created", "modified"]

    # restrict to columns that exist
    fields = [f for f in candidate_fields if f in existing_cols]

    if "id" not in fields:
        raise RuntimeError(f"{table} must have an 'id' column!")

    select_clause = ", ".join(fields)

    cursor.execute(f"SELECT {select_clause} FROM {table}")
    rows = cursor.fetchall()

    mapping = {}
    old_to_new_mappings[Model] = mapping

    # -----------------------
    # 2. Process rows
    # -----------------------
    for row in rows:
        data = dict(zip(fields, row))
        old_id = data["id"]

        base = ScrobblableItem.objects.create(
            uuid=data.get("uuid"),
            title=data.get("title", ""),
            description=data.get("description", "") or "",
            base_run_time_seconds=data.get("base_run_time_seconds"),
        )
        #base.created = data.get("created")
        #base.modified = data.get("modified")
        #base.save(update_fields=["created", "modified"])

        mapping[old_id] = base.id

        # -----------------------
        # 3. Update media table FK
        # -----------------------
        if app_label in M2M_TABLES: 
            for table_and_key in M2M_TABLES.get(app_label, []):
                table, key_id = table_and_key
                print(f"Updating M2M values for {table}: changing {old_id} to {base.id}")
                cursor.execute(
                    f"UPDATE {table} SET {key_id} = %s WHERE {key_id} = %s", [base.id, old_id]
                )
        else:
            cursor.execute(
                f"UPDATE {table} SET scrobblableitem_ptr_id = %s WHERE id = %s",
                [base.id, old_id],
            )

            # -----------------------
            # 4. Update scrobbles FK
            # -----------------------
            cursor.execute(
                f"UPDATE {scrobble_table} SET item_id = %s WHERE {model_id_field} = %s",
                [base.id, old_id],
            )

        # M2M
        #if app_label in M2M_TABLES:
        #    for table_and_key in M2M_TABLES.get(app_label):
        #        table, key = table_and_key
        #        print(f"Updating M2M for {table}")
        #        cursor.execute(
        #            f"UPDATE {table} SET {key} = %s WHERE {key} = %s", [base.id, old_id]
        #        )


        # -----------------------
        # 5. Copy tags using raw SQL
        # -----------------------
        try:
            ct_id = ContentType.objects.get(app_label=app_label, model=model_name.lower()).id

            # Only copy if the old table has tags
            cursor.execute("""
                UPDATE scrobbles_objectwithgenres SET object_id = %s
                WHERE content_type_id = %s AND object_id = %s
            """, [base.id, ct_id, old_id])
        except Exception:
            print(f"No content type for {app_label}.{model_name}")

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

def reassign_foreign_keys(apps, schema_editor, old_to_new_mappings):
    """
    Detect all FKs pointing to old media tables and reassign to new ScrobblableItem.
    """
    db = schema_editor.connection
    cursor = db.cursor()

    disable_fk_checks(cursor)
    for model in apps.get_models():
        table = model._meta.db_table
        for field in model._meta.get_fields():
            # Skip genre field
            if getattr(field, 'name', None) in ['genre', 'tags']:
                continue

            if field.is_relation and field.many_to_one and field.related_model in old_to_new_mappings:
                mapping = old_to_new_mappings[field.related_model]
                fk_column = field.column
                print(f"Updating FK {model._meta.label}.{field.name} ({table}.{fk_column})…")

                for old_id, new_id in mapping.items():
                    cursor.execute(f"UPDATE {table} SET {fk_column} = %s WHERE {fk_column} = %s",
                                   [new_id, old_id])
    enable_fk_checks(cursor)

def backfill_media(apps, schema_editor):
    models = (
        ('beers', 'Beer', 'beer_id'),
        ('boardgames', 'BoardGame', 'board_game_id'),
        ('books', 'Book', 'book_id'),
        ('books', 'Paper', 'paper_id'),
        ('bricksets', 'BrickSet', 'brick_set_id'),
        ('foods', 'Food', 'food_id'),
        ('lifeevents', 'LifeEvent', 'life_event_id'),
        ('locations', 'GeoLocation', 'geo_location_id'),
        ('moods', 'Mood', 'mood_id'),
        ('music', 'Track', 'track_id'),
        ('podcasts', 'PodcastEpisode', 'podcast_episode_id'),
        ('puzzles', 'Puzzle', 'puzzle_id'),
        ('sports', 'SportEvent', 'sport_event_id'),
        ('tasks', 'Task', 'task_id'),
        ('videos', 'Video', 'video_id'),
        ('webpages', 'WebPage', 'web_page_id'),
    )

    ChartRecord = apps.get_model("scrobbles", "ChartRecord")
    ChartRecord.objects.all().delete()

    old_to_new_mappings = {}
    for keys in models:
        create_and_copy_data(apps, schema_editor, keys, old_to_new_mappings)

    # After all media tables migrated, reassign FKs and M2Ms
    reassign_foreign_keys(apps, schema_editor, old_to_new_mappings)

class Migration(migrations.Migration):
    dependencies = [
        ('beers', '0007_add_scrobblableitem_ptr'),
        ('boardgames', '0014_add_scrobblableitem_ptr'),
        ('books', '0033_add_scrobblableitem_ptr'),
        ('bricksets', '0004_add_scrobblableitem_ptr'),
        ('foods', '0005_add_scrobblableitem_ptr'),
        ('lifeevents', '0004_add_scrobblableitem_ptr'),
        ('locations', '0009_add_scrobblableitem_ptr'),
        ('moods', '0005_add_scrobblableitem_ptr'),
        ('music', '0030_add_scrobblableitem_ptr'),
        ('podcasts', '0019_add_scrobblableitem_ptr'),
        ('puzzles', '0005_add_scrobblableitem_ptr'),
        ('sports', '0017_add_scrobblableitem_ptr'),
        ('tasks', '0006_add_scrobblableitem_ptr'),
        ('trails', '0007_add_scrobblableitem_ptr'),
        ('videogames', '0014_add_scrobblableitem_ptr'),
        ('videos', '0025_add_scrobblableitem_ptr'),
        ('webpages', '0007_add_scrobblableitem_ptr'),
        ('scrobbles', '0071_scrobblableitem'),
    ]

    operations = [
        migrations.RunPython(backfill_media, reverse_code=migrations.RunPython.noop),
        migrations.RemoveField(
            model_name="scrobble",
            name="beer",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="board_game",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="book",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="brick_set",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="food",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="geo_location",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="life_event",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="long_play_complete",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="long_play_seconds",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="mood",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="paper",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="podcast_episode",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="puzzle",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="sport_event",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="task",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="track",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="trail",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="video",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="video_game",
        ),
        migrations.RemoveField(
            model_name="scrobble",
            name="web_page",
        ),
    ]
