`scripts/rebrand.js`

185 lines
#!/usr/bin/env node
/**
 * Bucket 1 — REBRAND-IN-PLACE (fully reversible). For each of the 121 already-live
 * brand-collision mfr_skus, set vendor='Groundworks' on the existing LIVE product so it
 * joins the groundworks-wallcoverings smart collection (vendor=Groundworks rule). Keeps
 * handle, mfr_sku, dw_sku, variants intact. Verifies the sellable variant price == MAP
 * (wholesale x 1.5 from groundworks_catalog); if off-MAP, corrects it to MAP.
 *
 * Target-selection rule (DTD verdict A, 2026-07-22): among a mfr_sku's live products,
 * rebrand exactly ONE — the single ACTIVE product; if two ACTIVE (Lee Jofa/DWKK + Phillipe
 * Romano/DWWC dup pair), rebrand the Lee Jofa/DWKK one, leave the Phillipe Romano untouched.
 * NEVER rebrand an ARCHIVED product (discontinued stays archived).
 *
 * Records before->after vendor for every product touched (data/rebrand-results.json) so
 * the whole op is revertible. PG mirror updated first, then Shopify authoritative.
 *
 * Usage: node rebrand.js [--dry] [--limit N]
 */
const { Client } = require('pg');
const fs = require('fs');

const DRY = process.argv.includes('--dry');
const limIdx = process.argv.indexOf('--limit');
const LIMIT = limIdx >= 0 ? parseInt(process.argv[limIdx + 1], 10) : 0;

const DOMAIN = 'designer-laboratory-sandbox.myshopify.com';
const API = '2024-10';
const TOKEN = (fs.readFileSync(process.env.HOME + '/Projects/secrets-manager/.env', 'utf8')
  .split('\n').find(l => l.startsWith('SHOPIFY_ADMIN_TOKEN=')) || '').split('=').slice(1).join('=').trim();
if (!TOKEN) { console.error('no SHOPIFY_ADMIN_TOKEN'); process.exit(2); }

const GQL = `https://${DOMAIN}/admin/api/${API}/graphql.json`;
const sleep = ms => new Promise(r => setTimeout(r, ms));

async function shopify(query, variables) {
  for (let attempt = 0; attempt < 6; attempt++) {
    const res = await fetch(GQL, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json', 'X-Shopify-Access-Token': TOKEN },
      body: JSON.stringify({ query, variables }),
    });
    if (res.status === 429 || res.status >= 500) { await sleep(2500 * (attempt + 1)); continue; }
    const j = await res.json();
    if (j.errors) throw new Error('GQL: ' + JSON.stringify(j.errors).slice(0, 400));
    const cost = j.extensions?.cost?.throttleStatus;
    if (cost && cost.currentlyAvailable < 250) await sleep(1500);
    return j.data;
  }
  throw new Error('shopify retries exhausted');
}

// Fetch ALL products for one mfr_sku via metafield/variant sku search is unreliable;
// instead search by the DW SKUs we already know from the mirror. We resolve product ids
// from the local mirror, then read authoritative vendor/status/price from live Shopify.
async function liveProduct(pid) {
  const d = await shopify(`query($id:ID!){product(id:$id){id handle vendor status title
    variants(first:15){nodes{id sku price inventoryItem{id}}}}}`, { id: `gid://shopify/Product/${pid}` });
  return d.product;
}

function pickTarget(candidates) {
  // candidates = [{pid,vendor,status,dw_sku}], live-verified
  const active = candidates.filter(c => c.status === 'ACTIVE');
  if (active.length === 1) return { target: active[0], reason: 'single-active' };
  if (active.length === 0) return { target: null, reason: 'no-active' };
  // >1 active: prefer the Lee Jofa / DWKK (trade) product (DTD verdict A)
  const dwkk = active.find(c => (c.dw_sku || '').startsWith('DWKK'));
  const leejofa = active.find(c => /lee jofa/i.test(c.vendor || ''));
  const chosen = dwkk || leejofa || active[0];
  return { target: chosen, reason: 'dup-pair->leejofa/DWKK', skipped: active.filter(c => c.pid !== chosen.pid) };
}

async function main() {
  const pg = new Client({ host: '/tmp', database: 'dw_unified' });
  await pg.connect();

  const mfrs = fs.readFileSync('/tmp/gw_b1_mfrs.txt', 'utf8').trim().split('\n').filter(Boolean);
  const slice = LIMIT ? mfrs.slice(0, LIMIT) : mfrs;
  console.log(`Bucket-1 rebrand: ${slice.length} mfr_skus  DRY=${DRY}`);

  const results = [];
  let rebranded = 0, priceFixed = 0, held = 0;

  for (const mfr of slice) {
    // resolve all live product rows from mirror for this mfr_sku
    const rows = (await pg.query(
      `SELECT DISTINCT regexp_replace(shopify_id,'^gid://shopify/Product/','') AS pid, dw_sku, vendor, status
       FROM shopify_products WHERE mfr_sku=$1`, [mfr])).rows;
    if (!rows.length) { results.push({ mfr, skip: 'no-mirror-row' }); held++; continue; }

    // MAP source priority (Kravet-umbrella rule): (1) authoritative email-loaded new_map,
    // else (2) staging map_price (= wholesale x 1.5). The live prices already match the
    // authoritative Mar-2026 adjustment; staging map is staler, so authoritative wins.
    const gc = (await pg.query(`SELECT map_price, wholesale FROM groundworks_catalog WHERE mfr_sku=$1`, [mfr])).rows[0];
    const auth = (await pg.query(`SELECT new_map FROM kravet_authoritative_pricing WHERE mfr_sku=$1`, [mfr])).rows[0];
    const MAP = (auth && Number(auth.new_map) > 0) ? Number(auth.new_map) : (gc ? Number(gc.map_price) : null);
    const mapSrc = (auth && Number(auth.new_map) > 0) ? 'authoritative_new_map' : 'staging_map';

    // live-verify each distinct pid
    const candidates = [];
    const seen = new Set();
    for (const r of rows) {
      if (!r.pid || seen.has(r.pid)) continue; seen.add(r.pid);
      let lp;
      try { lp = await liveProduct(r.pid); } catch (e) { results.push({ mfr, pid: r.pid, err: 'read:' + String(e.message).slice(0, 120) }); continue; }
      if (!lp) continue;
      candidates.push({ pid: r.pid, vendor: lp.vendor, status: lp.status, dw_sku: r.dw_sku, live: lp });
      await sleep(150);
    }
    if (!candidates.length) { results.push({ mfr, skip: 'no-live-product' }); held++; continue; }

    const { target, reason, skipped } = pickTarget(candidates);
    if (!target) { results.push({ mfr, skip: 'no-active-product', candidates: candidates.map(c => ({ pid: c.pid, vendor: c.vendor, status: c.status })) }); held++; continue; }

    const beforeVendor = target.vendor;
    const entry = { mfr, pid: target.pid, dw_sku: target.dw_sku, before_vendor: beforeVendor, after_vendor: 'Groundworks', reason, handle: target.live.handle };
    if (skipped && skipped.length) entry.left_untouched = skipped.map(s => ({ pid: s.pid, vendor: s.vendor, status: s.status, dw_sku: s.dw_sku }));

    // sellable variant + current price. A product whose ONLY variant is the $4.25 sample
    // (no real sellable roll variant) must NOT be rebranded into the collection — hold + flag.
    const vs = target.live.variants.nodes;
    const sell = vs.find(v => v.sku && !v.sku.endsWith('-Sample'));
    entry.sell_sku = sell ? sell.sku : null;
    entry.price_before = sell ? Number(sell.price) : null;
    entry.map = MAP;
    entry.map_src = mapSrc;

    if (!sell) {
      results.push({ mfr, pid: target.pid, dw_sku: target.dw_sku, before_vendor: beforeVendor,
        skip: 'no-sellable-variant (only $4.25 sample) — needs sellable variant before activation', handle: target.live.handle });
      held++; continue;
    }

    if (beforeVendor === 'Groundworks') { entry.note = 'already-groundworks'; }

    if (DRY) {
      entry.dry = true;
      results.push(entry);
      continue;
    }

    // 1) PG mirror first: flip vendor on the mirror row(s) for this pid
    await pg.query(`UPDATE shopify_products SET vendor='Groundworks' WHERE regexp_replace(shopify_id,'^gid://shopify/Product/','')=$1`, [target.pid]);

    // 2) Shopify authoritative: set vendor
    if (beforeVendor !== 'Groundworks') {
      const up = await shopify(`mutation($input:ProductInput!){productUpdate(input:$input){product{id vendor} userErrors{field message}}}`,
        { input: { id: `gid://shopify/Product/${target.pid}`, vendor: 'Groundworks' } });
      const ue = up.productUpdate.userErrors;
      if (ue && ue.length) { entry.err = 'vendorUpd:' + JSON.stringify(ue); results.push(entry); held++; continue; }
      rebranded++;
    } else {
      entry.rebrand_skipped = 'already-groundworks';
    }

    // 3) MAP price correction on the sellable variant (Kravet-umbrella rule)
    if (sell && MAP > 0 && Math.abs(Number(sell.price) - MAP) > 0.005) {
      const pu = await shopify(`mutation($pid:ID!,$variants:[ProductVariantsBulkInput!]!){productVariantsBulkUpdate(productId:$pid,variants:$variants){userErrors{field message}}}`,
        { pid: `gid://shopify/Product/${target.pid}`, variants: [{ id: sell.id, price: MAP.toFixed(2) }] });
      const pe = pu.productVariantsBulkUpdate.userErrors;
      if (pe && pe.length) { entry.price_err = JSON.stringify(pe); }
      else { entry.price_after = MAP; entry.price_corrected = true; priceFixed++; }
    }

    // 4) writeback: allocate dw_sku/vendor on groundworks_catalog (points staging row at the rebranded live product)
    await pg.query(`UPDATE groundworks_catalog SET dw_sku=COALESCE($1,dw_sku), shopify_product_id=$2 WHERE mfr_sku=$3`,
      [target.dw_sku, target.pid, mfr]);

    results.push(entry);
    console.log(`  ✓ ${mfr}  pid=${target.pid}  ${beforeVendor} -> Groundworks${entry.price_corrected ? `  price ${entry.price_before}->${entry.price_after}` : ''}`);
    await sleep(700); // metered cadence
  }

  await pg.end();
  fs.writeFileSync(__dirname + '/../data/rebrand-results.json', JSON.stringify(results, null, 2));
  console.log(`\nDONE: rebranded=${rebranded} priceFixed=${priceFixed} held=${held} total=${results.length}`);
  const errs = results.filter(r => r.err || r.price_err);
  if (errs.length) console.log('ERRORS:', JSON.stringify(errs, null, 2));
  // Phillipe Romano -> Groundworks explicit list
  const pr = results.filter(r => /phillipe romano/i.test(r.before_vendor || ''));
  console.log(`\nPhillipe-Romano -> Groundworks (${pr.length}):`);
  pr.forEach(r => console.log(`  ${r.mfr}  pid=${r.pid}  dw=${r.dw_sku}  handle=${r.handle}`));
}
main().catch(e => { console.error(e); process.exit(1); });