/**
 * Internal notes. These live in their own table - never in `messages` - so
 * there is no code path that can accidentally deliver one to a customer.
 */
import { many, one } from '../../db/pool.js';
import type { NoteDTO } from '../types.js';

const toDTO = (r: any): NoteDTO => ({
  id: r.id,
  conversationId: r.conversation_id,
  agentId: r.agent_id ?? null,
  agentName: r.agent_name ?? null,
  body: r.body,
  createdAt: new Date(r.created_at).toISOString(),
});

export async function listNotes(conversationId: string): Promise<NoteDTO[]> {
  const rows = await many(
    `SELECT n.*, a.name AS agent_name
       FROM internal_notes n LEFT JOIN agents a ON a.id = n.agent_id
      WHERE n.conversation_id = $1
      ORDER BY n.created_at ASC`,
    [conversationId],
  );
  return rows.map(toDTO);
}

export async function addNote(conversationId: string, agentId: string, body: string): Promise<NoteDTO> {
  const row = await one(
    `WITH inserted AS (
       INSERT INTO internal_notes (conversation_id, agent_id, body)
       VALUES ($1,$2,$3) RETURNING *
     )
     SELECT i.*, a.name AS agent_name FROM inserted i LEFT JOIN agents a ON a.id = i.agent_id`,
    [conversationId, agentId, body.trim()],
  );
  return toDTO(row);
}