`scripts/ingest-ventura-corridor.js`

277 lines
#!/usr/bin/env node
// Ventura Blvd Corridor ingest — every registered business on the ~20-mile
// stretch of Ventura Boulevard through the San Fernando Valley.
//
// Ventura Blvd runs ~17.6 miles continuously from Topanga Canyon Blvd
// (Woodland Hills) through Tarzana → Encino → Sherman Oaks → Studio City
// → Universal City, then continues into Calabasas as Calabasas Rd.
// Total catchment ~20 miles. Multiple jurisdictions on this single street:
//   - City of Los Angeles (Sherman Oaks, Studio City, Encino, Tarzana, Woodland Hills)
//   - City of Calabasas
//   - small slivers of unincorporated LA County
//
// "Registered with the city" = LA Office of Finance BTRC for City-of-LA stretches,
// Calabasas business license for Calabasas. Neither has a public bulk feed
// (per la-research-agent), so we approximate by pulling every commercial
// presence on/near Ventura Blvd from OpenStreetMap and tagging them
// `corridor: 'ventura-blvd-20mi'` for filtering.
//
// Tag taxonomy: ALL business types — not just salons. Restaurants, cafes,
// banks, dry cleaners, gyms, dentists, retail. Whatever has an OSM presence
// on the strip.
//
//   node scripts/ingest-ventura-corridor.js          # full corridor ingest
//   DRY=1 node scripts/ingest-ventura-corridor.js    # parse only

import 'dotenv/config';
import fs from 'node:fs';
import path from 'node:path';
import { query, one } from '../src/lib/db.js';
import { slugify } from '../src/lib/slug.js';

const TMP_DIR = '/tmp/ventura-corridor';
const OVERPASS_URL = process.env.OVERPASS_URL || 'https://overpass-api.de/api/interpreter';
const CORRIDOR_TAG = 'ventura-blvd-20mi';

// Bounding box covering Ventura Blvd from Calabasas (west end) to Lankershim
// Blvd / Universal City (east end). Roughly 20 mi long × 0.5 mi wide buffer.
//   south-west: 34.130, -118.665
//   north-east: 34.165, -118.355
// Overpass uses "south,west,north,east".
const BBOX = '34.125,-118.680,34.175,-118.350';

// Reverse-mapping OSM tag → our category. We treat anything commercial as a
// candidate; the full list is intentionally broad to capture "all businesses".
const SHOP_TAG_TO_CATEGORY = {
  hairdresser: 'salon', beauty: 'salon', massage: 'spa', cosmetics: 'salon',
  // Eat/drink
  restaurant: 'restaurant', cafe: 'cafe', bakery: 'bakery', deli: 'deli',
  bar: 'bar', pub: 'bar', fast_food: 'restaurant', ice_cream: 'cafe',
  // Retail
  clothes: 'retail', shoes: 'retail', jewelry: 'retail', books: 'retail',
  florist: 'retail', gift: 'retail', wine: 'retail', alcohol: 'retail',
  optician: 'retail', furniture: 'retail', boutique: 'retail',
  supermarket: 'grocery', convenience: 'grocery',
  // Services
  dry_cleaning: 'service', laundry: 'service', tailor: 'service',
  car_repair: 'service', car: 'service',
  // Health / wellness
  pharmacy: 'pharmacy', chemist: 'pharmacy',
  // Pets
  pet: 'retail',
};
const AMENITY_TAG_TO_CATEGORY = {
  restaurant: 'restaurant', cafe: 'cafe', bar: 'bar', pub: 'bar',
  fast_food: 'restaurant', ice_cream: 'cafe', food_court: 'restaurant',
  bank: 'finance', atm: 'finance',
  pharmacy: 'pharmacy', dentist: 'health', doctors: 'health', clinic: 'health', veterinary: 'health',
  spa: 'spa',
  gym: 'fitness', fitness_centre: 'fitness',
  cinema: 'entertainment', theatre: 'entertainment', nightclub: 'entertainment',
  fuel: 'service', car_wash: 'service',
};

const DRY = process.env.DRY === '1';

function ts() { return new Date().toISOString().replace('T', ' ').slice(0, 19); }
function log(m) { console.log(`[${ts()}] ${m}`); }

function buildQuery() {
  return `[out:json][timeout:240];
(
  node["shop"](${BBOX});
  way["shop"](${BBOX});
  node["amenity"~"^(restaurant|cafe|bar|pub|fast_food|ice_cream|food_court|bank|atm|pharmacy|dentist|doctors|clinic|veterinary|spa|gym|fitness_centre|cinema|theatre|nightclub|fuel|car_wash)$"](${BBOX});
  way["amenity"~"^(restaurant|cafe|bar|pub|fast_food|ice_cream|food_court|bank|atm|pharmacy|dentist|doctors|clinic|veterinary|spa|gym|fitness_centre|cinema|theatre|nightclub|fuel|car_wash)$"](${BBOX});
  node["leisure"="spa"](${BBOX});
  way["leisure"="spa"](${BBOX});
  node["office"](${BBOX});
  way["office"](${BBOX});
);
out center tags;`;
}

async function fetchData() {
  const cachePath = path.join(TMP_DIR, 'corridor.json');
  if (fs.existsSync(cachePath) && fs.statSync(cachePath).size > 1000) {
    log(`reusing cache (${(fs.statSync(cachePath).size / 1024).toFixed(0)} KB)`);
    return JSON.parse(fs.readFileSync(cachePath, 'utf8'));
  }
  log('querying Overpass for the entire Ventura Blvd corridor (~30-90s)…');
  const body = 'data=' + encodeURIComponent(buildQuery());
  const res = await fetch(OVERPASS_URL, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/x-www-form-urlencoded',
      'Accept': 'application/json',
      'User-Agent': 'la-salon-directory/1.0 (+steveabramsdesigns@gmail.com)',
    },
    body,
  });
  if (!res.ok) throw new Error(`Overpass HTTP ${res.status}`);
  const j = await res.json();
  fs.writeFileSync(cachePath, JSON.stringify(j));
  log(`${j.elements?.length || 0} elements, cached → ${cachePath}`);
  return j;
}

// Determine category from OSM tags. Priority: shop → amenity → leisure → office.
function categorizeFromTags(t) {
  if (t.shop && SHOP_TAG_TO_CATEGORY[t.shop])         return { cat: SHOP_TAG_TO_CATEGORY[t.shop], rawTag: 'shop=' + t.shop };
  if (t.amenity && AMENITY_TAG_TO_CATEGORY[t.amenity]) return { cat: AMENITY_TAG_TO_CATEGORY[t.amenity], rawTag: 'amenity=' + t.amenity };
  if (t.leisure === 'spa')                            return { cat: 'spa', rawTag: 'leisure=spa' };
  if (t.office)                                       return { cat: 'office', rawTag: 'office=' + t.office };
  // Catch-all: any shop=* tag we didn't enumerate
  if (t.shop)                                         return { cat: 'retail', rawTag: 'shop=' + t.shop };
  return null;
}

// Address-based filter: only keep elements where addr:street contains
// "Ventura" (Ventura Blvd, Ventura Boulevard, Ventura Pl, etc.) — this
// excludes everything within the bbox that's on a side street.
function isOnVenturaBlvd(t) {
  const street = (t['addr:street'] || '').toLowerCase();
  return /ventura/.test(street);
}

function refineSalonCategory(rawTag, name, base) {
  if (base !== 'salon') return base;
  if (/\b(barber|barbers|barbershop|fade|cuts?|men.?s)\b/i.test(name || '')) return 'barbershop';
  return 'salon';
}

function makeSlug(name, zip, osmId) {
  const base = slugify(name) || 'biz';
  if (zip && /^\d{5}/.test(zip)) return `${base}-${zip.slice(0, 5)}`;
  return `osm-${osmId}-${base}`.slice(0, 200);
}

function processElement(el) {
  const t = el.tags || {};
  const name = (t.name || '').trim();
  if (!name) return null;
  const cat = categorizeFromTags(t);
  if (!cat) return null;
  if (!isOnVenturaBlvd(t)) return null;

  const lat = el.lat ?? el.center?.lat ?? null;
  const lon = el.lon ?? el.center?.lon ?? null;
  const street = [t['addr:housenumber'], t['addr:street']].filter(Boolean).join(' ').trim();
  const city = (t['addr:city'] || '').trim();
  const state = (t['addr:state'] || 'CA').trim();
  const zip = (t['addr:postcode'] || '').trim().slice(0, 10);
  const phone = (t.phone || t['contact:phone'] || '').trim().slice(0, 32) || null;
  const website = (t.website || t['contact:website'] || '').trim() || null;

  const finalCat = refineSalonCategory(cat.rawTag, name, cat.cat);

  return {
    slug: makeSlug(name, zip, el.id),
    name,
    category: finalCat,
    address: street || null,
    city: city || null,
    state,
    zip: zip || null,
    neighborhood: city || null, // Sherman Oaks, Studio City, Encino, etc. — these ARE the neighborhood for the corridor
    latitude: lat,
    longitude: lon,
    phone,
    website,
    source_data_json: {
      source: 'osm-corridor',
      source_id: `${el.type}/${el.id}`,
      source_url: `https://www.openstreetmap.org/${el.type}/${el.id}`,
      tier: 'B',
      retrieved_at: new Date().toISOString(),
      raw_tag: cat.rawTag,
      corridor: CORRIDOR_TAG,
      tags: t,
    },
  };
}

async function upsert(row) {
  const r = await query(
    `INSERT INTO businesses
       (slug, name, category, address, city, state, zip, neighborhood,
        latitude, longitude, phone, website, source_data_json, tier)
     VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,'free')
     ON CONFLICT (slug) DO UPDATE SET
       latitude       = COALESCE(businesses.latitude, EXCLUDED.latitude),
       longitude      = COALESCE(businesses.longitude, EXCLUDED.longitude),
       phone          = COALESCE(businesses.phone, EXCLUDED.phone),
       website        = COALESCE(businesses.website, EXCLUDED.website),
       neighborhood   = COALESCE(EXCLUDED.neighborhood, businesses.neighborhood),
       address        = COALESCE(NULLIF(businesses.address,''), EXCLUDED.address),
       source_data_json = jsonb_set(
         COALESCE(businesses.source_data_json, '{}'::jsonb),
         '{corridor}', to_jsonb(${"'" + CORRIDOR_TAG + "'"}::text)
       ),
       updated_at     = now()
     RETURNING (xmax = 0) AS was_insert`,
    [row.slug, row.name, row.category, row.address, row.city, row.state, row.zip,
     row.neighborhood, row.latitude, row.longitude, row.phone, row.website,
     JSON.stringify(row.source_data_json)]
  );
  return r.rows[0]?.was_insert;
}

async function main() {
  fs.mkdirSync(TMP_DIR, { recursive: true });
  log(`mode=${DRY ? 'DRY' : 'WRITE'} corridor=${CORRIDOR_TAG} bbox=${BBOX}`);

  const data = await fetchData();
  let matched = 0, inserted = 0, updated = 0, skippedNotOnVentura = 0, skippedNoCat = 0;
  const byCategory = {};
  const byCity = {};

  for (const el of data.elements || []) {
    const t = el.tags || {};
    const cat = categorizeFromTags(t);
    if (!cat || !t.name) { skippedNoCat++; continue; }
    if (!isOnVenturaBlvd(t)) { skippedNotOnVentura++; continue; }

    const row = processElement(el);
    if (!row) continue;
    matched++;
    byCategory[row.category] = (byCategory[row.category] || 0) + 1;
    if (row.city) byCity[row.city] = (byCity[row.city] || 0) + 1;

    if (DRY) {
      if (matched <= 12) console.log(`  ${(row.slug || '').padEnd(60)} ${row.name} · ${row.category} · ${row.city || '?'}`);
      continue;
    }
    try {
      const ins = await upsert(row);
      if (ins) inserted++; else updated++;
    } catch (e) {
      log(`  ERR ${row.slug}: ${e.message.slice(0, 200)}`);
    }
  }

  log(`MATCHED=${matched} inserted=${inserted} updated=${updated}`);
  log(`SKIPPED: not_on_ventura=${skippedNotOnVentura} no_category=${skippedNoCat}`);
  log(`BY CATEGORY: ${Object.entries(byCategory).sort((a,b)=>b[1]-a[1]).slice(0,15).map(([k,v])=>`${k}=${v}`).join(' ')}`);
  log(`BY CITY: ${Object.entries(byCity).sort((a,b)=>b[1]-a[1]).slice(0,10).map(([k,v])=>`${k}=${v}`).join(' ')}`);

  if (!DRY) {
    const tally = await one(`
      SELECT
        COUNT(*) FILTER (WHERE source_data_json->>'corridor' = $1) AS corridor,
        COUNT(*) FILTER (WHERE source_data_json->>'corridor' = $1 AND latitude IS NOT NULL) AS corridor_geocoded,
        json_object_agg(category, n) AS by_cat
      FROM (
        SELECT category, COUNT(*) AS n FROM businesses
        WHERE source_data_json->>'corridor' = $1 GROUP BY category
      ) s, businesses b WHERE b.source_data_json->>'corridor' = $1
      GROUP BY s.category, s.n
      LIMIT 1
    `, [CORRIDOR_TAG]).catch(() => null);
    if (tally) log(`DB now: corridor=${tally.corridor} geocoded=${tally.corridor_geocoded}`);
  }
  process.exit(0);
}

main().catch(e => { console.error(e); process.exit(2); });