Artifact [37704cfd81]

Artifact 37704cfd8190fabe10fe6d354f15781965ec6634e7e3b27891c4bb98b101145c:

• File internal/index/index.go — part of check-in [f08add75f5] at 2026-08-24 15:50:54 on branch trunk — events: the vocabulary as code, and what the bus really does

  RFC 0005 §1 as internal/evt, named at the eight sites that raise events instead of sliced out of a TxnID — so a ticket change that closes one is ticket.closed, not ticket. Measured the window: a gap IS detectable (first_seq - ack_floor) but the next fetch succeeds silently, so every consumer must check. dagnats has no scheduler; quarry owns the clock. (user: dmestas, size: 143372) [annotate] [blame] [check-ins using]

// Package index is the per-node derived index (0001 §5): SQLite tables
// folded from store artifacts, disposable and rebuildable by design —
// dropping the database file and re-ingesting must converge to identical
// state, and the disposability harness proves it. This package is a data
// plane; the arch gate restricts its importers.
//
// # The contract this index owes its readers
//
// Both of quarry's neighbours state this contract explicitly and quarry
// did not, which is how it came to serve an incomplete history as
// though it were whole — a missing check-in drew a second branch on the
// timeline while the store had one, and every surface reported "ok".
//
// zit's derivation cache (repocache.zig) is "a pure MEMO of the derived
// repo-view... Deleting the file must never change any answer: it is
// disposable by construction." It is keyed on the log's identity — end
// offset, frame count, last-frame header CRC — plus a self-checksum,
// and "a stale, truncated, or corrupt cache is therefore never
// trusted": any mismatch is a MISS and the caller rebuilds.
//
// Fossil checks a schema version when it opens a repository and, on a
// mismatch, refuses to run at all: "run fossil rebuild to fix this
// problem". It would rather stop than answer from derived tables whose
// shape it does not recognise.
//
// The shared rule is that derived state must be able to say it is
// WRONG. Serving a plausible answer from a stale or partial derivation
// is the failure both designs are built to prevent. quarry follows it
// with three checks, differing from fossil only in the remedy: this
// index is disposable, so a failed check rebuilds rather than refuses.
//
//  1. Shape. schemaVersion is stamped in the file and compared on open.
//     A file written by code with different derived tables is stale by
//     definition — no amount of folding forward repairs a table that
//     did not exist when its rows were written.
//  2. Completeness. DanglingParents finds edges naming a check-in this
//     index does not hold; the ingest pipeline asks the store whether
//     those artifacts still exist, and rebuilds when they do.
//  3. Convergence. The disposability harness proves a rebuild reaches
//     identical state, which is what makes 1 and 2 safe remedies.
package index

import (
	"database/sql"
	"errors"
	"fmt"
	"log"
	"regexp"
	"slices"
	"sort"
	"strings"
	"sync"

	"github.com/ncruces/go-sqlite3/driver"
	"github.com/ncruces/go-sqlite3/ext/fts5"

	"craftdesign.group/quarry/internal/artifact"
	"craftdesign.group/quarry/internal/markup"
)

const schema = `
CREATE TABLE IF NOT EXISTS checkin (
	hash    TEXT PRIMARY KEY,
	date    TEXT NOT NULL,
	user    TEXT NOT NULL,
	comment TEXT NOT NULL,
	branch  TEXT NOT NULL DEFAULT 'trunk'
);
-- One row per (path, check-in that changed it), plus a row per ancestor
-- directory, so "what last touched this entry" is an indexed exact-path
-- lookup for files and directories alike (fossil's mlink, flattened).
CREATE TABLE IF NOT EXISTS filechange (
	path    TEXT NOT NULL,
	checkin TEXT NOT NULL,
	date    TEXT NOT NULL,
	PRIMARY KEY (path, checkin)
);
CREATE INDEX IF NOT EXISTS filechange_by_path ON filechange (path, date DESC);
CREATE INDEX IF NOT EXISTS checkin_by_branch_date ON checkin (branch, date DESC);
-- Ordered by DATE alone, which every existing index leads with branch
-- and so could not serve: the check-in page's context walk was a full
-- scan of the table plus a sort, twice, for six neighbouring rows.
CREATE INDEX IF NOT EXISTS checkin_by_date ON checkin (date, hash);
CREATE TABLE IF NOT EXISTS checkin_tag (
	src     TEXT NOT NULL, -- artifact that carries the T card
	date    TEXT NOT NULL, -- that artifact's D card: tag priority order
	checkin TEXT NOT NULL, -- tag target ("*" resolved to src for manifests)
	name    TEXT NOT NULL, -- without the sign prefix
	sign    TEXT NOT NULL, -- + - *
	value   TEXT NOT NULL,
	PRIMARY KEY (src, checkin, name)
);
CREATE INDEX IF NOT EXISTS checkin_tag_by_target ON checkin_tag (checkin, name, date DESC);
CREATE TABLE IF NOT EXISTS checkin_parent (
	child  TEXT NOT NULL,
	parent TEXT NOT NULL,
	ord    INTEGER NOT NULL,
	PRIMARY KEY (child, ord)
);
-- Branch recompute walks parent->child; leaf detection asks "who lists
-- me as parent". Both need the reverse edge indexed.
CREATE INDEX IF NOT EXISTS checkin_parent_by_parent ON checkin_parent (parent);
-- Branch listing and per-branch tip lookups group by branch and take the
-- newest date; without this they degrade to a full scan per branch.
CREATE INDEX IF NOT EXISTS checkin_by_branch ON checkin (branch, date);
CREATE INDEX IF NOT EXISTS checkin_tag_by_checkin ON checkin_tag (checkin, name, sign);
CREATE TABLE IF NOT EXISTS technote (
	hash     TEXT PRIMARY KEY, -- the technote artifact
	event    TEXT NOT NULL,    -- E-card id (stable across edits; latest wins)
	date     TEXT NOT NULL,    -- E-card timestamp the note is filed under
	edited   TEXT NOT NULL,    -- D-card edit time (dedup: newest edit wins)
	user     TEXT NOT NULL,
	mimetype TEXT NOT NULL,
	comment  TEXT NOT NULL DEFAULT '', -- C card: the timeline title
	body     TEXT NOT NULL             -- W card
);
CREATE INDEX IF NOT EXISTS technote_by_event ON technote (event, edited);
CREATE TABLE IF NOT EXISTS forum_post (
	hash    TEXT PRIMARY KEY, -- the forum-post artifact
	thread  TEXT NOT NULL,    -- root post hash (self for a thread opener)
	parent  TEXT NOT NULL,    -- I-card reply target ('' for the opener)
	title    TEXT NOT NULL,   -- H card (opener only)
	date     TEXT NOT NULL,
	user     TEXT NOT NULL,
	mimetype TEXT NOT NULL DEFAULT '', -- N card ('' = Fossil wiki, per fossil)
	body     TEXT NOT NULL    -- W card
);
CREATE INDEX IF NOT EXISTS forum_post_by_thread ON forum_post (thread, date);
CREATE TABLE IF NOT EXISTS forum_anchor (
	post   TEXT NOT NULL, -- the forum post whose body carries the mention
	thread TEXT NOT NULL, -- that post's thread root
	target TEXT NOT NULL, -- the hex reference as written (a hash prefix)
	PRIMARY KEY (post, target)
);
CREATE INDEX IF NOT EXISTS forum_anchor_by_thread ON forum_anchor (thread);
-- A landing (zit#127): a branch proposed to land on a target. Derived
-- ENTIRELY from forum_post -- zit mints no artifact kind for it, so this
-- is a reading of the thread, recomputed whenever any post in it folds.
CREATE TABLE IF NOT EXISTS landing (
	id         TEXT PRIMARY KEY, -- the thread-root post that IS the record
	branch     TEXT NOT NULL,
	target     TEXT NOT NULL,    -- the branch it should land on
	title      TEXT NOT NULL,    -- H card of the root
	author     TEXT NOT NULL,
	opened     TEXT NOT NULL,
	updated    TEXT NOT NULL,    -- newest post in the thread
	state      TEXT NOT NULL,    -- open | accepted | rejected
	settled_by TEXT NOT NULL DEFAULT '' -- the reply that decided state
);
CREATE INDEX IF NOT EXISTS landing_by_state ON landing (state, updated);
CREATE INDEX IF NOT EXISTS landing_by_branch ON landing (branch);
CREATE TABLE IF NOT EXISTS attachment (
	hash   TEXT PRIMARY KEY, -- the attachment artifact (A card)
	name   TEXT NOT NULL,    -- filename as attached
	target TEXT NOT NULL,    -- ticket id, wiki page name, or technote event id
	src    TEXT NOT NULL,    -- content artifact; '' is fossil's DELETION form
	date   TEXT NOT NULL,
	user   TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS attachment_by_target ON attachment (target, name, date);
CREATE TABLE IF NOT EXISTS wiki_edit (
	hash     TEXT PRIMARY KEY,
	title    TEXT NOT NULL,
	date     TEXT NOT NULL,
	user     TEXT NOT NULL,
	mimetype TEXT NOT NULL,
	body     BLOB NOT NULL
);
CREATE TABLE IF NOT EXISTS ticket_change (
	hash   TEXT PRIMARY KEY,
	ticket TEXT NOT NULL,
	date   TEXT NOT NULL,
	user   TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ticket_change_by_ticket ON ticket_change (ticket, date);
CREATE TABLE IF NOT EXISTS ticket_field (
	hash  TEXT NOT NULL,
	name  TEXT NOT NULL,
	value TEXT NOT NULL,
	PRIMARY KEY (hash, name)
);
-- ticket is the folded CURRENT state of each ticket, one row per ticket.
--
-- The list page derived this per request: GROUP BY over every change row
-- (tk: 35,903), plus two correlated subqueries per ticket to find the
-- newest title and status — 11,618 of them, each with its own temp
-- b-tree — and a HAVING that filtered after grouping, so paging saved
-- nothing. 200ms on tk with no zit call in sight.
--
-- Same lesson as the timeline: fold it once at write time, read it by
-- index. The fold already computes exactly this state for the search
-- doc, so the summary is written from the same pass rather than derived
-- a second way that could disagree with it.
CREATE TABLE IF NOT EXISTS ticket (
	id      TEXT PRIMARY KEY,
	created TEXT NOT NULL,
	updated TEXT NOT NULL,
	title   TEXT NOT NULL,
	status  TEXT NOT NULL,
	-- The rest of fossil's default triage vocabulary. These were already
	-- folded into ticket_field by the same pass; keeping them out of the
	-- summary meant a board or a filtered list had to join per ticket,
	-- so neither existed. Denormalize at write time, read by index.
	--
	-- Absent is '' rather than NULL: a tracker that does not define a
	-- field and a ticket that leaves it blank are the same question to
	-- every reader, and '' compares without a three-valued surprise.
	type       TEXT NOT NULL DEFAULT '',
	severity   TEXT NOT NULL DEFAULT '',
	priority   TEXT NOT NULL DEFAULT '',
	subsystem  TEXT NOT NULL DEFAULT '',
	resolution TEXT NOT NULL DEFAULT '',
	-- Fossil can mark a whole ticket private. It is a folded COLUMN
	-- rather than one row among ticket_field so it can be excluded in
	-- the same index walk that lists tickets — a privacy filter that
	-- costs a join is a privacy filter somebody eventually skips.
	private INTEGER NOT NULL DEFAULT 0
);
-- The list's ordering: newest-updated first, id as the tiebreak so a
-- rebuild pages identically.
CREATE INDEX IF NOT EXISTS ticket_by_updated ON ticket (updated DESC, id);
CREATE TABLE IF NOT EXISTS search_doc (
	hash  TEXT PRIMARY KEY, -- artifact hash ("ticket:<id>" for folded ticket docs)
	kind  TEXT NOT NULL,    -- checkin | wiki | ticket
	ref   TEXT NOT NULL,    -- link target: checkin hash, wiki title, ticket id
	label TEXT NOT NULL,    -- human-readable result label
	text  TEXT NOT NULL
);
CREATE VIRTUAL TABLE IF NOT EXISTS search_fts USING fts5(
	text, content='search_doc', content_rowid='rowid'
);
-- event is the timeline, denormalized at WRITE time.
--
-- It was assembled at read time from a five-way UNION ALL over checkin,
-- wiki_edit, ticket_change, technote and forum_post. That plan ends in
-- USE TEMP B-TREE FOR ORDER BY: every request materialized and sorted
-- every event in the repository to return fifty. On tk (61k events) the
-- timeline cost 536ms and the repository list — which asks each store
-- for its newest event — cost 399ms for eleven repos.
--
-- Fossil solved this long ago and this is its shape: one table with a
-- type column and an index on time (event_i1 ON event(mtime)), so the
-- timeline is an index walk that stops at the limit. Fossil renders the
-- same page in 3-4ms whether the repository holds 503 check-ins or
-- 36,702, which is the behaviour worth copying.
--
-- The comment for a TICKET is deliberately not stored here: its label
-- comes from the folded ticket doc, which is refreshed after the change
-- rows land, so a value written now would be stale. It is joined for the
-- returned page only, at most one page of rows, by primary key.
CREATE TABLE IF NOT EXISTS event (
	hash    TEXT PRIMARY KEY, -- the artifact; globally unique across kinds
	kind    TEXT NOT NULL,    -- ci | w | t | e | f
	ref     TEXT NOT NULL,    -- link target: check-in hash, wiki title, ticket id…
	date    TEXT NOT NULL,
	user    TEXT NOT NULL,
	comment TEXT NOT NULL,
	branch  TEXT NOT NULL DEFAULT ''
);
-- The timeline's orderings. DESC in the index matters: the page reads
-- newest-first, and a mismatched direction costs the sort we are here to
-- remove.
CREATE INDEX IF NOT EXISTS event_by_date ON event (date DESC, hash DESC);
CREATE INDEX IF NOT EXISTS event_by_user ON event (user);
-- The tag listing filters on a name prefix ("sym-") and subqueries the
-- branch names out of the same table; without this both halves are full
-- scans with a temp B-tree over the result. An index changes no row a
-- fold writes, so it needs no schema bump and no rebuild — it is created
-- on open like every other index here.
CREATE INDEX IF NOT EXISTS checkin_tag_by_name ON checkin_tag (name, value);
CREATE INDEX IF NOT EXISTS event_by_kind ON event (kind, date DESC, hash DESC);
CREATE INDEX IF NOT EXISTS event_by_branch ON event (branch, date DESC, hash DESC);

-- Maintained by trigger rather than by the fold, for the same reason
-- search_fts is: five call sites that each remember to write a second
-- table is five chances for the timeline to disagree with the tables it
-- summarizes, and that disagreement would be invisible until someone
-- noticed a missing row.
-- contributor is the fleet's people surface, folded at write time for
-- the same reason the event table is: GROUP BY user over tcl's event
-- table is a 395ms scan with a temp B-tree, and an index only gets it
-- to 98ms because the work is the grouping, not the lookup. Across the
-- fleet that was half a second on a landing page.
--
-- Refreshed per batch for the users a batch touched, NOT by counting
-- triggers: SQLite does not fire DELETE triggers for rows a REPLACE
-- displaces unless recursive_triggers is on, so an increment/decrement
-- pair silently double-counts every event that is ever rewritten. A
-- recount over one user is exact whatever the writer did, and
-- event_by_user makes it an index scan.
CREATE TABLE IF NOT EXISTS contributor (
	user   TEXT PRIMARY KEY,
	events INTEGER NOT NULL,
	first  TEXT NOT NULL,
	last   TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS contributor_by_events ON contributor (events DESC, user);
CREATE TRIGGER IF NOT EXISTS event_ci_ai AFTER INSERT ON checkin BEGIN
	INSERT INTO event (hash, kind, ref, date, user, comment, branch)
	VALUES (new.hash, 'ci', new.hash, new.date, new.user, new.comment, new.branch)
	ON CONFLICT (hash) DO UPDATE SET kind=excluded.kind, ref=excluded.ref,
		date=excluded.date, user=excluded.user, comment=excluded.comment,
		branch=excluded.branch;
END;
CREATE TRIGGER IF NOT EXISTS event_ci_au AFTER UPDATE ON checkin BEGIN
	INSERT INTO event (hash, kind, ref, date, user, comment, branch)
	VALUES (new.hash, 'ci', new.hash, new.date, new.user, new.comment, new.branch)
	ON CONFLICT (hash) DO UPDATE SET kind=excluded.kind, ref=excluded.ref,
		date=excluded.date, user=excluded.user, comment=excluded.comment,
		branch=excluded.branch;
END;
CREATE TRIGGER IF NOT EXISTS event_ci_ad AFTER DELETE ON checkin BEGIN
	DELETE FROM event WHERE hash = old.hash;
END;
CREATE TRIGGER IF NOT EXISTS event_w_ai AFTER INSERT ON wiki_edit BEGIN
	INSERT INTO event (hash, kind, ref, date, user, comment, branch)
	VALUES (new.hash, 'w', new.title, new.date, new.user, 'Wiki: ' || new.title, '')
	ON CONFLICT (hash) DO UPDATE SET kind=excluded.kind, ref=excluded.ref,
		date=excluded.date, user=excluded.user, comment=excluded.comment,
		branch=excluded.branch;
END;
CREATE TRIGGER IF NOT EXISTS event_w_ad AFTER DELETE ON wiki_edit BEGIN
	DELETE FROM event WHERE hash = old.hash;
END;
CREATE TRIGGER IF NOT EXISTS event_t_ai AFTER INSERT ON ticket_change BEGIN
	INSERT INTO event (hash, kind, ref, date, user, comment, branch)
	VALUES (new.hash, 't', new.ticket, new.date, new.user, '', '')
	ON CONFLICT (hash) DO UPDATE SET kind=excluded.kind, ref=excluded.ref,
		date=excluded.date, user=excluded.user, comment=excluded.comment,
		branch=excluded.branch;
END;
CREATE TRIGGER IF NOT EXISTS event_t_ad AFTER DELETE ON ticket_change BEGIN
	DELETE FROM event WHERE hash = old.hash;
END;
-- A technote is ONE timeline row however often it is edited, which is
-- fossil's rule and not the rule the other kinds follow. Editing a wiki
-- page is an event — something happened, and the timeline says so.
-- Editing a technote is a CORRECTION to a note that is already filed at
-- an authored moment: the E card fixes where it sits, and every edit
-- claims that same moment. libfossil has one note with eight edits, and
-- rendering all eight put the superseded title on the page beside its
-- own correction, five rows apart.
--
-- So: insert, then evict every loser for this event id. Order-
-- independent by construction, which the fold requires — a clone
-- delivers artifacts in whatever order the wire gives them, so "the
-- newest edit arrives last" is not something to lean on. Whichever row
-- lands, only the current winner survives the statement.
CREATE TRIGGER IF NOT EXISTS event_e_ai AFTER INSERT ON technote BEGIN
	INSERT INTO event (hash, kind, ref, date, user, comment, branch)
	VALUES (new.hash, 'e', new.event, new.date, new.user,
		'Technote: ' || CASE WHEN new.comment != '' THEN new.comment ELSE new.event END, '')
	ON CONFLICT (hash) DO UPDATE SET kind=excluded.kind, ref=excluded.ref,
		date=excluded.date, user=excluded.user, comment=excluded.comment,
		branch=excluded.branch;
	DELETE FROM event WHERE kind = 'e' AND ref = new.event AND hash <> (
		SELECT hash FROM technote WHERE event = new.event
		ORDER BY edited DESC, hash DESC LIMIT 1);
END;
-- Deleting the winner promotes the next edit rather than dropping the
-- note off the timeline. A wholesale wipe promotes its way down the
-- versions and ends with nothing, which is the wanted end state.
CREATE TRIGGER IF NOT EXISTS event_e_ad AFTER DELETE ON technote BEGIN
	DELETE FROM event WHERE hash = old.hash;
	INSERT INTO event (hash, kind, ref, date, user, comment, branch)
	SELECT hash, 'e', event, date, user,
		'Technote: ' || CASE WHEN comment != '' THEN comment ELSE event END, ''
	FROM technote WHERE event = old.event
	ORDER BY edited DESC, hash DESC LIMIT 1;
END;
CREATE TRIGGER IF NOT EXISTS event_f_ai AFTER INSERT ON forum_post BEGIN
	INSERT INTO event (hash, kind, ref, date, user, comment, branch)
	VALUES (new.hash, 'f', new.thread, new.date, new.user,
		'Forum: ' || CASE WHEN new.title != '' THEN new.title ELSE substr(new.body, 1, 60) END, '')
	ON CONFLICT (hash) DO UPDATE SET kind=excluded.kind, ref=excluded.ref,
		date=excluded.date, user=excluded.user, comment=excluded.comment,
		branch=excluded.branch;
END;
CREATE TRIGGER IF NOT EXISTS event_f_ad AFTER DELETE ON forum_post BEGIN
	DELETE FROM event WHERE hash = old.hash;
END;
CREATE TRIGGER IF NOT EXISTS search_doc_ai AFTER INSERT ON search_doc BEGIN
	INSERT INTO search_fts (rowid, text) VALUES (new.rowid, new.text);
END;
CREATE TRIGGER IF NOT EXISTS search_doc_ad AFTER DELETE ON search_doc BEGIN
	INSERT INTO search_fts (search_fts, rowid, text) VALUES ('delete', old.rowid, old.text);
END;
CREATE TRIGGER IF NOT EXISTS search_doc_au AFTER UPDATE ON search_doc BEGIN
	INSERT INTO search_fts (search_fts, rowid, text) VALUES ('delete', old.rowid, old.text);
	INSERT INTO search_fts (rowid, text) VALUES (new.rowid, new.text);
END;
`

// Index is one repository's derived index.
type Index struct {
	db *sql.DB

	// stale is set at Open when the file's recorded shape does not
	// match this code's. Read by the ingest pipeline, which rebuilds.
	stale bool

	// countMu guards a memo of Counts. The section counts go in the
	// repository nav, so they are read on EVERY page render, and on the
	// largest store in the corpus (tk: 147k artifacts) computing them
	// costs ~110ms — dominated by the ticket and check-in scans. Paying
	// that per request would put a tenth of a second on every page in
	// the forge to render a handful of numbers.
	//
	// They only move when a fold does, so the memo is invalidated there
	// rather than expired on a timer: a nav count that lags a write is
	// the kind of small lie that makes a reader distrust the rest.
	countMu sync.Mutex
	counts  *Counts
}

// Counts is a repository's per-section content census — what the nav
// needs to say how much of each kind a store holds, and to tell an empty
// section from a full one.
type Counts struct {
	Checkins  int `json:"checkins"`
	WikiPages int `json:"wikiPages"`
	Tickets   int `json:"tickets"`
	// ForumThreads counts THREADS, not posts: the forum page lists
	// threads, so a count of posts would not match what a reader finds
	// when they follow the link.
	ForumThreads int `json:"forumThreads"`
	// OpenLandings counts branches awaiting a decision — the number the
	// tab exists to show. Settled records are history and belong on the
	// page, not on its badge.
	OpenLandings int `json:"openLandings"`
	Technotes    int `json:"technotes"`
	// Contributors is the number of distinct users who have authored
	// anything. "Is anybody working on this" is a question a listing can
	// answer and a commit message cannot.
	Contributors int `json:"contributors"`
	// Tags counts symbolic names that are not branches — releases.
	Tags int `json:"tags"`
}

// invalidateCounts drops the memo. Called wherever folded state changes.
func (ix *Index) invalidateCounts() {
	ix.countMu.Lock()
	ix.counts = nil
	ix.countMu.Unlock()
}

// Counts returns the per-section census, computing it at most once per
// fold.
func (ix *Index) Counts(includePrivate bool) (Counts, error) {
	ix.countMu.Lock()
	defer ix.countMu.Unlock()
	if ix.counts != nil {
		return *ix.counts, nil
	}
	var c Counts
	for _, q := range []struct {
		sql string
		at  *int
	}{
		{`SELECT count(*) FROM checkin`, &c.Checkins},
		// Live pages only, the list's own rule (newest edit non-empty):
		// a count that includes what the list hides is simply wrong.
		{`SELECT count(*) FROM (SELECT max(date || ' ' || hash), body FROM wiki_edit
			GROUP BY title HAVING length(body) > 0)`, &c.WikiPages},
		{`SELECT count(*) FROM ticket WHERE ` + privacyWhere(TicketFilter{IncludePrivate: includePrivate}), &c.Tickets},
		{`SELECT count(*) FROM forum_post WHERE hash = thread AND ` + notALanding("hash"), &c.ForumThreads},
		{`SELECT count(*) FROM landing WHERE state = 'open'`, &c.OpenLandings},
		{`SELECT count(DISTINCT event) FROM technote`, &c.Technotes},
		{`SELECT count(DISTINCT user) FROM event`, &c.Contributors},
		{`SELECT count(*) FROM (SELECT DISTINCT substr(name, 5) FROM checkin_tag
		   WHERE name LIKE 'sym-%' AND substr(name, 5) NOT IN
		     (SELECT value FROM checkin_tag WHERE name = 'branch'))`, &c.Tags},
	} {
		if err := ix.db.QueryRow(q.sql).Scan(q.at); err != nil {
			return Counts{}, err
		}
	}
	ix.counts = &c
	return c, nil
}

// Open opens (creating if needed) the index database at path.
// schemaVersion is the shape of the derived tables. The DEFAULT way to
// change the shape is an entry in migrations — additive, online, no
// wipe. Bump this INSTEAD only when the change cannot be expressed
// additively: a bump marks every existing index stale, and the staged
// rebuild that follows replays each store from zero while its surfaces
// serve partial data.
const schemaVersion = 4

// derivedTables is every table the index owns, for Reset's drop.
// search_fts's shadow tables go with it. Nothing here is source of
// truth — the store is — which is what makes dropping them all safe.
var derivedTables = []string{
	"checkin", "checkin_tag", "checkin_parent", "technote", "forum_post", "forum_anchor",
	"attachment", "wiki_edit", "ticket_change", "ticket_field", "ticket", "landing",
	"search_doc", "search_fts", "event", "contributor", "filechange",
}

// migrations are the additive column changes an existing file needs to
// reach this code's shape. THE DEFAULT PATH for schema change: an ALTER
// here ships online, with the forge serving throughout, and rows
// written before the column stay empty until a reindex. Bump
// schemaVersion INSTEAD only when the change cannot be expressed
// additively — that path wipes and refolds every store.
var migrations = []string{
	`ALTER TABLE technote ADD COLUMN comment TEXT NOT NULL DEFAULT ''`,
	`ALTER TABLE forum_post ADD COLUMN mimetype TEXT NOT NULL DEFAULT ''`,
	`ALTER TABLE ticket ADD COLUMN type TEXT NOT NULL DEFAULT ''`,
	`ALTER TABLE ticket ADD COLUMN severity TEXT NOT NULL DEFAULT ''`,
	`ALTER TABLE ticket ADD COLUMN priority TEXT NOT NULL DEFAULT ''`,
	`ALTER TABLE ticket ADD COLUMN subsystem TEXT NOT NULL DEFAULT ''`,
	`ALTER TABLE ticket ADD COLUMN resolution TEXT NOT NULL DEFAULT ''`,
	// The list row's people and conversation: who opened it, who last
	// touched it, how many remarks it carries. Folded here because the
	// list is an index walk and must stay one.
	`ALTER TABLE ticket ADD COLUMN author TEXT NOT NULL DEFAULT ''`,
	`ALTER TABLE ticket ADD COLUMN last_user TEXT NOT NULL DEFAULT ''`,
	`ALTER TABLE ticket ADD COLUMN comments INTEGER NOT NULL DEFAULT 0`,
}

// applySchema brings a database to this code's shape: triggers dropped
// and recreated (CREATE TRIGGER IF NOT EXISTS never replaces a changed
// body), tables created, additive migrations applied. Shared by Open
// and Reset so "the shape" has exactly one definition.
func applySchema(db *sql.DB) error {
	// Triggers persist, and a definition that changes must be dropped
	// first or every existing index keeps the old body forever. These
	// were once written with "INSERT OR REPLACE INTO event", which
	// SQLite DISCARDS inside a trigger fired by an upsert — refolding a
	// known artifact aborted whole batches with a UNIQUE failure.
	for _, t := range []string{"event_ci_ai", "event_ci_au", "event_w_ai",
		"event_t_ai", "event_e_ai", "event_f_ai"} {
		if _, err := db.Exec("DROP TRIGGER IF EXISTS " + t); err != nil {
			return fmt.Errorf("index: drop %s: %w", t, err)
		}
	}
	if _, err := db.Exec(schema); err != nil {
		return fmt.Errorf("index: schema: %w", err)
	}
	for _, alter := range migrations {
		if _, err := db.Exec(alter); err != nil && !strings.Contains(err.Error(), "duplicate column") {
			return fmt.Errorf("index: migrate: %w", err)
		}
	}
	return nil
}

func Open(path string) (*Index, error) {
	db, err := driver.Open("file:"+path, fts5.Register)
	if err != nil {
		return nil, err
	}
	// WAL keeps page reads flowing while a fold batch commits; the busy
	// timeout covers the brief checkpoint locks.
	if _, err := db.Exec(`PRAGMA journal_mode=WAL;
		PRAGMA synchronous=NORMAL;
		PRAGMA busy_timeout=5000;`); err != nil {
		db.Close()
		return nil, fmt.Errorf("index: pragmas: %w", err)
	}
	// Shape check, before anything reads a row. A file written by code
	// with different derived tables cannot be repaired by folding
	// forward — the rows already in it were derived by rules that no
	// longer apply — so it is reported as stale and the caller
	// rebuilds. Recorded, not guessed: PRAGMA user_version is stamped
	// only after a Reset, which is the one moment the file is known to
	// match this code's shape.
	var onDisk int
	if err := db.QueryRow(`PRAGMA user_version`).Scan(&onDisk); err != nil {
		db.Close()
		return nil, fmt.Errorf("index: schema version: %w", err)
	}
	stale := onDisk != schemaVersion

	if err := applySchema(db); err != nil {
		db.Close()
		return nil, err
	}
	ix := &Index{db: db}
	// The search index is a cache over search_doc, held to zit's cache
	// contract: verified on open, and any mismatch is a MISS that
	// rebuilds — never an error a reader sees. Drifted fts shadow state
	// (an unsynced delete anywhere in the file's history reuses rowids)
	// otherwise turns every search on the repo into a 500 while the
	// content table beside it is healthy. ~2.5s to verify and 0.6s to
	// rebuild on the fleet's largest index — boot-time insurance, paid once
	// per open. The rank argument makes the check verify against the
	// external content table too, not just the shadow tables' internal
	// consistency — the drift IS the disagreement between the two.
	if _, err := db.Exec(`INSERT INTO search_fts(search_fts, rank) VALUES('integrity-check', 1)`); err != nil {
		log.Printf("index: %s: search index disagrees with its content (%v); rebuilding it", path, err)
		if _, err := db.Exec(`INSERT INTO search_fts(search_fts) VALUES('rebuild')`); err != nil {
			db.Close()
			return nil, fmt.Errorf("index: search rebuild: %w", err)
		}
	}
	if err := ix.migratePrivateTickets(); err != nil {
		db.Close()
		return nil, fmt.Errorf("index: private ticket migration: %w", err)
	}
	if err := ix.backfillEvents(); err != nil {
		db.Close()
		return nil, fmt.Errorf("index: backfill events: %w", err)
	}
	if err := ix.evictSupersededTechnotes(); err != nil {
		db.Close()
		return nil, fmt.Errorf("index: evict superseded technotes: %w", err)
	}
	if err := ix.backfillContributors(); err != nil {
		ix.db.Close()
		return nil, fmt.Errorf("index: backfill contributors: %w", err)
	}
	if err := ix.backfillLandings(); err != nil {
		db.Close()
		return nil, fmt.Errorf("index: backfill landings: %w", err)
	}
	if err := ix.backfillTickets(); err != nil {
		db.Close()
		return nil, fmt.Errorf("index: backfill tickets: %w", err)
	}
	if err := ix.backfillTicketMeta(); err != nil {
		db.Close()
		return nil, fmt.Errorf("index: backfill ticket meta: %w", err)
	}
	ix.stale = stale
	if stale {
		// Not an error: an empty file reads as version 0 too, and a
		// fresh index is not a problem. Stamp it once the schema is
		// applied, and let the caller decide whether the CONTENT needs
		// rebuilding — Stale() answers that.
		if _, err := ix.db.Exec(fmt.Sprintf(`PRAGMA user_version = %d`, schemaVersion)); err != nil {
			ix.db.Close()
			return nil, fmt.Errorf("index: stamp schema version: %w", err)
		}
	}
	return ix, nil
}

// Stale reports that this file was written by code with a different
// derived shape, so its rows were folded under rules that no longer
// apply. It is true for a brand-new file too, which costs one rebuild
// of an empty index and keeps the rule free of exceptions.
//
// The caller rebuilds rather than refusing, because this state is
// disposable — where fossil stops and demands "fossil rebuild", quarry
// can simply do it.
func (ix *Index) Stale() bool { return ix.stale }

// ClearStale is called once a rebuild has re-derived every row under
// the current shape.
func (ix *Index) ClearStale() { ix.stale = false }

// refreshContributor recomputes one person's row from the event table.
// A recount rather than an adjustment: it is correct no matter how the
// event rows were written, and with event_by_user it touches only that
// person's rows. A person whose events all disappeared loses their row
// rather than lingering at zero.
func refreshContributor(tx *sql.Tx, user string) error {
	if _, err := tx.Exec(`INSERT OR REPLACE INTO contributor (user, events, first, last)
		SELECT user, count(*), min(date), max(date) FROM event
		WHERE user = ? GROUP BY user`, user); err != nil {
		return err
	}
	_, err := tx.Exec(`DELETE FROM contributor WHERE user = ?
		AND NOT EXISTS (SELECT 1 FROM event WHERE user = ?)`, user, user)
	return err
}

// migratePrivateTickets adds the private column to an index written
// before it existed and fills it from the folded fields.
//
// This one is not optional the way a display backfill is: an index that
// upgrades without it has the column defaulting to 0, which reports
// every private ticket as public. The whole point of the column is to
// withhold, so it must be filled in the same step that adds it.
func (ix *Index) migratePrivateTickets() error {
	_, err := ix.db.Exec(`ALTER TABLE ticket ADD COLUMN private INTEGER NOT NULL DEFAULT 0`)
	if err != nil && !strings.Contains(err.Error(), "duplicate column") {
		return err
	}
	if err == nil {
		// The column is new, so nothing in it is trustworthy yet.
		if _, err := ix.db.Exec(`
			UPDATE ticket SET private = 1 WHERE id IN (
				SELECT c.ticket FROM ticket_change c
				JOIN ticket_field f ON f.hash = c.hash AND f.name = 'is_private'
				WHERE lower(f.value) NOT IN ('', '0', 'false', 'no', 'off'))`); err != nil {
			return err
		}
		// And their search documents have to go with them.
		if _, err := ix.db.Exec(`DELETE FROM search_doc WHERE kind = 'ticket'
			AND substr(hash, 8) IN (SELECT id FROM ticket WHERE private = 1)`); err != nil {
			return err
		}
	}
	// The event sweep runs on EVERY open, not only when the column is
	// born: events learned to follow privacy after the column did, so an
	// index can hold the flag and the leak at once. One indexed delete,
	// idempotent, and the fold maintains it from here.
	if _, err := ix.db.Exec(`DELETE FROM event WHERE kind = 't'
		AND ref IN (SELECT id FROM ticket WHERE private = 1)`); err != nil {
		return err
	}
	return nil
}

// backfillContributors folds the people table from an event table
// written before it existed — same reasoning as backfillEvents, and
// same idempotence: it runs only when the table is empty and events
// are not.
func (ix *Index) backfillContributors() error {
	var have, events int
	if err := ix.db.QueryRow(`SELECT count(*) FROM contributor`).Scan(&have); err != nil {
		return err
	}
	if err := ix.db.QueryRow(`SELECT count(*) FROM event`).Scan(&events); err != nil {
		return err
	}
	if have > 0 || events == 0 {
		return nil
	}
	_, err := ix.db.Exec(`INSERT OR REPLACE INTO contributor (user, events, first, last)
		SELECT user, count(*), min(date), max(date) FROM event GROUP BY user`)
	return err
}

// backfillEvents populates the timeline table from indexes written
// before it existed. The triggers only fire on new writes, so without
// this an upgraded index serves an EMPTY timeline until someone happens
// to reindex — derived data, technically repairable, but a blank
// timeline reads as data loss and nobody would connect it to an upgrade.
//
// Cheap and idempotent: it runs only when the table is empty and the
// sources are not, and INSERT OR REPLACE keyed by hash makes a repeat
// harmless.
func (ix *Index) backfillEvents() error {
	var events, checkins int
	if err := ix.db.QueryRow(`SELECT count(*) FROM event`).Scan(&events); err != nil {
		return err
	}
	if err := ix.db.QueryRow(`SELECT count(*) FROM checkin`).Scan(&checkins); err != nil {
		return err
	}
	if events > 0 || checkins == 0 {
		return nil
	}
	tx, err := ix.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	for _, q := range []string{
		`INSERT OR REPLACE INTO event (hash,kind,ref,date,user,comment,branch)
		 SELECT hash,'ci',hash,date,user,comment,branch FROM checkin`,
		`INSERT OR REPLACE INTO event (hash,kind,ref,date,user,comment,branch)
		 SELECT hash,'w',title,date,user,'Wiki: '||title,'' FROM wiki_edit`,
		`INSERT OR REPLACE INTO event (hash,kind,ref,date,user,comment,branch)
		 SELECT hash,'t',ticket,date,user,'','' FROM ticket_change`,
		`INSERT OR REPLACE INTO event (hash,kind,ref,date,user,comment,branch)
		 SELECT hash,'e',event,date,user,
		   'Technote: '||CASE WHEN comment!='' THEN comment ELSE event END,''
		 FROM technote t WHERE edited = (
		   SELECT max(t2.edited) FROM technote t2 WHERE t2.event = t.event)
		 GROUP BY event`,
		`INSERT OR REPLACE INTO event (hash,kind,ref,date,user,comment,branch)
		 SELECT hash,'f',thread,date,user,
		   'Forum: '||CASE WHEN title!='' THEN title ELSE substr(body,1,60) END,'' FROM forum_post`,
	} {
		if _, err := tx.Exec(q); err != nil {
			return err
		}
	}
	return tx.Commit()
}

// evictSupersededTechnotes drops the timeline rows that indexes written
// before the collapsing trigger left behind. applySchema replaces
// trigger BODIES on open, so new writes are already correct — but a
// trigger cannot reach backwards, and the rows already in the table
// would keep every superseded edit on the timeline until somebody
// happened to reindex.
//
// Idempotent and cheap: event_by_kind covers the scan, the delete
// matches nothing on a healthy index, and it is a derived table either
// way. Unconditional for that reason — a guard that counted first
// would cost the same scan it was trying to avoid.
func (ix *Index) evictSupersededTechnotes() error {
	_, err := ix.db.Exec(`DELETE FROM event WHERE kind = 'e' AND hash NOT IN (
		SELECT hash FROM technote t WHERE edited = (
			SELECT max(t2.edited) FROM technote t2 WHERE t2.event = t.event)
		GROUP BY event)`)
	return err
}

// backfillTickets populates the folded ticket table for indexes written
// before it existed, for the same reason backfillEvents does: the fold
// only writes it on new changes, so an upgraded index would show an
// EMPTY ticket list until someone reindexed.
//
// This runs the expensive grouped query ONCE, at open, instead of on
// every request — which is the whole point of the table.
func (ix *Index) backfillTickets() error {
	var tickets, changes int
	if err := ix.db.QueryRow(`SELECT count(*) FROM ticket`).Scan(&tickets); err != nil {
		return err
	}
	if err := ix.db.QueryRow(`SELECT count(*) FROM ticket_change`).Scan(&changes); err != nil {
		return err
	}
	if tickets > 0 || changes == 0 {
		return nil
	}
	_, err := ix.db.Exec(`
		INSERT OR REPLACE INTO ticket (id, created, updated, title, status, private)
		SELECT c.ticket, min(c.date), max(c.date),
			COALESCE((SELECT f.value FROM ticket_change c2
				JOIN ticket_field f ON f.hash = c2.hash AND f.name = 'title'
				WHERE c2.ticket = c.ticket
				ORDER BY c2.date DESC, c2.hash DESC LIMIT 1), ''),
			COALESCE((SELECT f.value FROM ticket_change c2
				JOIN ticket_field f ON f.hash = c2.hash AND f.name = 'status'
				WHERE c2.ticket = c.ticket
				ORDER BY c2.date DESC, c2.hash DESC LIMIT 1), ''),
			CASE lower(COALESCE((SELECT f.value FROM ticket_change c2
				JOIN ticket_field f ON f.hash = c2.hash AND f.name = 'is_private'
				WHERE c2.ticket = c.ticket
				ORDER BY c2.date DESC, c2.hash DESC LIMIT 1), ''))
				WHEN '' THEN 0 WHEN '0' THEN 0 WHEN 'false' THEN 0
				WHEN 'no' THEN 0 WHEN 'off' THEN 0 ELSE 1 END
		FROM ticket_change c GROUP BY c.ticket`)
	return err
}

// backfillTicketMeta fills author/last_user/comments for summaries
// folded before those columns existed. Once, at open; a ticket whose
// author is known is left alone, and an authorless ticket (none exist
// — every change carries a user) would simply be recomputed each open.
func (ix *Index) backfillTicketMeta() error {
	_, err := ix.db.Exec(`
		UPDATE ticket SET
			author = COALESCE((SELECT user FROM ticket_change c WHERE c.ticket = ticket.id
				ORDER BY c.date, c.hash LIMIT 1), ''),
			last_user = COALESCE((SELECT user FROM ticket_change c WHERE c.ticket = ticket.id
				ORDER BY c.date DESC, c.hash DESC LIMIT 1), ''),
			comments = (SELECT count(*) FROM ticket_change c
				JOIN ticket_field f ON f.hash = c.hash AND f.name = 'icomment' AND f.value <> ''
				WHERE c.ticket = ticket.id)
		WHERE author = ''`)
	return err
}

func (ix *Index) Close() error { return ix.db.Close() }

// Parsed pairs an artifact hash with its parsed form; Art is nil for
// opaque content blobs (folded as nothing — content is served on demand
// through zit, never copied into the index).
type Parsed struct {
	Hash string
	Art  *artifact.Artifact
	// Changes is the file paths this check-in changed, resolved by the
	// ingest (which owns artifact plumbing) — nil for non-check-ins and
	// for check-ins whose baseline could not be recovered, in which
	// case the listing simply shows no last-change for those paths.
	Changes []string
}

// Fold applies one event batch atomically. Folds are idempotent upserts
// keyed by hash, so at-least-once event delivery converges. Ingest
// position is not stored here: the store cursor lives in NATS KV and the
// stream position in the durable consumer (0001 §4) — both advisory,
// both rebuilt by replaying stores.
// Fold reports whether the batch touched branch-bearing artifacts; the
// caller runs RecomputeBranches once after draining (recomputing per
// batch is quadratic over a bulk ingest).
func (ix *Index) Fold(batch []Parsed) (bool, error) {
	tx, err := ix.db.Begin()
	if err != nil {
		return false, err
	}
	defer tx.Rollback()
	branchy := false
	tickets := map[string]bool{}
	people := map[string]bool{}
	for _, p := range batch {
		if p.Art == nil {
			continue
		}
		if err := fold(tx, p.Hash, p.Art); err != nil {
			return false, fmt.Errorf("index: fold %s: %w", p.Hash, err)
		}
		if err := foldChanges(tx, p.Hash, p.Art, p.Changes); err != nil {
			return false, fmt.Errorf("index: changes %s: %w", p.Hash, err)
		}
		if p.Art.User != "" {
			people[p.Art.User] = true
		}
		switch p.Art.Type {
		case artifact.Manifest, artifact.Control:
			branchy = true
		case artifact.Ticket:
			tickets[p.Art.TicketID] = true
		}
	}
	// Ticket search docs hold the FOLDED state (one doc per ticket, keyed
	// by id), so snippets never show stale field values from early
	// changes (ticket 050). Once per touched ticket per batch — per
	// change is quadratic over a real ticket corpus.
	for id := range tickets {
		if err := refreshTicketDoc(tx, id); err != nil {
			return false, fmt.Errorf("index: ticket doc %s: %w", id, err)
		}
	}
	// Same shape as the ticket docs above: once per touched person per
	// batch, recounted from the event table rather than adjusted.
	for user := range people {
		if err := refreshContributor(tx, user); err != nil {
			return false, fmt.Errorf("index: contributor %s: %w", user, err)
		}
	}
	if err := tx.Commit(); err != nil {
		return false, err
	}
	// The memo is dropped AFTER the commit, so a concurrent reader can
	// never repopulate it from the pre-commit state and cache that.
	ix.invalidateCounts()
	return branchy, nil
}

// CheckinRecord is one check-in's derived facts from `zit crosslink`
// (zit#109) — everything the Manifest branch of fold() writes, without
// the manifest. Date is the canonical millisecond ISO form.
type CheckinRecord struct {
	Hash    string
	Date    string
	User    string
	Comment string
	Parents []string
	Tags    []artifact.Tag
	Changes []string
}

// FoldCheckins folds crosslink records in one transaction. It writes
// exactly the rows fold()'s Manifest branch writes — checkin, parents,
// tags, filechange — and the event/FTS triggers on those tables keep
// the derived surfaces identical to the artifact path by construction.
func (ix *Index) FoldCheckins(batch []CheckinRecord) error {
	tx, err := ix.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	// Prepared once per transaction: tx.Exec re-prepares per call, and
	// a large store folds millions of these rows in one rebuild.
	ciStmt, err := tx.Prepare(`INSERT INTO checkin (hash, date, user, comment)
		VALUES (?, ?, ?, ?)
		ON CONFLICT (hash) DO UPDATE SET date=excluded.date,
			user=excluded.user, comment=excluded.comment`)
	if err != nil {
		return err
	}
	defer ciStmt.Close()
	parStmt, err := tx.Prepare(`INSERT OR REPLACE INTO checkin_parent (child, parent, ord)
		VALUES (?, ?, ?)`)
	if err != nil {
		return err
	}
	defer parStmt.Close()
	// filechange is the volume table (1.66M rows on tcl): a 100-row
	// VALUES insert amortises the per-statement machinery.
	const fcChunk = 100
	fcWide, err := tx.Prepare(`INSERT INTO filechange (path, checkin, date) VALUES ` +
		strings.TrimSuffix(strings.Repeat("(?, ?, ?),", fcChunk), ",") +
		` ON CONFLICT (path, checkin) DO NOTHING`)
	if err != nil {
		return err
	}
	defer fcWide.Close()
	fcStmt, err := tx.Prepare(`INSERT INTO filechange (path, checkin, date)
		VALUES (?, ?, ?) ON CONFLICT (path, checkin) DO NOTHING`)
	if err != nil {
		return err
	}
	defer fcStmt.Close()
	var fcArgs []any
	flushFC := func() error {
		for len(fcArgs) >= fcChunk*3 {
			if _, err := fcWide.Exec(fcArgs[:fcChunk*3]...); err != nil {
				return err
			}
			fcArgs = fcArgs[fcChunk*3:]
		}
		return nil
	}
	sdStmt, err := tx.Prepare(`INSERT OR REPLACE INTO search_doc (hash, kind, ref, label, text)
		VALUES (?, 'checkin', ?, ?, ?)`)
	if err != nil {
		return err
	}
	defer sdStmt.Close()
	for _, r := range batch {
		if _, err := ciStmt.Exec(r.Hash, r.Date, r.User, r.Comment); err != nil {
			return fmt.Errorf("index: crosslink %s: %w", r.Hash, err)
		}
		for i, parent := range r.Parents {
			if _, err := parStmt.Exec(r.Hash, parent, i); err != nil {
				return err
			}
		}
		if err := foldTags(tx, r.Hash, r.Hash, r.Date, r.Tags); err != nil {
			return err
		}
		for _, path := range ancestorPaths(r.Changes) {
			fcArgs = append(fcArgs, path, r.Hash, r.Date)
		}
		if err := flushFC(); err != nil {
			return err
		}
		// The same search document fold() writes for a Manifest — a
		// crosslink-rebuilt store must answer check-in search
		// identically to an artifact-folded one.
		first, _, _ := strings.Cut(r.Comment, "\n")
		if _, err := sdStmt.Exec(r.Hash, r.Hash, first, r.Comment); err != nil {
			return err
		}
	}
	for i := 0; i+2 < len(fcArgs); i += 3 {
		if _, err := fcStmt.Exec(fcArgs[i], fcArgs[i+1], fcArgs[i+2]); err != nil {
			return err
		}
	}
	return tx.Commit()
}

// ancestorPaths expands changed paths with each ancestor directory,
// deduplicated — the same set foldChangePaths writes.
func ancestorPaths(changes []string) []string {
	seen := map[string]bool{}
	var out []string
	for _, path := range changes {
		for p := path; p != ""; {
			if seen[p] {
				break
			}
			seen[p] = true
			out = append(out, p)
			i := strings.LastIndexByte(p, '/')
			if i < 0 {
				break
			}
			p = p[:i]
		}
	}
	return out
}

// FoldBulk is Fold for a REBUILD: the per-batch ticket-document and
// contributor refreshes are skipped — over a bulk replay they are
// quadratic (every batch re-derives every ticket it touched, and a
// large tracker is touched by most batches) — and FinishBulk performs
// each refresh exactly once at the end.
func (ix *Index) FoldBulk(batch []Parsed) error {
	tx, err := ix.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	for _, p := range batch {
		if p.Art == nil {
			continue
		}
		if err := fold(tx, p.Hash, p.Art); err != nil {
			return fmt.Errorf("index: fold %s: %w", p.Hash, err)
		}
		if err := foldChanges(tx, p.Hash, p.Art, p.Changes); err != nil {
			return fmt.Errorf("index: changes %s: %w", p.Hash, err)
		}
	}
	return tx.Commit()
}

// FoldChangesBulk writes filechange rows for many check-ins in one
// transaction — the rebuild's second pass, after every manifest is in
// hand and changes can be derived without any store round-trip.
func (ix *Index) FoldChangesBulk(items []BulkChanges) error {
	tx, err := ix.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	for _, it := range items {
		if err := foldChanges(tx, it.Hash, it.Art, it.Changes); err != nil {
			return fmt.Errorf("index: changes %s: %w", it.Hash, err)
		}
	}
	return tx.Commit()
}

// BulkChanges pairs a check-in with its resolved changed paths.
type BulkChanges struct {
	Hash    string
	Art     *artifact.Artifact
	Changes []string
}

// bulkDroppable are the secondary indexes a bulk rebuild maintains
// row-by-row for no reader: dropped during the fold, rebuilt in one
// sorted pass by applySchema afterwards. Primary keys stay — the
// idempotent upserts need them.
var bulkDroppable = []string{
	"filechange_by_path", "checkin_by_branch_date", "checkin_tag_by_target",
	"event_by_date", "event_by_kind", "event_by_branch", "event_by_user",
	"contributor_by_events",
}

// SuspendDerivedMaintenance drops the FTS sync triggers and the
// secondary indexes for a bulk rebuild: per-row maintenance is the
// classic bulk-load tax — 1.66M random-order filechange rows on tcl
// churned b-trees nobody was reading.
func (ix *Index) SuspendDerivedMaintenance() error {
	for _, t := range []string{"search_doc_ai", "search_doc_ad", "search_doc_au"} {
		if _, err := ix.db.Exec("DROP TRIGGER IF EXISTS " + t); err != nil {
			return err
		}
	}
	for _, i := range bulkDroppable {
		if _, err := ix.db.Exec("DROP INDEX IF EXISTS " + i); err != nil {
			return err
		}
	}
	return nil
}

// ResumeDerivedMaintenance recreates triggers and indexes (applySchema
// owns their one definition). Call BEFORE FinishBulk — its refreshes
// read through these indexes.
func (ix *Index) ResumeDerivedMaintenance() error {
	return applySchema(ix.db)
}

// RebuildSearch repopulates the FTS index from search_doc in one pass —
// after FinishBulk, whose ticket documents must be included.
func (ix *Index) RebuildSearch() error {
	_, err := ix.db.Exec(`INSERT INTO search_fts(search_fts) VALUES('rebuild')`)
	return err
}

// FinishBulk performs the refreshes FoldBulk deferred: every ticket
// document and every contributor aggregate, once each.
func (ix *Index) FinishBulk() error {
	tx, err := ix.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	collect := func(q string) ([]string, error) {
		rows, err := tx.Query(q)
		if err != nil {
			return nil, err
		}
		defer rows.Close()
		var out []string
		for rows.Next() {
			var v string
			if err := rows.Scan(&v); err != nil {
				return nil, err
			}
			out = append(out, v)
		}
		return out, rows.Err()
	}
	ids, err := collect(`SELECT DISTINCT ticket FROM ticket_change`)
	if err != nil {
		return err
	}
	for _, id := range ids {
		if err := refreshTicketDoc(tx, id); err != nil {
			return fmt.Errorf("index: ticket doc %s: %w", id, err)
		}
	}
	users, err := collect(`SELECT DISTINCT user FROM event WHERE user <> ''`)
	if err != nil {
		return err
	}
	for _, u := range users {
		if err := refreshContributor(tx, u); err != nil {
			return fmt.Errorf("index: contributor %s: %w", u, err)
		}
	}
	if err := tx.Commit(); err != nil {
		return err
	}
	ix.invalidateCounts()
	return nil
}

// FoldNow folds one batch and recomputes branches immediately — the
// single-batch convenience for callers outside the drain loop.
func (ix *Index) FoldNow(batch []Parsed) error {
	branchy, err := ix.Fold(batch)
	if err != nil {
		return err
	}
	if branchy {
		return ix.RecomputeBranches()
	}
	return nil
}

// RecomputeBranches rewrites the branch column from recorded tag facts —
// once per fold round, after every batch has landed.
func (ix *Index) RecomputeBranches() error {
	tx, err := ix.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	if err := recomputeBranches(tx); err != nil {
		return fmt.Errorf("index: branches: %w", err)
	}
	return tx.Commit()
}

func fold(tx *sql.Tx, hash string, a *artifact.Artifact) error {
	switch a.Type {
	case artifact.Manifest:
		// INSERT OR REPLACE would clobber the computed branch column on
		// redelivery; upsert only the artifact-borne fields.
		if _, err := tx.Exec(`INSERT INTO checkin (hash, date, user, comment)
			VALUES (?, ?, ?, ?)
			ON CONFLICT (hash) DO UPDATE SET date=excluded.date,
				user=excluded.user, comment=excluded.comment`,
			hash, a.Date, a.User, a.Comment); err != nil {
			return err
		}
		for i, parent := range a.Parents {
			if _, err := tx.Exec(`INSERT OR REPLACE INTO checkin_parent (child, parent, ord)
				VALUES (?, ?, ?)`, hash, parent, i); err != nil {
				return err
			}
		}
		if err := foldTags(tx, hash, hash, a.Date, a.Tags); err != nil {
			return err
		}
	case artifact.Control:
		if err := foldTags(tx, hash, "", a.Date, a.Tags); err != nil {
			return err
		}
	case artifact.Wiki:
		if _, err := tx.Exec(`INSERT OR REPLACE INTO wiki_edit (hash, title, date, user, mimetype, body)
			VALUES (?, ?, ?, ?, ?, ?)`,
			hash, a.WikiTitle, a.Date, a.User, a.Mimetype, a.Body); err != nil {
			return err
		}
	case artifact.Attachment:
		// Fossil counts an attachment as an event on whatever it targets,
		// which is why a ticket-change census that ignores them reads
		// short. An A card with no source is a DELETION of that name on
		// that target, not a malformed record, so the row is kept and the
		// query resolves it.
		if at := a.Attachment; at != nil {
			if _, err := tx.Exec(`INSERT OR REPLACE INTO attachment (hash, name, target, src, date, user)
				VALUES (?, ?, ?, ?, ?, ?)`,
				hash, at.Name, at.Target, at.Source, a.Date, a.User); err != nil {
				return err
			}
		}
	case artifact.Ticket:
		if _, err := tx.Exec(`INSERT OR REPLACE INTO ticket_change (hash, ticket, date, user)
			VALUES (?, ?, ?, ?)`, hash, a.TicketID, a.Date, a.User); err != nil {
			return err
		}
		for _, f := range a.Fields {
			if _, err := tx.Exec(`INSERT OR REPLACE INTO ticket_field (hash, name, value)
				VALUES (?, ?, ?)`, hash, f.Name, f.Value); err != nil {
				return err
			}
		}
	case artifact.Technote:
		if _, err := tx.Exec(`INSERT OR REPLACE INTO technote (hash, event, date, edited, user, mimetype, comment, body)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
			hash, a.EventID, a.EventTime, a.Date, a.User, a.Mimetype, a.Comment, string(a.Body)); err != nil {
			return err
		}
	case artifact.Forum:
		// A thread opener has no G card; it roots its own thread.
		thread := a.ThreadRoot
		if thread == "" {
			thread = hash
		}
		if _, err := tx.Exec(`INSERT OR REPLACE INTO forum_post (hash, thread, parent, title, date, user, mimetype, body)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
			hash, thread, a.InReplyTo, a.ThreadTitle, a.Date, a.User, a.Mimetype, string(a.Body)); err != nil {
			return err
		}
		// Anchors: hex references in the body, EXTRACTED here and
		// resolved only at render — a mention folded before its target
		// arrives must still anchor once it does (0001 §5: rebuildable,
		// order-independent).
		if _, err := tx.Exec(`DELETE FROM forum_anchor WHERE post = ?`, hash); err != nil {
			return err
		}
		for _, ref := range hexRefs(string(a.Body)) {
			if _, err := tx.Exec(`INSERT OR IGNORE INTO forum_anchor (post, thread, target)
				VALUES (?, ?, ?)`, hash, thread, ref); err != nil {
				return err
			}
		}
		// A landing is a reading of the whole thread, so ANY post in it
		// recomputes the record -- which is what makes a reply folded
		// before its root converge on the same row.
		if err := foldLanding(tx, thread); err != nil {
			return err
		}
	}
	// Ticket search docs are refreshed once per ticket per batch by the
	// caller (fold order within the batch doesn't matter — the refresh
	// reads the folded rows).
	if a.Type != artifact.Ticket {
		if kind, ref, label, text := searchDoc(hash, a); kind != "" {
			if _, err := tx.Exec(`INSERT OR REPLACE INTO search_doc (hash, kind, ref, label, text)
				VALUES (?, ?, ?, ?, ?)`, hash, kind, ref, label, text); err != nil {
				return err
			}
		}
	}
	// Control, cluster, technote, forum, attachment: folded when their
	// pages land (phase 2); skipping them is safe because a reindex
	// replays every artifact.
	return nil
}

// foldTags records an artifact's T cards as tag facts. self names the
// check-in a "*" target resolves to (the carrying manifest); control
// artifacts have no self and their "*" targets are illegal anyway
// (§5.4), so those rows are skipped.
func foldTags(tx *sql.Tx, src, self, date string, tags []artifact.Tag) error {
	for _, t := range tags {
		target := t.Target
		if target == "*" {
			if self == "" {
				continue
			}
			target = self
		}
		if _, err := tx.Exec(`INSERT OR REPLACE INTO checkin_tag (src, date, checkin, name, sign, value)
			VALUES (?, ?, ?, ?, ?, ?)`,
			src, date, target, strings.TrimLeft(t.Name, "+-*"), t.Name[:1], t.Value); err != nil {
			return err
		}
	}
	return nil
}

// recomputeBranches rewrites every check-in's branch from the recorded
// tag facts: a propagating branch tag names its check-in's branch, and
// the name flows down primary-parent edges until a descendant carries
// its own. A WHOLESALE recompute after each fold batch is what makes
// the column order-independent — the same tag facts and DAG yield the
// same branches no matter what order artifacts arrived in (the
// disposability property). ~4k check-ins recompute in milliseconds.
func recomputeBranches(tx *sql.Tx) error {
	// The walk lands in an indexed temp table first: the final UPDATE's
	// per-row probe against a bare materialized CTE is O(n²) at real-store
	// sizes (sqlite.org's repo: 36k check-ins).
	if _, err := tx.Exec(`
		CREATE TEMP TABLE IF NOT EXISTS br_walk (hash TEXT PRIMARY KEY, branch TEXT) WITHOUT ROWID;
		DELETE FROM br_walk;
		INSERT OR IGNORE INTO br_walk (hash, branch)
		WITH RECURSIVE br(hash, branch) AS (
			-- one seed per check-in: the branch tag from the LATEST
			-- carrying artifact wins (bare column with max())
			SELECT t.checkin, t.value FROM (
				SELECT checkin, value, max(date || src)
				FROM checkin_tag WHERE name = 'branch' AND sign = '*'
				GROUP BY checkin
			) t JOIN checkin c ON c.hash = t.checkin
			UNION
			SELECT p.child, br.branch FROM br
			JOIN checkin_parent p ON p.parent = br.hash AND p.ord = 0
			WHERE NOT EXISTS (SELECT 1 FROM checkin_tag t2
				WHERE t2.checkin = p.child AND t2.name = 'branch' AND t2.sign = '*')
		)
		SELECT hash, branch FROM br;
		UPDATE checkin SET branch = COALESCE(
			(SELECT branch FROM br_walk WHERE br_walk.hash = checkin.hash), 'trunk');`); err != nil {
		return err
	}
	return nil
}

// refreshTicketDoc rebuilds one ticket's search doc from its folded
// current state, inside the fold transaction.
func refreshTicketDoc(tx *sql.Tx, ticket string) error {
	rows, err := tx.Query(`SELECT f.name, f.value FROM ticket_change c
		JOIN ticket_field f ON f.hash = c.hash
		WHERE c.ticket = ? ORDER BY c.date, c.hash, f.name`, ticket)
	if err != nil {
		return err
	}
	defer rows.Close()
	current := map[string]string{}
	var order []string
	for rows.Next() {
		var name, value string
		if err := rows.Scan(&name, &value); err != nil {
			return err
		}
		if _, seen := current[name]; !seen {
			order = append(order, name)
		}
		current[name] = value // latest assignment wins
	}
	if err := rows.Err(); err != nil {
		return err
	}
	var b strings.Builder
	for _, name := range order {
		b.WriteString(current[name])
		b.WriteByte('\n')
	}
	label := current["title"]
	if label == "" && len(ticket) >= 10 {
		label = ticket[:10]
	}
	// A private ticket is dropped from the search corpus, not merely
	// hidden from the list: a snippet leaks the content whether or not
	// the ticket page is reachable. Its timeline events go the same way
	// — an event row is id, author and date on public display. Both
	// transitions are handled here because privacy is a fold-time
	// property that can change over a ticket's life: hiding deletes,
	// and un-privating RESTORES from ticket_change, or erring toward
	// private would be a one-way door nobody dares open.
	if boolField(current["is_private"]) == 1 {
		if _, err := tx.Exec(`DELETE FROM search_doc WHERE hash = ?`, "ticket:"+ticket); err != nil {
			return err
		}
		if _, err := tx.Exec(`DELETE FROM event WHERE kind = 't' AND ref = ?`, ticket); err != nil {
			return err
		}
	} else {
		if _, err := tx.Exec(`INSERT OR REPLACE INTO search_doc (hash, kind, ref, label, text)
			VALUES (?, 'ticket', ?, ?, ?)`, "ticket:"+ticket, ticket, label, b.String()); err != nil {
			return err
		}
		if _, err := tx.Exec(`INSERT INTO event (hash, kind, ref, date, user, comment, branch)
			SELECT hash, 't', ticket, date, user, '', '' FROM ticket_change WHERE ticket = ?
			ON CONFLICT (hash) DO NOTHING`, ticket); err != nil {
			return err
		}
	}
	// The list surface, folded from the same pass. Dates come from the
	// (ticket, date) index rather than the rows above, which are ordered
	// for field folding and would need a second traversal to bound.
	var created, updated string
	if err := tx.QueryRow(`SELECT COALESCE(min(date),''), COALESCE(max(date),'')
		FROM ticket_change WHERE ticket = ?`, ticket).Scan(&created, &updated); err != nil {
		return err
	}
	var author, lastUser string
	var comments int
	if err := tx.QueryRow(`SELECT
		COALESCE((SELECT user FROM ticket_change WHERE ticket = ?1 ORDER BY date, hash LIMIT 1), ''),
		COALESCE((SELECT user FROM ticket_change WHERE ticket = ?1 ORDER BY date DESC, hash DESC LIMIT 1), ''),
		(SELECT count(*) FROM ticket_change c
			JOIN ticket_field f ON f.hash = c.hash AND f.name = 'icomment' AND f.value <> ''
			WHERE c.ticket = ?1)`, ticket).Scan(&author, &lastUser, &comments); err != nil {
		return err
	}
	_, err = tx.Exec(`INSERT INTO ticket
		(id, created, updated, title, status, private,
		 type, severity, priority, subsystem, resolution, author, last_user, comments)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT (id) DO UPDATE SET created=excluded.created,
			updated=excluded.updated, title=excluded.title, status=excluded.status,
			private=excluded.private, type=excluded.type,
			severity=excluded.severity, priority=excluded.priority,
			subsystem=excluded.subsystem, resolution=excluded.resolution,
			author=excluded.author, last_user=excluded.last_user, comments=excluded.comments`,
		ticket, created, updated, current["title"], current["status"],
		boolField(current["is_private"]),
		current["type"], current["severity"], current["priority"],
		current["subsystem"], current["resolution"], author, lastUser, comments)
	return err
}

// boolField reads fossil's private flag. It is a text field like every
// other, so anything that is not an explicit falsehood counts as TRUE:
// erring toward private is the only safe direction for a value whose
// whole purpose is to withhold.
func boolField(v string) int {
	switch strings.ToLower(strings.TrimSpace(v)) {
	case "", "0", "false", "no", "off":
		return 0
	}
	return 1
}

// searchDoc maps one immutable artifact to its search-corpus row; empty
// kind means "not searched here" (tickets go through refreshTicketDoc).
// Every wiki edit is indexed, so hits can land on historical revisions;
// queries dedupe by ref.
func searchDoc(hash string, a *artifact.Artifact) (kind, ref, label, text string) {
	switch a.Type {
	case artifact.Manifest:
		first, _, _ := strings.Cut(a.Comment, "\n")
		return "checkin", hash, first, a.Comment
	case artifact.Wiki:
		return "wiki", a.WikiTitle, a.WikiTitle, a.WikiTitle + "\n" + string(a.Body)
	case artifact.Technote:
		label := a.Comment // C card is the optional one-line title
		if label == "" {
			label, _, _ = strings.Cut(string(a.Body), "\n")
		}
		return "technote", a.EventID, label, string(a.Body)
	case artifact.Forum:
		label := a.ThreadTitle
		if label == "" {
			label, _, _ = strings.Cut(string(a.Body), "\n")
		}
		root := a.ThreadRoot
		if root == "" {
			root = hash
		}
		return "forum", root, label, string(a.Body)
	}
	return "", "", "", ""
}

// SearchResult is one hit, deduplicated by (kind, ref). Snippet marks
// matches with ⟦…⟧ — characters that cannot collide with markdown or
// bracket-bearing content (ticket 060); renderers turn them into <mark>.
type SearchResult struct {
	Kind    string `json:"kind"`
	Ref     string `json:"ref"`
	Label   string `json:"label"`
	Snippet string `json:"snippet"`
	// Rank is bm25, lower being better. It is not serialized: a score
	// is only meaningful against the corpus that produced it, so
	// publishing it would invite callers to compare scores from
	// different repositories as if they were one scale. A fleet-wide
	// merge uses it in-process and says plainly that it interleaves.
	Rank float64 `json:"-"`
}

// SearchKinds are the document kinds Search can restrict to, which is
// every kind searchDoc produces. A caller naming anything else gets an
// error rather than an empty result, because silently matching nothing
// is indistinguishable from a query that genuinely found nothing.
var SearchKinds = []string{"checkin", "wiki", "ticket", "forum", "technote"}

// Search runs an FTS5 query over the corpus. The user string is
// re-quoted token-by-token so FTS operator syntax cannot inject or
// error. Ranking is bm25 with ref as the deterministic tiebreak.
//
// An empty kind searches everything; naming one restricts to it, which
// is what lets a search box mean "in this section" on a section page.
func (ix *Index) Search(query, kind string, limit int) ([]SearchResult, error) {
	tokens := strings.Fields(query)
	if len(tokens) == 0 {
		return nil, nil
	}
	if kind != "" && !slices.Contains(SearchKinds, kind) {
		return nil, fmt.Errorf("index: unknown search kind %q", kind)
	}
	for i, tok := range tokens {
		tokens[i] = `"` + strings.ReplaceAll(tok, `"`, `""`) + `"`
	}
	// The MATERIALIZED hint stops SQLite flattening the CTE into the
	// aggregate query, where bm25() is not usable.
	rows, err := ix.db.Query(`
		WITH hits AS MATERIALIZED (
			SELECT d.kind AS kind, d.ref AS ref, d.label AS label,
				snippet(search_fts, 0, '⟦', '⟧', '…', 12) AS snip,
				bm25(search_fts) AS rank
			FROM search_fts JOIN search_doc d ON d.rowid = search_fts.rowid
			WHERE search_fts MATCH ?
		)
		SELECT kind, ref, label, snip, min(rank) AS best FROM hits
		WHERE (? = '' OR kind = ?)
		GROUP BY kind, ref ORDER BY best, kind, ref LIMIT ?`,
		strings.Join(tokens, " "), kind, kind, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []SearchResult
	for rows.Next() {
		var r SearchResult
		if err := rows.Scan(&r.Kind, &r.Ref, &r.Label, &r.Snippet, &r.Rank); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// SearchCount is how many distinct documents a query matches. The
// fleet-wide facet needs a true count, not the number of rows that fit
// under a limit: a facet reading "10" for every repository because ten
// was the page size tells a reader nothing about where to look.
func (ix *Index) SearchCount(query, kind string) (int, error) {
	tokens := strings.Fields(query)
	if len(tokens) == 0 {
		return 0, nil
	}
	if kind != "" && !slices.Contains(SearchKinds, kind) {
		return 0, fmt.Errorf("index: unknown search kind %q", kind)
	}
	for i, tok := range tokens {
		tokens[i] = `"` + strings.ReplaceAll(tok, `"`, `""`) + `"`
	}
	var n int
	err := ix.db.QueryRow(`
		SELECT count(*) FROM (
			SELECT 1 FROM search_fts JOIN search_doc d ON d.rowid = search_fts.rowid
			WHERE search_fts MATCH ? AND (? = '' OR d.kind = ?)
			GROUP BY d.kind, d.ref
		)`, strings.Join(tokens, " "), kind, kind).Scan(&n)
	return n, err
}

// Reset drops all derived state — the reindex path (0001 §5). The next
// Fold rebuilds from a zero cursor.
//
// It drops the TABLES, not their rows: Reset is the remedy the
// schema-version stamp points at, and a version bump means the shape
// changed. A Reset that only deleted rows promised a repair it could
// not perform — the 2026-08-19 bump wiped every store and then failed
// every refold against a table it had emptied but never widened.
// Dropping and reapplying the schema makes "whatever the file held" a
// question with one answer: this code's shape.
func (ix *Index) Reset() error {
	for _, table := range derivedTables {
		if _, err := ix.db.Exec("DROP TABLE IF EXISTS " + table); err != nil {
			return fmt.Errorf("index: reset %s: %w", table, err)
		}
	}
	if err := applySchema(ix.db); err != nil {
		return err
	}
	if _, err := ix.db.Exec(fmt.Sprintf(`PRAGMA user_version = %d`, schemaVersion)); err != nil {
		return fmt.Errorf("index: reset stamp: %w", err)
	}
	ix.invalidateCounts()
	return nil
}

// Tag is a symbolic name that is NOT a branch: fossil gives both a
// "sym-" tag, so the two are told apart by which names also appear as
// branch values. Tip is the check-in the name currently resolves to.
type Tag struct {
	Name string `json:"name"`
	Tip  string `json:"tip"`
	Date string `json:"date"`
}

// Tags lists release tags, most recent first. Branch names are excluded
// because the branch selector already offers them and listing a name in
// both places makes the two controls look like they disagree.
// TagCount is the number of distinct release tags — the Tags query
// without the rows, for the About panel's "+N tags" line.
func (ix *Index) TagCount() (int, error) {
	var n int
	err := ix.db.QueryRow(`
		SELECT count(DISTINCT substr(name, 5)) FROM checkin_tag
		WHERE name LIKE 'sym-%'
		  AND substr(name, 5) NOT IN (
		      SELECT value FROM checkin_tag WHERE name = 'branch')`).Scan(&n)
	return n, err
}

func (ix *Index) Tags(limit int) ([]Tag, error) {
	if limit <= 0 {
		limit = 100
	}
	rows, err := ix.db.Query(`
		SELECT substr(t.name, 5) AS tag, t.checkin, max(t.date) AS d
		FROM checkin_tag t
		WHERE t.name LIKE 'sym-%'
		  AND substr(t.name, 5) NOT IN (
		      SELECT value FROM checkin_tag WHERE name = 'branch')
		GROUP BY tag
		ORDER BY d DESC, tag
		LIMIT ?`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Tag
	for rows.Next() {
		var t Tag
		if err := rows.Scan(&t.Name, &t.Tip, &t.Date); err != nil {
			return nil, err
		}
		out = append(out, t)
	}
	return out, rows.Err()
}

// TagsOf lists the symbolic tags standing on one check-in, excluding
// branch names — the branch already has its own line on the page, and
// showing it twice makes the two look like they disagree.
//
// Fossil tags are a log, not a set: a later artifact can cancel an
// earlier one, so the newest word per name decides. Walking ascending
// and letting the last row win is that rule, and matches how ciVerdicts
// resolves a re-run.
func (ix *Index) TagsOf(hash string) ([]string, error) {
	rows, err := ix.db.Query(`
		SELECT substr(name, 5) AS tag, sign
		FROM checkin_tag
		WHERE checkin = ? AND name LIKE 'sym-%'
		  AND substr(name, 5) NOT IN (
		      SELECT value FROM checkin_tag WHERE name = 'branch')
		ORDER BY date ASC`, hash)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	standing := map[string]bool{}
	for rows.Next() {
		var tag, sign string
		if err := rows.Scan(&tag, &sign); err != nil {
			return nil, err
		}
		standing[tag] = sign != "-"
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	out := make([]string, 0, len(standing))
	for tag, live := range standing {
		if live {
			out = append(out, tag)
		}
	}
	sort.Strings(out)
	return out, nil
}

// TagEvent is one tag artifact's effect on a check-in, as recorded.
type TagEvent struct {
	Date  string `json:"date"`
	Src   string `json:"src"`  // the artifact that carried the T card
	Name  string `json:"name"` // without the sign prefix
	Sign  string `json:"sign"` // + - *
	Value string `json:"value"`
	// Standing reports whether this row is still the last word on its
	// name. A history is mostly superseded rows, and a reader needs to
	// see which one is in force without replaying the log themselves.
	Standing bool `json:"standing"`
}

// TagHistory returns every tag artifact that ever touched a check-in,
// oldest first — the provenance of the check-in rather than its current
// labels. Branch renames, retractions, a comment corrected after the
// fact, hidden and closed set and unset: fossil records all of it, and
// until now quarry folded it away and showed only the outcome.
func (ix *Index) TagHistory(hash string) ([]TagEvent, error) {
	rows, err := ix.db.Query(`SELECT date, src, name, sign, value
		FROM checkin_tag WHERE checkin = ? ORDER BY date ASC, name ASC`, hash)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []TagEvent
	for rows.Next() {
		var e TagEvent
		if err := rows.Scan(&e.Date, &e.Src, &e.Name, &e.Sign, &e.Value); err != nil {
			return nil, err
		}
		out = append(out, e)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	// Ascending order means the LAST row per name is the one in force.
	last := map[string]int{}
	for i, e := range out {
		last[e.Name] = i
	}
	for _, i := range last {
		out[i].Standing = out[i].Sign != "-"
	}
	return out, nil
}

// ForkPoint returns the check-in a branch grew from: walk the branch's
// OLDEST member back along its primary-parent edge and return the first
// ancestor that is not on the branch. Empty when the branch has no such
// ancestor — the root branch of a repository grew from nothing.
//
// This is what fossil calls merge-in:, and it is deliberately NOT the
// same base a landing uses. A landing diffs against the target's
// CURRENT tip ("what would merging change now"); a branch diff is
// against the fork ("what did this branch do"). The two answer
// different questions and drift apart the moment either side moves.
func (ix *Index) ForkPoint(branch string) (string, error) {
	var root string
	// The oldest check-in on the branch. Ties on date break by hash so
	// the answer is stable across rebuilds.
	err := ix.db.QueryRow(`SELECT hash FROM checkin WHERE branch = ?
		ORDER BY date ASC, hash ASC LIMIT 1`, branch).Scan(&root)
	if errors.Is(err, sql.ErrNoRows) {
		return "", nil
	}
	if err != nil {
		return "", err
	}
	var parent string
	err = ix.db.QueryRow(`SELECT p.parent FROM checkin_parent p
		JOIN checkin c ON c.hash = p.parent
		WHERE p.child = ? AND p.ord = 0 AND c.branch <> ?`, root, branch).Scan(&parent)
	if errors.Is(err, sql.ErrNoRows) {
		return "", nil
	}
	return parent, err
}

// RawDB exposes the handle for tests that need to simulate damage the
// fold path cannot produce on purpose — such as an artifact lost from
// the event window.
func (ix *Index) RawDB() *sql.DB { return ix.db }

// DanglingParents lists check-in hashes referenced as a parent that
// this index does not hold, capped at limit.
//
// A dangling edge is NOT by itself proof of damage. Fossil-born
// histories legitimately reference ancestors the store never held —
// shunned artifacts, or a history imported from a point rather than
// from its root; fossil, tcl and sqlite all carry some. Only the
// caller, which can ask the store, can tell those apart from artifacts
// that were LOST: see the ingest pipeline, where an edge whose parent
// the store still holds is the repairable kind.
//
// Returned as hashes rather than a count so that distinction can be
// made at all. It is an index walk, cheap enough to run every round.
func (ix *Index) DanglingParents(limit int) ([]string, error) {
	if limit <= 0 {
		limit = 100
	}
	rows, err := ix.db.Query(`SELECT DISTINCT p.parent FROM checkin_parent p
		WHERE NOT EXISTS (SELECT 1 FROM checkin WHERE hash = p.parent)
		LIMIT ?`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []string
	for rows.Next() {
		var h string
		if err := rows.Scan(&h); err != nil {
			return nil, err
		}
		out = append(out, h)
	}
	return out, rows.Err()
}

// Contributor is one person's activity in a repository, folded at write
// time. Events counts every kind the timeline shows, not just check-ins:
// somebody who only files tickets is a contributor.
type Contributor struct {
	User   string `json:"user"`
	Events int    `json:"events"`
	First  string `json:"first"`
	Last   string `json:"last"`
}

// Contributors returns the repository's people, busiest first. Reading a
// folded table, so this is an index walk rather than the half-second
// GROUP BY it replaced.
// A limit of zero or less means EVERY contributor. It used to mean a
// hundred, silently: people.list asks for all of them and got a
// hundred, so fossil's About panel said 151 contributors above a
// listing that said "of 100". A default that quietly truncates is worse
// than no default, because the caller cannot tell it happened.
func (ix *Index) Contributors(limit int) ([]Contributor, error) {
	if limit <= 0 {
		limit = -1 // SQLite: no limit
	}
	rows, err := ix.db.Query(`SELECT user, events, first, last FROM contributor
		ORDER BY events DESC, user LIMIT ?`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Contributor
	for rows.Next() {
		var c Contributor
		if err := rows.Scan(&c.User, &c.Events, &c.First, &c.Last); err != nil {
			return nil, err
		}
		out = append(out, c)
	}
	return out, rows.Err()
}

// TimelineEntry is one event on the unified timeline: a check-in
// (kind "ci"), a wiki edit ("w"), a ticket change ("t"), a technote
// ("e"), or a forum post ("f"). Ref is the page target: the check-in
// hash, the wiki title, the ticket id, the technote event id, or the
// forum thread root.
type TimelineEntry struct {
	Kind    string   `json:"kind"`
	Hash    string   `json:"hash"`
	Ref     string   `json:"ref"`
	Date    string   `json:"date"`
	User    string   `json:"user"`
	Comment string   `json:"comment"`
	Branch  string   `json:"branch"`
	IsLeaf  bool     `json:"isLeaf"`
	IsMerge bool     `json:"isMerge"`
	Parents []string `json:"parents"`
	// CI is the recorded verdict for this check-in — "ci-pass",
	// "ci-fail", or empty when none was written. It comes from a tag
	// artifact in the store (0001 §6), not from the engine, so it
	// survives cloning and does not require the engine to be reachable.
	CI string `json:"ci,omitempty"`
	// CommentOrig is the check-in's ORIGINAL C card, set only when a
	// later `comment` tag replaced it. Fossil lets an author correct a
	// check-in message after the fact, and the correction is what
	// fossil shows — so Comment carries the correction and this
	// carries what it replaced. Empty is the ordinary case: the
	// comment is the one that was committed.
	CommentOrig string `json:"commentOrig,omitempty"`
	// Hidden and Closed are the standing `hidden` and `closed` tags.
	// Both are facts a reader acts on — a hidden check-in is one
	// fossil omits from its timeline, and a closed leaf is one nobody
	// should build on.
	Hidden bool `json:"hidden,omitempty"`
	Closed bool `json:"closed,omitempty"`
	// Excerpt is the opening prose of a TECHNOTE, and only a technote.
	// The other kinds are records that something happened and a title
	// says all there is to say; a technote is a document somebody wrote
	// to be read, so the row shows that it has a body. Derived here so
	// the timeline and the note listing cannot disagree about what the
	// opening line is.
	Excerpt string `json:"excerpt,omitempty"`
}

// CheckinByPrefix resolves a hash prefix to exactly one check-in and its
// parents. ok is false when the prefix matches zero or several — an
// ambiguous prefix never resolves (the fossil rule).
// foldChanges records which paths a check-in touched — the file itself
// and every ancestor directory, so a directory's "last change" is the
// same exact-path lookup as a file's.
func foldChanges(tx *sql.Tx, hash string, art *artifact.Artifact, changes []string) error {
	if art == nil {
		return nil
	}
	return foldChangePaths(tx, hash, art.Date, changes)
}

func foldChangePaths(tx *sql.Tx, hash, date string, changes []string) error {
	if len(changes) == 0 {
		return nil
	}
	seen := map[string]bool{}
	for _, path := range changes {
		for p := path; p != ""; {
			if seen[p] {
				break // shared ancestors are already recorded
			}
			seen[p] = true
			if _, err := tx.Exec(`INSERT INTO filechange (path, checkin, date)
				VALUES (?, ?, ?) ON CONFLICT (path, checkin) DO NOTHING`,
				p, hash, date); err != nil {
				return err
			}
			i := strings.LastIndexByte(p, '/')
			if i < 0 {
				break
			}
			p = p[:i]
		}
	}
	return nil
}

// LastChange is the newest check-in that touched path (a file or a
// directory), optionally restricted to one branch. The answer comes
// from the filechange fold; a repository indexed before that fold
// existed simply reports nothing until its next rebuild.
// LastChanges dates many paths in ONE scan. A documentation index asks
// about every document at once, and asking LastChange each time is a
// hundred round trips to answer a single page — the difference between
// a listing and a listing that feels slow.
// FileChange is when a path last moved and who moved it.
type FileChange struct {
	Date string `json:"date"`
	User string `json:"user"`
}

func (ix *Index) LastChanges(paths []string) (map[string]FileChange, error) {
	out := make(map[string]FileChange, len(paths))
	// SQLite's parameter ceiling is generous but real; chunk rather
	// than discover it on somebody's monorepo.
	const chunk = 400
	for start := 0; start < len(paths); start += chunk {
		end := min(start+chunk, len(paths))
		part := paths[start:end]
		// max(c.date) with c.user beside it rides SQLite's bare-column
		// rule — the user comes from the row that produced the max, the
		// same guarantee WikiTitles leans on a few hundred lines up.
		q := `SELECT f.path, max(c.date), c.user FROM filechange f
			JOIN checkin c ON c.hash = f.checkin
			WHERE f.path IN (?` + strings.Repeat(",?", len(part)-1) + `)
			GROUP BY f.path`
		args := make([]any, len(part))
		for i, p := range part {
			args[i] = p
		}
		rows, err := ix.db.Query(q, args...)
		if err != nil {
			return nil, err
		}
		for rows.Next() {
			var path string
			var fc FileChange
			if err := rows.Scan(&path, &fc.Date, &fc.User); err != nil {
				rows.Close()
				return nil, err
			}
			out[path] = fc
		}
		rows.Close()
		if err := rows.Err(); err != nil {
			return nil, err
		}
	}
	return out, nil
}

func (ix *Index) LastChange(path, branch string) (TimelineEntry, bool, error) {
	q := `SELECT c.hash, c.date, c.user, c.comment, c.branch
		FROM filechange f JOIN checkin c ON c.hash = f.checkin
		WHERE f.path = ?`
	args := []any{path}
	if branch != "" {
		q += ` AND c.branch = ?`
		args = append(args, branch)
	}
	q += ` ORDER BY f.date DESC LIMIT 1`
	var e TimelineEntry
	err := ix.db.QueryRow(q, args...).
		Scan(&e.Hash, &e.Date, &e.User, &e.Comment, &e.Branch)
	if errors.Is(err, sql.ErrNoRows) {
		return e, false, nil
	}
	return e, err == nil, err
}

// DefaultBranch is the branch a nameless request means: among trunk,
// main and master, whichever exists with the NEWEST tip — a repository
// that renamed its trunk (tk, tcl) keeps an old literal "trunk" in the
// branch table forever, and recency is what distinguishes the alias
// from the artifact. No candidate at all falls back to the newest
// branch, then to "trunk" for an empty repository.
func (ix *Index) DefaultBranch() string {
	var b string
	err := ix.db.QueryRow(`SELECT branch FROM checkin
		WHERE branch IN ('trunk', 'main', 'master')
		ORDER BY date DESC LIMIT 1`).Scan(&b)
	if errors.Is(err, sql.ErrNoRows) {
		err = ix.db.QueryRow(`SELECT branch FROM checkin
			ORDER BY date DESC LIMIT 1`).Scan(&b)
	}
	if err != nil || b == "" {
		return "trunk"
	}
	return b
}

// CheckinContext is the check-ins immediately before and after one, in
// date order — fossil's "Context" panel. A commit read on its own says
// what changed; read among its neighbours it says what was going on,
// which is the question somebody bisecting a regression is actually
// asking.
//
// Two bounded index walks from the check-in's own date rather than one
// window around it: a window would have to guess how many of each side
// to keep, and on a busy day would return twenty of one and none of the
// other.
func (ix *Index) CheckinContext(hash string, span int) ([]TimelineEntry, error) {
	if span <= 0 {
		return nil, nil
	}
	var date string
	if err := ix.db.QueryRow(`SELECT date FROM checkin WHERE hash = ?`, hash).
		Scan(&date); err != nil {
		return nil, err
	}
	const cols = `hash, date, user, comment, branch`
	out := make([]TimelineEntry, 0, span*2+1)
	scan := func(q string, args ...any) ([]TimelineEntry, error) {
		rows, err := ix.db.Query(q, args...)
		if err != nil {
			return nil, err
		}
		defer rows.Close()
		var got []TimelineEntry
		for rows.Next() {
			var e TimelineEntry
			if err := rows.Scan(&e.Hash, &e.Date, &e.User, &e.Comment, &e.Branch); err != nil {
				return nil, err
			}
			e.Kind = "ci"
			got = append(got, e)
		}
		return got, rows.Err()
	}
	// Newer first, then reversed, so the panel reads top-down in time.
	newer, err := scan(`SELECT `+cols+` FROM checkin
		WHERE date > ?1 OR (date = ?1 AND hash > ?2)
		ORDER BY date ASC, hash ASC LIMIT ?3`, date, hash, span)
	if err != nil {
		return nil, err
	}
	for i := len(newer) - 1; i >= 0; i-- {
		out = append(out, newer[i])
	}
	self, err := scan(`SELECT `+cols+` FROM checkin WHERE hash = ?`, hash)
	if err != nil {
		return nil, err
	}
	out = append(out, self...)
	older, err := scan(`SELECT `+cols+` FROM checkin
		WHERE date < ?1 OR (date = ?1 AND hash < ?2)
		ORDER BY date DESC, hash DESC LIMIT ?3`, date, hash, span)
	if err != nil {
		return nil, err
	}
	return append(out, older...), nil
}

func (ix *Index) CheckinByPrefix(prefix string) (TimelineEntry, []string, bool, error) {
	var e TimelineEntry
	rows, err := ix.db.Query(`SELECT hash, date, user, comment, branch FROM checkin
		WHERE hash LIKE ? || '%' LIMIT 2`, prefix)
	if err != nil {
		return e, nil, false, err
	}
	defer rows.Close()
	n := 0
	for rows.Next() {
		n++
		if n > 1 {
			return TimelineEntry{}, nil, false, nil // ambiguous
		}
		if err := rows.Scan(&e.Hash, &e.Date, &e.User, &e.Comment, &e.Branch); err != nil {
			return e, nil, false, err
		}
	}
	if err := rows.Err(); err != nil || n == 0 {
		return e, nil, false, err
	}
	prows, err := ix.db.Query(`SELECT parent FROM checkin_parent WHERE child = ? ORDER BY ord`, e.Hash)
	if err != nil {
		return e, nil, false, err
	}
	defer prows.Close()
	var parents []string
	for prows.Next() {
		var p string
		if err := prows.Scan(&p); err != nil {
			return e, nil, false, err
		}
		parents = append(parents, p)
	}
	return e, parents, true, prows.Err()
}

// WikiTitle is one page in the wiki listing: its name, latest edit, and
// how many times it has been written. Fossil's own index shows that last
// column and it earns its place — a page edited thirty times is the
// project's living documentation, one edited twice is a stub someone
// started, and the listing cannot say so any other way.
type WikiTitle struct {
	Title     string `json:"title"`
	Date      string `json:"date"`
	User      string `json:"user"`
	Revisions int    `json:"revisions"`
}

// WikiTitles lists every wiki page by its most recent edit (fossil
// semantics: latest wins), sorted by title for determinism.
// EventByPrefix resolves an artifact hash prefix to the event that
// carries it — the /info fallback for artifacts that are not check-ins
// or tickets (wiki edits, technotes, forum posts). Ambiguous prefixes
// resolve to nothing, like every other prefix lookup here.
func (ix *Index) EventByPrefix(prefix string) (kind, ref string, ok bool, err error) {
	rows, err := ix.db.Query(`SELECT kind, ref FROM event
		WHERE hash LIKE ? || '%' LIMIT 2`, prefix)
	if err != nil {
		return "", "", false, err
	}
	defer rows.Close()
	n := 0
	for rows.Next() {
		n++
		if n > 1 {
			return "", "", false, nil
		}
		if err := rows.Scan(&kind, &ref); err != nil {
			return "", "", false, err
		}
	}
	return kind, ref, n == 1, rows.Err()
}

func (ix *Index) WikiTitles() ([]WikiTitle, error) {
	// Fossil deletes a page by writing an edit with an EMPTY body, so
	// "live" means the newest edit says something. The bare body column
	// rides SQLite's single-max() rule the same way user already does:
	// both come from the row max() selected. History is untouched — a
	// deleted page's revisions stay readable; this is a listing rule.
	rows, err := ix.db.Query(`SELECT title, max(date || ' ' || hash), user, count(*) FROM wiki_edit
		GROUP BY title HAVING length(body) > 0 ORDER BY title`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []WikiTitle
	for rows.Next() {
		var w WikiTitle
		var dateHash string
		if err := rows.Scan(&w.Title, &dateHash, &w.User, &w.Revisions); err != nil {
			return nil, err
		}
		w.Date, _, _ = strings.Cut(dateHash, " ")
		out = append(out, w)
	}
	return out, rows.Err()
}

// EventKinds counts the timeline by kind — an index-only walk of
// event_by_kind. It is what a feed's filter chips must show once the
// feed can be paged: a number that counted one window would promise
// less than clicking it delivers.
func (ix *Index) EventKinds() (map[string]int, error) {
	rows, err := ix.db.Query(`SELECT kind, count(*) FROM event GROUP BY kind`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := map[string]int{}
	for rows.Next() {
		var kind string
		var n int
		if err := rows.Scan(&kind, &n); err != nil {
			return nil, err
		}
		out[kind] = n
	}
	return out, rows.Err()
}

// WikiStructure is what a wiki cannot tell you about itself by listing
// its pages: which pages nothing links to, and which links go nowhere.
// Both are the questions that decide whether a wiki stays navigable, and
// both are derived from the live pages alone — nothing stored, so a wipe
// and reingest gives the same answer.
type WikiStructure struct {
	// Orphans are live pages no other live page links to. The home page
	// is never an orphan: it is where a reader starts, so nothing needs
	// to point at it.
	Orphans []string
	// Wanted are the page names that are linked to but do not exist,
	// with how many PAGES ask for each — the wiki's own to-do list.
	Wanted []WantedPage
}

// WantedPage is a name the wiki links to but has never written.
type WantedPage struct {
	Title string `json:"title"`
	Links int    `json:"links"`
	// From is the pages asking for it. A wanted page without them is a
	// to-do item with no brief: whoever writes it wants to read the
	// context that reached for the name.
	From []string `json:"from,omitempty"`
}

// WikiLinks reports the structure of the live wiki. It reads the current
// body of every live page once; the wiki index is not a hot page and
// this needs no schema of its own.
func (ix *Index) WikiLinks(home string, refs func(body []byte, fossilWiki bool) []markup.WikiRef) (WikiStructure, error) {
	rows, err := ix.db.Query(`SELECT title, mimetype, body FROM wiki_edit w
		WHERE date || ' ' || hash = (SELECT max(date || ' ' || hash) FROM wiki_edit
			WHERE title = w.title) AND length(body) > 0`)
	if err != nil {
		return WikiStructure{}, err
	}
	defer rows.Close()
	live := map[string]bool{}
	linkedFrom := map[string]int{}
	var order []string
	type page struct {
		title string
		links []markup.WikiRef
	}
	var pages []page
	for rows.Next() {
		var title, mimetype string
		var body []byte
		if err := rows.Scan(&title, &mimetype, &body); err != nil {
			return WikiStructure{}, err
		}
		live[title] = true
		order = append(order, title)
		// An omitted mimetype is text/x-fossil-wiki by fossil's rule —
		// the same dispatch the renderer applies. Reading it any other
		// way makes every page in an imported wiki look unreachable,
		// because its links are all in the syntax we skipped.
		pages = append(pages, page{title, refs(body,
			mimetype == "" || mimetype == "text/x-fossil-wiki")})
	}
	if err := rows.Err(); err != nil {
		return WikiStructure{}, err
	}
	var out WikiStructure
	// Who asks for each missing name, deduplicated: a page linking the
	// same absent name three times still asks once, which is what both
	// the field's own doc and the page's tooltip have always claimed.
	from := map[string][]string{}
	asking := map[string]map[string]bool{}
	for _, p := range pages {
		for _, ref := range p.links {
			if ref.Name == p.title {
				continue // a page linking itself is not an inbound link
			}
			if live[ref.Name] {
				linkedFrom[ref.Name]++
			} else if ref.Explicit {
				// Only [[Name]] asks for a page that is not there.
				// Fossil's [Name] form renders as text when it does not
				// resolve, so counting it would fill this list with
				// ordinary prose in square brackets.
				if !asking[ref.Name][p.title] {
					if asking[ref.Name] == nil {
						asking[ref.Name] = map[string]bool{}
					}
					asking[ref.Name][p.title] = true
					from[ref.Name] = append(from[ref.Name], p.title)
				}
			}
		}
	}
	sort.Strings(order)
	for _, title := range order {
		if linkedFrom[title] == 0 && !strings.EqualFold(title, home) {
			out.Orphans = append(out.Orphans, title)
		}
	}
	for name, askers := range from {
		sort.Strings(askers)
		out.Wanted = append(out.Wanted,
			WantedPage{Title: name, Links: len(askers), From: askers})
	}
	// Most-asked-for first: a name three pages reach for is a bigger
	// hole than one a single page mentions.
	sort.SliceStable(out.Wanted, func(i, j int) bool {
		if out.Wanted[i].Links != out.Wanted[j].Links {
			return out.Wanted[i].Links > out.Wanted[j].Links
		}
		return strings.ToLower(out.Wanted[i].Title) < strings.ToLower(out.Wanted[j].Title)
	})
	return out, nil
}

// WikiEdit is one page's content at one revision.
type WikiEdit struct {
	Hash     string
	Title    string
	Date     string
	User     string
	Mimetype string
	Body     []byte
}

// WikiPage returns the latest edit of one page; ok is false when the
// page does not exist. Date ties break by hash, deterministically.
func (ix *Index) WikiPage(title string) (WikiEdit, bool, error) {
	return ix.WikiRevision(title, "")
}

// WikiRevision returns one edit of a page: the revision whose artifact
// hash starts with rev, or the latest when rev is empty. ok is false
// when nothing matches (an ambiguous prefix picks nothing).
func (ix *Index) WikiRevision(title, rev string) (WikiEdit, bool, error) {
	var w WikiEdit
	var err error
	if rev == "" {
		err = ix.db.QueryRow(`SELECT hash, title, date, user, mimetype, body FROM wiki_edit
			WHERE title = ? ORDER BY date DESC, hash DESC LIMIT 1`, title).
			Scan(&w.Hash, &w.Title, &w.Date, &w.User, &w.Mimetype, &w.Body)
	} else {
		rows, qerr := ix.db.Query(`SELECT hash, title, date, user, mimetype, body FROM wiki_edit
			WHERE title = ? AND hash LIKE ? || '%' LIMIT 2`, title, rev)
		if qerr != nil {
			return w, false, qerr
		}
		defer rows.Close()
		n := 0
		for rows.Next() {
			n++
			if n > 1 {
				return WikiEdit{}, false, nil // ambiguous
			}
			if err := rows.Scan(&w.Hash, &w.Title, &w.Date, &w.User, &w.Mimetype, &w.Body); err != nil {
				return w, false, err
			}
		}
		if n == 0 {
			return w, false, rows.Err()
		}
		return w, true, rows.Err()
	}
	if err == sql.ErrNoRows {
		return w, false, nil
	}
	return w, err == nil, err
}

// WikiRev is one entry in a page's edit history.
type WikiRev struct {
	Hash string `json:"hash"`
	Date string `json:"date"`
	User string `json:"user"`
}

// WikiHistory lists every recorded edit of one page, newest first.
func (ix *Index) WikiHistory(title string) ([]WikiRev, error) {
	rows, err := ix.db.Query(`SELECT hash, date, user FROM wiki_edit
		WHERE title = ? ORDER BY date DESC, hash DESC`, title)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []WikiRev
	for rows.Next() {
		var r WikiRev
		if err := rows.Scan(&r.Hash, &r.Date, &r.User); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// Technote is one technote at its latest edit.
type Technote struct {
	Hash     string `json:"hash"`
	Event    string `json:"event"`
	Date     string `json:"date"`
	User     string `json:"user"`
	Mimetype string `json:"mimetype"`
	Comment  string `json:"comment"` // C card: the timeline/listing title
	Body     string `json:"body"`
	// Excerpt and Revisions are filled by the LISTING only, where Body
	// is left empty in exchange: a listing wants one line per note and
	// how many times it has been rewritten, and shipping twenty-six
	// full documents to render twenty-six summaries is the wrong
	// trade. TechnoteByEvent fills Body and leaves these zero.
	Excerpt   string `json:"excerpt,omitempty"`
	Revisions int    `json:"revisions,omitempty"`
}

// Technotes lists each technote at its latest edit, newest event first,
// with an opening line and an edit count instead of its body.
func (ix *Index) Technotes() ([]Technote, error) {
	rows, err := ix.db.Query(`SELECT t.hash, t.event, t.date, t.user, t.mimetype, t.comment,
			substr(t.body, 1, 400),
			(SELECT count(*) FROM technote t3 WHERE t3.event = t.event)
		FROM technote t
		WHERE t.edited = (SELECT max(t2.edited) FROM technote t2 WHERE t2.event = t.event)
		GROUP BY t.event
		ORDER BY t.date DESC, t.event`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Technote
	for rows.Next() {
		var n Technote
		var body string
		if err := rows.Scan(&n.Hash, &n.Event, &n.Date, &n.User, &n.Mimetype, &n.Comment,
			&body, &n.Revisions); err != nil {
			return nil, err
		}
		n.Excerpt = Excerpt(body)
		out = append(out, n)
	}
	return out, rows.Err()
}

// TechnoteByEvent returns the latest edit of one technote by event-id
// prefix; ok is false on zero or ambiguous matches.
func (ix *Index) TechnoteByEvent(prefix string) (Technote, bool, error) {
	rows, err := ix.db.Query(`SELECT hash, event, date, user, mimetype, comment, body FROM technote t
		WHERE event LIKE ? || '%'
		AND edited = (SELECT max(t2.edited) FROM technote t2 WHERE t2.event = t.event)
		GROUP BY event LIMIT 2`, prefix)
	if err != nil {
		return Technote{}, false, err
	}
	defer rows.Close()
	var found []Technote
	for rows.Next() {
		var n Technote
		if err := rows.Scan(&n.Hash, &n.Event, &n.Date, &n.User, &n.Mimetype, &n.Comment, &n.Body); err != nil {
			return Technote{}, false, err
		}
		found = append(found, n)
	}
	if len(found) != 1 {
		return Technote{}, false, rows.Err()
	}
	return found[0], true, rows.Err()
}

// Excerpt is the opening prose of a document body, for a listing row.
// Deliberately crude: it takes the first paragraph that reads like
// prose and strips the markup punctuation off it, rather than rendering
// the source to HTML and then throwing the HTML away. A row wants one
// line, not a document.
//
// Headings are skipped because a note whose body opens with its own
// title would otherwise show that title twice — once as the row title
// and once as its summary.
func Excerpt(body string) string {
	const width = 160
	for _, para := range strings.Split(strings.ReplaceAll(body, "\r\n", "\n"), "\n\n") {
		line := strings.TrimSpace(para)
		if line == "" || strings.HasPrefix(line, "#") || strings.HasPrefix(line, "<h") {
			continue
		}
		line = excerptNoise.ReplaceAllString(line, " ")
		line = strings.Join(strings.Fields(line), " ")
		if line == "" {
			continue
		}
		if len(line) <= width {
			return line
		}
		cut := line[:width]
		if i := strings.LastIndexByte(cut, ' '); i > width/2 {
			cut = cut[:i]
		}
		return strings.TrimRight(cut, " ,;:-") + "\u2026"
	}
	return ""
}

// excerptNoise is the markup that carries no meaning once the prose is
// one line: tags, fossil-wiki and markdown link brackets, emphasis runs
// and the leading punctuation of a list or quote.
var excerptNoise = regexp.MustCompile(`<[^>]*>|\[|\]\([^)]*\)|\]|^[>*+-]\s+|` + "`" + `+|\*+|_{2,}`)

// TechnoteRev is one edit of a technote.
//
// Both times are carried because they answer different questions and a
// technote is the one artifact where they routinely disagree: Edited is
// the D card, when this version was written; Date is the E card, the
// moment the note is FILED under, which the author chooses and an edit
// may move. libfossil has a note whose eight edits span nineteen hours
// while claiming three different filing times.
type TechnoteRev struct {
	Hash    string `json:"hash"`
	Date    string `json:"date"`
	Edited  string `json:"edited"`
	User    string `json:"user"`
	Comment string `json:"comment"`
}

// TechnoteHistory lists every recorded edit of one technote, newest
// first. The event id must be exact — callers resolve a prefix with
// TechnoteByEvent, so an ambiguous prefix cannot silently merge the
// histories of two notes.
func (ix *Index) TechnoteHistory(event string) ([]TechnoteRev, error) {
	rows, err := ix.db.Query(`SELECT hash, date, edited, user, comment FROM technote
		WHERE event = ? ORDER BY edited DESC, hash DESC`, event)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []TechnoteRev
	for rows.Next() {
		var r TechnoteRev
		if err := rows.Scan(&r.Hash, &r.Date, &r.Edited, &r.User, &r.Comment); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// TechnoteRevision returns ONE edit of a technote: the version whose
// artifact hash starts with rev, or the latest when rev is empty. Same
// shape as WikiRevision, including its rule that an ambiguous prefix
// resolves to nothing.
func (ix *Index) TechnoteRevision(prefix, rev string) (Technote, bool, error) {
	if rev == "" {
		return ix.TechnoteByEvent(prefix)
	}
	n, ok, err := ix.TechnoteByEvent(prefix)
	if err != nil || !ok {
		return Technote{}, false, err
	}
	rows, err := ix.db.Query(`SELECT hash, event, date, user, mimetype, comment, body
		FROM technote WHERE event = ? AND hash LIKE ? || '%' LIMIT 2`, n.Event, rev)
	if err != nil {
		return Technote{}, false, err
	}
	defer rows.Close()
	var found []Technote
	for rows.Next() {
		var v Technote
		if err := rows.Scan(&v.Hash, &v.Event, &v.Date, &v.User, &v.Mimetype, &v.Comment, &v.Body); err != nil {
			return Technote{}, false, err
		}
		found = append(found, v)
	}
	if len(found) != 1 {
		return Technote{}, false, rows.Err()
	}
	return found[0], true, rows.Err()
}

// ForumThread is a thread summary for the forum listing.
type ForumThread struct {
	Root    string `json:"root"`
	Title   string `json:"title"`
	Posts   int    `json:"posts"`
	Created string `json:"created"` // the opener's date — hot ranking's age
	Updated string `json:"updated"`
	User    string `json:"user"` // opener
	// Excerpt is the opener's first prose line; HasPoll marks a poll
	// block in the opener. Both derived from the body already held.
	Excerpt string `json:"excerpt,omitempty"`
	HasPoll bool   `json:"hasPoll,omitempty"`
	// Participants are the distinct posters in order of first
	// appearance (capped); Dates is every post's date, oldest first —
	// the API turns them into the activity sparkline and drops them.
	Participants []string `json:"participants,omitempty"`
	Dates        []string `json:"dates,omitempty"`
}

// ThreadExcerpt reduces a post body to one plain line: the first line
// that reads as prose — fences, headings and quote markers stripped,
// clipped on a rune boundary.
func ThreadExcerpt(body string) string {
	inFence := false
	for _, line := range strings.Split(body, "\n") {
		l := strings.TrimSpace(line)
		if strings.HasPrefix(l, "```") {
			inFence = !inFence
			continue
		}
		if inFence || l == "" {
			continue
		}
		l = strings.TrimLeft(l, "#> ")
		l = strings.Join(strings.Fields(l), " ")
		if l == "" {
			continue
		}
		runes := []rune(l)
		if len(runes) > 140 {
			return string(runes[:140]) + "…"
		}
		return l
	}
	return ""
}

// ForumThreads lists threads, most-recently-active first. The title is
// the opener's H card; a titleless thread falls back to its first line.
func (ix *Index) ForumThreads() ([]ForumThread, error) {
	rows, err := ix.db.Query(`SELECT p.thread,
			COALESCE((SELECT CASE WHEN o.title != '' THEN o.title
				ELSE substr(o.body, 1, 80) END FROM forum_post o WHERE o.hash = p.thread), ''),
			count(*), min(p.date), max(p.date),
			COALESCE((SELECT o.user FROM forum_post o WHERE o.hash = p.thread), ''),
			COALESCE((SELECT o.body FROM forum_post o WHERE o.hash = p.thread), '')
		FROM forum_post p
		WHERE ` + notALanding("p.thread") + `
		GROUP BY p.thread
		ORDER BY max(p.date) DESC, p.thread`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []ForumThread
	byRoot := map[string]int{}
	for rows.Next() {
		var t ForumThread
		var body string
		if err := rows.Scan(&t.Root, &t.Title, &t.Posts, &t.Created, &t.Updated, &t.User, &body); err != nil {
			return nil, err
		}
		t.Excerpt = ThreadExcerpt(body)
		t.HasPoll = strings.Contains(body, "```poll\n")
		byRoot[t.Root] = len(out)
		out = append(out, t)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	// Second pass: every post's author and date, oldest first, folded
	// into participant order and the activity series.
	prows, err := ix.db.Query(`SELECT thread, user, date FROM forum_post ORDER BY thread, date, hash`)
	if err != nil {
		return nil, err
	}
	defer prows.Close()
	const maxParticipants = 4
	for prows.Next() {
		var thread, user, date string
		if err := prows.Scan(&thread, &user, &date); err != nil {
			return nil, err
		}
		i, ok := byRoot[thread]
		if !ok {
			continue
		}
		t := &out[i]
		t.Dates = append(t.Dates, date)
		seen := false
		for _, u := range t.Participants {
			if u == user {
				seen = true
				break
			}
		}
		if !seen && len(t.Participants) < maxParticipants {
			t.Participants = append(t.Participants, user)
		}
	}
	return out, prows.Err()
}

// ForumPost is one post within a thread.
type ForumPost struct {
	Hash     string `json:"hash"`
	Parent   string `json:"parent"`
	Date     string `json:"date"`
	User     string `json:"user"`
	Mimetype string `json:"mimetype"`
	Body     string `json:"body"`
}

// ForumThreadPosts returns every post of a thread resolved by root-hash
// prefix, oldest first (reading order); ok is false on no match.
func (ix *Index) ForumThreadPosts(prefix string) (title string, posts []ForumPost, ok bool, err error) {
	var root string
	err = ix.db.QueryRow(`SELECT DISTINCT thread FROM forum_post
		WHERE thread LIKE ? || '%' LIMIT 1`, prefix).Scan(&root)
	if err == sql.ErrNoRows {
		return "", nil, false, nil
	}
	if err != nil {
		return "", nil, false, err
	}
	ix.db.QueryRow(`SELECT CASE WHEN title != '' THEN title ELSE substr(body, 1, 80) END
		FROM forum_post WHERE hash = ?`, root).Scan(&title)
	rows, err := ix.db.Query(`SELECT hash, parent, date, user, mimetype, body FROM forum_post
		WHERE thread = ? ORDER BY date, hash`, root)
	if err != nil {
		return "", nil, false, err
	}
	defer rows.Close()
	for rows.Next() {
		var p ForumPost
		if err := rows.Scan(&p.Hash, &p.Parent, &p.Date, &p.User, &p.Mimetype, &p.Body); err != nil {
			return "", nil, false, err
		}
		posts = append(posts, p)
	}
	return title, posts, len(posts) > 0, rows.Err()
}

// hexRefPat matches a plausible artifact reference: 6–64 hex chars on
// word boundaries. Plain hex-spelling English ("decade", "efface") is
// cut by requiring at least one digit; what survives is confirmed only
// at render, where an anchor shows nothing unless its target resolves.
var hexRefPat = regexp.MustCompile(`\b[0-9a-f]{6,64}\b`)

func hexRefs(body string) []string {
	var out []string
	seen := map[string]bool{}
	for _, m := range hexRefPat.FindAllString(body, 64) {
		if !strings.ContainsAny(m, "0123456789") || seen[m] {
			continue
		}
		seen[m] = true
		out = append(out, m)
	}
	return out
}

// ThreadsMentioning lists forum threads whose posts reference the given
// full hash by any prefix — the "Discussed in" backlink. The query is
// the honesty gate: a stored ref only surfaces on the page whose hash
// it actually prefixes.
func (ix *Index) ThreadsMentioning(hash string) ([]ForumThread, error) {
	rows, err := ix.db.Query(`SELECT DISTINCT a.thread,
			COALESCE((SELECT CASE WHEN o.title != '' THEN o.title
				ELSE substr(o.body, 1, 80) END FROM forum_post o WHERE o.hash = a.thread), ''),
			COALESCE((SELECT count(*) FROM forum_post p WHERE p.thread = a.thread), 0),
			COALESCE((SELECT max(p.date) FROM forum_post p WHERE p.thread = a.thread), '')
		FROM forum_anchor a
		WHERE ? LIKE a.target || '%'
		ORDER BY 4 DESC`, hash)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []ForumThread
	for rows.Next() {
		var t ForumThread
		if err := rows.Scan(&t.Root, &t.Title, &t.Posts, &t.Updated); err != nil {
			return nil, err
		}
		out = append(out, t)
	}
	return out, rows.Err()
}

// HasContributor reports whether a login appears in this repository's
// contributor fold — the gate for rendering an @mention as that
// person's link rather than plain text.
func (ix *Index) HasContributor(user string) bool {
	var one int
	err := ix.db.QueryRow(`SELECT 1 FROM contributor WHERE user = ?`, user).Scan(&one)
	return err == nil
}

// ForumPostThread resolves a post-hash prefix to the full post hash and
// its thread root. Votes are keyed on full hashes; pages carry prefixes.
func (ix *Index) ForumPostThread(prefix string) (post, thread string, ok bool, err error) {
	err = ix.db.QueryRow(`SELECT hash, thread FROM forum_post
		WHERE hash LIKE ? || '%' LIMIT 1`, prefix).Scan(&post, &thread)
	if err == sql.ErrNoRows {
		return "", "", false, nil
	}
	if err != nil {
		return "", "", false, err
	}
	return post, thread, true, nil
}

// TicketChange is one recorded change with the fields it set.
type TicketChange struct {
	Hash   string           `json:"hash"`
	Date   string           `json:"date"`
	User   string           `json:"user"`
	Fields []TicketFieldSet `json:"fields"`
}

// TicketFieldSet is one field assignment within a change.
type TicketFieldSet struct {
	Name  string `json:"name"`
	Value string `json:"value"`
}

// TicketState is a ticket's folded state: every field's latest value
// (fossil semantics), plus the change history in order.
type TicketState struct {
	ID      string `json:"id"`
	Created string `json:"created"`
	Updated string `json:"updated"`
	// Fields is the folded scalar state — status, priority, resolution
	// and the rest. Per-change plumbing is NOT here: see Comments.
	Fields []TicketFieldSet `json:"fields"`
	// Description is the ticket's body (fossil's `comment` field) and
	// DescriptionMimetype is how to render it (`cmimetype`).
	Description         string `json:"description"`
	DescriptionMimetype string `json:"descriptionMimetype"`
	// Comments is the discussion, oldest first. Fossil keeps each change's
	// `icomment` on the CHANGE and never folds it into ticket state; a
	// fold would leave one comment standing where a conversation was.
	Comments []TicketComment `json:"comments"`
	Changes  []TicketChange  `json:"changes"`
}

// TicketComment is one entry in a ticket's discussion.
type TicketComment struct {
	Hash string `json:"hash"`
	Date string `json:"date"`
	// Login is the authenticated account; Username is what the commenter
	// typed. Fossil shows both when they differ ("claiming to be"),
	// because on an open tracker they need not agree.
	Login    string `json:"login"`
	Username string `json:"username"`
	Mimetype string `json:"mimetype"`
	Body     string `json:"body"`
}

// perChange names the ticket fields that describe ONE change rather than
// the ticket's state. Folding them produces a field table full of
// plumbing and, worse, collapses a whole discussion into whichever
// comment happened to be last.
// cmimetype is deliberately absent: it describes the FOLDED comment, so
// it is ticket state and folds with it.
var perChange = map[string]bool{
	"icomment": true, "mimetype": true, "login": true, "username": true,
}

// TicketSummary is the list-surface fold: current title and status
// without materializing change histories.
type TicketSummary struct {
	ID         string `json:"id"`
	Created    string `json:"created"`
	Updated    string `json:"updated"`
	Title      string `json:"title"`
	Status     string `json:"status"`
	Type       string `json:"type,omitempty"`
	Severity   string `json:"severity,omitempty"`
	Priority   string `json:"priority,omitempty"`
	Subsystem  string `json:"subsystem,omitempty"`
	Resolution string `json:"resolution,omitempty"`
	Author     string `json:"author,omitempty"`   // who opened it
	LastUser   string `json:"lastUser,omitempty"` // who touched it last
	Comments   int    `json:"comments"`           // remarks appended after the description
}

// TicketSummaries reads the folded ticket table — newest-updated first,
// id as tiebreak so rebuilds page identically. before is a composite
// "updated~ticket" cursor (empty = first page); more reports whether an
// older page follows.
// ClosedStatuses are the status words a tracker uses to mean "done".
// Fossil ticket schemas define their own vocabulary, so this cannot be
// exhaustive — it recognizes the conventional words and treats anything
// else as open, which errs toward SHOWING a ticket rather than hiding
// one. A hidden ticket is invisible; a wrongly-shown one is merely
// noise.
// IsClosedStatus reports whether a status word means "done", by the same
// rule the listings use. Exported because the event vocabulary has to tell a
// ticket that was CLOSED from one that was merely edited, and two readings of
// "is this finished" would eventually disagree.
func IsClosedStatus(status string) bool {
	for _, w := range ClosedStatuses {
		if strings.EqualFold(strings.TrimSpace(status), w) {
			return true
		}
	}
	return false
}

var ClosedStatuses = []string{"closed", "fixed", "verified", "resolved",
	"deferred", "rejected", "duplicate", "deleted", "wontfix"}

// closedFilter is the SQL for "not one of the done words", case-folded
// because a repository holding both "Closed" and "closed" is real —
// quarry's own does.
func closedFilter() string {
	var q []string
	for _, s := range ClosedStatuses {
		q = append(q, "'"+s+"'")
	}
	return "lower(status) NOT IN (" + strings.Join(q, ",") + ")"
}

// TicketOrders is the ORDER BY whitelist, and the ONLY source of the
// text interpolated into a listing's ORDER BY. A reader clicking a
// column header names a key in this map; the SQL it selects is written
// here, so no column name ever travels from a URL into a query.
//
// Every order but the default breaks the updated~id cursor, since that
// cursor encodes the default's position. Callers take one bounded page
// for those and say so, which is what the priority view already does.
var TicketOrders = map[string]string{
	"":          "updated DESC, id",
	"updated":   "updated DESC, id",
	"oldest":    "updated ASC, id",
	"created":   "created DESC, id",
	"id":        "id",
	"title":     "lower(title), updated DESC",
	"status":    "lower(status), updated DESC",
	"type":      "lower(type), updated DESC",
	"subsystem": "lower(subsystem), updated DESC",
	"author":    "lower(author), updated DESC",
}

// TicketFilter narrows a listing to one report. The zero value is the
// tracker's working surface — every open ticket — which is what the
// page opens on. Each field is an exact match on the folded value,
// case-folded because a tracker holding both "Open" and "open" is real:
// quarry's own does.
type TicketFilter struct {
	// IncludePrivate lifts the privacy filter. It is the ONE field here
	// that is not a narrowing: every other one makes a listing smaller,
	// this one makes it bigger, so it must never be settable from a
	// request. Callers derive it from the viewer's capability e, which
	// arrives on the context and not in an input struct.
	IncludePrivate bool
	State          string // "" or open | closed | all
	Type           string
	Severity       string
	Priority       string
	Subsystem      string
	Status         string
	Author         string
	// Title matches anywhere in a ticket's title, case-folded. It is
	// the free words of a query box: deliberately NOT full text over
	// descriptions and comments, because this narrows a listing rather
	// than searching the repository, and the forge already has search.
	Title string
	// Offset skips rows before the page, for the ONE case the before
	// cursor cannot serve: a listing sorted by something other than the
	// default, where the cursor's updated~id position means nothing.
	// Offset paging drifts if the tracker changes under a reader
	// mid-page; a keyset cursor per sortable column would not, and is
	// not worth six cursors on a table of a few thousand rows.
	Offset int
	// Order names a key of TicketOrders. An unknown key sorts by the
	// default rather than erroring: a listing is a page, and a page
	// that 500s because a link went stale serves nobody.
	Order string
}

// filterable is every column a report may narrow on. A fixed list, so
// the column names interpolated below are literals from this file and
// never anything a caller sent.
func (f TicketFilter) filterable() []struct{ col, val string } {
	return []struct{ col, val string }{
		{"type", f.Type}, {"severity", f.Severity}, {"priority", f.Priority},
		{"subsystem", f.Subsystem}, {"status", f.Status}, {"author", f.Author},
	}
}

// offsetClause is empty for the first page, so the common query is the
// query it always was.
func offsetClause(n int) string {
	if n > 0 {
		return " OFFSET ?"
	}
	return ""
}

// likeEscape neutralises LIKE's wildcards in a reader's own words, so a
// search for "50%" finds the ticket about 50% and not every ticket.
func likeEscape(s string) string {
	return strings.NewReplacer(`\`, `\\`, "%", `\%`, "_", `\_`).Replace(s)
}

// fields appends the clauses that the listing and its counts SHARE, so a
// filtered header can never disagree with the rows under it.
func (f TicketFilter) fields(where *string, args *[]any) {
	for _, m := range f.filterable() {
		if m.val != "" {
			*where += " AND lower(" + m.col + ") = ?"
			*args = append(*args, strings.ToLower(m.val))
		}
	}
	if t := strings.TrimSpace(f.Title); t != "" {
		*where += ` AND lower(title) LIKE '%' || ? || '%' ESCAPE '\'`
		*args = append(*args, likeEscape(strings.ToLower(t)))
	}
}

// TicketCounts is the header's "71 open · 132 closed", counted through
// the same filter as the listing: with a report applied, "open" means
// open IN THIS REPORT. A header counting the whole tracker beside rows
// showing a slice of it is two numbers that cannot both be read.
func (ix *Index) TicketCounts(f TicketFilter) (open, closed int, err error) {
	where := privacyWhere(f)
	var args []any
	f.fields(&where, &args)
	err = ix.db.QueryRow(`SELECT
		count(*) FILTER (WHERE `+closedFilter()+`),
		count(*) FILTER (WHERE NOT (`+closedFilter()+`))
		FROM ticket WHERE `+where, args...).Scan(&open, &closed)
	return
}

// privacyWhere is the privacy clause every ticket read begins with.
// One function, so a new read path cannot forget it: writing the clause
// out by hand in a fourth place is how one of them eventually ships
// without it, and the failure is silent.
func privacyWhere(f TicketFilter) string {
	if f.IncludePrivate {
		return "1 = 1"
	}
	return "private = 0"
}

// TicketFacet is one value a field holds, with the open work under it.
type TicketFacet struct {
	Value string `json:"value"`
	Open  int    `json:"open"`
}

// TicketFacets lists the values the tracker actually HOLDS for the two
// fields no vocabulary can predict: subsystem is whatever this project
// divided itself into, author is whoever showed up. The configured
// fields come from .quarry/tickets.json instead, whose list order is
// sort order — offering a word the config ordered is the config's job.
//
// Counted OPEN and ordered by it, because a browse list answers "where
// is the work" before it answers "what exists": a subsystem with 20
// live tickets and one with a single ticket closed last year are not
// equally worth a reader's next click. Counts are over the whole
// tracker rather than the current report, so a rail entry means the
// same thing wherever it is read from — with the caveat that clicking
// one while another axis is set lands on the intersection, which is
// smaller than the number promised.
func (ix *Index) TicketFacets(includePrivate bool) (subsystems, authors []TicketFacet, err error) {
	for _, q := range []struct {
		col string
		out *[]TicketFacet
	}{{"subsystem", &subsystems}, {"author", &authors}} {
		rows, err := ix.db.Query(`SELECT ` + q.col + `,
			count(*) FILTER (WHERE ` + closedFilter() + `) FROM ticket
			WHERE ` + privacyWhere(TicketFilter{IncludePrivate: includePrivate}) + ` AND ` + q.col + ` <> ''
			GROUP BY ` + q.col + `
			ORDER BY 2 DESC, lower(` + q.col + `)`)
		if err != nil {
			return nil, nil, err
		}
		for rows.Next() {
			var f TicketFacet
			if err := rows.Scan(&f.Value, &f.Open); err != nil {
				rows.Close()
				return nil, nil, err
			}
			*q.out = append(*q.out, f)
		}
		rows.Close()
		if err := rows.Err(); err != nil {
			return nil, nil, err
		}
	}
	return subsystems, authors, nil
}

// TicketSummaries lists tickets newest-updated first, narrowed by f.
// f.State selects which lives are in scope: "" and "open" are the
// working surface, "closed" the finished work, "all" both. Fossil's own
// report pages offer the same three, and a reader who wants to browse
// what got fixed should not have to read past the open work to find it.
func (ix *Index) TicketSummaries(limit int, before string, f TicketFilter) ([]TicketSummary, bool, error) {
	if limit <= 0 {
		limit = 50 // the form codec defaults this; direct calls get the same
	}
	// A ticket its own tracker marked private is listed only for a
	// reader holding fossil's capability e — the same rule as
	// private_contact, and the exception quarry could not grant until
	// the capability model landed.
	where := privacyWhere(f)
	var args []any
	if before != "" {
		bu, bt, _ := strings.Cut(before, "~")
		where += " AND (updated < ? OR (updated = ? AND id > ?))"
		args = append(args, bu, bu, bt)
	}
	// Open-only by default: a tracker's list is a working surface, and
	// sqlite's is 50% closed, tk's 44%. Showing everything makes the
	// live work the minority of the page.
	switch f.State {
	case "all":
	case "closed":
		where += " AND NOT (" + closedFilter() + ")"
	default:
		where += " AND " + closedFilter()
	}
	f.fields(&where, &args)
	args = append(args, limit+1)
	if f.Offset > 0 {
		args = append(args, f.Offset)
	}
	order, ok := TicketOrders[f.Order]
	if !ok {
		order = TicketOrders[""]
	}
	// An index walk over the folded summaries, stopping at the limit.
	// The cursor is a WHERE, not a HAVING, so paging actually skips work
	// instead of grouping the whole table and discarding most of it.
	rows, err := ix.db.Query(`
		SELECT id, created, updated, title, status,
		       type, severity, priority, subsystem, resolution,
		       author, last_user, comments FROM ticket
		WHERE `+where+`
		ORDER BY `+order+`
		LIMIT ?`+offsetClause(f.Offset), args...)
	if err != nil {
		return nil, false, err
	}
	defer rows.Close()
	var out []TicketSummary
	for rows.Next() {
		var t TicketSummary
		if err := rows.Scan(&t.ID, &t.Created, &t.Updated, &t.Title, &t.Status,
			&t.Type, &t.Severity, &t.Priority, &t.Subsystem, &t.Resolution,
			&t.Author, &t.LastUser, &t.Comments); err != nil {
			return nil, false, err
		}
		out = append(out, t)
	}
	more := len(out) > limit
	if more {
		out = out[:limit]
	}
	return out, more, rows.Err()
}

// ticketStates folds change histories into current state for tickets
// matching the WHERE clause. Change order is (date, hash) so the fold is
// deterministic across rebuilds.
func (ix *Index) ticketStates(where string, args ...any) ([]TicketState, error) {
	rows, err := ix.db.Query(`
		SELECT c.ticket, c.hash, c.date, c.user, f.name, f.value
		FROM ticket_change c JOIN ticket_field f ON f.hash = c.hash
		`+where+`
		ORDER BY c.ticket, c.date, c.hash, f.name`, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []TicketState
	var cur *TicketState
	for rows.Next() {
		var ticket, hash, date, user, name, value string
		if err := rows.Scan(&ticket, &hash, &date, &user, &name, &value); err != nil {
			return nil, err
		}
		if cur == nil || cur.ID != ticket {
			out = append(out, TicketState{ID: ticket, Created: date})
			cur = &out[len(out)-1]
		}
		cur.Updated = date
		if n := len(cur.Changes); n == 0 || cur.Changes[n-1].Hash != hash {
			cur.Changes = append(cur.Changes, TicketChange{Hash: hash, Date: date, User: user})
		}
		ch := &cur.Changes[len(cur.Changes)-1]
		ch.Fields = append(ch.Fields, TicketFieldSet{Name: name, Value: value})

		if perChange[name] {
			continue // gathered per change below, never folded
		}
		// A leading + is fossil's APPEND form: "J +comment more" adds to
		// the existing comment rather than replacing it, which is how a
		// description grows over a ticket's life. Treating it as a
		// distinct field named "+comment" both loses the append and puts
		// a nonsense row in the field table.
		target, appending := strings.CutPrefix(name, "+")
		set := false
		for i := range cur.Fields {
			if cur.Fields[i].Name != target {
				continue
			}
			if appending {
				cur.Fields[i].Value += value
			} else {
				cur.Fields[i].Value = value
			}
			set = true
		}
		if !set {
			cur.Fields = append(cur.Fields, TicketFieldSet{Name: target, Value: value})
		}
	}
	// Second pass: the discussion, and the description lifted out of the
	// field table so a page can render it as prose rather than a cell.
	for i := range out {
		t := &out[i]
		for _, ch := range t.Changes {
			var body, mimetype, login, username string
			for _, f := range ch.Fields {
				switch f.Name {
				case "icomment":
					body = f.Value
				case "mimetype":
					mimetype = f.Value
				case "login":
					login = f.Value
				case "username":
					username = f.Value
				}
			}
			if body == "" {
				continue // fossil lists only changes that said something
			}
			if login == "" {
				login = ch.User
			}
			t.Comments = append(t.Comments, TicketComment{
				Hash: ch.Hash, Date: ch.Date, Login: login,
				Username: username, Mimetype: mimetype, Body: body,
			})
		}
		kept := t.Fields[:0]
		for _, f := range t.Fields {
			switch f.Name {
			case "comment":
				t.Description = f.Value
			case "cmimetype":
				t.DescriptionMimetype = f.Value
			default:
				kept = append(kept, f)
			}
		}
		t.Fields = kept
	}
	return out, rows.Err()
}

// TicketPrivate reports whether a ticket's tracker marked it private.
// An id the folded table does not know answers PRIVATE: this gates a
// read, and for a value whose whole purpose is to withhold, the failure
// direction must withhold too.
func (ix *Index) TicketPrivate(id string) (bool, error) {
	var private int
	err := ix.db.QueryRow(`SELECT private FROM ticket WHERE id = ?`, id).Scan(&private)
	if err == sql.ErrNoRows {
		return true, nil
	}
	return private != 0, err
}

// TicketByPrefix folds one ticket resolved by id prefix; ok is false on
// zero or multiple matches — including a match its tracker marked
// private, which reports NOT FOUND so an anonymous reader cannot even
// confirm it exists (the rule repoFor applies to private repositories,
// for the same reason: a refusal confirms the thing exists).
//
// Token-gated writes resolve with TicketByPrefixAll: erring toward
// private is only safe if un-privating stays possible, and a private
// ticket nobody can address is one nobody can ever make public again.
func (ix *Index) TicketByPrefix(prefix string) (TicketState, bool, error) {
	return ix.TicketByPrefixFor(prefix, false)
}

// TicketByPrefixFor resolves a ticket for a reader who may or may not
// hold fossil's capability e. Without it a private ticket does not
// resolve at all — not "forbidden", which would confirm it exists.
func (ix *Index) TicketByPrefixFor(prefix string, includePrivate bool) (TicketState, bool, error) {
	t, ok, err := ix.TicketByPrefixAll(prefix)
	if err != nil || !ok {
		return TicketState{}, false, err
	}
	if includePrivate {
		return t, true, nil
	}
	for _, f := range t.Fields {
		if f.Name == "is_private" && boolField(f.Value) == 1 {
			return TicketState{}, false, nil
		}
	}
	return t, true, nil
}

// TicketByPrefixAll is TicketByPrefix without the private refusal, for
// the token-gated write path only. Never serve its result to a reader.
func (ix *Index) TicketByPrefixAll(prefix string) (TicketState, bool, error) {
	all, err := ix.ticketStates(`WHERE c.ticket IN (
		SELECT DISTINCT ticket FROM ticket_change WHERE ticket LIKE ? || '%')`, prefix)
	if err != nil {
		return TicketState{}, false, err
	}
	var found *TicketState
	for i := range all {
		if strings.HasPrefix(all[i].ID, prefix) {
			if found != nil {
				return TicketState{}, false, nil // ambiguous
			}
			found = &all[i]
		}
	}
	if found == nil {
		return TicketState{}, false, nil
	}
	return *found, true, nil
}

// Timeline lists events newest first; ties break by hash so the
// listing is deterministic across rebuilds (the disposability
// property). kind filters to "ci", "w", or "t" ("" or "all" means
// every kind); a non-empty branch restricts to that branch's check-ins
// (fossil's rule). before is a cursor from a previous page's Next —
// "date~hash" — with a bare check-in hash prefix accepted for
// compatibility; more reports whether older events remain.
// TimelineFilter is what narrows a timeline. It is a struct rather
// than a fifth, sixth positional argument for the reason TicketFilter
// already is one in this file: a call site reading Timeline(50, "",
// "", "ci", "") says nothing about which empty string is which.
type TimelineFilter struct {
	Limit  int
	Before string
	Branch string
	Kind   string
	// Tag narrows to check-ins carrying a symbolic tag, as fossil's
	// own /timeline?t= does. Named WITHOUT the sym- prefix, the way a
	// reader writes it.
	Tag string
	// User narrows to one person's events, as fossil's /timeline?u=
	// does. Unlike Branch and Tag this does NOT imply check-ins only:
	// what someone did includes their wiki edits and ticket changes,
	// and fossil pairs u= with y=a for exactly that reason.
	User string
}

func (ix *Index) Timeline(f TimelineFilter) ([]TimelineEntry, bool, error) {
	limit, before, branch, kind := f.Limit, f.Before, f.Branch, f.Kind
	// Conditions and their arguments are appended TOGETHER so the two
	// lists cannot drift out of step. The previous form built them in
	// separate passes and then reassembled the arguments in a third
	// place, in an order a comment had to explain.
	var conds []string
	var all []any
	if branch != "" {
		conds = append(conds, `e.branch = ?`)
		all = append(all, branch)
		kind = "ci" // a branch filter implies check-ins only
	}
	if before != "" {
		bDate, bHash, ok := strings.Cut(before, "~")
		if !ok {
			b, _, found, err := ix.CheckinByPrefix(before)
			if err != nil {
				return nil, false, err
			}
			if !found {
				return nil, false, nil // unknown cursor: empty page, not an error
			}
			bDate, bHash = b.Date, b.Hash
		}
		conds = append(conds, `(e.date < ? OR (e.date = ? AND e.hash < ?))`)
		all = append(all, bDate, bDate, bHash)
	}
	if f.Tag != "" {
		// The STANDING tag, not merely a tag artifact that once
		// mentioned this check-in: fossil tags are a log and a later
		// artifact can cancel an earlier one, so the newest row per
		// name decides. Same rule as TagsOf, or a chip would link to a
		// timeline that disagrees with the chip.
		//
		// checkin_tag_by_target is (checkin, name, date DESC), which
		// is exactly this subquery's shape.
		conds = append(conds, `COALESCE((SELECT t.sign FROM checkin_tag t
			WHERE t.checkin = e.hash AND t.name = ?
			ORDER BY t.date DESC LIMIT 1), '-') <> '-'`)
		all = append(all, "sym-"+f.Tag)
		kind = "ci" // as with branch: only check-ins carry tags
	}
	if f.User != "" {
		conds = append(conds, `e.user = ?`)
		all = append(all, f.User)
	}
	if kind != "" && kind != "all" {
		conds = append(conds, `e.kind = ?`)
		all = append(all, kind)
	}
	where := ""
	if len(conds) > 0 {
		where = "WHERE " + strings.Join(conds, " AND ")
	}
	all = append(all, limit+1)

	// One index walk over the denormalized timeline, stopping at the
	// limit. The two per-row extras are evaluated for the RETURNED rows
	// only, which is the whole difference from the old plan:
	//
	//   isleaf — ALWAYS FALSE here. Leaf status is repo-state, which the
	//     repo-state RFC defines and zit already derives and caches;
	//     the API decorates these rows from zit's answer. This index
	//     used to compute it as "nothing lists it as a parent", which
	//     §7.2 names explicitly as NOT the canonical test — the real
	//     one is "no child on the same branch", and open leaves also
	//     exclude an active closed tag. On fossil that was 490 marked
	//     against a true 1432, and a different set. The column stays so
	//     the row shape is unchanged; the value is filled by the caller
	//     that can ask the store.
	//   ticket label — lives in the folded ticket doc, refreshed after
	//     the change rows land, so it is joined here rather than written
	//     by the trigger, which would capture a stale value.
	rows, err := ix.db.Query(`
		SELECT e.kind, e.hash, e.ref, e.date, e.user,
			CASE WHEN e.kind = 't'
				THEN 'Ticket [' || substr(e.ref, 1, 10) || '] ' || COALESCE(d.label, '')
				ELSE e.comment END,
			e.branch,
			0,
			CASE WHEN e.kind = 'e' THEN COALESCE(substr(n.body, 1, 400), '') ELSE '' END
		FROM event e
		LEFT JOIN search_doc d ON e.kind = 't' AND d.hash = 'ticket:' || e.ref
		LEFT JOIN technote n ON e.kind = 'e' AND n.hash = e.hash
		`+where+` ORDER BY e.date DESC, e.hash DESC LIMIT ?`, all...)
	if err != nil {
		return nil, false, err
	}
	defer rows.Close()
	var out []TimelineEntry
	for rows.Next() {
		var e TimelineEntry
		var body string
		if err := rows.Scan(&e.Kind, &e.Hash, &e.Ref, &e.Date, &e.User, &e.Comment, &e.Branch, &e.IsLeaf, &body); err != nil {
			return nil, false, err
		}
		e.Excerpt = Excerpt(body)
		out = append(out, e)
	}
	if err := rows.Err(); err != nil {
		return nil, false, err
	}
	more := false
	if len(out) > limit {
		out, more = out[:limit], true
	}
	if err := ix.fillParents(out); err != nil {
		return nil, false, err
	}
	if err := ix.AttachCI(out); err != nil {
		return nil, false, err
	}
	return out, more, nil
}

// fillParents attaches each entry's ordered parent list (primary first)
// and the merge flag — the graph's edge data.
func (ix *Index) fillParents(entries []TimelineEntry) error {
	byHash := map[string]*TimelineEntry{}
	for i := range entries {
		if entries[i].Kind != "" && entries[i].Kind != "ci" {
			continue // only check-ins have DAG edges
		}
		byHash[entries[i].Hash] = &entries[i]
	}
	for hash, e := range byHash {
		rows, err := ix.db.Query(`SELECT parent FROM checkin_parent
			WHERE child = ? ORDER BY ord`, hash)
		if err != nil {
			return err
		}
		for rows.Next() {
			var p string
			if err := rows.Scan(&p); err != nil {
				rows.Close()
				return err
			}
			e.Parents = append(e.Parents, p)
		}
		if err := rows.Err(); err != nil {
			rows.Close()
			return err
		}
		rows.Close()
		e.IsMerge = len(e.Parents) > 1
	}
	return nil
}

// LastEvent returns the newest event's date — what a forge listing sorts
// activity on. One index walk stopping at the first row.
func (ix *Index) LastEvent() (string, bool, error) {
	var date string
	err := ix.db.QueryRow(`SELECT date FROM event ORDER BY date DESC LIMIT 1`).Scan(&date)
	if errors.Is(err, sql.ErrNoRows) {
		return "", false, nil
	}
	return date, err == nil, err
}

// CheckinCount reports the indexed check-in total — the fleet page's
// headline number.
func (ix *Index) CheckinCount() (int, error) {
	var n int
	err := ix.db.QueryRow(`SELECT count(*) FROM checkin`).Scan(&n)
	return n, err
}

// Attachment is one live attachment on a ticket, wiki page or technote.
type Attachment struct {
	Hash string `json:"hash"`
	Name string `json:"name"`
	Src  string `json:"src"`
	Date string `json:"date"`
	User string `json:"user"`
}

// AttachmentsFor returns the attachments currently live on target,
// newest first. Fossil's model is a log, not a set: each A card names a
// filename on a target, and a card with NO source removes it. So the
// answer is the latest card per filename, minus the ones whose latest
// card is a deletion — an attachment replaced twice and then deleted
// must not come back.
func (ix *Index) AttachmentsFor(target string) ([]Attachment, error) {
	rows, err := ix.db.Query(`
		SELECT a.hash, a.name, a.src, a.date, a.user
		FROM attachment a
		JOIN (SELECT name, max(date) AS d FROM attachment
		      WHERE target = ? GROUP BY name) latest
		  ON latest.name = a.name AND latest.d = a.date
		WHERE a.target = ? AND a.src <> ''
		ORDER BY a.date DESC, a.name`, target, target)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Attachment
	for rows.Next() {
		var at Attachment
		if err := rows.Scan(&at.Hash, &at.Name, &at.Src, &at.Date, &at.User); err != nil {
			return nil, err
		}
		out = append(out, at)
	}
	return out, rows.Err()
}

// CountAttachments reports how many attachment artifacts are indexed —
// the census figure to compare against fossil's own attachment table.
func (ix *Index) CountAttachments() (int, error) {
	var n int
	err := ix.db.QueryRow(`SELECT count(*) FROM attachment`).Scan(&n)
	return n, err
}

// AttachmentName resolves a content hash to the filename it is attached
// under, and reports whether it is a LIVE attachment. This is the gate
// on serving attachment bytes: without it the download route would
// serve any artifact in the store by hash, including check-in manifests
// and file blobs that were never attached to anything.
//
// A hash whose newest card is a deletion answers false — the file was
// taken down, and taken-down content must not stay reachable by URL.
func (ix *Index) AttachmentName(src string) (string, bool, error) {
	var name, target string
	err := ix.db.QueryRow(`SELECT name, target FROM attachment
		WHERE src = ? ORDER BY date DESC LIMIT 1`, src).Scan(&name, &target)
	if err == sql.ErrNoRows {
		return "", false, nil
	}
	if err != nil {
		return "", false, err
	}
	// Live means: the newest card for this (target, name) still carries a
	// source. Re-checking by name rather than trusting the row above is
	// what makes a later deletion win.
	var newest string
	if err := ix.db.QueryRow(`SELECT src FROM attachment
		WHERE target = ? AND name = ? ORDER BY date DESC LIMIT 1`,
		target, name).Scan(&newest); err != nil {
		return "", false, err
	}
	if newest == "" {
		return "", false, nil
	}
	return name, true, nil
}

// Branch is one branch and the check-in at its tip.
//
// zit permits two branches to carry the SAME name (0002 §1: forgejo
// assumes names are unique and quarry cannot), so a selector keyed on
// name alone would silently pick one. Tip disambiguates them.
type Branch struct {
	Name string `json:"name"`
	Tip  string `json:"tip"`
	Date string `json:"date"`
	// Closed is fossil's branch lifecycle: the newest "closed" tag on
	// the tip, with its sign — a cancelled closure (-closed) reopens.
	Closed bool `json:"closed,omitempty"`
}

// Branches lists each branch with its newest check-in, most recently
// active first.
func (ix *Index) Branches() ([]Branch, error) {
	// One grouped scan, not a correlated subquery per row. SQLite's
	// bare-column rule picks the row that produced max(date), so the
	// hash belongs to the tip it reports.
	//
	// The first form of this cost 434 SECONDS on tcl (42,074 check-ins,
	// 1,855 branches): the subquery re-scanned the table once per row.
	// It is the reason checkin_by_branch exists.
	rows, err := ix.db.Query(`
		SELECT b.branch, b.hash, b.d,
		       COALESCE((SELECT t.sign FROM checkin_tag t
		                 WHERE t.checkin = b.hash AND t.name = 'closed'
		                 ORDER BY t.date DESC LIMIT 1), '') = '+'
		FROM (SELECT branch, hash, max(date) AS d FROM checkin
		      GROUP BY branch) b
		ORDER BY b.d DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Branch
	for rows.Next() {
		var b Branch
		if err := rows.Scan(&b.Name, &b.Tip, &b.Date, &b.Closed); err != nil {
			return nil, err
		}
		out = append(out, b)
	}
	return out, rows.Err()
}

// TipOf returns the newest check-in on a branch.
func (ix *Index) TipOf(branch string) (Branch, bool, error) {
	var b Branch
	err := ix.db.QueryRow(`SELECT branch, hash, date FROM checkin
		WHERE branch = ? ORDER BY date DESC LIMIT 1`, branch).Scan(&b.Name, &b.Tip, &b.Date)
	if err == sql.ErrNoRows {
		return Branch{}, false, nil
	}
	return b, err == nil, err
}

// CheckinsAfter returns check-ins newer than the named one, oldest
// first, so a consumer processes them in the order they were made.
//
// Ordering is by date, not by DAG reachability: a watermark is "how far
// have I looked", and a check-in that arrives by sync with an older
// date than the watermark is genuinely older work. Walking descendants
// instead would re-run branches that merged later.
//
// An unknown watermark returns only the tip — the safe answer, because
// the alternative is treating the entire history as new.
func (ix *Index) CheckinsAfter(hash string, limit int) ([]TimelineEntry, error) {
	if limit <= 0 {
		limit = 20
	}
	var date string
	if hash != "" {
		if err := ix.db.QueryRow(`SELECT date FROM checkin WHERE hash = ?`, hash).Scan(&date); err != nil {
			date = ""
		}
	}
	q := `SELECT hash, date, user, comment, branch FROM checkin ORDER BY date DESC LIMIT 1`
	args := []any{}
	if date != "" {
		q = `SELECT hash, date, user, comment, branch FROM checkin
			WHERE date > ? ORDER BY date ASC LIMIT ` + itoa(limit)
		args = append(args, date)
	}
	rows, err := ix.db.Query(q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []TimelineEntry
	for rows.Next() {
		var e TimelineEntry
		if err := rows.Scan(&e.Hash, &e.Date, &e.User, &e.Comment, &e.Branch); err != nil {
			return nil, err
		}
		e.Kind = "ci"
		out = append(out, e)
	}
	return out, rows.Err()
}

func itoa(n int) string {
	if n == 0 {
		return "0"
	}
	var b [20]byte
	i := len(b)
	for n > 0 {
		i--
		b[i] = byte('0' + n%10)
		n /= 10
	}
	return string(b[i:])
}

// tagState is the standing tag-derived display state for one check-in:
// the CI verdict, the effective comment, and whether it is hidden or
// closed.
type tagState struct {
	ci      string
	comment string
	hidden  bool
	closed  bool
}

// tagState returns that state per check-in hash, for the hashes given.
// One query rather than one per row: a timeline page asks about fifty
// check-ins at once.
//
// Fossil tags are a LOG. A later artifact can re-verdict a CI run,
// correct a comment, or cancel a hidden flag, so rows are walked
// ascending by date and the last word wins. This is the same rule
// TagsOf and the timeline's tag filter apply; all three must agree or
// the page contradicts itself.
func (ix *Index) tagState(hashes []string) (map[string]tagState, error) {
	if len(hashes) == 0 {
		return nil, nil
	}
	q := `SELECT checkin, name, sign, value FROM checkin_tag
		WHERE name IN ('ci-pass','ci-fail','comment','hidden','closed')
		  AND checkin IN (?` + strings.Repeat(",?", len(hashes)-1) + `)
		ORDER BY date ASC`
	args := make([]any, 0, len(hashes))
	for _, h := range hashes {
		args = append(args, h)
	}
	rows, err := ix.db.Query(q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := map[string]tagState{}
	for rows.Next() {
		var checkin, name, sign, value string
		if err := rows.Scan(&checkin, &name, &sign, &value); err != nil {
			return nil, err
		}
		st := out[checkin]
		switch name {
		case "ci-pass", "ci-fail":
			st.ci = name // ascending date: the last row seen is newest
		case "comment":
			// A cancelled correction restores what it replaced, which
			// is the stored C card — so an empty value here means "no
			// override", not "an empty comment".
			if sign == "-" {
				st.comment = ""
			} else {
				st.comment = value
			}
		case "hidden":
			st.hidden = sign != "-"
		case "closed":
			st.closed = sign != "-"
		}
		out[checkin] = st
	}
	return out, rows.Err()
}

// AttachCI fills the CI field on timeline entries.
func (ix *Index) AttachCI(entries []TimelineEntry) error {
	hashes := make([]string, 0, len(entries))
	for _, e := range entries {
		if e.Kind == "ci" || e.Kind == "" {
			hashes = append(hashes, e.Hash)
		}
	}
	state, err := ix.tagState(hashes)
	if err != nil || len(state) == 0 {
		return err
	}
	for i := range entries {
		st, ok := state[entries[i].Hash]
		if !ok {
			continue
		}
		entries[i].CI, entries[i].Hidden, entries[i].Closed = st.ci, st.hidden, st.closed
		if st.comment != "" && st.comment != entries[i].Comment {
			// The stored C card is left alone in the table — it is an
			// artifact fact, and the store is its authority. What a
			// reader is shown is the correction, with the superseded
			// text kept beside it rather than thrown away.
			entries[i].CommentOrig, entries[i].Comment = entries[i].Comment, st.comment
		}
	}
	return nil
}