45 lines
1.7 KiB
SQL
45 lines
1.7 KiB
SQL
-- 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');
|