# Generated by hand on 2026-01-02
# Migrate ArchiveResult from integer PK to UUID PK (matching Snapshot)

from django.db import migrations, models, connection
from uuid import UUID
from archivebox.uuid_compat import uuid7


def migrate_archiveresult_id_to_uuid(apps, schema_editor):
    """
    Migrate ArchiveResult from integer PK to UUID PK.

    Strategy:
    1. Add old_id field to store current integer IDs
    2. Generate UUIDs for any records missing them
    3. Swap id and uuid fields (uuid becomes PK, old integer id becomes old_id)
    """
    cursor = connection.cursor()

    # Check if table exists and has data
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='core_archiveresult'")
    if not cursor.fetchone():
        print('ArchiveResult table does not exist, skipping migration')
        return

    cursor.execute("SELECT COUNT(*) FROM core_archiveresult")
    row_count = cursor.fetchone()[0]

    if row_count == 0:
        print('No ArchiveResult records to migrate')
        return

    print(f'Migrating {row_count} ArchiveResult records from integer PK to UUID PK...')

    # Step 0: Check if machine_process table exists, if not NULL out process_id values
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='machine_process'")
    machine_process_exists = cursor.fetchone() is not None

    if not machine_process_exists:
        print('machine_process table does not exist yet, setting process_id to NULL')
        cursor.execute("UPDATE core_archiveresult SET process_id = NULL WHERE process_id IS NOT NULL")

    # Step 1: Create new table with UUID as primary key
    cursor.execute("""
        CREATE TABLE core_archiveresult_new (
            id TEXT PRIMARY KEY NOT NULL,
            old_id INTEGER,
            uuid TEXT UNIQUE,
            created_at DATETIME NOT NULL,
            modified_at DATETIME NOT NULL,

            snapshot_id TEXT NOT NULL,
            plugin VARCHAR(32) NOT NULL,
            hook_name VARCHAR(255) NOT NULL DEFAULT '',

            status VARCHAR(15) NOT NULL DEFAULT 'queued',
            retry_at DATETIME,

            start_ts DATETIME,
            end_ts DATETIME,

            output_str TEXT NOT NULL DEFAULT '',
            output_json TEXT,
            output_files TEXT NOT NULL DEFAULT '{}',
            output_size BIGINT NOT NULL DEFAULT 0,
            output_mimetypes VARCHAR(512) NOT NULL DEFAULT '',

            config TEXT NOT NULL DEFAULT '{}',
            notes TEXT NOT NULL DEFAULT '',
            num_uses_succeeded INTEGER NOT NULL DEFAULT 0,
            num_uses_failed INTEGER NOT NULL DEFAULT 0,

            process_id TEXT,

            FOREIGN KEY (snapshot_id) REFERENCES core_snapshot(id) ON DELETE CASCADE,
            FOREIGN KEY (process_id) REFERENCES machine_process(id) ON DELETE SET NULL
        );
    """)

    # Step 2: Generate UUIDs for records that don't have them
    cursor.execute("SELECT id, uuid FROM core_archiveresult")
    records = cursor.fetchall()

    id_to_uuid = {}
    for old_id, existing_uuid in records:
        if existing_uuid:
            # Normalize existing UUID to 32-char hex format (Django SQLite UUIDField format)
            # (existing UUIDs might be stored with or without dashes in old schema)
            id_to_uuid[old_id] = UUID(existing_uuid).hex
        else:
            # Generate new UUIDv7 (time-ordered) as 32-char hex
            id_to_uuid[old_id] = uuid7().hex

    # Step 3: Copy data with UUIDs as new primary key
    cursor.execute("SELECT * FROM core_archiveresult")
    old_records = cursor.fetchall()

    # Get column names
    cursor.execute("PRAGMA table_info(core_archiveresult)")
    columns = cursor.fetchall()
    col_names = [col[1] for col in columns]

    for i, record in enumerate(old_records):
        old_id = record[col_names.index('id')]
        new_uuid = id_to_uuid[old_id]

        # Build insert with new structure
        values = {col_names[i]: record[i] for i in range(len(col_names))}

        # Check which fields exist in new table
        fields_to_copy = [
            'created_at', 'modified_at', 'snapshot_id', 'plugin', 'hook_name',
            'status', 'retry_at', 'start_ts', 'end_ts',
            'output_str', 'output_json', 'output_files', 'output_size', 'output_mimetypes',
            'config', 'notes', 'num_uses_succeeded', 'num_uses_failed', 'process_id'
        ]

        # Build INSERT statement
        existing_fields = [f for f in fields_to_copy if f in values]
        placeholders = ', '.join(['?'] * (len(existing_fields) + 3))  # +3 for id, old_id, uuid
        field_list = 'id, old_id, uuid, ' + ', '.join(existing_fields)

        insert_values = [new_uuid, old_id, new_uuid] + [values.get(f) for f in existing_fields]

        cursor.execute(
            f"INSERT INTO core_archiveresult_new ({field_list}) VALUES ({placeholders})",
            insert_values
        )

    # Step 4: Replace old table with new table
    cursor.execute("DROP TABLE core_archiveresult")
    cursor.execute("ALTER TABLE core_archiveresult_new RENAME TO core_archiveresult")

    # Step 5: Create indexes
    cursor.execute("CREATE INDEX core_archiveresult_snapshot_id_idx ON core_archiveresult(snapshot_id)")
    cursor.execute("CREATE INDEX core_archiveresult_plugin_idx ON core_archiveresult(plugin)")
    cursor.execute("CREATE INDEX core_archiveresult_status_idx ON core_archiveresult(status)")
    cursor.execute("CREATE INDEX core_archiveresult_retry_at_idx ON core_archiveresult(retry_at)")
    cursor.execute("CREATE INDEX core_archiveresult_created_at_idx ON core_archiveresult(created_at)")
    cursor.execute("CREATE INDEX core_archiveresult_hook_name_idx ON core_archiveresult(hook_name)")
    cursor.execute("CREATE INDEX core_archiveresult_process_id_idx ON core_archiveresult(process_id)")
    cursor.execute("CREATE INDEX core_archiveresult_old_id_idx ON core_archiveresult(old_id)")

    print(f'✓ Migrated {row_count} ArchiveResult records to UUID primary key')


class Migration(migrations.Migration):

    dependencies = [
        ('core', '0028_alter_snapshot_fs_version'),
    ]

    operations = [
        migrations.SeparateDatabaseAndState(
            database_operations=[
                migrations.RunPython(
                    migrate_archiveresult_id_to_uuid,
                    reverse_code=migrations.RunPython.noop,
                ),
            ],
            state_operations=[
                # Remove old uuid field
                migrations.RemoveField(
                    model_name='archiveresult',
                    name='uuid',
                ),
                # Change id from AutoField to UUIDField
                migrations.AlterField(
                    model_name='archiveresult',
                    name='id',
                    field=models.UUIDField(primary_key=True, default=uuid7, editable=False, unique=True),
                ),
                # Add old_id field to preserve legacy integer IDs
                migrations.AddField(
                    model_name='archiveresult',
                    name='old_id',
                    field=models.IntegerField(null=True, blank=True, db_index=True, help_text='Legacy integer ID from pre-0.9.0 versions'),
                ),
            ],
        ),
    ]
