← back to Letsbegin
last20day_full_monty.js
402 lines
#!/usr/bin/env node
/**
* Last-20-Day Full Monty backfill (NO room settings)
* Cohort: shopify_products created in the last 20 days, full_monty_at IS NULL.
* As of build: 1,003 products (1,000 Rebel Walls + Cole & Son + Glitter Walls + Designer Wallcoverings).
*
* Pipeline per product (cheap + high-value phases adapted from glitter_full_monty.js):
* 1. Migrate global.* -> custom.* metafields (width, fire_rating, material, repeat, match_type, etc.)
* (skips any custom.* key already present — most Rebel Walls already have these)
* 2. Gemini color analysis (dominant color name + hex + background + color_details JSON) -> custom.*
* 3. Specs metafields + title normalization "Pattern - Color | <Vendor>"
* 4. Image alt tags: "{SKU} {Pattern} {Color} designer-wallcoverings-los-angeles"
* 5. Stamp DB: full_monty_at, fm_metafields_verified=1, fm_specs_at, fm_tags_at, full_monty_tags
*
* EXPLICITLY OUT OF SCOPE THIS PASS: room settings, body rewrite generation, cost/price changes.
* (Body is left untouched; cost is gated on a trade discount Steve hasn't given.)
*
* RESUMABLE: cohort query filters full_monty_at IS NULL, so re-running skips finished rows.
*
* Usage:
* node last20day_full_monty.js # full run
* node last20day_full_monty.js --limit 3 # validation run (first 3 rows)
*
* Env required: SHOPIFY_ADMIN_TOKEN, GEMINI_API_KEY
* Optional: PG* (defaults: user stevestudio2, db dw_unified, local socket)
*/
const https = require('https');
const http = require('http');
const fs = require('fs');
const { Client } = require('pg');
const STORE = 'designer-laboratory-sandbox.myshopify.com';
const TOKEN = process.env.SHOPIFY_ADMIN_TOKEN || process.env.SHOPIFY_PRODUCT_TOKEN;
const GEMINI_KEY = process.env.GEMINI_API_KEY;
if (!GEMINI_KEY) { console.error('FATAL: GEMINI_API_KEY environment variable is required'); process.exit(1); }
if (!TOKEN) { console.error('FATAL: SHOPIFY_ADMIN_TOKEN environment variable is required'); process.exit(1); }
const LOG_FILE = '/Users/macstudio3/Projects/Letsbegin/last20day-backfill.log';
const API = '/admin/api/2024-10/graphql.json';
// --limit N
let LIMIT = null;
{
const li = process.argv.indexOf('--limit');
if (li !== -1 && process.argv[li + 1]) LIMIT = parseInt(process.argv[li + 1], 10) || null;
}
function log(msg) {
const line = `[${new Date().toISOString()}] ${msg}`;
console.log(line);
try { fs.appendFileSync(LOG_FILE, line + '\n'); } catch {}
}
function sleep(ms) { return new Promise(r => setTimeout(r, ms)); }
// ---------- Shopify GraphQL ----------
function gqlRaw(body) {
return new Promise((resolve, reject) => {
const data = JSON.stringify(body);
const req = https.request({
hostname: STORE, path: API, method: 'POST',
headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json', 'Content-Length': Buffer.byteLength(data) },
}, res => { let c = ''; res.on('data', d => c += d); res.on('end', () => { try { resolve(JSON.parse(c)); } catch { resolve({ error: c.slice(0, 300) }); } }); });
req.on('error', reject);
req.setTimeout(60000, () => { req.destroy(); reject(new Error('timeout')); });
req.write(data); req.end();
});
}
async function gql(body, retries = 4) {
for (let attempt = 1; attempt <= retries; attempt++) {
try {
const result = await gqlRaw(body);
const throttled = result?.errors?.some(e => /Throttled/i.test(e.message || ''));
const lowBudget = (result?.extensions?.cost?.throttleStatus?.currentlyAvailable || 9999) < 200;
if (throttled) { log(` GQL throttled, waiting 3s (attempt ${attempt}/${retries})`); await sleep(3000); continue; }
if (lowBudget) await sleep(1500); // back off when bucket low
return result;
} catch (e) {
if (attempt < retries) { log(` GQL error: ${e.message}, retry in ${attempt * 2}s (${attempt}/${retries})`); await sleep(attempt * 2000); continue; }
throw e;
}
}
}
// ---------- image download + Gemini ----------
function downloadImage(url, dest) {
return new Promise((resolve, reject) => {
const mod = url.startsWith('https') ? https : http;
const file = fs.createWriteStream(dest);
mod.get(url, { headers: { 'User-Agent': 'Mozilla/5.0' } }, res => {
if (res.statusCode >= 300 && res.statusCode < 400 && res.headers.location) {
file.close(); return downloadImage(res.headers.location, dest).then(resolve).catch(reject);
}
if (res.statusCode !== 200) { file.close(); return reject(new Error(`HTTP ${res.statusCode}`)); }
res.pipe(file); file.on('finish', () => { file.close(); resolve(); });
}).on('error', e => { file.close(); reject(e); });
});
}
function geminiCall(reqBody) {
return new Promise((resolve) => {
const data = JSON.stringify(reqBody);
const url = new URL(`https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash:generateContent?key=${GEMINI_KEY}`);
const req = https.request({
hostname: url.hostname, path: url.pathname + url.search, method: 'POST',
headers: { 'Content-Type': 'application/json', 'Content-Length': Buffer.byteLength(data) }
}, res => {
const chunks = [];
res.on('data', c => chunks.push(c));
res.on('end', () => { try { resolve(JSON.parse(Buffer.concat(chunks).toString())); } catch { resolve(null); } });
});
req.on('error', () => resolve(null));
req.setTimeout(120000, () => { req.destroy(); resolve(null); });
req.write(data); req.end();
});
}
// ---------- cleaners (from glitter) ----------
function cleanWidth(v) { if (!v) return null; const m = v.match(/([\d.]+)/); return m ? `${m[1]} Inches` : null; }
function cleanRepeat(v) { if (!v || v === 'N/A' || v === '0' || v.toLowerCase() === 'random' || v.toLowerCase() === 'none') return null; const m = v.match(/([\d.]+)/); return m ? `${m[1]} Inches` : null; }
function cleanFireRating(v) { if (!v) return null; let f = v.replace(/["']/g, '').trim(); if (f.length > 100) return null; if (/class\s*a/i.test(f) && !f.includes('ASTM') && !/EU Fire/i.test(f)) f = 'ASTM E-84 Class A'; return f || null; }
function cleanMaterial(v) { if (!v) return null; return v.replace(/\bWallpapers\b/gi, 'Wallcoverings').replace(/\bWallpaper\b/gi, 'Wallcovering').trim() || null; }
const FIELD_MAP = {
'width': { key: 'width', clean: cleanWidth, type: 'single_line_text_field' },
'Width': { key: 'width', clean: cleanWidth, type: 'single_line_text_field' },
'repeat': { key: 'pattern_repeat', clean: cleanRepeat, type: 'single_line_text_field' },
'Vert-Rpt': { key: 'pattern_repeat', clean: cleanRepeat, type: 'single_line_text_field' },
'Vert-Repeat': { key: 'pattern_repeat', clean: cleanRepeat, type: 'single_line_text_field' },
'fire_rating': { key: 'fire_rating', clean: cleanFireRating, type: 'single_line_text_field' },
'FLAMMABILITY': { key: 'fire_rating', clean: cleanFireRating, type: 'single_line_text_field' },
'Contents': { key: 'material', clean: cleanMaterial, type: 'multi_line_text_field' },
'Content': { key: 'material', clean: cleanMaterial, type: 'multi_line_text_field' },
'Construction': { key: 'material', clean: cleanMaterial, type: 'multi_line_text_field' },
'material': { key: 'material', clean: cleanMaterial, type: 'multi_line_text_field' },
'Collection': { key: 'collection_name', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'collection_name': { key: 'collection_name', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'Brand': { key: 'brand', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'MATCH': { key: 'match_type', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'Match': { key: 'match_type', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'match': { key: 'match_type', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'Finish': { key: 'finish', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'FINISH': { key: 'finish', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'Cleaning': { key: 'care', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'Clean-Code': { key: 'care', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'application': { key: 'application', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'Substrate': { key: 'backing', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'substrate': { key: 'backing', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'length': { key: 'length', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'Length': { key: 'length', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'packaged': { key: 'packaging', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'Packaged': { key: 'packaging', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'unit_of_measure': { key: 'unit_of_measure', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'Weight': { key: 'product_weight', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'Country': { key: 'origin', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'Country-of-Origin': { key: 'origin', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'color': { key: 'color', clean: v => v?.trim() || null, type: 'single_line_text_field' },
'Color-Way': { key: 'color', clean: v => v?.trim() || null, type: 'single_line_text_field' },
};
// ---------- title helpers ----------
function stripVendorSuffix(title) {
// Everything from the FIRST pipe onward is vendor/marketing chrome (some titles have 2+ pipes).
const pi = title.indexOf('|');
return (pi >= 0 ? title.slice(0, pi) : title).trim();
}
function extractPatternAndColor(title) {
let cleaned = stripVendorSuffix(title);
cleaned = cleaned.replace(/\(tm\)/gi, '').replace(/\bwallcoverings?\b/gi, '').replace(/\bwall\s*coverings?\b/gi, '').replace(/\bwall\s*papers?\b/gi, '').replace(/\s+/g, ' ').trim();
// Separators we treat as "pattern <sep> color": SPACED dash/en-dash " - " or comma ", ".
// NOT bare hyphens (e.g. "2-Pack") and NOT middots "·" (those are descriptor chains, not color).
const commaIdx = cleaned.search(/,\s/);
const dashIdx = cleaned.search(/\s[-–]\s/);
// pick the LAST occurring valid separator so "Sequin - Eel Skin - White Gold" keeps the trailing color
function lastSep(re) { let m, last = -1; const r = new RegExp(re, 'g'); while ((m = r.exec(cleaned))) last = m.index; return last; }
const lastComma = lastSep(',\\s');
const lastDash = lastSep('\\s[-–]\\s');
if (commaIdx === -1 && dashIdx === -1) return { pattern: cleaned.trim(), color: null };
// prefer whichever valid separator appears LATER (closer to the color, which is usually last)
if (lastDash >= 0 && lastDash >= lastComma) {
const m = cleaned.slice(0, lastDash).trim();
const c = cleaned.slice(lastDash).replace(/^\s[-–]\s/, '').trim();
return { pattern: m, color: c };
}
if (lastComma >= 0) {
const m = cleaned.slice(0, lastComma).trim();
const c = cleaned.slice(lastComma + 1).trim();
return { pattern: m, color: c };
}
return { pattern: cleaned.trim(), color: null };
}
function toTitleCase(s) { if (!s) return ''; return s.replace(/\b\w+/g, w => w.charAt(0).toUpperCase() + w.slice(1).toLowerCase()); }
function buildTitle(pattern, color, vendor) {
const tc = toTitleCase(color);
return tc ? `${pattern} - ${tc} | ${vendor}` : `${pattern} | ${vendor}`;
}
// ---------- per-product fetch ----------
const PRODUCT_QUERY = (id) => ({ query: `{
product(id:"${id}") {
id title bodyHtml vendor tags
images(first:15){ edges{ node{ id url altText } } }
media(first:15){ edges{ node{ ... on MediaImage { id } } } }
variants(first:5){ edges{ node{ id sku title } } }
metafields(first:100){ edges{ node{ namespace key value type } } }
}
}` });
const MF_MUTATION = 'mutation metafieldsSet($m: [MetafieldsSetInput!]!) { metafieldsSet(metafields: $m) { metafields { key } userErrors { message field } } }';
const PRODUCT_UPDATE = 'mutation ($i: ProductInput!) { productUpdate(input: $i) { product { id } userErrors { message field } } }';
async function processProduct(p, idx, total, pg) {
const pid = p.id;
const origTitle = p.title || '';
const vendor = p.vendor || 'Designer Wallcoverings';
log(`\n[${idx + 1}/${total}] ${pid} ${origTitle}`);
const globals = {}, customs = {};
for (const m of (p.metafields?.edges || [])) {
const { namespace, key, value } = m.node;
if (namespace === 'global' && value) globals[key] = value;
if (namespace === 'custom' && value) customs[key] = value;
}
const firstVariant = p.variants?.edges?.find(e => e.node?.sku && !/-sample$/i.test(e.node.sku))?.node || p.variants?.edges?.[0]?.node;
const sku = firstVariant?.sku || '';
const cleanSku = sku.replace(/-sample$/i, '').trim();
const result = { specs: 0, color: false, title: false, alt: 0, errors: [] };
// ---- PHASE 1: global.* -> custom.* (skip keys already in custom.*) ----
const mfToSet = [];
const seen = new Set();
for (const [gKey, mapping] of Object.entries(FIELD_MAP)) {
if (!globals[gKey]) continue;
if (customs[mapping.key]) continue;
if (seen.has(mapping.key)) continue;
const cleaned = mapping.clean(globals[gKey]);
if (!cleaned) continue;
mfToSet.push({ ownerId: pid, namespace: 'custom', key: mapping.key, value: cleaned, type: mapping.type });
seen.add(mapping.key);
}
if (mfToSet.length > 0) {
try {
const r = await gql({ query: MF_MUTATION, variables: { m: mfToSet } });
const errs = r?.data?.metafieldsSet?.userErrors || [];
if (errs.length) result.errors.push(`Specs: ${errs[0].message}`);
else { result.specs = mfToSet.length; log(` P1 Specs: ${mfToSet.length} migrated`); }
} catch (e) { result.errors.push(`Specs: ${e.message}`); }
await sleep(400);
} else { log(` P1 Specs: already complete`); }
// ---- PHASE 2: Gemini color analysis ----
const imgUrl = p.images?.edges?.[0]?.node?.url;
let colorName = customs['color_name'] || customs['color'] || globals['color'] || null;
if (imgUrl && !customs['color_details']) {
try {
const tmpImg = `/tmp/last20_img_${process.pid}.jpg`;
await downloadImage(imgUrl, tmpImg);
const imgB64 = fs.readFileSync(tmpImg).toString('base64');
const colorResult = await geminiCall({
contents: [{ parts: [
{ text: 'Analyze this wallcovering swatch/interior image. Return ONLY valid JSON (no markdown fencing): {"dominantColor":"ColorName","dominantHex":"#XXXXXX","backgroundColor":"ColorName","backgroundHex":"#XXXXXX","colors":[{"name":"Color Name","hex":"#XXXXXX","percentage":40}]}. Use 3-6 colors summing to 100%. Title Case names. Be specific (e.g., "Champagne Gold" not "Gold"). Focus on the wallcovering surface, not room furnishings.' },
{ inlineData: { mimeType: 'image/jpeg', data: imgB64 } }
]}],
generationConfig: { temperature: 0.2, maxOutputTokens: 500 }
});
const text = colorResult?.candidates?.[0]?.content?.parts?.[0]?.text || '';
const clean = text.replace(/```json?\s*/g, '').replace(/```/g, '').trim();
const cd = JSON.parse(clean);
colorName = cd.dominantColor || colorName;
const colorMf = [
{ ownerId: pid, namespace: 'custom', key: 'color_hex', value: cd.dominantHex, type: 'single_line_text_field' },
{ ownerId: pid, namespace: 'custom', key: 'background_color', value: cd.backgroundColor, type: 'single_line_text_field' },
{ ownerId: pid, namespace: 'custom', key: 'color_details', value: JSON.stringify(cd.colors), type: 'json' },
{ ownerId: pid, namespace: 'custom', key: 'color_name', value: cd.dominantColor, type: 'single_line_text_field' },
].filter(m => m.value != null && m.value !== '');
const r = await gql({ query: MF_MUTATION, variables: { m: colorMf } });
const errs = r?.data?.metafieldsSet?.userErrors || [];
if (errs.length) result.errors.push(`Color: ${errs[0].message}`);
else { result.color = true; log(` P2 Color: ${cd.dominantColor} (${cd.dominantHex})`); }
await sleep(1200);
} catch (e) {
result.errors.push(`Color: ${(e.message || '').slice(0, 60)}`);
log(` P2 Color: ERROR ${(e.message || '').slice(0, 60)}`);
}
} else if (customs['color_details']) {
log(` P2 Color: already done`); result.color = true;
if (!colorName) { try { const d = JSON.parse(customs['color_details']); if (Array.isArray(d) && d[0]) colorName = d[0].name; } catch {} }
} else { log(` P2 Color: no image`); }
// ---- PHASE 3: title normalization ----
const { pattern, color: parsedColor } = extractPatternAndColor(origTitle);
const badColorValues = ['random', 'n/a', 'none', 'varies', 'n a', ''];
const vendorColor = (customs['color'] || globals['color'] || globals['Color-Way'] || '').trim();
const useVendorColor = vendorColor && !badColorValues.includes(vendorColor.toLowerCase()) ? vendorColor : null;
const titleColor = useVendorColor || parsedColor || colorName || '';
const newTitle = buildTitle(pattern, titleColor, vendor);
if (newTitle && newTitle !== origTitle) {
try {
const r = await gql({ query: PRODUCT_UPDATE, variables: { i: { id: pid, title: newTitle } } });
const errs = r?.data?.productUpdate?.userErrors || [];
if (errs.length) result.errors.push(`Title: ${errs[0].message}`);
else { result.title = true; log(` P3 Title: "${origTitle}" -> "${newTitle}"`); }
} catch (e) { result.errors.push(`Title: ${(e.message || '').slice(0, 40)}`); }
await sleep(400);
} else { log(` P3 Title: already correct`); result.title = true; }
// ---- PHASE 4: image alt tags ----
const mediaEdges = p.media?.edges || [];
const altBase = `${cleanSku || sku} ${pattern} ${titleColor} designer-wallcoverings-los-angeles`.replace(/\s+/g, ' ').trim();
if (mediaEdges.length > 0) {
const mediaUpdates = [];
for (let mi = 0; mi < mediaEdges.length; mi++) {
const node = mediaEdges[mi].node;
if (!node?.id) continue;
const altTag = mi === 0 ? altBase : `${altBase} ${mi + 1}`;
mediaUpdates.push({ id: node.id, alt: altTag });
}
if (mediaUpdates.length > 0) {
try {
const mediaJson = mediaUpdates.map(m => `{id: "${m.id}", alt: "${m.alt.replace(/\\/g, '\\\\').replace(/"/g, '\\"')}"}`).join(', ');
const r = await gql({ query: `mutation { productUpdateMedia(productId: "${pid}", media: [${mediaJson}]) { media { ... on MediaImage { id } } mediaUserErrors { message } } }` });
const errs = r?.data?.productUpdateMedia?.mediaUserErrors || [];
if (errs.length) result.errors.push(`Alt: ${errs[0].message}`);
else { result.alt = mediaUpdates.length; log(` P4 Alt: ${mediaUpdates.length} images`); }
} catch (e) { result.errors.push(`Alt: ${(e.message || '').slice(0, 40)}`); }
await sleep(400);
}
} else { log(` P4 Alt: no media`); }
if (result.errors.length) log(` ERRORS: ${result.errors.join(' | ')}`);
// ---- PHASE 5: stamp DB (idempotency) ----
// Only stamp full_monty_at if the core write phases didn't hard-fail.
const hardFail = result.errors.some(e => /Specs|Title|Alt/.test(e));
if (!hardFail) {
try {
await pg.query(
`UPDATE shopify_products
SET full_monty_at = now(),
fm_metafields_verified = 1,
fm_specs_at = now(),
fm_tags_at = CASE WHEN $2 > 0 THEN now() ELSE fm_tags_at END,
full_monty_tags = $2
WHERE shopify_id = $1`,
[pid, result.alt]
);
log(` P5 DB: full_monty_at stamped, fm_metafields_verified=1`);
} catch (e) { log(` P5 DB: ERROR ${e.message}`); result.errors.push(`DB: ${e.message}`); }
} else {
log(` P5 DB: SKIPPED stamp (hard error this row, will retry on resume)`);
}
return result;
}
async function main() {
fs.appendFileSync(LOG_FILE, `\n=== Last-20-Day Full Monty run started ${new Date().toISOString()}${LIMIT ? ` (LIMIT ${LIMIT})` : ' (FULL)'} ===\n`);
log('=== Last-20-Day Full Monty (NO room settings) ===');
log(`Store: ${STORE}`);
const pg = new Client({ user: process.env.PGUSER || 'stevestudio2', database: process.env.PGDATABASE || 'dw_unified', host: process.env.PGHOST || '/tmp' });
await pg.connect();
const cohortSql = `SELECT shopify_id, handle, vendor FROM shopify_products
WHERE created_at_shopify >= now()-interval '20 days' AND full_monty_at IS NULL
ORDER BY vendor, shopify_id` + (LIMIT ? ` LIMIT ${LIMIT}` : '');
const { rows } = await pg.query(cohortSql);
log(`Cohort to process: ${rows.length}${LIMIT ? ` (limited)` : ''}`);
const stats = { total: rows.length, specs: 0, colors: 0, titles: 0, alt: 0, done: 0, errors: 0 };
for (let i = 0; i < rows.length; i++) {
try {
const fetchR = await gql(PRODUCT_QUERY(rows[i].shopify_id));
const product = fetchR?.data?.product;
if (!product) { log(`\n[${i + 1}/${rows.length}] ${rows[i].shopify_id} — NOT FOUND on Shopify, skipping`); stats.errors++; continue; }
const result = await processProduct(product, i, rows.length, pg);
stats.specs += result.specs;
if (result.color) stats.colors++;
if (result.title) stats.titles++;
stats.alt += result.alt;
stats.errors += result.errors.length;
if (!result.errors.some(e => /Specs|Title|Alt/.test(e))) stats.done++;
} catch (e) {
log(` FATAL on row ${i + 1}: ${e.message}`); stats.errors++;
}
await sleep(250);
if ((i + 1) % 25 === 0) {
log(`\n--- PROGRESS ${i + 1}/${rows.length} (${((i + 1) / rows.length * 100).toFixed(1)}%) | done:${stats.done} specs:${stats.specs} colors:${stats.colors} titles:${stats.titles} alt:${stats.alt} errors:${stats.errors} ---\n`);
}
}
await pg.end();
log(`\n=== COMPLETE === total:${stats.total} done:${stats.done} specs:${stats.specs} colors:${stats.colors} titles:${stats.titles} alt:${stats.alt} errors:${stats.errors} Finished ${new Date().toISOString()}\n`);
}
main().catch(e => { log(`FATAL: ${e.message}\n${e.stack}`); process.exit(1); });