`scripts/permit-crew.js`

116 lines
// Named build crew (READ-ONLY except a scrape-cache, $0). For a property/build: pull every
// permit on the parcel, GET each LADBS permit-detail (public, single GET, id1/id2/id3 =
// permit# split on '-'), extract Contractor/Architect/Engineer + CSLB license, and match the
// contractor license -> our enriched cslb_raw (website/phone). The GC is on the building
// permit; the SUBS are the contractors on the electrical/plumbing/mech permits — so this
// names the full crew. Results cached in permit_crew.
//
// Usage: node scripts/permit-crew.js "<address>" | --apn=<APN> | --top=N (top-N current deals)
import { q, pool } from '../src/db.js';
const sleep = ms => new Promise(r => setTimeout(r, ms));
const kv = Object.fromEntries(process.argv.slice(2).filter(a => a.startsWith('--') && a.includes('=')).map(a => a.slice(2).split('=')));
const flags = process.argv.slice(2).filter(a => a.startsWith('--') && !a.includes('='));
const positional = process.argv.slice(2).filter(a => !a.startsWith('--')).join(' ').trim();
const DELAY = Number(process.env.CREW_DELAY_MS || 1500); // polite to city infra
const ERA_DAYS = 550;                                    // build-era window: permits within N days of anchor

const TRADE = { 'pi9x-tg5x': 'GC / Building', 'dyxf-7hc4': 'GC / Building', 'e67z-kt2n': 'GC / Building', 'ysqd-apz7': 'Electrical sub', '67is-svtd': 'Plumbing/Mech sub' };
const decode = s => String(s)
  .replace(/&nbsp;/g, ' ').replace(/&amp;/g, '&').replace(/&#0?39;|&rsquo;|&apos;/g, "'")
  .replace(/&quot;/g, '"').replace(/&#(\d+);/g, (_, n) => String.fromCharCode(+n)).replace(/&[a-z]+;/gi, ' ');
const nameOf = s => s ? s.split(';')[0].replace(/,\s*,/g, ',').trim() : null;
const licNum = s => { const m = s && s.match(/Lic\.?\s*No\.?:?\s*([A-Za-z0-9]+)/i); return m ? m[1].replace(/[^0-9]/g, '') : null; };
function role(html, label) {
  const m = html.match(new RegExp('>\\s*' + label + '\\s*</td>\\s*<td[^>]*>(.*?)</td>', 'si'));
  if (!m) return null;
  const v = decode(m[1].replace(/<[^>]+>/g, '')).replace(/\s+/g, ' ').trim();
  return v || null;
}

async function scrapePermit(nbr) {
  const cached = (await q(`SELECT * FROM permit_crew WHERE permit_nbr=$1`, [nbr])).rows[0];
  if (cached) return { ...cached, cached: true };
  const [a, b, c] = String(nbr).split('-');
  if (!a || !b || !c) return null;
  let html = '';
  try {
    const res = await fetch(`https://www.ladbsservices2.lacity.org/OnlineServices/PermitReport/PcisPermitDetail?id1=${a}&id2=${b}&id3=${c}`,
      { headers: { 'User-Agent': 'Mozilla/5.0' }, signal: AbortSignal.timeout(15000) });
    html = await res.text();
  } catch { return null; }
  if (/No Permits Match/i.test(html)) { await q(`INSERT INTO permit_crew(permit_nbr,contractor) VALUES($1,'(no match)') ON CONFLICT(permit_nbr) DO NOTHING`, [nbr]); return { permit_nbr: nbr, contractor: '(no match)' }; }
  const contractor = role(html, 'Contractor'), architect = role(html, 'Architect'), engineer = role(html, 'Engineer'), owner = role(html, 'Owner');
  const row = { permit_nbr: nbr, contractor, contractor_lic: licNum(contractor), architect, engineer, owner };
  await q(`INSERT INTO permit_crew(permit_nbr,contractor,contractor_lic,architect,engineer,owner) VALUES($1,$2,$3,$4,$5,$6)
    ON CONFLICT(permit_nbr) DO UPDATE SET contractor=EXCLUDED.contractor,contractor_lic=EXCLUDED.contractor_lic,architect=EXCLUDED.architect,engineer=EXCLUDED.engineer,owner=EXCLUDED.owner,scraped_at=now()`,
    [nbr, contractor, row.contractor_lic, architect, engineer, owner]);
  return row;
}
async function cslbMatch(lic) {
  if (!lic) return null;
  return (await q(`SELECT "BusinessName" name, "City" city, website, email, linkedin, linkedin_source,
    COALESCE(NULLIF("BusinessPhone",''), phone_web) phone FROM cslb_raw WHERE "LicenseNo"=$1 LIMIT 1`, [lic])).rows[0] || null;
}

// Returns era-scoped named crew for a parcel as structured data (importable).
export async function getCrew(apn) {
  const permits = (await q(`SELECT dataset_id, permit_nbr, permit_type, to_char(issue_date,'YYYY-MM-DD') issued
    FROM la_building_permits_raw WHERE apn=$1 AND issue_date IS NOT NULL ORDER BY issue_date DESC`, [apn])).rows;
  const bldg = permits.filter(p => /pi9x|dyxf|e67z/.test(p.dataset_id));
  const anchor = (bldg[0] || permits[0])?.issued;
  const eraStart = anchor ? new Date(new Date(anchor).getTime() - ERA_DAYS * 86400 * 1000).toISOString().slice(0, 10) : '0000-01-01';
  const era = permits.filter(p => p.issued >= eraStart);
  const seen = new Set(), roleByName = {}, crew = [];
  for (const p of era) {
    const d = await scrapePermit(p.permit_nbr);
    if (!d?.cached) await sleep(DELAY);
    if (!d || !d.contractor || d.contractor === '(no match)') continue;
    const nm = nameOf(d.contractor), trade = TRADE[p.dataset_id] || p.dataset_id;
    const key = (d.contractor_lic || nm) + '|' + trade;
    if (seen.has(key)) continue; seen.add(key);
    (roleByName[nm] ||= new Set()).add(trade.split(' ')[0]);
    const m = await cslbMatch(d.contractor_lic);
    crew.push({ issued: p.issued, trade, contractor: nm, lic: d.contractor_lic, city: m?.city || null,
      phone: m?.phone || null, website: m?.website || null, email: m?.email || null,
      linkedin: m?.linkedin || null, linkedin_source: m?.linkedin_source || null,
      cslb: !!m, architect: nameOf(d.architect), engineer: nameOf(d.engineer) });
  }
  const multiRole = Object.entries(roleByName).filter(([, r]) => r.size > 1).map(([n]) => n);
  return { apn, anchor, eraStart, inEra: era.length, olderExcluded: permits.length - era.length, crew, multiRole };
}

async function crewForApn(apn, label) {
  const r = await getCrew(apn);
  console.log(`\n=== BUILD CREW — ${label} (APN ${apn}) ===`);
  console.log(`Build era: permits since ${r.eraStart} (anchored to latest building permit ${r.anchor || 'n/a'}). ${r.inEra} in-era, ${r.olderExcluded} older excluded.`);
  for (const c of r.crew) {
    const web = c.website ? `  🌐 ${c.website}` : '', ph = c.phone ? `  ☎ ${c.phone}` : (c.lic ? '  (no CSLB match)' : '');
    console.log(`  ${c.issued}  ${c.trade.padEnd(18)} ${(c.contractor || '?').slice(0, 32).padEnd(32)} lic ${c.lic || '—'}${web}${ph}`);
    if (c.architect) console.log(`  ${' '.repeat(30)}architect: ${c.architect}`);
    if (c.engineer) console.log(`  ${' '.repeat(30)}engineer:  ${c.engineer}`);
  }
  if (r.multiRole.length) console.log(`  ⚠ same firm across roles (likely GC pulled the sub-permit): ${r.multiRole.join(', ')}`);
}

async function main() {
  if (kv.top) {
    const deals = (await q(`SELECT DISTINCT ON (apn) apn, primary_address FROM la_building_permits_raw
      WHERE dataset_id='pi9x-tg5x' AND status_desc='Issued' AND permit_type='Bldg-New'
        AND issue_date > now()-interval '120 days' AND apn IS NOT NULL AND valuation>=1000000
      ORDER BY apn, valuation DESC`)).rows.slice(0, Number(kv.top));
    console.log(`Naming crews for top ${deals.length} current new-build deals…`);
    for (const d of deals) await crewForApn(d.apn, d.primary_address);
  } else {
    const apn = kv.apn ? kv.apn.replace(/[^0-9]/g, '')
      : (await q(`SELECT apn, primary_address FROM la_building_permits_raw WHERE primary_address ILIKE $1 AND apn IS NOT NULL ORDER BY issue_date DESC LIMIT 1`, ['%' + positional + '%'])).rows[0]?.apn;
    if (!apn) { console.error('no property found'); process.exit(1); }
    const label = kv.apn ? kv.apn : positional;
    await crewForApn(apn, label);
  }
  console.log('\n$0 (public LADBS detail GET + local CSLB join).');
}
// run the CLI only when invoked directly (not when imported by property.js)
if (process.argv[1] && /permit-crew\.js$/.test(process.argv[1])) {
  main().catch(e => { console.error('permit-crew error:', e.message); process.exitCode = 1; }).finally(() => pool.end());
}