WFDASH-WEBAPP/scripts/migrations/2026-10-07-add-pos-sales-refund-fields.ts

/* eslint-disable */
/**
 * Migration: add refund fields to `pos_sales`.
 *
 * ## Why
 *
 * A customer who brings goods back needs the sale reversed: the items go
 * back into stock and the money comes back off the till. There was no way
 * to mark a POS sale as refunded, so nothing stopped the same sale being
 * refunded twice — which would credit the customer twice and put the
 * stock back on the shelf twice.
 *
 * Refunds are WHOLE-SALE only by operator decision (2026-10-07): no
 * partial refunds. That is what makes three plain columns enough — a sale
 * is either refunded or it isn't, so a boolean is the complete answer and
 * no `pos_refunds` collection is needed. If partial refunds are ever
 * wanted this schema has to grow a per-line refund table; the boolean
 * here becomes "fully refunded".
 *
 * The stock side needs no schema at all: `return` is already a member of
 * `STOCK_REASON_VALUES`, it just has never been used. The refund writes
 * `stock_ledger` rows with `reason: "return"`, positive `change_qty`,
 * and `reference_type: "pos_sale_refund"` so the reversal is traceable
 * back to the sale without overwriting the original
 * `customer_fulfillment` rows. The ledger is the stock-side audit trail;
 * these three columns are the money-side one.
 *
 * ## What it does
 *
 * Adds to `pos_sales`:
 *
 *   - `refunded`       (bool, default false)    Has this sale been reversed?
 *   - `refunded_at`    (date, optional)          When.
 *   - `refund_amount`  (number, optional)        The rand value credited back.
 *                                                 Stamped from the sale's
 *                                                 post-discount `total`, which
 *                                                 is what the customer actually
 *                                                 paid.
 *
 * Idempotent — safe to re-run.
 *
 * Run with:
 *   npx tsx scripts/migrations/2026-10-07-add-pos-sales-refund-fields.ts
 */
import PocketBase from "pocketbase";
import { readFileSync } from "node:fs";
import { resolve } from "node:path";

function loadEnvFile(name: string) {
  try {
    const raw = readFileSync(resolve(process.cwd(), name), "utf8");
    for (const line of raw.split("\n")) {
      const trimmed = line.trim();
      if (!trimmed || trimmed.startsWith("#")) continue;
      const m = trimmed.match(/^([A-Z0-9_]+)\s*=\s*(.+)$/i);
      if (m && !process.env[m[1]]) {
        process.env[m[1]] = m[2].replace(/^["']|["']$/g, "").trim();
      }
    }
  } catch {
    /* fine — rely on shell env */
  }
}
loadEnvFile(".env");
loadEnvFile(".env.local");

const TAG = "[pos-sales-refund]";

async function main() {
  const url = process.env.NEXT_PUBLIC_POCKETBASE_URL;
  const email = process.env.PB_ADMIN_EMAIL;
  const password = process.env.PB_ADMIN_PASSWORD;
  if (!url || !email || !password) {
    throw new Error(
      "Set NEXT_PUBLIC_POCKETBASE_URL, PB_ADMIN_EMAIL, PB_ADMIN_PASSWORD (e.g. sourced from .env.local).",
    );
  }

  const pb = new PocketBase(url);
  await pb.collection("_superusers").authWithPassword(email, password);
  console.log(`${TAG} Authenticated against ${url}`);

  const posSales = await pb.collections.getOne("pos_sales");
  const existing = new Set(
    (posSales.fields as Array<{ name: string }>).map((f) => f.name),
  );

  // Key shapes copied from the live schema rather than invented — PB rejects
  // a field object it doesn't recognise. `id` is generated by PB on write.
  // `date` carries min/max "" and `number` min/max null, matching
  // customers.dateadded and pos_sales.cash_given respectively.
  const additions = [
    {
      name: "refunded",
      type: "bool",
      required: false,
      presentable: false,
    },
    {
      name: "refunded_at",
      type: "date",
      required: false,
      presentable: false,
      min: "",
      max: "",
    },
    {
      name: "refund_amount",
      type: "number",
      required: false,
      presentable: false,
      min: null,
      max: null,
      onlyInt: false,
    },
  ].filter((f) => !existing.has(f.name));

  if (additions.length === 0) {
    console.log(`${TAG} pos_sales refund fields already present — nothing to do.`);
    return;
  }

  await pb.collections.update(posSales.id, {
    fields: [...posSales.fields, ...additions],
  });
  console.log(`${TAG} Added to pos_sales: ${additions.map((f) => f.name).join(", ")}`);

  const after = await pb.collections.getOne("pos_sales");
  const nowHave = new Set((after.fields as Array<{ name: string }>).map((f) => f.name));
  for (const f of additions) {
    if (!nowHave.has(f.name)) throw new Error(`${TAG} field ${f.name} missing after write`);
  }
  console.log(`${TAG} Verified on the live schema.`);
}

main().catch((err) => {
  console.error(`${TAG} FAILED:`, err);
  process.exit(1);
});