← back to Wallco Ai
scripts/build_professional_palette_reference.js
419 lines
#!/usr/bin/env node
/**
* Build professional-palette-reference.json + professional-palette-suffix.txt
* from REAL dw_unified.shopify_color_enrichment rows for high-end vendors.
*
* Source: dw_unified.shopify_color_enrichment (jsonb hex_codes + colors[{hex,name,percentage}])
*
* Steve's rule: stop using made-up "ivory/taupe/sand" lists. Use REAL
* Schumacher / Maya Romanoff / Thibaut / Arte / Cole & Son palettes.
*/
const { execSync } = require('child_process');
const fs = require('fs');
const path = require('path');
// Top-20 prestige decorative wallcovering lines — Steve's quality benchmark
// (2026-05-27): the reference set that defines "catalog-grade / most like our
// unified-db patterns" for cactus (and all) ranking. Each carries a one-line
// aesthetic. Zuber & Cie is the named scenic anchor but has 0 rows in
// shopify_color_enrichment, so it contributes NO db palette — aesthetic-only.
const TOP_20_LINES = [
{ brand: 'Zuber & Cie', aesthetic: 'hand-blocked scenic panoramic', db: false },
{ brand: 'Thibaut', aesthetic: 'archival classic' },
{ brand: 'Schumacher', aesthetic: 'American archival glamour' },
{ brand: 'Cole & Son', aesthetic: 'iconic British print archive' },
{ brand: 'Scalamandré', aesthetic: 'old-world opulence' },
{ brand: 'Osborne & Little', aesthetic: 'English eclectic colour' },
{ brand: 'Designers Guild', aesthetic: 'refined modern romantic' },
{ brand: 'Nina Campbell', aesthetic: 'English country elegance' },
{ brand: 'Maya Romanoff', aesthetic: 'handcrafted luxe surface' },
{ brand: 'Anna French', aesthetic: 'delicate prints & lace' },
{ brand: 'Harlequin', aesthetic: 'contemporary British design-led' },
{ brand: 'Arte International', aesthetic: 'natural-fibre luxe' },
{ brand: 'Brunschwig & Fils', aesthetic: 'French archival document prints' },
{ brand: 'William Morris', aesthetic: 'Arts & Crafts heritage' },
{ brand: '1838 Wallcoverings', aesthetic: 'British flocked/velvet revival' },
{ brand: 'Lee Jofa', aesthetic: 'English country-house archive' },
{ brand: 'Romo', aesthetic: 'painterly British' },
{ brand: 'GP & J Baker', aesthetic: 'English heritage florals' },
{ brand: 'Ralph Lauren', aesthetic: 'American heritage luxe' },
{ brand: 'Élitis', aesthetic: 'French textural avant-garde' },
];
// Vendor-name variants exactly as they appear in shopify_color_enrichment (the
// palette pull matches on these), plus DW's own premium bespoke line
// ('DW Bespoke Studio', ~1,029 enriched rows — Steve 2026-05-27). Zuber omitted
// (0 rows). Pierre Frey kept as an extra French maison present in the db.
const PRO_VENDORS = [
'Thibaut', 'Thibaut Wallpaper', 'THIBAUT AT DW',
'Schumacher', 'Schumacher Wallcoverings',
'Cole & Son',
'Scalamandre Wallpaper', 'Scalamandre',
'Osborne & Little',
'Designers Guild',
'Nina Campbell',
'Maya Romanoff',
'Anna French',
'Harlequin',
'Arte International', 'Arte International Wallcoverings', 'Arte',
'Brunschwig & Fils',
'William Morris', 'Morris and Company',
'1838 Wallcoverings',
'Lee Jofa',
'Romo',
'GP & J Baker',
'Ralph Lauren', 'Ralph Lauren Wallpaper',
'Elitis', 'Élitis',
'Pierre Frey',
'DW Bespoke Studio',
];
// ---------- color math helpers ----------
function hexToRgb(hex) {
if (typeof hex !== 'string') return null;
let h = hex.trim().replace(/^#/, '');
if (h.length === 3) h = h.split('').map((c) => c + c).join('');
if (h.length !== 6 || /[^0-9a-f]/i.test(h)) return null;
const n = parseInt(h, 16);
return { r: (n >> 16) & 255, g: (n >> 8) & 255, b: n & 255 };
}
function rgbToHsl({ r, g, b }) {
const R = r / 255, G = g / 255, B = b / 255;
const max = Math.max(R, G, B), min = Math.min(R, G, B);
const l = (max + min) / 2;
let h = 0, s = 0;
if (max !== min) {
const d = max - min;
s = l > 0.5 ? d / (2 - max - min) : d / (max + min);
switch (max) {
case R: h = (G - B) / d + (G < B ? 6 : 0); break;
case G: h = (B - R) / d + 2; break;
case B: h = (R - G) / d + 4; break;
}
h *= 60;
}
return { h, s: s * 100, l: l * 100 };
}
// 30° bins on hue + neutral bucket when saturation <15%
const HUE_BINS = [
[ 0, 15, 'red'],
[ 15, 45, 'red-orange'],
[ 45, 75, 'orange-yellow'],
[ 75, 105, 'yellow-green'],
[105, 150, 'green'],
[150, 195, 'blue-green'],
[195, 240, 'blue'],
[240, 285, 'blue-purple'],
[285, 315, 'purple'],
[315, 345, 'magenta-pink'],
[345, 360, 'red'],
];
function hueBucket(hsl) {
if (hsl.s < 15) {
if (hsl.l < 20) return 'neutral-black';
if (hsl.l > 85) return 'neutral-white';
return 'neutral-gray';
}
for (const [a, b, label] of HUE_BINS) {
if (hsl.h >= a && hsl.h < b) return label;
}
return 'red';
}
// Nearest plain-English name from a small designer-paint vocabulary.
// Curated specifically for wallpaper trade colors — names a designer says,
// not a Crayola box.
const COLOR_VOCAB = [
// neutrals
['ivory', '#FFFFF0'],
['cream', '#F5F0E1'],
['ecru', '#E6DDC2'],
['parchment', '#EFE4C8'],
['linen', '#E8DFD0'],
['bone', '#E3DAC9'],
['oyster', '#D6CFC2'],
['stone', '#A8A294'],
['mushroom', '#A99F8C'],
['taupe', '#9C8E7D'],
['greige', '#A89A87'],
['fawn', '#C2B091'],
['dove', '#C9C5BC'],
['pearl', '#EAE6DF'],
['ash', '#9B9B95'],
['charcoal', '#3A3A3A'],
['ink', '#1B1B1B'],
['noir', '#0A0A0A'],
['off-white', '#FAF7F0'],
// greens
['sage', '#9CAE92'],
['celadon', '#A7C3A4'],
['olive', '#6B6B3A'],
['moss', '#7A8450'],
['fern', '#5C7050'],
['forest', '#2E4A2F'],
['eucalyptus', '#A3B8A0'],
['hunter', '#355E3B'],
['emerald', '#1F6E4A'],
['malachite', '#0F6B43'],
['jade', '#3F7368'],
// blues
['robin egg', '#9AC0CD'],
['powder blue', '#B8C9D8'],
['cornflower', '#6A8FC5'],
['sky', '#A6C5DA'],
['slate', '#6C7B89'],
['denim', '#5076A0'],
['indigo', '#2A3E66'],
['midnight', '#152238'],
['navy', '#1B2A47'],
['teal', '#2F6970'],
['peacock', '#1E5A6D'],
['cerulean', '#2E5C8A'],
['delft blue', '#385E8A'],
// purples
['lavender', '#B7A6C9'],
['mauve', '#A88A99'],
['plum', '#5E3B5C'],
['aubergine', '#3D2B3E'],
['heather', '#9B8AA0'],
// reds / pinks
['blush', '#E5C2BC'],
['rose', '#C99393'],
['coral', '#D58A75'],
['terracotta', '#B8634B'],
['rust', '#9C5234'],
['oxblood', '#5A1F22'],
['burgundy', '#5C1A2B'],
['claret', '#6E2334'],
['cinnabar', '#A33A2B'],
['venetian red', '#A33A2B'],
// yellows / browns
['butter', '#EDDFA6'],
['straw', '#D8C58A'],
['mustard', '#B58A2D'],
['ochre', '#B68642'],
['gold', '#A98740'],
['bronze', '#8C6E3F'],
['caramel', '#8E5D32'],
['cocoa', '#5E3F2C'],
['walnut', '#4E3A2A'],
['chocolate', '#3D2A1E'],
['umber', '#5A4232'],
['sienna', '#8A4A30'],
];
function colorDist(a, b) {
// simple weighted RGB; fine for "closest plain-English label"
const dr = a.r - b.r, dg = a.g - b.g, db = a.b - b.b;
return dr * dr * 0.3 + dg * dg * 0.59 + db * db * 0.11;
}
function nearestName(hex) {
const rgb = hexToRgb(hex);
if (!rgb) return 'unknown';
let best = null, bestD = Infinity;
for (const [name, vhex] of COLOR_VOCAB) {
const v = hexToRgb(vhex);
const d = colorDist(rgb, v);
if (d < bestD) { bestD = d; best = name; }
}
return best;
}
// ---------- main ----------
(async () => {
const vendorList = PRO_VENDORS.map(v => v.replace(/'/g, "''")).map(v => `'${v}'`).join(',');
const sql = `
SELECT coalesce(json_agg(row_to_json(t)), '[]') FROM (
SELECT vendor, hex_codes, colors
FROM shopify_color_enrichment
WHERE vendor IN (${vendorList})
AND hex_codes IS NOT NULL
AND jsonb_typeof(hex_codes) = 'array'
AND jsonb_array_length(hex_codes) > 0
) t
`;
const raw = execSync(
`psql -d dw_unified -tA -c ${JSON.stringify(sql.replace(/\s+/g, ' '))}`,
{ encoding: 'utf8', maxBuffer: 256 << 20 }
);
const rows = JSON.parse(raw.trim());
console.log(`[pro-palette] surveyed ${rows.length.toLocaleString()} professional products`);
const vendorCounts = {};
const hexFreq = new Map(); // hex -> product count (one vote per product)
const hexProducts = new Map(); // hex -> Set(rowIdx) for pct math
const hexPercentSum = new Map(); // hex -> sum of "percentage" across rows when colors{} present
const hexPercentN = new Map(); // hex -> n contributing
let hexInstancesTotal = 0;
const hueBucketCount = {};
const satBuckets = { '0-20':0, '20-40':0, '40-60':0, '60-80':0, '80-100':0 };
const lightBuckets = { '0-20':0, '20-40':0, '40-60':0, '60-80':0, '80-100':0 };
let colorsPerDesignHist = {}; // n distinct hexes per product
rows.forEach((row, idx) => {
vendorCounts[row.vendor] = (vendorCounts[row.vendor] || 0) + 1;
const hexes = Array.isArray(row.hex_codes) ? row.hex_codes : [];
const named = Array.isArray(row.colors) ? row.colors : [];
// dedupe within a product so we count "products that contain hex" not "instances"
const uniq = new Set();
hexes.forEach((h) => {
if (typeof h !== 'string') return;
const norm = h.trim().toUpperCase();
if (!/^#[0-9A-F]{6}$/.test(norm)) return;
uniq.add(norm);
});
const n = uniq.size;
colorsPerDesignHist[n] = (colorsPerDesignHist[n] || 0) + 1;
uniq.forEach((h) => {
hexFreq.set(h, (hexFreq.get(h) || 0) + 1);
hexInstancesTotal++;
const rgb = hexToRgb(h);
if (rgb) {
const hsl = rgbToHsl(rgb);
const bucket = hueBucket(hsl);
hueBucketCount[bucket] = (hueBucketCount[bucket] || 0) + 1;
const sBin = Math.min(Math.floor(hsl.s / 20), 4);
const lBin = Math.min(Math.floor(hsl.l / 20), 4);
const sKey = `${sBin*20}-${(sBin+1)*20}`;
const lKey = `${lBin*20}-${(lBin+1)*20}`;
satBuckets[sKey] = (satBuckets[sKey] || 0) + 1;
lightBuckets[lKey] = (lightBuckets[lKey] || 0) + 1;
}
});
// accumulate named percentage info
named.forEach((c) => {
if (!c || typeof c.hex !== 'string') return;
const norm = c.hex.trim().toUpperCase();
if (!/^#[0-9A-F]{6}$/.test(norm)) return;
const pct = Number(c.percentage);
if (!Number.isFinite(pct)) return;
hexPercentSum.set(norm, (hexPercentSum.get(norm) || 0) + pct);
hexPercentN.set(norm, (hexPercentN.get(norm) || 0) + 1);
});
});
const ranked = Array.from(hexFreq.entries())
.sort((a, b) => b[1] - a[1])
.slice(0, 50)
.map(([hex, count]) => {
const rgb = hexToRgb(hex);
const hsl = rgbToHsl(rgb);
return {
hex,
pct_of_products: +(count / rows.length).toFixed(4),
appearances: count,
avg_share_in_design: hexPercentN.get(hex)
? +(hexPercentSum.get(hex) / hexPercentN.get(hex)).toFixed(1)
: null,
hsl: { h: +hsl.h.toFixed(1), s: +hsl.s.toFixed(1), l: +hsl.l.toFixed(1) },
hue_bucket: hueBucket(hsl),
common_name: nearestName(hex),
};
});
// normalize hue distribution as a percentage
const totalHex = hexInstancesTotal;
const hueDist = {};
Object.entries(hueBucketCount)
.sort((a, b) => b[1] - a[1])
.forEach(([k, v]) => { hueDist[k] = +(v / totalHex).toFixed(4); });
const satDist = {};
const lightDist = {};
Object.entries(satBuckets).forEach(([k, v]) => satDist[k] = +(v / totalHex).toFixed(4));
Object.entries(lightBuckets).forEach(([k, v]) => lightDist[k] = +(v / totalHex).toFixed(4));
const hexesPerDesign = Object.entries(colorsPerDesignHist)
.sort((a, b) => Number(a[0]) - Number(b[0]))
.map(([n, c]) => ({ distinct_hexes: Number(n), product_count: c, pct: +(c/rows.length).toFixed(4) }));
const reference = {
source: 'dw_unified.shopify_color_enrichment',
ts: new Date().toISOString(),
vendors_surveyed: Object.entries(vendorCounts).sort((a,b)=>b[1]-a[1]).map(([v,c]) => ({ vendor: v, product_count: c })),
product_count: rows.length,
hex_instance_count: hexInstancesTotal,
distinct_hex_count: hexFreq.size,
top_50_hexes: ranked,
hue_distribution: hueDist,
saturation_distribution: satDist,
lightness_distribution: lightDist,
hexes_per_design_distribution: hexesPerDesign,
professional_palette_words: Array.from(new Set(ranked.map(r => r.common_name))),
methodology: {
table: 'shopify_color_enrichment',
filter: 'vendor IN PRO_VENDORS AND hex_codes is non-empty jsonb array',
counting: 'one vote per product per distinct hex (deduped within a product)',
naming: 'nearest weighted-RGB match against a curated 70-name designer-paint vocab',
hue_bins: '11 hue bins (30°-ish) + neutral-white / neutral-gray / neutral-black when S<15%',
},
};
// ---------- write reference json ----------
const outDir = path.join(__dirname, '..', 'data');
const refPath = path.join(outDir, 'professional-palette-reference.json');
fs.writeFileSync(refPath, JSON.stringify(reference, null, 2));
console.log(`[pro-palette] wrote ${refPath}`);
// ---------- build the prompt suffix ----------
// Pick the top ~25 hexes for the suffix (any more and prompts get bloated).
const topForSuffix = ranked.slice(0, 25);
const hexList = topForSuffix.map(r => `${r.hex} (${r.common_name})`).join(', ');
const satLow = ((satDist['0-20'] || 0) * 100).toFixed(1);
const satMid = ((satDist['20-40'] || 0) * 100).toFixed(1);
const satHigh = (((satDist['60-80'] || 0) + (satDist['80-100'] || 0)) * 100).toFixed(1);
// observed median number of distinct hexes per product
const sorted = [];
hexesPerDesign.forEach(({ distinct_hexes, product_count }) => {
for (let i = 0; i < product_count; i++) sorted.push(distinct_hexes);
});
sorted.sort((a, b) => a - b);
const median = sorted[Math.floor(sorted.length / 2)] || 0;
const p90 = sorted[Math.floor(sorted.length * 0.9)] || 0;
const suffix =
`Professional designer-wallpaper palette grounded in real-world high-end vendor catalogs ` +
`(top-20 lines: ${TOP_20_LINES.map(l => l.brand).join(', ')} + DW Bespoke Studio — ${rows.length.toLocaleString()} ` +
`real products analyzed in dw_unified.shopify_color_enrichment). ` +
`Use ONLY colors from this learned palette: ${hexList}. ` +
`Saturation distribution observed in real professional wallpaper: S<20% = ${satLow}% of all color usage, ` +
`S 20–40% = ${satMid}%, S>60% = ${satHigh}%. ` +
`Most professional wallpapers use a low-saturation, tone-on-tone or 2–3 color story. ` +
`Median distinct hues per design = ${median}; 90th percentile = ${p90}. ` +
`Keep total distinct hues per design ≤ ${Math.max(p90, 5)}. ` +
`Never use neon, fluorescent, rainbow, primary-saturated red/yellow/blue, hot pink, or electric green. ` +
`Prefer aged, slightly-grayed, archival pigments over pure chromatic colors.`;
const suffixPath = path.join(outDir, 'professional-palette-suffix.txt');
fs.writeFileSync(suffixPath, suffix + '\n');
console.log(`[pro-palette] wrote ${suffixPath}`);
// ---------- console headline ----------
console.log('\n=== HEADLINE ===');
console.log(`vendors surveyed: ${Object.keys(vendorCounts).length}`);
console.log(`products surveyed: ${rows.length.toLocaleString()}`);
console.log(`distinct hexes: ${hexFreq.size.toLocaleString()}`);
console.log(`hex instances total: ${hexInstancesTotal.toLocaleString()}`);
console.log(`S<20%: ${satLow}% of color usage`);
console.log(`S 20–40%: ${satMid}%`);
console.log(`S>60%: ${satHigh}%`);
console.log(`median hues/design: ${median}`);
console.log(`p90 hues/design: ${p90}`);
console.log('\nTop 10 hexes:');
ranked.slice(0, 10).forEach((r, i) => {
console.log(` ${String(i+1).padStart(2)}. ${r.hex} ${String(r.common_name).padEnd(14)} ${(r.pct_of_products*100).toFixed(1)}% of products bucket=${r.hue_bucket}`);
});
console.log('\nHue distribution:');
Object.entries(hueDist).slice(0, 12).forEach(([k, v]) => {
console.log(` ${k.padEnd(16)} ${(v*100).toFixed(1)}%`);
});
})().catch((e) => {
console.error(e);
process.exit(1);
});