← back to Gmc Titlefix
gmc-price-override.js
201 lines
// GMC PRICE OVERRIDE (Option A) — fix the $4.25 leak on LIVE Google offers by riding a
// SUPPLEMENTAL price override on top of the Shopify channel's offers (same offerId → no
// duplicate listing). For every live Google offer currently priced $4.25 whose product has
// a real (higher) price in the controlled feed, override the price to the real/highest value.
// Sample-only products (feed price IS $4.25) are left at $4.25 (Steve policy).
//
// node gmc-price-override.js # DRY-RUN: build override list from feed⋈MC, no writes
// node gmc-price-override.js --dwla-pid-join # DRY-RUN (DWLA only): build override list via pid join, no writes
// node gmc-price-override.js --create-ds # GATED: create the supplemental datasource (prints + PERSISTS its name)
// node gmc-price-override.js --apply [<DS>] # GATED: push price overrides. <DS> optional — resolves
// # explicit arg → .price-override-ds file → DEFAULT_DS.
// # (no placeholder to substitute → auto-executor safe)
//
// Cost: $0 (Google Content/Merchant API + local feed read; no per-call charge).
const { token, MERCHANT } = require('./_auth.js');
const fs = require('fs');
const { execFileSync } = require('child_process');
const FEED_TSV = '/Users/macstudio3/Projects/designerwallcoverings/today-viewer/data/google-merchant-feed.tsv';
const OUT = '/Users/macstudio3/.claude/yolo-queue/gmc-price-override-list.json';
// Separate output for the DWLA pid-join path so it never clobbers the default SKU-join list.
const OUT_DWLA = '/Users/macstudio3/.claude/yolo-queue/gmc-price-override-dwla-list.json';
const args = process.argv.slice(2);
const CREATE_DS = args.includes('--create-ds');
const APPLY = args.includes('--apply');
// Where --create-ds persists the datasource name so a later --apply needs NO argument.
const DS_FILE = '/Users/macstudio3/.claude/yolo-queue/gmc-price-override-ds.txt';
// The datasource already created live on 2026-07-27 (step b of the approval run). Fallback of last resort.
const DEFAULT_DS = 'accounts/146735262/dataSources/10693978453';
// Resolve the datasource for --apply WITHOUT depending on a shell-substituted <DS> placeholder:
// 1) an explicit, non-flag arg after --apply 2) the persisted DS_FILE 3) DEFAULT_DS.
function resolveDS() {
const a = args[args.indexOf('--apply') + 1];
if (a && !a.startsWith('--')) return a;
try { const f = fs.readFileSync(DS_FILE, 'utf8').trim(); if (f) return f; } catch {}
return DEFAULT_DS;
}
const DS = APPLY ? resolveDS() : null;
// OPT-IN, guarded DWLA path — pid-based feed↔MC join (see 2026-07-16 memo EXECUTION LOG).
// Off by default; does NOT change the default SKU/mpn-join dry-run behavior above.
const DWLA_PID_JOIN = args.includes('--dwla-pid-join');
const sleep = ms => new Promise(r => setTimeout(r, ms));
// 1) Load real/highest price per SKU from the controlled feed (id/mpn = sku, price = "X.XX USD").
function loadFeedPrices() {
const lines = fs.readFileSync(FEED_TSV, 'utf8').split('\n');
const cols = lines[0].split('\t');
const iId = cols.indexOf('id'), iPrice = cols.indexOf('price'), iMpn = cols.indexOf('mpn');
const map = new Map(); // sku -> max real price seen
for (let i = 1; i < lines.length; i++) {
if (!lines[i]) continue;
const f = lines[i].split('\t');
const price = parseFloat((f[iPrice] || '').split(' ')[0]);
if (isNaN(price)) continue;
for (const key of [f[iId], f[iMpn]]) {
if (!key) continue;
if (!map.has(key) || price > map.get(key)) map.set(key, price);
}
}
return map;
}
// --- DWLA pid-join path (OPT-IN via --dwla-pid-join) -----------------------------------------
// The controlled feed keys on SKU (DWLA-XXXXXX-Yard) but the live MC offers carry an empty mpn
// and an offerId of the form shopify_US_<pid>_<vid>, so the default feed.get(mpn||offerId) join
// misses every DWLA offer. The bridge that DOES exist is the Shopify product numeric id (pid):
// it is embedded in the MC offerId AND present in dw_unified.justin_david_pricing_2026.shopify_id
// (gid://shopify/Product/<pid>), which also carries the authoritative retail_yd. So we join on pid.
// Build pid -> retail_yd from dw_unified.justin_david_pricing_2026 (apply_status='ok').
// Read-only local PG via psql (no pg module dependency; host=/tmp socket, dbname=dw_unified).
function loadDwlaPidRetailMap() {
const sql = "SELECT regexp_replace(shopify_id, '^gid://shopify/Product/', '') AS pid, retail_yd "
+ "FROM justin_david_pricing_2026 "
+ "WHERE apply_status='ok' AND shopify_id ~ '^gid://shopify/Product/[0-9]+$';";
const out = execFileSync('psql', ['host=/tmp dbname=dw_unified', '-tA', '-F', '\t', '-c', sql],
{ encoding: 'utf8', maxBuffer: 32 * 1024 * 1024 });
const map = new Map(); // pid (string) -> retail_yd (number)
for (const line of out.split('\n')) {
if (!line.trim()) continue;
const [pid, retail] = line.split('\t');
const r = parseFloat(retail);
if (pid && !isNaN(r)) map.set(pid, r);
}
return map;
}
// Extract <pid> from a MC offerId shaped shopify_US_<pid>_<vid>.
function pidFromOfferId(offerId) {
const m = /^shopify_US_(\d+)_/.exec(offerId || '');
return m ? m[1] : null;
}
// DRY-RUN (DWLA only, pid join): mark an override when the offer's pid is a DWLA pid AND the offer
// price <= 4.26 AND retail_yd > 4.26. Writes to OUT_DWLA (never the default OUT). NO writes to MC.
async function dryRunDwlaPidJoin() {
const pidMap = loadDwlaPidRetailMap();
console.log(`Loaded ${pidMap.size} DWLA pid→retail_yd entries from justin_david_pricing_2026 (apply_status='ok').`);
const offers = await listMcOffers();
console.log(`Read ${offers.length} live Google offers.`);
const overrides = [];
let dwlaAlreadyReal = 0, dwlaSampleLeak = 0, nonDwla = 0, dwlaNoRetail = 0;
for (const o of offers) {
const pid = pidFromOfferId(o.offerId);
if (!pid || !pidMap.has(pid)) { nonDwla++; continue; } // not a DWLA offer — leave untouched
const retail = pidMap.get(pid);
if (o.price > 4.26) { dwlaAlreadyReal++; continue; } // DWLA offer already at a real price
if (!(retail > 4.26)) { dwlaNoRetail++; continue; } // no real per-yard price to promote to
dwlaSampleLeak++;
overrides.push({ offerId: o.offerId, pid, mpn: o.mpn, contentLanguage: o.contentLanguage, feedLabel: o.feedLabel, currentPrice: o.price, realPrice: retail, title: o.title });
}
fs.writeFileSync(OUT_DWLA, JSON.stringify({ generated_at: new Date().toISOString(), join: 'dwla-pid', total_offers: offers.length, dwla_pids_in_map: pidMap.size, overrides_needed: overrides.length, dwla_already_real_price: dwlaAlreadyReal, dwla_no_retail: dwlaNoRetail, non_dwla_offers: nonDwla, overrides }, null, 2));
console.log(`\n[DWLA pid-join] OVERRIDES NEEDED ($4.25 DWLA offers → per-yard retail): ${overrides.length}`);
console.log(` DWLA offers already showing a real price: ${dwlaAlreadyReal}`);
console.log(` DWLA offers with no real per-yard retail (>4.26): ${dwlaNoRetail}`);
console.log(` non-DWLA offers left untouched: ${nonDwla}`);
console.log(`Wrote ${OUT_DWLA}`);
console.log('--- sample DWLA overrides ---');
overrides.slice(0, 8).forEach(o => console.log(` ${o.offerId} pid ${o.pid} $${o.currentPrice} → $${o.realPrice} ${o.title}`));
console.log('\nNOTE: no MC writes performed. To apply, Steve gates: --create-ds then --apply <DS> (apply reads the default OUT list; point it at OUT_DWLA or copy first).');
}
// --- end DWLA pid-join path -------------------------------------------------------------------
async function listMcOffers() {
const tok = await token(); const H = { Authorization: 'Bearer ' + tok };
const offers = []; let page = null, scanned = 0;
do {
const r = await (await fetch(`https://shoppingcontent.googleapis.com/content/v2.1/${MERCHANT}/products?maxResults=250` + (page ? `&pageToken=${page}` : ''), { headers: H })).json();
if (r.error) { console.error('MC list err:', JSON.stringify(r.error).slice(0, 200)); break; }
for (const p of (r.resources || [])) {
scanned++;
offers.push({ offerId: p.offerId, mpn: p.mpn || '', price: parseFloat(p.price?.value || '0'), feedLabel: p.feedLabel || 'US', contentLanguage: p.contentLanguage || 'en', title: (p.title || '').slice(0, 50) });
}
page = r.nextPageToken;
if (scanned % 5000 === 0) process.stderr.write(` ...${scanned} MC offers read\n`);
} while (page);
return offers;
}
async function createDataSource() {
const tok = await token();
const url = `https://merchantapi.googleapis.com/datasources/v1/accounts/${MERCHANT}/dataSources`;
for (const supp of [{}, { contentLanguage: 'en' }, { contentLanguage: 'en', feedLabel: 'US' }]) {
const body = { displayName: 'DW Real-Price Overrides', supplementalProductDataSource: supp };
const r = await fetch(url, { method: 'POST', headers: { Authorization: 'Bearer ' + tok, 'Content-Type': 'application/json' }, body: JSON.stringify(body) });
const j = await r.json();
if (r.status >= 200 && r.status < 300 && j.name) {
try { fs.writeFileSync(DS_FILE, j.name + '\n'); } catch (e) { console.error(' (warn) could not persist DS to', DS_FILE, e.message); }
console.log('DATASOURCE CREATED:\n' + j.name + '\n(persisted to ' + DS_FILE + ' — `--apply` will now find it with no argument)');
return;
}
console.error(' shape', JSON.stringify(supp), '->', r.status, (j.error?.message || '').slice(0, 120));
}
console.error('all datasource shapes failed');
}
async function applyOverrides(ds) {
const list = JSON.parse(fs.readFileSync(OUT, 'utf8')).overrides;
let tok = await token(), tokAt = Date.now(), ok = 0, fail = 0;
console.log(`Pushing ${list.length} price overrides → ${ds}`);
for (let i = 0; i < list.length; i++) {
if (Date.now() - tokAt > 50 * 60 * 1000) { tok = await token(); tokAt = Date.now(); }
const row = list[i];
const url = `https://merchantapi.googleapis.com/products/v1/accounts/${MERCHANT}/productInputs:insert?dataSource=${encodeURIComponent(ds)}`;
const body = { offerId: row.offerId, contentLanguage: row.contentLanguage, feedLabel: row.feedLabel, productAttributes: { price: { amountMicros: String(Math.round(row.realPrice * 1e6)), currencyCode: 'USD' } } };
const r = await fetch(url, { method: 'POST', headers: { Authorization: 'Bearer ' + tok, 'Content-Type': 'application/json' }, body: JSON.stringify(body) });
if (r.ok) ok++; else { fail++; if (fail <= 10) console.error('FAIL', row.offerId, r.status, (await r.text()).slice(0, 120)); }
if (i % 500 === 0) console.log(` ${i}/${list.length} | ok ${ok} fail ${fail}`);
if (r.status === 429) await sleep(3000);
}
console.log(`DONE: ok ${ok} fail ${fail} of ${list.length}`);
}
(async () => {
if (CREATE_DS) return createDataSource();
if (APPLY) { if (!DS) { console.error('need datasource name: --apply <DS>'); process.exit(1); } return applyOverrides(DS); }
if (DWLA_PID_JOIN) return dryRunDwlaPidJoin(); // OPT-IN DWLA pid-join dry-run (no writes)
// DRY-RUN: build the override list (feed ⋈ MC)
const feed = loadFeedPrices();
console.log(`Loaded ${feed.size} SKU→real-price entries from the controlled feed.`);
const offers = await listMcOffers();
console.log(`Read ${offers.length} live Google offers.`);
const overrides = []; let leakNoMatch = 0, sampleOnlyOk = 0, alreadyReal = 0;
for (const o of offers) {
if (o.price > 4.26) { alreadyReal++; continue; } // already showing real price
const real = feed.get(o.mpn) || feed.get(o.offerId);
if (real === undefined) { if (o.price <= 4.26) leakNoMatch++; continue; } // can't map — skip (needs review)
if (real > 4.26) overrides.push({ offerId: o.offerId, mpn: o.mpn, contentLanguage: o.contentLanguage, feedLabel: o.feedLabel, currentPrice: o.price, realPrice: real, title: o.title });
else sampleOnlyOk++; // feed price is also $4.25 → legit sample-only
}
fs.writeFileSync(OUT, JSON.stringify({ generated_at: new Date().toISOString(), total_offers: offers.length, overrides_needed: overrides.length, sample_only_left_at_425: sampleOnlyOk, already_real_price: alreadyReal, leak_unmatched_needs_review: leakNoMatch, overrides }, null, 2));
console.log(`\nOVERRIDES NEEDED (roll products at $4.25 → real price): ${overrides.length}`);
console.log(` legit sample-only left at $4.25: ${sampleOnlyOk}`);
console.log(` already showing real price: ${alreadyReal}`);
console.log(` $4.25 offers with NO feed match (review): ${leakNoMatch}`);
console.log(`Wrote ${OUT}`);
console.log('--- sample overrides ---');
overrides.slice(0, 8).forEach(o => console.log(` ${o.offerId} $${o.currentPrice} → $${o.realPrice} ${o.title}`));
})().catch(e => { console.error('FATAL', e.message); process.exit(1); });