77 lines
3.2 KiB
SQL
77 lines
3.2 KiB
SQL
-- ============================================================
|
|
-- Kitchen / Dietary enum types
|
|
-- ============================================================
|
|
|
|
CREATE TYPE app.meal_type AS ENUM ('breakfast', 'lunch', 'dinner', 'snack');
|
|
CREATE TYPE app.meal_status AS ENUM ('planned', 'ready', 'served', 'completed');
|
|
CREATE TYPE app.meal_attendance_status AS ENUM ('expected', 'confirmed', 'cancelled', 'served');
|
|
CREATE TYPE app.meal_attendance_source AS ENUM ('stay', 'retreat', 'manual');
|
|
|
|
-- ============================================================
|
|
-- Dietary profiles
|
|
-- ============================================================
|
|
|
|
CREATE TABLE app.dietary_profile (
|
|
profile_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL UNIQUE REFERENCES app."user"(user_id) ON DELETE CASCADE,
|
|
is_vegan BOOLEAN NOT NULL DEFAULT false,
|
|
is_vegetarian BOOLEAN NOT NULL DEFAULT false,
|
|
is_gluten_free BOOLEAN NOT NULL DEFAULT false,
|
|
is_dairy_free BOOLEAN NOT NULL DEFAULT false,
|
|
has_nut_allergy BOOLEAN NOT NULL DEFAULT false,
|
|
allergy_notes TEXT,
|
|
other_requirements TEXT,
|
|
dislikes TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_dietary_profile_user ON app.dietary_profile(user_id);
|
|
|
|
-- ============================================================
|
|
-- Meal services
|
|
-- ============================================================
|
|
|
|
CREATE TABLE app.meal_service (
|
|
service_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
service_type app.meal_type NOT NULL,
|
|
service_date DATE NOT NULL,
|
|
retreat_id UUID REFERENCES app.retreat(retreat_id),
|
|
planned_headcount INT,
|
|
menu_notes TEXT,
|
|
status app.meal_status NOT NULL DEFAULT 'planned',
|
|
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_meal_service_date ON app.meal_service(service_date);
|
|
CREATE INDEX idx_meal_service_type ON app.meal_service(service_type);
|
|
CREATE INDEX idx_meal_service_retreat ON app.meal_service(retreat_id);
|
|
|
|
-- ============================================================
|
|
-- Meal attendance
|
|
-- ============================================================
|
|
|
|
CREATE TABLE app.meal_attendance (
|
|
attendance_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
meal_service_id UUID NOT NULL REFERENCES app.meal_service(service_id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES app."user"(user_id),
|
|
stay_id UUID REFERENCES app.stay(stay_id),
|
|
source_type app.meal_attendance_source NOT NULL DEFAULT 'manual',
|
|
status app.meal_attendance_status NOT NULL DEFAULT 'expected',
|
|
notes TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE(meal_service_id, user_id)
|
|
);
|
|
|
|
CREATE INDEX idx_meal_attendance_service ON app.meal_attendance(meal_service_id);
|
|
CREATE INDEX idx_meal_attendance_user ON app.meal_attendance(user_id);
|
|
|
|
-- Enable audit
|
|
SELECT app.enable_audit('dietary_profile', 'profile_id');
|
|
SELECT app.enable_audit('meal_service', 'service_id');
|
|
SELECT app.enable_audit('meal_attendance', 'attendance_id');
|