/**
 * UOK Execution Graph Persistence
 *
 * Purpose: persist execution graph snapshots and task state to SQLite so the
 * /tasks surface can query background work without parsing JSON files. Graph
 * state is the authority; JSON files under .sf/runtime are read-only
 * projections for recovery and external tools.
 *
 * Consumer: ExecutionGraphSchedulerV2, /tasks command, and UOK kernel
 * background-work tracking.
 */

import {
	normalizeTaskState,
	TASK_STATES,
	TASK_TERMINAL_STATES,
} from "./task-state.js";

const TASK_STATE_QUERY_ALIASES = {
	done: ["done", "completed", "complete"],
	reviewing: ["reviewing", "review", "manual-attention", "manual_attention"],
	running: ["running", "in_progress", "progress"],
	todo: ["todo", "queued", "claimed", "pending"],
	verifying: ["verifying", "validating"],
};

function expandTaskStateFilters(states) {
	const expanded = new Set();
	for (const state of states) {
		const normalized = normalizeTaskState(state);
		expanded.add(normalized);
		expanded.add(state);
		for (const alias of TASK_STATE_QUERY_ALIASES[normalized] ?? []) {
			expanded.add(alias);
		}
	}
	return [...expanded].filter(Boolean);
}

/**
 * Ensure the execution graph tables exist.
 *
 * Purpose: idempotent schema setup so the scheduler can write task state
 * without knowing the full DB schema.
 */
export function ensureExecutionGraphSchema(db) {
	db.exec(`
    CREATE TABLE IF NOT EXISTS uok_execution_graphs (
      id            TEXT PRIMARY KEY,
      milestone_id  TEXT,
      session_id    TEXT,
      phase         TEXT,
      node_count    INTEGER,
      created_at    TEXT NOT NULL,
      completed_at  TEXT,
      status        TEXT DEFAULT 'active'
    );

    CREATE TABLE IF NOT EXISTS uok_graph_nodes (
      id              TEXT NOT NULL,
      graph_id        TEXT NOT NULL,
      kind            TEXT NOT NULL,
      unit_type       TEXT,
      unit_id         TEXT,
      milestone_id    TEXT,
      slice_id        TEXT,
      task_id         TEXT,
      title           TEXT,
      depends_on      TEXT, -- JSON array of node IDs
      writes          TEXT, -- JSON array of file paths
      status          TEXT DEFAULT 'todo',
      worker_id       TEXT,
      started_at      TEXT,
      ended_at        TEXT,
      duration_ms     INTEGER,
      cost_usd        REAL,
      model_id        TEXT,
      gate_results    TEXT, -- JSON array
      error           TEXT,
      updated_at      TEXT NOT NULL,
      PRIMARY KEY (graph_id, id)
    );

    CREATE INDEX IF NOT EXISTS idx_graph_nodes_graph ON uok_graph_nodes(graph_id);
    CREATE INDEX IF NOT EXISTS idx_graph_nodes_status ON uok_graph_nodes(status);
    CREATE INDEX IF NOT EXISTS idx_graph_nodes_unit ON uok_graph_nodes(unit_type, unit_id);
    CREATE INDEX IF NOT EXISTS idx_graph_nodes_milestone ON uok_graph_nodes(milestone_id);

    CREATE TABLE IF NOT EXISTS uok_graph_progress (
      id        INTEGER PRIMARY KEY AUTOINCREMENT,
      graph_id  TEXT NOT NULL,
      node_id   TEXT,
      type      TEXT NOT NULL,
      payload   TEXT, -- JSON
      ts        TEXT NOT NULL
    );

    CREATE INDEX IF NOT EXISTS idx_graph_progress_graph ON uok_graph_progress(graph_id);
  `);
}

/**
 * Insert or update a graph snapshot.
 */
export function persistGraphSnapshot(db, snapshot) {
	ensureExecutionGraphSchema(db);

	const {
		id,
		milestoneId,
		sessionId,
		phase,
		nodeCount,
		createdAt,
		status = "active",
	} = snapshot;

	db.prepare(
		`
    INSERT INTO uok_execution_graphs (id, milestone_id, session_id, phase, node_count, created_at, status)
    VALUES (:id, :milestoneId, :sessionId, :phase, :nodeCount, :createdAt, :status)
    ON CONFLICT(id) DO UPDATE SET
      milestone_id = COALESCE(excluded.milestone_id, milestone_id),
      session_id = COALESCE(excluded.session_id, session_id),
      phase = COALESCE(excluded.phase, phase),
      node_count = COALESCE(excluded.node_count, node_count),
      status = excluded.status,
      completed_at = CASE
        WHEN excluded.status IN ('completed','cancelled','failed') THEN COALESCE(completed_at, datetime('now'))
        ELSE completed_at
      END
  `,
	).run({
		id,
		milestoneId: milestoneId ?? null,
		sessionId: sessionId ?? null,
		phase: phase ?? null,
		nodeCount: nodeCount ?? null,
		createdAt,
		status,
	});
}

/**
 * Insert or update a graph node (task record).
 */
export function persistGraphNode(db, graphId, node) {
	ensureExecutionGraphSchema(db);

	const {
		id,
		kind = "unit",
		unitType,
		unitId,
		milestoneId,
		sliceId,
		taskId,
		title,
		dependsOn = [],
		writes = [],
		state = "todo",
		workerId,
		startedAt,
		endedAt,
		durationMs,
		costUsd,
		modelId,
		gateResults = [],
		error,
	} = node;
	const normalizedState = normalizeTaskState(state);

	db.prepare(
		`
    INSERT INTO uok_graph_nodes (
      id, graph_id, kind, unit_type, unit_id, milestone_id, slice_id, task_id,
      title, depends_on, writes, status, worker_id, started_at, ended_at,
      duration_ms, cost_usd, model_id, gate_results, error, updated_at
    ) VALUES (
      :id, :graphId, :kind, :unitType, :unitId, :milestoneId, :sliceId, :taskId,
      :title, :dependsOn, :writes, :state, :workerId, :startedAt, :endedAt,
      :durationMs, :costUsd, :modelId, :gateResults, :error, datetime('now')
    )
    ON CONFLICT(graph_id, id) DO UPDATE SET
      kind = excluded.kind,
      unit_type = COALESCE(excluded.unit_type, unit_type),
      unit_id = COALESCE(excluded.unit_id, unit_id),
      milestone_id = COALESCE(excluded.milestone_id, milestone_id),
      slice_id = COALESCE(excluded.slice_id, slice_id),
      task_id = COALESCE(excluded.task_id, task_id),
      title = COALESCE(excluded.title, title),
      depends_on = excluded.depends_on,
      writes = excluded.writes,
      status = excluded.status,
      worker_id = excluded.worker_id,
      started_at = COALESCE(excluded.started_at, started_at),
      ended_at = excluded.ended_at,
      duration_ms = excluded.duration_ms,
      cost_usd = excluded.cost_usd,
      model_id = excluded.model_id,
      gate_results = excluded.gate_results,
      error = excluded.error,
      updated_at = excluded.updated_at
  `,
	).run({
		id,
		graphId,
		kind,
		unitType: unitType ?? null,
		unitId: unitId ?? null,
		milestoneId: milestoneId ?? null,
		sliceId: sliceId ?? null,
		taskId: taskId ?? null,
		title: title ?? null,
		dependsOn: JSON.stringify(dependsOn),
		writes: JSON.stringify(writes),
		state: normalizedState,
		workerId: workerId ?? null,
		startedAt: startedAt ?? null,
		endedAt: endedAt ?? null,
		durationMs: durationMs ?? null,
		costUsd: costUsd ?? null,
		modelId: modelId ?? null,
		gateResults: JSON.stringify(gateResults),
		error: error ?? null,
	});
}

/**
 * Record a progress event.
 */
export function persistProgressEvent(db, graphId, event) {
	ensureExecutionGraphSchema(db);
	let payload = event.payload ?? event;
	if (typeof payload === "string") {
		try {
			payload = JSON.parse(payload);
		} catch {
			payload = { text: payload };
		}
	}

	db.prepare(
		`
    INSERT INTO uok_graph_progress (graph_id, node_id, type, payload, ts)
    VALUES (:graphId, :nodeId, :type, :payload, :ts)
  `,
	).run({
		graphId,
		nodeId: event.nodeId ?? null,
		type: event.type,
		payload: JSON.stringify(payload),
		ts: event.ts ?? new Date().toISOString(),
	});
}

/**
 * Query tasks by state for /tasks display.
 */
export function queryTasksByState(db, filters = {}) {
	const {
		graphId,
		milestoneId,
		sliceId,
		states = [],
		limit = 100,
		offset = 0,
	} = filters;

	const conditions = ["1=1"];
	const params = {};

	if (graphId) {
		conditions.push("graph_id = :graphId");
		params.graphId = graphId;
	}
	if (milestoneId) {
		conditions.push("milestone_id = :milestoneId");
		params.milestoneId = milestoneId;
	}
	if (sliceId) {
		conditions.push("slice_id = :sliceId");
		params.sliceId = sliceId;
	}
	if (states.length > 0) {
		const stateFilters = expandTaskStateFilters(states);
		conditions.push(
			`status IN (${stateFilters.map((_, i) => `:s${i}`).join(", ")})`,
		);
		for (let i = 0; i < stateFilters.length; i++) {
			params[`s${i}`] = stateFilters[i];
		}
	}

	const where = conditions.join(" AND ");

	const rows = db
		.prepare(
			`
    SELECT * FROM uok_graph_nodes
    WHERE ${where}
    ORDER BY updated_at DESC
    LIMIT :limit OFFSET :offset
  `,
		)
		.all({ ...params, limit, offset });

	return rows.map((r) => ({
		...r,
		status: normalizeTaskState(r.status),
		dependsOn: r.depends_on ? JSON.parse(r.depends_on) : [],
		writes: r.writes ? JSON.parse(r.writes) : [],
		gateResults: r.gate_results ? JSON.parse(r.gate_results) : [],
	}));
}

/**
 * Get aggregate counts by state for a graph.
 */
export function getGraphStateSummary(db, graphId) {
	const rows = db
		.prepare(
			`
    SELECT status, COUNT(*) as count
    FROM uok_graph_nodes
    WHERE graph_id = :graphId
    GROUP BY status
  `,
		)
		.all({ graphId });

	const counts = Object.fromEntries(TASK_STATES.map((s) => [s, 0]));
	let total = 0;
	for (const r of rows) {
		const status = normalizeTaskState(r.status);
		counts[status] = (counts[status] ?? 0) + r.count;
		total += r.count;
	}

	const terminal = TASK_STATES.filter((s) =>
		TASK_TERMINAL_STATES.has(s),
	).reduce((sum, s) => sum + (counts[s] ?? 0), 0);

	return {
		graphId,
		counts,
		total,
		terminal,
		progress: total > 0 ? Math.round((terminal / total) * 100) : 0,
		isComplete: terminal === total && total > 0,
	};
}

/**
 * Get recent progress events for a graph.
 */
export function getGraphProgressStream(db, graphId, limit = 50) {
	return db
		.prepare(
			`
    SELECT * FROM uok_graph_progress
    WHERE graph_id = :graphId
    ORDER BY ts DESC
    LIMIT :limit
  `,
		)
		.all({ graphId, limit })
		.map((r) => ({
			...r,
			payload: r.payload ? JSON.parse(r.payload) : null,
		}));
}

/**
 * Convenience: persist an entire graph with all nodes in one transaction.
 */
export function persistFullGraph(db, graphId, nodes, meta = {}) {
	db.exec("BEGIN IMMEDIATE");
	try {
		persistGraphSnapshot(db, {
			id: graphId,
			milestoneId: meta.milestoneId,
			sessionId: meta.sessionId,
			phase: meta.phase,
			nodeCount: nodes.length,
			createdAt: new Date().toISOString(),
			status: meta.status ?? "active",
		});

		for (const node of nodes) {
			persistGraphNode(db, graphId, node);
		}
		db.exec("COMMIT");
	} catch (err) {
		try {
			db.exec("ROLLBACK");
		} catch {
			// Preserve the original persistence failure.
		}
		throw err;
	}
}
