WFDASH-WEBAPP/scripts/migrations/2026-07-10-restore-counters.ts

/* eslint-disable */
/**
 * Migration — restore the `quote_counters` collection and add the new
 * `order_counters` collection.
 *
 * WHY THIS EXISTS
 * ───────────────
 * Both `quotes-service.ts` and `orders-service.ts` rely on dedicated
 * PocketBase collections to atomically generate per-(year, month)
 * sequence numbers (the `QYYYYMMNNN` quote number and the
 * `ORD-YYYY-MM-NNN` order number). The atomic guarantee comes from
 * PocketBase's `last_seq+` increment operator — a single read-then-write
 * that the server evaluates against the current row state, so two
 * concurrent callers can never observe the same value and collide.
 *
 * If either counter collection is missing, the next number generation
 * crashes with a 404 ("Missing collection context"). The destructive
 * `scripts/setup-pocketbase.ts` (used to wipe the dev PB before a
 * rebuild) was the most common cause — it deleted the counter tables
 * along with everything else.
 *
 * This migration:
 *   1. Creates `quote_counters` if it doesn't exist (re-creates after a
 *      wipe). Schema mirrors `docs/pocketbase/quote_counters.collection.json`.
 *   2. Creates `order_counters` if it doesn't exist. Schema mirrors
 *      `docs/pocketbase/order_counters.collection.json`. This one was
 *      new as of 2026-07-10 — before then, `orders-service.ts` had no
 *      atomic counter and used a read-then-write fallback that was
 *      racy under concurrent order creation.
 *   3. For each new counter row created in this run, backfills
 *      `last_seq` from any pre-existing records (quotes / orders) for
 *      that (year, month) so the first generated number doesn't
 *      collide with existing data.
 *
 * IDEMPOTENT — running twice is a no-op. The script logs what it did
 * (or skipped) and exits 0 either way.
 *
 * Required env (in .env or .env.local):
 *   NEXT_PUBLIC_POCKETBASE_URL
 *   PB_ADMIN_EMAIL
 *   PB_ADMIN_PASSWORD
 *
 * Run with:
 *   npx tsx scripts/migrations/2026-07-10-restore-counters.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 {
    /* file not present — fine, rely on shell env */
  }
}
loadEnvFile(".env");
loadEnvFile(".env.local");

const AUTH_RULE = "@request.auth.id != ''";

type CounterDef = {
  name: "quote_counters" | "order_counters";
  // The collection whose pre-existing rows we backfill from.
  backfillSource: "quotes" | "orders";
  // The numeric field on the source that we extract the sequence from.
  sourceField: "quote_number" | "order_number";
  // Regex group 1 captures the trailing integer sequence.
  sequenceRegex: RegExp;
};

const COUNTERS: CounterDef[] = [
  {
    name: "quote_counters",
    backfillSource: "quotes",
    sourceField: "quote_number",
    // Match the current no-dash format `QYYYYMMNNN`. Also accept the
    // legacy dashed `Q-YYYY-MM-NNN` so an in-flight migration doesn't
    // strand the count. The year/month extraction below does the same.
    sequenceRegex: /^Q-?(\d{4})-?(\d{2})-?(\d+)$/,
  },
  {
    name: "order_counters",
    backfillSource: "orders",
    sourceField: "order_number",
    sequenceRegex: /^ORD-(\d{4})-(\d{2})-(\d+)$/,
  },
];

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

  // 1. Find which counter collections are missing.
  const allCols = await pb.collections.getFullList();
  const existing = new Set(allCols.map((c) => c.name));

  let createdCollections = 0;
  let createdRows = 0;
  let backfilledRows = 0;

  for (const def of COUNTERS) {
    if (existing.has(def.name)) {
      console.log(`[counters] ${def.name} already exists — skip creation.`);
    } else {
      await pb.collections.create({
        name: def.name,
        type: "base",
        system: false,
        listRule: AUTH_RULE,
        viewRule: AUTH_RULE,
        createRule: AUTH_RULE,
        updateRule: AUTH_RULE,
        deleteRule: AUTH_RULE,
        fields: [
          {
            name: "year",
            type: "number",
            required: true,
            min: 2000,
            max: 2999,
            noDecimal: true,
          },
          {
            name: "month",
            type: "number",
            required: true,
            min: 1,
            max: 12,
            noDecimal: true,
          },
          {
            name: "last_seq",
            type: "number",
            required: false,
            min: 0,
            noDecimal: true,
          },
        ],
        indexes: [
          `CREATE UNIQUE INDEX idx_${def.name}_year_month ON ${def.name} (year, month)`,
        ],
      });
      createdCollections++;
      console.log(`[counters] Created ${def.name}.`);
    }

    // 2. Backfill any (year, month) where the source collection has rows
    //    but the counter does not. Without this, the first generate
    //    after a wipe would start at 001 and collide with the
    //    existing NNN-th record.
    const countersRows = await pb.collection(def.name).getFullList<{
      year: number;
      month: number;
    }>();
    const counterKeys = new Set(
      countersRows.map((r) => `${r.year}-${r.month}`),
    );

    const sourceRows = await pb
      .collection(def.backfillSource)
      .getFullList<Record<string, unknown>>({ fields: def.sourceField });
    const byMonth = new Map<string, number>();
    for (const row of sourceRows) {
      const num = row[def.sourceField];
      if (typeof num !== "string") continue;
      const m = num.match(def.sequenceRegex);
      if (!m) continue;
      // Groups depend on the regex per def above. For the quote regex
      // it's `[full, year, month, seq]`; for orders it's `[full, year,
      // month, seq]` too. Always the last group is the sequence, year
      // and month are the prior two.
      const year = parseInt(m[1], 10);
      const month = parseInt(m[2], 10);
      const seq = parseInt(m[3], 10);
      if (!Number.isFinite(year) || !Number.isFinite(month) || !Number.isFinite(seq)) {
        continue;
      }
      if (month < 1 || month > 12 || year < 2000 || year > 2999) continue;
      const key = `${year}-${month}`;
      const cur = byMonth.get(key) ?? 0;
      if (seq > cur) byMonth.set(key, seq);
    }

    for (const [key, maxSeq] of byMonth.entries()) {
      if (counterKeys.has(key)) continue;
      const [yearStr, monthStr] = key.split("-");
      const year = parseInt(yearStr, 10);
      const month = parseInt(monthStr, 10);
      await pb.collection(def.name).create({
        year,
        month,
        last_seq: maxSeq,
      });
      createdRows++;
      backfilledRows++;
      console.log(
        `[counters] Backfilled ${def.name} (${key}) with last_seq=${maxSeq} (from existing ${def.backfillSource} rows).`,
      );
    }
  }

  console.log(
    `[counters] Done. Collections created: ${createdCollections}, counter rows created: ${createdRows}, backfilled: ${backfilledRows}.`,
  );
  console.log("[counters] Migration complete.");
}

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