`src/ingest/here_discover.ts`

233 lines
/**
 * HERE Discover ingest — Ventura Blvd corridor.
 *
 * Strategy:
 *   1. Walk a coarse polyline of Ventura Blvd west→east in ~600m steps.
 *   2. For each anchor point, call HERE /discover with q="businesses" and a small radius.
 *   3. UPSERT each unique result into businesses (source='here', source_id=HERE id).
 *   4. Mark on_corridor=TRUE only when address.street matches /VENTURA BLVD/i.
 *   5. Log the run in ingest_runs.
 *
 * Free tier: 30,000 requests/month. Each step = 1 request. ~70 steps = ~70 req → trivial cost.
 *
 * Run: npm run ingest:here
 *      or: tsx src/ingest/here_discover.ts -- --limit-steps=5  (smoke test)
 */
import 'dotenv/config';
import { pool, query } from '../../db/pool.ts';

const HERE_KEY = process.env.HERE_API_KEY;
if (!HERE_KEY) {
  console.error('[here] missing HERE_API_KEY in .env. Get one at https://platform.here.com or via /secrets.');
  process.exit(1);
}

// ─── Ventura Blvd polyline anchor points (approx, west → east) ──────────
// Pulled from OpenStreetMap centerline, sampled every ~600m. Covers
// Calabasas (Mulholland) → Woodland Hills → Tarzana → Encino → Sherman Oaks
// → Studio City → North Hollywood (Cahuenga). Total ~17 mi.
const ANCHORS: Array<{ lat: number; lng: number; city: string }> = [
  // Calabasas / west end
  { lat: 34.1466, lng: -118.6086, city: 'Calabasas' },
  { lat: 34.1505, lng: -118.6005, city: 'Calabasas' },
  { lat: 34.1530, lng: -118.5915, city: 'Woodland Hills' },
  { lat: 34.1545, lng: -118.5810, city: 'Woodland Hills' },
  { lat: 34.1556, lng: -118.5705, city: 'Woodland Hills' },
  { lat: 34.1563, lng: -118.5605, city: 'Woodland Hills' },
  { lat: 34.1568, lng: -118.5500, city: 'Woodland Hills' },
  { lat: 34.1572, lng: -118.5400, city: 'Woodland Hills' },
  { lat: 34.1575, lng: -118.5305, city: 'Tarzana' },
  { lat: 34.1577, lng: -118.5210, city: 'Tarzana' },
  { lat: 34.1580, lng: -118.5115, city: 'Tarzana' },
  { lat: 34.1583, lng: -118.5025, city: 'Encino' },
  { lat: 34.1586, lng: -118.4930, city: 'Encino' },
  { lat: 34.1590, lng: -118.4835, city: 'Encino' },
  { lat: 34.1593, lng: -118.4740, city: 'Encino' },
  { lat: 34.1597, lng: -118.4645, city: 'Encino' },
  { lat: 34.1600, lng: -118.4550, city: 'Sherman Oaks' },
  { lat: 34.1604, lng: -118.4455, city: 'Sherman Oaks' },
  { lat: 34.1606, lng: -118.4360, city: 'Sherman Oaks' },
  { lat: 34.1599, lng: -118.4270, city: 'Sherman Oaks' },
  { lat: 34.1565, lng: -118.4200, city: 'Sherman Oaks' },
  { lat: 34.1505, lng: -118.4140, city: 'Studio City' },
  { lat: 34.1450, lng: -118.4070, city: 'Studio City' },
  { lat: 34.1421, lng: -118.3985, city: 'Studio City' },
  { lat: 34.1402, lng: -118.3895, city: 'Studio City' },
  { lat: 34.1385, lng: -118.3820, city: 'Studio City' },
  { lat: 34.1370, lng: -118.3760, city: 'North Hollywood' }, // Cahuenga junction (east end)
];

const RADIUS_M = 350;            // overlap so we don't miss between anchors
const PER_STEP_LIMIT = 100;      // HERE max items per call
const SLEEP_MS = 250;            // polite throttle

const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));

interface HereItem {
  id: string;
  title: string;
  resultType?: string;
  address?: {
    label?: string;
    countryCode?: string;
    state?: string;
    city?: string;
    district?: string;
    street?: string;
    houseNumber?: string;
    postalCode?: string;
  };
  position?: { lat: number; lng: number };
  contacts?: Array<{ phone?: Array<{ value: string }>; www?: Array<{ value: string }> }>;
  categories?: Array<{ id: string; name: string; primary?: boolean }>;
}

function topCategory(item: HereItem): { name: string | null; raw: string | null } {
  const cats = item.categories || [];
  const primary = cats.find((c) => c.primary) || cats[0];
  return { name: primary?.name || null, raw: primary?.id || null };
}

function normalizeStreet(s?: string): string | null {
  if (!s) return null;
  return s.toUpperCase().replace(/\s+/g, ' ').trim();
}

function isVenturaBlvd(s?: string): boolean {
  if (!s) return false;
  return /\bVENTURA\s+B(L)?V?D\b/i.test(s) || /\bVENTURA\s+BOULEVARD\b/i.test(s);
}

async function fetchStep(anchor: { lat: number; lng: number }): Promise<HereItem[]> {
  const url = new URL('https://discover.search.hereapi.com/v1/discover');
  url.searchParams.set('apiKey', HERE_KEY!);
  url.searchParams.set('at', `${anchor.lat},${anchor.lng}`);
  url.searchParams.set('q', 'business');
  url.searchParams.set('limit', String(PER_STEP_LIMIT));
  url.searchParams.set('in', `circle:${anchor.lat},${anchor.lng};r=${RADIUS_M}`);
  const r = await fetch(url, { headers: { 'User-Agent': 'ventura-corridor/0.1 (+local)' } });
  if (!r.ok) {
    const txt = await r.text().catch(() => '');
    throw new Error(`HERE ${r.status} ${r.statusText}: ${txt.slice(0, 200)}`);
  }
  const j = await r.json() as { items?: HereItem[] };
  return j.items || [];
}

async function upsertBusiness(item: HereItem, anchorCity: string): Promise<'new' | 'updated'> {
  const cat = topCategory(item);
  const street = normalizeStreet(item.address?.street);
  const onCorridor = isVenturaBlvd(item.address?.street) ||
                     isVenturaBlvd(item.address?.label);
  const block = item.address?.houseNumber
    ? Math.floor(parseInt(item.address.houseNumber, 10) / 100) * 100 || null
    : null;
  const phone = item.contacts?.[0]?.phone?.[0]?.value || null;
  const website = item.contacts?.[0]?.www?.[0]?.value || null;

  const r = await query<{ id: number; first_seen_at: string }>(`
    INSERT INTO businesses (
      source, source_id, name, category, category_raw,
      address, street_number, street_name, city, zip, lat, lng,
      phone, website, on_corridor, corridor_block, raw, last_seen_at
    ) VALUES (
      'here', $1, $2, $3, $4,
      $5, $6, $7, $8, $9, $10, $11,
      $12, $13, $14, $15, $16, NOW()
    )
    ON CONFLICT (source, source_id) DO UPDATE SET
      name           = EXCLUDED.name,
      category       = EXCLUDED.category,
      category_raw   = EXCLUDED.category_raw,
      address        = EXCLUDED.address,
      street_number  = EXCLUDED.street_number,
      street_name    = EXCLUDED.street_name,
      city           = COALESCE(EXCLUDED.city, businesses.city),
      zip            = COALESCE(EXCLUDED.zip, businesses.zip),
      lat            = EXCLUDED.lat,
      lng            = EXCLUDED.lng,
      phone          = COALESCE(EXCLUDED.phone, businesses.phone),
      website        = COALESCE(EXCLUDED.website, businesses.website),
      on_corridor    = EXCLUDED.on_corridor OR businesses.on_corridor,
      corridor_block = COALESCE(EXCLUDED.corridor_block, businesses.corridor_block),
      raw            = EXCLUDED.raw,
      last_seen_at   = NOW()
    RETURNING id, first_seen_at
  `, [
    item.id,
    item.title,
    cat.name,
    cat.raw,
    item.address?.label || null,
    item.address?.houseNumber || null,
    street,
    item.address?.city || anchorCity,
    item.address?.postalCode || null,
    item.position?.lat ?? null,
    item.position?.lng ?? null,
    phone,
    website,
    onCorridor,
    block,
    JSON.stringify(item),
  ]);
  // Same-second first_seen_at means this row was just inserted.
  return Date.parse(r.rows[0].first_seen_at) > Date.now() - 5_000 ? 'new' : 'updated';
}

async function main() {
  const argLimit = process.argv.find((a) => a.startsWith('--limit-steps='));
  const stepLimit = argLimit ? parseInt(argLimit.split('=')[1], 10) : ANCHORS.length;

  const run = await query<{ id: number }>(
    `INSERT INTO ingest_runs (source, notes) VALUES ('here', $1) RETURNING id`,
    [`Ventura Blvd corridor — ${stepLimit}/${ANCHORS.length} anchors`],
  );
  const runId = run.rows[0].id;
  console.log(`[here] ingest_run #${runId} starting · ${stepLimit} anchors · ${RADIUS_M}m radius`);

  let inTotal = 0, newTotal = 0, updTotal = 0, errMessage: string | null = null;

  for (let i = 0; i < stepLimit; i++) {
    const a = ANCHORS[i];
    process.stdout.write(`[step ${i + 1}/${stepLimit}] ${a.city.padEnd(16)} ${a.lat.toFixed(4)},${a.lng.toFixed(4)} · `);
    try {
      const items = await fetchStep(a);
      let stepNew = 0, stepUpd = 0;
      for (const it of items) {
        const v = await upsertBusiness(it, a.city);
        if (v === 'new') stepNew++; else stepUpd++;
      }
      inTotal += items.length;
      newTotal += stepNew;
      updTotal += stepUpd;
      console.log(`fetched ${items.length} · new ${stepNew} · upd ${stepUpd}`);
    } catch (e: any) {
      console.log(`ERROR: ${e.message}`);
      errMessage = e.message;
      break;
    }
    if (i < stepLimit - 1) await sleep(SLEEP_MS);
  }

  await query(
    `UPDATE ingest_runs SET finished_at=NOW(), rows_in=$1, rows_new=$2, rows_updated=$3, error_message=$4 WHERE id=$5`,
    [inTotal, newTotal, updTotal, errMessage, runId],
  );

  // Summary
  const corridor = await query<{ n: string }>(
    `SELECT COUNT(*)::text AS n FROM businesses WHERE on_corridor`,
  );
  console.log('');
  console.log(`[here] done. fetched ${inTotal} · new ${newTotal} · updated ${updTotal}`);
  console.log(`[here] businesses on Ventura Blvd: ${corridor.rows[0].n}`);

  await pool.end();
}

main().catch((e) => {
  console.error('[here]', e);
  process.exit(1);
});