`scripts/build_manifest.mjs`

139 lines
// build_manifest.mjs — SDG go-live: enrich → assign dw_sku → 5-field/6-gate → settlement-scope tag → emit manifest.
// $0: descriptions/tags via LOCAL Ollama (mac1 qwen2.5:7b). NEVER fabricates price/color/dimension.
// Reads the 4 *_feed_pricing tables; writes pilot/manifest-<brand>.json + a HELD report.
// Idempotent dw_sku assignment: numbering continues from the max already on Shopify per prefix.
//
// Gate fields (must all pass to be BUILD-eligible; else HELD with reason):
//   1 sellable variant (implicit)  2 sample variant (implicit)  3 complete price (retail_usd>0)
//   4 description (enriched)        5 >=2 tags                   6 featured image (feed image -> absolute URL)
// color falls back to pattern label when blank (Morris); width/length omitted-not-fabricated when absent.
import fs from 'node:fs';
import { execSync } from 'node:child_process';

const OLLAMA = process.env.OLLAMA_URL || 'http://192.168.1.133:11434';
const MODEL = 'qwen2.5:7b';
const IMG_BASE = 'https://www.sanderson.design/static/media/catalog';
const PILOT = new URL('../pilot/', import.meta.url).pathname;

// brand -> { table, vendor (customer-facing), prefix (registered), startNum }
const BRANDS = {
  sanderson: { table: 'sanderson_feed_pricing', vendor: 'Sanderson',      prefix: 'DWXH', start: 600000 },
  harlequin: { table: 'harlequin_feed_pricing', vendor: 'Harlequin',      prefix: 'DWHF', start: 70608 },
  zoffany:   { table: 'zoffany_feed_pricing',   vendor: 'Zoffany',        prefix: 'DWZF', start: 600000 },
  morris:    { table: 'morris_feed_pricing',    vendor: 'Morris & Co.',   prefix: 'DWWM', start: 600000 },
};

function psql(sql) {
  return execSync(`psql -h /tmp -d dw_unified -tA -F $'\\t' -c "${sql.replace(/"/g, '\\"')}"`, { encoding: 'utf8', maxBuffer: 1 << 28 });
}

// absolute image URL from feed relative path (or empty)
const absImg = (rel) => {
  const s = String(rel || '').trim();
  if (!s) return '';
  if (/^https?:\/\//i.test(s)) return s;
  return IMG_BASE + (s.startsWith('/') ? s : '/' + s);
};

// derive tags: product_type, collection, brand, pattern (all real feed values — never AI)
function deriveTags(r, vendor) {
  const t = new Set();
  if (r.product_type) t.add(r.product_type === 'fabric' ? 'Fabric' : 'Wallcovering');
  t.add(vendor);
  if (r.collection) t.add(r.collection.slice(0, 60));
  if (r.pattern) t.add(r.pattern.split('/')[0].trim().slice(0, 40));
  if (r.color) t.add(r.color.split('/')[0].trim().slice(0, 30));
  return [...t].filter(Boolean);
}

async function ollamaDesc(r, vendor, timeoutMs = 20000) {
  const isFabric = r.product_type === 'fabric';
  const kind = isFabric ? 'fabric' : 'wallcovering';
  const unit = isFabric ? 'yard' : 'roll';
  const nounRule = isFabric
    ? `This product is a FABRIC — call it "fabric" or "textile". Do NOT call it a wallcovering or wallpaper.`
    : `This product is a WALLCOVERING — call it "wallcovering". NEVER use the word "wallpaper".`;
  const prompt = `Write ONE concise, elegant 2-sentence product description for a luxury ${kind} for an interior-design storefront. Pattern: "${r.pattern}". Colorway: "${r.color || r.pattern}". Brand: ${vendor}. Collection: "${r.collection || ''}". Sold per ${unit}. RULES: ${nounRule} Do not invent dimensions, prices, or materials not given; no markdown, no lists, plain prose only. Output only the description text.`;
  try {
    const ctrl = new AbortController();
    const to = setTimeout(() => ctrl.abort(), timeoutMs);
    const res = await fetch(`${OLLAMA}/api/generate`, {
      method: 'POST', signal: ctrl.signal,
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ model: MODEL, prompt, stream: false, options: { temperature: 0.6, num_predict: 120 } }),
    });
    clearTimeout(to);
    const j = await res.json();
    let d = String(j.response || '').trim().replace(/<think>[\s\S]*?<\/think>/gi, '').trim();
    // "wallpaper" is ALWAYS banned. For fabric rows, "wallcovering" is factually wrong -> "fabric".
    d = d.replace(/\bwallpapers?\b/gi, (m) => (m[0] === m[0].toUpperCase() ? 'Wallcovering' : 'wallcovering'));
    if (isFabric) d = d.replace(/\bwall\s*coverings?\b/gi, (m) => (m[0] === m[0].toUpperCase() ? 'Fabric' : 'fabric'));
    return d.split('\n').filter(Boolean).join(' ').slice(0, 600);
  } catch { return ''; }
}

async function main() {
  const only = (process.argv.find(a => a.startsWith('--brand=')) || '').split('=')[1];
  const limit = parseInt((process.argv.find(a => a.startsWith('--limit=')) || '').split('=')[1] || '0', 10);
  const brands = only ? { [only]: BRANDS[only] } : BRANDS;
  fs.mkdirSync(PILOT, { recursive: true });

  for (const [bk, b] of Object.entries(brands)) {
    const cols = 'base_code,mfr_sku,product_type,pattern,color,collection,width,length,ssp_usd,trade_usd,retail_usd,image';
    const rows = psql(`SELECT ${cols} FROM ${b.table} ORDER BY base_code`).trim().split('\n').filter(Boolean).map(line => {
      const [base_code, mfr_sku, product_type, pattern, color, collection, width, length, ssp_usd, trade_usd, retail_usd, image] = line.split('\t');
      return { base_code, mfr_sku, product_type, pattern, color, collection, width, length, ssp_usd, trade_usd, retail_usd, image };
    });

    // existing shopify mfr_skus for this vendor -> dedup HOLD
    const existing = new Set(
      psql(`SELECT upper(mfr_sku) FROM shopify_products WHERE mfr_sku IS NOT NULL AND vendor ILIKE '%${b.vendor.replace(/[^a-z ]/gi, '')}%'`)
        .trim().split('\n').filter(Boolean)
    );

    const manifest = [];
    const held = [];
    let num = b.start;
    const slice = limit > 0 ? rows.slice(0, limit) : rows;

    for (const r of slice) {
      const color = (r.color && r.color.trim()) ? r.color.trim() : (r.pattern ? r.pattern.trim() : '');
      const img = absImg(r.image);
      const price = parseFloat(r.retail_usd);
      const reasons = [];
      // GATE 3: price
      if (!(price > 0)) reasons.push('5field:no-price');
      // GATE 6: image
      if (!img) reasons.push('image-missing');
      // color fallback -> if STILL empty, cannot title -> HOLD (never "Unknown")
      if (!r.pattern && !color) reasons.push('5field:no-title-source');
      // dedup: already on shopify by mfr_sku
      if (r.mfr_sku && existing.has(String(r.mfr_sku).toUpperCase())) reasons.push('dedup:already-on-shopify');

      if (reasons.length) { held.push({ base_code: r.base_code, mfr_sku: r.mfr_sku, reasons, img: !!img, price: price || null }); continue; }

      const dw_sku = `${b.prefix}-${num++}`;
      const tags = deriveTags({ ...r, color }, b.vendor);
      if (tags.length < 2) tags.push(b.vendor + (r.product_type === 'fabric' ? ' Fabric' : ' Wallcovering'));
      const description = await ollamaDesc({ ...r, color }, b.vendor);
      // GATE 4: description — if ollama failed, deterministic non-AI fallback (still a real description)
      const desc = description || `${r.pattern}${color ? ' in ' + color : ''} from the ${r.collection || b.vendor} collection by ${b.vendor}, sold per ${r.product_type === 'fabric' ? 'yard' : 'roll'}.`;

      manifest.push({
        dw_sku, mfr_sku: r.mfr_sku, base_code: r.base_code,
        vendor: b.vendor, product_type: r.product_type === 'fabric' ? 'Fabric' : 'Wallcovering',
        pattern: r.pattern, color, collection: r.collection || '',
        width: r.width || '', length: r.length || '',
        retail_usd: price.toFixed(2), sample_price: '4.25',
        image: img, description: desc, tags,
        unit: r.product_type === 'fabric' ? 'Yard' : 'Roll',
      });
    }
    fs.writeFileSync(`${PILOT}manifest-${bk}.json`, JSON.stringify(manifest, null, 2));
    fs.writeFileSync(`${PILOT}held-${bk}.json`, JSON.stringify(held, null, 2));
    const holdBy = held.reduce((a, h) => { const k = h.reasons[0]; a[k] = (a[k] || 0) + 1; return a; }, {});
    console.log(`${bk}: BUILD-eligible=${manifest.length} HELD=${held.length} ${JSON.stringify(holdBy)}`);
  }
}
main();