`db/rooms.sql`

30 lines
-- Room Builder: a "room" is a saved, shoppable composition of catalog products +
-- an optional Samplize wall color + an optional AI-rendered scene. Built by curators
-- (featured) or visitors (public).
CREATE TABLE IF NOT EXISTS rooms (
  id           BIGSERIAL PRIMARY KEY,
  slug         TEXT UNIQUE NOT NULL,
  title        TEXT NOT NULL,
  room_type    TEXT,                         -- living-room, office, bedroom, dining, ...
  style        TEXT,                         -- modern, mid-century, coastal, ...
  wall_paint_id BIGINT REFERENCES products(id) ON DELETE SET NULL,  -- a Samplize sample
  wall_hex     TEXT,                          -- resolved swatch color for the board
  product_ids  BIGINT[] DEFAULT '{}',         -- the shoppable pieces
  scene_image  TEXT,                          -- AI-rendered room scene (nullable)
  note         TEXT,                          -- short editorial caption
  created_by   TEXT DEFAULT 'visitor',        -- 'curator' (featured) | 'visitor'
  featured     BOOLEAN DEFAULT FALSE,
  public       BOOLEAN DEFAULT TRUE,
  hotspots     JSONB NOT NULL DEFAULT '[]',    -- [{id,box:{x,y,w,h},...}] shoppable pins on the scene
  created_at   TIMESTAMPTZ DEFAULT now(),
  updated_at   TIMESTAMPTZ DEFAULT now()
);
-- Existing DBs predating the column: add it idempotently (createRoom() writes hotspots).
ALTER TABLE rooms ADD COLUMN IF NOT EXISTS hotspots JSONB NOT NULL DEFAULT '[]';
CREATE INDEX IF NOT EXISTS idx_rooms_featured ON rooms (featured, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_rooms_style ON rooms (style);

-- Mark wall-paint products so the builder's color picker can find them.
ALTER TABLE products ADD COLUMN IF NOT EXISTS is_wall_paint BOOLEAN DEFAULT FALSE;
CREATE INDEX IF NOT EXISTS idx_products_paint ON products (is_wall_paint) WHERE is_wall_paint;