`apps/workers/scripts/enrich-nearby.ts`

166 lines
/**
 * Public-records "nearby" enrichment stage.
 *
 * WHY: builders' own nearby-schools / nearby-places / amenities pages are
 * frequently robots.txt-disallowed (e.g. Lennar `/*nearby-schools`,
 * `/*amenities`). Rather than scrape those blocked pages, we attach the SAME
 * objective facts from PUBLIC RECORDS:
 *   - schools  → NCES CCD via the Urban Institute Education Data API
 *                (public domain; objective only — NO ratings/quality → Fair-Housing safe)
 *   - places   → OpenStreetMap / Overpass (ODbL; attribution recorded)
 *
 * Keyed on Community.lat/lon; writes to Community.amenities (JSON) with
 * per-source attribution + retrievedAt. Idempotent (skips already-enriched),
 * op-N batched (NEARBY_LIMIT, default 40), $0 (free public APIs).
 *
 * Run:  DATABASE_URL=… NEARBY_LIMIT=40 pnpm --filter @homesonspec/workers exec tsx scripts/enrich-nearby.ts
 */
import { prisma, Prisma } from "@homesonspec/database";

const YEAR = process.env.NCES_YEAR ?? "2022";
const LIMIT = Number(process.env.NEARBY_LIMIT ?? 40);
const SCHOOL_N = 6;
const PLACE_RADIUS_M = 8000;
const UA = "HomesOnSpecBot/0.1 (+https://homesonspec.com/bot; contact: data@homesonspec.com)";

const STATE_FIPS: Record<string, number> = {
  AL:1,AK:2,AZ:4,AR:5,CA:6,CO:8,CT:9,DE:10,DC:11,FL:12,GA:13,HI:15,ID:16,IL:17,IN:18,IA:19,
  KS:20,KY:21,LA:22,ME:23,MD:24,MA:25,MI:26,MN:27,MS:28,MO:29,MT:30,NE:31,NV:32,NH:33,NJ:34,
  NM:35,NY:36,NC:37,ND:38,OH:39,OK:40,OR:41,PA:42,RI:44,SC:45,SD:46,TN:47,TX:48,UT:49,VT:50,
  VA:51,WA:53,WV:54,WI:55,WY:56,
};
const LEVEL: Record<number, string> = { 1: "Elementary", 2: "Middle", 3: "High", 4: "Other" };

function milesBetween(aLat: number, aLon: number, bLat: number, bLon: number): number {
  const R = 3958.8;
  const p1 = (aLat * Math.PI) / 180, p2 = (bLat * Math.PI) / 180;
  const dp = ((bLat - aLat) * Math.PI) / 180, dl = ((bLon - aLon) * Math.PI) / 180;
  const x = Math.sin(dp / 2) ** 2 + Math.cos(p1) * Math.cos(p2) * Math.sin(dl / 2) ** 2;
  return R * 2 * Math.asin(Math.sqrt(x));
}

interface School { ncessch: string; name: string; level: string; lat: number; lon: number; city: string | null }
const stateSchoolCache = new Map<number, School[]>();

async function schoolsForState(fips: number): Promise<School[]> {
  const hit = stateSchoolCache.get(fips);
  if (hit) return hit;
  let out: School[] = [];
  try {
    const res = await fetch(`https://educationdata.urban.org/api/v1/schools/ccd/directory/${YEAR}/?fips=${fips}`, {
      headers: { "User-Agent": UA, Accept: "application/json" },
      signal: AbortSignal.timeout(45_000),
    });
    if (res.ok) {
      const data: any = await res.json();
      out = (data.results ?? [])
        .filter((s: any) => s.latitude != null && s.longitude != null)
        .map((s: any): School => ({
          ncessch: String(s.ncessch),
          name: s.school_name,
          level: LEVEL[s.school_level] ?? "",
          lat: s.latitude,
          lon: s.longitude,
          city: s.city_location ?? null,
        }));
    }
  } catch {
    /* fail-soft: no schools for this state this run */
  }
  stateSchoolCache.set(fips, out);
  return out;
}

interface Place { name: string; type: string; distance_mi: number }
let osmFails = 0;
let osmDisabled = false; // circuit breaker: stop hammering Overpass once it's clearly busy
async function placesNear(lat: number, lon: number): Promise<{ places: Place[]; ok: boolean }> {
  if (osmDisabled) return { places: [], ok: false };
  const q = `[out:json][timeout:25];(` +
    `nwr(around:${PLACE_RADIUS_M},${lat},${lon})[amenity=hospital];` +
    `nwr(around:${PLACE_RADIUS_M},${lat},${lon})[amenity=pharmacy];` +
    `nwr(around:${PLACE_RADIUS_M},${lat},${lon})[amenity=library];` +
    `nwr(around:${PLACE_RADIUS_M},${lat},${lon})[shop=supermarket];` +
    `nwr(around:${PLACE_RADIUS_M},${lat},${lon})[leisure=park];` +
    `);out center tags 60;`;
  for (const ep of ["https://overpass-api.de/api/interpreter", "https://overpass.kumi.systems/api/interpreter"]) {
    try {
      const res = await fetch(ep, {
        method: "POST",
        headers: { "User-Agent": UA, "Content-Type": "application/x-www-form-urlencoded" },
        body: "data=" + encodeURIComponent(q),
        signal: AbortSignal.timeout(35_000),
      });
      if (!res.ok) continue;
      const ct = res.headers.get("content-type") ?? "";
      if (!ct.includes("json")) continue; // busy → XML error page
      const data: any = await res.json();
      const rows: Place[] = [];
      for (const el of data.elements ?? []) {
        const t = el.tags ?? {};
        if (!t.name) continue;
        const la = el.lat ?? el.center?.lat, lo = el.lon ?? el.center?.lon;
        if (la == null) continue;
        rows.push({ name: t.name, type: t.amenity || t.shop || t.leisure || "place", distance_mi: Math.round(milesBetween(lat, lon, la, lo) * 10) / 10 });
      }
      rows.sort((a, b) => a.distance_mi - b.distance_mi);
      osmFails = 0;
      return { places: rows.slice(0, 10), ok: true };
    } catch {
      /* try next mirror */
    }
  }
  if (++osmFails >= 3) { osmDisabled = true; console.log("  (OSM/Overpass busy — disabling places for the rest of this run; schools unaffected)"); }
  return { places: [], ok: false }; // OSM transiently unavailable — schools still land
}

async function main() {
  const communities = await prisma.community.findMany({
    where: { lat: { not: null }, lon: { not: null }, amenities: { equals: Prisma.DbNull } },
    select: { id: true, name: true, city: true, state: true, lat: true, lon: true },
    orderBy: { createdAt: "asc" },
    take: LIMIT,
  });
  console.log(`enrich-nearby: ${communities.length} unenriched communities with coords (limit ${LIMIT}, NCES ${YEAR})`);
  let enriched = 0, withSchools = 0, withPlaces = 0;
  const nowIso = new Date().toISOString();

  for (const c of communities) {
    const lat = Number(c.lat), lon = Number(c.lon);
    const fips = STATE_FIPS[(c.state ?? "").toUpperCase()];
    let schools: any[] = [];
    if (fips) {
      const all = await schoolsForState(fips);
      schools = all
        .map((s) => ({ ...s, distance_mi: Math.round(milesBetween(lat, lon, s.lat, s.lon) * 10) / 10 }))
        .sort((a, b) => a.distance_mi - b.distance_mi)
        .slice(0, SCHOOL_N)
        .map((s) => ({ name: s.name, level: s.level, distance_mi: s.distance_mi, city: s.city, ncessch: s.ncessch }));
    }
    const { places, ok: placesOk } = await placesNear(lat, lon);

    const sources: any[] = [];
    if (schools.length) sources.push({ field: "schools", source: "NCES Common Core of Data (US Dept of Education) via Urban Institute Education Data API", license: "public domain", retrievedAt: nowIso });
    if (places.length) sources.push({ field: "places", source: "OpenStreetMap", license: "ODbL — © OpenStreetMap contributors", retrievedAt: nowIso });

    const payload = {
      schools,
      places,
      _note: "Objective public-records facts near this community. Schools are location facts only — no ratings (Fair Housing). Sourced from public records, NOT the builder site.",
      _sources: sources,
      _enrichedAt: nowIso,
      _placesSourceStatus: placesOk ? "ok" : "unavailable-this-run",
    };
    await prisma.community.update({ where: { id: c.id }, data: { amenities: payload } });
    enriched++;
    if (schools.length) withSchools++;
    if (places.length) withPlaces++;
    console.log(`  ✓ ${c.name}, ${c.city} ${c.state}: ${schools.length} schools, ${places.length} places${placesOk ? "" : " (OSM busy)"}`);
  }

  console.log(`\nDONE: enriched ${enriched} communities · ${withSchools} got schools · ${withPlaces} got places · $0 (public APIs)`);
  await prisma.$disconnect();
}

main().catch(async (e) => { console.error(e); await prisma.$disconnect(); process.exit(1); });