`scripts/resolve-priceability.js`

128 lines
#!/usr/bin/env node
/**
 * Step-0 priceability resolver (DTD verdict B). READ-ONLY.
 *
 * For every ACTIVE product that needs a fix (missing a sellable product variant
 * OR missing a sample OR has a $0 variant), determine whether it is AUTO-PRICEABLE
 * — i.e. a REAL roll cost/price exists in a trusted source — vs NO-COST HOLD.
 *
 * Trusted cost sources, in priority (placeholder product_pricing stubs EXCLUDED):
 *  Kravet family: kravet_dwkk_variant_map.price(dw_sku) -> product_map.map_price(dw_sku)
 *                 -> kravet_master_price.map_price(mfr_sku) -> kravet_catalog dw_sell_price(mfr_sku)
 *  All others:    the vendor's cadence costExpr (vendors.js) against its catalog
 *                 table, joined by mfr_sku then dw_sku, value > 0.
 *
 * Emits per-vendor + grand-total split and writes JSON + a PG audit table.
 */
const fs = require('fs');
const { execSync } = require('child_process');
delete process.env.PGPASSWORD; delete process.env.DATABASE_URL;
const VENDORS = require(process.env.HOME + '/Projects/Designer-Wallcoverings/shopify/scripts/cadence/vendors.js');

const psql = sql => execSync(`psql -d dw_unified -tAF '\t' -f -`, { input: sql, encoding: 'utf8', maxBuffer: 1 << 28 });

const KRAVET_FAM = new Set([
  'Kravet','Kravet Couture','Kravet Design','Kravet Contract','Kravet Basics',
  'Lee Jofa','Lee Jofa Modern','Groundworks','Brunschwig & Fils','Cole & Son',
  'GP & J Baker','Colefax And Fowler','Colefax and Fowler','Clarke And Clarke',
  'Clarke & Clarke','Mulberry','Mulberry Home','Threads','Baker Lifestyle',
  'Andrew Martin','Nicolette Mayer','Aerin','Barclay Butera','Thom Filicia',
  'Gaston Y Daniela','Gaston y Daniela','Donghia',
]);

// Map a Shopify vendor name -> the cadence vendors.js config (table + costExpr).
// vendors.js is keyed by its own label; match loosely on table presence.
function cfgForVendor(vendor) {
  for (const [k, c] of Object.entries(VENDORS)) {
    if (k.toLowerCase() === vendor.toLowerCase()) return c;
  }
  // a few known aliases (Shopify vendor name -> vendors.js key / catalog table)
  return null;
}

// Build a per-vendor priceable count for the gap set.
function main() {
  // gap vendors with counts of the fix-needing set
  const gapRows = psql(`
    SELECT vendor,
      count(*) FILTER (WHERE NOT has_product OR NOT has_sample OR any_zero) AS need_fix,
      count(*) FILTER (WHERE NOT has_product) AS miss_product,
      count(*) FILTER (WHERE NOT has_sample)  AS miss_sample,
      count(*) FILTER (WHERE any_zero)        AS zero
    FROM active_five_field_status
    GROUP BY vendor HAVING count(*) FILTER (WHERE NOT has_product OR NOT has_sample OR any_zero) > 0
    ORDER BY need_fix DESC;`).trim().split('\n').filter(Boolean).map(l => {
      const [vendor, need_fix, miss_product, miss_sample, zero] = l.split('\t');
      return { vendor, need_fix:+need_fix, miss_product:+miss_product, miss_sample:+miss_sample, zero:+zero };
    });

  const results = [];
  for (const g of gapRows) {
    const v = g.vendor.replace(/'/g, "''");
    let priceable = 0, source = 'none';

    if (KRAVET_FAM.has(g.vendor)) {
      source = 'kravet-map';
      priceable = +psql(`
        SELECT count(DISTINCT a.shopify_id)
        FROM active_five_field_status a
        JOIN shopify_products sp ON sp.shopify_id=a.shopify_id
        LEFT JOIN kravet_dwkk_variant_map vm ON vm.dw_sku=sp.dw_sku
        LEFT JOIN product_map pm ON pm.dw_sku=sp.dw_sku
        LEFT JOIN kravet_master_price kmp ON upper(kmp.mfr_sku)=upper(sp.mfr_sku)
        LEFT JOIN kravet_catalog kc ON upper(kc.mfr_sku)=upper(sp.mfr_sku)
        WHERE a.vendor='${v}' AND (NOT a.has_product OR NOT a.has_sample OR a.any_zero)
          AND (vm.price>0 OR pm.map_price>0 OR kmp.map_price>0 OR kc.dw_sell_price>0);`).trim();
    } else {
      // sample-only gap needs NO new cost (sample is flat $4.25); only roll-build needs cost.
      // Priceability here = "can we build the roll" = real catalog cost exists.
      const cfg = cfgForVendor(g.vendor);
      if (cfg && cfg.table) {
        source = `${cfg.table}:${cfg.costExpr}`;
        // try mfr_sku then dw_sku join; costExpr references catalog columns
        const q = key => `
          SELECT count(DISTINCT a.shopify_id)
          FROM active_five_field_status a
          JOIN shopify_products sp ON sp.shopify_id=a.shopify_id
          JOIN ${cfg.table} c ON ${key}
          WHERE a.vendor='${v}' AND (NOT a.has_product OR NOT a.has_sample OR a.any_zero)
            AND (${cfg.costExpr})::numeric > 0;`;
        let byMfr = 0, byDw = 0;
        try { byMfr = +psql(q(`upper(c.mfr_sku)=upper(sp.mfr_sku)`)).trim() || 0; } catch {}
        try { byDw  = +psql(q(`c.dw_sku=sp.dw_sku`)).trim() || 0; } catch {}
        priceable = Math.max(byMfr, byDw);
      } else {
        // no cadence config -> probe product_map MAP (some non-kravet have real MAP).
        // DTD-C 2026-06-21: join product_map by dw_sku OR by mfr_sku so dw_sku-less
        // private-label rows (Justin-David: dw_sku NULL everywhere, keyed by mfr_sku)
        // resolve to their durable map_price after a full audit rebuild.
        source = 'product_map(dw_sku|mfr_sku)';
        priceable = +psql(`
          SELECT count(DISTINCT a.shopify_id)
          FROM active_five_field_status a
          JOIN shopify_products sp ON sp.shopify_id=a.shopify_id
          LEFT JOIN product_map pm  ON pm.dw_sku=sp.dw_sku
          LEFT JOIN product_map pmm ON pmm.mfr_sku IS NOT NULL
                                   AND upper(pmm.mfr_sku)=upper(sp.mfr_sku)
          WHERE a.vendor='${v}' AND (NOT a.has_product OR NOT a.has_sample OR a.any_zero)
            AND (pm.map_price>0 OR pmm.map_price>0);`).trim() || 0;
      }
    }
    // sample-only-gap SKUs are always "fixable" (flat $4.25) even with no cost,
    // so report both: roll-priceable vs sample-only(no-cost-needed) vs true-hold.
    results.push({ ...g, priceable, source, hold: Math.max(0, g.need_fix - priceable) });
  }

  const tot = results.reduce((a, r) => ({
    need_fix: a.need_fix + r.need_fix, miss_product: a.miss_product + r.miss_product,
    miss_sample: a.miss_sample + r.miss_sample, priceable: a.priceable + r.priceable,
    hold: a.hold + r.hold,
  }), { need_fix:0, miss_product:0, miss_sample:0, priceable:0, hold:0 });

  const out = { generatedAt: new Date().toISOString(), totals: tot, vendors: results };
  fs.writeFileSync(__dirname + '/../out/priceability.json', JSON.stringify(out, null, 2));
  console.log('TOTALS', JSON.stringify(tot));
  console.table(results.slice(0, 40).map(r => ({ vendor: r.vendor, need_fix: r.need_fix, miss_product: r.miss_product, miss_sample: r.miss_sample, priceable: r.priceable, hold: r.hold, source: r.source.slice(0, 28) })));
}
main();