WFDASH-WEBAPP/scripts/recent-stock-movements.ts

/**
 * One-off diagnostic: list every `stock_items` row that was created
 * or updated since 2026-09-14T22:00:00Z (~36h ago, "yesterday +
 * today" Johannesburg time), and highlight the ones still missing
 * a `cost_price` or `selling_price`.
 *
 * Why this exists
 * ───────────────
 * The operator noticed "new" items on the POS are showing without
 * prices. The recent 80% markup commit (Mon night) patched a lot
 * of rows in-place via `updated`, so the "updated in window" set
 * conflates two populations:
 *   (a) genuinely-new items added recently, and
 *   (b) pre-existing rows whose `selling_price` / `markup` was just
 *       patched by the markup run.
 * This script splits those two apart in the summary block and
 * then prints a full table of every in-window row that STILL has
 * `cost_price <= 0` OR `selling_price <= 0` — the actual gap the
 * POS is exposing.
 *
 * Read-only — never writes to PB.
 *
 * Run with:
 *   npx tsx scripts/recent-stock-movements.ts
 *
 * Requires .env.local with NEXT_PUBLIC_POCKETBASE_URL,
 * PB_ADMIN_EMAIL, PB_ADMIN_PASSWORD.
 */

import "./_env"; // side-effect import: loads .env.local before anything else

import { getAdminPocketBase } from "../src/lib/server/pocketbase-admin";

// ─── Constants ────────────────────────────────────────────────────────

/**
 * Lower bound of the "recently moved" window. Hard-coded to the
 * Johannesburg "yesterday + today" cutoff the operator asked for
 * (2026-09-14 22:00 UTC == 2026-09-15 00:00 SAST). Edit here if
 * the operator wants a different window.
 */
const WINDOW_START_ISO = "2026-09-14T22:00:00.000Z";

// ─── Types ────────────────────────────────────────────────────────────

/** Single `stock_items` row shape — the fields we read for the
 *  report. `expand.supplier` is the joined supplier name. */
type StockRow = {
  id: string;
  sku: string;
  name: string;
  current_qty: number;
  cost_price?: number | null;
  selling_price?: number | null;
  markup?: number | null;
  created: string;
  updated: string;
  supplier?: string;
  expand?: {
    supplier?: { id?: string; suppliername?: string };
  };
};

// ─── Formatting ────────────────────────────────────────────────────────

/** Format a number as R X.XX, or "—" for missing/non-finite values. */
function fmtMoney(n: number | null | undefined): string {
  if (typeof n !== "number" || !Number.isFinite(n)) return "—";
  return `R ${n.toFixed(2)}`;
}

/** Format a markup percent. PB stores markup as a plain number
 *  (e.g. 80 means 80%); render with a trailing "%" when present. */
function fmtMarkup(n: number | null | undefined): string {
  if (typeof n !== "number" || !Number.isFinite(n)) return "—";
  return `${n}%`;
}

/** Truncate an ISO timestamp to "YYYY-MM-DD HH:MM" in UTC. PB's
 *  autodate strings are already UTC, so no conversion is needed —
 *  we just slice the trailing ":00.000Z" off to keep the table
 *  compact. */
function fmtDateTime(iso: string): string {
  if (!iso) return "—";
  const d = new Date(iso);
  if (Number.isNaN(d.getTime())) return iso;
  const pad = (n: number): string => String(n).padStart(2, "0");
  return (
    `${d.getUTCFullYear()}-${pad(d.getUTCMonth() + 1)}-${pad(d.getUTCDate())}` +
    ` ${pad(d.getUTCHours())}:${pad(d.getUTCMinutes())}`
  );
}

/** Render the gap table. Numeric columns are right-aligned via the
 *  `---:` colon convention so the operator can scan decimals. */
function renderGapTable(rows: StockRow[]): string {
  const lines: string[] = [];
  lines.push(
    "| SKU | Name | Supplier | Cost (R) | Selling (R) | Markup | Qty | Created | Updated |",
  );
  lines.push(
    "| --- | --- | --- | ---: | ---: | ---: | ---: | --- | --- |",
  );

  for (const r of rows) {
    const sku = r.sku || "(no sku)";
    const name = (r.name || "(no name)").slice(0, 60); // keep names from blowing up the table
    const supplier = r.expand?.supplier?.suppliername?.trim() || "—";
    const qty = Number.isFinite(r.current_qty) ? r.current_qty : 0;

    lines.push(
      `| ${sku} | ${name} | ${supplier} | ${fmtMoney(r.cost_price)} | ${fmtMoney(r.selling_price)} | ${fmtMarkup(r.markup)} | ${qty} | ${fmtDateTime(r.created)} | ${fmtDateTime(r.updated)} |`,
    );
  }

  return lines.join("\n");
}

// ─── PB plumbing ──────────────────────────────────────────────────────

/**
 * Pull every `stock_items` row whose `created` or `updated` is on
 * or after `WINDOW_START_ISO`. Both fields are autodate strings in
 * PB and lexicographically compare correctly as ISO-8601 UTC, so
 * the `>=` operator works without parsing.
 *
 * The `~ ` operator is NOT used here — PB's `~` is `contains`,
 * not `regex`, and it's irrelevant for an exact-bound comparison.
 *
 * We do NOT pass a `fields` projection: passing `fields=` together
 * with `expand=supplier` causes PB to drop the `expand` payload
 * (see the comment in `list-irda-stock.ts`). Pulling the full row
 * is fine here — the dataset is bounded by the ~36h window, not
 * by the whole catalogue.
 *
 * Sort: `-updated` so the most-recently-moved rows float to the
 * top. The client-side sub-sort for the gap table also uses
 * `updated DESC` (and then `created DESC`) so an item patched
 * moments ago reads before one patched yesterday morning.
 */
async function listRecentStockItems(windowStartIso: string): Promise<StockRow[]> {
  const pb = await getAdminPocketBase();
  const rows = await pb.collection("stock_items").getFullList<StockRow>({
    filter: `created >= "${windowStartIso}" || updated >= "${windowStartIso}"`,
    expand: "supplier",
    sort: "-updated",
    batch: 500,
  });
  return rows;
}

// ─── Main ─────────────────────────────────────────────────────────────

async function main(): Promise<void> {
  const startTs = Date.now();
  const windowStartIso = WINDOW_START_ISO;
  const windowStart = new Date(windowStartIso);

  console.log(
    `=== Recently-moved stock items (since ${windowStartIso}) ===\n`,
  );

  const allRows = await listRecentStockItems(windowStartIso);
  const totalInWindow = allRows.length;

  // Split the in-window set into "newly created" vs "only patched"
  // by comparing `created` against the window start. An item whose
  // `created` is BEFORE the window but whose `updated` is IN the
  // window was patched (the Monday-night 80% markup run hit a lot
  // of these). An item whose `created` is IN the window is
  // genuinely new.
  const isInWindow = (iso: string): boolean => {
    if (!iso) return false;
    const d = new Date(iso);
    return d.getTime() >= windowStart.getTime();
  };

  const newlyCreated = allRows.filter((r) => isInWindow(r.created));
  const updatedOnly = allRows.filter(
    (r) => !isInWindow(r.created) && isInWindow(r.updated),
  );

  // The gap = cost_price <= 0 OR selling_price <= 0. PB's autodate
  // for missing numerics resolves to 0 / null on the typed shape;
  // we treat anything `!(x > 0)` as missing, matching the
  // convention used in `apply-default-markup.ts` and
  // `list-irda-stock.ts`.
  const isGapRow = (r: StockRow): boolean => {
    const cost = Number(r.cost_price ?? 0);
    const sell = Number(r.selling_price ?? 0);
    return !(cost > 0) || !(sell > 0);
  };

  const gapRows = allRows
    .filter(isGapRow)
    // Sort newest-created/updated first within the gap. Compare on
    // the max(created, updated) so a row created last week but
    // patched yesterday sorts before one created last week and
    // never touched again. Falls back to created if both are
    // missing.
    .toSorted((a, b) => {
      const aMax = Math.max(
        new Date(a.created).getTime() || 0,
        new Date(a.updated).getTime() || 0,
      );
      const bMax = Math.max(
        new Date(b.created).getTime() || 0,
        new Date(b.updated).getTime() || 0,
      );
      return bMax - aMax;
    });

  const missingCost = gapRows.filter((r) => !(Number(r.cost_price ?? 0) > 0));
  const missingSelling = gapRows.filter(
    (r) => !(Number(r.selling_price ?? 0) > 0),
  );
  // Rows that have BOTH prices populated (the "happy path"
  // population — what the markup commit was aiming for).
  const fullyPriced = allRows.filter(
    (r) =>
      Number(r.cost_price ?? 0) > 0 && Number(r.selling_price ?? 0) > 0,
  );

  // Summary block. The operator specifically asked us to be loud
  // about `Items missing cost_price` — if it's zero we say so.
  console.log("Summary:");
  console.log(`  Total items changed in window:           ${totalInWindow}`);
  console.log(`  Items still missing cost_price:            ${missingCost.length}${missingCost.length === 0 ? "   ← no missing-cost rows in window; the gap is purely on the SELL side" : ""}`);
  console.log(`  Items still missing selling_price:         ${missingSelling.length}`);
  console.log(`  Items with both fields populated:          ${fullyPriced.length}`);
  console.log(`  Items created fresh (not just patched):    ${newlyCreated.length}`);
  console.log(`  Items updated only (cost/markup patch):    ${updatedOnly.length}`);

  console.log(
    `\n=== Gap rows: cost_price <= 0 OR selling_price <= 0 (${gapRows.length} row${gapRows.length === 1 ? "" : "s"}) ===\n`,
  );
  if (gapRows.length === 0) {
    console.log(
      "  (none — every in-window row has both cost_price and selling_price populated)",
    );
  } else {
    console.log(renderGapTable(gapRows));
  }

  // A small pattern-recognition addendum: tally supplier frequency
  // and qty=0 rate inside the gap, so the operator can eyeball
  // whether the gap is "one supplier forgot to ship pricing" or
  // "every supplier has a few stragglers".
  if (gapRows.length > 0) {
    const supplierCounts = new Map<string, number>();
    let zeroQty = 0;
    for (const r of gapRows) {
      const name = r.expand?.supplier?.suppliername?.trim() || "(no supplier)";
      supplierCounts.set(name, (supplierCounts.get(name) ?? 0) + 1);
      if (!(Number(r.current_qty) > 0)) zeroQty += 1;
    }
    const supplierLines = [...supplierCounts.entries()]
      .toSorted((a, b) => b[1] - a[1])
      .map(([name, n]) => `    ${n}× ${name}`);

    console.log(`\nGap breakdown by supplier:`);
    console.log(supplierLines.join("\n"));
    console.log(`\nGap rows with current_qty = 0: ${zeroQty} / ${gapRows.length}`);
  }

  const duration = ((Date.now() - startTs) / 1000).toFixed(1);
  console.log(`\n  duration: ${duration}s`);
}

main().catch((err) => {
  console.error("FATAL:", err);
  process.exit(1);
});