package console

import (
	"path/filepath"
	"reflect"
	"sort"
	"sync/atomic"
	"testing"
	"time"
)

// testAccountID hands out distinct account ids for fixtures that just need an
// account to exist. Real minting is a bucket operation now
// (domains.MintAccount, which allocates the next free integer with a
// create-only manifest write) and most of these tests have no bucket in play;
// they only care that two fixture accounts differ. Starts well above the
// built-in ops id and any hand-written literal in these tests.
var testIDs atomic.Int64

func testAccountID() int64 { return 1000 + testIDs.Add(1) }

func openTestStore(t *testing.T) *Store {
	t.Helper()
	s, err := Open(filepath.Join(t.TempDir(), "console.db"))
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() { s.Close() })
	return s
}

func TestOwnersRoundTrip(t *testing.T) {
	s := openTestStore(t)
	for _, g := range [][2]string{
		{"paul@keymail.dev", "kass"},
		{"paul@keymail.dev", "kass"}, // idempotent
		{"paul@keymail.dev", "aria"},
		{"bob@keymail.dev", "kass"},
	} {
		if err := s.Grant(g[0], g[1]); err != nil {
			t.Fatal(err)
		}
	}
	apps, err := s.AppsFor("paul@keymail.dev")
	if err != nil {
		t.Fatal(err)
	}
	if !reflect.DeepEqual(apps, []string{"aria", "kass"}) {
		t.Fatalf("AppsFor = %v, want [aria kass]", apps)
	}
	owners, err := s.Owners()
	if err != nil {
		t.Fatal(err)
	}
	want := map[string][]string{
		"paul@keymail.dev": {"aria", "kass"},
		"bob@keymail.dev":  {"kass"},
	}
	if !reflect.DeepEqual(owners, want) {
		t.Fatalf("Owners = %v, want %v", owners, want)
	}
	if err := s.Revoke("paul@keymail.dev", "kass"); err != nil {
		t.Fatal(err)
	}
	apps, _ = s.AppsFor("paul@keymail.dev")
	if !reflect.DeepEqual(apps, []string{"aria"}) {
		t.Fatalf("after revoke AppsFor = %v, want [aria]", apps)
	}
	if apps, _ := s.AppsFor("nobody@keymail.dev"); len(apps) != 0 {
		t.Fatalf("AppsFor unknown = %v, want empty", apps)
	}
}

func TestSessions(t *testing.T) {
	s := openTestStore(t)
	now := time.Now()
	raw, err := s.CreateSession("paul@keymail.dev", "paul.km-token", now)
	if err != nil {
		t.Fatal(err)
	}
	if raw == "" {
		t.Fatal("empty raw session token")
	}
	if addr, ok := s.SessionAddress(raw, now.Add(time.Hour)); !ok || addr != "paul@keymail.dev" {
		t.Fatalf("SessionAddress = %q %v, want paul@keymail.dev true", addr, ok)
	}
	if _, ok := s.SessionAddress("not-a-token", now); ok {
		t.Fatal("bogus token resolved")
	}
	if _, ok := s.SessionAddress(raw, now.Add(SessionTTL+time.Minute)); ok {
		t.Fatal("expired session resolved")
	}
	if err := s.DeleteSession(raw); err != nil {
		t.Fatal(err)
	}
	if _, ok := s.SessionAddress(raw, now); ok {
		t.Fatal("deleted session resolved")
	}
}

func TestPurgeExpired(t *testing.T) {
	s := openTestStore(t)
	now := time.Now()
	raw, err := s.CreateSession("paul@keymail.dev", "", now)
	if err != nil {
		t.Fatal(err)
	}
	conf, err := s.CreateConfirmation("paul@keymail.dev", now)
	if err != nil {
		t.Fatal(err)
	}
	if err := s.PurgeExpired(now.Add(SessionTTL + time.Hour)); err != nil {
		t.Fatal(err)
	}
	if _, ok := s.SessionAddress(raw, now); ok {
		t.Fatal("purged session still resolves at an in-window time")
	}
	if _, ok := s.TakeConfirmation(conf, now); ok {
		t.Fatal("purged confirmation still takeable")
	}
}

func TestEnsureUser(t *testing.T) {
	s := openTestStore(t)
	now := time.Now()
	created, err := s.EnsureUser("paul@keymail.dev", now)
	if err != nil || !created {
		t.Fatalf("first EnsureUser = %v %v, want true nil", created, err)
	}
	created, err = s.EnsureUser("paul@keymail.dev", now.Add(time.Hour))
	if err != nil || created {
		t.Fatalf("second EnsureUser = %v %v, want false nil", created, err)
	}
}

func TestGrantToken(t *testing.T) {
	s := openTestStore(t)
	now := time.Now()
	if tok, err := s.GrantToken("paul@keymail.dev"); err != nil || tok != "" {
		t.Fatalf("GrantToken unknown = %q %v, want empty nil", tok, err)
	}
	if err := s.SetGrantToken("paul@keymail.dev", "paul.tok1", now); err != nil {
		t.Fatal(err)
	}
	if tok, _ := s.GrantToken("paul@keymail.dev"); tok != "paul.tok1" {
		t.Fatalf("GrantToken = %q, want paul.tok1", tok)
	}
	// Re-authorizing rotates the token.
	if err := s.SetGrantToken("paul@keymail.dev", "paul.tok2", now.Add(time.Hour)); err != nil {
		t.Fatal(err)
	}
	if tok, _ := s.GrantToken("paul@keymail.dev"); tok != "paul.tok2" {
		t.Fatalf("rotated GrantToken = %q, want paul.tok2", tok)
	}
}

func TestConfirmationSingleUse(t *testing.T) {
	s := openTestStore(t)
	now := time.Now()
	raw, err := s.CreateConfirmation("paul@keymail.dev", now)
	if err != nil {
		t.Fatal(err)
	}
	addr, ok := s.TakeConfirmation(raw, now.Add(time.Minute))
	if !ok || addr != "paul@keymail.dev" {
		t.Fatalf("TakeConfirmation = %q %v, want paul@keymail.dev true", addr, ok)
	}
	if _, ok := s.TakeConfirmation(raw, now.Add(time.Minute)); ok {
		t.Fatal("confirmation token worked twice")
	}
}

func TestConfirmationExpiresAndBurns(t *testing.T) {
	s := openTestStore(t)
	now := time.Now()
	raw, err := s.CreateConfirmation("paul@keymail.dev", now)
	if err != nil {
		t.Fatal(err)
	}
	if _, ok := s.TakeConfirmation(raw, now.Add(ConfirmationTTL+time.Minute)); ok {
		t.Fatal("expired confirmation accepted")
	}
	// Burn-then-check: even at a valid time it is now gone.
	if _, ok := s.TakeConfirmation(raw, now); ok {
		t.Fatal("expired take did not burn the token")
	}
}

func TestSigningKeyStableAcrossReopen(t *testing.T) {
	path := filepath.Join(t.TempDir(), "console.db")
	s, err := Open(path)
	if err != nil {
		t.Fatal(err)
	}
	k1, err := s.SigningKey()
	if err != nil {
		t.Fatal(err)
	}
	k2, err := s.SigningKey()
	if err != nil {
		t.Fatal(err)
	}
	if !k1.Equal(k2) {
		t.Fatal("two SigningKey calls returned different keys")
	}
	s.Close()
	s2, err := Open(path)
	if err != nil {
		t.Fatal(err)
	}
	defer s2.Close()
	k3, err := s2.SigningKey()
	if err != nil {
		t.Fatal(err)
	}
	if !k1.Equal(k3) {
		t.Fatal("signing key changed across reopen")
	}
}

func TestReopenMigrationIdempotent(t *testing.T) {
	path := filepath.Join(t.TempDir(), "console.db")
	s, err := Open(path)
	if err != nil {
		t.Fatal(err)
	}
	if err := s.Grant("paul@keymail.dev", "kass"); err != nil {
		t.Fatal(err)
	}
	s.Close()
	s2, err := Open(path)
	if err != nil {
		t.Fatalf("reopen: %v", err)
	}
	defer s2.Close()
	apps, err := s2.AppsFor("paul@keymail.dev")
	if err != nil || len(apps) != 1 {
		t.Fatalf("after reopen AppsFor = %v %v", apps, err)
	}
}

func TestAuditEvents(t *testing.T) {
	s := openTestStore(t)
	base := time.Date(2026, 8, 1, 12, 0, 0, 0, time.UTC)
	for i, ev := range []Event{
		{App: "kass", Address: "paul@keymail.dev", Action: "promote", Detail: "promoted v1 to stable"},
		{App: "kass", Address: "paul@keymail.dev", Action: "canary-create", Detail: "created canary rc at v2"},
		{App: "aria", Address: "bob@keymail.dev", Action: "promote", Detail: "promoted z1 to stable"},
	} {
		if err := s.RecordEvent(OpsAccountID, ev.App, ev.Address, ev.Action, ev.Detail, base.Add(time.Duration(i)*time.Minute)); err != nil {
			t.Fatal(err)
		}
	}
	events, err := s.Events(OpsAccountID, "kass", 10)
	if err != nil {
		t.Fatal(err)
	}
	if len(events) != 2 {
		t.Fatalf("Events(kass) = %+v, want 2 rows", events)
	}
	// Newest first, and no cross-app leakage.
	if events[0].Action != "canary-create" || events[1].Action != "promote" {
		t.Fatalf("order = %s, %s — want canary-create first", events[0].Action, events[1].Action)
	}
	if events[0].Address != "paul@keymail.dev" || events[0].Detail != "created canary rc at v2" {
		t.Fatalf("event = %+v", events[0])
	}
	if !events[0].CreatedAt.Equal(base.Add(time.Minute)) {
		t.Fatalf("CreatedAt = %v, want %v", events[0].CreatedAt, base.Add(time.Minute))
	}
	// The cap trims oldest-first.
	if events, _ := s.Events(OpsAccountID, "kass", 1); len(events) != 1 || events[0].Action != "canary-create" {
		t.Fatalf("Events(kass, 1) = %+v", events)
	}
	if events, _ := s.Events(OpsAccountID, "ghost", 10); len(events) != 0 {
		t.Fatalf("Events(ghost) = %+v, want empty", events)
	}
}

func TestStructuredActivityRoundTrip(t *testing.T) {
	s := openTestStore(t)
	now := time.Date(2026, 9, 8, 9, 30, 0, 0, time.UTC)
	want := Activity{
		Action: "instance-create", TargetType: "instance",
		Target: "web.example.test", Message: "add the public web copy",
		Detail: "created instance web.example.test (cli)",
	}
	if err := s.RecordActivity(OpsAccountID, "kass", "paul@keymail.dev", want, now); err != nil {
		t.Fatal(err)
	}
	events, err := s.Events(OpsAccountID, "kass", 1)
	if err != nil || len(events) != 1 {
		t.Fatalf("Events = %+v, %v", events, err)
	}
	got := events[0]
	if got.Action != want.Action || got.TargetType != want.TargetType || got.Target != want.Target ||
		got.Message != want.Message || got.Detail != want.Detail {
		t.Fatalf("event = %+v, want activity %+v", got, want)
	}
	if !got.CreatedAt.Equal(now) {
		t.Fatalf("CreatedAt = %v, want %v", got.CreatedAt, now)
	}
}

func TestOperatorRoundTrip(t *testing.T) {
	s := openTestStore(t)
	if op, err := s.IsOperator("paul@keymail.dev"); err != nil || op {
		t.Fatalf("IsOperator before any grant = %v %v, want false nil", op, err)
	}
	// Box-side only: SetOperator works even though this address has never
	// signed in (no users row from EnsureUser), exactly like an app grant.
	if err := s.SetOperator("paul@keymail.dev", true); err != nil {
		t.Fatal(err)
	}
	if op, err := s.IsOperator("paul@keymail.dev"); err != nil || !op {
		t.Fatalf("IsOperator after grant = %v %v, want true nil", op, err)
	}
	if op, err := s.IsOperator("bob@keymail.dev"); err != nil || op {
		t.Fatalf("IsOperator(bob) = %v %v, want false nil", op, err)
	}
	// A real sign-up (EnsureUser) after the operator bit was set must not
	// clobber it back to 0.
	if _, err := s.EnsureUser("paul@keymail.dev", time.Now()); err != nil {
		t.Fatal(err)
	}
	if op, err := s.IsOperator("paul@keymail.dev"); err != nil || !op {
		t.Fatalf("IsOperator after EnsureUser = %v %v, want true nil (grant survives sign-up)", op, err)
	}
	if err := s.SetOperator("paul@keymail.dev", false); err != nil {
		t.Fatal(err)
	}
	if op, err := s.IsOperator("paul@keymail.dev"); err != nil || op {
		t.Fatalf("IsOperator after revoke = %v %v, want false nil", op, err)
	}
}

func TestOperatorMigrationIdempotent(t *testing.T) {
	path := filepath.Join(t.TempDir(), "console.db")
	s, err := Open(path)
	if err != nil {
		t.Fatal(err)
	}
	if err := s.SetOperator("paul@keymail.dev", true); err != nil {
		t.Fatal(err)
	}
	s.Close()
	// Re-running migrate (every Open) must not fail on the operator column
	// already existing, and the bit must survive the reopen.
	s2, err := Open(path)
	if err != nil {
		t.Fatalf("reopen: %v", err)
	}
	defer s2.Close()
	if op, err := s2.IsOperator("paul@keymail.dev"); err != nil || !op {
		t.Fatalf("IsOperator after reopen = %v %v, want true nil", op, err)
	}
}

// TestOperatorsList: the alert watcher's recipient list is exactly the
// addresses holding the operator bit, sorted, and revoking removes you.
func TestOperatorsList(t *testing.T) {
	s := openTestStore(t)
	if ops, err := s.Operators(); err != nil || len(ops) != 0 {
		t.Fatalf("Operators on an empty store = %v %v, want none", ops, err)
	}
	for _, a := range []string{"zoe@keymail.dev", "amy@keymail.dev"} {
		if err := s.SetOperator(a, true); err != nil {
			t.Fatal(err)
		}
	}
	// A plain user (never an operator) is not on the list.
	if _, err := s.EnsureUser("bob@keymail.dev", time.Now()); err != nil {
		t.Fatal(err)
	}
	ops, err := s.Operators()
	if err != nil {
		t.Fatal(err)
	}
	if !reflect.DeepEqual(ops, []string{"amy@keymail.dev", "zoe@keymail.dev"}) {
		t.Fatalf("Operators = %v", ops)
	}
	if err := s.SetOperator("zoe@keymail.dev", false); err != nil {
		t.Fatal(err)
	}
	if ops, _ := s.Operators(); !reflect.DeepEqual(ops, []string{"amy@keymail.dev"}) {
		t.Fatalf("Operators after revoke = %v", ops)
	}
}

// TestAlertSeenRoundTrip: the watcher's memory, one row per recipient.
// "Never told them" and "told them it was quiet" are different answers —
// the first is news on a fire, the second is what makes the second pass
// quiet — and one operator's row must never answer for another's.
func TestAlertSeenRoundTrip(t *testing.T) {
	s := openTestStore(t)
	const amy, zoe = "amy@keymail.dev", "zoe@keymail.dev"
	if firing, known, err := s.AlertSeen("box-a", "disk", amy); err != nil || known || firing {
		t.Fatalf("AlertSeen before anything = %v %v %v, want false false nil", firing, known, err)
	}
	if err := s.SetAlertSeen("box-a", "disk", amy, true); err != nil {
		t.Fatal(err)
	}
	if firing, known, err := s.AlertSeen("box-a", "disk", amy); err != nil || !known || !firing {
		t.Fatalf("AlertSeen after fire = %v %v %v, want true true nil", firing, known, err)
	}
	// Same (box, rule, address) upserts rather than duplicating.
	if err := s.SetAlertSeen("box-a", "disk", amy, false); err != nil {
		t.Fatal(err)
	}
	if firing, known, err := s.AlertSeen("box-a", "disk", amy); err != nil || !known || firing {
		t.Fatalf("AlertSeen after clear = %v %v %v, want false true nil", firing, known, err)
	}
	// Rules, boxes and addresses are all independent parts of the key.
	if _, known, _ := s.AlertSeen("box-a", "mem", amy); known {
		t.Fatal("AlertSeen(box-a, mem) is known — the rule key is not being used")
	}
	if _, known, _ := s.AlertSeen("box-b", "disk", amy); known {
		t.Fatal("AlertSeen(box-b, disk) is known — the box key is not being used")
	}
	if _, known, _ := s.AlertSeen("box-a", "disk", zoe); known {
		t.Fatal("AlertSeen(box-a, disk, zoe) is known — amy's row is answering for zoe")
	}
	// The synthetic "nobody was mailable" recipient is its own row.
	if err := s.SetAlertSeen("box-a", "disk", "", true); err != nil {
		t.Fatal(err)
	}
	if firing, known, err := s.AlertSeen("box-a", "disk", ""); err != nil || !known || !firing {
		t.Fatalf("AlertSeen for the empty address = %v %v %v, want true true nil", firing, known, err)
	}
	if firing, _, _ := s.AlertSeen("box-a", "disk", amy); firing {
		t.Fatal("the synthetic row overwrote amy's")
	}
}

// TestTrashAppRowRoundTrip: a trashed app vanishes from the live listings but
// stays visible — and its name stays claimed — in the trashed ones, until
// restore or purge.
func TestTrashAppRowRoundTrip(t *testing.T) {
	s := openTestStore(t)
	now := time.Now()
	acct := testAccountID()
	if created, err := s.CreateAppIn(acct, "zephyr", "paul@keymail.dev", now); err != nil || !created {
		t.Fatalf("CreateAppIn = %v %v, want true nil", created, err)
	}
	if err := s.TrashAppRow(acct, "zephyr", now); err != nil {
		t.Fatal(err)
	}
	// Gone from the live doors.
	if apps, err := s.AppsIn(acct); err != nil || len(apps) != 0 {
		t.Fatalf("AppsIn after trash = %v %v, want empty", apps, err)
	}
	if has, err := s.HasAppIn(acct, "zephyr"); err != nil || has {
		t.Fatalf("HasAppIn after trash = %v %v, want false", has, err)
	}
	// Still visible via the trash doors.
	if has, err := s.HasTrashedAppIn(acct, "zephyr"); err != nil || !has {
		t.Fatalf("HasTrashedAppIn after trash = %v %v, want true", has, err)
	}
	trashed, err := s.TrashedAppsIn(acct)
	if err != nil || len(trashed) != 1 || trashed[0].App != "zephyr" {
		t.Fatalf("TrashedAppsIn = %+v %v, want one zephyr row", trashed, err)
	}
	if !trashed[0].DeletedAt.Equal(time.Unix(now.Unix(), 0).UTC()) {
		t.Fatalf("TrashedAppsIn DeletedAt = %v, want %v", trashed[0].DeletedAt, now)
	}
	// The name is still claimed — claim-once still refuses it. The PK row
	// exists; this is not a fresh claim succeeding by accident.
	if created, err := s.CreateAppIn(acct, "zephyr", "bob@keymail.dev", now); err != nil || created {
		t.Fatalf("CreateAppIn on a trashed name = %v %v, want false nil", created, err)
	}
	// Trashing again does NOT move the cooloff clock.
	later := now.Add(time.Hour)
	if err := s.TrashAppRow(acct, "zephyr", later); err != nil {
		t.Fatal(err)
	}
	trashed, err = s.TrashedAppsIn(acct)
	if err != nil || len(trashed) != 1 || !trashed[0].DeletedAt.Equal(time.Unix(now.Unix(), 0).UTC()) {
		t.Fatalf("second TrashAppRow moved the timestamp: %+v %v", trashed, err)
	}
	// Restore: visible again everywhere live, gone from trash.
	if err := s.RestoreAppRow(acct, "zephyr"); err != nil {
		t.Fatal(err)
	}
	if apps, err := s.AppsIn(acct); err != nil || len(apps) != 1 || apps[0] != "zephyr" {
		t.Fatalf("AppsIn after restore = %v %v, want [zephyr]", apps, err)
	}
	if has, err := s.HasTrashedAppIn(acct, "zephyr"); err != nil || has {
		t.Fatalf("HasTrashedAppIn after restore = %v %v, want false", has, err)
	}
	// Purge: the row is gone outright, and the name frees for reclaiming.
	if err := s.TrashAppRow(acct, "zephyr", now); err != nil {
		t.Fatal(err)
	}
	if err := s.PurgeAppRow(acct, "zephyr"); err != nil {
		t.Fatal(err)
	}
	if has, err := s.HasTrashedAppIn(acct, "zephyr"); err != nil || has {
		t.Fatalf("HasTrashedAppIn after purge = %v %v, want false", has, err)
	}
	if created, err := s.CreateAppIn(acct, "zephyr", "carol@keymail.dev", now); err != nil || !created {
		t.Fatalf("CreateAppIn after purge = %v %v, want true nil — the name should be free", created, err)
	}
}

// TestAppPlacementRoundTrip: a fresh app reads unplaced, SetAppPlacement
// stamps a target AppPlacement reads back, and clearing (target "") returns
// it to unplaced — the store-of-record fleets.go's appPlacement now
// consults first (2026-08-06 ruling), ahead of the older route-derived
// fallback.
func TestAppPlacementRoundTrip(t *testing.T) {
	s := openTestStore(t)
	now := time.Now()
	acct := testAccountID()
	if _, err := s.CreateAppIn(acct, "titogo", "paul@keymail.dev", now); err != nil {
		t.Fatal(err)
	}
	if p, err := s.AppPlacement(acct, "titogo"); err != nil || p != "" {
		t.Fatalf("AppPlacement of a fresh app = %q %v, want \"\" nil", p, err)
	}
	// An app this store has no row for at all also reads as unplaced, not an
	// error.
	if p, err := s.AppPlacement(acct, "no-such-app"); err != nil || p != "" {
		t.Fatalf("AppPlacement of an unknown app = %q %v, want \"\" nil", p, err)
	}
	if err := s.SetAppPlacement(acct, "titogo", "fleet/pi"); err != nil {
		t.Fatal(err)
	}
	if p, err := s.AppPlacement(acct, "titogo"); err != nil || p != "fleet/pi" {
		t.Fatalf("AppPlacement after set = %q %v, want fleet/pi", p, err)
	}
	if err := s.SetAppPlacement(acct, "titogo", ""); err != nil {
		t.Fatal(err)
	}
	if p, err := s.AppPlacement(acct, "titogo"); err != nil || p != "" {
		t.Fatalf("AppPlacement after clear = %q %v, want \"\"", p, err)
	}
}

// TestAppCountCountsTrashedRows: account deletion's pre-flight must keep
// refusing while a trashed app's payload still exists, so the count is
// deliberately unfiltered by deleted_at.
// TestPurgeAppRowRefusesLiveRow: purge is the one irreversible verb here, so a
// live row must be structurally unpurgeable even if a caller gets confused —
// the console row and a bucket marker can diverge (a restore that raced or
// out-lived a failed marker delete elsewhere), and that must never be the
// thing that frees a serving app's name.
func TestPurgeAppRowRefusesLiveRow(t *testing.T) {
	s := openTestStore(t)
	now := time.Now()
	acct := testAccountID()
	if _, err := s.CreateAppIn(acct, "kass", "paul@keymail.dev", now); err != nil {
		t.Fatal(err)
	}
	if err := s.TrashAppRow(acct, "kass", now); err != nil {
		t.Fatal(err)
	}
	if err := s.RestoreAppRow(acct, "kass"); err != nil {
		t.Fatal(err)
	}
	// Live again: PurgeAppRow must refuse, not free the name.
	if err := s.PurgeAppRow(acct, "kass"); err != nil {
		t.Fatal(err)
	}
	if has, err := s.HasAppIn(acct, "kass"); err != nil || !has {
		t.Fatalf("HasAppIn after purging a restored (live) row = %v %v, want true — the row must survive", has, err)
	}
	if created, err := s.CreateAppIn(acct, "kass", "bob@keymail.dev", now); err != nil || created {
		t.Fatalf("CreateAppIn after purging a live row = %v %v, want false — the name is still reserved", created, err)
	}
	// Trashed: PurgeAppRow now takes effect.
	if err := s.TrashAppRow(acct, "kass", now); err != nil {
		t.Fatal(err)
	}
	if err := s.PurgeAppRow(acct, "kass"); err != nil {
		t.Fatal(err)
	}
	if has, err := s.HasTrashedAppIn(acct, "kass"); err != nil || has {
		t.Fatalf("HasTrashedAppIn after purging a trashed row = %v %v, want false — the row should be gone", has, err)
	}
	if created, err := s.CreateAppIn(acct, "kass", "bob@keymail.dev", now); err != nil || !created {
		t.Fatalf("CreateAppIn after purge = %v %v, want true — the name should be free", created, err)
	}
}

func TestAppCountCountsTrashedRows(t *testing.T) {
	s := openTestStore(t)
	now := time.Now()
	acct := testAccountID()
	if _, err := s.CreateAppIn(acct, "kass", "paul@keymail.dev", now); err != nil {
		t.Fatal(err)
	}
	if err := s.TrashAppRow(acct, "kass", now); err != nil {
		t.Fatal(err)
	}
	if n, err := s.AppCount(acct); err != nil || n != 1 {
		t.Fatalf("AppCount with only a trashed row = %d %v, want 1", n, err)
	}
}

// TestDeleteAccountMarksNotRemoves: the console must remember tombstones —
// deleting the row would forget the account was ever seen and re-arm the
// resurrection attack the spec's §8 describes (see DeleteAccount's comment).
func TestDeleteAccountMarksNotRemoves(t *testing.T) {
	s := openTestStore(t)
	now := time.Now()
	id := testAccountID()
	sqid := "test-del-1"
	if err := s.CreateAccount(id, sqid, "Testco", "paul@keymail.dev", now); err != nil {
		t.Fatal(err)
	}
	if err := s.DeleteAccount(id, now); err != nil {
		t.Fatal(err)
	}
	// Invisible via every filtered door.
	if a, err := s.AccountBySqid(sqid); err != nil || a != nil {
		t.Fatalf("AccountBySqid after delete = %+v %v, want nil nil", a, err)
	}
	if a, err := s.AccountByID(id); err != nil || a != nil {
		t.Fatalf("AccountByID after delete = %+v %v, want nil nil", a, err)
	}
	// But the console still remembers it: the row is marked, not gone.
	a, deleted, err := s.AccountAnyBySqid(sqid)
	if err != nil {
		t.Fatal(err)
	}
	if a == nil {
		t.Fatal("AccountAnyBySqid after delete = nil, want the marked row")
	}
	if !deleted {
		t.Fatal("AccountAnyBySqid after delete reports deleted=false")
	}
	if a.ID != id || a.Sqid != sqid || a.Name != "Testco" {
		t.Fatalf("AccountAnyBySqid = %+v, want id=%d sqid=%s name=Testco", a, id, sqid)
	}
}

func TestDeleteAccountRefusesOps(t *testing.T) {
	s := openTestStore(t)
	if err := s.DeleteAccount(OpsAccountID, time.Now()); err == nil {
		t.Fatal("DeleteAccount(ops) succeeded, want a refusal")
	}
}

// TestReapAccountClearsAndMarks: materialization's verb for a manifest
// tombstoned on a LATER pass — memberships, invites and apps rows are
// deleted outright, last_account references are cleared, and the accounts
// row is marked, never removed, for the same tombstone-memory reason
// DeleteAccount marks it.
func TestReapAccountClearsAndMarks(t *testing.T) {
	s := openTestStore(t)
	now := time.Now()
	id := testAccountID()
	sqid := "test-reap-1"
	if err := s.CreateAccount(id, sqid, "Reapco", "paul@keymail.dev", now); err != nil {
		t.Fatal(err)
	}
	if err := s.AddMember(id, "bob@keymail.dev", RoleMember, now); err != nil {
		t.Fatal(err)
	}
	if _, err := s.CreateAppIn(id, "leftover", "paul@keymail.dev", now); err != nil {
		t.Fatal(err)
	}
	if _, err := s.CreateInvite(id, "carol@keymail.dev", "paul@keymail.dev", now); err != nil {
		t.Fatal(err)
	}
	if err := s.SetLastAccount("bob@keymail.dev", id); err != nil {
		t.Fatal(err)
	}

	if err := s.ReapAccount(id, now); err != nil {
		t.Fatal(err)
	}

	if role, err := s.Role(id, "paul@keymail.dev"); err != nil || role != "" {
		t.Fatalf("Role(owner) after reap = %q %v, want empty", role, err)
	}
	if role, err := s.Role(id, "bob@keymail.dev"); err != nil || role != "" {
		t.Fatalf("Role(member) after reap = %q %v, want empty", role, err)
	}
	if apps, err := s.AppsIn(id); err != nil || len(apps) != 0 {
		t.Fatalf("AppsIn after reap = %v %v, want empty", apps, err)
	}
	if trashed, err := s.TrashedAppsIn(id); err != nil || len(trashed) != 0 {
		t.Fatalf("TrashedAppsIn after reap = %v %v, want empty — reap deletes app rows, it does not trash them", trashed, err)
	}
	if invites, err := s.PendingInvites(id); err != nil || len(invites) != 0 {
		t.Fatalf("PendingInvites after reap = %v %v, want empty", invites, err)
	}
	if last, err := s.LastAccount("bob@keymail.dev"); err != nil || last != 0 {
		t.Fatalf("LastAccount after reap = %d %v, want 0", last, err)
	}
	if a, err := s.AccountBySqid(sqid); err != nil || a != nil {
		t.Fatalf("AccountBySqid after reap = %+v %v, want nil nil", a, err)
	}
	a, deleted, err := s.AccountAnyBySqid(sqid)
	if err != nil || a == nil || !deleted {
		t.Fatalf("AccountAnyBySqid after reap = %+v %v %v, want a marked row", a, deleted, err)
	}
}

func TestReapAccountRefusesOps(t *testing.T) {
	s := openTestStore(t)
	if err := s.ReapAccount(OpsAccountID, time.Now()); err == nil {
		t.Fatal("ReapAccount(ops) succeeded, want a refusal")
	}
}

func TestCreateAppClaimOnce(t *testing.T) {
	s := openTestStore(t)
	now := time.Now()
	created, err := s.CreateApp("zephyr", "paul@keymail.dev", now)
	if err != nil || !created {
		t.Fatalf("first CreateApp = %v %v, want true nil", created, err)
	}
	created, err = s.CreateApp("zephyr", "bob@keymail.dev", now)
	if err != nil || created {
		t.Fatalf("second CreateApp = %v %v, want false nil", created, err)
	}
	if has, _ := s.HasApp("zephyr"); !has {
		t.Fatal("HasApp(zephyr) = false after claim")
	}
	if has, _ := s.HasApp("ghost"); has {
		t.Fatal("HasApp(ghost) = true")
	}
	// HasOwners sees CLI-style grants independently of the apps table.
	if has, _ := s.HasOwners("kass"); has {
		t.Fatal("HasOwners before any grant")
	}
	if err := s.Grant("paul@keymail.dev", "kass"); err != nil {
		t.Fatal(err)
	}
	if has, _ := s.HasOwners("kass"); !has {
		t.Fatal("HasOwners(kass) = false after grant")
	}
}

// AppsWithActionSince answers each (account, app) with a row of the action
// at or after since, once, by the account's sqid. Older rows and other
// actions are left out.
func TestAppsWithActionSince(t *testing.T) {
	t.Parallel()
	s := openTestStore(t)
	now := time.Now()
	one, other := testAccountID(), testAccountID()
	for id, sqid := range map[int64]string{one: "acct-one", other: "acct-other"} {
		if err := s.CreateAccount(id, sqid, sqid, "bob@example.com", now); err != nil {
			t.Fatal(err)
		}
	}
	for _, r := range []struct {
		acct   int64
		app    string
		action string
		at     time.Time
	}{
		{one, "shop", "run-start", now.Add(-time.Hour)},
		{one, "shop", "run-start", now.Add(-2 * time.Hour)}, // the same pair again
		{one, "cafe", "run-start", now.Add(-49 * time.Hour)},
		{one, "blog", "run-end", now.Add(-time.Hour)},
		{one, "edge", "run-start", now.Add(-48 * time.Hour)}, // exactly since: in
		{other, "shop", "run-start", now.Add(-time.Minute)},
	} {
		if err := s.RecordActivity(r.acct, r.app, "bob@example.com", Activity{Action: r.action}, r.at); err != nil {
			t.Fatal(err)
		}
	}
	got, err := s.AppsWithActionSince("run-start", now.Add(-48*time.Hour))
	if err != nil {
		t.Fatal(err)
	}
	want := []AppRef{{Sqid: "acct-one", App: "edge"}, {Sqid: "acct-one", App: "shop"}, {Sqid: "acct-other", App: "shop"}}
	sort.Slice(got, func(i, j int) bool { return got[i].Sqid+"/"+got[i].App < got[j].Sqid+"/"+got[j].App })
	sort.Slice(want, func(i, j int) bool { return want[i].Sqid+"/"+want[i].App < want[j].Sqid+"/"+want[j].App })
	if !reflect.DeepEqual(got, want) {
		t.Fatalf("AppsWithActionSince = %+v, want %+v", got, want)
	}
}