← back to Letsbegin
rw-cost-write.js
239 lines
#!/usr/bin/env node
/**
* rw-cost-write.js — Write the portal-confirmed C3 per-m² COST metafield onto
* every Rebel Walls custom mural in the sandbox store.
*
* Steve approved ("go"). Cost confirmed live from partner.gimmersta.com trade
* portal (Rebel Walls shares Sandberg/Gimmersta group price list):
* C3 standard non-woven: RRP $76.40/m² → Net (our cost, excl tax) = $44.50/m².
*
* Writes (namespace `custom`) on each product:
* - custom.cost_per_m2 = 44.50 (number_decimal — no existing def, safe)
* - custom.cost_currency = USD (single_line_text_field)
* - custom.price_group = C3 (single_line_text_field)
* - custom.cost_basis = <provenance string> (multi_line_text_field)
*
* Does NOT touch: color/title/tag metafields, Shopify variant price/compare-at,
* or any non-RW product. Cohort is locked by the dw_unified query below.
*
* Idempotent + resumable: re-fetches each product's existing custom.* metafields
* first; if all 4 already match the target values it SKIPs the write. So a
* re-run only writes the rows that aren't already correct.
*
* Rate-limited (~2 req/s) with the same Throttled-backoff as enrich-write.js.
*
* Usage:
* node rw-cost-write.js --validate # write+verify first 3 only, print before/after
* node rw-cost-write.js # run all 1000
* node rw-cost-write.js --limit N # run first N
* node rw-cost-write.js --reqa # re-fetch ~15 and print cost_per_m2/price_group, no writes
*
* Env: SHOPIFY_ADMIN_TOKEN (loaded from secrets-manager/.env). PG defaults
* user stevestudio2, db dw_unified, local socket.
*/
const https = require('https');
const fs = require('fs');
const { Client } = require('pg');
// ---------- env (parse secrets-manager/.env; zsh-source-unsafe, so do it here) ----------
function loadEnv(path) {
try {
for (const line of fs.readFileSync(path, 'utf8').split('\n')) {
const m = line.match(/^\s*([A-Z0-9_]+)\s*=\s*(.*)$/);
if (!m) continue;
let v = m[2].trim();
if ((v.startsWith('"') && v.endsWith('"')) || (v.startsWith("'") && v.endsWith("'"))) v = v.slice(1, -1);
if (process.env[m[1]] == null) process.env[m[1]] = v;
}
} catch { /* file optional if env already set */ }
}
loadEnv('/Users/macstudio3/Projects/secrets-manager/.env');
const STORE = process.env.SHOPIFY_STORE || 'designer-laboratory-sandbox.myshopify.com';
const TOKEN = process.env.SHOPIFY_ADMIN_TOKEN || process.env.SHOPIFY_PRODUCT_TOKEN;
const API = '/admin/api/2024-10/graphql.json';
if (!TOKEN) { console.error('FATAL: SHOPIFY_ADMIN_TOKEN env var required'); process.exit(1); }
// HARD GUARD: sandbox store only.
if (STORE !== 'designer-laboratory-sandbox.myshopify.com') {
console.error(`FATAL: refusing to run against non-sandbox store "${STORE}"`); process.exit(1);
}
// ---------- target values (portal-confirmed) ----------
const COST_PER_M2 = '44.50';
const COST_CURRENCY = 'USD';
const PRICE_GROUP = 'C3';
const COST_BASIS = 'C3 standard non-woven; Net excl tax from partner.gimmersta.com 2026-06; RRP $76.40/m2; ~41.8% off RRP. Provisional — C2/C4/P&S material variants pending portal Net.';
// ---------- cli ----------
const args = process.argv.slice(2);
const VALIDATE = args.includes('--validate');
const REQA = args.includes('--reqa');
const limIdx = args.indexOf('--limit');
const LIMIT = limIdx >= 0 ? parseInt(args[limIdx + 1], 10) : null;
function sleep(ms) { return new Promise(r => setTimeout(r, ms)); }
// ---------- Shopify GraphQL (gqlRaw/gql backoff lifted from enrich-write.js) ----------
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 = 5) {
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) { await sleep(3000); continue; }
if (lowBudget) await sleep(1500);
return result;
} catch (e) {
if (attempt < retries) { await sleep(attempt * 2000); continue; }
throw e;
}
}
}
const MF_MUTATION = 'mutation metafieldsSet($m: [MetafieldsSetInput!]!) { metafieldsSet(metafields: $m) { metafields { key } userErrors { message field } } }';
// Fetch only the 4 cost-related custom metafields for a product (cheap, targeted).
async function fetchCost(pid) {
const r = await gql({ query: `{ product(id:"${pid}") {
id title vendor
mfA: metafield(namespace:"custom", key:"cost_per_m2"){ value }
mfB: metafield(namespace:"custom", key:"cost_currency"){ value }
mfC: metafield(namespace:"custom", key:"price_group"){ value }
mfD: metafield(namespace:"custom", key:"cost_basis"){ value }
} }` });
const p = r?.data?.product;
if (!p) return null;
return {
id: p.id, title: p.title, vendor: p.vendor,
cost_per_m2: p.mfA?.value ?? null,
cost_currency: p.mfB?.value ?? null,
price_group: p.mfC?.value ?? null,
cost_basis: p.mfD?.value ?? null,
};
}
function alreadyCorrect(c) {
// number_decimal normalizes "44.50" -> "44.5"; accept either spelling.
const numOk = c.cost_per_m2 === COST_PER_M2 || c.cost_per_m2 === '44.5';
return numOk && c.cost_currency === COST_CURRENCY && c.price_group === PRICE_GROUP && c.cost_basis === COST_BASIS;
}
async function writeCost(pid) {
const mf = [
{ ownerId: pid, namespace: 'custom', key: 'cost_per_m2', value: COST_PER_M2, type: 'number_decimal' },
{ ownerId: pid, namespace: 'custom', key: 'cost_currency', value: COST_CURRENCY, type: 'single_line_text_field' },
{ ownerId: pid, namespace: 'custom', key: 'price_group', value: PRICE_GROUP, type: 'single_line_text_field' },
{ ownerId: pid, namespace: 'custom', key: 'cost_basis', value: COST_BASIS, type: 'multi_line_text_field' },
];
const r = await gql({ query: MF_MUTATION, variables: { m: mf } });
const errs = r?.data?.metafieldsSet?.userErrors || [];
return errs.map(e => `${(e.field || []).join('.')}: ${e.message}`);
}
// ---------- cohort from dw_unified (RW murals, last 20 days) ----------
async function loadCohort() {
const pg = new Client({ user: process.env.PGUSER || 'stevestudio2', database: process.env.PGDATABASE || 'dw_unified', host: process.env.PGHOST || '/tmp' });
await pg.connect();
try {
const res = await pg.query(
`SELECT shopify_id FROM shopify_products
WHERE created_at_shopify >= now() - interval '20 days'
AND vendor = 'Rebel Walls'
ORDER BY shopify_id`
);
return res.rows.map(r => r.shopify_id).filter(Boolean);
} finally { await pg.end(); }
}
async function main() {
let ids = await loadCohort();
console.log(`[rw-cost-write] cohort = ${ids.length} Rebel Walls murals (sandbox)`);
if (ids.length === 0) { console.error('FATAL: cohort empty'); process.exit(1); }
// ---- --reqa: re-fetch a 15-item sample, print, no writes ----
if (REQA) {
const step = Math.max(1, Math.floor(ids.length / 15));
const sample = ids.filter((_, i) => i % step === 0).slice(0, 15);
console.log(`[re-QA] sampling ${sample.length} products...`);
let ok = 0, bad = 0;
for (const pid of sample) {
const c = await fetchCost(pid);
const good = c && (c.cost_per_m2 === '44.5' || c.cost_per_m2 === '44.50') && c.price_group === 'C3';
if (good) ok++; else bad++;
console.log(` ${good ? 'OK ' : 'BAD'} ${pid} cost_per_m2=${c?.cost_per_m2} price_group=${c?.price_group} cur=${c?.cost_currency} vendor=${c?.vendor}`);
await sleep(450);
}
console.log(`[re-QA] ${ok}/${sample.length} confirmed cost_per_m2=44.50 & price_group=C3, ${bad} bad`);
return;
}
if (VALIDATE) ids = ids.slice(0, 3);
else if (LIMIT) ids = ids.slice(0, LIMIT);
let written = 0, skipped = 0, failed = 0;
const failures = [];
for (let i = 0; i < ids.length; i++) {
const pid = ids[i];
const before = await fetchCost(pid);
if (!before) { failed++; failures.push(`${pid}: product not found`); console.log(` [${i + 1}/${ids.length}] ${pid} NOT FOUND`); continue; }
// Guardrail: never touch a non-RW product even if the cohort query drifted.
if (before.vendor !== 'Rebel Walls') {
failed++; failures.push(`${pid}: vendor="${before.vendor}" not Rebel Walls — skipped`);
console.log(` [${i + 1}/${ids.length}] ${pid} GUARD: vendor="${before.vendor}" not RW, skipped`);
continue;
}
if (alreadyCorrect(before)) {
skipped++;
if (VALIDATE || i % 100 === 0) console.log(` [${i + 1}/${ids.length}] ${pid} already correct, skip`);
continue;
}
const errs = await writeCost(pid);
await sleep(350); // ~2 req/s (fetch + write per item)
if (errs.length) {
failed++; failures.push(`${pid}: ${errs.join('; ')}`);
console.log(` [${i + 1}/${ids.length}] ${pid} ERROR: ${errs.join('; ')}`);
continue;
}
if (VALIDATE) {
const after = await fetchCost(pid);
console.log(` VALIDATE ${pid}`);
console.log(` BEFORE: cost_per_m2=${before.cost_per_m2} price_group=${before.price_group} cur=${before.cost_currency}`);
console.log(` AFTER : cost_per_m2=${after.cost_per_m2} price_group=${after.price_group} cur=${after.cost_currency} basis=${after.cost_basis ? 'set' : 'MISSING'}`);
const ok = (after.cost_per_m2 === '44.5' || after.cost_per_m2 === '44.50') && after.price_group === 'C3' && after.cost_currency === 'USD' && !!after.cost_basis;
if (!ok) { failed++; failures.push(`${pid}: post-write verify failed`); }
else written++;
await sleep(350);
} else {
written++;
if (i % 50 === 0 || i === ids.length - 1) console.log(` [${i + 1}/${ids.length}] progress: written=${written} skipped=${skipped} failed=${failed}`);
}
}
console.log(`\n=== DONE (${VALIDATE ? 'VALIDATE' : 'FULL'}) ===`);
console.log(`cohort=${ids.length} written=${written} skipped(already-correct)=${skipped} failed=${failed}`);
if (failures.length) { console.log('FAILURES:'); failures.slice(0, 30).forEach(f => console.log(' - ' + f)); }
if (failed > 0) process.exit(1);
}
main().catch(e => { console.error(`FATAL: ${e.message}\n${e.stack}`); process.exit(1); });