`classify.mjs`

93 lines
#!/usr/bin/env node
// Derive a normalized `custom.material` for every ACTIVE DW product from the
// signals we already have (structured "Material:" tag → keyword scan over
// tags+title). Priority order matters: decorative/specialty surfaces and
// specific natural fibers rank ABOVE generic "Vinyl" (which is almost always
// the substrate, not the look). Read-only: emits a distribution + a write-plan
// JSONL (shopify_id → material). $0 (local, rules-based — no AI).
import { execFileSync } from 'node:child_process';
import { writeFileSync } from 'node:fs';

// pull active products from the local mirror as TSV (id, type, title, tags, existing)
const SQL = `
copy (
  select shopify_id,
         translate(coalesce(product_type,''), E'\\t\\n\\r', '   '),
         translate(coalesce(title,''),        E'\\t\\n\\r', '   '),
         translate(coalesce(tags,''),         E'\\t\\n\\r', '   '),
         translate(coalesce(metafields->'custom'->'material_category'->>'value',''), E'\\t\\n\\r', '   ')
  from shopify_products
  where lower(status)='active' and shopify_id is not null
) to stdout`;   // default TEXT format: tab-delimited, empty string stays empty (no "" quoting)
const raw = execFileSync('psql', ['host=/tmp dbname=dw_unified', '-tA', '-c', SQL], { maxBuffer: 1 << 30 }).toString();

// canonical taxonomy, HIGHEST priority first. Each: [label, /regex/].
// Vinyl is LAST — only if nothing else matched (it's the backing, not the face).
const RULES = [
  ['Glass Bead',  /glass[\s-]*bead|beaded/],
  ['Mica',        /\bmica\b/],
  ['Cork',        /\bcork\b/],
  ['Grasscloth',  /grass[\s-]*cloth/],
  ['Sisal',       /\bsisal\b/],
  ['Seagrass',    /sea[\s-]*grass/],
  ['Jute',        /\bjute\b/],
  ['Hemp',        /\bhemp\b/],
  ['Raffia',      /\braffia\b/],
  ['Paperweave',  /paper[\s-]*weave|arrowroot/],
  ['Leather',     /\bleather\b/],
  ['Suede',       /\bsuede\b|ultrasuede/],
  ['Silk',        /\bsilk\b/],
  ['Linen',       /\blinen\b/],
  ['Wool',        /\bwool\b|\bfelt\b/],
  ['Cotton',      /\bcotton\b/],
  ['Flock',       /\bflock(ed)?\b/],
  ['Velvet',      /\bvelvet\b/],
  ['Mylar',       /\bmylar\b/],
  ['Metallic',    /\bmetallic\b|\bfoil\b|gold leaf|silver leaf|\bgilt\b/],
  ['Wood',        /\bwood\b|\bveneer\b|\bbamboo\b/],
  ['Vinyl',       /\bvinyl\b/],
];
// explicit "Material: X" structured tag → canonical (highest confidence)
const STRUCT = { linen:'Linen', cotton:'Cotton', silk:'Silk', velvet:'Velvet',
  metallic:'Metallic', wool:'Wool', leather:'Leather', suede:'Suede', cork:'Cork',
  grasscloth:'Grasscloth', vinyl:'Vinyl' };

function classify(type, title, tags, existing) {
  const ex = (existing || '').replace(/^"+|"+$/g, '').trim();   // strip any stray CSV quoting
  if (ex) return { m: ex, src: 'existing' };
  const hay = (title + ' ' + tags).toLowerCase();
  // 1) structured Material: tag
  const st = tags.match(/Material:\s*([A-Za-z][A-Za-z \-]*)/i);
  if (st) { const k = st[1].trim().toLowerCase().split(/[\s-]/)[0]; if (STRUCT[k]) return { m: STRUCT[k], src: 'struct-tag' }; }
  // 2) priority keyword scan
  for (const [label, re] of RULES) if (re.test(hay)) return { m: label, src: 'keyword' };
  return null; // unclassified
}

const dist = {}, bySrc = {}, plan = [], examples = {};
let total = 0, classified = 0;
for (const line of raw.split('\n')) {
  if (!line) continue;
  total++;
  const c = line.split('\t');
  const [sid, type, title, tags, existing] = [c[0]||'', c[1]||'', c[2]||'', c[3]||'', c[4]||''];
  const r = classify(type, title, tags, existing);
  if (!r) continue;
  classified++;
  dist[r.m] = (dist[r.m] || 0) + 1;
  bySrc[r.src] = (bySrc[r.src] || 0) + 1;
  (examples[r.m] = examples[r.m] || []).length < 3 && examples[r.m].push(`${title.slice(0,52)}  [${r.src}]`);
  if (r.src !== 'existing') plan.push({ shopify_id: sid, material: r.m }); // material_category value to write
}

writeFileSync('data/material-plan.jsonl', plan.map(p => JSON.stringify(p)).join('\n') + '\n');
console.log(`active total          : ${total}`);
console.log(`classified            : ${classified} (${(classified/total*100).toFixed(1)}%)`);
console.log(`unclassified (no write): ${total - classified}`);
console.log(`to WRITE material_category: ${plan.length}  (already-set kept: ${bySrc.existing||0})`);
console.log('by source             :', bySrc);
console.log('\nmaterial_category distribution + sample titles:');
for (const [k, v] of Object.entries(dist).sort((a, b) => b[1] - a[1])) {
  console.log(`  ${k.padEnd(12)} ${String(v).padStart(6)}   e.g. ${(examples[k]||[]).slice(0,2).join('  |  ')}`);
}