jorgecuadros-platform/scripts/numid-audit.mjs

#!/usr/bin/env node
/**
 * NUMid recycle audit.
 *
 * Prints which portal NUMids (customer_legacy_refs, utilities/DATGRAL) are dead
 * enough to hand to a new customer, and which are merely quiet. Read-only: it
 * writes nothing and reassigns nothing.
 *
 *   node scripts/numid-audit.mjs            # summary + both candidate tiers
 *   node scripts/numid-audit.mjs --csv      # full per-NUMid table on stdout
 *   node scripts/numid-audit.mjs --numid 501
 *
 * Needs DATABASE_URL. Run it against PROD before acting on anything: the tiers
 * describe whatever database it is pointed at, and a stale copy will happily
 * report a NUMid as empty that prod has been billing all year.
 */
import { readFileSync } from "node:fs";
import { dirname, join } from "node:path";
import { fileURLToPath } from "node:url";
// Relative, not "@jorgecuadros/database": the workspace link is not always
// present at the repo root, and this script has to run from a bare checkout and
// from inside the API container alike.
import pkg from "../packages/database/generated/client/index.js";

const { PrismaClient } = pkg;

const HERE = dirname(fileURLToPath(import.meta.url));

/**
 * Anything here means the id carries history a new owner would inherit.
 * Counted, not sampled: a single row in any of them disqualifies.
 */
const HISTORY_COLUMNS = [
  "veh",
  "trust",
  "stmt",
  "ocr",
  "enl",
  "elog",
  "ash",
  "nopago",
];

const n = (v) => (v == null ? 0 : Number(v));
const hasHistory = (r) => HISTORY_COLUMNS.some((c) => n(r[c]) > 0);
const zeroBalance = (r) => n(r.balMxn) === 0 && n(r.balUsd) === 0;

/**
 * EMPTY — the id was created and never used. Safe for an allocator to take
 * without a human looking, subject to the legacy check below.
 *
 * "Never transacted" means zero movements once the synthetic opening-balance row
 * is removed; that row exists for all 1,171 NUMids and is not evidence of use.
 * Services are checked as anySvc rather than activeSvc, because a deactivated
 * water account still says a person once lived behind this id.
 */
const isEmpty = (r) =>
  n(r.realTx) === 0 &&
  zeroBalance(r) &&
  n(r.anySvc) === 0 &&
  n(r.anyPol) === 0 &&
  n(r.insRef) === 0 &&
  n(r.hasEmail) === 0 &&
  !hasHistory(r);

/**
 * DORMANT — used once, quiet for years, owes nothing. NOT auto-allocatable:
 * a returning snowbird is indistinguishable from an abandoned account here.
 */
const isDormant = (r) =>
  !isEmpty(r) &&
  n(r.realTx36m) === 0 &&
  zeroBalance(r) &&
  n(r.activeSvc) === 0 &&
  n(r.activePol) === 0 &&
  !hasHistory(r);

function line(r) {
  return (
    `  ${String(r.numid).padStart(5)}  ${(r.name || "(sin nombre)").slice(0, 30).padEnd(30)}` +
    ` last=${(r.lastRealTx ? new Date(r.lastRealTx).toISOString().slice(0, 10) : "never").padStart(10)}` +
    ` tx=${String(n(r.realTx)).padStart(3)}` +
    ` bal=${n(r.balMxn).toFixed(2).padStart(10)}` +
    ` svc=${n(r.anySvc)}` +
    ` pol=${n(r.anyPol)}`
  );
}

async function main() {
  const args = process.argv.slice(2);
  const prisma = new PrismaClient();
  try {
    const sql = readFileSync(join(HERE, "numid-audit.sql"), "utf8");
    const rows = await prisma.$queryRawUnsafe(sql);

    const one = args.indexOf("--numid");
    if (one !== -1) {
      const want = Number(args[one + 1]);
      const r = rows.find((x) => Number(x.numid) === want);
      if (!r) {
        console.log(`NUMid ${want} is not in the utilities/DATGRAL pool.`);
        return;
      }
      console.log(JSON.stringify(r, (_k, v) => (typeof v === "bigint" ? Number(v) : v), 2));
      console.log(
        `\nverdict: ${isEmpty(r) ? "EMPTY" : isDormant(r) ? "DORMANT" : "IN USE"}`,
      );
      return;
    }

    if (args.includes("--csv")) {
      const cols = Object.keys(rows[0]);
      console.log(cols.join(","));
      for (const r of rows) {
        console.log(cols.map((c) => JSON.stringify(r[c] ?? "")).join(","));
      }
      return;
    }

    const empty = rows.filter(isEmpty);
    const dormant = rows.filter(isDormant);
    const max = rows.reduce((m, r) => Math.max(m, Number(r.numid)), 0);

    console.log(`pool: ${rows.length} NUMids, max ${max}`);
    console.log(`  EMPTY   (never used, auto-allocatable): ${empty.length}`);
    console.log(`  DORMANT (quiet, needs a human):         ${dormant.length}`);
    console.log(`  IN USE:                                 ${rows.length - empty.length - dormant.length}`);

    console.log("\nEMPTY");
    empty.forEach((r) => console.log(line(r)));
    console.log("\nDORMANT");
    dormant.forEach((r) => console.log(line(r)));

    console.log(
      "\nNOTE: this audit sees the platform only. Every NUMid here also exists in\n" +
        "Access, and freakma republishes DATGRAL in full on each export, so an id\n" +
        "reassigned here comes back under its old owner unless it is removed at the\n" +
        "source or the NUMid is routed to the platform. Confirm against prod\n" +
        "datosfreak before reassigning.",
    );
  } finally {
    await prisma.$disconnect();
  }
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});