`remediate.mjs`

139 lines
#!/usr/bin/env node
// HWC Group-A/B remediation — writes the correct HWC canonical code into the
// dw_sku metafields (custom/dwc/global) on the LIVE store. Dry-run unless --apply.
//   node remediate.mjs --part archived        (221 archived: copy existing HWC col -> metafields)
//   node remediate.mjs --part mint75           (75 clean verified: mint HWC-61245+ -> metafields)
// Worklist is pulled fresh from the local dw_unified mirror (psql, /tmp socket).
import { execFileSync } from 'node:child_process';
import fs from 'node:fs';

const APPLY = process.argv.includes('--apply');
const PART  = (process.argv[process.argv.indexOf('--part')+1]) || '';
const SHOP='designer-laboratory-sandbox.myshopify.com', API='2024-10';
const TOKEN=(fs.readFileSync('/Users/macstudio3/Projects/secrets-manager/.env','utf8').match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m)||[])[1]?.trim();
if(!TOKEN){console.error('No SHOPIFY_ADMIN_TOKEN');process.exit(1);}

const psql = sql => JSON.parse(execFileSync('psql',['-h','/tmp','-d','dw_unified','-tAc',sql],{encoding:'utf8'}).trim()||'[]');

// Base Group-B / archived predicate (Hollywood line, fabricated dw_sku metafield)
const FAB = `(metafields->'custom'->'dw_sku'->>'value' ~ '^DWH[DW]' OR metafields->'dwc'->'dw_sku'->>'value' ~ '^DWH[DW]' OR metafields->'global'->'dw_sku'->>'value' ~ '^DWH[DW]')`;

let SQL;
if(PART==='archived'){
  // 221 ARCHIVED: correct HWC already in dw_sku column -> copy into each fabricated dw_sku metafield
  SQL = `WITH a AS (
    SELECT DISTINCT regexp_replace(shopify_id,'\\D','','g') AS pid, dw_sku AS code,
      (metafields->'custom'->'dw_sku'->>'value' ~ '^DWH[DW]') c,
      (metafields->'dwc'->'dw_sku'->>'value' ~ '^DWH[DW]') d,
      (metafields->'global'->'dw_sku'->>'value' ~ '^DWH[DW]') g
    FROM shopify_products
    WHERE (vendor='Hollywood Wallcoverings' OR dw_sku LIKE 'HWC-%')
      AND status='ARCHIVED' AND dw_sku ~ '^HWC-\\d+$' AND ${FAB}
  ), w AS (
    SELECT pid,'custom' ns,code FROM a WHERE c
    UNION ALL SELECT pid,'dwc',code FROM a WHERE d
    UNION ALL SELECT pid,'global',code FROM a WHERE g
  )
  SELECT COALESCE(json_agg(json_build_object('ownerId','gid://shopify/Product/'||pid,'namespace',ns,'key','dw_sku','type','single_line_text_field','value',code) ORDER BY pid,ns),'[]') FROM w;`;
} else if(PART==='mint75'){
  // 75 CLEAN: verified against momentum_colorways (pattern_sku/lcode) AND mfr_sku unique in catalog.
  // Deterministic assignment HWC-(61244 + rank) ordered by pid. Written to the fabricated dw_sku metafields.
  SQL = `WITH b AS (
    SELECT DISTINCT regexp_replace(shopify_id,'\\D','','g') AS pid, TRIM(mfr_sku) AS mfr,
      (metafields->'custom'->'dw_sku'->>'value' ~ '^DWH[DW]') c,
      (metafields->'dwc'->'dw_sku'->>'value' ~ '^DWH[DW]') d,
      (metafields->'global'->'dw_sku'->>'value' ~ '^DWH[DW]') g
    FROM shopify_products sp
    WHERE (vendor='Hollywood Wallcoverings' OR dw_sku LIKE 'HWC-%')
      AND status='ACTIVE' AND dw_sku IS NULL AND ${FAB}
      AND mfr_sku IS NOT NULL AND TRIM(mfr_sku)<>''
      AND EXISTS (SELECT 1 FROM momentum_colorways m WHERE TRIM(mfr_sku) IN (TRIM(m.pattern_sku),TRIM(m.lcode)))
      AND NOT EXISTS (SELECT 1 FROM shopify_products o WHERE TRIM(o.mfr_sku)=TRIM(sp.mfr_sku) AND o.shopify_id<>sp.shopify_id)
  ), r AS (SELECT pid,c,d,g,'HWC-'||(61244 + row_number() OVER (ORDER BY pid))::text AS code FROM b),
  w AS (
    SELECT pid,'custom' ns,code FROM r WHERE c
    UNION ALL SELECT pid,'dwc',code FROM r WHERE d
    UNION ALL SELECT pid,'global',code FROM r WHERE g
  )
  SELECT COALESCE(json_agg(json_build_object('ownerId','gid://shopify/Product/'||pid,'namespace',ns,'key','dw_sku','type','single_line_text_field','value',code) ORDER BY pid,ns),'[]') FROM w;`;
} else if(PART==='recover24'){
  // 24 no-mfr (Goat Rock=Seto, Hearst Castle=Layout): recover per-color Momentum sku into manufacturer_sku
  // AND mint HWC-61320+ into dw_sku. mint75 consumed HWC-61245..61319, so this continues at 61320.
  SQL = `WITH b AS (
    SELECT DISTINCT regexp_replace(shopify_id,'\\D','','g') AS pid, title,
      regexp_replace(title,'^(Goat Rock|Hearst Castle) - ([A-Za-z]+).*','\\2') AS color,
      CASE WHEN title LIKE 'Goat Rock%' THEN 'Seto' WHEN title LIKE 'Hearst Castle%' THEN 'Layout' END AS mpat,
      (metafields->'custom'->'dw_sku'->>'value' ~ '^DWH[DW]') c,
      (metafields->'dwc'->'dw_sku'->>'value' ~ '^DWH[DW]') d,
      (metafields->'global'->'dw_sku'->>'value' ~ '^DWH[DW]') g
    FROM shopify_products
    WHERE (vendor='Hollywood Wallcoverings' OR dw_sku LIKE 'HWC-%')
      AND status='ACTIVE' AND dw_sku IS NULL AND ${FAB}
      AND (mfr_sku IS NULL OR TRIM(mfr_sku)='')
  ), r AS (
    SELECT b.pid, b.c, b.d, b.g, m.momentum_sku AS msku,
      'HWC-'||(61319 + row_number() OVER (ORDER BY b.pid))::text AS hwc
    FROM b JOIN LATERAL (
      SELECT momentum_sku FROM momentum_colorways m
      WHERE m.pattern_name=b.mpat AND TRIM(m.color_name)=b.color LIMIT 1
    ) m ON true
  ), w AS (
    -- mint HWC into all 3 line-standard dw_sku namespaces (matches mint75; clears the fabricated code + populates the mirror)
    SELECT pid,'custom' ns,'dw_sku' k,hwc v FROM r
    UNION ALL SELECT pid,'dwc','dw_sku',hwc FROM r
    UNION ALL SELECT pid,'global','dw_sku',hwc FROM r
    -- write recovered Momentum sku into manufacturer_sku across the same 3 namespaces
    UNION ALL SELECT pid,'custom','manufacturer_sku',msku FROM r
    UNION ALL SELECT pid,'dwc','manufacturer_sku',msku FROM r
    UNION ALL SELECT pid,'global','manufacturer_sku',msku FROM r
  )
  SELECT COALESCE(json_agg(json_build_object('ownerId','gid://shopify/Product/'||pid,'namespace',ns,'key',k,'type','single_line_text_field','value',v) ORDER BY pid,k,ns),'[]') FROM w;`;
} else if(PART==='mint14'){
  // 14 collides: provenance-verified Momentum products whose mfr code collides on a shared pattern string.
  // Their manufacturer_sku already holds the pattern code; just mint the unique HWC canonical into dw_sku.
  // recover24 consumed HWC-61320..61343, so this continues at 61344.
  SQL = `WITH b AS (
    SELECT DISTINCT regexp_replace(shopify_id,'\\D','','g') AS pid, TRIM(mfr_sku) AS mfr
    FROM shopify_products sp
    WHERE (vendor='Hollywood Wallcoverings' OR dw_sku LIKE 'HWC-%')
      AND status='ACTIVE' AND dw_sku IS NULL AND ${FAB}
      AND mfr_sku IS NOT NULL AND TRIM(mfr_sku)<>''
      AND EXISTS (SELECT 1 FROM momentum_colorways m WHERE TRIM(sp.mfr_sku) IN (TRIM(m.pattern_sku),TRIM(m.lcode)))
      AND EXISTS (SELECT 1 FROM shopify_products o WHERE TRIM(o.mfr_sku)=TRIM(sp.mfr_sku) AND o.shopify_id<>sp.shopify_id)
  ), r AS (SELECT pid,'HWC-'||(61343 + row_number() OVER (ORDER BY pid))::text AS hwc FROM b),
  w AS (
    SELECT pid,'custom' ns,'dw_sku' k,hwc v FROM r
    UNION ALL SELECT pid,'dwc','dw_sku',hwc FROM r
    UNION ALL SELECT pid,'global','dw_sku',hwc FROM r
  )
  SELECT COALESCE(json_agg(json_build_object('ownerId','gid://shopify/Product/'||pid,'namespace',ns,'key',k,'type','single_line_text_field','value',v) ORDER BY pid,ns),'[]') FROM w;`;
} else { console.error('Pass --part archived | mint75 | recover24 | mint14'); process.exit(1); }

const payload = psql(SQL);   // json_agg -> a single JSON array, parsed by the psql() helper

const prods = new Set(payload.map(p=>p.ownerId));
console.log(`Part: ${PART}  |  ${payload.length} dw_sku metafield writes across ${prods.size} products`);
console.log(APPLY ? '>>> APPLY MODE — writing to LIVE Shopify' : '>>> DRY RUN (no writes). Pass --apply to execute.');

if(!APPLY){
  const byProd={}; for(const p of payload){ (byProd[p.ownerId] ||= []).push(`${p.namespace}.${p.key}=${p.value}`); }
  for(const [gid,sets] of Object.entries(byProd)) console.log(`  ${gid.split('/').pop()}  ${sets.join('  ')}`);
  console.log(`\nDry run complete. ${payload.length} writes staged across ${prods.size} products.`);
  process.exit(0);
}

const MUT=`mutation($mf:[MetafieldsSetInput!]!){metafieldsSet(metafields:$mf){metafields{id} userErrors{field message}}}`;
let ok=0,errs=[];
for(let i=0;i<payload.length;i+=25){
  const batch=payload.slice(i,i+25);
  const r=await fetch(`https://${SHOP}/admin/api/${API}/graphql.json`,{method:'POST',headers:{'Content-Type':'application/json','X-Shopify-Access-Token':TOKEN},body:JSON.stringify({query:MUT,variables:{mf:batch}})});
  const j=await r.json();
  const ue=j?.data?.metafieldsSet?.userErrors||[]; const set=j?.data?.metafieldsSet?.metafields||[];
  if(j.errors){errs.push(JSON.stringify(j.errors));console.error('GraphQL',j.errors);}
  if(ue.length){errs.push(...ue.map(e=>JSON.stringify(e)));console.error('userErrors',ue);}
  ok+=set.length; console.log(`  batch ${i/25+1}: set ${set.length}/${batch.length}`);
  await new Promise(r=>setTimeout(r,600));
}
console.log(`\nDone. ${ok}/${payload.length} dw_sku metafields set. Errors: ${errs.length}`);
if(errs.length) process.exit(1);