import { existsSync, mkdirSync, readFileSync } from "node:fs";
import { join } from "node:path";
import { atomicWriteSync } from "./atomic-write.js";
import {
	_getAdapter,
	type MilestoneRow,
	readTransaction,
	restoreManifest,
	type SliceRow,
	type TaskRow,
} from "./sf-db.js";
import type { Decision } from "./types.js";

// ─── Manifest Types ──────────────────────────────────────────────────────

/**
 * Verification evidence row from the database.
 * Records a command execution, exit code, verdict, and duration.
 */
export interface VerificationEvidenceRow {
	id: number;
	task_id: string;
	slice_id: string;
	milestone_id: string;
	command: string;
	exit_code: number | null;
	verdict: string;
	duration_ms: number | null;
	created_at: string;
}

/**
 * Complete snapshot of workflow state exported from the database.
 * Includes milestones, slices, tasks, decisions, and verification evidence.
 */
export interface StateManifest {
	version: 1;
	exported_at: string; // ISO 8601
	milestones: MilestoneRow[];
	slices: SliceRow[];
	tasks: TaskRow[];
	decisions: Decision[];
	verification_evidence: VerificationEvidenceRow[];
}

// ─── helpers ─────────────────────────────────────────────────────────────

/**
 * Get the database adapter or throw if no database is open.
 */
function requireDb() {
	const db = _getAdapter();
	if (!db) throw new Error("workflow-manifest: No database open");
	return db;
}

/**
 * Coerce a raw DB value to a number, returning `fallback` for
 * null/undefined/non-numeric strings (e.g. "-", "N/A", "").
 * SQLite can store TEXT in INTEGER columns after migrations or manual inserts.
 */
/**
 * Coerce a database value to a number with fallback for nulls and non-numeric text.
 */
export function toNumeric(
	value: unknown,
	fallback: number | null = null,
): number | null {
	if (value === null || value === undefined) return fallback;
	if (typeof value === "number")
		return Number.isFinite(value) ? value : fallback;
	if (typeof value === "string") {
		const trimmed = value.trim();
		if (trimmed === "" || trimmed === "-" || trimmed === "N/A") return fallback;
		const n = Number(trimmed);
		return Number.isFinite(n) ? n : fallback;
	}
	return fallback;
}

/**
 * Parse a JSON TEXT column that is expected to be an array of strings.
 * Falls back to `[]` if the column is absent, empty, or if the parsed value
 * is not an array whose every element is a string.  This prevents malformed
 * but technically-parseable JSON (e.g. escaped-quote corruption) from
 * silently producing a wrong-typed value downstream.
 */
function parseStringArray(raw: unknown): string[] {
	if (typeof raw !== "string" || raw.trim() === "") return [];
	try {
		const parsed = JSON.parse(raw);
		if (
			Array.isArray(parsed) &&
			parsed.every((item) => typeof item === "string")
		) {
			return parsed as string[];
		}
		// Parseable but wrong shape — fall back to empty
		return [];
	} catch {
		return [];
	}
}

/**
 * Parse a TEXT column expected to hold a JSON array of plain objects.
 * Returns [] for missing, malformed, or wrong-shaped input.
 *
 * Used for structured columns like `milestones.key_risks` (Array<{risk, whyItMatters}>)
 * and `milestones.proof_strategy` (Array<{riskOrUnknown, retireIn, whatWillBeProven}>).
 * The exact object shape isn't validated here — that's the caller's job. We
 * only assert it's an array of objects.
 */
function parseObjectArray<T>(raw: unknown): T[] {
	if (typeof raw !== "string" || raw.trim() === "") return [];
	try {
		const parsed = JSON.parse(raw);
		if (
			Array.isArray(parsed) &&
			parsed.every((item) => item !== null && typeof item === "object")
		) {
			return parsed as T[];
		}
		return [];
	} catch {
		return [];
	}
}

// ─── snapshotState ───────────────────────────────────────────────────────

/**
 * Capture complete DB state as a StateManifest.
 * Reads all rows from milestones, slices, tasks, decisions, verification_evidence.
 *
 * Note: rows returned from raw queries are plain objects with TEXT columns for
 * JSON arrays. We parse them into typed Row objects using the same logic as
 * sf-db helper functions.
 */
export function snapshotState(): StateManifest {
	const db = requireDb();

	// Wrap all reads in a deferred transaction so the snapshot is consistent
	// (all SELECTs see the same DB state even if a concurrent write lands between them).
	return readTransaction(() => {
		const rawMilestones = db
			.prepare("SELECT * FROM milestones ORDER BY id")
			.all() as Record<string, unknown>[];
		const milestones: MilestoneRow[] = rawMilestones.map((r) => ({
			id: r["id"] as string,
			title: r["title"] as string,
			status: r["status"] as string,
			depends_on: parseStringArray(r["depends_on"]),
			created_at: r["created_at"] as string,
			completed_at: (r["completed_at"] as string) ?? null,
			vision: (r["vision"] as string) ?? "",
			success_criteria: parseStringArray(r["success_criteria"]),
			key_risks: parseObjectArray<{ risk: string; whyItMatters: string }>(r["key_risks"]),
			proof_strategy: parseObjectArray<{ riskOrUnknown: string; retireIn: string; whatWillBeProven: string }>(r["proof_strategy"]),
			verification_contract: (r["verification_contract"] as string) ?? "",
			verification_integration: (r["verification_integration"] as string) ?? "",
			verification_operational: (r["verification_operational"] as string) ?? "",
			verification_uat: (r["verification_uat"] as string) ?? "",
			definition_of_done: parseStringArray(r["definition_of_done"]),
			requirement_coverage: (r["requirement_coverage"] as string) ?? "",
			boundary_map_markdown: (r["boundary_map_markdown"] as string) ?? "",
			vision_meeting:
				typeof r["vision_meeting_json"] === "string" &&
				(r["vision_meeting_json"] as string).trim().length > 0
					? JSON.parse(r["vision_meeting_json"] as string)
					: null,
		}));

		const rawSlices = db
			.prepare("SELECT * FROM slices ORDER BY milestone_id, sequence, id")
			.all() as Record<string, unknown>[];
		const slices: SliceRow[] = rawSlices.map((r) => ({
			milestone_id: r["milestone_id"] as string,
			id: r["id"] as string,
			title: r["title"] as string,
			status: r["status"] as string,
			risk: r["risk"] as string,
			depends: parseStringArray(r["depends"]),
			demo: (r["demo"] as string) ?? "",
			created_at: r["created_at"] as string,
			completed_at: (r["completed_at"] as string) ?? null,
			full_summary_md: (r["full_summary_md"] as string) ?? "",
			full_uat_md: (r["full_uat_md"] as string) ?? "",
			goal: (r["goal"] as string) ?? "",
			success_criteria: (r["success_criteria"] as string) ?? "",
			proof_level: (r["proof_level"] as string) ?? "",
			integration_closure: (r["integration_closure"] as string) ?? "",
			observability_impact: (r["observability_impact"] as string) ?? "",
			adversarial_partner: (r["adversarial_partner"] as string) ?? "",
			adversarial_combatant: (r["adversarial_combatant"] as string) ?? "",
			adversarial_architect: (r["adversarial_architect"] as string) ?? "",
			planning_meeting:
				typeof r["planning_meeting_json"] === "string" &&
				(r["planning_meeting_json"] as string).trim().length > 0
					? JSON.parse(r["planning_meeting_json"] as string)
					: null,
			sequence: toNumeric(r["sequence"], 0) as number,
			replan_triggered_at: (r["replan_triggered_at"] as string) ?? null,
		}));

		const rawTasks = db
			.prepare(
				"SELECT * FROM tasks ORDER BY milestone_id, slice_id, sequence, id",
			)
			.all() as Record<string, unknown>[];
		const tasks: TaskRow[] = rawTasks.map((r) => ({
			milestone_id: r["milestone_id"] as string,
			slice_id: r["slice_id"] as string,
			id: r["id"] as string,
			title: r["title"] as string,
			status: r["status"] as string,
			one_liner: (r["one_liner"] as string) ?? "",
			narrative: (r["narrative"] as string) ?? "",
			verification_result: (r["verification_result"] as string) ?? "",
			duration: (r["duration"] as string) ?? "",
			completed_at: (r["completed_at"] as string) ?? null,
			blocker_discovered: (r["blocker_discovered"] as number) === 1,
			deviations: (r["deviations"] as string) ?? "",
			known_issues: (r["known_issues"] as string) ?? "",
			key_files: parseStringArray(r["key_files"]),
			key_decisions: parseStringArray(r["key_decisions"]),
			full_summary_md: (r["full_summary_md"] as string) ?? "",
			description: (r["description"] as string) ?? "",
			estimate: (r["estimate"] as string) ?? "",
			files: parseStringArray(r["files"]),
			verify: (r["verify"] as string) ?? "",
			inputs: parseStringArray(r["inputs"]),
			expected_output: parseStringArray(r["expected_output"]),
			observability_impact: (r["observability_impact"] as string) ?? "",
			full_plan_md: (r["full_plan_md"] as string) ?? "",
			sequence: toNumeric(r["sequence"], 0) as number,
		}));

		const rawDecisions = db
			.prepare("SELECT * FROM decisions ORDER BY seq")
			.all() as Record<string, unknown>[];
		const decisions: Decision[] = rawDecisions.map((r) => ({
			seq: toNumeric(r["seq"], 0) as number,
			id: r["id"] as string,
			when_context: (r["when_context"] as string) ?? "",
			scope: (r["scope"] as string) ?? "",
			decision: (r["decision"] as string) ?? "",
			choice: (r["choice"] as string) ?? "",
			rationale: (r["rationale"] as string) ?? "",
			revisable: (r["revisable"] as string) ?? "",
			made_by: (r["made_by"] as string as Decision["made_by"]) ?? "agent",
			superseded_by: (r["superseded_by"] as string) ?? null,
		}));

		const rawEvidence = db
			.prepare("SELECT * FROM verification_evidence ORDER BY id")
			.all() as Record<string, unknown>[];
		const verification_evidence: VerificationEvidenceRow[] = rawEvidence.map(
			(r) => ({
				id: r["id"] as number,
				task_id: r["task_id"] as string,
				slice_id: r["slice_id"] as string,
				milestone_id: r["milestone_id"] as string,
				command: r["command"] as string,
				exit_code: toNumeric(r["exit_code"]),
				verdict: (r["verdict"] as string) ?? "",
				duration_ms: toNumeric(r["duration_ms"]),
				created_at: r["created_at"] as string,
			}),
		);

		const result: StateManifest = {
			version: 1,
			exported_at: new Date().toISOString(),
			milestones,
			slices,
			tasks,
			decisions,
			verification_evidence,
		};

		return result;
	});
}

// ─── restore ─────────────────────────────────────────────────────────────
//
// The actual restore() implementation lives in sf-db.ts (single-writer
// invariant). This module only orchestrates reading the manifest file
// and handing it to the writer.

// ─── writeManifest ───────────────────────────────────────────────────────

/**
 * Write current DB state to .sf/state-manifest.json via atomicWriteSync.
 * Uses JSON.stringify with 2-space indent for git three-way merge friendliness.
 */
export function writeManifest(basePath: string): void {
	const manifest = snapshotState();
	const json = JSON.stringify(manifest, null, 2);
	const dir = join(basePath, ".sf");
	mkdirSync(dir, { recursive: true });
	atomicWriteSync(join(dir, "state-manifest.json"), json);
}

// ─── readManifest ────────────────────────────────────────────────────────

/**
 * Read state-manifest.json and return parsed manifest, or null if not found.
 */
export function readManifest(basePath: string): StateManifest | null {
	const manifestPath = join(basePath, ".sf", "state-manifest.json");

	if (!existsSync(manifestPath)) {
		return null;
	}

	const raw = readFileSync(manifestPath, "utf-8");
	const parsed = JSON.parse(raw) as StateManifest;

	if (parsed.version !== 1) {
		throw new Error(`Unsupported manifest version: ${parsed.version}`);
	}

	// Validate required fields to avoid cryptic errors during restore
	if (
		!Array.isArray(parsed.milestones) ||
		!Array.isArray(parsed.slices) ||
		!Array.isArray(parsed.tasks) ||
		!Array.isArray(parsed.decisions) ||
		!Array.isArray(parsed.verification_evidence)
	) {
		throw new Error("Malformed manifest: missing or invalid required arrays");
	}

	return parsed;
}

// ─── bootstrapFromManifest ──────────────────────────────────────────────

/**
 * Read state-manifest.json and restore DB state from it.
 * Returns true if bootstrap succeeded, false if manifest file doesn't exist.
 */
export function bootstrapFromManifest(basePath: string): boolean {
	const manifest = readManifest(basePath);

	if (!manifest) {
		return false;
	}

	restoreManifest(manifest);
	return true;
}
