import type { PoolConnection } from 'mysql2/promise';
import { queryWithConn, executeWithConn } from '../../config/database.js';
import type { OrderComment } from '../types/index.js';

function rowToComment(row: Record<string, unknown>): OrderComment {
  return {
    id: row.id as string,
    order_id: row.order_id as string,
    employee_id: row.employee_id as string,
    comment: row.comment as string,
    created_at: (row.created_at as Date).toISOString(),
  };
}

export async function findByOrderId(orderId: string, conn?: PoolConnection): Promise<OrderComment[]> {
  const sql = `
    SELECT id as id, order_id as order_id,
           employee_id as employee_id, comment, created_at
    FROM order_comments
    WHERE order_id = ?
    ORDER BY created_at ASC
  `;
  const rows = await queryWithConn(sql, [orderId], conn) as unknown as Record<string, unknown>[];
  return rows.map(rowToComment);
}

export async function insert(
  data: { order_id: string; employee_id: string; comment: string },
  id: string,
  conn?: PoolConnection,
): Promise<void> {
  await executeWithConn(
    'INSERT INTO order_comments (id, order_id, employee_id, comment) VALUES (?, ?, ?, ?)',
    [id, data.order_id, data.employee_id, data.comment],
    conn,
  );
}