package registry

import (
	"database/sql"
	"errors"
	"os"
	"path/filepath"
	"strings"
	"testing"
)

// List must observe rows committed through a DIFFERENT Registry handle — the
// in-process stand-in for `carlos add` writing while the edge holds its own
// long-lived handle. Pins titogo's live failure: a route added while the
// router ran never routed (through SIGHUP reloads), because List read a stale
// WAL snapshot off the long-lived pool and the fingerprint watcher, already
// matching, never retriggered.
func TestListSeesOtherHandlesCommits(t *testing.T) {
	path := filepath.Join(t.TempDir(), "registry.db")
	router, err := Open(path)
	if err != nil {
		t.Fatal(err)
	}

	adder, err := Open(path)
	if err != nil {
		t.Fatal(err)
	}
	if err := adder.Upsert(Route{
		Host: "a.example.com", Slug: "a", Kind: "instance",
		Socket: "/tmp/a.sock", Status: "ready",
	}); err != nil {
		t.Fatal(err)
	}

	routes, err := router.List()
	if err != nil {
		t.Fatal(err)
	}
	if len(routes) != 1 || routes[0].Slug != "a" {
		t.Fatalf("List through the first handle missed the second handle's commit: %+v", routes)
	}

	r := routes[0]
	r.Status = "failed"
	if err := adder.Upsert(r); err != nil {
		t.Fatal(err)
	}
	routes, err = router.List()
	if err != nil {
		t.Fatal(err)
	}
	if len(routes) != 1 || routes[0].Status != "failed" {
		t.Fatalf("List missed a cross-handle status flip: %+v", routes)
	}
}

// TestUpsertRoundTripsAllColumns is the column-shift canary: routeCols,
// scanRoute's destination list, Upsert's placeholder row and Upsert's
// argument list are four hand-maintained lists that must agree, and a
// miscount among them is a silent shift (a grant landing in created_at)
// rather than an error. Every column carries a DISTINCT value here on
// purpose — two columns sharing a value would let a swap pass.
func TestUpsertRoundTripsAllColumns(t *testing.T) {
	reg, err := Open(filepath.Join(t.TempDir(), "registry.db"))
	if err != nil {
		t.Fatal(err)
	}
	want := Route{
		Host: "b.example.com", Slug: "b", Kind: "instance",
		Socket: "", Addr: "127.0.0.1:8080",
		Addrs:  "10.0.0.1:3000,10.0.0.2:3000",
		Health: `{"path":"/up","status":200}`,
		Status: "ready", LastError: "prior fail",
		DBPath: "/data/b.db", Version: "abc1234", PrevVersion: "def5678",
		App: "kass", Pool: "shared", Region: "ie", Channel: "stable",
		Environment: "demo", Caps: "host-role,port:25",
		Hibernate: true, Canary: true, Replication: "batched",
		NextDue: 4200, Backing: BackingUnit, Unit: "console.service",
		Steering:    "latency",
		Replicas:    "ie,uk,us",
		Gate:        "$2a$10$distinct",
		CreatedAt:   1700000000,
		GoodVersion: "good789", GoodDigest: "sha256:good", GoodDemand: "swap-demand",
		ServingVersion: "serve012",
		UpdateSettings: `{"updates":"manual","channel":"stable"}`,
		BuildState:     `{"known":true,"state":{"schema":1,"last_executed":"bs345"}}`,
		SeedSource:     `{"id":"sd_aaaaaaaaaaaaaaaaaaaaaaaaaa","kind":"replica","state":"ready"}`,
		ImportGate:     "0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef",
	}
	if err := reg.Upsert(want); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("b.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got == nil {
		t.Fatal("Get returned nil for an upserted route")
	}
	if *got != want {
		t.Fatalf("round trip mismatch:\n got  %+v\n want %+v", *got, want)
	}

	// The replica mark and the replica set are the #162 pair, and Upsert's
	// invariant blanks the SET on a row that IS a replica (a replica of a
	// replica is not a shape the platform carries) — so the mark needs its
	// own round trip, on a row whose set is empty.
	wantReplica := Route{
		Host: "r.example.com", Slug: "r", Kind: "instance",
		Addr: "127.0.0.1:8081", Status: "ready",
		App: "kass", Pool: "shared", Region: "uk", Channel: "stable",
		Replica: true, CreatedAt: 1700000001,
	}
	if err := reg.Upsert(wantReplica); err != nil {
		t.Fatal(err)
	}
	gotReplica, err := reg.Get("r.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if gotReplica == nil {
		t.Fatal("Get returned nil for the replica row")
	}
	if *gotReplica != wantReplica {
		t.Fatalf("replica row round trip mismatch:\n got  %+v\n want %+v", *gotReplica, wantReplica)
	}
}

// An addrs or health change must move the edge's Fingerprint — the edge only
// reloads on a fingerprint move, so a column it can't see means serving a
// stale upstream set (or a stale/absent probe) forever.
func TestFingerprintSeesAddrsAndHealthChanges(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{Host: "m.example.com", Kind: "service", Addrs: "10.0.0.1:3000,10.0.0.2:3000", Status: "ready"}); err != nil {
		t.Fatal(err)
	}
	fp1, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.SetUpstreams("m.example.com", "", "10.0.0.1:3000,10.0.0.3:3000", ""); err != nil {
		t.Fatal(err)
	}
	fp2, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if fp1 == fp2 {
		t.Fatal("addrs change invisible to Fingerprint — edge would serve a stale upstream set forever")
	}
	if err := reg.SetUpstreams("m.example.com", "", "10.0.0.1:3000,10.0.0.3:3000", `{"path":"/up","status":200}`); err != nil {
		t.Fatal(err)
	}
	fp3, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if fp2 == fp3 {
		t.Fatal("health change invisible to Fingerprint")
	}
	// The fingerprint moves on ANY column change, so the two moves above don't
	// prove SetUpstreams wrote the right value into the right column — an
	// addrs<->health argument swap inside SetUpstreams would move the
	// fingerprint too. Check the values landed where asked.
	got, err := reg.Get("m.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got.Addrs != "10.0.0.1:3000,10.0.0.3:3000" {
		t.Fatalf("Addrs = %q, want the second SetUpstreams' addrs arg", got.Addrs)
	}
	if got.Health != `{"path":"/up","status":200}` {
		t.Fatalf("Health = %q, want the second SetUpstreams' health arg", got.Health)
	}
}

// AddrList must fold the legacy single Addr into a one-element list when
// Addrs is empty, split Addrs on the comma when it isn't, and return nil
// (not an empty slice) when the route carries no upstream at all.
func TestAddrListFoldsLegacyAddr(t *testing.T) {
	if got := (Route{Addr: "a:1"}).AddrList(); len(got) != 1 || got[0] != "a:1" {
		t.Fatalf("fold = %v", got)
	}
	if got := (Route{Addrs: "a:1,b:2"}).AddrList(); len(got) != 2 || got[1] != "b:2" {
		t.Fatalf("split = %v", got)
	}
	if got := (Route{}).AddrList(); got != nil {
		t.Fatalf("empty = %v", got)
	}
}

// TestSetUpstreamsRetiresLegacyAddr: the setter owns ALL THREE upstream
// columns, in both directions. A route migrating from the legacy single addr
// to a set must come out of the write with addr EMPTY — AddrList folds addr
// in whenever addrs is empty, so an addr left standing is a decommissioned
// upstream lying in wait for the next time the set is cleared, and the
// provision pass's shape comparison would call the row permanently divergent
// in the meantime.
func TestSetUpstreamsRetiresLegacyAddr(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{Host: "m.example.com", Kind: "service", Addr: "10.0.0.9:9443", Status: "ready"}); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetUpstreams("m.example.com", "", "10.0.0.1:3000,10.0.0.2:3000", `{"path":"/up"}`); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("m.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got.Addr != "" {
		t.Errorf("Addr = %q after migrating to an addrs set, want it retired", got.Addr)
	}
	if list := got.AddrList(); len(list) != 2 || list[0] != "10.0.0.1:3000" {
		t.Errorf("AddrList = %v, want exactly the new set", list)
	}

	// And back the other way: a route returning to a single upstream must not
	// keep the set beside it.
	if err := reg.SetUpstreams("m.example.com", "10.0.0.9:9443", "", ""); err != nil {
		t.Fatal(err)
	}
	if got, err = reg.Get("m.example.com"); err != nil {
		t.Fatal(err)
	}
	if got.Addrs != "" || got.Health != "" {
		t.Errorf("Addrs/Health = %q/%q, want both retired", got.Addrs, got.Health)
	}
	if list := got.AddrList(); len(list) != 1 || list[0] != "10.0.0.9:9443" {
		t.Errorf("AddrList = %v, want the single upstream", list)
	}
}

func TestSetUpstreamsErrorsOnUnmatchedHost(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.SetUpstreams("nope.example.com", "", "a:1", ""); err == nil {
		t.Fatal("SetUpstreams on an unmatched host: want error, got nil")
	}
}

// TestSetSteeringRoundTrips: the setter writes exactly the value given and
// nothing else — a single-column UPDATE, unlike SetUpstreams' three.
func TestSetSteeringRoundTrips(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{Host: "m.example.com", Kind: "instance", Status: "ready"}); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetSteering("m.example.com", "latency"); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("m.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got.Steering != "latency" {
		t.Fatalf("Steering = %q, want latency", got.Steering)
	}
	// And clearing: "" is a legitimate write, the column's only "off" value.
	if err := reg.SetSteering("m.example.com", ""); err != nil {
		t.Fatal(err)
	}
	if got, err = reg.Get("m.example.com"); err != nil {
		t.Fatal(err)
	}
	if got.Steering != "" {
		t.Fatalf("Steering = %q after clearing, want empty", got.Steering)
	}
}

func TestSetSteeringErrorsOnUnmatchedHost(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.SetSteering("nope.example.com", "latency"); err == nil {
		t.Fatal("SetSteering on an unmatched host: want error, got nil")
	}
}

// TestFingerprintIgnoresSteering is the re-ruled pin (plan amendment
// b485ec5, review finding 3): steering is deliberately OUT of Fingerprint,
// the same idiom as caps/unit (Route.Steering's own doc comment) — it
// changes what DNS answers, not what the edge serves, and the converger
// that acts on it is a host housekeeper duty reading the registry directly,
// never gated on the edge's reload poll. This task's own first pass hashed
// it here; that was wrong, and this test is what would have caught it.
func TestFingerprintIgnoresSteering(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{Host: "m.example.com", Kind: "instance", Status: "ready"}); err != nil {
		t.Fatal(err)
	}
	before, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.SetSteering("m.example.com", "latency"); err != nil {
		t.Fatal(err)
	}
	after, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if after != before {
		t.Fatalf("Fingerprint moved on a steering change (%s -> %s) — the edge would reload for a change that does not touch what it serves", before, after)
	}
}

// TestSnapshotFingerprintSeesSteeringChange is UNCHANGED by the re-ruling:
// SnapshotFingerprint hashes routeCols generically (its own doc comment: "a
// column added to routeCols is covered here automatically"), and the
// restore surface must reproduce steering even though the reload surface
// (Fingerprint, above) deliberately ignores it — the two answer different
// questions, per SnapshotFingerprint's own doc comment.
func TestSnapshotFingerprintSeesSteeringChange(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{Host: "m.example.com", Kind: "instance", Status: "ready"}); err != nil {
		t.Fatal(err)
	}
	fp1, err := reg.SnapshotFingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.SetSteering("m.example.com", "latency"); err != nil {
		t.Fatal(err)
	}
	fp2, err := reg.SnapshotFingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if fp1 == fp2 {
		t.Fatal("steering change invisible to SnapshotFingerprint — a cold-standby restore would silently drop it")
	}
}

func TestGetMissingIsNilNil(t *testing.T) {
	reg, err := Open(filepath.Join(t.TempDir(), "registry.db"))
	if err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("nope.example.com")
	if err != nil || got != nil {
		t.Fatalf("Get(missing) = %v, %v; want nil, nil", got, err)
	}
}

func TestDueInstances(t *testing.T) {
	reg, err := Open(filepath.Join(t.TempDir(), "registry.db"))
	if err != nil {
		t.Fatal(err)
	}
	for _, r := range []Route{
		{Host: "due.example.com", Kind: "instance", NextDue: 100},
		{Host: "future.example.com", Kind: "instance", NextDue: 900},
		{Host: "never.example.com", Kind: "instance", NextDue: 0},
	} {
		if err := reg.Upsert(r); err != nil {
			t.Fatal(err)
		}
	}
	due, err := reg.DueInstances(500)
	if err != nil {
		t.Fatal(err)
	}
	if len(due) != 1 || due[0].Host != "due.example.com" {
		t.Fatalf("DueInstances(500) = %+v; want exactly due.example.com", due)
	}
}

func TestFingerprintChangesOnWrite(t *testing.T) {
	reg, err := Open(filepath.Join(t.TempDir(), "registry.db"))
	if err != nil {
		t.Fatal(err)
	}
	before, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.Upsert(Route{Host: "c.example.com", Kind: "instance", Addr: "127.0.0.1:9"}); err != nil {
		t.Fatal(err)
	}
	after, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if before == after {
		t.Fatalf("fingerprint unchanged across a write: %q", after)
	}
	// A cross-handle write must change the fingerprint read here too (fresh
	// connection semantics — same quirk as List).
	other, err := Open(reg.Path())
	if err != nil {
		t.Fatal(err)
	}
	if err := other.SetHibernate("c.example.com", true); err != nil {
		t.Fatal(err)
	}
	final, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if final == after {
		t.Fatal("fingerprint blind to a cross-handle column write")
	}
	// Backing flips must reload the edge too: a woken host's backend choice
	// (exec child vs carlos-app@ unit) rides the route the reload captured.
	rt, err := reg.Get("c.example.com")
	if err != nil {
		t.Fatal(err)
	}
	rt.Backing = BackingUnit
	if err := reg.Upsert(*rt); err != nil {
		t.Fatal(err)
	}
	withBacking, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if withBacking == final {
		t.Fatal("fingerprint blind to a backing flip")
	}
	// Tunnel bindings must reload the edge too: this poll is the ONLY thing
	// that notices a `carlos route -tunnel` written by another process, and a
	// route the edge never reloads stays pointed at its old backend.
	if err := reg.SetTunnel("c.example.com", "pi-box"); err != nil {
		t.Fatal(err)
	}
	withTunnel, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if withTunnel == withBacking {
		t.Fatal("fingerprint blind to a tunnel binding")
	}
	// db_path rewrites must reload the edge too: a re-`carlos add` that only
	// fixes -db otherwise leaves the running activator waking the host with
	// the stale cached path forever — the exact vitogo incident (issue #69):
	// `add` reported success, the row was updated, and wakes still died on
	// the old path minutes later because this poll never moved.
	rt, err = reg.Get("c.example.com")
	if err != nil {
		t.Fatal(err)
	}
	rt.DBPath = "/data/somewhere-else.db"
	if err := reg.Upsert(*rt); err != nil {
		t.Fatal(err)
	}
	withDBPath, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if withDBPath == withTunnel {
		t.Fatal("fingerprint blind to a db_path rewrite")
	}
}

func TestSetVersionsAndHibernateAreNarrow(t *testing.T) {
	reg, err := Open(filepath.Join(t.TempDir(), "registry.db"))
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.Upsert(Route{Host: "d.example.com", Kind: "instance", Slug: "d", Channel: "stable"}); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetVersions("d.example.com", "v2", "v1"); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetHibernate("d.example.com", true); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetNextDue("d.example.com", 777); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("d.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got.Version != "v2" || got.PrevVersion != "v1" || !got.Hibernate || got.NextDue != 777 {
		t.Fatalf("narrow updates lost: %+v", got)
	}
	if got.Slug != "d" || got.Channel != "stable" {
		t.Fatalf("narrow update clobbered unrelated columns: %+v", got)
	}
}

// Backing must survive an Upsert/List round trip — the #14 incident was
// a route whose backing stamp went missing, and everything that branches
// on rt.Backing (activator dispatch, config sync scope) reasons from it.
func TestUpsertListRoundTripsBacking(t *testing.T) {
	r := openTestRegistry(t)
	if err := r.Upsert(Route{Host: "u.example.com", Kind: "instance", App: "a", Backing: BackingUnit}); err != nil {
		t.Fatalf("Upsert: %v", err)
	}
	routes, err := r.List()
	if err != nil {
		t.Fatalf("List: %v", err)
	}
	if len(routes) != 1 || routes[0].Backing != BackingUnit {
		t.Fatalf("routes = %+v, want one route with Backing=unit", routes)
	}
}

// openTestRegistry opens a fresh registry under a per-test temp dir — the
// shared open helper for tests that don't care about the path.
func openTestRegistry(t *testing.T) *Registry {
	t.Helper()
	reg, err := Open(filepath.Join(t.TempDir(), "registry.db"))
	if err != nil {
		t.Fatal(err)
	}
	return reg
}

// The durability property this whole multi-account spec exists for: the id
// is minted OUTSIDE the registry (by the console, into the bucket), so a
// registry rebuilt from scratch re-materializes the SAME id — and therefore
// the same sqid, and therefore the same platform domains. The old
// AUTOINCREMENT+EnsureAccount shape failed exactly here: ids were assigned in
// bucket-listing order, so a rebuild silently re-homed every platform domain.
func TestAccountsMaterializeIdenticallyAfterRebuild(t *testing.T) {
	dir := t.TempDir()
	r1, err := Open(filepath.Join(dir, "registry.db"))
	if err != nil {
		t.Fatal(err)
	}
	if err := r1.PutAccount(793481920157, "kqmv", "Kass"); err != nil {
		t.Fatal(err)
	}
	r1.Close()
	// Rebuild from scratch — the id comes from the bucket, so it must survive.
	os.Remove(filepath.Join(dir, "registry.db"))
	r2, err := Open(filepath.Join(dir, "registry.db"))
	if err != nil {
		t.Fatal(err)
	}
	defer r2.Close()
	if err := r2.PutAccount(793481920157, "kqmv", "Kass"); err != nil {
		t.Fatal(err)
	}
	id, name, err := r2.AccountBySqid("kqmv")
	if err != nil || id != 793481920157 || name != "Kass" {
		t.Fatalf("rebuild lost identity: id=%d name=%q err=%v", id, name, err)
	}
}

// The ops account is the migration's own row — id 1, sqid "bab", always, on
// every deployment — so platform-domain sqids for CLI-added apps are stable
// without any console ever existing.
func TestOpsAccountIsAlwaysOne(t *testing.T) {
	reg := openTestRegistry(t)
	id, name, err := reg.AccountBySqid("bab")
	if err != nil || id != 1 || name != "ops" {
		t.Fatalf("ops: id=%d name=%q err=%v, want 1/ops", id, name, err)
	}
	// Absent is 0, nil — not an error: "no such account" is an answer.
	if id, _, err := reg.AccountBySqid("nosuch"); err != nil || id != 0 {
		t.Fatalf("AccountBySqid(absent) = %d, %v; want 0, nil", id, err)
	}
}

// PutAccount is an upsert BY ID: re-materializing the same manifest is a
// no-op, and a rename in the bucket lands without minting a second row.
func TestPutAccountIdempotentAndRenames(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.PutAccount(42, "cdef", "Old"); err != nil {
		t.Fatal(err)
	}
	if err := reg.PutAccount(42, "cdef", "New"); err != nil {
		t.Fatal(err)
	}
	_, name, _ := reg.AccountBySqid("cdef")
	if name != "New" {
		t.Errorf("rename not applied: %q", name)
	}
	accts, _ := reg.Accounts()
	if len(accts) != 2 { // ops + this one, no duplicate row
		t.Errorf("want ops + one, got %d: %+v", len(accts), accts)
	}
}

// Account names are deliberately NOT unique (spec §1): identity is the sqid,
// so there is nothing to squat. A leftover UNIQUE(name) from the pre-sqid
// accounts table would turn two teams both calling their account "Blog" into
// a materialization failure.
func TestAccountNamesNeedNotBeUnique(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.PutAccount(42, "cdef", "Blog"); err != nil {
		t.Fatal(err)
	}
	if err := reg.PutAccount(43, "cdeg", "Blog"); err != nil {
		t.Fatalf("two accounts named Blog must both materialize: %v", err)
	}
	if id, _, _ := reg.AccountBySqid("cdeg"); id != 43 {
		t.Errorf("second Blog lost: id=%d", id)
	}
}

// The accounts table is DROPPED and rebuilt on upgrade — the one deliberate
// exception to additive-only migrations, because its legacy rows carry the
// assigned ids this whole change exists to stop trusting, and its
// UNIQUE(name) would refuse two accounts both called "Blog". A drop is where
// data quietly dies, so this pins exactly what survives and what does not:
// the ROUTES (the registry's actual data) are untouched, ops comes back as
// id 1 / sqid bab, and re-opening is clean and idempotent.
func TestAccountsRebuildKeepsRoutesAndRestoresOps(t *testing.T) {
	path := filepath.Join(t.TempDir(), "registry.db")

	// A registry in the pre-multi-account shape: the legacy accounts table
	// (AUTOINCREMENT id, UNIQUE name) with rows, plus real route data.
	first, err := Open(path)
	if err != nil {
		t.Fatal(err)
	}
	if _, err := first.sql.Exec(`DROP TABLE accounts`); err != nil {
		t.Fatal(err)
	}
	if _, err := first.sql.Exec(`CREATE TABLE accounts (
  id   INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT UNIQUE NOT NULL
);`); err != nil {
		t.Fatal(err)
	}
	for _, name := range []string{"ops", "paul@keymail.dev"} {
		if _, err := first.sql.Exec(`INSERT INTO accounts (name) VALUES (?)`, name); err != nil {
			t.Fatal(err)
		}
	}
	routes := []Route{
		{Host: "kass.training", Kind: "instance", App: "kass", Channel: "stable",
			Version: "v1", Addr: "127.0.0.1:9000", DBPath: "/data/kass.db", Hibernate: true},
		{Host: "site.example.com", Kind: "static", App: "site", Channel: "canary/x", Status: "ready"},
	}
	for _, rt := range routes {
		if err := first.Upsert(rt); err != nil {
			t.Fatal(err)
		}
	}
	first.Close()

	// Two opens: the rebuild must be idempotent, so restarting twice neither
	// rebuilds twice nor loses anything the second time.
	for pass := 1; pass <= 2; pass++ {
		r, err := Open(path)
		if err != nil {
			t.Fatalf("pass %d: open over the legacy shape: %v", pass, err)
		}
		id, name, err := r.AccountBySqid("bab")
		if err != nil || id != 1 || name != "ops" {
			t.Errorf("pass %d: ops = %d/%q (%v), want 1/ops", pass, id, name, err)
		}
		accts, err := r.Accounts()
		if err != nil {
			t.Fatal(err)
		}
		// The legacy assigned-id rows are deliberately NOT carried across:
		// they are the bug. Everything but ops re-materializes from the
		// bucket's own manifests on the next reconcile pass.
		for _, a := range accts {
			if a.Name == "paul@keymail.dev" {
				t.Errorf("pass %d: a legacy assigned-id row survived: %+v", pass, a)
			}
		}
		// The routes — the registry's actual data — are untouched.
		got, err := r.List()
		if err != nil {
			t.Fatal(err)
		}
		if len(got) != len(routes) {
			t.Fatalf("pass %d: %d routes survived, want %d", pass, len(got), len(routes))
		}
		byHost := map[string]Route{}
		for _, rt := range got {
			byHost[rt.Host] = rt
		}
		for _, want := range routes {
			g, ok := byHost[want.Host]
			if !ok {
				t.Errorf("pass %d: route %s vanished", pass, want.Host)
				continue
			}
			if g.App != want.App || g.Channel != want.Channel || g.Version != want.Version ||
				g.Addr != want.Addr || g.DBPath != want.DBPath || g.Hibernate != want.Hibernate ||
				g.Kind != want.Kind {
				t.Errorf("pass %d: route %s lost fields: %+v, want %+v", pass, want.Host, g, want)
			}
		}
		// A materialized account survives the SECOND open: only the legacy
		// shape is ever dropped, and it is gone after pass 1.
		if pass == 1 {
			if err := r.PutAccount(793481920157, "kqmvexample", "Kass"); err != nil {
				t.Fatal(err)
			}
		} else if id, _, _ := r.AccountBySqid("kqmvexample"); id != 793481920157 {
			t.Errorf("pass 2 dropped an account materialized after the rebuild (id %d)", id)
		}
		r.Close()
	}
}

func TestDomainColumnsRoundTrip(t *testing.T) {
	reg := openTestRegistry(t)
	r := Route{Host: "kass.abs.oncarlos.com", Kind: "instance", App: "kass",
		Platform: true, DNSStatus: "pointing", DNSCheckedAt: 123, CertExpiresAt: 456, Account: "ops"}
	if err := reg.Upsert(r); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get(r.Host)
	if err != nil {
		t.Fatal(err)
	}
	if !got.Platform || got.DNSStatus != "pointing" || got.DNSCheckedAt != 123 ||
		got.CertExpiresAt != 456 || got.Account != "ops" {
		t.Errorf("round-trip lost fields: %+v", got)
	}
}

// The domain-health stamps land every few minutes on every route, so they go
// through narrow UPDATEs (the SetVersions rationale: a read-modify-write
// Upsert racing the activator's SetNextDue or a deploy's SetVersions would
// clobber them).
func TestDomainSettersAreNarrow(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{Host: "kass.training", Kind: "instance", App: "kass", Channel: "stable"}); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetAccount("kass.training", "paul"); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetDNSStatus("kass.training", "elsewhere", 111); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetCertExpiresAt("kass.training", 222); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("kass.training")
	if err != nil {
		t.Fatal(err)
	}
	if got.Account != "paul" || got.DNSStatus != "elsewhere" || got.DNSCheckedAt != 111 || got.CertExpiresAt != 222 {
		t.Fatalf("narrow domain updates lost: %+v", got)
	}
	if got.App != "kass" || got.Channel != "stable" {
		t.Fatalf("narrow domain update clobbered unrelated columns: %+v", got)
	}
}

func TestStatusNamedRoundTrip(t *testing.T) {
	reg, err := Open(filepath.Join(t.TempDir(), "registry.db"))
	if err != nil {
		t.Fatal(err)
	}
	r := Route{Host: "kass.training", Kind: "instance", App: "kass", StatusNamed: true}
	if err := reg.Upsert(r); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("kass.training")
	if err != nil {
		t.Fatal(err)
	}
	if got == nil {
		t.Fatal("Get returned nil for an upserted route")
	}
	if !got.StatusNamed {
		t.Error("status_named did not round-trip")
	}
}

// SetChannel is the narrow UPDATE `carlos route` writes through: same
// rationale as SetAccount (a read-modify-write Upsert racing a deploy's
// SetVersions or the activator's SetNextDue would clobber those columns), and
// it must fail loudly on an unknown host so the CLI can name it rather than
// silently doing nothing.
func TestSetChannelNarrow(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{Host: "keymail.dev", Kind: "instance", App: "keymail",
		Channel: "canary/keymail", Version: "5457379"}); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetChannel("keymail.dev", "stable"); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("keymail.dev")
	if err != nil {
		t.Fatal(err)
	}
	if got.Channel != "stable" {
		t.Fatalf("channel = %q, want stable", got.Channel)
	}
	if got.App != "keymail" || got.Version != "5457379" {
		t.Fatalf("SetChannel clobbered unrelated columns: %+v", got)
	}
}

func TestSetChannelUnknownHostErrors(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.SetChannel("nope.example.com", "stable"); err == nil {
		t.Fatal("SetChannel on an unknown host: got nil error, want one")
	}
}

// SetChannels is `carlos route -app`'s write path: one transaction across
// every matched route, so a bad host mid-batch (or a crash mid-write) can
// never leave an app split across two channels — the CRITICAL 1 finding in
// the route-verb review. A check-then-write-per-route loop had already
// proven this fails: with a caller doing the checking up front and this
// doing the writing, either every row moves or none do.
func TestSetChannelsAllOrNothing(t *testing.T) {
	reg := openTestRegistry(t)
	for _, h := range []string{"a.example.com", "b.example.com"} {
		if err := reg.Upsert(Route{Host: h, Kind: "instance", App: "demo", Channel: "canary/x"}); err != nil {
			t.Fatal(err)
		}
	}
	err := reg.SetChannels([]string{"a.example.com", "nosuch.example.com", "b.example.com"}, "stable")
	if err == nil {
		t.Fatal("SetChannels with an unknown host in the batch: got nil error, want one")
	}
	for _, h := range []string{"a.example.com", "b.example.com"} {
		got, gerr := reg.Get(h)
		if gerr != nil {
			t.Fatal(gerr)
		}
		if got.Channel != "canary/x" {
			t.Fatalf("%s channel = %q after a failed batch, want untouched canary/x — the batch was not atomic", h, got.Channel)
		}
	}
}

func TestSetChannelsUpdatesEveryHost(t *testing.T) {
	reg := openTestRegistry(t)
	for _, h := range []string{"a.example.com", "b.example.com", "c.example.com"} {
		if err := reg.Upsert(Route{Host: h, Kind: "instance", App: "demo", Channel: "canary/x"}); err != nil {
			t.Fatal(err)
		}
	}
	if err := reg.SetChannels([]string{"a.example.com", "b.example.com", "c.example.com"}, "stable"); err != nil {
		t.Fatal(err)
	}
	for _, h := range []string{"a.example.com", "b.example.com", "c.example.com"} {
		got, err := reg.Get(h)
		if err != nil {
			t.Fatal(err)
		}
		if got.Channel != "stable" {
			t.Fatalf("%s channel = %q, want stable", h, got.Channel)
		}
	}
}

// A no-op call must not open a transaction it never uses (harmless either
// way, but it should not error).
func TestSetChannelsEmptyIsNoop(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.SetChannels(nil, "stable"); err != nil {
		t.Fatalf("SetChannels(nil, ...): %v, want no error", err)
	}
}

// SetEnvironment mirrors SetChannel exactly: a narrow UPDATE (so a
// read-modify-write Upsert racing SetVersions/SetNextDue/SetChannel can
// never clobber this column or be clobbered by it), erroring on an unknown
// host so `carlos route -environment` can name it.
func TestSetEnvironmentNarrow(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{Host: "demo.example.com", Kind: "instance", App: "tito",
		Channel: "stable", Version: "v1"}); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetEnvironment("demo.example.com", "demo"); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("demo.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got.Environment != "demo" {
		t.Fatalf("environment = %q, want demo", got.Environment)
	}
	if got.App != "tito" || got.Version != "v1" || got.Channel != "stable" {
		t.Fatalf("SetEnvironment clobbered unrelated columns: %+v", got)
	}
}

func TestSetEnvironmentUnknownHostErrors(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.SetEnvironment("nope.example.com", "demo"); err == nil {
		t.Fatal("SetEnvironment on an unknown host: got nil error, want one")
	}
}

// SetEnvironment("", ...) clears back to default — a plain UPDATE to "" is
// exactly that; no separate "clear" verb is needed at the registry layer,
// only at the CLI layer (`-environment -`).
func TestSetEnvironmentEmptyClearsToDefault(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{Host: "demo.example.com", Kind: "instance", App: "tito"}); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetEnvironment("demo.example.com", "demo"); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetEnvironment("demo.example.com", ""); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("demo.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got.Environment != "" {
		t.Fatalf("environment = %q, want empty (default) after clearing", got.Environment)
	}
}

// SetEnvironments is the bulk sibling of SetEnvironment — one transaction
// across every matched route, same all-or-nothing contract as SetChannels.
func TestSetEnvironmentsAllOrNothing(t *testing.T) {
	reg := openTestRegistry(t)
	for _, h := range []string{"a.example.com", "b.example.com"} {
		if err := reg.Upsert(Route{Host: h, Kind: "instance", App: "tito"}); err != nil {
			t.Fatal(err)
		}
	}
	err := reg.SetEnvironments([]string{"a.example.com", "nosuch.example.com", "b.example.com"}, "demo")
	if err == nil {
		t.Fatal("SetEnvironments with an unknown host in the batch: got nil error, want one")
	}
	for _, h := range []string{"a.example.com", "b.example.com"} {
		got, gerr := reg.Get(h)
		if gerr != nil {
			t.Fatal(gerr)
		}
		if got.Environment != "" {
			t.Fatalf("%s environment = %q after a failed batch, want untouched \"\" — the batch was not atomic", h, got.Environment)
		}
	}
}

func TestSetEnvironmentsUpdatesEveryHost(t *testing.T) {
	reg := openTestRegistry(t)
	for _, h := range []string{"a.example.com", "b.example.com", "c.example.com"} {
		if err := reg.Upsert(Route{Host: h, Kind: "instance", App: "tito"}); err != nil {
			t.Fatal(err)
		}
	}
	if err := reg.SetEnvironments([]string{"a.example.com", "b.example.com", "c.example.com"}, "demo"); err != nil {
		t.Fatal(err)
	}
	for _, h := range []string{"a.example.com", "b.example.com", "c.example.com"} {
		got, err := reg.Get(h)
		if err != nil {
			t.Fatal(err)
		}
		if got.Environment != "demo" {
			t.Fatalf("%s environment = %q, want demo", h, got.Environment)
		}
	}
}

func TestSetEnvironmentsEmptyIsNoop(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.SetEnvironments(nil, "demo"); err != nil {
		t.Fatalf("SetEnvironments(nil, ...): %v, want no error", err)
	}
}

// SetChannelsAndEnvironments is the combined write path `carlos route
// -channel X -environment Y` uses: one transaction sets BOTH columns for
// every host, so a channel-and-environment move can never leave a route
// repointed on one axis but not the other — the same all-or-nothing
// contract as SetChannels/SetEnvironments, but spanning two columns instead
// of one.
func TestSetChannelsAndEnvironmentsWritesBothColumnsAtomically(t *testing.T) {
	reg := openTestRegistry(t)
	for _, h := range []string{"a.example.com", "b.example.com"} {
		if err := reg.Upsert(Route{Host: h, Kind: "instance", App: "tito", Channel: "canary/x"}); err != nil {
			t.Fatal(err)
		}
	}
	if err := reg.SetChannelsAndEnvironments([]string{"a.example.com", "b.example.com"}, "stable", "demo"); err != nil {
		t.Fatal(err)
	}
	for _, h := range []string{"a.example.com", "b.example.com"} {
		got, err := reg.Get(h)
		if err != nil {
			t.Fatal(err)
		}
		if got.Channel != "stable" || got.Environment != "demo" {
			t.Fatalf("%s = channel %q environment %q, want stable/demo", h, got.Channel, got.Environment)
		}
	}
}

func TestSetChannelsAndEnvironmentsAllOrNothing(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{Host: "a.example.com", Kind: "instance", App: "tito", Channel: "canary/x"}); err != nil {
		t.Fatal(err)
	}
	err := reg.SetChannelsAndEnvironments([]string{"a.example.com", "nosuch.example.com"}, "stable", "demo")
	if err == nil {
		t.Fatal("SetChannelsAndEnvironments with an unknown host in the batch: got nil error, want one")
	}
	got, err := reg.Get("a.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got.Channel != "canary/x" || got.Environment != "" {
		t.Fatalf("a.example.com = channel %q environment %q after a failed batch, want both untouched", got.Channel, got.Environment)
	}
}

// Provisioned + Seed round-trip through Upsert/Get, and SetSeed is the
// reconciler's narrow-update door (same contract as SetChannel: errors on an
// unmatched host so a vanished row is noticed, not silently no-opped).
func TestProvisionedSeedRoundTrip(t *testing.T) {
	r := openTestRegistry(t)
	if err := r.Upsert(Route{Host: "a.go.tito.io", Kind: "instance", Provisioned: true, Seed: `["seed"]`}); err != nil {
		t.Fatal(err)
	}
	got, err := r.Get("a.go.tito.io")
	if err != nil {
		t.Fatal(err)
	}
	if got == nil {
		t.Fatal("Get returned nil for an upserted route")
	}
	if !got.Provisioned || got.Seed != `["seed"]` {
		t.Fatalf("provisioned/seed did not round-trip: %+v", got)
	}

	if err := r.SetSeed("a.go.tito.io", `["seed","-v"]`); err != nil {
		t.Fatal(err)
	}
	got, err = r.Get("a.go.tito.io")
	if err != nil {
		t.Fatal(err)
	}
	if got.Seed != `["seed","-v"]` {
		t.Fatalf("SetSeed did not update: %+v", got)
	}
	if !got.Provisioned {
		t.Fatalf("SetSeed clobbered an unrelated column: %+v", got)
	}

	err = r.SetSeed("nope.example.com", `["x"]`)
	if err == nil {
		t.Fatal("SetSeed on an unknown host: got nil error, want one naming the host")
	}
	if !strings.Contains(err.Error(), "nope.example.com") {
		t.Fatalf("SetSeed unknown-host error = %q, want it to name the host", err.Error())
	}
}

// Upsert must not let a caller that knows nothing about provisioning revoke
// the reconcile pass's removal authority. `carlos add` builds a full Route
// with provisioned=false and seed="" — run against a host an instance
// record already minted, a plain overwrite would strip the flag the pass
// removes by (leaving a route no bucket deletion can ever retire) and erase
// the first-boot argv. The zero value preserves, a real value wins, and
// clearing keeps exactly one door each: row deletion, and SetSeed.
func TestUpsertPreservesProvisionedAndSeed(t *testing.T) {
	r := openTestRegistry(t)
	const host = "acme.go.tito.io"
	if err := r.Upsert(Route{Host: host, Kind: "instance", App: "titogo",
		Provisioned: true, Seed: `["seed"]`}); err != nil {
		t.Fatal(err)
	}
	// The runAdd shape: every column set from flags, nothing about
	// provisioning in it at all.
	if err := r.Upsert(Route{Host: host, Kind: "instance", App: "titogo",
		Socket: "/run/carlos/" + host + ".sock", DBPath: "/data/" + host + ".db"}); err != nil {
		t.Fatal(err)
	}
	got, err := r.Get(host)
	if err != nil {
		t.Fatal(err)
	}
	if !got.Provisioned {
		t.Errorf("a re-add revoked the pass's removal authority: %+v", got)
	}
	if got.Seed != `["seed"]` {
		t.Errorf("a re-add erased the first-boot argv: seed = %q", got.Seed)
	}
	if got.Socket != "/run/carlos/"+host+".sock" {
		t.Errorf("the re-add's own columns did not land: %+v", got)
	}

	// A non-zero incoming value still wins — this is preservation, not a
	// column that can never be written.
	if err := r.Upsert(Route{Host: host, Kind: "instance", App: "titogo",
		Provisioned: true, Seed: `["seed","-v"]`}); err != nil {
		t.Fatal(err)
	}
	if got, _ = r.Get(host); got.Seed != `["seed","-v"]` {
		t.Errorf("an explicit seed lost to the existing value: %q", got.Seed)
	}

	// The narrow door still clears: the reconcile pass converging a
	// manifest whose seed was removed must be able to empty it.
	if err := r.SetSeed(host, ""); err != nil {
		t.Fatal(err)
	}
	if got, _ = r.Get(host); got.Seed != "" {
		t.Errorf("SetSeed(\"\") did not clear the seed: %q", got.Seed)
	}
	if !got.Provisioned {
		t.Errorf("SetSeed touched provisioned: %+v", got)
	}

	// And provisioned's only clear is the row going away — which is what
	// the removal phase does.
	if err := r.Delete(host); err != nil {
		t.Fatal(err)
	}
	if err := r.Upsert(Route{Host: host, Kind: "instance", App: "titogo"}); err != nil {
		t.Fatal(err)
	}
	if got, _ = r.Get(host); got.Provisioned {
		t.Errorf("provisioned survived the row's deletion: %+v", got)
	}
}

// Opening a DB created before this change (no provisioned/seed columns) must
// migrate cleanly — the ALTER list is additive, duplicate-column errors are
// swallowed, and pre-existing rows default provisioned=false seed="" rather
// than failing to open.
func TestProvisionedSeedMigratesFromPreChangeDB(t *testing.T) {
	path := filepath.Join(t.TempDir(), "registry.db")
	db, err := sql.Open("sqlite", path)
	if err != nil {
		t.Fatal(err)
	}
	if _, err := db.Exec(`
CREATE TABLE routes (
  host         TEXT PRIMARY KEY,
  kind         TEXT NOT NULL,
  socket       TEXT NOT NULL DEFAULT '',
  status       TEXT NOT NULL DEFAULT 'ready',
  last_error   TEXT NOT NULL DEFAULT '',
  db_path      TEXT NOT NULL DEFAULT '',
  version      TEXT NOT NULL DEFAULT '',
  prev_version TEXT NOT NULL DEFAULT '',
  created_at   INTEGER NOT NULL
);`); err != nil {
		t.Fatal(err)
	}
	if _, err := db.Exec(`INSERT INTO routes (host, kind, created_at) VALUES (?, ?, ?)`,
		"old.example.com", "instance", 1234); err != nil {
		t.Fatal(err)
	}
	if err := db.Close(); err != nil {
		t.Fatal(err)
	}

	r, err := Open(path)
	if err != nil {
		t.Fatalf("Open over a pre-change DB: %v", err)
	}
	defer r.Close()
	got, err := r.Get("old.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got == nil {
		t.Fatal("pre-existing row vanished on migrate")
	}
	if got.Provisioned || got.Seed != "" {
		t.Fatalf("pre-existing row should default provisioned=false seed=\"\": %+v", got)
	}

	// Reopening again (the ALTER already applied) must stay idempotent.
	r2, err := Open(path)
	if err != nil {
		t.Fatalf("second Open: %v", err)
	}
	defer r2.Close()
	if got2, err := r2.Get("old.example.com"); err != nil || got2 == nil {
		t.Fatalf("second Open lost the row: %+v, %v", got2, err)
	}
}

// Review IMPORTANT 3: Upsert must not clear an existing row's Environment.
// `carlos add` (and any other full-row Upsert caller) does not know about
// environment bindings and passes the zero value, so a re-run on a bound host
// used to silently unbind it — and the next config sync then re-rendered the
// default bundle over that route's env file and restarted the unit.
func TestUpsertPreservesAnExistingEnvironmentBinding(t *testing.T) {
	reg, err := Open(filepath.Join(t.TempDir(), "registry.db"))
	if err != nil {
		t.Fatal(err)
	}
	base := Route{Host: "demo.example.com", Kind: "instance", App: "tito", Status: "ready"}
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetEnvironment("demo.example.com", "demo"); err != nil {
		t.Fatal(err)
	}
	// The re-add: same row, no Environment in hand.
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("demo.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got.Environment != "demo" {
		t.Fatalf("Environment = %q after a re-Upsert, want the binding preserved", got.Environment)
	}
	// An explicit non-empty value still wins — preservation is a floor, not a
	// freeze, so a caller that genuinely knows the binding can still set it.
	base.Environment = "staging"
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	got, err = reg.Get("demo.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got.Environment != "staging" {
		t.Fatalf("Environment = %q, want an explicit Upsert value to win", got.Environment)
	}
	// Clearing stays possible, through the narrow setter the clear verb uses
	// (`carlos route -environment -`), which is the only intentional clear.
	if err := reg.SetEnvironment("demo.example.com", ""); err != nil {
		t.Fatal(err)
	}
	got, err = reg.Get("demo.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got.Environment != "" {
		t.Fatalf("Environment = %q after an explicit clear, want it cleared", got.Environment)
	}
}

// A registry file written BEFORE the caps column existed must still open, and
// its rows must read as "nothing granted" — the additive-migration contract
// applied to the column that decides whether a unit gets box authority. The
// pre-change file is simulated by dropping the column from a fresh registry
// and writing a row through raw SQL, which is exactly the shape an older
// binary left behind.
func TestPreCapsRegistryOpensAndReadsUngranted(t *testing.T) {
	path := filepath.Join(t.TempDir(), "registry.db")
	reg, err := Open(path)
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.Close(); err != nil {
		t.Fatal(err)
	}

	old, err := sql.Open("sqlite", path)
	if err != nil {
		t.Fatal(err)
	}
	if _, err := old.Exec(`ALTER TABLE routes DROP COLUMN caps`); err != nil {
		t.Fatalf("simulate a pre-caps registry: %v", err)
	}
	if _, err := old.Exec(`INSERT INTO routes (host, kind, status, created_at)
VALUES ('legacy.example.com', 'instance', 'ready', 1700000000)`); err != nil {
		t.Fatal(err)
	}
	if err := old.Close(); err != nil {
		t.Fatal(err)
	}

	reg, err = Open(path)
	if err != nil {
		t.Fatalf("a pre-caps registry must still open: %v", err)
	}
	got, err := reg.Get("legacy.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got == nil {
		t.Fatal("the pre-existing row vanished across the migration")
	}
	if got.Caps != "" {
		t.Fatalf("Caps = %q on a row written before the column existed, want %q — a migrated row must mean 'nothing granted'", got.Caps, "")
	}
	if got.Host != "legacy.example.com" || got.Kind != "instance" || got.CreatedAt != 1700000000 {
		t.Fatalf("the migrated row shifted columns: %+v", *got)
	}
	// And the new column is writable on that same row.
	if err := reg.SetCaps("legacy.example.com", "", "port:25"); err != nil {
		t.Fatal(err)
	}
	got, err = reg.Get("legacy.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got.Caps != "port:25" {
		t.Fatalf("Caps = %q after SetCaps on a migrated row", got.Caps)
	}
}

// SetCaps is narrow (it touches no other column) and refuses an unknown host
// rather than silently doing nothing — `carlos route -grant` is a hostname a
// human types, and a typo that reports success while granting nothing is the
// worst possible outcome for this particular column.
func TestSetCapsIsNarrowAndErrorsOnAnUnknownHost(t *testing.T) {
	reg, err := Open(filepath.Join(t.TempDir(), "registry.db"))
	if err != nil {
		t.Fatal(err)
	}
	base := Route{
		Host: "keymail.dev", Kind: "instance", App: "keymail", Backing: BackingUnit,
		Channel: "canary/keymail", Environment: "prod", Version: "abc1234",
		Status: "ready", NextDue: 99, DBPath: "/var/lib/carlos-app/keymail.dev/registry.db",
	}
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetCaps("keymail.dev", "", "port:25"); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("keymail.dev")
	if err != nil {
		t.Fatal(err)
	}
	want := base
	want.Caps = "port:25"
	want.CreatedAt = got.CreatedAt
	if *got != want {
		t.Fatalf("SetCaps was not narrow:\n got  %+v\n want %+v", *got, want)
	}
	if err := reg.SetCaps("nope.example.com", "", "port:25"); err == nil {
		t.Fatal("SetCaps on an unknown host must error, not silently write nothing")
	}
	// The compare-and-swap: a write built on a stale read refuses, naming the
	// current value, instead of merging over a concurrent grant/revoke.
	if err := reg.SetCaps("keymail.dev", "stale-read", "host-role"); err == nil {
		t.Fatal("SetCaps with a stale prior value must refuse, not overwrite")
	} else if !strings.Contains(err.Error(), "changed underneath") || !strings.Contains(err.Error(), "port:25") {
		t.Fatalf("a lost race should name the condition and the current value, got: %v", err)
	}
}

// SetDBPath is the converge pass's narrow stamp (it teaches ops.FleetDBs
// where a platform-replicated app's databases live), with the same
// unknown-host contract.
func TestSetDBPathIsNarrowAndErrorsOnAnUnknownHost(t *testing.T) {
	reg, err := Open(filepath.Join(t.TempDir(), "registry.db"))
	if err != nil {
		t.Fatal(err)
	}
	base := Route{
		Host: "console.example.com", Kind: "instance", App: "console",
		Backing: BackingUnit, Channel: "canary/console", Status: "ready",
	}
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetCaps("console.example.com", "", "host-role"); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetDBPath("console.example.com", "", "/var/lib/carlos-app/console.example.com/console.db"); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("console.example.com")
	if err != nil {
		t.Fatal(err)
	}
	want := base
	want.Caps = "host-role"
	want.DBPath = "/var/lib/carlos-app/console.example.com/console.db"
	want.CreatedAt = got.CreatedAt
	if *got != want {
		t.Fatalf("SetDBPath was not narrow:\n got  %+v\n want %+v", *got, want)
	}
	if err := reg.SetDBPath("nope.example.com", "", "/x"); err == nil {
		t.Fatal("SetDBPath on an unknown host must error")
	}
	// Compare-and-swap, like SetCaps: the hourly pass decided to write
	// BECAUSE of the value it read, so a value that moved underneath it is a
	// refusal naming the current one — never a blind overwrite of a path
	// somebody else just set, which decides which file gets replicated.
	if err := reg.SetDBPath("console.example.com", "/data/stale.db", "/var/lib/carlos-app/console.example.com/other.db"); err == nil {
		t.Fatal("SetDBPath with a stale prior value must refuse, not overwrite")
	} else if !strings.Contains(err.Error(), "changed underneath") || !strings.Contains(err.Error(), "console.db") {
		t.Fatalf("a lost race should name the condition and the current value, got: %v", err)
	}
}

// SetBacking is the migration door (app-capabilities design §5b/§5c): it
// moves a route between an exec child of the agent and a carlos-app@<host>
// unit, in both directions, narrowly — every other column of the row is
// untouched, so a stamp mid-migration cannot lose a grant or a version.
func TestSetBackingIsNarrowAndReversible(t *testing.T) {
	reg, err := Open(filepath.Join(t.TempDir(), "registry.db"))
	if err != nil {
		t.Fatal(err)
	}
	base := Route{Host: "console.example.com", Kind: "instance", App: "console", Channel: "canary/console", Status: "ready", DBPath: "/data/console.db"}
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetBacking("console.example.com", BackingUnit); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("console.example.com")
	if err != nil {
		t.Fatal(err)
	}
	want := base
	want.Backing = BackingUnit
	want.CreatedAt = got.CreatedAt
	if *got != want {
		t.Fatalf("SetBacking was not narrow:\n got  %+v\n want %+v", *got, want)
	}
	// Reversible: the migration's rollback step is the same verb, back.
	if err := reg.SetBacking("console.example.com", ""); err != nil {
		t.Fatal(err)
	}
	if got, err := reg.Get("console.example.com"); err != nil {
		t.Fatal(err)
	} else if got.Backing != "" {
		t.Fatalf("backing = %q, want it back to the exec child", got.Backing)
	}
	if err := reg.SetBacking("nope.example.com", BackingUnit); err == nil {
		t.Fatal("SetBacking on an unknown host must error")
	}
}

// The named finding, as a test: `carlos add` re-runs and the domains
// reconciler re-mints, and neither knows anything about grants. A plain
// excluded.caps would silently REVOKE port:25 on a re-add — a capability
// disappearing hours later, on the next converge pass, with nothing in the
// operator's shell history connecting the two.
func TestUpsertPreservesAnExistingGrant(t *testing.T) {
	reg, err := Open(filepath.Join(t.TempDir(), "registry.db"))
	if err != nil {
		t.Fatal(err)
	}
	base := Route{Host: "keymail.dev", Kind: "instance", App: "keymail", Backing: BackingUnit, Status: "ready"}
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetCaps("keymail.dev", "", "host-role,port:25"); err != nil {
		t.Fatal(err)
	}
	// The re-add / the re-mint: same row, no grants in hand.
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("keymail.dev")
	if err != nil {
		t.Fatal(err)
	}
	if got.Caps != "host-role,port:25" {
		t.Fatalf("Caps = %q after a re-Upsert, want the grant preserved", got.Caps)
	}
	// Preservation is a floor, not a freeze: an explicit non-empty value from
	// a caller that genuinely knows the grants still wins.
	base.Caps = "port:25"
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	got, err = reg.Get("keymail.dev")
	if err != nil {
		t.Fatal(err)
	}
	if got.Caps != "port:25" {
		t.Fatalf("Caps = %q, want an explicit Upsert value to win", got.Caps)
	}
	// Revoking to nothing has exactly one door, and it is not Upsert.
	if err := reg.SetCaps("keymail.dev", "port:25", ""); err != nil {
		t.Fatal(err)
	}
	got, err = reg.Get("keymail.dev")
	if err != nil {
		t.Fatal(err)
	}
	if got.Caps != "" {
		t.Fatalf("Caps = %q after an explicit revoke-to-empty, want it cleared", got.Caps)
	}
}

// A grant changes unit shape, not routing: the edge has no reason to reload
// for one, so the fingerprint must NOT move when caps does. (The inverse is
// pinned by TestFingerprintChangesOnWrite for the columns that do matter.)
func TestFingerprintIgnoresAGrant(t *testing.T) {
	path := filepath.Join(t.TempDir(), "registry.db")
	reg, err := Open(path)
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.Upsert(Route{Host: "keymail.dev", Kind: "instance", App: "keymail", Backing: BackingUnit}); err != nil {
		t.Fatal(err)
	}
	before, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.SetCaps("keymail.dev", "", "host-role,port:25"); err != nil {
		t.Fatal(err)
	}
	after, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if after != before {
		t.Fatalf("Fingerprint moved on a grant (%s -> %s) — the edge would reload for a change that does not touch routing", before, after)
	}
}

// TestFingerprintSeesSameLengthVersionSwap pins issue #15: a same-length
// version rewrite (git short shas are always the same length) must change
// the fingerprint, or the edge's X-Carlos-Version header goes stale.
func TestFingerprintSeesSameLengthVersionSwap(t *testing.T) {
	reg := openTestRegistry(t)
	rt := Route{Host: "fp.example.test", Kind: "instance", App: "echo", Status: "ready", Version: "abc1234"}
	if err := reg.Upsert(rt); err != nil {
		t.Fatal(err)
	}
	before, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.SetVersions("fp.example.test", "def5678", "abc1234"); err != nil {
		t.Fatal(err)
	}
	after, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if before == after {
		t.Fatal("same-length version swap invisible to Fingerprint (issue #15)")
	}
}

func TestFingerprintSeesSameLengthChannelSwap(t *testing.T) {
	reg := openTestRegistry(t)
	rt := Route{Host: "fp2.example.test", Kind: "instance", App: "echo", Status: "ready", Channel: "canary/1"}
	if err := reg.Upsert(rt); err != nil {
		t.Fatal(err)
	}
	before, _ := reg.Fingerprint()
	rt.Channel = "canary/2"
	if err := reg.Upsert(rt); err != nil {
		t.Fatal(err)
	}
	after, _ := reg.Fingerprint()
	if before == after {
		t.Fatal("same-length channel swap invisible to Fingerprint (issue #15)")
	}
}

func TestTunnelColumnRoundTrips(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{Host: "demo.example", Kind: "instance", Status: "ready"}); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetTunnel("demo.example", "pi-box"); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("demo.example")
	if err != nil {
		t.Fatal(err)
	}
	if got.Tunnel != "pi-box" {
		t.Fatalf("Tunnel = %q, want pi-box", got.Tunnel)
	}
}

// Same rationale as TestUpsertPreservesAnExistingEnvironmentBinding: a
// full-row Upsert caller that does not know about tunnel bindings must not
// silently unbind one by re-writing the zero value.
func TestUpsertPreservesTunnel(t *testing.T) {
	reg := openTestRegistry(t)
	base := Route{Host: "demo.example", Kind: "instance", Status: "ready"}
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetTunnel("demo.example", "pi-box"); err != nil {
		t.Fatal(err)
	}
	// A full-row re-Upsert with zero-value Tunnel must not unbind.
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("demo.example")
	if err != nil {
		t.Fatal(err)
	}
	if got.Tunnel != "pi-box" {
		t.Fatalf("Upsert clobbered Tunnel: %q", got.Tunnel)
	}
	// Clearing has exactly one door: SetTunnel(host, "").
	if err := reg.SetTunnel("demo.example", ""); err != nil {
		t.Fatal(err)
	}
	got, err = reg.Get("demo.example")
	if err != nil {
		t.Fatal(err)
	}
	if got.Tunnel != "" {
		t.Fatalf("SetTunnel clear failed: %q", got.Tunnel)
	}
}

// Same contract as TestSetEnvironmentUnknownHostErrors: a human types a
// hostname into `carlos route -tunnel` directly, and a silent no-op on a
// typo would be worse than a refusal.
func TestSetTunnelUnknownHostErrors(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.SetTunnel("nope.example.com", "pi-box"); err == nil {
		t.Fatal("SetTunnel on an unknown host: got nil error, want one")
	}
}

// Mirrors appconfig.TestValidEnvironmentName: ValidTunnelName gates what
// reaches the `tunnel` column before it does — a name later matched against
// a dialed-in tunnel identity and used to build keys (remote-host-agent
// feature, issue #53), so path separators, spaces, and "" must never reach
// SetTunnel/Upsert unvalidated. Unlike ValidEnvironmentName there is no
// reserved name (no "default" tunnel), so "" is invalid only because it
// fails the character class, not a separate reservation.
func TestValidTunnelName(t *testing.T) {
	valid := []string{"demo", "a", "pi-box", "connect-timeslots", "0-9"}
	invalid := []string{
		"",          // empty
		"Demo",      // uppercase
		"has space", // space
		"has_us",    // underscore not allowed
		"../../x",   // path traversal
		"UP",
		string(make([]byte, 33)), // too long (33 NUL bytes, also fails the char class)
	}
	for _, n := range valid {
		if !ValidTunnelName(n) {
			t.Errorf("ValidTunnelName(%q) = false, want true", n)
		}
	}
	for _, n := range invalid {
		if ValidTunnelName(n) {
			t.Errorf("ValidTunnelName(%q) = true, want false", n)
		}
	}
}

// A registry file written BEFORE the unit column existed must still open, its
// rows must read as "no unit" (which is what every route on every box is
// today), and the new column must be writable on that same migrated row. The
// pre-change file is simulated the way TestPreCapsRegistryOpensAndReadsUngranted
// simulates its own: drop the column from a fresh registry and write a row
// through raw SQL, which is the shape an older binary left behind.
func TestPreUnitRegistryOpensAndReadsNoUnit(t *testing.T) {
	path := filepath.Join(t.TempDir(), "registry.db")
	reg, err := Open(path)
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.Close(); err != nil {
		t.Fatal(err)
	}

	old, err := sql.Open("sqlite", path)
	if err != nil {
		t.Fatal(err)
	}
	if _, err := old.Exec(`ALTER TABLE routes DROP COLUMN unit`); err != nil {
		t.Fatalf("simulate a pre-unit registry: %v", err)
	}
	if _, err := old.Exec(`INSERT INTO routes (host, kind, addr, status, created_at)
VALUES ('console.carloku.com', 'instance', '127.0.0.1:8090', 'ready', 1700000000)`); err != nil {
		t.Fatal(err)
	}
	if err := old.Close(); err != nil {
		t.Fatal(err)
	}

	reg, err = Open(path)
	if err != nil {
		t.Fatalf("a pre-unit registry must still open: %v", err)
	}
	defer reg.Close()
	got, err := reg.Get("console.carloku.com")
	if err != nil {
		t.Fatal(err)
	}
	if got == nil {
		t.Fatal("the pre-existing row vanished across the migration")
	}
	if got.Unit != "" {
		t.Fatalf("Unit = %q on a row written before the column existed, want %q", got.Unit, "")
	}
	if got.Host != "console.carloku.com" || got.Kind != "instance" ||
		got.Addr != "127.0.0.1:8090" || got.CreatedAt != 1700000000 {
		t.Fatalf("the migrated row shifted columns: %+v", *got)
	}
	// The stamp the live consoles need, on exactly that migrated row.
	if err := reg.SetUnit("console.carloku.com", "console.service"); err != nil {
		t.Fatal(err)
	}
	got, err = reg.Get("console.carloku.com")
	if err != nil {
		t.Fatal(err)
	}
	if got.Unit != "console.service" {
		t.Fatalf("Unit = %q after SetUnit on a migrated row", got.Unit)
	}
	// And clearing it again has a door, so a mis-stamp is reversible.
	if err := reg.SetUnit("console.carloku.com", ""); err != nil {
		t.Fatal(err)
	}
	got, err = reg.Get("console.carloku.com")
	if err != nil {
		t.Fatal(err)
	}
	if got.Unit != "" {
		t.Fatalf("SetUnit clear failed: %q", got.Unit)
	}
}

// SetUnit is narrow (it touches no other column) and refuses an unknown host
// — the SetBacking contract, for the same reason: a human types the hostname
// in directly.
func TestSetUnitIsNarrowAndErrorsOnAnUnknownHost(t *testing.T) {
	reg := openTestRegistry(t)
	base := Route{
		Host: "console.example.com", Kind: "instance", App: "console",
		Addr: "127.0.0.1:8090", Status: "ready", Version: "abc1234", Channel: "canary/console",
	}
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetUnit("console.example.com", "console.service"); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("console.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got.Unit != "console.service" {
		t.Fatalf("Unit = %q, want console.service", got.Unit)
	}
	if got.Version != "abc1234" || got.Channel != "canary/console" || got.Addr != "127.0.0.1:8090" {
		t.Fatalf("SetUnit touched another column: %+v", *got)
	}
	if err := reg.SetUnit("nope.example.com", "console.service"); err == nil {
		t.Fatal("SetUnit on an unknown host: got nil error, want one")
	}
}

// A typo'd unit must fail at WRITE time, loudly, rather than at restart time
// silently — the whole reason the column exists is that a restart which
// reaches nothing looks exactly like a restart that worked.
func TestSetUnitRefusesAMalformedName(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{Host: "console.example.com", Kind: "instance", Status: "ready"}); err != nil {
		t.Fatal(err)
	}
	for _, bad := range []string{"console.servcie", "console", "console.timer", "console service", "console.service extra"} {
		if err := reg.SetUnit("console.example.com", bad); err == nil {
			t.Fatalf("SetUnit(%q) accepted; want a refusal", bad)
		}
	}
	got, err := reg.Get("console.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got.Unit != "" {
		t.Fatalf("a refused SetUnit still wrote %q", got.Unit)
	}
}

func TestValidUnitName(t *testing.T) {
	valid := []string{
		"",                                   // the column's default: no unit
		"console.service",                    //
		"keymail-relay.service",              // hyphens, just not leading
		"titogo-canary@demo.service",         // template instance
		"carlos_app.service",                 // underscore
		"a.b.c.service",                      // dots, just not leading
		"a.service",                          // one-character stem
		strings.Repeat("a", 64) + ".service", // the length bound itself
	}
	invalid := []string{
		"console",                            // no suffix
		"console.servcie",                    // the typo this rail is for
		"carlos-adopt.timer",                 // a unit, but not one that owns a process
		"console.socket",                     //
		"has space.service",                  // space
		"../../etc/x.service",                // path traversal
		"console.service; reboot",            // shell-shaped, and refused as a shape
		".service",                           // suffix alone
		"-x.service",                         // leading dash: reads as a flag in any argv built from this column
		"..service",                          // dot-only stem
		"...service",                         //
		".x.service",                         // leading dot
		strings.Repeat("a", 65) + ".service", // one over the bound
	}
	for _, n := range valid {
		if !ValidUnitName(n) {
			t.Errorf("ValidUnitName(%q) = false, want true", n)
		}
	}
	for _, n := range invalid {
		if ValidUnitName(n) {
			t.Errorf("ValidUnitName(%q) = true, want false", n)
		}
	}
}

// Same rationale as TestUpsertPreservesTunnel, with the stakes of the caps
// column: a full-row Upsert caller (`carlos add`, a reconciler's mint) knows
// nothing about which unit owns a route's process, and dropping the stamp
// would silently restore issue #110 on the one row it matters for.
func TestUpsertPreservesUnit(t *testing.T) {
	reg := openTestRegistry(t)
	base := Route{Host: "console.example.com", Kind: "instance", App: "console", Status: "ready"}
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetUnit("console.example.com", "console.service"); err != nil {
		t.Fatal(err)
	}
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("console.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got.Unit != "console.service" {
		t.Fatalf("Upsert clobbered Unit: %q", got.Unit)
	}
	// Preservation is a floor, not a freeze: an explicit value still wins.
	base.Unit = "console-next.service"
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	got, err = reg.Get("console.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got.Unit != "console-next.service" {
		t.Fatalf("Unit = %q, want an explicit Upsert value to win", got.Unit)
	}
}

// unit changes who RESTARTS the process, not what the edge serves — so the
// edge must not reload for one. Same rule and same test shape as
// TestFingerprintIgnoresAGrant, and this pins the exclusion so a later
// "hash every column" tidy-up has to argue with a test.
func TestFingerprintIgnoresAUnitStamp(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{
		Host: "console.example.com", Kind: "instance", App: "console",
		Addr: "127.0.0.1:8090", Status: "ready",
	}); err != nil {
		t.Fatal(err)
	}
	before, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.SetUnit("console.example.com", "console.service"); err != nil {
		t.Fatal(err)
	}
	after, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if after != before {
		t.Fatalf("Fingerprint moved on a unit stamp (%s -> %s) — the edge would reload for a change that does not touch routing", before, after)
	}
}

// The other half of TestFingerprintIgnoresAGrant and
// TestFingerprintIgnoresAUnitStamp: what the edge is right to ignore, the
// cold-standby snapshot must NOT ignore. caps and unit are box-scoped rows
// nothing else in the bucket can rebuild, so if SnapshotFingerprint sat
// still for them the snapshot pass would never fire and a restore would come
// back without them. These two tests pin the divergence in both directions
// at once — Fingerprint still still, SnapshotFingerprint moved.
func TestSnapshotFingerprintSeesACapsGrant(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{
		Host: "mail.example.com", Kind: "instance", App: "mail",
		Addr: "127.0.0.1:8091", Status: "ready",
	}); err != nil {
		t.Fatal(err)
	}
	beforeSnap, err := reg.SnapshotFingerprint()
	if err != nil {
		t.Fatal(err)
	}
	beforeEdge, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.SetCaps("mail.example.com", "", "net_bind_service"); err != nil {
		t.Fatal(err)
	}
	afterSnap, err := reg.SnapshotFingerprint()
	if err != nil {
		t.Fatal(err)
	}
	afterEdge, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if afterSnap == beforeSnap {
		t.Fatalf("SnapshotFingerprint unchanged across a caps grant (%s) — the snapshot pass would never fire, and a cold-standby restore would silently drop the grant", beforeSnap)
	}
	if afterEdge != beforeEdge {
		t.Fatalf("Fingerprint moved on a caps grant (%s -> %s) — the edge-reload digest must stay indifferent to it", beforeEdge, afterEdge)
	}
}

func TestSnapshotFingerprintSeesAUnitStamp(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{
		Host: "console.example.com", Kind: "instance", App: "console",
		Addr: "127.0.0.1:8090", Status: "ready",
	}); err != nil {
		t.Fatal(err)
	}
	beforeSnap, err := reg.SnapshotFingerprint()
	if err != nil {
		t.Fatal(err)
	}
	beforeEdge, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.SetUnit("console.example.com", "console.service"); err != nil {
		t.Fatal(err)
	}
	afterSnap, err := reg.SnapshotFingerprint()
	if err != nil {
		t.Fatal(err)
	}
	afterEdge, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if afterSnap == beforeSnap {
		t.Fatalf("SnapshotFingerprint unchanged across a unit stamp (%s) — a restored console route would come back unstamped and stop auto-cycling", beforeSnap)
	}
	if afterEdge != beforeEdge {
		t.Fatalf("Fingerprint moved on a unit stamp (%s -> %s)", beforeEdge, afterEdge)
	}
}

// Determinism: repeated calls over an unchanged registry must agree, or the
// snapshot pass writes a generation every 5 minutes forever. (This is the
// property that ruled out hashing the VACUUM INTO page image.)
func TestSnapshotFingerprintStableAcrossCalls(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{
		Host: "a.example.com", Kind: "service", Addr: "10.0.0.1:3000", Status: "ready",
	}); err != nil {
		t.Fatal(err)
	}
	first, err := reg.SnapshotFingerprint()
	if err != nil {
		t.Fatal(err)
	}
	for i := 0; i < 3; i++ {
		again, err := reg.SnapshotFingerprint()
		if err != nil {
			t.Fatal(err)
		}
		if again != first {
			t.Fatalf("SnapshotFingerprint call %d = %s, want %s — an unstable digest snapshots on every pass", i+2, again, first)
		}
	}
}

// An account rename has no route column behind it, and only the snapshot can
// restore the accounts table — so it has to move the snapshot digest even
// though no route changed at all.
func TestSnapshotFingerprintSeesAnAccountWrite(t *testing.T) {
	reg := openTestRegistry(t)
	before, err := reg.SnapshotFingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if _, err := reg.sql.Exec(`INSERT INTO accounts (id, sqid, name) VALUES (?, ?, ?)`, 4242, "zzz", "later"); err != nil {
		t.Fatal(err)
	}
	after, err := reg.SnapshotFingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if after == before {
		t.Fatalf("SnapshotFingerprint unchanged across an accounts write (%s)", before)
	}
}

func TestValidTunnelNameLengthBoundary(t *testing.T) {
	ok32 := "01234567890123456789012345678901" // 32 chars
	if len(ok32) != 32 {
		t.Fatalf("test fixture is %d chars, want 32", len(ok32))
	}
	if !ValidTunnelName(ok32) {
		t.Errorf("ValidTunnelName(32 chars) = false, want true")
	}
	tooLong := ok32 + "x"
	if ValidTunnelName(tooLong) {
		t.Errorf("ValidTunnelName(33 chars) = true, want false")
	}
}

func TestSetProvisioned(t *testing.T) {
	r := openTestRegistry(t)
	if err := r.Upsert(Route{Host: "a.example.com", Kind: "instance", App: "x", Status: "ready"}); err != nil {
		t.Fatal(err)
	}
	if err := r.SetProvisioned("a.example.com", true); err != nil {
		t.Fatal(err)
	}
	rt, err := r.Get("a.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if !rt.Provisioned {
		t.Error("SetProvisioned(true) did not stamp the column")
	}
	if err := r.SetProvisioned("missing.example.com", true); err == nil {
		t.Error("SetProvisioned on an unknown host returned nil; want an error (the SetChannel contract)")
	}
}

func TestSetStatusNamed(t *testing.T) {
	r := openTestRegistry(t)
	if err := r.Upsert(Route{Host: "a.example.com", Kind: "instance", App: "x", Status: "ready"}); err != nil {
		t.Fatal(err)
	}
	if err := r.SetStatusNamed("a.example.com", true); err != nil {
		t.Fatal(err)
	}
	rt, err := r.Get("a.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if !rt.StatusNamed {
		t.Error("SetStatusNamed(true) did not stamp the column")
	}
	if err := r.SetStatusNamed("a.example.com", false); err != nil {
		t.Fatal(err)
	}
	rt, err = r.Get("a.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if rt.StatusNamed {
		t.Error("SetStatusNamed(false) did not clear the column")
	}
	if err := r.SetStatusNamed("missing.example.com", true); err == nil {
		t.Error("SetStatusNamed on an unknown host returned nil; want an error (the SetChannel contract)")
	}
}

// TestSetFleetBoxRoundTrips mirrors TestSetTunnel's shape: SetFleetBox is a
// narrow UPDATE on the new fleet_box column, which records which fleet box
// is CURRENTLY serving a placed route (distinct from Region/placement
// target, which says where the route WANTS to run).
func TestSetFleetBoxRoundTrips(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{Host: "demo.example", Kind: "instance", Status: "ready"}); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetFleetBox("demo.example", "pi-1"); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("demo.example")
	if err != nil {
		t.Fatal(err)
	}
	if got.FleetBox != "pi-1" {
		t.Fatalf("FleetBox = %q, want pi-1", got.FleetBox)
	}
	if err := reg.SetFleetBox("demo.example", ""); err != nil {
		t.Fatal(err)
	}
	got, err = reg.Get("demo.example")
	if err != nil {
		t.Fatal(err)
	}
	if got.FleetBox != "" {
		t.Fatalf("SetFleetBox clear failed: %q", got.FleetBox)
	}
}

// Same contract as TestSetTunnelUnknownHostErrors: a human/reconciler typo'd
// host must get a refusal, not a silent no-op.
func TestSetFleetBoxUnknownHostErrors(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.SetFleetBox("nope.example.com", "pi-1"); err == nil {
		t.Fatal("SetFleetBox on an unknown host: got nil error, want one")
	}
}

// Same rationale as TestUpsertPreservesTunnel: a full-row Upsert caller that
// does not know about fleet placement must not silently unbind a box another
// pass stamped.
func TestUpsertPreservesFleetBox(t *testing.T) {
	reg := openTestRegistry(t)
	base := Route{Host: "demo.example", Kind: "instance", Status: "ready"}
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetFleetBox("demo.example", "pi-1"); err != nil {
		t.Fatal(err)
	}
	// A full-row re-Upsert with zero-value FleetBox must not unbind.
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("demo.example")
	if err != nil {
		t.Fatal(err)
	}
	if got.FleetBox != "pi-1" {
		t.Fatalf("Upsert clobbered FleetBox: %q", got.FleetBox)
	}
	// An explicit non-empty value still wins.
	base.FleetBox = "pi-2"
	if err := reg.Upsert(base); err != nil {
		t.Fatal(err)
	}
	got, err = reg.Get("demo.example")
	if err != nil {
		t.Fatal(err)
	}
	if got.FleetBox != "pi-2" {
		t.Fatalf("FleetBox = %q, want an explicit Upsert value to win", got.FleetBox)
	}
	// Clearing has exactly one door: SetFleetBox(host, "").
	if err := reg.SetFleetBox("demo.example", ""); err != nil {
		t.Fatal(err)
	}
	got, err = reg.Get("demo.example")
	if err != nil {
		t.Fatal(err)
	}
	if got.FleetBox != "" {
		t.Fatalf("SetFleetBox clear failed: %q", got.FleetBox)
	}
}

// A registry file written BEFORE the fleet_box column existed must still
// open, and its rows must read as "no box bound" — the same additive-
// migration contract as TestProvisionedSeedMigratesFromPreChangeDB.
func TestFleetBoxMigratesFromPreChangeDB(t *testing.T) {
	path := filepath.Join(t.TempDir(), "registry.db")
	db, err := sql.Open("sqlite", path)
	if err != nil {
		t.Fatal(err)
	}
	if _, err := db.Exec(`
CREATE TABLE routes (
  host         TEXT PRIMARY KEY,
  kind         TEXT NOT NULL,
  socket       TEXT NOT NULL DEFAULT '',
  status       TEXT NOT NULL DEFAULT 'ready',
  last_error   TEXT NOT NULL DEFAULT '',
  db_path      TEXT NOT NULL DEFAULT '',
  version      TEXT NOT NULL DEFAULT '',
  prev_version TEXT NOT NULL DEFAULT '',
  created_at   INTEGER NOT NULL
);`); err != nil {
		t.Fatal(err)
	}
	if _, err := db.Exec(`INSERT INTO routes (host, kind, created_at) VALUES (?, ?, ?)`,
		"old.example.com", "instance", 1234); err != nil {
		t.Fatal(err)
	}
	if err := db.Close(); err != nil {
		t.Fatal(err)
	}

	r, err := Open(path)
	if err != nil {
		t.Fatalf("Open over a pre-change DB: %v", err)
	}
	defer r.Close()
	got, err := r.Get("old.example.com")
	if err != nil {
		t.Fatal(err)
	}
	if got == nil {
		t.Fatal("pre-existing row vanished on migrate")
	}
	if got.FleetBox != "" {
		t.Fatalf("pre-existing row should default fleet_box=\"\": %+v", got)
	}
}

// TestSetPlacementRoundTrips pins SetPlacement's grammar: "" clears,
// "fleet/<name>" sets Region to the fleet placement target, and anything
// else — a bare region code, an invalid fleet name — is refused rather than
// silently written. Bare region codes are a FUTURE placement form; v1 only
// accepts "" or "fleet/<name>" here (FleetPlacement, fleets.go, is what
// distinguishes the two forms on read).
func TestSetPlacementRoundTrips(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{Host: "demo.example", Kind: "instance", Status: "ready"}); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetPlacement("demo.example", "fleet/pi"); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get("demo.example")
	if err != nil {
		t.Fatal(err)
	}
	if got.Region != "fleet/pi" {
		t.Fatalf("Region = %q, want fleet/pi", got.Region)
	}
	if err := reg.SetPlacement("demo.example", ""); err != nil {
		t.Fatal(err)
	}
	got, err = reg.Get("demo.example")
	if err != nil {
		t.Fatal(err)
	}
	if got.Region != "" {
		t.Fatalf("SetPlacement(\"\") did not clear Region: %q", got.Region)
	}
}

func TestSetPlacementRejectsBadGrammar(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{Host: "demo.example", Kind: "instance", Status: "ready"}); err != nil {
		t.Fatal(err)
	}
	for _, bad := range []string{"bogus", "eu", "fleet/UPPER", "fleet/", "fleet"} {
		if err := reg.SetPlacement("demo.example", bad); err == nil {
			t.Errorf("SetPlacement(%q): got nil error, want one", bad)
		}
	}
	got, err := reg.Get("demo.example")
	if err != nil {
		t.Fatal(err)
	}
	if got.Region != "" {
		t.Fatalf("a rejected SetPlacement wrote Region anyway: %q", got.Region)
	}
}

func TestSetPlacementUnknownHostErrors(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.SetPlacement("nope.example.com", "fleet/pi"); err == nil {
		t.Fatal("SetPlacement on an unknown host: got nil error, want one")
	}
}

// TestRouteAliasCRUD pins UpsertAlias/ListAliases/DeleteAlias's contract: a
// re-upsert repoints host/account/app but must NOT reset the DNS/cert stamps
// or created_at — those are reconcile-pass-owned and box-stamped state, the
// same shape as Upsert's preserved columns on routes.
func TestRouteAliasCRUD(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.UpsertAlias(RouteAlias{
		Alias: "extra.example.com", Host: "app.example.com",
		Account: "bab", App: "myapp", CreatedAt: 1000,
	}); err != nil {
		t.Fatalf("UpsertAlias: %v", err)
	}
	got, err := reg.ListAliases()
	if err != nil {
		t.Fatalf("ListAliases: %v", err)
	}
	if len(got) != 1 {
		t.Fatalf("ListAliases = %+v, want exactly one row", got)
	}
	if got[0].Alias != "extra.example.com" || got[0].Host != "app.example.com" ||
		got[0].Account != "bab" || got[0].App != "myapp" || got[0].CreatedAt != 1000 {
		t.Fatalf("round-tripped alias = %+v, want the upserted values", got[0])
	}

	// Stamp the row the way a box-side pass would, THEN re-upsert a repoint —
	// the stamps and created_at must survive.
	if err := reg.SetAliasDNSStatus("extra.example.com", "pointing", 2000); err != nil {
		t.Fatalf("SetAliasDNSStatus: %v", err)
	}
	if err := reg.SetAliasCertExpiresAt("extra.example.com", 3000); err != nil {
		t.Fatalf("SetAliasCertExpiresAt: %v", err)
	}
	if err := reg.UpsertAlias(RouteAlias{
		Alias: "extra.example.com", Host: "other.example.com",
		Account: "bad", App: "otherapp", CreatedAt: 9999,
	}); err != nil {
		t.Fatalf("re-UpsertAlias: %v", err)
	}
	got, err = reg.ListAliases()
	if err != nil {
		t.Fatalf("ListAliases: %v", err)
	}
	if len(got) != 1 {
		t.Fatalf("ListAliases after re-upsert = %+v, want exactly one row", got)
	}
	a := got[0]
	if a.Host != "other.example.com" || a.Account != "bad" || a.App != "otherapp" {
		t.Fatalf("re-upsert did not repoint host/account/app: %+v", a)
	}
	if a.DNSStatus != "pointing" || a.DNSCheckedAt != 2000 || a.CertExpiresAt != 3000 {
		t.Fatalf("re-upsert clobbered stamps: %+v", a)
	}
	if a.CreatedAt != 1000 {
		t.Fatalf("re-upsert clobbered created_at: got %d, want the FIRST insert's 1000", a.CreatedAt)
	}

	// Delete is idempotent: gone is success, called twice is still success.
	if err := reg.DeleteAlias("extra.example.com"); err != nil {
		t.Fatalf("DeleteAlias: %v", err)
	}
	if err := reg.DeleteAlias("extra.example.com"); err != nil {
		t.Fatalf("DeleteAlias (already gone): %v", err)
	}
	got, err = reg.ListAliases()
	if err != nil {
		t.Fatalf("ListAliases: %v", err)
	}
	if len(got) != 0 {
		t.Fatalf("ListAliases after delete = %+v, want none", got)
	}
}

// TestRouteAliasStamps pins SetAliasDNSStatus/SetAliasCertExpiresAt as narrow
// UPDATEs that persist independently of UpsertAlias — same rationale as
// SetDNSStatus/SetCertExpiresAt on routes.
func TestRouteAliasStamps(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.UpsertAlias(RouteAlias{Alias: "extra.example.com", Host: "app.example.com"}); err != nil {
		t.Fatalf("UpsertAlias: %v", err)
	}
	if err := reg.SetAliasDNSStatus("extra.example.com", "elsewhere", 111); err != nil {
		t.Fatalf("SetAliasDNSStatus: %v", err)
	}
	if err := reg.SetAliasCertExpiresAt("extra.example.com", 222); err != nil {
		t.Fatalf("SetAliasCertExpiresAt: %v", err)
	}
	got, err := reg.ListAliases()
	if err != nil {
		t.Fatalf("ListAliases: %v", err)
	}
	if len(got) != 1 {
		t.Fatalf("ListAliases = %+v, want exactly one row", got)
	}
	if got[0].DNSStatus != "elsewhere" || got[0].DNSCheckedAt != 111 || got[0].CertExpiresAt != 222 {
		t.Fatalf("stamps did not persist: %+v", got[0])
	}

	// A same-host UpsertAlias (no repoint) must not disturb the stamps either.
	if err := reg.UpsertAlias(RouteAlias{Alias: "extra.example.com", Host: "app.example.com"}); err != nil {
		t.Fatalf("UpsertAlias: %v", err)
	}
	got, err = reg.ListAliases()
	if err != nil {
		t.Fatalf("ListAliases: %v", err)
	}
	if got[0].DNSStatus != "elsewhere" || got[0].DNSCheckedAt != 111 || got[0].CertExpiresAt != 222 {
		t.Fatalf("UpsertAlias disturbed stamps that survived a plain re-upsert: %+v", got[0])
	}
}

// TestFingerprintCoversAliases pins the fingerprint contract from the task
// brief: an alias write moves the fingerprint (so the edge reloads and
// starts routing it), but a STAMP write never does — stamping runs on a
// 5-minute cadence and hashing it would force a pointless fleet-wide edge
// reload every pass (same reasoning as the routes section's own omission of
// dns_status/cert_expires_at).
func TestFingerprintCoversAliases(t *testing.T) {
	reg := openTestRegistry(t)
	fp1, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.UpsertAlias(RouteAlias{Alias: "extra.example.com", Host: "app.example.com", Account: "bab", App: "myapp"}); err != nil {
		t.Fatalf("UpsertAlias: %v", err)
	}
	fp2, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if fp2 == fp1 {
		t.Fatal("Fingerprint blind to an alias UpsertAlias")
	}

	if err := reg.SetAliasDNSStatus("extra.example.com", "pointing", 12345); err != nil {
		t.Fatalf("SetAliasDNSStatus: %v", err)
	}
	fp3, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if fp3 != fp2 {
		t.Fatalf("Fingerprint moved on an alias stamp (%s -> %s) — the edge would reload for a change that does not touch routing", fp2, fp3)
	}

	if err := reg.DeleteAlias("extra.example.com"); err != nil {
		t.Fatalf("DeleteAlias: %v", err)
	}
	fp4, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if fp4 == fp2 {
		t.Fatal("Fingerprint blind to a DeleteAlias")
	}
}

// TestFingerprintCoversWildcardDomains is Task 4's carry-over from Task 2's
// review: wildcard_domains rows (Task 3's reconcile-derived delegated
// wildcard parents) were entirely absent from Fingerprint, so a new
// delegated parent landing (or leaving) the registry would not move the
// digest the edge's 2s Watch poll keys off — it would sit unrouted until the
// wildcard manager's own 12h tick, rather than reaching wildcard.go's
// refresh() (registered via Edge.OnReload) within the usual 2s bound. This
// pins the fix the same way TestFingerprintCoversAliases pins route_aliases:
// an Upsert and a Delete of a wildcard_domains row must both move the
// fingerprint.
func TestFingerprintCoversWildcardDomains(t *testing.T) {
	reg := openTestRegistry(t)
	fp1, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.UpsertWildcardDomain(WildcardDomain{Host: "woodstar.app", Account: "bab", App: "myapp", CreatedAt: 1}); err != nil {
		t.Fatalf("UpsertWildcardDomain: %v", err)
	}
	fp2, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if fp2 == fp1 {
		t.Fatal("Fingerprint blind to a wildcard_domains UpsertWildcardDomain")
	}

	if err := reg.DeleteWildcardDomain("woodstar.app"); err != nil {
		t.Fatalf("DeleteWildcardDomain: %v", err)
	}
	fp3, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if fp3 == fp2 {
		t.Fatal("Fingerprint blind to a DeleteWildcardDomain")
	}
	if fp3 != fp1 {
		t.Fatalf("Fingerprint after add+remove = %s, want back to the original %s", fp3, fp1)
	}
}

// TestMigrateIdempotentWithAliases pins migrate()'s additive contract for the
// new table: opening an already-migrated registry a second time (CREATE
// TABLE IF NOT EXISTS route_aliases) must not error.
func TestMigrateIdempotentWithAliases(t *testing.T) {
	path := filepath.Join(t.TempDir(), "registry.db")
	r1, err := Open(path)
	if err != nil {
		t.Fatalf("first Open: %v", err)
	}
	if err := r1.UpsertAlias(RouteAlias{Alias: "extra.example.com", Host: "app.example.com"}); err != nil {
		t.Fatalf("UpsertAlias: %v", err)
	}
	if err := r1.Close(); err != nil {
		t.Fatal(err)
	}

	r2, err := Open(path)
	if err != nil {
		t.Fatalf("second Open over the same path: %v", err)
	}
	defer r2.Close()
	got, err := r2.ListAliases()
	if err != nil {
		t.Fatalf("ListAliases: %v", err)
	}
	if len(got) != 1 || got[0].Alias != "extra.example.com" {
		t.Fatalf("route_aliases row lost across a second Open: %+v", got)
	}
}

func TestWildcardDomains(t *testing.T) {
	r := openTestRegistry(t)

	if err := r.UpsertWildcardDomain(WildcardDomain{Host: "woodstar.app", Account: "bab", App: "woodstar-relay", CreatedAt: 100}); err != nil {
		t.Fatal(err)
	}
	if err := r.UpsertWildcardDomain(WildcardDomain{Host: "example.org", Account: "bad", App: "other", CreatedAt: 200}); err != nil {
		t.Fatal(err)
	}
	// Upsert by host: same host, new owner fields win.
	if err := r.UpsertWildcardDomain(WildcardDomain{Host: "woodstar.app", Account: "bab", App: "woodstar-relay2", CreatedAt: 300}); err != nil {
		t.Fatal(err)
	}
	got, err := r.ListWildcardDomains()
	if err != nil {
		t.Fatal(err)
	}
	if len(got) != 2 || got[0].Host != "example.org" || got[1].Host != "woodstar.app" || got[1].App != "woodstar-relay2" {
		t.Fatalf("list = %+v", got)
	}
	if err := r.DeleteWildcardDomain("example.org"); err != nil {
		t.Fatal(err)
	}
	got, _ = r.ListWildcardDomains()
	if len(got) != 1 || got[0].Host != "woodstar.app" {
		t.Fatalf("after delete: %+v", got)
	}
}

// TestDueInstancesIncludesSidecars pins the reason this task exists: a
// sidecar cannot be dialed, so its due-time wake is its ONLY schedule wake
// source. Left filtering kind='instance', DueInstances would store a
// sidecar's next_due and never once act on it.
func TestDueInstancesIncludesSidecars(t *testing.T) {
	r := openTestRegistry(t)
	if err := r.Upsert(Route{
		Host: "bot.eleven.bdm.sidecar.internal", Kind: "sidecar",
		DBPath: "/tmp/bot.db", Hibernate: true, Status: "ready",
	}); err != nil {
		t.Fatalf("upsert sidecar: %v", err)
	}
	if err := r.SetNextDue("bot.eleven.bdm.sidecar.internal", 100); err != nil {
		t.Fatalf("SetNextDue: %v", err)
	}

	due, err := r.DueInstances(200)
	if err != nil {
		t.Fatalf("DueInstances: %v", err)
	}
	if len(due) != 1 || due[0].Host != "bot.eleven.bdm.sidecar.internal" {
		t.Fatalf("DueInstances = %+v, want the due sidecar — a sidecar's timer wake is its ONLY schedule wake source", due)
	}
}

// TestClearNextDueIfConsumesOnlyTheValueThatFired pins both halves of the
// one-shot rule a sidecar's timer depends on: the fired value is consumed,
// and a value written since (the child's own /v1/next-due, landing while its
// wake was still resolving) is left standing.
func TestClearNextDueIfConsumesOnlyTheValueThatFired(t *testing.T) {
	r := openTestRegistry(t)
	host := "bot.eleven.bdm.sidecar.internal"
	if err := r.Upsert(Route{Host: host, Kind: "sidecar", DBPath: "/tmp/bot.db", Hibernate: true, Status: "ready"}); err != nil {
		t.Fatalf("upsert sidecar: %v", err)
	}
	if err := r.SetNextDue(host, 100); err != nil {
		t.Fatalf("SetNextDue: %v", err)
	}
	// A different value: not the one that fired, so it must survive.
	if err := r.ClearNextDueIf(host, 99); err != nil {
		t.Fatalf("ClearNextDueIf: %v", err)
	}
	if rt, err := r.Get(host); err != nil || rt.NextDue != 100 {
		t.Fatalf("next_due = %+v (%v), want 100 — a row that moved since the wake fired must be left alone", rt, err)
	}
	if err := r.ClearNextDueIf(host, 100); err != nil {
		t.Fatalf("ClearNextDueIf: %v", err)
	}
	if rt, err := r.Get(host); err != nil || rt.NextDue != 0 {
		t.Fatalf("next_due = %+v (%v), want 0 — a fired due time must be consumed by the wake it caused", rt, err)
	}
}

// TestArgsRoundTrips checks Args survives Upsert and can be repointed
// narrowly by SetArgs, the door the sidecar reconcile pass uses when the
// app's manifest argv changes.
func TestArgsRoundTrips(t *testing.T) {
	r := openTestRegistry(t)
	if err := r.Upsert(Route{Host: "bot.x.internal", Kind: "sidecar", Args: `["sidecar","run"]`}); err != nil {
		t.Fatalf("upsert: %v", err)
	}
	got, err := r.Get("bot.x.internal")
	if err != nil {
		t.Fatalf("get: %v", err)
	}
	if got.Args != `["sidecar","run"]` {
		t.Fatalf("Args = %q, want the argv back", got.Args)
	}

	if err := r.SetArgs("bot.x.internal", `["worker","run"]`); err != nil {
		t.Fatalf("SetArgs: %v", err)
	}
	got, _ = r.Get("bot.x.internal")
	if got.Args != `["worker","run"]` {
		t.Fatalf("Args after SetArgs = %q", got.Args)
	}
}

// TestWorkerLimitsRoundTrip checks IdleSeconds/MaxRunSeconds survive Upsert
// and can be repointed narrowly by SetWorkerLimits — the sweeper reads these
// two columns locally on every tick, so a bucket read must never be in that
// hot path.
func TestWorkerLimitsRoundTrip(t *testing.T) {
	r := openTestRegistry(t)
	if err := r.Upsert(Route{Host: "bot.x.internal", Kind: "sidecar", IdleSeconds: 300, MaxRunSeconds: 3600}); err != nil {
		t.Fatalf("upsert: %v", err)
	}
	got, _ := r.Get("bot.x.internal")
	if got.IdleSeconds != 300 || got.MaxRunSeconds != 3600 {
		t.Fatalf("limits = %d/%d, want 300/3600 — the sweeper reads these locally on every tick", got.IdleSeconds, got.MaxRunSeconds)
	}
	if err := r.SetWorkerLimits("bot.x.internal", 60, 120); err != nil {
		t.Fatalf("SetWorkerLimits: %v", err)
	}
	got, _ = r.Get("bot.x.internal")
	if got.IdleSeconds != 60 || got.MaxRunSeconds != 120 {
		t.Fatalf("limits after set = %d/%d", got.IdleSeconds, got.MaxRunSeconds)
	}
}

// TestRouteWrittenBeforeArgsStillParses pins the additive rule: a row
// written by code that predates this column has no args/idle/max-run value,
// and must still read back as the zero value rather than fail to parse.
func TestRouteWrittenBeforeArgsStillParses(t *testing.T) {
	r := openTestRegistry(t)
	if err := r.Upsert(Route{Host: "old.example.com", Kind: "instance"}); err != nil {
		t.Fatalf("upsert: %v", err)
	}
	got, err := r.Get("old.example.com")
	if err != nil {
		t.Fatalf("get: %v", err)
	}
	if got.Args != "" || got.IdleSeconds != 0 || got.MaxRunSeconds != 0 {
		t.Fatalf("row = %+v, want zero values for a pre-existing row", got)
	}
}

// TestUpsertAppliesNewArgsAndLimits (fix round 1, 2026-08-17): a re-Upsert
// carrying a NEW non-empty args/idle/max_run over an existing row must land
// the new values — the sidecar reconcile pass re-Upserts on every pass when
// the app's manifest changes, and omission from the ON CONFLICT clause
// would silently ignore even a genuine new value, not merely preserve an
// old one.
func TestUpsertAppliesNewArgsAndLimits(t *testing.T) {
	r := openTestRegistry(t)
	if err := r.Upsert(Route{
		Host: "bot.y.internal", Kind: "sidecar",
		Args: `["sidecar","run"]`, IdleSeconds: 300, MaxRunSeconds: 3600,
	}); err != nil {
		t.Fatalf("first upsert: %v", err)
	}
	if err := r.Upsert(Route{
		Host: "bot.y.internal", Kind: "sidecar",
		Args: `["sidecar","run","-v"]`, IdleSeconds: 60, MaxRunSeconds: 120,
	}); err != nil {
		t.Fatalf("second upsert: %v", err)
	}
	got, err := r.Get("bot.y.internal")
	if err != nil {
		t.Fatalf("get: %v", err)
	}
	if got.Args != `["sidecar","run","-v"]` || got.IdleSeconds != 60 || got.MaxRunSeconds != 120 {
		t.Fatalf("row = %+v, want the second Upsert's new args/limits to have landed", got)
	}
}

// TestUpsertPreservesArgsAndLimitsWhenCallerKnowsNothingOfThem (fix round 1,
// 2026-08-17): a re-Upsert carrying EMPTY args and ZERO ceilings — the
// `carlos add` shape, which knows nothing about sidecars — over a row that
// already has them must PRESERVE the existing values, not blank them. Same
// preserve-if-empty precedent as `seed` in the same ON CONFLICT clause.
func TestUpsertPreservesArgsAndLimitsWhenCallerKnowsNothingOfThem(t *testing.T) {
	r := openTestRegistry(t)
	if err := r.Upsert(Route{
		Host: "bot.z.internal", Kind: "sidecar",
		Args: `["sidecar","run"]`, IdleSeconds: 300, MaxRunSeconds: 3600,
	}); err != nil {
		t.Fatalf("first upsert: %v", err)
	}
	// The carlos-add shape: a full-row Upsert that names none of these
	// three columns at all.
	if err := r.Upsert(Route{Host: "bot.z.internal", Kind: "sidecar"}); err != nil {
		t.Fatalf("second upsert: %v", err)
	}
	got, err := r.Get("bot.z.internal")
	if err != nil {
		t.Fatalf("get: %v", err)
	}
	if got.Args != `["sidecar","run"]` || got.IdleSeconds != 300 || got.MaxRunSeconds != 3600 {
		t.Fatalf("row = %+v, want the original args/limits preserved, not blanked", got)
	}
}

// #207: the snapshot gate must hold across the stamps the box rewrites on
// its own clock — otherwise every 5-minute tick is a "change" and the
// 8-generation history is 40 minutes deep instead of 8 changes deep. A
// real DNS status flip, by contrast, still moves it.
func TestSnapshotFingerprintIgnoresSchedulingChurn(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{
		Host: "app.example.com", Kind: "instance", App: "app",
		Addr: "127.0.0.1:8091", Status: "ready",
	}); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetDNSStatus("app.example.com", "ok", 1000); err != nil {
		t.Fatal(err)
	}
	before, err := reg.SnapshotFingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.SetNextDue("app.example.com", 4242); err != nil {
		t.Fatal(err)
	}
	if err := reg.SetDNSStatus("app.example.com", "ok", 2000); err != nil {
		t.Fatal(err)
	}
	after, err := reg.SnapshotFingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if after != before {
		t.Fatalf("SnapshotFingerprint moved on next_due/dns_checked_at churn (%s -> %s) — the snapshot pass would write every tick", before, after)
	}
	if err := reg.SetDNSStatus("app.example.com", "mismatch", 3000); err != nil {
		t.Fatal(err)
	}
	flipped, err := reg.SnapshotFingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if flipped == before {
		t.Fatal("a dns_status change is invisible to SnapshotFingerprint")
	}
}

// The skip list is by column NAME, so a churn column must actually exist in
// routeCols — a rename would silently stop excluding it.
func TestSnapshotChurnColsExist(t *testing.T) {
	for col := range snapshotChurnCols {
		if !strings.Contains(routeCols, col) {
			t.Errorf("snapshotChurnCols names %q, which is not in routeCols", col)
		}
	}
}

// Gate round-trips through Upsert (TestUpsertRoundTripsAllColumns is the
// column-shift canary; this pins the column's own semantics), a full-row
// Upsert with an empty gate PRESERVES a set one, and SetGate is the one
// door that clears it.
func TestGateColumnPreservedByUpsertClearedBySetGate(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{Host: "g.example.com", Kind: "static", Status: "ready", Gate: "$2a$10$hash"}); err != nil {
		t.Fatal(err)
	}
	if got := mustRoute(t, reg, "g.example.com").Gate; got != "$2a$10$hash" {
		t.Fatalf("Gate after upsert = %q", got)
	}
	// A writer that knows nothing about the column must not open the host.
	if err := reg.Upsert(Route{Host: "g.example.com", Kind: "static", Status: "ready"}); err != nil {
		t.Fatal(err)
	}
	if got := mustRoute(t, reg, "g.example.com").Gate; got != "$2a$10$hash" {
		t.Fatalf("Gate after a gate-less upsert = %q, want it preserved", got)
	}
	if err := reg.SetGate("g.example.com", ""); err != nil {
		t.Fatal(err)
	}
	if got := mustRoute(t, reg, "g.example.com").Gate; got != "" {
		t.Fatalf("Gate after SetGate(\"\") = %q, want cleared", got)
	}
	if err := reg.SetGate("g.example.com", "$2a$10$other"); err != nil {
		t.Fatal(err)
	}
	if got := mustRoute(t, reg, "g.example.com").Gate; got != "$2a$10$other" {
		t.Fatalf("Gate after SetGate = %q", got)
	}
}

func TestSetGateErrorsOnUnmatchedHost(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.SetGate("nope.example.com", "$2a$10$hash"); err == nil {
		t.Fatal("SetGate on an unmatched host: want error, got nil")
	}
}

// The edge reloads only when Fingerprint moves; a gate set or cleared with
// the column left out would take effect on the next unrelated reload — an
// operator turning the password off and watching the page keep asking.
func TestFingerprintSeesGateChanges(t *testing.T) {
	reg := openTestRegistry(t)
	if err := reg.Upsert(Route{Host: "g.example.com", Kind: "static", Status: "ready"}); err != nil {
		t.Fatal(err)
	}
	fp1, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.SetGate("g.example.com", "$2a$10$hash"); err != nil {
		t.Fatal(err)
	}
	fp2, err := reg.Fingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if fp1 == fp2 {
		t.Fatal("gate change invisible to Fingerprint — the edge would keep serving the old answer")
	}
}

func mustRoute(t *testing.T, reg *Registry, host string) Route {
	t.Helper()
	got, err := reg.Get(host)
	if err != nil {
		t.Fatal(err)
	}
	if got == nil {
		t.Fatalf("no route %s", host)
	}
	return *got
}

// TestSetInstancePathsIsACompareAndSwapOnBothColumns: SetInstancePaths moves
// a row between the flat and the nested layout by one UPDATE guarded by BOTH
// path columns' current values, so a row a concurrent pass already moved,
// reverted or re-added is left exactly as it is rather than overwritten.
func TestSetInstancePathsIsACompareAndSwapOnBothColumns(t *testing.T) {
	r := openTestRegistry(t)
	if err := r.Upsert(Route{Host: "h.example.com", Kind: "instance", Socket: "/run/carlos/h.example.com.sock", DBPath: "/data/h.example.com.db"}); err != nil {
		t.Fatal(err)
	}
	if err := r.SetInstancePaths("h.example.com", "/data/h.example.com.db", "/run/carlos/h.example.com.sock", "/data/instances/h.example.com/h.example.com.db", "/run/carlos/instances/h.example.com/app.sock"); err != nil {
		t.Fatalf("CAS from the current values: %v", err)
	}
	rt := mustRoute(t, r, "h.example.com")
	if rt.DBPath != "/data/instances/h.example.com/h.example.com.db" || rt.Socket != "/run/carlos/instances/h.example.com/app.sock" {
		t.Fatalf("row after CAS = %q %q", rt.DBPath, rt.Socket)
	}
	// A second CAS from the OLD values misses on both columns and names the current ones.
	err := r.SetInstancePaths("h.example.com", "/data/h.example.com.db", "/run/carlos/h.example.com.sock", "/x", "/y")
	if !errors.Is(err, ErrPathsChanged) || !strings.Contains(err.Error(), "/data/instances/h.example.com/h.example.com.db") {
		t.Fatalf("stale CAS = %v", err)
	}
	// A CAS where only ONE column matches also misses: both must agree.
	err = r.SetInstancePaths("h.example.com", "/data/instances/h.example.com/h.example.com.db", "/run/carlos/h.example.com.sock", "/x", "/y")
	if !errors.Is(err, ErrPathsChanged) {
		t.Fatalf("half-matching CAS = %v", err)
	}
	if err := r.SetInstancePaths("absent.example.com", "a", "b", "c", "d"); err == nil || strings.Contains(err.Error(), "changed") {
		t.Fatalf("absent host = %v, want a no-route error", err)
	}
}

// The good build (zero-downtime spec §8.5) has exactly one writer, the agent,
// and every write states what it expects to replace. A full-row Upsert from a
// caller that knows nothing about rollouts (carlos add, a re-mint) must never
// clear it: a lost good build is an instance with nothing to fall back to.
func TestGoodBuildIsWrittenOnlyByItsOwnDoor(t *testing.T) {
	reg, err := Open(filepath.Join(t.TempDir(), "registry.db"))
	if err != nil {
		t.Fatal(err)
	}
	const host = "a.example.com"
	if err := reg.Upsert(Route{Host: host, Kind: "instance", App: "blog", Version: "v1"}); err != nil {
		t.Fatal(err)
	}
	g1 := GoodBuild{Version: "v1", Digest: "aa11"}
	if err := reg.SetGoodBuild(host, GoodBuild{}, g1); err != nil {
		t.Fatalf("first write: %v", err)
	}
	err = reg.SetGoodBuild(host, GoodBuild{}, GoodBuild{Version: "v2", Digest: "bb22"})
	if !errors.Is(err, ErrGoodBuildChanged) {
		t.Fatalf("stale expectation: err = %v, want ErrGoodBuildChanged", err)
	}
	if err := reg.Upsert(Route{Host: host, Kind: "instance", App: "blog", Version: "v2"}); err != nil {
		t.Fatal(err)
	}
	got, err := reg.Get(host)
	if err != nil || got == nil {
		t.Fatalf("Get: %v, %v", got, err)
	}
	if got.Good() != g1 {
		t.Fatalf("Upsert changed the good build to %+v, want %+v", got.Good(), g1)
	}
	if got.Version != "v2" {
		t.Fatalf("Version = %q, want v2", got.Version)
	}
	g2 := GoodBuild{Version: "v2", Digest: "bb22", Demand: "hold"}
	if err := reg.SetGoodBuild(host, g1, g2); err != nil {
		t.Fatal(err)
	}
	routes, err := reg.List()
	if err != nil {
		t.Fatal(err)
	}
	if routes[0].Good() != g2 {
		t.Fatalf("List reads %+v, want %+v", routes[0].Good(), g2)
	}
	err = reg.SetGoodBuild("nope.example.com", GoodBuild{}, g1)
	if err == nil {
		t.Fatal("a missing row must be an error, not a silent no-op")
	}
	if errors.Is(err, ErrGoodBuildChanged) {
		t.Fatalf("a missing row is not a stale expectation: %v", err)
	}
}

// serving_version moves on every wake and swap, with no operator change
// behind it, so it must not trigger a registry snapshot (#207's rule).
func TestServingVersionIsChurnForTheSnapshotGate(t *testing.T) {
	reg, err := Open(filepath.Join(t.TempDir(), "registry.db"))
	if err != nil {
		t.Fatal(err)
	}
	const host = "a.example.com"
	if err := reg.Upsert(Route{Host: host, Kind: "instance", App: "blog"}); err != nil {
		t.Fatal(err)
	}
	before, err := reg.SnapshotFingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.SetServingVersion(host, "v9"); err != nil {
		t.Fatal(err)
	}
	after, err := reg.SnapshotFingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if before != after {
		t.Fatal("SetServingVersion moved the snapshot fingerprint")
	}
	got, _ := reg.Get(host)
	if got.ServingVersion != "v9" {
		t.Fatalf("ServingVersion = %q, want v9", got.ServingVersion)
	}
}

// The good build is restore state, so a change to it must move the snapshot
// fingerprint, even though a registry that never set one hashes as before.
func TestGoodBuildMovesTheSnapshotFingerprint(t *testing.T) {
	reg, err := Open(filepath.Join(t.TempDir(), "registry.db"))
	if err != nil {
		t.Fatal(err)
	}
	const host = "a.example.com"
	if err := reg.Upsert(Route{Host: host, Kind: "instance", App: "blog"}); err != nil {
		t.Fatal(err)
	}
	before, err := reg.SnapshotFingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if err := reg.SetGoodBuild(host, GoodBuild{}, GoodBuild{Version: "v1", Digest: "aa11"}); err != nil {
		t.Fatal(err)
	}
	after, err := reg.SnapshotFingerprint()
	if err != nil {
		t.Fatal(err)
	}
	if before == after {
		t.Fatal("a new good build did not move the snapshot fingerprint")
	}
}