145 lines
8.2 KiB
PL/PgSQL
145 lines
8.2 KiB
PL/PgSQL
-- DailyMeals ingredient nutrition catalog
|
|
-- Reference data: macros per 100 g, grouped by food category (meat, vegetables, etc.)
|
|
-- Apply manually: psql "$DATABASE_URL" -f database/scripts/002_ingredient_nutrition_catalog.sql
|
|
|
|
BEGIN;
|
|
|
|
CREATE TABLE IF NOT EXISTS diet."IngredientCategories" (
|
|
"Id" serial PRIMARY KEY,
|
|
"Code" varchar(32) NOT NULL UNIQUE,
|
|
"Name" varchar(100) NOT NULL,
|
|
"SortOrder" int NOT NULL DEFAULT 0,
|
|
"IsActive" boolean NOT NULL DEFAULT true
|
|
);
|
|
|
|
COMMENT ON TABLE diet."IngredientCategories" IS 'Food groups for the nutrition catalog (meat, vegetables, etc.).';
|
|
|
|
CREATE TABLE IF NOT EXISTS diet."IngredientNutritionCatalog" (
|
|
"Id" serial PRIMARY KEY,
|
|
"CategoryId" int NOT NULL
|
|
REFERENCES diet."IngredientCategories"("Id") ON DELETE RESTRICT,
|
|
"Name" varchar(255) NOT NULL,
|
|
"CaloriesPer100G" int NOT NULL CHECK ("CaloriesPer100G" >= 0),
|
|
"ProteinGPer100G" numeric(6,2) NOT NULL DEFAULT 0 CHECK ("ProteinGPer100G" >= 0),
|
|
"FatGPer100G" numeric(6,2) NOT NULL DEFAULT 0 CHECK ("FatGPer100G" >= 0),
|
|
"CarbsGPer100G" numeric(6,2) NOT NULL DEFAULT 0 CHECK ("CarbsGPer100G" >= 0),
|
|
"FiberGPer100G" numeric(6,2) NULL CHECK ("FiberGPer100G" IS NULL OR "FiberGPer100G" >= 0),
|
|
"SortOrder" int NOT NULL DEFAULT 0,
|
|
"IsActive" boolean NOT NULL DEFAULT true,
|
|
CONSTRAINT "UQ_IngredientNutritionCatalog_Category_Name" UNIQUE ("CategoryId", "Name")
|
|
);
|
|
|
|
COMMENT ON TABLE diet."IngredientNutritionCatalog" IS 'Ingredient nutrition values per 100 g (calories, protein, fat, carbs).';
|
|
|
|
CREATE INDEX IF NOT EXISTS "IX_IngredientNutritionCatalog_CategoryId"
|
|
ON diet."IngredientNutritionCatalog" ("CategoryId", "SortOrder", "Name");
|
|
|
|
CREATE INDEX IF NOT EXISTS "IX_IngredientNutritionCatalog_Name"
|
|
ON diet."IngredientNutritionCatalog" (lower("Name"));
|
|
|
|
INSERT INTO diet."IngredientCategories" ("Code", "Name", "SortOrder")
|
|
VALUES
|
|
('meat', 'Meat', 1),
|
|
('poultry', 'Poultry', 2),
|
|
('fish', 'Fish', 3),
|
|
('seafood', 'Seafood', 4),
|
|
('vegetables', 'Vegetables', 5),
|
|
('fruits', 'Fruits', 6),
|
|
('dairy', 'Dairy', 7),
|
|
('eggs', 'Eggs', 8),
|
|
('grains', 'Grains', 9),
|
|
('legumes', 'Legumes', 10),
|
|
('nuts', 'Nuts & seeds', 11),
|
|
('oils', 'Oils & fats', 12)
|
|
ON CONFLICT ("Code") DO NOTHING;
|
|
|
|
-- Helper: insert items per category code
|
|
INSERT INTO diet."IngredientNutritionCatalog"
|
|
("CategoryId", "Name", "CaloriesPer100G", "ProteinGPer100G", "FatGPer100G", "CarbsGPer100G", "FiberGPer100G", "SortOrder")
|
|
SELECT c."Id", v."Name", v."Calories", v."Protein", v."Fat", v."Carbs", v."Fiber", v."SortOrder"
|
|
FROM (VALUES
|
|
-- meat
|
|
('meat', 'Beef sirloin', 271, 25.80, 18.00, 0.00, NULL, 1),
|
|
('meat', 'Pork loin', 242, 27.30, 14.00, 0.00, NULL, 2),
|
|
('meat', 'Lamb leg', 258, 25.60, 16.50, 0.00, NULL, 3),
|
|
('meat', 'Ground beef (15% fat)', 250, 26.00, 15.00, 0.00, NULL, 4),
|
|
('meat', 'Ham (cooked)', 145, 21.00, 6.00, 1.50, NULL, 5),
|
|
-- poultry
|
|
('poultry', 'Chicken breast', 165, 31.00, 3.60, 0.00, NULL, 1),
|
|
('poultry', 'Chicken thigh', 209, 26.00, 10.90, 0.00, NULL, 2),
|
|
('poultry', 'Turkey breast', 135, 30.00, 1.00, 0.00, NULL, 3),
|
|
('poultry', 'Duck breast', 202, 23.50, 11.20, 0.00, NULL, 4),
|
|
-- fish
|
|
('fish', 'Salmon', 208, 20.40, 13.40, 0.00, NULL, 1),
|
|
('fish', 'Cod', 82, 17.80, 0.70, 0.00, NULL, 2),
|
|
('fish', 'Tuna (canned in water)', 116, 25.50, 0.80, 0.00, NULL, 3),
|
|
('fish', 'Trout', 148, 20.80, 6.60, 0.00, NULL, 4),
|
|
('fish', 'Mackerel', 205, 18.60, 13.90, 0.00, NULL, 5),
|
|
-- seafood
|
|
('seafood', 'Shrimp', 99, 24.00, 0.30, 0.20, NULL, 1),
|
|
('seafood', 'Mussels', 172, 23.80, 4.50, 7.40, NULL, 2),
|
|
('seafood', 'Squid', 92, 15.60, 1.40, 3.10, NULL, 3),
|
|
('seafood', 'Crab', 97, 19.40, 1.50, 0.00, NULL, 4),
|
|
-- vegetables
|
|
('vegetables', 'Tomato', 18, 0.90, 0.20, 3.90, 1.20, 1),
|
|
('vegetables', 'Carrot', 41, 0.90, 0.20, 9.60, 2.80, 2),
|
|
('vegetables', 'Broccoli', 34, 2.80, 0.40, 6.60, 2.60, 3),
|
|
('vegetables', 'Spinach', 23, 2.90, 0.40, 3.60, 2.20, 4),
|
|
('vegetables', 'Potato', 77, 2.00, 0.10, 17.50, 2.20, 5),
|
|
('vegetables', 'Cucumber', 15, 0.70, 0.10, 3.60, 0.50, 6),
|
|
('vegetables', 'Bell pepper', 31, 1.00, 0.30, 6.00, 2.10, 7),
|
|
('vegetables', 'Onion', 40, 1.10, 0.10, 9.30, 1.70, 8),
|
|
('vegetables', 'Zucchini', 17, 1.20, 0.30, 3.10, 1.00, 9),
|
|
('vegetables', 'Mushrooms', 22, 3.10, 0.30, 3.30, 1.00, 10),
|
|
-- fruits
|
|
('fruits', 'Apple', 52, 0.30, 0.20, 13.80, 2.40, 1),
|
|
('fruits', 'Banana', 89, 1.10, 0.30, 22.80, 2.60, 2),
|
|
('fruits', 'Orange', 47, 0.90, 0.10, 11.80, 2.40, 3),
|
|
('fruits', 'Strawberries', 32, 0.70, 0.30, 7.70, 2.00, 4),
|
|
('fruits', 'Blueberries', 57, 0.70, 0.30, 14.50, 2.40, 5),
|
|
('fruits', 'Grapes', 69, 0.70, 0.20, 18.10, 0.90, 6),
|
|
('fruits', 'Avocado', 160, 2.00, 14.70, 8.50, 6.70, 7),
|
|
-- dairy
|
|
('dairy', 'Whole milk', 61, 3.20, 3.30, 4.80, NULL, 1),
|
|
('dairy', 'Skim milk', 34, 3.40, 0.10, 5.00, NULL, 2),
|
|
('dairy', 'Greek yogurt (plain)', 59, 10.00, 0.40, 3.60, NULL, 3),
|
|
('dairy', 'Cheddar cheese', 403, 25.00, 33.00, 1.30, NULL, 4),
|
|
('dairy', 'Mozzarella', 280, 28.00, 17.00, 3.10, NULL, 5),
|
|
('dairy', 'Cottage cheese', 98, 11.10, 4.30, 3.40, NULL, 6),
|
|
-- eggs
|
|
('eggs', 'Chicken egg (whole)', 155, 12.60, 10.60, 1.10, NULL, 1),
|
|
('eggs', 'Egg white', 52, 10.90, 0.20, 0.70, NULL, 2),
|
|
('eggs', 'Egg yolk', 322, 15.90, 26.50, 3.60, NULL, 3),
|
|
-- grains
|
|
('grains', 'White rice (cooked)', 130, 2.70, 0.30, 28.20, 0.40, 1),
|
|
('grains', 'Brown rice (cooked)', 123, 2.70, 1.00, 25.60, 1.60, 2),
|
|
('grains', 'Pasta (cooked)', 131, 5.00, 1.10, 25.00, 1.80, 3),
|
|
('grains', 'Oats (dry)', 389, 16.90, 6.90, 66.30, 10.60, 4),
|
|
('grains', 'Whole wheat bread', 247, 13.00, 3.40, 41.00, 6.00, 5),
|
|
('grains', 'White bread', 265, 9.00, 3.20, 49.00, 2.70, 6),
|
|
('grains', 'Quinoa (cooked)', 120, 4.40, 1.90, 21.30, 2.80, 7),
|
|
-- legumes
|
|
('legumes', 'Lentils (cooked)', 116, 9.00, 0.40, 20.10, 7.90, 1),
|
|
('legumes', 'Chickpeas (cooked)', 164, 8.90, 2.60, 27.40, 7.60, 2),
|
|
('legumes', 'Black beans (cooked)', 132, 8.90, 0.50, 23.70, 8.70, 3),
|
|
('legumes', 'Kidney beans (cooked)', 127, 8.70, 0.50, 22.80, 6.40, 4),
|
|
('legumes', 'Green peas (cooked)', 84, 5.40, 0.20, 15.60, 5.50, 5),
|
|
('legumes', 'Tofu (firm)', 76, 8.10, 4.80, 1.90, 0.30, 6),
|
|
-- nuts
|
|
('nuts', 'Almonds', 579, 21.20, 49.90, 21.60, 12.50, 1),
|
|
('nuts', 'Walnuts', 654, 15.20, 65.20, 13.70, 6.70, 2),
|
|
('nuts', 'Peanuts', 567, 25.80, 49.20, 16.10, 8.50, 3),
|
|
('nuts', 'Cashews', 553, 18.20, 43.80, 30.20, 3.30, 4),
|
|
('nuts', 'Sunflower seeds', 584, 20.80, 51.50, 20.00, 8.60, 5),
|
|
('nuts', 'Chia seeds', 486, 16.50, 30.70, 42.10, 34.40, 6),
|
|
-- oils
|
|
('oils', 'Olive oil', 884, 0.00, 100.00, 0.00, NULL, 1),
|
|
('oils', 'Butter', 717, 0.90, 81.00, 0.10, NULL, 2),
|
|
('oils', 'Coconut oil', 862, 0.00, 100.00, 0.00, NULL, 3),
|
|
('oils', 'Rapeseed oil', 884, 0.00, 100.00, 0.00, NULL, 4)
|
|
) AS v("CategoryCode", "Name", "Calories", "Protein", "Fat", "Carbs", "Fiber", "SortOrder")
|
|
JOIN diet."IngredientCategories" c ON c."Code" = v."CategoryCode"
|
|
ON CONFLICT ("CategoryId", "Name") DO NOTHING;
|
|
|
|
COMMIT;
|