`scripts/import.js`

133 lines
#!/usr/bin/env node
/**
 * Rebel Walls -> dw_unified import.
 *   node scripts/import.js <crawl-json>
 *
 * Steps:
 *  1. Load crawl rows, drop error rows.
 *  2. Per-row Tier-1 gate (image_url, roll_width, mfr_sku, pattern_name, price_retail>0).
 *     QUARANTINE failures — never imported.
 *  3. Apply 2.5x markup: price_retail(retail/m2) = vendor_base_m2 * 2.5.
 *  4. Assign DWRW-360000+ SKUs (skip already-registered mfr_sku; reuse its dw_sku).
 *  5. Upsert into rebelwalls_catalog + register in dw_sku_registry.
 *
 * PostgreSQL BEFORE Shopify. dw_unified is source of truth.
 */
'use strict';
const fs = require('fs');
const path = require('path');
const { Client } = require('pg');

const MARKUP = 2.5;
const SKU_PREFIX = 'DWRW';
const SKU_VENDOR = 'Rebel Walls';
const SKU_START = 360000;
const SQFT_PER_M2 = 10.7639;

const PG = {
  host: process.env.PGHOST || '127.0.0.1',
  user: process.env.PGUSER || 'dw_admin',
  password: process.env.PGPASSWORD || '',
  database: process.env.PGDATABASE || 'dw_unified',
};

const nonEmpty = (v) => v != null && String(v).trim() !== '' && !(Array.isArray(v) && v.length === 0);
function tier1Pass(r) {
  return nonEmpty(r.image_url) && nonEmpty(r.roll_width) && nonEmpty(r.mfr_sku)
      && nonEmpty(r.pattern_name) && Number(r.price_retail) > 0;
}

(async () => {
  const jsonPath = process.argv[2];
  if (!jsonPath || !fs.existsSync(jsonPath)) { console.error('Usage: import.js <crawl-json>'); process.exit(1); }
  const all = JSON.parse(fs.readFileSync(jsonPath, 'utf8'));
  const errored = all.filter((r) => r && r.error);
  const rows = all.filter((r) => r && !r.error);

  const passed = [], quarantined = [];
  for (const r of rows) (tier1Pass(r) ? passed : quarantined).push(r);

  console.error(`Loaded ${all.length} rows: ${rows.length} non-error, ${errored.length} crawl-error.`);
  console.error(`Tier-1 gate: ${passed.length} PASS, ${quarantined.length} QUARANTINED.`);

  const client = new Client(PG);
  await client.connect();

  // Next free DWRW number (max existing in range, else START).
  const { rows: maxR } = await client.query(
    `SELECT COALESCE(MAX((regexp_replace(dw_sku,'^DWRW-',''))::int), $1 - 1) AS maxn
       FROM dw_sku_registry WHERE dw_sku ~ '^DWRW-[0-9]+$'`, [SKU_START]);
  let nextNum = Math.max(Number(maxR[0].maxn) + 1, SKU_START);
  console.error(`Next DWRW number: ${nextNum}`);

  let imported = 0, reused = 0, skuAssigned = 0;
  for (const r of passed) {
    const vendorBaseM2 = Number(r.price_retail);              // crawl stores vendor base in price_retail
    const retailM2 = +(vendorBaseM2 * MARKUP).toFixed(2);
    const retailSqft = +(retailM2 / SQFT_PER_M2).toFixed(2);

    // Reuse existing dw_sku if mfr_sku already registered for this prefix.
    const { rows: ex } = await client.query(
      `SELECT dw_sku FROM dw_sku_registry WHERE vendor_prefix=$1 AND mfr_sku=$2 LIMIT 1`,
      [SKU_PREFIX, r.mfr_sku]);
    let dwSku;
    if (ex.length) { dwSku = ex[0].dw_sku; reused++; }
    else {
      dwSku = `${SKU_PREFIX}-${nextNum++}`;
      await client.query(
        `INSERT INTO dw_sku_registry (dw_sku, vendor_prefix, vendor_name, mfr_sku, status)
         VALUES ($1,$2,$3,$4,'active')
         ON CONFLICT (vendor_prefix, mfr_sku) DO NOTHING`,
        [dwSku, SKU_PREFIX, SKU_VENDOR, r.mfr_sku]);
      skuAssigned++;
    }

    await client.query(
      `INSERT INTO rebelwalls_catalog
        (mfr_sku, dw_sku, pattern_name, color_name, collection, product_type, base_unit,
         roll_width, grammage, fire_rating, light_fastness, cleanability, sustainability,
         paper_quality, manufacturer, repeat_h, repeat_v, match_type, material,
         price_retail, price_unit, display_dimension, vendor_base_m2, markup,
         price_per_sqft_nonwoven, price_per_sqft_peelstick, price_per_sqft_commercial,
         price_per_sqft_retail, currency, image_url, all_images, room_setting_images,
         colorways, product_url, in_stock, last_scraped, updated_at)
       VALUES
        ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,
         $20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36, NOW())
       ON CONFLICT (mfr_sku) DO UPDATE SET
         dw_sku=EXCLUDED.dw_sku, pattern_name=EXCLUDED.pattern_name, color_name=EXCLUDED.color_name,
         collection=EXCLUDED.collection, roll_width=EXCLUDED.roll_width, grammage=EXCLUDED.grammage,
         fire_rating=EXCLUDED.fire_rating, light_fastness=EXCLUDED.light_fastness,
         cleanability=EXCLUDED.cleanability, sustainability=EXCLUDED.sustainability,
         paper_quality=EXCLUDED.paper_quality, manufacturer=EXCLUDED.manufacturer,
         repeat_h=EXCLUDED.repeat_h, repeat_v=EXCLUDED.repeat_v, match_type=EXCLUDED.match_type,
         price_retail=EXCLUDED.price_retail, vendor_base_m2=EXCLUDED.vendor_base_m2,
         markup=EXCLUDED.markup, price_per_sqft_nonwoven=EXCLUDED.price_per_sqft_nonwoven,
         price_per_sqft_peelstick=EXCLUDED.price_per_sqft_peelstick,
         price_per_sqft_commercial=EXCLUDED.price_per_sqft_commercial,
         price_per_sqft_retail=EXCLUDED.price_per_sqft_retail, image_url=EXCLUDED.image_url,
         all_images=EXCLUDED.all_images, room_setting_images=EXCLUDED.room_setting_images,
         colorways=EXCLUDED.colorways, product_url=EXCLUDED.product_url,
         in_stock=EXCLUDED.in_stock, last_scraped=EXCLUDED.last_scraped, updated_at=NOW()`,
      [r.mfr_sku, dwSku, r.pattern_name, r.color_name || '', r.collection || '', r.product_type || 'Mural',
       r.base_unit || '', r.roll_width || '', r.grammage || '', r.fire_rating || '', r.light_fastness || '',
       r.cleanability || '', r.sustainability || '', r.paper_quality || '', r.manufacturer || '',
       r.repeat_h || '', r.repeat_v || '', r.match_type || '', r.material || 'Non-woven',
       retailM2, r.price_unit || 'Priced Per Square Meter', r.display_dimension || '1 m² (≈10.76 sq ft)',
       vendorBaseM2, MARKUP, r.price_per_sqft_nonwoven, r.price_per_sqft_peelstick, r.price_per_sqft_commercial,
       retailSqft, r.currency || 'USD', r.image_url || '',
       JSON.stringify(r.all_images || []), JSON.stringify(r.room_setting_images || []),
       JSON.stringify(r.colorways || []), r.product_url || '', r.in_stock !== false,
       r.last_scraped || new Date().toISOString()]);
    imported++;
  }

  // Quarantine file for audit.
  const qPath = path.join(path.dirname(jsonPath), `quarantine-${new Date().toISOString().slice(0,10)}.json`);
  fs.writeFileSync(qPath, JSON.stringify({ quarantined, crawl_errors: errored }, null, 2));

  await client.end();
  console.error(`\nImported/upserted: ${imported} (new SKU ${skuAssigned}, reused ${reused}).`);
  console.error(`Quarantined ${quarantined.length} gate-fail + ${errored.length} crawl-error -> ${qPath}`);
})().catch((e) => { console.error('FATAL', e); process.exit(1); });