`scripts/build-sites-json.js`

123 lines
#!/usr/bin/env node
/**
 * all.designerwallcoverings.com — build data/sites.json
 *
 * READ-ONLY query against the LOCAL dw_unified mirror: every distinct vendor
 * with active products (count + one sample product image), slugified to its
 * candidate <slug>.designerwallcoverings.com subdomain, merged with the
 * hand-maintained config/known-subdomains.json (existing microsites +
 * non-brand internal subdomains, marked type:"internal").
 *
 * Output: data/sites.json  [{slug, vendor, activeProducts, sampleImage, type, knownProject?, ...}]
 * $0 (local PG read).
 */
const fs = require('fs');
const os = require('os');
const path = require('path');
const { Pool } = require('pg');

const OUT = path.join(__dirname, '..', 'data', 'sites.json');
const KNOWN = JSON.parse(fs.readFileSync(path.join(__dirname, '..', 'config', 'known-subdomains.json'), 'utf8'));

// Same connection pattern as astek-landing/scripts/build-products-json.js
const PW = (() => {
  try {
    const env = fs.readFileSync(os.homedir() + '/Projects/secrets-manager/.env', 'utf8');
    const m = env.match(/^DW_ADMIN_DB_PASSWORD=(.*)$/m);
    if (m) return m[1].replace(/^["']|["']$/g, '').trim();
  } catch {}
  return process.env.PGPASSWORD || '';
})();
const pool = process.env.DATABASE_URL
  ? new Pool({ connectionString: process.env.DATABASE_URL })
  : new Pool({ host: '127.0.0.1', port: 5432, user: 'dw_admin', database: 'dw_unified', password: PW });

// Banned-word scrub on every DISPLAY string (standing rule). Raw vendor stays
// internal for slug derivation only.
const clean = s => (s == null ? s : String(s)
  .replace(/\bWallpapers\b/gi, 'Wallcoverings')
  .replace(/\bWallpaper\b/gi, 'Wallcovering'));

// vendor -> candidate subdomain slug: lowercase, alphanumeric only.
const slugify = v => String(v || '').toLowerCase().normalize('NFD').replace(/[̀-ͯ]/g, '').replace(/&/g, 'and').replace(/[^a-z0-9]/g, '');

async function main() {
  // One row per vendor with ≥1 ACTIVE product; sample image = most recent non-null image.
  const { rows } = await pool.query(`
    SELECT vendor,
           COUNT(*)::int AS active_products,
           (ARRAY_REMOVE(ARRAY_AGG(image_url ORDER BY created_at_shopify DESC NULLS LAST), NULL))[1] AS sample_image
    FROM shopify_products
    WHERE UPPER(status) = 'ACTIVE' AND vendor IS NOT NULL AND vendor <> ''
    GROUP BY vendor
    ORDER BY COUNT(*) DESC
  `);

  const knownBrands = new Map((KNOWN.brands || []).map(b => [b.slug, b]));
  const sites = [];
  const seenSlugs = new Set();

  for (const r of rows) {
    const slug = slugify(r.vendor);
    if (!slug || seenSlugs.has(slug)) { if (seenSlugs.has(slug)) console.warn(`  ! slug collision skipped: ${r.vendor} -> ${slug}`); continue; }
    seenSlugs.add(slug);
    const k = knownBrands.get(slug) || {};
    sites.push({
      slug,
      vendor: clean(r.vendor),
      activeProducts: r.active_products,
      sampleImage: r.sample_image || null,
      type: 'brand',
      hasMicrosite: !!k.hasMicrosite,
      knownProject: k.knownProject || null,
      localUrl: k.localUrl || null,
      note: k.note || null,
    });
  }

  // Known brand sites whose vendor slug didn't come out of the active query still get a card.
  for (const [slug, k] of knownBrands) {
    if (seenSlugs.has(slug)) continue;
    seenSlugs.add(slug);
    sites.push({
      slug, vendor: clean(k.vendor || slug), activeProducts: 0, sampleImage: null,
      type: 'brand', hasMicrosite: !!k.hasMicrosite,
      knownProject: k.knownProject || null, localUrl: k.localUrl || null, note: k.note || null,
    });
  }

  // Non-brand internal subdomains.
  for (const i of (KNOWN.internal || [])) {
    if (seenSlugs.has(i.slug)) continue;
    seenSlugs.add(i.slug);
    sites.push({
      slug: i.slug, vendor: clean(i.label || i.slug), activeProducts: null, sampleImage: null,
      type: 'internal', hasMicrosite: null, knownProject: i.knownProject || null,
      localUrl: i.localUrl || null, note: i.note || null,
    });
  }

  const brands = sites.filter(s => s.type === 'brand');
  const activeBrands = brands.filter(s => s.activeProducts > 0);
  const withMicrosite = activeBrands.filter(s => s.hasMicrosite);

  const snapshot = {
    built_at: new Date().toISOString(),
    source: 'dw_unified.shopify_products (local mirror, read-only) + config/known-subdomains.json',
    counts: {
      sites: sites.length,
      brands: brands.length,
      activeBrands: activeBrands.length,
      internal: sites.length - brands.length,
      brandsWithMicrosite: withMicrosite.length,
    },
    sites,
  };
  fs.writeFileSync(OUT, JSON.stringify(snapshot, null, 1));
  console.log(`sites.json -> ${OUT}`);
  console.log(`  sites: ${sites.length} (brands: ${brands.length}, active brands: ${activeBrands.length}, internal: ${sites.length - brands.length})`);
  console.log(`  rollout: ${withMicrosite.length} of ${activeBrands.length} active brands have a microsite (${(100 * withMicrosite.length / Math.max(1, activeBrands.length)).toFixed(1)}%)`);
  await pool.end();
}
main().catch(e => { console.error(e); process.exit(1); });