`scripts/backfill-broker-of-record.js`

99 lines
// backfill-broker-of-record.js — seed the broker-of-record history from current data (TK-10139).
//
// One-time (idempotent, re-run safe) seed of the first observation per address, from two sources:
//   1. data/broker-history-full.json  — per-property history (current_broker = "Agent | Firm",
//      last_deed_date as the observed_at anchor when present).
//   2. data/ranked.json               — the enriched deals with broker_firm / broker_agent /
//      source_of_truth / source_host (the broker-of-record + firm-direct rows).
//
// Because the recorder only INSERTs on a change vs the latest row, re-running is safe (unchanged
// observations no-op). Also writes data/last-broker-of-record.json (address_key -> last broker/firm)
// so the mls.html grid can merge the two new columns without a live server round-trip.
//
// Usage: node scripts/backfill-broker-of-record.js
'use strict';
const fs = require('fs');
const path = require('path');
const ROOT = path.join(__dirname, '..');
const { recordBrokerOfRecord, addressKey, historyFor, DB_PATH } = require('./broker-of-record-recorder');

const readJSON = (p) => { try { return JSON.parse(fs.readFileSync(p, 'utf8')); } catch (_) { return null; } };

// broker-history-full "current_broker" is EITHER a string "Agent | Firm" (agent first) OR an
// object { name, firm, ... }. Normalize both to { agent, firm }.
function splitCurrentBroker(s) {
  if (s && typeof s === 'object') {
    const agent = s.name != null ? String(s.name).trim() : '';
    const firm = s.firm != null ? String(s.firm).trim() : '';
    return { agent: agent || null, firm: firm || null };
  }
  const str = String(s == null ? '' : s).trim();
  if (!str) return { agent: null, firm: null };
  const parts = str.split('|').map(x => x.trim()).filter(Boolean);
  if (parts.length >= 2) return { agent: parts[0], firm: parts.slice(1).join(' | ') };
  return { agent: parts[0] || null, firm: null };
}

(function main() {
  const stat = { seenHistory: 0, seenRanked: 0, recorded: 0, unchanged: 0, skipped: 0 };
  const seen = new Set();               // address_keys touched, for the static map

  // ── 1) broker-history-full.json (object keyed by property id, OR array) ──────────────────────
  const bh = readJSON(path.join(ROOT, 'data', 'broker-history-full.json'));
  const bhRows = Array.isArray(bh) ? bh
    : (bh && Array.isArray(bh.properties)) ? bh.properties          // { meta, properties:[...] } shape
    : (bh && Array.isArray(bh.history)) ? bh.history
    : (bh && typeof bh === 'object') ? Object.values(bh).filter(v => v && typeof v === 'object' && (v.address || v.current_broker))
    : [];
  for (const row of bhRows) {
    if (!row || typeof row !== 'object') continue;
    stat.seenHistory++;
    const { agent, firm } = splitCurrentBroker(row.current_broker);
    if (!agent && !firm) { stat.skipped++; continue; }
    // observed_at anchor: last_deed_date if present (as the "first observation" time), else now.
    const observed_at = row.last_deed_date ? new Date(row.last_deed_date + 'T00:00:00Z').toISOString() : undefined;
    const r = recordBrokerOfRecord({
      address: row.address, city: row.city, ain: row.ain || null,
      broker_firm: firm, broker_agent: agent,
      source_of_truth: 'broker-of-record', source_host: row.broker_source || null, observed_at,
    });
    if (r.recorded) stat.recorded++; else if (r.reason === 'unchanged') stat.unchanged++; else stat.skipped++;
    if (r.address_key) seen.add(r.address_key);
  }

  // ── 2) ranked.json (enriched deals with a real broker-of-record) ─────────────────────────────
  const ranked = readJSON(path.join(ROOT, 'data', 'ranked.json'));
  const deals = (ranked && ranked.ranked) || [];
  for (const d of deals) {
    if (!d || (!d.broker_firm && !d.broker_agent)) { if (d) stat.skipped++; continue; }
    stat.seenRanked++;
    const r = recordBrokerOfRecord({
      address: d.address, city: d.city, ain: d.ain || null,
      broker_firm: d.broker_firm || null, broker_agent: d.broker_agent || null,
      source_of_truth: d.source_of_truth || null, source_host: d.source_host || null,
    });
    if (r.recorded) stat.recorded++; else if (r.reason === 'unchanged') stat.unchanged++; else stat.skipped++;
    if (r.address_key) seen.add(r.address_key);
  }

  // ── 3) static map for the grid: address_key -> { last_broker_of_record, last_firm_of_record, observed_at } ──
  const map = {};
  for (const key of seen) {
    const rows = historyFor(key);       // newest first
    if (!rows.length) continue;
    const top = rows[0];
    map[key] = {
      last_broker_of_record: top.broker_agent || null,   // agent = the broker of record
      last_firm_of_record: top.broker_firm || null,       // firm = the firm of record
      observed_at: top.observed_at || null,
      source_of_truth: top.source_of_truth || null,
      history_count: rows.length,
    };
  }
  const mapPath = path.join(ROOT, 'data', 'last-broker-of-record.json');
  fs.writeFileSync(mapPath, JSON.stringify({ generated_at: new Date().toISOString(), by_address_key: map }, null, 0));

  const total = require('better-sqlite3')(DB_PATH).prepare('SELECT count(*) n FROM broker_of_record_history').get().n;
  console.log(JSON.stringify({ ...stat, addresses_in_map: Object.keys(map).length, table_rows: total, db: DB_PATH, map: mapPath }, null, 2));
})();