`scripts/enrich-instagram.js`

151 lines
#!/usr/bin/env node
// IG enrichment for unclaimed installers.
//
// Per DATA_POLICY.md §3 — IG handle ONLY if it appears as a clickable link on
// the studio's own public website. We do NOT touch instagram.com directly.
//
// For each unclaimed installer with a website and no IG handle yet:
//   1. Skip if website's domain is in directory_optout
//   2. fetch() the homepage with our identifying UA
//   3. Look for instagram.com/<handle> patterns in the HTML
//   4. Filter out non-profile paths (p, reel, tv, explore, etc.)
//   5. UPDATE installer with first valid handle
//   6. 3-second delay between requests
//
// Usage:
//   node scripts/enrich-instagram.js                # dry-run
//   node scripts/enrich-instagram.js --commit       # write IG handles
//   node scripts/enrich-instagram.js --max=100      # cap (default 50)

require('dotenv').config();
const db = require('../lib/db');

const UA = 'NationalPaperHangersBot/1.0 (+https://nationalpaperhangers.com/about)';
const SOURCE = 'studio_site_ig';
const REQUEST_DELAY_MS = 3000;
const COMMIT = process.argv.includes('--commit');
const MAX = parseInt((process.argv.find(a => a.startsWith('--max=')) || '--max=50').split('=')[1], 10);

// Reserved Instagram paths that aren't profile handles
const NON_HANDLE_PATHS = new Set(['p', 'reel', 'reels', 'tv', 'explore', 'about', 'developer', 'directory', 'accounts', 'web']);
// Site-builder / platform / generic handles that show up in footers but aren't the studio
const PLATFORM_HANDLES = new Set([
  'squarespace', 'wix', 'wixcom', 'godaddy', 'wordpress', 'weebly', 'shopify',
  'bigcommerce', 'webflow', 'duda', 'hostgator', 'wpengine',
  'meta', 'instagram', 'facebook', 'whatsapp',
  'google', 'youtube', 'twitter', 'tiktok', 'linkedin', 'pinterest',
  'mailchimp', 'constantcontact'
]);

function sleep(ms) { return new Promise(r => setTimeout(r, ms)); }

async function logRequest(url, status, bytes, durMs, error) {
  await db.query(
    `INSERT INTO scrape_log (source, url, http_status, bytes, duration_ms, error)
     VALUES ($1,$2,$3,$4,$5,$6)`,
    [SOURCE, url, status, bytes, durMs, error]
  );
}

async function loadOptOutSet() {
  const rows = await db.many(`SELECT domain FROM directory_optout`);
  return new Set(rows.map(r => (r.domain || '').toLowerCase()));
}

function extractIG(html) {
  if (!html) return null;
  // Match instagram.com/<handle> where handle is letters/digits/dot/underscore
  // Stop at next quote, slash, ?, #, or whitespace
  const re = /(?:https?:)?\/\/(?:www\.)?instagram\.com\/([A-Za-z0-9._]{1,30})(?=["'\/?#\s>])/g;
  let m;
  const handles = new Set();
  while ((m = re.exec(html)) !== null) {
    const h = m[1];
    if (h.length < 2) continue;
    const hl = h.toLowerCase();
    if (NON_HANDLE_PATHS.has(hl)) continue;
    if (PLATFORM_HANDLES.has(hl)) continue;
    handles.add(h);
  }
  // Prefer the first non-generic handle
  for (const h of handles) return h;
  return null;
}

async function main() {
  console.log(`[ig-enrich] start · commit=${COMMIT} · max=${MAX}`);

  const targets = await db.many(
    `SELECT id, slug, business_name, website
       FROM installers
      WHERE claim_status='unclaimed'
        AND website IS NOT NULL
        AND website <> ''
        AND instagram_handle IS NULL
      ORDER BY id
      LIMIT $1`,
    [MAX]
  );

  console.log(`[ig-enrich] ${targets.length} candidates`);

  // Preload all opt-out domains once — avoids an N+1 query per installer.
  const optOutSet = await loadOptOutSet();

  let found = 0, missed = 0, skipped = 0, failed = 0;
  for (const t of targets) {
    let host;
    try { host = new URL(t.website).hostname.replace(/^www\./, ''); } catch {
      console.log(`  · ${t.business_name}: bad URL — skip`);
      skipped++;
      continue;
    }
    if (optOutSet.has(host.toLowerCase())) { console.log(`  · ${t.business_name}: opted out`); skipped++; continue; }

    const t0 = Date.now();
    let status = 0, html = null, bytes = 0, err = null;
    try {
      const r = await fetch(t.website, {
        headers: { 'user-agent': UA, accept: 'text/html' },
        signal: AbortSignal.timeout(15000),
        redirect: 'follow'
      });
      status = r.status;
      if (r.ok) html = await r.text();
      bytes = html ? html.length : 0;
    } catch (e) {
      err = e.message;
    }
    await logRequest(t.website, status, bytes, Date.now() - t0, err);

    if (!html) {
      console.log(`  ✗ ${t.business_name} (${host}): ${status} ${err || ''}`);
      failed++;
      await sleep(REQUEST_DELAY_MS);
      continue;
    }

    const handle = extractIG(html);
    if (!handle) {
      console.log(`  · ${t.business_name} (${host}): no IG`);
      missed++;
    } else {
      console.log(`  ✓ ${t.business_name} (${host}) → @${handle}`);
      found++;
      if (COMMIT) {
        await db.query('UPDATE installers SET instagram_handle=$2 WHERE id=$1', [t.id, handle]);
      }
    }

    await sleep(REQUEST_DELAY_MS);
  }

  console.log(`[ig-enrich] done · found=${found} missed=${missed} skipped=${skipped} failed=${failed}`);
  if (failed / Math.max(1, targets.length) > 0.10) {
    console.warn(`[ig-enrich] FAILURE-RATE-WARNING: failure rate exceeded 10%`);
  }
  await db.pool.end();
}

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