use std::collections::HashSet;
use std::path::Path;
use std::time::Duration;

use rusqlite::{Connection, params};

use crate::migrations::{MIGRATIONS, Migration};

pub(crate) const SQLITE_AUTO_VACUUM_FULL: i64 = 1;
pub(crate) const SQLITE_AUTO_VACUUM_INCREMENTAL: i64 = 2;
const SQLITE_BUSY_TIMEOUT: Duration = Duration::from_secs(30);
const INCREMENTAL_VACUUM_PAGES_AFTER_DELETE: i64 = 4096;

mod repository;
pub use repository::is_duplicate_repository_path_error;

mod settings;
pub use settings::RepositoryMcpServer;

mod scm;
pub use scm::{RepoScmListCacheRow, ScmStatusCacheRow, WorkspaceScmLinkRow};

mod scheduling;

mod terminal;
pub use terminal::CLAUDETTE_TERMINAL_TITLE;

mod remote;

mod checkpoint;
pub(crate) use checkpoint::sha256_hex;

mod chat;

mod workspace;
pub use workspace::WORKSPACE_ORDER_MODE_PREFIX;

mod commands;

mod usage;

#[cfg(test)]
pub(crate) mod test_support;

pub struct Database {
    conn: Connection,
}

impl Database {
    /// Open (or create) the SQLite database at `path` and run any
    /// pending migrations. Wrapped in a `claudette::db` span at TRACE
    /// because Tauri commands open a fresh `Connection` per call —
    /// at INFO/DEBUG this would dominate the file log. Migrations get
    /// their own DEBUG span ([`Database::migrate`]) since they only
    /// fire on first call.
    #[tracing::instrument(level = "trace", target = "claudette::db", skip_all, fields(path = %path.display()))]
    pub fn open(path: &Path) -> Result<Self, rusqlite::Error> {
        if let Some(parent) = path.parent() {
            std::fs::create_dir_all(parent).map_err(|e| {
                rusqlite::Error::InvalidPath(
                    format!("Failed to create database directory: {e}").into(),
                )
            })?;
        }
        let is_fresh_file = is_fresh_database_file(path);
        let conn = Connection::open(path)?;
        Self::configure_connection(&conn, is_fresh_file)?;
        let db = Self { conn };
        db.migrate()?;
        Ok(db)
    }

    #[allow(dead_code)]
    pub fn open_in_memory() -> Result<Self, rusqlite::Error> {
        let conn = Connection::open_in_memory()?;
        Self::configure_connection(&conn, true)?;
        let db = Self { conn };
        db.migrate()?;
        Ok(db)
    }

    fn configure_connection(
        conn: &Connection,
        enable_incremental_auto_vacuum_before_schema: bool,
    ) -> Result<(), rusqlite::Error> {
        conn.busy_timeout(SQLITE_BUSY_TIMEOUT)?;
        if enable_incremental_auto_vacuum_before_schema {
            conn.execute_batch("PRAGMA auto_vacuum=INCREMENTAL;")?;
        }
        conn.execute_batch("PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;")
    }

    /// Execute raw SQL. Intended for test setup only.
    #[cfg(test)]
    pub fn execute_batch(&self, sql: &str) -> Result<(), rusqlite::Error> {
        self.conn.execute_batch(sql)
    }

    /// Re-run migrations. Intended for test setup only — lets a test rewind
    /// the DB to an older `user_version` and exercise a specific migration.
    #[cfg(test)]
    pub fn run_migrations_for_test(&self) -> Result<(), rusqlite::Error> {
        self.migrate()
    }

    #[tracing::instrument(
        level = "debug",
        target = "claudette::db",
        skip_all,
        fields(migration_count = MIGRATIONS.len()),
    )]
    fn migrate(&self) -> Result<(), rusqlite::Error> {
        self.bootstrap_and_backfill(MIGRATIONS)?;
        Self::run_migrations(&self.conn, MIGRATIONS)?;
        self.heal_orphaned_sessions()
    }

    /// Ensure freed pages can be returned to the OS.
    ///
    /// SQLite only honors `PRAGMA auto_vacuum` immediately before any tables
    /// exist. Existing field databases therefore need a one-time `VACUUM`
    /// after flipping the pragma; this must live outside the SQL migration
    /// runner because migrations execute inside transactions and `VACUUM`
    /// is prohibited there.
    pub(crate) fn ensure_incremental_auto_vacuum(&self) -> Result<bool, rusqlite::Error> {
        let mode = self.auto_vacuum_mode()?;
        if mode == SQLITE_AUTO_VACUUM_INCREMENTAL {
            return Ok(false);
        }
        if mode == SQLITE_AUTO_VACUUM_FULL {
            // FULL already returns freed pages to the OS on each commit. Avoid
            // rewriting a user/dev DB just to change to incremental mode.
            return Ok(false);
        }

        self.conn.execute_batch("PRAGMA auto_vacuum=INCREMENTAL;")?;
        tracing::info!(
            target: "claudette::db",
            "converting database to incremental auto-vacuum; running one-time VACUUM"
        );
        self.conn.execute_batch("VACUUM;")?;
        Ok(true)
    }

    pub(crate) fn auto_vacuum_mode(&self) -> Result<i64, rusqlite::Error> {
        self.conn
            .query_row("PRAGMA auto_vacuum", [], |row| row.get(0))
    }

    pub(crate) fn freelist_page_count(&self) -> Result<i64, rusqlite::Error> {
        self.conn
            .query_row("PRAGMA freelist_count", [], |row| row.get(0))
    }

    pub(crate) fn vacuum_for_space_reclaim(&self) -> Result<(), rusqlite::Error> {
        self.conn.execute_batch("VACUUM;")
    }

    pub(crate) fn incremental_vacuum_for_space_reclaim(
        &self,
        max_pages: i64,
    ) -> Result<i64, rusqlite::Error> {
        if max_pages <= 0 {
            return Ok(0);
        }
        let before = self.freelist_page_count()?;
        self.incremental_vacuum(max_pages)?;
        let after = self.freelist_page_count()?;
        Ok(before.saturating_sub(after))
    }

    fn best_effort_incremental_vacuum_after_delete(&self, rows_deleted: usize) {
        if rows_deleted > 0 {
            self.best_effort_incremental_vacuum(INCREMENTAL_VACUUM_PAGES_AFTER_DELETE);
        }
    }

    /// Drop `checkpoint_blobs` rows that no `checkpoint_files` row references.
    /// Called after delete paths that cascade through `checkpoint_files` via
    /// `conversation_checkpoints` — the FK chain reclaims the reference rows
    /// but not the blob bytes themselves. Best-effort: failure is logged and
    /// swallowed so user-facing deletes don't fail on housekeeping.
    ///
    /// Uses a correlated `NOT EXISTS` rather than `NOT IN (SELECT DISTINCT …)`
    /// so SQLite can drive the lookup off `idx_checkpoint_files_blob_sha256`
    /// directly without materializing the DISTINCT set, and so that NULL
    /// `blob_sha256` values (legacy un-backfilled rows) don't poison the
    /// outer match the way they would under `NOT IN`'s three-valued logic.
    fn gc_orphan_blobs_after_delete(&self, rows_deleted: usize) {
        if rows_deleted == 0 {
            return;
        }
        if let Err(e) = self.conn.execute(
            "DELETE FROM checkpoint_blobs
             WHERE NOT EXISTS (
                 SELECT 1 FROM checkpoint_files
                  WHERE blob_sha256 = checkpoint_blobs.sha256
             )",
            [],
        ) {
            tracing::warn!(
                target: "claudette::db",
                error = %e,
                "orphan checkpoint blob GC skipped"
            );
        }
    }

    fn best_effort_incremental_vacuum(&self, max_pages: i64) {
        if let Err(e) = self.incremental_vacuum(max_pages) {
            tracing::warn!(
                target: "claudette::db",
                max_pages,
                error = %e,
                "incremental vacuum skipped"
            );
        }
    }

    fn incremental_vacuum(&self, max_pages: i64) -> Result<(), rusqlite::Error> {
        if max_pages <= 0 {
            return Ok(());
        }
        if self.auto_vacuum_mode()? != SQLITE_AUTO_VACUUM_INCREMENTAL {
            return Ok(());
        }
        let freelist_count = self.freelist_page_count()?;
        if freelist_count <= 0 {
            return Ok(());
        }
        let pages = freelist_count.min(max_pages);
        self.conn
            .execute_batch(&format!("PRAGMA incremental_vacuum({pages});"))
    }

    /// Ensure `schema_migrations` exists; seed it from `PRAGMA user_version`
    /// on pre-redesign databases. Idempotent: subsequent calls are no-ops.
    fn bootstrap_and_backfill(&self, migrations: &[Migration]) -> Result<(), rusqlite::Error> {
        let table_exists: bool = self.conn.query_row(
            "SELECT EXISTS(SELECT 1 FROM sqlite_master
                           WHERE type='table' AND name='schema_migrations')",
            [],
            |r| r.get(0),
        )?;
        if table_exists {
            return Ok(());
        }

        let legacy_version: i32 = self
            .conn
            .query_row("PRAGMA user_version", [], |r| r.get(0))?;

        let tx = self.conn.unchecked_transaction()?;
        tx.execute_batch(
            "CREATE TABLE schema_migrations (
                 id         TEXT PRIMARY KEY,
                 applied_at TEXT NOT NULL DEFAULT (datetime('now'))
             );",
        )?;
        for m in migrations {
            if let Some(v) = m.legacy_version
                && v <= legacy_version
            {
                tx.execute(
                    "INSERT INTO schema_migrations (id) VALUES (?1)",
                    params![m.id],
                )?;
            }
        }
        tx.commit()?;
        Ok(())
    }

    /// Apply every migration in `migrations` that is not already recorded in
    /// `schema_migrations`. Each migration's SQL and its tracking-row insert
    /// run inside a single transaction, so a failure leaves no partial state.
    fn run_migrations(conn: &Connection, migrations: &[Migration]) -> Result<(), rusqlite::Error> {
        let mut seen: HashSet<&str> = HashSet::with_capacity(migrations.len());
        for m in migrations {
            assert!(
                seen.insert(m.id),
                "duplicate migration id in MIGRATIONS: {}",
                m.id,
            );
        }

        let applied: HashSet<String> = conn
            .prepare("SELECT id FROM schema_migrations")?
            .query_map([], |r| r.get::<_, String>(0))?
            .collect::<Result<_, _>>()?;

        for m in migrations {
            if applied.contains(m.id) {
                continue;
            }
            let tx = conn.unchecked_transaction()?;
            match tx.execute_batch(m.sql) {
                Ok(()) => {
                    // `OR IGNORE` makes the ledger write idempotent so two
                    // connections opened during first boot can't wedge each
                    // other on a UNIQUE-constraint failure if both compute
                    // `applied` before either commits.
                    tx.execute(
                        "INSERT OR IGNORE INTO schema_migrations (id) VALUES (?1)",
                        params![m.id],
                    )?;
                    tx.commit()?;
                }
                Err(e) if is_already_exists_error(&e) => {
                    // The schema object the migration tried to create (table /
                    // index / column) is already present — the most common
                    // cause is a developer who hand-applied the SQL or merged
                    // a branch whose migrations they had already run. Drop
                    // the aborted transaction and record the migration as
                    // applied so the runner doesn't wedge the app on every
                    // subsequent boot.
                    drop(tx);
                    tracing::warn!(
                        target: "claudette::db",
                        migration_id = m.id,
                        error = %e,
                        "migration skipped: schema object already present; marking applied"
                    );
                    let tx = conn.unchecked_transaction()?;
                    tx.execute(
                        "INSERT OR IGNORE INTO schema_migrations (id) VALUES (?1)",
                        params![m.id],
                    )?;
                    tx.commit()?;
                }
                Err(e) => return Err(e),
            }
        }
        Ok(())
    }

    fn heal_orphaned_sessions(&self) -> Result<(), rusqlite::Error> {
        let has_orphaned_ws = self
            .conn
            .query_row(
                "SELECT EXISTS(
                     SELECT 1 FROM workspaces w
                     WHERE NOT EXISTS (
                         SELECT 1 FROM chat_sessions cs WHERE cs.workspace_id = w.id
                     )
                 )",
                [],
                |row| row.get::<_, bool>(0),
            )
            .unwrap_or(false);

        if has_orphaned_ws {
            let tx = self.conn.unchecked_transaction()?;
            let orphaned: Vec<(String, Option<String>, i64)> = {
                let mut stmt = tx.prepare(
                    "SELECT w.id, w.session_id, w.turn_count
                     FROM workspaces w
                     WHERE NOT EXISTS (
                         SELECT 1 FROM chat_sessions cs WHERE cs.workspace_id = w.id
                     )",
                )?;
                stmt.query_map([], |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?)))?
                    .collect::<Result<Vec<_>, _>>()?
            };
            for (ws_id, claude_sid, tc) in &orphaned {
                let sid = uuid::Uuid::new_v4().to_string();
                tx.execute(
                    "INSERT INTO chat_sessions
                        (id, workspace_id, session_id, name, name_edited,
                         turn_count, sort_order, status)
                     VALUES (?1, ?2, ?3, 'Main', 0, ?4, 0, 'active')",
                    params![sid, ws_id, claude_sid, tc],
                )?;
                tx.execute(
                    "UPDATE chat_messages SET chat_session_id = ?1
                     WHERE workspace_id = ?2 AND chat_session_id IS NULL",
                    params![sid, ws_id],
                )?;
                tx.execute(
                    "UPDATE conversation_checkpoints SET chat_session_id = ?1
                     WHERE workspace_id = ?2 AND chat_session_id IS NULL",
                    params![sid, ws_id],
                )?;
            }
            tx.commit()?;
        }

        let has_null_sessions: bool = self
            .conn
            .query_row(
                "SELECT EXISTS(SELECT 1 FROM chat_messages WHERE chat_session_id IS NULL)",
                [],
                |row| row.get(0),
            )
            .unwrap_or(false);

        if has_null_sessions {
            self.conn.execute_batch(
                "UPDATE chat_messages SET chat_session_id = (
                     SELECT cs.id FROM chat_sessions cs
                     WHERE cs.workspace_id = chat_messages.workspace_id
                     ORDER BY cs.sort_order, cs.created_at LIMIT 1
                 )
                 WHERE chat_session_id IS NULL;

                 UPDATE conversation_checkpoints SET chat_session_id = (
                     SELECT cs.id FROM chat_sessions cs
                     WHERE cs.workspace_id = conversation_checkpoints.workspace_id
                     ORDER BY cs.sort_order, cs.created_at LIMIT 1
                 )
                 WHERE chat_session_id IS NULL;",
            )?;
        }

        Ok(())
    }
}

#[cfg(test)]
impl Database {
    /// Test-only accessor: expose the underlying connection for setup needs
    /// that don't fit `execute_batch` (e.g. parameterized queries).
    pub fn conn(&self) -> &Connection {
        &self.conn
    }

    /// Test-only: run the migration runner against a caller-supplied slice.
    /// Used to inject synthetic migrations for error-path and ordering tests.
    pub fn migrate_with(&self, migrations: &[Migration]) -> Result<(), rusqlite::Error> {
        self.bootstrap_and_backfill(migrations)?;
        Self::run_migrations(&self.conn, migrations)
    }
}

/// Returns true when `err` is a benign "object already exists" failure from a
/// DDL statement: `CREATE TABLE/INDEX/VIEW/TRIGGER` against an existing
/// object, or `ALTER TABLE ADD COLUMN` against an existing column. SQLite
/// reports all of these under the generic primary code `SQLITE_ERROR` (which
/// rusqlite maps to `ErrorCode::Unknown`), so we additionally match on the
/// message text. The error can surface as either `SqliteFailure` (step-time)
/// or `SqlInputError` (prepare-time, on `modern_sqlite` builds), so both
/// variants are checked.
fn is_already_exists_error(err: &rusqlite::Error) -> bool {
    let (code, msg) = match err {
        rusqlite::Error::SqliteFailure(code, Some(msg)) => (code.code, msg.as_str()),
        rusqlite::Error::SqlInputError { error, msg, .. } => (error.code, msg.as_str()),
        _ => return false,
    };
    if code != rusqlite::ErrorCode::Unknown {
        return false;
    }
    msg.contains("already exists") || msg.contains("duplicate column name")
}

fn is_fresh_database_file(path: &Path) -> bool {
    match std::fs::metadata(path) {
        Ok(metadata) => metadata.len() == 0,
        Err(e) if e.kind() == std::io::ErrorKind::NotFound => true,
        Err(_) => false,
    }
}

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

    // --- Migration runner tests ---

    fn count_applied(db: &Database) -> i64 {
        db.conn()
            .query_row("SELECT COUNT(*) FROM schema_migrations", [], |r| r.get(0))
            .unwrap()
    }

    fn applied_ids(db: &Database) -> Vec<String> {
        let mut stmt = db
            .conn()
            .prepare("SELECT id FROM schema_migrations ORDER BY id")
            .unwrap();
        stmt.query_map([], |r| r.get::<_, String>(0))
            .unwrap()
            .collect::<Result<Vec<_>, _>>()
            .unwrap()
    }

    fn auto_vacuum_mode(db: &Database) -> i64 {
        db.conn()
            .query_row("PRAGMA auto_vacuum", [], |r| r.get(0))
            .unwrap()
    }

    fn busy_timeout_ms(db: &Database) -> i64 {
        db.conn()
            .query_row("PRAGMA busy_timeout", [], |r| r.get(0))
            .unwrap()
    }

    fn freelist_count(db: &Database) -> i64 {
        db.conn()
            .query_row("PRAGMA freelist_count", [], |r| r.get(0))
            .unwrap()
    }

    /// Apply the SQL bodies of the first N pre-redesign migrations directly,
    /// then set `PRAGMA user_version = N`, producing a DB that looks exactly
    /// like one from before the redesign at that version.
    fn build_legacy_db_at_version(n: i32) -> Connection {
        let conn = Connection::open_in_memory().unwrap();
        conn.execute_batch("PRAGMA foreign_keys=ON;").unwrap();
        for m in MIGRATIONS.iter().take(n as usize) {
            conn.execute_batch(m.sql).unwrap();
        }
        conn.execute_batch(&format!("PRAGMA user_version = {n};"))
            .unwrap();
        conn
    }

    #[test]
    fn test_migrations_unique_ids() {
        let mut seen = HashSet::new();
        for m in MIGRATIONS {
            assert!(
                seen.insert(m.id),
                "duplicate migration id in MIGRATIONS: {}",
                m.id,
            );
        }
    }

    #[test]
    fn test_migrations_timestamp_prefix_format() {
        for m in MIGRATIONS {
            let prefix: String = m.id.chars().take(14).collect();
            assert_eq!(
                prefix.len(),
                14,
                "migration id too short, expected 14-digit timestamp prefix: {}",
                m.id,
            );
            assert!(
                prefix.chars().all(|c| c.is_ascii_digit()),
                "migration id must start with 14 ASCII digits: {}",
                m.id,
            );
            assert_eq!(
                m.id.chars().nth(14),
                Some('_'),
                "migration id must have underscore after timestamp: {}",
                m.id,
            );
        }
    }

    #[test]
    fn test_fresh_db_applies_all_migrations() {
        let db = Database::open_in_memory().unwrap();
        assert_eq!(count_applied(&db) as usize, MIGRATIONS.len());
    }

    #[test]
    fn test_fresh_file_db_uses_incremental_auto_vacuum() {
        let dir = tempfile::tempdir().unwrap();
        let path = dir.path().join("claudette.db");

        let db = Database::open(&path).unwrap();

        assert_eq!(auto_vacuum_mode(&db), SQLITE_AUTO_VACUUM_INCREMENTAL);
    }

    #[test]
    fn test_database_connections_wait_on_busy_locks() {
        let db = Database::open_in_memory().unwrap();
        assert_eq!(busy_timeout_ms(&db), SQLITE_BUSY_TIMEOUT.as_millis() as i64);
    }

    #[test]
    fn test_existing_file_db_defers_auto_vacuum_conversion_until_maintenance() {
        let dir = tempfile::tempdir().unwrap();
        let path = dir.path().join("claudette.db");
        {
            let conn = Connection::open(&path).unwrap();
            conn.execute_batch(
                "PRAGMA auto_vacuum=NONE;
                 CREATE TABLE preexisting_table (id INTEGER PRIMARY KEY, payload BLOB);
                 INSERT INTO preexisting_table (payload) VALUES (zeroblob(65536));",
            )
            .unwrap();
            assert_eq!(
                conn.query_row("PRAGMA auto_vacuum", [], |r| r.get::<_, i64>(0))
                    .unwrap(),
                0
            );
        }

        let db = Database::open(&path).unwrap();

        assert_eq!(auto_vacuum_mode(&db), 0);
        let preserved: i64 = db
            .conn()
            .query_row("SELECT COUNT(*) FROM preexisting_table", [], |r| r.get(0))
            .unwrap();
        assert_eq!(preserved, 1);
    }

    #[test]
    fn test_explicit_maintenance_converts_existing_file_db_to_incremental_auto_vacuum() {
        let dir = tempfile::tempdir().unwrap();
        let path = dir.path().join("claudette.db");
        {
            let conn = Connection::open(&path).unwrap();
            conn.execute_batch(
                "PRAGMA auto_vacuum=NONE;
                 CREATE TABLE preexisting_table (id INTEGER PRIMARY KEY, payload BLOB);
                 INSERT INTO preexisting_table (payload) VALUES (zeroblob(65536));",
            )
            .unwrap();
        }

        let db = Database::open(&path).unwrap();
        let converted = db.ensure_incremental_auto_vacuum().unwrap();

        assert!(converted);
        assert_eq!(auto_vacuum_mode(&db), SQLITE_AUTO_VACUUM_INCREMENTAL);
        let preserved: i64 = db
            .conn()
            .query_row("SELECT COUNT(*) FROM preexisting_table", [], |r| r.get(0))
            .unwrap();
        assert_eq!(preserved, 1);
    }

    #[test]
    fn test_open_does_not_incrementally_vacuum_converted_db() {
        let dir = tempfile::tempdir().unwrap();
        let path = dir.path().join("claudette.db");
        let freelist_before = {
            let db = Database::open(&path).unwrap();
            db.conn()
                .execute_batch(
                    "CREATE TABLE vacuum_probe (id INTEGER PRIMARY KEY, payload BLOB);
                     INSERT INTO vacuum_probe (payload) VALUES (zeroblob(1048576));
                     DELETE FROM vacuum_probe;",
                )
                .unwrap();
            let freelist_before = freelist_count(&db);
            assert!(
                freelist_before > 0,
                "test setup should leave free pages to reclaim"
            );
            freelist_before
        };

        let db = Database::open(&path).unwrap();

        assert_eq!(freelist_count(&db), freelist_before);
    }

    #[test]
    fn test_full_auto_vacuum_db_is_left_unchanged() {
        let dir = tempfile::tempdir().unwrap();
        let path = dir.path().join("claudette.db");
        {
            let conn = Connection::open(&path).unwrap();
            conn.execute_batch(
                "PRAGMA auto_vacuum=FULL;
                 CREATE TABLE preexisting_table (id INTEGER PRIMARY KEY, payload BLOB);
                 INSERT INTO preexisting_table (payload) VALUES (zeroblob(65536));",
            )
            .unwrap();
            assert_eq!(
                conn.query_row("PRAGMA auto_vacuum", [], |r| r.get::<_, i64>(0))
                    .unwrap(),
                SQLITE_AUTO_VACUUM_FULL
            );
        }

        let db = Database::open(&path).unwrap();

        assert_eq!(auto_vacuum_mode(&db), SQLITE_AUTO_VACUUM_FULL);
        let preserved: i64 = db
            .conn()
            .query_row("SELECT COUNT(*) FROM preexisting_table", [], |r| r.get(0))
            .unwrap();
        assert_eq!(preserved, 1);
    }

    /// Pin the keybinding rename: after the renaming migration runs,
    /// any user-customized override of `keybinding:file-viewer.close-file-tab`
    /// must land at `keybinding:global.close-tab` and the legacy key
    /// must be gone. The migration is run as part of `Database::open_in_memory`
    /// alongside everything else, so this test seeds the *prior* state
    /// by inserting the legacy row and re-running the migration's SQL
    /// directly — exercising the same INSERT OR IGNORE / DELETE pair
    /// the runtime applies.
    #[test]
    fn test_close_tab_keybinding_rename_migration() {
        let db = Database::open_in_memory().unwrap();
        // Seed the legacy row that an existing user would have had
        // before pulling this build.
        db.conn()
            .execute(
                "INSERT INTO app_settings (key, value) VALUES (?1, ?2)",
                params!["keybinding:file-viewer.close-file-tab", "mod+x"],
            )
            .unwrap();
        // Re-execute the migration body. INSERT OR IGNORE is the
        // important guard — a user who already has a custom binding on
        // the new id keeps it; only orphaned legacy rows are migrated.
        let migration_sql =
            include_str!("../migrations/20260509000540_rename_close_file_tab_keybinding.sql");
        db.conn().execute_batch(migration_sql).unwrap();

        let new_value: Option<String> = db
            .conn()
            .query_row(
                "SELECT value FROM app_settings WHERE key = 'keybinding:global.close-tab'",
                [],
                |r| r.get(0),
            )
            .ok();
        assert_eq!(new_value.as_deref(), Some("mod+x"));

        let legacy_present: bool = db
            .conn()
            .query_row(
                "SELECT EXISTS(SELECT 1 FROM app_settings WHERE key = 'keybinding:file-viewer.close-file-tab')",
                [],
                |r| r.get(0),
            )
            .unwrap();
        assert!(!legacy_present, "legacy row must be removed");
    }

    /// `INSERT OR IGNORE` semantics: an explicit override under the
    /// new id wins over the legacy value when both are present (e.g.
    /// a user who customised the binding across both versions). The
    /// legacy row is still removed afterward.
    #[test]
    fn test_close_tab_keybinding_rename_preserves_existing_new_id() {
        let db = Database::open_in_memory().unwrap();
        db.conn()
            .execute_batch(
                "INSERT INTO app_settings (key, value) VALUES \
                 ('keybinding:file-viewer.close-file-tab', 'legacy-value'), \
                 ('keybinding:global.close-tab', 'new-value');",
            )
            .unwrap();
        let migration_sql =
            include_str!("../migrations/20260509000540_rename_close_file_tab_keybinding.sql");
        db.conn().execute_batch(migration_sql).unwrap();

        let new_value: String = db
            .conn()
            .query_row(
                "SELECT value FROM app_settings WHERE key = 'keybinding:global.close-tab'",
                [],
                |r| r.get(0),
            )
            .unwrap();
        assert_eq!(
            new_value, "new-value",
            "explicit new-id override must not be clobbered by INSERT OR IGNORE",
        );
    }

    #[test]
    fn test_migrate_is_idempotent() {
        let db = Database::open_in_memory().unwrap();
        let before = count_applied(&db);
        // Re-invoke — same MIGRATIONS slice, already-applied rows must be skipped.
        db.migrate_with(MIGRATIONS).unwrap();
        assert_eq!(before, count_applied(&db));
    }

    #[test]
    fn test_backfill_from_user_version_19() {
        let conn = build_legacy_db_at_version(19);
        let db = Database { conn };
        db.migrate().unwrap();

        let ids = applied_ids(&db);
        assert_eq!(ids.len(), MIGRATIONS.len());
        for m in MIGRATIONS {
            assert!(
                ids.contains(&m.id.to_string()),
                "missing backfilled id: {}",
                m.id,
            );
        }
    }

    #[test]
    fn test_backfill_from_user_version_0() {
        let conn = Connection::open_in_memory().unwrap();
        conn.execute_batch("PRAGMA foreign_keys=ON;").unwrap();
        let db = Database { conn };
        db.migrate().unwrap();
        assert_eq!(count_applied(&db) as usize, MIGRATIONS.len());
    }

    #[test]
    fn test_partial_backfill_from_mid_version() {
        let conn = build_legacy_db_at_version(10);
        let db = Database { conn };
        db.migrate().unwrap();

        // All 19 legacy + none extra: migrations 1-10 got backfilled rows,
        // 11-19 ran for real as fresh migrations. Either way the final row
        // count is MIGRATIONS.len() and all IDs are present.
        assert_eq!(count_applied(&db) as usize, MIGRATIONS.len());
        for m in MIGRATIONS {
            let present: bool = db
                .conn()
                .query_row(
                    "SELECT EXISTS(SELECT 1 FROM schema_migrations WHERE id = ?1)",
                    params![m.id],
                    |r| r.get(0),
                )
                .unwrap();
            assert!(present, "id not present after partial backfill: {}", m.id);
        }
    }

    #[test]
    fn test_skips_already_applied_migration() {
        // Synthetic migration: inject an id into schema_migrations and point
        // its SQL at something that would fail if re-run. The runner must
        // skip it because the id is already present.
        let db = Database::open_in_memory().unwrap();
        let synthetic = [Migration {
            id: "29991231235959_synthetic_broken_sql",
            sql: "this is not valid sql and would fail if executed",
            legacy_version: None,
        }];
        db.conn()
            .execute(
                "INSERT INTO schema_migrations (id) VALUES (?1)",
                params![synthetic[0].id],
            )
            .unwrap();
        db.migrate_with(&synthetic).unwrap();
    }

    #[test]
    fn test_migration_failure_is_atomic() {
        let db = Database::open_in_memory().unwrap();
        let bad = [Migration {
            id: "29991231235959_synthetic_bad",
            sql: "ALTER TABLE does_not_exist ADD COLUMN x INTEGER;",
            legacy_version: None,
        }];
        let err = db.migrate_with(&bad);
        assert!(err.is_err(), "expected migration failure to bubble up");

        let present: bool = db
            .conn()
            .query_row(
                "SELECT EXISTS(SELECT 1 FROM schema_migrations WHERE id = ?1)",
                params![bad[0].id],
                |r| r.get(0),
            )
            .unwrap();
        assert!(
            !present,
            "failed migration must not leave tracking row in schema_migrations",
        );
    }

    #[test]
    fn test_migration_skips_when_table_already_exists() {
        // Simulates the dev case: a migration's CREATE TABLE targets an object
        // a developer already created out of band. The runner must mark the
        // migration applied and continue, not propagate the error.
        let db = Database::open_in_memory().unwrap();
        db.execute_batch("CREATE TABLE dup_t (x INTEGER);").unwrap();
        let synthetic = [Migration {
            id: "29991231235959_synthetic_dup_table",
            sql: "CREATE TABLE dup_t (x INTEGER);",
            legacy_version: None,
        }];
        db.migrate_with(&synthetic)
            .expect("already-exists must be tolerated");
        let present: bool = db
            .conn()
            .query_row(
                "SELECT EXISTS(SELECT 1 FROM schema_migrations WHERE id = ?1)",
                params![synthetic[0].id],
                |r| r.get(0),
            )
            .unwrap();
        assert!(
            present,
            "tolerated migration must still be recorded in schema_migrations",
        );
    }

    #[test]
    fn test_migration_skips_when_column_already_exists() {
        // `repositories.icon` is added by the released migration #3, so it's
        // present after `open_in_memory`. A synthetic migration that tries to
        // add it again must be tolerated.
        let db = Database::open_in_memory().unwrap();
        let synthetic = [Migration {
            id: "29991231235959_synthetic_dup_column",
            sql: "ALTER TABLE repositories ADD COLUMN icon TEXT;",
            legacy_version: None,
        }];
        db.migrate_with(&synthetic)
            .expect("duplicate column must be tolerated");
        let present: bool = db
            .conn()
            .query_row(
                "SELECT EXISTS(SELECT 1 FROM schema_migrations WHERE id = ?1)",
                params![synthetic[0].id],
                |r| r.get(0),
            )
            .unwrap();
        assert!(present);
    }

    #[test]
    fn test_migration_propagates_non_already_exists_errors() {
        // Real schema mistakes (here: targeting a missing table) must still
        // surface as errors — leniency is scoped to "already exists" /
        // "duplicate column name" only.
        let db = Database::open_in_memory().unwrap();
        let bad = [Migration {
            id: "29991231235959_synthetic_no_such_table",
            sql: "INSERT INTO __no_such_table__ VALUES (1);",
            legacy_version: None,
        }];
        let err = db.migrate_with(&bad);
        assert!(err.is_err(), "non-tolerable errors must bubble up");
        let present: bool = db
            .conn()
            .query_row(
                "SELECT EXISTS(SELECT 1 FROM schema_migrations WHERE id = ?1)",
                params![bad[0].id],
                |r| r.get(0),
            )
            .unwrap();
        assert!(!present);
    }

    /// Regression: dev DBs that ran migration `20260505214219` before
    /// commit `2fc1b316` amended its SQL recorded the migration id as
    /// applied without ever picking up the late-added
    /// `agent_tool_calls_json` column. The follow-up healing migration
    /// `20260506170933_heal_turn_tool_activity_agent_tool_calls_json`
    /// restores the column on those DBs and is a no-op (via the
    /// runner's "already exists" leniency) on clean installs.
    #[test]
    fn test_heal_migration_restores_missing_agent_tool_calls_json_column() {
        let db = Database::open_in_memory().unwrap();

        // Simulate the broken state: drop the column that the amended
        // migration was supposed to add, and forget that the healing
        // migration ever ran so the runner replays it.
        db.execute_batch("ALTER TABLE turn_tool_activities DROP COLUMN agent_tool_calls_json;")
            .unwrap();
        db.conn()
            .execute(
                "DELETE FROM schema_migrations WHERE id = ?1",
                params!["20260506170933_heal_turn_tool_activity_agent_tool_calls_json"],
            )
            .unwrap();
        let column_exists = |name: &str| -> bool {
            db.conn()
                .query_row(
                    "SELECT COUNT(*) FROM pragma_table_info('turn_tool_activities') WHERE name = ?1",
                    params![name],
                    |r| r.get::<_, i64>(0),
                )
                .unwrap()
                > 0
        };
        assert!(!column_exists("agent_tool_calls_json"));

        // Re-running the canonical migrations should now heal the DB.
        db.run_migrations_for_test()
            .expect("healing migration must apply cleanly");
        assert!(column_exists("agent_tool_calls_json"));

        // And running it a second time on a healed DB must remain a
        // no-op via the runner's duplicate-column leniency.
        db.run_migrations_for_test()
            .expect("healing migration must be idempotent");
        assert!(column_exists("agent_tool_calls_json"));
    }

    #[test]
    fn test_is_already_exists_error_classifier() {
        let conn = rusqlite::Connection::open_in_memory().unwrap();
        conn.execute_batch("CREATE TABLE t (a INTEGER, b INTEGER UNIQUE);")
            .unwrap();

        // CREATE TABLE over an existing table.
        let err = conn
            .execute_batch("CREATE TABLE t (a INTEGER);")
            .unwrap_err();
        assert!(
            super::is_already_exists_error(&err),
            "expected duplicate-table error to be tolerated, got {err:?}",
        );

        // ALTER TABLE ADD COLUMN over an existing column.
        let err = conn
            .execute_batch("ALTER TABLE t ADD COLUMN a INTEGER;")
            .unwrap_err();
        assert!(
            super::is_already_exists_error(&err),
            "expected duplicate-column error to be tolerated, got {err:?}",
        );

        // CREATE INDEX over an existing index.
        conn.execute_batch("CREATE INDEX idx_t_a ON t(a);").unwrap();
        let err = conn
            .execute_batch("CREATE INDEX idx_t_a ON t(a);")
            .unwrap_err();
        assert!(
            super::is_already_exists_error(&err),
            "expected duplicate-index error to be tolerated, got {err:?}",
        );

        // No such table — must NOT be tolerated.
        let err = conn
            .execute_batch("INSERT INTO __no_such_table__ VALUES (1);")
            .unwrap_err();
        assert!(
            !super::is_already_exists_error(&err),
            "no-such-table is not an already-exists case, got {err:?}",
        );

        // UNIQUE constraint violation — must NOT be tolerated (different
        // primary code).
        conn.execute_batch("INSERT INTO t (a, b) VALUES (1, 1);")
            .unwrap();
        let err = conn
            .execute_batch("INSERT INTO t (a, b) VALUES (2, 1);")
            .unwrap_err();
        assert!(
            !super::is_already_exists_error(&err),
            "constraint violations are not already-exists, got {err:?}",
        );
    }

    #[test]
    fn test_chat_sessions_migration_backfills_sessions() {
        let db = Database::open_in_memory().unwrap();

        // Rewind: drop chat_sessions structures and remove the migration
        // tracking row so re-running migrations will re-apply it. We also
        // re-add the legacy `workspaces.session_id` / `workspaces.turn_count`
        // columns (dropped by 20260508142050) and clear that migration's
        // bookkeeping so its second run drops them again — this test
        // specifically exercises the original chat_sessions backfill, which
        // depends on the legacy columns being present at run time.
        db.execute_batch(
            "PRAGMA foreign_keys=OFF;
             DROP INDEX IF EXISTS idx_chat_messages_chat_session;
             DROP INDEX IF EXISTS idx_checkpoints_chat_session;
             DROP INDEX IF EXISTS idx_chat_sessions_ws;
             DROP INDEX IF EXISTS idx_chat_sessions_active;
             ALTER TABLE chat_messages DROP COLUMN chat_session_id;
             ALTER TABLE conversation_checkpoints DROP COLUMN chat_session_id;
             DROP TABLE chat_sessions;
             ALTER TABLE workspaces ADD COLUMN session_id TEXT;
             ALTER TABLE workspaces ADD COLUMN turn_count INTEGER NOT NULL DEFAULT 0;
             DELETE FROM schema_migrations WHERE id = '20260422000000_chat_sessions';
             DELETE FROM schema_migrations WHERE id = '20260508142050_drop_legacy_workspace_session_columns';
             PRAGMA foreign_keys=ON;",
        )
        .unwrap();

        // Seed: repo + two workspaces, one with an existing claude session
        // and turn count + messages + checkpoint; one fresh.
        db.insert_repository(&make_repo("r1", "/tmp/repo1", "repo1"))
            .unwrap();
        db.execute_batch(
            "INSERT INTO workspaces (id, repository_id, name, branch_name, worktree_path, status, status_line)
                 VALUES ('w1', 'r1', 'first-ws', 'r1/first-ws', NULL, 'active', '');
             INSERT INTO workspaces (id, repository_id, name, branch_name, worktree_path, status, status_line)
                 VALUES ('w2', 'r1', 'second-ws', 'r1/second-ws', NULL, 'active', '');
             UPDATE workspaces SET session_id = 'claude-abc', turn_count = 3 WHERE id = 'w1';
             INSERT INTO chat_messages (id, workspace_id, role, content)
                 VALUES ('m1', 'w1', 'user', 'hello');
             INSERT INTO chat_messages (id, workspace_id, role, content)
                 VALUES ('m2', 'w1', 'assistant', 'hi');
             INSERT INTO conversation_checkpoints (id, workspace_id, message_id, turn_index)
                 VALUES ('cp1', 'w1', 'm2', 0);",
        )
        .unwrap();

        // Re-run migrations — the chat_sessions migration should re-apply.
        db.run_migrations_for_test().unwrap();

        struct SessionRow {
            id: String,
            workspace_id: String,
            session_id: Option<String>,
            name: String,
            turn_count: i64,
            sort_order: i32,
            status: String,
        }

        // Both workspaces should now have exactly one "Main" session.
        let session_rows: Vec<SessionRow> = {
            let mut stmt = db
                .conn()
                .prepare(
                    "SELECT id, workspace_id, session_id, name, turn_count, sort_order, status
                     FROM chat_sessions ORDER BY workspace_id",
                )
                .unwrap();
            stmt.query_map([], |row| {
                Ok(SessionRow {
                    id: row.get(0)?,
                    workspace_id: row.get(1)?,
                    session_id: row.get(2)?,
                    name: row.get(3)?,
                    turn_count: row.get(4)?,
                    sort_order: row.get(5)?,
                    status: row.get(6)?,
                })
            })
            .unwrap()
            .collect::<Result<Vec<_>, _>>()
            .unwrap()
        };
        assert_eq!(session_rows.len(), 2);
        // w1: claude session and turn count forwarded.
        let w1 = session_rows
            .iter()
            .find(|r| r.workspace_id == "w1")
            .unwrap();
        assert_eq!(w1.session_id.as_deref(), Some("claude-abc"));
        assert_eq!(w1.name, "Main");
        assert_eq!(w1.turn_count, 3);
        assert_eq!(w1.sort_order, 0);
        assert_eq!(w1.status, "active");
        // w2: empty session + zero turns.
        let w2 = session_rows
            .iter()
            .find(|r| r.workspace_id == "w2")
            .unwrap();
        assert!(w2.session_id.is_none());
        assert_eq!(w2.turn_count, 0);

        // Messages and checkpoint point at w1's chat session.
        let w1_chat_session_id = w1.id.clone();
        let msg_sessions: Vec<Option<String>> = db
            .conn()
            .prepare("SELECT chat_session_id FROM chat_messages WHERE workspace_id = 'w1'")
            .unwrap()
            .query_map([], |row| row.get(0))
            .unwrap()
            .collect::<Result<_, _>>()
            .unwrap();
        assert_eq!(msg_sessions.len(), 2);
        assert!(
            msg_sessions
                .iter()
                .all(|s| s.as_deref() == Some(&w1_chat_session_id))
        );

        let cp_session: Option<String> = db
            .conn()
            .query_row(
                "SELECT chat_session_id FROM conversation_checkpoints WHERE id = 'cp1'",
                [],
                |row| row.get(0),
            )
            .unwrap();
        assert_eq!(cp_session.as_deref(), Some(w1_chat_session_id.as_str()));
    }

    #[test]
    fn test_save_chat_session_state_persists_session_id() {
        let db = Database::open_in_memory().unwrap();
        db.insert_repository(&make_repo("r1", "/tmp/repo1", "repo1"))
            .unwrap();
        db.insert_workspace(&make_workspace("w1", "r1", "ws"))
            .unwrap();
        let sess = db.create_chat_session("w1").unwrap();
        assert!(sess.session_id.is_none());

        db.save_chat_session_state(&sess.id, "claude-sid-1", 3)
            .unwrap();
        let reloaded = db.get_chat_session(&sess.id).unwrap().unwrap();
        assert_eq!(reloaded.session_id.as_deref(), Some("claude-sid-1"));
        assert_eq!(reloaded.turn_count, 3);
    }

    /// Regression pin for the "switching models loses context" bug.
    ///
    /// The frontend `applySelectedModel` helper used to call
    /// `reset_agent_session` on every model swap, which in turn called
    /// `clear_chat_session_state` and wiped the Claude CLI `--resume`
    /// key. The fix is to skip the reset for same-harness swaps. This
    /// test pins the durable contract that fix relies on: a chat
    /// session's `session_id` is preserved across the natural
    /// per-turn writeback path (`save_chat_session_state`) — only an
    /// explicit `clear_chat_session_state` wipes it.
    ///
    /// If a future change starts implicitly nulling `session_id` from
    /// any other code path, this test must fail loudly so the
    /// regression doesn't ship a second time.
    #[test]
    fn save_chat_session_state_preserves_session_id_across_turn_writebacks() {
        let db = Database::open_in_memory().unwrap();
        db.insert_repository(&make_repo("r1", "/tmp/repo1", "repo1"))
            .unwrap();
        db.insert_workspace(&make_workspace("w1", "r1", "ws"))
            .unwrap();
        let sess = db.create_chat_session("w1").unwrap();

        // Simulate prior turns under model A: session_id is set.
        db.save_chat_session_state(&sess.id, "claude-sid-keep", 3)
            .unwrap();
        let after_first_turns = db.get_chat_session(&sess.id).unwrap().unwrap();
        assert_eq!(
            after_first_turns.session_id.as_deref(),
            Some("claude-sid-keep")
        );
        assert_eq!(after_first_turns.turn_count, 3);

        // Simulate the user swapping models (same harness). With the
        // fix in place, no `clear_chat_session_state` happens — only
        // the natural turn writeback runs. The session_id must
        // survive that path so `claude --resume <sid>` keeps the
        // conversation.
        db.save_chat_session_state(&sess.id, "claude-sid-keep", 4)
            .unwrap();
        let after_swap = db.get_chat_session(&sess.id).unwrap().unwrap();
        assert_eq!(
            after_swap.session_id.as_deref(),
            Some("claude-sid-keep"),
            "session_id must survive natural per-turn writebacks — \
             this is what `claude --resume` reads on the next turn"
        );
        assert_eq!(after_swap.turn_count, 4);

        // Sanity check the explicit reset path still works: explicit
        // user-initiated resets continue to clear the session_id.
        db.clear_chat_session_state(&sess.id).unwrap();
        let after_reset = db.get_chat_session(&sess.id).unwrap().unwrap();
        assert!(
            after_reset.session_id.is_none(),
            "explicit reset must still null out session_id"
        );
        assert_eq!(after_reset.turn_count, 0);
    }

    #[test]
    fn test_archive_chat_session_ensuring_active_creates_replacement() {
        let db = Database::open_in_memory().unwrap();
        db.insert_repository(&make_repo("r1", "/tmp/repo1", "repo1"))
            .unwrap();
        db.insert_workspace(&make_workspace("w1", "r1", "ws"))
            .unwrap();
        // insert_workspace auto-creates one active session — archive it.
        let only = db
            .list_chat_sessions_for_workspace("w1", false)
            .unwrap()
            .into_iter()
            .next()
            .unwrap();

        let fresh = db
            .archive_chat_session_ensuring_active(&only.id, "w1")
            .unwrap();
        let fresh = fresh.expect("replacement session must be created");
        assert_ne!(fresh.id, only.id);

        let actives = db.list_chat_sessions_for_workspace("w1", false).unwrap();
        assert_eq!(actives.len(), 1);
        assert_eq!(actives[0].id, fresh.id);
    }

    #[test]
    fn test_archive_chat_session_ensuring_active_skips_when_siblings_exist() {
        let db = Database::open_in_memory().unwrap();
        db.insert_repository(&make_repo("r1", "/tmp/repo1", "repo1"))
            .unwrap();
        db.insert_workspace(&make_workspace("w1", "r1", "ws"))
            .unwrap();
        let first = db
            .list_chat_sessions_for_workspace("w1", false)
            .unwrap()
            .into_iter()
            .next()
            .unwrap();
        let _second = db.create_chat_session("w1").unwrap();

        let fresh = db
            .archive_chat_session_ensuring_active(&first.id, "w1")
            .unwrap();
        assert!(
            fresh.is_none(),
            "should not create a replacement when siblings remain",
        );
        let actives = db.list_chat_sessions_for_workspace("w1", false).unwrap();
        assert_eq!(actives.len(), 1);
    }

    /// `archive_chat_session_only` is the deliberate opt-out from the
    /// always-≥1-active invariant — used by the "close last tab" path so
    /// the workspace's empty-tabs view can surface. If a regression ever
    /// re-introduces auto-replacement here, the welcome flow's whole
    /// reason for existing collapses.
    #[test]
    fn test_archive_chat_session_only_leaves_workspace_empty() {
        let db = Database::open_in_memory().unwrap();
        db.insert_repository(&make_repo("r1", "/tmp/repo1", "repo1"))
            .unwrap();
        db.insert_workspace(&make_workspace("w1", "r1", "ws"))
            .unwrap();
        let only = db
            .list_chat_sessions_for_workspace("w1", false)
            .unwrap()
            .into_iter()
            .next()
            .unwrap();

        db.archive_chat_session_only(&only.id).unwrap();

        let actives = db.list_chat_sessions_for_workspace("w1", false).unwrap();
        assert!(
            actives.is_empty(),
            "archive_chat_session_only must NOT create a replacement",
        );
        let all = db.list_chat_sessions_for_workspace("w1", true).unwrap();
        assert_eq!(all.len(), 1, "the archived row must still be queryable");
        assert_eq!(all[0].id, only.id);
    }

    #[test]
    fn test_restore_chat_session_reactivates_archived_session() {
        let db = Database::open_in_memory().unwrap();
        db.insert_repository(&make_repo("r1", "/tmp/repo1", "repo1"))
            .unwrap();
        db.insert_workspace(&make_workspace("w1", "r1", "ws"))
            .unwrap();
        let only = db
            .list_chat_sessions_for_workspace("w1", false)
            .unwrap()
            .into_iter()
            .next()
            .unwrap();
        db.archive_chat_session_only(&only.id).unwrap();
        assert!(
            db.list_chat_sessions_for_workspace("w1", false)
                .unwrap()
                .is_empty()
        );

        let restored = db.restore_chat_session(&only.id).unwrap();
        assert_eq!(restored.id, only.id);
        let actives = db.list_chat_sessions_for_workspace("w1", false).unwrap();
        assert_eq!(actives.len(), 1);
        assert_eq!(actives[0].id, only.id);
    }

    /// Restoring a session that doesn't exist must surface an error rather
    /// than silently succeeding — the empty-tabs view's "resume previous
    /// session" affordance relies on the returned row to populate the new
    /// tab; a silent no-op would leave the UI in a broken state.
    #[test]
    fn test_restore_chat_session_errors_on_unknown_id() {
        let db = Database::open_in_memory().unwrap();
        let err = db.restore_chat_session("does-not-exist").unwrap_err();
        assert!(matches!(err, rusqlite::Error::QueryReturnedNoRows));
    }
}
