-- Stay requests (user intent)
CREATE TABLE app.stay_request (
  request_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES app."user"(user_id),
  request_type app.stay_request_type NOT NULL DEFAULT 'guest',
  requested_start_at DATE NOT NULL,
  requested_end_at DATE NOT NULL,
  retreat_id UUID,
  notes TEXT,
  status app.stay_request_status NOT NULL DEFAULT 'submitted',
  reviewed_by_user_id UUID REFERENCES app."user"(user_id),
  reviewed_at TIMESTAMPTZ,
  review_notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_stay_request_user ON app.stay_request(user_id);
CREATE INDEX idx_stay_request_status ON app.stay_request(status);

-- Stays (confirmed island presence)
CREATE TABLE app.stay (
  stay_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES app."user"(user_id),
  stay_type app.stay_type NOT NULL DEFAULT 'guest',
  source_request_id UUID REFERENCES app.stay_request(request_id),
  retreat_id UUID,
  start_at DATE NOT NULL,
  end_at DATE NOT NULL,
  status app.stay_status NOT NULL DEFAULT 'pending',
  checked_in_at TIMESTAMPTZ,
  checked_out_at TIMESTAMPTZ,
  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_stay_user ON app.stay(user_id);
CREATE INDEX idx_stay_status ON app.stay(status);
CREATE INDEX idx_stay_dates ON app.stay(start_at, end_at);

SELECT app.enable_audit('stay_request', 'request_id');
SELECT app.enable_audit('stay', 'stay_id');