111 lines
5.4 KiB
PL/PgSQL
111 lines
5.4 KiB
PL/PgSQL
-- 006_diet_and_recipe_generators.sql
|
||
-- AI-assisted diet plan and recipe generation modules.
|
||
-- Apply after scripts 001–005.
|
||
|
||
BEGIN;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- User profile for TDEE / goal-based planning
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS diet."DietUserProfiles" (
|
||
"UserId" uuid PRIMARY KEY
|
||
REFERENCES diet."AspNetUsers"("Id") ON DELETE CASCADE,
|
||
"HeightCm" numeric(5,2) NOT NULL CHECK ("HeightCm" > 0),
|
||
"WeightKg" numeric(5,2) NOT NULL CHECK ("WeightKg" > 0),
|
||
"Age" int NOT NULL CHECK ("Age" BETWEEN 10 AND 120),
|
||
"Sex" varchar(16) NOT NULL CHECK ("Sex" IN ('male', 'female')),
|
||
"ActivityLevel" varchar(32) NOT NULL
|
||
CHECK ("ActivityLevel" IN ('sedentary', 'light', 'moderate', 'active', 'very_active')),
|
||
"Goal" varchar(32) NOT NULL
|
||
CHECK ("Goal" IN ('lose_weight', 'maintain', 'gain_muscle', 'recomp')),
|
||
"AllergiesNotes" text NULL,
|
||
"DislikedFoods" text NULL,
|
||
"MealsPerDayMask" int NOT NULL DEFAULT 15 CHECK ("MealsPerDayMask" BETWEEN 1 AND 31),
|
||
"CreatedAt" timestamptz NOT NULL DEFAULT now(),
|
||
"UpdatedAt" timestamptz NULL
|
||
);
|
||
|
||
COMMENT ON COLUMN diet."DietUserProfiles"."MealsPerDayMask" IS 'Bitmask: bit0=Breakfast, bit1=SecondBreakfast, bit2=Lunch, bit3=Dinner, bit4=Snack';
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- Diet generator wizard sessions
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS diet."DietGeneratorSessions" (
|
||
"Id" serial PRIMARY KEY,
|
||
"UserId" uuid NOT NULL REFERENCES diet."AspNetUsers"("Id") ON DELETE CASCADE,
|
||
"Status" varchar(32) NOT NULL DEFAULT 'macros_pending',
|
||
"ProposedCalories" int NULL CHECK ("ProposedCalories" IS NULL OR "ProposedCalories" > 0),
|
||
"ProposedProteinG" numeric(6,2) NULL,
|
||
"ProposedFatG" numeric(6,2) NULL,
|
||
"ProposedCarbsG" numeric(6,2) NULL,
|
||
"MacroRationale" text NULL,
|
||
"MacrosAcceptedAt" timestamptz NULL,
|
||
"PlanStartDate" date NULL,
|
||
"PlanDayCount" int NOT NULL DEFAULT 7 CHECK ("PlanDayCount" BETWEEN 1 AND 28),
|
||
"CalorieToleranceKcal" int NOT NULL DEFAULT 10 CHECK ("CalorieToleranceKcal" >= 0),
|
||
"PreferencesJson" jsonb NULL,
|
||
"CommittedAt" timestamptz NULL,
|
||
"CreatedAt" timestamptz NOT NULL DEFAULT now(),
|
||
"UpdatedAt" timestamptz NULL
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS "IX_DietGeneratorSessions_UserId_Status"
|
||
ON diet."DietGeneratorSessions" ("UserId", "Status");
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- Draft meals (review before commit to MealPlanEntries)
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS diet."DietGeneratorDraftMeals" (
|
||
"Id" serial PRIMARY KEY,
|
||
"SessionId" int NOT NULL REFERENCES diet."DietGeneratorSessions"("Id") ON DELETE CASCADE,
|
||
"PlanDate" date NOT NULL,
|
||
"MealCategory" int NOT NULL CHECK ("MealCategory" BETWEEN 0 AND 4),
|
||
"RecipeId" int NOT NULL REFERENCES diet."Recipes"("Id") ON DELETE CASCADE,
|
||
"Portions" numeric(4,2) NOT NULL DEFAULT 1 CHECK ("Portions" > 0),
|
||
"Calories" int NULL,
|
||
"ProteinG" numeric(6,2) NULL,
|
||
"FatG" numeric(6,2) NULL,
|
||
"CarbsG" numeric(6,2) NULL,
|
||
"IsLocked" boolean NOT NULL DEFAULT false,
|
||
"GenerationVersion" int NOT NULL DEFAULT 1,
|
||
UNIQUE ("SessionId", "PlanDate", "MealCategory")
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS "IX_DietGeneratorDraftMeals_SessionId"
|
||
ON diet."DietGeneratorDraftMeals" ("SessionId");
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- Recipe generator sessions (draft JSON until commit)
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS diet."RecipeGeneratorSessions" (
|
||
"Id" serial PRIMARY KEY,
|
||
"UserId" uuid NOT NULL REFERENCES diet."AspNetUsers"("Id") ON DELETE CASCADE,
|
||
"Status" varchar(32) NOT NULL DEFAULT 'draft',
|
||
"ConstraintsJson" jsonb NOT NULL DEFAULT '{}',
|
||
"DraftJson" jsonb NULL,
|
||
"SavedRecipeId" int NULL REFERENCES diet."Recipes"("Id") ON DELETE SET NULL,
|
||
"GenerationVersion" int NOT NULL DEFAULT 1,
|
||
"CreatedAt" timestamptz NOT NULL DEFAULT now(),
|
||
"UpdatedAt" timestamptz NULL
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS "IX_RecipeGeneratorSessions_UserId"
|
||
ON diet."RecipeGeneratorSessions" ("UserId");
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- Recipe provenance (AI vs manual)
|
||
-- ---------------------------------------------------------------------------
|
||
ALTER TABLE diet."Recipes"
|
||
ADD COLUMN IF NOT EXISTS "Source" varchar(32) NOT NULL DEFAULT 'manual';
|
||
|
||
ALTER TABLE diet."Recipes"
|
||
ADD COLUMN IF NOT EXISTS "CreatedByUserId" uuid NULL
|
||
REFERENCES diet."AspNetUsers"("Id") ON DELETE SET NULL;
|
||
|
||
ALTER TABLE diet."Recipes"
|
||
ADD COLUMN IF NOT EXISTS "IsDraft" boolean NOT NULL DEFAULT false;
|
||
|
||
COMMENT ON COLUMN diet."Recipes"."Source" IS 'manual | excel | ai_generated';
|
||
|
||
COMMIT;
|