`scripts/rotate-headline-variants.js`

117 lines
#!/usr/bin/env node
/**
 * rotate-headline-variants.js
 *
 * Diversify the 1,819 templated headlines using `id % 3` deterministic
 * variant selection. Each (type, city) combo gets 3 phrasings instead of 1:
 *
 *   variant 0:  "Hair salon in Sherman Oaks."        (current)
 *   variant 1:  "Sherman Oaks hair salon."           (city first)
 *   variant 2:  "Hair salon · Sherman Oaks corridor." (mid-dot, corridor cue)
 *
 * Triples distinct headlines: 170 → ~510. Avg rows-per-phrasing drops from
 * ~11 to ~4. Same template flavor; same SEO targets; less visible repetition
 * on the find grid.
 *
 * Idempotent — the variant is fully determined by `(template, city, id % 3)`,
 * so re-running produces the same output. Only edits headlines that still
 * end with ' in <city>.' (matches the post-headline-rewrite shape from
 * commit 5a49679 — won't touch owner-customized headlines).
 */

require('dotenv').config({ path: require('path').resolve(__dirname, '..', '.env') });
const db = require('../lib/db');

// Convert "Hair salon" / "Sit-down restaurant" / "Sushi restaurant" into
// the three canonical variants. Lowercase the type when it's mid-sentence
// so "Sherman Oaks hair salon" reads naturally.
function variants(type, city) {
  const tLower = type.charAt(0).toLowerCase() + type.slice(1);
  return [
    `${type} in ${city}.`,                 // 0: original
    `${city} ${tLower}.`,                  // 1: city-first
    `${type} · ${city} corridor.`          // 2: mid-dot variant
  ];
}

(async () => {
  // Pull every public_records row with an end-of-headline " in <city>." shape
  // — that's the rewritten template format from commit 5a49679. Owners who've
  // claimed and customized their headline write something different, so they
  // won't match this regex and will be left alone.
  const rows = await db.many(`
    SELECT id, slug, headline, neighborhood, city
      FROM businesses
     WHERE source='public_records'
       AND status IN ('active','hidden')
       AND headline ~ ' in [A-Za-z][A-Za-z ]+\\.$'
  `);
  console.log(`[rotate] candidate rows: ${rows.length}`);

  let updated = 0, skipped = 0;
  // Batch updates in a transaction — 1,819 rows shouldn't be 1,819 round
  // trips. (PROSECUTOR caught this pattern in an earlier debate.)
  const client = await db.pool.connect();
  try {
    await client.query('BEGIN');

    for (const r of rows) {
      // Parse "<Type> in <City>." → [type, city]
      const m = r.headline.match(/^(.+?) in ([A-Za-z][A-Za-z ]+)\.$/);
      if (!m) { skipped++; continue; }
      const type = m[1];
      const city = m[2];

      // City must match the row's neighborhood/city (sanity check).
      const expected = (r.neighborhood || r.city || '').trim();
      if (expected && expected.toLowerCase() !== city.toLowerCase()) {
        skipped++; continue;
      }

      const variantIdx = Number(r.id) % 3;
      const newHeadline = variants(type, expected || city)[variantIdx];

      if (newHeadline === r.headline) { skipped++; continue; }

      // Re-stitch description. Keep the prefix sentence intact, swap the
      // trailing headline echo to match the new headline.
      const verticalPrefixRe = new RegExp(`^(.+? is a [a-z _]+ business on the Ventura Blvd corridor in ${expected || city}\\. ).+$`);
      // Update the headline; let the description regenerate via raw SQL
      // concatenation instead of regex (faster + less error-prone).
      await client.query(
        `UPDATE businesses
            SET headline = $1,
                description = (
                  business_name || ' is a ' || REPLACE(vertical, '_', ' ')
                  || ' business on the Ventura Blvd corridor in ' || COALESCE(neighborhood, city)
                  || '. ' || $1
                )
          WHERE id = $2`,
        [newHeadline, r.id]
      );
      updated++;
    }

    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }

  // Tally distinct headlines now
  const distinct = await db.one(`
    SELECT COUNT(DISTINCT headline)::int AS n
      FROM businesses WHERE source='public_records' AND status='active'
  `);
  console.log(`[rotate] updated=${updated} skipped=${skipped} distinct_headlines_now=${distinct.n}`);

  await db.pool.end();
  process.exit(0);
})().catch(async (err) => {
  console.error('[rotate] fatal', err);
  try { await db.pool.end(); } catch (_) {}
  process.exit(1);
});