`db/load-distributor-lines.js`

76 lines
#!/usr/bin/env node
/**
 * Load the 20 distributor internal LINES into canonical dw_unified from
 * staging/onboarding-manifest.json. GATED (Steve-approved 2026-07-06). Idempotent.
 * EACH distributor gets its OWN vendor identity:
 *   - vendor_registry row (auto id), vendor_code=<slug>-sg (won't clobber existing
 *     Koroseal/ColeSon/Harlequin vendors),
 *   - a UNIQUE compliant sku_prefix  (DW + 2 allowed letters; forbidden B/D/S/F/M/N/V),
 *   - a sku_range_start spaced 5000 apart from the current max,
 *   - is_active=false (offline), use_for_ai=true,
 *   - keep_offline → INSERT into vendor_never_on_shopify.
 * NEVER publishes to Shopify (products stay in sangetsu_catalog, on_shopify=false).
 *   DATABASE_URL=... node db/load-distributor-lines.js
 */
const { Client } = require('pg');
const fs = require('fs'), path = require('path');

const man = JSON.parse(fs.readFileSync(path.join(__dirname, '..', 'staging', 'onboarding-manifest.json'), 'utf8'));
const ALLOWED = 'ACEGHIJKLOPQRTUWXYZ'.split(''); // no B,D,S,F,M,N,V (hard to hear on the phone)

(async () => {
  const db = new Client({ connectionString: process.env.DATABASE_URL });
  await db.connect();
  await db.query("SET lock_timeout='4s'");   // row writes fail fast rather than hang
  // use_for_ai lives in a SIDE table — NO ALTER on the hot vendor_registry (that ALTER needs
  // an ACCESS EXCLUSIVE lock and was queue-blocking the live app). CREATE TABLE is independent.
  await db.query(`create table if not exists vendor_ai_flags (
    vendor_code text primary key, use_for_ai boolean default true, added_at timestamptz default now())`);
  // keep_offline tracker — exists on the mirror but not always canonical; create if missing.
  await db.query(`create table if not exists vendor_never_on_shopify (
    vendor_code text primary key, reason text, added_at timestamptz default now())`);

  // fleet-wide unique prefix pool + current range high-water mark
  const used = new Set((await db.query("select distinct upper(sku_prefix) p from vendor_registry where sku_prefix is not null")).rows.map((r) => r.p));
  let range = Number((await db.query('select coalesce(max(sku_range_start),100000) m from vendor_registry')).rows[0].m);
  const nextPrefix = () => {
    for (const a of ALLOWED) for (const b of ALLOWED) { const p = 'DW' + a + b; if (!used.has(p)) { used.add(p); return p; } }
    throw new Error('DW prefix space exhausted');
  };

  let n = 0;
  for (const [dist, L] of Object.entries(man)) {
    if (dist === '(unassigned)') continue;
    const code = ((L.slug || dist.toLowerCase().replace(/[^a-z0-9]+/g, '-')).replace(/^-|-$/g, '')) + '-sg';
    const name = `${dist} (Sangetsu)`;
    const ex = await db.query('select id, sku_prefix from vendor_registry where vendor_code=$1', [code]);
    let prefix, rstart, id;
    if (ex.rows.length) {
      id = ex.rows[0].id;
      prefix = ex.rows[0].sku_prefix || nextPrefix();
      rstart = null; // keep existing range on update
      await db.query('update vendor_registry set is_active=false, catalog_table=$2, sku_prefix=coalesce(sku_prefix,$3), updated_at=now() where vendor_code=$1',
        [code, 'sangetsu_catalog', prefix]);
    } else {
      prefix = nextPrefix();
      range += 5000; rstart = range;
      const ins = await db.query(
        `insert into vendor_registry (vendor_name,vendor_code,catalog_table,sku_prefix,sku_range_start,is_active,is_private_label,display_prices,created_at,updated_at)
         values ($1,$2,'sangetsu_catalog',$3,$4,false,false,false,now(),now()) returning id`,
        [name, code, prefix, rstart]);
      id = ins.rows[0].id;
    }
    // use_for_ai (side table) + keep_offline (vendor_never_on_shopify)
    await db.query(`insert into vendor_ai_flags (vendor_code,use_for_ai) values ($1,true)
       on conflict (vendor_code) do update set use_for_ai=true`, [code]);
    await db.query(
      `insert into vendor_never_on_shopify (vendor_code,reason,added_at)
       select $1,'internal distributor line — keep offline (Sangetsu distribution)',now()
       where not exists (select 1 from vendor_never_on_shopify where vendor_code=$1)`, [code]);
    n++;
    console.log(`  id=${String(id).padStart(5)} ${code.padEnd(26)} prefix=${prefix} range=${rstart || '(kept)'} patterns=${L.pattern_count} skus=${L.colorway_count}`);
  }
  await db.end();
  console.log(`\nloaded ${n} distributor vendor lines — each with its own id + unique prefix + range (offline, use_for_ai=true). No Shopify.`);
})().catch((e) => { console.error(e); process.exit(1); });