import { existsSync, readFileSync } from "node:fs";
import { SF_STALE_STATE, SFError } from "../errors.js";
import {
	_getAdapter,
	hasPlanningPayload,
	insertMilestoneSpecIfAbsent,
	isEmptyMilestoneSpec,
	parseJsonOrFallback,
	parseProductResearch,
	parseVisionMeeting,
	rowToMilestone,
	transaction,
} from "./sf-db-core.js";

export function insertMilestone(m) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT OR IGNORE INTO milestones (
      id, title, status, depends_on, created_at,
      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, sequence
    ) VALUES (
      :id, :title, :status, :depends_on, :created_at,
      :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, :sequence
    )`)
		.run({
			":id": m.id,
			":title": m.title ?? "",
			// Default to "queued" — never auto-create milestones as "active" (#3380).
			// Callers that need "active" must pass it explicitly.
			":status": m.status ?? "queued",
			":depends_on": JSON.stringify(m.depends_on ?? []),
			":created_at": new Date().toISOString(),
			":vision": m.planning?.vision ?? "",
			":success_criteria": JSON.stringify(m.planning?.successCriteria ?? []),
			":key_risks": JSON.stringify(m.planning?.keyRisks ?? []),
			":proof_strategy": JSON.stringify(m.planning?.proofStrategy ?? []),
			":verification_contract": m.planning?.verificationContract ?? "",
			":verification_integration": m.planning?.verificationIntegration ?? "",
			":verification_operational": m.planning?.verificationOperational ?? "",
			":verification_uat": m.planning?.verificationUat ?? "",
			":definition_of_done": JSON.stringify(m.planning?.definitionOfDone ?? []),
			":requirement_coverage": m.planning?.requirementCoverage ?? "",
			":boundary_map_markdown": m.planning?.boundaryMapMarkdown ?? "",
			":vision_meeting_json": m.planning?.visionMeeting
				? JSON.stringify(m.planning.visionMeeting)
				: "",
			":product_research_json": m.planning?.productResearch
				? JSON.stringify(m.planning.productResearch)
				: "",
			":sequence": m.sequence ?? 0,
		});
	if (hasPlanningPayload(m.planning)) {
		insertMilestoneSpecIfAbsent(m.id, m.planning ?? {});
	}
}

export function upsertMilestonePlanning(milestoneId, planning) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	insertMilestoneSpecIfAbsent(milestoneId, planning);
	currentDb
		.prepare(`UPDATE milestones SET
      title = COALESCE(NULLIF(:title, ''), title),
      status = COALESCE(NULLIF(:status, ''), status),
      vision = COALESCE(:vision, vision),
      success_criteria = COALESCE(:success_criteria, success_criteria),
      key_risks = COALESCE(:key_risks, key_risks),
      proof_strategy = COALESCE(:proof_strategy, proof_strategy),
      verification_contract = COALESCE(:verification_contract, verification_contract),
      verification_integration = COALESCE(:verification_integration, verification_integration),
      verification_operational = COALESCE(:verification_operational, verification_operational),
      verification_uat = COALESCE(:verification_uat, verification_uat),
      definition_of_done = COALESCE(:definition_of_done, definition_of_done),
      requirement_coverage = COALESCE(:requirement_coverage, requirement_coverage),
      boundary_map_markdown = COALESCE(:boundary_map_markdown, boundary_map_markdown),
      vision_meeting_json = COALESCE(:vision_meeting_json, vision_meeting_json),
      product_research_json = COALESCE(:product_research_json, product_research_json)
     WHERE id = :id`)
		.run({
			":id": milestoneId,
			":title": planning.title ?? "",
			":status": planning.status ?? "",
			":vision": planning.vision ?? null,
			":success_criteria": planning.successCriteria
				? JSON.stringify(planning.successCriteria)
				: null,
			":key_risks": planning.keyRisks
				? JSON.stringify(planning.keyRisks)
				: null,
			":proof_strategy": planning.proofStrategy
				? JSON.stringify(planning.proofStrategy)
				: null,
			":verification_contract": planning.verificationContract ?? null,
			":verification_integration": planning.verificationIntegration ?? null,
			":verification_operational": planning.verificationOperational ?? null,
			":verification_uat": planning.verificationUat ?? null,
			":definition_of_done": planning.definitionOfDone
				? JSON.stringify(planning.definitionOfDone)
				: null,
			":requirement_coverage": planning.requirementCoverage ?? null,
			":boundary_map_markdown": planning.boundaryMapMarkdown ?? null,
			":vision_meeting_json": planning.visionMeeting
				? JSON.stringify(planning.visionMeeting)
				: null,
			":product_research_json": planning.productResearch
				? JSON.stringify(planning.productResearch)
				: null,
		});
}

export function getAllMilestones() {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	const rows = currentDb
		.prepare(
			"SELECT * FROM milestones ORDER BY tier ASC, CASE WHEN sequence > 0 THEN 0 ELSE 1 END, sequence, id",
		)
		.all();
	return rows.map(rowToMilestone);
}

export function getMilestone(id) {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	const row = currentDb
		.prepare("SELECT * FROM milestones WHERE id = :id")
		.get({ ":id": id });
	if (!row) return null;
	return rowToMilestone(row);
}

export function updateMilestoneStatus(milestoneId, status, completedAt) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(
			`UPDATE milestones SET status = :status, completed_at = :completed_at WHERE id = :id`,
		)
		.run({
			":status": status,
			":completed_at": completedAt ?? null,
			":id": milestoneId,
		});
}

export function updateMilestoneQueueOrder(order) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	transaction(() => {
		const stmt = currentDb.prepare(
			"UPDATE milestones SET sequence = :sequence WHERE id = :id",
		);
		for (let i = 0; i < order.length; i++) {
			stmt.run({ ":sequence": i + 1, ":id": order[i] });
		}
	});
}

export function getActiveMilestoneFromDb() {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	const row = currentDb
		.prepare(
			"SELECT * FROM milestones WHERE status NOT IN ('complete', 'parked') ORDER BY tier ASC, CASE WHEN sequence > 0 THEN 0 ELSE 1 END, sequence, id LIMIT 1",
		)
		.get();
	if (!row) return null;
	return rowToMilestone(row);
}

export function getActiveMilestoneIdFromDb() {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	const row = currentDb
		.prepare(
			"SELECT id, status FROM milestones WHERE status NOT IN ('complete', 'parked') ORDER BY id LIMIT 1",
		)
		.get();
	if (!row) return null;
	return { id: row["id"], status: row["status"] };
}

export function deleteMilestone(milestoneId) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	transaction(() => {
		currentDb
			.prepare(`DELETE FROM verification_evidence WHERE milestone_id = :mid`)
			.run({ ":mid": milestoneId });
		currentDb
			.prepare(`DELETE FROM quality_gates WHERE milestone_id = :mid`)
			.run({ ":mid": milestoneId });
		currentDb
			.prepare(`DELETE FROM tasks WHERE milestone_id = :mid`)
			.run({ ":mid": milestoneId });
		currentDb
			.prepare(`DELETE FROM slice_dependencies WHERE milestone_id = :mid`)
			.run({ ":mid": milestoneId });
		currentDb
			.prepare(`DELETE FROM slices WHERE milestone_id = :mid`)
			.run({ ":mid": milestoneId });
		currentDb
			.prepare(`DELETE FROM replan_history WHERE milestone_id = :mid`)
			.run({ ":mid": milestoneId });
		currentDb
			.prepare(`DELETE FROM assessments WHERE milestone_id = :mid`)
			.run({ ":mid": milestoneId });
		currentDb
			.prepare(`DELETE FROM artifacts WHERE milestone_id = :mid`)
			.run({ ":mid": milestoneId });
		currentDb
			.prepare(`DELETE FROM milestones WHERE id = :mid`)
			.run({ ":mid": milestoneId });
	});
}

export function bulkInsertLegacyHierarchy(payload) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const db = currentDb;
	const { milestones, slices, tasks, clearMilestoneIds, createdAt } = payload;
	if (clearMilestoneIds.length === 0) return;
	const placeholders = clearMilestoneIds.map(() => "?").join(",");
	transaction(() => {
		db.prepare(`DELETE FROM tasks WHERE milestone_id IN (${placeholders})`).run(
			...clearMilestoneIds,
		);
		db.prepare(
			`DELETE FROM slices WHERE milestone_id IN (${placeholders})`,
		).run(...clearMilestoneIds);
		db.prepare(`DELETE FROM milestones WHERE id IN (${placeholders})`).run(
			...clearMilestoneIds,
		);
		const insertMilestone = db.prepare(
			"INSERT INTO milestones (id, title, status, created_at) VALUES (?, ?, ?, ?)",
		);
		for (const m of milestones) {
			insertMilestone.run(m.id, m.title, m.status, createdAt);
		}
		const insertSliceStmt = db.prepare(
			"INSERT INTO slices (id, milestone_id, title, status, risk, depends, sequence, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
		);
		for (const s of slices) {
			insertSliceStmt.run(
				s.id,
				s.milestoneId,
				s.title,
				s.status,
				s.risk,
				"[]",
				s.sequence,
				createdAt,
			);
		}
		const insertTaskStmt = db.prepare(
			"INSERT INTO tasks (id, slice_id, milestone_id, title, description, status, estimate, files, sequence) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
		);
		for (const t of tasks) {
			insertTaskStmt.run(
				t.id,
				t.sliceId,
				t.milestoneId,
				t.title,
				"",
				t.status,
				"",
				"[]",
				t.sequence,
			);
		}
	});
}

export function clearEngineHierarchy() {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	transaction(() => {
		currentDb.exec("DELETE FROM tasks");
		currentDb.exec("DELETE FROM slices");
		currentDb.exec("DELETE FROM milestones");
	});
}

export function restoreManifest(manifest) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const db = currentDb;
	transaction(() => {
		// Clear engine tables (order matters for foreign-key-like consistency)
		db.exec("DELETE FROM verification_evidence");
		db.exec("DELETE FROM tasks");
		db.exec("DELETE FROM slices");
		db.exec("DELETE FROM milestones");
		db.exec("DELETE FROM decisions WHERE 1=1");
		// Restore milestones
		const msStmt =
			db.prepare(`INSERT INTO milestones (id, title, status, depends_on, created_at, completed_at,
        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)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`);
		for (const m of manifest.milestones) {
			msStmt.run(
				m.id,
				m.title,
				m.status,
				JSON.stringify(m.depends_on),
				m.created_at,
				m.completed_at,
				m.vision,
				JSON.stringify(m.success_criteria),
				JSON.stringify(m.key_risks),
				JSON.stringify(m.proof_strategy),
				m.verification_contract,
				m.verification_integration,
				m.verification_operational,
				m.verification_uat,
				JSON.stringify(m.definition_of_done),
				m.requirement_coverage,
				m.boundary_map_markdown,
				m.vision_meeting ? JSON.stringify(m.vision_meeting) : "",
				m.product_research ? JSON.stringify(m.product_research) : "",
			);
		}
		// Restore slices
		const slStmt =
			db.prepare(`INSERT INTO slices (milestone_id, id, title, status, risk, depends, demo,
        created_at, completed_at, full_summary_md, full_uat_md,
        goal, success_criteria, proof_level, integration_closure, observability_impact,
        adversarial_partner, adversarial_combatant, adversarial_architect, planning_meeting_json,
        sequence, replan_triggered_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`);
		for (const s of manifest.slices) {
			slStmt.run(
				s.milestone_id,
				s.id,
				s.title,
				s.status,
				s.risk,
				JSON.stringify(s.depends),
				s.demo,
				s.created_at,
				s.completed_at,
				s.full_summary_md,
				s.full_uat_md,
				s.goal,
				s.success_criteria,
				s.proof_level,
				s.integration_closure,
				s.observability_impact,
				s.adversarial_partner ?? "",
				s.adversarial_combatant ?? "",
				s.adversarial_architect ?? "",
				s.planning_meeting ? JSON.stringify(s.planning_meeting) : "",
				s.sequence,
				s.replan_triggered_at,
			);
		}
		// Restore tasks
		const tkStmt =
			db.prepare(`INSERT INTO tasks (milestone_id, slice_id, id, title, status,
        one_liner, narrative, verification_result, duration, completed_at,
        blocker_discovered, deviations, known_issues, key_files, key_decisions,
        full_summary_md, description, estimate, files, verify,
        inputs, expected_output, observability_impact, full_plan_md, sequence)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`);
		for (const t of manifest.tasks) {
			tkStmt.run(
				t.milestone_id,
				t.slice_id,
				t.id,
				t.title,
				t.status,
				t.one_liner,
				t.narrative,
				t.verification_result,
				t.duration,
				t.completed_at,
				t.blocker_discovered ? 1 : 0,
				t.deviations,
				t.known_issues,
				JSON.stringify(t.key_files),
				JSON.stringify(t.key_decisions),
				t.full_summary_md,
				t.description,
				t.estimate,
				JSON.stringify(t.files),
				t.verify,
				JSON.stringify(t.inputs),
				JSON.stringify(t.expected_output),
				t.observability_impact,
				t.full_plan_md,
				t.sequence,
			);
		}
		// Restore decisions
		const dcStmt =
			db.prepare(`INSERT INTO decisions (seq, id, when_context, scope, decision, choice, rationale, revisable, made_by, superseded_by)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`);
		for (const d of manifest.decisions) {
			dcStmt.run(
				d.seq,
				d.id,
				d.when_context,
				d.scope,
				d.decision,
				d.choice,
				d.rationale,
				d.revisable,
				d.made_by,
				d.superseded_by,
			);
		}
		// Restore verification evidence
		const evStmt =
			db.prepare(`INSERT INTO verification_evidence (task_id, slice_id, milestone_id, command, exit_code, verdict, duration_ms, created_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`);
		for (const e of manifest.verification_evidence) {
			evStmt.run(
				e.task_id,
				e.slice_id,
				e.milestone_id,
				e.command,
				e.exit_code,
				e.verdict,
				e.duration_ms,
				e.created_at,
			);
		}
	});
}
