`scripts/seed-corridor-food.js`

254 lines
#!/usr/bin/env node
/**
 * seed-corridor-food.js
 *
 * Seed the food vertical on /find?vertical=food using public Ventura corridor
 * data (OSM POIs + LA City Business Tax Registration Certificates) already
 * normalized into the `ventura_corridor` PG database (~/Projects/ventura).
 *
 * - OSM source: 89 hand-tagged restaurants/cafes/bars on the Boulevard with
 *   names, websites, phones, cuisine — clean signal.
 * - la_btrc source: ~575 NAICS-classified food businesses with tax-cert addr.
 *   We filter on `category_raw` (NAICS label), not the noisy fuzzy-matched
 *   `category`, so "Barber shops" don't sneak in as bars.
 *
 * Output: rows in ventura_claw_leads.businesses with vertical='food',
 * tier='free', claim_status='unclaimed', source='public_records'. These are
 * directory shells — businesses claim them via the standard /claim flow.
 */

require('dotenv').config({ path: require('path').resolve(__dirname, '..', '.env') });
const slugify = require('slugify');
const { Pool } = require('pg');

// Source DB: corridor ingest (~/Projects/ventura)
const corridor = new Pool({
  host: process.env.PGHOST || 'localhost',
  port: parseInt(process.env.PGPORT || '5432', 10),
  database: 'ventura_corridor',
  user: process.env.PGUSER || process.env.USER,
  max: 4
});

// Target DB: ventura-claw-leads
const target = require('../lib/db');

// LA City BTRC NAICS labels we accept as "food vertical".
// Excludes "Barber shops" (286 false positives), "Other direct selling"
// (multi-level marketing), and "Other food mfg." (manufacturers, not retail).
const BTRC_FOOD_LABELS = new Set([
  'Full-service restaurants',
  'Full-Service Restaurants',
  'Limited-Service Restaurants',
  'Special food services (including food service contractors & caterers)',
  'Snack and Nonalcoholic Beverage Bars',
  'Drinking places (alcoholic beverages)',
  'Coffee and Tea Manufacturing',
  'Caterers',
  'Food Service Contractors',
  'Mobile Food Services'
]);

// Map LA-area city values (corridor data is mixed-case + UPPERCASE) to the
// canonical Title Case used by ventura-claw-leads view templates.
const CITY_MAP = {
  'sherman oaks':   'Sherman Oaks',
  'studio city':    'Studio City',
  'encino':         'Encino',
  'tarzana':        'Tarzana',
  'woodland hills': 'Woodland Hills',
  'woodland hls':   'Woodland Hills'
};

// ZIP fallback when city is missing — Ventura Blvd ZIPs only.
const ZIP_TO_CITY = {
  '91423': 'Sherman Oaks',
  '91403': 'Sherman Oaks',
  '91604': 'Studio City',
  '91436': 'Encino',
  '91316': 'Encino',
  '91356': 'Tarzana',
  '91364': 'Woodland Hills'
};

const TARGET_CITIES = new Set(Object.values(CITY_MAP));

function normalizeCity(raw, zip) {
  if (raw) {
    const key = String(raw).toLowerCase().split(';')[0].trim();
    if (CITY_MAP[key]) return CITY_MAP[key];
  }
  if (zip) {
    const z = String(zip).slice(0, 5);
    if (ZIP_TO_CITY[z]) return ZIP_TO_CITY[z];
  }
  return null;
}

// "JAMBA JUICE #8" -> "Jamba Juice #8". Keep numbers/punct, just titlecase
// each whitespace-separated word. la_btrc rows arrive ALL CAPS.
function titleCaseName(name) {
  if (!name) return name;
  if (name === name.toLowerCase() || name !== name.toUpperCase()) return name;
  return name.toLowerCase().replace(/\b([a-z])/g, c => c.toUpperCase());
}

// Same idea for street addresses.
function titleCaseStreet(s) {
  if (!s) return s;
  if (s !== s.toUpperCase()) return s;
  return s.toLowerCase().replace(/\b([a-z])/g, c => c.toUpperCase());
}

// Build a per-row headline. For OSM rows we have cuisine + amenity tags; for
// BTRC rows we have NAICS label. Either gives us a one-line description that
// reads better than blank.
function buildHeadline({ name, category, raw, category_raw }) {
  const tags = (raw && raw.tags) || {};
  const cuisine = tags.cuisine ? tags.cuisine.replace(/[_;]/g, ' ').replace(/\b\w/g, c => c.toUpperCase()) : null;
  const isCafe   = category === 'amenity: cafe';
  const isBar    = category === 'amenity: bar';
  const isFast   = category === 'amenity: fast food';
  const isRest   = category === 'amenity: restaurant';

  if (cuisine && isRest)   return `${cuisine} restaurant on Ventura Blvd.`;
  if (cuisine && isFast)   return `${cuisine} — quick-serve on the Boulevard.`;
  if (isCafe)              return `Coffee + light fare on Ventura Blvd.`;
  if (isBar)               return `Neighborhood bar on the Boulevard.`;
  if (isRest)              return `Sit-down restaurant on Ventura Blvd.`;
  if (isFast)              return `Quick-serve on the Boulevard.`;

  if (category_raw) {
    if (/Coffee|Tea/i.test(category_raw))     return `Coffee + tea on Ventura Blvd.`;
    if (/Caterer|Food Service Contractor/i.test(category_raw))
                                              return `Caterer + food-service on the Boulevard.`;
    if (/Drinking places/i.test(category_raw)) return `Bar on Ventura Blvd.`;
    if (/Limited-Service/i.test(category_raw)) return `Quick-serve on Ventura Blvd.`;
    if (/Snack|Beverage Bars/i.test(category_raw))
                                              return `Snacks + drinks on the Boulevard.`;
    if (/Mobile Food/i.test(category_raw))    return `Mobile food vendor on Ventura Blvd.`;
  }
  return `Local food + drink on Ventura Blvd.`;
}

// Skip rows whose name is obviously a person (la_btrc has a chunk of
// owner-name registrations like "DIANA ROBINSON" mis-tagged as bars). Also
// skip pure numerical / placeholder names.
function isPersonNameOrJunk(name) {
  if (!name) return true;
  if (name.length < 3) return true;
  // "FIRSTNAME LASTNAME" all-caps with no digits and no business-y suffix
  const looksLikePerson = /^[A-Z]+ [A-Z]+(?:[ ,]+[A-Z]+)?$/.test(name) &&
    !/(LLC|INC|CORP|CO|RESTAURANT|CAFE|GRILL|KITCHEN|BAR|FOOD|BAKERY|PIZZA|BURGER|TACO|TAQUERIA|BISTRO|BBQ|JUICE|SUSHI|RAMEN|DELI|CATERING|COFFEE|TEA|PANINI|SANDWICH)/i.test(name);
  if (looksLikePerson) return true;
  // Generic strip-mall placeholders sometimes appear
  if (/^(SUITE|UNIT|TBD|N\/A|UNKNOWN)/i.test(name)) return true;
  return false;
}

(async () => {
  console.log('[seed-corridor-food] pulling food rows from ventura_corridor…');

  // OSM-source rows (clean POIs)
  const osmSql = `
    SELECT name, category, category_raw, address, street_number, street_name, city, zip,
           lat, lng, phone, website, raw
      FROM businesses
     WHERE on_corridor = true
       AND merged_into IS NULL
       AND source = 'osm'
       AND category IN ('amenity: restaurant','amenity: cafe','amenity: bar','amenity: fast food')
  `;

  // la_btrc rows filtered on NAICS labels (avoids the "Barber shops"->bar bug)
  const btrcSql = `
    SELECT name, category, category_raw, address, street_number, street_name, city, zip,
           lat, lng, phone, website, raw
      FROM businesses
     WHERE on_corridor = true
       AND merged_into IS NULL
       AND source = 'la_btrc'
       AND category_raw = ANY($1::text[])
  `;

  const [{ rows: osmRows }, { rows: btrcRows }] = await Promise.all([
    corridor.query(osmSql),
    corridor.query(btrcSql, [Array.from(BTRC_FOOD_LABELS)])
  ]);

  console.log(`[seed-corridor-food] pulled ${osmRows.length} osm rows + ${btrcRows.length} btrc rows`);

  // OSM is higher-trust signal — ingest first so subsequent BTRC dups are skipped.
  const all = [...osmRows, ...btrcRows];
  const seenSlug = new Set();
  const seenNameAddr = new Set(); // dedupe key: lower(name)|street_number+street_name|zip

  let inserted = 0, skipped = 0, conflicted = 0, dropped_junk = 0, dropped_city = 0;

  for (const r of all) {
    const cleanName = titleCaseName(r.name);
    if (isPersonNameOrJunk(r.name)) { dropped_junk++; continue; }

    const city = normalizeCity(r.city, r.zip);
    if (!city || !TARGET_CITIES.has(city)) { dropped_city++; continue; }

    const street = titleCaseStreet(r.address || [r.street_number, r.street_name].filter(Boolean).join(' '));
    if (!street) { dropped_junk++; continue; }

    // Strip city/zip suffix from address ("14704 Ventura Boulevard Sherman Oaks 91403")
    let cleanStreet = street;
    cleanStreet = cleanStreet.replace(new RegExp(`\\s+${city}\\s+\\d{5}(-\\d{4})?$`, 'i'), '');
    cleanStreet = cleanStreet.replace(/\s+\d{5}(-\d{4})?$/, '');

    const dedupeKey = `${cleanName.toLowerCase()}|${cleanStreet.toLowerCase()}|${(r.zip||'').slice(0,5)}`;
    if (seenNameAddr.has(dedupeKey)) { skipped++; continue; }
    seenNameAddr.add(dedupeKey);

    // Slug must be globally unique. Append city for collision safety
    // (Chipotle on Ventura appears in 3 cities).
    let slug = slugify(`${cleanName} ${city}`, { lower: true, strict: true });
    if (seenSlug.has(slug)) {
      // Disambiguate with last 4 of zip + first 3 of street number
      const sn = (r.street_number || '').replace(/\D/g, '').slice(0, 4) || '0';
      slug = `${slug}-${sn}`;
    }
    seenSlug.add(slug);

    const headline = buildHeadline(r);
    const description = `${cleanName} is a food & drink business on the Ventura Blvd corridor in ${city}. ${headline}`;

    const phone   = r.phone ? r.phone.replace(/^\+1[-\s]?/, '').trim() : null;
    const website = r.website || null;
    const zip     = (r.zip || '').slice(0, 10) || null;

    try {
      const ins = await target.query(
        `INSERT INTO businesses
           (slug, business_name, vertical, headline, description, street, city, state, zip, neighborhood,
            latitude, longitude, phone, website,
            tier, claim_status, verified, status, source)
         VALUES ($1,$2,'food',$3,$4,$5,$6,'CA',$7,$8,$9,$10,$11,$12,
                 'free','unclaimed',false,'active','public_records')
         ON CONFLICT (slug) DO NOTHING
         RETURNING id`,
        [slug, cleanName, headline, description, cleanStreet, city, zip, city,
         r.lat, r.lng, phone, website]
      );
      if (ins.rows.length) inserted++;
      else conflicted++;
    } catch (err) {
      console.error(`[seed-corridor-food] insert failed for "${cleanName}": ${err.message}`);
      skipped++;
    }
  }

  console.log(`[seed-corridor-food] inserted=${inserted} skipped(dup)=${skipped} conflicted(slug-exists)=${conflicted} dropped_junk=${dropped_junk} dropped_city=${dropped_city}`);

  await corridor.end();
  await target.pool.end();
  process.exit(0);
})().catch(err => {
  console.error('[seed-corridor-food] fatal', err);
  process.exit(1);
});