`scripts/export-license-directory.js`

61 lines
#!/usr/bin/env node
// Export a static License Directory from the local `cre` DB → data/license-directory.json,
// so the CRCP CRM can render licensed RE agents + firms + title/escrow companies as
// contactable cards on PROD (which has no `cre` DB — same static-export pattern as
// brokers-snapshot.json). Scoped to SoCal luxury metros for a shippable v1.
//
// Usage: NODE_PATH=$HOME/.claude/skills/browserbase/node_modules node scripts/export-license-directory.js
const fs = require('fs');
const path = require('path');
const { Client } = require('pg');

const OUT = path.join(__dirname, '..', 'data', 'license-directory.json');
const METROS = ['beverly-hills', 'malibu', 'montecito', 'newport-beach'];
const slug = s => String(s || '').toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-|-$/g, '');

(async () => {
  const c = new Client({ host: '/tmp', database: 'cre' });
  await c.connect();
  const metroList = METROS.map((_, i) => `$${i + 1}`).join(',');

  // Agents (individual licensees) in the SoCal luxury metros.
  const agents = (await c.query(
    `SELECT name, firm, license_number, license_type, city, metro, expiration::text AS expiration, source
       FROM gov_licensed_agent
      WHERE state='CA' AND metro IN (${metroList})
      ORDER BY firm NULLS LAST, name`, METROS)).rows
    .map(r => ({
      id: 'agent:' + slug(r.name) + ':' + (r.license_number || slug(r.firm || '')),
      kind: 'agent', name: r.name, firm: r.firm || '', license_number: r.license_number || '',
      license_type: r.license_type || '', city: r.city || '', metro: r.metro || '',
      expiration: r.expiration ? String(r.expiration).slice(0, 10) : null, source: r.source || '',
    }));

  // Firms with their SoCal licensed-agent counts.
  const firms = (await c.query(
    `SELECT firm AS name, count(*)::int AS agent_count,
            mode() WITHIN GROUP (ORDER BY metro) AS metro
       FROM gov_licensed_agent
      WHERE state='CA' AND metro IN (${metroList}) AND firm IS NOT NULL AND firm <> ''
      GROUP BY firm ORDER BY count(*) DESC`, METROS)).rows
    .map(r => ({ id: 'firm:' + slug(r.name), kind: 'firm', name: r.name, agent_count: r.agent_count, metro: r.metro || '' }));

  // Title + escrow companies (deal-side).
  const companies = (await c.query(
    `SELECT name, kind, underwriter FROM transaction_company WHERE kind IN ('title','escrow') ORDER BY kind, name`)).rows
    .map(r => ({ id: r.kind + ':' + slug(r.name), kind: r.kind, name: r.name, underwriter: r.underwriter || '' }));

  await c.end();
  const out = {
    generated_at: new Date().toISOString(),
    scope: { state: 'CA', metros: METROS },
    counts: { agents: agents.length, firms: firms.length, title: companies.filter(x => x.kind === 'title').length, escrow: companies.filter(x => x.kind === 'escrow').length },
    agents, firms,
    title: companies.filter(x => x.kind === 'title'),
    escrow: companies.filter(x => x.kind === 'escrow'),
  };
  fs.writeFileSync(OUT, JSON.stringify(out));
  console.log('Wrote', OUT);
  console.log('counts:', JSON.stringify(out.counts));
})().catch(e => { console.error('FATAL', e.message); process.exit(1); });