defter 0.7.2

//! Library storage in SQLite.
//!
//! Objects are stored as opaque JSON keyed by (kind, key). Every write bumps
//! the single library version counter inside an immediate transaction and
//! stamps the affected rows with the new version, so the client's `since`
//! reads and `If-Unmodified-Since-Version` writes stay coherent. The columns
//! queries need (type, parent, trash, sort keys, search text, tags,
//! collections) are derived from the JSON on every write.
//!
//! Ported from zhost's PostgreSQL store (<https://github.com/mulatta/zhost>,
//! MIT), with the single-object endpoints and the Web API write semantics
//! commonmeta sync relies on added.

use rusqlite::{
    Connection, OptionalExtension, Result, Row, Transaction, TransactionBehavior, params,
    params_from_iter, types::Value as Sql,
};
use serde_json::{Map, Value, json};

use crate::query::{ItemQuery, QMode};

/// The three synced object kinds.
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum Kind {
    Item,
    Collection,
    Search,
}

impl Kind {
    pub fn as_str(self) -> &'static str {
        match self {
            Kind::Item => "item",
            Kind::Collection => "collection",
            Kind::Search => "search",
        }
    }

    /// The query parameter naming keys of this kind (`itemKey=a,b`).
    pub fn key_param(self) -> &'static str {
        match self {
            Kind::Item => "itemKey",
            Kind::Collection => "collectionKey",
            Kind::Search => "searchKey",
        }
    }
}

/// A write either committed at a new library version or was rejected because
/// the client's `If-Unmodified-Since-Version` no longer matches.
#[derive(Debug)]
pub enum Outcome<T> {
    Done(T),
    Conflict(i64),
}

/// The outcome of a single-object write or delete, where the precondition is
/// the object's own version.
#[derive(Debug)]
pub enum Single {
    /// Committed at this library version; `file_removed` when an attachment
    /// file went with a deleted item.
    Done {
        version: i64,
        file_removed: bool,
    },
    /// The object is at this version, not the one the client expected.
    Conflict(i64),
    NotFound,
    Invalid(String),
}

/// Zotero's object key alphabet: digits 2-9 and A-Z without `O`.
const KEY_ALPHABET: &[u8] = b"23456789ABCDEFGHIJKLMNPQRSTUVWXYZ";

/// A Zotero object key: exactly 8 chars from [`KEY_ALPHABET`]. The client
/// rejects anything else ("key is not valid"), so a malformed key is refused at
/// the API boundary rather than synced.
pub fn valid_object_key(key: &str) -> bool {
    key.len() == 8 && key.bytes().all(|b| KEY_ALPHABET.contains(&b))
}

/// A fresh object key for an object posted without one.
pub fn generate_key() -> String {
    let n = KEY_ALPHABET.len();
    // Reject bytes past the largest multiple of the alphabet size, so every
    // character is equally likely.
    let limit = (256 / n * n) as u8;
    let mut key = String::with_capacity(8);
    while key.len() < 8 {
        let mut buf = [0u8; 16];
        getrandom::fill(&mut buf).expect("OS random number generator");
        for b in buf {
            if b < limit && key.len() < 8 {
                key.push(KEY_ALPHABET[b as usize % n] as char);
            }
        }
    }
    key
}

/// Zotero's `fromJSON` processes fields in order and needs a discriminator
/// first: an attachment's `linkMode` before `filename`/`path`, an annotation's
/// `annotationType` before its other fields. Re-emit both first, whatever order
/// the object was stored in. Relies on serde_json's `preserve_order`.
fn order_fields(data: Value) -> Value {
    const FIRST: [&str; 2] = ["annotationType", "linkMode"];
    let Value::Object(map) = data else {
        return data;
    };
    if !FIRST.iter().any(|k| map.contains_key(*k)) {
        return Value::Object(map);
    }
    let mut ordered = Map::new();
    for key in FIRST {
        if let Some(value) = map.get(key) {
            ordered.insert(key.to_string(), value.clone());
        }
    }
    for (key, value) in map {
        if !FIRST.contains(&key.as_str()) {
            ordered.insert(key, value);
        }
    }
    Value::Object(ordered)
}

// ── derived columns ──────────────────────────────────────────────────────────

/// What queries need from an object's JSON, computed on every write.
#[derive(Debug, Default, PartialEq)]
struct Derived {
    item_type: Option<String>,
    parent_item: Option<String>,
    deleted: bool,
    title: Option<String>,
    creator: Option<String>,
    date_year: Option<String>,
    date_added: Option<String>,
    date_modified: Option<String>,
    search_text: String,
    everything_text: String,
    tags: Vec<String>,
    collections: Vec<String>,
}

fn text_field(data: &Value, name: &str) -> Option<String> {
    data.get(name)
        .and_then(Value::as_str)
        .map(str::trim)
        .filter(|s| !s.is_empty())
        .map(String::from)
}

/// Zotero writes flags as `true`, `1` or `"1"`.
fn truthy(value: Option<&Value>) -> bool {
    match value {
        Some(Value::Bool(b)) => *b,
        Some(Value::Number(n)) => n.as_i64().is_some_and(|n| n != 0),
        Some(Value::String(s)) => s == "1" || s == "true",
        _ => false,
    }
}

/// The first standalone 4-digit run in a freeform date ("circa 1990" → 1990;
/// "12345" → none).
fn year_of(date: &str) -> Option<String> {
    let bytes = date.as_bytes();
    let digit = |i: usize| bytes.get(i).is_some_and(u8::is_ascii_digit);
    (0..bytes.len().saturating_sub(3)).find_map(|i| {
        let run = (i..i + 4).all(digit);
        let bounded = (i == 0 || !digit(i - 1)) && !digit(i + 4);
        (run && bounded).then(|| date[i..i + 4].to_string())
    })
}

/// Fields that hold no searchable text.
const NOT_SEARCHED: &[&str] = &[
    "key",
    "version",
    "parentItem",
    "collections",
    "relations",
    "dateAdded",
    "dateModified",
    "md5",
    "mtime",
];

fn collect_text(value: &Value, out: &mut Vec<String>) {
    match value {
        Value::String(s) if !s.is_empty() => out.push(s.to_lowercase()),
        Value::Array(items) => items.iter().for_each(|v| collect_text(v, out)),
        Value::Object(map) => map.values().for_each(|v| collect_text(v, out)),
        _ => {}
    }
}

fn derive(data: &Value) -> Derived {
    let creators: Vec<&Value> = data
        .get("creators")
        .and_then(Value::as_array)
        .map(|a| a.iter().collect())
        .unwrap_or_default();
    let creator = creators
        .first()
        .and_then(|c| text_field(c, "lastName").or_else(|| text_field(c, "name")));

    let mut search = Vec::new();
    for field in ["title", "date"] {
        if let Some(text) = text_field(data, field) {
            search.push(text.to_lowercase());
        }
    }
    for c in &creators {
        for field in ["lastName", "firstName", "name"] {
            if let Some(text) = text_field(c, field) {
                search.push(text.to_lowercase());
            }
        }
    }

    let mut everything = Vec::new();
    if let Value::Object(map) = data {
        for (field, value) in map {
            if !NOT_SEARCHED.contains(&field.as_str()) {
                collect_text(value, &mut everything);
            }
        }
    }

    let tags = data
        .get("tags")
        .and_then(Value::as_array)
        .map(|tags| {
            tags.iter()
                .filter_map(|t| t.get("tag").and_then(Value::as_str))
                .filter(|t| !t.is_empty())
                .map(String::from)
                .collect()
        })
        .unwrap_or_default();
    let collections = data
        .get("collections")
        .and_then(Value::as_array)
        .map(|keys| {
            keys.iter()
                .filter_map(Value::as_str)
                .map(String::from)
                .collect()
        })
        .unwrap_or_default();

    Derived {
        item_type: text_field(data, "itemType"),
        // A top-level item has no parentItem, or `false`.
        parent_item: text_field(data, "parentItem"),
        deleted: truthy(data.get("deleted")),
        title: text_field(data, "title"),
        creator,
        date_year: text_field(data, "date").and_then(|d| year_of(&d)),
        date_added: text_field(data, "dateAdded"),
        date_modified: text_field(data, "dateModified"),
        search_text: search.join(" "),
        everything_text: everything.join(" "),
        tags,
        collections,
    }
}

// ── primitives ───────────────────────────────────────────────────────────────

fn begin(conn: &mut Connection) -> Result<Transaction<'_>> {
    conn.transaction_with_behavior(TransactionBehavior::Immediate)
}

pub fn current_version(conn: &Connection) -> Result<i64> {
    conn.query_row("SELECT version FROM library WHERE id = 1", [], |r| r.get(0))
}

fn set_version(tx: &Connection, version: i64) -> Result<()> {
    tx.execute("UPDATE library SET version = ?1 WHERE id = 1", [version])?;
    Ok(())
}

/// Check the client's expected library version inside the transaction and
/// reserve the next one. The single writer connection and the immediate
/// transaction serialize writes, so check and bump are atomic.
fn guarded_version(tx: &Connection, expected: Option<i64>) -> Result<Outcome<i64>> {
    let current = current_version(tx)?;
    if matches!(expected, Some(v) if v != current) {
        return Ok(Outcome::Conflict(current));
    }
    Ok(Outcome::Done(current + 1))
}

/// A write that changes nothing: report the current version (or a conflict)
/// without bumping it, so a no-op does not make every other client resync.
fn no_change(conn: &Connection, expected: Option<i64>) -> Result<Outcome<i64>> {
    let current = current_version(conn)?;
    Ok(match expected {
        Some(v) if v != current => Outcome::Conflict(current),
        _ => Outcome::Done(current),
    })
}

fn placeholders(n: usize) -> String {
    vec!["?"; n].join(",")
}

fn parse_json(text: &str) -> Value {
    serde_json::from_str(text).unwrap_or(Value::Null)
}

fn get_object(conn: &Connection, kind: Kind, key: &str) -> Result<Option<(i64, Value)>> {
    conn.query_row(
        "SELECT version, data FROM object WHERE kind = ?1 AND key = ?2",
        params![kind.as_str(), key],
        |r| Ok((r.get(0)?, parse_json(&r.get::<_, String>(1)?))),
    )
    .optional()
}

/// Who makes a change to a group library, as its items record it.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct Actor {
    pub id: u64,
    pub username: String,
    pub name: String,
}

/// Record `actor`'s names, for the objects they add or change.
fn note_actor(tx: &Connection, actor: Option<&Actor>) -> Result<Option<i64>> {
    let Some(actor) = actor else {
        return Ok(None);
    };
    tx.execute(
        "INSERT INTO person (id, username, name) VALUES (?1, ?2, ?3)
         ON CONFLICT (id) DO UPDATE SET username = excluded.username, name = excluded.name",
        params![actor.id as i64, actor.username, actor.name],
    )?;
    Ok(Some(actor.id as i64))
}

/// Store `data` as object `key` at `version`, refreshing its derived columns,
/// tag and collection rows, and dropping any deletion-log entry for it. `actor`
/// added it if it is new, and changed it last.
fn put_object(
    tx: &Connection,
    kind: Kind,
    key: &str,
    version: i64,
    data: &Value,
    actor: Option<&Actor>,
) -> Result<()> {
    let d = derive(data);
    let actor = note_actor(tx, actor)?;
    tx.execute(
        "INSERT INTO object (kind, key, version, data, item_type, parent_item, deleted, title,
                             creator, date_year, date_added, date_modified, search_text,
                             everything_text, created_by, modified_by)
         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?15)
         ON CONFLICT (kind, key) DO UPDATE SET
             version = excluded.version, data = excluded.data,
             item_type = excluded.item_type, parent_item = excluded.parent_item,
             deleted = excluded.deleted, title = excluded.title, creator = excluded.creator,
             date_year = excluded.date_year, date_added = excluded.date_added,
             date_modified = excluded.date_modified, search_text = excluded.search_text,
             everything_text = excluded.everything_text,
             created_by = coalesce(object.created_by, excluded.created_by),
             modified_by = coalesce(excluded.modified_by, object.modified_by)",
        params![
            kind.as_str(),
            key,
            version,
            data.to_string(),
            d.item_type,
            d.parent_item,
            d.deleted,
            d.title,
            d.creator,
            d.date_year,
            d.date_added,
            d.date_modified,
            d.search_text,
            d.everything_text,
            actor,
        ],
    )?;
    if kind == Kind::Item {
        tx.execute("DELETE FROM item_tag WHERE item_key = ?1", [key])?;
        for tag in &d.tags {
            tx.execute(
                "INSERT OR IGNORE INTO item_tag (item_key, tag) VALUES (?1, ?2)",
                [key, tag],
            )?;
            // A tag in use again is no longer deleted: a client syncing from an
            // older version would otherwise strip it from every item.
            tx.execute(
                "DELETE FROM deletion WHERE kind = 'tag' AND key = ?1",
                [tag],
            )?;
        }
        tx.execute("DELETE FROM item_collection WHERE item_key = ?1", [key])?;
        for collection in &d.collections {
            tx.execute(
                "INSERT OR IGNORE INTO item_collection (item_key, collection_key) VALUES (?1, ?2)",
                [key, collection],
            )?;
        }
    }
    tx.execute(
        "DELETE FROM deletion WHERE kind = ?1 AND key = ?2",
        [kind.as_str(), key],
    )?;
    Ok(())
}

/// Record a deletion at `version` for `/deleted`.
fn log_deletion(tx: &Connection, kind: &str, key: &str, version: i64) -> Result<()> {
    tx.execute(
        "INSERT INTO deletion (kind, key, version) VALUES (?1, ?2, ?3)
         ON CONFLICT (kind, key) DO UPDATE SET version = excluded.version",
        params![kind, key, version],
    )?;
    Ok(())
}

/// Delete an object and what hangs off it, logging the deletion. Returns
/// whether it existed and whether an attachment file was registered for it (so
/// the caller can remove the bytes from object storage).
fn remove_object(tx: &Connection, kind: Kind, key: &str, version: i64) -> Result<(bool, bool)> {
    let existed = tx.execute(
        "DELETE FROM object WHERE kind = ?1 AND key = ?2",
        [kind.as_str(), key],
    )? > 0;
    let mut had_file = false;
    if kind == Kind::Item {
        tx.execute("DELETE FROM item_tag WHERE item_key = ?1", [key])?;
        tx.execute("DELETE FROM item_collection WHERE item_key = ?1", [key])?;
        tx.execute("DELETE FROM fulltext WHERE item_key = ?1", [key])?;
        had_file = tx.execute("DELETE FROM file WHERE item_key = ?1", [key])? > 0;
    }
    log_deletion(tx, kind.as_str(), key, version)?;
    Ok((existed, had_file))
}

// ── reads ────────────────────────────────────────────────────────────────────

/// The columns [`object_row`] reads: key, version, data, for top-level items
/// how many children (attachments, notes) they have, and in a group library who
/// added and last changed it.
const OBJECT_COLUMNS: &str = "key, version, data,
    CASE WHEN kind = 'item' AND parent_item IS NULL THEN
        (SELECT count(*) FROM object c
         WHERE c.kind = 'item' AND c.parent_item = object.key AND c.deleted = 0)
    END,
    created_by, (SELECT username FROM person WHERE id = created_by),
    (SELECT name FROM person WHERE id = created_by),
    modified_by, (SELECT username FROM person WHERE id = modified_by),
    (SELECT name FROM person WHERE id = modified_by)";

/// `{key, version, meta?, data}` — the Web API object shape.
fn object_row(row: &Row) -> Result<Value> {
    let key: String = row.get(0)?;
    let version: i64 = row.get(1)?;
    let data = order_fields(parse_json(&row.get::<_, String>(2)?));
    let num_children: Option<i64> = row.get(3)?;
    let person = |at: usize| -> Result<Option<Value>> {
        let Some(id) = row.get::<_, Option<i64>>(at)? else {
            return Ok(None);
        };
        let username: String = row.get::<_, Option<String>>(at + 1)?.unwrap_or_default();
        let name: String = row.get::<_, Option<String>>(at + 2)?.unwrap_or_default();
        Ok(Some(
            json!({ "id": id, "username": username, "name": name }),
        ))
    };
    let mut meta = Map::new();
    if let Some(user) = person(4)? {
        meta.insert("createdByUser".into(), user);
    }
    if let Some(user) = person(7)? {
        meta.insert("lastModifiedByUser".into(), user);
    }
    if let Some(n) = num_children {
        meta.insert("numChildren".into(), n.into());
    }
    let mut object = Map::new();
    object.insert("key".into(), key.into());
    object.insert("version".into(), version.into());
    if !meta.is_empty() {
        object.insert("meta".into(), Value::Object(meta));
    }
    object.insert("data".into(), data);
    Ok(Value::Object(object))
}

/// `{key: version}` for objects of `kind` changed after `since`; with `top`,
/// only top-level items (the client's parent-first phase).
pub fn versions(conn: &Connection, kind: Kind, since: i64, top: bool) -> Result<Value> {
    let sql = format!(
        "SELECT key, version FROM object WHERE kind = ?1 AND version > ?2{}",
        if top { " AND parent_item IS NULL" } else { "" }
    );
    let mut stmt = conn.prepare(&sql)?;
    let rows = stmt.query_map(params![kind.as_str(), since], |r| {
        Ok((r.get::<_, String>(0)?, r.get::<_, i64>(1)?))
    })?;
    let mut map = Map::new();
    for row in rows {
        let (key, version) = row?;
        map.insert(key, version.into());
    }
    Ok(Value::Object(map))
}

/// `[{key, version, data}]` for the requested keys.
pub fn objects(conn: &Connection, kind: Kind, keys: &[String]) -> Result<Vec<Value>> {
    if keys.is_empty() {
        return Ok(Vec::new());
    }
    let sql = format!(
        "SELECT {OBJECT_COLUMNS} FROM object WHERE kind = ? AND key IN ({}) ORDER BY key",
        placeholders(keys.len())
    );
    let mut values = vec![Sql::from(kind.as_str().to_string())];
    values.extend(keys.iter().cloned().map(Sql::from));
    let mut stmt = conn.prepare(&sql)?;
    stmt.query_map(params_from_iter(values), object_row)?
        .collect()
}

/// One object with its own version, or `None`.
pub fn object(conn: &Connection, kind: Kind, key: &str) -> Result<Option<(i64, Value)>> {
    let sql = format!("SELECT {OBJECT_COLUMNS} FROM object WHERE kind = ?1 AND key = ?2");
    conn.query_row(&sql, params![kind.as_str(), key], |r| {
        Ok((r.get::<_, i64>(1)?, object_row(r)?))
    })
    .optional()
}

/// A page of collections or searches, and their total count.
pub fn list_objects(
    conn: &Connection,
    kind: Kind,
    limit: i64,
    start: i64,
) -> Result<(Vec<Value>, i64)> {
    let total = conn.query_row(
        "SELECT count(*) FROM object WHERE kind = ?1",
        [kind.as_str()],
        |r| r.get(0),
    )?;
    let sql = format!(
        "SELECT {OBJECT_COLUMNS} FROM object WHERE kind = ?1 ORDER BY key LIMIT ?2 OFFSET ?3"
    );
    let mut stmt = conn.prepare(&sql)?;
    let items = stmt
        .query_map(params![kind.as_str(), limit, start], object_row)?
        .collect::<Result<_>>()?;
    Ok((items, total))
}

/// Append the shared `WHERE` predicate for an item query, binding its values.
/// The count, page and key queries all use it, so they filter identically.
fn push_item_filters(sql: &mut String, values: &mut Vec<Sql>, q: &ItemQuery) {
    sql.push_str(" WHERE kind = 'item'");

    if q.only_trashed {
        sql.push_str(" AND deleted = 1");
    } else if !q.include_trashed {
        sql.push_str(" AND deleted = 0");
    }

    if let Some(term) = &q.q {
        let term = term.to_lowercase();
        match q.qmode {
            QMode::TitleCreatorYear => {
                sql.push_str(" AND instr(search_text, ?) > 0");
                values.push(term.into());
            }
            QMode::Everything => {
                // Full text is matched with LIKE, which folds ASCII case only;
                // the derived text columns are lowercased in full.
                let like = format!(
                    "%{}%",
                    term.replace('\\', "\\\\")
                        .replace('%', "\\%")
                        .replace('_', "\\_")
                );
                sql.push_str(
                    " AND (instr(everything_text, ?) > 0 OR EXISTS (
                         SELECT 1 FROM fulltext f WHERE f.item_key = object.key
                         AND f.content LIKE ? ESCAPE '\\'))",
                );
                values.push(term.into());
                values.push(like.into());
            }
        }
    }

    if !q.item_type.include.is_empty() {
        sql.push_str(&format!(
            " AND item_type IN ({})",
            placeholders(q.item_type.include.len())
        ));
        values.extend(q.item_type.include.iter().cloned().map(Sql::from));
    }
    if !q.item_type.exclude.is_empty() {
        sql.push_str(&format!(
            " AND (item_type IS NULL OR item_type NOT IN ({}))",
            placeholders(q.item_type.exclude.len())
        ));
        values.extend(q.item_type.exclude.iter().cloned().map(Sql::from));
    }

    // AND across groups, OR within a group.
    for group in &q.tags {
        sql.push_str(&format!(
            " AND EXISTS (SELECT 1 FROM item_tag t WHERE t.item_key = object.key AND t.tag IN ({}))",
            placeholders(group.len())
        ));
        values.extend(group.iter().cloned().map(Sql::from));
    }

    if q.top {
        sql.push_str(" AND parent_item IS NULL");
    }
    if let Some(parent) = &q.parent {
        sql.push_str(" AND parent_item = ?");
        values.push(parent.clone().into());
    }
    if let Some(collection) = &q.collection {
        sql.push_str(
            " AND EXISTS (SELECT 1 FROM item_collection c
                 WHERE c.item_key = object.key AND c.collection_key = ?)",
        );
        values.push(collection.clone().into());
    }
}

/// An item listing: filter, search, sort and page. Returns the page and the
/// full match count, counted separately so it stays right past the last page.
pub fn query_items(conn: &Connection, q: &ItemQuery) -> Result<(Vec<Value>, i64)> {
    let mut count_sql = String::from("SELECT count(*) FROM object");
    let mut values = Vec::new();
    push_item_filters(&mut count_sql, &mut values, q);
    let total: i64 = conn.query_row(&count_sql, params_from_iter(values), |r| r.get(0))?;

    let mut sql = format!("SELECT {OBJECT_COLUMNS} FROM object");
    let mut values = Vec::new();
    push_item_filters(&mut sql, &mut values, q);
    // order_expr/sql() are fixed strings, never user input.
    sql.push_str(&format!(
        " ORDER BY {} {} NULLS LAST, key ASC LIMIT ? OFFSET ?",
        q.sort.order_expr(),
        q.direction.sql()
    ));
    values.push(q.limit.into());
    values.push(q.start.into());
    let mut stmt = conn.prepare(&sql)?;
    let items = stmt
        .query_map(params_from_iter(values), object_row)?
        .collect::<Result<_>>()?;
    Ok((items, total))
}

/// Every matching item key, unpaged (`format=keys`).
pub fn item_keys(conn: &Connection, q: &ItemQuery) -> Result<Vec<String>> {
    let mut sql = String::from("SELECT key FROM object");
    let mut values = Vec::new();
    push_item_filters(&mut sql, &mut values, q);
    sql.push_str(" ORDER BY key");
    let mut stmt = conn.prepare(&sql)?;
    stmt.query_map(params_from_iter(values), |r| r.get(0))?
        .collect()
}

/// Distinct tags of items not in the trash, with item counts.
pub fn tags(conn: &Connection) -> Result<Vec<Value>> {
    let mut stmt = conn.prepare(
        "SELECT t.tag, count(*) FROM item_tag t
         JOIN object o ON o.kind = 'item' AND o.key = t.item_key
         WHERE o.deleted = 0
         GROUP BY t.tag ORDER BY t.tag",
    )?;
    stmt.query_map([], |r| {
        Ok(json!({
            "tag": r.get::<_, String>(0)?,
            "meta": { "type": 0, "numItems": r.get::<_, i64>(1)? },
        }))
    })?
    .collect()
}

// ── object writes ────────────────────────────────────────────────────────────

/// Stamp an item's dates as zotero.org does on a write through the API, so
/// that a change made on the server — by commonmeta sync, say — reaches the
/// Zotero client as one: `dateAdded` is kept (the stored one when `data` lacks
/// it), else now; `dateModified` is now when the client sent none, or when a
/// replacing write (`PUT`) echoes the stored value back. A date the client
/// sends is otherwise kept, so syncing from Zotero changes nothing.
fn stamp_dates(
    data: &mut Map<String, Value>,
    provided: &Map<String, Value>,
    stored: Option<&Value>,
    replace: bool,
    now: &str,
) {
    let date = |map: Option<&Map<String, Value>>, field: &str| {
        map.and_then(|m| m.get(field))
            .and_then(Value::as_str)
            .filter(|d| !d.trim().is_empty())
            .map(String::from)
    };
    let stored = stored.and_then(Value::as_object);
    if date(Some(data), "dateAdded").is_none() {
        let added = date(stored, "dateAdded").unwrap_or_else(|| now.to_string());
        data.insert("dateAdded".into(), added.into());
    }
    let sent = date(Some(provided), "dateModified");
    let echoed = replace && sent.is_some() && sent == date(stored, "dateModified");
    if sent.is_none() || echoed {
        data.insert("dateModified".into(), now.into());
    }
}

/// Remove the fields of an item that a write sets to `""`. A Zotero client
/// clears a field that way — when an item changes type, every field the new
/// type lacks — and zotero.org keeps no empty fields. Kept, a field the item's
/// type does not have breaks the next client that downloads the item: Zotero
/// refuses `"libraryCatalog": ""` on a blog post as an invalid field.
fn drop_cleared(data: &mut Map<String, Value>) {
    data.retain(|_, v| v.as_str() != Some(""));
}

/// Now as Zotero's API writes times.
fn now_iso() -> String {
    crate::backup::iso_time(crate::control::now().max(0) as u64)
}

/// What a multi-object write did, keyed by the object's index in the batch.
#[derive(Debug, Default)]
pub struct BatchResult {
    pub version: i64,
    pub successful: Map<String, Value>,
    pub failed: Map<String, Value>,
}

fn failure(key: Option<&str>, code: u16, message: &str) -> Value {
    json!({ "key": key.unwrap_or(""), "code": code, "message": message })
}

/// Store a batch (`POST`/`PATCH` on a collection endpoint), creating or
/// updating each object with **merge** semantics: provided top-level fields
/// are overlaid onto the stored object and omitted ones kept. The Zotero client
/// uploads only an existing object's changed fields, so a full replace would
/// drop `itemType`/`linkMode` and corrupt it.
///
/// With `expected` (the client's `If-Unmodified-Since-Version`) the whole batch
/// is checked against the library version. Without it — the header is
/// optional in the Web API, and commonmeta sends none when creating — an
/// object carrying a `version` that differs from the stored one fails with 412
/// on its own.
pub fn write_batch(
    conn: &mut Connection,
    kind: Kind,
    batch: Vec<Value>,
    expected: Option<i64>,
    actor: Option<&Actor>,
) -> Result<Outcome<BatchResult>> {
    if batch.is_empty() {
        return Ok(match no_change(conn, expected)? {
            Outcome::Done(version) => Outcome::Done(BatchResult {
                version,
                ..Default::default()
            }),
            Outcome::Conflict(current) => Outcome::Conflict(current),
        });
    }
    let tx = begin(conn)?;
    let version = match guarded_version(&tx, expected)? {
        Outcome::Done(version) => version,
        Outcome::Conflict(current) => return Ok(Outcome::Conflict(current)),
    };

    let mut result = BatchResult {
        version,
        ..Default::default()
    };
    let now = now_iso();
    for (index, provided) in batch.into_iter().enumerate() {
        let index = index.to_string();
        let Value::Object(fields) = provided else {
            result
                .failed
                .insert(index, failure(None, 400, "object expected"));
            continue;
        };
        let key = match fields.get("key").and_then(Value::as_str) {
            Some(key) if !valid_object_key(key) => {
                result
                    .failed
                    .insert(index, failure(Some(key), 400, "invalid object key"));
                continue;
            }
            Some(key) => key.to_string(),
            None => generate_key(),
        };
        let existing = get_object(&tx, kind, &key)?;
        if expected.is_none()
            && let (Some((stored, _)), Some(given)) =
                (&existing, fields.get("version").and_then(Value::as_i64))
            && given != *stored
        {
            result.failed.insert(
                index,
                failure(
                    Some(&key),
                    412,
                    &format!("object has been modified since version {given}"),
                ),
            );
            continue;
        }
        let stored = existing.as_ref().map(|(_, data)| data.clone());
        let mut data = match existing {
            Some((_, Value::Object(mut base))) => {
                base.extend(fields.clone());
                base
            }
            _ => fields.clone(),
        };
        if kind == Kind::Item {
            drop_cleared(&mut data);
            stamp_dates(&mut data, &fields, stored.as_ref(), false, &now);
        }
        data.insert("key".into(), key.clone().into());
        data.insert("version".into(), version.into());
        let data = Value::Object(data);
        put_object(&tx, kind, &key, version, &data, actor)?;
        result.successful.insert(
            index,
            json!({ "key": key, "version": version, "data": order_fields(data) }),
        );
    }

    if result.successful.is_empty() {
        // Nothing changed: leave the version where it was.
        drop(tx);
        result.version = current_version(conn)?;
        return Ok(Outcome::Done(result));
    }
    set_version(&tx, version)?;
    tx.commit()?;
    Ok(Outcome::Done(result))
}

/// Write one object (`PUT`/`PATCH /items/<key>`). `expected` is the object's
/// own version (from `If-Unmodified-Since-Version` or the body's `version`);
/// `0` creates an object that does not exist yet (`PUT` only). `merge` overlays
/// the provided fields (`PATCH`); otherwise they replace the object (`PUT`).
pub fn write_one(
    conn: &mut Connection,
    kind: Kind,
    key: &str,
    provided: Value,
    expected: i64,
    merge: bool,
    actor: Option<&Actor>,
) -> Result<Single> {
    let Value::Object(fields) = provided else {
        return Ok(Single::Invalid("object expected".into()));
    };
    if !valid_object_key(key) {
        return Ok(Single::Invalid(format!("invalid object key: {key}")));
    }
    if fields
        .get("key")
        .and_then(Value::as_str)
        .is_some_and(|k| k != key)
    {
        return Ok(Single::Invalid("key does not match the URL".into()));
    }
    let tx = begin(conn)?;
    let existing = get_object(&tx, kind, key)?;
    let stored = existing.as_ref().map(|(_, data)| data.clone());
    let mut data = match existing {
        Some((stored, _)) if stored != expected => return Ok(Single::Conflict(stored)),
        Some((_, Value::Object(mut base))) if merge => {
            base.extend(fields.clone());
            base
        }
        Some(_) => fields.clone(),
        None if merge || expected != 0 => return Ok(Single::NotFound),
        None => fields.clone(),
    };
    if kind == Kind::Item {
        drop_cleared(&mut data);
        stamp_dates(&mut data, &fields, stored.as_ref(), !merge, &now_iso());
    }
    let version = current_version(&tx)? + 1;
    data.insert("key".into(), key.into());
    data.insert("version".into(), version.into());
    put_object(&tx, kind, key, version, &Value::Object(data), actor)?;
    set_version(&tx, version)?;
    tx.commit()?;
    Ok(Single::Done {
        version,
        file_removed: false,
    })
}

/// Delete one object if it is still at `expected`.
pub fn delete_one(conn: &mut Connection, kind: Kind, key: &str, expected: i64) -> Result<Single> {
    let tx = begin(conn)?;
    match get_object(&tx, kind, key)? {
        None => return Ok(Single::NotFound),
        Some((stored, _)) if stored != expected => return Ok(Single::Conflict(stored)),
        Some(_) => {}
    }
    let version = current_version(&tx)? + 1;
    let (_, file_removed) = remove_object(&tx, kind, key, version)?;
    set_version(&tx, version)?;
    tx.commit()?;
    Ok(Single::Done {
        version,
        file_removed,
    })
}

/// Delete objects of `kind`, recording them in the deletion log. Returns the
/// new library version and the item keys whose attachment files went too.
pub fn delete_batch(
    conn: &mut Connection,
    kind: Kind,
    keys: &[String],
    expected: Option<i64>,
) -> Result<Outcome<(i64, Vec<String>)>> {
    if keys.is_empty() {
        return Ok(match no_change(conn, expected)? {
            Outcome::Done(v) => Outcome::Done((v, Vec::new())),
            Outcome::Conflict(c) => Outcome::Conflict(c),
        });
    }
    let tx = begin(conn)?;
    let version = match guarded_version(&tx, expected)? {
        Outcome::Done(version) => version,
        Outcome::Conflict(current) => return Ok(Outcome::Conflict(current)),
    };
    let mut files = Vec::new();
    for key in keys {
        let (_, had_file) = remove_object(&tx, kind, key, version)?;
        if had_file {
            files.push(key.clone());
        }
    }
    set_version(&tx, version)?;
    tx.commit()?;
    Ok(Outcome::Done((version, files)))
}

/// Remove tags library-wide: strip each from every item carrying it (bumping
/// those items) and log it, so `/deleted` propagates the removal even for a
/// tag no item carries any more.
pub fn delete_tags(
    conn: &mut Connection,
    tags: &[String],
    expected: Option<i64>,
    actor: Option<&Actor>,
) -> Result<Outcome<i64>> {
    if tags.is_empty() {
        return no_change(conn, expected);
    }
    let tx = begin(conn)?;
    let version = match guarded_version(&tx, expected)? {
        Outcome::Done(version) => version,
        Outcome::Conflict(current) => return Ok(Outcome::Conflict(current)),
    };
    for tag in tags {
        let keys: Vec<String> = {
            let mut stmt = tx.prepare("SELECT item_key FROM item_tag WHERE tag = ?1")?;
            stmt.query_map([tag], |r| r.get(0))?
                .collect::<Result<_>>()?
        };
        for key in keys {
            let Some((_, Value::Object(mut data))) = get_object(&tx, Kind::Item, &key)? else {
                continue;
            };
            if let Some(Value::Array(item_tags)) = data.get_mut("tags") {
                item_tags.retain(|t| t.get("tag").and_then(Value::as_str) != Some(tag.as_str()));
            }
            data.insert("version".into(), version.into());
            put_object(&tx, Kind::Item, &key, version, &Value::Object(data), actor)?;
        }
        log_deletion(&tx, "tag", tag, version)?;
    }
    set_version(&tx, version)?;
    tx.commit()?;
    Ok(Outcome::Done(version))
}

// ── settings ─────────────────────────────────────────────────────────────────

/// Settings changed after `since`, as `{key: {value, version}}`.
pub fn settings(conn: &Connection, since: i64) -> Result<Value> {
    let mut stmt = conn.prepare("SELECT key, version, value FROM setting WHERE version > ?1")?;
    let rows = stmt.query_map([since], |r| {
        Ok((
            r.get::<_, String>(0)?,
            r.get::<_, i64>(1)?,
            parse_json(&r.get::<_, String>(2)?),
        ))
    })?;
    let mut map = Map::new();
    for row in rows {
        let (key, version, value) = row?;
        map.insert(key, json!({ "value": value, "version": version }));
    }
    Ok(Value::Object(map))
}

/// Store a `{key: {value}}` settings object. A setting whose value is unchanged
/// keeps its version, and if none changes neither does the library's, as on
/// zotero.org. Clients count on it: Zotero sends `feeds` back unchanged after
/// downloading it, so a new version each time would have clients syncing it back
/// and forth, and turn real changes on other devices into conflicts.
pub fn write_settings(
    conn: &mut Connection,
    body: Value,
    expected: Option<i64>,
) -> Result<Outcome<i64>> {
    let Value::Object(entries) = body else {
        return no_change(conn, expected);
    };
    if entries.is_empty() {
        return no_change(conn, expected);
    }
    let tx = begin(conn)?;
    let version = match guarded_version(&tx, expected)? {
        Outcome::Done(version) => version,
        Outcome::Conflict(current) => return Ok(Outcome::Conflict(current)),
    };
    let mut changed = false;
    for (key, entry) in entries {
        let value = entry.get("value").cloned().unwrap_or(entry);
        let stored: Option<String> = tx
            .query_row("SELECT value FROM setting WHERE key = ?1", [&key], |r| {
                r.get(0)
            })
            .optional()?;
        // As values, so that a different order of an object's keys is no change.
        if stored.is_some_and(|stored| parse_json(&stored) == value) {
            continue;
        }
        changed = true;
        tx.execute(
            "INSERT INTO setting (key, version, value) VALUES (?1, ?2, ?3)
             ON CONFLICT (key) DO UPDATE SET version = excluded.version, value = excluded.value",
            params![key, version, value.to_string()],
        )?;
        tx.execute(
            "DELETE FROM deletion WHERE kind = 'setting' AND key = ?1",
            [&key],
        )?;
    }
    if !changed {
        return Ok(Outcome::Done(version - 1));
    }
    set_version(&tx, version)?;
    tx.commit()?;
    Ok(Outcome::Done(version))
}

/// Delete the named settings, logging each for `/deleted`.
pub fn delete_settings(
    conn: &mut Connection,
    keys: &[String],
    expected: Option<i64>,
) -> Result<Outcome<i64>> {
    if keys.is_empty() {
        return no_change(conn, expected);
    }
    let tx = begin(conn)?;
    let version = match guarded_version(&tx, expected)? {
        Outcome::Done(version) => version,
        Outcome::Conflict(current) => return Ok(Outcome::Conflict(current)),
    };
    for key in keys {
        tx.execute("DELETE FROM setting WHERE key = ?1", [key])?;
        log_deletion(&tx, "setting", key, version)?;
    }
    set_version(&tx, version)?;
    tx.commit()?;
    Ok(Outcome::Done(version))
}

/// Deleted keys after `since`, grouped by kind.
pub fn deleted(conn: &Connection, since: i64) -> Result<Value> {
    let mut stmt = conn.prepare("SELECT kind, key FROM deletion WHERE version > ?1")?;
    let rows = stmt.query_map([since], |r| {
        Ok((r.get::<_, String>(0)?, r.get::<_, String>(1)?))
    })?;
    let (mut collections, mut searches, mut items, mut settings, mut tags) =
        (Vec::new(), Vec::new(), Vec::new(), Vec::new(), Vec::new());
    for row in rows {
        let (kind, key) = row?;
        match kind.as_str() {
            "collection" => collections.push(Value::from(key)),
            "search" => searches.push(Value::from(key)),
            "setting" => settings.push(Value::from(key)),
            // Tag deletions are {tag, type}; the type is not kept, so 0.
            "tag" => tags.push(json!({ "tag": key, "type": 0 })),
            _ => items.push(Value::from(key)),
        }
    }
    Ok(json!({
        "collections": collections,
        "searches": searches,
        "items": items,
        "settings": settings,
        "tags": tags,
    }))
}

// ── files ────────────────────────────────────────────────────────────────────

/// A registered attachment file.
#[derive(Debug, Clone, PartialEq)]
pub struct FileMeta {
    pub md5: String,
    pub filename: String,
    pub filesize: i64,
    /// Modification time in milliseconds.
    pub mtime: i64,
    pub content_type: Option<String>,
    /// Stored as the ZIP Zotero uploaded: `md5` is the main file's, `filesize`
    /// the ZIP's.
    pub compressed: bool,
}

pub fn file_meta(conn: &Connection, item_key: &str) -> Result<Option<FileMeta>> {
    conn.query_row(
        "SELECT md5, filename, filesize, mtime, content_type, compressed
         FROM file WHERE item_key = ?1",
        [item_key],
        |r| {
            Ok(FileMeta {
                md5: r.get(0)?,
                filename: r.get(1)?,
                filesize: r.get(2)?,
                mtime: r.get(3)?,
                content_type: r.get(4)?,
                compressed: r.get(5)?,
            })
        },
    )
    .optional()
}

// ── public works ─────────────────────────────────────────────────────────────
//
// With the public profile enabled, every work in My Publications is public, as
// long as it is at the top level and not in the trash. Its tags are not used
// or shown, they are the owner's own notes, with one exception: a manual tag
// `featured` (any case) marks the works "featured only" narrows to. Automatic
// tags (`type: 1`) don't count: imports bring subject tags, such as a blog's
// own `featured`, that say nothing about the owner's choice.

/// SQL: the item `o` is a work in My Publications.
const PUBLIC_WORK: &str = "o.kind = 'item' AND o.deleted = 0 AND o.parent_item IS NULL
     AND o.item_type NOT IN ('note', 'attachment', 'annotation')
     AND json_extract(o.data, '$.inPublications') = 1";

/// SQL: the item `o` carries the manual tag `featured`.
const FEATURED_WORK: &str = "EXISTS (SELECT 1 FROM json_each(o.data, '$.tags') t
     WHERE lower(json_extract(t.value, '$.tag')) = 'featured'
       AND coalesce(json_extract(t.value, '$.type'), 0) = 0)";

/// Newest first, by the work's date.
const BY_DATE: &str =
    "o.date_year DESC, json_extract(o.data, '$.date') DESC, o.date_added DESC, o.key";

/// What the public works are narrowed to: values of a facet are ORed, the
/// facets ANDed. `q` matches title, creators and date, not the tags and notes
/// that stay private.
#[derive(Debug, Default, Clone)]
pub struct WorkFilter {
    pub years: Vec<String>,
    pub types: Vec<String>,
    pub q: Option<String>,
    pub featured: bool,
    /// Only these works: those with a PDF to link to, which takes more than SQL
    /// to tell (see `profile::work_pdfs`).
    pub pdf_keys: Option<std::sync::Arc<Vec<String>>>,
    /// Languages and licenses picked, as ISO 639-1 codes and SPDX IDs, which
    /// [`WorkFilter::term_keys`] holds the works of.
    pub languages: Vec<String>,
    pub licenses: Vec<String>,
    /// Only these works: those in a picked language and under a picked
    /// license, which takes more than SQL to tell (see `vocab`).
    pub term_keys: Option<std::sync::Arc<Vec<String>>>,
    /// Only this work, for exporting one.
    pub key: Option<String>,
}

/// A page of the public works, newest first, and how many match in all.
pub fn public_works(
    conn: &Connection,
    filter: &WorkFilter,
    start: usize,
    limit: usize,
) -> Result<(usize, Vec<Value>)> {
    let mut sql = PUBLIC_WORK.to_string();
    let mut params: Vec<String> = Vec::new();
    if filter.featured {
        sql += &format!(" AND {FEATURED_WORK}");
    }
    if !filter.years.is_empty() {
        sql += &format!(" AND o.date_year IN ({})", placeholders(filter.years.len()));
        params.extend(filter.years.iter().cloned());
    }
    if !filter.types.is_empty() {
        sql += &format!(" AND o.item_type IN ({})", placeholders(filter.types.len()));
        params.extend(filter.types.iter().cloned());
    }
    if let Some(q) = &filter.q {
        sql += " AND instr(o.search_text, ?) > 0";
        params.push(q.to_lowercase());
    }
    if let Some(key) = &filter.key {
        sql += " AND o.key = ?";
        params.push(key.clone());
    }
    for keys in [&filter.pdf_keys, &filter.term_keys].into_iter().flatten() {
        sql += " AND o.key IN (SELECT value FROM json_each(?))";
        params.push(serde_json::to_string(keys.as_ref()).unwrap_or_default());
    }
    let total: i64 = conn.query_row(
        &format!("SELECT count(*) FROM object o WHERE {sql}"),
        rusqlite::params_from_iter(&params),
        |r| r.get(0),
    )?;
    let mut stmt = conn.prepare(&format!(
        "SELECT o.data FROM object o WHERE {sql}
         ORDER BY {BY_DATE} LIMIT {limit} OFFSET {start}"
    ))?;
    let works = stmt
        .query_map(rusqlite::params_from_iter(&params), |r| {
            Ok(parse_json(&r.get::<_, String>(0)?))
        })?
        .collect::<Result<_>>()?;
    Ok((total as usize, works))
}

/// What the public works can be narrowed by, with how many of all of them have
/// each value.
#[derive(Debug, Default)]
pub struct WorkFacets {
    /// The most recent years.
    pub years: Vec<(String, usize)>,
    /// The most used item types.
    pub types: Vec<(String, usize)>,
    /// How many are featured.
    pub featured: usize,
}

pub fn public_work_facets(conn: &Connection, limit: usize) -> Result<WorkFacets> {
    let counts = |column: &str, order: &str| -> Result<Vec<(String, usize)>> {
        let mut stmt = conn.prepare(&format!(
            "SELECT {column}, count(*) FROM object o
             WHERE {PUBLIC_WORK} AND {column} IS NOT NULL
             GROUP BY {column} ORDER BY {order} LIMIT {limit}"
        ))?;
        stmt.query_map([], |r| Ok((r.get(0)?, r.get::<_, i64>(1)? as usize)))?
            .collect()
    };
    Ok(WorkFacets {
        years: counts("o.date_year", "o.date_year DESC")?,
        types: counts("o.item_type", "count(*) DESC, o.item_type")?,
        featured: conn.query_row(
            &format!("SELECT count(*) FROM object o WHERE {PUBLIC_WORK} AND {FEATURED_WORK}"),
            [],
            |r| Ok(r.get::<_, i64>(0)? as usize),
        )?,
    })
}

/// Where a public work's PDFs may be: its own URL, and the source URLs of its
/// attached PDFs, oldest first. That is where Zotero fetched a file from or links
/// to, not where it is stored: an attachment uploaded from disk has none.
#[derive(Debug, PartialEq)]
pub struct PdfSources {
    pub key: String,
    pub url: String,
    pub attached: Vec<String>,
}

pub fn public_work_pdf_sources(conn: &Connection) -> Result<Vec<PdfSources>> {
    let mut stmt = conn.prepare(&format!(
        "SELECT o.key, coalesce(json_extract(o.data, '$.url'), ''),
                (SELECT json_group_array(u) FROM (
                   SELECT json_extract(a.data, '$.url') AS u FROM object a
                   WHERE a.kind = 'item' AND a.deleted = 0 AND a.item_type = 'attachment'
                     AND a.parent_item = o.key
                     AND json_extract(a.data, '$.linkMode') IN ('imported_url', 'linked_url')
                     AND lower(json_extract(a.data, '$.contentType')) LIKE 'application/pdf%'
                     AND json_extract(a.data, '$.url') IS NOT NULL
                   ORDER BY a.date_added, a.key))
         FROM object o WHERE {PUBLIC_WORK}"
    ))?;
    stmt.query_map([], |r| {
        Ok(PdfSources {
            key: r.get(0)?,
            url: r.get(1)?,
            attached: serde_json::from_str(&r.get::<_, String>(2)?).unwrap_or_default(),
        })
    })?
    .collect()
}

/// What a public work says of its language and license, as Zotero holds them.
#[derive(Debug, Clone, PartialEq)]
pub struct WorkTerms {
    pub key: String,
    pub language: String,
    pub rights: String,
}

pub fn public_work_terms(conn: &Connection) -> Result<Vec<WorkTerms>> {
    let mut stmt = conn.prepare(&format!(
        "SELECT o.key, coalesce(json_extract(o.data, '$.language'), ''),
                coalesce(json_extract(o.data, '$.rights'), '')
         FROM object o WHERE {PUBLIC_WORK}"
    ))?;
    stmt.query_map([], |r| {
        Ok(WorkTerms {
            key: r.get(0)?,
            language: r.get(1)?,
            rights: r.get(2)?,
        })
    })?
    .collect()
}

/// How many works are public.
pub fn public_work_count(conn: &Connection) -> Result<usize> {
    conn.query_row(
        &format!("SELECT count(*) FROM object o WHERE {PUBLIC_WORK}"),
        [],
        |r| Ok(r.get::<_, i64>(0)? as usize),
    )
}

/// An item's version and the md5 of its stored file, if it has one; `None`
/// when there is no such item.
/// The bytes of every attachment file in the library, and of `item_key`'s, which
/// an upload for it would replace.
pub fn file_usage(conn: &Connection, item_key: &str) -> Result<(i64, i64)> {
    Ok((
        conn.query_row("SELECT coalesce(sum(filesize), 0) FROM file", [], |r| {
            r.get(0)
        })?,
        conn.query_row(
            "SELECT coalesce(sum(filesize), 0) FROM file WHERE item_key = ?1",
            [item_key],
            |r| r.get(0),
        )?,
    ))
}

pub fn item_file_state(conn: &Connection, item_key: &str) -> Result<Option<(i64, Option<String>)>> {
    conn.query_row(
        "SELECT o.version, f.md5 FROM object o LEFT JOIN file f ON f.item_key = o.key
         WHERE o.kind = 'item' AND o.key = ?1",
        [item_key],
        |r| Ok((r.get(0)?, r.get(1)?)),
    )
    .optional()
}

/// Register an uploaded file and stamp its `md5`/`mtime` into the attachment
/// item, as zotero.org does: the downloading client needs them to reconcile
/// the file. Always bumps the library version.
pub fn register_file(
    conn: &mut Connection,
    item_key: &str,
    file: &FileMeta,
    actor: Option<&Actor>,
) -> Result<i64> {
    let tx = begin(conn)?;
    let version = current_version(&tx)? + 1;
    tx.execute(
        "INSERT INTO file (item_key, md5, filename, filesize, mtime, content_type, compressed,
                           version)
         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)
         ON CONFLICT (item_key) DO UPDATE SET
             md5 = excluded.md5, filename = excluded.filename, filesize = excluded.filesize,
             mtime = excluded.mtime, content_type = excluded.content_type,
             compressed = excluded.compressed, version = excluded.version",
        params![
            item_key,
            file.md5,
            file.filename,
            file.filesize,
            file.mtime,
            file.content_type,
            file.compressed,
            version
        ],
    )?;
    if let Some((_, Value::Object(mut data))) = get_object(&tx, Kind::Item, item_key)? {
        data.insert("md5".into(), file.md5.clone().into());
        data.insert("mtime".into(), file.mtime.into());
        data.insert("version".into(), version.into());
        put_object(
            &tx,
            Kind::Item,
            item_key,
            version,
            &Value::Object(data),
            actor,
        )?;
    }
    set_version(&tx, version)?;
    tx.commit()?;
    Ok(version)
}

// ── full text ────────────────────────────────────────────────────────────────

/// `{itemKey: version}` for content changed after `since`. The version is the
/// row's own, matching the per-item download's `Last-Modified-Version`, so the
/// client skips content it already holds.
pub fn fulltext_versions(conn: &Connection, since: i64) -> Result<Value> {
    let mut stmt = conn.prepare("SELECT item_key, version FROM fulltext WHERE version > ?1")?;
    let rows = stmt.query_map([since], |r| {
        Ok((r.get::<_, String>(0)?, r.get::<_, i64>(1)?))
    })?;
    let mut map = Map::new();
    for row in rows {
        let (key, version) = row?;
        map.insert(key, version.into());
    }
    Ok(Value::Object(map))
}

/// One item's content as `(row_version, {content, indexedChars, …})`.
pub fn fulltext_item(conn: &Connection, item_key: &str) -> Result<Option<(i64, Value)>> {
    conn.query_row(
        "SELECT content, indexed_chars, total_chars, indexed_pages, total_pages, version
         FROM fulltext WHERE item_key = ?1",
        [item_key],
        |r| {
            Ok((
                r.get::<_, i64>(5)?,
                json!({
                    "content": r.get::<_, String>(0)?,
                    "indexedChars": r.get::<_, i64>(1)?,
                    "totalChars": r.get::<_, i64>(2)?,
                    "indexedPages": r.get::<_, i64>(3)?,
                    "totalPages": r.get::<_, i64>(4)?,
                }),
            ))
        },
    )
    .optional()
}

/// Store a batch of full-text content at a new library version.
pub fn write_fulltext(
    conn: &mut Connection,
    batch: Vec<Value>,
    expected: Option<i64>,
) -> Result<Outcome<BatchResult>> {
    if batch.is_empty() {
        return Ok(match no_change(conn, expected)? {
            Outcome::Done(version) => Outcome::Done(BatchResult {
                version,
                ..Default::default()
            }),
            Outcome::Conflict(current) => Outcome::Conflict(current),
        });
    }
    let tx = begin(conn)?;
    let version = match guarded_version(&tx, expected)? {
        Outcome::Done(version) => version,
        Outcome::Conflict(current) => return Ok(Outcome::Conflict(current)),
    };
    let mut result = BatchResult {
        version,
        ..Default::default()
    };
    for (index, item) in batch.into_iter().enumerate() {
        let key = item.get("key").and_then(Value::as_str).unwrap_or_default();
        if !valid_object_key(key) {
            result.failed.insert(
                index.to_string(),
                failure(Some(key), 400, "invalid item key"),
            );
            continue;
        }
        let count = |name: &str| item.get(name).and_then(Value::as_i64).unwrap_or(0);
        tx.execute(
            "INSERT INTO fulltext (item_key, content, indexed_chars, total_chars,
                                   indexed_pages, total_pages, version)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
             ON CONFLICT (item_key) DO UPDATE SET
                 content = excluded.content, indexed_chars = excluded.indexed_chars,
                 total_chars = excluded.total_chars, indexed_pages = excluded.indexed_pages,
                 total_pages = excluded.total_pages, version = excluded.version",
            params![
                key,
                item.get("content")
                    .and_then(Value::as_str)
                    .unwrap_or_default(),
                count("indexedChars"),
                count("totalChars"),
                count("indexedPages"),
                count("totalPages"),
                version
            ],
        )?;
        result
            .successful
            .insert(index.to_string(), json!({ "key": key }));
    }
    if result.successful.is_empty() {
        drop(tx);
        result.version = current_version(conn)?;
        return Ok(Outcome::Done(result));
    }
    set_version(&tx, version)?;
    tx.commit()?;
    Ok(Outcome::Done(result))
}

// ── dashboard ────────────────────────────────────────────────────────────────

/// Library statistics for the web UI.
#[derive(Debug, Clone, PartialEq, serde::Serialize)]
#[serde(rename_all = "camelCase")]
pub struct LibraryStats {
    pub version: i64,
    /// Regular top-level items, not in the trash.
    pub items: i64,
    pub attachments: i64,
    pub notes: i64,
    pub trashed: i64,
    pub collections: i64,
    pub searches: i64,
    pub tags: i64,
    pub files: i64,
    pub file_bytes: i64,
    pub fulltext: i64,
}

pub fn stats(conn: &Connection) -> Result<LibraryStats> {
    let count = |sql: &str| conn.query_row(sql, [], |r| r.get::<_, i64>(0));
    Ok(LibraryStats {
        version: current_version(conn)?,
        items: count(
            "SELECT count(*) FROM object WHERE kind = 'item' AND deleted = 0
             AND parent_item IS NULL
             AND coalesce(item_type, '') NOT IN ('attachment', 'note', 'annotation')",
        )?,
        attachments: count(
            "SELECT count(*) FROM object WHERE kind = 'item' AND deleted = 0
             AND item_type = 'attachment'",
        )?,
        notes: count(
            "SELECT count(*) FROM object WHERE kind = 'item' AND deleted = 0 AND item_type = 'note'",
        )?,
        trashed: count("SELECT count(*) FROM object WHERE kind = 'item' AND deleted = 1")?,
        collections: count("SELECT count(*) FROM object WHERE kind = 'collection'")?,
        searches: count("SELECT count(*) FROM object WHERE kind = 'search'")?,
        tags: count("SELECT count(DISTINCT tag) FROM item_tag")?,
        files: count("SELECT count(*) FROM file")?,
        file_bytes: count("SELECT coalesce(sum(filesize), 0) FROM file")?,
        fulltext: count("SELECT count(*) FROM fulltext")?,
    })
}

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

    fn conn() -> Connection {
        let mut conn = Connection::open_in_memory().unwrap();
        crate::db::migrate(&mut conn, crate::db::LIBRARY).unwrap();
        conn
    }

    fn done<T: std::fmt::Debug>(outcome: Outcome<T>) -> T {
        match outcome {
            Outcome::Done(v) => v,
            other => panic!("expected Done, got {other:?}"),
        }
    }

    fn post(conn: &mut Connection, objects: Value, expected: Option<i64>) -> BatchResult {
        let Value::Array(batch) = objects else {
            panic!()
        };
        done(write_batch(conn, Kind::Item, batch, expected, None).unwrap())
    }

    /// The dates of an API write: what a client sends is kept; what it leaves
    /// out, or a replacing write echoes back, is stamped.
    #[test]
    fn writes_stamp_the_dates_a_client_leaves_out() {
        const NOW: &str = "2026-11-02T06:00:00Z";
        const THEN: &str = "2020-01-01T00:00:00Z";
        const LATER: &str = "2021-06-01T12:00:00Z";
        let stamped = |provided: Value, stored: Option<Value>, replace: bool| {
            let provided = provided.as_object().unwrap().clone();
            let mut data = match (&stored, replace) {
                (Some(Value::Object(base)), false) => {
                    let mut base = base.clone();
                    base.extend(provided.clone());
                    base
                }
                _ => provided.clone(),
            };
            stamp_dates(&mut data, &provided, stored.as_ref(), replace, NOW);
            (data["dateAdded"].clone(), data["dateModified"].clone())
        };
        let stored = json!({"title": "A", "dateAdded": THEN, "dateModified": THEN});
        // Created without dates (commonmeta), or with Zotero's.
        assert_eq!(
            stamped(json!({"title": "A"}), None, false),
            (json!(NOW), json!(NOW))
        );
        assert_eq!(
            stamped(
                json!({"dateAdded": THEN, "dateModified": LATER}),
                None,
                false
            ),
            (json!(THEN), json!(LATER))
        );
        // A desktop batch update carries its own dateModified.
        assert_eq!(
            stamped(
                json!({"title": "B", "dateModified": LATER}),
                Some(stored.clone()),
                false
            ),
            (json!(THEN), json!(LATER))
        );
        // A PATCH without one is a change now.
        assert_eq!(
            stamped(json!({"title": "B"}), Some(stored.clone()), false),
            (json!(THEN), json!(NOW))
        );
        // A PUT echoing what it read is a change now; one with a newer date
        // keeps it; one without dateAdded keeps the stored.
        assert_eq!(
            stamped(
                json!({"title": "B", "dateAdded": THEN, "dateModified": THEN}),
                Some(stored.clone()),
                true
            ),
            (json!(THEN), json!(NOW))
        );
        assert_eq!(
            stamped(
                json!({"title": "B", "dateModified": LATER}),
                Some(stored.clone()),
                true
            ),
            (json!(THEN), json!(LATER))
        );
        assert_eq!(
            stamped(json!({"title": "B"}), Some(stored), true),
            (json!(THEN), json!(NOW))
        );
    }

    /// A field a write sends as "" is cleared, not kept: a Zotero client clears
    /// the fields an item's new type lacks that way, and a client downloading
    /// the item later refuses an empty field its type does not have.
    #[test]
    fn a_field_sent_empty_is_cleared() {
        let mut c = conn();
        post(
            &mut c,
            json!([{"key": "AAAA2345", "itemType": "journalArticle", "title": "A",
                    "libraryCatalog": "rogue-scholar.org"}]),
            Some(0),
        );
        // The type change as Zotero uploads it: the new type, the old type's
        // fields cleared.
        let version = current_version(&c).unwrap();
        post(
            &mut c,
            json!([{"key": "AAAA2345", "itemType": "blogPost", "libraryCatalog": "",
                    "extra": "libraryCatalog: rogue-scholar.org"}]),
            Some(version),
        );
        let (version, item) = object(&c, Kind::Item, "AAAA2345").unwrap().unwrap();
        assert_eq!(item["data"]["itemType"], "blogPost");
        assert!(item["data"].get("libraryCatalog").is_none(), "{item}");
        assert_eq!(item["data"]["title"], "A");

        let put = json!({"key": "AAAA2345", "itemType": "blogPost", "title": "B",
                         "abstractNote": ""});
        write_one(&mut c, Kind::Item, "AAAA2345", put, version, false, None).unwrap();
        let (_, item) = object(&c, Kind::Item, "AAAA2345").unwrap().unwrap();
        assert!(item["data"].get("abstractNote").is_none(), "{item}");
    }

    /// Migration 0004 strips the empty fields earlier versions stored, and
    /// leaves every other value as it was, versions included.
    #[test]
    fn stored_empty_fields_are_removed() {
        let mut c = Connection::open_in_memory().unwrap();
        crate::db::migrate(&mut c, &crate::db::LIBRARY[..3]).unwrap();
        post(
            &mut c,
            json!([{"key": "AAAA2345", "itemType": "blogPost", "title": "A",
                    "creators": [{"creatorType": "author", "lastName": "F"}],
                    "tags": [], "relations": {}, "inPublications": true, "deleted": false}]),
            Some(0),
        );
        c.execute(
            "UPDATE object SET data = json_set(data, '$.libraryCatalog', '', '$.note', '')",
            [],
        )
        .unwrap();
        let before = object(&c, Kind::Item, "AAAA2345").unwrap().unwrap();
        crate::db::migrate(&mut c, crate::db::LIBRARY).unwrap();
        let (version, item) = object(&c, Kind::Item, "AAAA2345").unwrap().unwrap();
        assert_eq!(version, before.0, "no version bump");
        let data = &item["data"];
        assert!(
            data.get("libraryCatalog").is_none() && data.get("note").is_none(),
            "{data}"
        );
        assert_eq!(data["title"], "A");
        assert_eq!(data["inPublications"], json!(true));
        assert_eq!(data["deleted"], json!(false));
        assert_eq!(data["creators"][0]["lastName"], "F");
        assert_eq!(data["relations"], json!({}));
        assert_eq!(data["tags"], json!([]));
        assert_eq!(data["version"], json!(version));
    }

    /// Through the writes themselves: items are stamped, collections are not.
    #[test]
    fn only_items_are_stamped() {
        let mut c = conn();
        post(
            &mut c,
            json!([{"key": "AAAA2345", "itemType": "book", "title": "A"}]),
            Some(0),
        );
        let (version, item) = object(&c, Kind::Item, "AAAA2345").unwrap().unwrap();
        let added = item["data"]["dateAdded"].as_str().unwrap().to_string();
        assert!(added.ends_with('Z') && added.len() == 20, "{added}");
        let put = json!({"key": "AAAA2345", "itemType": "book", "title": "B",
                         "dateModified": item["data"]["dateModified"]});
        let outcome = write_one(&mut c, Kind::Item, "AAAA2345", put, version, false, None).unwrap();
        assert!(matches!(outcome, Single::Done { .. }));
        let (_, item) = object(&c, Kind::Item, "AAAA2345").unwrap().unwrap();
        assert_eq!(
            item["data"]["dateAdded"],
            json!(added),
            "kept on a PUT without it"
        );
        assert_eq!(item["data"]["title"], "B");

        done(
            write_batch(
                &mut c,
                Kind::Collection,
                vec![json!({"key": "CCCC2345", "name": "C"})],
                None,
                None,
            )
            .unwrap(),
        );
        let (_, collection) = object(&c, Kind::Collection, "CCCC2345").unwrap().unwrap();
        assert!(
            collection["data"].get("dateAdded").is_none(),
            "{collection}"
        );
    }

    #[test]
    fn keys_are_zotero_shaped() {
        for _ in 0..100 {
            assert!(valid_object_key(&generate_key()));
        }
        assert!(!valid_object_key("ABCDEFG0"));
        assert!(!valid_object_key("abc"));
    }

    #[test]
    fn years_are_standalone_four_digit_runs() {
        assert_eq!(year_of("circa 1990").as_deref(), Some("1990"));
        assert_eq!(year_of("2001-03-04").as_deref(), Some("2001"));
        assert_eq!(year_of("12345"), None);
        assert_eq!(year_of(""), None);
    }

    #[test]
    fn derives_query_columns() {
        let d = derive(&json!({
            "itemType": "journalArticle",
            "title": "Deep Learning",
            "date": "May 2015",
            "creators": [{"lastName": "LeCun", "firstName": "Yann"}],
            "DOI": "10.1038/nature14539",
            "tags": [{"tag": "ml"}, {"tag": "ai", "type": 1}, "junk"],
            "collections": ["ABCD2345"],
            "parentItem": false,
            "deleted": 1,
        }));
        assert_eq!(d.item_type.as_deref(), Some("journalArticle"));
        assert_eq!(d.parent_item, None);
        assert!(d.deleted);
        assert_eq!(d.creator.as_deref(), Some("LeCun"));
        assert_eq!(d.date_year.as_deref(), Some("2015"));
        assert_eq!(d.search_text, "deep learning may 2015 lecun yann");
        assert!(d.everything_text.contains("10.1038/nature14539"));
        assert!(d.everything_text.contains("ml"));
        assert_eq!(d.tags, vec!["ml", "ai"]);
        assert_eq!(d.collections, vec!["ABCD2345"]);
    }

    #[test]
    fn attachment_fields_come_back_link_mode_first() {
        let data = order_fields(json!({"filename": "a.pdf", "linkMode": "imported_file"}));
        let keys: Vec<_> = data.as_object().unwrap().keys().cloned().collect();
        assert_eq!(keys, vec!["linkMode", "filename"]);
    }

    #[test]
    fn batch_writes_merge_and_bump_the_version() {
        let mut c = conn();
        let r = post(
            &mut c,
            json!([{"key": "AAAAAAAA", "itemType": "book", "title": "One"}]),
            Some(0),
        );
        assert_eq!(r.version, 1);
        // A partial upload keeps the omitted fields.
        let r = post(
            &mut c,
            json!([{"key": "AAAAAAAA", "title": "Two"}]),
            Some(1),
        );
        assert_eq!(r.version, 2);
        let data = &r.successful["0"]["data"];
        assert_eq!(data["itemType"], "book");
        assert_eq!(data["title"], "Two");
        assert_eq!(data["version"], 2);
        // A stale library version is a conflict.
        assert!(matches!(
            write_batch(
                &mut c,
                Kind::Item,
                vec![json!({"title": "x"})],
                Some(1),
                None
            )
            .unwrap(),
            Outcome::Conflict(2)
        ));
    }

    #[test]
    fn batch_without_precondition_checks_object_versions() {
        let mut c = conn();
        post(
            &mut c,
            json!([{"key": "AAAAAAAA", "itemType": "book"}]),
            None,
        );
        let r = post(
            &mut c,
            json!([
                {"key": "AAAAAAAA", "version": 7, "title": "stale"},
                {"itemType": "note", "note": "new"},
                {"key": "bad"},
            ]),
            None,
        );
        assert_eq!(r.failed["0"]["code"], 412);
        assert_eq!(r.failed["2"]["code"], 400);
        let created = r.successful["1"]["key"].as_str().unwrap();
        assert!(valid_object_key(created));
        assert_eq!(r.version, 2);
    }

    #[test]
    fn nothing_written_leaves_the_version() {
        let mut c = conn();
        let r = post(&mut c, json!([{"key": "bad"}]), None);
        assert_eq!(r.version, 0);
        assert_eq!(current_version(&c).unwrap(), 0);
    }

    #[test]
    fn single_writes_check_the_object_version() {
        let mut c = conn();
        let created = write_one(
            &mut c,
            Kind::Item,
            "BBBBBBBB",
            json!({"itemType": "book"}),
            0,
            false,
            None,
        )
        .unwrap();
        assert!(matches!(created, Single::Done { version: 1, .. }));
        // Another object bumps the library, not this object.
        post(
            &mut c,
            json!([{"key": "CCCCCCCC", "itemType": "book"}]),
            Some(1),
        );
        let patched = write_one(
            &mut c,
            Kind::Item,
            "BBBBBBBB",
            json!({"title": "T"}),
            1,
            true,
            None,
        )
        .unwrap();
        assert!(matches!(patched, Single::Done { version: 3, .. }));
        let (version, stored) = object(&c, Kind::Item, "BBBBBBBB").unwrap().unwrap();
        assert_eq!(version, 3);
        assert_eq!(stored["data"]["itemType"], "book");
        assert!(matches!(
            write_one(
                &mut c,
                Kind::Item,
                "BBBBBBBB",
                json!({"title": "U"}),
                1,
                true,
                None
            )
            .unwrap(),
            Single::Conflict(3)
        ));
        assert!(matches!(
            write_one(&mut c, Kind::Item, "DDDDDDDD", json!({}), 0, true, None).unwrap(),
            Single::NotFound
        ));
        // PUT replaces.
        write_one(
            &mut c,
            Kind::Item,
            "BBBBBBBB",
            json!({"itemType": "book"}),
            3,
            false,
            None,
        )
        .unwrap();
        let (_, stored) = object(&c, Kind::Item, "BBBBBBBB").unwrap().unwrap();
        assert!(stored["data"].get("title").is_none());
        // DELETE logs the deletion.
        assert!(matches!(
            delete_one(&mut c, Kind::Item, "BBBBBBBB", 4).unwrap(),
            Single::Done { version: 5, .. }
        ));
        assert_eq!(deleted(&c, 4).unwrap()["items"], json!(["BBBBBBBB"]));
    }

    #[test]
    fn an_unchanged_setting_keeps_its_version() {
        let mut c = conn();
        let feeds = json!({"https://a.example/rss": ["A", 3, 30, 60], "https://b.example/rss": ["B", 3, 30, 60]});
        let write = |c: &mut Connection, body: Value, expected| {
            done(write_settings(c, body, Some(expected)).unwrap())
        };
        assert_eq!(write(&mut c, json!({"feeds": {"value": feeds}}), 0), 1);

        // The same value again: no new version, for the library or the setting.
        assert_eq!(write(&mut c, json!({"feeds": {"value": feeds}}), 1), 1);
        assert_eq!(current_version(&c).unwrap(), 1);
        assert_eq!(settings(&c, 0).unwrap()["feeds"]["version"], 1);
        assert_eq!(settings(&c, 1).unwrap(), json!({}));
        // Nor with an object's keys in another order, which means the same.
        let reordered = json!({"https://b.example/rss": ["B", 3, 30, 60], "https://a.example/rss": ["A", 3, 30, 60]});
        assert_eq!(write(&mut c, json!({"feeds": {"value": reordered}}), 1), 1);

        // A stale client still hears about it, changed or not.
        assert!(matches!(
            write_settings(&mut c, json!({"feeds": {"value": feeds}}), Some(0)).unwrap(),
            Outcome::Conflict(1)
        ));

        // Only what changes gets a new version.
        let body = json!({"feeds": {"value": feeds}, "tagColors": {"value": [1]}});
        assert_eq!(write(&mut c, body, 1), 2);
        assert_eq!(settings(&c, 0).unwrap()["feeds"]["version"], 1);
        assert_eq!(settings(&c, 0).unwrap()["tagColors"]["version"], 2);
        // A real change, where order within an array does count.
        let changed = json!({"feeds": {"value": {"https://a.example/rss": [3, "A", 30, 60]}}});
        assert_eq!(write(&mut c, changed, 2), 3);
        assert_eq!(settings(&c, 2).unwrap()["feeds"]["version"], 3);

        // After a deletion the same value is new, and leaves `/deleted`.
        done(delete_settings(&mut c, &["tagColors".into()], Some(3)).unwrap());
        assert_eq!(write(&mut c, json!({"tagColors": {"value": [1]}}), 4), 5);
        assert_eq!(deleted(&c, 0).unwrap()["settings"], json!([]));
    }

    #[test]
    fn every_work_in_my_publications_is_public() {
        let mut c = conn();
        post(
            &mut c,
            json!([
                {"key": "ARTICLEA", "itemType": "journalArticle", "title": "A", "date": "2020",
                 "inPublications": true},
                {"key": "BLGPSTAA", "itemType": "blogPost", "title": "B", "date": "2021-05-01",
                 "inPublications": true, "tags": [{"tag": "Featured"}]},
                {"key": "BLGPSTBB", "itemType": "blogPost", "title": "C", "date": "2020-01-01",
                 "inPublications": true},
                {"key": "NYEARAAA", "itemType": "book", "title": "D", "inPublications": true,
                 "tags": [{"tag": "featured", "type": 1}]},
                // Not public: outside My Publications, in the trash, a child.
                {"key": "LIBRARYA", "itemType": "book", "title": "L", "date": "1999"},
                {"key": "TRASHEDA", "itemType": "book", "title": "T", "date": "1998",
                 "inPublications": true, "deleted": 1},
                {"key": "CHILDNTE", "itemType": "note", "parentItem": "ARTICLEA", "note": "n",
                 "inPublications": true},
                {"key": "PDFWEBAA", "itemType": "attachment", "parentItem": "ARTICLEA",
                 "linkMode": "imported_url", "contentType": "application/pdf",
                 "url": "https://example.org/a.pdf"},
                {"key": "PDFDISKA", "itemType": "attachment", "parentItem": "ARTICLEA",
                 "linkMode": "imported_file", "contentType": "application/pdf",
                 "filename": "a.pdf"},
                {"key": "PDFTRASH", "itemType": "attachment", "parentItem": "ARTICLEA",
                 "linkMode": "imported_url", "contentType": "application/pdf",
                 "url": "https://example.org/trashed.pdf", "deleted": 1},
                {"key": "HTMLSNAP", "itemType": "attachment", "parentItem": "ARTICLEA",
                 "linkMode": "imported_url", "contentType": "text/html",
                 "url": "https://example.org/a.html"},
                {"key": "PDFLIBRA", "itemType": "attachment", "parentItem": "LIBRARYA",
                 "linkMode": "imported_url", "contentType": "application/pdf",
                 "url": "https://example.org/private.pdf"},
                {"key": "PDFWEBAA", "itemType": "attachment", "parentItem": "ARTICLEA",
                 "linkMode": "imported_url", "contentType": "application/pdf",
                 "url": "https://example.org/a.pdf"},
                {"key": "PDFDISKA", "itemType": "attachment", "parentItem": "ARTICLEA",
                 "linkMode": "imported_file", "contentType": "application/pdf",
                 "filename": "a.pdf"},
                {"key": "PDFTRASH", "itemType": "attachment", "parentItem": "ARTICLEA",
                 "linkMode": "imported_url", "contentType": "application/pdf",
                 "url": "https://example.org/trashed.pdf", "deleted": 1},
                {"key": "HTMLSNAP", "itemType": "attachment", "parentItem": "ARTICLEA",
                 "linkMode": "imported_url", "contentType": "text/html",
                 "url": "https://example.org/a.html"},
            ]),
            Some(0),
        );
        let keys = |filter: &WorkFilter, start, limit| {
            let (total, works) = public_works(&c, filter, start, limit).unwrap();
            let keys: Vec<String> = works
                .iter()
                .map(|w| w["key"].as_str().unwrap().to_string())
                .collect();
            (total, keys)
        };
        let all = WorkFilter::default();
        assert_eq!(
            keys(&all, 0, 10),
            (
                4,
                ["BLGPSTAA", "BLGPSTBB", "ARTICLEA", "NYEARAAA"]
                    .map(String::from)
                    .to_vec()
            )
        );
        assert_eq!(public_work_count(&c).unwrap(), 4);
        // Paging.
        assert_eq!(keys(&all, 1, 1), (4, vec!["BLGPSTBB".to_string()]));

        // Values of a facet are ORed, the facets ANDed.
        let years = |y: &[&str]| y.iter().map(|y| y.to_string()).collect::<Vec<_>>();
        let two_years = WorkFilter {
            years: years(&["2020", "2021"]),
            ..WorkFilter::default()
        };
        assert_eq!(keys(&two_years, 0, 10).0, 3);
        let narrowed = WorkFilter {
            years: years(&["2020"]),
            types: vec!["blogPost".to_string()],
            ..WorkFilter::default()
        };
        assert_eq!(keys(&narrowed, 0, 10), (1, vec!["BLGPSTBB".to_string()]));
        // A value is a parameter, not SQL.
        let injected = WorkFilter {
            years: years(&["2020') OR ('1'='1"]),
            ..WorkFilter::default()
        };
        assert_eq!(keys(&injected, 0, 10).0, 0);

        // Search looks at title, creators and date, in any case.
        let search = |q: &str| WorkFilter {
            q: Some(q.to_string()),
            ..WorkFilter::default()
        };
        assert_eq!(keys(&search("b"), 0, 10), (1, vec!["BLGPSTAA".to_string()]));
        assert_eq!(keys(&search("2021"), 0, 10).0, 1);
        assert_eq!(keys(&search("nothing like it"), 0, 10).0, 0);

        // PDFs: where they came from, of public works, not uploaded or trashed.
        let sources = public_work_pdf_sources(&c).unwrap();
        assert_eq!(sources.len(), 4);
        let article = sources.iter().find(|s| s.key == "ARTICLEA").unwrap();
        assert_eq!(article.attached, ["https://example.org/a.pdf"]);
        let blog = sources.iter().find(|s| s.key == "BLGPSTAA").unwrap();
        assert!(blog.attached.is_empty());
        let with_pdf = WorkFilter {
            pdf_keys: Some(std::sync::Arc::new(vec!["ARTICLEA".to_string()])),
            ..WorkFilter::default()
        };
        assert_eq!(keys(&with_pdf, 0, 10), (1, vec!["ARTICLEA".to_string()]));

        // One work, if it is public.
        let one = |key: &str| WorkFilter {
            key: Some(key.to_string()),
            ..WorkFilter::default()
        };
        assert_eq!(
            keys(&one("ARTICLEA"), 0, 10),
            (1, vec!["ARTICLEA".to_string()])
        );
        assert_eq!(keys(&one("LIBRARYA"), 0, 10).0, 0);
        assert_eq!(keys(&one("CHILDNTE"), 0, 10).0, 0);

        // Only a manual tag features a work.
        let featured = WorkFilter {
            featured: true,
            ..WorkFilter::default()
        };
        assert_eq!(keys(&featured, 0, 10), (1, vec!["BLGPSTAA".to_string()]));
        assert_eq!(public_work_facets(&c, 10).unwrap().featured, 1);

        // Facets count all public works; recent years and common types first.
        let facets = public_work_facets(&c, 10).unwrap();
        assert_eq!(
            facets.years,
            [("2021".to_string(), 1), ("2020".to_string(), 2)]
        );
        assert_eq!(
            facets.types,
            [
                ("blogPost".to_string(), 2),
                ("book".to_string(), 1),
                ("journalArticle".to_string(), 1)
            ]
        );
        let facets = public_work_facets(&c, 1).unwrap();
        assert_eq!(facets.years, [("2021".to_string(), 1)]);
    }

    #[test]
    fn item_queries_filter_search_and_count_children() {
        let mut c = conn();
        post(
            &mut c,
            json!([
                {"key": "AAAAAAAA", "itemType": "journalArticle", "title": "Deep Learning",
                 "DOI": "10.1038/nature14539", "tags": [{"tag": "ml"}], "collections": ["CCCCCCCC"]},
                {"key": "BBBBBBBB", "itemType": "attachment", "parentItem": "AAAAAAAA",
                 "contentType": "application/pdf", "linkMode": "imported_file"},
                {"key": "DDDDDDDD", "itemType": "book", "title": "Other", "deleted": 1},
            ]),
            Some(0),
        );
        let query = |raw: &str| ItemQuery::from_params(&Params::parse(Some(raw)));

        let (items, total) = query_items(&c, &query("q=deep")).unwrap();
        assert_eq!((items.len(), total), (1, 1));
        // The DOI is found only when searching everything.
        assert_eq!(query_items(&c, &query("q=nature14539")).unwrap().1, 0);
        assert_eq!(
            query_items(&c, &query("q=nature14539&qmode=everything"))
                .unwrap()
                .1,
            1
        );
        assert_eq!(query_items(&c, &query("tag=ml")).unwrap().1, 1);
        assert_eq!(
            query_items(&c, &query("itemType=-attachment")).unwrap().1,
            1
        );
        assert_eq!(query_items(&c, &query("includeTrashed=1")).unwrap().1, 3);

        let mut top = query("");
        top.top = true;
        let (items, _) = query_items(&c, &top).unwrap();
        assert_eq!(items[0]["meta"]["numChildren"], 1);

        let mut children = query("");
        children.parent = Some("AAAAAAAA".into());
        assert_eq!(item_keys(&c, &children).unwrap(), vec!["BBBBBBBB"]);

        let mut in_collection = query("");
        in_collection.collection = Some("CCCCCCCC".into());
        assert_eq!(item_keys(&c, &in_collection).unwrap(), vec!["AAAAAAAA"]);

        assert_eq!(
            versions(&c, Kind::Item, 0, true).unwrap(),
            json!({"AAAAAAAA": 1, "DDDDDDDD": 1})
        );
    }

    #[test]
    fn deleting_a_tag_strips_it_from_items() {
        let mut c = conn();
        post(
            &mut c,
            json!([{"key": "AAAAAAAA", "itemType": "book", "tags": [{"tag": "a"}, {"tag": "b"}]}]),
            Some(0),
        );
        done(delete_tags(&mut c, &["a".into()], Some(1), None).unwrap());
        let (_, item) = object(&c, Kind::Item, "AAAAAAAA").unwrap().unwrap();
        assert_eq!(item["data"]["tags"], json!([{"tag": "b"}]));
        assert_eq!(tags(&c).unwrap().len(), 1);
        assert_eq!(deleted(&c, 1).unwrap()["tags"][0]["tag"], "a");

        // Used again, it is no longer deleted for anyone, however far back
        // they sync from.
        post(
            &mut c,
            json!([{"key": "BBBBBBBB", "itemType": "book", "tags": [{"tag": "a"}]}]),
            Some(2),
        );
        assert_eq!(deleted(&c, 0).unwrap()["tags"], json!([]));
    }

    #[test]
    fn registering_a_file_stamps_the_item() {
        let mut c = conn();
        post(
            &mut c,
            json!([{"key": "AAAAAAAA", "itemType": "attachment", "linkMode": "imported_file"}]),
            Some(0),
        );
        let file = FileMeta {
            md5: "abc".into(),
            filename: "a.pdf".into(),
            filesize: 3,
            mtime: 1000,
            content_type: Some("application/pdf".into()),
            compressed: false,
        };
        assert_eq!(register_file(&mut c, "AAAAAAAA", &file, None).unwrap(), 2);
        assert_eq!(file_meta(&c, "AAAAAAAA").unwrap(), Some(file));
        let (version, item) = object(&c, Kind::Item, "AAAAAAAA").unwrap().unwrap();
        assert_eq!(version, 2);
        assert_eq!(item["data"]["md5"], "abc");
        assert_eq!(item["data"]["mtime"], 1000);
        // Deleting the item reports the file for removal from storage.
        let (_, files) =
            done(delete_batch(&mut c, Kind::Item, &["AAAAAAAA".into()], Some(2)).unwrap());
        assert_eq!(files, vec!["AAAAAAAA"]);
        assert_eq!(file_meta(&c, "AAAAAAAA").unwrap(), None);
    }

    #[test]
    fn settings_and_fulltext_round_trip() {
        let mut c = conn();
        done(write_settings(&mut c, json!({"tagColors": {"value": [1]}}), Some(0)).unwrap());
        assert_eq!(
            settings(&c, 0).unwrap(),
            json!({"tagColors": {"value": [1], "version": 1}})
        );
        assert_eq!(settings(&c, 1).unwrap(), json!({}));
        done(delete_settings(&mut c, &["tagColors".into()], Some(1)).unwrap());
        assert_eq!(deleted(&c, 0).unwrap()["settings"], json!(["tagColors"]));

        let r = done(
            write_fulltext(
                &mut c,
                vec![json!({"key": "AAAAAAAA", "content": "Hello", "indexedPages": 1})],
                Some(2),
            )
            .unwrap(),
        );
        assert_eq!(r.version, 3);
        assert_eq!(fulltext_versions(&c, 0).unwrap(), json!({"AAAAAAAA": 3}));
        let (version, content) = fulltext_item(&c, "AAAAAAAA").unwrap().unwrap();
        assert_eq!(version, 3);
        assert_eq!(content["content"], "Hello");
    }
}