`scripts/check-coverage.mjs`

184 lines
#!/usr/bin/env node
/**
 * DWHA Harlequin Check Coverage — check-coverage.mjs
 * TK-10882 — VP DW Commerce
 *
 * Dry-run audit: shows exactly which harlequin_catalog rows are onboard-ready
 * vs skipped, and why. No writes. No Shopify calls. No image downloads.
 *
 * Usage:
 *   node scripts/check-coverage.mjs              # summary
 *   node scripts/check-coverage.mjs --verbose    # full row list
 *   node scripts/check-coverage.mjs --json       # machine-readable JSON
 */

import pg from 'pg';
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const PROJECT_ROOT = path.resolve(__dirname, '..');
const RESTORE_DIR  = path.join(PROJECT_ROOT, 'restore-maps');

const IS_VERBOSE = process.argv.includes('--verbose');
const IS_JSON    = process.argv.includes('--json');

function computeRetailPrice(price_trade) {
  const raw = parseFloat(price_trade) / 0.65 / 0.85;
  return Math.round(raw * 100) / 100;
}

// Settlement-risk keywords (floral/bird/tropical require settlement gate before ACTIVE)
const SETTLEMENT_KEYWORDS = [
  'floral', 'flower', 'botanical', 'bird', 'tropical', 'leaf', 'leaves',
  'fauna', 'flora', 'butterfly', 'dragonfly', 'peacock', 'palm', 'fern',
  'above and below', 'marine', 'seascape',
];

function hasSettlementRisk(row) {
  const text = [row.pattern_name, row.design, row.features, row.color_name, row.collection]
    .filter(Boolean).join(' ').toLowerCase();
  return SETTLEMENT_KEYWORDS.some(kw => text.includes(kw));
}

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

  let allRows;
  try {
    const result = await pool.query(`
      SELECT
        id, dw_sku, mfr_sku, pattern_name, color_name, collection,
        price_trade, price_retail, image_url, product_url,
        width, material, discontinued, on_shopify, shopify_product_id,
        image_rejected, image_rejection_reason, design, features
      FROM harlequin_catalog
      WHERE dw_sku LIKE 'DWHA-%' OR dw_sku IS NULL
      ORDER BY id ASC
    `);
    allRows = result.rows;
  } catch (err) {
    console.error('DB error:', err.message);
    await pool.end();
    process.exit(1);
  }

  await pool.end();

  // Already rolled out (on_shopify = true)
  const alreadyOnShopify = allRows.filter(r => r.on_shopify === true);

  // Classify each row
  const ready   = [];
  const skipped = [];

  for (const row of allRows) {
    if (row.on_shopify) {
      skipped.push({ ...row, skip_reason: 'already-on-shopify' });
      continue;
    }
    const missing = [];
    if (!row.dw_sku)       missing.push('dw_sku');
    if (!row.price_trade)  missing.push('price_trade');
    if (!row.image_url)    missing.push('image_url');
    if (row.discontinued)  missing.push('discontinued');
    if (row.image_rejected) missing.push('image_rejected');

    if (missing.length) {
      skipped.push({ ...row, skip_reason: missing.join(',') });
    } else {
      ready.push({
        ...row,
        retail_price: computeRetailPrice(row.price_trade),
        settlement_risk: hasSettlementRisk(row),
      });
    }
  }

  const settlementFlagged = ready.filter(r => r.settlement_risk);

  // Restore-map count (already created this run)
  let restoreMaps = 0;
  try {
    restoreMaps = fs.readdirSync(RESTORE_DIR).filter(f => f.endsWith('.json') && !f.includes('.rolled-back')).length;
  } catch {}

  // Skip reason breakdown
  const skipReasons = {};
  skipped.forEach(r => {
    const reasons = r.skip_reason.split(',');
    reasons.forEach(reason => { skipReasons[reason] = (skipReasons[reason] || 0) + 1; });
  });

  if (IS_JSON) {
    console.log(JSON.stringify({
      generated: new Date().toISOString(),
      total: allRows.length,
      ready: ready.length,
      skipped: skipped.length,
      already_on_shopify: alreadyOnShopify.length,
      settlement_flagged: settlementFlagged.length,
      restore_maps: restoreMaps,
      skip_reasons: skipReasons,
      ready_rows: IS_VERBOSE ? ready : ready.slice(0, 20),
      skip_rows: IS_VERBOSE ? skipped : skipped.slice(0, 10),
    }, null, 2));
    return;
  }

  // Human-readable output
  console.log('\n=== DWHA Harlequin Coverage Check ===');
  console.log(`Generated:          ${new Date().toLocaleString()}`);
  console.log(`Total rows in DB:   ${allRows.length}`);
  console.log(`─────────────────────────────────────`);
  console.log(`READY to onboard:   ${ready.length}  (have dw_sku + price_trade + image_url, not discontinued, not rejected)`);
  console.log(`Already on Shopify: ${alreadyOnShopify.length}`);
  console.log(`Skipped (issues):   ${skipped.length - alreadyOnShopify.length}`);
  console.log(`─────────────────────────────────────`);
  console.log(`Settlement-flagged: ${settlementFlagged.length}  (ready but require settlement gate before ACTIVE publish)`);
  console.log(`Clean (no risk):    ${ready.length - settlementFlagged.length}`);
  console.log(`─────────────────────────────────────`);
  console.log(`Restore-maps today: ${restoreMaps}  (DRAFT products created this session)`);

  console.log('\nSkip reason breakdown:');
  Object.entries(skipReasons).sort((a,b) => b[1]-a[1]).forEach(([reason, count]) => {
    console.log(`  ${reason.padEnd(22)} ${count}`);
  });

  console.log('\nPrice range (retail):');
  const prices = ready.map(r => r.retail_price).sort((a, b) => a - b);
  if (prices.length) {
    console.log(`  Min: $${prices[0]}   Max: $${prices[prices.length - 1]}   Avg: $${(prices.reduce((s,p)=>s+p,0)/prices.length).toFixed(2)}`);
  }

  console.log('\nCollections in ready set:');
  const colls = {};
  ready.forEach(r => { if (r.collection) colls[r.collection] = (colls[r.collection]||0) + 1; });
  Object.entries(colls).sort((a,b)=>b[1]-a[1]).slice(0, 10).forEach(([c, n]) => {
    console.log(`  ${c.padEnd(30)} ${n}`);
  });

  if (IS_VERBOSE) {
    console.log('\n--- READY rows (first 50) ---');
    ready.slice(0, 50).forEach((r, i) => {
      const colorDisplay = r.color_name && !r.color_name.toLowerCase().includes('curation')
        ? r.color_name : r.mfr_sku;
      console.log(`  ${String(i+1).padStart(3)}. ${r.dw_sku}  "${r.pattern_name} ${colorDisplay}"  trade:$${r.price_trade}  retail:$${r.retail_price}${r.settlement_risk ? '  [SETTLEMENT-RISK]' : ''}`);
    });
    if (ready.length > 50) console.log(`  ... and ${ready.length - 50} more`);

    console.log('\n--- Settlement-risk rows ---');
    settlementFlagged.slice(0, 20).forEach(r => {
      console.log(`  ${r.dw_sku}  "${r.pattern_name}"  — will need settlement gate before ACTIVE`);
    });
  }

  console.log('\n=== Cadence plan ===');
  console.log(`  25/day × ${Math.ceil(ready.length/25)} days = ~${Math.ceil(ready.length/25)} days to fully onboard`);
  console.log(`  All created as DRAFT — active publish is a separate Steve-gated step.`);
  console.log('');
}

main().catch(err => { console.error('Fatal:', err); process.exit(1); });