JEI_Import_Documents-App/app/stock-codes/page.tsx

import Link from "next/link";
import { getPb } from "../lib/pb";

export const dynamic = "force-dynamic";

export default async function StockCodesPage({
  searchParams,
}: {
  searchParams: { q?: string };
}) {
  const pb = getPb();
  const q = (searchParams.q || "").trim();

  // Fetch all stock codes
  let codes: any[] = [];
  try {
    // Don't pass filter when empty — SDK serializes `undefined` as literal "undefined"
    const opts: any = { sort: "code", perPage: 500 };
    if (q) opts.filter = `code~"${q.replace(/"/g, '\\"')}" || description_goods~"${q.replace(/"/g, '\\"')}"`;
    const r = await pb.collection("JEI_stock_codes").getList(1, 500, opts);
    codes = r.items;
  } catch (e: any) {
    return (
      <div className="space-y-4">
        <h1 className="text-2xl font-bold text-brand-dark">Stock Codes</h1>
        <div className="bg-red-50 border border-red-200 text-red-800 rounded p-4">
          Cannot reach PocketBase: {String(e?.message || e)}
        </div>
      </div>
    );
  }

  // Fetch all invoice lines once, aggregate per stock code
  let linesByStock: Record<string, { qty: number; value: number; count: number }> = {};
  try {
    const lr = await pb.collection("JEI_invoice_lines").getList(1, 5000);
    for (const line of lr.items) {
      const sc = line.stock_code;
      if (!linesByStock[sc]) linesByStock[sc] = { qty: 0, value: 0, count: 0 };
      linesByStock[sc].qty += line.quantity || 0;
      linesByStock[sc].value += line.net_amount || 0;
      linesByStock[sc].count += 1;
    }
  } catch (e) {
    // Lines unavailable — show codes without totals
  }

  return (
    <div className="space-y-6">
      <div className="flex items-center justify-between">
        <h1 className="text-2xl font-bold text-brand-dark">Stock Codes</h1>
        <div className="text-sm text-slate-500">{codes.length} code(s)</div>
      </div>

      <form className="flex gap-3 items-end" method="get">
        <div className="flex-1">
          <input
            type="text"
            name="q"
            defaultValue={q}
            placeholder="Search code or description..."
            className="w-full border rounded px-3 py-2"
          />
        </div>
        <button className="bg-brand text-white rounded px-4 py-2 hover:bg-brand-dark">Search</button>
        {q && (
          <Link href="/stock-codes" className="text-sm text-slate-500 hover:underline">Clear</Link>
        )}
      </form>

      <div className="bg-white rounded-lg shadow overflow-hidden">
        <table className="w-full text-sm">
          <thead className="bg-slate-100 text-left">
            <tr>
              <th className="px-3 py-2">Code</th>
              <th className="px-3 py-2">Description</th>
              <th className="px-3 py-2 text-right">Total Qty</th>
              <th className="px-3 py-2 text-right">Total (£)</th>
              <th className="px-3 py-2 text-right">Lines</th>
            </tr>
          </thead>
          <tbody>
            {codes.length === 0 && (
              <tr>
                <td colSpan={5} className="px-3 py-8 text-center text-slate-500">
                  No stock codes yet. Run <code>python scripts/import_to_pb.py</code> to import.
                </td>
              </tr>
            )}
            {codes.map((c) => {
              const s = linesByStock[c.id] || { qty: 0, value: 0, count: 0 };
              return (
                <tr key={c.id} className="border-t hover:bg-slate-50">
                  <td className="px-3 py-2">
                    <Link href={`/stock-codes/${encodeURIComponent(c.code)}`} className="text-brand hover:underline font-medium">
                      {c.code}
                    </Link>
                  </td>
                  <td className="px-3 py-2 text-slate-700">{c.description_goods}</td>
                  <td className="px-3 py-2 text-right tabular-nums">{s.qty.toFixed(0)}</td>
                  <td className="px-3 py-2 text-right tabular-nums">£{s.value.toFixed(2)}</td>
                  <td className="px-3 py-2 text-right tabular-nums text-slate-600">{s.count}</td>
                </tr>
              );
            })}
          </tbody>
        </table>
      </div>
    </div>
  );
}