WFDASH-WEBAPP/scripts/migrations/2026-07-20-delivery-tracking.ts

/**
 * Migration — 2026-07-20-delivery-tracking.ts
 *
 * Schema changes for the new /deliveries page (Operations section).
 * Tracks the post-invoice physical-delivery loop: load onto truck →
 * driver brings back signed paper → operator checks in with the
 * reference number. (The future n8n step that uploads the signed
 * copy + auto-completes the item lands on `completed`; that workflow
 * is a separate doc — see docs/deliveries-n8n-workflow.md once
 * shipped.)
 *
 * ## What it does
 *
 * 1. `order_items.item_status` select gets 3 new values:
 *      with_driver  — item is on the truck / out with the driver
 *      checked_in   — operator typed the reference number; verified
 *      completed    — reserved for the n8n signed-copy flow
 *
 *    The pre-existing `invoiced` value is the source state the
 *    "To Load" tab filters on; the "Load for driver" action flips
 *    `invoiced → with_driver` directly. `to_load` is NOT a new
 *    value — the warehouse-queue concept is handled by leaving
 *    items at `invoiced` until the operator is ready to load.
 *
 * 2. `order_items` gets 4 new fields:
 *      loaded_at          (date)    — when the item was loaded for driver
 *      delivery_reference (text)    — invoice/DN number on the signed paper
 *      checked_in_at      (date)    — when the operator typed the reference
 *      checked_in_by      (relation → users) — who typed the reference
 *
 *    `delivery_reference` is the join key the operator uses when the
 *    driver hands back a paper — the same string they type on the
 *    "Check in" modal. It's free-text on purpose: a customer might
 *    use "INV-12345", a QuickBooks number, or the original DN number;
 *    we don't normalise.
 *
 * Idempotent — safe to re-run. Adds missing values / fields only;
 * leaves anything that already matches the target schema alone.
 *
 * Run with:
 *   npx tsx scripts/migrations/2026-07-20-delivery-tracking.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");

function getAdminPocketBase(): PocketBase {
  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 (sourced from .env.local).",
    );
  }
  return new PocketBase(url);
}

async function authedPb(): Promise<PocketBase> {
  const pb = getAdminPocketBase();
  await pb
    .collection("_superusers")
    .authWithPassword(
      process.env.PB_ADMIN_EMAIL!,
      process.env.PB_ADMIN_PASSWORD!,
    );
  return pb;
}

const ORDER_ITEMS = "order_items";
const USERS_AUTH = "_pb_users_auth_";

// 3 new item_status values. Existing values are kept so legacy rows
// render unchanged. The "to_load" intermediate value mentioned in
// early discussion was deliberately dropped — the "To Load" tab
// filters on the existing `invoiced` value (the warehouse-queue
// concept is implicit in "operator hasn't hit Load yet").
const TARGET_ITEM_STATUS_VALUES = [
  "pending",
  "processed",
  "po_generated",
  "collection_pool",
  "assigned",
  "collected",
  "stock_verified",
  "picking",
  "ready_delivery",
  "delivered",
  "cancelled",
  "ready_invoice",
  "invoiced",
  "stock_booked",
  // New for the /deliveries flow (2026-07-20):
  "with_driver",
  "checked_in",
  "completed",
];

// 4 new fields on order_items. Each is fully specified so a clean
// `pb.collections.update` works without "rebuild" surprises — type,
// nullability, and the relation target are all explicit.
//
// Field IDs are generated to match PB's pattern of 10-char random
// strings. They don't need to be deterministic across re-runs; PB
// only cares that they're unique within the collection.
function buildNewFields() {
  return [
    {
      // When the item was loaded onto the truck. Stamped by
      // POST /api/v1/deliveries/load. Nullable: items that
      // never left the warehouse (still `invoiced`) have no
      // loaded_at yet.
      name: "loaded_at",
      type: "date",
      required: false,
      presentable: false,
    },
    {
      // Invoice/DN number the driver brought back on the
      // signed paper. Typed by the operator in the
      // "Check in" modal. Nullable until the operator
      // checks the item in. Free text — no enum, no
      // normalisation. Max 64 chars matches the
      // legacy_order_item_id field above for consistency.
      name: "delivery_reference",
      type: "text",
      required: false,
      max: 64,
      min: 0,
      pattern: "",
      presentable: false,
    },
    {
      // When the operator typed the reference. Nullable
      // until check-in. Stamped by the check-in API
      // route.
      name: "checked_in_at",
      type: "date",
      required: false,
      presentable: false,
    },
    {
      // Who typed the reference. Nullable until
      // check-in. Single relation to the users auth
      // collection — same target as `drv_id` and
      // `driver` on this row. cascadeDelete: false —
      // we keep the audit row even if the user is
      // later deleted (PB will null the relation
      // automatically, but the timestamp + reference
      // survive).
      name: "checked_in_by",
      type: "relation",
      required: false,
      collectionId: USERS_AUTH,
      cascadeDelete: false,
      maxSelect: 1,
      minSelect: 0,
      presentable: false,
    },
  ];
}

type AnyField = {
  name: string;
  type?: string;
  required?: boolean;
  values?: string[];
  maxSelect?: number;
  minSelect?: number;
  collectionId?: string;
  max?: number;
  min?: number;
  pattern?: string;
  presentable?: boolean;
  [k: string]: unknown;
};

export async function run(): Promise<void> {
  const pb = await authedPb();

  // ── 1. Reconcile `order_items.item_status.values` ────────────
  const col = await pb.collections.getOne(ORDER_ITEMS);
  const fields = (col.fields ?? []) as AnyField[];
  let statusChanged = false;
  for (const f of fields) {
    if (f.name !== "item_status") continue;
    const current = new Set(f.values ?? []);
    const target = new Set(TARGET_ITEM_STATUS_VALUES);
    const needsAdd = [...target].some((v) => !current.has(v));
    const needsRemove = [...current].some((v) => !target.has(v));
    if (needsAdd || needsRemove) {
      f.values = [...target];
      statusChanged = true;
    }
    break;
  }
  if (statusChanged) {
    await pb.collections.update(ORDER_ITEMS, { fields });
    console.log(
      "[migration:delivery-tracking] ✓ added with_driver / checked_in / completed to order_items.item_status.values",
    );
  } else {
    console.log(
      "[migration:delivery-tracking] order_items.item_status already has the new values — skip",
    );
  }

  // ── 2. Add missing fields on order_items ─────────────────────
  const fresh = await pb.collections.getOne(ORDER_ITEMS);
  const freshFields = (fresh.fields ?? []) as AnyField[];
  const existingNames = new Set(freshFields.map((f) => f.name));
  const wanted = buildNewFields().filter((f) => !existingNames.has(f.name));
  if (wanted.length > 0) {
    const next = [...freshFields, ...wanted];
    await pb.collections.update(ORDER_ITEMS, { fields: next });
    console.log(
      `[migration:delivery-tracking] ✓ added ${wanted.length} field(s) to order_items: ${wanted
        .map((f) => f.name)
        .join(", ")}`,
    );
  } else {
    console.log(
      "[migration:delivery-tracking] all 4 new fields already exist on order_items — skip",
    );
  }

  // ── 3. Verify ────────────────────────────────────────────────
  const vCol = await pb.collections.getOne(ORDER_ITEMS);
  const vFields = (vCol.fields ?? []) as AnyField[];
  const statusField = vFields.find((f) => f.name === "item_status");
  const loadedAt = vFields.find((f) => f.name === "loaded_at");
  const deliveryRef = vFields.find((f) => f.name === "delivery_reference");
  const checkedInAt = vFields.find((f) => f.name === "checked_in_at");
  const checkedInBy = vFields.find((f) => f.name === "checked_in_by");

  const statusValues = new Set(statusField?.values ?? []);
  const checks: Array<[string, boolean]> = [
    [
      "item_status.values includes with_driver",
      statusValues.has("with_driver"),
    ],
    [
      "item_status.values includes checked_in",
      statusValues.has("checked_in"),
    ],
    [
      "item_status.values includes completed",
      statusValues.has("completed"),
    ],
    ["order_items has loaded_at (date)", loadedAt?.type === "date"],
    [
      "order_items has delivery_reference (text)",
      deliveryRef?.type === "text",
    ],
    [
      "order_items has checked_in_at (date)",
      checkedInAt?.type === "date",
    ],
    [
      "order_items has checked_in_by (relation → users)",
      checkedInBy?.type === "relation" &&
        checkedInBy?.collectionId === USERS_AUTH,
    ],
  ];

  let allOk = true;
  for (const [label, ok] of checks) {
    console.log(`  ${ok ? "✓" : "✗"} ${label}`);
    if (!ok) allOk = false;
  }

  if (!allOk) {
    throw new Error(
      "[migration:delivery-tracking] verification failed — see ✗ lines above",
    );
  }
  console.log("[migration:delivery-tracking] Migration complete.");
}

if (require.main === module) {
  run().catch((err) => {
    console.error("[migration:delivery-tracking] fatal:", err);
    process.exit(1);
  });
}