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

export function listBacklogItems() {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	return currentDb
		.prepare(
			"SELECT * FROM backlog_items ORDER BY CASE WHEN sequence > 0 THEN 0 ELSE 1 END, sequence, id",
		)
		.all()
		.map(rowToBacklogItem);
}

export function nextBacklogItemId() {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const row = currentDb
		.prepare(
			"SELECT id FROM backlog_items WHERE id LIKE '999.%' ORDER BY CAST(substr(id, 5) AS INTEGER) DESC LIMIT 1",
		)
		.get();
	const next = row?.id ? Number.parseInt(String(row.id).slice(4), 10) + 1 : 1;
	return `999.${Number.isFinite(next) ? next : 1}`;
}

export function addBacklogItem({
	id,
	title,
	note = "",
	source = "manual",
	triageRunId = null,
	status = "pending",
}) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const itemId = id ?? nextBacklogItemId();
	const now = new Date().toISOString();
	const sequenceRow = currentDb
		.prepare(
			"SELECT COALESCE(MAX(sequence), 0) + 1 AS sequence FROM backlog_items",
		)
		.get();
	currentDb
		.prepare(`INSERT INTO backlog_items (
      id, title, status, note, source, triage_run_id, sequence, created_at, updated_at, promoted_at
    ) VALUES (
      :id, :title, :status, :note, :source, :triage_run_id, :sequence, :created_at, :updated_at, :promoted_at
    )
    ON CONFLICT(id) DO UPDATE SET
      title = excluded.title,
      status = excluded.status,
      note = excluded.note,
      source = excluded.source,
      triage_run_id = excluded.triage_run_id,
      updated_at = excluded.updated_at,
      promoted_at = excluded.promoted_at`)
		.run({
			":id": itemId,
			":title": title,
			":status": status,
			":note": note,
			":source": source,
			":triage_run_id": triageRunId,
			":sequence": sequenceRow?.sequence ?? 1,
			":created_at": now,
			":updated_at": now,
			":promoted_at": status === "promoted" ? now : null,
		});
	return itemId;
}

export function updateBacklogItemStatus(id, status, note = "") {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const now = new Date().toISOString();
	const result = currentDb
		.prepare(`UPDATE backlog_items
      SET status = :status,
          note = :note,
          updated_at = :updated_at,
          promoted_at = CASE WHEN :status = 'promoted' THEN :updated_at ELSE promoted_at END
      WHERE id = :id`)
		.run({
			":id": id,
			":status": status,
			":note": note,
			":updated_at": now,
		});
	return (result?.changes ?? 0) > 0;
}

export function removeBacklogItem(id) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const result = currentDb
		.prepare("DELETE FROM backlog_items WHERE id = :id")
		.run({ ":id": id });
	return (result?.changes ?? 0) > 0;
}
