//! The local database: projects, versions of main, workspaces, changes,
//! saves and the operation log. One SQLite file per user, shared by every
//! workspace and the saver. For local projects its versions table is the
//! authority; for hosted ones it's a cache of the host's.

use std::path::{Path, PathBuf};

use anyhow::{Context, Result};
use rusqlite::{Connection, OptionalExtension, params};
use serde::{Deserialize, Serialize};
use vcs_core::Id;
use vcs_core::version::Version;

use crate::ids;

const SCHEMA: &str = r#"
CREATE TABLE IF NOT EXISTS projects (
  id TEXT PRIMARY KEY,
  address TEXT NOT NULL UNIQUE,
  kind TEXT NOT NULL,
  api TEXT,
  checked INTEGER NOT NULL DEFAULT 0,
  created INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS versions (
  project TEXT NOT NULL,
  n INTEGER NOT NULL,
  id TEXT NOT NULL,
  tree TEXT NOT NULL,
  prev TEXT,
  change TEXT NOT NULL,
  author TEXT NOT NULL,
  time INTEGER NOT NULL,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  git TEXT,
  PRIMARY KEY (project, n)
);
CREATE INDEX IF NOT EXISTS versions_change ON versions(project, change);
CREATE TABLE IF NOT EXISTS workspaces (
  id TEXT PRIMARY KEY,
  project TEXT NOT NULL,
  path TEXT NOT NULL UNIQUE,
  change TEXT NOT NULL,
  only TEXT NOT NULL DEFAULT '[]',
  created INTEGER NOT NULL,
  used INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS changes (
  id TEXT PRIMARY KEY,
  project TEXT NOT NULL,
  title TEXT NOT NULL DEFAULT '',
  body TEXT NOT NULL DEFAULT '',
  base_tree TEXT NOT NULL,
  base_version INTEGER,
  on_change TEXT,
  after_change TEXT,
  head_tree TEXT,
  saves INTEGER NOT NULL DEFAULT 0,
  state TEXT NOT NULL DEFAULT 'open',
  version INTEGER,
  author TEXT NOT NULL DEFAULT '',
  conflicts TEXT NOT NULL DEFAULT '[]',
  workspace TEXT,
  shared TEXT NOT NULL DEFAULT '[]',
  remote_rev INTEGER NOT NULL DEFAULT 0,
  created INTEGER NOT NULL,
  updated INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS changes_project ON changes(project, state);
CREATE TABLE IF NOT EXISTS saves (
  change TEXT NOT NULL,
  n INTEGER NOT NULL,
  tree TEXT NOT NULL,
  base_tree TEXT NOT NULL,
  at INTEGER NOT NULL,
  workspace TEXT,
  uploaded INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (change, n)
);
CREATE INDEX IF NOT EXISTS saves_time ON saves(workspace, at);
CREATE TABLE IF NOT EXISTS ops (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  workspace TEXT NOT NULL,
  at INTEGER NOT NULL,
  kind TEXT NOT NULL,
  data TEXT NOT NULL,
  undone INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS ops_workspace ON ops(workspace, id);
CREATE TABLE IF NOT EXISTS complete (
  tree TEXT PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS personal (
  project TEXT PRIMARY KEY,
  patterns TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS mine (
  project TEXT NOT NULL,
  path TEXT NOT NULL,
  file TEXT NOT NULL,
  exec INTEGER NOT NULL DEFAULT 0,
  at INTEGER NOT NULL,
  PRIMARY KEY (project, path)
);
CREATE INDEX IF NOT EXISTS versions_git ON versions(project, git);
CREATE INDEX IF NOT EXISTS versions_tree ON versions(project, tree);
CREATE TABLE IF NOT EXISTS imports (
  project TEXT PRIMARY KEY,
  source TEXT NOT NULL,
  branch TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS uploaded (
  project TEXT NOT NULL,
  tree TEXT NOT NULL,
  PRIMARY KEY (project, tree)
);
CREATE TABLE IF NOT EXISTS git_ids (
  project TEXT NOT NULL,
  native TEXT NOT NULL,
  git TEXT NOT NULL,
  PRIMARY KEY (project, native)
);
"#;

pub struct Db {
    pub conn: Connection,
}

#[derive(Debug, Clone)]
pub struct Project {
    pub id: String,
    /// `host/owner/name` for hosted projects, the folder for local ones.
    pub address: String,
    /// "local" or "hosted".
    pub kind: String,
    pub api: Option<String>,
    /// When main was last checked with the host (seconds).
    pub checked: i64,
}

impl Project {
    pub fn hosted(&self) -> bool {
        self.kind == "hosted"
    }
}

#[derive(Debug, Clone)]
pub struct Workspace {
    pub id: String,
    pub project: String,
    pub path: PathBuf,
    pub change: String,
    pub only: Vec<String>,
}

#[derive(Debug, Clone, Serialize, Deserialize, PartialEq)]
pub struct Conflict {
    pub path: String,
    #[serde(default)]
    pub version: Option<u64>,
}

#[derive(Debug, Clone)]
pub struct Change {
    pub id: String,
    pub project: String,
    pub title: String,
    pub body: String,
    /// The tree the change starts from.
    pub base_tree: Id,
    /// Set when the base is a version of main.
    pub base_version: Option<u64>,
    /// Set when stacked on an unlanded change.
    pub on_change: Option<String>,
    /// Set when the change continues after one that landed from the same workspace.
    pub after_change: Option<String>,
    /// The latest save (None: nothing saved, same as the base).
    pub head_tree: Option<Id>,
    pub saves: i64,
    /// open, waiting, landed, dropped.
    pub state: String,
    pub version: Option<u64>,
    pub author: String,
    pub conflicts: Vec<Conflict>,
    pub workspace: Option<String>,
    pub shared: Vec<String>,
    pub remote_rev: i64,
    pub created: i64,
    pub updated: i64,
}

impl Change {
    pub fn new(id: String, project: &str, base_tree: Id, base_version: Option<u64>, author: &str) -> Change {
        let now = ids::now();
        Change {
            id,
            project: project.to_string(),
            title: String::new(),
            body: String::new(),
            base_tree,
            base_version,
            on_change: None,
            after_change: None,
            head_tree: None,
            saves: 0,
            state: "open".into(),
            version: None,
            author: author.to_string(),
            conflicts: Vec::new(),
            workspace: None,
            shared: Vec::new(),
            remote_rev: 0,
            created: now,
            updated: now,
        }
    }

    /// The change's content: its latest save, or its base.
    pub fn tree(&self) -> Id {
        self.head_tree.unwrap_or(self.base_tree)
    }

    pub fn is_empty(&self) -> bool {
        self.tree() == self.base_tree
    }

    pub fn message(&self) -> String {
        if self.body.is_empty() { self.title.clone() } else { format!("{}\n\n{}", self.title, self.body) }
    }
}

#[derive(Debug, Clone)]
pub struct Save {
    pub change: String,
    pub n: i64,
    pub tree: Id,
    pub base_tree: Id,
    pub at: i64,
    pub uploaded: bool,
}

#[derive(Debug, Clone)]
pub struct Op {
    pub id: i64,
    pub at: i64,
    pub kind: String,
    pub data: serde_json::Value,
}

fn id_col(row: &rusqlite::Row, i: usize) -> rusqlite::Result<Id> {
    let text: String = row.get(i)?;
    Id::parse(&text).ok_or_else(|| rusqlite::Error::InvalidColumnType(i, "id".into(), rusqlite::types::Type::Text))
}

fn opt_id_col(row: &rusqlite::Row, i: usize) -> rusqlite::Result<Option<Id>> {
    let text: Option<String> = row.get(i)?;
    Ok(text.and_then(|t| Id::parse(&t)))
}

impl Db {
    pub fn open(home: &Path) -> Result<Db> {
        std::fs::create_dir_all(home).with_context(|| format!("creating {}", home.display()))?;
        let conn = Connection::open(home.join("vcs.db")).context("opening the vcs database")?;
        Self::setup(conn)
    }

    pub fn setup(conn: Connection) -> Result<Db> {
        // Many agents on one machine share this database. SQLite's own
        // busy wait sleeps up to 100 ms between tries; this one tries again
        // within a millisecond, for up to a minute.
        conn.busy_handler(Some(|tries: i32| {
            std::thread::sleep(std::time::Duration::from_micros(300 + (tries as u64 % 5) * 150));
            tries < 120_000
        }))?;
        conn.pragma_update(None, "journal_mode", "WAL")?;
        conn.pragma_update(None, "synchronous", "NORMAL")?;
        conn.execute_batch(SCHEMA)?;
        Ok(Db { conn })
    }

    // Projects.

    pub fn put_project(&self, p: &Project) -> Result<()> {
        self.conn.execute(
            "INSERT INTO projects (id, address, kind, api, checked, created) VALUES (?1, ?2, ?3, ?4, ?5, ?6)
             ON CONFLICT(id) DO UPDATE SET address=excluded.address, kind=excluded.kind, api=excluded.api, checked=excluded.checked",
            params![p.id, p.address, p.kind, p.api, p.checked, ids::now()],
        )?;
        Ok(())
    }

    fn project_row(row: &rusqlite::Row) -> rusqlite::Result<Project> {
        Ok(Project { id: row.get(0)?, address: row.get(1)?, kind: row.get(2)?, api: row.get(3)?, checked: row.get(4)? })
    }

    pub fn project(&self, id: &str) -> Result<Option<Project>> {
        Ok(self
            .conn
            .query_row("SELECT id, address, kind, api, checked FROM projects WHERE id = ?1", [id], Self::project_row)
            .optional()?)
    }

    pub fn project_by_address(&self, address: &str) -> Result<Option<Project>> {
        Ok(self
            .conn
            .query_row("SELECT id, address, kind, api, checked FROM projects WHERE address = ?1", [address], Self::project_row)
            .optional()?)
    }

    pub fn set_checked(&self, project: &str, at: i64) -> Result<()> {
        self.conn.execute("UPDATE projects SET checked = ?2 WHERE id = ?1", params![project, at])?;
        Ok(())
    }

    // Versions.

    /// Forgets versions after `n` (the host's project was rolled back).
    pub fn drop_versions_after(&self, project: &str, n: u64) -> Result<usize> {
        Ok(self.conn.execute("DELETE FROM versions WHERE project = ?1 AND n > ?2", params![project, n as i64])?)
    }

    pub fn put_version(&self, project: &str, v: &Version) -> Result<()> {
        self.conn.execute(
            "INSERT OR REPLACE INTO versions (project, n, id, tree, prev, change, author, time, title, body, git)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11)",
            params![
                project,
                v.n as i64,
                v.id(project).hex(),
                v.tree.hex(),
                v.prev.map(|p| p.hex()),
                v.change,
                v.author,
                v.time,
                v.title,
                v.body,
                v.git
            ],
        )?;
        Ok(())
    }

    fn version_row(row: &rusqlite::Row) -> rusqlite::Result<Version> {
        Ok(Version {
            n: row.get::<_, i64>(0)? as u64,
            tree: id_col(row, 1)?,
            prev: opt_id_col(row, 2)?,
            change: row.get(3)?,
            author: row.get(4)?,
            time: row.get(5)?,
            title: row.get(6)?,
            body: row.get(7)?,
            git: row.get(8)?,
        })
    }

    const VERSION_COLUMNS: &'static str = "n, tree, prev, change, author, time, title, body, git";

    pub fn version(&self, project: &str, n: u64) -> Result<Option<Version>> {
        let sql = format!("SELECT {} FROM versions WHERE project = ?1 AND n = ?2", Self::VERSION_COLUMNS);
        Ok(self.conn.query_row(&sql, params![project, n as i64], Self::version_row).optional()?)
    }

    pub fn latest_version(&self, project: &str) -> Result<Option<Version>> {
        let sql = format!("SELECT {} FROM versions WHERE project = ?1 ORDER BY n DESC LIMIT 1", Self::VERSION_COLUMNS);
        Ok(self.conn.query_row(&sql, [project], Self::version_row).optional()?)
    }

    pub fn versions(&self, project: &str, limit: i64) -> Result<Vec<Version>> {
        let sql = format!("SELECT {} FROM versions WHERE project = ?1 ORDER BY n DESC LIMIT ?2", Self::VERSION_COLUMNS);
        let mut stmt = self.conn.prepare(&sql)?;
        let rows = stmt.query_map(params![project, limit], Self::version_row)?;
        Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
    }

    pub fn version_of_change(&self, project: &str, change: &str) -> Result<Option<Version>> {
        let sql = format!("SELECT {} FROM versions WHERE project = ?1 AND change = ?2 ORDER BY n DESC LIMIT 1", Self::VERSION_COLUMNS);
        Ok(self.conn.query_row(&sql, params![project, change], Self::version_row).optional()?)
    }

    // Workspaces.

    pub fn put_workspace(&self, w: &Workspace) -> Result<()> {
        let now = ids::now();
        // A folder holds one workspace: any other one recorded at this path
        // was deleted with its folder. Its change stays.
        self.conn.execute("DELETE FROM workspaces WHERE path = ?1 AND id != ?2", params![w.path.to_string_lossy(), w.id])?;
        self.conn.execute(
            "INSERT INTO workspaces (id, project, path, change, only, created, used) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?6)
             ON CONFLICT(id) DO UPDATE SET project=excluded.project, path=excluded.path, change=excluded.change,
               only=excluded.only, used=excluded.used",
            params![w.id, w.project, w.path.to_string_lossy(), w.change, serde_json::to_string(&w.only)?, now],
        )?;
        Ok(())
    }

    fn workspace_row(row: &rusqlite::Row) -> rusqlite::Result<Workspace> {
        let only: String = row.get(4)?;
        Ok(Workspace {
            id: row.get(0)?,
            project: row.get(1)?,
            path: PathBuf::from(row.get::<_, String>(2)?),
            change: row.get(3)?,
            only: serde_json::from_str(&only).unwrap_or_default(),
        })
    }

    pub fn workspace(&self, id: &str) -> Result<Option<Workspace>> {
        Ok(self
            .conn
            .query_row("SELECT id, project, path, change, only FROM workspaces WHERE id = ?1", [id], Self::workspace_row)
            .optional()?)
    }

    pub fn project_workspaces(&self, project: &str) -> Result<Vec<Workspace>> {
        let mut stmt =
            self.conn.prepare("SELECT id, project, path, change, only FROM workspaces WHERE project = ?1 ORDER BY used DESC")?;
        let rows = stmt.query_map([project], Self::workspace_row)?;
        Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
    }

    pub fn all_workspaces(&self) -> Result<Vec<Workspace>> {
        let mut stmt = self.conn.prepare("SELECT id, project, path, change, only FROM workspaces ORDER BY used DESC")?;
        let rows = stmt.query_map([], Self::workspace_row)?;
        Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
    }

    pub fn workspace_holding(&self, change: &str) -> Result<Option<Workspace>> {
        Ok(self
            .conn
            .query_row(
                "SELECT id, project, path, change, only FROM workspaces WHERE change = ?1 ORDER BY used DESC LIMIT 1",
                [change],
                Self::workspace_row,
            )
            .optional()?)
    }

    pub fn delete_workspace(&self, id: &str) -> Result<()> {
        self.conn.execute("DELETE FROM workspaces WHERE id = ?1", [id])?;
        Ok(())
    }

    // Changes.

    pub fn put_change(&self, c: &Change) -> Result<()> {
        self.conn.execute(
            "INSERT INTO changes (id, project, title, body, base_tree, base_version, on_change, after_change, head_tree,
               saves, state, version, author, conflicts, workspace, shared, remote_rev, created, updated)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19)
             ON CONFLICT(id) DO UPDATE SET title=excluded.title, body=excluded.body, base_tree=excluded.base_tree,
               base_version=excluded.base_version, on_change=excluded.on_change, after_change=excluded.after_change,
               head_tree=excluded.head_tree, saves=excluded.saves, state=excluded.state, version=excluded.version,
               author=excluded.author, conflicts=excluded.conflicts, workspace=excluded.workspace,
               shared=excluded.shared, remote_rev=excluded.remote_rev, updated=excluded.updated",
            params![
                c.id,
                c.project,
                c.title,
                c.body,
                c.base_tree.hex(),
                c.base_version.map(|v| v as i64),
                c.on_change,
                c.after_change,
                c.head_tree.map(|t| t.hex()),
                c.saves,
                c.state,
                c.version.map(|v| v as i64),
                c.author,
                serde_json::to_string(&c.conflicts)?,
                c.workspace,
                serde_json::to_string(&c.shared)?,
                c.remote_rev,
                c.created,
                ids::now()
            ],
        )?;
        Ok(())
    }

    const CHANGE_COLUMNS: &'static str = "id, project, title, body, base_tree, base_version, on_change, after_change, head_tree, saves, state, version, author, conflicts, workspace, shared, remote_rev, created, updated";

    fn change_row(row: &rusqlite::Row) -> rusqlite::Result<Change> {
        let conflicts: String = row.get(13)?;
        let shared: String = row.get(15)?;
        Ok(Change {
            id: row.get(0)?,
            project: row.get(1)?,
            title: row.get(2)?,
            body: row.get(3)?,
            base_tree: id_col(row, 4)?,
            base_version: row.get::<_, Option<i64>>(5)?.map(|v| v as u64),
            on_change: row.get(6)?,
            after_change: row.get(7)?,
            head_tree: opt_id_col(row, 8)?,
            saves: row.get(9)?,
            state: row.get(10)?,
            version: row.get::<_, Option<i64>>(11)?.map(|v| v as u64),
            author: row.get(12)?,
            conflicts: serde_json::from_str(&conflicts).unwrap_or_default(),
            workspace: row.get(14)?,
            shared: serde_json::from_str(&shared).unwrap_or_default(),
            remote_rev: row.get(16)?,
            created: row.get(17)?,
            updated: row.get(18)?,
        })
    }

    pub fn change(&self, id: &str) -> Result<Option<Change>> {
        let sql = format!("SELECT {} FROM changes WHERE id = ?1", Self::CHANGE_COLUMNS);
        Ok(self.conn.query_row(&sql, [id], Self::change_row).optional()?)
    }

    pub fn changes(&self, project: &str) -> Result<Vec<Change>> {
        let sql = format!("SELECT {} FROM changes WHERE project = ?1 ORDER BY updated DESC", Self::CHANGE_COLUMNS);
        let mut stmt = self.conn.prepare(&sql)?;
        let rows = stmt.query_map([project], Self::change_row)?;
        Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
    }

    pub fn change_ids(&self, project: &str) -> Result<Vec<String>> {
        let mut stmt = self.conn.prepare("SELECT id FROM changes WHERE project = ?1")?;
        let rows = stmt.query_map([project], |r| r.get::<_, String>(0))?;
        Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
    }

    pub fn changes_by_prefix(&self, project: &str, prefix: &str) -> Result<Vec<Change>> {
        let sql = format!("SELECT {} FROM changes WHERE project = ?1 AND id LIKE ?2 || '%'", Self::CHANGE_COLUMNS);
        let mut stmt = self.conn.prepare(&sql)?;
        let rows = stmt.query_map(params![project, prefix], Self::change_row)?;
        Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
    }

    // Saves.

    pub fn add_save(&self, s: &Save, workspace: &str) -> Result<()> {
        self.conn.execute(
            "INSERT OR REPLACE INTO saves (change, n, tree, base_tree, at, workspace, uploaded) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
            params![s.change, s.n, s.tree.hex(), s.base_tree.hex(), s.at, workspace, s.uploaded as i64],
        )?;
        Ok(())
    }

    fn save_row(row: &rusqlite::Row) -> rusqlite::Result<Save> {
        Ok(Save {
            change: row.get(0)?,
            n: row.get(1)?,
            tree: id_col(row, 2)?,
            base_tree: id_col(row, 3)?,
            at: row.get(4)?,
            uploaded: row.get::<_, i64>(5)? != 0,
        })
    }

    pub fn saves(&self, change: &str) -> Result<Vec<Save>> {
        let mut stmt = self
            .conn
            .prepare("SELECT change, n, tree, base_tree, at, uploaded FROM saves WHERE change = ?1 ORDER BY n")?;
        let rows = stmt.query_map([change], Self::save_row)?;
        Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
    }

    pub fn save(&self, change: &str, n: i64) -> Result<Option<Save>> {
        Ok(self
            .conn
            .query_row(
                "SELECT change, n, tree, base_tree, at, uploaded FROM saves WHERE change = ?1 AND n = ?2",
                params![change, n],
                Self::save_row,
            )
            .optional()?)
    }

    /// The latest save made in `workspace` at or before `at`, across changes.
    pub fn save_at(&self, workspace: &str, at: i64) -> Result<Option<Save>> {
        Ok(self
            .conn
            .query_row(
                "SELECT change, n, tree, base_tree, at, uploaded FROM saves WHERE workspace = ?1 AND at <= ?2
                 ORDER BY at DESC, rowid DESC LIMIT 1",
                params![workspace, at],
                Self::save_row,
            )
            .optional()?)
    }

    pub fn unuploaded_saves(&self, change: &str) -> Result<Vec<Save>> {
        let mut stmt = self.conn.prepare(
            "SELECT change, n, tree, base_tree, at, uploaded FROM saves WHERE change = ?1 AND uploaded = 0 ORDER BY n",
        )?;
        let rows = stmt.query_map([change], Self::save_row)?;
        Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
    }

    pub fn mark_uploaded(&self, change: &str, n: i64) -> Result<()> {
        self.conn.execute("UPDATE saves SET uploaded = 1 WHERE change = ?1 AND n = ?2", params![change, n])?;
        Ok(())
    }

    /// Drops saves (the secret scrub): every save of `change` goes, and the
    /// caller records a fresh one.
    pub fn delete_saves(&self, change: &str) -> Result<()> {
        self.conn.execute("DELETE FROM saves WHERE change = ?1", [change])?;
        Ok(())
    }

    // Operations.

    pub fn add_op(&self, workspace: &str, kind: &str, data: &serde_json::Value) -> Result<i64> {
        self.conn.execute(
            "INSERT INTO ops (workspace, at, kind, data) VALUES (?1, ?2, ?3, ?4)",
            params![workspace, ids::now(), kind, data.to_string()],
        )?;
        Ok(self.conn.last_insert_rowid())
    }

    fn op_row(row: &rusqlite::Row) -> rusqlite::Result<Op> {
        let data: String = row.get(3)?;
        Ok(Op { id: row.get(0)?, at: row.get(1)?, kind: row.get(2)?, data: serde_json::from_str(&data).unwrap_or_default() })
    }

    pub fn last_op(&self, workspace: &str) -> Result<Option<Op>> {
        Ok(self
            .conn
            .query_row(
                "SELECT id, at, kind, data FROM ops WHERE workspace = ?1 AND undone = 0 ORDER BY id DESC LIMIT 1",
                [workspace],
                Self::op_row,
            )
            .optional()?)
    }

    pub fn ops(&self, workspace: &str, limit: i64) -> Result<Vec<(Op, bool)>> {
        let mut stmt =
            self.conn.prepare("SELECT id, at, kind, data, undone FROM ops WHERE workspace = ?1 ORDER BY id DESC LIMIT ?2")?;
        let rows = stmt.query_map(params![workspace, limit], |row| Ok((Self::op_row(row)?, row.get::<_, i64>(4)? != 0)))?;
        Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
    }

    pub fn mark_undone(&self, id: i64) -> Result<()> {
        self.conn.execute("UPDATE ops SET undone = 1 WHERE id = ?1", [id])?;
        Ok(())
    }

    // Mine files: what the user's own files looked like when last saved.

    pub fn put_mine(&self, project: &str, path: &str, file: &Id, exec: bool) -> Result<()> {
        self.conn.execute(
            "INSERT OR REPLACE INTO mine (project, path, file, exec, at) VALUES (?1, ?2, ?3, ?4, ?5)",
            params![project, path, file.hex(), exec as i64, ids::now()],
        )?;
        Ok(())
    }

    pub fn mine(&self, project: &str) -> Result<Vec<(String, Id, bool)>> {
        let mut stmt = self.conn.prepare("SELECT path, file, exec FROM mine WHERE project = ?1 ORDER BY path")?;
        let rows = stmt.query_map([project], |r| Ok((r.get::<_, String>(0)?, id_col(r, 1)?, r.get::<_, i64>(2)? != 0)))?;
        Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
    }

    /// The newest version this machine has every version up to. A landing
    /// stores its own version before the ones others landed just ahead of
    /// it, so the newest version alone can leave a gap.
    pub fn versions_complete_to(&self, project: &str) -> Result<u64> {
        let first: Option<i64> = self.conn.query_row("SELECT 1 FROM versions WHERE project = ?1 AND n = 1", [project], |r| r.get(0)).optional()?;
        if first.is_none() {
            return Ok(0);
        }
        let n: i64 = self.conn.query_row(
            "SELECT MIN(n) FROM versions v WHERE project = ?1 AND NOT EXISTS (SELECT 1 FROM versions w WHERE w.project = v.project AND w.n = v.n + 1)",
            [project],
            |r| r.get(0),
        )?;
        Ok(n as u64)
    }

    /// Whether some version of main has this tree.
    pub fn versions_with_tree(&self, project: &str, tree: &Id) -> Result<bool> {
        Ok(self.conn.query_row("SELECT 1 FROM versions WHERE project = ?1 AND tree = ?2 LIMIT 1", params![project, tree.hex()], |_| Ok(())).optional()?.is_some())
    }

    /// A tree whose objects all reached the project's host.
    pub fn mark_uploaded_tree(&self, project: &str, tree: &Id) -> Result<()> {
        self.conn.execute("INSERT OR IGNORE INTO uploaded (project, tree) VALUES (?1, ?2)", params![project, tree.hex()])?;
        Ok(())
    }

    pub fn uploaded_tree(&self, project: &str, tree: &Id) -> Result<bool> {
        Ok(self.conn.query_row("SELECT 1 FROM uploaded WHERE project = ?1 AND tree = ?2", params![project, tree.hex()], |_| Ok(())).optional()?.is_some())
    }

    /// Where an imported project's git history came from.
    pub fn set_import(&self, project: &str, source: &str, branch: &str) -> Result<()> {
        self.conn.execute("INSERT OR REPLACE INTO imports (project, source, branch) VALUES (?1, ?2, ?3)", params![project, source, branch])?;
        Ok(())
    }

    pub fn import_source(&self, project: &str) -> Result<Option<(String, String)>> {
        Ok(self
            .conn
            .query_row("SELECT source, branch FROM imports WHERE project = ?1", [project], |r| Ok((r.get(0)?, r.get(1)?)))
            .optional()?)
    }

    /// Native object → git object, for objects an import read from git.
    pub fn put_git_ids(&self, project: &str, ids: &[(Id, String)]) -> Result<()> {
        let tx = self.conn.unchecked_transaction()?;
        {
            let mut stmt = tx.prepare("INSERT OR REPLACE INTO git_ids (project, native, git) VALUES (?1, ?2, ?3)")?;
            for (native, git) in ids {
                stmt.execute(params![project, native.hex(), git])?;
            }
        }
        tx.commit()?;
        Ok(())
    }

    pub fn git_ids(&self, project: &str) -> Result<Vec<(String, String)>> {
        let mut stmt = self.conn.prepare("SELECT native, git FROM git_ids WHERE project = ?1")?;
        let rows = stmt.query_map([project], |r| Ok((r.get(0)?, r.get(1)?)))?;
        Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
    }

    pub fn version_by_git(&self, project: &str, git: &str) -> Result<Option<Version>> {
        let sql = format!("SELECT {} FROM versions WHERE project = ?1 AND git = ?2 ORDER BY n DESC LIMIT 1", Self::VERSION_COLUMNS);
        Ok(self.conn.query_row(&sql, params![project, git], Self::version_row).optional()?)
    }
}