import { existsSync, readdirSync, readFileSync } from "node:fs";
import { join } from "node:path";
import { SF_STALE_STATE, SFError } from "../errors.js";
import {
	_getAdapter,
	insertSliceSpecIfAbsent,
	parsePlanningMeeting,
	rowToSlice,
	rowToTask,
	safeParseJsonArray,
	transaction,
} from "./sf-db-core.js";

export function insertSlice(s) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT INTO slices (
      milestone_id, id, title, status, risk, depends, demo, created_at,
      goal, success_criteria, proof_level, integration_closure, observability_impact,
      adversarial_partner, adversarial_combatant, adversarial_architect, planning_meeting_json, sequence,
      is_sketch, sketch_scope, traces_vision_fragment
    ) VALUES (
      :milestone_id, :id, :title, :status, :risk, :depends, :demo, :created_at,
      :goal, :success_criteria, :proof_level, :integration_closure, :observability_impact,
      :adversarial_partner, :adversarial_combatant, :adversarial_architect, :planning_meeting_json, :sequence,
      :is_sketch, :sketch_scope, :traces_vision_fragment
    )
    ON CONFLICT (milestone_id, id) DO UPDATE SET
      title = CASE WHEN :raw_title IS NOT NULL THEN excluded.title ELSE slices.title END,
      status = CASE WHEN slices.status IN ('complete', 'done') THEN slices.status ELSE excluded.status END,
      risk = CASE WHEN :raw_risk IS NOT NULL THEN excluded.risk ELSE slices.risk END,
      depends = excluded.depends,
      demo = CASE WHEN :raw_demo IS NOT NULL THEN excluded.demo ELSE slices.demo END,
      goal = CASE WHEN :raw_goal IS NOT NULL THEN excluded.goal ELSE slices.goal END,
      success_criteria = CASE WHEN :raw_success_criteria IS NOT NULL THEN excluded.success_criteria ELSE slices.success_criteria END,
      proof_level = CASE WHEN :raw_proof_level IS NOT NULL THEN excluded.proof_level ELSE slices.proof_level END,
      integration_closure = CASE WHEN :raw_integration_closure IS NOT NULL THEN excluded.integration_closure ELSE slices.integration_closure END,
      observability_impact = CASE WHEN :raw_observability_impact IS NOT NULL THEN excluded.observability_impact ELSE slices.observability_impact END,
      adversarial_partner = CASE WHEN :raw_adversarial_partner IS NOT NULL THEN excluded.adversarial_partner ELSE slices.adversarial_partner END,
      adversarial_combatant = CASE WHEN :raw_adversarial_combatant IS NOT NULL THEN excluded.adversarial_combatant ELSE slices.adversarial_combatant END,
      adversarial_architect = CASE WHEN :raw_adversarial_architect IS NOT NULL THEN excluded.adversarial_architect ELSE slices.adversarial_architect END,
      planning_meeting_json = CASE WHEN :raw_planning_meeting_json IS NOT NULL THEN excluded.planning_meeting_json ELSE slices.planning_meeting_json END,
      sequence = CASE WHEN :raw_sequence IS NOT NULL THEN excluded.sequence ELSE slices.sequence END,
      is_sketch = CASE WHEN :raw_is_sketch IS NOT NULL THEN excluded.is_sketch ELSE slices.is_sketch END,
      sketch_scope = CASE WHEN :raw_sketch_scope IS NOT NULL THEN excluded.sketch_scope ELSE slices.sketch_scope END,
      traces_vision_fragment = CASE WHEN :raw_traces_vision_fragment IS NOT NULL THEN excluded.traces_vision_fragment ELSE slices.traces_vision_fragment END`)
		.run({
			":milestone_id": s.milestoneId,
			":id": s.id,
			":title": s.title ?? "",
			":status": s.status ?? "pending",
			":risk": s.risk ?? "medium",
			":depends": JSON.stringify(s.depends ?? []),
			":demo": s.demo ?? "",
			":created_at": new Date().toISOString(),
			":goal": s.planning?.goal ?? "",
			":success_criteria": s.planning?.successCriteria ?? "",
			":proof_level": s.planning?.proofLevel ?? "",
			":integration_closure": s.planning?.integrationClosure ?? "",
			":observability_impact": s.planning?.observabilityImpact ?? "",
			":adversarial_partner": s.planning?.adversarialReview?.partner ?? "",
			":adversarial_combatant": s.planning?.adversarialReview?.combatant ?? "",
			":adversarial_architect": s.planning?.adversarialReview?.architect ?? "",
			":planning_meeting_json": s.planning?.planningMeeting
				? JSON.stringify(s.planning.planningMeeting)
				: "",
			":sequence": s.sequence ?? 0,
			":is_sketch": s.isSketch === true ? 1 : 0,
			":sketch_scope": s.sketchScope ?? "",
			// ADR-0000 P2 (schema v69): NULL allowed for legacy callers that pre-date
			// the vision-trace requirement; plan-milestone validates non-empty + in-vision.
			":traces_vision_fragment": s.tracesVisionFragment ?? null,
			// Raw sentinel params: NULL when caller omitted the field, used in ON CONFLICT guards
			":raw_title": s.title ?? null,
			":raw_risk": s.risk ?? null,
			":raw_demo": s.demo ?? null,
			":raw_goal": s.planning?.goal ?? null,
			":raw_success_criteria": s.planning?.successCriteria ?? null,
			":raw_proof_level": s.planning?.proofLevel ?? null,
			":raw_integration_closure": s.planning?.integrationClosure ?? null,
			":raw_observability_impact": s.planning?.observabilityImpact ?? null,
			":raw_adversarial_partner":
				s.planning?.adversarialReview?.partner ?? null,
			":raw_adversarial_combatant":
				s.planning?.adversarialReview?.combatant ?? null,
			":raw_adversarial_architect":
				s.planning?.adversarialReview?.architect ?? null,
			":raw_planning_meeting_json": s.planning?.planningMeeting
				? JSON.stringify(s.planning.planningMeeting)
				: null,
			":raw_sequence": s.sequence ?? null,
			":raw_is_sketch": s.isSketch === undefined ? null : s.isSketch ? 1 : 0,
			":raw_sketch_scope": s.sketchScope === undefined ? null : s.sketchScope,
			":raw_traces_vision_fragment": s.tracesVisionFragment ?? null,
		});
	insertSliceSpecIfAbsent(s.milestoneId, s.id, s.planning ?? {});
}

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

export function clearSliceSketch(milestoneId, sliceId) {
	setSliceSketchFlag(milestoneId, sliceId, false);
}

export function setSliceSketchFlag(milestoneId, sliceId, isSketch) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(
			`UPDATE slices SET is_sketch = :is_sketch WHERE milestone_id = :mid AND id = :sid`,
		)
		.run({
			":is_sketch": isSketch ? 1 : 0,
			":mid": milestoneId,
			":sid": sliceId,
		});
}

export function autoHealSketchFlags(milestoneId, hasPlanFile) {
	const currentDb = _getAdapter();
	if (!currentDb) return;
	const rows = currentDb
		.prepare(
			`SELECT id FROM slices WHERE milestone_id = :mid AND is_sketch = 1`,
		)
		.all({ ":mid": milestoneId });
	for (const row of rows) {
		if (hasPlanFile(row.id)) {
			setSliceSketchFlag(milestoneId, row.id, false);
		}
	}
}

export function upsertSlicePlanning(milestoneId, sliceId, planning) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	insertSliceSpecIfAbsent(milestoneId, sliceId, planning);
	currentDb
		.prepare(`UPDATE slices SET
      goal = COALESCE(:goal, goal),
      success_criteria = COALESCE(:success_criteria, success_criteria),
      proof_level = COALESCE(:proof_level, proof_level),
      integration_closure = COALESCE(:integration_closure, integration_closure),
      observability_impact = COALESCE(:observability_impact, observability_impact),
      adversarial_partner = COALESCE(:adversarial_partner, adversarial_partner),
      adversarial_combatant = COALESCE(:adversarial_combatant, adversarial_combatant),
      adversarial_architect = COALESCE(:adversarial_architect, adversarial_architect),
      planning_meeting_json = COALESCE(:planning_meeting_json, planning_meeting_json),
      traces_vision_fragment = COALESCE(:traces_vision_fragment, traces_vision_fragment)
     WHERE milestone_id = :milestone_id AND id = :id`)
		.run({
			":milestone_id": milestoneId,
			":id": sliceId,
			":goal": planning.goal ?? null,
			":success_criteria": planning.successCriteria ?? null,
			":proof_level": planning.proofLevel ?? null,
			":integration_closure": planning.integrationClosure ?? null,
			":observability_impact": planning.observabilityImpact ?? null,
			":adversarial_partner": planning.adversarialReview?.partner ?? null,
			":adversarial_combatant": planning.adversarialReview?.combatant ?? null,
			":adversarial_architect": planning.adversarialReview?.architect ?? null,
			":planning_meeting_json": planning.planningMeeting
				? JSON.stringify(planning.planningMeeting)
				: null,
			// ADR-0000 P2 (schema v69): vision trace fragment is part of planning.
			":traces_vision_fragment": planning.tracesVisionFragment ?? null,
		});
}

// ADR-0000 P2 (schema v69): focused setter so callers that already have a
// validated fragment (e.g. validate-milestone backfilling legacy slices) can
// update just this column without rebuilding the full planning payload.
export function updateSliceVisionTrace(milestoneId, sliceId, fragment) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(
			"UPDATE slices SET traces_vision_fragment = :fragment WHERE milestone_id = :mid AND id = :sid",
		)
		.run({
			":fragment": fragment ?? null,
			":mid": milestoneId,
			":sid": sliceId,
		});
}

export function getSlice(milestoneId, sliceId) {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	const row = currentDb
		.prepare("SELECT * FROM slices WHERE milestone_id = :mid AND id = :sid")
		.get({ ":mid": milestoneId, ":sid": sliceId });
	if (!row) return null;
	return rowToSlice(row);
}

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

export function setSliceUatVerdict(milestoneId, sliceId, verdict) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(
			`UPDATE slices SET uat_verdict = :verdict WHERE milestone_id = :mid AND id = :sid`,
		)
		.run({ ":mid": milestoneId, ":sid": sliceId, ":verdict": verdict });
}

export function getSliceUatVerdict(milestoneId, sliceId) {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	const row = currentDb
		.prepare(
			`SELECT uat_verdict FROM slices WHERE milestone_id = :mid AND id = :sid`,
		)
		.get({ ":mid": milestoneId, ":sid": sliceId });
	return row?.uat_verdict ?? null;
}

export function backfillUatVerdicts(basePath) {
	const currentDb = _getAdapter();
	if (!currentDb) return;
	// Find all slices that have no verdict yet
	const rows = currentDb
		.prepare(`SELECT milestone_id, id FROM slices WHERE uat_verdict IS NULL`)
		.all();
	if (!rows.length) return;
	// Extract verdict from content — inline to avoid cross-module import at db layer
	function parseVerdictFromContent(content) {
		const fmMatch = content.match(/^---\n([\s\S]*?)\n---/);
		if (fmMatch) {
			const m = fmMatch[1].match(/verdict:\s*([\w-]+)/i);
			if (m) {
				let v = m[1].toLowerCase();
				if (v === "passed") v = "pass";
				return v;
			}
			return null;
		}
		const bodyMatch = content.match(
			/\*\*Verdict:?\*\*\s*(?:✅\s*)?(\w[\w-]*)/i,
		);
		if (bodyMatch) {
			let v = bodyMatch[1].toLowerCase();
			if (v === "passed") v = "pass";
			return v;
		}
		return null;
	}
	const stmt = currentDb.prepare(
		`UPDATE slices SET uat_verdict = :verdict WHERE milestone_id = :mid AND id = :sid`,
	);
	for (const row of rows) {
		const mid = row["milestone_id"];
		const sid = row["id"];
		const sliceDir = join(basePath, ".sf", "milestones", mid, "slices", sid);
		const candidates = [
			join(sliceDir, `${sid}-ASSESSMENT.md`),
			join(sliceDir, `${sid}-UAT_RESULT.md`),
		];
		for (const candidatePath of candidates) {
			if (!existsSync(candidatePath)) continue;
			try {
				const content = readFileSync(candidatePath, "utf8");
				const verdict = parseVerdictFromContent(content);
				if (verdict) {
					stmt.run({ ":mid": mid, ":sid": sid, ":verdict": verdict });
					break;
				}
			} catch {
				// Skip unreadable files
			}
		}
	}
}

export function setSliceSummaryMd(milestoneId, sliceId, summaryMd, uatMd) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(
			`UPDATE slices SET full_summary_md = :summary_md, full_uat_md = :uat_md WHERE milestone_id = :mid AND id = :sid`,
		)
		.run({
			":mid": milestoneId,
			":sid": sliceId,
			":summary_md": summaryMd,
			":uat_md": uatMd,
		});
}

export function getMilestoneSlices(milestoneId) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	const rows = currentDb
		.prepare(
			"SELECT * FROM slices WHERE milestone_id = :mid ORDER BY sequence, id",
		)
		.all({ ":mid": milestoneId });
	return rows.map(rowToSlice);
}

export function getSliceStatusSummary(milestoneId) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	return currentDb
		.prepare(
			"SELECT id, status FROM slices WHERE milestone_id = :mid ORDER BY sequence, id",
		)
		.all({ ":mid": milestoneId })
		.map((r) => ({ id: r["id"], status: r["status"] }));
}

export function getSliceTaskCounts(milestoneId, sliceId) {
	const currentDb = _getAdapter();
	if (!currentDb) return { total: 0, done: 0, pending: 0 };
	const row = currentDb
		.prepare(`SELECT
       COUNT(*) as total,
       SUM(CASE WHEN status IN ('complete', 'done') THEN 1 ELSE 0 END) as done,
       SUM(CASE WHEN status NOT IN ('complete', 'done') THEN 1 ELSE 0 END) as pending
     FROM tasks WHERE milestone_id = :mid AND slice_id = :sid`)
		.get({ ":mid": milestoneId, ":sid": sliceId });
	if (!row) return { total: 0, done: 0, pending: 0 };
	return {
		total: row["total"] ?? 0,
		done: row["done"] ?? 0,
		pending: row["pending"] ?? 0,
	};
}

export function syncSliceDependencies(milestoneId, sliceId, depends) {
	const currentDb = _getAdapter();
	if (!currentDb) return;
	currentDb
		.prepare(
			"DELETE FROM slice_dependencies WHERE milestone_id = :mid AND slice_id = :sid",
		)
		.run({ ":mid": milestoneId, ":sid": sliceId });
	for (const dep of depends) {
		currentDb
			.prepare(
				"INSERT OR IGNORE INTO slice_dependencies (milestone_id, slice_id, depends_on_slice_id) VALUES (:mid, :sid, :dep)",
			)
			.run({ ":mid": milestoneId, ":sid": sliceId, ":dep": dep });
	}
}

export function getDependentSlices(milestoneId, sliceId) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	return currentDb
		.prepare(
			"SELECT slice_id FROM slice_dependencies WHERE milestone_id = :mid AND depends_on_slice_id = :sid",
		)
		.all({ ":mid": milestoneId, ":sid": sliceId })
		.map((r) => r["slice_id"]);
}

export function getActiveSliceFromDb(milestoneId) {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	// Find the first non-complete slice whose dependencies are all satisfied.
	// Primary: uses the slice_dependencies junction table (kept in sync by syncSliceDependencies).
	// Fallback: for slices with no junction rows, check the `depends` JSON column directly
	// to handle legacy data or rows that were written before syncSliceDependencies ran.
	const candidates = currentDb
		.prepare(`SELECT s.* FROM slices s
     WHERE s.milestone_id = :mid
       AND s.status NOT IN ('complete', 'done', 'skipped')
       AND NOT EXISTS (
         SELECT 1 FROM slice_dependencies d
         WHERE d.milestone_id = :mid
           AND d.slice_id = s.id
           AND d.depends_on_slice_id NOT IN (
             SELECT id FROM slices WHERE milestone_id = :mid AND status IN ('complete', 'done', 'skipped')
           )
       )
     ORDER BY s.sequence, s.id`)
		.all({ ":mid": milestoneId });
	if (candidates.length === 0) return null;
	// Collect completed slice IDs for JSON-dep fallback check.
	const completedIds = new Set(
		currentDb
			.prepare(
				"SELECT id FROM slices WHERE milestone_id = :mid AND status IN ('complete', 'done', 'skipped')",
			)
			.all({ ":mid": milestoneId })
			.map((r) => r["id"]),
	);
	for (const candidate of candidates) {
		const hasSyncedDeps =
			(currentDb
				.prepare(
					"SELECT COUNT(*) as c FROM slice_dependencies WHERE milestone_id = :mid AND slice_id = :sid",
				)
				.get({ ":mid": milestoneId, ":sid": candidate["id"] })?.c ?? 0) > 0;
		if (hasSyncedDeps) {
			// Junction table is authoritative and candidate already passed the NOT EXISTS check.
			return rowToSlice(candidate);
		}
		// No junction rows for this slice — fall back to JSON depends column.
		const jsonDeps = safeParseJsonArray(candidate["depends"]);
		if (jsonDeps.length === 0 || jsonDeps.every((d) => completedIds.has(d))) {
			return rowToSlice(candidate);
		}
		// JSON deps not yet satisfied — continue to next candidate.
	}
	return null;
}

export function updateSliceFields(milestoneId, sliceId, fields) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`UPDATE slices SET
      title = COALESCE(:title, title),
      risk = COALESCE(:risk, risk),
      depends = COALESCE(:depends, depends),
      demo = COALESCE(:demo, demo)
     WHERE milestone_id = :milestone_id AND id = :id`)
		.run({
			":milestone_id": milestoneId,
			":id": sliceId,
			":title": fields.title ?? null,
			":risk": fields.risk ?? null,
			":depends": fields.depends ? JSON.stringify(fields.depends) : null,
			":demo": fields.demo ?? null,
		});
}

export function setSliceReplanTriggeredAt(milestoneId, sliceId, ts) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(
			"UPDATE slices SET replan_triggered_at = :ts WHERE milestone_id = :mid AND id = :sid",
		)
		.run({ ":ts": ts, ":mid": milestoneId, ":sid": sliceId });
}

export function deleteSlice(milestoneId, sliceId) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	transaction(() => {
		// Cascade-style manual deletion: evidence → tasks → dependencies → slice
		currentDb
			.prepare(
				`DELETE FROM verification_evidence WHERE milestone_id = :mid AND slice_id = :sid`,
			)
			.run({ ":mid": milestoneId, ":sid": sliceId });
		currentDb
			.prepare(
				`DELETE FROM tasks WHERE milestone_id = :mid AND slice_id = :sid`,
			)
			.run({ ":mid": milestoneId, ":sid": sliceId });
		currentDb
			.prepare(
				`DELETE FROM slice_dependencies WHERE milestone_id = :mid AND slice_id = :sid`,
			)
			.run({ ":mid": milestoneId, ":sid": sliceId });
		currentDb
			.prepare(
				`DELETE FROM slice_dependencies WHERE milestone_id = :mid AND depends_on_slice_id = :sid`,
			)
			.run({ ":mid": milestoneId, ":sid": sliceId });
		currentDb
			.prepare(`DELETE FROM slices WHERE milestone_id = :mid AND id = :sid`)
			.run({ ":mid": milestoneId, ":sid": sliceId });
	});
}
