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

from django.db import migrations


class Migration(migrations.Migration):

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

    operations = [
        migrations.RunSQL(
            # Forward SQL
            sql="""
                -- ============================================================================
                -- PART 1: Rename extractor → plugin in core_archiveresult
                -- ============================================================================
                -- SQLite doesn't support renaming columns directly, so we need to check if the rename is needed
                -- If 'extractor' exists and 'plugin' doesn't, we do a table rebuild

                CREATE TABLE IF NOT EXISTS core_archiveresult_new (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    uuid TEXT,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    modified_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

                    cmd TEXT,
                    pwd VARCHAR(256),
                    cmd_version VARCHAR(128),

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

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

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

                    binary_id TEXT,
                    iface_id TEXT,
                    process_id TEXT,

                    FOREIGN KEY (snapshot_id) REFERENCES core_snapshot(id) ON DELETE CASCADE,
                    FOREIGN KEY (binary_id) REFERENCES machine_binary(id) ON DELETE SET NULL,
                    FOREIGN KEY (iface_id) REFERENCES machine_networkinterface(id) ON DELETE SET NULL,
                    FOREIGN KEY (process_id) REFERENCES machine_process(id) ON DELETE RESTRICT
                );

                -- Only copy if old table exists
                INSERT OR IGNORE INTO core_archiveresult_new (
                    id, uuid, created_at, modified_at, snapshot_id, plugin,
                    cmd, pwd, cmd_version, start_ts, end_ts, status, output_str
                )
                SELECT
                    id, uuid,
                    COALESCE(start_ts, CURRENT_TIMESTAMP) as created_at,
                    COALESCE(end_ts, start_ts, CURRENT_TIMESTAMP) as modified_at,
                    snapshot_id,
                    COALESCE(extractor, '') as plugin,
                    cmd, pwd, cmd_version,
                    start_ts, end_ts, status,
                    COALESCE(output, '') as output_str
                FROM core_archiveresult
                WHERE EXISTS (SELECT 1 FROM sqlite_master WHERE type='table' AND name='core_archiveresult');

                DROP TABLE IF EXISTS core_archiveresult;
                ALTER TABLE core_archiveresult_new RENAME TO core_archiveresult;

                CREATE INDEX IF NOT EXISTS core_archiveresult_snapshot_id_idx ON core_archiveresult(snapshot_id);
                CREATE INDEX IF NOT EXISTS core_archiveresult_plugin_idx ON core_archiveresult(plugin);
                CREATE INDEX IF NOT EXISTS core_archiveresult_status_idx ON core_archiveresult(status);
                CREATE INDEX IF NOT EXISTS core_archiveresult_retry_at_idx ON core_archiveresult(retry_at);
                CREATE INDEX IF NOT EXISTS core_archiveresult_created_at_idx ON core_archiveresult(created_at);
                CREATE INDEX IF NOT EXISTS core_archiveresult_uuid_idx ON core_archiveresult(uuid);

                -- ============================================================================
                -- PART 2: Upgrade core_snapshot table
                -- ============================================================================

                CREATE TABLE IF NOT EXISTS core_snapshot_new (
                    id TEXT PRIMARY KEY NOT NULL,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    modified_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

                    url TEXT NOT NULL,
                    timestamp VARCHAR(32) NOT NULL UNIQUE,
                    bookmarked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

                    crawl_id TEXT,
                    parent_snapshot_id TEXT,

                    title VARCHAR(512),
                    downloaded_at DATETIME,
                    depth INTEGER NOT NULL DEFAULT 0,
                    fs_version VARCHAR(10) NOT NULL DEFAULT '0.9.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,

                    status VARCHAR(15) NOT NULL DEFAULT 'queued',
                    retry_at DATETIME,
                    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
                );

                -- Copy data from old table if it exists
                -- Map v0.7.2 fields: added → bookmarked_at/created_at, updated → modified_at
                INSERT OR IGNORE INTO core_snapshot_new (
                    id, url, timestamp, title, bookmarked_at, created_at, modified_at
                )
                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
                FROM core_snapshot
                WHERE EXISTS (SELECT 1 FROM sqlite_master WHERE type='table' AND name='core_snapshot');

                DROP TABLE IF EXISTS core_snapshot;
                ALTER TABLE core_snapshot_new RENAME TO core_snapshot;

                CREATE INDEX IF NOT EXISTS core_snapshot_url_idx ON core_snapshot(url);
                CREATE INDEX IF NOT EXISTS core_snapshot_timestamp_idx ON core_snapshot(timestamp);
                CREATE INDEX IF NOT EXISTS core_snapshot_bookmarked_at_idx ON core_snapshot(bookmarked_at);
                CREATE INDEX IF NOT EXISTS core_snapshot_crawl_id_idx ON core_snapshot(crawl_id);
                CREATE INDEX IF NOT EXISTS core_snapshot_status_idx ON core_snapshot(status);
                CREATE INDEX IF NOT EXISTS core_snapshot_retry_at_idx ON core_snapshot(retry_at);
                CREATE INDEX IF NOT EXISTS core_snapshot_created_at_idx ON core_snapshot(created_at);
                CREATE UNIQUE INDEX IF NOT EXISTS core_snapshot_url_crawl_unique ON core_snapshot(url, crawl_id);

                -- ============================================================================
                -- PART 3: Upgrade core_tag table
                -- ============================================================================

                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
                );

                -- Copy data from old table if it exists
                INSERT OR IGNORE INTO core_tag_new (id, name, slug)
                SELECT id, name, slug
                FROM core_tag
                WHERE EXISTS (SELECT 1 FROM sqlite_master WHERE type='table' AND name='core_tag');

                DROP TABLE IF EXISTS core_tag;
                ALTER TABLE core_tag_new RENAME TO core_tag;

                CREATE INDEX IF NOT EXISTS core_tag_created_at_idx ON core_tag(created_at);
                CREATE INDEX IF NOT EXISTS core_tag_created_by_id_idx ON core_tag(created_by_id);

                -- core_snapshot_tags table already exists in v0.7.2, no changes needed
            """,
            # Reverse SQL (best effort - data loss may occur)
            reverse_sql="""
                -- This is a best-effort rollback - data in new fields will be lost
                SELECT 'Migration 0023 cannot be fully reversed - new fields will be lost';
            """
        ),
    ]
