import { SF_STALE_STATE, SFError } from "../errors.js";
import { logError, logWarning } from "../workflow-logger.js";
import {
	_getAdapter,
	boolToInt,
	headlessRunFromRow,
	intBool,
	parseJsonObject,
	solverEvalCaseFromRow,
	solverEvalRunFromRow,
	transaction,
} from "./sf-db-core.js";

export function insertLlmTaskOutcome(input) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	try {
		currentDb
			.prepare(`INSERT INTO llm_task_outcomes (
         model_id,
         provider,
         unit_type,
         unit_id,
         succeeded,
         retries,
         escalated,
         verification_passed,
         blocker_discovered,
         duration_ms,
         tokens_total,
         cost_usd,
         failure_mode,
         recorded_at
       ) VALUES (
         :model_id,
         :provider,
         :unit_type,
         :unit_id,
         :succeeded,
         :retries,
         :escalated,
         :verification_passed,
         :blocker_discovered,
         :duration_ms,
         :tokens_total,
         :cost_usd,
         :failure_mode,
         :recorded_at
       )
       ON CONFLICT(unit_type, unit_id, recorded_at) DO UPDATE SET
         model_id = excluded.model_id,
         provider = excluded.provider,
         succeeded = excluded.succeeded,
         retries = excluded.retries,
         escalated = excluded.escalated,
         verification_passed = excluded.verification_passed,
         blocker_discovered = excluded.blocker_discovered,
         duration_ms = excluded.duration_ms,
         tokens_total = excluded.tokens_total,
         cost_usd = excluded.cost_usd,
         failure_mode = excluded.failure_mode`)
			.run({
				":model_id": input.modelId,
				":provider": input.provider,
				":unit_type": input.unitType,
				":unit_id": input.unitId,
				":succeeded": boolToInt(input.succeeded),
				":retries": input.retries ?? 0,
				":escalated": boolToInt(input.escalated ?? false),
				":verification_passed": boolToInt(input.verification_passed ?? null),
				":blocker_discovered": boolToInt(input.blocker_discovered ?? false),
				":duration_ms": input.duration_ms ?? null,
				":tokens_total": input.tokens_total ?? null,
				":cost_usd": input.cost_usd ?? null,
				":failure_mode": input.failure_mode ?? null,
				":recorded_at": input.recorded_at ?? Date.now(),
			});
		return true;
	} catch {
		return false;
	}
}

export function getLlmTaskOutcomesByUnit(unitType, unitId, limit = 20) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	try {
		return currentDb
			.prepare(
				`SELECT
					model_id,
					provider,
					unit_type,
					unit_id,
					succeeded,
					retries,
					escalated,
					verification_passed,
					blocker_discovered,
					duration_ms,
					tokens_total,
					cost_usd,
					recorded_at
				FROM llm_task_outcomes
				WHERE unit_type = :unit_type
					AND unit_id = :unit_id
				ORDER BY recorded_at DESC
				LIMIT :limit`,
			)
			.all({
				":unit_type": unitType,
				":unit_id": unitId,
				":limit": limit,
			});
	} catch {
		return [];
	}
}

export function getLlmTaskOutcomesByModel(modelId, limit = 50) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	try {
		return currentDb
			.prepare(
				`SELECT
					model_id,
					provider,
					unit_type,
					unit_id,
					succeeded,
					retries,
					escalated,
					verification_passed,
					blocker_discovered,
					duration_ms,
					tokens_total,
					cost_usd,
					recorded_at
				FROM llm_task_outcomes
				WHERE model_id = :model_id
				ORDER BY recorded_at DESC
				LIMIT :limit`,
			)
			.all({
				":model_id": modelId,
				":limit": limit,
			});
	} catch {
		return [];
	}
}

export function getRecentLlmTaskOutcomes(hours = 24, limit = 100) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	const cutoff = Date.now() - hours * 60 * 60 * 1000;
	try {
		return currentDb
			.prepare(
				`SELECT
					model_id,
					provider,
					unit_type,
					unit_id,
					succeeded,
					retries,
					escalated,
					verification_passed,
					blocker_discovered,
					duration_ms,
					tokens_total,
					cost_usd,
					recorded_at
				FROM llm_task_outcomes
				WHERE recorded_at >= :cutoff
				ORDER BY recorded_at DESC
				LIMIT :limit`,
			)
			.all({
				":cutoff": cutoff,
				":limit": limit,
			});
	} catch {
		return [];
	}
}

export function getLlmTaskOutcomeStats(modelId, windowHours = 24) {
	const currentDb = _getAdapter();
	if (!currentDb) {
		return {
			total: 0,
			succeeded: 0,
			failed: 0,
			totalCostUsd: 0,
			totalTokens: 0,
			avgDurationMs: 0,
		};
	}
	const cutoff = Date.now() - windowHours * 60 * 60 * 1000;
	try {
		const row = currentDb
			.prepare(
				`SELECT
					COUNT(*) AS total,
					COALESCE(SUM(CASE WHEN succeeded = 1 THEN 1 ELSE 0 END), 0) AS succeeded,
					COALESCE(SUM(CASE WHEN succeeded = 0 THEN 1 ELSE 0 END), 0) AS failed,
					COALESCE(SUM(cost_usd), 0) AS totalCostUsd,
					COALESCE(SUM(tokens_total), 0) AS totalTokens,
					COALESCE(AVG(duration_ms), 0) AS avgDurationMs
				FROM llm_task_outcomes
				WHERE model_id = :model_id
					AND recorded_at >= :cutoff`,
			)
			.get({ ":model_id": modelId, ":cutoff": cutoff });
		if (!row) {
			return {
				total: 0,
				succeeded: 0,
				failed: 0,
				totalCostUsd: 0,
				totalTokens: 0,
				avgDurationMs: 0,
			};
		}
		return {
			total: row.total ?? 0,
			succeeded: row.succeeded ?? 0,
			failed: row.failed ?? 0,
			totalCostUsd: row.totalCostUsd ?? 0,
			totalTokens: row.totalTokens ?? 0,
			avgDurationMs: row.avgDurationMs ?? 0,
		};
	} catch {
		return {
			total: 0,
			succeeded: 0,
			failed: 0,
			totalCostUsd: 0,
			totalTokens: 0,
			avgDurationMs: 0,
		};
	}
}

export function recordSolverEvalRun(report) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const now = new Date().toISOString();
	transaction(() => {
		currentDb
			.prepare(`INSERT INTO solver_eval_runs (
          run_id, suite_source, cases_count, summary_json, report_path,
          results_path, db_recorded, created_at, updated_at
        ) VALUES (
          :run_id, :suite_source, :cases_count, :summary_json, :report_path,
          :results_path, 1, :created_at, :updated_at
        )
        ON CONFLICT(run_id) DO UPDATE SET
          suite_source = excluded.suite_source,
          cases_count = excluded.cases_count,
          summary_json = excluded.summary_json,
          report_path = excluded.report_path,
          results_path = excluded.results_path,
          db_recorded = 1,
          updated_at = excluded.updated_at`)
			.run({
				":run_id": report.runId,
				":suite_source": report.suiteSource ?? "",
				":cases_count": report.summary?.cases ?? report.results?.length ?? 0,
				":summary_json": JSON.stringify(report.summary ?? {}),
				":report_path": report.reportPath ?? "",
				":results_path": report.resultsPath ?? "",
				":created_at": report.createdAt ?? now,
				":updated_at": now,
			});
		const stmt = currentDb.prepare(`INSERT INTO solver_eval_case_results (
        run_id, case_id, title, mode, passed, false_complete, duration_ms,
        command_status, solver_outcome, pdd_complete, result_json, created_at
      ) VALUES (
        :run_id, :case_id, :title, :mode, :passed, :false_complete, :duration_ms,
        :command_status, :solver_outcome, :pdd_complete, :result_json, :created_at
      )
      ON CONFLICT(run_id, case_id, mode) DO UPDATE SET
        title = excluded.title,
        passed = excluded.passed,
        false_complete = excluded.false_complete,
        duration_ms = excluded.duration_ms,
        command_status = excluded.command_status,
        solver_outcome = excluded.solver_outcome,
        pdd_complete = excluded.pdd_complete,
        result_json = excluded.result_json,
        created_at = excluded.created_at`);
		for (const result of report.results ?? []) {
			stmt.run({
				":run_id": report.runId,
				":case_id": result.caseId,
				":title": result.title ?? "",
				":mode": result.mode,
				":passed": intBool(result.passed),
				":false_complete": intBool(result.falseComplete),
				":duration_ms": result.command?.durationMs ?? null,
				":command_status": result.command?.status ?? null,
				":solver_outcome": result.solverSignals?.outcome ?? null,
				":pdd_complete":
					result.solverSignals?.pddComplete === undefined
						? null
						: intBool(result.solverSignals.pddComplete),
				":result_json": JSON.stringify(result),
				":created_at": report.createdAt ?? now,
			});
		}
	});
}

export function listSolverEvalRuns(limit = 10) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	return currentDb
		.prepare(`SELECT run_id, suite_source, cases_count, summary_json,
              report_path, results_path, db_recorded, created_at, updated_at
       FROM solver_eval_runs
       ORDER BY created_at DESC, run_id DESC
       LIMIT :limit`)
		.all({ ":limit": Math.max(1, Math.min(100, Number(limit) || 10)) })
		.map(solverEvalRunFromRow);
}

export function getSolverEvalRun(runId) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const row = currentDb
		.prepare(`SELECT run_id, suite_source, cases_count, summary_json,
              report_path, results_path, db_recorded, created_at, updated_at
       FROM solver_eval_runs
       WHERE run_id = :run_id`)
		.get({ ":run_id": runId });
	return row ? solverEvalRunFromRow(row) : null;
}

export function getSolverEvalCaseResults(runId) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	return currentDb
		.prepare(`SELECT run_id, case_id, title, mode, passed, false_complete,
              duration_ms, command_status, solver_outcome, pdd_complete,
              result_json, created_at
       FROM solver_eval_case_results
       WHERE run_id = :run_id
       ORDER BY case_id ASC, mode ASC`)
		.all({ ":run_id": runId })
		.map(solverEvalCaseFromRow);
}

export function recordHeadlessRun(entry) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const now = new Date().toISOString();
	currentDb
		.prepare(`INSERT INTO headless_runs (
        run_id, command, status, exit_code, timed_out, interrupted,
        restart_count, max_restarts, duration_ms, total_events, tool_calls,
        solver_eval_run_id, solver_eval_report_path, details_json,
        created_at, updated_at
      ) VALUES (
        :run_id, :command, :status, :exit_code, :timed_out, :interrupted,
        :restart_count, :max_restarts, :duration_ms, :total_events, :tool_calls,
        :solver_eval_run_id, :solver_eval_report_path, :details_json,
        :created_at, :updated_at
      )
      ON CONFLICT(run_id) DO UPDATE SET
        command = excluded.command,
        status = excluded.status,
        exit_code = excluded.exit_code,
        timed_out = excluded.timed_out,
        interrupted = excluded.interrupted,
        restart_count = excluded.restart_count,
        max_restarts = excluded.max_restarts,
        duration_ms = excluded.duration_ms,
        total_events = excluded.total_events,
        tool_calls = excluded.tool_calls,
        solver_eval_run_id = excluded.solver_eval_run_id,
        solver_eval_report_path = excluded.solver_eval_report_path,
        details_json = excluded.details_json,
        updated_at = excluded.updated_at`)
		.run({
			":run_id": entry.runId,
			":command": entry.command ?? "",
			":status": entry.status ?? "",
			":exit_code": Number(entry.exitCode ?? 0),
			":timed_out": intBool(entry.timedOut),
			":interrupted": intBool(entry.interrupted),
			":restart_count": Number(entry.restartCount ?? 0),
			":max_restarts": Number(entry.maxRestarts ?? 0),
			":duration_ms": Number(entry.durationMs ?? 0),
			":total_events": Number(entry.totalEvents ?? 0),
			":tool_calls": Number(entry.toolCalls ?? 0),
			":solver_eval_run_id": entry.solverEvalRunId ?? null,
			":solver_eval_report_path": entry.solverEvalReportPath ?? null,
			":details_json": JSON.stringify(entry.details ?? {}),
			":created_at": entry.createdAt ?? now,
			":updated_at": now,
		});
}

export function listHeadlessRuns(limit = 20) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	return currentDb
		.prepare(`SELECT run_id, command, status, exit_code, timed_out,
              interrupted, restart_count, max_restarts, duration_ms,
              total_events, tool_calls, solver_eval_run_id,
              solver_eval_report_path, details_json, created_at, updated_at
       FROM headless_runs
       ORDER BY created_at DESC, run_id DESC
       LIMIT :limit`)
		.all({ ":limit": Math.max(1, Math.min(100, Number(limit) || 20)) })
		.map(headlessRunFromRow);
}

export function upsertRoutingOutcome(db, pattern, tier, success) {
	db.prepare(
		`INSERT INTO routing_history (pattern, tier, success_count, fail_count, updated_at)
     VALUES (:pattern, :tier, :success_count, :fail_count, :updated_at)
     ON CONFLICT(pattern, tier) DO UPDATE SET
       success_count = success_count + excluded.success_count,
       fail_count = fail_count + excluded.fail_count,
       updated_at = excluded.updated_at`,
	).run({
		":pattern": pattern,
		":tier": tier,
		":success_count": success ? 1 : 0,
		":fail_count": success ? 0 : 1,
		":updated_at": new Date().toISOString(),
	});
}

export function getAllRoutingHistory(db) {
	return db
		.prepare(
			"SELECT pattern, tier, success_count, fail_count, updated_at FROM routing_history",
		)
		.all();
}

export function getRoutingHistoryForPattern(db, pattern) {
	return db
		.prepare(
			"SELECT tier, success_count, fail_count FROM routing_history WHERE pattern = ?",
		)
		.all(pattern);
}

export function insertRoutingFeedback(db, pattern, tier, feedback) {
	db.prepare(
		`INSERT INTO routing_feedback (pattern, tier, feedback, recorded_at)
     VALUES (:pattern, :tier, :feedback, :recorded_at)`,
	).run({
		":pattern": pattern,
		":tier": tier,
		":feedback": feedback,
		":recorded_at": new Date().toISOString(),
	});
}

export function clearRoutingHistory(db) {
	db.prepare("DELETE FROM routing_history").run();
	db.prepare("DELETE FROM routing_feedback").run();
}

export function insertTriageRun(id, sourceFile, createdAt) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(
			`INSERT INTO triage_runs (id, source_file, status, created_at)
       VALUES (:id, :source_file, 'complete', :created_at)
       ON CONFLICT(id) DO NOTHING`,
		)
		.run({
			":id": id,
			":source_file": sourceFile ?? null,
			":created_at": createdAt ?? new Date().toISOString(),
		});
}

export function insertTriageEval(id, runId, data, createdAt) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(
			`INSERT INTO triage_evals (id, run_id, task_input, expected_behavior, evidence, failure_mode, status, created_at)
       VALUES (:id, :run_id, :task_input, :expected_behavior, :evidence, :failure_mode, 'pending', :created_at)
       ON CONFLICT(id) DO NOTHING`,
		)
		.run({
			":id": id,
			":run_id": runId,
			":task_input": data.task_input ?? "",
			":expected_behavior": data.expected_behavior ?? "",
			":evidence": data.evidence ?? null,
			":failure_mode": data.failure_mode ?? null,
			":created_at": createdAt ?? new Date().toISOString(),
		});
}

export function insertTriageItem(
	id,
	runId,
	kind,
	content,
	evidence,
	createdAt,
) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(
			`INSERT INTO triage_items (id, run_id, kind, content, evidence, status, created_at)
       VALUES (:id, :run_id, :kind, :content, :evidence, 'pending', :created_at)
       ON CONFLICT(id) DO NOTHING`,
		)
		.run({
			":id": id,
			":run_id": runId,
			":kind": kind,
			":content": content,
			":evidence": evidence ?? null,
			":created_at": createdAt ?? new Date().toISOString(),
		});
}

export function insertTriageSkill(id, runId, data, createdAt) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(
			`INSERT INTO triage_skills (id, run_id, name, description, trigger, raw_json, status, created_at)
       VALUES (:id, :run_id, :name, :description, :trigger, :raw_json, 'pending', :created_at)
       ON CONFLICT(id) DO NOTHING`,
		)
		.run({
			":id": id,
			":run_id": runId,
			":name": data.title ?? data.name ?? null,
			":description": data.description ?? null,
			":trigger": data.trigger_pattern ?? data.trigger ?? null,
			":raw_json": JSON.stringify(data),
			":created_at": createdAt ?? new Date().toISOString(),
		});
}
