WFDASH-WEBAPP/scripts/migrations/2026-07-06-order-items-collection-pool-rename.ts

/**
 * 2026-07-06-order-items-collection-pool-rename.ts
 *
 * Rename `item_status` value `driver_pool` → `collection_pool` on every
 * existing order_items row, so the persisted state matches the UI label
 * ("Collection Pool") and the new write path in
 * `purchase-orders-service.ts:recordPurchaseOrderSendOutcome`.
 *
 * Background:
 *   The internal `item_status` enum value was historically `driver_pool`
 *   because the related `collection_pool` table was the canonical
 *   source-of-truth for queued-for-collection lines. That table was
 *   later renamed to `DO_NOT_USE_collection_pool` (and is no longer
 *   used), but the `item_status` value on order_items was never
 *   renamed to match. Result: UI calls this state "Collection Pool"
 *   but the underlying value is `driver_pool`. New writes (post-rename
 *   code) produce `collection_pool`, so old rows show up on the wrong
 *   filter tab / count until migrated.
 *
 * This script does TWO things, both idempotent:
 *   1. Ensure the schema's `item_status.values` includes `collection_pool`
 *      (and not `driver_pool`). If `driver_pool` is missing or the
 *      field's options are stale, PB rejects the row updates.
 *   2. Update every existing row from `driver_pool` → `collection_pool`.
 *
 * Row updates are batched at 200 per call to stay under the 5MB payload
 * limit on bulk updates.
 */
import { getAdminPocketBase } from "@/lib/server/pocketbase-admin";

const ORDER_ITEMS = "order_items";
const BATCH_SIZE = 200;

/** Target enum values for `item_status`. Must stay in sync with
 *  `src/lib/types.ts:ItemStatus` and `src/lib/server/order-item-status.ts`. */
const TARGET_ITEM_STATUS_VALUES = [
  "pending",
  "processed",
  "po_generated",
  "collection_pool",
  "assigned",
  "collected",
  "stock_verified",
  "picking",
  "ready_delivery",
  "delivered",
  "cancelled",
];

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

  // ── 1. Reconcile the schema's select options ───────────────────
  // PB rejects row updates when the value being written isn't in
  // the field's `values` array. Add `collection_pool` first (so the
  // migration below can write it), and remove `driver_pool` from
  // the enum so no NEW rows can land on the old name. PB keeps the
  // existing `driver_pool` rows untouched until step 2 flips them.
  const coll = await pb.collections.getOne(ORDER_ITEMS);
  const fields = (coll.fields ?? []) as Array<{
    name?: string;
    values?: string[];
    maxSelect?: number;
  }>;
  let schemaChanged = 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];
      schemaChanged = true;
    }
    break;
  }
  if (schemaChanged) {
    await pb.collections.update(ORDER_ITEMS, { fields });
    console.log(
      "[migration:collection-pool-rename] schema reconciled — " +
        "item_status.values now: " +
        JSON.stringify(TARGET_ITEM_STATUS_VALUES)
    );
  }

  // ── 2. Update every row stuck on the old name ──────────────────
  let page = 1;
  let totalUpdated = 0;
  let totalFailed = 0;

  // eslint-disable-next-line no-constant-condition
  while (true) {
    const batch = await pb.collection(ORDER_ITEMS).getList(page, BATCH_SIZE, {
      filter: `item_status = "driver_pool"`,
      fields: "id,item_status",
    });
    if (batch.items.length === 0) break;

    for (const row of batch.items) {
      try {
        await pb.collection(ORDER_ITEMS).update(row.id, {
          item_status: "collection_pool",
        });
        totalUpdated++;
      } catch (err) {
        // Don't abort the whole migration on a single bad row — log
        // and continue. PB's row-level errors are rare but possible
        // (concurrent edit, missing field, etc.).
        const msg = err instanceof Error ? err.message : String(err);
        console.warn(
          `[migration:collection-pool-rename] failed to update ${row.id}: ${msg}`
        );
        totalFailed++;
      }
    }

    if (batch.items.length < BATCH_SIZE) break;
    page++;
  }

  // ── 3. Verify ───────────────────────────────────────────────────
  const remaining = await pb.collection(ORDER_ITEMS).getList(1, 1, {
    filter: `item_status = "driver_pool"`,
  });

  console.log(
    `[migration:collection-pool-rename] done — updated ${totalUpdated}, ` +
      `failed ${totalFailed}, remaining on driver_pool: ${remaining.totalItems}`
  );
}

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