`scripts/backfill-palette.js`

123 lines
#!/usr/bin/env node
/**
 * Gemini palette backfill for shopify_products without enrichment.
 *
 * Pulls ACTIVE+image products missing shopify_color_enrichment rows,
 * calls Gemini 2.0 Flash on the image, extracts dominant_hex + hex_codes,
 * inserts into shopify_color_enrichment (status='backfill').
 *
 * Rate-limited to 20 rpm to stay well under Gemini free tier limits.
 *
 * Usage:
 *   GEMINI_API_KEY=xxx DATABASE_URL=postgres://... node scripts/backfill-palette.js [--limit 100] [--dry-run]
 */
require('dotenv').config();
const { Pool } = require('pg');

const LIMIT = parseInt(process.argv.find(a => a.startsWith('--limit='))?.split('=')[1] || '5500', 10);
const DRY = process.argv.includes('--dry-run');
const SLEEP_MS = parseInt(process.env.SLEEP_MS || '3500', 10);   // ~17 rpm
const GEMINI_KEY = process.env.GEMINI_API_KEY;
const MODEL = process.env.GEMINI_MODEL || 'gemini-2.0-flash';

if (!GEMINI_KEY && !DRY) {
  console.error('FATAL: GEMINI_API_KEY missing. Set it or run --dry-run');
  process.exit(1);
}
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const PROMPT = `Look at this wallcovering pattern image. Return ONLY a JSON object with two fields:
{
  "dominant_hex": "#RRGGBB",
  "hex_codes": ["#RRGGBB", "#RRGGBB", "#RRGGBB", "#RRGGBB", "#RRGGBB"]
}
- dominant_hex: the single most prevalent color
- hex_codes: 3-6 colors representing the full palette, ordered by visual prominence
Return JSON ONLY, no markdown, no commentary.`;

async function fetchTargets(limit) {
  const r = await pool.query(`
    SELECT sp.shopify_id, sp.dw_sku, sp.handle, sp.title, sp.vendor, sp.image_url
    FROM shopify_products sp
    LEFT JOIN shopify_color_enrichment sce ON sce.shopify_id = sp.shopify_id
    WHERE sp.status='ACTIVE'
      AND sp.image_url IS NOT NULL
      AND sce.shopify_id IS NULL
    ORDER BY sp.synced_at DESC NULLS LAST
    LIMIT $1
  `, [limit]);
  return r.rows;
}

async function geminiPalette(imageUrl) {
  // Download the image as base64
  const imgRes = await fetch(imageUrl);
  if (!imgRes.ok) throw new Error(`image fetch ${imgRes.status}`);
  const buf = Buffer.from(await imgRes.arrayBuffer());
  const mime = imgRes.headers.get('content-type') || 'image/jpeg';
  const b64 = buf.toString('base64');

  const body = {
    contents: [{
      parts: [
        { text: PROMPT },
        { inline_data: { mime_type: mime, data: b64 } }
      ]
    }],
    generationConfig: { temperature: 0, response_mime_type: 'application/json' }
  };
  const url = `https://generativelanguage.googleapis.com/v1beta/models/${MODEL}:generateContent?key=${GEMINI_KEY}`;
  const r = await fetch(url, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify(body)
  });
  if (!r.ok) throw new Error(`gemini ${r.status} ${await r.text()}`);
  const j = await r.json();
  const text = j.candidates?.[0]?.content?.parts?.[0]?.text || '';
  const parsed = JSON.parse(text);
  return parsed;
}

async function insert(row, palette) {
  if (DRY) {
    console.log(`  [DRY] ${row.dw_sku} → ${palette.dominant_hex} + ${(palette.hex_codes||[]).length} codes`);
    return;
  }
  await pool.query(`
    INSERT INTO shopify_color_enrichment
      (shopify_id, handle, title, vendor, image_url, dominant_hex, hex_codes, status, gemini_model, enrichment_date)
    VALUES ($1,$2,$3,$4,$5,$6,$7::jsonb,'backfill',$8,now())
    ON CONFLICT (shopify_id) DO UPDATE SET
      dominant_hex = EXCLUDED.dominant_hex,
      hex_codes    = EXCLUDED.hex_codes,
      status       = 'backfill',
      enrichment_date = now()
  `, [row.shopify_id, row.handle, row.title, row.vendor, row.image_url,
      palette.dominant_hex, JSON.stringify(palette.hex_codes || []), MODEL]);
}

(async () => {
  console.log(`backfill-palette: target ${LIMIT} products  model=${MODEL}  sleep=${SLEEP_MS}ms`);
  const rows = await fetchTargets(LIMIT);
  console.log(`fetched ${rows.length} candidates`);
  let ok=0, err=0, skip=0;
  for (let i=0; i<rows.length; i++) {
    const row = rows[i];
    if (!row.image_url) { skip++; continue; }
    try {
      const pal = await geminiPalette(row.image_url);
      if (!pal?.dominant_hex) throw new Error('no dominant_hex');
      await insert(row, pal);
      ok++;
      if (ok % 25 === 0) console.log(`  ${i+1}/${rows.length}  ok=${ok}  err=${err}  ${row.dw_sku} → ${pal.dominant_hex}`);
    } catch (e) {
      err++;
      console.error(`  ${row.dw_sku} FAIL: ${e.message}`);
    }
    await new Promise(r => setTimeout(r, SLEEP_MS));
  }
  console.log(`DONE  ok=${ok}  err=${err}  skip=${skip}  total=${rows.length}`);
  await pool.end();
})().catch(e => { console.error('FATAL', e); process.exit(1); });