package console

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"path/filepath"
	"strings"
	"sync/atomic"
	"testing"
	"time"
)

func allowAllClaimSQL(context.Context, claimSQLScope) error { return nil }

func gateCtx(t *testing.T) context.Context {
	t.Helper()
	ctx, cancel := context.WithTimeout(context.Background(), 20*time.Second)
	t.Cleanup(cancel)
	return ctx
}

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

func openGatedCustom(t *testing.T, tables map[string]claimSQLTable, ddl ...string) *Store {
	t.Helper()
	path := filepath.Join(t.TempDir(), "custom.db")
	s, err := openGated(path, "file:"+path+"?_pragma=foreign_keys(1)", tables, func(s *Store) error {
		for _, d := range ddl {
			if _, err := s.db.Exec(d); err != nil {
				return err
			}
		}
		return nil
	})
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() { s.Close() })
	return s
}

func requireDenied(t *testing.T, what string, err error) {
	t.Helper()
	if err == nil {
		t.Fatalf("%s: succeeded, want a claim denial", what)
	}
	if !strings.Contains(err.Error(), claimDenyMessage) {
		t.Fatalf("%s: error %v, want %q", what, err, claimDenyMessage)
	}
}

func drainRows(rows *sql.Rows, err error) error {
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
	}
	return rows.Err()
}

func countWhere(t *testing.T, s *Store, table, where string, args ...any) int {
	t.Helper()
	var n int
	if err := s.db.QueryRow(`SELECT count(*) FROM `+table+` WHERE `+where, args...).Scan(&n); err != nil {
		t.Fatal(err)
	}
	return n
}

const gateAcct = int64(7)

var gateScope = claimSQLScope{AccountID: gateAcct, App: "blog", AppID: "app-A", Mode: claimModeEffect}

// seedGateApp writes the account row (not app-scoped) and a full set of app
// rows for gateScope, under a permit.
func seedGateApp(t *testing.T, s *Store) {
	t.Helper()
	if _, err := s.db.Exec(`INSERT INTO accounts (id, sqid, name, created_at) VALUES (?, 'acct7', 'seven', 1)`, gateAcct); err != nil {
		t.Fatal(err)
	}
	err := s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, allowAllClaimSQL, func(v *Store) error {
		for _, q := range []string{
			`INSERT INTO apps (app, account_id, created_by, created_at, app_id) VALUES ('blog', 7, 'p@x', 1, 'app-A')`,
			`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p@x', 'seed', 1, 7)`,
			`INSERT INTO door_keys (account_id, app, id, sha256, created_at) VALUES (7, 'blog', 'k1', 'h', 1)`,
		} {
			if _, err := v.db.Exec(q); err != nil {
				return err
			}
		}
		return nil
	})
	if err != nil {
		t.Fatal(err)
	}
}

// ---- unauthorized writes ------------------------------------------------------

func TestClaimSQLUnauthorizedWritesDenied(t *testing.T) {
	s := openGatedStore(t)
	seedGateApp(t, s)
	ctx := gateCtx(t)

	t.Run("Exec", func(t *testing.T) {
		_, err := s.db.Exec(`INSERT INTO apps (app, account_id, created_by, created_at) VALUES ('evil', 7, 'x', 1)`)
		requireDenied(t, "insert", err)
		_, err = s.db.Exec(`UPDATE apps SET placement = 'z' WHERE app = 'blog'`)
		requireDenied(t, "update", err)
		_, err = s.db.Exec(`DELETE FROM apps WHERE app = 'blog'`)
		requireDenied(t, "delete", err)
		_, err = s.db.Exec(`INSERT INTO owners (address, app, created_at) VALUES ('p@x', 'blog', 1)`)
		requireDenied(t, "insert owners", err)
	})
	t.Run("ExecContext", func(t *testing.T) {
		_, err := s.db.ExecContext(ctx, `UPDATE audit SET action = 'forged'`)
		requireDenied(t, "update audit", err)
		_, err = s.db.ExecContext(ctx, `DELETE FROM door_keys`)
		requireDenied(t, "delete door_keys", err)
	})
	t.Run("prepared", func(t *testing.T) {
		st, err := s.db.Prepare(`DELETE FROM audit WHERE app = ?`)
		if err != nil {
			t.Fatal(err)
		}
		defer st.Close()
		_, err = st.Exec("blog")
		requireDenied(t, "prepared delete", err)
		st2, err := s.db.PrepareContext(ctx, `INSERT INTO audit (app, address, action, created_at, account_id) VALUES (?, 'x', 'a', 1, 7)`)
		if err != nil {
			t.Fatal(err)
		}
		defer st2.Close()
		_, err = st2.ExecContext(ctx, "blog")
		requireDenied(t, "prepared insert", err)
	})
	t.Run("transaction", func(t *testing.T) {
		tx, err := s.db.Begin()
		if err != nil {
			t.Fatal(err)
		}
		_, err = tx.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'x', 'tx', 1, 7)`)
		requireDenied(t, "tx insert", err)
		if err := tx.Rollback(); err != nil {
			t.Fatal(err)
		}
	})
	t.Run("QueryRETURNING", func(t *testing.T) {
		err := drainRows(s.db.QueryContext(ctx, `INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'x', 'ret', 1, 7) RETURNING id`))
		requireDenied(t, "insert returning", err)
		err = drainRows(s.db.Query(`DELETE FROM audit RETURNING id`))
		requireDenied(t, "delete returning", err)
	})
	t.Run("REPLACE", func(t *testing.T) {
		_, err := s.db.Exec(`INSERT OR REPLACE INTO apps (app, account_id, created_by, created_at, app_id) VALUES ('blog', 7, 'x', 1, 'app-A')`)
		requireDenied(t, "replace", err)
		_, err = s.db.Exec(`REPLACE INTO owners (address, app, created_at) VALUES ('p@x', 'blog', 9)`)
		requireDenied(t, "replace owners", err)
	})
	t.Run("mail telemetry is gated too", func(t *testing.T) {
		_, err := s.db.Exec(`INSERT INTO mail_events (account, app, notice, recipient, message_id, type, at, received_at)
VALUES ('acct7', 'blog', 'n', 'r', 'm', 'Bounce', 1, 1)`)
		requireDenied(t, "mail_events", err)
		_, err = s.db.Exec(`INSERT INTO mail_sender_counts (account, app, sender, day) VALUES ('acct7', 'blog', 's', '2026-01-01')`)
		requireDenied(t, "mail_sender_counts", err)
	})
	t.Run("app-scoped token", func(t *testing.T) {
		_, err := s.db.Exec(`INSERT INTO cli_tokens (token_hash, address, created_at, expires_at, kind, account_id, app) VALUES ('h1', 'a', 1, 0, 'app', 7, 'blog')`)
		requireDenied(t, "app token", err)
		// A user token carries no app: it is not app-scoped, so login still works.
		if _, err := s.db.Exec(`INSERT INTO cli_tokens (token_hash, address, created_at, expires_at) VALUES ('h2', 'a', 1, 0)`); err != nil {
			t.Fatalf("user token insert: %v", err)
		}
		_, err = s.db.Exec(`UPDATE cli_tokens SET app = 'blog' WHERE token_hash = 'h2'`)
		requireDenied(t, "moving a token into an app", err)
	})
	t.Run("non-app tables stay writable", func(t *testing.T) {
		if _, err := s.db.Exec(`INSERT INTO users (address, created_at) VALUES ('u@x', 1)`); err != nil {
			t.Fatal(err)
		}
		if _, err := s.db.Exec(`INSERT INTO sessions (token_hash, address, created_at, expires_at) VALUES ('t', 'u@x', 1, 2)`); err != nil {
			t.Fatal(err)
		}
	})

	if n := countWhere(t, s, "apps", "1=1"); n != 1 {
		t.Fatalf("apps rows = %d, want 1", n)
	}
	if n := countWhere(t, s, "audit", "action <> 'seed'"); n != 0 {
		t.Fatalf("%d unauthorized audit rows landed", n)
	}
	if n := countWhere(t, s, "audit", "action = 'seed'"); n != 1 {
		t.Fatalf("seed audit rows = %d, want 1", n)
	}
}

func TestClaimSQLViaTableNeedsParentScope(t *testing.T) {
	s := openGatedStore(t)
	seedGateApp(t, s)
	err := s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, allowAllClaimSQL, func(v *Store) error {
		if _, err := v.db.Exec(`INSERT INTO pending_changes (id, account_id, app, kind, payload, quorum, proposer, created_at, expires_at) VALUES ('c1', 7, 'blog', 'k', '{}', 1, 'p', 1, 2)`); err != nil {
			return err
		}
		if _, err := v.db.Exec(`INSERT INTO pending_confirms (change_id, address, confirmed_at) VALUES ('c1', 'p', 1)`); err != nil {
			return err
		}
		// A confirm for a change that does not exist has no scope: refused.
		_, err := v.db.Exec(`INSERT INTO pending_confirms (change_id, address, confirmed_at) VALUES ('ghost', 'p', 1)`)
		requireDenied(t, "ghost confirm", err)
		return nil
	})
	if err != nil {
		t.Fatal(err)
	}
	_, err = s.db.Exec(`DELETE FROM pending_confirms WHERE change_id = 'c1'`)
	requireDenied(t, "confirm delete outside scope", err)
}

// ---- admitted writes ---------------------------------------------------------

func TestClaimSQLAdmittedWritesPassAndAreScoped(t *testing.T) {
	s := openGatedStore(t)
	seedGateApp(t, s)
	var calls atomic.Int64
	check := func(context.Context, claimSQLScope) error { calls.Add(1); return nil }
	err := s.withPreparedClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, check, check, nil, false, func(v *Store) error {
		for _, q := range []string{
			`UPDATE apps SET placement = 'eu' WHERE account_id = 7 AND app = 'blog'`,
			`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p@x', 'edit', 2, 7)`,
			`INSERT INTO mail_events (account, app, notice, recipient, message_id, type, at, received_at) VALUES ('acct7', 'blog', 'n', 'r', 'm', 'Bounce', 1, 1)`,
		} {
			if _, err := v.db.Exec(q); err != nil {
				t.Errorf("%s: %v", q, err)
			}
		}
		// Reads need no check and see the writes.
		var placement string
		if err := v.db.QueryRow(`SELECT placement FROM apps WHERE app = 'blog'`).Scan(&placement); err != nil || placement != "eu" {
			t.Errorf("placement = %q, %v", placement, err)
		}
		// The permit is for blog only.
		_, err := v.db.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('other', 'p@x', 'x', 1, 7)`)
		requireDenied(t, "other app", err)
		_, err = v.db.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p@x', 'x', 1, 8)`)
		requireDenied(t, "other account", err)
		_, err = v.db.Exec(`INSERT INTO mail_events (account, app, notice, recipient, message_id, type, at, received_at) VALUES ('acct8', 'blog', 'n', 'r', 'm', 'Bounce', 1, 1)`)
		requireDenied(t, "other account sqid", err)
		return nil
	})
	if err != nil {
		t.Fatal(err)
	}
	// 1 admission + 3 writes + 3 refused writes (the callback runs before each write).
	if got := calls.Load(); got != 7 {
		t.Fatalf("check ran %d times, want 7 (admission + every write)", got)
	}
	if n := countWhere(t, s, "audit", "action = 'edit'"); n != 1 {
		t.Fatalf("edit audit rows = %d, want 1", n)
	}
	// Outside the callback the scope is gone again.
	_, err = s.db.Exec(`UPDATE apps SET placement = 'us' WHERE app = 'blog'`)
	requireDenied(t, "after callback", err)
}

func TestClaimSQLAdmissionRefusalRunsNothing(t *testing.T) {
	s := openGatedStore(t)
	boom := errors.New("not yours")
	ran := false
	err := s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, func(context.Context, claimSQLScope) error { return boom }, func(*Store) error {
		ran = true
		return nil
	})
	if !errors.Is(err, ErrClaimSQLDenied) || !errors.Is(err, boom) || ran {
		t.Fatalf("err = %v ran = %v, want ErrClaimSQLDenied wrapping boom and no callback", err, ran)
	}
	for _, bad := range [][]claimSQLScope{nil, {{AccountID: 0, App: "a", Mode: claimModeEffect}}, {{AccountID: 1, App: "", Mode: claimModeEffect}},
		{{AccountID: 1, App: "a", AppID: "x"}}, {{AccountID: 1, App: "a", AppID: "x", Mode: "admin"}}, {{AccountID: 1, App: "a", Mode: claimModeRestore}}} {
		if err := s.withClaimSQL(gateCtx(t), bad, allowAllClaimSQL, func(*Store) error { return nil }); !errors.Is(err, ErrClaimSQLScope) {
			t.Fatalf("scopes %v: err = %v, want ErrClaimSQLScope", bad, err)
		}
	}
}

func TestClaimSQLFlagOffStoreIsUngated(t *testing.T) {
	s := openTestStore(t)
	if s.claimGated() {
		t.Fatal("Open must not arm the gate")
	}
	if _, err := s.db.Exec(`INSERT INTO owners (address, app, created_at) VALUES ('a', 'b', 1)`); err != nil {
		t.Fatal(err)
	}
	ran := false
	if err := s.withClaimSQL(gateCtx(t), nil, nil, func(v *Store) error { ran = v == s; return nil }); err != nil || !ran {
		t.Fatalf("withClaimSQL flag-off: ran=%v err=%v", ran, err)
	}
}

// ---- revocation, expiry, successor ids ---------------------------------------

func TestClaimSQLRevokedCheckStopsMutation(t *testing.T) {
	s := openGatedStore(t)
	seedGateApp(t, s)
	var revoked atomic.Bool
	check := func(context.Context, claimSQLScope) error {
		if revoked.Load() {
			return errors.New("operation revoked")
		}
		return nil
	}
	err := s.withPreparedClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, check, check, nil, false, func(v *Store) error {
		if _, err := v.db.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'first', 1, 7)`); err != nil {
			t.Fatalf("first write: %v", err)
		}
		revoked.Store(true)
		_, err := v.db.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'second', 1, 7)`)
		if !errors.Is(err, ErrClaimSQLDenied) || !strings.Contains(err.Error(), "operation revoked") {
			t.Fatalf("second write err = %v, want denied by the callback", err)
		}
		// Even with the callback relenting, the scope stays dead.
		revoked.Store(false)
		_, err = v.db.Exec(`DELETE FROM apps WHERE app = 'blog'`)
		if err == nil {
			t.Fatal("write after a refused check must stay refused")
		}
		return nil
	})
	if err != nil {
		t.Fatal(err)
	}
	if countWhere(t, s, "audit", "action = 'first'") != 1 || countWhere(t, s, "audit", "action = 'second'") != 0 || countWhere(t, s, "apps", "app = 'blog'") != 1 {
		t.Fatal("revoked scope mutated state")
	}
}

func TestClaimSQLRevokedBeforeCommitRollsBack(t *testing.T) {
	s := openGatedStore(t)
	seedGateApp(t, s)
	var revoked atomic.Bool
	check := func(context.Context, claimSQLScope) error {
		if revoked.Load() {
			return errors.New("expired")
		}
		return nil
	}
	err := s.withPreparedClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, check, check, nil, false, func(v *Store) error {
		tx, err := v.db.Begin()
		if err != nil {
			return err
		}
		if _, err := tx.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'in-tx', 1, 7)`); err != nil {
			t.Fatalf("tx write: %v", err)
		}
		revoked.Store(true)
		err = tx.Commit()
		if !errors.Is(err, ErrClaimSQLDenied) {
			t.Fatalf("commit err = %v, want ErrClaimSQLDenied", err)
		}
		return nil
	})
	if err != nil {
		t.Fatal(err)
	}
	if n := countWhere(t, s, "audit", "action = 'in-tx'"); n != 0 {
		t.Fatalf("%d rows survived a refused commit", n)
	}
	// The pool is healthy and the permit is gone.
	_, err = s.db.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'late', 1, 7)`)
	requireDenied(t, "after refused commit", err)
}

func TestClaimSQLExpiryCheckedOnEveryWrite(t *testing.T) {
	s := openGatedStore(t)
	seedGateApp(t, s)
	now := time.Unix(1000, 0)
	expires := time.Unix(1010, 0)
	check := func(context.Context, claimSQLScope) error {
		if !now.Before(expires) {
			return errors.New("permit expired")
		}
		return nil
	}
	err := s.withPreparedClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, check, check, nil, false, func(v *Store) error {
		if _, err := v.db.Exec(`UPDATE apps SET placement = 'a' WHERE app = 'blog'`); err != nil {
			t.Fatal(err)
		}
		now = time.Unix(1011, 0)
		if _, err := v.db.Exec(`UPDATE apps SET placement = 'b' WHERE app = 'blog'`); err == nil {
			t.Fatal("write after expiry succeeded")
		}
		return nil
	})
	if err != nil {
		t.Fatal(err)
	}
	var placement string
	if err := s.db.QueryRow(`SELECT placement FROM apps WHERE app = 'blog'`).Scan(&placement); err != nil || placement != "a" {
		t.Fatalf("placement = %q (%v), want a", placement, err)
	}
}

func TestClaimSQLSuccessorAppIDMismatchRefused(t *testing.T) {
	s := openGatedStore(t)
	seedGateApp(t, s) // apps row blog is incarnation app-A
	successor := claimSQLScope{AccountID: gateAcct, App: "blog", AppID: "app-B", Mode: claimModeEffect}
	err := s.withClaimSQL(gateCtx(t), []claimSQLScope{successor}, allowAllClaimSQL, func(v *Store) error {
		_, err := v.db.Exec(`UPDATE apps SET placement = 'x' WHERE app = 'blog'`)
		requireDenied(t, "successor update of predecessor row", err)
		_, err = v.db.Exec(`DELETE FROM apps WHERE app = 'blog'`)
		requireDenied(t, "successor delete of predecessor row", err)
		_, err = v.db.Exec(`UPDATE apps SET app_id = 'app-B' WHERE app = 'blog'`)
		requireDenied(t, "successor taking over the id", err)
		return nil
	})
	if err != nil {
		t.Fatal(err)
	}
	// A permit never covers another app's rows.
	err = s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, allowAllClaimSQL, func(v *Store) error {
		_, err := v.db.Exec(`INSERT INTO apps (app, account_id, created_by, created_at) VALUES ('legacy', 7, 'x', 1)`)
		requireDenied(t, "row for an app the permit does not name", err)
		return nil
	})
	if err != nil {
		t.Fatal(err)
	}
	// An unidentified (app_id '') row is adopted by a permit that names its app.
	adopt := claimSQLScope{AccountID: gateAcct, App: "adopt", AppID: "app-C", Mode: claimModeEffect}
	err = s.withClaimSQL(gateCtx(t), []claimSQLScope{adopt}, allowAllClaimSQL, func(v *Store) error {
		if _, err := v.db.Exec(`INSERT INTO apps (app, account_id, created_by, created_at) VALUES ('adopt', 7, 'x', 1)`); err != nil {
			return err
		}
		_, err := v.db.Exec(`UPDATE apps SET app_id = 'app-C' WHERE app = 'adopt'`)
		return err
	})
	if err != nil {
		t.Fatal(err)
	}
	if n := countWhere(t, s, "apps", "app = 'adopt' AND app_id = 'app-C'"); n != 1 {
		t.Fatal("adoption did not land")
	}
	if n := countWhere(t, s, "apps", "app = 'blog' AND placement = '' AND app_id = 'app-A'"); n != 1 {
		t.Fatal("predecessor row changed under another incarnation's permit")
	}
}

// ---- handles that outlive the callback ---------------------------------------

func TestClaimSQLRetainedHandlesDieWithTheCallback(t *testing.T) {
	s := openGatedStore(t)
	seedGateApp(t, s)
	var tx *sql.Tx
	var stmt *sql.Stmt
	err := s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, allowAllClaimSQL, func(v *Store) error {
		var err error
		if tx, err = v.db.Begin(); err != nil {
			return err
		}
		if _, err = tx.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'retained-tx', 1, 7)`); err != nil {
			return err
		}
		if stmt, err = v.db.Prepare(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'retained-stmt', 1, 7)`); err != nil {
			return err
		}
		return nil // tx neither committed nor rolled back, stmt and tx leaked
	})
	// The forced rollback lost the tx's write, so the scope must not succeed.
	if !errors.Is(err, ErrClaimSQLScope) {
		t.Fatalf("open tx at scope end: %v, want ErrClaimSQLScope", err)
	}
	// No pool deadlock: the single connection is back.
	short, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()
	if _, err := s.db.ExecContext(short, `INSERT INTO users (address, created_at) VALUES ('after@x', 1)`); err != nil {
		t.Fatalf("pool wedged after callback: %v", err)
	}
	if _, err := tx.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'zombie', 1, 7)`); err == nil {
		t.Fatal("retained tx still writes")
	}
	if err := tx.Commit(); err == nil {
		t.Fatal("retained tx committed")
	}
	if _, err := stmt.Exec(); err == nil {
		t.Fatal("retained statement still writes")
	}
	if n := countWhere(t, s, "audit", "action IN ('retained-tx','retained-stmt','zombie')"); n != 0 {
		t.Fatalf("%d rows written through retained handles", n)
	}
}

func TestClaimSQLRetainedRowsDoNotWedgeThePool(t *testing.T) {
	s := openGatedStore(t)
	seedGateApp(t, s)
	var rows *sql.Rows
	err := s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, allowAllClaimSQL, func(v *Store) error {
		var err error
		rows, err = v.db.Query(`SELECT address FROM owners`)
		return err
	})
	if err != nil {
		t.Fatal(err)
	}
	defer rows.Close()
	short, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()
	if _, err := s.db.ExecContext(short, `INSERT INTO users (address, created_at) VALUES ('after2@x', 1)`); err != nil {
		t.Fatalf("pool wedged by retained rows: %v", err)
	}
}

func TestClaimSQLViewCannotNestOrClose(t *testing.T) {
	s := openGatedStore(t)
	err := s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, allowAllClaimSQL, func(v *Store) error {
		if err := v.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, allowAllClaimSQL, func(*Store) error { return nil }); !errors.Is(err, ErrClaimSQLScope) {
			t.Errorf("nested withClaimSQL err = %v, want ErrClaimSQLScope", err)
		}
		if err := v.Close(); !errors.Is(err, ErrClaimSQLScope) {
			t.Errorf("view Close err = %v, want ErrClaimSQLScope", err)
		}
		return nil
	})
	if err != nil {
		t.Fatal(err)
	}
	// An outer-store call inside the callback is refused at once rather than
	// waiting for the connection the view holds (review m-5).
	err = s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, allowAllClaimSQL, func(*Store) error {
		short, cancel := context.WithTimeout(context.Background(), 200*time.Millisecond)
		defer cancel()
		_, err := s.db.ExecContext(short, `INSERT INTO users (address, created_at) VALUES ('outer@x', 1)`)
		if !errors.Is(err, ErrClaimSQLScope) {
			t.Errorf("outer call err = %v, want ErrClaimSQLScope", err)
		}
		return nil
	})
	if err != nil {
		t.Fatal(err)
	}
}

// ---- the wrapper refuses what would defeat the gate --------------------------

func TestClaimSQLOrdinaryHandleRefusesDefeatingStatements(t *testing.T) {
	s := openGatedStore(t)
	seedGateApp(t, s)
	stmts := []string{
		`PRAGMA recursive_triggers = OFF`,
		`pragma writable_schema = 1`,
		`CREATE TABLE x (a)`,
		`CREATE TEMP TABLE x (a)`,
		`DROP TRIGGER claim_delete_apps`,
		`DROP TABLE apps`,
		`ALTER TABLE apps ADD COLUMN z TEXT`,
		`ATTACH DATABASE ':memory:' AS m`,
		`DETACH DATABASE m`,
		`VACUUM`,
		`BEGIN`,
		`COMMIT`,
		`SAVEPOINT a`,
		`EXPLAIN SELECT 1`,
		`DELETE FROM temp.claim_permit`,
		`INSERT INTO claim_permit (account_id, sqid, app, app_id) VALUES (7, NULL, 'other', '')`,
		`INSERT INTO "claim_permit" VALUES (7, NULL, 'other', '')`,
		`INSERT INTO [claim_permit] VALUES (7, NULL, 'other', '')`,
		"INSERT INTO `claim_permit` VALUES (7, NULL, 'other', '')",
		`INSERT INTO 'claim_permit' VALUES (7, NULL, 'other', '')`,
		`UPDATE temp."CLAIM_PERMIT" SET app = 'blog'`,
		`WITH x AS (SELECT 1) DELETE FROM claim_permit`,
		`SELECT * FROM sqlite_temp_master`,
		`DELETE FROM sqlite_sequence`,
		`SELECT * FROM pragma_table_info('apps')`,
		// multi-statement loopholes
		`SELECT 1; PRAGMA recursive_triggers = OFF`,
		`SELECT 1; DELETE FROM claim_permit`,
		`SELECT ';'; PRAGMA recursive_triggers = OFF`,
		"SELECT 1 /* ; */; DROP TRIGGER claim_delete_apps",
		"SELECT 1 -- ;\n; DROP TRIGGER claim_delete_apps",
		`SELECT "a;b"; DROP TRIGGER claim_delete_apps`,
		`INSERT INTO users (address, created_at) VALUES ('m@x', 1); ATTACH DATABASE ':memory:' AS m`,
		`SELECT 1;;; CREATE TABLE y (a)`,
	}
	ctx := gateCtx(t)
	check := func(name string, err error) {
		t.Helper()
		if !errors.Is(err, ErrClaimSQLForbidden) {
			t.Errorf("%s: err = %v, want ErrClaimSQLForbidden", name, err)
		}
	}
	scoped := func(f func(v *Store)) {
		if err := s.withClaimSQL(ctx, []claimSQLScope{gateScope}, allowAllClaimSQL, func(v *Store) error { f(v); return nil }); err != nil {
			t.Fatal(err)
		}
	}
	for _, q := range stmts {
		_, err := s.db.Exec(q)
		check("Exec "+q, err)
		_, err = s.db.ExecContext(ctx, q)
		check("ExecContext "+q, err)
		check("Query "+q, drainRows(s.db.Query(q)))
		check("QueryRow "+q, s.db.QueryRow(q).Scan(new(any)))
		st, err := s.db.Prepare(q)
		if err == nil {
			st.Close()
		}
		check("Prepare "+q, err)
		tx, err := s.db.Begin()
		if err != nil {
			t.Fatal(err)
		}
		_, err = tx.Exec(q)
		check("tx.Exec "+q, err)
		tx.Rollback()
		scoped(func(v *Store) {
			_, err := v.db.Exec(q)
			check("view Exec "+q, err)
			tx, err := v.db.Begin()
			if err != nil {
				t.Error(err)
				return
			}
			_, err = tx.Exec(q)
			check("view tx.Exec "+q, err)
			tx.Rollback()
		})
	}
	// None of that disarmed anything.
	_, err := s.db.Exec(`DELETE FROM apps`)
	requireDenied(t, "after the loophole attempts", err)
	if n := countWhere(t, s, "apps", "1=1"); n != 1 {
		t.Fatalf("apps rows = %d, want 1", n)
	}
}

func TestInspectClaimSQL(t *testing.T) {
	ok := []struct {
		q     string
		write bool
	}{
		{`SELECT 1`, false},
		{`SELECT ';'`, false},
		{`SELECT 'a' || "b" FROM t -- trailing; comment`, false},
		{`SELECT 1;`, false},
		{`  select * from apps where app = ?  ;  `, false},
		{`WITH x AS (SELECT 1) SELECT * FROM x`, false},
		{`INSERT INTO t VALUES ('temp')`, true},
		{`INSERT OR REPLACE INTO t VALUES (1)`, true},
		{`UPDATE t SET a = 1`, true},
		{`DELETE FROM t`, true},
		{`WITH x AS (SELECT 1) DELETE FROM t WHERE a IN x`, true},
		{`SELECT replace(a, 'x', 'y') FROM t`, true}, // conservative
		{`INSERT INTO t VALUES (1); SELECT 2`, true},
	}
	for _, c := range ok {
		info, err := inspectClaimSQL(c.q)
		if err != nil {
			t.Errorf("%q: %v", c.q, err)
			continue
		}
		if info.write != c.write {
			t.Errorf("%q: write = %v, want %v", c.q, info.write, c.write)
		}
	}
	for _, q := range []string{
		`PRAGMA user_version`, `CREATE INDEX i ON t (a)`, `SELECT 1; VACUUM`, `SELECT * FROM claim_permit`,
		`SELECT 'claim_permit'`, `SELECT * FROM "Claim_Permit"`, `SELECT * FROM sqlite_master`, `SELECT load_extension('x')`,
	} {
		if _, err := inspectClaimSQL(q); !errors.Is(err, ErrClaimSQLForbidden) {
			t.Errorf("%q: err = %v, want ErrClaimSQLForbidden", q, err)
		}
	}
}

// ---- the census --------------------------------------------------------------

func TestClaimSQLTableCensus(t *testing.T) {
	s := openGatedStore(t)
	present, err := claimSQLCensus(s.bootDB(), claimSQLTables)
	if err != nil {
		t.Fatal(err)
	}
	// The default schema really is covered, and every app-referencing table
	// is gated: re-derive that from sqlite_master and pragma_table_info.
	rows, err := s.bootDB().Query(`SELECT m.name, p.name FROM main.sqlite_master m, pragma_table_info(m.name) p WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite\_%' ESCAPE '\'`)
	if err != nil {
		t.Fatal(err)
	}
	type tc struct{ table, col string }
	var all []tc
	for rows.Next() {
		var x tc
		if err := rows.Scan(&x.table, &x.col); err != nil {
			t.Fatal(err)
		}
		all = append(all, x)
	}
	rows.Close()
	if len(all) == 0 {
		t.Fatal("census saw no tables")
	}
	for _, x := range all {
		spec, ok := claimSQLTables[x.table]
		if !ok {
			t.Errorf("table %q is not classified", x.table)
			continue
		}
		if spec.NonApp != "" && claimAppish(x.col) {
			t.Errorf("NonApp table %q has app column %q", x.table, x.col)
		}
	}
	for _, name := range []string{"apps", "owners", "audit", "cli_tokens", "door_keys", "door_key_revisions", "pending_changes", "pending_confirms",
		"mail_counts", "mail_sender_counts", "mail_events", "mail_event_hooks", "mail_event_deliveries"} {
		if sp, ok := present[name]; !ok || sp.NonApp != "" {
			t.Errorf("%s must be present and gated", name)
		}
	}
	for name, sp := range claimSQLTables {
		if sp.NonApp == "" && sp.App == "" {
			t.Errorf("gated table %q has no App column", name)
		}
	}
	var n int
	if err := s.bootDB().QueryRow(`SELECT count(*) FROM main.sqlite_master WHERE type = 'trigger'`).Scan(&n); err != nil {
		t.Fatal(err)
	}
	if n != 0 {
		t.Fatalf("%d persistent triggers in the main schema; the gate's triggers must be TEMP only", n)
	}
}

func TestClaimSQLCensusRefusesNewAppTable(t *testing.T) {
	path := filepath.Join(t.TempDir(), "c.db")
	_, err := openGated(path, path, claimSQLTables, func(s *Store) error {
		if err := s.migrate(); err != nil {
			return err
		}
		if err := s.migrateClaimAppIdentity(); err != nil {
			return err
		}
		_, err := s.db.Exec(`CREATE TABLE sneaky_app_data (account_id INTEGER, app TEXT, v TEXT)`)
		return err
	})
	if !errors.Is(err, ErrClaimSQLCensus) || !strings.Contains(err.Error(), "sneaky_app_data") {
		t.Fatalf("err = %v, want a census refusal naming sneaky_app_data", err)
	}
}

func TestClaimSQLCensusRefusals(t *testing.T) {
	plain := openTestStore(t)
	if err := plain.migrateClaimAppIdentity(); err != nil {
		t.Fatal(err)
	}
	clone := func(edit func(map[string]claimSQLTable)) map[string]claimSQLTable {
		m := map[string]claimSQLTable{}
		for k, v := range claimSQLTables {
			m[k] = v
		}
		edit(m)
		return m
	}
	cases := []struct {
		name  string
		setup string
		edit  func(map[string]claimSQLTable)
		want  string
	}{
		{"unclassified table", `CREATE TABLE brand_new (x)`, func(map[string]claimSQLTable) {}, "brand_new"},
		{"view", `CREATE VIEW v AS SELECT 1`, func(map[string]claimSQLTable) {}, "view"},
		{"persistent trigger", `CREATE TRIGGER trg AFTER INSERT ON users BEGIN SELECT 1; END`, func(map[string]claimSQLTable) {}, "trigger"},
		{"reserved name", `CREATE TABLE claim_permit (x)`, func(m map[string]claimSQLTable) { m["claim_permit"] = claimSQLTable{NonApp: "x"} }, "reserved"},
		{"app column hidden in a NonApp table", ``, func(m map[string]claimSQLTable) { m["owners"] = claimSQLTable{NonApp: "claimed harmless"} }, "app column"},
		{"app_id not enforced", ``, func(m map[string]claimSQLTable) { m["apps"] = claimSQLTable{Account: "account_id", App: "app"} }, "app_id"},
		{"missing column", ``, func(m map[string]claimSQLTable) { m["audit"] = claimSQLTable{Account: "no_such", App: "app"} }, "no_such"},
		{"empty spec", ``, func(m map[string]claimSQLTable) { m["users"] = claimSQLTable{} }, "no App column"},
	}
	for _, c := range cases {
		t.Run(c.name, func(t *testing.T) {
			s := openTestStore(t)
			if err := s.migrateClaimAppIdentity(); err != nil {
				t.Fatal(err)
			}
			if c.setup != "" {
				if _, err := s.db.Exec(c.setup); err != nil {
					t.Fatal(err)
				}
			}
			_, err := claimSQLCensus(s.bootDB(), clone(c.edit))
			if !errors.Is(err, ErrClaimSQLCensus) || !strings.Contains(err.Error(), c.want) {
				t.Fatalf("err = %v, want a census refusal mentioning %q", err, c.want)
			}
		})
	}
	// The unmodified schema passes on an unarmed store.
	if _, err := claimSQLCensus(plain.bootDB(), claimSQLTables); err != nil {
		t.Fatal(err)
	}
}

// ---- REPLACE and cascade -----------------------------------------------------

var customGateTables = map[string]claimSQLTable{
	"fam_parent": {Account: "account_id", App: "app"},
	"fam_child":  {Account: "account_id", App: "app"},
	"fam_uniq":   {Account: "account_id", App: "app"},
}

const (
	famParentDDL = `CREATE TABLE fam_parent (id INTEGER PRIMARY KEY, account_id INTEGER NOT NULL, app TEXT NOT NULL)`
	famChildDDL  = `CREATE TABLE fam_child (id INTEGER PRIMARY KEY, account_id INTEGER NOT NULL, app TEXT NOT NULL, pid INTEGER NOT NULL REFERENCES fam_parent(id) ON DELETE CASCADE)`
	famUniqDDL   = `CREATE TABLE fam_uniq (account_id INTEGER NOT NULL, app TEXT NOT NULL, k TEXT NOT NULL UNIQUE, v TEXT)`
)

var (
	scopeA = claimSQLScope{AccountID: 1, App: "a", Mode: claimModeEffect}
	scopeB = claimSQLScope{AccountID: 1, App: "b", Mode: claimModeEffect}
)

func TestClaimSQLReplaceImplicitDeleteIsGated(t *testing.T) {
	s := openGatedCustom(t, customGateTables, famUniqDDL)
	err := s.withClaimSQL(gateCtx(t), []claimSQLScope{scopeB}, allowAllClaimSQL, func(v *Store) error {
		_, err := v.db.Exec(`INSERT INTO fam_uniq (account_id, app, k, v) VALUES (1, 'b', 'k', 'old')`)
		return err
	})
	if err != nil {
		t.Fatal(err)
	}
	// A's own row passes the INSERT check, but REPLACE would implicitly delete
	// B's row: only recursive_triggers makes that visible to the DELETE trigger.
	err = s.withClaimSQL(gateCtx(t), []claimSQLScope{scopeA}, allowAllClaimSQL, func(v *Store) error {
		_, err := v.db.Exec(`INSERT OR REPLACE INTO fam_uniq (account_id, app, k, v) VALUES (1, 'a', 'k', 'new')`)
		requireDenied(t, "replace over another app's row", err)
		return nil
	})
	if err != nil {
		t.Fatal(err)
	}
	if n := countWhere(t, s, "fam_uniq", "app = 'b' AND v = 'old'"); n != 1 {
		t.Fatal("REPLACE deleted another app's row")
	}
	// Control: with both scopes the same statement is allowed.
	err = s.withClaimSQL(gateCtx(t), []claimSQLScope{scopeA, scopeB}, allowAllClaimSQL, func(v *Store) error {
		_, err := v.db.Exec(`INSERT OR REPLACE INTO fam_uniq (account_id, app, k, v) VALUES (1, 'a', 'k', 'new')`)
		return err
	})
	if err != nil {
		t.Fatal(err)
	}
	if n := countWhere(t, s, "fam_uniq", "app = 'a' AND v = 'new'"); n != 1 {
		t.Fatal("control REPLACE did not land")
	}
}

func TestClaimSQLCascadeDeleteIsGated(t *testing.T) {
	s := openGatedCustom(t, customGateTables, famParentDDL, famChildDDL)
	both := []claimSQLScope{scopeA, scopeB}
	err := s.withClaimSQL(gateCtx(t), both, allowAllClaimSQL, func(v *Store) error {
		for _, q := range []string{
			`INSERT INTO fam_parent (id, account_id, app) VALUES (1, 1, 'a')`,
			// Owned by B, but cascade-attached to A's parent.
			`INSERT INTO fam_child (id, account_id, app, pid) VALUES (1, 1, 'b', 1)`,
		} {
			if _, err := v.db.Exec(q); err != nil {
				return err
			}
		}
		return nil
	})
	if err != nil {
		t.Fatal(err)
	}
	err = s.withClaimSQL(gateCtx(t), []claimSQLScope{scopeA}, allowAllClaimSQL, func(v *Store) error {
		_, err := v.db.Exec(`DELETE FROM fam_parent WHERE id = 1`)
		requireDenied(t, "cascade into another app's rows", err)
		return nil
	})
	if err != nil {
		t.Fatal(err)
	}
	if countWhere(t, s, "fam_parent", "id = 1") != 1 || countWhere(t, s, "fam_child", "id = 1") != 1 {
		t.Fatal("cascade removed rows the permit does not cover")
	}
	// Control: with both scopes the cascade is allowed.
	err = s.withClaimSQL(gateCtx(t), both, allowAllClaimSQL, func(v *Store) error {
		_, err := v.db.Exec(`DELETE FROM fam_parent WHERE id = 1`)
		return err
	})
	if err != nil {
		t.Fatal(err)
	}
	if countWhere(t, s, "fam_child", "1=1") != 0 {
		t.Fatal("control cascade did not run (foreign keys off?)")
	}
}

// A connection opened before arming must not survive it: every connection the
// pool hands out afterwards is armed.
func TestClaimSQLEveryConnectionIsArmed(t *testing.T) {
	s := openGatedStore(t)
	seedGateApp(t, s)
	for i := 0; i < 3; i++ {
		conn, err := s.rawPool.Conn(gateCtx(t))
		if err != nil {
			t.Fatal(err)
		}
		_, err = conn.ExecContext(gateCtx(t), `DELETE FROM apps`)
		requireDenied(t, "fresh pool connection", err)
		conn.Close()
		// Force a new physical connection next round.
		s.rawPool.SetMaxIdleConns(0)
		s.rawPool.SetMaxIdleConns(2)
	}
}

// ---- permit modes ------------------------------------------------------------

func scopeMode(sc claimSQLScope, mode string) claimSQLScope { sc.Mode = mode; return sc }

// owners rows name no account, so no permit of any mode may write them.
func TestClaimSQLOwnersRefusedUnderEveryPermit(t *testing.T) {
	s := openGatedStore(t)
	seedGateApp(t, s)
	for _, mode := range []string{claimModeEffect, claimModePurge, claimModeRestore, claimModeTrash} {
		err := s.withClaimSQL(gateCtx(t), []claimSQLScope{scopeMode(gateScope, mode)}, allowAllClaimSQL, func(v *Store) error {
			_, err := v.db.Exec(`INSERT INTO owners (address, app, created_at) VALUES ('p@x', 'blog', 1)`)
			requireDenied(t, mode+" owners insert", err)
			_, err = v.db.Exec(`REPLACE INTO owners (address, app, created_at) VALUES ('p@x', 'blog', 2)`)
			requireDenied(t, mode+" owners replace", err)
			return nil
		})
		if err != nil {
			t.Fatal(err)
		}
	}
	if n := countWhere(t, s, "owners", "1=1"); n != 0 {
		t.Fatalf("owners rows = %d, want 0", n)
	}
}

// seedTerminalWorld adds, under effect permits: gateScope's row trashed at
// 100 with restoring set, an unidentified (empty app_id) row "blog8" in
// account 8, a door-key revision, a mail event and an app token.
func seedTerminalWorld(t *testing.T, s *Store) {
	t.Helper()
	seedGateApp(t, s)
	if _, err := s.db.Exec(`INSERT INTO accounts (id, sqid, name, created_at) VALUES (8, 'acct8', 'eight', 1)`); err != nil {
		t.Fatal(err)
	}
	legacy := claimSQLScope{AccountID: 8, App: "blog8", AppID: "app-L", Mode: claimModeEffect}
	err := s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope, legacy}, allowAllClaimSQL, func(v *Store) error {
		for _, q := range []string{
			`UPDATE apps SET deleted_at = 100, restoring = 1 WHERE account_id = 7 AND app = 'blog'`,
			`INSERT INTO apps (app, account_id, created_by, created_at) VALUES ('blog8', 8, 'x', 1)`,
			`INSERT INTO door_key_revisions (account_id, app, revision) VALUES (7, 'blog', 3)`,
			`INSERT INTO mail_events (account, app, notice, recipient, message_id, type, at, received_at) VALUES ('acct7', 'blog', 'n', 'r', 'm', 'Bounce', 1, 1)`,
			`INSERT INTO cli_tokens (token_hash, address, created_at, expires_at, kind, account_id, app) VALUES ('ht', 'a', 1, 0, 'app', 7, 'blog')`,
		} {
			if _, err := v.db.Exec(q); err != nil {
				return fmt.Errorf("%s: %w", q, err)
			}
		}
		return nil
	})
	if err != nil {
		t.Fatal(err)
	}
}

// Terminal grants: their callbacks try every statement outside their family
// (INSERT, a column they do not own, DELETE, re-binding app_id, adopting an
// unidentified row) and SQLite refuses each one, through Exec, prepared
// statements, Query ... RETURNING and transactions.
func TestClaimSQLTerminalModesRefuseOutsideTheirFamily(t *testing.T) {
	for _, mode := range []string{claimModeRestore, claimModeTrash, claimModePurge} {
		t.Run(mode, func(t *testing.T) {
			s := openGatedStore(t)
			seedTerminalWorld(t, s)
			ctx := gateCtx(t)
			unbound := claimSQLScope{AccountID: 8, App: "blog8", AppID: "app-U", Mode: mode}
			err := s.withClaimSQL(ctx, []claimSQLScope{scopeMode(gateScope, mode), unbound}, allowAllClaimSQL, func(v *Store) error {
				_, err := v.db.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'forged', 1, 7)`)
				requireDenied(t, "insert", err)
				_, err = v.db.Exec(`INSERT INTO apps (app, account_id, created_by, created_at, app_id) VALUES ('blog2', 7, 'x', 1, 'app-A')`)
				requireDenied(t, "insert apps", err)
				err = drainRows(v.db.Query(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'ret', 1, 7) RETURNING id`))
				requireDenied(t, "insert returning", err)
				_, err = v.db.Exec(`UPDATE apps SET placement = 'evil' WHERE account_id = 7 AND app = 'blog'`)
				requireDenied(t, "update a column the mode does not own", err)
				st, perr := v.db.Prepare(`UPDATE apps SET region = 'evil' WHERE account_id = 7 AND app = 'blog'`)
				if perr != nil {
					t.Fatal(perr)
				}
				_, err = st.Exec()
				st.Close()
				requireDenied(t, "prepared update of a foreign column", err)
				_, err = v.db.Exec(`UPDATE apps SET app_id = 'app-Z' WHERE account_id = 7 AND app = 'blog'`)
				requireDenied(t, "re-binding app_id", err)
				_, err = v.db.Exec(`UPDATE audit SET action = 'forged' WHERE app = 'blog'`)
				requireDenied(t, "update of another table", err)
				// Adoption: the unidentified row of a name the permit names.
				_, err = v.db.Exec(`UPDATE apps SET app_id = 'app-U' WHERE account_id = 8 AND app = 'blog8'`)
				requireDenied(t, "adopting an unidentified row", err)
				_, err = v.db.Exec(`UPDATE apps SET deleted_at = 5 WHERE account_id = 8 AND app = 'blog8'`)
				requireDenied(t, "projecting onto an unidentified row", err)
				_, err = v.db.Exec(`DELETE FROM apps WHERE account_id = 8 AND app = 'blog8'`)
				requireDenied(t, "deleting an unidentified row", err)
				if mode != claimModePurge {
					_, err = v.db.Exec(`DELETE FROM apps WHERE account_id = 7 AND app = 'blog'`)
					requireDenied(t, "delete", err)
					_, err = v.db.Exec(`DELETE FROM audit WHERE account_id = 7`)
					requireDenied(t, "delete side rows", err)
					tx, terr := v.db.Begin()
					if terr != nil {
						t.Fatal(terr)
					}
					_, err = tx.Exec(`DELETE FROM door_keys WHERE account_id = 7`)
					requireDenied(t, "tx delete", err)
					tx.Rollback()
				} else {
					_, err = v.db.Exec(`UPDATE door_key_revisions SET revision = revision - 1 WHERE account_id = 7`)
					requireDenied(t, "lowering the door-key revision", err)
					_, err = v.db.Exec(`UPDATE door_keys SET label = 'x' WHERE account_id = 7`)
					requireDenied(t, "door key label", err)
				}
				return nil
			})
			if err != nil {
				t.Fatal(err)
			}
			if countWhere(t, s, "audit", "action <> 'seed'") != 0 || countWhere(t, s, "audit", "action = 'seed'") != 1 ||
				countWhere(t, s, "apps", "placement <> '' OR region <> ''") != 0 ||
				countWhere(t, s, "apps", "app = 'blog8' AND app_id = '' AND deleted_at = 0") != 1 || countWhere(t, s, "apps", "app = 'blog' AND app_id = 'app-A'") != 1 {
				t.Fatal("a terminal permit wrote outside its family")
			}
		})
	}
}

// Each terminal mode's own family passes, on its own incarnation only.
func TestClaimSQLTerminalModesAllowTheirFamily(t *testing.T) {
	t.Run("trash", func(t *testing.T) {
		s := openGatedStore(t)
		seedTerminalWorld(t, s)
		err := s.withClaimSQL(gateCtx(t), []claimSQLScope{scopeMode(gateScope, claimModeTrash)}, allowAllClaimSQL, func(v *Store) error {
			_, err := v.SetClaimRestoring(gateAcct, "blog", "app-A", false)
			return err
		})
		if err != nil || countWhere(t, s, "apps", "app = 'blog' AND restoring = 0") != 1 {
			t.Fatalf("trash projection: %v", err)
		}
	})
	t.Run("restore", func(t *testing.T) {
		s := openGatedStore(t)
		seedTerminalWorld(t, s)
		err := s.withClaimSQL(gateCtx(t), []claimSQLScope{scopeMode(gateScope, claimModeRestore)}, allowAllClaimSQL, func(v *Store) error {
			if changed, err := v.FinishClaimRestoreRow(gateAcct, "blog", "app-A", 99, time.Unix(1000, 0)); err != nil || changed {
				return fmt.Errorf("a receipt for another trash restored the row: %v %v", changed, err)
			}
			_, err := v.FinishClaimRestoreRow(gateAcct, "blog", "app-A", 100, time.Unix(1000, 0))
			return err
		})
		if err != nil || countWhere(t, s, "apps", "app = 'blog' AND deleted_at = 0 AND restoring = 0") != 1 {
			t.Fatalf("restore projection: %v", err)
		}
	})
	t.Run("purge", func(t *testing.T) {
		s := openGatedStore(t)
		seedTerminalWorld(t, s)
		err := s.withClaimSQL(gateCtx(t), []claimSQLScope{scopeMode(gateScope, claimModePurge)}, allowAllClaimSQL, func(v *Store) error {
			if err := v.BindClaimAppOperation(gateAcct, "blog", "app-A", "purge-1", 9); err != nil {
				return err
			}
			if err := v.MarkClaimAppRetired(gateAcct, "blog", "app-A", time.Unix(1000, 0)); err != nil {
				return err
			}
			return v.PurgeClaimAppRow(gateCtx(t), gateAcct, "blog", "app-A", "purge-1")
		})
		if err != nil {
			t.Fatalf("purge replay: %v", err)
		}
		if countWhere(t, s, "apps", "app = 'blog'") != 0 || countWhere(t, s, "mail_events", "app = 'blog'") != 0 || countWhere(t, s, "cli_tokens", "app = 'blog'") != 0 ||
			countWhere(t, s, "door_keys", "app = 'blog' AND revoked_at = 0") != 0 || countWhere(t, s, "door_key_revisions", "app = 'blog' AND revision = 4") != 1 {
			t.Fatal("purge replay left the retired incarnation's rows")
		}
		if countWhere(t, s, "apps", "app = 'blog8'") != 1 {
			t.Fatal("purge reached another app")
		}
	})
}

// A purge permit for a retired incarnation cannot reach a successor
// incarnation's rows: not its apps row (other app_id), and not the
// app-keyed side rows while a successor apps row exists.
func TestClaimSQLPurgeModeSparesSuccessor(t *testing.T) {
	s := openGatedStore(t)
	seedTerminalWorld(t, s)
	retired := claimSQLScope{AccountID: gateAcct, App: "blog", AppID: "app-OLD", Mode: claimModePurge}
	err := s.withClaimSQL(gateCtx(t), []claimSQLScope{retired}, allowAllClaimSQL, func(v *Store) error {
		_, err := v.db.Exec(`DELETE FROM apps WHERE account_id = 7 AND app = 'blog'`)
		requireDenied(t, "successor apps row", err)
		_, err = v.db.Exec(`DELETE FROM mail_events WHERE account = 'acct7' AND app = 'blog'`)
		requireDenied(t, "side rows while a successor row exists", err)
		_, err = v.db.Exec(`UPDATE door_keys SET revoked_at = 5 WHERE account_id = 7 AND app = 'blog'`)
		requireDenied(t, "successor door keys", err)
		_, err = v.db.Exec(`UPDATE apps SET deleted_at = 1 WHERE account_id = 7 AND app = 'blog'`)
		requireDenied(t, "retiring the successor row", err)
		return nil
	})
	if err != nil {
		t.Fatal(err)
	}
	if countWhere(t, s, "apps", "app = 'blog'") != 1 || countWhere(t, s, "mail_events", "app = 'blog'") != 1 || countWhere(t, s, "door_keys", "app = 'blog' AND revoked_at = 0") != 1 {
		t.Fatal("a retired incarnation's purge permit reached its successor")
	}
}