# Generated by hand on 2025-12-29
# Upgrades core app from v0.7.2/v0.8.6rc0 (migration 0022) to v0.9.0 using raw SQL
# Handles both fresh installs and upgrades from v0.7.2/v0.8.6rc0

from django.db import migrations, models, connection
import django.utils.timezone


def get_table_columns(table_name):
    """Get list of column names for a table."""
    cursor = connection.cursor()
    cursor.execute(f"PRAGMA table_info({table_name})")
    return {row[1] for row in cursor.fetchall()}


def upgrade_core_tables(apps, schema_editor):
    """Upgrade core tables from v0.7.2 or v0.8.6rc0 to v0.9.0."""
    cursor = connection.cursor()

    # Check if core_archiveresult table exists
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='core_archiveresult'")
    if not cursor.fetchone():
        # Fresh install - no migration needed, tables will be created by later migrations
        return

    # Check if table has any rows
    cursor.execute("SELECT COUNT(*) FROM core_archiveresult")
    row_count = cursor.fetchone()[0]
    has_data = row_count > 0

    # Detect which version we're migrating from
    archiveresult_cols = get_table_columns('core_archiveresult')
    has_uuid = 'uuid' in archiveresult_cols
    has_abid = 'abid' in archiveresult_cols

    print(f'DEBUG: ArchiveResult row_count={row_count}, has_data={has_data}, has_uuid={has_uuid}, has_abid={has_abid}')

    # ============================================================================
    # PART 1: Upgrade core_archiveresult table
    # ============================================================================
    # Create minimal table with only OLD fields that exist in v0.7.2/v0.8.6rc0
    # Migration 0025 will add the NEW fields (plugin, hook_name, output_files, etc.)
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS core_archiveresult_new (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            uuid TEXT,
            snapshot_id TEXT NOT NULL,
            cmd TEXT,
            pwd VARCHAR(256),
            cmd_version VARCHAR(128),
            start_ts DATETIME,
            end_ts DATETIME,
            status VARCHAR(15) NOT NULL DEFAULT 'queued',
            extractor VARCHAR(32),
            output VARCHAR(1024),

            FOREIGN KEY (snapshot_id) REFERENCES core_snapshot(id) ON DELETE CASCADE
        );
    """)

    if has_data:
        if has_uuid and not has_abid:
            # Migrating from v0.7.2 (has uuid, minimal fields)
            print('Migrating ArchiveResult from v0.7.2 schema...')
            cursor.execute("""
                INSERT OR IGNORE INTO core_archiveresult_new (
                    id, uuid, snapshot_id, cmd, pwd, cmd_version,
                    start_ts, end_ts, status, extractor, output
                )
                SELECT
                    id, uuid, snapshot_id, cmd, pwd, cmd_version,
                    start_ts, end_ts, status, extractor, output
                FROM core_archiveresult;
            """)
        elif has_abid and not has_uuid:
            # Migrating from v0.8.6rc0 (has abid instead of uuid)
            print('Migrating ArchiveResult from v0.8.6rc0 schema...')
            cursor.execute("""
                INSERT OR IGNORE INTO core_archiveresult_new (
                    id, uuid, snapshot_id, cmd, pwd, cmd_version,
                    start_ts, end_ts, status, extractor, output
                )
                SELECT
                    id, abid as uuid, snapshot_id, cmd, pwd, cmd_version,
                    start_ts, end_ts, status, extractor, output
                FROM core_archiveresult;
            """)
        else:
            print(f'Warning: Unexpected schema - has_uuid={has_uuid}, has_abid={has_abid}')

    cursor.execute("DROP TABLE IF EXISTS core_archiveresult;")
    cursor.execute("ALTER TABLE core_archiveresult_new RENAME TO core_archiveresult;")

    # Don't create indexes - migration 0025 will handle them

    # ============================================================================
    # PART 2: Upgrade core_snapshot table
    # ============================================================================
    # Create table with NEW field names for timestamps (bookmarked_at, created_at, modified_at)
    # and all other fields needed by later migrations
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS core_snapshot_new (
            id TEXT PRIMARY KEY NOT NULL,
            url TEXT NOT NULL,
            timestamp VARCHAR(32) NOT NULL UNIQUE,
            title VARCHAR(512),
            crawl_id TEXT,
            parent_snapshot_id TEXT,

            bookmarked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            modified_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

            depth INTEGER NOT NULL DEFAULT 0,
            fs_version VARCHAR(10) NOT NULL DEFAULT '0.8.0',
            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,
            current_step INTEGER NOT NULL DEFAULT 0,

            FOREIGN KEY (crawl_id) REFERENCES crawls_crawl(id) ON DELETE CASCADE,
            FOREIGN KEY (parent_snapshot_id) REFERENCES core_snapshot(id) ON DELETE SET NULL
        );
    """)

    # Check if core_snapshot exists (it should)
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='core_snapshot'")
    if cursor.fetchone():
        # Check if table has any rows
        cursor.execute("SELECT COUNT(*) FROM core_snapshot")
        snapshot_has_data = cursor.fetchone()[0] > 0

        if snapshot_has_data:
            # Detect which version we're migrating from
            snapshot_cols = get_table_columns('core_snapshot')
            has_added = 'added' in snapshot_cols
            has_bookmarked_at = 'bookmarked_at' in snapshot_cols

            if has_added and not has_bookmarked_at:
                # Migrating from v0.7.2 (has added/updated fields)
                print('Migrating Snapshot from v0.7.2 schema...')
                # Transform added→bookmarked_at/created_at and updated→modified_at
                cursor.execute("""
                    INSERT OR IGNORE INTO core_snapshot_new (
                        id, url, timestamp, title,
                        bookmarked_at, created_at, modified_at,
                        status
                    )
                    SELECT
                        id, url, timestamp, title,
                        COALESCE(added, CURRENT_TIMESTAMP) as bookmarked_at,
                        COALESCE(added, CURRENT_TIMESTAMP) as created_at,
                        COALESCE(updated, added, CURRENT_TIMESTAMP) as modified_at,
                        'queued' as status
                    FROM core_snapshot;
                """)
            elif has_bookmarked_at and not has_added:
                # Migrating from v0.8.6rc0 (already has bookmarked_at/created_at/modified_at)
                print('Migrating Snapshot from v0.8.6rc0 schema...')
                # Check what fields exist
                has_status = 'status' in snapshot_cols
                has_retry_at = 'retry_at' in snapshot_cols
                has_crawl_id = 'crawl_id' in snapshot_cols

                # Build column list based on what exists
                cols = ['id', 'url', 'timestamp', 'title', 'bookmarked_at', 'created_at', 'modified_at', 'downloaded_at']
                if has_crawl_id:
                    cols.append('crawl_id')
                if has_status:
                    cols.append('status')
                if has_retry_at:
                    cols.append('retry_at')

                cursor.execute(f"""
                    INSERT OR IGNORE INTO core_snapshot_new ({', '.join(cols)})
                    SELECT {', '.join(cols)}
                    FROM core_snapshot;
                """)
            else:
                print(f'Warning: Unexpected Snapshot schema - has_added={has_added}, has_bookmarked_at={has_bookmarked_at}')

    cursor.execute("DROP TABLE IF EXISTS core_snapshot;")
    cursor.execute("ALTER TABLE core_snapshot_new RENAME TO core_snapshot;")

    # Create indexes
    cursor.execute("CREATE INDEX IF NOT EXISTS core_snapshot_url_idx ON core_snapshot(url);")
    cursor.execute("CREATE INDEX IF NOT EXISTS core_snapshot_timestamp_idx ON core_snapshot(timestamp);")
    cursor.execute("CREATE INDEX IF NOT EXISTS core_snapshot_bookmarked_at_idx ON core_snapshot(bookmarked_at);")
    cursor.execute("CREATE INDEX IF NOT EXISTS core_snapshot_crawl_id_idx ON core_snapshot(crawl_id);")
    cursor.execute("CREATE INDEX IF NOT EXISTS core_snapshot_status_idx ON core_snapshot(status);")
    cursor.execute("CREATE INDEX IF NOT EXISTS core_snapshot_retry_at_idx ON core_snapshot(retry_at);")
    cursor.execute("CREATE INDEX IF NOT EXISTS core_snapshot_created_at_idx ON core_snapshot(created_at);")
    cursor.execute("CREATE UNIQUE INDEX IF NOT EXISTS core_snapshot_url_crawl_unique ON core_snapshot(url, crawl_id);")

    # ============================================================================
    # PART 3: Upgrade core_tag table
    # ============================================================================
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS core_tag_new (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            modified_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

            name VARCHAR(100) NOT NULL UNIQUE,
            slug VARCHAR(100) NOT NULL UNIQUE,

            created_by_id INTEGER,

            FOREIGN KEY (created_by_id) REFERENCES auth_user(id) ON DELETE CASCADE
        );
    """)

    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='core_tag'")
    if cursor.fetchone():
        # Check if table has any rows
        cursor.execute("SELECT COUNT(*) FROM core_tag")
        tag_has_data = cursor.fetchone()[0] > 0

        if tag_has_data:
            tag_cols = get_table_columns('core_tag')
            cursor.execute("PRAGMA table_info(core_tag)")
            tag_id_type = None
            for row in cursor.fetchall():
                if row[1] == 'id':  # row[1] is column name
                    tag_id_type = row[2]  # row[2] is type
                    break

            if tag_id_type and 'char' in tag_id_type.lower():
                # v0.8.6rc0: Tag IDs are UUIDs, need to convert to INTEGER
                print('Converting Tag IDs from UUID to INTEGER...')

                # Get all tags with their UUIDs
                cursor.execute("SELECT id, name, slug, created_at, modified_at, created_by_id FROM core_tag ORDER BY name")
                tags = cursor.fetchall()

                # Create mapping from old UUID to new INTEGER ID
                uuid_to_int_map = {}
                for i, tag in enumerate(tags, start=1):
                    old_id, name, slug, created_at, modified_at, created_by_id = tag
                    uuid_to_int_map[old_id] = i
                    # Insert with new INTEGER ID
                    cursor.execute("""
                        INSERT OR IGNORE INTO core_tag_new (id, name, slug, created_at, modified_at, created_by_id)
                        VALUES (?, ?, ?, ?, ?, ?)
                    """, (i, name, slug, created_at, modified_at, created_by_id))

                # Update snapshot_tags to use new INTEGER IDs
                cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='core_snapshot_tags'")
                if cursor.fetchone():
                    cursor.execute("SELECT id, snapshot_id, tag_id FROM core_snapshot_tags")
                    snapshot_tags = cursor.fetchall()

                    # Delete old entries
                    cursor.execute("DELETE FROM core_snapshot_tags")

                    # Re-insert with new integer tag IDs
                    for st_id, snapshot_id, old_tag_id in snapshot_tags:
                        new_tag_id = uuid_to_int_map.get(old_tag_id)
                        if new_tag_id:
                            cursor.execute("""
                                INSERT OR IGNORE INTO core_snapshot_tags (id, snapshot_id, tag_id)
                                VALUES (?, ?, ?)
                            """, (st_id, snapshot_id, new_tag_id))
            else:
                # v0.7.2: Tag IDs are already INTEGER
                print('Migrating Tag from v0.7.2 schema...')
                cursor.execute("""
                    INSERT OR IGNORE INTO core_tag_new (id, name, slug)
                    SELECT id, name, slug
                    FROM core_tag;
                """)

    cursor.execute("DROP TABLE IF EXISTS core_tag;")
    cursor.execute("ALTER TABLE core_tag_new RENAME TO core_tag;")

    # Create indexes
    cursor.execute("CREATE INDEX IF NOT EXISTS core_tag_created_at_idx ON core_tag(created_at);")
    cursor.execute("CREATE INDEX IF NOT EXISTS core_tag_created_by_id_idx ON core_tag(created_by_id);")

    if has_data:
        print('✓ Core tables upgraded to v0.9.0')


class Migration(migrations.Migration):

    dependencies = [
        ('core', '0022_auto_20231023_2008'),
        ('crawls', '0001_initial'),
        ('auth', '0012_alter_user_first_name_max_length'),
    ]

    operations = [
        migrations.SeparateDatabaseAndState(
            database_operations=[
                migrations.RunPython(
                    upgrade_core_tables,
                    reverse_code=migrations.RunPython.noop,
                ),
            ],
            state_operations=[
                # NOTE: We do NOT remove extractor/output for ArchiveResult!
                # They are still in the database and will be removed by migration 0025
                # after copying their data to plugin/output_str.

                # However, for Snapshot, we DO remove added/updated and ADD the new timestamp fields
                # because the SQL above already transformed them.
                migrations.RemoveField(model_name='snapshot', name='added'),
                migrations.RemoveField(model_name='snapshot', name='updated'),
                migrations.AddField(
                    model_name='snapshot',
                    name='bookmarked_at',
                    field=models.DateTimeField(db_index=True, default=django.utils.timezone.now),
                ),
                migrations.AddField(
                    model_name='snapshot',
                    name='created_at',
                    field=models.DateTimeField(db_index=True, default=django.utils.timezone.now),
                ),
                migrations.AddField(
                    model_name='snapshot',
                    name='modified_at',
                    field=models.DateTimeField(auto_now=True),
                ),
                # Declare fs_version (already created in database with DEFAULT '0.8.0')
                migrations.AddField(
                    model_name='snapshot',
                    name='fs_version',
                    field=models.CharField(
                        max_length=10,
                        default='0.8.0',
                        help_text='Filesystem version of this snapshot (e.g., "0.7.0", "0.8.0", "0.9.0"). Used to trigger lazy migration on save().'
                    ),
                ),

                # SnapshotTag table already exists from v0.7.2, just declare it in state
                migrations.CreateModel(
                    name='SnapshotTag',
                    fields=[
                        ('id', models.AutoField(primary_key=True, serialize=False)),
                        ('snapshot', models.ForeignKey(to='core.Snapshot', db_column='snapshot_id', on_delete=models.CASCADE)),
                        ('tag', models.ForeignKey(to='core.Tag', db_column='tag_id', on_delete=models.CASCADE)),
                    ],
                    options={
                        'db_table': 'core_snapshot_tags',
                        'unique_together': {('snapshot', 'tag')},
                    },
                ),
                # Declare that Snapshot.tags M2M already uses through=SnapshotTag (from v0.7.2)
                migrations.AlterField(
                    model_name='snapshot',
                    name='tags',
                    field=models.ManyToManyField(
                        'Tag',
                        blank=True,
                        related_name='snapshot_set',
                        through='SnapshotTag',
                        through_fields=('snapshot', 'tag'),
                    ),
                ),
            ],
        ),
    ]
