`scripts/audit-public-sites.mjs`

263 lines
// Real site audit — fetches every website_analyses row that has a public URL,
// parses with cheerio, extracts SEO/quality signals, and writes the report
// into summary_json.live_audit. Adds REAL HTML-derived signal to the audit
// grade (beyond the synthesis-only rubric in audit-grade.js).
//
//   USAGE:
//     node scripts/audit-public-sites.mjs                     # all public URLs
//     node scripts/audit-public-sites.mjs --slug <slug>       # one
//     node scripts/audit-public-sites.mjs --rebuild           # re-audit even
//                                                             # those with live_audit

import 'dotenv/config';
import * as cheerio from 'cheerio';
import { many, query } from '../src/lib/db.js';

const args = process.argv.slice(2);
const REBUILD = args.includes('--rebuild');
const SLUG = (() => { const i = args.indexOf('--slug'); return i >= 0 ? args[i + 1] : null; })();

const TIMEOUT_MS = 15_000;
const UA = 'SmallBusinessBuilderAuditBot/1.0 (+https://builds.agentabrams.com)';

function isPublicUrl(u) {
  try {
    const p = new URL(u);
    if (p.protocol !== 'http:' && p.protocol !== 'https:') return false;
    const h = p.hostname;
    if (/^(127\.|10\.|192\.168\.|172\.(1[6-9]|2\d|3[01])\.|100\.)/.test(h)) return false;
    if (h === 'localhost' || h.endsWith('.local')) return false;
    return true;
  } catch { return false; }
}

async function fetchHtml(url) {
  const res = await fetch(url, {
    redirect: 'follow',
    signal: AbortSignal.timeout(TIMEOUT_MS),
    headers: { 'User-Agent': UA, 'Accept': 'text/html,application/xhtml+xml,*/*;q=0.8' },
  });
  // Re-validate post-redirect target hasn't slipped into private space.
  if (!isPublicUrl(res.url)) throw new Error(`redirected to non-public ${res.url}`);
  if (!res.ok) throw new Error(`HTTP ${res.status}`);
  const ct = res.headers.get('content-type') || '';
  if (!ct.includes('html')) throw new Error(`non-html content-type ${ct}`);
  const buf = await res.arrayBuffer();
  if (buf.byteLength > 5_000_000) throw new Error(`html too large (${buf.byteLength} bytes)`);
  return { html: new TextDecoder().decode(buf), bytes: buf.byteLength, finalUrl: res.url, status: res.status };
}

function parse(html, finalUrl) {
  const $ = cheerio.load(html);
  const u = new URL(finalUrl);

  const title = ($('title').first().text() || '').trim();
  const metaDesc = ($('meta[name="description"]').attr('content') || '').trim();
  const ogImage = ($('meta[property="og:image"]').attr('content') || '').trim();
  const ogTitle = ($('meta[property="og:title"]').attr('content') || '').trim();
  const ogDesc = ($('meta[property="og:description"]').attr('content') || '').trim();
  const twitterCard = ($('meta[name="twitter:card"]').attr('content') || '').trim();
  const viewport = ($('meta[name="viewport"]').attr('content') || '').trim();
  const charset = ($('meta[charset]').attr('charset') || '').trim();
  const lang = ($('html').attr('lang') || '').trim();
  const canonical = ($('link[rel="canonical"]').attr('href') || '').trim();
  const favicon = $('link[rel="icon"], link[rel="shortcut icon"], link[rel="apple-touch-icon"]').length > 0;
  const robots = ($('meta[name="robots"]').attr('content') || '').trim();

  const h1s = $('h1').map((_, el) => $(el).text().trim()).get().filter(Boolean);
  const h2Count = $('h2').length;
  const h3Count = $('h3').length;

  const imgs = $('img').toArray();
  const imgsWithAlt = imgs.filter(el => ($(el).attr('alt') || '').trim().length > 0).length;
  const imgCount = imgs.length;

  const links = $('a[href]').toArray();
  let internal = 0, external = 0;
  for (const el of links) {
    const href = $(el).attr('href') || '';
    if (href.startsWith('#') || href.startsWith('javascript:') || href.startsWith('mailto:') || href.startsWith('tel:')) continue;
    try {
      const abs = new URL(href, finalUrl);
      if (abs.host === u.host) internal += 1; else external += 1;
    } catch {}
  }

  const jsonLd = $('script[type="application/ld+json"]').map((_, el) => {
    try { return JSON.parse($(el).contents().text()); } catch { return null; }
  }).get().filter(Boolean);
  const jsonLdTypes = [...new Set(jsonLd.flatMap(o => Array.isArray(o['@graph']) ? o['@graph'].map(g => g['@type']) : [o['@type']]).filter(Boolean))];

  return {
    title, title_len: title.length,
    meta_description: metaDesc, meta_description_len: metaDesc.length,
    og_image: ogImage || null,
    og_title: ogTitle || null,
    og_description: ogDesc || null,
    twitter_card: twitterCard || null,
    viewport: viewport || null,
    charset: charset || null,
    lang: lang || null,
    canonical: canonical || null,
    favicon,
    robots: robots || null,
    h1_count: h1s.length,
    h1_first: h1s[0] || null,
    h2_count: h2Count,
    h3_count: h3Count,
    img_count: imgCount,
    img_with_alt: imgsWithAlt,
    img_alt_pct: imgCount > 0 ? Math.round((imgsWithAlt / imgCount) * 100) : null,
    links_internal: internal,
    links_external: external,
    jsonld_types: jsonLdTypes,
    is_https: u.protocol === 'https:',
  };
}

function scoreReport(rep) {
  let score = 0;
  const reasons = [];
  const wins = [];
  const issues = [];

  if (rep.title_len >= 30 && rep.title_len <= 60) { score += 10; wins.push('title length 30-60 chars'); }
  else if (rep.title_len > 0) { score += 4; issues.push(`title ${rep.title_len} chars (target 30-60)`); }
  else { issues.push('no <title> tag'); }

  if (rep.meta_description_len >= 70 && rep.meta_description_len <= 160) { score += 10; wins.push('meta description 70-160 chars'); }
  else if (rep.meta_description_len > 0) { score += 4; issues.push(`meta description ${rep.meta_description_len} chars (target 70-160)`); }
  else { issues.push('no meta description'); }

  if (rep.og_image) { score += 8; wins.push('og:image set'); } else { issues.push('no og:image — bad social previews'); }
  if (rep.og_title || rep.og_description) { score += 5; wins.push('og:title/description set'); }
  if (rep.twitter_card) { score += 3; wins.push('twitter:card set'); }
  if (rep.favicon) { score += 5; wins.push('favicon present'); } else { issues.push('no favicon'); }
  if (rep.viewport) { score += 10; wins.push('mobile viewport meta'); } else { issues.push('no viewport meta — broken mobile'); }
  if (rep.charset) { score += 3; wins.push('charset declared'); }
  if (rep.lang) { score += 5; wins.push(`html lang="${rep.lang}"`); } else { issues.push('no html lang attribute'); }
  if (rep.canonical) { score += 5; wins.push('canonical link set'); }
  if (rep.h1_count === 1) { score += 10; wins.push('exactly one <h1>'); }
  else if (rep.h1_count === 0) { issues.push('no <h1> tag'); }
  else { score += 3; issues.push(`${rep.h1_count} <h1> tags (one is best)`); }

  if (rep.img_count === 0) { /* no penalty — text-only is fine */ }
  else if (rep.img_alt_pct >= 80) { score += 10; wins.push(`${rep.img_alt_pct}% of images have alt text`); }
  else { issues.push(`only ${rep.img_alt_pct}% of ${rep.img_count} images have alt text`); }

  if (rep.jsonld_types.length > 0) { score += 8; wins.push(`structured data (${rep.jsonld_types.join(', ')})`); }
  else { issues.push('no JSON-LD structured data'); }

  if (rep.is_https) { score += 5; wins.push('HTTPS'); } else { issues.push('not on HTTPS'); }

  if (score > 100) score = 100;
  const letter = score >= 80 ? 'A' : score >= 65 ? 'B' : score >= 45 ? 'C' : score >= 25 ? 'D' : 'F';
  return { score, letter, wins, issues, reasons };
}

async function auditOne(row) {
  const t0 = Date.now();
  const fetched = await fetchHtml(row.url);
  const rep = parse(fetched.html, fetched.finalUrl);
  const grade = scoreReport(rep);
  const audit = {
    url: row.url,
    final_url: fetched.finalUrl,
    fetched_at: new Date().toISOString(),
    http_status: fetched.status,
    bytes: fetched.bytes,
    fetch_ms: Date.now() - t0,
    signals: rep,
    grade,
    top_3_issues: grade.issues.slice(0, 3),
    top_3_wins:   grade.wins.slice(0, 3),
  };

  // Audit history: keep the last 4 snapshots (slim — just score + issues count
  // + fetched_at) so we can plot a sparkline + flag regressions.
  const prevAudit = row.summary_json?.live_audit;
  const prevHistory = Array.isArray(row.summary_json?.live_audit_history) ? row.summary_json.live_audit_history : [];
  const newHistory = prevAudit ? [
    {
      score:       prevAudit.grade?.score,
      letter:      prevAudit.grade?.letter,
      fetched_at:  prevAudit.fetched_at,
      issues:      prevAudit.top_3_issues?.length ?? 0,
      bytes:       prevAudit.bytes,
    },
    ...prevHistory,
  ].slice(0, 4) : prevHistory;

  // Flag regression if score dropped > 10 points or letter dropped (e.g. B → C).
  const prevScore = prevAudit?.grade?.score;
  const regression = (Number.isFinite(prevScore) && (prevScore - grade.score) > 10)
    ? { from: prevScore, to: grade.score, delta: grade.score - prevScore, detected_at: new Date().toISOString() }
    : null;

  // Use jsonb || merge instead of read-modify-write so concurrent runs on the
  // same slug don't silently overwrite each other's live_audit / copy_pack.
  await query("UPDATE website_analyses SET summary_json = COALESCE(summary_json, '{}'::jsonb) || $1::jsonb, updated_at=NOW() WHERE id=$2",
    [JSON.stringify({
      live_audit: audit,
      live_audit_history: newHistory,
      ...(regression ? { live_audit_regression: regression } : {}),
    }), row.id]);
  return {
    slug: row.slug, status: 'ok',
    score: grade.score, letter: grade.letter, ms: audit.fetch_ms,
    issues: grade.issues.length, wins: grade.wins.length,
    delta: Number.isFinite(prevScore) ? grade.score - prevScore : null,
    regression: !!regression,
  };
}

async function main() {
  let rows;
  if (SLUG) {
    rows = await many('SELECT id, slug, url, summary_json FROM website_analyses WHERE slug = $1', [SLUG]);
  } else {
    const baseWhere = `WHERE url ~ '^https?://' AND url NOT LIKE '%.local%' AND url NOT LIKE 'local://%' AND url NOT LIKE 'http://100.%' AND url NOT LIKE 'http://192.168.%'`;
    const skipExisting = REBUILD ? '' : ` AND NOT (summary_json ? 'live_audit')`;
    rows = await many(`SELECT id, slug, url, summary_json FROM website_analyses ${baseWhere}${skipExisting} ORDER BY id`);
  }
  const filtered = rows.filter(r => isPublicUrl(r.url));
  console.log(`Auditing ${filtered.length} public URLs (skipped ${rows.length - filtered.length} private)`);
  let ok = 0, err = 0;
  const regressions = [];
  for (const row of filtered) {
    try {
      const r = await auditOne(row);
      ok += 1;
      const deltaStr = r.delta != null ? (r.delta > 0 ? `+${r.delta}` : `${r.delta}`).padStart(4) : '   ·';
      console.log(`  [${ok}/${filtered.length}] ${r.letter} ${String(r.score).padStart(3)}${r.regression ? ' ⚠' : '  '} ${deltaStr}  ${r.slug.padEnd(28)}  ${r.ms}ms  ✓${r.wins} ✗${r.issues}`);
      if (r.regression) regressions.push({ slug: r.slug, score: r.score, delta: r.delta });
    } catch (e) {
      err += 1;
      console.error(`  ERR  ${row.slug}  ${e.message}`);
    }
  }
  console.log(`\nDONE — ok:${ok} err:${err}${regressions.length ? `  ⚠ ${regressions.length} REGRESSIONS` : ''}`);

  // Email digest via George if any site regressed > 10 points.
  if (regressions.length) {
    try {
      const body = `Live audit run finished with ${regressions.length} regression${regressions.length === 1 ? '' : 's'}:\n\n` +
        regressions.map(r => `  ${r.slug}: now ${r.score} (${r.delta > 0 ? '+' : ''}${r.delta})`).join('\n') +
        `\n\nFull details: https://livesiteaudit.agentabrams.com/wall (login to /builds for admin view)`;
      await fetch('http://localhost:9850/api/send', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({
          to: 'steveabramsdesigns@gmail.com',
          subject: `[livesiteaudit] ${regressions.length} site${regressions.length === 1 ? '' : 's'} regressed`,
          body,
        }),
      });
      console.log(`Alerted Steve via George (${regressions.length} regressions)`);
    } catch (e) { console.error('George alert failed:', e.message); }
  }
  process.exit(0);
}

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