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

export function insertDecision(d) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT INTO decisions (id, when_context, scope, decision, choice, rationale, revisable, made_by, superseded_by)
     VALUES (:id, :when_context, :scope, :decision, :choice, :rationale, :revisable, :made_by, :superseded_by)`)
		.run({
			":id": d.id,
			":when_context": d.when_context,
			":scope": d.scope,
			":decision": d.decision,
			":choice": d.choice,
			":rationale": d.rationale,
			":revisable": d.revisable,
			":made_by": d.made_by ?? "agent",
			":superseded_by": d.superseded_by,
		});
}

export function getDecisionById(id) {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	const row = currentDb.prepare("SELECT * FROM decisions WHERE id = ?").get(id);
	if (!row) return null;
	return {
		seq: row["seq"],
		id: row["id"],
		when_context: row["when_context"],
		scope: row["scope"],
		decision: row["decision"],
		choice: row["choice"],
		rationale: row["rationale"],
		revisable: row["revisable"],
		made_by: row["made_by"] ?? "agent",
		superseded_by: row["superseded_by"] ?? null,
	};
}

export function getActiveDecisions() {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	const rows = currentDb.prepare("SELECT * FROM active_decisions").all();
	return rows.map((row) => ({
		seq: row["seq"],
		id: row["id"],
		when_context: row["when_context"],
		scope: row["scope"],
		decision: row["decision"],
		choice: row["choice"],
		rationale: row["rationale"],
		revisable: row["revisable"],
		made_by: row["made_by"] ?? "agent",
		superseded_by: null,
	}));
}

export function upsertDecision(d) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	// Use ON CONFLICT DO UPDATE instead of INSERT OR REPLACE to preserve the
	// seq column. INSERT OR REPLACE deletes then reinserts, resetting seq and
	// corrupting decision ordering in DECISIONS.md after reconcile replay.
	currentDb
		.prepare(`INSERT INTO decisions (id, when_context, scope, decision, choice, rationale, revisable, made_by, superseded_by)
     VALUES (:id, :when_context, :scope, :decision, :choice, :rationale, :revisable, :made_by, :superseded_by)
     ON CONFLICT(id) DO UPDATE SET
       when_context = excluded.when_context,
       scope = excluded.scope,
       decision = excluded.decision,
       choice = excluded.choice,
       rationale = excluded.rationale,
       revisable = excluded.revisable,
       made_by = excluded.made_by,
       superseded_by = excluded.superseded_by`)
		.run({
			":id": d.id,
			":when_context": d.when_context,
			":scope": d.scope,
			":decision": d.decision,
			":choice": d.choice,
			":rationale": d.rationale,
			":revisable": d.revisable,
			":made_by": d.made_by ?? "agent",
			":superseded_by": d.superseded_by ?? null,
		});
}

export function deleteDecisionById(id) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb.prepare("DELETE FROM decisions WHERE id = :id").run({ ":id": id });
}

export function insertRequirement(r) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT INTO requirements (id, class, status, description, why, source, primary_owner, supporting_slices, validation, notes, full_content, superseded_by)
     VALUES (:id, :class, :status, :description, :why, :source, :primary_owner, :supporting_slices, :validation, :notes, :full_content, :superseded_by)`)
		.run({
			":id": r.id,
			":class": r.class,
			":status": r.status,
			":description": r.description,
			":why": r.why,
			":source": r.source,
			":primary_owner": r.primary_owner,
			":supporting_slices": r.supporting_slices,
			":validation": r.validation,
			":notes": r.notes,
			":full_content": r.full_content,
			":superseded_by": r.superseded_by,
		});
}

export function getRequirementById(id) {
	const currentDb = _getAdapter();
	if (!currentDb) return null;
	const row = currentDb
		.prepare("SELECT * FROM requirements WHERE id = ?")
		.get(id);
	if (!row) return null;
	return {
		id: row["id"],
		class: row["class"],
		status: row["status"],
		description: row["description"],
		why: row["why"],
		source: row["source"],
		primary_owner: row["primary_owner"],
		supporting_slices: row["supporting_slices"],
		validation: row["validation"],
		notes: row["notes"],
		full_content: row["full_content"],
		superseded_by: row["superseded_by"] ?? null,
	};
}

export function getActiveRequirements() {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	const rows = currentDb.prepare("SELECT * FROM active_requirements").all();
	return rows.map((row) => ({
		id: row["id"],
		class: row["class"],
		status: row["status"],
		description: row["description"],
		why: row["why"],
		source: row["source"],
		primary_owner: row["primary_owner"],
		supporting_slices: row["supporting_slices"],
		validation: row["validation"],
		notes: row["notes"],
		full_content: row["full_content"],
		superseded_by: null,
	}));
}

export function upsertRequirement(r) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(`INSERT OR REPLACE INTO requirements (id, class, status, description, why, source, primary_owner, supporting_slices, validation, notes, full_content, superseded_by)
     VALUES (:id, :class, :status, :description, :why, :source, :primary_owner, :supporting_slices, :validation, :notes, :full_content, :superseded_by)`)
		.run({
			":id": r.id,
			":class": r.class,
			":status": r.status,
			":description": r.description,
			":why": r.why,
			":source": r.source,
			":primary_owner": r.primary_owner,
			":supporting_slices": r.supporting_slices,
			":validation": r.validation,
			":notes": r.notes,
			":full_content": r.full_content,
			":superseded_by": r.superseded_by ?? null,
		});
}

export function deleteRequirementById(id) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare("DELETE FROM requirements WHERE id = :id")
		.run({ ":id": id });
}
