`scripts/contractor-intel.js`

74 lines
// Contractor-market intelligence (READ-ONLY, $0 local, INTERNAL — not the public site).
// Joins active CSLB contractors (LA County) to LA building-permit activity to characterize
// supply (contractors by trade classification) vs demand (permit volume/value by area).
//
// HONESTY CAVEAT (baked into labels): a contractor's ZIP in CSLB is their BUSINESS
// address, NOT where they work. Per-ZIP contractor counts are "business-located in ZIP",
// a proxy for local supply — NOT "contractors serving that ZIP". County/classification
// supply figures are robust; per-ZIP density is directional only.
import { q, pool } from '../src/db.js';
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const OUT = path.resolve(__dirname, '../tmp/leads');
const money = v => '$' + Math.round(Number(v || 0)).toLocaleString();
// CSLB classification -> readable trade. Data mixes 'C10' and 'C-7' forms; key by
// normalized (dash-stripped, upper) code so both match (validated vs cslb_raw codes).
const TRADE = {
  B: 'General Building', B2: 'Residential Remodel', A: 'General Engineering',
  C10: 'Electrical', C36: 'Plumbing', C20: 'HVAC', C33: 'Painting', C27: 'Landscaping',
  C15: 'Flooring', C39: 'Roofing', C7: 'Low Voltage', C54: 'Ceramic/Tile', C8: 'Concrete',
  C6: 'Cabinets/Millwork', C53: 'Swimming Pool', C17: 'Glazing', C35: 'Plastering',
  C29: 'Masonry', C5: 'Framing/Carpentry', C9: 'Drywall', C13: 'Fencing', C12: 'Earthwork',
  C43: 'Sheet Metal', C61: 'Limited Specialty', C34: 'Pipeline', C23: 'Ornamental Metal',
  C42: 'Sanitation', C51: 'Structural Steel', C46: 'Solar', C31: 'Construction Zone Traffic',
};
const norm = c => String(c).toUpperCase().replace(/-/g, '').trim();
const trade = c => TRADE[norm(c)] || norm(c);

async function main() {
  const stamp = (await q(`SELECT to_char(now(),'YYYY-MM-DD') d`)).rows[0].d;
  fs.mkdirSync(OUT, { recursive: true });

  // 1. SUPPLY — active LA County contractors by classification (split pipe-separated codes)
  const classes = (await q(`
    SELECT trim(c) AS class, count(*)::int AS contractors
    FROM cslb_raw, unnest(string_to_array("Classifications(s)", '|')) AS c
    WHERE "County"='Los Angeles' AND "PrimaryStatus"='CLEAR' AND trim(c) <> ''
    GROUP BY 1 ORDER BY 2 DESC LIMIT 15`)).rows;

  // 2. COUNTY-level supply vs demand by trade — VALID (no business-ZIP proxy; Cody c6
  //    dropped the per-ZIP ratio as a residential-ness artifact). Match key trades to their
  //    permit datasets: General Building->building, C10->electrical, C36->mech/plumbing.
  const supplyByClass = Object.fromEntries(classes.map(r => [norm(r.class), r.contractors]));
  const TRADE_DEMAND = [
    { class: 'B',   label: 'General Building', ds: 'pi9x-tg5x' },
    { class: 'C10', label: 'Electrical',       ds: 'ysqd-apz7' },
    { class: 'C36', label: 'Plumbing/Mech',    ds: '67is-svtd' },
  ];
  const market = [];
  for (const t of TRADE_DEMAND) {
    const demand = Number((await q(`SELECT count(*) c FROM la_building_permits_raw WHERE dataset_id=$1 AND issue_date > now()-interval '365 days'`, [t.ds])).rows[0].c);
    const supply = supplyByClass[norm(t.class)] || 0;
    market.push({ ...t, supply, demand_1y: demand, permits_per_contractor_yr: supply ? Math.round(demand / supply * 10) / 10 : null });
  }

  fs.writeFileSync(path.join(OUT, `contractor-intel-classes-${stamp}.csv`),
    ['class,trade,contractors', ...classes.map(r => `${r.class},"${trade(r.class)}",${r.contractors}`)].join('\n'));
  fs.writeFileSync(path.join(OUT, `contractor-intel-market-${stamp}.csv`),
    ['class,trade,active_la_contractors,permits_1y,permits_per_contractor_yr',
      ...market.map(r => `${r.class},"${r.label}",${r.supply},${r.demand_1y},${r.permits_per_contractor_yr ?? ''}`)].join('\n'));

  console.log(`\nContractor-market intelligence — ${stamp}  (INTERNAL; LA County active CSLB contractors)\n`);
  console.log('  ACTIVE LA COUNTY CONTRACTORS by trade (supply):');
  classes.slice(0, 10).forEach(r => console.log(`   ${String(r.class).padEnd(5)} ${trade(r.class).padEnd(22)} ${r.contractors.toLocaleString().padStart(7)}`));
  console.log('\n  COUNTY SUPPLY vs 1-YEAR PERMIT DEMAND by trade (valid county-level ratio):');
  market.forEach(r => console.log(
    `   ${r.label.padEnd(20)} ${String(r.supply).toLocaleString().padStart(7)} active contractors  vs  ${r.demand_1y.toLocaleString().padStart(7)} permits/yr  →  ${r.permits_per_contractor_yr} permits per contractor/yr`));
  console.log(`\n  CSVs → tmp/leads/contractor-intel-classes-${stamp}.csv, contractor-intel-market-${stamp}.csv\n`);
}

main().catch(e => { console.error('contractor-intel error:', e.message); process.exitCode = 1; }).finally(() => pool.end());