66 lines
2.5 KiB
SQL
66 lines
2.5 KiB
SQL
-- 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');
|