-- MCP server (add-mcp-server). Agents reach the ledger over MCP authenticated by
-- a bearer API token, and their categorizations are recorded as a distinct,
-- honest provenance source rather than wearing a person's identity.

-- One row per minted token. Only the hash is stored: a leaked database must not
-- hand over live agent credentials (sessions set the same precedent). `user_did`
-- is the creator on a multi-user server and NULL in single-user local mode
-- (add-desktop-local-remote-modes). `last_used_at` turns a forgotten token into
-- something visible and revocable rather than an invisible standing grant.
CREATE TABLE api_tokens (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	label TEXT NOT NULL,
	token_hash TEXT NOT NULL UNIQUE,
	scope TEXT NOT NULL CHECK (scope IN ('read', 'readwrite')),
	user_did TEXT REFERENCES users (did), -- creator; NULL in single-user local mode
	created_at TEXT NOT NULL,
	last_used_at TEXT,
	-- Revocation is soft: a token that has authored agent categorization events
	-- is referenced by the append-only event log (api_token_id below), so it is
	-- never deleted. Its label must keep resolving in old provenance; verify
	-- simply refuses a revoked token.
	revoked_at TEXT
);

-- Widen categorization_events.source to admit 'agent'. SQLite cannot alter a
-- CHECK constraint in place, so the table is rebuilt. It is a leaf in the FK
-- graph (nothing references it; it only references transactions/categories/
-- rules/users/api_tokens), so the drop-and-rename is safe with foreign_keys ON.
-- The append-only guarantee is preserved: every existing event is copied
-- verbatim, ids intact, api_token_id NULL (none were agent-sourced).
CREATE TABLE categorization_events_new (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	transaction_id INTEGER NOT NULL REFERENCES transactions (id),
	category_id INTEGER REFERENCES categories (id), -- NULL clears the category
	source TEXT NOT NULL CHECK (source IN ('rule', 'manual', 'reconciliation', 'agent')),
	rule_id INTEGER REFERENCES rules (id),
	actor_did TEXT REFERENCES users (did),
	api_token_id INTEGER REFERENCES api_tokens (id),
	created_at TEXT NOT NULL,
	CHECK (source != 'rule' OR rule_id IS NOT NULL),
	CHECK (source != 'manual' OR actor_did IS NOT NULL),
	CHECK (source != 'agent' OR api_token_id IS NOT NULL)
);

INSERT INTO categorization_events_new
	(id, transaction_id, category_id, source, rule_id, actor_did, api_token_id, created_at)
SELECT id, transaction_id, category_id, source, rule_id, actor_did, NULL, created_at
FROM categorization_events;

DROP TABLE categorization_events;
ALTER TABLE categorization_events_new RENAME TO categorization_events;
CREATE INDEX idx_catevents_transaction ON categorization_events (transaction_id);