-- Rooms
CREATE TABLE app.room (
  room_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  room_type app.room_type NOT NULL DEFAULT 'private',
  capacity INT NOT NULL DEFAULT 1,
  is_active BOOLEAN NOT NULL DEFAULT true,
  notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Room blocks (maintenance, retreat holds, admin reservations)
CREATE TABLE app.room_block (
  block_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  room_id UUID NOT NULL REFERENCES app.room(room_id),
  block_type app.room_block_type NOT NULL DEFAULT 'maintenance',
  retreat_id UUID,
  starts_at DATE NOT NULL,
  ends_at DATE NOT NULL,
  reason TEXT,
  created_by_user_id UUID NOT NULL REFERENCES app."user"(user_id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_room_block_room ON app.room_block(room_id);
CREATE INDEX idx_room_block_dates ON app.room_block(starts_at, ends_at);

-- Room requests (preferences)
CREATE TABLE app.room_request (
  request_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  stay_id UUID NOT NULL REFERENCES app.stay(stay_id),
  requested_room_type app.room_type,
  preference_notes TEXT,
  status app.room_request_status NOT NULL DEFAULT 'submitted',
  reviewed_by_user_id UUID REFERENCES app."user"(user_id),
  reviewed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Room allocations (actual assignment)
CREATE TABLE app.room_allocation (
  allocation_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  stay_id UUID NOT NULL REFERENCES app.stay(stay_id),
  room_id UUID NOT NULL REFERENCES app.room(room_id),
  starts_at DATE NOT NULL,
  ends_at DATE NOT NULL,
  status app.room_allocation_status NOT NULL DEFAULT 'planned',
  allocation_reason TEXT,
  created_by_user_id UUID NOT NULL REFERENCES app."user"(user_id),
  updated_by_user_id UUID REFERENCES app."user"(user_id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_room_allocation_room ON app.room_allocation(room_id);
CREATE INDEX idx_room_allocation_stay ON app.room_allocation(stay_id);
CREATE INDEX idx_room_allocation_dates ON app.room_allocation(starts_at, ends_at);

SELECT app.enable_audit('room', 'room_id');
SELECT app.enable_audit('room_block', 'block_id');
SELECT app.enable_audit('room_request', 'request_id');
SELECT app.enable_audit('room_allocation', 'allocation_id');