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

export function insertVerificationEvidence(e) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT OR IGNORE INTO verification_evidence (task_id, slice_id, milestone_id, command, exit_code, verdict, duration_ms, created_at)
     VALUES (:task_id, :slice_id, :milestone_id, :command, :exit_code, :verdict, :duration_ms, :created_at)`)
		.run({
			":task_id": e.taskId,
			":slice_id": e.sliceId,
			":milestone_id": e.milestoneId,
			":command": e.command,
			":exit_code": e.exitCode,
			":verdict": e.verdict,
			":duration_ms": e.durationMs,
			":created_at": new Date().toISOString(),
		});
}

export function getVerificationEvidence(milestoneId, sliceId, taskId) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	const rows = currentDb
		.prepare(
			"SELECT * FROM verification_evidence WHERE milestone_id = :mid AND slice_id = :sid AND task_id = :tid ORDER BY id",
		)
		.all({ ":mid": milestoneId, ":sid": sliceId, ":tid": taskId });
	return rows;
}

export function deleteVerificationEvidence(milestoneId, sliceId, taskId) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	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 });
}

export function insertMilestoneEvidence(
	milestoneId,
	evidenceType,
	content,
	phaseName,
	recordedBy,
) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT INTO milestone_evidence (milestone_id, evidence_type, content, recorded_at, phase_name, recorded_by)
       VALUES (?, ?, ?, ?, ?, ?)`)
		.run(
			milestoneId,
			evidenceType,
			content,
			new Date().toISOString(),
			phaseName || "",
			recordedBy || "",
		);
}

export function insertSliceEvidence(
	milestoneId,
	sliceId,
	evidenceType,
	content,
	phaseName,
	recordedBy,
) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT INTO slice_evidence (milestone_id, slice_id, evidence_type, content, recorded_at, phase_name, recorded_by)
       VALUES (?, ?, ?, ?, ?, ?, ?)`)
		.run(
			milestoneId,
			sliceId,
			evidenceType,
			content,
			new Date().toISOString(),
			phaseName || "",
			recordedBy || "",
		);
}

export function insertTaskEvidence(
	milestoneId,
	sliceId,
	taskId,
	evidenceType,
	content,
	phaseName,
	recordedBy,
) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT INTO task_evidence (milestone_id, slice_id, task_id, evidence_type, content, recorded_at, phase_name, recorded_by)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`)
		.run(
			milestoneId,
			sliceId,
			taskId,
			evidenceType,
			content,
			new Date().toISOString(),
			phaseName || "",
			recordedBy || "",
		);
}

export function getMilestoneAuditTrail(milestoneId) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	return currentDb
		.prepare(`
       SELECT 
         r.id, r.title, r.status,
         s.vision, s.spec_version,
         e.evidence_type, e.content, e.recorded_at, e.phase_name, e.recorded_by
       FROM milestones r
       LEFT JOIN milestone_specs s ON r.id = s.id
       LEFT JOIN milestone_evidence e ON r.id = e.milestone_id
       WHERE r.id = ?
       ORDER BY e.recorded_at ASC
     `)
		.all(milestoneId);
}

export function getSliceAuditTrail(milestoneId, sliceId) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	return currentDb
		.prepare(`
       SELECT 
         r.id, r.title, r.status,
         s.goal, s.spec_version,
         e.evidence_type, e.content, e.recorded_at, e.phase_name, e.recorded_by
       FROM slices r
       LEFT JOIN slice_specs s ON r.milestone_id = s.milestone_id AND r.id = s.slice_id
       LEFT JOIN slice_evidence e ON r.milestone_id = e.milestone_id AND r.id = e.slice_id
       WHERE r.milestone_id = ? AND r.id = ?
       ORDER BY e.recorded_at ASC
     `)
		.all(milestoneId, sliceId);
}

export function getTaskAuditTrail(milestoneId, sliceId, taskId) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	return currentDb
		.prepare(`
       SELECT 
         r.id, r.title, r.status,
         s.verify, s.spec_version,
         e.evidence_type, e.content, e.recorded_at, e.phase_name, e.recorded_by
       FROM tasks r
       LEFT JOIN task_specs s ON r.milestone_id = s.milestone_id AND r.slice_id = s.slice_id AND r.id = s.task_id
       LEFT JOIN task_evidence e ON r.milestone_id = e.milestone_id AND r.slice_id = e.slice_id AND r.id = e.task_id
       WHERE r.milestone_id = ? AND r.slice_id = ? AND r.id = ?
       ORDER BY e.recorded_at ASC
     `)
		.all(milestoneId, sliceId, taskId);
}

export function insertAssessment(entry) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT OR REPLACE INTO assessments (path, milestone_id, slice_id, task_id, status, scope, full_content, created_at)
     VALUES (:path, :milestone_id, :slice_id, :task_id, :status, :scope, :full_content, :created_at)`)
		.run({
			":path": entry.path,
			":milestone_id": entry.milestoneId,
			":slice_id": entry.sliceId ?? null,
			":task_id": entry.taskId ?? null,
			":status": entry.status,
			":scope": entry.scope,
			":full_content": entry.fullContent,
			":created_at": new Date().toISOString(),
		});
}

export function deleteAssessmentByScope(milestoneId, scope) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(
			`DELETE FROM assessments WHERE milestone_id = :mid AND scope = :scope`,
		)
		.run({ ":mid": milestoneId, ":scope": scope });
}

export function getAssessment(path) {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	const row = currentDb
		.prepare(`SELECT * FROM assessments WHERE path = :path`)
		.get({ ":path": path });
	return row ?? null;
}

export function getAssessmentByScope(milestoneId, scope) {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	const row = currentDb
		.prepare(
			`SELECT * FROM assessments
			 WHERE milestone_id = :mid AND scope = :scope
			 ORDER BY created_at DESC
			 LIMIT 1`,
		)
		.get({ ":mid": milestoneId, ":scope": scope });
	return row ?? null;
}

export function getMilestoneValidationAssessment(milestoneId) {
	return getAssessmentByScope(milestoneId, "milestone-validation");
}

export function insertReplanHistory(entry) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	// INSERT OR REPLACE: idempotent on (milestone_id, slice_id, task_id) via schema v11 unique index.
	// Retrying the same replan silently updates summary instead of accumulating duplicate rows.
	currentDb
		.prepare(`INSERT OR REPLACE INTO replan_history (milestone_id, slice_id, task_id, summary, previous_artifact_path, replacement_artifact_path, created_at)
     VALUES (:milestone_id, :slice_id, :task_id, :summary, :previous_artifact_path, :replacement_artifact_path, :created_at)`)
		.run({
			":milestone_id": entry.milestoneId,
			":slice_id": entry.sliceId ?? null,
			":task_id": entry.taskId ?? null,
			":summary": entry.summary,
			":previous_artifact_path": entry.previousArtifactPath ?? null,
			":replacement_artifact_path": entry.replacementArtifactPath ?? null,
			":created_at": new Date().toISOString(),
		});
}

export function getReplanHistory(milestoneId, sliceId) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	if (sliceId) {
		return currentDb
			.prepare(
				`SELECT * FROM replan_history WHERE milestone_id = :mid AND slice_id = :sid ORDER BY created_at DESC`,
			)
			.all({ ":mid": milestoneId, ":sid": sliceId });
	}
	return currentDb
		.prepare(
			`SELECT * FROM replan_history WHERE milestone_id = :mid ORDER BY created_at DESC`,
		)
		.all({ ":mid": milestoneId });
}
