`src/ingest/foursquare.ts`

244 lines
/**
 * Foursquare Places ingest — Ventura Blvd corridor.
 *
 * Free tier: 100,000 requests/month with sign-up. Best free option that
 * returns the business website explicitly (HERE is OK; Yelp rarely returns it).
 *
 * Get a key at https://location.foursquare.com → developer dashboard.
 * Add to ~/Projects/ventura-corridor/.env:  FOURSQUARE_API_KEY=fsq3...
 *
 * Strategy: same anchor walk as HERE (~27 points along Ventura Blvd, 350m
 * radius). UPSERTs into businesses(source='foursquare'). Marks on_corridor=TRUE
 * only when address.address matches /VENTURA BL?V?D/i.
 *
 * Endpoint: https://places-api.foursquare.com/places/search?ll=...&radius=...
 *
 * Run:
 *   npm run ingest:foursquare
 *   tsx src/ingest/foursquare.ts -- --limit-steps=3   # smoke test
 */
import 'dotenv/config';
import { pool, query } from '../../db/pool.ts';

const FSQ_KEY = process.env.FOURSQUARE_API_KEY;
if (!FSQ_KEY) {
  console.error('[fsq] missing FOURSQUARE_API_KEY in .env. Get one at https://location.foursquare.com.');
  process.exit(1);
}

// Same anchor list as HERE — Ventura Blvd west→east, ~600m apart.
const ANCHORS: Array<{ lat: number; lng: number; city: string }> = [
  { 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' },
];

const RADIUS_M = 350;
const PER_STEP_LIMIT = 50;        // Foursquare hard cap is 50 per request
const SLEEP_MS = 200;

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

interface FsqPlace {
  fsq_place_id: string;
  name: string;
  categories?: Array<{ id: number; name: string; short_name?: string; plural_name?: string }>;
  location?: {
    address?: string;
    locality?: string;
    region?: string;
    postcode?: string;
    formatted_address?: string;
  };
  latitude?: number;
  longitude?: number;
  tel?: string;
  website?: string;
  social_media?: { facebook_id?: string; instagram?: string; twitter?: string };
  email?: string;
}

function topCategory(p: FsqPlace): { name: string | null; raw: string | null } {
  const cats = p.categories || [];
  const c = cats[0];
  return { name: c?.name || null, raw: c ? String(c.id) : null };
}

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);
}

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

function parseHouseNumber(addr?: string): string | null {
  if (!addr) return null;
  const m = addr.match(/^\s*(\d+)\s/);
  return m ? m[1] : null;
}

async function fetchStep(anchor: { lat: number; lng: number }): Promise<FsqPlace[]> {
  const url = new URL('https://places-api.foursquare.com/places/search');
  url.searchParams.set('ll', `${anchor.lat},${anchor.lng}`);
  url.searchParams.set('radius', String(RADIUS_M));
  url.searchParams.set('limit', String(PER_STEP_LIMIT));
  url.searchParams.set('fields', 'fsq_place_id,name,categories,location,latitude,longitude,tel,website,social_media,email');
  const r = await fetch(url, {
    headers: {
      'X-Places-Api-Version': '2025-06-17',
      'Authorization': `Bearer ${FSQ_KEY}`,
      'Accept': 'application/json',
      'User-Agent': 'ventura-corridor/0.1 (+local)',
    },
  });
  if (!r.ok) {
    const txt = await r.text().catch(() => '');
    throw new Error(`Foursquare ${r.status} ${r.statusText}: ${txt.slice(0, 200)}`);
  }
  const j = await r.json() as { results?: FsqPlace[] };
  return j.results || [];
}

async function upsertPlace(p: FsqPlace, anchorCity: string): Promise<'new' | 'updated'> {
  const cat = topCategory(p);
  const addr = p.location?.address;
  const street = normalizeStreet(addr);
  const onCorridor = isVenturaBlvd(addr) || isVenturaBlvd(p.location?.formatted_address);
  const houseNum = parseHouseNumber(addr);
  const block = houseNum ? Math.floor(parseInt(houseNum, 10) / 100) * 100 || null : 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 (
      'foursquare', $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
  `, [
    p.fsq_place_id,
    p.name,
    cat.name,
    cat.raw,
    p.location?.formatted_address || addr || null,
    houseNum,
    street,
    p.location?.locality || anchorCity,
    p.location?.postcode || null,
    p.latitude ?? null,
    p.longitude ?? null,
    p.tel || null,
    p.website || null,
    onCorridor,
    block,
    JSON.stringify(p),
  ]);
  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 ('foursquare', $1) RETURNING id`,
    [`Ventura Blvd corridor — ${stepLimit}/${ANCHORS.length} anchors`],
  );
  const runId = run.rows[0].id;
  console.log(`[fsq] 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 places = await fetchStep(a);
      let stepNew = 0, stepUpd = 0;
      for (const p of places) {
        const v = await upsertPlace(p, a.city);
        if (v === 'new') stepNew++; else stepUpd++;
      }
      inTotal += places.length;
      newTotal += stepNew;
      updTotal += stepUpd;
      console.log(`fetched ${places.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],
  );

  const corridor = await query<{ n: string }>(
    `SELECT COUNT(*)::text AS n FROM businesses WHERE on_corridor`,
  );
  const withSite = await query<{ n: string }>(
    `SELECT COUNT(*)::text AS n FROM businesses WHERE on_corridor AND website IS NOT NULL`,
  );
  console.log('');
  console.log(`[fsq] done. fetched ${inTotal} · new ${newTotal} · updated ${updTotal}`);
  console.log(`[fsq] businesses on Ventura Blvd: ${corridor.rows[0].n}`);
  console.log(`[fsq] with websites: ${withSite.rows[0].n}`);

  await pool.end();
}

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