WFDASH-WEBAPP/scripts/migrations/2026-06-27-drop-orders-status-and-wipe.ts

/* eslint-disable */
/**
 * Migration: ditch the `orders.status` lifecycle field.
 *
 * Why this exists:
 *   `orders.status` was a stored enum kept in sync with the items
 *   by `recomputeAndPersistOrderStatus` after every item transition.
 *   In practice it desynced from the items (the memo lagged, the
 *   UI computed its own value, etc) — the user-visible symptom was
 *   the order list showing "new" while the order detail page showed
 *   "processing" / "collection pool", and a refresh flipping them
 *   around.
 *
 * The fix is structural: stop storing a header status at all.
 * Item-level `order_items.item_status` is the source of truth. The
 * UI tabs on /orders filter by item status groups, and the only
 * order-level indicator that remains is a derived "Complete" badge
 * when every item is `delivered`.
 *
 * Steps (idempotent — safe to re-run):
 *
 *   1. Drop `orders.status` (select field) if present.
 *   2. Add `orders.invoiced` (bool, default false) if absent.
 *      Kept separate from the dropped status so a future "Mark
 *      Invoiced" action has somewhere to write without re-introducing
 *      a lifecycle enum.
 *   3. Wipe every row in `orders` and `order_items`.
 *      WFDASH data is currently all test data per the operator; this
 *      gives us a clean slate so the UI can be rebuilt without
 *      contorting around stale legacy rows.
 *
 * The migration does NOT touch `collection_pool`, `purchase_orders`,
 * `delivery_notes`, `repairs`, or any other collection that points
 * back to orders — those FKs stay intact. After the wipe, just create
 * fresh orders via /orders/new or /api/v1/orders/from-quote.
 *
 * Required env (in .env / .env.local / shell):
 *   NEXT_PUBLIC_POCKETBASE_URL
 *   PB_ADMIN_EMAIL
 *   PB_ADMIN_PASSWORD
 *
 * Usage:  npx tsx scripts/migrations/2026-06-27-drop-orders-status-and-wipe.ts
 */
import PocketBase from "pocketbase";
import { readFileSync } from "node:fs";
import { resolve } from "node:path";

// Load .env / .env.local if present (tsx doesn't auto-load them).
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 {
    // file not present — fine, rely on shell env
  }
}
loadEnvFile(".env");
loadEnvFile(".env.local");

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(`[drop-orders-status] Authenticated against ${url}`);

  // ---------------------------------------------------------------------
  // 1. Drop orders.status
  // ---------------------------------------------------------------------
  const orders = await pb.collections.getOne("orders");
  const statusField = orders.fields.find((f) => f.name === "status");

  if (statusField) {
    if (statusField.type !== "select") {
      throw new Error(
        `orders.status exists but is type "${statusField.type}", not "select" — refusing to drop.`
      );
    }
    const nextFields = orders.fields
      .filter((f) => f.name !== "status")
      // Drop any legacy options.values too, in case a prior migration
      // wrote to the wrong location (matches the 2026-06-26 fix).
      .map((f) => {
        if (!f.options || typeof f.options !== "object") return f;
        const opts = { ...(f.options as Record<string, unknown>) };
        delete opts.values;
        return { ...f, options: opts };
      });
    await pb.collections.update(orders.id, { fields: nextFields });
    console.log("[drop-orders-status] Dropped orders.status (select field).");
  } else {
    console.log("[drop-orders-status] orders.status already absent — skipping.");
  }

  // Re-fetch the orders schema so step 2 sees the post-drop state.
  const ordersFresh = await pb.collections.getOne("orders");

  // ---------------------------------------------------------------------
  // 2. Add orders.invoiced (bool, default false)
  // ---------------------------------------------------------------------
  const hasInvoiced = ordersFresh.fields.some((f) => f.name === "invoiced");
  if (!hasInvoiced) {
    await pb.collections.update(ordersFresh.id, {
      fields: [
        ...ordersFresh.fields,
        {
          name: "invoiced",
          type: "bool",
          required: false,
          presentable: false,
        },
      ],
    });
    console.log("[drop-orders-status] Added orders.invoiced (bool, default false).");
  } else {
    console.log("[drop-orders-status] orders.invoiced already present — skipping.");
  }

  // ---------------------------------------------------------------------
  // 3. Wipe orders + order_items
  // ---------------------------------------------------------------------
  for (const collectionName of ["order_items", "orders"]) {
    let page = 1;
    let scanned = 0;
    let deleted = 0;
    while (true) {
      const batch = await pb.collection(collectionName).getList(page, 500, {
        fields: "id",
      });
      if (batch.items.length === 0) break;
      for (const row of batch.items) {
        scanned++;
        try {
          await pb.collection(collectionName).delete(row.id);
          deleted++;
        } catch (err: unknown) {
          const msg = err instanceof Error ? err.message : String(err);
          console.error(
            `[drop-orders-status] Failed to delete ${collectionName}/${row.id}: ${msg}`
          );
          throw err;
        }
      }
      if (batch.page >= batch.totalPages) break;
      page++;
    }
    console.log(
      `[drop-orders-status] ${collectionName}: scanned ${scanned}, deleted ${deleted}.`
    );
  }

  console.log("[drop-orders-status] Migration complete.");
}

main().catch((err) => {
  console.error("[drop-orders-status] Migration failed:", err);
  process.exit(1);
});