`lib/rooms.js`

108 lines
// Room Builder data layer: catalog search for the builder, the Samplize wall-paint
// palette, and room create/fetch/list.
const db = require('./db');
const COLS = require('./cols');
const { intIds } = require('./ids');

const slugify = (s) => (s || '').toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-|-$/g, '').slice(0, 60) || 'room';

// Same storefront affiliate on/off gate the faceted catalog uses (see lib/catalog.js).
// Applied to the LIVE browse surfaces (Room Builder search + brand facet) so a
// switched-off advertiser disappears everywhere a shopper can discover it. Saved
// rooms + the paint palette are deliberately NOT gated — they're curated artifacts.
const AFFILIATE_ENABLED = `NOT EXISTS (
    SELECT 1 FROM affiliate_settings a
    WHERE a.enabled = FALSE AND a.network = products.network
      AND (a.advertiser = '' OR a.advertiser = COALESCE(products.advertiser, '')))`;

async function searchProducts({ q, room, style, color, colors, advertiser, cat, max, limit = 40, offset = 0 } = {}) {
  const where = ['in_stock', 'NOT suppressed', 'is_wall_paint = FALSE', 'image_url IS NOT NULL', AFFILIATE_ENABLED];
  const params = [];
  if (room) { params.push(room); where.push(`room = $${params.length}`); }
  if (style) { params.push(style); where.push(`style = $${params.length}`); }
  if (color) { params.push(color); where.push(`color = $${params.length}`); }
  if (colors && colors.length) { params.push(colors); where.push(`color = ANY($${params.length})`); }
  if (advertiser) { params.push(advertiser); where.push(`advertiser = $${params.length}`); }
  if (cat) { params.push('%' + cat + '%'); where.push(`title ILIKE $${params.length}`); }
  if (max) { params.push(max); where.push(`COALESCE(sale_price, price) <= $${params.length}`); }
  if (q) { params.push('%' + q + '%'); where.push(`(title ILIKE $${params.length} OR brand ILIKE $${params.length} OR advertiser ILIKE $${params.length})`); }
  params.push(Math.min(limit, 80));
  const limIdx = params.length;
  params.push(Math.max(0, offset | 0));
  const { rows } = await db.query(
    `SELECT id,title,brand,advertiser,price,sale_price,image_url,room,style,color,affiliate_url
     FROM products WHERE ${where.join(' AND ')} ORDER BY featured DESC, created_at DESC, id DESC LIMIT $${limIdx} OFFSET $${params.length}`, params);
  return rows;
}

async function getPaints(limit = 80) {
  const { rows } = await db.query(
    `SELECT id,title,image_url,affiliate_url,price FROM products WHERE is_wall_paint ORDER BY title LIMIT $1`, [limit]);
  return rows;
}

async function createRoom(d = {}) {
  const base = slugify(d.title || `${d.style || ''} ${d.room_type || 'room'}`);
  let slug = base, n = 1;
  while ((await db.query('SELECT 1 FROM rooms WHERE slug=$1', [slug])).rowCount) slug = `${base}-${++n}`;
  const cb = ['curator', 'auto', 'visitor'].includes(d.created_by) ? d.created_by : 'visitor';
  const isCurator = cb === 'curator';
  const title = (d.title || `${d.style || ''} ${d.room_type || 'Room'}`).trim().replace(/\b\w/g, c => c.toUpperCase());
  const ids = intIds(d.product_ids);
  // hotspots: [{id,box:{x,y,w,h},...}] — stored as jsonb so the saved room stays shoppable
  const hotspots = Array.isArray(d.hotspots) ? d.hotspots : [];
  const { rows } = await db.query(
    `INSERT INTO rooms (slug,title,room_type,style,wall_paint_id,product_ids,scene_image,note,created_by,featured,public,hotspots)
     VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,TRUE,$11) RETURNING slug`,
    [slug, title, d.room_type || null, d.style || null, d.wall_paint_id || null, ids,
     d.scene_image || null, d.note || null, cb, isCurator, JSON.stringify(hotspots)]);
  return rows[0].slug;
}

async function getRoom(slug) {
  const { rows } = await db.query(`SELECT ${COLS.ROOM} FROM rooms WHERE slug=$1`, [slug]);
  if (!rows.length) return null;
  const r = rows[0];
  let products = [];
  if (r.product_ids && r.product_ids.length) {
    const pr = await db.query(`SELECT ${COLS.PRODUCT} FROM products WHERE id = ANY($1) AND NOT suppressed`, [r.product_ids]);
    // preserve the saved order
    products = r.product_ids.map(id => pr.rows.find(p => p.id === id)).filter(Boolean);
  }
  let paint = null;
  if (r.wall_paint_id) paint = (await db.query(`SELECT ${COLS.PRODUCT} FROM products WHERE id=$1 AND NOT suppressed`, [r.wall_paint_id])).rows[0] || null;
  // drop any scene hotspot whose product has since been suppressed, so a hidden item
  // (e.g. Honiture) can't stay tappable on the scene while it's gone from the grid.
  if (r.hotspots && r.hotspots.length) {
    const hotIds = r.hotspots.map((h) => h.id).filter(Boolean);
    if (hotIds.length) {
      const sup = await db.query('SELECT id FROM products WHERE id = ANY($1) AND suppressed', [hotIds]);
      if (sup.rows.length) { const gone = new Set(sup.rows.map((x) => x.id)); r.hotspots = r.hotspots.filter((h) => !gone.has(h.id)); }
    }
  }
  return { room: r, products, paint };
}

async function listRooms({ limit = 60 } = {}) {
  const { rows } = await db.query(`SELECT ${COLS.ROOM}, (SELECT count(*) FROM products p WHERE p.id = ANY(rooms.product_ids) AND NOT p.suppressed) AS piece_count FROM rooms WHERE public ORDER BY featured DESC, created_at DESC LIMIT $1`, [limit]);
  // attach up to 4 thumbnails per room
  for (const r of rows) {
    r.thumbs = [];
    if (r.product_ids && r.product_ids.length) {
      const t = await db.query('SELECT image_url FROM products WHERE id = ANY($1) AND image_url IS NOT NULL AND NOT suppressed LIMIT 4', [r.product_ids.slice(0, 4)]);
      r.thumbs = t.rows.map(x => x.image_url);
    }
  }
  return rows;
}

async function listBrands(limit = 40) {
  const { rows } = await db.query(
    `SELECT advertiser, count(*) n FROM products WHERE advertiser IS NOT NULL AND NOT is_wall_paint AND image_url IS NOT NULL
     AND NOT suppressed AND ${AFFILIATE_ENABLED}
     GROUP BY advertiser HAVING count(*) >= 3 ORDER BY n DESC LIMIT $1`, [limit]);
  return rows;
}

module.exports = { slugify, searchProducts, getPaints, createRoom, getRoom, listRooms, listBrands };