import { readTraceEvents } from "../uok/trace-writer.js";
import { logWarning } from "../workflow-logger.js";
import {
	_getAdapter,
	capErrorForStorage,
	parseJsonObject,
	rowToUnitMetrics,
} from "./sf-db-core.js";

export function recordUokRunStart(entry) {
	const currentDb = _getAdapter();
	if (!currentDb) return;
	const now = entry.startedAt ?? new Date().toISOString();
	currentDb
		.prepare(`INSERT INTO uok_runs (
      run_id, session_id, path, status, started_at, ended_at, error, flags_json, updated_at
    ) VALUES (
      :run_id, :session_id, :path, 'started', :started_at, NULL, NULL, :flags_json, :updated_at
    )
    ON CONFLICT(run_id) DO UPDATE SET
      session_id = excluded.session_id,
      path = excluded.path,
      status = 'started',
      started_at = excluded.started_at,
      ended_at = NULL,
      error = NULL,
      flags_json = excluded.flags_json,
      updated_at = excluded.updated_at`)
		.run({
			":run_id": entry.runId,
			":session_id": entry.sessionId ?? null,
			":path": entry.path ?? "",
			":started_at": now,
			":flags_json": JSON.stringify(entry.flags ?? {}),
			":updated_at": now,
		});
}

export function recordUokRunExit(entry) {
	const currentDb = _getAdapter();
	if (!currentDb) return;
	const now = entry.endedAt ?? new Date().toISOString();
	currentDb
		.prepare(`INSERT INTO uok_runs (
      run_id, session_id, path, status, started_at, ended_at, error, flags_json, updated_at
    ) VALUES (
      :run_id, :session_id, :path, :status, :started_at, :ended_at, :error, :flags_json, :updated_at
    )
    ON CONFLICT(run_id) DO UPDATE SET
      session_id = COALESCE(excluded.session_id, uok_runs.session_id),
      path = CASE WHEN excluded.path = '' THEN uok_runs.path ELSE excluded.path END,
      status = excluded.status,
      ended_at = excluded.ended_at,
      error = excluded.error,
      flags_json = CASE WHEN excluded.flags_json = '{}' THEN uok_runs.flags_json ELSE excluded.flags_json END,
      updated_at = excluded.updated_at`)
		.run({
			":run_id": entry.runId,
			":session_id": entry.sessionId ?? null,
			":path": entry.path ?? "",
			":status": entry.status ?? "ok",
			":started_at": entry.startedAt ?? now,
			":ended_at": now,
			":error": entry.error
				? capErrorForStorage(entry.error, entry.runId)
				: null,
			":flags_json": JSON.stringify(entry.flags ?? {}),
			":updated_at": now,
		});
}

export function getUokRuns(limit = 500) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	return currentDb
		.prepare(
			`SELECT run_id, session_id, path, status, started_at, ended_at, error, flags_json, updated_at
       FROM uok_runs
       ORDER BY started_at DESC
       LIMIT :limit`,
		)
		.all({ ":limit": limit })
		.map((row) => ({
			runId: row.run_id,
			sessionId: row.session_id,
			path: row.path,
			status: row.status,
			startedAt: row.started_at,
			endedAt: row.ended_at,
			error: row.error,
			flags: (() => {
				try {
					return JSON.parse(row.flags_json || "{}");
				} catch {
					return {};
				}
			})(),
			updatedAt: row.updated_at,
		}));
}

export function insertAuditEvent(_entry) {
	// no-op: audit events now written exclusively to JSONL files
}

export function insertUokMessage(msg) {
	const currentDb = _getAdapter();
	if (!currentDb) return;
	currentDb
		.prepare(
			`INSERT OR IGNORE INTO uok_messages (id, from_agent, to_agent, body, metadata_json, sent_at, delivered_at)
			 VALUES (:id, :from_agent, :to_agent, :body, :metadata_json, :sent_at, :delivered_at)`,
		)
		.run({
			":id": msg.id,
			":from_agent": msg.from,
			":to_agent": msg.to,
			":body": msg.body ?? "",
			":metadata_json": JSON.stringify(msg.metadata ?? {}),
			":sent_at": msg.sentAt,
			":delivered_at": msg.deliveredAt ?? null,
		});
}

export function getUokMessagesForAgent(
	agentId,
	limit = 1000,
	unreadOnly = false,
) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	try {
		let sql = `SELECT m.id, m.from_agent AS "from", m.to_agent AS "to", m.body, m.metadata_json AS metadataJson, m.sent_at AS sentAt, m.delivered_at AS deliveredAt,
					  CASE WHEN r.agent_id IS NOT NULL THEN 1 ELSE 0 END AS read
				   FROM uok_messages m
				   LEFT JOIN uok_message_reads r ON r.message_id = m.id AND r.agent_id = :agent_id
				   WHERE m.to_agent = :agent_id`;
		if (unreadOnly) {
			sql += " AND r.agent_id IS NULL";
		}
		sql += " ORDER BY m.sent_at ASC LIMIT :limit";
		const rows = currentDb.prepare(sql).all({
			":agent_id": agentId,
			":limit": Math.max(1, Math.min(10_000, Number(limit) || 1000)),
		});
		return rows.map((r) => ({
			id: r.id,
			from: r.from,
			to: r.to,
			body: r.body,
			metadata: parseJsonObject(r.metadataJson, {}),
			sentAt: r.sentAt,
			deliveredAt: r.deliveredAt,
			read: !!r.read,
		}));
	} catch {
		return [];
	}
}

export function getUokConversation(agentA, agentB, limit = 1000) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	try {
		const rows = currentDb
			.prepare(
				`SELECT id, from_agent AS "from", to_agent AS "to", body, metadata_json AS metadataJson, sent_at AS sentAt, delivered_at AS deliveredAt
				 FROM uok_messages
				 WHERE (from_agent = :a AND to_agent = :b) OR (from_agent = :b AND to_agent = :a)
				 ORDER BY sent_at DESC
				 LIMIT :limit`,
			)
			.all({ ":a": agentA, ":b": agentB, ":limit": limit });
		return rows.map((r) => ({
			id: r.id,
			from: r.from,
			to: r.to,
			body: r.body,
			metadata: parseJsonObject(r.metadataJson, {}),
			sentAt: r.sentAt,
			deliveredAt: r.deliveredAt,
		}));
	} catch {
		return [];
	}
}

export function markUokMessageRead(messageId, agentId) {
	const currentDb = _getAdapter();
	if (!currentDb) return false;
	try {
		currentDb
			.prepare(
				`INSERT OR IGNORE INTO uok_message_reads (message_id, agent_id, read_at) VALUES (:message_id, :agent_id, :read_at)`,
			)
			.run({
				":message_id": messageId,
				":agent_id": agentId,
				":read_at": new Date().toISOString(),
			});
		return true;
	} catch {
		return false;
	}
}

export function getUokMessageUnreadCount(agentId) {
	const currentDb = _getAdapter();
	if (!currentDb) return 0;
	try {
		const row = currentDb
			.prepare(
				`SELECT COUNT(*) AS cnt FROM uok_messages m
				 WHERE m.to_agent = :agent_id
				 AND NOT EXISTS (
				   SELECT 1 FROM uok_message_reads r
				   WHERE r.message_id = m.id AND r.agent_id = :agent_id
				 )`,
			)
			.get({ ":agent_id": agentId });
		return row?.cnt ?? 0;
	} catch {
		return 0;
	}
}

export function compactUokMessages(retentionDays) {
	const currentDb = _getAdapter();
	if (!currentDb) return { before: 0, after: 0 };
	try {
		const cutoff = new Date(
			Date.now() - retentionDays * 24 * 60 * 60 * 1000,
		).toISOString();
		const beforeRow = currentDb
			.prepare("SELECT COUNT(*) AS cnt FROM uok_messages")
			.get();
		currentDb
			.prepare("DELETE FROM uok_messages WHERE sent_at < :cutoff")
			.run({ ":cutoff": cutoff });
		const afterRow = currentDb
			.prepare("SELECT COUNT(*) AS cnt FROM uok_messages")
			.get();
		return { before: beforeRow?.cnt ?? 0, after: afterRow?.cnt ?? 0 };
	} catch {
		return { before: 0, after: 0 };
	}
}

export function getUokMessageReadIds(agentId) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	try {
		const rows = currentDb
			.prepare(
				"SELECT message_id FROM uok_message_reads WHERE agent_id = :agent_id",
			)
			.all({ ":agent_id": agentId });
		return rows.map((r) => r.message_id);
	} catch {
		return [];
	}
}

export function getUokMessageBusMetrics() {
	const currentDb = _getAdapter();
	if (!currentDb) {
		return {
			totalMessages: 0,
			totalUnread: 0,
			uniqueAgents: 0,
			uniqueConversations: 0,
		};
	}
	try {
		const totalRow = currentDb
			.prepare("SELECT COUNT(*) AS cnt FROM uok_messages")
			.get();
		const unreadRow = currentDb
			.prepare(
				`SELECT COUNT(*) AS cnt FROM uok_messages m
					 WHERE NOT EXISTS (
					   SELECT 1 FROM uok_message_reads r
					   WHERE r.message_id = m.id
					     AND r.agent_id = m.to_agent
					 )`,
			)
			.get();
		const agentsRow = currentDb
			.prepare(`SELECT COUNT(DISTINCT to_agent) AS cnt FROM uok_messages`)
			.get();
		const convRow = currentDb
			.prepare(
				`SELECT COUNT(DISTINCT from_agent || ':' || to_agent) AS cnt FROM uok_messages`,
			)
			.get();
		return {
			totalMessages: totalRow?.cnt ?? 0,
			totalUnread: unreadRow?.cnt ?? 0,
			uniqueAgents: agentsRow?.cnt ?? 0,
			uniqueConversations: convRow?.cnt ?? 0,
		};
	} catch {
		return {
			totalMessages: 0,
			totalUnread: 0,
			uniqueAgents: 0,
			uniqueConversations: 0,
		};
	}
}

export function upsertUnitMetrics(db, unit) {
	db.prepare(
		`INSERT OR REPLACE INTO unit_metrics (
			type, id, started_at, finished_at, model, auto_session_key,
			tokens_input, tokens_output, tokens_cache_read, tokens_cache_write, tokens_total,
			cost, tool_calls, assistant_messages, user_messages, api_requests,
			tier, model_downgraded, context_window_tokens, truncation_sections,
			continue_here_fired, prompt_char_count, baseline_char_count, cache_hit_rate, skills
		) VALUES (
			:type, :id, :started_at, :finished_at, :model, :auto_session_key,
			:tokens_input, :tokens_output, :tokens_cache_read, :tokens_cache_write, :tokens_total,
			:cost, :tool_calls, :assistant_messages, :user_messages, :api_requests,
			:tier, :model_downgraded, :context_window_tokens, :truncation_sections,
			:continue_here_fired, :prompt_char_count, :baseline_char_count, :cache_hit_rate, :skills
		)`,
	).run({
		":type": unit.type,
		":id": unit.id,
		":started_at": unit.startedAt,
		":finished_at": unit.finishedAt,
		":model": unit.model,
		":auto_session_key": unit.autoSessionKey ?? null,
		":tokens_input": unit.tokens.input,
		":tokens_output": unit.tokens.output,
		":tokens_cache_read": unit.tokens.cacheRead,
		":tokens_cache_write": unit.tokens.cacheWrite,
		":tokens_total": unit.tokens.total,
		":cost": unit.cost,
		":tool_calls": unit.toolCalls,
		":assistant_messages": unit.assistantMessages,
		":user_messages": unit.userMessages,
		":api_requests": unit.apiRequests ?? unit.assistantMessages,
		":tier": unit.tier ?? null,
		":model_downgraded":
			unit.modelDowngraded != null ? (unit.modelDowngraded ? 1 : 0) : null,
		":context_window_tokens": unit.contextWindowTokens ?? null,
		":truncation_sections": unit.truncationSections ?? null,
		":continue_here_fired":
			unit.continueHereFired != null ? (unit.continueHereFired ? 1 : 0) : null,
		":prompt_char_count": unit.promptCharCount ?? null,
		":baseline_char_count": unit.baselineCharCount ?? null,
		":cache_hit_rate": unit.cacheHitRate ?? null,
		":skills": unit.skills != null ? JSON.stringify(unit.skills) : null,
	});
}

export function getAllUnitMetrics(db) {
	return db
		.prepare("SELECT * FROM unit_metrics ORDER BY started_at ASC")
		.all()
		.map(rowToUnitMetrics);
}

export function pruneUnitMetrics(db, keepCount) {
	db.prepare(
		`DELETE FROM unit_metrics WHERE rowid NOT IN (
			SELECT rowid FROM unit_metrics ORDER BY finished_at DESC LIMIT :keepCount
		)`,
	).run({ ":keepCount": keepCount });
}
