// CLI identity, storage half: the approval requests `carlos auth login`
// opens, and the bearer tokens the console issues when a human approves one.
//
// Shape borrowed from the OAuth 2.1 device authorization grant (RFC 8628),
// because the CLI's real constraint is the same one that grant exists for:
// the terminal asking for the credential frequently has no browser on it —
// it is an SSH session on a box, or a headless CI shell. So the console
// prints a SHORT code the human carries to any browser they do have, and the
// CLI polls with a long code it never shows anyone. No local callback
// listener, no assumption that localhost:port is reachable from the browser.
//
// Two secrets, deliberately different:
//
//   - the user code is short (39 bits) because a human types it. It is stored
//     in the clear — it must be looked up BY value when the human types it —
//     and is only safe because it lives ten minutes, is rate-limited at the
//     door, and grants nothing on its own: approving it hands the token to
//     whoever holds the DEVICE code, not to whoever typed the user code.
//   - the device code is 256 bits and is the CLI's bearer secret for the
//     length of the flow. Only its sha256 is stored, like sessions.
package console

import (
	"context"
	"crypto/rand"
	"crypto/subtle"
	"database/sql"
	"encoding/hex"
	"errors"
	"fmt"
	"strings"
	"sync/atomic"
	"time"

	"github.com/carlosframework/platform/internal/runs"
	"github.com/carlosframework/platform/internal/svcrole"
)

const (
	// CLIAuthTTL bounds one login attempt — the same ten minutes the browser
	// OAuth flow gets. It bounds the APPROVED state too: an approval nobody
	// collected is a CLI that walked away, and it must not mint a token later.
	CLIAuthTTL = 10 * time.Minute

	// CLITokenTTL is how long an issued CLI token lives: 90 days.
	//
	// Longer than the 30-day browser session on purpose. Re-authenticating a
	// CLI is not a click — it is "open a browser, find the code, approve" —
	// and a credential that expires monthly trains people to paste the token
	// into a shell profile or a CI secret and never think about it again,
	// which is strictly worse than a bounded, revocable, listed token. 90 days
	// is a quarter: long enough that nobody routes around it, short enough
	// that a laptop that fell out of a bag stops being an identity by itself.
	// Every token is revocable the moment it is suspect (see CLITokens).
	CLITokenTTL = 90 * 24 * time.Hour

	// userCodeLen is the length of the typed code, before the grouping dash.
	// Eight characters of the 30-symbol alphabet below is ~39 bits
	// (8·log2(30) = 39.3), which with a ten-minute life and a rate-limited
	// approval endpoint is far more than guessing survives.
	userCodeLen = 8

	// userCodeAlphabet has no 0/O, 1/I/L or U (which turns into V in some
	// hands and is the usual source of accidental profanity in code
	// generators). What is left is 30 symbols, unambiguous read aloud down a
	// phone.
	userCodeAlphabet = "23456789ABCDEFGHJKMNPQRSTVWXYZ"
)

// Poll statuses. Ours, not RFC 8628's error codes: this is the console's own
// endpoint and a plain word is what the CLI prints to a human.
const (
	CLIAuthPending  = "pending"
	CLIAuthApproved = "approved"
	CLIAuthDenied   = "denied"
	CLIAuthExpired  = "expired"
	// CLIAuthUnknown covers "never existed" and "already collected" with one
	// answer, so a device code cannot be used to probe for live flows.
	CLIAuthUnknown = "unknown"
)

// CLIAuthRequest is one pending login, as the approval page sees it. It
// deliberately carries no secret: the page renders the label and where the
// request came from so a human can recognise their own terminal.
type CLIAuthRequest struct {
	UserCode   string
	Label      string
	RemoteAddr string
	CreatedAt  time.Time
	ExpiresAt  time.Time
}

// CLIToken is one issued credential as its owner sees it on the console. ID
// is the token's sha256, which is safe to put in a page and in a form: it
// names the row without being the bearer secret.
//
// Kind/AccountID/App carry what an app token IS, because a listing that
// showed only Label and ExpiresAt would render a never-expiring app token
// (expires_at 0) as expiring on 1 Jan 1970 — a credential that reads as
// long dead is one nobody thinks to rotate, and one whose "Revoke" button
// gets pressed by somebody who thinks it does nothing.
type CLIToken struct {
	ID         string
	Address    string
	Label      string
	RemoteAddr string
	Kind       string // "" = a person's token, "app" = app-held (see NewAppToken), "service" = a service principal
	AccountID  int64  // app/service tokens only: the account it is bound to
	App        string // app/service tokens only: the app it is bound to ("" = account-scoped)
	// Role is a svcrole catalogue name, service principals only. Plain
	// string, not svcrole.Role: this crosses into an html/template, which
	// cannot call svcrole.Parse.
	Role      string
	Name      string // service principals only: the stable handle rotate/revoke address by
	CreatedAt time.Time
	ExpiresAt time.Time
	LastUsed  time.Time
	RevokedAt time.Time // zero unless the credential has been revoked
	// RotatedAt is when this credential's CURRENT secret began: stamped at
	// mint and again at every RotateServicePrincipal, left alone by
	// everything else. CreatedAt is when the credential was granted and
	// never changes; RotatedAt is how a rotation policy answers "how old is
	// the secret in use right now" without that question destroying the
	// answer to "how long has this credential existed". Zero for every row
	// minted before this column existed, including every legacy kind='app'
	// row NewAppToken mints — those never rotate through this path.
	RotatedAt time.Time
	// Rungs is a deploy credential's scope in its stored, canonical form
	// ("canary,edge"); empty for every other kind and role.
	Rungs string
	// Commands is a run credential's granted command prefixes in their
	// stored form, canonical JSON; empty for every other kind and role.
	Commands string
}

// RungList is Rungs for a person to read: "canary, edge".
func (t CLIToken) RungList() string { return strings.ReplaceAll(t.Rungs, ",", ", ") }

// CommandPrefixes is Commands parsed: nil when there are none, or when the
// stored form cannot be read (a listing shows nothing rather than guessing;
// CLIPrincipal refuses such a row outright).
func (t CLIToken) CommandPrefixes() [][]string {
	p, ok := parseStoredCommands(t.Commands)
	if !ok {
		return nil
	}
	return p
}

// CommandsUnreadable reports a run credential whose stored command list
// cannot be read. CLIPrincipal refuses such a row, so a listing must say
// so rather than show it as having no commands.
func (t CLIToken) CommandsUnreadable() bool {
	if t.Kind != cliKindService || t.Role != string(svcrole.Run) {
		return false
	}
	_, ok := parseStoredCommands(t.Commands)
	return !ok
}

// UnreadableCommands is what a listing shows for CommandsUnreadable.
const UnreadableCommands = runs.UnreadableCommands

// CommandList is Commands for a person to read: "import classic, import
// sync", or UnreadableCommands for a run row whose list cannot be read.
func (t CLIToken) CommandList() string {
	if t.CommandsUnreadable() {
		return UnreadableCommands
	}
	return runs.FormatCommandList(t.CommandPrefixes())
}

// NeverExpires reports whether this credential has no expiry — true only
// for app and service tokens, whose expires_at is 0 by design (spec §8). The
// page asks this rather than testing ExpiresAt against the epoch itself, so
// the "0 means never" convention lives in exactly one place.
func (t CLIToken) NeverExpires() bool {
	return (t.IsApp() || t.IsService()) && (t.ExpiresAt.IsZero() || t.ExpiresAt.Unix() == 0)
}

// IsApp reports whether this row is an app-held token, for the page's
// label. A template cannot see cliKindApp, so it asks here instead of
// comparing against a string literal of its own.
func (t CLIToken) IsApp() bool { return t.Kind == cliKindApp }

// IsService reports whether this row is a service principal, for the same
// reason IsApp exists: a template cannot see cliKindService.
func (t CLIToken) IsService() bool { return t.Kind == cliKindService }

// Handle is how this credential is ADDRESSED by the account pane's rotate
// and revoke forms and by `carlos services revoke`: the NAME its owner chose
// when it has one, and its listing id otherwise.
//
// A legacy kind='app' row has no name — every one of them carries the empty
// string — so the id is the only handle it will ever have, and an owner who
// could see such a row without being able to address it could not kill it.
// The two grammars are disjoint by length (a name is at most 32 characters,
// a listing id is exactly 64 hex), which is what lets serviceHandleOf read
// either from one field without a second one saying which it is.
//
// It lives here rather than in the template so the addressing rule is
// written down once, next to IsApp and IsService and for the same reason.
func (t CLIToken) Handle() string {
	if t.Name != "" {
		return t.Name
	}
	return t.ID
}

// migrateCLIAuth creates the CLI-identity tables. Additive and idempotent,
// called from Store.migrate.
func (s *Store) migrateCLIAuth() error {
	for _, stmt := range []string{
		// One in-flight login. code_hash is the sha256 of the device code;
		// user_code is stored in the clear because the human types it and it
		// has to be looked up by value.
		`CREATE TABLE IF NOT EXISTS cli_auth_requests (
  code_hash   TEXT PRIMARY KEY,
  user_code   TEXT NOT NULL UNIQUE,
  label       TEXT NOT NULL DEFAULT '',
  remote_addr TEXT NOT NULL DEFAULT '',
  address     TEXT NOT NULL DEFAULT '',
  state       TEXT NOT NULL,
  created_at  INTEGER NOT NULL,
  expires_at  INTEGER NOT NULL
);`,
		// The issued credential. Only the hash is stored — a console database
		// that leaks must not hand out working CLI tokens.
		`CREATE TABLE IF NOT EXISTS cli_tokens (
  token_hash  TEXT PRIMARY KEY,
  address     TEXT NOT NULL,
  label       TEXT NOT NULL DEFAULT '',
  remote_addr TEXT NOT NULL DEFAULT '',
  created_at  INTEGER NOT NULL,
  expires_at  INTEGER NOT NULL,
  last_used   INTEGER NOT NULL DEFAULT 0
);`,
		`CREATE INDEX IF NOT EXISTS cli_tokens_address ON cli_tokens (address);`,
		// Expiry is swept on the write path, and a sweep without these is a
		// full table scan on a database with one writer that every console
		// page also queues behind.
		`CREATE INDEX IF NOT EXISTS cli_tokens_expires ON cli_tokens (expires_at);`,
		`CREATE INDEX IF NOT EXISTS cli_auth_requests_expires ON cli_auth_requests (expires_at);`,
	} {
		if _, err := s.db.Exec(stmt); err != nil {
			return err
		}
	}
	// New columns go in this ALTER list — the "duplicate column" error is
	// swallowed so the list stays idempotent across reopens, the same pattern
	// the registry migrate uses. Every one defaults to the USER-token value,
	// so every row written before this change reads back as exactly what it
	// was: kind '', bound to no account and no app, no role, no name, not
	// revoked.
	for _, stmt := range []string{
		`ALTER TABLE cli_tokens ADD COLUMN kind TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE cli_tokens ADD COLUMN account_id INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE cli_tokens ADD COLUMN app TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE cli_tokens ADD COLUMN role TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE cli_tokens ADD COLUMN name TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE cli_tokens ADD COLUMN revoked_at INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE cli_tokens ADD COLUMN rotated_at INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE cli_tokens ADD COLUMN rungs TEXT NOT NULL DEFAULT ''`,
		// credential_id is a service credential's stable identity: random,
		// minted with the row, kept by rotation, never derived from the name
		// (a revoked name can be minted again and must not inherit what the
		// old credential started). commands is a run credential's granted
		// command prefixes, canonical JSON. Both default to '', a person's
		// token's value; backfillCredentialIDs gives older service rows an id.
		`ALTER TABLE cli_tokens ADD COLUMN credential_id TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE cli_tokens ADD COLUMN commands TEXT NOT NULL DEFAULT ''`,
	} {
		if _, err := s.db.Exec(stmt); err != nil && !strings.Contains(err.Error(), "duplicate column") {
			return err
		}
	}
	// credential_id is unique wherever it is set. It is 128 random bits, so
	// this never fires on a real mint; it makes "one id, one credential"
	// structural, which is what a run's ownership check (Task 3) leans on.
	// A person's token keeps '' and any number of those coexist. Like the
	// index below, it must follow the ALTER loop that adds the column.
	if _, err := s.db.Exec(`CREATE UNIQUE INDEX IF NOT EXISTS cli_tokens_credential_id
ON cli_tokens (credential_id) WHERE credential_id != '';`); err != nil {
		return err
	}
	// This index needs `name`, which the ALTER loop above just added (or
	// already had, on a reopened database). It cannot live in the
	// CREATE INDEX IF NOT EXISTS block above: that block runs before the
	// ALTER loop, so on a fresh database it would fail with "no such column:
	// name" and take Open down with it.
	if _, err := s.db.Exec(`CREATE INDEX IF NOT EXISTS cli_tokens_account_name ON cli_tokens (account_id, name);`); err != nil {
		return err
	}
	// Name uniqueness among live service principals is enforced by
	// NewServicePrincipal's own COUNT-then-INSERT inside a transaction, which
	// is race-free only because SetMaxOpenConns(1) (store.go) gives that
	// transaction the sole connection — a fact this file cannot see. This
	// UNIQUE partial index makes the invariant structural rather than
	// resting on a pool setting two files away: it is scoped to
	// kind='service' AND revoked_at=0 so it composes with the soft revoke —
	// a revoked `hub` does not block a fresh `hub` of the same name. The
	// transaction stays too, belt and braces, and is what turns a would-be
	// constraint violation into the friendly ErrServiceNameTaken.
	if _, err := s.db.Exec(`CREATE UNIQUE INDEX IF NOT EXISTS cli_tokens_service_name
ON cli_tokens (account_id, name)
WHERE kind = 'service' AND revoked_at = 0;`); err != nil {
		return err
	}
	return s.backfillCredentialIDs()
}

// backfillCredentialIDs gives every service row (kind 'service', and the
// legacy kind 'app', which reads back as a service principal) that has no
// credential_id a fresh random one. It runs at every Open and writes only
// rows whose id is still empty, so a second run changes nothing: an id,
// once given, is never replaced. A person's row keeps ”.
//
// The ids come from crypto/rand here rather than SQLite's randomblob, so
// the backfill's ids are drawn exactly as a mint's are.
func (s *Store) backfillCredentialIDs() error {
	rows, err := s.db.Query(`SELECT token_hash FROM cli_tokens WHERE kind IN (?, ?) AND credential_id = ''`,
		cliKindService, cliKindApp)
	if err != nil {
		return err
	}
	var hashes []string
	for rows.Next() {
		var h string
		if err := rows.Scan(&h); err != nil {
			rows.Close()
			return err
		}
		hashes = append(hashes, h)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		return err
	}
	for _, h := range hashes {
		id, err := newCredentialID()
		if err != nil {
			return err
		}
		if _, err := s.db.Exec(`UPDATE cli_tokens SET credential_id = ? WHERE token_hash = ? AND credential_id = ''`, id, h); err != nil {
			return err
		}
	}
	return nil
}

// newCredentialID mints a credential's stable id: 128 random bits, hex. It
// is an identity, not a secret, and it is never derived from the name.
func newCredentialID() (string, error) {
	b := make([]byte, 16)
	if _, err := rand.Read(b); err != nil {
		return "", err
	}
	return hex.EncodeToString(b), nil
}

// The three token kinds cli_tokens holds. The user kind is the empty string
// on purpose: it is what every row written before the app kind existed
// already says, so the default IS the safe answer. cliKindService is the
// principal that is not an address (see svcprincipal.go).
const (
	cliKindUser    = ""
	cliKindApp     = "app"
	cliKindService = "service"
)

// newUserCode mints the typed code, grouped XXXX-XXXX so it can be read out
// and typed back without losing your place.
func newUserCode() (string, error) {
	b := make([]byte, userCodeLen)
	if _, err := rand.Read(b); err != nil {
		return "", err
	}
	out := make([]byte, 0, userCodeLen+1)
	for i, v := range b {
		if i == userCodeLen/2 {
			out = append(out, '-')
		}
		// Modulo bias over a 30-symbol alphabet is at most 0.4 bits across the
		// whole code; irrelevant against a rate-limited ten-minute window, and
		// rejection sampling here would only add a loop to read wrong.
		out = append(out, userCodeAlphabet[int(v)%len(userCodeAlphabet)])
	}
	return string(out), nil
}

// NormalizeUserCode is what the approval form does to whatever a human typed:
// upper-case, dashes and spaces dropped, then re-grouped. "wxyz 4823",
// "wxyz-4823" and "WXYZ4823" are the same code.
func NormalizeUserCode(in string) string {
	var b strings.Builder
	for _, r := range strings.ToUpper(strings.TrimSpace(in)) {
		if r == '-' || r == ' ' || r == '\t' {
			continue
		}
		b.WriteRune(r)
	}
	s := b.String()
	if len(s) != userCodeLen {
		return s // wrong length: hand it back and let the lookup miss
	}
	return s[:userCodeLen/2] + "-" + s[userCodeLen/2:]
}

// NewCLIAuth opens a login attempt: a typed user code for the human, a device
// code for the CLI. label is what the CLI calls itself (user@host) and
// remoteAddr where it dialled from — both are shown on the approval page so a
// human can tell their own terminal from somebody else's.
//
// Housekeeping on the write path, the same idiom handleContinue uses: expired
// requests and tokens are dropped here rather than by a sweeper nobody runs.
func (s *Store) NewCLIAuth(label, remoteAddr string, now time.Time) (userCode, deviceCode string, err error) {
	s.PurgeCLIAuth(now)
	deviceCode, err = randomToken()
	if err != nil {
		return "", "", err
	}
	// One retry is enough: a collision on 39 bits of live codes means the
	// second draw is astronomically unlikely to collide too.
	for attempt := 0; attempt < 2; attempt++ {
		userCode, err = newUserCode()
		if err != nil {
			return "", "", err
		}
		_, err = s.db.Exec(`INSERT INTO cli_auth_requests
(code_hash, user_code, label, remote_addr, state, created_at, expires_at)
VALUES (?, ?, ?, ?, ?, ?, ?)`,
			hashToken(deviceCode), userCode, label, remoteAddr, CLIAuthPending,
			now.Unix(), now.Add(CLIAuthTTL).Unix())
		if err == nil {
			return userCode, deviceCode, nil
		}
		if !strings.Contains(err.Error(), "UNIQUE") {
			return "", "", err
		}
	}
	return "", "", fmt.Errorf("console: could not mint a free login code")
}

// lastCLIPurge throttles the housekeeping sweep. Process-global rather than
// per-Store because a process runs one console; the only cost of a skipped
// sweep is rows living a little longer, since every read already checks
// expiry itself. Without the throttle a flood of login starts turns into a
// delete-per-request against a database with SetMaxOpenConns(1) — which is
// every console page's queue too.
var lastCLIPurge atomic.Int64

// cliPurgeEvery bounds how often the sweep runs.
const cliPurgeEvery = 60 * time.Second

// PurgeCLIAuth drops what has aged out, at most once every cliPurgeEvery.
// Best-effort: losing a sweep must never fail the login it is housekeeping
// for. Exported so PurgeExpired — the console's general housekeeping — sweeps
// these tables too.
func (s *Store) PurgeCLIAuth(now time.Time) {
	last := lastCLIPurge.Load()
	if now.Unix()-last < int64(cliPurgeEvery/time.Second) {
		return
	}
	if !lastCLIPurge.CompareAndSwap(last, now.Unix()) {
		return // another request is doing it
	}
	s.db.Exec(`DELETE FROM cli_auth_requests WHERE expires_at < ?`, now.Unix())
	// expires_at = 0 means "never" (app tokens, see NewAppToken) and must be
	// excluded from a sweep that compares against the clock — 0 is less than
	// every now, so an unguarded sweep would delete exactly the credentials
	// that are supposed to outlive it.
	s.db.Exec(`DELETE FROM cli_tokens WHERE expires_at != 0 AND expires_at < ?`, now.Unix())
}

// CLIAuthByUserCode resolves a typed code to the request it names, for the
// approval page. Expired and already-decided requests are "no such code" —
// the page must never offer to approve something that cannot be approved.
func (s *Store) CLIAuthByUserCode(userCode string, now time.Time) (*CLIAuthRequest, error) {
	var r CLIAuthRequest
	var created, expires int64
	var state string
	err := s.db.QueryRow(`SELECT user_code, label, remote_addr, state, created_at, expires_at
FROM cli_auth_requests WHERE user_code = ?`, NormalizeUserCode(userCode)).
		Scan(&r.UserCode, &r.Label, &r.RemoteAddr, &state, &created, &expires)
	if err == sql.ErrNoRows {
		return nil, errors.New("console: no such login code")
	}
	if err != nil {
		return nil, err
	}
	if state != CLIAuthPending || now.Unix() > expires {
		return nil, errors.New("console: that login code has expired or was already used")
	}
	r.CreatedAt = time.Unix(created, 0).UTC()
	r.ExpiresAt = time.Unix(expires, 0).UTC()
	return &r, nil
}

// ApproveCLIAuth records that address approved the request. The token is NOT
// minted here: it is minted when the CLI collects it (PollCLIAuth), so no
// bearer credential is ever at rest waiting to be picked up.
func (s *Store) ApproveCLIAuth(userCode, address string, now time.Time) error {
	return s.decideCLIAuth(userCode, address, CLIAuthApproved, now)
}

// DenyCLIAuth is the "that wasn't me" button. A denied request is final:
// approving it afterwards is refused, so a mis-click cannot be walked back
// into a credential by whoever is holding the terminal.
func (s *Store) DenyCLIAuth(userCode string, now time.Time) error {
	return s.decideCLIAuth(userCode, "", CLIAuthDenied, now)
}

func (s *Store) decideCLIAuth(userCode, address, state string, now time.Time) error {
	res, err := s.db.Exec(`UPDATE cli_auth_requests SET state = ?, address = ?
WHERE user_code = ? AND state = ? AND expires_at >= ?`,
		state, address, NormalizeUserCode(userCode), CLIAuthPending, now.Unix())
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return errors.New("console: that login code has expired or was already used")
	}
	return nil
}

// PollCLIAuth is what `carlos auth login` calls in a loop. On approval it
// mints the token, records it, deletes the request, and returns the raw token
// exactly once — the caller never gets a second copy, so a stolen device code
// replayed later collects nothing.
// The approving address comes back with the token so the caller never has to
// re-read the row to learn it — reading it back through CLITokenAddress would
// stamp "last used" on a credential at the moment it was issued, and "never
// used since it was issued" is exactly the thing the tokens page exists to
// show.
func (s *Store) PollCLIAuth(deviceCode string, now time.Time) (status, rawToken, address string, err error) {
	if deviceCode == "" {
		return CLIAuthUnknown, "", "", nil
	}
	tx, err := s.db.Begin()
	if err != nil {
		return "", "", "", err
	}
	defer tx.Rollback()
	hash := hashToken(deviceCode)
	var storedHash, state, label, remote string
	var expires int64
	err = tx.QueryRow(`SELECT code_hash, state, address, label, remote_addr, expires_at
FROM cli_auth_requests WHERE code_hash = ?`, hash).
		Scan(&storedHash, &state, &address, &label, &remote, &expires)
	if err == sql.ErrNoRows {
		return CLIAuthUnknown, "", "", nil
	}
	if err != nil {
		return "", "", "", err
	}
	// Belt and braces over the indexed lookup: the comparison that decides
	// whether this caller holds the secret is constant time.
	if subtle.ConstantTimeCompare([]byte(storedHash), []byte(hash)) != 1 {
		return CLIAuthUnknown, "", "", nil
	}
	if now.Unix() > expires {
		return CLIAuthExpired, "", "", nil
	}
	switch state {
	case CLIAuthPending:
		return CLIAuthPending, "", "", nil
	case CLIAuthDenied:
		return CLIAuthDenied, "", "", nil
	case CLIAuthApproved:
	default:
		return CLIAuthUnknown, "", "", nil
	}
	raw, err := randomToken()
	if err != nil {
		return "", "", "", err
	}
	if _, err := tx.Exec(`INSERT INTO cli_tokens
(token_hash, address, label, remote_addr, created_at, expires_at)
VALUES (?, ?, ?, ?, ?, ?)`,
		hashToken(raw), address, label, remote, now.Unix(), now.Add(CLITokenTTL).Unix()); err != nil {
		return "", "", "", err
	}
	// Burn the request in the same transaction that mints the token: the two
	// must never be separable, or a crash could hand out two tokens for one
	// approval.
	if _, err := tx.Exec(`DELETE FROM cli_auth_requests WHERE code_hash = ?`, hash); err != nil {
		return "", "", "", err
	}
	if err := tx.Commit(); err != nil {
		return "", "", "", err
	}
	return CLIAuthApproved, raw, address, nil
}

// NewAppToken mints a credential an APP holds, bound to one account and one
// app rather than to a person. mintedBy is the member who minted it: it goes
// in `address`, which is what the audit trail, the owner's tokens listing and
// revocation all key on — the token speaks for an app, but a human stays
// answerable for it.
//
// The token never expires (`expires_at = 0`), and that is a deliberate trade
// against the 90-day user-token TTL, recorded in spec §8: a production signup
// path that silently dies on day 91 is a worse failure than a long-lived
// credential whose rotation is one `carlos env set` + bounce away. Revocation
// (the minting member's tokens page, where it lists under their address) is
// the control.
func (s *Store) claimRawNewAppToken(accountID int64, app, mintedBy, label string, now time.Time) (string, error) {
	raw, err := randomToken()
	if err != nil {
		return "", err
	}
	credID, err := newCredentialID()
	if err != nil {
		return "", err
	}
	if _, err := s.db.Exec(`INSERT INTO cli_tokens
(token_hash, address, label, remote_addr, created_at, expires_at, kind, account_id, app, credential_id)
VALUES (?, ?, ?, '', ?, 0, ?, ?, ?, ?)`,
		hashToken(raw), mintedBy, label, now.Unix(), cliKindApp, accountID, app, credID); err != nil {
		return "", err
	}
	return raw, nil
}

// CLITokenAddress resolves a bearer token to the address that approved it; ok
// is false for unknown, revoked and expired tokens. This is the whole
// authorization decision for every CLI-facing endpoint.
func (s *Store) CLITokenAddress(rawToken string, now time.Time) (string, bool) {
	if rawToken == "" {
		return "", false
	}
	hash := hashToken(rawToken)
	var storedHash, addr, kind string
	var expires, revoked int64
	err := s.db.QueryRow(`SELECT token_hash, address, kind, expires_at, revoked_at FROM cli_tokens WHERE token_hash = ?`,
		hash).Scan(&storedHash, &addr, &kind, &expires, &revoked)
	if err != nil {
		return "", false
	}
	if subtle.ConstantTimeCompare([]byte(storedHash), []byte(hash)) != 1 {
		return "", false
	}
	// An app or service token presented to a user door must look exactly
	// like a token that does not exist.
	if kind != cliKindUser {
		return "", false
	}
	// A revoked credential must look exactly like a token that does not
	// exist — a service principal killed through RevokeServicePrincipal
	// leaves a row behind (soft revoke, see ServicePrincipals), and this door
	// must not answer for it just because it once could.
	if revoked != 0 {
		return "", false
	}
	if now.Unix() > expires {
		return "", false
	}
	// Best-effort: "last used" is for the owner's benefit on the tokens page,
	// and a failed write must not fail the request it is annotating.
	s.db.Exec(`UPDATE cli_tokens SET last_used = ? WHERE token_hash = ?`, now.Unix(), hash)
	return addr, true
}

// scanCLITokenRows drains a `cli_tokens` listing query into CLIToken values.
// CLITokens (by address) and ServicePrincipals (by account) select the same
// sixteen columns in the same order and need the same legacy-row
// normalization — a kind='app' row's `role` column is empty in the database but
// IS the instance role (spec §5). This is the one place that normalization
// happens, so neither caller, nor any reader downstream of them, has to
// re-derive it.
func scanCLITokenRows(rows *sql.Rows) ([]CLIToken, error) {
	defer rows.Close()
	var out []CLIToken
	for rows.Next() {
		var t CLIToken
		var created, expires, used, revoked, rotated int64
		if err := rows.Scan(&t.ID, &t.Address, &t.Label, &t.RemoteAddr, &t.Kind, &t.AccountID, &t.App,
			&t.Role, &t.Name, &created, &expires, &used, &revoked, &rotated, &t.Rungs, &t.Commands); err != nil {
			return nil, err
		}
		t.CreatedAt = time.Unix(created, 0).UTC()
		t.ExpiresAt = time.Unix(expires, 0).UTC()
		if used != 0 {
			t.LastUsed = time.Unix(used, 0).UTC()
		}
		if revoked != 0 {
			t.RevokedAt = time.Unix(revoked, 0).UTC()
		}
		if rotated != 0 {
			t.RotatedAt = time.Unix(rotated, 0).UTC()
		}
		if t.Kind == cliKindApp {
			t.Role = string(svcrole.Instance)
		}
		out = append(out, t)
	}
	return out, rows.Err()
}

// CLITokens lists one address's live CLI credentials, newest first — the
// listing the tokens page renders so an owner can see, and kill, every
// terminal that speaks as them. revoked_at = 0: a soft-revoked service
// principal minted under this address (see RevokeServicePrincipal) must not
// render here as an unbadged, still-live, still-revocable row — the comment
// above already promised "live", and until this filter the query did not
// keep that promise.
func (s *Store) CLITokens(address string) ([]CLIToken, error) {
	rows, err := s.db.Query(`SELECT token_hash, address, label, remote_addr, kind, account_id, app,
role, name, created_at, expires_at, last_used, revoked_at, rotated_at, rungs, commands
FROM cli_tokens WHERE address = ? AND revoked_at = 0 ORDER BY created_at DESC, token_hash`, address)
	if err != nil {
		return nil, err
	}
	return scanCLITokenRows(rows)
}

// RevokeCLIToken kills one token by its listing id, scoped to its owner: an
// id is not a secret, so the address is what makes this safe.
func (s *Store) RevokeCLIToken(address, id string) error {
	return s.RevokeCLITokenContext(context.Background(), address, id)
}

// errNoSuchCLIToken is RevokeCLIToken's "nothing matched": the row is gone, or
// was never this address's. Every other error is a failure to act.
var errNoSuchCLIToken = errors.New("console: no such token")

// RevokeCLITokenContext is RevokeCLIToken under a caller context. A person's
// terminal token (app ”) is deployment-free SQL and deletes directly. A
// legacy kind='app' (or service) row names an app incarnation, so its DELETE
// is admitted for that app first, exactly as NewAppToken's INSERT is; on a
// console without the host claim there is no gate and this is the same single
// DELETE as before.
func (s *Store) RevokeCLITokenContext(ctx context.Context, address, id string) error {
	if s.gate == nil {
		return s.claimRawRevokeCLIToken(address, id)
	}
	var accountID int64
	var app string
	err := s.db.QueryRowContext(ctx, `SELECT account_id, app FROM cli_tokens WHERE token_hash = ? AND address = ?`,
		id, address).Scan(&accountID, &app)
	if errors.Is(err, sql.ErrNoRows) {
		return errNoSuchCLIToken
	}
	if err != nil {
		return err
	}
	return s.withAutoAppSQL(ctx, accountID, app, "sql.RevokeCLIToken", func(view *Store) error {
		return view.claimRawRevokeCLIToken(address, id)
	})
}

func (s *Store) claimRawRevokeCLIToken(address, id string) error {
	res, err := s.db.Exec(`DELETE FROM cli_tokens WHERE token_hash = ? AND address = ?`, id, address)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return errNoSuchCLIToken
	}
	return nil
}

// RevokeCLITokenValue kills a token by the token itself — what `carlos auth
// logout` sends, so signing out of a terminal actually retires the credential
// rather than just forgetting it locally.
func (s *Store) RevokeCLITokenValue(rawToken string) error {
	return s.RevokeCLITokenValueContext(context.Background(), rawToken)
}

// RevokeCLITokenValueContext is RevokeCLITokenValue under a caller context. The
// DELETE matches on the hash alone, so it removes a legacy kind='app' or a
// service row exactly as it removes a person's: the only route that calls it
// sits behind cliUser, which admits person tokens (app ”) alone, but the method
// does not rely on that. A row naming an app incarnation is admitted for that
// app first, as RevokeCLITokenContext and NewAppToken are; a token that matches
// nothing is not an error, as before.
func (s *Store) RevokeCLITokenValueContext(ctx context.Context, rawToken string) error {
	hash := hashToken(rawToken)
	if s.gate == nil {
		return s.claimRawRevokeCLITokenValue(hash)
	}
	var accountID int64
	var app string
	err := s.db.QueryRowContext(ctx, `SELECT account_id, app FROM cli_tokens WHERE token_hash = ?`, hash).Scan(&accountID, &app)
	if errors.Is(err, sql.ErrNoRows) {
		return nil
	}
	if err != nil {
		return err
	}
	return s.withAutoAppSQL(ctx, accountID, app, "sql.RevokeCLITokenValue", func(view *Store) error {
		return view.claimRawRevokeCLITokenValue(hash)
	})
}

func (s *Store) claimRawRevokeCLITokenValue(hash string) error {
	_, err := s.db.Exec(`DELETE FROM cli_tokens WHERE token_hash = ?`, hash)
	return err
}
func (s *Store) NewAppToken(accountID int64, app, mintedBy, label string, now time.Time) (claimResult0 string, claimResult1 error) {
	return s.NewAppTokenContext(context.Background(), accountID, app, mintedBy, label, now)
}
func (s *Store) NewAppTokenContext(claimContext context.Context, accountID int64, app, mintedBy, label string, now time.Time) (claimResult0 string, claimResult1 error) {
	claimResult1 = s.withAutoAppSQL(claimContext, accountID, app, "sql.NewAppToken", func(claimView *Store) error {
		claimResult0, claimResult1 = claimView.claimRawNewAppToken(accountID, app, mintedBy, label, now)
		return claimResult1
	})
	return
}