import type { AuditEntry, AuditKind } from '@wellformed/shared';
import type { Db } from '../db/driver.js';

export interface Actor {
  id: string | null;
  name: string;
  /**
   * The agent key this change came through, by its label, when it did.
   *
   * An assistant acts *as* its holder — same account, same role, on a key that
   * account made and can revoke — so `id` and `name` stay theirs, and this is
   * the disclosure rather than a second identity.
   */
  via?: string | null;
}

/**
 * Who a line is attributed to when nobody typed it — a workflow rule firing, a
 * billing webhook landing. The name is the product's, because that is the only
 * honest answer to "who changed this".
 */
export const SYSTEM_ACTOR: Actor = { id: null, name: 'Wellformed' };

export interface AuditInput {
  kind: AuditKind;
  table?: string | null;
  rowId?: string | null;
  rowLabel?: string | null;
  column?: string | null;
  old?: string | null;
  nw?: string | null;
}

/**
 * The audit trail is append-only and written in the same transaction as the
 * change it describes. No route deletes from it.
 */
export function recordAudit(db: Db, actor: Actor, entry: AuditInput): void {
  db.sqlite
    .prepare(
      `INSERT INTO _wellformed_audit (user_id, who, via, kind, table_name, row_id, row_label, column_name, old_value, new_value)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    )
    .run(
      actor.id,
      actor.name,
      actor.via ?? null,
      entry.kind,
      entry.table ?? null,
      entry.rowId ?? null,
      entry.rowLabel ?? null,
      entry.column ?? null,
      entry.old ?? null,
      entry.nw ?? null,
    );
}

interface AuditRow {
  id: number;
  ts: string;
  who: string;
  via: string | null;
  kind: string;
  table_name: string | null;
  row_id: string | null;
  row_label: string | null;
  column_name: string | null;
  old_value: string | null;
  new_value: string | null;
}

export interface AuditQuery {
  table?: string;
  rowId?: string;
  limit: number;
  before?: number;
  /** Only what came through an agent key. The trail's answer to "what did the AI do?". */
  agentsOnly?: boolean;
}

export function readAudit(db: Db, query: AuditQuery): AuditEntry[] {
  const clauses: string[] = [];
  const params: unknown[] = [];

  if (query.table) {
    clauses.push('table_name = ?');
    params.push(query.table);
  }
  if (query.rowId) {
    clauses.push('row_id = ?');
    params.push(query.rowId);
  }
  if (query.before !== undefined) {
    clauses.push('id < ?');
    params.push(query.before);
  }
  if (query.agentsOnly) {
    clauses.push('via IS NOT NULL');
  }

  const where = clauses.length ? ` WHERE ${clauses.join(' AND ')}` : '';
  const limit = Math.min(Math.max(1, Math.floor(query.limit)), 500);

  return db.sqlite
    .prepare<unknown[], AuditRow>(
      `SELECT id, ts, who, via, kind, table_name, row_id, row_label, column_name, old_value, new_value
       FROM _wellformed_audit${where} ORDER BY id DESC LIMIT ${limit}`,
    )
    .all(...params)
    .map((r) => ({
      id: r.id,
      ts: r.ts,
      who: r.who,
      via: r.via,
      kind: r.kind as AuditKind,
      table: r.table_name,
      rowId: r.row_id,
      rowLabel: r.row_label,
      column: r.column_name,
      old: r.old_value,
      nw: r.new_value,
    }));
}