`scripts/fill-tier-copy.cjs`

82 lines
#!/usr/bin/env node
// fill-tier-copy.js — backfill marketing_copy JSONB on tier_pricing rows
// where it's NULL. Uses local qwen3:14b. Per-row generation, idempotent
// (only fills NULL — re-run safe).

const { execSync } = require('child_process');
const DB = process.env.DB_NAME || 'ventura_corridor';
const PSQL = (process.platform === 'linux') ? `sudo -n -u postgres psql ${DB} -At -F"|" -q` : `psql ${DB} -At -F"|" -q`;
function psql(sql) { return execSync(PSQL, { input: sql, encoding: 'utf8', maxBuffer: 16 * 1024 * 1024 }).trim(); }

async function qwen3(sysPrompt, userPrompt) {
  const r = await fetch('http://127.0.0.1:11434/api/chat', {
    method: 'POST', headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({
      model: 'qwen3:14b',
      stream: false, think: false, format: 'json',
      options: { temperature: 0.65, num_predict: 400 },
      messages: [
        { role: 'system', content: '/no_think\n' + sysPrompt },
        { role: 'user', content: userPrompt },
      ],
    }),
  });
  if (!r.ok) throw new Error('ollama http ' + r.status);
  const j = await r.json();
  const raw = (j.message?.content || '').replace(/<think>[\s\S]*?<\/think>/g, '').trim();
  try { return JSON.parse(raw); }
  catch { const m = raw.match(/\{[\s\S]*\}/); return m ? JSON.parse(m[0]) : null; }
}

const SYS = `You are a copywriter for Ventura Corridor, a directory of businesses on Ventura Boulevard. You write tier marketing copy for business owners deciding whether to upgrade their listing. Tone: confident, friendly, sales-aware, no buzzwords, no AI talk.

Output STRICT JSON only:
{
  "title": "<5-9 words — tier display name + price>",
  "subtitle": "<one sentence — the value prop in plain English>",
  "bullets": ["<concise feature 1>","<feature 2>","<feature 3>","<feature 4>","<feature 5>"],
  "cta": "<2-4 word call to action — verb-led>"
}

Bullets must be 3-6 items. Each bullet is a real feature, not a vibe. Mention specific things (logo, photos, video, booking, badge, priority sort, lead form, weekly digest, reservations link, etc.) appropriate to the tier level and the business category.`;

const TIER_HINTS = {
  basic:    { intent: 'free baseline — what every listing gets', features: 'OSM-sourced name + address + phone + hours; standard sort; no logo' },
  featured: { intent: 'mid-tier — visual upgrade + boost', features: 'logo + photos + boosted in search/browse + a badge + category tags' },
  verified: { intent: 'mid-tier — credentials + intake', features: 'verification badge + credentials displayed + lead/intake form + headshot' },
  spotlight:{ intent: 'top-tier — video + priority + niche extras', features: 'video embed + reservations/booking link + priority placement + weekly digest + promo slot' },
};

async function main() {
  const rows = psql(`SELECT category||'|'||tier||'|'||display_name||'|'||price_cents FROM tier_pricing WHERE marketing_copy IS NULL ORDER BY category, price_cents`).split('\n').filter(Boolean);
  console.log(`📝 Filling marketing_copy for ${rows.length} tier rows · qwen3:14b`);
  let ok = 0, fail = 0;
  const t0 = Date.now();
  for (const line of rows) {
    const [category, tier, display, priceCents] = line.split('|');
    const price = parseInt(priceCents, 10);
    const hint = TIER_HINTS[tier] || TIER_HINTS.featured;
    const user = `Category: ${category} (OSM-style)
Tier: ${tier}
Display name: ${display}
Price: ${price === 0 ? 'FREE' : '$' + (price/100).toFixed(0) + '/month'}
Tier role: ${hint.intent}
Typical features at this tier: ${hint.features}

Generate JSON.`;
    try {
      const copy = await qwen3(SYS, user);
      if (!copy || !copy.bullets || !Array.isArray(copy.bullets)) { fail++; process.stdout.write('✗'); continue; }
      const json = JSON.stringify(copy).replace(/'/g, "''");
      psql(`UPDATE tier_pricing SET marketing_copy = '${json}'::jsonb WHERE category = '${category.replace(/'/g, "''")}' AND tier = '${tier}'`);
      ok++;
      process.stdout.write('.');
    } catch (e) {
      fail++; process.stdout.write('✗');
      console.error(`\n  · err ${category}/${tier}:`, e.message);
    }
  }
  console.log(`\nDone: ${ok} filled · ${fail} failed · ${((Date.now()-t0)/1000).toFixed(1)}s`);
}
main().catch(e => { console.error(e); process.exit(1); });