/**
 * Agent report 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';

// ---------------------------------------------------------------------------
// Constants
// ---------------------------------------------------------------------------

/** Default number of reports returned by list queries when no limit given. */
const DEFAULT_LIST_LIMIT = 100;

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

/** Fields required (or optional) when inserting a report. */
export interface ReportInsert {
  run_id: string;
  project_id?: string | null;
  agent_id: string;
  action: string;
  summary: string;
  details?: string | null;
  created_at: number;
}

/** Row shape returned from agent_reports queries (all columns). */
export interface ReportRow {
  id: number;
  project_id: string | null;
  run_id: string;
  agent_id: string;
  action: string;
  summary: string;
  details: string | null;
  created_at: number;
}

/** Filter options for `listReportsByAgent`. */
export interface ListReportsByAgentOptions {
  limit?: number;
  scope: ProjectScope;
}

export interface ListReportsOptions {
  scope: ProjectScope;
}

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

const REPORT_COLUMNS = [
  'id',
  'project_id',
  'run_id',
  'agent_id',
  'action',
  'summary',
  'details',
  'created_at',
] as const;

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

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

/** Normalize a SQLite result row into a typed ReportRow. */
function toReportRow(row: Record<string, unknown>): ReportRow {
  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,
    action: row.action as string,
    summary: row.summary as string,
    details: (row.details as string) ?? null,
    created_at: row.created_at as number,
  };
}

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

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

  const info = db.prepare(
    `INSERT INTO agent_reports (
       project_id, run_id, agent_id, action, summary, details, created_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.action,
    data.summary,
    data.details ?? null,
    data.created_at,
  );

  const reportId = Number(info.lastInsertRowid);

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

/**
 * List all reports for a specific run, ordered by created_at ASC.
 */
export function listReports(runId: string, options: ListReportsOptions): ReportRow[] {
  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_reports
     WHERE ${conditions.join(' AND ')}
     ORDER BY created_at ASC`,
  ).all(...params) as Record<string, unknown>[];

  return rows.map(toReportRow);
}

/**
 * List reports by agent, ordered by created_at DESC.
 */
export function listReportsByAgent(
  agentId: string,
  options: ListReportsByAgentOptions,
): ReportRow[] {
  const db = getDatabase();

  const limit = options.limit ?? DEFAULT_LIST_LIMIT;
  const conditions = ['agent_id = ?'];
  const params: unknown[] = [agentId];
  appendProjectCondition(conditions, params, options.scope);

  const rows = db.prepare(
    `SELECT ${SELECT_COLUMNS}
     FROM agent_reports
     WHERE ${conditions.join(' AND ')}
     ORDER BY created_at DESC
     LIMIT ?`,
  ).all(...params, limit) as Record<string, unknown>[];

  return rows.map(toReportRow);
}
