pinready 0.27.5

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,
}

/// What the player recorded about one table (`table_user`).
#[derive(Debug, Clone, Default, PartialEq, Eq)]
pub struct UserTable {
    pub favorite: bool,
    pub favorite_at: Option<i64>,
    /// Tenths of a star: 0 = unrated, 50 = five stars (3.2 stars is 32).
    pub rating_tenths: u8,
    pub rated_at: Option<i64>,
    pub last_run: Option<i64>,
    pub start_count: u32,
    /// Total play time in seconds.
    pub run_time: u32,
}

/// One row of `backglass_meta` (see the table's comment).
#[derive(Debug, Clone)]
pub struct BackglassMeta {
    /// Source mtime (Unix seconds) the row was made for.
    pub mtime: i64,
    /// Grid thumbnail, `None` for rows cached before thumbnails existed.
    pub thumb: Option<Vec<u8>>,
    /// The table was scanned and has no backglass.
    pub no_image: bool,
}

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

            -- Small side table of the backglass cache: everything the
            -- launcher needs at start-up WITHOUT touching `backglass.image`.
            -- SQLite stores a row's columns in order, so reading any column
            -- that follows the (1-2 MB) `image` blob walks all of its
            -- overflow pages: on a 3.5 GB cache that made the start-up
            -- queries read most of the file. `no_image = 1` records that
            -- the table was scanned and has no backglass at all (negative
            -- cache), valid while `cached_at_mtime` is not older than the
            -- table's sources.
            CREATE TABLE IF NOT EXISTS backglass_meta (
                rel_path        TEXT    PRIMARY KEY,
                cached_at_mtime INTEGER NOT NULL DEFAULT 0,
                thumb           BLOB,
                no_image        INTEGER NOT NULL DEFAULT 0
            );

            -- What the player says about a table, kept per `.vpx` path like
            -- the other caches. `rating` is in tenths of a star: 0 (unrated)
            -- to 50 (five stars), so 3.2 stars is 32; VPinFE / VPinPlay use
            -- whole stars 0 to 5, which is this value rounded. The dates are
            -- Unix seconds. `last_run`, `start_count` and
            -- `run_time` (seconds) are filled by the launcher itself.
            CREATE TABLE IF NOT EXISTS table_user (
                rel_path    TEXT    PRIMARY KEY,
                favorite    INTEGER NOT NULL DEFAULT 0,
                favorite_at INTEGER,
                rating      INTEGER NOT NULL DEFAULT 0,
                rated_at    INTEGER,
                last_run    INTEGER,
                start_count INTEGER NOT NULL DEFAULT 0,
                run_time    INTEGER NOT NULL DEFAULT 0
            );

            -- Community ratings downloaded from VPinPlay, by VPS id: average
            -- (1 to 5) and number of ratings. Replaced as a whole at each
            -- refresh.
            CREATE TABLE IF NOT EXISTS community_rating (
                vps_id TEXT PRIMARY KEY,
                avg    REAL    NOT NULL,
                count  INTEGER NOT NULL
            );

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

            -- Per-table state of the DMD area check of the .directb2s
            -- (see b2s_dmd.rs). Keyed on `rel_path` like `vbs_patches`.
            -- `status` is NotNeeded / Applied / NoHole / Failed;
            -- `last_checked_mtime` is the mtime of the .directb2s after the
            -- check, so the file is read again only when it changes.
            CREATE TABLE IF NOT EXISTS b2s_dmd (
                rel_path           TEXT    PRIMARY KEY,
                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")?;
        // v5 → v6: fill the side table once from the existing cache (one
        // pass over the big table, a few seconds on a large collection).
        let meta_empty: bool = self
            .conn
            .query_row(
                "SELECT NOT EXISTS (SELECT 1 FROM backglass_meta)",
                [],
                |r| r.get(0),
            )
            .unwrap_or(false);
        if meta_empty {
            let n = self
                .conn
                .execute(
                    "INSERT OR IGNORE INTO backglass_meta (rel_path, cached_at_mtime, thumb)
                     SELECT rel_path, cached_at_mtime, thumb FROM backglass",
                    [],
                )
                .context("Failed to fill backglass_meta")?;
            if n > 0 {
                log::info!("backglass_meta filled from the cache ({n} rows)");
            }
        }
        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<()> {
        // `thumb` lives in `backglass_meta` only (see its comment); the
        // legacy `backglass.thumb` column is left alone.
        let tx = self.conn.unchecked_transaction()?;
        tx.execute(
            "INSERT INTO backglass (rel_path, image, cached_at_mtime)
             VALUES (?1, ?2, ?3)
             ON CONFLICT(rel_path) DO UPDATE
             SET image = excluded.image,
                 cached_at_mtime = excluded.cached_at_mtime",
            rusqlite::params![rel_path, image, source_mtime],
        )
        .context("Failed to insert backglass row")?;
        tx.execute(
            "INSERT INTO backglass_meta (rel_path, cached_at_mtime, thumb, no_image)
             VALUES (?1, ?2, ?3, 0)
             ON CONFLICT(rel_path) DO UPDATE
             SET cached_at_mtime = excluded.cached_at_mtime,
                 thumb = excluded.thumb,
                 no_image = 0",
            rusqlite::params![rel_path, source_mtime, thumb],
        )
        .context("Failed to insert backglass_meta row")?;
        tx.commit()?;
        Ok(())
    }

    /// Record that a table was scanned and has no backglass at all, as of
    /// `source_mtime`. Drops any stale cached picture for it. The scanner
    /// skips the table until one of its source files changes.
    pub fn set_no_backglass(&self, rel_path: &str, source_mtime: i64) -> Result<()> {
        let tx = self.conn.unchecked_transaction()?;
        tx.execute("DELETE FROM backglass WHERE rel_path = ?1", [rel_path])?;
        tx.execute(
            "INSERT INTO backglass_meta (rel_path, cached_at_mtime, thumb, no_image)
             VALUES (?1, ?2, NULL, 1)
             ON CONFLICT(rel_path) DO UPDATE
             SET cached_at_mtime = excluded.cached_at_mtime,
                 thumb = NULL,
                 no_image = 1",
            rusqlite::params![rel_path, source_mtime],
        )
        .context("Failed to record a table without backglass")?;
        tx.commit()?;
        Ok(())
    }

    /// What the player recorded about every table, in one query.
    pub fn get_user_tables(&self) -> std::collections::HashMap<String, UserTable> {
        let mut out = std::collections::HashMap::new();
        let Ok(mut stmt) = self.conn.prepare(
            "SELECT rel_path, favorite, favorite_at, rating, rated_at, last_run,
                    start_count, run_time FROM table_user",
        ) else {
            return out;
        };
        let rows = stmt.query_map([], |r| {
            Ok((
                r.get::<_, String>(0)?,
                UserTable {
                    favorite: r.get::<_, i64>(1)? != 0,
                    favorite_at: r.get(2)?,
                    rating_tenths: r.get::<_, i64>(3)?.clamp(0, 50) as u8,
                    rated_at: r.get(4)?,
                    last_run: r.get(5)?,
                    start_count: r.get::<_, i64>(6)?.max(0) as u32,
                    run_time: r.get::<_, i64>(7)?.max(0) as u32,
                },
            ))
        });
        if let Ok(rows) = rows {
            out.extend(rows.flatten());
        }
        out
    }

    /// Mark or unmark a table as a favourite (`now` is Unix seconds).
    pub fn set_favorite(&self, rel_path: &str, favorite: bool, now: i64) -> Result<()> {
        self.conn
            .execute(
                "INSERT INTO table_user (rel_path, favorite, favorite_at) VALUES (?1, ?2, ?3)
                 ON CONFLICT(rel_path) DO UPDATE
                 SET favorite = excluded.favorite, favorite_at = excluded.favorite_at",
                rusqlite::params![rel_path, favorite as i64, now],
            )
            .context("Failed to store the favourite flag")?;
        Ok(())
    }

    /// Rate a table in tenths of a star, 0 (clear) to 50 (`now` is Unix
    /// seconds).
    pub fn set_rating(&self, rel_path: &str, tenths: u8, now: i64) -> Result<()> {
        self.conn
            .execute(
                "INSERT INTO table_user (rel_path, rating, rated_at) VALUES (?1, ?2, ?3)
                 ON CONFLICT(rel_path) DO UPDATE
                 SET rating = excluded.rating, rated_at = excluded.rated_at",
                rusqlite::params![rel_path, tenths.min(50) as i64, now],
            )
            .context("Failed to store the rating")?;
        Ok(())
    }

    /// A table was started: count it and remember when.
    pub fn record_start(&self, rel_path: &str, now: i64) -> Result<()> {
        self.conn
            .execute(
                "INSERT INTO table_user (rel_path, last_run, start_count) VALUES (?1, ?2, 1)
                 ON CONFLICT(rel_path) DO UPDATE
                 SET last_run = excluded.last_run, start_count = start_count + 1",
                rusqlite::params![rel_path, now],
            )
            .context("Failed to record the start")?;
        Ok(())
    }

    /// Add the length of a finished session (seconds).
    pub fn add_run_time(&self, rel_path: &str, seconds: u32) -> Result<()> {
        self.conn
            .execute(
                "INSERT INTO table_user (rel_path, run_time) VALUES (?1, ?2)
                 ON CONFLICT(rel_path) DO UPDATE SET run_time = run_time + excluded.run_time",
                rusqlite::params![rel_path, seconds as i64],
            )
            .context("Failed to record the play time")?;
        Ok(())
    }

    /// The community ratings kept from the last VPinPlay download.
    pub fn get_community_ratings(
        &self,
    ) -> std::collections::HashMap<String, crate::vpinplay::CommunityRating> {
        let mut out = std::collections::HashMap::new();
        let Ok(mut stmt) = self
            .conn
            .prepare("SELECT vps_id, avg, count FROM community_rating")
        else {
            return out;
        };
        let rows = stmt.query_map([], |r| {
            Ok((
                r.get::<_, String>(0)?,
                crate::vpinplay::CommunityRating {
                    avg: r.get::<_, f64>(1)? as f32,
                    count: r.get::<_, i64>(2)?.max(0) as u32,
                },
            ))
        });
        if let Ok(rows) = rows {
            out.extend(rows.flatten());
        }
        out
    }

    /// Replace the kept community ratings (`fetched_at` in Unix seconds).
    pub fn replace_community_ratings(
        &self,
        ratings: &std::collections::HashMap<String, crate::vpinplay::CommunityRating>,
        fetched_at: i64,
    ) -> Result<()> {
        let tx = self.conn.unchecked_transaction()?;
        tx.execute("DELETE FROM community_rating", [])?;
        {
            let mut stmt = tx
                .prepare("INSERT INTO community_rating (vps_id, avg, count) VALUES (?1, ?2, ?3)")?;
            for (id, r) in ratings {
                stmt.execute(rusqlite::params![id, r.avg as f64, r.count as i64])?;
            }
        }
        tx.execute(
            "INSERT INTO config (key, value) VALUES (?1, ?2)
             ON CONFLICT(key) DO UPDATE SET value = excluded.value",
            rusqlite::params![crate::vpinplay::KEY_RATINGS_AT, fetched_at.to_string()],
        )?;
        tx.commit()?;
        Ok(())
    }

    /// The whole backglass cache index in one query, without touching the
    /// pictures: `rel_path -> BackglassMeta`.
    pub fn get_backglass_meta(&self) -> std::collections::HashMap<String, BackglassMeta> {
        let mut out = std::collections::HashMap::new();
        let Ok(mut stmt) = self
            .conn
            .prepare("SELECT rel_path, cached_at_mtime, thumb, no_image FROM backglass_meta")
        else {
            return out;
        };
        let rows = stmt.query_map([], |row| {
            Ok((
                row.get::<_, String>(0)?,
                BackglassMeta {
                    mtime: row.get::<_, i64>(1)?,
                    thumb: row.get::<_, Option<Vec<u8>>>(2)?,
                    no_image: row.get::<_, i64>(3)? != 0,
                },
            ))
        });
        if let Ok(rows) = rows {
            out.extend(rows.flatten());
        }
        out
    }

    /// 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_meta 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.
    #[cfg(test)]
    pub fn list_without_thumb(&self) -> Vec<(String, i64)> {
        let Ok(mut stmt) = self.conn.prepare(
            "SELECT rel_path, cached_at_mtime FROM backglass_meta
                 WHERE thumb IS NULL AND no_image = 0",
        ) 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_meta 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")?;
        self.conn
            .execute("DELETE FROM backglass_meta WHERE rel_path = ?1", [rel_path])
            .context("Failed to delete backglass_meta row")?;
        Ok(())
    }

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

    // --- b2s_dmd (per-table DMD area check) ---

    /// `(status, last_checked_mtime)` recorded for a table's .directb2s.
    /// Every b2s_dmd record in one query: `rel_path -> last_checked_mtime`.
    pub fn get_b2s_dmd_mtimes(&self) -> std::collections::HashMap<String, i64> {
        self.two_column_map("SELECT rel_path, last_checked_mtime FROM b2s_dmd")
    }

    /// Every VBS-patch record in one query: `rel_path -> last_checked_mtime`.
    pub fn get_vbs_patch_mtimes(&self) -> std::collections::HashMap<String, i64> {
        self.two_column_map("SELECT rel_path, last_checked_mtime FROM vbs_patches")
    }

    fn two_column_map(&self, sql: &str) -> std::collections::HashMap<String, i64> {
        let mut out = std::collections::HashMap::new();
        let Ok(mut stmt) = self.conn.prepare(sql) else {
            return out;
        };
        if let Ok(rows) = stmt.query_map([], |r| Ok((r.get::<_, String>(0)?, r.get::<_, i64>(1)?)))
        {
            out.extend(rows.flatten());
        }
        out
    }

    /// VPS link of every table in one query: `rel_path -> (vps_id,
    /// update_available, reliable)`; `reliable` is false for a low-confidence
    /// match, which must not be shared with anyone.
    pub fn get_vps_overview(&self) -> std::collections::HashMap<String, (String, bool, bool)> {
        let mut out = std::collections::HashMap::new();
        let Ok(mut stmt) = self
            .conn
            .prepare("SELECT rel_path, vps_id, update_available, confidence FROM vps_link")
        else {
            return out;
        };
        let rows = stmt.query_map([], |r| {
            Ok((
                r.get::<_, String>(0)?,
                (
                    r.get::<_, String>(1)?,
                    r.get::<_, i64>(2)? != 0,
                    !r.get::<_, String>(3)?.eq_ignore_ascii_case("low"),
                ),
            ))
        });
        if let Ok(rows) = rows {
            out.extend(rows.flatten());
        }
        out
    }

    #[cfg(test)]
    pub fn get_b2s_dmd(&self, rel_path: &str) -> Option<(String, i64)> {
        self.conn
            .query_row(
                "SELECT status, last_checked_mtime FROM b2s_dmd WHERE rel_path = ?1",
                [rel_path],
                |r| Ok((r.get(0)?, r.get(1)?)),
            )
            .ok()
    }

    pub fn set_b2s_dmd(&self, rel_path: &str, status: &str, mtime: i64) -> Result<()> {
        self.conn
            .execute(
                "INSERT INTO b2s_dmd (rel_path, status, last_checked_mtime)
                 VALUES (?1, ?2, ?3)
                 ON CONFLICT(rel_path) DO UPDATE SET
                     status = excluded.status,
                     last_checked_mtime = excluded.last_checked_mtime",
                rusqlite::params![rel_path, status, mtime],
            )
            .context("Failed to upsert b2s_dmd row")?;
        Ok(())
    }

    /// Forget every check, so the next scan reads each .directb2s again.
    pub fn clear_b2s_dmd(&self) -> Result<()> {
        self.conn
            .execute("DELETE FROM b2s_dmd", [])
            .context("Failed to clear b2s_dmd")?;
        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).
    #[cfg(test)]
    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" },
        )
    }

    /// Add the DMD area to the `.directb2s` files that lack it (see
    /// `b2s_dmd.rs`). **On by default**: without it the DMD of those tables
    /// stays black in the backglass frame. The original file is kept.
    pub fn b2s_dmd_enabled(&self) -> bool {
        !matches!(self.get_config("b2s_dmd_enabled").as_deref(), Some("false"))
    }

    pub fn set_b2s_dmd_enabled(&self, enabled: bool) -> Result<()> {
        self.set_config("b2s_dmd_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(())
    }

    /// 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 b2s_dmd_is_on_by_default_and_roundtrips() {
        let db = Database::open(None).unwrap();
        assert!(db.b2s_dmd_enabled());
        db.set_b2s_dmd_enabled(false).unwrap();
        assert!(!db.b2s_dmd_enabled());
        db.set_b2s_dmd_enabled(true).unwrap();
        assert!(db.b2s_dmd_enabled());

        assert!(db.get_b2s_dmd("t/t.vpx").is_none());
        db.set_b2s_dmd("t/t.vpx", "Applied", 10).unwrap();
        db.set_b2s_dmd("t/t.vpx", "NotNeeded", 20).unwrap();
        assert_eq!(db.get_b2s_dmd("t/t.vpx"), Some(("NotNeeded".into(), 20)));
        db.clear_b2s_dmd().unwrap();
        assert!(db.get_b2s_dmd("t/t.vpx").is_none());
    }

    #[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 favourites_ratings_and_play_stats_are_kept_per_table() {
        let db = temp_db();
        assert!(db.get_user_tables().is_empty());
        db.set_favorite("A/a.vpx", true, 100).unwrap();
        db.set_rating("A/a.vpx", 32, 110).unwrap();
        db.record_start("A/a.vpx", 120).unwrap();
        db.record_start("A/a.vpx", 130).unwrap();
        db.add_run_time("A/a.vpx", 600).unwrap();
        db.set_rating("B/b.vpx", 90, 140).unwrap(); // capped at 5 stars
        let all = db.get_user_tables();
        assert_eq!(
            all["A/a.vpx"],
            UserTable {
                favorite: true,
                favorite_at: Some(100),
                rating_tenths: 32,
                rated_at: Some(110),
                last_run: Some(130),
                start_count: 2,
                run_time: 600,
            }
        );
        assert_eq!(all["B/b.vpx"].rating_tenths, 50);
        assert!(!all["B/b.vpx"].favorite);
        // Un-favouriting and clearing keep the rest.
        db.set_favorite("A/a.vpx", false, 200).unwrap();
        db.set_rating("A/a.vpx", 0, 210).unwrap();
        let a = &db.get_user_tables()["A/a.vpx"];
        assert!(!a.favorite && a.rating_tenths == 0 && a.start_count == 2);
        assert_eq!(a.rated_at, Some(210));
    }

    #[test]
    fn community_ratings_are_replaced_as_a_whole() {
        use crate::vpinplay::CommunityRating;
        let db = temp_db();
        let mut first = std::collections::HashMap::new();
        first.insert("a".to_string(), CommunityRating { avg: 4.5, count: 3 });
        first.insert("b".to_string(), CommunityRating { avg: 2.0, count: 1 });
        db.replace_community_ratings(&first, 100).unwrap();
        assert_eq!(db.get_community_ratings().len(), 2);
        let mut second = std::collections::HashMap::new();
        second.insert("c".to_string(), CommunityRating { avg: 3.0, count: 9 });
        db.replace_community_ratings(&second, 200).unwrap();
        let all = db.get_community_ratings();
        assert_eq!(all.len(), 1);
        assert_eq!(all["c"], CommunityRating { avg: 3.0, count: 9 });
        assert_eq!(
            db.get_config(crate::vpinplay::KEY_RATINGS_AT).as_deref(),
            Some("200")
        );
    }

    #[test]
    fn a_table_without_backglass_is_remembered() {
        let db = temp_db();
        db.set_no_backglass("N/n.vpx", 7).unwrap();
        let meta = db.get_backglass_meta();
        let n = &meta["N/n.vpx"];
        assert!(n.no_image && n.thumb.is_none() && n.mtime == 7);
        // It is not a legacy row waiting for a thumbnail.
        assert!(db.list_without_thumb().is_empty());
        assert!(db.get_all_thumbs().is_empty());
        // A picture found later replaces the record.
        db.set_backglass("N/n.vpx", b"full", Some(b"t"), 9).unwrap();
        let meta = db.get_backglass_meta();
        assert!(!meta["N/n.vpx"].no_image);
        assert_eq!(meta["N/n.vpx"].thumb.as_deref(), Some(&b"t"[..]));
        // And going back to "none" drops the stale picture.
        db.set_no_backglass("N/n.vpx", 11).unwrap();
        assert_eq!(db.get_backglass("N/n.vpx"), None);
    }

    #[test]
    fn the_meta_table_is_filled_from_an_existing_cache() {
        let db = temp_db();
        db.conn.execute("DELETE FROM backglass_meta", []).unwrap();
        db.conn
            .execute(
                "INSERT INTO backglass (rel_path, image, cached_at_mtime, thumb) VALUES ('o.vpx', x'0102', 3, x'09')",
                [],
            )
            .unwrap();
        db.migrate().unwrap();
        db.init_schema().unwrap();
        let meta = db.get_backglass_meta();
        assert_eq!(meta["o.vpx"].mtime, 3);
        assert_eq!(meta["o.vpx"].thumb.as_deref(), Some(&[9u8][..]));
        assert!(!meta["o.vpx"].no_image);
    }

    #[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();
        db.init_schema().unwrap();
        assert_eq!(db.list_without_thumb(), vec![("x.vpx".to_string(), 5)]);
    }
}