pinready 0.24.0

use anyhow::{Context, Result};
use rusqlite::Connection;
use std::path::{Path, PathBuf};

/// Default database location, following OS conventions:
/// - Linux:   ~/.local/share/pinready/pinready.db
/// - macOS:   ~/Library/Application Support/pinready/pinready.db
/// - Windows: %APPDATA%\pinready\pinready.db
pub fn default_db_path() -> PathBuf {
    dirs::data_dir()
        .unwrap_or_else(|| PathBuf::from("."))
        .join("pinready")
        .join("pinready.db")
}

pub struct Database {
    conn: Connection,
}

impl Database {
    pub fn open(path: Option<&Path>) -> Result<Self> {
        let path = path.map(PathBuf::from).unwrap_or_else(default_db_path);

        if let Some(parent) = path.parent() {
            std::fs::create_dir_all(parent)
                .with_context(|| format!("Failed to create db directory: {}", parent.display()))?;
        }

        let conn = Connection::open(&path)
            .with_context(|| format!("Failed to open database: {}", path.display()))?;

        // WAL mode: lets the per-table scan workers each open their own
        // Database::open(None) and write to disjoint rows concurrently
        // without queueing on the file-level exclusive lock that the
        // default DELETE journal mode would impose. Readers also stop
        // blocking writers (catalog read while bg-worker writes).
        // Idempotent: PRAGMA journal_mode=WAL only flips when the DB is
        // not already in WAL.
        conn.pragma_update(None, "journal_mode", "WAL")
            .context("Failed to enable WAL journal mode")?;
        // synchronous=NORMAL is the WAL recommendation: durable across
        // app crashes, fsync only at checkpoint (much faster than FULL,
        // which fsyncs on every commit). PinReady's data is rebuildable
        // from .vpx + .directb2s anyway so we don't need FULL guarantees.
        conn.pragma_update(None, "synchronous", "NORMAL")
            .context("Failed to set synchronous=NORMAL")?;

        let db = Self { conn };
        db.migrate()?;
        db.init_schema()?;
        Ok(db)
    }

    /// Apply destructive schema migrations before `init_schema` creates
    /// the current-shape tables. Only the `backglass` cache table has
    /// ever been reshaped; its contents are always regeneratable from
    /// the `.vpx`/`.directb2s` files on disk, so dropping the table on
    /// schema mismatch is safe.
    fn migrate(&self) -> Result<()> {
        // v1 of the backglass table used columns (path, image, source,
        // extracted_at); v2 uses (rel_path, image). Detect the old shape
        // by the presence of a `path` column and drop it so the
        // subsequent CREATE installs the new schema.
        let has_old_backglass: bool = self
            .conn
            .query_row(
                "SELECT 1 FROM pragma_table_info('backglass')
                 WHERE name = 'path' LIMIT 1",
                [],
                |row| row.get::<_, i32>(0),
            )
            .map(|_| true)
            .unwrap_or(false);
        if has_old_backglass {
            log::info!("Dropping v1 backglass cache (schema upgrade to v2)");
            self.conn
                .execute("DROP TABLE backglass", [])
                .context("Failed to drop v1 backglass table")?;
        }

        // v2 → v3: add cached_at_mtime column (Unix seconds of the source
        // file we extracted from). Existing rows get 0 → considered stale
        // on next scan and re-extracted once, from then on mtime-based
        // invalidation kicks in.
        let has_mtime_col: bool = self
            .conn
            .query_row(
                "SELECT 1 FROM pragma_table_info('backglass')
                 WHERE name = 'cached_at_mtime' LIMIT 1",
                [],
                |row| row.get::<_, i32>(0),
            )
            .map(|_| true)
            .unwrap_or(false);
        let has_v2_backglass: bool = self
            .conn
            .query_row(
                "SELECT 1 FROM pragma_table_info('backglass')
                 WHERE name = 'rel_path' LIMIT 1",
                [],
                |row| row.get::<_, i32>(0),
            )
            .map(|_| true)
            .unwrap_or(false);
        if has_v2_backglass && !has_mtime_col {
            log::info!("Adding cached_at_mtime column (schema upgrade to v3)");
            self.conn
                .execute(
                    "ALTER TABLE backglass ADD COLUMN cached_at_mtime INTEGER NOT NULL DEFAULT 0",
                    [],
                )
                .context("Failed to add cached_at_mtime column")?;
        }

        // v4 → v5: add `thumb` to backglass (grid tiles no longer share the
        // full-size image: it filled the GPU with 2000+ textures of 5 MB).
        let has_thumb_col: bool = self
            .conn
            .query_row(
                "SELECT 1 FROM pragma_table_info('backglass')
                 WHERE name = 'thumb' LIMIT 1",
                [],
                |row| row.get::<_, i32>(0),
            )
            .map(|_| true)
            .unwrap_or(false);
        let backglass_exists: bool = self
            .conn
            .query_row(
                "SELECT 1 FROM sqlite_master WHERE type='table' AND name='backglass'",
                [],
                |row| row.get::<_, i32>(0),
            )
            .map(|_| true)
            .unwrap_or(false);
        if backglass_exists && !has_thumb_col {
            log::info!("Adding thumb column to backglass (schema upgrade to v5)");
            self.conn
                .execute("ALTER TABLE backglass ADD COLUMN thumb BLOB", [])
                .context("Failed to add thumb column")?;
        }

        // v3 → v4: add `update_available` to vps_link. Set by the catalog
        // worker when the live `Game.updated_at` has moved past the value
        // we stored at link time → surfaced in the launcher as a "↑"
        // badge. Existing rows get 0 (no signal) until the next worker
        // run reclassifies them.
        let vps_link_exists: bool = self
            .conn
            .query_row(
                "SELECT 1 FROM sqlite_master WHERE type='table' AND name='vps_link'",
                [],
                |row| row.get::<_, i32>(0),
            )
            .map(|_| true)
            .unwrap_or(false);
        if vps_link_exists {
            let has_update_col: bool = self
                .conn
                .query_row(
                    "SELECT 1 FROM pragma_table_info('vps_link')
                     WHERE name = 'update_available' LIMIT 1",
                    [],
                    |row| row.get::<_, i32>(0),
                )
                .map(|_| true)
                .unwrap_or(false);
            if !has_update_col {
                log::info!("Adding update_available column (schema upgrade to v4)");
                self.conn
                    .execute(
                        "ALTER TABLE vps_link ADD COLUMN update_available INTEGER NOT NULL DEFAULT 0",
                        [],
                    )
                    .context("Failed to add update_available column")?;
            }
        }
        Ok(())
    }

    fn init_schema(&self) -> Result<()> {
        self.conn
            .execute_batch(
                "CREATE TABLE IF NOT EXISTS config (
                key   TEXT PRIMARY KEY,
                value TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS tables (
                id           INTEGER PRIMARY KEY AUTOINCREMENT,
                path         TEXT NOT NULL UNIQUE,
                name         TEXT NOT NULL,
                manufacturer TEXT,
                year         INTEGER,
                rom_name     TEXT,
                last_scanned TEXT NOT NULL
            );

            -- Per-table backglass cache. Keyed by the .vpx path RELATIVE
            -- to the configured tables directory, so moving the tables
            -- folder to another disk (and updating `tables_dir` in config)
            -- doesn't invalidate the cache. `image` holds JPEG bytes at
            -- quality 85 (~5× smaller than PNG, visually lossless on the
            -- photographic backglass content at 1280×1024); PNG/WebP bytes
            -- stored as-is when the source was a user override at
            -- `<table_dir>/medias/launcher.(png|webp|jpg|jpeg)` or
            -- `<table_dir>/medias/bg.png` (vpinmediadb).
            -- `thumb` is the small grid tile made from `image` (JPEG, see
            -- `assets::THUMB_WIDTH`); NULL on rows cached before the column
            -- existed, filled in the background at the next launch.
            -- `cached_at_mtime` is the Unix-seconds mtime of the source
            -- file at extraction time; the scanner re-extracts when any
            -- candidate source file on disk is newer.
            CREATE TABLE IF NOT EXISTS backglass (
                rel_path        TEXT    PRIMARY KEY,
                image           BLOB    NOT NULL,
                cached_at_mtime INTEGER NOT NULL DEFAULT 0,
                thumb           BLOB
            );

            -- jsm174/vpx-standalone-scripts catalog cache. Single-row
            -- table (keyed on last_commit_sha). At startup we hit
            -- api.github.com to read the master branch's latest commit
            -- SHA and compare — if different, we re-fetch hashes.json.
            -- Saves bandwidth (hashes.json is ~150 KB) and lets us work
            -- offline with the last-known catalog.
            CREATE TABLE IF NOT EXISTS vbs_catalog (
                last_commit_sha TEXT    PRIMARY KEY,
                hashes_json     TEXT    NOT NULL,
                fetched_at      INTEGER NOT NULL
            );

            -- Per-table VBS patch state. Keyed on `rel_path` (same
            -- semantics as the backglass cache — the .vpx path relative
            -- to tables_dir). `embedded_sha256` is the SHA256 of the VBS
            -- embedded inside the .vpx; `sidecar_sha256` is the SHA of
            -- the companion .vbs file next to the .vpx, if any.
            -- `status` records what the scanner did: NotInCatalog /
            -- AlreadyPatched / Applied / CustomPreserved (the user's
            -- sidecar was renamed to .pre_standalone.vbs before applying
            -- the patched version from the catalog) / Failed.
            -- `last_checked_mtime` is max(mtime(.vpx), mtime(sidecar))
            -- at classification time — scanner re-evaluates when any
            -- source file is newer than this.
            CREATE TABLE IF NOT EXISTS vbs_patches (
                rel_path           TEXT    PRIMARY KEY,
                embedded_sha256    TEXT    NOT NULL,
                sidecar_sha256     TEXT,
                status             TEXT    NOT NULL,
                last_checked_mtime INTEGER NOT NULL DEFAULT 0
            );

            -- Catalog enrichment from Virtual Pinball Spreadsheet
            -- (`vps-db`) + VPinMediaDB. Keyed on the same `rel_path` as
            -- `backglass`/`vbs_patches` so the three caches age together.
            -- `vps_id` is the stable VPS-assigned game ID (joined against
            -- `vpsdb.json[*].id`); `vps_table_id` pins the specific table
            -- variant inside that game tableFiles[*]. confidence is
            -- one of high/medium/low from the matcher; lower
            -- confidences may want a user confirmation step before we
            -- treat the link as authoritative. vps_updated_at is the
            -- catalog updatedAt at link time — when the live VPS DB
            -- moves past it, the launcher can flag update-available.
            -- `media_*_md5` records the catalog-declared md5 of each
            -- asset we pulled, so a re-scan only re-downloads when the
            -- catalog publishes a fresher copy. Per-rel_path so moving
            -- a table folder doesn't lose the link.
            CREATE TABLE IF NOT EXISTS vps_link (
                rel_path         TEXT    PRIMARY KEY,
                vps_id           TEXT    NOT NULL,
                vps_table_id     TEXT,
                confidence       TEXT    NOT NULL,
                strategy         TEXT    NOT NULL,
                vps_updated_at   INTEGER NOT NULL DEFAULT 0,
                media_bg_md5     TEXT,
                media_audio_md5  TEXT,
                media_wheel_md5  TEXT,
                last_synced_at   INTEGER NOT NULL DEFAULT 0,
                update_available INTEGER NOT NULL DEFAULT 0
            );",
            )
            .context("Failed to initialize database schema")?;
        Ok(())
    }

    /// Lookup the cached backglass image for a table by its `.vpx` path
    /// relative to the configured `tables_dir`. Returns `(image_bytes,
    /// cached_at_mtime)` so the scanner can compare against source-file
    /// mtimes and re-extract when stale. `None` if no entry exists.
    pub fn get_backglass(&self, rel_path: &str) -> Option<(Vec<u8>, i64)> {
        self.conn
            .query_row(
                "SELECT image, cached_at_mtime FROM backglass WHERE rel_path = ?1",
                [rel_path],
                |row| Ok((row.get::<_, Vec<u8>>(0)?, row.get::<_, i64>(1)?)),
            )
            .ok()
    }

    /// Upsert an encoded backglass image (JPEG/PNG/WebP depending on
    /// source) and its grid thumbnail for a table's relative path.
    /// `source_mtime` is the Unix seconds mtime of the file we extracted
    /// from — stored so the next scan can detect user-dropped launcher.*
    /// overrides without a manual rescan.
    pub fn set_backglass(
        &self,
        rel_path: &str,
        image: &[u8],
        thumb: Option<&[u8]>,
        source_mtime: i64,
    ) -> Result<()> {
        self.conn
            .execute(
                "INSERT INTO backglass (rel_path, image, cached_at_mtime, thumb)
                 VALUES (?1, ?2, ?3, ?4)
                 ON CONFLICT(rel_path) DO UPDATE
                 SET image = excluded.image,
                     cached_at_mtime = excluded.cached_at_mtime,
                     thumb = excluded.thumb",
                rusqlite::params![rel_path, image, source_mtime, thumb],
            )
            .context("Failed to insert backglass row")?;
        Ok(())
    }

    /// Every cached grid thumbnail: `rel_path -> (thumb bytes,
    /// cached_at_mtime)`. One query at launch: thumbnails are small
    /// (~20 KB), unlike the full images that `get_backglass` reads one by one.
    pub fn get_all_thumbs(&self) -> std::collections::HashMap<String, (Vec<u8>, i64)> {
        let mut out = std::collections::HashMap::new();
        let Ok(mut stmt) = self.conn.prepare(
            "SELECT rel_path, thumb, cached_at_mtime FROM backglass WHERE thumb IS NOT NULL",
        ) else {
            return out;
        };
        let rows = stmt.query_map([], |row| {
            Ok((
                row.get::<_, String>(0)?,
                row.get::<_, Vec<u8>>(1)?,
                row.get::<_, i64>(2)?,
            ))
        });
        if let Ok(rows) = rows {
            for (rel, thumb, mtime) in rows.flatten() {
                out.insert(rel, (thumb, mtime));
            }
        }
        out
    }

    /// Rows cached before the `thumb` column existed: `(rel_path,
    /// cached_at_mtime)`. The launcher makes their thumbnails in the
    /// background from the stored full image.
    pub fn list_without_thumb(&self) -> Vec<(String, i64)> {
        let Ok(mut stmt) = self
            .conn
            .prepare("SELECT rel_path, cached_at_mtime FROM backglass WHERE thumb IS NULL")
        else {
            return Vec::new();
        };
        stmt.query_map([], |row| {
            Ok((row.get::<_, String>(0)?, row.get::<_, i64>(1)?))
        })
        .map(|rows| rows.flatten().collect())
        .unwrap_or_default()
    }

    /// Store the thumbnail of an already cached backglass.
    pub fn set_thumb(&self, rel_path: &str, thumb: &[u8]) -> Result<()> {
        self.conn
            .execute(
                "UPDATE backglass SET thumb = ?2 WHERE rel_path = ?1",
                rusqlite::params![rel_path, thumb],
            )
            .context("Failed to store backglass thumbnail")?;
        Ok(())
    }

    /// Wipe every cached backglass. Called by the long-press rescan so the
    /// next scan re-extracts all images from scratch.
    /// Drop the cached backglass entry for one rel_path. Used by the
    /// catalog worker after a re-match flips `vps_id` so the next scan
    /// re-extracts from the freshly-downloaded `medias/bg.png` instead
    /// of serving the cached image of the previously-matched (wrong)
    /// game.
    pub fn delete_backglass(&self, rel_path: &str) -> Result<()> {
        self.conn
            .execute("DELETE FROM backglass WHERE rel_path = ?1", [rel_path])
            .context("Failed to delete backglass row")?;
        Ok(())
    }

    pub fn clear_backglass(&self) -> Result<()> {
        self.conn
            .execute("DELETE FROM backglass", [])
            .context("Failed to clear backglass cache")?;
        Ok(())
    }

    // --- vbs_catalog (jsm174/vpx-standalone-scripts cache) ---

    /// Read the cached jsm174 catalog. Returns `(last_commit_sha,
    /// hashes_json)`. At most one row ever exists — the SHA is the
    /// primary key and we just overwrite on each refresh.
    pub fn get_vbs_catalog(&self) -> Option<(String, String)> {
        self.conn
            .query_row(
                "SELECT last_commit_sha, hashes_json FROM vbs_catalog LIMIT 1",
                [],
                |row| Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?)),
            )
            .ok()
    }

    /// Replace the cached catalog with a fresh (sha, hashes_json). The
    /// previous row is wiped first so only one entry ever lives here.
    pub fn set_vbs_catalog(&self, last_commit_sha: &str, hashes_json: &str) -> Result<()> {
        let now = std::time::SystemTime::now()
            .duration_since(std::time::UNIX_EPOCH)
            .map(|d| d.as_secs() as i64)
            .unwrap_or(0);
        self.conn
            .execute("DELETE FROM vbs_catalog", [])
            .context("Failed to clear vbs_catalog")?;
        self.conn
            .execute(
                "INSERT INTO vbs_catalog (last_commit_sha, hashes_json, fetched_at)
                 VALUES (?1, ?2, ?3)",
                rusqlite::params![last_commit_sha, hashes_json, now],
            )
            .context("Failed to insert vbs_catalog row")?;
        Ok(())
    }

    // --- vbs_patches (per-table state) ---

    /// Lookup the recorded VBS-patch state for a table. Returns
    /// `(embedded_sha, sidecar_sha, status, last_checked_mtime)`.
    /// `None` if no entry exists (never classified).
    pub fn get_vbs_patch(&self, rel_path: &str) -> Option<(String, Option<String>, String, i64)> {
        self.conn
            .query_row(
                "SELECT embedded_sha256, sidecar_sha256, status, last_checked_mtime
                 FROM vbs_patches WHERE rel_path = ?1",
                [rel_path],
                |row| {
                    Ok((
                        row.get::<_, String>(0)?,
                        row.get::<_, Option<String>>(1)?,
                        row.get::<_, String>(2)?,
                        row.get::<_, i64>(3)?,
                    ))
                },
            )
            .ok()
    }

    /// Upsert the VBS-patch state for a table after the scanner has
    /// classified + acted on it.
    pub fn set_vbs_patch(
        &self,
        rel_path: &str,
        embedded_sha: &str,
        sidecar_sha: Option<&str>,
        status: &str,
        last_checked_mtime: i64,
    ) -> Result<()> {
        self.conn
            .execute(
                "INSERT INTO vbs_patches
                 (rel_path, embedded_sha256, sidecar_sha256, status, last_checked_mtime)
                 VALUES (?1, ?2, ?3, ?4, ?5)
                 ON CONFLICT(rel_path) DO UPDATE SET
                   embedded_sha256 = excluded.embedded_sha256,
                   sidecar_sha256  = excluded.sidecar_sha256,
                   status          = excluded.status,
                   last_checked_mtime = excluded.last_checked_mtime",
                rusqlite::params![
                    rel_path,
                    embedded_sha,
                    sidecar_sha,
                    status,
                    last_checked_mtime
                ],
            )
            .context("Failed to upsert vbs_patches row")?;
        Ok(())
    }

    /// Wipe every recorded VBS-patch state. Called by the Rebuild
    /// action so the next scan re-classifies all tables from scratch.
    // Wired in by the Rebuild button (task #9). Drop once consumed.
    #[allow(dead_code)]
    pub fn clear_vbs_patches(&self) -> Result<()> {
        self.conn
            .execute("DELETE FROM vbs_patches", [])
            .context("Failed to clear vbs_patches")?;
        Ok(())
    }

    /// Mark the wizard as completed.
    pub fn set_configured(&self) -> Result<()> {
        self.set_config("wizard_completed", "true")
    }

    /// Settings a reset keeps, because they describe the room rather than the
    /// configuration: the screen is still lying flat after a reset, and being
    /// made to straighten the wizard again every time is the opposite of what
    /// remembering the rotation was for.
    const RESET_KEEPS: [&'static str; 1] = ["ui_rotation"];

    /// Wipe every setting PinReady holds, so the next start runs the wizard
    /// from a blank slate — bar the few in [`RESET_KEEPS`].
    ///
    /// Only PinReady's own table is cleared: the scanned tables, their cached
    /// backglasses and the VPX ini are left alone. Re-answering the wizard is
    /// meant to be cheap, not to cost a rescan of the collection — and the
    /// ini gets rewritten when the wizard is validated anyway.
    ///
    /// [`RESET_KEEPS`]: Self::RESET_KEEPS
    pub fn reset_config(&self) -> Result<()> {
        let kept = Self::RESET_KEEPS
            .iter()
            .map(|k| format!("'{k}'"))
            .collect::<Vec<_>>()
            .join(", ");
        self.conn
            .execute(&format!("DELETE FROM config WHERE key NOT IN ({kept})"), [])
            .context("Failed to clear the configuration")?;
        Ok(())
    }

    /// Get a config value by key.
    pub fn get_config(&self, key: &str) -> Option<String> {
        self.conn
            .query_row("SELECT value FROM config WHERE key = ?1", [key], |row| {
                row.get(0)
            })
            .ok()
    }

    /// Set a config value.
    pub fn set_config(&self, key: &str, value: &str) -> Result<()> {
        self.conn
            .execute(
                "INSERT INTO config (key, value) VALUES (?1, ?2)
                 ON CONFLICT(key) DO UPDATE SET value = excluded.value",
                [key, value],
            )
            .context("Failed to set config value")?;
        Ok(())
    }

    /// Is auto VBS patching from jsm174/vpx-standalone-scripts enabled?
    /// Opt-in (default `false`) because the remote catalog occasionally
    /// ships patches that clobber inputs on specific tables — users
    /// should enable it deliberately once they understand the trade-off.
    pub fn jsm174_patching_enabled(&self) -> bool {
        matches!(
            self.get_config("jsm174_patching_enabled").as_deref(),
            Some("true")
        )
    }

    /// Persist the opt-in state.
    pub fn set_jsm174_patching_enabled(&self, enabled: bool) -> Result<()> {
        self.set_config(
            "jsm174_patching_enabled",
            if enabled { "true" } else { "false" },
        )
    }

    /// Catalog enrichment: VPSDB sync + VPinMediaDB media fetch at
    /// scan time. **On by default** — the first sync downloads ~7 MB
    /// of JSON plus per-matched-table media (a few MB each), but the
    /// hover-preview experience is so tied to it that the feature is
    /// effectively part of the launcher's baseline. Users on metered
    /// connections can opt out from the Tables wizard page.
    /// Optional self-hosted mirror for the VBS catalog and VPin media DB.
    /// When set (e.g. `https://pinready.syl21.org`), HTTP fetches are
    /// rewritten to:
    ///
    /// * `https://<mirror>/vpx-standalone-scripts/hashes.json` and
    ///   per-script `<path>/<file>.vbs`
    /// * `https://<mirror>/vpinmediadb/vpinmdb.json` and per-asset
    ///   `<game_id>/...`
    ///
    /// The mirror server is responsible for rewriting URLs inside the
    /// JSON manifests so they point back at itself; the client just
    /// changes the *index* endpoints. Empty string or unset = direct
    /// GitHub fetch.
    pub fn mirror_base_url(&self) -> Option<String> {
        self.get_config("mirror_base_url")
            .filter(|s| !s.trim().is_empty())
            .map(|s| s.trim().trim_end_matches('/').to_string())
    }

    pub fn set_mirror_base_url(&self, url: &str) -> Result<()> {
        self.set_config("mirror_base_url", url.trim())
    }

    /// Persisted source paths for the merge/import step in the Tables
    /// wizard page. Empty string = unset. The user-picked I/O strategy
    /// (`copy` | `move` | `symlink`) is stored under the same scheme.
    pub fn get_merge_source(&self, kind: &str) -> String {
        self.get_config(&format!("merge_source_{kind}"))
            .unwrap_or_default()
    }
    pub fn set_merge_source(&self, kind: &str, path: &str) -> Result<()> {
        self.set_config(&format!("merge_source_{kind}"), path)
    }
    pub fn get_merge_strategy(&self) -> String {
        self.get_config("merge_strategy")
            .unwrap_or_else(|| "copy".to_string())
    }
    pub fn set_merge_strategy(&self, strategy: &str) -> Result<()> {
        self.set_config("merge_strategy", strategy)
    }

    pub fn catalog_enrichment_enabled(&self) -> bool {
        // Default true: only the explicit string "false" disables it.
        !matches!(
            self.get_config("catalog_enrichment_enabled").as_deref(),
            Some("false")
        )
    }
    pub fn set_catalog_enrichment_enabled(&self, enabled: bool) -> Result<()> {
        self.set_config(
            "catalog_enrichment_enabled",
            if enabled { "true" } else { "false" },
        )
    }

    // --- vps_link (table ↔ VPS DB binding) ---

    /// What we already know about this rel_path's VPS link, if any.
    /// Returned as `(vps_id, vps_table_id, confidence, strategy,
    /// vps_updated_at, media_bg_md5, media_audio_md5, media_wheel_md5)`.
    #[allow(clippy::type_complexity)]
    pub fn get_vps_link(
        &self,
        rel_path: &str,
    ) -> Option<(
        String,
        Option<String>,
        String,
        String,
        i64,
        Option<String>,
        Option<String>,
        Option<String>,
    )> {
        self.conn
            .query_row(
                "SELECT vps_id, vps_table_id, confidence, strategy, vps_updated_at,
                        media_bg_md5, media_audio_md5, media_wheel_md5
                 FROM vps_link WHERE rel_path = ?1",
                [rel_path],
                |r| {
                    Ok((
                        r.get::<_, String>(0)?,
                        r.get::<_, Option<String>>(1)?,
                        r.get::<_, String>(2)?,
                        r.get::<_, String>(3)?,
                        r.get::<_, i64>(4)?,
                        r.get::<_, Option<String>>(5)?,
                        r.get::<_, Option<String>>(6)?,
                        r.get::<_, Option<String>>(7)?,
                    ))
                },
            )
            .ok()
    }

    /// Upsert the link record. Called both on first match and after a
    /// media refresh.
    #[allow(clippy::too_many_arguments)]
    pub fn set_vps_link(
        &self,
        rel_path: &str,
        vps_id: &str,
        vps_table_id: Option<&str>,
        confidence: &str,
        strategy: &str,
        vps_updated_at: i64,
        media_bg_md5: Option<&str>,
        media_audio_md5: Option<&str>,
        media_wheel_md5: Option<&str>,
    ) -> Result<()> {
        let now = std::time::SystemTime::now()
            .duration_since(std::time::UNIX_EPOCH)
            .map(|d| d.as_secs() as i64)
            .unwrap_or(0);
        self.conn
            .execute(
                "INSERT INTO vps_link (
                     rel_path, vps_id, vps_table_id, confidence, strategy,
                     vps_updated_at, media_bg_md5, media_audio_md5, media_wheel_md5,
                     last_synced_at
                 )
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)
                 ON CONFLICT(rel_path) DO UPDATE
                 SET vps_id          = excluded.vps_id,
                     vps_table_id    = excluded.vps_table_id,
                     confidence      = excluded.confidence,
                     strategy        = excluded.strategy,
                     vps_updated_at  = excluded.vps_updated_at,
                     media_bg_md5    = COALESCE(excluded.media_bg_md5,    media_bg_md5),
                     media_audio_md5 = COALESCE(excluded.media_audio_md5, media_audio_md5),
                     media_wheel_md5 = COALESCE(excluded.media_wheel_md5, media_wheel_md5),
                     last_synced_at  = excluded.last_synced_at",
                rusqlite::params![
                    rel_path,
                    vps_id,
                    vps_table_id,
                    confidence,
                    strategy,
                    vps_updated_at,
                    media_bg_md5,
                    media_audio_md5,
                    media_wheel_md5,
                    now,
                ],
            )
            .context("Failed to upsert vps_link")?;
        Ok(())
    }

    /// Clear the cached `media_*_md5` columns on a vps_link row.
    /// Called after a re-match flips `vps_id` so the next media-fetch
    /// pass treats every asset as never-downloaded and overwrites the
    /// stale `medias/bg.png` / `medias/audio.mp3` files inherited
    /// from the wrong game.
    #[allow(dead_code)] // pre-v0.12 helper; the new scan_worker stops at delete_backglass + on-disk file removal, so the md5 columns are never read
    pub fn clear_link_media_md5s(&self, rel_path: &str) -> Result<()> {
        self.conn
            .execute(
                "UPDATE vps_link
                 SET media_bg_md5    = NULL,
                     media_audio_md5 = NULL,
                     media_wheel_md5 = NULL
                 WHERE rel_path = ?1",
                [rel_path],
            )
            .context("Failed to clear link media md5s")?;
        Ok(())
    }

    /// Update only the `update_available` flag on an existing vps_link
    /// row. No-op if the row doesn't exist (the catalog worker only
    /// sets this after a successful match).
    pub fn set_update_available(&self, rel_path: &str, available: bool) -> Result<()> {
        self.conn
            .execute(
                "UPDATE vps_link SET update_available = ?2 WHERE rel_path = ?1",
                rusqlite::params![rel_path, if available { 1 } else { 0 }],
            )
            .context("Failed to update update_available")?;
        Ok(())
    }

    /// Read the `update_available` flag for one rel_path. Returns `false`
    /// if no link exists or the column wasn't populated yet.
    pub fn get_update_available(&self, rel_path: &str) -> bool {
        self.conn
            .query_row(
                "SELECT update_available FROM vps_link WHERE rel_path = ?1",
                [rel_path],
                |row| row.get::<_, i64>(0),
            )
            .map(|v| v != 0)
            .unwrap_or(false)
    }

    /// Get the tables root directory.
    pub fn get_tables_dir(&self) -> Option<String> {
        self.get_config("tables_dir")
    }

    /// Set the tables root directory.
    pub fn set_tables_dir(&self, path: &str) -> Result<()> {
        self.set_config("tables_dir", path)
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    fn temp_db() -> Database {
        // Use in-memory-like temp file for isolation
        let dir = tempfile::tempdir().unwrap();
        let path = dir.path().join("test.db");
        let db = Database::open(Some(&path)).unwrap();
        // Keep dir alive by leaking — tests are short-lived
        std::mem::forget(dir);
        db
    }

    #[test]
    fn open_creates_schema() {
        let db = temp_db();
        // Tables should exist — insert should work
        db.set_config("test_key", "test_value").unwrap();
    }

    #[test]
    fn get_config_missing_returns_none() {
        let db = temp_db();
        assert_eq!(db.get_config("nonexistent"), None);
    }

    #[test]
    fn set_and_get_config() {
        let db = temp_db();
        db.set_config("my_key", "my_value").unwrap();
        assert_eq!(db.get_config("my_key"), Some("my_value".to_string()));
    }

    #[test]
    fn set_config_upserts() {
        let db = temp_db();
        db.set_config("key", "v1").unwrap();
        db.set_config("key", "v2").unwrap();
        assert_eq!(db.get_config("key"), Some("v2".to_string()));
    }

    #[test]
    fn set_configured() {
        let db = temp_db();
        db.set_configured().unwrap();
        assert_eq!(db.get_config("wizard_completed"), Some("true".to_string()));
    }

    #[test]
    fn tables_dir_roundtrip() {
        let db = temp_db();
        assert_eq!(db.get_tables_dir(), None);
        db.set_tables_dir("/home/user/tables").unwrap();
        assert_eq!(db.get_tables_dir(), Some("/home/user/tables".to_string()));
    }

    #[test]
    fn multiple_config_keys_independent() {
        let db = temp_db();
        db.set_config("a", "1").unwrap();
        db.set_config("b", "2").unwrap();
        assert_eq!(db.get_config("a"), Some("1".to_string()));
        assert_eq!(db.get_config("b"), Some("2".to_string()));
    }

    #[test]
    fn vbs_catalog_roundtrip() {
        let db = temp_db();
        assert!(db.get_vbs_catalog().is_none());
        db.set_vbs_catalog("abc123", "{\"files\": []}").unwrap();
        let (sha, json) = db.get_vbs_catalog().unwrap();
        assert_eq!(sha, "abc123");
        assert_eq!(json, "{\"files\": []}");
    }

    #[test]
    fn vbs_catalog_replaces_single_row() {
        let db = temp_db();
        db.set_vbs_catalog("sha1", "{\"v\":1}").unwrap();
        db.set_vbs_catalog("sha2", "{\"v\":2}").unwrap();
        let (sha, json) = db.get_vbs_catalog().unwrap();
        assert_eq!(sha, "sha2");
        assert_eq!(json, "{\"v\":2}");
        // Ensure only one row exists
        let count: i64 = db
            .conn
            .query_row("SELECT COUNT(*) FROM vbs_catalog", [], |r| r.get(0))
            .unwrap();
        assert_eq!(count, 1);
    }

    #[test]
    fn vbs_patch_roundtrip_with_sidecar() {
        let db = temp_db();
        db.set_vbs_patch(
            "Totan/Totan.vpx",
            "embed_sha_hex",
            Some("sidecar_sha_hex"),
            "Applied",
            1_700_000_000,
        )
        .unwrap();
        let (emb, side, status, mtime) = db.get_vbs_patch("Totan/Totan.vpx").unwrap();
        assert_eq!(emb, "embed_sha_hex");
        assert_eq!(side, Some("sidecar_sha_hex".to_string()));
        assert_eq!(status, "Applied");
        assert_eq!(mtime, 1_700_000_000);
    }

    #[test]
    fn vbs_patch_roundtrip_no_sidecar() {
        let db = temp_db();
        db.set_vbs_patch("Foo/Foo.vpx", "emb", None, "NotInCatalog", 0)
            .unwrap();
        let (_, side, status, _) = db.get_vbs_patch("Foo/Foo.vpx").unwrap();
        assert_eq!(side, None);
        assert_eq!(status, "NotInCatalog");
    }

    #[test]
    fn vbs_patch_upserts_on_rescan() {
        let db = temp_db();
        db.set_vbs_patch("t/t.vpx", "emb1", None, "NotInCatalog", 100)
            .unwrap();
        db.set_vbs_patch("t/t.vpx", "emb2", Some("sc"), "Applied", 200)
            .unwrap();
        let (emb, side, status, mtime) = db.get_vbs_patch("t/t.vpx").unwrap();
        assert_eq!(emb, "emb2");
        assert_eq!(side, Some("sc".to_string()));
        assert_eq!(status, "Applied");
        assert_eq!(mtime, 200);
    }

    #[test]
    fn clear_vbs_patches_wipes_all() {
        let db = temp_db();
        db.set_vbs_patch("a.vpx", "e", None, "s", 1).unwrap();
        db.set_vbs_patch("b.vpx", "e", None, "s", 1).unwrap();
        db.clear_vbs_patches().unwrap();
        assert!(db.get_vbs_patch("a.vpx").is_none());
        assert!(db.get_vbs_patch("b.vpx").is_none());
    }

    #[test]
    fn a_reset_forgets_the_configuration_but_not_the_screen_orientation() {
        let db = temp_db();
        db.set_config("ui_rotation", "90").unwrap();
        db.set_config("wizard_completed", "true").unwrap();
        db.set_config("tables_dir", "/somewhere").unwrap();

        db.reset_config().unwrap();

        // The wizard starts over…
        assert_eq!(db.get_config("wizard_completed"), None);
        assert_eq!(db.get_config("tables_dir"), None);
        // …but the playfield is still lying flat, so the wizard comes back
        // the right way up instead of sideways.
        assert_eq!(db.get_config("ui_rotation").as_deref(), Some("90"));
    }

    #[test]
    fn backglass_thumbnail_round_trip() {
        let db = temp_db();
        db.set_backglass("A/a.vpx", b"full-a", Some(b"thumb-a"), 10)
            .unwrap();
        db.set_backglass("B/b.vpx", b"full-b", None, 20).unwrap();
        let all = db.get_all_thumbs();
        assert_eq!(all.len(), 1);
        assert_eq!(all["A/a.vpx"], (b"thumb-a".to_vec(), 10));
        // A row without a thumbnail is listed for the background fill, with
        // the mtime it was cached at; its full picture is still readable.
        assert_eq!(db.list_without_thumb(), vec![("B/b.vpx".to_string(), 20)]);
        assert_eq!(db.get_backglass("B/b.vpx"), Some((b"full-b".to_vec(), 20)));
        db.set_thumb("B/b.vpx", b"thumb-b").unwrap();
        assert!(db.list_without_thumb().is_empty());
        assert_eq!(db.get_all_thumbs().len(), 2);
        // Re-extraction replaces both.
        db.set_backglass("A/a.vpx", b"full-a2", Some(b"thumb-a2"), 30)
            .unwrap();
        assert_eq!(db.get_all_thumbs()["A/a.vpx"], (b"thumb-a2".to_vec(), 30));
    }

    #[test]
    fn old_backglass_table_gains_the_thumb_column() {
        let db = temp_db();
        // Rebuild the v4 shape (no `thumb`), with a row, then migrate again.
        db.conn.execute("DROP TABLE backglass", []).unwrap();
        db.conn
            .execute(
                "CREATE TABLE backglass (
                    rel_path TEXT PRIMARY KEY,
                    image BLOB NOT NULL,
                    cached_at_mtime INTEGER NOT NULL DEFAULT 0
                )",
                [],
            )
            .unwrap();
        db.conn
            .execute(
                "INSERT INTO backglass (rel_path, image, cached_at_mtime) VALUES ('x.vpx', x'01', 5)",
                [],
            )
            .unwrap();
        db.migrate().unwrap();
        assert_eq!(db.list_without_thumb(), vec![("x.vpx".to_string(), 5)]);
    }
}