`db/promote-import-queue.js`

88 lines
#!/usr/bin/env node
/**
 * Promote the viewer's import queue (staging/import-queue.jsonl) into dw_unified.
 *
 * GATED — this is the customer-data write half of the "⤓ Import" button. It is
 * DRY-RUN by default (prints the planned upserts, touches nothing, zero deps).
 * `--commit` performs the writes and requires `pg` + DATABASE_URL. Even on
 * --commit it ONLY stages rows PostgreSQL-side (on_shopify=false / status draft),
 * per DW's "PostgreSQL BEFORE Shopify" rule — it NEVER publishes to Shopify.
 * Do not run --commit against canonical dw_unified without Steve's go.
 *
 *   node db/promote-import-queue.js            # dry-run (safe)
 *   node db/promote-import-queue.js --commit    # gated: writes to dw_unified
 */
const fs = require('fs');
const path = require('path');

const QUEUE = path.join(__dirname, '..', 'staging', 'import-queue.jsonl');
const COMMIT = process.argv.includes('--commit');

// source → target per-vendor catalog table + vendor_code (matches DW *_catalog convention)
const TARGET = {
  sangetsu:  { table: 'sangetsu_catalog',  vendor_code: 'sangetsu' },
  lilycolor: { table: 'lilycolor_catalog', vendor_code: 'lilycolor' },
  greenland: { table: 'greenland_catalog', vendor_code: 'greenland' },
};

function rows() {
  if (!fs.existsSync(QUEUE)) return [];
  return fs.readFileSync(QUEUE, 'utf8').trim().split('\n').filter(Boolean)
    .map((l) => { try { return JSON.parse(l); } catch { return null; } }).filter(Boolean);
}

async function main() {
  const q = rows();
  if (!q.length) { console.log('import queue empty — nothing to promote.'); return; }
  const byVendor = {};
  for (const r of q) (byVendor[r.source] = byVendor[r.source] || []).push(r);
  console.log(`Queue: ${q.length} SKUs across ${Object.keys(byVendor).length} vendor(s).`);
  for (const [src, list] of Object.entries(byVendor)) {
    const t = TARGET[src];
    console.log(`\n${src} → ${t ? t.table : '(no mapping!)'}  (${list.length} SKUs)`);
    for (const r of list.slice(0, 5)) console.log(`   ${r.sku}  us_distributor=${r.distributor||'(none)'}  ${r.title||''}`);
    if (list.length > 5) console.log(`   … +${list.length - 5} more`);
  }
  if (!COMMIT) {
    console.log('\nDRY-RUN — no writes. Re-run with --commit (gated) to stage into dw_unified.');
    console.log('Each row upserts (vendor_code, mfr_sku) with: pattern_name, collection, product_url,');
    console.log('image_url, us_distributor=<distributor>, on_shopify=false. Shopify publish stays separate/gated.');
    return;
  }
  // ---- gated write path ----
  const DSN = process.env.DATABASE_URL;
  if (!DSN) { console.error('COMMIT aborted: DATABASE_URL not set.'); process.exit(1); }
  let Client;
  try { ({ Client } = require('pg')); } catch { console.error('COMMIT needs `pg` — run: npm i pg'); process.exit(1); }
  // SETTLEMENT GUARD: this script ONLY stages PostgreSQL-side with on_shopify=false.
  // It NEVER publishes to Shopify. The Sangetsu mural line (~60% birds/tropical) must
  // clear the `settlement` gate before ANY on_shopify flip — which is a separate,
  // Steve-gated step this script deliberately does not perform.
  if (byVendor.sangetsu && byVendor.sangetsu.length) {
    console.log(`\n⚠️  SETTLEMENT: ${byVendor.sangetsu.length} Sangetsu SKUs will be STAGED (on_shopify=false) only.`);
    console.log('   Do NOT flip on_shopify=true / publish to Shopify until the settlement gate passes per SKU.');
  }
  const db = new Client({ connectionString: DSN });
  await db.connect();
  let n = 0;
  for (const [src, list] of Object.entries(byVendor)) {
    const t = TARGET[src]; if (!t) { console.warn(`skip ${src}: no target table`); continue; }
    const exists = await db.query('select to_regclass($1) as t', [`public.${t.table}`]);
    if (!exists.rows[0].t) { console.warn(`skip ${src}: ${t.table} does not exist (create it first — gated schema step)`); continue; }
    for (const r of list) {
      await db.query(
        `insert into ${t.table} (vendor_code, mfr_sku, pattern_name, collection, product_url, image_url, us_distributor, on_shopify)
         values ($1,$2,$3,$4,$5,$6,$7,false)
         on conflict (vendor_code, mfr_sku) do update set
           pattern_name=excluded.pattern_name, collection=excluded.collection,
           product_url=excluded.product_url, image_url=excluded.image_url,
           us_distributor=excluded.us_distributor`,
        [t.vendor_code, r.sku, r.title, r.collection, r.url, r.image, r.distributor]);
      n++;
    }
  }
  await db.end();
  console.log(`\nCommitted ${n} rows to dw_unified (on_shopify=false). Shopify publish remains a separate gated step.`);
}
main().catch((e) => { console.error(e); process.exit(1); });