`db/migrations/008_structured_booking_brief.sql`

34 lines
-- 008 · Structured booking brief — UX idea #6
--
-- Generic directories ask "what kind of project?" — useless for wallcovering.
-- A studio arrives wrong-equipped because the brief was a textarea. These
-- columns capture the specifics an installer needs to bid + arrive prepared.

BEGIN;

ALTER TABLE bookings
  ADD COLUMN IF NOT EXISTS ceiling_height_ft  NUMERIC(4,1),
  ADD COLUMN IF NOT EXISTS surface_state      TEXT,
  ADD COLUMN IF NOT EXISTS access_constraints TEXT,
  ADD COLUMN IF NOT EXISTS brand_sku          TEXT,
  ADD COLUMN IF NOT EXISTS roll_count_estimate INTEGER;

-- Validate enums via CHECK rather than a separate lookup table — these are
-- small fixed sets and we want the DB to refuse bad input directly.
ALTER TABLE bookings
  DROP CONSTRAINT IF EXISTS bookings_surface_state_check,
  ADD CONSTRAINT bookings_surface_state_check
    CHECK (surface_state IS NULL OR surface_state IN (
      'new_plaster', 'painted_drywall', 'wallpaper_to_remove',
      'brick', 'wood_panel', 'other'
    ));

ALTER TABLE bookings
  DROP CONSTRAINT IF EXISTS bookings_access_constraints_check,
  ADD CONSTRAINT bookings_access_constraints_check
    CHECK (access_constraints IS NULL OR access_constraints IN (
      'ground_floor', 'second_floor', 'atrium_double_height', 'high_rise', 'restricted_hours'
    ));

COMMIT;