`scripts/ingest-sunset-corridor.js`

315 lines
#!/usr/bin/env node
// Sunset Blvd Corridor ingest — every registered business on the ~22-mile
// stretch of Sunset Boulevard from Echo Park to the Pacific Ocean.
//
// Sunset Blvd runs ~22 miles continuously from Sunset/Figueroa (Echo Park,
// downtown) west through Silver Lake → Hollywood → Sunset Strip
// (West Hollywood) → Beverly Hills → Bel-Air → Brentwood → Pacific
// Palisades, ending at Pacific Coast Highway. Multiple jurisdictions on this
// single street:
//   - City of Los Angeles (Echo Park, Silver Lake, Hollywood, Bel-Air,
//     Brentwood, Pacific Palisades neighborhoods)
//   - City of West Hollywood (Sunset Strip)
//   - City of Beverly Hills
//
// "Registered with the city" = LA Office of Finance BTRC for City-of-LA stretches,
// WeHo and Beverly Hills business licenses for those jurisdictions. Neither has
// a public bulk feed (per la-research-agent), so we approximate by pulling
// every commercial presence on/near Sunset Blvd from OpenStreetMap and tagging
// them `corridor: 'sunset-blvd-22mi'` 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-sunset-corridor.js          # full corridor ingest
//   DRY=1 node scripts/ingest-sunset-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/osm-ingest';
const OVERPASS_URL = process.env.OVERPASS_URL || 'https://overpass-api.de/api/interpreter';
const CORRIDOR_TAG = 'sunset-blvd-22mi';

// Bounding box covering Sunset Blvd from Echo Park (east end, ~ -118.26)
// to Pacific Coast Highway (west end, ~ -118.55). Roughly 22 mi long.
// Overpass uses "south,west,north,east".
const BBOX = '34.050,-118.560,34.120,-118.230';

// 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';

// BBCB zip codes that fall along Sunset Blvd. Used in the SQL post-step to
// retroactively flag DCA-licensed shops that are already in the DB but
// weren't tagged with the corridor (their `addr:street` from BBCB usually
// mentions "Sunset" if they're actually on the strip).
const SUNSET_ZIPS = [
  '90026', // Echo Park / Silver Lake (eastern end)
  '90039', // Silver Lake
  '90029', // East Hollywood
  '90028', // Hollywood
  '90069', // West Hollywood (Sunset Strip)
  '90046', // West Hollywood / Hollywood Hills West
  '90048', // Beverly Grove area
  '90212', // Beverly Hills
  '90210', // Beverly Hills (Sunset crosses through 90210)
  '90077', // Bel-Air
  '90049', // Brentwood
  '90272', // Pacific Palisades
];

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, 'sunset-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 Sunset 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
// "Sunset" (Sunset Blvd, Sunset Boulevard, Sunset Plaza Dr, etc.) — this
// excludes everything within the bbox that's on a side street.
function isOnSunsetBlvd(t) {
  const street = (t['addr:street'] || '').toLowerCase();
  return /sunset/.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 (!isOnSunsetBlvd(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, // Hollywood, West Hollywood, Beverly Hills, 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;
}

// Tag any DCA-licensed BBCB shop whose zip falls in the Sunset corridor zip
// list AND whose address contains "sunset". Mirrors the Ventura post-step.
async function tagBbcbAlongSunset() {
  const zipPattern = '^(' + SUNSET_ZIPS.join('|') + ')';
  const r = await query(
    `UPDATE businesses
        SET source_data_json = jsonb_set(
              COALESCE(source_data_json, '{}'::jsonb),
              '{corridor}',
              to_jsonb($2::text)
            ),
            updated_at = now()
      WHERE source_data_json->>'source' = 'dca_bbcb'
        AND zip ~ $1
        AND address ~* 'sunset'
      RETURNING slug`,
    [zipPattern, CORRIDOR_TAG]
  );
  return r.rowCount;
}

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, skippedNotOnSunset = 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 (!isOnSunsetBlvd(t)) { skippedNotOnSunset++; 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_sunset=${skippedNotOnSunset} 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(' ')}`);

  let bbcbTagged = 0;
  if (!DRY) {
    bbcbTagged = await tagBbcbAlongSunset();
    log(`BBCB-along-Sunset tagged: ${bbcbTagged}`);

    const tally = await one(`
      SELECT COUNT(*) AS n,
             COUNT(*) FILTER (WHERE latitude IS NOT NULL) AS geocoded
        FROM businesses
       WHERE source_data_json->>'corridor' = $1
    `, [CORRIDOR_TAG]).catch(() => null);
    if (tally) log(`DB now: corridor=${tally.n} geocoded=${tally.geocoded}`);
  }
  process.exit(0);
}

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