`scripts/stage-db.js`

79 lines
#!/usr/bin/env node
// Stage Fentucci Naturals (Tokiwa — INTERNAL name only) into dw_unified.tokiwa_catalog
// PostgreSQL FIRST, isolated NEW staging table, status='staged'. No Shopify, no FileMaker.
// Applies colorway names from data/colors.json (dedup within pattern: " II", " III"...)
const fs = require('fs');
const path = require('path');
const { execFileSync } = require('child_process');

const ROOT = path.join(__dirname, '..');
const products = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'products.json')));
const colors = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'colors.json')));

const ROMAN = ['', ' II', ' III', ' IV', ' V', ' VI', ' VII', ' VIII', ' IX', ' X',
  ' XI', ' XII', ' XIII', ' XIV', ' XV', ' XVI', ' XVII', ' XVIII', ' XIX', ' XX'];

// assign deduped color names within each pattern (deterministic: dw_sku order)
const usedByPattern = new Map();
for (const p of products) {
  const c = colors[p.mfr_sku];
  let base = c ? c.name : null;
  if (!base) { p.color_name = null; p.color_hex = null; continue; }
  const used = usedByPattern.get(p.pattern_key) || new Map();
  const n = used.get(base) || 0;
  used.set(base, n + 1);
  usedByPattern.set(p.pattern_key, used);
  p.color_name = base + (ROMAN[n] !== undefined ? ROMAN[n] : ` ${n + 1}`);
  p.color_hex = c.hex;
}
fs.writeFileSync(path.join(ROOT, 'data', 'products.json'), JSON.stringify(products, null, 2));

const esc = s => s == null ? 'NULL' : `'${String(s).replace(/'/g, "''")}'`;

const ddl = `
CREATE TABLE IF NOT EXISTS tokiwa_catalog (
  id serial PRIMARY KEY,
  mfr_sku text NOT NULL UNIQUE,
  dw_sku text UNIQUE,
  pattern_series text,
  pattern_name text,
  color_name text,
  color_hex text,
  brand text DEFAULT 'Fentucci Naturals',
  product_type text DEFAULT 'Wallcovering',
  material text,
  source_category text,
  source_url text,
  image_url text,
  image_local text,
  price_mode text DEFAULT 'quote_only',
  sample_price numeric(10,2) DEFAULT 4.25,
  status text DEFAULT 'staged',
  created_at timestamptz DEFAULT now()
);`;

const rows = products.map(p => `(${[
  esc(p.mfr_sku), esc(p.dw_sku), esc(p.pattern_series), esc(p.pattern_name),
  esc(p.color_name), esc(p.color_hex), esc(p.brand), `'Wallcovering'`, esc(p.material),
  esc(p.source_category), esc(p.source_page), esc(p.image_url), esc(p.image_local),
  `'quote_only'`, '4.25', `'staged'`,
].join(',')})`);

const sql = ddl + `
INSERT INTO tokiwa_catalog (mfr_sku, dw_sku, pattern_series, pattern_name, color_name, color_hex,
  brand, product_type, material, source_category, source_url, image_url, image_local,
  price_mode, sample_price, status)
VALUES\n${rows.join(',\n')}
ON CONFLICT (mfr_sku) DO UPDATE SET
  dw_sku = EXCLUDED.dw_sku, pattern_series = EXCLUDED.pattern_series,
  pattern_name = EXCLUDED.pattern_name, color_name = EXCLUDED.color_name,
  color_hex = EXCLUDED.color_hex, material = EXCLUDED.material,
  source_category = EXCLUDED.source_category, source_url = EXCLUDED.source_url,
  image_url = EXCLUDED.image_url, image_local = EXCLUDED.image_local;
SELECT count(*) AS staged FROM tokiwa_catalog;`;

const tmp = path.join(ROOT, 'data', 'stage.sql');
fs.writeFileSync(tmp, sql);
const out = execFileSync('psql', ['host=/tmp dbname=dw_unified', '-f', tmp], { encoding: 'utf8' });
console.log(out.split('\n').slice(-6).join('\n'));