import { DatabaseSync } from "node:sqlite";
import { dirname, join } from "node:path";
import { mkdirSync } from "node:fs";

export interface VariableRow {
	id: number;
	entry_id: string | null;
	kind: string;
	content: string;
	summary: string;
	size_tokens: number;
	seq: number;
	created_at: number;
	pinned: number; // 0 none, 1 decayable, 2 persistent
	dropped: number;
	in_context: number;
	rollup_id: number | null;
}

export interface PendingDecision {
	id: number;
	target: string;
	target_type: string;
	action: string;
	decay: number;
	summary: string | null;
	ts: number;
}

export class VarStore {
	private db: DatabaseSync;
	private ro: DatabaseSync;
	readonly path: string;

	constructor(path: string) {
		this.path = path;
		try {
			mkdirSync(dirname(path), { recursive: true });
		} catch {
			// dir may already exist or be unwritable; DatabaseSync will surface real errors
		}
		this.db = new DatabaseSync(path);
		this.db.exec("PRAGMA journal_mode=WAL");
		this.db.exec("PRAGMA busy_timeout=5000");
		this.ro = new DatabaseSync(path, { readOnly: true });
		this.init();
	}

	private init() {
		this.db.exec(`
CREATE TABLE IF NOT EXISTS variables (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  entry_id TEXT UNIQUE,
  kind TEXT NOT NULL DEFAULT 'other',
  content TEXT NOT NULL DEFAULT '',
  summary TEXT NOT NULL DEFAULT '',
  size_tokens INTEGER NOT NULL DEFAULT 0,
  seq INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL,
  pinned INTEGER NOT NULL DEFAULT 0,
  dropped INTEGER NOT NULL DEFAULT 0,
  in_context INTEGER NOT NULL DEFAULT 0,
  rollup_id INTEGER
);
CREATE TABLE IF NOT EXISTS depends_on (
  var_id INTEGER NOT NULL,
  dep_var_id INTEGER NOT NULL,
  PRIMARY KEY (var_id, dep_var_id)
);
CREATE TABLE IF NOT EXISTS decisions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  target TEXT NOT NULL,
  target_type TEXT NOT NULL DEFAULT 'entry',
  action TEXT NOT NULL,
  decay INTEGER NOT NULL DEFAULT 1,
  summary TEXT,
  ts INTEGER NOT NULL,
  applied INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS rollups (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  range TEXT NOT NULL,
  summary TEXT NOT NULL DEFAULT '',
  created_at INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS meta (
  key TEXT PRIMARY KEY,
  value TEXT
);
CREATE INDEX IF NOT EXISTS idx_vars_seq ON variables(seq);
CREATE INDEX IF NOT EXISTS idx_vars_inctx ON variables(in_context);
CREATE INDEX IF NOT EXISTS idx_decisions_applied ON decisions(applied);
`);
	}

	// ---- meta ----
	getMeta(key: string): string | null {
		const row = this.db.prepare("SELECT value FROM meta WHERE key = ?").get(key) as unknown as { value: string } | undefined;
		return row ? row.value : null;
	}
	setMeta(key: string, value: string) {
		this.db
			.prepare("INSERT INTO meta (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value")
			.run(key, value);
	}

	nextSeq(): number {
		const cur = Number(this.getMeta("seq") ?? "0");
		const next = cur + 1;
		this.setMeta("seq", String(next));
		return next;
	}

	// ---- variables ----
	upsertByEntry(
		entryId: string,
		fields: { kind: string; content: string; summary: string; sizeTokens: number; seq: number; pinned: number; dropped: number; inContext: number },
	): number {
		const now = Date.now();
		this.db
			.prepare(
				`INSERT INTO variables (entry_id, kind, content, summary, size_tokens, seq, created_at, pinned, dropped, in_context)
				 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
				 ON CONFLICT(entry_id) DO UPDATE SET
				   kind = excluded.kind,
				   content = excluded.content,
				   summary = excluded.summary,
				   size_tokens = excluded.size_tokens,
				   seq = excluded.seq,
				   pinned = excluded.pinned,
				   dropped = excluded.dropped,
				   in_context = excluded.in_context`,
			)
			.run(entryId, fields.kind, fields.content, fields.summary, fields.sizeTokens, fields.seq, now, fields.pinned, fields.dropped, fields.inContext);
		const row = this.db.prepare("SELECT id FROM variables WHERE entry_id = ?").get(entryId) as unknown as { id: number };
		return row.id;
	}

	getByEntry(entryId: string): VariableRow | null {
		return (this.db.prepare("SELECT * FROM variables WHERE entry_id = ?").get(entryId) as unknown as VariableRow) ?? null;
	}

	/**
	 * Realtime population: create/refresh a variable for a message as it
	 * happens. Only content/kind/size are written; compaction state
	 * (pinned, dropped, in_context, summary, rollup_id, seq) is preserved.
	 * Idempotent — safe to call multiple times for the same entry.
	 */
	populate(entryId: string, kind: string, content: string, sizeTokens: number): number {
		const now = Date.now();
		const existing = this.getByEntry(entryId);
		if (existing) {
			this.db
				.prepare("UPDATE variables SET kind = ?, content = ?, size_tokens = ? WHERE id = ?")
				.run(kind, content, sizeTokens, existing.id);
			return existing.id;
		}
		const res = this.db
			.prepare(
				"INSERT INTO variables (entry_id, kind, content, summary, size_tokens, seq, created_at, pinned, dropped, in_context) VALUES (?, ?, ?, '', ?, ?, ?, 0, 0, 0)",
			)
			.run(entryId, kind, content, sizeTokens, this.nextSeq(), now);
		return Number(res.lastInsertRowid);
	}

	getVar(id: number): VariableRow | null {
		return (this.db.prepare("SELECT * FROM variables WHERE id = ?").get(id) as unknown as VariableRow) ?? null;
	}

	getVarByRef(ref: string): VariableRow | null {
		const m = /^var_(\d+)$/.exec(ref.trim());
		if (!m) return null;
		return this.getVar(Number(m[1]));
	}

	updateSummary(varId: number, summary: string) {
		this.db.prepare("UPDATE variables SET summary = ? WHERE id = ?").run(summary, varId);
	}

	/** Re-key a variable row to its real session entry id (keeps seq/state). */
	updateEntryId(varId: number, entryId: string) {
		const clash = this.db.prepare("SELECT id FROM variables WHERE entry_id = ? AND id != ?").get(entryId, varId) as unknown as { id: number } | undefined;
		if (clash) {
			// A row already exists under the real id (e.g. compaction created it):
			// merge content into it and drop the duplicate.
			const current = this.getVar(varId);
			if (current) {
				this.db
					.prepare("UPDATE variables SET content = ?, size_tokens = ? WHERE id = ?")
					.run(current.content, current.size_tokens, clash.id);
				this.db.prepare("DELETE FROM variables WHERE id = ?").run(varId);
			}
			return;
		}
		this.db.prepare("UPDATE variables SET entry_id = ? WHERE id = ?").run(entryId, varId);
	}

	setPinned(varId: number, level: number) {
		this.db.prepare("UPDATE variables SET pinned = ? WHERE id = ?").run(level, varId);
	}

	setDropped(varId: number, dropped: number, inContext: number) {
		this.db.prepare("UPDATE variables SET dropped = ?, in_context = ? WHERE id = ?").run(dropped, inContext, varId);
	}

	setInContext(varId: number, inContext: number) {
		this.db.prepare("UPDATE variables SET in_context = ? WHERE id = ?").run(inContext, varId);
	}

	setRollup(varId: number, rollupId: number) {
		this.db.prepare("UPDATE variables SET rollup_id = ?, in_context = 0 WHERE id = ?").run(rollupId, varId);
	}

	listPinned(level: number): VariableRow[] {
		return this.db.prepare("SELECT * FROM variables WHERE pinned = ? ORDER BY seq ASC").all(level) as unknown as VariableRow[];
	}

	/** All pins which must be rendered in the next compacted context. */
	listPinnedAll(): VariableRow[] {
		// A pin is authoritative even if an older buggy version left stale
		// dropped/in_context flags on the row.
		return this.db.prepare("SELECT * FROM variables WHERE pinned > 0 ORDER BY seq ASC").all() as unknown as VariableRow[];
	}

	listInContext(): VariableRow[] {
		return this.db.prepare("SELECT * FROM variables WHERE in_context = 1 AND dropped = 0 ORDER BY seq ASC").all() as unknown as VariableRow[];
	}

	listRecent(limit: number): VariableRow[] {
		return this.db.prepare("SELECT * FROM variables ORDER BY seq DESC LIMIT ?").all(limit) as unknown as VariableRow[];
	}

	addDep(varId: number, depVarId: number) {
		this.db
			.prepare("INSERT OR IGNORE INTO depends_on (var_id, dep_var_id) VALUES (?, ?)")
			.run(varId, depVarId);
	}

	depsOf(varId: number): number[] {
		const rows = this.db.prepare("SELECT dep_var_id FROM depends_on WHERE var_id = ?").all(varId) as unknown as { dep_var_id: number }[];
		return rows.map((r) => r.dep_var_id);
	}

	// ---- decisions ----
	recordDecision(target: string, targetType: "entry" | "var", action: string, decay: boolean, summary?: string) {
		this.db
			.prepare("INSERT INTO decisions (target, target_type, action, decay, summary, ts) VALUES (?, ?, ?, ?, ?, ?)")
			.run(target, targetType, action, decay ? 1 : 0, summary ?? null, Date.now());
	}

	pendingDecisions(): PendingDecision[] {
		return this.db.prepare("SELECT * FROM decisions WHERE applied = 0 ORDER BY id ASC").all() as unknown as PendingDecision[];
	}

	markDecisionsApplied(ids: number[]) {
		if (ids.length === 0) return;
		const stmt = this.db.prepare("UPDATE decisions SET applied = 1 WHERE id = ?");
		for (const id of ids) stmt.run(id);
	}

	// ---- rollups ----
	listRollups(): Array<{ range: string; summary: string; id: number }> {
		return this.db.prepare("SELECT id, range, summary FROM rollups ORDER BY id ASC").all() as unknown as Array<{ range: string; summary: string; id: number }>;
	}

	/** Add or refresh a rollup range. Idempotence prevents repeated compactions
	 * from creating duplicate visible rollups for the same range. */
	addRollup(range: string, summary: string): number {
		const existing = this.db.prepare("SELECT id FROM rollups WHERE range = ? ORDER BY id ASC LIMIT 1").get(range) as unknown as { id: number } | undefined;
		if (existing) {
			this.db.prepare("UPDATE rollups SET summary = ? WHERE id = ?").run(summary, existing.id);
			return existing.id;
		}
		const res = this.db.prepare("INSERT INTO rollups (range, summary, created_at) VALUES (?, ?, ?)").run(range, summary, Date.now());
		return Number(res.lastInsertRowid);
	}

	// ---- query (read-only, for context_query tool) ----
	query(sql: string): unknown[] {
		const trimmed = sql.trim();
		if (!/^(SELECT|WITH)\b/i.test(trimmed)) {
			throw new Error("Only SELECT/WITH queries are allowed");
		}
		if (trimmed.includes(";")) {
			throw new Error("Only a single statement is allowed");
		}
		const rows = this.ro.prepare(trimmed).all() as unknown[];
		return rows;
	}

	stats(): Record<string, unknown> {
		const count = (this.db.prepare("SELECT COUNT(*) AS c FROM variables").get() as unknown as { c: number }).c;
		const inCtx = (this.db.prepare("SELECT COUNT(*) AS c FROM variables WHERE in_context = 1").get() as unknown as { c: number }).c;
		const dropped = (this.db.prepare("SELECT COUNT(*) AS c FROM variables WHERE dropped = 1").get() as unknown as { c: number }).c;
		const pinned = (this.db.prepare("SELECT COUNT(*) AS c FROM variables WHERE pinned > 0").get() as unknown as { c: number }).c;
		const pending = (this.db.prepare("SELECT COUNT(*) AS c FROM decisions WHERE applied = 0").get() as unknown as { c: number }).c;
		const rollups = (this.db.prepare("SELECT COUNT(*) AS c FROM rollups").get() as unknown as { c: number }).c;
		return { variables: count, inContext: inCtx, dropped, pinned, pendingDecisions: pending, rollups };
	}

	reset() {
		this.db.exec("DELETE FROM variables; DELETE FROM depends_on; DELETE FROM decisions; DELETE FROM rollups; DELETE FROM meta;");
	}

	close() {
		try {
			this.ro.close();
		} catch {
			// ignore
		}
		try {
			this.db.close();
		} catch {
			// ignore
		}
	}
}