auto-save: 2026-07-26T17:14:46 (1 files) — gmc-price-override.js

c10e177acfe41cc3afa66afe7d7f9a8f4e548cb2 · 2026-07-26 17:14:54 -0700 · Steve Abrams
Files touched

M gmc-price-override.js
Diff

commit c10e177acfe41cc3afa66afe7d7f9a8f4e548cb2
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Jul 26 17:14:54 2026 -0700

    auto-save: 2026-07-26T17:14:46 (1 files) — gmc-price-override.js
---
 gmc-price-override.js | 69 +++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 69 insertions(+)

diff --git a/gmc-price-override.js b/gmc-price-override.js
index 85fec44..3b65ace 100644
--- a/gmc-price-override.js
+++ b/gmc-price-override.js
@@ -5,19 +5,26 @@
 // 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 its name)
 //   node gmc-price-override.js --apply <DS>     # GATED: push price overrides to datasource <DS>
 //
 // 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');
 const DS = APPLY ? args[args.indexOf('--apply') + 1] : 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").
@@ -39,6 +46,67 @@ function loadFeedPrices() {
   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;
@@ -88,6 +156,7 @@ async function applyOverrides(ds) {
 (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();
← 2abd4af auto-save: 2026-07-17T19:47:29 (1 files) — gmc-dwla-peryard- · back to Gmc Titlefix · price-override: --apply self-resolves DS (file→DEFAULT_DS), 9204be4 →