WFDASH-WEBAPP/scripts/2026-08-21-backfill-order-duplicates.ts

/**
 * Backfill: cancel existing duplicate orders so the UNIQUE index
 * on `orders.email_message_id` can be created.
 *
 * Context
 * ───────
 * The 2026-08-21 order-pipeline investigation (commit fb28376) found
 * that 84.6% of auto-created orders are duplicates of the same email.
 * One worst case: a single Master Drilling email produced
 * 138 duplicate orders, one every 5 minutes for hours.
 *
 * Cause: the IMAP poll keeps returning the same email every 5 minutes,
 * and the order-creation flow has no `email_message_id` dedup check.
 * The deploy on 2026-08-21 added the dedup check (n8n + service
 * layer), but the 423+ existing duplicates are still in the DB.
 *
 * What this script does
 * ─────────────────────
 * 1. Calls `findDuplicateOrders()` to enumerate every duplicate group
 *    that's still "on the board" (has at least one active item).
 * 2. For each group, keeps the OLDEST record (first created) and
 *    cancels the rest via `cancelOrder({ allowDelivered: true })`.
 * 3. Logs the outcome per group.
 *
 * Operators can run the dedicated `/orders` "Find duplicates" dialog
 * to review the per-item details before cancelling; this script is
 * the "I trust the data, just clean it up" path.
 *
 * Idempotent: re-running produces no further changes once every
 * duplicate group has been reduced to one record.
 *
 * Run with:
 *   npx tsx --env-file=.env.local scripts/2026-08-21-backfill-order-duplicates.ts
 *
 * Requires .env.local with NEXT_PUBLIC_POCKETBASE_URL, PB_ADMIN_EMAIL,
 * PB_ADMIN_PASSWORD.
 */

import "./_env"; // side-effect import: loads .env.local before anything else
import {
  findDuplicateOrders,
  cancelOrder,
} from "../src/lib/server/orders-service";

const DRY_RUN = process.argv.includes("--dry-run");

async function main() {
  console.log("Finding duplicate orders (active only)...");
  const groups = await findDuplicateOrders();
  console.log(`Found ${groups.length} duplicate groups.`);

  if (DRY_RUN) {
    console.log("\n[dry-run] would cancel the following:");
    for (const g of groups) {
      const sorted = [...g.records].sort((a, b) => a.created.localeCompare(b.created));
      const keep = sorted[0];
      const cancel = sorted.slice(1);
      console.log(
        `  group ${g.kind}="${g.orderNumber}"  keep=${keep.id} (created ${keep.created})  cancel=${cancel.length}`,
      );
      for (const r of cancel) {
        console.log(`    - ${r.id} (created ${r.created})`);
      }
    }
    return;
  }

  let totalKept = 0;
  let totalCancelled = 0;
  let totalFailed = 0;
  const failures: Array<{ id: string; error: string }> = [];

  for (const group of groups) {
    const sorted = [...group.records].sort((a, b) => a.created.localeCompare(b.created));
    const keep = sorted[0];
    const toCancel = sorted.slice(1);
    totalKept += 1;
    console.log(
      `\ngroup ${group.kind}="${group.orderNumber}"  (${group.records.length} records)  keep=${keep.id} created=${keep.created}`,
    );

    for (const r of toCancel) {
      try {
        const result = await cancelOrder(r.id, { allowDelivered: true });
        console.log(
          `  ✓ cancelled ${r.id}  itemsCancelled=${result.itemsCancelled}  itemsSkipped=${result.itemsSkipped.length}`,
        );
        totalCancelled += 1;
      } catch (err) {
        const msg = err instanceof Error ? err.message : String(err);
        console.log(`  ✗ failed ${r.id}: ${msg}`);
        failures.push({ id: r.id, error: msg });
        totalFailed += 1;
      }
    }
  }

  console.log("\n=== Backfill summary ===");
  console.log(`  groups:        ${groups.length}`);
  console.log(`  records kept:  ${totalKept}`);
  console.log(`  cancelled:     ${totalCancelled}`);
  console.log(`  failed:        ${totalFailed}`);
  if (failures.length > 0) {
    console.log("\nFailures:");
    for (const f of failures) {
      console.log(`  ${f.id}: ${f.error}`);
    }
    process.exitCode = 1;
  }
}

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