`scripts/pipe-title-backfill/go-2clean-and-wm-reconcile.mjs`

112 lines
#!/usr/bin/env node
// (A) Apply the 2 genuinely-named draft rows (Dig-540050, Dig-540053) title-only, PG-first then Shopify.
// (B) WM RECONCILE (investigate only, NO status/gid write): for DWWM-14364/65/66/67 confirm the stored
//     gid is dead, search live Shopify for a re-created match, classify RE-SYNC vs ARCHIVE, write a memo.
// Steve approved in-session 2026-07-13. Live store = designer-laboratory-sandbox.myshopify.com (real).
// Author: steve@designerwallcoverings.com
import pg from 'pg';
import fs from 'node:fs';

const DB = process.env.DATABASE_URL || 'postgresql://stevestudio2@/dw_unified?host=/tmp';
const SHOP = 'designer-laboratory-sandbox.myshopify.com';
const API = '2024-10';
const GQL = `https://${SHOP}/admin/api/${API}/graphql.json`;
const HOME = process.env.HOME;

function readToken() {
  const line = fs.readFileSync(`${HOME}/Projects/secrets-manager/.env`, 'utf8')
    .split('\n').find(l => /^SHOPIFY_ADMIN_TOKEN=/.test(l));
  if (!line) throw new Error('SHOPIFY_ADMIN_TOKEN not found');
  return line.replace(/^SHOPIFY_ADMIN_TOKEN=/, '').replace(/["']/g, '').trim();
}
const TOKEN = readToken();
const sleep = ms => new Promise(r => setTimeout(r, ms));

async function gql(query, variables, attempt = 0) {
  const res = await fetch(GQL, {
    method: 'POST',
    headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' },
    body: JSON.stringify({ query, variables }),
  });
  if ([429, 502, 503].includes(res.status)) {
    if (attempt >= 6) throw new Error(`Shopify ${res.status} after ${attempt} retries`);
    await sleep(Math.min(30000, 2000 * 2 ** attempt));
    return gql(query, variables, attempt + 1);
  }
  return res.json();
}
const afterTitle = r => (typeof r.after === 'object' && r.after ? r.after.title : r.after);

const PAYLOAD = `${HOME}/.claude/yolo-queue/pending-approval/pipe-title-backfill-payload.json`;
const pay = JSON.parse(fs.readFileSync(PAYLOAD, 'utf8'));
const all = [...(pay.apply || []), ...(pay.draft || [])];
const bySku = sku => all.find(r => r.dw_sku === sku);

const client = new pg.Client({ connectionString: DB });
await client.connect();

// ───────────────────────── (A) 2 clean ─────────────────────────
console.log('== (A) apply 2 clean ==');
const cleanRows = ['Dig-540050', 'Dig-540053'].map(bySku).filter(Boolean);
for (const r of cleanRows) {
  const title = afterTitle(r);
  await client.query('UPDATE public.shopify_products SET title=$1 WHERE id=$2', [title, r.id]);
  const chk = await client.query('SELECT title FROM public.shopify_products WHERE id=$1', [r.id]);
  const g = await gql(
    `mutation($input:ProductInput!){productUpdate(input:$input){product{id title} userErrors{field message}}}`,
    { input: { id: r.shopify_id, title } });
  const errs = g.data?.productUpdate?.userErrors?.length ? g.data.productUpdate.userErrors
    : (g.errors || null);
  const live = g.data?.productUpdate?.product?.title || null;
  console.log(` ${r.dw_sku}: PG="${chk.rows[0].title}" | Shopify=${errs ? JSON.stringify(errs) : `"${live}"`}`);
  r.applied = true; r.applied_at = null; r.apply_result = { pg: chk.rows[0].title, shopify: live, errs };
  await sleep(2500);
}

// ───────────────────────── (B) WM reconcile (investigate only) ─────────────────────────
console.log('\n== (B) WM reconcile (investigate; NO status/gid write) ==');
const wmSkus = ['DWWM-14364', 'DWWM-14365', 'DWWM-14366', 'DWWM-14367'];
const wm = wmSkus.map(bySku).filter(Boolean);
const report = [];
for (const r of wm) {
  const title = afterTitle(r);
  const core = String(title).replace(/\s*\|.*/, '').trim();          // pattern name w/o vendor suffix
  // 1. confirm the stored gid is dead
  const cur = await gql(`query($id:ID!){product(id:$id){id title status}}`, { id: r.shopify_id });
  const storedDead = !cur.data?.product;
  // 2. search live Shopify for a re-created match by the corrected title core
  const q = `title:${JSON.stringify(core)}`;
  const s = await gql(`query($q:String!){products(first:5,query:$q){edges{node{id title status vendor}}}}`, { q });
  const hits = (s.data?.products?.edges || []).map(e => e.node);
  const cls = hits.length ? 'RE-SYNC?' : 'ARCHIVE?';
  report.push({ dw_sku: r.dw_sku, stored_gid: r.shopify_id, stored_gid_dead: storedDead, corrected_title: title, search_core: core, candidates: hits, recommend: cls });
  console.log(` ${r.dw_sku}: stored_gid_dead=${storedDead} | candidates=${hits.length} -> ${cls}`);
  await sleep(2000);
}

// write reconcile memo (draft; Steve decides)
const memoPath = `${HOME}/.claude/yolo-queue/pending-approval/2026-07-13-wm-ghost-reconcile.md`;
let memo = `# APPROVAL NEEDED — William Morris ghost SKUs reconcile\n\n`;
memo += `**Date:** 2026-07-13 · **Origin:** /yolo pipe-title-backfill follow-up · **Owner:** vp-dw-commerce\n\n`;
memo += `4 SKUs are status=ACTIVE in dw_unified but their stored Shopify gids are dead. Titles already corrected in PG.\n`;
memo += `Live-Shopify search (by corrected pattern name) results below. **No status or gid write was made** — your call.\n\n`;
for (const x of report) {
  memo += `## ${x.dw_sku} — recommend: ${x.recommend}\n`;
  memo += `- stored gid: \`${x.stored_gid}\` (dead: ${x.stored_gid_dead})\n`;
  memo += `- corrected title: \`${x.corrected_title}\`\n`;
  memo += `- searched Shopify for \`${x.search_core}\` → ${x.candidates.length} candidate(s)\n`;
  for (const c of x.candidates) memo += `  - \`${c.id}\` — "${c.title}" [${c.status}] (${c.vendor || 'no-vendor'})\n`;
  memo += `\n`;
}
memo += `## Decision (APPROVE / REVISE / BLOCK per SKU)\n`;
memo += `- If a candidate matches → **RE-SYNC**: point the dw_unified row's shopify_id at the found gid.\n`;
memo += `- If 0 candidates → **ARCHIVE**: flip status ACTIVE→ARCHIVED (they're genuinely gone from the store).\n`;
memo += `- Both are canonical writes; awaiting Steve's go.\n`;
fs.writeFileSync(memoPath, memo);
console.log(`\nWM reconcile memo → ${memoPath}`);

// persist the 2-clean applied flags back to payload
fs.writeFileSync(PAYLOAD, JSON.stringify(pay, null, 2));
await client.end();
console.log('\nDONE. 2 clean applied (PG+Shopify); WM memo drafted (no status/gid write).');