`scripts/enrich.mjs`

109 lines
#!/usr/bin/env node
/**
 * Gracie image enrichment — LOCAL qwen2.5vl:7b vision on Mac1 Ollama.
 * Reads gracie_catalog rows with an image_url, downloads the packshot,
 * asks qwen2.5vl for ai_colors (hex+name), color_hex (dominant), ai_styles,
 * ai_tags, and a clean 1-2 sentence description. Writes back to PG.
 * Cost: $0 (local Ollama). No paid API.
 *
 * Resumable: only enriches rows where ai_accepted_at IS NULL (or --all to redo).
 */
import { Client } from 'pg';

const OLLAMA = process.env.OLLAMA || 'http://192.168.1.133:11434';
const MODEL = 'qwen2.5vl:7b';
const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 Chrome/120 Safari/537.36';
const REDO = process.argv.includes('--all');
const LIMIT = (() => { const i = process.argv.indexOf('--limit'); return i > -1 ? parseInt(process.argv[i + 1], 10) : null; })();

// Sanity CDN lets us request a smaller render for faster vision — append ?w=768
function smallImg(url) {
  if (!url) return url;
  return url.includes('?') ? url : `${url}?w=768&q=75`;
}

async function fetchB64(url) {
  const res = await fetch(url, { headers: { 'User-Agent': UA } });
  if (!res.ok) throw new Error(`img HTTP ${res.status}`);
  const buf = Buffer.from(await res.arrayBuffer());
  return buf.toString('base64');
}

const PROMPT = `You are a luxury interior-design cataloguer looking at a hand-painted wallcovering panel image.
Return STRICT JSON only, no prose, with this exact shape:
{
 "colors": [{"name":"<color name e.g. Celadon>","hex":"#rrggbb"}, ... up to 5, ordered by prominence],
 "dominant_hex": "#rrggbb",
 "background_hex": "#rrggbb",
 "styles": ["<e.g. Chinoiserie, Scenic, Traditional>"],
 "tags": ["<motif/subject words e.g. flowering-tree, songbird, pagoda>"],
 "description": "<one or two elegant sentences describing the scene and palette>"
}
Use real color names an interior designer would use. Hex must be valid 6-digit. Do not include markdown fences.`;

function extractJson(txt) {
  if (!txt) return null;
  let s = txt.trim().replace(/^```json?/i, '').replace(/```$/,'').trim();
  const a = s.indexOf('{'), b = s.lastIndexOf('}');
  if (a === -1 || b === -1) return null;
  try { return JSON.parse(s.slice(a, b + 1)); } catch { return null; }
}

async function enrichOne(imgUrl) {
  const b64 = await fetchB64(smallImg(imgUrl));
  const res = await fetch(`${OLLAMA}/api/generate`, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({
      model: MODEL, prompt: PROMPT, images: [b64], stream: false,
      options: { temperature: 0.2 }, format: 'json',
    }),
  });
  if (!res.ok) throw new Error(`ollama HTTP ${res.status}`);
  const j = await res.json();
  return extractJson(j.response);
}

async function main() {
  const pg = new Client({ connectionString: process.env.PG || 'postgresql://localhost/dw_unified' });
  await pg.connect();
  const where = REDO ? 'image_url IS NOT NULL' : 'image_url IS NOT NULL AND ai_accepted_at IS NULL';
  const { rows } = await pg.query(
    `SELECT id, mfr_sku, image_url FROM gracie_catalog WHERE ${where} ORDER BY id ${LIMIT ? `LIMIT ${LIMIT}` : ''}`
  );
  console.log(`[enrich] ${rows.length} rows to enrich via ${MODEL} @ ${OLLAMA}. Cost: $0 (local)`);
  let ok = 0, fail = 0;
  for (const r of rows) {
    try {
      const e = await enrichOne(r.image_url);
      if (!e || !Array.isArray(e.colors)) { fail++; console.warn(`  ✗ ${r.mfr_sku}: no JSON`); continue; }
      const hex = (e.dominant_hex && /^#[0-9a-fA-F]{6}$/.test(e.dominant_hex)) ? e.dominant_hex.toLowerCase() : null;
      await pg.query(
        `UPDATE gracie_catalog SET
           ai_colors=$2, color_hex=COALESCE($3,color_hex), dominant_color_hex=COALESCE($3,dominant_color_hex),
           ai_background_color=$4, ai_styles=$5, ai_tags=COALESCE($6, ai_tags),
           ai_description=$7, ai_accepted_at=NOW(), agent_updated_at=NOW()
         WHERE id=$1`,
        [
          r.id,
          JSON.stringify(e.colors || []),
          hex,
          (e.background_hex && /^#[0-9a-fA-F]{6}$/.test(e.background_hex)) ? e.background_hex.toLowerCase() : null,
          JSON.stringify(e.styles || []),
          Array.isArray(e.tags) && e.tags.length ? JSON.stringify(e.tags) : null,
          e.description || null,
        ]
      );
      ok++;
      if (ok % 10 === 0) console.log(`  … ${ok}/${rows.length}`);
    } catch (err) {
      fail++;
      console.warn(`  ✗ ${r.mfr_sku}: ${err.message}`);
    }
  }
  console.log(`[enrich] DONE. ok=${ok} fail=${fail}. Cost: $0 (local)`);
  await pg.end();
}

main().catch((e) => { console.error('[enrich] FATAL', e); process.exit(1); });