import { SF_STALE_STATE, SFError } from "../errors.js";
import { _getAdapter } from "./sf-db-core.js";

export function getMilestoneSpec(milestoneId) {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	return currentDb
		.prepare("SELECT * FROM milestone_specs WHERE id = ?")
		.get(milestoneId);
}

export function getSliceSpec(milestoneId, sliceId) {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	return currentDb
		.prepare(
			"SELECT * FROM slice_specs WHERE milestone_id = ? AND slice_id = ?",
		)
		.get(milestoneId, sliceId);
}

export function getTaskSpec(milestoneId, sliceId, taskId) {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	return currentDb
		.prepare(
			"SELECT * FROM task_specs WHERE milestone_id = ? AND slice_id = ? AND task_id = ?",
		)
		.get(milestoneId, sliceId, taskId);
}

export function startValidationRun({ milestoneId, sliceId, taskId, contract }) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const runId = crypto.randomUUID();
	currentDb
		.prepare(
			`INSERT INTO validation_runs
			 (run_id, milestone_id, slice_id, task_id, contract, status, started_at, created_at)
			 VALUES (:run_id, :milestone_id, :slice_id, :task_id, :contract, 'running', datetime('now'), datetime('now'))`,
		)
		.run({
			":run_id": runId,
			":milestone_id": milestoneId,
			":slice_id": sliceId ?? null,
			":task_id": taskId ?? null,
			":contract": contract ?? "",
		});
	return runId;
}

export function completeValidationRun({
	runId,
	verdict,
	rationale = "",
	findings = "",
}) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const status =
		verdict === "pass" ? "pass" : verdict === "fail" ? "fail" : "error";
	const result = currentDb
		.prepare(
			`UPDATE validation_runs SET
			 status = :status,
			 verdict = :verdict,
			 rationale = :rationale,
			 findings = :findings,
			 completed_at = datetime('now')
			 WHERE run_id = :run_id AND status = 'running'`,
		)
		.run({
			":run_id": runId,
			":status": status,
			":verdict": verdict ?? "",
			":rationale": rationale ?? "",
			":findings": findings ?? "",
		});
	if (result.changes === 0) {
		throw new SFError(
			SF_STALE_STATE,
			`sf-db: completeValidationRun: no running validation run found for run_id=${runId}`,
		);
	}
}

export function getLatestValidationState(milestoneId, sliceId, taskId) {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	const rows = currentDb
		.prepare(
			`SELECT * FROM validation_runs
			 WHERE milestone_id = :milestone_id
			   AND slice_id IS :slice_id
			   AND task_id IS :task_id
			 ORDER BY created_at DESC, run_id DESC
			 LIMIT 1`,
		)
		.all({
			":milestone_id": milestoneId,
			":slice_id": sliceId ?? null,
			":task_id": taskId ?? null,
		});
	return rows[0] ?? null;
}

export function getValidationHistory(milestoneId, sliceId, taskId, limit = 20) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	return currentDb
		.prepare(
			`SELECT * FROM validation_runs
			 WHERE milestone_id = :milestone_id
			   AND slice_id IS :slice_id
			   AND task_id IS :task_id
			 ORDER BY created_at DESC, run_id DESC
			 LIMIT :limit`,
		)
		.all({
			":milestone_id": milestoneId,
			":slice_id": sliceId ?? null,
			":task_id": taskId ?? null,
			":limit": limit,
		});
}

export function getValidationAttentionMarker(milestoneId) {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	return (
		currentDb
			.prepare(
				"SELECT * FROM validation_attention_markers WHERE milestone_id = ?",
			)
			.get(milestoneId) ?? null
	);
}

export function upsertValidationAttentionMarker(milestoneId, marker) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const now = new Date().toISOString();
	currentDb
		.prepare(
			`INSERT INTO validation_attention_markers
         (milestone_id, created_at, source, remediation_round, revalidation_round, revalidation_requested_at)
       VALUES (:milestone_id, :created_at, :source, :remediation_round, :revalidation_round, :revalidation_requested_at)
       ON CONFLICT(milestone_id) DO UPDATE SET
         source = excluded.source,
         remediation_round = excluded.remediation_round,
         revalidation_round = excluded.revalidation_round,
         revalidation_requested_at = excluded.revalidation_requested_at`,
		)
		.run({
			":milestone_id": milestoneId,
			":created_at": marker.createdAt ?? now,
			":source": marker.source ?? null,
			":remediation_round": marker.remediationRound ?? null,
			":revalidation_round": marker.revalidationRound ?? null,
			":revalidation_requested_at": marker.revalidationRequestedAt ?? null,
		});
}
