chatbot/server/erpTools.ts

/**
 * ERP business-query helpers for the chatbot.
 * Each function fetches ERP data and returns a concise, LLM-friendly string
 * (or structured object) that can be injected into the system prompt context.
 *
 * Sensitive fields (cost prices, internal IDs, etc.) are intentionally omitted.
 */

import {
  fetchCatalog,
  fetchContacts,
  fetchOrderDetail,
  fetchOrdersList,
  fetchStock,
  type OrderDetail,
  type OrderListItem,
  type UserCtx,
} from "./erpClient";

export type { UserCtx };

// ─── Order lookup ─────────────────────────────────────────────────────────────

/**
 * Fetch a single sales order by SO-ID and return a chatbot-friendly summary.
 */
export async function lookupOrder(soId: string, userCtx?: UserCtx): Promise<string> {
  const order = await fetchOrderDetail(soId.trim().toUpperCase(), userCtx);
  if (!order) return `Sales order "${soId}" was not found in the system.`;
  return formatOrderDetail(order);
}

/**
 * Fetch the N most recent orders for a customer (by CID or name).
 */
export async function lookupOrdersByCustomer(
  customerCid: string,
  limit = 5,
  userCtx?: UserCtx
): Promise<string> {
  const rows = await fetchOrdersList({ customer_cid: customerCid, limit }, userCtx);
  if (!rows.length) return `No orders found for customer "${customerCid}".`;
  return formatOrderList(rows);
}

/**
 * Fetch recent orders for a customer and return tracking-focused summary.
 */
export async function lookupTrackingByCustomer(
  customerCid: string,
  limit = 5,
  userCtx?: UserCtx
): Promise<string> {
  const rows = await fetchOrdersList({ customer_cid: customerCid, limit }, userCtx);
  if (!rows.length) return `No recent orders found for customer "${customerCid}".`;
  return formatTrackingList(rows);
}

/**
 * Search orders by PO number or partial SO-ID.
 */
export async function lookupOrdersByPO(poId: string, userCtx?: UserCtx): Promise<string> {
  const rows = await fetchOrdersList({ po_id: poId, limit: 10 }, userCtx);
  if (!rows.length) return `No orders found with PO number "${poId}".`;
  return formatOrderList(rows);
}

// ─── Catalog / product lookup ─────────────────────────────────────────────────

export async function lookupCatalog(params: {
  model?: string;
  description?: string;
  category?: string;
  manufacturer?: string;
  limit?: number;
}, userCtx?: UserCtx): Promise<string> {
  const items = await fetchCatalog({ ...params, limit: params.limit ?? 10 }, userCtx);
  if (!items.length) return "No matching products found in the catalog.";

  const lines = items.map((i) => {
    const parts = [
      `• ${i.Model ?? "—"}`,
      i.Manufacturer ? `by ${i.Manufacturer}` : null,
      i.Description ? `— ${i.Description}` : null,
      i.Type ? `[${i.Type}]` : null,
      i.Status ? `Status: ${i.Status}` : null,
      i.StockQTY != null ? `Stock: ${i.StockQTY} ${i.UOM ?? ""}`.trim() : null,
    ].filter(Boolean);
    return parts.join("  ");
  });

  return `Product catalog results (${items.length}):\n${lines.join("\n")}`;
}

// ─── Stock lookup ─────────────────────────────────────────────────────────────

export async function lookupStock(params: {
  model: string;
  warehouse_cid?: string;
  limit?: number;
}, userCtx?: UserCtx): Promise<string> {
  const records = await fetchStock({ ...params, limit: params.limit ?? 30 }, userCtx);
  if (!records.length)
    return `No stock records found for model "${params.model}".`;

  // Aggregate quantity by model
  const byModel: Record<string, { qty: number; wh: Set<string>; eta: string | null }> = {};
  for (const r of records) {
    const key = r.Model ?? "UNKNOWN";
    if (!byModel[key]) byModel[key] = { qty: 0, wh: new Set(), eta: null };
    byModel[key].qty += r.Quantity ?? 0;
    if (r.WarehouseCID) byModel[key].wh.add(r.WarehouseCID);
    if (!byModel[key].eta && (r.A_ETA ?? r.B_ETA))
      byModel[key].eta = r.A_ETA ?? r.B_ETA;
  }

  const lines = Object.entries(byModel).map(([model, { qty, wh, eta }]) => {
    const parts = [`• ${model}`, `Qty: ${qty}`];
    if (wh.size) parts.push(`Warehouse: ${[...wh].join(", ")}`);
    if (eta) parts.push(`ETA: ${eta}`);
    return parts.join("  ");
  });

  return `Stock availability for "${params.model}" (${records.length} pallet records):\n${lines.join("\n")}`;
}

// ─── Contact / customer lookup ────────────────────────────────────────────────

export async function lookupContact(params: {
  contact_id?: string;
  company?: string;
  name?: string;
}, userCtx?: UserCtx): Promise<string> {
  const contacts = await fetchContacts({ ...params, limit: 5 }, userCtx);
  if (!contacts.length) return "No matching customer records found.";

  const lines = contacts.map((c) => {
    const parts = [
      `• ${c.Company ?? "—"}`,
      c.ContactName1 ? `(${c.ContactName1})` : null,
      c.Phone1 ? `Ph: ${c.Phone1}` : null,
      c.Email1 ? `Email: ${c.Email1}` : null,
      c.City ? `${c.City}, ${c.State ?? ""} ${c.Country ?? ""}`.trim() : null,
      c.Terms ? `Terms: ${c.Terms}` : null,
      c.AR_CreditHold ? "⚠ Credit Hold" : null,
    ].filter(Boolean);
    return parts.join("  ");
  });

  return `Customer records (${contacts.length}):\n${lines.join("\n")}`;
}

// ─── Formatters ───────────────────────────────────────────────────────────────

function formatOrderDetail(o: OrderDetail): string {
  const header = [
    `Order: ${o.SOID ?? "—"}`,
    `Status: ${o.StatusDesc ?? o.Status ?? "—"}`,
    o.CustomerName ? `Customer: ${o.CustomerName}` : null,
    o.POID ? `PO#: ${o.POID}` : null,
    o.ScheduleDate ? `Ship Date: ${o.ScheduleDate}` : null,
    o.ShipVia ? `Ship Via: ${o.ShipVia}` : null,
    o.ReceiverName
      ? `Receiver: ${o.ReceiverName}, ${o.ReceiverCity ?? ""} ${o.ReceiverState ?? ""}`
      : null,
    o.Notes ? `Notes: ${o.Notes}` : null,
  ]
    .filter(Boolean)
    .join("\n");

  const items =
    o.Items?.length
      ? "\nLine items:\n" +
        o.Items.map((i) => {
          const parts = [
            `  [${i.LineNo}] ${i.Model ?? "—"}`,
            i.Description ? `— ${i.Description}` : null,
            `Qty: ${i.Quantity ?? 0}`,
            i.UnitPrice != null ? `@ $${i.UnitPrice}` : null,
            i.StatusDesc ? `(${i.StatusDesc})` : null,
            i.ETD ? `ETD: ${i.ETD}` : null,
            i.ItemNote ? `Note: ${i.ItemNote}` : null,
          ].filter(Boolean);
          return parts.join("  ");
        }).join("\n")
      : "";

  return header + items;
}

function formatOrderList(rows: OrderListItem[]): string {
  // Group by SOID to deduplicate (one row per line item)
  const seen = new Set<string>();
  const lines: string[] = [];

  for (const r of rows) {
    const soId = r.SOID ?? "—";
    if (!seen.has(soId)) {
      seen.add(soId);
      const parts = [
        `• ${soId}`,
        r.CustomerName ? `— ${r.CustomerName}` : null,
        r.Status ? `[${r.Status}]` : null,
        r.ScheduleDate ? `Ship: ${r.ScheduleDate}` : null,
        r.InvoiceNo ? `Inv: ${r.InvoiceNo}` : null,
        r.TrackingNumber ? `Track: ${r.TrackingNumber}` : null,
      ].filter(Boolean);
      lines.push(parts.join("  "));
    }

    // Show line items beneath the order
    if (r.Model) {
      lines.push(
        `    – ${r.Model}  Qty: ${r.Quantity ?? 0}  Price: $${r.UnitPrice ?? 0}`
      );
    }
  }

  return `Orders (${seen.size} found):\n${lines.join("\n")}`;
}

function formatTrackingList(rows: OrderListItem[]): string {
  const seen = new Set<string>();
  const lines: string[] = [];

  for (const r of rows) {
    const soId = r.SOID ?? "—";
    if (seen.has(soId)) continue;
    seen.add(soId);

    const parts = [
      `• SO: ${soId}`,
      r.Status ? `[${r.Status}]` : null,
      r.POID ? `PO: ${r.POID}` : null,
      r.ShipVia ? `Ship Via: ${r.ShipVia}` : null,
      r.Carrier ? `Carrier: ${r.Carrier}` : null,
      r.TrackingNumber ? `Tracking #: ${r.TrackingNumber}` : "Tracking #: Not yet available",
      r.ETD ? `ETD: ${r.ETD}` : null,
      r.InvoiceDate ? `Invoice Date: ${r.InvoiceDate}` : null,
    ].filter(Boolean);
    lines.push(parts.join("  "));
  }

  return `Shipment tracking for your recent orders (${seen.size}):\n${lines.join("\n")}`;
}