`scripts/rollups.js`

77 lines
// Per-council-district + per-ZIP permit-activity rollups (READ-ONLY, $0 local).
// Aggregates la_building_permits_raw (2020-present) with the shared permit_lead_score,
// joined to the gis_features council-district NAMES loaded into the platform.
// Writes CSVs to tmp/leads/ and prints top summaries. New local analytics surface —
// does not touch the live public viewer.
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();

// CD number -> member name, from the gis council_district layer (name = "N - Member").
async function cdNames() {
  const rows = (await q(`SELECT name FROM la_gis_features WHERE layer='council_district' AND name IS NOT NULL`)).rows;
  const map = {};
  for (const r of rows) {
    const m = String(r.name).match(/^\s*(\d+)\s*[-–]\s*(.+)$/);
    if (m) map[m[1]] = m[2].trim();
  }
  const matched = Object.keys(map).length;
  console.log(`  [join] council_district gis rows: ${rows.length}, name-parsed: ${matched}${matched === 0 ? '  ⚠ member column will be BLANK' : ''}`);
  return map;
}

const AGG = (groupExpr, groupAlias) => `
  SELECT ${groupExpr} AS ${groupAlias},
         count(*)::int AS permits,
         count(*) FILTER (WHERE p.status_desc='Issued')::int AS issued,
         count(*) FILTER (WHERE p.permit_type='Bldg-New')::int AS new_builds,
         round(sum(p.valuation))::bigint AS total_value,
         round(avg(p.valuation))::bigint AS avg_value,
         percentile_cont(0.5) WITHIN GROUP (ORDER BY p.valuation)::bigint AS median_value,
         round(avg(permit_lead_score(p.issue_date,p.valuation,p.permit_type,p.permit_sub_type,p.status_desc)),1) AS avg_lead_score,
         count(*) FILTER (WHERE p.issue_date >= now()-interval '90 days')::int AS last_90d
  FROM la_building_permits_raw p
  WHERE p.dataset_id='pi9x-tg5x' AND ${groupExpr} IS NOT NULL
  GROUP BY 1`;

function writeCsv(file, header, rows) {
  const esc = v => { const s = v == null ? '' : String(v); return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s; };
  fs.writeFileSync(file, [header.join(','), ...rows.map(r => header.map(h => esc(r[h])).join(','))].join('\n'));
}

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

  // --- Council district ---
  const cd = (await q(`${AGG('p.council_district', 'cd')} ORDER BY total_value DESC NULLS LAST`)).rows
    .map(r => ({ ...r, council_member: names[r.cd] || '' }));
  const cdHeader = ['cd', 'council_member', 'permits', 'issued', 'new_builds', 'last_90d', 'total_value', 'median_value', 'avg_value', 'avg_lead_score'];
  writeCsv(path.join(OUT, `rollup-council-district-${stamp}.csv`), cdHeader, cd);

  // --- ZIP (top by value) ---
  const zip = (await q(`${AGG('p.zip_code', 'zip')} ORDER BY total_value DESC NULLS LAST LIMIT 60`)).rows;
  const zipHeader = ['zip', 'permits', 'issued', 'new_builds', 'last_90d', 'total_value', 'median_value', 'avg_value', 'avg_lead_score'];
  writeCsv(path.join(OUT, `rollup-zip-${stamp}.csv`), zipHeader, zip);

  // --- report ---
  console.log(`\nPermit activity rollups — ${stamp}`);
  console.log('  Scope: BUILDING permits only (dataset pi9x-tg5x, 2020-present). Excludes 2010-2019, pre-2010, and electrical/mechanical trade permits.');
  console.log('  Note: total_value sums raw valuations across years (not inflation-adjusted, not project-deduped); median_value is the skew-robust central figure.\n');
  console.log('  TOP COUNCIL DISTRICTS by total project value:');
  cd.slice(0, 8).forEach(r => console.log(
    `   CD ${String(r.cd).padStart(2)} ${(r.council_member || '').padEnd(22).slice(0, 22)} ${String(r.permits).padStart(7)} permits  ${money(r.total_value).padStart(16)}  median ${money(r.median_value).padStart(9)}  score ${r.avg_lead_score}  (${r.new_builds} new)`));
  console.log('\n  TOP ZIPs by total project value:');
  zip.slice(0, 8).forEach(r => console.log(
    `   ${r.zip}  ${String(r.permits).padStart(7)} permits  ${money(r.total_value).padStart(16)}  median ${money(r.median_value).padStart(9)}  score ${r.avg_lead_score}  (${r.new_builds} new)`));
  console.log(`\n  CSVs → tmp/leads/rollup-council-district-${stamp}.csv, rollup-zip-${stamp}.csv\n`);
}

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