`src/ingest/listings/shared.ts`

184 lines
/**
 * Shared listing-ingest helpers: polite fetch (UA + jitter), robots.txt honoring,
 * JSON-LD extraction, firm resolution by domain, and county-centroid region crosswalk.
 */
import { query } from '../../../db/pool.ts';

export const UA =
  'Mozilla/5.0 (compatible; USRealEstate-Research/1.0; +https://usrealestate.local/about)';

const jitter = () => 2000 + Math.floor(Math.random() * 2000); // 2–4s
export const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));

export async function politeFetch(url: string): Promise<{ ok: boolean; status: number; body: string }> {
  await sleep(jitter());
  try {
    const res = await fetch(url, { redirect: 'follow', headers: { 'user-agent': UA } });
    const body = res.ok ? await res.text() : '';
    return { ok: res.ok, status: res.status, body };
  } catch {
    return { ok: false, status: 0, body: '' };
  }
}

/** Raw fetch (no jitter) for sitemaps/robots — still polite UA, still honors status. */
export async function rawFetch(url: string): Promise<{ ok: boolean; status: number; body: string }> {
  try {
    const res = await fetch(url, { redirect: 'follow', headers: { 'user-agent': UA } });
    const body = res.ok ? await res.text() : '';
    return { ok: res.ok, status: res.status, body };
  } catch {
    return { ok: false, status: 0, body: '' };
  }
}

/**
 * Minimal robots.txt gate for `User-agent: *`. Returns a predicate that is true when a
 * path is ALLOWED. Honors Disallow with `*` wildcards and Allow overrides (longest-match).
 * A missing/unreachable robots.txt = allow-all (standard behavior).
 */
export async function robotsGate(host: string): Promise<(path: string) => boolean> {
  const { ok, body } = await rawFetch(`https://${host}/robots.txt`);
  if (!ok) return () => true;
  const rules: { allow: boolean; pattern: string }[] = [];
  let inStar = false;
  for (const raw of body.split('\n')) {
    const line = raw.replace(/#.*/, '').trim();
    if (!line) continue;
    const m = line.match(/^(user-agent|allow|disallow)\s*:\s*(.*)$/i);
    if (!m) continue;
    const [, k, v] = m;
    const key = k.toLowerCase();
    if (key === 'user-agent') { inStar = v.trim() === '*'; continue; }
    if (!inStar) continue;
    if (key === 'allow' && v) rules.push({ allow: true, pattern: v.trim() });
    if (key === 'disallow' && v) rules.push({ allow: false, pattern: v.trim() });
  }
  const toRe = (p: string) =>
    new RegExp('^' + p.replace(/[.+?^${}()|[\]\\]/g, '\\$&').replace(/\*/g, '.*'));
  const compiled = rules.map((r) => ({ allow: r.allow, re: toRe(r.pattern), len: r.pattern.length }));
  return (path: string) => {
    let best: { allow: boolean; len: number } | null = null;
    for (const r of compiled) {
      if (r.re.test(path) && (!best || r.len > best.len)) best = { allow: r.allow, len: r.len };
    }
    return best ? best.allow : true;
  };
}

/** Extract all parseable JSON-LD objects (flattening @graph + arrays) from a page. */
export function extractJsonLd(html: string): any[] {
  const out: any[] = [];
  // quotes optional: some sites (e.g. Weichert) emit `<script type=application/ld+json>` unquoted
  const re = /<script[^>]*type=["']?application\/ld\+json["']?[^>]*>([\s\S]*?)<\/script>/gi;
  let m: RegExpExecArray | null;
  while ((m = re.exec(html))) {
    let parsed: any;
    try { parsed = JSON.parse(m[1].trim()); } catch { continue; }
    const items = Array.isArray(parsed) ? parsed : [parsed];
    for (const it of items) {
      if (it && it['@graph'] && Array.isArray(it['@graph'])) out.push(...it['@graph']);
      else out.push(it);
    }
  }
  return out;
}

export function typeMatches(node: any, ...types: string[]): boolean {
  const t = node?.['@type'];
  const arr = Array.isArray(t) ? t : [t];
  return arr.some((x) => types.includes(String(x)));
}

/** "300,000.00" | "$97,500" | 97500 -> 97500. null on junk. */
export function num(v: unknown): number | null {
  if (v == null) return null;
  const n = Number(String(v).replace(/[^0-9.]/g, ''));
  return Number.isFinite(n) && n > 0 ? n : null;
}

/** "1344 sqft" | "1,344" -> 1344. */
export function sqftOf(v: unknown): number | null {
  return num(v);
}

/** Resolve the firm holding this listing by matching the source host to firm_site.url. */
export async function resolveFirmByHost(host: string): Promise<number | null> {
  const bare = host.replace(/^www\./, '');
  const r = await query<{ id: number }>(
    `SELECT f.id
       FROM firm_site fs JOIN firm f ON f.id = fs.firm_id
      WHERE fs.url ILIKE $1
      ORDER BY f.agent_count DESC NULLS LAST, f.id
      LIMIT 1`,
    [`%${bare}%`],
  );
  return r.rows[0]?.id ?? null;
}

/**
 * County region_id for a listing: nearest county centroid within the same state.
 * Cheap squared-distance over the ~254 counties of one state; no geocode API. Null if
 * no state or no lat/lng.
 */
const countyCache = new Map<string, { id: number; lat: number; lng: number }[]>();
export async function resolveRegion(
  state: string | null,
  lat: number | null,
  lng: number | null,
): Promise<number | null> {
  if (!state || lat == null || lng == null) return null;
  const st = state.toUpperCase();
  let counties = countyCache.get(st);
  if (!counties) {
    const r = await query<{ id: number; lat: number; lng: number }>(
      `SELECT id, lat, lng FROM region
        WHERE region_type='county' AND state_code=$1 AND lat IS NOT NULL AND lng IS NOT NULL`,
      [st],
    );
    counties = r.rows;
    countyCache.set(st, counties);
  }
  let best: number | null = null;
  let bestD = Infinity;
  for (const c of counties) {
    const d = (c.lat - lat) ** 2 + (c.lng - lng) ** 2;
    if (d < bestD) { bestD = d; best = c.id; }
  }
  return best;
}

/**
 * Fallback region resolver by ZIP → county_fips → county region, for adapters whose
 * listings carry a postalCode but no lat/lng (e.g. Weichert). Chain:
 * zip_county.zip → zip_county.county_fips = region.fips (region_type='county').
 */
const zipRegionCache = new Map<string, number | null>();
export async function resolveRegionByZip(zip: string | null): Promise<number | null> {
  if (!zip) return null;
  const z = zip.trim().slice(0, 5);
  if (!/^\d{5}$/.test(z)) return null;
  if (zipRegionCache.has(z)) return zipRegionCache.get(z)!;
  const r = await query<{ id: number }>(
    `SELECT r.id FROM zip_county zc
        JOIN region r ON r.fips = zc.county_fips AND r.region_type = 'county'
       WHERE zc.zip = $1 LIMIT 1`, [z]);
  const id = r.rows[0]?.id ?? null;
  zipRegionCache.set(z, id);
  return id;
}

/** Pull all leaf <loc> URLs from a sitemap or sitemap-index (one level of recursion). */
export async function sitemapLocs(url: string, depth = 1): Promise<string[]> {
  const { ok, body } = await rawFetch(url);
  if (!ok) return [];
  const locs = [...body.matchAll(/<loc>\s*([^<]+?)\s*<\/loc>/gi)].map((m) => m[1]);
  const isIndex = /<sitemapindex/i.test(body);
  if (isIndex && depth > 0) {
    const child: string[] = [];
    for (const l of locs.slice(0, 3)) child.push(...(await sitemapLocs(l, depth - 1)));
    return child;
  }
  return locs;
}