import { existsSync } from "node:fs";
import { join } from "node:path";
import { DatabaseSync } from "node:sqlite";
import type { InspectData } from "../../web/lib/remaining-command-types.ts";
import { resolveBridgeRuntimeConfig } from "./bridge-service.ts";

/**
 * Collects project inspection data by reading the live .sf/sf.db SQLite
 * database (decisions, requirements, artifacts, schema_version). The
 * earlier implementation read a now-obsolete .sf/sf-db.json projection
 * file that SF stopped generating when the DB-first runtime landed —
 * the endpoint returned an empty payload on every modern repo because
 * the JSON file no longer exists.
 *
 * Read-only mode; never writes to the DB. The autonomous loop holds the
 * WAL writer; this query path uses readOnly so concurrent reads are
 * safe without contention.
 */
export async function collectInspectData(
	projectCwdOverride?: string,
): Promise<InspectData> {
	const config = resolveBridgeRuntimeConfig(undefined, projectCwdOverride);
	const { projectCwd } = config;

	const dbPath = join(projectCwd, ".sf", "sf.db");

	if (!existsSync(dbPath)) {
		return {
			schemaVersion: null,
			counts: { decisions: 0, requirements: 0, artifacts: 0 },
			recentDecisions: [],
			recentRequirements: [],
		};
	}

	let db: DatabaseSync | null = null;
	try {
		db = new DatabaseSync(dbPath, { readOnly: true });

		const schemaRow = db
			.prepare("SELECT MAX(version) AS v FROM schema_version")
			.get() as { v: number | null } | undefined;
		const schemaVersion = schemaRow?.v ?? null;

		const decisionsCount = (
			db.prepare("SELECT COUNT(*) AS n FROM decisions").get() as {
				n: number;
			}
		).n;
		const requirementsCount = (
			db.prepare("SELECT COUNT(*) AS n FROM requirements").get() as {
				n: number;
			}
		).n;
		const artifactsCount = (
			db.prepare("SELECT COUNT(*) AS n FROM artifacts").get() as {
				n: number;
			}
		).n;

		const recentDecisions = db
			.prepare(
				"SELECT id, decision, choice FROM decisions ORDER BY seq DESC LIMIT 5",
			)
			.all() as Array<{ id: string; decision: string; choice: string }>;

		// requirements has no monotonic seq column — order by id descending,
		// which matches the R001/R002/.../R0NN naming so newest first.
		const recentRequirements = db
			.prepare(
				"SELECT id, status, description FROM requirements ORDER BY id DESC LIMIT 5",
			)
			.all() as Array<{ id: string; status: string; description: string }>;

		return {
			schemaVersion,
			counts: {
				decisions: decisionsCount,
				requirements: requirementsCount,
				artifacts: artifactsCount,
			},
			recentDecisions,
			recentRequirements,
		};
	} catch {
		// Corrupt / locked / schema-drift — return empty payload rather than
		// 500 so the operator-facing endpoint stays available.
		return {
			schemaVersion: null,
			counts: { decisions: 0, requirements: 0, artifacts: 0 },
			recentDecisions: [],
			recentRequirements: [],
		};
	} finally {
		try {
			db?.close();
		} catch {
			// best-effort
		}
	}
}
