pn-new-crm/scripts/backfill-phone-e164.ts

/**
 * Backfill `client_contacts.value_e164` from `value` for phone / whatsapp
 * contacts where it's null or empty.
 *
 * The legacy seed (and pre-normalization production data) stored phone
 * numbers in `value` as free text — "+33 4 93 00 0002" — but `value_e164`
 * is what every UI surface and dedup matcher reads. This script runs the
 * raw `value` through libphonenumber-js (via the script-safe wrapper to
 * avoid the Node 25 metadata-loader bug) and writes the canonical E.164
 * form back.
 *
 * Usage:
 *   pnpm tsx scripts/backfill-phone-e164.ts            # dry-run report
 *   pnpm tsx scripts/backfill-phone-e164.ts --apply    # actually write
 *
 * The dry-run report prints, for each unparseable row, the contact id +
 * raw value so you can hand-clean before re-running.
 */
import 'dotenv/config';
import { and, eq, inArray, isNull, or, sql } from 'drizzle-orm';

import { db } from '@/lib/db';
import { clientContacts } from '@/lib/db/schema/clients';
import { parsePhoneScriptSafe } from '@/lib/dedup/phone-parse';
import type { CountryCode } from '@/lib/i18n/countries';

const APPLY = process.argv.includes('--apply');

interface PhoneRow {
  id: string;
  channel: string;
  value: string | null;
  valueCountry: string | null;
}

async function main() {
  console.log(`Phone E.164 backfill — ${APPLY ? 'APPLY MODE' : 'dry-run'}`);
  console.log('');

  // Find candidate rows: phone or whatsapp contacts with a `value` set but
  // `value_e164` null/empty.
  const rows: PhoneRow[] = await db
    .select({
      id: clientContacts.id,
      channel: clientContacts.channel,
      value: clientContacts.value,
      valueCountry: clientContacts.valueCountry,
    })
    .from(clientContacts)
    .where(
      and(
        inArray(clientContacts.channel, ['phone', 'whatsapp']),
        or(isNull(clientContacts.valueE164), eq(clientContacts.valueE164, '')),
        sql`${clientContacts.value} IS NOT NULL AND ${clientContacts.value} <> ''`,
      ),
    );

  console.log(`  found ${rows.length} candidate rows`);

  let parsedFull = 0;
  let parsedE164Only = 0;
  let unparseable = 0;
  const updates: Array<{
    id: string;
    valueE164: string;
    valueCountry: CountryCode | null;
  }> = [];
  const fails: Array<{ id: string; value: string; reason: string }> = [];

  for (const row of rows) {
    if (!row.value) continue;
    const defaultCountry = (row.valueCountry as CountryCode | null) ?? undefined;
    const parsed1 = parsePhoneScriptSafe(row.value, defaultCountry);

    if (parsed1.e164 && parsed1.country) {
      // Both e164 + country resolved — best case.
      updates.push({ id: row.id, valueE164: parsed1.e164, valueCountry: parsed1.country });
      parsedFull++;
    } else if (parsed1.e164) {
      // E.164 came back but country didn't (e.g. UK +44 7700 900xxx
      // fictional/reserved range — libphonenumber returns the e164 form
      // but refuses to assign a country). Still safe to write — the e164
      // is canonical. Country stays null.
      updates.push({
        id: row.id,
        valueE164: parsed1.e164,
        valueCountry: (row.valueCountry as CountryCode | null) ?? null,
      });
      parsedE164Only++;
    } else {
      fails.push({
        id: row.id,
        value: row.value,
        reason: row.value.trim().startsWith('+')
          ? 'has + prefix but parse failed'
          : 'no leading + and no country hint',
      });
      unparseable++;
    }
  }

  console.log('');
  console.log('  ✓ parsed cleanly (e164 + country)', parsedFull);
  console.log('  ✓ parsed e164 only (no country) ', parsedE164Only);
  console.log('  ✗ unparseable                   ', unparseable);
  console.log('');

  if (fails.length > 0) {
    console.log('Failures (first 10):');
    for (const f of fails.slice(0, 10)) {
      console.log(`  [${f.id}] "${f.value}" — ${f.reason}`);
    }
    console.log('');
  }

  if (!APPLY) {
    console.log('Dry-run only. Re-run with --apply to write the updates.');
    return;
  }

  if (updates.length === 0) {
    console.log('No updates to write.');
    return;
  }

  console.log(`Writing ${updates.length} updates...`);

  for (const u of updates) {
    await db
      .update(clientContacts)
      .set({
        valueE164: u.valueE164,
        valueCountry: u.valueCountry,
      })
      .where(eq(clientContacts.id, u.id));
  }

  console.log(`  ✓ wrote ${updates.length} rows`);
}

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