`scripts/data-hound-social.js`

120 lines
#!/usr/bin/env node
// Data Hound #2 — for businesses with a website but no IG/Yelp/FB on file,
// fetch the homepage and scrape outbound social links + about_text fallback.
// Uses existing src/scrape/website_meta.js for the safe SSRF-guarded fetch.
//
//   node scripts/data-hound-social.js
//   LIMIT=100 node scripts/data-hound-social.js
//   DRY=1 node scripts/data-hound-social.js

import 'dotenv/config';
import { query } from '../src/lib/db.js';
import { fetchHtml } from '../src/scrape/website_meta.js';
import * as cheerio from 'cheerio';

const SLEEP_MS = 1500; // be polite — 1 page every 1.5s
const LIMIT = process.env.LIMIT ? parseInt(process.env.LIMIT, 10) : null;
const DRY = process.env.DRY === '1';

const ts = () => new Date().toISOString().replace('T', ' ').slice(0, 19);
const log = m => console.log(`[${ts()}] ${m}`);
const sleep = ms => new Promise(r => setTimeout(r, ms));

const SOCIAL_PATTERNS = {
  instagram: /(?:https?:\/\/)?(?:www\.)?instagram\.com\/([a-zA-Z0-9._]{2,30})\/?(?:[?#].*)?$/i,
  yelp:      /(?:https?:\/\/)?(?:www\.)?yelp\.com\/biz\/([a-zA-Z0-9_-]+)/i,
  facebook:  /(?:https?:\/\/)?(?:www\.)?facebook\.com\/([a-zA-Z0-9.\-_]+)\/?(?:[?#].*)?$/i,
  tiktok:    /(?:https?:\/\/)?(?:www\.)?tiktok\.com\/@([a-zA-Z0-9._]{2,30})/i,
};

const SKIP_HANDLES = new Set([
  'p', 'explore', 'reel', 'reels', 'tv', 'stories', 'login', 'about',
  'pages', 'permalink', 'sharer', 'share', 'help', 'business', 'help-en',
  'privacy', 'terms', 'support',
]);

function extractSocials(html, baseUrl) {
  const $ = cheerio.load(html);
  const found = {};
  $('a[href]').each((_, el) => {
    const href = $(el).attr('href');
    if (!href) return;
    for (const [platform, re] of Object.entries(SOCIAL_PATTERNS)) {
      if (found[platform]) continue;
      const m = href.match(re);
      if (m) {
        const handle = m[1].toLowerCase();
        if (SKIP_HANDLES.has(handle)) continue;
        found[platform] = { url: href.startsWith('http') ? href : 'https://' + href.replace(/^\/\//,''), handle };
      }
    }
  });
  return found;
}

async function run() {
  // Eligible: have a website, missing at least one social platform
  const rows = await query(
    `SELECT b.id, b.slug, b.name, b.website, b.about_text,
            EXISTS (SELECT 1 FROM business_socials s WHERE s.business_id=b.id AND s.platform='instagram') AS has_ig,
            EXISTS (SELECT 1 FROM business_socials s WHERE s.business_id=b.id AND s.platform='yelp')      AS has_yelp,
            EXISTS (SELECT 1 FROM business_socials s WHERE s.business_id=b.id AND s.platform='facebook')  AS has_fb
       FROM businesses b
      WHERE b.website IS NOT NULL AND b.website <> ''
        AND NOT (
          EXISTS (SELECT 1 FROM business_socials s WHERE s.business_id=b.id AND s.platform='instagram')
          AND EXISTS (SELECT 1 FROM business_socials s WHERE s.business_id=b.id AND s.platform='yelp')
        )
      ORDER BY b.id
      ${LIMIT ? `LIMIT ${LIMIT}` : ''}`
  );
  log(`Social-link hound: ${rows.rows.length} candidates`);

  let scanned = 0, found = 0, errs = 0, totalSocials = 0;
  for (const b of rows.rows) {
    scanned++;
    if (DRY) { log(`DRY: ${b.website}`); await sleep(SLEEP_MS); continue; }
    try {
      const { html } = await fetchHtml(b.website);
      const socials = extractSocials(html, b.website);

      let added = 0;
      for (const [platform, info] of Object.entries(socials)) {
        if (b[`has_${platform === 'facebook' ? 'fb' : platform}`]) continue;
        try {
          await query(
            `INSERT INTO business_socials (business_id, platform, url, handle)
             VALUES ($1, $2, $3, $4)
             ON CONFLICT (business_id, platform) DO NOTHING`,
            [b.id, platform, info.url, info.handle]
          );
          added++; totalSocials++;
        } catch {} // ON CONFLICT might still raise on schema mismatches
      }

      // Backfill about_text from <meta description> if empty
      if (!b.about_text) {
        const $ = cheerio.load(html);
        const desc = $('meta[property="og:description"]').attr('content')
                  || $('meta[name="description"]').attr('content');
        if (desc && desc.length >= 40) {
          await query(`UPDATE businesses SET about_text = $1 WHERE id = $2 AND (about_text IS NULL OR about_text = '')`,
            [desc.slice(0, 600), b.id]);
        }
      }

      if (added > 0) found++;
      if (scanned % 25 === 0) log(`  scanned=${scanned} sites_with_finds=${found} socials_added=${totalSocials} errors=${errs}`);
    } catch (e) {
      errs++;
      if (errs % 10 === 0) log(`  ${errs} errors (last: ${e.message?.slice(0, 60)})`);
    }
    await sleep(SLEEP_MS);
  }

  log(`DONE — scanned=${scanned} hits=${found} totalSocials=${totalSocials} errors=${errs}`);
  process.exit(0);
}

run().catch(e => { console.error(e); process.exit(1); });