store

var ErrNewerDatabase = errors.New("store: database schema is newer than this version")

var Migrations = [][]string{{
	`CREATE TABLE accounts (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		email TEXT NOT NULL UNIQUE,
		name TEXT, alias TEXT, signature TEXT,
		is_default INTEGER DEFAULT 0,
		sync_limit INTEGER DEFAULT 2000,
		auth_type TEXT DEFAULT 'password',
		status TEXT DEFAULT 'ok',
		username TEXT,
		imap_host TEXT, imap_port INTEGER, imap_security TEXT,
		smtp_host TEXT, smtp_port INTEGER, smtp_security TEXT,
		created_at INTEGER)`,
	`CREATE TABLE folders (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		name TEXT,
		role TEXT NOT NULL DEFAULT '',
		uidvalidity INTEGER,
		last_uid INTEGER DEFAULT 0,
		UNIQUE(account_id, name))`,
	`CREATE TABLE threads (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		subject TEXT,
		last_date INTEGER)`,
	`CREATE INDEX threads_account_date ON threads(account_id, last_date DESC)`,
	`CREATE TABLE messages (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		folder_id INTEGER NOT NULL REFERENCES folders(id) ON DELETE CASCADE,
		server_folder_id INTEGER REFERENCES folders(id) ON DELETE SET NULL,
		server_uidvalidity INTEGER,
		server_uid INTEGER,
		thread_id INTEGER REFERENCES threads(id) ON DELETE CASCADE,
		message_id TEXT, in_reply_to TEXT, refs TEXT,
		subject TEXT, from_addr TEXT, to_addrs TEXT, cc_addrs TEXT, reply_to TEXT,
		date INTEGER,
		flags TEXT NOT NULL DEFAULT '',
		size INTEGER,
		snippet TEXT, body_text TEXT, body_html TEXT,
		body_truncated INTEGER DEFAULT 0,
		has_attachments INTEGER,
		UNIQUE(server_folder_id, server_uid))`,
	`CREATE INDEX messages_folder_date ON messages(folder_id, date DESC, id DESC)`,
	`CREATE INDEX messages_thread ON messages(thread_id)`,
	`CREATE INDEX messages_account_mid ON messages(account_id, message_id)`,
	`CREATE TABLE attachments (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		message_id INTEGER NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
		part TEXT, encoding TEXT, charset TEXT, filename TEXT, content_type TEXT,
		size INTEGER, content_id TEXT, inline INTEGER, cache_path TEXT, last_used INTEGER)`,
	`CREATE TABLE thread_refs (
		account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		message_id_hdr TEXT NOT NULL,
		thread_id INTEGER NOT NULL REFERENCES threads(id) ON DELETE CASCADE,
		PRIMARY KEY(account_id, message_id_hdr))`,
	`CREATE TABLE events_log (
		seq INTEGER PRIMARY KEY AUTOINCREMENT,
		type TEXT, account_id INTEGER, message_id INTEGER, created_at INTEGER)`,
	`CREATE VIRTUAL TABLE messages_fts USING fts5(subject, from_addr, body, tokenize='trigram')`,
	`CREATE TRIGGER messages_ai AFTER INSERT ON messages BEGIN
		INSERT INTO messages_fts(rowid, subject, from_addr, body)
		VALUES (new.id, new.subject, new.from_addr, new.body_text);
	END`,
	`CREATE TRIGGER messages_ai_event AFTER INSERT ON messages BEGIN
		INSERT INTO events_log(type, account_id, message_id, created_at)
		VALUES ('message.received', new.account_id, new.id, unixepoch());
	END`,
	`CREATE TRIGGER messages_au AFTER UPDATE OF subject, from_addr, body_text ON messages BEGIN
		UPDATE messages_fts SET subject = new.subject, from_addr = new.from_addr, body = new.body_text
		WHERE rowid = old.id;
	END`,
	`CREATE TRIGGER messages_ad AFTER DELETE ON messages BEGIN
		DELETE FROM messages_fts WHERE rowid = old.id;
	END`,
	`CREATE TABLE drafts (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		mode TEXT,
		source_message_id INTEGER REFERENCES messages(id) ON DELETE SET NULL,
		to_addrs TEXT, cc_addrs TEXT, bcc_addrs TEXT, reply_to TEXT,
		subject TEXT, body_html TEXT, body_text TEXT, updated_at INTEGER)`,
	`CREATE TABLE draft_attachments (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		draft_id INTEGER NOT NULL REFERENCES drafts(id) ON DELETE CASCADE,
		filename TEXT, content_type TEXT, size INTEGER, path TEXT)`,
	`CREATE TABLE ops (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		message_id INTEGER REFERENCES messages(id) ON DELETE SET NULL,
		kind TEXT,
		dst_folder_id INTEGER,
		payload TEXT,
		depends_on INTEGER,
		state TEXT DEFAULT 'pending',
		attempts INTEGER DEFAULT 0,
		last_error TEXT,
		created_at INTEGER)`,
	`CREATE TABLE outbox (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		message_id_hdr TEXT, raw_path TEXT, rcpts TEXT,
		state TEXT,
		attempts INTEGER DEFAULT 0,
		last_error TEXT,
		created_at INTEGER)`,
}, {
	`ALTER TABLE outbox ADD COLUMN subject TEXT`,
}, {
	`CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL)`,
}, {

	`ALTER TABLE accounts ADD COLUMN smtp_reauth INTEGER NOT NULL DEFAULT 0`,
}, {
	`ALTER TABLE accounts ADD COLUMN provider TEXT NOT NULL DEFAULT ''`,

	`ALTER TABLE accounts ADD COLUMN status_detail TEXT NOT NULL DEFAULT ''`,
}, {
	`CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT NOT NULL)`,
	`CREATE TABLE model_services (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, kind TEXT NOT NULL,
		base_url TEXT NOT NULL, models TEXT NOT NULL DEFAULT '[]', version INTEGER NOT NULL DEFAULT 1, created_at INTEGER)`,
	`CREATE TABLE ai_cache (key TEXT PRIMARY KEY, account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		output TEXT NOT NULL, created_at INTEGER)`,
	`CREATE TABLE conversations (id INTEGER PRIMARY KEY AUTOINCREMENT, kind TEXT NOT NULL,
		draft_id INTEGER REFERENCES drafts(id) ON DELETE CASCADE, title TEXT, model TEXT, state BLOB,
		created_at INTEGER, updated_at INTEGER)`,
	`CREATE TABLE runs (id INTEGER PRIMARY KEY AUTOINCREMENT,
		conversation_id INTEGER NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
		state TEXT NOT NULL, model TEXT NOT NULL, tools TEXT NOT NULL, snapshot BLOB, created_at INTEGER, updated_at INTEGER)`,
	`CREATE UNIQUE INDEX runs_one_active ON runs(conversation_id) WHERE state IN ('running','interrupted','resuming')`,
	`CREATE TABLE approvals (id INTEGER PRIMARY KEY AUTOINCREMENT, run_id INTEGER NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
		seq INTEGER NOT NULL, call_id TEXT NOT NULL, tool TEXT NOT NULL, args TEXT NOT NULL, summary TEXT,
		target_kind TEXT, target_id INTEGER, target_version INTEGER, state TEXT NOT NULL, result TEXT, undo TEXT,
		created_at INTEGER, updated_at INTEGER, UNIQUE(run_id, seq))`,
	`CREATE TABLE audit_log (id INTEGER PRIMARY KEY AUTOINCREMENT, at INTEGER, source TEXT, tool TEXT,
		target_kind TEXT, target_id INTEGER, decision TEXT)`,
	`ALTER TABLE drafts ADD COLUMN revision INTEGER NOT NULL DEFAULT 1`,
	`ALTER TABLE drafts ADD COLUMN ai_approval_id INTEGER`,
}, {
	`CREATE TABLE plugins (name TEXT PRIMARY KEY, version TEXT NOT NULL, manifest TEXT NOT NULL, source_hash TEXT NOT NULL,
		permission_hash TEXT NOT NULL, config_version INTEGER NOT NULL DEFAULT 1, enabled INTEGER NOT NULL DEFAULT 0, granted_hash TEXT,
		failures INTEGER NOT NULL DEFAULT 0, disabled_reason TEXT NOT NULL DEFAULT '', installed_at INTEGER)`,
	`CREATE TABLE plugin_config (plugin TEXT NOT NULL REFERENCES plugins(name) ON DELETE CASCADE, key TEXT NOT NULL,
		value TEXT NOT NULL, PRIMARY KEY(plugin, key))`,
	`CREATE TABLE plugin_kv (plugin TEXT NOT NULL REFERENCES plugins(name) ON DELETE CASCADE, key TEXT NOT NULL,
		value TEXT NOT NULL, PRIMARY KEY(plugin, key))`,

	`DROP TRIGGER messages_ai_event`,
	`ALTER TABLE events_log ADD COLUMN subject TEXT`,
	`ALTER TABLE events_log ADD COLUMN from_addr TEXT`,
	`ALTER TABLE events_log ADD COLUMN msg_date INTEGER`,
	`CREATE TABLE plugin_cursors (plugin TEXT PRIMARY KEY REFERENCES plugins(name) ON DELETE CASCADE, seq INTEGER NOT NULL)`,
	`CREATE TABLE mcp_servers (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, transport TEXT NOT NULL,
		command TEXT, args TEXT NOT NULL DEFAULT '[]', env_keys TEXT NOT NULL DEFAULT '[]', url TEXT,
		header_keys TEXT NOT NULL DEFAULT '[]', version INTEGER NOT NULL DEFAULT 1, enabled INTEGER NOT NULL DEFAULT 0,
		granted_version INTEGER NOT NULL DEFAULT 0, created_at INTEGER)`,
	`CREATE TABLE mcp_tool_trust (server_id INTEGER NOT NULL REFERENCES mcp_servers(id) ON DELETE CASCADE, tool TEXT NOT NULL,
		definition_hash TEXT NOT NULL, PRIMARY KEY(server_id, tool))`,
	`ALTER TABLE audit_log ADD COLUMN extension TEXT`,
	`ALTER TABLE audit_log ADD COLUMN call_id TEXT`,
	`ALTER TABLE audit_log ADD COLUMN destination TEXT`,
	`ALTER TABLE audit_log ADD COLUMN reason TEXT`,
	`ALTER TABLE approvals ADD COLUMN exec_mode TEXT NOT NULL DEFAULT 'tx'`,
}, {

	`ALTER TABLE plugins ADD COLUMN install_dir TEXT NOT NULL DEFAULT ''`,

	`ALTER TABLE plugins ADD COLUMN secret_keys TEXT NOT NULL DEFAULT '[]'`,

	`ALTER TABLE approvals ADD COLUMN destinations TEXT NOT NULL DEFAULT '[]'`,

	`ALTER TABLE approvals ADD COLUMN binding TEXT NOT NULL DEFAULT ''`,

	`ALTER TABLE approvals ADD COLUMN source TEXT NOT NULL DEFAULT 'wit'`,
	`ALTER TABLE approvals ADD COLUMN extension TEXT NOT NULL DEFAULT ''`,
}, {

	`ALTER TABLE folders ADD COLUMN synced INTEGER NOT NULL DEFAULT 0`,
	`UPDATE folders SET synced = 1 WHERE last_uid > 0`,
}, {

	`ALTER TABLE conversations ADD COLUMN read_mail INTEGER NOT NULL DEFAULT 0`,

	`ALTER TABLE plugins ADD COLUMN holds_mail INTEGER NOT NULL DEFAULT 0`,
}, {

	`CREATE TABLE run_citations (id INTEGER PRIMARY KEY AUTOINCREMENT, run_id INTEGER NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
		call_id TEXT NOT NULL DEFAULT '', message_id INTEGER NOT NULL, subject TEXT NOT NULL, from_addr TEXT NOT NULL, date INTEGER, suspect INTEGER NOT NULL DEFAULT 0)`,
	`CREATE INDEX run_citations_run ON run_citations(run_id)`,

	`ALTER TABLE runs ADD COLUMN hist_from INTEGER`,
	`ALTER TABLE runs ADD COLUMN hist_to INTEGER`,
}, {

	`ALTER TABLE model_services ADD COLUMN slug TEXT`,
	`CREATE UNIQUE INDEX model_services_slug ON model_services(slug)`,
}, {

	`CREATE TABLE model_service_aliases (name TEXT PRIMARY KEY, service_id INTEGER NOT NULL REFERENCES model_services(id) ON DELETE CASCADE)`,

	`ALTER TABLE model_services ADD COLUMN key_version INTEGER NOT NULL DEFAULT 1`,

	`CREATE TABLE model_key_trash (service_id INTEGER NOT NULL, key_version INTEGER NOT NULL, PRIMARY KEY(service_id, key_version))`,
}, {

	`ALTER TABLE run_citations ADD COLUMN quote TEXT NOT NULL DEFAULT ''`,
}, {

	`ALTER TABLE model_services ADD COLUMN provider TEXT NOT NULL DEFAULT 'other'`,
	`UPDATE model_services SET provider = 'openai' WHERE kind IN ('openai', 'openai-responses')
		AND (base_url = 'https://api.openai.com' OR base_url LIKE 'https://api.openai.com/%')`,
	`UPDATE model_services SET provider = 'anthropic' WHERE kind = 'anthropic'
		AND (base_url = 'https://api.anthropic.com' OR base_url LIKE 'https://api.anthropic.com/%')`,
	`ALTER TABLE model_services ADD COLUMN judge_format TEXT NOT NULL DEFAULT ''`,
	`ALTER TABLE model_services ADD COLUMN judge_model TEXT NOT NULL DEFAULT ''`,

	`CREATE TABLE model_service_status (service_id INTEGER NOT NULL REFERENCES model_services(id) ON DELETE CASCADE,
		cap TEXT NOT NULL, state TEXT NOT NULL, latency_ms INTEGER NOT NULL DEFAULT 0, http_status INTEGER NOT NULL DEFAULT 0,
		code TEXT NOT NULL DEFAULT '', at INTEGER NOT NULL, PRIMARY KEY(service_id, cap))`,
}, {

	`ALTER TABLE accounts ADD COLUMN sync_since_months INTEGER NOT NULL DEFAULT 0`,

	`ALTER TABLE folders ADD COLUMN filled_since INTEGER`,
}, {

	`ALTER TABLE messages ADD COLUMN entered_at INTEGER NOT NULL DEFAULT 0`,
	`UPDATE messages SET entered_at = unixepoch()`,

	`ALTER TABLE folders ADD COLUMN role_override TEXT NOT NULL DEFAULT ''`,

	`ALTER TABLE folders ADD COLUMN role_since INTEGER NOT NULL DEFAULT 0`,
	`UPDATE folders SET role_since = unixepoch()`,
	`CREATE TRIGGER folders_role_since AFTER UPDATE OF role, role_override ON folders
		WHEN old.role IS NOT new.role OR old.role_override IS NOT new.role_override BEGIN
		UPDATE folders SET role_since = unixepoch() WHERE id = new.id;
	END`,

	`ALTER TABLE folders ADD COLUMN purged_at INTEGER NOT NULL DEFAULT 0`,

	`CREATE TABLE purge_policies (account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		role TEXT NOT NULL, days INTEGER NOT NULL DEFAULT 0, since INTEGER NOT NULL, folder_id INTEGER,
		blocked INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(account_id, role))`,
}, {

	`CREATE TABLE signature_images (account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		cid TEXT NOT NULL, content_type TEXT NOT NULL, data BLOB NOT NULL, PRIMARY KEY(account_id, cid))`,
}, {

	`CREATE TABLE labels (id INTEGER PRIMARY KEY AUTOINCREMENT, slug TEXT NOT NULL UNIQUE, name TEXT NOT NULL,
		description TEXT NOT NULL DEFAULT '', color TEXT NOT NULL, icon TEXT NOT NULL,
		builtin INTEGER NOT NULL DEFAULT 0, position INTEGER NOT NULL)`,
	`CREATE TABLE message_labels (message_id INTEGER NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
		label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
		source TEXT NOT NULL, active INTEGER NOT NULL, PRIMARY KEY(message_id, label_id))`,
	`CREATE INDEX message_labels_label ON message_labels(label_id, active)`,
	`ALTER TABLE messages ADD COLUMN labeled_at INTEGER`,
	`CREATE INDEX messages_labeled ON messages(folder_id, labeled_at)`,
}, {

	`ALTER TABLE messages ADD COLUMN label_fp TEXT`,
	`CREATE INDEX messages_label_fp ON messages(account_id, label_fp)`,
	`CREATE TABLE label_keep (account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, fp TEXT NOT NULL,
		label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE, active INTEGER NOT NULL, PRIMARY KEY(account_id, fp, label_id))`,
	`CREATE TRIGGER messages_label_stash BEFORE DELETE ON messages
		WHEN old.label_fp IS NOT NULL AND EXISTS (SELECT 1 FROM message_labels WHERE message_id = old.id AND source = 'manual')
			AND EXISTS (SELECT 1 FROM accounts WHERE id = old.account_id) BEGIN
		INSERT OR REPLACE INTO label_keep(account_id, fp, label_id, active)
			SELECT old.account_id, old.label_fp, label_id, active FROM message_labels WHERE message_id = old.id AND source = 'manual';
		INSERT OR REPLACE INTO message_labels(message_id, label_id, source, active)
			SELECT m.id, ml.label_id, 'manual', ml.active FROM messages m JOIN message_labels ml ON ml.message_id = old.id AND ml.source = 'manual'
			WHERE m.account_id = old.account_id AND m.label_fp = old.label_fp AND m.id != old.id;
	END`,
	`CREATE TRIGGER messages_label_keep AFTER INSERT ON messages
		WHEN new.label_fp IS NOT NULL AND EXISTS (SELECT 1 FROM label_keep WHERE account_id = new.account_id AND fp = new.label_fp) BEGIN
		INSERT OR REPLACE INTO message_labels(message_id, label_id, source, active)
			SELECT new.id, label_id, 'manual', active FROM label_keep WHERE account_id = new.account_id AND fp = new.label_fp;
	END`,
}, {

	`ALTER TABLE model_services ADD COLUMN oauth_email TEXT NOT NULL DEFAULT ''`,
	`ALTER TABLE model_services ADD COLUMN oauth_client_id TEXT NOT NULL DEFAULT ''`,
}, {

	`ALTER TABLE plugins ADD COLUMN trust TEXT NOT NULL DEFAULT ''`,
	`ALTER TABLE plugins ADD COLUMN library_hash TEXT NOT NULL DEFAULT ''`,
}}