package main

import (
	"context"
	"database/sql"
	"fmt"
	"io"
	"log/slog"
	"net/http"
	"net/http/cookiejar"
	"net/http/httptest"
	"net/url"
	"os"
	"path/filepath"
	"regexp"
	"strconv"
	"strings"
	"testing"

	"github.com/carlosframework/rastrillo/db"
	"github.com/carlosframework/rastrillo/migrate"
	"github.com/carlosframework/rastrillo/password"

	"amadan.net/rastrillo/idear"
)

// The reviewers' standing complaint about idear was that nothing had
// been proven against a real app shell — every earlier test drove
// idear's own handlers over idear's own harness. These tests drive THE
// EXAMPLE: its router, its templates, its identity plugin, its
// migrations, over a real HTTP server with a real cookie jar. If the
// mount is wrong, they go red; that is the point of them.

// testApp is the example, served.
type testApp struct {
	t      *testing.T
	app    *app
	db     *db.DB
	server *httptest.Server
	origin string
}

func newTestApp(t *testing.T) *testApp {
	t.Helper()
	d, err := db.Open(filepath.Join(t.TempDir(), "board.db"), nil)
	if err != nil {
		t.Fatalf("db.Open: %v", err)
	}
	t.Cleanup(func() { d.Close() })

	// The listener exists before Start, so the origin — which decides
	// the CSRF check and the cookie attributes — is knowable before
	// the app that has to be configured with it.
	srv := httptest.NewUnstartedServer(nil)
	origin := "http://" + srv.Listener.Addr().String()

	a, err := newApp(d, origin, "The Example Board", slog.New(slog.NewTextHandler(io.Discard, nil)))
	if err != nil {
		t.Fatalf("newApp: %v", err)
	}
	srv.Config.Handler = a.mux
	srv.Start()
	t.Cleanup(srv.Close)

	return &testApp{t: t, app: a, db: d, server: srv, origin: origin}
}

// client is one browser: a cookie jar, and whatever session it holds.
type client struct {
	ta   *testApp
	http *http.Client
}

func (ta *testApp) visitor() *client {
	ta.t.Helper()
	jar, err := cookiejar.New(nil)
	if err != nil {
		ta.t.Fatalf("cookiejar.New: %v", err)
	}
	return &client{ta: ta, http: &http.Client{
		Jar: jar,
		// Redirects are not followed: the 303 IS the assertion on
		// every successful mutation, and a client that chased it would
		// report the destination's 200 instead.
		CheckRedirect: func(*http.Request, []*http.Request) error { return http.ErrUseLastResponse },
	}}
}

type result struct {
	Status   int
	Body     string
	Location string
}

func (c *client) get(path string) *result { return c.do(http.MethodGet, path, nil) }

func (c *client) post(path string, form url.Values) *result {
	return c.do(http.MethodPost, path, form)
}

func (c *client) do(method, path string, form url.Values) *result {
	c.ta.t.Helper()
	var body io.Reader
	if form != nil {
		body = strings.NewReader(form.Encode())
	}
	req, err := http.NewRequest(method, c.ta.origin+path, body)
	if err != nil {
		c.ta.t.Fatalf("%s %s: %v", method, path, err)
	}
	if form != nil {
		req.Header.Set("Content-Type", "application/x-www-form-urlencoded")
	}
	if method != http.MethodGet {
		// The evidence a browser sends on a same-origin form
		// submission. csrf.Protect is mounted app-wide, so a mutation
		// without it is refused 403 — which is exactly what a
		// cross-site forgery looks like. It is set for every mutation,
		// body or no body: several of idear's routes (remove, restore,
		// revoke) are buttons with nothing in the form at all.
		req.Header.Set("Origin", c.ta.origin)
	}
	res, err := c.http.Do(req)
	if err != nil {
		c.ta.t.Fatalf("%s %s: %v", method, path, err)
	}
	defer res.Body.Close()
	b, err := io.ReadAll(res.Body)
	if err != nil {
		c.ta.t.Fatalf("reading %s: %v", path, err)
	}
	return &result{Status: res.StatusCode, Body: string(b), Location: res.Header.Get("Location")}
}

// follow chases a redirect the way a browser would: the flash notice
// is only readable on the page the 303 lands on.
func (c *client) follow(res *result) *result {
	c.ta.t.Helper()
	if res.Location == "" {
		c.ta.t.Fatalf("nothing to follow: status %d, body %q", res.Status, res.Body)
	}
	return c.get(res.Location)
}

// signUp posts the signup form, with an invitation token when there is
// one. The "invite" field is what rs.CarryToken reads.
func (c *client) signUp(email, invite string) *result {
	c.ta.t.Helper()
	return c.signUpWith(email, invite, "demo-password")
}

func (c *client) signUpWith(email, invite, pw string) *result {
	c.ta.t.Helper()
	form := url.Values{"email": {email}, "password": {pw}}
	if invite != "" {
		form.Set("invite", invite)
	}
	return c.post("/signup", form)
}

func (c *client) signIn(email string) *result {
	c.ta.t.Helper()
	return c.post("/signin", url.Values{"email": {email}, "password": {"demo-password"}})
}

// tokenPattern pulls the invitation link out of the flash notice.
// HandlerConfig.Deliver is nil in this example, so idear puts the link
// itself in the notice the inviting admin sees — see app.go.
var tokenPattern = regexp.MustCompile(`/invitations/([0-9a-f]{64})`)

func (ta *testApp) tokenFrom(res *result) string {
	ta.t.Helper()
	m := tokenPattern.FindStringSubmatch(res.Body)
	if m == nil {
		ta.t.Fatalf("no invitation link in the page: %q", res.Body)
	}
	return m[1]
}

// memberID resolves an address to its roster id, for building the
// management URLs a real admin clicks.
func (ta *testApp) memberID(email string) int64 {
	ta.t.Helper()
	members, err := ta.app.roster.Members(context.Background())
	if err != nil {
		ta.t.Fatalf("listing members: %v", err)
	}
	for _, m := range members {
		if m.Email == email {
			return m.ID
		}
	}
	ta.t.Fatalf("no member with address %q in %+v", email, members)
	return 0
}

func (ta *testApp) member(email string) idear.Member {
	ta.t.Helper()
	m, err := ta.app.roster.ByID(context.Background(), ta.memberID(email))
	if err != nil {
		ta.t.Fatalf("loading member %q: %v", email, err)
	}
	return *m
}

func want(t *testing.T, res *result, status int, what string) {
	t.Helper()
	if res.Status != status {
		t.Fatalf("%s: status %d, want %d; body %q", what, res.Status, status, res.Body)
	}
}

// TestSignUpClaimInviteAcceptMembersRoleChange is the whole flow the
// example exists to prove, through real HTTP: sign up (claiming the
// instance), invite, accept the invitation as a second browser, read
// the members page, and change a role.
func TestSignUpClaimInviteAcceptMembersRoleChange(t *testing.T) {
	ta := newTestApp(t)

	// Signed out, "/" is the app's session guard's problem, not
	// idear's: a redirect to the sign-in page, never a 404. idear's
	// Require never redirects — that division is why it mounts INSIDE
	// this guard.
	res := ta.visitor().get("/")
	want(t, res, http.StatusSeeOther, "signed-out GET /")
	if !strings.HasPrefix(res.Location, "/signin") {
		t.Fatalf("signed-out GET / went to %q, want /signin", res.Location)
	}

	// The claim: the first account on an empty roster is the Owner,
	// with no invitation involved.
	ada := ta.visitor()
	res = ada.signUp("ada@example.test", "")
	want(t, res, http.StatusSeeOther, "first signup")
	if owner := ta.member("ada@example.test"); owner.Role != idear.RoleOwner {
		t.Fatalf("first account is %q, want owner", owner.Role)
	}

	// And now the instance is closed. A stranger with no token is
	// refused at 403 with idear's one constant refusal copy — no
	// mention of the address, and the same words whatever the reason.
	res = ta.visitor().signUp("mallory@example.test", "")
	want(t, res, http.StatusForbidden, "uninvited signup")
	if !strings.Contains(res.Body, "Sign-up here is by invitation.") {
		t.Fatalf("uninvited signup body %q, want the refusal copy", res.Body)
	}

	// The Owner reaches the board and the members page.
	want(t, ada.get("/"), http.StatusOK, "owner GET /")
	page := ada.get("/members")
	want(t, page, http.StatusOK, "owner GET /members")
	if !strings.Contains(page.Body, "ada@example.test") {
		t.Fatalf("members page does not list the owner: %q", page.Body)
	}
	// Grantable for an Owner is Admin and Member, never Owner:
	// ownership moves only by Transfer.
	if !strings.Contains(page.Body, `<option value="admin">`) ||
		!strings.Contains(page.Body, `<option value="member">`) {
		t.Fatalf("owner's role selector is missing admin/member: %q", page.Body)
	}
	if strings.Contains(page.Body, `<option value="owner">`) {
		t.Fatalf("owner's role selector offers owner: %q", page.Body)
	}

	// Invite an Admin. The link comes back in the flash notice.
	res = ada.post("/members/invitations", url.Values{
		"email": {"kim@example.test"}, "role": {"admin"},
	})
	want(t, res, http.StatusSeeOther, "invite")
	if res.Location != "/members" {
		t.Fatalf("invite redirected to %q, want /members", res.Location)
	}
	adminToken := ta.tokenFrom(ada.follow(res))

	// The PUBLIC invitation page: it names the role and the instance,
	// and it must not name the invited address.
	kim := ta.visitor()
	invitePage := kim.get("/invitations/" + adminToken)
	want(t, invitePage, http.StatusOK, "public invitation page")
	if !strings.Contains(invitePage.Body, "Admin") {
		t.Fatalf("invitation page does not name the role: %q", invitePage.Body)
	}
	if strings.Contains(invitePage.Body, "kim@example.test") {
		t.Fatalf("invitation page leaked the invited address: %q", invitePage.Body)
	}
	if !strings.Contains(invitePage.Body, `name="invite" value="`+adminToken+`"`) {
		t.Fatalf("invitation page has no hidden invite field: %q", invitePage.Body)
	}

	// Accept it by signing up with the token, which is what the hidden
	// field above posts and what rs.CarryToken lifts off the form.
	res = kim.signUp("kim@example.test", adminToken)
	want(t, res, http.StatusSeeOther, "invited signup")
	if got := ta.member("kim@example.test"); got.Role != idear.RoleAdmin {
		t.Fatalf("invited account is %q, want admin", got.Role)
	}
	want(t, kim.get("/"), http.StatusOK, "admin GET /")
	want(t, kim.get("/members"), http.StatusOK, "admin GET /members")

	// The Admin invites a plain Member. checkInviteRole is
	// strictly-below, so this is the only role an Admin may grant.
	res = kim.post("/members/invitations", url.Values{
		"email": {"sam@example.test"}, "role": {"member"},
	})
	want(t, res, http.StatusSeeOther, "admin invites a member")
	memberToken := ta.tokenFrom(kim.follow(res))

	sam := ta.visitor()
	res = sam.signUp("sam@example.test", memberToken)
	want(t, res, http.StatusSeeOther, "member signup")
	if got := ta.member("sam@example.test"); got.Role != idear.RoleMember {
		t.Fatalf("second invited account is %q, want member", got.Role)
	}

	// A plain Member sees the roster and is offered nothing to do to
	// it: Grantable is empty for anyone below Admin.
	page = sam.get("/members")
	want(t, page, http.StatusOK, "member GET /members")
	if strings.Contains(page.Body, "Invite someone") {
		t.Fatalf("a plain member is offered the invite form: %q", page.Body)
	}
	// And the management routes refuse them at 403 — they may see the
	// page, they may not act on it.
	res = sam.post("/members/invitations", url.Values{
		"email": {"eve@example.test"}, "role": {"member"},
	})
	want(t, res, http.StatusForbidden, "member tries to invite")

	samID := ta.memberID("sam@example.test")

	// THE ROLE CHANGE. The Admin cannot make a peer: idear refuses a
	// grant that is not strictly below the granter's rank, and it does
	// so at 403 rather than silently doing nothing.
	res = kim.post("/members/"+strconv.FormatInt(samID, 10)+"/role", url.Values{"role": {"admin"}})
	want(t, res, http.StatusForbidden, "admin promotes to admin")
	if got := ta.member("sam@example.test"); got.Role != idear.RoleMember {
		t.Fatalf("refused promotion still landed: role is %q", got.Role)
	}

	// The Owner can.
	res = ada.post("/members/"+strconv.FormatInt(samID, 10)+"/role", url.Values{"role": {"admin"}})
	want(t, res, http.StatusSeeOther, "owner promotes to admin")
	if got := ta.member("sam@example.test"); got.Role != idear.RoleAdmin {
		t.Fatalf("owner's promotion did not land: role is %q", got.Role)
	}
	if body := ada.follow(res).Body; !strings.Contains(body, "Role updated.") {
		t.Fatalf("members page after a role change: %q", body)
	}

	// role=owner never lands, on any path, for any actor — including
	// the Owner.
	res = ada.post("/members/"+strconv.FormatInt(samID, 10)+"/role", url.Values{"role": {"owner"}})
	want(t, res, http.StatusForbidden, "owner posts role=owner")
	if got := ta.member("sam@example.test"); got.Role != idear.RoleAdmin {
		t.Fatalf("role=owner landed: role is %q", got.Role)
	}
}

// TestDeactivationIsEnforcedPerRequestNotAtSignIn is the reason "/" is
// behind Require, stated as a test.
//
// password.Signin runs Lookup, Verify and mint with no idear
// involvement at all, so a removed member still signs in successfully.
// What refuses them is Require, on every route, per request. An
// ungated landing page is the one place this design leaks, and this
// test is what would notice one appearing.
func TestDeactivationIsEnforcedPerRequestNotAtSignIn(t *testing.T) {
	ta := newTestApp(t)

	ada := ta.visitor()
	want(t, ada.signUp("ada@example.test", ""), http.StatusSeeOther, "claim")

	res := ada.post("/members/invitations", url.Values{
		"email": {"sam@example.test"}, "role": {"member"},
	})
	want(t, res, http.StatusSeeOther, "invite")
	token := ta.tokenFrom(ada.follow(res))

	sam := ta.visitor()
	want(t, sam.signUp("sam@example.test", token), http.StatusSeeOther, "accept")
	want(t, sam.get("/"), http.StatusOK, "member GET / before removal")

	samID := ta.memberID("sam@example.test")
	want(t, ada.post("/members/"+strconv.FormatInt(samID, 10)+"/remove", nil),
		http.StatusSeeOther, "remove")

	// The removed member's LIVE session stops resolving on the very
	// next request.
	want(t, sam.get("/"), http.StatusNotFound, "removed member GET / on the old session")

	// And a fresh sign-in still SUCCEEDS — the password plugin knows
	// nothing about the roster — yet buys nothing at all.
	back := ta.visitor()
	res = back.signIn("sam@example.test")
	want(t, res, http.StatusSeeOther, "removed member signs in")
	gated := back.get("/")
	want(t, gated, http.StatusNotFound, "removed member GET / on a fresh session")
	want(t, back.get("/members"), http.StatusNotFound, "removed member GET /members")

	// THE 404 IS THE APP'S OWN, and it is byte-identical to the 404
	// for a path that simply does not exist. Two different renderers
	// here would let a stranger tell "this exists but not for you"
	// from "no such route", which is the membership oracle the whole
	// design is arranged around — and it is the one misconfiguration
	// idear cannot detect at runtime.
	nowhere := back.get("/no-such-path-at-all")
	want(t, nowhere, http.StatusNotFound, "chi's own NotFound")
	if gated.Body != nowhere.Body {
		t.Fatalf("idear's 404 and chi's 404 differ:\nidear: %q\nchi:   %q", gated.Body, nowhere.Body)
	}
	if !strings.Contains(nowhere.Body, "There is nothing here.") {
		t.Fatalf("the 404 is not the app's own page: %q", nowhere.Body)
	}

	// Restored, they are back — at the role their row still carried.
	want(t, ada.post("/members/"+strconv.FormatInt(samID, 10)+"/restore", nil),
		http.StatusSeeOther, "restore")
	want(t, back.get("/"), http.StatusOK, "restored member GET /")
}

// TestAppRouteRoleGate proves the app's OWN role-gated route, which is
// the stacking every app has to get right: RequireRole inside Require,
// never bare.
func TestAppRouteRoleGate(t *testing.T) {
	ta := newTestApp(t)
	if err := Seed(context.Background(), ta.app.db, ta.app.roster); err != nil {
		t.Fatalf("Seed: %v", err)
	}

	member := ta.visitor()
	want(t, member.signIn(SeedMember), http.StatusSeeOther, "member signs in")
	board := member.get("/")
	want(t, board, http.StatusOK, "member GET /")
	// The template hides the control...
	if strings.Contains(board.Body, "/delete") {
		t.Fatalf("a plain member is shown a delete button: %q", board.Body)
	}
	// ...and the route refuses it anyway, which is the half that
	// counts.
	var post Post
	if err := ta.app.db.First(&post).Error; err != nil {
		t.Fatalf("loading the seeded post: %v", err)
	}
	id := strconv.FormatInt(post.ID, 10)
	want(t, member.post("/posts/"+id+"/delete", nil), http.StatusForbidden, "member deletes a post")

	admin := ta.visitor()
	want(t, admin.signIn(SeedAdmin), http.StatusSeeOther, "admin signs in")
	want(t, admin.post("/posts/"+id+"/delete", nil), http.StatusSeeOther, "admin deletes a post")

	// A member may still post: Require admits them, RequireRole is
	// only on the delete route.
	want(t, member.post("/posts", url.Values{"body": {"hello"}}), http.StatusSeeOther, "member posts")
}

// TestCSRFRefusesACrossOriginMutation pins that csrf.Protect is
// actually mounted app-wide, over idear's routes as well as the app's.
func TestCSRFRefusesACrossOriginMutation(t *testing.T) {
	ta := newTestApp(t)
	ada := ta.visitor()
	want(t, ada.signUp("ada@example.test", ""), http.StatusSeeOther, "claim")

	req, err := http.NewRequest(http.MethodPost, ta.origin+"/members/invitations",
		strings.NewReader(url.Values{"email": {"eve@example.test"}, "role": {"admin"}}.Encode()))
	if err != nil {
		t.Fatalf("building the request: %v", err)
	}
	req.Header.Set("Content-Type", "application/x-www-form-urlencoded")
	req.Header.Set("Origin", "https://attacker.example")
	res, err := ada.http.Do(req)
	if err != nil {
		t.Fatalf("posting: %v", err)
	}
	defer res.Body.Close()
	if res.StatusCode != http.StatusForbidden {
		t.Fatalf("cross-origin POST to an idear route: status %d, want 403", res.StatusCode)
	}
}

// TestSeedProducesThreeRoles: the seed is what makes the role gates
// clickable, and it is idempotent.
func TestSeedProducesThreeRoles(t *testing.T) {
	ta := newTestApp(t)
	ctx := context.Background()
	if err := Seed(ctx, ta.app.db, ta.app.roster); err != nil {
		t.Fatalf("Seed: %v", err)
	}
	if err := Seed(ctx, ta.app.db, ta.app.roster); err != nil {
		t.Fatalf("Seed again: %v", err)
	}
	members, err := ta.app.roster.Members(ctx)
	if err != nil {
		t.Fatalf("Members: %v", err)
	}
	if len(members) != 3 {
		t.Fatalf("seed produced %d members, want 3: %+v", len(members), members)
	}
	byRole := map[idear.Role]string{}
	for _, m := range members {
		byRole[m.Role] = m.Email
	}
	for role, email := range map[idear.Role]string{
		idear.RoleOwner:  SeedOwner,
		idear.RoleAdmin:  SeedAdmin,
		idear.RoleMember: SeedMember,
	} {
		if byRole[role] != email {
			t.Errorf("%s is %q, want %q", role, byRole[role], email)
		}
	}
}

// TestSchemaAndModelsAgree is `rastrillo migration check` in test form:
// replay this app's OWN Schema and diff the result against this app's
// OWN Models. It goes red the moment a model changes without a
// migration — and it is also what would catch an idear model being
// added to Models, since idear's tables reach the database through
// BootSchema and are not in Schema at all.
func TestSchemaAndModelsAgree(t *testing.T) {
	changes, err := migrate.Generate(context.Background(), Schema.All(), Models)
	if err != nil {
		t.Fatalf("migrate.Generate: %v", err)
	}
	if len(changes) != 0 {
		for _, c := range changes {
			t.Errorf("pending change: %+v", c)
		}
		t.Fatalf("Schema and Models disagree: %d pending change(s)", len(changes))
	}
}

// TestInvitedSignupSurvivesAValidationFailure is the workaround in
// renderSignup, pinned.
//
// password.PageData has Error, Email and ReturnTo and NOWHERE to put
// an invitation token, so a signup that fails validation re-renders a
// form whose hidden "invite" field comes back empty unless the app
// puts it back. The invitee's FIRST attempt then looks fine and their
// SECOND is refused for holding no token — "invited people can never
// join", surfacing one step later than the mistake.
//
// This is the one piece of copy-this-code an app cannot get from
// idear's mount, and rewriting the signup page is the likeliest thing
// a copier does. Deleting idear.TokenFrom(r) from render.go turns the
// 422 assertion below red.
func TestInvitedSignupSurvivesAValidationFailure(t *testing.T) {
	ta := newTestApp(t)

	ada := ta.visitor()
	want(t, ada.signUp("ada@example.test", ""), http.StatusSeeOther, "claim")
	res := ada.post("/members/invitations", url.Values{
		"email": {"kim@example.test"}, "role": {"admin"},
	})
	want(t, res, http.StatusSeeOther, "invite")
	token := ta.tokenFrom(ada.follow(res))

	// A password too short for password's own rule: 422, and the form
	// comes back. The token must come back WITH it.
	kim := ta.visitor()
	res = kim.signUpWith("kim@example.test", token, "short")
	want(t, res, http.StatusUnprocessableEntity, "signup with a short password")
	if !strings.Contains(res.Body, `name="invite" value="`+token+`"`) {
		t.Fatalf("the re-rendered signup form dropped the invitation token: %q", res.Body)
	}

	// The second attempt — the one a real invitee makes — still works,
	// and still lands at the invited role rather than being refused.
	res = kim.signUpWith("kim@example.test", token, "demo-password")
	want(t, res, http.StatusSeeOther, "second signup attempt")
	if got := ta.member("kim@example.test"); got.Role != idear.RoleAdmin {
		t.Fatalf("second attempt admitted at %q, want admin", got.Role)
	}
}

// ---------------------------------------------------------------
// SKILL.md §6 break-glass: rebinding a subject.
// ---------------------------------------------------------------

// TestBreakGlassRebindsASubject executes the rebind block from
// SKILL.md §6 — READ OUT OF THE FILE, not restated here — against a
// database built by this app's own migrations, and then proves the
// result end to end: sign in over real HTTP as the new subject and
// perform an Owner-only action with it.
//
// Documented recovery SQL that nobody has run is a guess. §6's other
// block, the ownership transfer, was verified this way and the review
// found `deactivated_at = NULL` in it load-bearing; this one carries
// the same clause for the same reason, plus the unique index on
// subject, which is what turns a careless rebind into a failed UPDATE.
//
// Reading the SQL from the document rather than copying it here is the
// point of the test. A copy can go stale silently; this cannot — edit
// §6's block into something that does not work and this test is what
// says so.
func TestBreakGlassRebindsASubject(t *testing.T) {
	ta := newTestApp(t)
	ctx := context.Background()
	if err := Seed(ctx, ta.app.db, ta.app.roster); err != nil {
		t.Fatalf("Seed: %v", err)
	}
	owner := ta.member(SeedOwner)
	if owner.Role != idear.RoleOwner {
		t.Fatalf("the seeded owner is %s", owner.Role)
	}
	successorID := ta.memberID(SeedAdmin)

	// The Owner's identity changed. Under keymail that is a new
	// address; under password — this app — it is a new user row, and
	// the subject is that row's decimal id. Either way the roster row
	// still points at the old one, and the person is now a stranger to
	// every guarded route.
	const newAddress = "ada.new@example.test"
	hash, err := password.Hash(SeedPassword)
	if err != nil {
		t.Fatalf("password.Hash: %v", err)
	}
	newID, err := createUser(ta.app.db)(ctx, newAddress, hash)
	if err != nil {
		t.Fatalf("creating the replacement user: %v", err)
	}

	stranger := ta.visitor()
	want(t, stranger.signIn(newAddress), http.StatusSeeOther, "sign in as the new identity")
	if res := stranger.get("/"); res.Status != http.StatusNotFound {
		t.Fatalf("the new identity reached the board before any rebind: status %d", res.Status)
	}

	// The break-glass, verbatim: only the three placeholders are
	// filled in, exactly as an operator editing the block would.
	script := breakGlassRebind(t, map[string]string{
		"OLD-SUBJECT": owner.Subject,
		"NEW-SUBJECT": subjectFor(newID),
		"NEW-EMAIL":   newAddress,
	})
	t.Log("\n" + runSQLScript(t, ta, script))

	// End to end, through the front door: a real sign-in, a real
	// session, and the membership gate.
	ada := ta.visitor()
	want(t, ada.signIn(newAddress), http.StatusSeeOther, "sign in as the rebound owner")
	want(t, ada.get("/"), http.StatusOK, "the board, as the rebound owner")

	// The OWNER-ONLY action. RequireRole(RoleOwner) 403s everybody
	// else, and Transfer is the only route behind it — so a 303 here
	// is the rebind having restored ownership and not merely
	// membership.
	res := ada.post("/members/transfer", url.Values{
		"member": {strconv.FormatInt(successorID, 10)},
	})
	want(t, res, http.StatusSeeOther, "transfer ownership as the rebound owner")
	if got := ta.member(SeedAdmin); got.Role != idear.RoleOwner {
		t.Fatalf("after the transfer %s is %s, want owner", SeedAdmin, got.Role)
	}
	if got := ta.member(newAddress); got.ID != owner.ID || got.Role != idear.RoleAdmin {
		t.Fatalf("the rebound row is %+v, want member %d demoted to admin by its own transfer", got, owner.ID)
	}

	// And the OLD subject is nobody: the old user row still signs in —
	// credentials are the app's table, not idear's — and 404s on every
	// guarded route, which is what a rebind means.
	old := ta.visitor()
	want(t, old.signIn(SeedOwner), http.StatusSeeOther, "sign in as the old identity")
	if res := old.get("/"); res.Status != http.StatusNotFound {
		t.Fatalf("the old subject still reached the board after the rebind: status %d", res.Status)
	}
}

// TestBreakGlassRebindClearsDeactivation is the OTHER half of the §6
// rebind, and it is why `deactivated_at = NULL` is in that statement.
//
// The realistic shape: somebody was offboarded (correctly — §7 says
// offboarding under keymail MUST deactivate), then came back under a
// new identity. The row still carries their role and their removal.
// Rebound without clearing it, the new subject resolves to a
// deactivated member and 404s on every guarded route — which is
// indistinguishable, from the operator's side, from the rebind not
// having worked at all. Delete that clause from SKILL.md and the GET
// below turns red.
func TestBreakGlassRebindClearsDeactivation(t *testing.T) {
	ta := newTestApp(t)
	ctx := context.Background()
	if err := Seed(ctx, ta.app.db, ta.app.roster); err != nil {
		t.Fatalf("Seed: %v", err)
	}
	kim := ta.member(SeedAdmin)

	// Removed through the real route, by the real Owner.
	ada := ta.visitor()
	want(t, ada.signIn(SeedOwner), http.StatusSeeOther, "sign in as the owner")
	want(t, ada.post(fmt.Sprintf("/members/%d/remove", kim.ID), url.Values{}),
		http.StatusSeeOther, "remove the admin")
	if removed := ta.member(SeedAdmin); removed.Active() {
		t.Fatal("the admin is still active after remove")
	}

	const newAddress = "kim.new@example.test"
	hash, err := password.Hash(SeedPassword)
	if err != nil {
		t.Fatalf("password.Hash: %v", err)
	}
	newID, err := createUser(ta.app.db)(ctx, newAddress, hash)
	if err != nil {
		t.Fatalf("creating the replacement user: %v", err)
	}

	t.Log("\n" + runSQLScript(t, ta, breakGlassRebind(t, map[string]string{
		"OLD-SUBJECT": kim.Subject,
		"NEW-SUBJECT": subjectFor(newID),
		"NEW-EMAIL":   newAddress,
	})))

	back := ta.visitor()
	want(t, back.signIn(newAddress), http.StatusSeeOther, "sign in as the rebound admin")
	want(t, back.get("/"), http.StatusOK, "the board, as the rebound admin")
	got := ta.member(newAddress)
	if got.ID != kim.ID || got.Role != idear.RoleAdmin || !got.Active() {
		t.Fatalf("the rebound row is %+v, want member %d, admin, active", got, kim.ID)
	}
}

// breakGlassRebind returns SKILL.md §6's rebind block with its
// placeholders replaced. The block is located by a placeholder rather
// than by a heading or an index, so reordering the document does not
// silently point this at some other SQL.
func breakGlassRebind(t *testing.T, subs map[string]string) string {
	t.Helper()
	doc, err := os.ReadFile(filepath.Join("..", "SKILL.md"))
	if err != nil {
		t.Fatalf("reading SKILL.md: %v", err)
	}
	block := sqlBlock(t, string(doc), "OLD-SUBJECT")
	for name, value := range subs {
		if !strings.Contains(block, "'"+name+"'") {
			t.Fatalf("SKILL.md's rebind block has no '%s' placeholder to fill in:\n%s", name, block)
		}
		// Only the QUOTED placeholder is filled in — the same edit an
		// operator makes — so the prose above each statement is left
		// as the document wrote it.
		block = strings.ReplaceAll(block, "'"+name+"'", "'"+value+"'")
	}
	return block
}

// sqlBlock is the one ```sql fence in doc that mentions needle.
// Exactly one: two would mean the test is guessing.
func sqlBlock(t *testing.T, doc, needle string) string {
	t.Helper()
	var found []string
	var cur *strings.Builder
	for _, line := range strings.Split(doc, "\n") {
		switch {
		case cur == nil && strings.TrimSpace(line) == "```sql":
			cur = &strings.Builder{}
		case cur != nil && strings.TrimSpace(line) == "```":
			if strings.Contains(cur.String(), needle) {
				found = append(found, cur.String())
			}
			cur = nil
		case cur != nil:
			cur.WriteString(line + "\n")
		}
	}
	if len(found) != 1 {
		t.Fatalf("SKILL.md has %d sql blocks mentioning %q, want exactly 1", len(found), needle)
	}
	return found[0]
}

// runSQLScript executes the script one statement at a time on a SINGLE
// writer connection — the sqlite3 session an operator would have — and
// returns a transcript of what each statement did.
//
// One connection is not a detail: the block wraps its UPDATE in
// BEGIN/COMMIT, and a pool that handed those three statements to
// different connections would prove nothing about the transaction the
// document tells an operator to run.
func runSQLScript(t *testing.T, ta *testApp, script string) string {
	t.Helper()
	ctx := context.Background()
	conn, err := ta.db.Writer().Conn(ctx)
	if err != nil {
		t.Fatalf("taking the writer connection: %v", err)
	}
	defer conn.Close()

	var out strings.Builder
	for _, stmt := range strings.Split(script, ";") {
		stmt = strings.TrimSpace(stmt)
		if stmt == "" {
			continue
		}
		fmt.Fprintf(&out, "sqlite> %s;\n", stmt)
		if strings.HasPrefix(strings.ToUpper(bare(stmt)), "SELECT") {
			fmt.Fprint(&out, query(t, ctx, conn, stmt))
			continue
		}
		res, err := conn.ExecContext(ctx, stmt)
		if err != nil {
			t.Fatalf("SKILL.md §6 statement failed: %v\n%s", err, stmt)
		}
		switch verb := strings.ToUpper(bare(stmt)); {
		case strings.HasPrefix(verb, "BEGIN"), strings.HasPrefix(verb, "COMMIT"):
			// RowsAffected means nothing for these, and the driver
			// answers 1 for both — a number in the transcript that
			// looked like a result would be worse than no number.
			out.WriteString("-- ok\n")
		default:
			n, err := res.RowsAffected()
			if err != nil {
				t.Fatalf("reading rows affected: %v", err)
			}
			fmt.Fprintf(&out, "-- %d row(s)\n", n)
		}
	}
	return out.String()
}

// bare strips the leading comment lines from a statement, so the
// SELECT/other decision reads the SQL and not the prose above it.
func bare(stmt string) string {
	for _, line := range strings.Split(stmt, "\n") {
		line = strings.TrimSpace(line)
		if line != "" && !strings.HasPrefix(line, "--") {
			return line
		}
	}
	return ""
}

// query runs a SELECT and renders its rows. Values are scanned into
// any, not into a typed column list: this prints whatever the schema
// holds, including the NULL deactivated_at the verification step is
// looking for.
func query(t *testing.T, ctx context.Context, conn *sql.Conn, stmt string) string {
	t.Helper()
	rows, err := conn.QueryContext(ctx, stmt)
	if err != nil {
		t.Fatalf("SKILL.md §6 query failed: %v\n%s", err, stmt)
	}
	defer rows.Close()
	cols, err := rows.Columns()
	if err != nil {
		t.Fatalf("reading columns: %v", err)
	}

	var out strings.Builder
	fmt.Fprintf(&out, "%s\n", strings.Join(cols, "|"))
	n := 0
	for rows.Next() {
		cells := make([]any, len(cols))
		into := make([]any, len(cols))
		for i := range cells {
			into[i] = &cells[i]
		}
		if err := rows.Scan(into...); err != nil {
			t.Fatalf("scanning a row: %v", err)
		}
		parts := make([]string, len(cells))
		for i, c := range cells {
			parts[i] = fmt.Sprintf("%v", c)
		}
		fmt.Fprintf(&out, "%s\n", strings.Join(parts, "|"))
		n++
	}
	if err := rows.Err(); err != nil {
		t.Fatalf("reading rows: %v", err)
	}
	if n == 0 {
		out.WriteString("-- no rows\n")
	}
	return out.String()
}