import { appendFileSync, existsSync, readFileSync, statSync } from "node:fs";
import { join } from "node:path";
import { SF_STALE_STATE, SFError } from "../errors.js";
import {
	normalizeSchedulerStatus,
	normalizeTaskStatus,
	taskFrontmatterFromRecord,
	withTaskFrontmatter,
} from "../task-frontmatter.js";
import { logWarning } from "../workflow-logger.js";
import {
	_getAdapter,
	hasTaskSpecIntent,
	insertTaskSpecIfAbsent,
	rowToTask,
	safeParseJsonArray,
	transaction,
} from "./sf-db-core.js";

// ─── Layer B: iter-truth revert guard ─────────────────────────────────────────

const SF_REVERT_BLOCK_WINDOW_MS_DEFAULT = 1800000; // 30 minutes

/**
 * Scan iterations.jsonl backwards for the most recent entry whose unitId
 * matches `<milestoneId>/<sliceId>/<taskId>` and return `{outcome, ts}`.
 * Returns null when the file is absent, unreadable, or no match is found.
 * Capped at last 2000 lines / 2 MB.
 */
function _findLatestIterOutcomeForUnit(basePath, milestoneId, sliceId, taskId) {
	const iterPath = join(
		basePath,
		".sf",
		"runtime",
		"autonomous-solver",
		"iterations.jsonl",
	);
	if (!existsSync(iterPath)) return null;
	try {
		const MAX_BYTES = 2 * 1024 * 1024;
		const stat = statSync(iterPath);
		// File is small in practice; if somehow large, clamp to last 2 MB via string slice
		let raw = readFileSync(iterPath, "utf-8");
		if (stat.size > MAX_BYTES) {
			raw = raw.slice(raw.length - MAX_BYTES);
		}
		const unitId = `${milestoneId}/${sliceId}/${taskId}`;
		const lines = raw.split("\n").filter((l) => l.trim().length > 0);
		// Scan from the end, cap at last 2000 lines
		const tail = lines.slice(-2000);
		for (let i = tail.length - 1; i >= 0; i--) {
			try {
				const entry = JSON.parse(tail[i]);
				if (entry.unitId === unitId && entry.outcome) {
					return { outcome: entry.outcome, ts: entry.ts };
				}
			} catch {
				// skip malformed lines
			}
		}
	} catch {
		// best-effort — never block on file read failure
	}
	return null;
}

/**
 * Append a revert-blocked self-feedback entry directly to
 * `<basePath>/.sf/self-feedback.jsonl` (best-effort, never throws).
 */
function _appendRevertBlockedFeedback(
	basePath,
	unitId,
	iterCompleteTs,
	attemptedRevert,
	stackFrame,
) {
	try {
		const ts = Date.now().toString(36);
		const rnd = Math.random().toString(36).slice(2, 8);
		const id = `sf-${ts}-${rnd}`;
		const entry = {
			schemaVersion: 1,
			id,
			ts: new Date().toISOString(),
			kind: "revert-blocked-by-iter-truth",
			severity: "medium",
			summary: `Revert to "${attemptedRevert}" blocked for ${unitId} — iterations.jsonl shows outcome=complete at ${iterCompleteTs}`,
			evidence: stackFrame ?? "(no stack frame)",
			unitId,
			iterCompleteTs,
			attemptedRevert,
		};
		const sfJsonlPath = join(basePath, ".sf", "self-feedback.jsonl");
		appendFileSync(sfJsonlPath, `${JSON.stringify(entry)}\n`, "utf-8");
	} catch {
		// best-effort — never let feedback write block the error path
	}
}

export function insertTask(t) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT INTO tasks (
      milestone_id, slice_id, id, title, status, task_status, one_liner, narrative,
      verification_result, verification_status, duration, completed_at, blocker_discovered,
      deviations, known_issues, key_files, key_decisions, full_summary_md,
      description, estimate, files, verify, inputs, expected_output, observability_impact, sequence,
      purpose_trace
    ) VALUES (
      :milestone_id, :slice_id, :id, :title, :status, :task_status, :one_liner, :narrative,
      :verification_result, :verification_status, :duration, :completed_at, :blocker_discovered,
      :deviations, :known_issues, :key_files, :key_decisions, :full_summary_md,
      :description, :estimate, :files, :verify, :inputs, :expected_output, :observability_impact, :sequence,
      :purpose_trace
    )
    ON CONFLICT(milestone_id, slice_id, id) DO UPDATE SET
      title = CASE WHEN NULLIF(:title, '') IS NOT NULL THEN :title ELSE tasks.title END,
      status = :status,
      task_status = :task_status,
      one_liner = :one_liner,
      narrative = :narrative,
      verification_result = :verification_result,
      verification_status = :verification_status,
      duration = :duration,
      completed_at = :completed_at,
      blocker_discovered = :blocker_discovered,
      deviations = :deviations,
      known_issues = :known_issues,
      key_files = :key_files,
      key_decisions = :key_decisions,
      full_summary_md = :full_summary_md,
      description = CASE WHEN NULLIF(:description, '') IS NOT NULL THEN :description ELSE tasks.description END,
      estimate = CASE WHEN NULLIF(:estimate, '') IS NOT NULL THEN :estimate ELSE tasks.estimate END,
      files = CASE WHEN NULLIF(:files, '[]') IS NOT NULL THEN :files ELSE tasks.files END,
      verify = CASE WHEN NULLIF(:verify, '') IS NOT NULL THEN :verify ELSE tasks.verify END,
      inputs = CASE WHEN NULLIF(:inputs, '[]') IS NOT NULL THEN :inputs ELSE tasks.inputs END,
      expected_output = CASE WHEN NULLIF(:expected_output, '[]') IS NOT NULL THEN :expected_output ELSE tasks.expected_output END,
      observability_impact = CASE WHEN NULLIF(:observability_impact, '') IS NOT NULL THEN :observability_impact ELSE tasks.observability_impact END,
      sequence = :sequence,
      purpose_trace = COALESCE(:purpose_trace, tasks.purpose_trace)`)
		.run({
			":milestone_id": t.milestoneId,
			":slice_id": t.sliceId,
			":id": t.id,
			":title": t.title ?? "",
			":status": t.status ?? "pending",
			":task_status": normalizeTaskStatus(t.taskStatus ?? t.status) ?? "todo",
			":one_liner": t.oneLiner ?? "",
			":narrative": t.narrative ?? "",
			":verification_result": t.verificationResult ?? "",
			":verification_status": t.verificationStatus ?? "",
			":duration": t.duration ?? "",
			":completed_at":
				t.status === "done" || t.status === "complete"
					? new Date().toISOString()
					: null,
			":blocker_discovered": t.blockerDiscovered ? 1 : 0,
			":deviations": t.deviations ?? "",
			":known_issues": t.knownIssues ?? "",
			":key_files": JSON.stringify(t.keyFiles ?? []),
			":key_decisions": JSON.stringify(t.keyDecisions ?? []),
			":full_summary_md": t.fullSummaryMd ?? "",
			":description": t.planning?.description ?? "",
			":estimate": t.planning?.estimate ?? "",
			":files": JSON.stringify(t.planning?.files ?? []),
			":verify": t.planning?.verify ?? "",
			":inputs": JSON.stringify(t.planning?.inputs ?? []),
			":expected_output": JSON.stringify(t.planning?.expectedOutput ?? []),
			":observability_impact": t.planning?.observabilityImpact ?? "",
			":sequence": t.sequence ?? 0,
			":purpose_trace":
				typeof t.purposeTrace === "string" && t.purposeTrace.trim().length > 0
					? t.purposeTrace.trim()
					: null,
		});
	if (hasTaskSpecIntent(t.planning)) {
		insertTaskSpecIfAbsent(t.milestoneId, t.sliceId, t.id, t.planning ?? {});
	}
	upsertTaskSchedulerStatus(t.milestoneId, t.sliceId, t.id, "queued", {
		onlyIfAbsent: true,
	});
}

export function insertOrIgnoreTask(args) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT OR IGNORE INTO tasks (milestone_id, slice_id, id, title, status, created_at)
     VALUES (:mid, :sid, :tid, :title, 'pending', :ts)`)
		.run({
			":mid": args.milestoneId,
			":sid": args.sliceId,
			":tid": args.taskId,
			":title": args.title,
			":ts": args.createdAt,
		});
}

export function upsertTaskSchedulerStatus(
	milestoneId,
	sliceId,
	taskId,
	status = "queued",
	{ onlyIfAbsent = false } = {},
) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const schedulerStatus = normalizeSchedulerStatus(status) ?? "queued";
	const sql = onlyIfAbsent
		? `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
      )`
		: `INSERT INTO task_scheduler (
        milestone_id, slice_id, task_id, status, updated_at
      ) VALUES (
        :milestone_id, :slice_id, :task_id, :status, :updated_at
      )
      ON CONFLICT(milestone_id, slice_id, task_id) DO UPDATE SET
        status = excluded.status,
        updated_at = excluded.updated_at`;
	currentDb.prepare(sql).run({
		":milestone_id": milestoneId,
		":slice_id": sliceId,
		":task_id": taskId,
		":status": schedulerStatus,
		":updated_at": new Date().toISOString(),
	});
}

export function updateTaskStatus(
	milestoneId,
	sliceId,
	taskId,
	status,
	completedAt,
	purposeTrace,
	{ basePath = process.cwd() } = {},
) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const taskStatus = normalizeTaskStatus(status) ?? "todo";
	// ADR-0000 (SF v70): purpose_trace is the goal-anchored sentence the
	// executor recorded when finishing the task. COALESCE keeps any value
	// previously written when callers omit it on a no-op status update.
	const traceParam =
		typeof purposeTrace === "string" && purposeTrace.trim().length > 0
			? purposeTrace.trim()
			: null;
	// Layer B: block reverts when iterations.jsonl recently recorded outcome=complete
	// for this (milestone, slice, task) triple (R072 + T02-clobber fix).
	const isRevert =
		status !== "complete" && status !== "done" && status !== "in_progress";
	if (isRevert) {
		const windowMs =
			process.env.SF_REVERT_BLOCK_WINDOW_MS !== undefined
				? Number(process.env.SF_REVERT_BLOCK_WINDOW_MS)
				: SF_REVERT_BLOCK_WINDOW_MS_DEFAULT;
		if (windowMs > 0) {
			const iterResult = _findLatestIterOutcomeForUnit(
				basePath,
				milestoneId,
				sliceId,
				taskId,
			);
			if (iterResult && iterResult.outcome === "complete") {
				const iterCompleteTs = iterResult.ts;
				const iterCompleteMs = iterCompleteTs
					? new Date(iterCompleteTs).getTime()
					: 0;
				const ageMs = Date.now() - iterCompleteMs;
				if (ageMs <= windowMs) {
					const unitId = `${milestoneId}/${sliceId}/${taskId}`;
					const stackLine = new Error().stack?.split("\n")[2]?.trim() ?? "";
					_appendRevertBlockedFeedback(
						basePath,
						unitId,
						iterCompleteTs,
						status,
						stackLine,
					);
					throw new Error(
						`revert-blocked-by-iter-truth: ${unitId} has outcome=complete in iterations.jsonl at ${iterCompleteTs} (${Math.round(ageMs / 1000)}s ago, window=${windowMs}ms); attempted revert to "${status}"`,
					);
				}
			}
		}
	}
	// R072: When reverting a task to a non-complete status (e.g. the safety
	// file-change guard or hook retry path calls updateTaskStatus("pending")),
	// clear verification_status so it cannot drift out of sync with status.
	// Without this, completeTaskAtomic sets both fields atomically but a later
	// status-revert leaves verification_status=all_pass while status=pending,
	// which triggers the status-completion-drift quarantine in state-db.js and
	// prevents the task from being re-dispatched. Completion writes must go
	// through completeTaskAtomic; this function is revert/lifecycle only.
	const isComplete = status === "complete" || status === "done";
	const verificationStatusUpdate = isComplete
		? "" // completing via this function is non-standard; don't touch verification_status
		: ", verification_status = ''";
	currentDb
		.prepare(`UPDATE tasks SET
      status = :status,
      completed_at = :completed_at,
      task_status = :task_status,
      purpose_trace = COALESCE(:purpose_trace, purpose_trace)${verificationStatusUpdate}
     WHERE milestone_id = :milestone_id AND slice_id = :slice_id AND id = :id`)
		.run({
			":status": status,
			":completed_at": completedAt ?? null,
			":task_status": taskStatus,
			":purpose_trace": traceParam,
			":milestone_id": milestoneId,
			":slice_id": sliceId,
			":id": taskId,
		});
}

/**
 * Record the goal-anchored purpose sentence the executor named when finishing
 * a task. complete-slice consults this column to verify every task actually
 * served the slice goal before it flips the slice to complete (ADR-0000).
 *
 * Purpose: doctrine restoration — without a purpose trace per task, the
 * slice-level "did we solve the right problem" check has nothing to verify.
 *
 * Consumer: complete-task handler (on success) and complete-slice handler
 * (refuses to close when any row is NULL).
 */
export function updateTaskPurposeTrace(milestoneId, sliceId, taskId, trace) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	if (typeof trace !== "string" || trace.trim().length === 0) {
		throw new SFError(
			SF_STALE_STATE,
			"updateTaskPurposeTrace: trace must be a non-empty string",
		);
	}
	currentDb
		.prepare(
			`UPDATE tasks SET purpose_trace = :trace
       WHERE milestone_id = :mid AND slice_id = :sid AND id = :tid`,
		)
		.run({
			":trace": trace.trim(),
			":mid": milestoneId,
			":sid": sliceId,
			":tid": taskId,
		});
}

export function setTaskEscalationPending(
	milestoneId,
	sliceId,
	taskId,
	artifactPath,
) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`UPDATE tasks
         SET escalation_pending = 1,
             escalation_awaiting_review = 0,
             escalation_artifact_path = :path
       WHERE milestone_id = :mid AND slice_id = :sid AND id = :tid`)
		.run({
			":path": artifactPath,
			":mid": milestoneId,
			":sid": sliceId,
			":tid": taskId,
		});
}

export function setTaskEscalationAwaitingReview(
	milestoneId,
	sliceId,
	taskId,
	artifactPath,
) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`UPDATE tasks
         SET escalation_awaiting_review = 1,
             escalation_pending = 0,
             escalation_artifact_path = :path
       WHERE milestone_id = :mid AND slice_id = :sid AND id = :tid`)
		.run({
			":path": artifactPath,
			":mid": milestoneId,
			":sid": sliceId,
			":tid": taskId,
		});
}

export function clearTaskEscalationFlags(milestoneId, sliceId, taskId) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`UPDATE tasks
         SET escalation_pending = 0,
             escalation_awaiting_review = 0
       WHERE milestone_id = :mid AND slice_id = :sid AND id = :tid`)
		.run({ ":mid": milestoneId, ":sid": sliceId, ":tid": taskId });
}

export function findUnappliedEscalationOverride(milestoneId, sliceId) {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	const row = currentDb
		.prepare(`SELECT id, escalation_artifact_path
       FROM tasks
       WHERE milestone_id = :mid
         AND slice_id = :sid
         AND escalation_artifact_path IS NOT NULL
         AND escalation_pending = 0
         AND escalation_awaiting_review = 0
         AND escalation_override_applied = 0
       ORDER BY sequence ASC, id ASC
       LIMIT 1`)
		.get({ ":mid": milestoneId, ":sid": sliceId });
	if (!row || !row.escalation_artifact_path) return null;
	return { taskId: row.id, artifactPath: row.escalation_artifact_path };
}

export function claimEscalationOverride(milestoneId, sliceId, taskId) {
	const currentDb = _getAdapter();
	if (!currentDb) return false;
	const result = currentDb
		.prepare(`UPDATE tasks
         SET escalation_override_applied = 1
       WHERE milestone_id = :mid
         AND slice_id = :sid
         AND id = :tid
         AND escalation_override_applied = 0`)
		.run({ ":mid": milestoneId, ":sid": sliceId, ":tid": taskId });
	return (result?.changes ?? 0) > 0;
}

export function setTaskBlockerDiscovered(
	milestoneId,
	sliceId,
	taskId,
	discovered,
) {
	const currentDb = _getAdapter();
	if (!currentDb) return;
	currentDb
		.prepare(
			`UPDATE tasks SET blocker_discovered = :discovered WHERE milestone_id = :mid AND slice_id = :sid AND id = :tid`,
		)
		.run({
			":discovered": discovered ? 1 : 0,
			":mid": milestoneId,
			":sid": sliceId,
			":tid": taskId,
		});
}

export function upsertTaskPlanning(milestoneId, sliceId, taskId, planning) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	insertTaskSpecIfAbsent(milestoneId, sliceId, taskId, planning);
	const { normalized: frontmatter, errors: fmErrors } =
		taskFrontmatterFromRecord(planning);
	if (fmErrors?.length)
		logWarning(
			"sf-db:upsertTaskPlanning",
			`frontmatter validation errors for ${milestoneId}/${sliceId}/${taskId}: ${fmErrors.join(", ")}`,
		);
	const hasTaskStatus =
		planning.taskStatus !== undefined ||
		planning.task_status !== undefined ||
		planning.status !== undefined;
	currentDb
		.prepare(`UPDATE tasks SET
      title = COALESCE(:title, title),
      description = COALESCE(:description, description),
      estimate = COALESCE(:estimate, estimate),
      files = COALESCE(:files, files),
      verify = COALESCE(:verify, verify),
      inputs = COALESCE(:inputs, inputs),
      expected_output = COALESCE(:expected_output, expected_output),
      observability_impact = COALESCE(:observability_impact, observability_impact),
      full_plan_md = COALESCE(:full_plan_md, full_plan_md),
      risk = :risk,
      mutation_scope = :mutation_scope,
      verification_type = :verification_type,
      plan_approval = :plan_approval,
      task_status = CASE WHEN :has_task_status = 1 THEN :task_status ELSE task_status END,
      estimated_effort = :estimated_effort,
      dependencies = :dependencies,
      blocks_parallel = :blocks_parallel,
      requires_user_input = :requires_user_input,
      auto_retry = :auto_retry,
      max_retries = :max_retries,
      frontmatter_version = :frontmatter_version
     WHERE milestone_id = :milestone_id AND slice_id = :slice_id AND id = :id`)
		.run({
			":milestone_id": milestoneId,
			":slice_id": sliceId,
			":id": taskId,
			":title": planning.title ?? null,
			":description": planning.description ?? null,
			":estimate": planning.estimate ?? null,
			":files": planning.files ? JSON.stringify(planning.files) : null,
			":verify": planning.verify ?? null,
			":inputs": planning.inputs ? JSON.stringify(planning.inputs) : null,
			":expected_output": planning.expectedOutput
				? JSON.stringify(planning.expectedOutput)
				: null,
			":observability_impact": planning.observabilityImpact ?? null,
			":full_plan_md": planning.fullPlanMd ?? null,
			":risk": frontmatter.risk,
			":mutation_scope": frontmatter.mutationScope,
			":verification_type": frontmatter.verification,
			":plan_approval": frontmatter.planApproval,
			":task_status": frontmatter.taskStatus,
			":has_task_status": hasTaskStatus ? 1 : 0,
			":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,
			":frontmatter_version": frontmatter.frontmatterVersion,
		});
	if (
		planning.schedulerStatus !== undefined ||
		planning.scheduler_status !== undefined
	) {
		upsertTaskSchedulerStatus(
			milestoneId,
			sliceId,
			taskId,
			frontmatter.schedulerStatus,
		);
	} else {
		upsertTaskSchedulerStatus(milestoneId, sliceId, taskId, "queued", {
			onlyIfAbsent: true,
		});
	}
}

export function getTask(milestoneId, sliceId, taskId) {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	const row = currentDb
		.prepare(
			`SELECT t.*, ts.status AS scheduler_status
			   FROM tasks t
			   LEFT JOIN task_scheduler ts
			     ON t.milestone_id = ts.milestone_id
			    AND t.slice_id = ts.slice_id
			    AND t.id = ts.task_id
			  WHERE t.milestone_id = :mid AND t.slice_id = :sid AND t.id = :tid`,
		)
		.get({ ":mid": milestoneId, ":sid": sliceId, ":tid": taskId });
	if (!row) return null;
	return rowToTask(row);
}

export function setTaskSummaryMd(milestoneId, sliceId, taskId, md) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(
			`UPDATE tasks
			   SET full_summary_md = :md
			 WHERE milestone_id = :mid
			   AND slice_id = :sid
			   AND id = :tid
			   AND status IN ('complete', 'done')`,
		)
		.run({ ":mid": milestoneId, ":sid": sliceId, ":tid": taskId, ":md": md });
}

function verificationStatusFromCompletionFields(fields) {
	if (typeof fields.verificationStatus === "string") {
		const trimmed = fields.verificationStatus.trim();
		if (trimmed) return trimmed;
	}
	const result = String(fields.verificationResult ?? "").toLowerCase();
	if (/\ball[_ -]?pass\b|\bpass(?:ed|es)?\b|\bsuccess(?:ful)?\b/.test(result)) {
		return "all_pass";
	}
	if (/\bpartial\b/.test(result)) return "partial";
	if (/\ball[_ -]?fail\b|\bfail(?:ed|ure|ures)?\b/.test(result)) {
		return "all_fail";
	}
	return "";
}

/**
 * Mark a task complete and persist its prose/evidence fields in one DB
 * transaction.
 *
 * Purpose: keep task status, frontmatter status, verification state, and prose
 * completion artifacts from drifting apart so autonomous dispatch never loops on
 * a row whose narrative says complete while its structured status says pending.
 *
 * Consumer: complete-task and explicit SUMMARY reconciliation remediation.
 */
export function completeTaskAtomic(milestoneId, sliceId, taskId, fields = {}) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const completedAt = fields.completedAt ?? new Date().toISOString();
	const status = fields.status ?? "complete";
	const taskStatus = normalizeTaskStatus(fields.taskStatus ?? status) ?? "done";
	const verificationStatus = verificationStatusFromCompletionFields(fields);
	transaction(() => {
		currentDb
			.prepare(`INSERT INTO tasks (
		      milestone_id, slice_id, id, title, status, task_status,
		      one_liner, narrative, verification_result, verification_status,
		      duration, completed_at, blocker_discovered, deviations, known_issues,
		      key_files, key_decisions, full_summary_md, purpose_trace
		    ) VALUES (
		      :mid, :sid, :tid, :title, :status, :task_status,
		      :one_liner, :narrative, :verification_result, :verification_status,
		      :duration, :completed_at, :blocker_discovered, :deviations, :known_issues,
		      :key_files, :key_decisions, :summary_md, :purpose_trace
		    )
		    ON CONFLICT(milestone_id, slice_id, id) DO UPDATE SET
		      title = CASE WHEN NULLIF(:title, '') IS NOT NULL THEN :title ELSE tasks.title END,
		      status = :status,
		      task_status = :task_status,
		      one_liner = CASE WHEN NULLIF(:one_liner, '') IS NOT NULL THEN :one_liner ELSE tasks.one_liner END,
		      narrative = :narrative,
		      verification_result = :verification_result,
		      verification_status = :verification_status,
		      duration = CASE WHEN NULLIF(:duration, '') IS NOT NULL THEN :duration ELSE tasks.duration END,
		      completed_at = :completed_at,
		      blocker_discovered = :blocker_discovered,
		      deviations = CASE WHEN NULLIF(:deviations, '') IS NOT NULL THEN :deviations ELSE tasks.deviations END,
		      known_issues = CASE WHEN NULLIF(:known_issues, '') IS NOT NULL THEN :known_issues ELSE tasks.known_issues END,
		      key_files = :key_files,
		      key_decisions = :key_decisions,
		      full_summary_md = :summary_md,
		      purpose_trace = COALESCE(:purpose_trace, tasks.purpose_trace)`)
			.run({
				":mid": milestoneId,
				":sid": sliceId,
				":tid": taskId,
				":title": fields.title ?? fields.oneLiner ?? taskId,
				":status": status,
				":task_status": taskStatus,
				":one_liner": fields.oneLiner ?? "",
				":narrative": fields.narrative ?? "",
				":verification_result": fields.verificationResult ?? "",
				":verification_status": verificationStatus,
				":duration": fields.duration ?? "",
				":completed_at": completedAt,
				":blocker_discovered": fields.blockerDiscovered ? 1 : 0,
				":deviations": fields.deviations ?? "",
				":known_issues": fields.knownIssues ?? "",
				":key_files": JSON.stringify(fields.keyFiles ?? []),
				":key_decisions": JSON.stringify(fields.keyDecisions ?? []),
				":summary_md": fields.summaryMd ?? "",
				":purpose_trace":
					typeof fields.purposeTrace === "string" &&
					fields.purposeTrace.trim().length > 0
						? fields.purposeTrace.trim()
						: null,
			});
	});
}

/**
 * Apply on-disk SUMMARY.md frontmatter and body to the DB task row.
 *
 * Purpose: operator-invoked remediation when the DB-driven reconcile refuses
 * to silently import disk state (state-db.js:reconcileSliceTasks). Writes
 * status, task_status, completed_at, verification_result, verification_status,
 * key_files/key_decisions (as JSON), blocker flag, and the full markdown body
 * through completeTaskAtomic so the row matches the on-disk SUMMARY shape.
 *
 * Consumer: state-reconcile.js: reconcileTaskFromSummary.
 */
export function setTaskSummaryFields(
	milestoneId,
	sliceId,
	taskId,
	{
		status,
		completedAt,
		verificationResult,
		blockerDiscovered,
		keyFiles,
		keyDecisions,
		narrative,
		summaryMd,
		verificationStatus,
	},
) {
	completeTaskAtomic(milestoneId, sliceId, taskId, {
		status,
		completedAt,
		verificationResult,
		verificationStatus,
		blockerDiscovered,
		keyFiles,
		keyDecisions,
		narrative,
		summaryMd,
	});
}

export function getActiveTaskFromDb(milestoneId, sliceId) {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	const row = currentDb
		.prepare(
			"SELECT * FROM tasks WHERE milestone_id = :mid AND slice_id = :sid AND status NOT IN ('complete', 'done') ORDER BY sequence, id LIMIT 1",
		)
		.get({ ":mid": milestoneId, ":sid": sliceId });
	if (!row) return null;
	return rowToTask(row);
}

export function getActiveTaskIdFromDb(milestoneId, sliceId) {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	const row = currentDb
		.prepare(
			"SELECT id, status, title FROM tasks WHERE milestone_id = :mid AND slice_id = :sid AND status NOT IN ('complete', 'done') ORDER BY sequence, id LIMIT 1",
		)
		.get({ ":mid": milestoneId, ":sid": sliceId });
	if (!row) return null;
	return {
		id: row["id"],
		status: row["status"],
		title: row["title"],
	};
}

export function deleteTask(milestoneId, sliceId, taskId) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	transaction(() => {
		// Must delete verification_evidence first (FK constraint)
		currentDb
			.prepare(
				`DELETE FROM verification_evidence WHERE milestone_id = :mid AND slice_id = :sid AND task_id = :tid`,
			)
			.run({ ":mid": milestoneId, ":sid": sliceId, ":tid": taskId });
		currentDb
			.prepare(
				`DELETE FROM tasks WHERE milestone_id = :mid AND slice_id = :sid AND id = :tid`,
			)
			.run({ ":mid": milestoneId, ":sid": sliceId, ":tid": taskId });
	});
}

export function listEscalationArtifacts(milestoneId, includeResolved = false) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	const filter = includeResolved
		? "escalation_artifact_path IS NOT NULL"
		: "(escalation_pending = 1 OR escalation_awaiting_review = 1) AND escalation_artifact_path IS NOT NULL";
	const rows = currentDb
		.prepare(
			`SELECT * FROM tasks WHERE milestone_id = :mid AND ${filter} ORDER BY slice_id, sequence, id`,
		)
		.all({ ":mid": milestoneId });
	return rows.map(rowToTask);
}

export function getSliceTasks(milestoneId, sliceId) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	const rows = currentDb
		.prepare(
			`SELECT t.*, ts.status AS scheduler_status
			   FROM tasks t
			   LEFT JOIN task_scheduler ts
			     ON t.milestone_id = ts.milestone_id
			    AND t.slice_id = ts.slice_id
			    AND t.id = ts.task_id
			  WHERE t.milestone_id = :mid AND t.slice_id = :sid
			  ORDER BY t.sequence, t.id`,
		)
		.all({ ":mid": milestoneId, ":sid": sliceId });
	return rows.map(rowToTask);
}
