import { Database, type Statement } from "bun:sqlite";
import * as fs from "node:fs";
import * as path from "node:path";
import {
	type AuthCredential,
	type AuthCredentialStore,
	parseServiceTier,
	SqliteAuthCredentialStore,
	type ServiceTier,
	type StoredAuthCredential,
} from "@oh-my-pi/pi-ai";
import {
	AsyncDrain,
	checkpointWal,
	getAgentDbPath,
	getDbBusyTimeoutMs,
	getStatsDbPath,
	isRecord,
	logger,
	openSqliteDatabase,
	postmortem,
} from "@oh-my-pi/pi-utils";
import type { RawSettings as Settings } from "../config/settings";

/** Row shape for settings table queries */
type SettingsRow = {
	key: string;
	value: string;
};

/** Row shape for the model_usage MRU query (ordering column stays in SQL) */
type ModelUsageRow = {
	model_key: string;
};

/** Row shape for model_perf table queries */
type ModelPerfRow = {
	model_key: string;
	samples: number;
	output_tokens: number;
	gen_ms: number;
	ttft_samples: number;
	ttft_ms: number;
};

/** Row shape read from an `omp stats` messages table during backfill. */
type StatsMessageRow = {
	rowid: number;
	timestamp: number;
	provider: string;
	model: string;
	output_tokens: number;
	duration: number;
	ttft: number | null;
	/** Served service tier; absent on stats databases written before the column existed. */
	service_tier?: string | null;
};

/** Per-model running sums accumulated during a backfill walk. */
type PerfAccum = {
	samples: number;
	outputTokens: number;
	genMs: number;
	ttftSamples: number;
	ttftMs: number;
};

/** One completed request's timing, folded into the per-model aggregates. */
export interface ModelPerfSample {
	/** Output tokens the provider reported for the turn. */
	outputTokens: number;
	/** Total request duration in milliseconds. */
	durationMs: number;
	/** Time to first token in milliseconds; omit when the provider did not report one. */
	ttftMs?: number;
}

/** Validated, insert-ready model_perf sample (see {@link normalizeModelPerfSample}). */
type ModelPerfInsert = {
	modelKey: string;
	outputTokens: number;
	durationMs: number;
	ttftSamples: 0 | 1;
	ttftMs: number;
};

/** Recency-weighted per-model performance averages. */
export interface ModelPerfStats {
	/** Decayed sample count backing the averages. */
	samples: number;
	/** Average output tokens/sec over the total request duration. */
	tps: number;
	/** Average time-to-first-token in milliseconds; null when no sample reported one. */
	ttftMs: number | null;
}

/**
 * Decay threshold for model_perf running sums: once a model accumulates this
 * many samples, each new sample first halves every aggregate, turning the
 * plain average into a recency-weighted one (provider speeds drift over time).
 */
const MODEL_PERF_DECAY_AT = 256;
/** meta-table marker set once historical stats.db rows have been imported into model_perf. */
const MODEL_PERF_BACKFILL_KEY = "model_perf_backfill_v2";
/**
 * Marker the v1 import wrote. Its presence means the aggregates already hold
 * the stats history (tierless, so blended into the bare rows), so the v2 pass
 * keeps the live aggregates and only records itself complete instead of adding
 * the same history again.
 */
const MODEL_PERF_BACKFILL_V1_KEY = "model_perf_backfill";
/**
 * Batch window for deferred model_perf writes. Perf aggregates are advisory, so
 * one transaction per minute replaces one per turn; the timer is unref'd and the
 * pending batch is flushed by {@link AgentStorage.close}, which the exit-only
 * postmortem hook runs on every real exit (normal, signal, fatal), and by
 * {@link AgentStorage.getModelPerf}, so in-process reads never lag the window.
 */
const MODEL_PERF_FLUSH_DELAY_MS = 60_000;
/** Backfill ignores stats.db history older than this; decay makes stale provider speeds worthless anyway. */
const MODEL_PERF_BACKFILL_MAX_AGE_MS = 90 * 86_400_000;
/** Rows fetched per synchronous backfill chunk — keeps per-chunk event-loop blocking under ~20ms even on cold I/O. */
const MODEL_PERF_BACKFILL_CHUNK = 2048;
/** Hard ceiling on rows scanned per backfill run, whatever the age cutoff admits — bounds total CPU on very high-volume databases (models only seen earlier than the newest N measurable rows get no backfill). */
const MODEL_PERF_BACKFILL_MAX_ROWS = 250_000;

/**
 * `model_perf` row key: the model, plus the service tier when the turn ran on a
 * non-default one (`provider/model@ultrafast`). Tier rows keep a fast serving
 * path's throughput from blending into the standard average — a 300 t/s
 * ultrafast turn and a 25 t/s standard turn are different measurements, not one
 * 160 t/s model. Readers that do not know the tier read the bare
 * `provider/model` row, which stays the standard/default-tier aggregate.
 */
export function modelPerfKey(modelKey: string, serviceTier?: ServiceTier | null): string {
	return serviceTier && serviceTier !== "auto" && serviceTier !== "default" ? `${modelKey}@${serviceTier}` : modelKey;
}

/**
 * Validates one request timing and shapes it for the model_perf upsert.
 * Returns null for unmeasurable samples (no tokens, no duration). Out-of-range
 * TTFT (>= duration) is bogus latency data; the sample still measures throughput.
 */
function normalizeModelPerfSample(modelKey: string, sample: ModelPerfSample): ModelPerfInsert | null {
	const { outputTokens, durationMs } = sample;
	if (!Number.isFinite(outputTokens) || outputTokens <= 0) return null;
	if (!Number.isFinite(durationMs) || durationMs <= 0) return null;
	const ttftMs =
		sample.ttftMs !== undefined && Number.isFinite(sample.ttftMs) && sample.ttftMs > 0 && sample.ttftMs < durationMs
			? sample.ttftMs
			: undefined;
	return { modelKey, outputTokens, durationMs, ttftSamples: ttftMs !== undefined ? 1 : 0, ttftMs: ttftMs ?? 0 };
}

/**
 * Named usage counters kept in agent.db, one `<kind>_usage` table each:
 * `command` ranks slash-command autocomplete, `hint` retires learned composer hints.
 */
export type UsageKind = (typeof USAGE_KINDS)[number];
const USAGE_KINDS = ["command", "hint"] as const;

/** Prepared statements for one `<kind>_usage` table. */
type UsageStatements = { upsert: Statement; list: Statement };

/** Current agent.db schema version; bump when schema changes require migration. */
export const SCHEMA_VERSION = 6;
const SQLITE_NOW_EPOCH = "CAST(strftime('%s','now') AS INTEGER)";

/** Singleton instances per database path */
const instances = new Map<string, AgentStorage>();
let cancelExitCleanup: (() => void) | undefined;

/**
 * The agent.db surface sessions and tools reach through `Settings.getStorage()`.
 * {@link AgentStorage} implements it over SQLite; a subagent worker's settings
 * mirrors return a remote implementation that runs every call on the main
 * thread's open storage (`thread/services/runtime-remote.ts`), so workers never
 * open a second connection.
 */
export interface AgentStorageApi {
	/** Cached value by key; null when missing or expired. */
	getCache(key: string): string | null;
	/** Set a cached value with expiry time (unix seconds). */
	setCache(key: string, value: string, expiresAtSec: number): void;
	/** Stored auth credentials, optionally filtered by provider. */
	listAuthCredentials(provider?: string, includeDisabled?: boolean): StoredAuthCredential[];
	/** Record a model use ("provider/modelId"), bumping it in the MRU order. */
	recordModelUsage(modelKey: string): void;
	/** Model keys, most recently used first. */
	getModelUsageOrder(): string[];
	/** Fold one completed request's timing into the model's perf aggregates. */
	recordModelPerf(modelKey: string, sample: ModelPerfSample, serviceTier?: ServiceTier | null): Promise<void>;
	/** Recency-weighted perf averages keyed by "provider/modelId". */
	getModelPerf(): Map<string, ModelPerfStats>;
	/** Claim a one-time notice across every process sharing the database. */
	claimNotice(key: string, expiresAtMs: number): boolean;
}

/**
 * Unified SQLite storage for agent settings, model usage, and auth credentials.
 * Delegates auth credential operations to AuthCredentialStore from @oh-my-pi/pi-ai.
 * Uses singleton pattern per database path; access via AgentStorage.open().
 */
export class AgentStorage implements AgentStorageApi {
	#db: Database;
	#authStore: AuthCredentialStore;

	#listSettingsStmt: Statement;
	#upsertModelUsageStmt: Statement;
	#listModelUsageStmt: Statement;
	#upsertModelPerfStmt: Statement;
	#listModelPerfStmt: Statement;
	#usageStmts: Record<UsageKind, UsageStatements>;
	#modelUsageCache: string[] | null = null;
	/** Only the real user db auto-imports stats.db history; custom paths (tests, embedding) opt in explicitly. */
	#autoPerfBackfill: boolean;
	/** One backfill *check* per process; the persistent gate is the meta marker. */
	#perfBackfillChecked = false;
	/** Coalesces per-turn perf samples into one deferred transaction off the turn's hot path. */
	#perfDrain = new AsyncDrain<ModelPerfInsert>(MODEL_PERF_FLUSH_DELAY_MS, { unref: true });
	#closing = false;

	private constructor(db: Database, dbPath: string) {
		this.#db = db;
		this.#autoPerfBackfill = dbPath === getAgentDbPath();

		this.#initializeSchema();
		this.#hardenPermissions(dbPath);

		// Create AuthCredentialStore with our open database
		this.#authStore = new SqliteAuthCredentialStore(this.#db);

		this.#listSettingsStmt = this.#db.prepare("SELECT key, value FROM settings");
		this.#upsertModelUsageStmt = this.#db.prepare(
			`INSERT INTO model_usage (model_key, last_used_at) VALUES (?, ${SQLITE_NOW_EPOCH}) ON CONFLICT(model_key) DO UPDATE SET last_used_at = ${SQLITE_NOW_EPOCH}`,
		);
		this.#listModelUsageStmt = this.#db.prepare("SELECT model_key FROM model_usage ORDER BY last_used_at DESC");
		// Recency-weighted upsert: past MODEL_PERF_DECAY_AT samples, every new
		// sample first halves the aggregates so old measurements fade out.
		this.#upsertModelPerfStmt = this.#db.prepare(
			`INSERT INTO model_perf (model_key, samples, output_tokens, gen_ms, ttft_samples, ttft_ms, updated_at)
VALUES (?1, 1, ?2, ?3, ?4, ?5, ${SQLITE_NOW_EPOCH})
ON CONFLICT(model_key) DO UPDATE SET
	samples = (CASE WHEN model_perf.samples >= ${MODEL_PERF_DECAY_AT} THEN model_perf.samples / 2 ELSE model_perf.samples END) + 1,
	output_tokens = (CASE WHEN model_perf.samples >= ${MODEL_PERF_DECAY_AT} THEN model_perf.output_tokens * 0.5 ELSE model_perf.output_tokens END) + excluded.output_tokens,
	gen_ms = (CASE WHEN model_perf.samples >= ${MODEL_PERF_DECAY_AT} THEN model_perf.gen_ms * 0.5 ELSE model_perf.gen_ms END) + excluded.gen_ms,
	ttft_samples = (CASE WHEN model_perf.samples >= ${MODEL_PERF_DECAY_AT} THEN model_perf.ttft_samples * 0.5 ELSE model_perf.ttft_samples END) + excluded.ttft_samples,
	ttft_ms = (CASE WHEN model_perf.samples >= ${MODEL_PERF_DECAY_AT} THEN model_perf.ttft_ms * 0.5 ELSE model_perf.ttft_ms END) + excluded.ttft_ms,
	updated_at = ${SQLITE_NOW_EPOCH}`,
		);
		// `model_key TEXT PRIMARY KEY` admits NULL (SQLite rowid-table quirk); a NULL
		// key would break every consumer that treats perf keys as selectors.
		this.#listModelPerfStmt = this.#db.prepare(
			"SELECT model_key, samples, output_tokens, gen_ms, ttft_samples, ttft_ms FROM model_perf WHERE model_key IS NOT NULL",
		);
		this.#usageStmts = {
			command: this.#prepareUsageStatements("command"),
			hint: this.#prepareUsageStatements("hint"),
		};
	}

	#prepareUsageStatements(kind: UsageKind): UsageStatements {
		const table = `${kind}_usage`;
		return {
			upsert: this.#db.prepare(
				`INSERT INTO ${table} (name, count, last_used_at) VALUES (?, 1, ${SQLITE_NOW_EPOCH})
ON CONFLICT(name) DO UPDATE SET count = ${table}.count + 1, last_used_at = ${SQLITE_NOW_EPOCH}`,
			),
			list: this.#db.prepare(`SELECT name, count FROM ${table}`),
		};
	}

	/**
	 * Creates tables if missing and migrates legacy settings.
	 * AuthCredentialStore handles auth_credentials and cache tables.
	 */
	#initializeSchema(): void {
		this.#db.run(`
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;

CREATE TABLE IF NOT EXISTS model_usage (
	model_key TEXT PRIMARY KEY,
	last_used_at INTEGER NOT NULL DEFAULT (${SQLITE_NOW_EPOCH})
);

CREATE TABLE IF NOT EXISTS model_perf (
	model_key TEXT PRIMARY KEY,
	samples REAL NOT NULL DEFAULT 0,
	output_tokens REAL NOT NULL DEFAULT 0,
	gen_ms REAL NOT NULL DEFAULT 0,
	ttft_samples REAL NOT NULL DEFAULT 0,
	ttft_ms REAL NOT NULL DEFAULT 0,
	updated_at INTEGER NOT NULL DEFAULT (${SQLITE_NOW_EPOCH})
);

CREATE TABLE IF NOT EXISTS command_usage (
	name TEXT PRIMARY KEY,
	count INTEGER NOT NULL DEFAULT 0,
	last_used_at INTEGER NOT NULL DEFAULT (${SQLITE_NOW_EPOCH})
);

CREATE TABLE IF NOT EXISTS hint_usage (
	name TEXT PRIMARY KEY,
	count INTEGER NOT NULL DEFAULT 0,
	last_used_at INTEGER NOT NULL DEFAULT (${SQLITE_NOW_EPOCH})
);

CREATE TABLE IF NOT EXISTS meta (
	key TEXT PRIMARY KEY,
	value TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS notice_claims (
	key TEXT PRIMARY KEY,
	expires_at_ms INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS schema_version (version INTEGER PRIMARY KEY);
`);

		// One-off statements are scoped with `using`: an unfinalized statement keeps the SQLite
		// connection (and agent.db) open after close() until GC collects it.
		using settingsInfoStmt = this.#db.prepare("PRAGMA table_info(settings)");
		const settingsInfo = settingsInfoStmt.all() as Array<{ name?: string }>;
		const hasSettingsTable = settingsInfo.length > 0;
		const hasKey = settingsInfo.some(column => column.name === "key");
		const hasValue = settingsInfo.some(column => column.name === "value");

		if (!hasSettingsTable) {
			this.#db.run(`
CREATE TABLE settings (
	key TEXT PRIMARY KEY,
	value TEXT NOT NULL,
	updated_at INTEGER NOT NULL DEFAULT (${SQLITE_NOW_EPOCH})
);
`);
		} else if (!hasKey || !hasValue) {
			// Migrate v1 schema: single JSON blob in `data` column → per-key rows
			let legacySettings: Record<string, unknown> | null = null;
			using legacyRowStmt = this.#db.prepare("SELECT data FROM settings WHERE id = 1");
			const row = legacyRowStmt.get() as { data?: string } | undefined;
			if (row?.data) {
				try {
					const parsed = JSON.parse(row.data);
					if (isRecord(parsed)) {
						legacySettings = parsed;
					} else {
						logger.warn("AgentStorage legacy settings invalid shape");
					}
				} catch (error) {
					logger.warn("AgentStorage failed to parse legacy settings", { error: String(error) });
				}
			}

			const migrate = this.#db.transaction((settings: Record<string, unknown> | null) => {
				this.#db.run("DROP TABLE settings");
				this.#db.run(`
CREATE TABLE settings (
	key TEXT PRIMARY KEY,
	value TEXT NOT NULL,
	updated_at INTEGER NOT NULL DEFAULT (${SQLITE_NOW_EPOCH})
);
`);
				if (settings) {
					using insert = this.#db.prepare(
						`INSERT INTO settings (key, value, updated_at) VALUES (?, ?, ${SQLITE_NOW_EPOCH})`,
					);
					for (const [key, value] of Object.entries(settings)) {
						if (value === undefined) continue;
						const serialized = JSON.stringify(value);
						if (serialized === undefined) continue;
						insert.run(key, serialized);
					}
				}
			});

			migrate(legacySettings);
		}

		using versionStmt = this.#db.prepare("SELECT version FROM schema_version ORDER BY version DESC LIMIT 1");
		const versionRow = versionStmt.get() as { version?: number } | undefined;
		const schemaVersion = typeof versionRow?.version === "number" ? versionRow.version : 0;
		if (versionRow?.version !== undefined && versionRow.version !== SCHEMA_VERSION) {
			logger.warn("AgentStorage schema version mismatch", {
				current: versionRow.version,
				expected: SCHEMA_VERSION,
			});
		}
		if (schemaVersion < SCHEMA_VERSION) {
			this.#migrateSchema(schemaVersion);
			// Only an upgrade (or a fresh db) records the version; rewriting the same
			// row on every open was a write transaction per process start.
			using recordVersionStmt = this.#db.prepare("INSERT OR REPLACE INTO schema_version(version) VALUES (?)");
			recordVersionStmt.run(SCHEMA_VERSION);
		}
	}

	#migrateSchema(fromVersion: number): void {
		if (fromVersion < 4) {
			// v3 → v4: Add disabled column to auth_credentials (handled by AuthCredentialStore)
			// Nothing to do here - AuthCredentialStore will handle this migration
		}
		if (fromVersion < 5) {
			this.#migrateSchemaV4ToV5();
		}
		if (fromVersion < 6) {
			// v5 → v6: TPS switched from the post-TTFT decode window to total
			// request duration (hidden reasoning made decode-window rates bogus).
			// Purge the old aggregates and re-arm the stats.db backfill so
			// history is re-imported through the corrected fold.
			this.#db.run("DELETE FROM model_perf");
			using clearBackfillStmt = this.#db.prepare("DELETE FROM meta WHERE key = ?");
			clearBackfillStmt.run(MODEL_PERF_BACKFILL_KEY);
		}
	}

	#migrateSchemaV4ToV5(): void {
		const migrate = this.#db.transaction(() => {
			this.#db.run("ALTER TABLE settings RENAME TO settings_legacy");
			this.#db.run(`
CREATE TABLE settings (
	key TEXT PRIMARY KEY,
	value TEXT NOT NULL,
	updated_at INTEGER NOT NULL DEFAULT (${SQLITE_NOW_EPOCH})
);
`);
			this.#db.run(`
INSERT INTO settings (key, value, updated_at)
SELECT key, value, updated_at
FROM settings_legacy
`);
			this.#db.run("DROP TABLE settings_legacy");

			this.#db.run("ALTER TABLE model_usage RENAME TO model_usage_legacy");
			this.#db.run(`
CREATE TABLE model_usage (
	model_key TEXT PRIMARY KEY,
	last_used_at INTEGER NOT NULL DEFAULT (${SQLITE_NOW_EPOCH})
);
`);
			this.#db.run(`
INSERT INTO model_usage (model_key, last_used_at)
SELECT model_key, last_used_at
FROM model_usage_legacy
`);
			this.#db.run("DROP TABLE model_usage_legacy");
		});
		migrate();
	}

	/**
	 * Returns singleton instance for the given database path, creating if needed.
	 * Retries on the `SQLITE_BUSY` family (including `SQLITE_BUSY_RECOVERY`) with
	 * exponential backoff. Corrupt stores are quarantined and initialized once
	 * from an empty replacement. See issue #2421.
	 * @param dbPath - Path to the SQLite database file (defaults to config path)
	 * @returns AgentStorage instance for the given path
	 */
	static async open(dbPath: string = getAgentDbPath()): Promise<AgentStorage> {
		const existing = instances.get(dbPath);
		if (existing) return existing;

		fs.mkdirSync(path.dirname(dbPath), { recursive: true, mode: 0o700 });
		return openSqliteDatabase(
			dbPath,
			db => {
				const storage = new AgentStorage(db, dbPath);
				// Publish synchronously: concurrent opens must reuse this handle before the helper yields.
				// Exit-only cleanup keeps the connection valid for continuing sessions.
				cancelExitCleanup ??= postmortem.register("agent-storage", () => AgentStorage.close(), { exitOnly: true });
				instances.set(dbPath, storage);
				return storage;
			},
			{ recoverCorruption: true },
		);
	}

	/** Flushes deferred writes, closes every process-wide database, and permits reopening them. */
	static close(): void {
		for (const storage of instances.values()) storage.#close();
		instances.clear();
		cancelExitCleanup?.();
		cancelExitCleanup = undefined;
	}

	#close(): void {
		this.#closing = true;
		// Model-performance batches are synchronous once invoked, so this
		// persists them before finalizing their statements during process exit.
		void this.#perfDrain.flush();
		// Best-effort: a database whose directory was removed (agent dir deleted underneath the
		// process) cannot checkpoint, and that must not keep the remaining handles open.
		try {
			checkpointWal(this.#db);
		} catch (error) {
			logger.debug("AgentStorage: WAL checkpoint on close failed", { error: String(error) });
		}
		this.#listSettingsStmt.finalize();
		this.#upsertModelUsageStmt.finalize();
		this.#listModelUsageStmt.finalize();
		this.#upsertModelPerfStmt.finalize();
		this.#listModelPerfStmt.finalize();
		for (const kind of USAGE_KINDS) {
			const stmts = this.#usageStmts[kind];
			stmts.upsert.finalize();
			stmts.list.finalize();
		}
		// SqliteAuthCredentialStore.close() finalizes its own statements and
		// closes the shared #db handle — must run after our statements finalize.
		this.#authStore.close();
	}

	/**
	 * Reads legacy settings persisted in the agent.db `settings` table.
	 * The canonical settings store is `config.yml`; this accessor only
	 * exists so the config loader can migrate values from older installs.
	 * @returns Settings object, or null if no settings are stored
	 */
	getSettings(): Settings | null {
		const rows = (this.#listSettingsStmt.all() as SettingsRow[]) ?? [];
		if (rows.length === 0) return null;
		const settings: Record<string, unknown> = {};
		for (const row of rows) {
			try {
				settings[row.key] = JSON.parse(row.value) as unknown;
			} catch (error) {
				logger.warn("AgentStorage failed to parse setting", {
					key: row.key,
					error: String(error),
				});
			}
		}
		return settings as Settings;
	}

	/**
	 * Drops legacy `settings` rows after they have been written to config.yml.
	 * The table is only a migration source; leaving rows would resurrect values
	 * if config.yml is later deleted.
	 */
	clearMigratedSettings(): void {
		this.#db.run("DELETE FROM settings");
	}

	/**
	 * Records model usage, updating the last-used timestamp.
	 * @param modelKey - Model key in "provider/modelId" format
	 */
	recordModelUsage(modelKey: string): void {
		try {
			this.#upsertModelUsageStmt.run(modelKey);
			this.#modelUsageCache = null;
		} catch (error) {
			logger.warn("AgentStorage failed to record model usage", { modelKey, error: String(error) });
		}
	}

	/**
	 * Gets model keys ordered by most recently used.
	 * Results are cached until recordModelUsage is called.
	 * @returns Array of model keys ("provider/modelId") in MRU order
	 */
	getModelUsageOrder(): string[] {
		if (this.#modelUsageCache) {
			return this.#modelUsageCache;
		}
		try {
			const rows = this.#listModelUsageStmt.all() as ModelUsageRow[];
			this.#modelUsageCache = rows.map(row => row.model_key);
			return this.#modelUsageCache;
		} catch (error) {
			logger.warn("AgentStorage failed to get model usage order", { error: String(error) });
			return [];
		}
	}
	/**
	 * Records one use of `name`, bumping its count and last-used timestamp.
	 * Failures are logged, never thrown: usage counts are advisory.
	 * @param name - Counter key, e.g. a canonical command name ("model", "skill:review") or hint id
	 */
	recordUsage(kind: UsageKind, name: string): void {
		try {
			this.#usageStmts[kind].upsert.run(name);
		} catch (error) {
			logger.warn("AgentStorage failed to record usage", { kind, name, error: String(error) });
		}
	}

	/**
	 * Claims a one-time notice: true only for the first claim of `key` among every
	 * process sharing this database, so concurrent omp windows show it once. The
	 * claim lasts until `expiresAtMs`, when the notice no longer applies.
	 * Failures are logged and return false: a notice that cannot be recorded
	 * stays unshown rather than repeating on every check.
	 */
	claimNotice(key: string, expiresAtMs: number): boolean {
		try {
			using prune = this.#db.prepare("DELETE FROM notice_claims WHERE expires_at_ms <= ?");
			prune.run(Date.now());
			using claim = this.#db.prepare("INSERT OR IGNORE INTO notice_claims (key, expires_at_ms) VALUES (?, ?)");
			return claim.run(key, expiresAtMs).changes > 0;
		} catch (error) {
			logger.warn("AgentStorage failed to claim notice", { key, error: String(error) });
			return false;
		}
	}

	/**
	 * Gets usage counts of one kind; empty when the read fails.
	 * @returns Counter key → use count
	 */
	listUsage(kind: UsageKind): Record<string, number> {
		try {
			const rows = this.#usageStmts[kind].list.all() as Array<{ name: string; count: number }>;
			const counts: Record<string, number> = {};
			for (const row of rows) counts[row.name] = row.count;
			return counts;
		} catch (error) {
			logger.warn("AgentStorage failed to list usage", { kind, error: String(error) });
			return {};
		}
	}

	/**
	 * Folds one completed request's timing into the model's perf aggregates.
	 * TPS is measured over the total request duration — not the post-TTFT
	 * decode window, which undercounts generation time (and so inflates the
	 * rate) when reasoning tokens are generated before the first visible
	 * token. Invalid samples (no tokens, no duration) are dropped.
	 *
	 * Deferred like prompt history: samples are batched and written in one
	 * transaction after {@link MODEL_PERF_FLUSH_DELAY_MS}, keeping SQLite off
	 * the turn-completion hot path. Fire-and-forget safe — flush failures are
	 * logged, never thrown; await the returned promise only to observe the flush.
	 * @param modelKey - Model key in "provider/modelId" format
	 * @param serviceTier - Tier the turn ran on; non-default tiers aggregate in
	 * their own row (see {@link modelPerfKey})
	 */
	recordModelPerf(modelKey: string, sample: ModelPerfSample, serviceTier?: ServiceTier | null): Promise<void> {
		const row = normalizeModelPerfSample(modelPerfKey(modelKey, serviceTier), sample);
		if (!row) return Promise.resolve();
		return this.#perfDrain.push(row, rows => this.#flushModelPerf(rows));
	}

	#flushModelPerf(rows: ModelPerfInsert[]): void {
		// A close-triggered flush must persist only the queued live batch. Starting
		// the async stats import here could commit aggregates without its marker.
		if (!this.#closing) this.#kickModelPerfBackfill();
		try {
			this.#db.transaction((batch: ModelPerfInsert[]) => {
				for (const row of batch) this.#foldModelPerf(row);
			})(rows);
		} catch (error) {
			logger.warn("AgentStorage failed to record model perf", { error: String(error) });
		}
	}

	#foldModelPerf(row: ModelPerfInsert): void {
		this.#upsertModelPerfStmt.run(row.modelKey, row.outputTokens, row.durationMs, row.ttftSamples, row.ttftMs);
	}

	/**
	 * Returns recency-weighted TPS/TTFT averages for every model with recorded
	 * requests, keyed by "provider/modelId". Read by the /models browser.
	 * Also kicks the one-time background stats.db import; until it completes,
	 * models without live samples are simply absent. Drains the pending perf
	 * batch first so reads reflect every sample this process has recorded.
	 */
	getModelPerf(): Map<string, ModelPerfStats> {
		// The drain handler runs synchronously, so the batch is committed before the read.
		void this.#perfDrain.flush();
		this.#kickModelPerfBackfill();
		const stats = new Map<string, ModelPerfStats>();
		try {
			for (const row of this.#listModelPerfStmt.all() as ModelPerfRow[]) {
				if (row.gen_ms <= 0 || row.output_tokens <= 0) continue;
				stats.set(row.model_key, {
					samples: row.samples,
					tps: (row.output_tokens * 1000) / row.gen_ms,
					ttftMs: row.ttft_samples > 0 ? row.ttft_ms / row.ttft_samples : null,
				});
			}
		} catch (error) {
			logger.warn("AgentStorage failed to read model perf", { error: String(error) });
		}
		return stats;
	}

	/**
	 * One-time, non-blocking import of historical request timings from the
	 * `omp stats` database (`~/.omp/stats.db`) into model_perf. Fire-and-forget:
	 * the walk runs in bounded chunks with event-loop yields between them
	 * (bun:sqlite is synchronous — an unbounded scan here froze the TUI for
	 * ~30s on multi-million-row stats databases), and the persistent meta
	 * marker is only set on success so a crash or error retries next process.
	 * A missing stats.db leaves the marker unset so a later `omp stats` run
	 * still gets imported. No-op for non-default db paths.
	 */
	#kickModelPerfBackfill(): void {
		if (!this.#autoPerfBackfill || this.#perfBackfillChecked) return;
		this.#perfBackfillChecked = true;
		try {
			using markerStmt = this.#db.prepare("SELECT value FROM meta WHERE key = ?");
			const marker = markerStmt.get(MODEL_PERF_BACKFILL_KEY);
			if (marker) return;
			// The v1 import already folded the stats history into the bare rows, and no
			// stats row written before the served-tier field carries a tier, so
			// re-importing would only double-count it. Keep the live aggregates (blended
			// history decays out of them) and just record v2.
			if (markerStmt.get(MODEL_PERF_BACKFILL_V1_KEY)) {
				using markCompleteStmt = this.#db.prepare("INSERT OR REPLACE INTO meta (key, value) VALUES (?, ?)");
				markCompleteStmt.run(MODEL_PERF_BACKFILL_KEY, "complete");
				return;
			}
			const statsDbPath = getStatsDbPath();
			if (!fs.existsSync(statsDbPath)) return;
			void this.backfillModelPerfFromStats(statsDbPath)
				.then(imported => {
					using markCompleteStmt = this.#db.prepare("INSERT OR REPLACE INTO meta (key, value) VALUES (?, ?)");
					markCompleteStmt.run(MODEL_PERF_BACKFILL_KEY, "complete");
					logger.info("AgentStorage imported model perf history from stats.db", { imported });
				})
				.catch(error => {
					logger.warn("AgentStorage model perf backfill failed", { error: String(error) });
				});
		} catch (error) {
			logger.warn("AgentStorage model perf backfill failed", { error: String(error) });
		}
	}

	/**
	 * Imports recent measurable request rows from an `omp stats` database
	 * (`messages` table) into the model_perf aggregates. Walks newest-first
	 * over the timestamp index in {@link MODEL_PERF_BACKFILL_CHUNK}-row chunks,
	 * yielding to the event loop between chunks, and keeps at most
	 * {@link MODEL_PERF_DECAY_AT} rows per model within the
	 * {@link MODEL_PERF_BACKFILL_MAX_AGE_MS} window — beyond either bound the
	 * live decay would erase the contribution anyway. Errored turns are
	 * excluded; aborted turns with reported usage count, matching live capture.
	 * Sums land in one additive transaction at the end, so concurrent live
	 * samples merge correctly regardless of order.
	 * @param statsDbPath - Path to a stats.db file; opened read-only
	 * @returns Number of rows folded in
	 * @throws When the stats db cannot be opened or queried
	 */
	async backfillModelPerfFromStats(statsDbPath: string): Promise<number> {
		const statsDb = new Database(statsDbPath, { readonly: true });
		try {
			statsDb.run(`PRAGMA busy_timeout = ${getDbBusyTimeoutMs()}`);
			// Stats databases written before the served-tier column existed cannot
			// separate a fast serving path's samples from standard ones; those rows
			// import as standard-tier history.
			const hasTier = (statsDb.prepare("PRAGMA table_info(messages)").all() as { name: string }[]).some(
				column => column.name === "service_tier",
			);
			using select = statsDb.prepare(
				`SELECT rowid, timestamp, provider, model, output_tokens, duration, ttft${hasTier ? ", service_tier" : ""}
FROM messages
WHERE (timestamp < ?1 OR (timestamp = ?1 AND rowid < ?2))
	AND timestamp >= ?3
	AND duration > 0 AND output_tokens > 0 AND stop_reason != 'error'
ORDER BY timestamp DESC, rowid DESC
LIMIT ?4`,
			);
			const cutoff = Date.now() - MODEL_PERF_BACKFILL_MAX_AGE_MS;
			const sums = new Map<string, PerfAccum>();
			let cursorTimestamp = Number.MAX_SAFE_INTEGER;
			let cursorRowid = Number.MAX_SAFE_INTEGER;
			let scanned = 0;
			let imported = 0;
			while (scanned < MODEL_PERF_BACKFILL_MAX_ROWS) {
				const chunk = Math.min(MODEL_PERF_BACKFILL_CHUNK, MODEL_PERF_BACKFILL_MAX_ROWS - scanned);
				const rows = select.all(cursorTimestamp, cursorRowid, cutoff, chunk) as StatsMessageRow[];
				if (rows.length === 0) break;
				scanned += rows.length;
				const last = rows[rows.length - 1];
				cursorTimestamp = last.timestamp;
				cursorRowid = last.rowid;
				for (const row of rows) {
					const key = modelPerfKey(`${row.provider}/${row.model}`, parseServiceTier(row.service_tier));
					let accum = sums.get(key);
					if (accum && accum.samples >= MODEL_PERF_DECAY_AT) continue;
					const normalized = normalizeModelPerfSample(key, {
						outputTokens: row.output_tokens,
						durationMs: row.duration,
						ttftMs: row.ttft ?? undefined,
					});
					if (!normalized) continue;
					if (!accum) {
						accum = { samples: 0, outputTokens: 0, genMs: 0, ttftSamples: 0, ttftMs: 0 };
						sums.set(key, accum);
					}
					accum.samples += 1;
					accum.outputTokens += normalized.outputTokens;
					accum.genMs += normalized.durationMs;
					accum.ttftSamples += normalized.ttftSamples;
					accum.ttftMs += normalized.ttftMs;
					imported++;
				}
				if (rows.length < chunk) break;
				// Yield so a chunked walk never freezes the TUI (bun:sqlite is sync).
				await Bun.sleep(0);
			}
			if (sums.size > 0) {
				using upsert = this.#db.prepare(
					`INSERT INTO model_perf (model_key, samples, output_tokens, gen_ms, ttft_samples, ttft_ms, updated_at)
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ${SQLITE_NOW_EPOCH})
ON CONFLICT(model_key) DO UPDATE SET
	samples = model_perf.samples + excluded.samples,
	output_tokens = model_perf.output_tokens + excluded.output_tokens,
	gen_ms = model_perf.gen_ms + excluded.gen_ms,
	ttft_samples = model_perf.ttft_samples + excluded.ttft_samples,
	ttft_ms = model_perf.ttft_ms + excluded.ttft_ms,
	updated_at = ${SQLITE_NOW_EPOCH}`,
				);
				this.#db.transaction(() => {
					for (const [key, accum] of sums) {
						upsert.run(key, accum.samples, accum.outputTokens, accum.genMs, accum.ttftSamples, accum.ttftMs);
					}
				})();
			}
			return imported;
		} finally {
			statsDb.close();
		}
	}

	/**
	 * Checks if any auth credentials exist in storage.
	 * @returns True if at least one credential is stored
	 */
	hasAuthCredentials(): boolean {
		return this.#authStore.listAuthCredentials().length > 0;
	}

	/**
	 * Returns the underlying {@link AuthCredentialStore} so callers that need
	 * the lower-level pi-ai abstraction (e.g. `findAnthropicAuth(store)`) can
	 * reuse this storage's open database connection instead of opening their
	 * own.
	 */
	get authStore(): AuthCredentialStore {
		return this.#authStore;
	}

	/**
	 * Lists auth credentials, optionally filtered by provider.
	 * Only returns active (non-disabled) credentials by default.
	 * @param provider - Optional provider name to filter by
	 * @param includeDisabled - If true, includes disabled credentials
	 * @returns Array of stored credentials with their database IDs
	 */
	listAuthCredentials(provider?: string, includeDisabled = false): StoredAuthCredential[] {
		const credentials = this.#authStore.listAuthCredentials(provider);
		if (!includeDisabled) return credentials;

		using stmt = this.#db.prepare(
			provider
				? "SELECT id, provider, credential_type, data, disabled_cause FROM auth_credentials WHERE provider = ? ORDER BY id ASC"
				: "SELECT id, provider, credential_type, data, disabled_cause FROM auth_credentials ORDER BY id ASC",
		);
		const rows = (provider ? stmt.all(provider) : stmt.all()) as Array<{
			id: number;
			provider: string;
			credential_type: string;
			data: string;
			disabled_cause: string | null;
		}>;

		const results: StoredAuthCredential[] = [];
		for (const row of rows) {
			try {
				const parsed = JSON.parse(row.data);
				if (!parsed || typeof parsed !== "object") continue;

				let credential: AuthCredential;
				if (row.credential_type === "api_key" && typeof (parsed as { key?: unknown }).key === "string") {
					credential = { type: "api_key", key: (parsed as { key: string }).key };
				} else if (row.credential_type === "oauth") {
					credential = { type: "oauth", ...(parsed as Record<string, unknown>) } as AuthCredential;
				} else {
					continue;
				}

				results.push({ id: row.id, provider: row.provider, credential, disabledCause: row.disabled_cause });
			} catch {}
		}
		return results;
	}

	/**
	 * Atomically replaces all credentials for a provider.
	 * Useful for OAuth token refresh where old tokens should be discarded.
	 * @param provider - Provider name (e.g., "anthropic", "openai")
	 * @param credentials - New credentials to store
	 * @returns Array of newly stored credentials with their database IDs
	 */
	replaceAuthCredentials(provider: string, credentials: AuthCredential[]): Promise<StoredAuthCredential[]> {
		return this.#authStore.replaceAuthCredentials(provider, credentials);
	}

	/**
	 * Updates an existing auth credential by ID.
	 * @param id - Database row ID of the credential to update
	 * @param credential - New credential data
	 */
	updateAuthCredential(id: number, credential: AuthCredential): void {
		this.#authStore.updateAuthCredential(id, credential);
	}

	/**
	 * Disables an auth credential by ID with a persisted cause.
	 * @param id - Database row ID of the credential to disable
	 * @param disabledCause - Human-readable cause stored with the disabled row
	 */
	deleteAuthCredential(id: number, disabledCause: string): Promise<boolean> {
		return this.#authStore.deleteAuthCredential(id, disabledCause);
	}

	/**
	 * Disables all auth credentials for a provider with a persisted cause.
	 * @param provider - Provider name whose credentials should be disabled
	 * @param disabledCause - Human-readable cause stored with the disabled rows
	 */
	deleteAuthCredentials(provider: string, disabledCause: string): Promise<void> {
		return this.#authStore.deleteAuthCredentials(provider, disabledCause);
	}

	/**
	 * Gets a cached value by key. Returns null if not found or expired.
	 */
	getCache(key: string): string | null {
		return this.#authStore.getCache(key);
	}

	/**
	 * Sets a cached value with expiry time (unix seconds).
	 */
	setCache(key: string, value: string, expiresAtSec: number): void {
		this.#authStore.setCache(key, value, expiresAtSec);
	}

	/**
	 * Deletes expired cache entries. Call periodically for cleanup.
	 */
	cleanExpiredCache(): void {
		this.#authStore.cleanExpiredCache();
	}

	/**
	 * Restricts the agent dir to 0700 and the db to 0600, touching each only when
	 * its mode differs. POSIX modes are meaningless on Windows, so it is a no-op there.
	 */
	#hardenPermissions(dbPath: string): void {
		if (process.platform === "win32") return;
		const dir = path.dirname(dbPath);
		AgentStorage.#chmodIfNeeded(dir, 0o700, "AgentStorage failed to chmod agent dir");
		AgentStorage.#chmodIfNeeded(dbPath, 0o600, "AgentStorage failed to chmod db file");
	}

	static #chmodIfNeeded(target: string, mode: number, failureMessage: string): void {
		let current: number;
		try {
			current = fs.statSync(target).mode & 0o777;
		} catch {
			// Missing target (e.g. db not yet materialized): nothing to harden.
			return;
		}
		if (current === mode) return;
		try {
			fs.chmodSync(target, mode);
		} catch (error) {
			logger.warn(failureMessage, { path: target, error: String(error) });
		}
	}
}