`scripts/broker-email-commit-from-logs.js`

99 lines
#!/usr/bin/env node
/*
 * broker-email-commit-from-logs.js — write ONLY genuinely-clean broker emails from the finder's
 * dry-run logs to broker.email (NO re-scrape). Reversible (rollback file). Hardened after a
 * contrarian pass found name-match substring collisions + shared firm inboxes:
 *   - name-match: the broker's LAST name must appear as an EXACT delimited token in the local-part
 *     (kills "ken"⊂"kendra", "chris"⊂"christina", "tony.solomon" for Tony Azzi).
 *   - cross-domain: email domain must match the site domain (kills utilityconnect@ on homebasedrealty).
 *   - vendor/platform blocklist (moatable/wix/squarespace/agentfire/sentry/…).
 *   - corp megasite/office-page guard on sole-email (unchanged).
 *   - DEDUP: an email written to >1 broker (in-batch OR already elsewhere in the DB) is DROPPED —
 *     a shared firm switchboard (info@kidder.com ×11) is not a personal contact.
 *   node broker-email-commit-from-logs.js <log...> [--commit]
 */
'use strict';
const fs = require('fs'); const { execFileSync } = require('child_process');
const args = process.argv.slice(2);
const COMMIT = args.includes('--commit');
const logs = args.filter(a => a.endsWith('.log') && fs.existsSync(a));
const psql = s => execFileSync('psql', ['-At', '-d', 'cre', '-c', s], { encoding: 'utf8' }).trim();
const q = s => "'" + String(s).replace(/'/g, "''") + "'";

const CORP = /marcusmillichap|cbre|kw\.com|kwcommercial|remax|coldwell|century21|compass|colliers|cushwake|jll|berkadia|newmark|kidder|bhhs|naiglobal|svn\.com|matthews\.com|cbcworldwide|lyonstahl/i;
const OFFICE = /\/(offices?|about-us|team|our-team|agents)\//i;
const VENDOR = /moatable|wix|squarespace|agentfire|sentry|godaddy|weebly|wordpress|shopify|hubspot|mailchimp|constantcontact|bugreport|noreply|no-reply|donotreply|webmaster|postmaster|abuse@|privacy@|support@agent/i;
const dom = e => (e.split('@')[1] || '').replace(/^www\./, '');
const siteDom = url => { const m = url.match(/https?:\/\/([^\/\s]+)/i); return m ? m[1].replace(/^www\./, '') : ''; };
const sameDom = (email, site) => { const a = dom(email), b = siteDom(site); return a && b && (a === b || a.endsWith('.' + b) || b.endsWith('.' + a)); };
const tokens = s => s.toLowerCase().split(/[._\-+]+/).filter(Boolean);

function nameMatchStrict(name, email) {
  const parts = String(name || '').toLowerCase().replace(/["']/g, '').split(/\s+/).filter(w => w.length > 1);
  if (!parts.length) return false;
  const last = parts[parts.length - 1], first = parts[0];
  const lt = tokens(email.split('@')[0]);
  const localFlat = email.split('@')[0].toLowerCase().replace(/[._\-+]/g, '');
  // last name as an EXACT token, OR the concatenation firstlast / flast is the whole local-part
  if (last.length >= 3 && lt.includes(last)) return true;
  if (localFlat === first + last || localFlat === (first[0] + last) || localFlat === (last + first)) return true;
  return false;
}

function parse(text) {
  const out = []; const lines = text.split('\n');
  for (let i = 0; i < lines.length; i++) {
    const m = lines[i].match(/^✓\s+(.*?)\s+\(#(\d+)\)\s+\[([a-z+-]+)\]/);
    if (!m) continue;
    const name = m[1], id = +m[2], basis = m[3].replace('+browser', '');
    let site = '', email = null;
    for (let j = 1; j <= 3 && i + j < lines.length; j++) {
      if (/^\s+site:/.test(lines[i + j])) site = lines[i + j].replace(/^\s+site:\s*/, '').trim();
      const e = lines[i + j].match(/EMAIL:\s+(\S+)/); if (e) { email = e[1].toLowerCase(); break; }
    }
    if (email) out.push({ id, name, basis, email, site });
  }
  return out;
}

// collect first-seen per broker, apply per-hit guards
const seen = new Map();
for (const lg of logs) for (const r of parse(fs.readFileSync(lg, 'utf8'))) {
  if (seen.has(r.id)) continue;
  if (VENDOR.test(r.email)) continue;                                          // platform / role junk
  if (!sameDom(r.email, r.site)) continue;                                     // cross-domain → drop
  if (r.basis === 'name-match' && !nameMatchStrict(r.name, r.email)) continue; // substring collision → drop
  if (r.basis === 'sole-email' && (CORP.test(r.site) || CORP.test(r.email) || OFFICE.test(r.site))) continue;
  // sole-email whose local-part looks like a PERSON (not a role inbox) must be THIS broker — else it's
  // a colleague at the same small firm (e.g. larry@ for broker Brett Howard). Role local-parts pass.
  const roleLocal = /^(info|contact|hello|sales|admin|office|team|reception|frontdesk|leasing|main)$/i.test(r.email.split('@')[0]);
  if (r.basis === 'sole-email' && !roleLocal && !nameMatchStrict(r.name, r.email)) continue;
  if (r.basis === 'role-inbox' && (CORP.test(r.email) || CORP.test(r.site))) continue; // corp firm inbox → drop
  seen.set(r.id, r);
}
let rows = [...seen.values()];

// DEDUP: an email assigned to >1 broker in this batch is a shared inbox → drop all of them
const emailCount = {}; rows.forEach(r => emailCount[r.email] = (emailCount[r.email] || 0) + 1);
rows = rows.filter(r => emailCount[r.email] === 1);
// also drop any email already present on a DIFFERENT broker in the DB
rows = rows.filter(r => {
  const n = +psql(`SELECT count(*) FROM broker WHERE lower(email)=${q(r.email)} AND id<>${r.id};`);
  return n === 0;
});

const byBasis = {}; rows.forEach(r => byBasis[r.basis] = (byBasis[r.basis] || 0) + 1);
console.log(`CLEAN committable hits: ${rows.length}  ${JSON.stringify(byBasis)}`);
console.log('sample:', rows.slice(0, 8).map(r => `#${r.id} ${r.name.split(' ')[0]}→${r.email} [${r.basis}]`).join('\n        '));

if (!COMMIT) { console.log('\nDRY — nothing written.'); process.exit(0); }
const rollback = [];
for (const r of rows) {
  const cur = psql(`SELECT COALESCE(email,'') FROM broker WHERE id=${r.id};`);
  if (cur) continue;
  psql(`UPDATE broker SET email=${q(r.email)} WHERE id=${r.id} AND (email IS NULL OR email='');`);
  rollback.push(r.id);
}
fs.writeFileSync('/tmp/broker-email-commit-rollback.txt', rollback.join(','));
console.log(`\nCOMMITTED ${rollback.length} clean emails. Rollback ids -> /tmp/broker-email-commit-rollback.txt`);