Workflow-App/Workflow/scripts/backfill_client_status.mjs

#!/usr/bin/env node
// scripts/backfill_client_status.mjs
//
// One-shot backfill of ysterbaard_companies.client_status from existing
// is_client (bool) + enterprise_status (text) values.
//
// Rules (added 2026-08-20):
//   is_client=true  AND enterprise_status contains "Deregistered" -> "deregistered"
//   is_client=true                                          else -> "active_client"
//   is_client=false AND enterprise_status contains "In Process" -> "non_client"
//   is_client=false                                        else -> "non_client"
//
// Dormant clients are a manual judgement call — this script does NOT
// set anyone to dormant_client. Operators must toggle dormant manually.
//
// Idempotent: re-running overwrites previous values with the same derivation.

import fs from "node:fs";
import path from "node:path";
import { fileURLToPath } from "node:url";

const __dirname = path.dirname(fileURLToPath(import.meta.url));

// Load PB_TOKEN from .env.local
const envPath = path.resolve(__dirname, "..", ".env.local");
const env = {};
for (const line of fs.readFileSync(envPath, "utf8").split("\n")) {
  const m = line.match(/^([A-Z_]+)=(.*)$/);
  if (m) env[m[1]] = m[2].trim().replace(/^["']|["']$/g, "");
}
const PB_URL = env.PB_URL || "https://pb.agent.apphub.co.za";
const PB_TOKEN = env.PB_TOKEN;

function derive(isClient, enterpriseStatus) {
  const status = (enterpriseStatus || "").toLowerCase();
  if (isClient === true && status.includes("deregistered")) return "deregistered";
  if (isClient === true) return "active_client";
  return "non_client";
}

async function main() {
  if (!PB_TOKEN) {
    console.error("PB_TOKEN missing in .env.local");
    process.exit(1);
  }

  // Page through all companies
  let page = 1;
  let totalProcessed = 0;
  let totalUpdated = 0;
  const buckets = { active_client: 0, dormant_client: 0, deregistered: 0, non_client: 0 };

  while (true) {
    const url = `${PB_URL}/api/collections/pbc_3748838150/records?perPage=200&page=${page}&fields=id,enterprise_no,enterprise_name,is_client,enterprise_status,client_status`;
    const res = await fetch(url, { headers: { Authorization: PB_TOKEN } });
    if (!res.ok) {
      console.error(`PB list HTTP ${res.status}: ${(await res.text()).slice(0, 300)}`);
      process.exit(1);
    }
    const data = await res.json();
    const items = data.items || [];
    if (items.length === 0) break;

    for (const row of items) {
      totalProcessed++;
      const target = derive(row.is_client, row.enterprise_status);
      buckets[target]++;
      if (row.client_status === target) continue; // already correct
      const patch = await fetch(`${PB_URL}/api/collections/pbc_3748838150/records/${row.id}`, {
        method: "PATCH",
        headers: { Authorization: PB_TOKEN, "Content-Type": "application/json" },
        body: JSON.stringify({ client_status: target }),
      });
      if (!patch.ok) {
        console.error(`PATCH failed for ${row.id} (${row.enterprise_no}): HTTP ${patch.status}`);
        continue;
      }
      totalUpdated++;
      console.log(`  ${row.enterprise_no || row.id}  ${row.enterprise_name || "(no name)"}  -> ${target}`);
    }

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

  console.log(`\nDone. Processed ${totalProcessed}, updated ${totalUpdated}.`);
  console.log("Buckets:", buckets);
  console.log("\nNote: dormant_client requires manual judgement. Review the active_client list and set dormant_client where appropriate.");
}

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