`lib/catalog.js`

147 lines
// lib/catalog.js — build a slideshow queue straight from the live dw_unified catalog.
// Shells out to the local passwordless `psql -d dw_unified` (no `pg` dependency),
// parses each product's tags into facets, writes real editorial body copy, and
// downloads the lead product photo to use as a slide background.
import { execFileSync } from 'node:child_process'
import fs from 'node:fs'
import path from 'node:path'
import { ensureDir, slugify, titleCase, cap } from './util.js'

function psqlJSON(sql) {
  const out = execFileSync('psql', ['-d', 'dw_unified', '-tAc', sql],
    { encoding: 'utf8', maxBuffer: 1 << 26 }).trim()
  return out ? JSON.parse(out) : []
}

// ---- tag -> facets ---------------------------------------------------------
const STYLES = ['Chinoiserie', 'Damask', 'Toile', 'Botanical', 'Floral', 'Geometric',
  'Traditional', 'Modern', 'Contemporary', 'Art Deco', 'Grasscloth', 'Cottagecore',
  'Animal', 'Stripe', 'Ikat', 'Trellis', 'Abstract', 'Tropical', 'Moroccan']
const MATERIALS = ['Grasscloth', 'Raffia', 'Linen', 'Silk', 'Cork', 'Vinyl', 'Mylar',
  'Non Woven', 'Sisal', 'Paperweave', 'Mica', 'Leather', 'Wool', 'Cotton', 'Velvet', 'Suede']
const ROOMS = ['Bedroom', 'Dining Room', 'Living Room', 'Powder Room', 'Bathroom',
  'Entry', 'Office', 'Nursery', 'Hallway', 'Kitchen']

const decode = s => String(s || '')
  .replace(/&#x27;|&#39;/g, "'").replace(/&amp;/g, '&').replace(/&quot;/g, '"').trim()

function facets(tagStr) {
  const tags = decode(tagStr).split(',').map(t => t.trim()).filter(Boolean)
  const f = { color: null, style: [], material: null, rooms: [], match: null }
  for (const t of tags) {
    const m = t.match(/^colou?r\s*[:=]\s*(.+)/i)
    if (m && !f.color) { f.color = titleCase(m[1].trim()); continue }
    if (/(straight|half\s*drop|random|free)\s*match/i.test(t)) f.match = t
    for (const s of STYLES) if (t.toLowerCase() === s.toLowerCase() && !f.style.includes(s)) f.style.push(s)
    for (const mat of MATERIALS) if (new RegExp(mat, 'i').test(t) && !f.material) f.material = t
    for (const r of ROOMS) if (t.toLowerCase() === r.toLowerCase() && !f.rooms.includes(r)) f.rooms.push(r)
  }
  return f
}

function patternName(title) {
  let t = decode(title).split('|')[0].trim()      // drop "| Vendor"
  t = t.split(/\s+[-–]\s+/)[0].trim()             // drop "- colorway/desc"
  t = t.replace(/\b(Wallcovering|Wallpaper|Fabric)\b\s*$/i, '').trim()
  return t || decode(title)
}

function factsFromFacets(f, type) {
  const out = []
  if (f.material) out.push({ title: 'The texture', subtitle: cap(`${titleCase(f.material)} brings real depth to the wall — texture a flat paint can't touch.`, 130) })
  if (f.color)    out.push({ title: 'The colorway', subtitle: cap(`Shown in ${f.color} — a tone that reads warm and intentional in any light.`, 130) })
  if (f.style.length) out.push({ title: 'The look', subtitle: cap(`${titleCase(f.style.slice(0, 2).join(' · '))} — a pattern that anchors a room without shouting.`, 130) })
  if (f.rooms.length) out.push({ title: 'Where it works', subtitle: cap(`Designers reach for it in the ${f.rooms.slice(0, 2).join(' and ').toLowerCase()}.`, 130) })
  if (f.match)    out.push({ title: 'Easy install', subtitle: cap(`${titleCase(f.match)} for a clean, professional hang.`, 130) })
  return out.slice(0, 4)
}

async function download(url, dest) {
  const res = await fetch(url, { redirect: 'follow' })
  if (!res.ok) throw new Error(`HTTP ${res.status}`)
  const buf = Buffer.from(await res.arrayBuffer())
  ensureDir(path.dirname(dest)); fs.writeFileSync(dest, buf)
  return dest
}

// ---- queue builders --------------------------------------------------------
export async function buildCatalogQueue(opts = {}) {
  const { vendor, type, limit = 6, since, mode = 'spotlight', bgDir, photos = true } = opts
  const where = [
    "status='active'", 'online_store_published is true',
    "image_url like 'http%'",
    'tags is not null', 'length(tags) > 10',  // tags drive the slide copy; mirror lacks body_html
  ]
  if (vendor) where.push(`vendor = '${vendor.replace(/'/g, "''")}'`)
  if (type)   where.push(`product_type = '${type.replace(/'/g, "''")}'`)
  if (since)  where.push(`created_at_shopify >= '${since.replace(/'/g, "''")}'`)
  const sql = `select json_agg(t) from (
    select id, title, vendor, product_type, tags, image_url, dw_sku,
           created_at_shopify::date as created
    from shopify_products
    where ${where.join(' and ')}
    order by created_at_shopify desc nulls last
    limit ${Number(limit) || 6}
  ) t`
  const rows = psqlJSON(sql) || []
  if (!rows.length) throw new Error('no matching catalog products')

  if (mode === 'roundup') return [await buildRoundup(rows, opts)]
  // spotlight: one carousel per product
  const items = []
  for (const r of rows) {
    const f = facets(r.tags)
    const pat = patternName(r.title)
    let body = factsFromFacets(f, r.product_type)
    const backgrounds = {}
    if (photos) {
      try {
        const dest = path.join(bgDir, `${r.id}.jpg`)
        await download(r.image_url, dest)
        backgrounds['1'] = dest; backgrounds['6'] = dest // photo on hook + CTA
      } catch (e) { process.stderr.write(`  [catalog] img ${r.id} failed: ${e.message}\n`) }
    }
    items.push({
      id: slugify(`${pat}-${r.dw_sku || r.id}`),
      topic: pat,
      angle: `${r.vendor} · ${r.product_type}`,
      style: f.style[0] || null,
      colorway: f.color || null,
      room: f.rooms[0] || null,
      slides: body.length >= 2 ? body : undefined, // else hooks.js writes generic body
      backgrounds,
      cta: { label: 'Shop this pattern', sub: 'designerwallcoverings.com' },
      hashtags: dedupeTags(['#designerwallcoverings', tagify(r.vendor), tagify(f.material), tagify(f.style[0]), tagify(r.product_type)]),
    })
  }
  return items
}

async function buildRoundup(rows, opts) {
  const { bgDir, photos = true } = opts
  const vendor = rows[0].vendor
  const type = rows[0].product_type || 'Wallcovering'
  const body = []
  const backgrounds = {}
  for (let i = 0; i < Math.min(4, rows.length); i++) {
    const r = rows[i], f = facets(r.tags), pat = patternName(r.title)
    body.push({ title: cap(pat, 38), subtitle: cap(`${f.color ? f.color + ' · ' : ''}${f.material ? titleCase(f.material) + ' · ' : ''}${f.style[0] || type}.`, 130) })
    if (photos) {
      try { const dest = path.join(bgDir, `roundup-${r.id}.jpg`); await download(r.image_url, dest); backgrounds[String(i + 2)] = dest }
      catch {}
    }
  }
  return {
    id: slugify(`new-${vendor}-arrivals`),
    topic: `New ${vendor} arrivals`,
    angle: `${rows.length} just landed`,
    slides: body,
    backgrounds,
    cta: { label: 'Shop new arrivals', sub: 'designerwallcoverings.com' },
    hashtags: dedupeTags(['#designerwallcoverings', tagify(vendor), '#newarrivals', tagify(type), '#interiordesign']),
  }
}

const tagify = s => s ? '#' + slugify(s).replace(/-/g, '') : null
const dedupeTags = arr => [...new Set(arr.filter(Boolean))].slice(0, 5)