// SF Database Abstraction Layer
// Provides a SQLite database via node:sqlite (Node >= 26 built-in).
//
// Exposes a unified sync API for decisions and requirements storage.
// Schema is initialized on first open with WAL mode for file-backed DBs.
//
// ─── Single-writer invariant ─────────────────────────────────────────────
// The sf-db/ modules are the only place in the codebase that issue write SQL
// (INSERT / UPDATE / DELETE / REPLACE / BEGIN-COMMIT transactions) against
// the engine database at `.sf/sf.db`. All other modules must call the typed
// wrappers exported through ../sf-db.js. Keep schema DDL in sf-db-schema.js and
// runtime writes in the relevant domain module.
//
// `_getAdapter()` is retained for read-only SELECTs in query modules
// (context-store, memory-store queries, doctor checks, projections).
// Do NOT use it for writes — add a wrapper here instead.
//
// The separate `.sf/unit-claims.db` managed by `unit-ownership.ts` is an
// intentionally independent store for cross-worktree claim races and is
// excluded from this invariant.
import {
	existsSync,
	mkdirSync,
	readdirSync,
	readFileSync,
	renameSync,
	rmSync,
	statSync,
	unlinkSync,
	writeFileSync,
} from "node:fs";
import { dirname, join } from "node:path";
import { DatabaseSync } from "node:sqlite";
import { getErrorMessage } from "../error-utils.js";
import { SF_STALE_STATE, SFError } from "../errors.js";
import {
	normalizeSchedulerStatus,
	taskFrontmatterFromRecord,
	withTaskFrontmatter,
} from "../task-frontmatter.js";
import { logError, logWarning } from "../workflow-logger.js";
import { initSchema } from "./sf-db-schema.js";

let loadAttempted = false;
function loadProvider() {
	if (loadAttempted) return;
	loadAttempted = true;
	// node:sqlite is built-in in Node >= 26
}
function normalizeRow(row) {
	if (row == null) return undefined;
	if (Object.getPrototypeOf(row) === null) {
		return { ...row };
	}
	return row;
}
function normalizeRows(rows) {
	return rows.map((r) => normalizeRow(r));
}
const DB_QUERY_TIMEOUT_MS = 30_000;
const DB_BACKUP_MIN_INTERVAL_MS = 15 * 60 * 1000;
const DB_BACKUP_RETENTION = 24;
const DB_FULL_VACUUM_MIN_INTERVAL_MS = 6 * 60 * 60 * 1000;
const MAX_ERROR_STORED_BYTES = 2048;

function createAdapter(rawDb) {
	const db = rawDb;
	const stmtCache = new Map();
	function wrapStmt(raw) {
		return {
			run(...params) {
				return raw.run(...params);
			},
			get(...params) {
				return normalizeRow(raw.get(...params));
			},
			all(...params) {
				return normalizeRows(raw.all(...params));
			},
		};
	}
	return {
		exec(sql) {
			db.exec(sql);
		},
		prepare(sql) {
			let cached = stmtCache.get(sql);
			if (cached) return cached;
			cached = wrapStmt(db.prepare(sql));
			stmtCache.set(sql, cached);
			return cached;
		},
		close() {
			stmtCache.clear();
			db.close();
		},
	};
}

/**
 * Execute a database query with timeout protection.
 * Falls back to empty result if query exceeds timeout.
 *
 * Purpose: Prevent hanging reads from blocking autonomous dispatch.
 *
 * Consumer: memory-repository.js, context-store.js, and any read query
 * that needs a safety ceiling.
 */
export function withQueryTimeout(
	operation,
	fallbackValue,
	timeoutMs = DB_QUERY_TIMEOUT_MS,
) {
	try {
		return operation();
	} catch (err) {
		if (err?.message?.includes("timeout") || err?.message?.includes("busy")) {
			logWarning(
				"sf-db",
				`Query timed out after ${timeoutMs}ms, returning fallback`,
			);
			return fallbackValue;
		}
		throw err;
	}
}
function openRawDb(path) {
	loadProvider();
	return new DatabaseSync(path);
}
function sqliteStringLiteral(value) {
	return `'${String(value).replaceAll("'", "''")}'`;
}
function databaseBackupDir(path) {
	return join(dirname(path), "backups", "db");
}
function latestDatabaseBackupMtime(dir) {
	if (!existsSync(dir)) return 0;
	let latest = 0;
	for (const entry of readdirSync(dir)) {
		if (!entry.startsWith("sf.db.")) continue;
		const file = join(dir, entry);
		try {
			const stat = statSync(file);
			if (stat.isFile() && stat.mtimeMs > latest) latest = stat.mtimeMs;
		} catch {
			// Ignore files that disappear during pruning.
		}
	}
	return latest;
}
function pruneDatabaseBackups(dir) {
	if (!existsSync(dir)) return;
	const backups = [];
	for (const entry of readdirSync(dir)) {
		if (!entry.startsWith("sf.db.")) continue;
		const file = join(dir, entry);
		try {
			const stat = statSync(file);
			if (stat.isFile()) backups.push({ file, mtimeMs: stat.mtimeMs });
		} catch {
			// Ignore files that disappear during pruning.
		}
	}
	backups.sort((a, b) => b.mtimeMs - a.mtimeMs);
	for (const backup of backups.slice(DB_BACKUP_RETENTION)) {
		try {
			unlinkSync(backup.file);
		} catch {
			// Best-effort retention; never block DB open on pruning.
		}
	}
}
function databaseMaintenancePath(path) {
	return join(databaseBackupDir(path), "maintenance.json");
}
function readDatabaseMaintenanceState(path) {
	try {
		return JSON.parse(readFileSync(databaseMaintenancePath(path), "utf-8"));
	} catch {
		return {};
	}
}
function writeDatabaseMaintenanceState(path, state) {
	try {
		writeFileSync(
			databaseMaintenancePath(path),
			JSON.stringify(state, null, 2) + "\n",
			"utf-8",
		);
	} catch {
		// Best-effort maintenance metadata.
	}
}
function createDatabaseSnapshot(rawDb, path) {
	if (path === ":memory:" || process.env.SF_DB_BACKUP_DISABLE === "1") return;
	const dir = databaseBackupDir(path);
	try {
		mkdirSync(dir, { recursive: true });
		const latest = latestDatabaseBackupMtime(dir);
		if (latest > 0 && Date.now() - latest < DB_BACKUP_MIN_INTERVAL_MS) return;
		const stamp = new Date().toISOString().replace(/[:.]/g, "-");
		const backupPath = join(dir, `sf.db.${stamp}`);
		const tmpPath = `${backupPath}.tmp`;
		rmSync(tmpPath, { force: true });
		rawDb.exec(`VACUUM INTO ${sqliteStringLiteral(tmpPath)}`);
		renameSync(tmpPath, backupPath);
		pruneDatabaseBackups(dir);
	} catch (err) {
		logWarning("sf-db", `database snapshot failed: ${getErrorMessage(err)}`);
		try {
			for (const entry of readdirSync(dir)) {
				if (entry.startsWith("sf.db.") && entry.endsWith(".tmp")) {
					rmSync(join(dir, entry), { force: true });
				}
			}
		} catch {
			// Best-effort cleanup; preserve the original snapshot warning.
		}
	}
}
function performDatabaseMaintenance(rawDb, path) {
	if (path === ":memory:" || process.env.SF_DB_MAINTENANCE_DISABLE === "1")
		return;
	try {
		const quickCheck = rawDb.prepare("PRAGMA quick_check").get();
		if (quickCheck?.quick_check !== "ok") {
			logWarning("sf-db", "database quick_check failed; skipping maintenance");
			return;
		}
		rawDb.exec("PRAGMA wal_checkpoint(PASSIVE)");
		rawDb.exec("PRAGMA optimize");
		rawDb.exec("PRAGMA incremental_vacuum(128)");

		const state = readDatabaseMaintenanceState(path);
		const lastFullVacuumAt =
			typeof state.lastFullVacuumAt === "string"
				? Date.parse(state.lastFullVacuumAt)
				: 0;
		if (
			!Number.isFinite(lastFullVacuumAt) ||
			Date.now() - lastFullVacuumAt >= DB_FULL_VACUUM_MIN_INTERVAL_MS
		) {
			rawDb.exec("VACUUM");
			writeDatabaseMaintenanceState(path, {
				...state,
				lastFullVacuumAt: new Date().toISOString(),
			});
		}
	} catch (err) {
		logWarning("sf-db", `database maintenance failed: ${getErrorMessage(err)}`);
	}
}
export function hasPlanningPayload(planning = {}) {
	return (
		Boolean(planning.vision) ||
		(planning.successCriteria?.length ?? 0) > 0 ||
		(planning.keyRisks?.length ?? 0) > 0 ||
		(planning.proofStrategy?.length ?? 0) > 0 ||
		Boolean(planning.verificationContract) ||
		Boolean(planning.verificationIntegration) ||
		Boolean(planning.verificationOperational) ||
		Boolean(planning.verificationUat) ||
		(planning.definitionOfDone?.length ?? 0) > 0 ||
		Boolean(planning.requirementCoverage) ||
		Boolean(planning.boundaryMapMarkdown) ||
		Boolean(planning.visionMeeting) ||
		Boolean(planning.productResearch)
	);
}
export function parseJsonOrFallback(raw, fallback) {
	if (typeof raw !== "string" || raw.trim().length === 0) return fallback;
	try {
		return JSON.parse(raw);
	} catch {
		return fallback;
	}
}
export function isEmptyMilestoneSpec(row) {
	if (!row) return true;
	return (
		(row["vision"] ?? "") === "" &&
		parseJsonOrFallback(row["success_criteria"], []).length === 0 &&
		parseJsonOrFallback(row["key_risks"], []).length === 0 &&
		parseJsonOrFallback(row["proof_strategy"], []).length === 0 &&
		(row["verification_contract"] ?? "") === "" &&
		(row["verification_integration"] ?? "") === "" &&
		(row["verification_operational"] ?? "") === "" &&
		(row["verification_uat"] ?? "") === "" &&
		parseJsonOrFallback(row["definition_of_done"], []).length === 0 &&
		(row["requirement_coverage"] ?? "") === "" &&
		(row["boundary_map_markdown"] ?? "") === "" &&
		(row["vision_meeting_json"] ?? "") === "" &&
		(row["product_research_json"] ?? "") === ""
	);
}
let currentDb = null;
let currentPath = null;
let currentPid = 0;
let _exitHandlerRegistered = false;
let _dbOpenAttempted = false;
/**
 * Get the name of the SQLite provider currently loaded (or null if unavailable).
 */
export function getDbProvider() {
	loadProvider();
	return "node:sqlite";
}
/**
 * Check if the database is currently open and available for queries.
 */
export function isDbAvailable() {
	return currentDb !== null;
}
/**
 * Returns true if openDatabase() has been called at least once this session.
 * Used to distinguish "DB not yet initialized" from "DB genuinely unavailable"
 * so that early callers (e.g. before_agent_start context injection) don't
 * trigger a false degraded-mode warning.
 */
export function wasDbOpenAttempted() {
	return _dbOpenAttempted;
}
/**
 * Get the current database adapter, or null if the database is not open.
 */
export function getDatabase() {
	return currentDb;
}
/**
 * Open the database at the specified path. Returns true if successful.
 */
export function openDatabase(path) {
	_dbOpenAttempted = true;
	if (currentDb && currentPath !== path) closeDatabase();
	if (currentDb && currentPath === path) return true;
	const rawDb = openRawDb(path);
	if (!rawDb) return false;
	const adapter = createAdapter(rawDb);
	const fileBacked = path !== ":memory:";
	try {
		initSchema(adapter, fileBacked, { currentPath: path, withQueryTimeout });
		createDatabaseSnapshot(rawDb, path);
		performDatabaseMaintenance(rawDb, path);
	} catch (err) {
		// Corrupt freelist: DDL fails with "malformed" but VACUUM can rebuild.
		// Attempt VACUUM recovery before giving up (see #2519).
		if (
			fileBacked &&
			err instanceof Error &&
			err.message?.includes("malformed")
		) {
			try {
				adapter.exec("VACUUM");
				initSchema(adapter, fileBacked, {
					currentPath: path,
					withQueryTimeout,
				});
				process.stderr.write("sf-db: recovered corrupt database via VACUUM\n");
			} catch (retryErr) {
				try {
					adapter.close();
				} catch (e) {
					logWarning("db", `close after VACUUM failed: ${e.message}`);
				}
				throw retryErr;
			}
		} else {
			try {
				adapter.close();
			} catch (e) {
				logWarning("db", `close after VACUUM failed: ${e.message}`);
			}
			throw err;
		}
	}
	currentDb = adapter;
	currentPath = path;
	currentPid = process.pid;
	if (!_exitHandlerRegistered) {
		_exitHandlerRegistered = true;
		process.on("exit", () => {
			try {
				closeDatabase();
			} catch (e) {
				logWarning("db", `exit handler close failed: ${e.message}`);
			}
		});
	}
	return true;
}
/**
 * Flush the WAL to the main DB file using a PASSIVE checkpoint.
 *
 * Purpose: safely persist all committed transactions to the main DB file at
 * controlled loop boundaries (post-unit finalize). With wal_autocheckpoint=0,
 * this is the only way WAL pages are flushed — keeping the checkpoint window
 * predictable and crash-safe (no mid-operation checkpoint that an OOM kill
 * could interrupt).
 *
 * PASSIVE is used (not TRUNCATE) so concurrent readers are not blocked. The
 * WAL is truncated on close via closeDatabase().
 *
 * Consumer: runFinalize() in auto/phases.js after each successful unit.
 */
export function checkpointWal() {
	if (!currentDb) return;
	try {
		currentDb.exec("PRAGMA wal_checkpoint(PASSIVE)");
	} catch (e) {
		logWarning("db", `WAL checkpoint failed: ${getErrorMessage(e)}`);
	}
}

/**
 * Close the database connection.
 */
export function closeDatabase() {
	if (currentDb) {
		try {
			currentDb.exec("PRAGMA wal_checkpoint(TRUNCATE)");
		} catch (e) {
			logWarning("db", `WAL checkpoint failed: ${e.message}`);
		}
		try {
			// Incremental vacuum to reclaim space without blocking
			currentDb.exec("PRAGMA incremental_vacuum(64)");
		} catch (e) {
			logWarning("db", `incremental vacuum failed: ${e.message}`);
		}
		try {
			currentDb.close();
		} catch (e) {
			logWarning("db", `database close failed: ${e.message}`);
		}
		currentDb = null;
		currentPath = null;
		currentPid = 0;
		_dbOpenAttempted = false;
	}
}
/** Run a full VACUUM — call sparingly (e.g. after milestone completion). */
/**
 * Vacuum the database to reclaim disk space and optimize.
 */
export function vacuumDatabase() {
	if (!currentDb) return;
	try {
		currentDb.exec("VACUUM");
	} catch (e) {
		logWarning("db", `VACUUM failed: ${e.message}`);
	}
}
let _txDepth = 0;
/**
 * Execute a callback within a database transaction (BEGIN...COMMIT or ROLLBACK).
 */
export function transaction(fn) {
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	// Re-entrant: if already inside a transaction, just run fn() without
	// starting a new one. SQLite does not support nested BEGIN/COMMIT.
	if (_txDepth > 0) {
		_txDepth++;
		try {
			return fn();
		} finally {
			_txDepth--;
		}
	}
	_txDepth++;
	currentDb.exec("BEGIN");
	try {
		const result = fn();
		currentDb.exec("COMMIT");
		return result;
	} catch (err) {
		currentDb.exec("ROLLBACK");
		throw err;
	} finally {
		_txDepth--;
	}
}
/**
 * Wrap a block of reads in a DEFERRED transaction so that all SELECTs observe
 * a consistent snapshot of the DB even if a concurrent writer commits between
 * them. Use this for multi-query read flows (e.g. tool executors that query
 * milestone + slices + counts and want one snapshot). Re-entrant — if already
 * inside a transaction, runs fn() without starting a nested one.
 */
/**
 * Execute a callback within a read-only database transaction.
 */
export function readTransaction(fn) {
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	if (_txDepth > 0) {
		_txDepth++;
		try {
			return fn();
		} finally {
			_txDepth--;
		}
	}
	_txDepth++;
	currentDb.exec("BEGIN DEFERRED");
	try {
		const result = fn();
		currentDb.exec("COMMIT");
		return result;
	} catch (err) {
		try {
			currentDb.exec("ROLLBACK");
		} catch (rollbackErr) {
			// A failed ROLLBACK after a failed read is a split-brain signal —
			// the transaction is in an indeterminate state. Surface it via the
			// logger instead of swallowing it.
			logError("db", "snapshotState ROLLBACK failed", {
				error: rollbackErr.message,
			});
		}
		throw err;
	} finally {
		_txDepth--;
	}
}
export function getDbOwnerPid() {
	return currentPid;
}
export function getDbPath() {
	return currentPath;
}

export function _getAdapter() {
	return currentDb;
}
export function _resetProvider() {
	loadAttempted = false;
}

export function insertMilestoneSpecIfAbsent(milestoneId, planning = {}) {
	if (!hasPlanningPayload(planning)) return;
	const existing = currentDb
		.prepare("SELECT * FROM milestone_specs WHERE id = ?")
		.get(milestoneId);
	if (existing && !isEmptyMilestoneSpec(existing)) return;
	const params = {
		":id": milestoneId,
		":vision": planning.vision ?? "",
		":success_criteria": JSON.stringify(planning.successCriteria ?? []),
		":key_risks": JSON.stringify(planning.keyRisks ?? []),
		":proof_strategy": JSON.stringify(planning.proofStrategy ?? []),
		":verification_contract": planning.verificationContract ?? "",
		":verification_integration": planning.verificationIntegration ?? "",
		":verification_operational": planning.verificationOperational ?? "",
		":verification_uat": planning.verificationUat ?? "",
		":definition_of_done": JSON.stringify(planning.definitionOfDone ?? []),
		":requirement_coverage": planning.requirementCoverage ?? "",
		":boundary_map_markdown": planning.boundaryMapMarkdown ?? "",
		":vision_meeting_json": planning.visionMeeting
			? JSON.stringify(planning.visionMeeting)
			: "",
		":product_research_json": planning.productResearch
			? JSON.stringify(planning.productResearch)
			: "",
		":created_at": new Date().toISOString(),
	};
	if (existing) {
		const { ":created_at": _createdAt, ...updateParams } = params;
		currentDb
			.prepare(`UPDATE milestone_specs SET
	      vision = :vision,
	      success_criteria = :success_criteria,
	      key_risks = :key_risks,
	      proof_strategy = :proof_strategy,
	      verification_contract = :verification_contract,
	      verification_integration = :verification_integration,
	      verification_operational = :verification_operational,
	      verification_uat = :verification_uat,
	      definition_of_done = :definition_of_done,
	      requirement_coverage = :requirement_coverage,
	      boundary_map_markdown = :boundary_map_markdown,
	      vision_meeting_json = :vision_meeting_json,
	      product_research_json = :product_research_json
	     WHERE id = :id`)
			.run(updateParams);
		return;
	}
	currentDb
		.prepare(`INSERT OR IGNORE INTO milestone_specs (
      id, vision, success_criteria, key_risks, proof_strategy,
      verification_contract, verification_integration, verification_operational, verification_uat,
      definition_of_done, requirement_coverage, boundary_map_markdown, vision_meeting_json, product_research_json,
      spec_version, created_at
    ) VALUES (
      :id, :vision, :success_criteria, :key_risks, :proof_strategy,
      :verification_contract, :verification_integration, :verification_operational, :verification_uat,
      :definition_of_done, :requirement_coverage, :boundary_map_markdown, :vision_meeting_json, :product_research_json,
      1, :created_at
    )`)
		.run(params);
}

export function insertSliceSpecIfAbsent(milestoneId, sliceId, planning = {}) {
	currentDb
		.prepare(`INSERT OR IGNORE INTO slice_specs (
      milestone_id, slice_id, goal, success_criteria, proof_level,
      integration_closure, observability_impact,
      adversarial_partner, adversarial_combatant, adversarial_architect,
      planning_meeting_json, spec_version, created_at
    ) VALUES (
      :milestone_id, :slice_id, :goal, :success_criteria, :proof_level,
      :integration_closure, :observability_impact,
      :adversarial_partner, :adversarial_combatant, :adversarial_architect,
      :planning_meeting_json, 1, :created_at
    )`)
		.run({
			":milestone_id": milestoneId,
			":slice_id": sliceId,
			":goal": planning.goal ?? "",
			":success_criteria": planning.successCriteria ?? "",
			":proof_level": planning.proofLevel ?? "",
			":integration_closure": planning.integrationClosure ?? "",
			":observability_impact": planning.observabilityImpact ?? "",
			":adversarial_partner": planning.adversarialReview?.partner ?? "",
			":adversarial_combatant": planning.adversarialReview?.combatant ?? "",
			":adversarial_architect": planning.adversarialReview?.architect ?? "",
			":planning_meeting_json": planning.planningMeeting
				? JSON.stringify(planning.planningMeeting)
				: "",
			":created_at": new Date().toISOString(),
		});
}

export function hasTaskSpecIntent(planning = {}) {
	if (!planning || typeof planning !== "object") return false;
	if (typeof planning.verify === "string" && planning.verify.trim())
		return true;
	if (Array.isArray(planning.inputs) && planning.inputs.length > 0) return true;
	if (
		Array.isArray(planning.expectedOutput) &&
		planning.expectedOutput.length > 0
	) {
		return true;
	}
	for (const key of [
		"risk",
		"mutationScope",
		"mutation_scope",
		"verification",
		"verificationType",
		"verification_type",
		"planApproval",
		"plan_approval",
		"estimatedEffort",
		"estimated_effort",
		"dependencies",
		"blocksParallel",
		"blocks_parallel",
		"requiresUserInput",
		"requires_user_input",
		"autoRetry",
		"auto_retry",
		"maxRetries",
		"max_retries",
	]) {
		if (planning[key] !== undefined) return true;
	}
	return false;
}

export function insertTaskSpecIfAbsent(
	milestoneId,
	sliceId,
	taskId,
	planning = {},
) {
	if (!hasTaskSpecIntent(planning)) return;
	const { normalized: frontmatter, errors } =
		taskFrontmatterFromRecord(planning);
	if (errors?.length)
		logWarning(
			"sf-db:insertTaskSpec",
			`frontmatter validation errors for ${milestoneId}/${sliceId}/${taskId}: ${errors.join(", ")}`,
		);
	currentDb
		.prepare(`INSERT OR IGNORE INTO task_specs (
      milestone_id, slice_id, task_id, verify, inputs, expected_output,
      risk, mutation_scope, verification_type, plan_approval, estimated_effort,
      dependencies, blocks_parallel, requires_user_input, auto_retry, max_retries,
      spec_version, created_at
    ) VALUES (
      :milestone_id, :slice_id, :task_id, :verify, :inputs, :expected_output,
      :risk, :mutation_scope, :verification_type, :plan_approval, :estimated_effort,
      :dependencies, :blocks_parallel, :requires_user_input, :auto_retry, :max_retries,
      1, :created_at
    )`)
		.run({
			":milestone_id": milestoneId,
			":slice_id": sliceId,
			":task_id": taskId,
			":verify": planning.verify ?? "",
			":inputs": JSON.stringify(planning.inputs ?? []),
			":expected_output": JSON.stringify(planning.expectedOutput ?? []),
			":risk": frontmatter.risk,
			":mutation_scope": frontmatter.mutationScope,
			":verification_type": frontmatter.verification,
			":plan_approval": frontmatter.planApproval,
			":estimated_effort": frontmatter.estimatedEffort,
			":dependencies": JSON.stringify(frontmatter.dependencies),
			":blocks_parallel": frontmatter.blocksParallel ? 1 : 0,
			":requires_user_input": frontmatter.requiresUserInput ? 1 : 0,
			":auto_retry": frontmatter.autoRetry ? 1 : 0,
			":max_retries": frontmatter.maxRetries,
			":created_at": new Date().toISOString(),
		});
}

export function insertTaskSchedulerIfAbsent(milestoneId, sliceId, taskId) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT OR IGNORE INTO task_scheduler (
        milestone_id, slice_id, task_id, status, updated_at
      ) VALUES (
        :milestone_id, :slice_id, :task_id, :status, :updated_at
      )`)
		.run({
			":milestone_id": milestoneId,
			":slice_id": sliceId,
			":task_id": taskId,
			":status": normalizeSchedulerStatus("queued") ?? "queued",
			":updated_at": new Date().toISOString(),
		});
}

export function parsePlanningMeeting(raw) {
	if (typeof raw !== "string" || raw.trim() === "") return null;
	try {
		return JSON.parse(raw);
	} catch {
		return null;
	}
}

export function rowToSlice(row) {
	return {
		milestone_id: row["milestone_id"],
		id: row["id"],
		title: row["title"],
		status: row["status"],
		risk: row["risk"],
		depends: safeParseJsonArray(row["depends"]),
		demo: row["demo"] ?? "",
		created_at: row["created_at"],
		completed_at: row["completed_at"] ?? null,
		full_summary_md: row["full_summary_md"] ?? "",
		full_uat_md: row["full_uat_md"] ?? "",
		goal: row["goal"] ?? "",
		success_criteria: row["success_criteria"] ?? "",
		proof_level: row["proof_level"] ?? "",
		integration_closure: row["integration_closure"] ?? "",
		observability_impact: row["observability_impact"] ?? "",
		adversarial_partner: row["adversarial_partner"] ?? "",
		adversarial_combatant: row["adversarial_combatant"] ?? "",
		adversarial_architect: row["adversarial_architect"] ?? "",
		planning_meeting: parsePlanningMeeting(row["planning_meeting_json"]),
		sequence: row["sequence"] ?? 0,
		replan_triggered_at: row["replan_triggered_at"] ?? null,
		sketch_scope: row["sketch_scope"] ?? "",
		is_sketch: row["is_sketch"] ?? 0,
		// ADR-0000 P2 (schema v69): literal milestone.vision clause this slice
		// serves. NULL for legacy rows planned before v69; required for new slices.
		traces_vision_fragment: row["traces_vision_fragment"] ?? null,
	};
}

export function safeParseJsonArray(raw, fallback = []) {
	if (typeof raw !== "string" || raw.trim() === "") return fallback;
	try {
		const parsed = JSON.parse(raw);
		return Array.isArray(parsed) ? parsed : fallback;
	} catch {
		return fallback;
	}
}

export function parseTaskArrayColumn(raw) {
	if (typeof raw !== "string" || raw.trim() === "") return [];
	try {
		const parsed = JSON.parse(raw);
		if (Array.isArray(parsed)) return parsed.map((value) => String(value));
		if (parsed === null || parsed === undefined || parsed === "") return [];
		return [String(parsed)];
	} catch {
		// Older/corrupt rows may contain comma-separated strings instead of JSON.
		return raw
			.split(",")
			.map((value) => value.trim())
			.filter(Boolean);
	}
}

export function rowToTask(row) {
	const parseTaskArray = (value) => {
		if (Array.isArray(value)) {
			return value.filter((entry) => typeof entry === "string");
		}
		if (typeof value !== "string") return [];
		const trimmed = value.trim();
		if (!trimmed) return [];
		try {
			const parsed = JSON.parse(trimmed);
			if (Array.isArray(parsed)) {
				return parsed.filter((entry) => typeof entry === "string");
			}
			if (typeof parsed === "string" && parsed.trim()) {
				return [parsed.trim()];
			}
		} catch {
			// Older/corrupt DB rows may contain raw comma-separated paths instead of JSON arrays.
		}
		return trimmed
			.split(",")
			.map((entry) => entry.trim())
			.filter(Boolean);
	};
	return withTaskFrontmatter({
		milestone_id: row["milestone_id"],
		slice_id: row["slice_id"],
		id: row["id"],
		title: row["title"],
		status: row["status"],
		one_liner: row["one_liner"],
		narrative: row["narrative"],
		verification_result: row["verification_result"],
		duration: row["duration"],
		completed_at: row["completed_at"] ?? null,
		blocker_discovered: row["blocker_discovered"] === 1,
		deviations: row["deviations"],
		known_issues: row["known_issues"],
		key_files: parseTaskArrayColumn(row["key_files"]),
		key_decisions: parseTaskArrayColumn(row["key_decisions"]),
		full_summary_md: row["full_summary_md"],
		description: row["description"] ?? "",
		estimate: row["estimate"] ?? "",
		files: parseTaskArray(row["files"]),
		verify: row["verify"] ?? "",
		inputs: parseTaskArray(row["inputs"]),
		expected_output: parseTaskArray(row["expected_output"]),
		observability_impact: row["observability_impact"] ?? "",
		full_plan_md: row["full_plan_md"] ?? "",
		sequence: row["sequence"] ?? 0,
		verification_status: row["verification_status"] ?? "",
		risk: row["risk"] ?? "low",
		mutation_scope: row["mutation_scope"] ?? "isolated",
		verification_type: row["verification_type"] ?? "self-check",
		plan_approval: row["plan_approval"] ?? "not-required",
		task_status: row["task_status"] ?? row["status"] ?? "todo",
		scheduler_status: row["scheduler_status"] ?? "queued",
		estimated_effort: row["estimated_effort"] ?? null,
		dependencies: parseTaskArray(row["dependencies"]),
		blocks_parallel: row["blocks_parallel"] ?? 0,
		requires_user_input: row["requires_user_input"] ?? 0,
		auto_retry: row["auto_retry"] ?? 1,
		max_retries: row["max_retries"] ?? 2,
		escalation_pending: row["escalation_pending"] ?? 0,
		escalation_awaiting_review: row["escalation_awaiting_review"] ?? 0,
		escalation_override_applied: row["escalation_override_applied"] ?? 0,
		escalation_artifact_path: row["escalation_artifact_path"] ?? null,
		// ADR-0000 (SF v70): goal-anchored purpose sentence the executor named
		// when finishing the task. NULL on legacy rows; complete-slice refuses
		// to close while any row in its slice is still NULL.
		purpose_trace: row["purpose_trace"] ?? null,
	});
}

export function rowToSelfFeedback(row) {
	// purpose_anchor (v71, ADR-0000): expose the structured column verbatim,
	// falling back to the JSON projection so reads from legacy rows (where the
	// column is NULL but the anchor lives inside full_json) still surface it.
	const columnPurposeAnchor =
		typeof row["purpose_anchor"] === "string" && row["purpose_anchor"] !== ""
			? row["purpose_anchor"]
			: null;
	try {
		const parsed = JSON.parse(row["full_json"]);
		return {
			...parsed,
			resolvedAt: row["resolved_at"] ?? parsed.resolvedAt,
			resolvedReason: row["resolved_reason"] ?? parsed.resolvedReason,
			resolvedBySfVersion:
				row["resolved_by_sf_version"] ?? parsed.resolvedBySfVersion,
			resolvedEvidence: row["resolved_evidence_json"]
				? JSON.parse(row["resolved_evidence_json"])
				: parsed.resolvedEvidence,
			resolvedCriteriaMet: row["resolved_criteria_json"]
				? JSON.parse(row["resolved_criteria_json"])
				: parsed.resolvedCriteriaMet,
			impactScore:
				typeof row["impact_score"] === "number"
					? row["impact_score"]
					: (parsed.impactScore ?? null),
			effortEstimate:
				typeof row["effort_estimate"] === "number"
					? row["effort_estimate"]
					: (parsed.effortEstimate ?? null),
			purposeAnchor: columnPurposeAnchor ?? parsed.purposeAnchor ?? null,
		};
	} catch {
		return {
			id: row["id"],
			ts: row["ts"],
			kind: row["kind"],
			severity: row["severity"],
			blocking: row["blocking"] === 1,
			repoIdentity: row["repo_identity"],
			sfVersion: row["sf_version"],
			basePath: row["base_path"],
			occurredIn: {
				unitType: row["unit_type"] ?? undefined,
				milestone: row["milestone_id"] ?? undefined,
				slice: row["slice_id"] ?? undefined,
				task: row["task_id"] ?? undefined,
			},
			summary: row["summary"],
			evidence: row["evidence"],
			suggestedFix: row["suggested_fix"],
			resolvedAt: row["resolved_at"] ?? undefined,
			resolvedReason: row["resolved_reason"] ?? undefined,
			resolvedBySfVersion: row["resolved_by_sf_version"] ?? undefined,
			resolvedEvidence: row["resolved_evidence_json"]
				? JSON.parse(row["resolved_evidence_json"])
				: undefined,
			resolvedCriteriaMet: row["resolved_criteria_json"]
				? JSON.parse(row["resolved_criteria_json"])
				: undefined,
			impactScore:
				typeof row["impact_score"] === "number"
					? row["impact_score"]
					: undefined,
			effortEstimate:
				typeof row["effort_estimate"] === "number"
					? row["effort_estimate"]
					: undefined,
			purposeAnchor: columnPurposeAnchor ?? undefined,
		};
	}
}

export function parseVisionMeeting(raw) {
	if (typeof raw !== "string" || raw.trim().length === 0) return null;
	try {
		return JSON.parse(raw);
	} catch {
		return null;
	}
}

export function parseProductResearch(raw) {
	if (typeof raw !== "string" || raw.trim().length === 0) return null;
	try {
		return JSON.parse(raw);
	} catch {
		return null;
	}
}

export function rowToMilestone(row) {
	return {
		id: row["id"],
		title: row["title"],
		status: row["status"],
		depends_on: safeParseJsonArray(row["depends_on"]),
		created_at: row["created_at"],
		completed_at: row["completed_at"] ?? null,
		vision: row["vision"] ?? "",
		success_criteria: safeParseJsonArray(row["success_criteria"]),
		key_risks: safeParseJsonArray(row["key_risks"]),
		proof_strategy: safeParseJsonArray(row["proof_strategy"]),
		verification_contract: row["verification_contract"] ?? "",
		verification_integration: row["verification_integration"] ?? "",
		verification_operational: row["verification_operational"] ?? "",
		verification_uat: row["verification_uat"] ?? "",
		definition_of_done: safeParseJsonArray(row["definition_of_done"]),
		requirement_coverage: row["requirement_coverage"] ?? "",
		boundary_map_markdown: row["boundary_map_markdown"] ?? "",
		vision_meeting: parseVisionMeeting(row["vision_meeting_json"]),
		product_research: parseProductResearch(row["product_research_json"]),
		sequence: row["sequence"] ?? 0,
	};
}

export function rowToArtifact(row) {
	return {
		path: row["path"],
		artifact_type: row["artifact_type"],
		milestone_id: row["milestone_id"] ?? null,
		slice_id: row["slice_id"] ?? null,
		task_id: row["task_id"] ?? null,
		full_content: row["full_content"],
		imported_at: row["imported_at"],
	};
}

export function rowToBacklogItem(row) {
	return {
		id: row["id"],
		title: row["title"],
		status: row["status"],
		note: row["note"] ?? "",
		source: row["source"] ?? "",
		triageRunId: row["triage_run_id"] ?? null,
		sequence: row["sequence"] ?? 0,
		createdAt: row["created_at"],
		updatedAt: row["updated_at"],
		promotedAt: row["promoted_at"] ?? null,
	};
}

export function rowToGate(row) {
	return {
		milestone_id: row["milestone_id"],
		slice_id: row["slice_id"],
		gate_id: row["gate_id"],
		scope: row["scope"],
		task_id: row["task_id"] ?? "",
		status: row["status"],
		verdict: row["verdict"] || "",
		rationale: row["rationale"] || "",
		findings: row["findings"] || "",
		evaluated_at: row["evaluated_at"] ?? null,
	};
}

export function capErrorForStorage(error, runId) {
	if (!error || error.length <= MAX_ERROR_STORED_BYTES) return error;
	try {
		const errDir = join(dirname(currentPath), "runtime", "errors");
		mkdirSync(errDir, { recursive: true });
		writeFileSync(join(errDir, `${runId}.txt`), error, "utf-8");
	} catch {
		// non-fatal — best-effort spill
	}
	const head = error.slice(0, 2048);
	const tail = error.slice(-2048);
	const dropped = error.length - MAX_ERROR_STORED_BYTES;
	return `${head}\n\n[...${dropped} chars truncated — full error in .sf/runtime/errors/${runId}.txt]\n\n${tail}`;
}

export function boolToInt(value) {
	if (value === null || value === undefined) return null;
	return value ? 1 : 0;
}

export function normalizeScheduleScope(scope) {
	return scope === "global" ? "global" : "project";
}

export function scheduleEntryFromRow(row) {
	if (!row) return null;
	const full = parseJsonObject(row.full_json, {});
	return {
		...full,
		schemaVersion: row.schema_version ?? full.schemaVersion ?? 1,
		id: row.id,
		kind: row.kind,
		status: row.status,
		due_at: row.due_at,
		created_at: row.created_at,
		snoozed_at: row.snoozed_at ?? full.snoozed_at,
		payload: parseJsonObject(row.payload_json, full.payload ?? {}),
		created_by: row.created_by,
		autonomous_dispatch: !!row.autonomous_dispatch,
	};
}

export function asStringOrNull(value) {
	return typeof value === "string" && value.length > 0 ? value : null;
}

export function intBool(value) {
	return value ? 1 : 0;
}

export function parseJsonObject(raw, fallback = {}) {
	try {
		return JSON.parse(raw);
	} catch {
		return fallback;
	}
}

export function solverEvalRunFromRow(row) {
	return {
		runId: row["run_id"],
		suiteSource: row["suite_source"],
		casesCount: row["cases_count"] ?? 0,
		summary: parseJsonObject(row["summary_json"], {}),
		reportPath: row["report_path"],
		resultsPath: row["results_path"],
		dbRecorded: row["db_recorded"] === 1,
		createdAt: row["created_at"],
		updatedAt: row["updated_at"],
	};
}

export function solverEvalCaseFromRow(row) {
	return {
		runId: row["run_id"],
		caseId: row["case_id"],
		title: row["title"],
		mode: row["mode"],
		passed: row["passed"] === 1,
		falseComplete: row["false_complete"] === 1,
		durationMs: row["duration_ms"],
		commandStatus: row["command_status"],
		solverOutcome: asStringOrNull(row["solver_outcome"]),
		pddComplete:
			row["pdd_complete"] === null || row["pdd_complete"] === undefined
				? null
				: row["pdd_complete"] === 1,
		result: parseJsonObject(row["result_json"], {}),
		createdAt: row["created_at"],
	};
}

export function headlessRunFromRow(row) {
	return {
		runId: row["run_id"],
		command: row["command"],
		status: row["status"],
		exitCode: row["exit_code"],
		timedOut: row["timed_out"] === 1,
		interrupted: row["interrupted"] === 1,
		restartCount: row["restart_count"] ?? 0,
		maxRestarts: row["max_restarts"] ?? 0,
		durationMs: row["duration_ms"] ?? 0,
		totalEvents: row["total_events"] ?? 0,
		toolCalls: row["tool_calls"] ?? 0,
		solverEvalRunId: asStringOrNull(row["solver_eval_run_id"]),
		solverEvalReportPath: asStringOrNull(row["solver_eval_report_path"]),
		details: parseJsonObject(row["details_json"], {}),
		createdAt: row["created_at"],
		updatedAt: row["updated_at"],
	};
}

export function rowToUnitMetrics(row) {
	const unit = {
		type: row["type"],
		id: row["id"],
		model: row["model"],
		startedAt: row["started_at"],
		finishedAt: row["finished_at"],
		tokens: {
			input: row["tokens_input"],
			output: row["tokens_output"],
			cacheRead: row["tokens_cache_read"],
			cacheWrite: row["tokens_cache_write"],
			total: row["tokens_total"],
		},
		cost: row["cost"],
		toolCalls: row["tool_calls"],
		assistantMessages: row["assistant_messages"],
		userMessages: row["user_messages"],
		apiRequests: row["api_requests"],
	};
	if (row["auto_session_key"] != null)
		unit.autoSessionKey = row["auto_session_key"];
	if (row["tier"] != null) unit.tier = row["tier"];
	if (row["model_downgraded"] != null)
		unit.modelDowngraded = row["model_downgraded"] === 1;
	if (row["context_window_tokens"] != null)
		unit.contextWindowTokens = row["context_window_tokens"];
	if (row["truncation_sections"] != null)
		unit.truncationSections = row["truncation_sections"];
	if (row["continue_here_fired"] != null)
		unit.continueHereFired = row["continue_here_fired"] === 1;
	if (row["prompt_char_count"] != null)
		unit.promptCharCount = row["prompt_char_count"];
	if (row["baseline_char_count"] != null)
		unit.baselineCharCount = row["baseline_char_count"];
	if (row["cache_hit_rate"] != null) unit.cacheHitRate = row["cache_hit_rate"];
	if (row["skills"] != null) unit.skills = JSON.parse(row["skills"]);
	return unit;
}
