← back to Tk 11331 Exec
d3b_addvariant.mjs
190 lines
// TK-11331 DECISION-3b (follow-on A) — ADD a sellable "Sold Per Yard" variant to each of the
// 18 recovered Momentum "Don't Cross Me" (Phillipe Romano private-label) products that are
// currently ACTIVE + SAMPLE-ONLY (single $4.25 Sample variant, no sellable per-yard variant).
//
// Per-product, in ONE tight loop (create -> reorder -> metafields -> verify), it:
// 1. CREATE a sellable variant option "Size" = "Sold Per Yard", SKU = bare dw_sku,
// price = recovered_hw_price ($50.61), inventoryItem.tracked=true, inventoryPolicy=CONTINUE,
// taxable=true (mirrors the LIVE healthy sibling XXP-969035 exactly).
// 2. REORDER via productVariantsBulkReorder so the SELLABLE variant is POSITION 1 and the
// $4.25 Sample is demoted to POSITION 2 — CRITICAL per memory
// `adding-sellable-variant-must-take-position-1`: a sellable variant left at pos 2 makes
// JSON-LD/GMC advertise $4.25 (the exact D1 leak). We never "append and fix later" beyond
// the ~1s window inside this loop; and the product currently advertises no sellable price
// at all, so the intermediate state is no worse than current.
// 3. APPLY the D2 offer rules global.v_prods_quantity_order_min="5" and
// global.unit_of_measure="Sold Per Yard" (PG mirror first per rule 5, then Shopify).
// Self-contained copy of the D2 pattern — does NOT import or touch any d2_* file.
// 4. VERIFY Admin re-read (sellable @ pos1) AND a storefront .json corroboration
// (variants[0].price == 50.61, variants[0].title != "Sample").
//
// DEDUP: the loop keys on shopify_product_id (NEVER mfr_sku), so mfr_sku T2-DM-11 -> BOTH
// XXP-969201 (pid 6936687771699) and XXP-969204 (pid 6936687837235) each get a variant.
//
// Records a full restore map (data/d3b-addvariant-restore.jsonl) BEFORE each write so the
// companion d3b_addvariant_rollback.mjs is one-command reversible.
//
// DRY-RUN by default. --apply to write. --limit=N to cap. --offset=N to slice.
// $0 local except the Shopify Admin API calls (free) — no paid API.
import fs from 'fs';
import {execSync} from 'child_process';
import {gql,sleep} from './lib.mjs';
const APPLY=process.argv.includes('--apply');
const LIMIT=parseInt((process.argv.find(a=>a.startsWith('--limit='))||'').split('=')[1]||'99999');
const OFFSET=parseInt((process.argv.find(a=>a.startsWith('--offset='))||'').split('=')[1]||'0');
const SRC='data/d3b-recovered-needs-variant.jsonl';
const RESTORE='data/d3b-addvariant-restore.jsonl';
const PUBLIC_HOST='designerwallcoverings.com';
const BAND_LO=15.0, BAND_HI=72.0; // same price sanity band the D3b builder used
const SAMPLE_CENTS=425;
const all=fs.readFileSync(SRC,'utf8').trim().split('\n').filter(Boolean).map(l=>JSON.parse(l));
const batch=all.slice(OFFSET,OFFSET+LIMIT);
console.log(`${APPLY?'APPLY':'DRY-RUN'} D3b add-variant — ${batch.length} products (of ${all.length}); offset=${OFFSET} limit=${LIMIT}`);
const isSample=s=>((s||'').toLowerCase().endsWith('sample'));
const psql=sql=>execSync(`psql -h /tmp -d dw_unified -Atc ${JSON.stringify(sql)}`,{encoding:'utf8'}).trim();
// --- GraphQL ---
const READ=`query($id:ID!){product(id:$id){id title handle status
options{name}
mfmin:metafield(namespace:"global",key:"v_prods_quantity_order_min"){value}
mfuom:metafield(namespace:"global",key:"unit_of_measure"){value}
variants(first:20){nodes{id sku title position price selectedOptions{name value}}}}}`;
const CREATE=`mutation($pid:ID!,$variants:[ProductVariantsBulkInput!]!){
productVariantsBulkCreate(productId:$pid,variants:$variants,strategy:DEFAULT){
productVariants{id sku title price position selectedOptions{name value}}
userErrors{field message} }}`;
const REORDER=`mutation($pid:ID!,$moves:[ProductVariantPositionInput!]!){
productVariantsBulkReorder(productId:$pid,positions:$moves){userErrors{field message}}}`;
const SETMF=`mutation($mf:[MetafieldsSetInput!]!){metafieldsSet(metafields:$mf){
metafields{namespace key value} userErrors{field message}}}`;
const VERIFY=`query($id:ID!){product(id:$id){handle
mfmin:metafield(namespace:"global",key:"v_prods_quantity_order_min"){value}
mfuom:metafield(namespace:"global",key:"unit_of_measure"){value}
variants(first:20){nodes{id sku title position price}}}}`;
async function storefrontLeadPrice(handle){
// Corroborate on the RENDERED storefront (ground truth for JSON-LD / GMC). CDN can lag,
// so retry a couple times; a persistent mismatch is a WARN (Admin re-read is the hard gate).
for(let i=0;i<3;i++){
try{
const r=await fetch(`https://${PUBLIC_HOST}/products/${handle}.json`,{headers:{'Accept':'application/json'}});
if(r.ok){ const j=await r.json(); const v=(j.product?.variants||[])[0];
if(v) return {price:Number(v.price),title:v.title,sku:v.sku}; }
}catch(_){}
await sleep(3000);
}
return null;
}
const rstream=APPLY?fs.createWriteStream(RESTORE,{flags:'a'}):null;
let created=0,fail=0,skip=0,sfwarn=0; const fails=[]; const results=[];
for(const row of batch){
const pid=String(row.shopify_product_id);
const gid=`gid://shopify/Product/${pid}`;
const dwsku=row.dw_sku; // bare sellable SKU, e.g. XXP-969191
const price=Number(row.recovered_hw_price ?? row.recovered_new_price);
const tag=`${dwsku} (${row.mfr_sku})`;
if(!(price>0) || !(BAND_LO<=price && price<=BAND_HI)){
skip++; console.log(` SKIP ${tag} — price ${price} outside $${BAND_LO}-${BAND_HI}/yd band`); continue;
}
// authoritative LIVE read right before write
const p=(await gql(READ,{id:gid})).product;
if(!p){ fail++; fails.push({pid,tag,reason:'product-not-found'}); console.log(` ERR ${tag} product not found`); continue; }
const vs=p.variants.nodes.slice().sort((a,b)=>a.position-b.position);
const sellableExisting=vs.filter(v=>!isSample(v.sku));
const sample=vs.find(v=>isSample(v.sku));
// GUARDS — must be exactly the sample-only shape we expect
if(sellableExisting.length){ skip++; console.log(` SKIP ${tag} — already has a sellable variant (${sellableExisting[0].sku}); nothing to add`); continue; }
if(!sample){ fail++; fails.push({pid,tag,reason:'no-sample-variant'}); console.log(` ERR ${tag} — no Sample variant present`); continue; }
if(Math.round(parseFloat(sample.price)*100)!==SAMPLE_CENTS){ skip++; console.log(` SKIP ${tag} — sample price ${sample.price} != 4.25 (unexpected shape)`); continue; }
const optName=(p.options?.[0]?.name)||'Size';
if(optName!=='Size') console.log(` NOTE ${tag} — option name is "${optName}" (expected "Size"); using it as-is`);
const needMin = p.mfmin?.value!=='5';
const needUom = p.mfuom?.value!=='Sold Per Yard';
if(!APPLY){
console.log(` would ADD sellable ${dwsku} "Sold Per Yard" @ $${price.toFixed(2)} -> pos1, demote ${sample.sku} -> pos2`
+ `${needMin?' | +min=5':''}${needUom?' | +uom=Sold Per Yard':''}`);
created++; continue;
}
// ---- restore record BEFORE any write ----
const restoreRec={ts:new Date().toISOString(),pid,gid,handle:p.handle,dw_sku:dwsku,mfr_sku:row.mfr_sku,
sample_variant_id:sample.id,
order_before:vs.map(v=>({id:v.id,sku:v.sku,position:v.position})), // was: Sample @ pos1 only
mf_old:{min:p.mfmin?.value??null,uom:p.mfuom?.value??null},
mf_wrote:{min:needMin?'5':null,uom:needUom?'Sold Per Yard':null},
created_variant_id:null, // filled in after create so rollback can delete it
undo:'delete created_variant_id via productVariantsBulkDelete; delete created global.min/uom metafields (old was null); order auto-restores to Sample@pos1'};
// 1) CREATE sellable variant (lands at pos2 by default; reordered next step)
const cvars=[{price:price.toFixed(2),taxable:true,inventoryPolicy:'CONTINUE',
inventoryItem:{sku:dwsku,tracked:true},
optionValues:[{optionName:optName,name:'Sold Per Yard'}]}];
const cres=await gql(CREATE,{pid:gid,variants:cvars});
const cue=cres.productVariantsBulkCreate.userErrors;
if(cue.length){ fail++; fails.push({pid,tag,stage:'create',ue:cue}); console.log(` CREATE-ERR ${tag}`,JSON.stringify(cue)); continue; }
const newV=cres.productVariantsBulkCreate.productVariants.find(v=>!isSample(v.sku));
if(!newV){ fail++; fails.push({pid,tag,stage:'create',reason:'created-variant-missing'}); console.log(` CREATE-ERR ${tag} created variant not returned`); continue; }
restoreRec.created_variant_id=newV.id;
rstream.write(JSON.stringify(restoreRec)+'\n'); // persist immediately so a mid-loop crash is still reversible
await sleep(400);
// 2) REORDER — sellable pos1, Sample pos2
const moves=[{id:newV.id,position:1},{id:sample.id,position:2}];
const rres=await gql(REORDER,{pid:gid,moves});
const rue=rres.productVariantsBulkReorder.userErrors;
if(rue.length){ fail++; fails.push({pid,tag,stage:'reorder',ue:rue}); console.log(` REORDER-ERR ${tag}`,JSON.stringify(rue)); continue; }
await sleep(400);
// 3) METAFIELDS (D2 offer rules) — PG mirror first (rule 5), then Shopify authoritative
if(needMin||needUom){
let expr='coalesce(metafields,\'{}\'::jsonb)';
if(needMin) expr=`jsonb_set(${expr},'{global,v_prods_quantity_order_min}','{"type":"single_line_text_field","value":"5"}'::jsonb,true)`;
if(needUom) expr=`jsonb_set(${expr},'{global,unit_of_measure}','{"type":"single_line_text_field","value":"Sold Per Yard"}'::jsonb,true)`;
try{ psql(`update shopify_products set metafields=${expr} where shopify_id='${gid}';`); }
catch(e){ fail++; fails.push({pid,tag,stage:'pg-metafield',err:String(e).slice(0,200)}); console.log(` PG-ERR ${tag}`); continue; }
const mf=[];
if(needMin) mf.push({ownerId:gid,namespace:'global',key:'v_prods_quantity_order_min',type:'single_line_text_field',value:'5'});
if(needUom) mf.push({ownerId:gid,namespace:'global',key:'unit_of_measure',type:'single_line_text_field',value:'Sold Per Yard'});
const mres=await gql(SETMF,{mf});
const mue=mres.metafieldsSet.userErrors;
if(mue.length){ fail++; fails.push({pid,tag,stage:'shopify-metafield',ue:mue}); console.log(` MF-ERR ${tag}`,JSON.stringify(mue)); continue; }
await sleep(350);
}
// 4) VERIFY — Admin re-read (hard gate) + storefront corroboration (WARN if CDN-lagged)
const after=(await gql(VERIFY,{id:gid})).product;
const av=after.variants.nodes.slice().sort((a,b)=>a.position-b.position);
const pos1=av[0];
const okPos = pos1 && !isSample(pos1.sku) && Math.abs(Number(pos1.price)-price)<0.005;
const okMin = !needMin || after.mfmin?.value==='5';
const okUom = !needUom || after.mfuom?.value==='Sold Per Yard';
if(!(okPos&&okMin&&okUom)){
fail++; fails.push({pid,tag,stage:'verify',pos1:pos1&&{sku:pos1.sku,price:pos1.price},min:after.mfmin?.value,uom:after.mfuom?.value});
console.log(` VERIFY-FAIL ${tag} pos1=${pos1?.sku}@${pos1?.price} min=${after.mfmin?.value} uom=${after.mfuom?.value}`); continue;
}
const sf=await storefrontLeadPrice(after.handle);
let sfNote='sf:unchecked';
if(sf){
const sfLeak = !(Math.abs(sf.price-price)<0.005) || /sample/i.test(sf.title||'');
if(sfLeak){ sfwarn++; sfNote=`sf-WARN lead=${sf.price}/${sf.title}`; console.log(` SF-WARN ${tag} storefront lead price=${sf.price} title="${sf.title}" (CDN lag? re-verify)`); }
else sfNote=`sf-ok lead=${sf.price}`;
}
created++; results.push({pid,dw_sku:dwsku,mfr_sku:row.mfr_sku,created_variant_id:newV.id,price,sfNote});
console.log(` OK ${tag} — sellable ${dwsku}@$${price.toFixed(2)} pos1, ${sample.sku} pos2 | ${sfNote}`);
await sleep(200);
}
if(rstream) rstream.end();
console.log(`\nD3b ${APPLY?'APPLIED':'DRY'} — created=${created} fail=${fail} skip=${skip} storefront-warn=${sfwarn}`);
if(fails.length) fs.writeFileSync('data/d3b-addvariant-fails.json',JSON.stringify(fails,null,1));
if(results.length) fs.writeFileSync('data/d3b-addvariant-results.json',JSON.stringify(results,null,1));