/**
 * Agent turn CRUD query helpers.
 *
 * All functions obtain the SQLite instance internally via `getDatabase()`.
 * Queries use positional `?` placeholders throughout (better-sqlite3).
 */

import { getDatabase } from '@myco/db/client.js';
import { appendProjectCondition, type ProjectScope } from '@myco/db/queries/project-scope.js';

// ---------------------------------------------------------------------------
// Types
// ---------------------------------------------------------------------------

/** Fields required (or optional) when inserting a turn. */
export interface TurnInsert {
  run_id: string;
  project_id?: string | null;
  agent_id: string;
  turn_number: number;
  tool_name: string;
  tool_input?: string | null;
  tool_output_summary?: string | null;
  started_at?: number | null;
  completed_at?: number | null;
}

/** Fields that can be updated after a turn row has been created. */
export interface TurnCompletion {
  tool_output_summary?: string | null;
  completed_at?: number | null;
}

/** Row shape returned from agent_turns queries (all columns). */
export interface TurnRow {
  id: number;
  project_id: string | null;
  run_id: string;
  agent_id: string;
  turn_number: number;
  tool_name: string;
  tool_input: string | null;
  tool_output_summary: string | null;
  started_at: number | null;
  completed_at: number | null;
}

// ---------------------------------------------------------------------------
// Column list
// ---------------------------------------------------------------------------

const TURN_COLUMNS = [
  'id',
  'project_id',
  'run_id',
  'agent_id',
  'turn_number',
  'tool_name',
  'tool_input',
  'tool_output_summary',
  'started_at',
  'completed_at',
] as const;

const SELECT_COLUMNS = TURN_COLUMNS.join(', ');

// ---------------------------------------------------------------------------
// Helpers
// ---------------------------------------------------------------------------

/** Normalize a SQLite result row into a typed TurnRow. */
function toTurnRow(row: Record<string, unknown>): TurnRow {
  return {
    id: row.id as number,
    project_id: (row.project_id as string) ?? null,
    run_id: row.run_id as string,
    agent_id: row.agent_id as string,
    turn_number: row.turn_number as number,
    tool_name: row.tool_name as string,
    tool_input: (row.tool_input as string) ?? null,
    tool_output_summary: (row.tool_output_summary as string) ?? null,
    started_at: (row.started_at as number) ?? null,
    completed_at: (row.completed_at as number) ?? null,
  };
}

export interface ListTurnsOptions {
  scope: ProjectScope;
}

// ---------------------------------------------------------------------------
// Public API
// ---------------------------------------------------------------------------

/**
 * Insert a new agent turn.
 *
 * The `id` is auto-generated by the INTEGER PRIMARY KEY (AUTOINCREMENT).
 */
export function insertTurn(data: TurnInsert): TurnRow {
  const db = getDatabase();

  const info = db.prepare(
    `INSERT INTO agent_turns (
       project_id, run_id, agent_id, turn_number, tool_name,
       tool_input, tool_output_summary, started_at, completed_at
     ) VALUES (
       COALESCE(?, (SELECT project_id FROM agent_runs WHERE id = ?)), ?, ?, ?, ?,
       ?, ?, ?, ?
     )`,
  ).run(
    data.project_id ?? null,
    data.run_id,
    data.run_id,
    data.agent_id,
    data.turn_number,
    data.tool_name,
    data.tool_input ?? null,
    data.tool_output_summary ?? null,
    data.started_at ?? null,
    data.completed_at ?? null,
  );

  const turnId = Number(info.lastInsertRowid);

  return toTurnRow(
    db.prepare(`SELECT ${SELECT_COLUMNS} FROM agent_turns WHERE id = ?`).get(turnId) as Record<string, unknown>,
  );
}

/**
 * Update completion metadata for an existing turn.
 *
 * @returns the updated row, or null if the turn does not exist.
 */
export function updateTurn(id: number, completion: TurnCompletion): TurnRow | null {
  const db = getDatabase();

  const info = db.prepare(
    `UPDATE agent_turns
     SET tool_output_summary = ?, completed_at = ?
     WHERE id = ?`,
  ).run(
    completion.tool_output_summary ?? null,
    completion.completed_at ?? null,
    id,
  );

  if (info.changes === 0) return null;

  return toTurnRow(
    db.prepare(`SELECT ${SELECT_COLUMNS} FROM agent_turns WHERE id = ?`).get(id) as Record<string, unknown>,
  );
}

/**
 * List all turns for a specific run, ordered by turn_number ASC.
 */
export function listTurns(runId: string, options: ListTurnsOptions): TurnRow[] {
  const db = getDatabase();
  const conditions = ['run_id = ?'];
  const params: unknown[] = [runId];
  appendProjectCondition(conditions, params, options.scope);

  const rows = db.prepare(
    `SELECT ${SELECT_COLUMNS}
     FROM agent_turns
     WHERE ${conditions.join(' AND ')}
     ORDER BY turn_number ASC`,
  ).all(...params) as Record<string, unknown>[];

  return rows.map(toTurnRow);
}

/**
 * List all agent turns for a run, ordered by turn_number ASC.
 *
 * Alias for `listTurns` with an explicit "by run" naming convention used
 * by the dashboard API layer.
 */
export function listTurnsByRun(runId: string, options: ListTurnsOptions): TurnRow[] {
  return listTurns(runId, options);
}

/** Count tool calls by name for a specific run. */
export function countToolCallsByRun(
  runId: string,
  toolNames: string[],
): Record<string, number> {
  if (toolNames.length === 0) return {};
  const db = getDatabase();
  const placeholders = toolNames.map(() => '?').join(', ');
  const rows = db.prepare(
    `SELECT tool_name, COUNT(*) as count
     FROM agent_turns
     WHERE run_id = ? AND tool_name IN (${placeholders})
     GROUP BY tool_name`,
  ).all(runId, ...toolNames) as Array<{ tool_name: string; count: number }>;

  const result: Record<string, number> = {};
  for (const row of rows) {
    result[row.tool_name] = row.count;
  }
  return result;
}
