source: sql/dml_nutritioneer_populating_1_0.sql

Last change on this file was 2e0035f, checked in by Acko <lavurovski.a65@…>, 8 days ago

Initial Commit
  • Property mode set to 100644
File size: 37.5 KB
RevLine 
[2e0035f]1-- ---------------------------------------------------------------------
2-- Користени queries
3-- ---------------------------------------------------------------------
4
5INSERT INTO "user" (email, username, password, role) VALUES
6('admin@nutritioneer.mk', 'admin_nutri', 'testPass@1', 'administrator'),
7('viktor.ilievski@nutritioneer.mk', 'coach_viktor', 'testPass@1', 'trainer'),
8('teodora.mihajlova@nutritioneer.mk','coach_teodora', 'testPass@1', 'trainer'),
9('marija.trajkovska@gmail.com', 'marija_t', 'testPass@1', 'user'),
10('stefan.petrov@gmail.com', 'stefan_p', 'testPass@1', 'user'),
11('ana.jovanovska@yahoo.com', 'ana_j', 'testPass@1', 'user'),
12('nikola.stojanov@gmail.com', 'nikola_s', 'testPass@1', 'user'),
13('elena.dimitrova@hotmail.com', 'elena_d', 'testPass@1', 'user'),
14('bojan.kostov@gmail.com', 'bojan_k', 'testPass@1', 'user'),
15('filip.angelov@gmail.com', 'filip_a', 'testPass@1', 'user');
16
17INSERT INTO ingredient (name, description, energy, kcal, type) VALUES
18('Chicken breast', 'Skinless boneless chicken breast, raw', 690, 165, 'meat'),
19('Beef mince', 'Ground beef, 20 percent fat, raw', 1046, 250, 'meat'),
20('Pork shoulder', 'Pork shoulder cut, raw', 1013, 242, 'meat'),
21('Trout fillet', 'Freshwater trout fillet, raw', 619, 148, 'fish'),
22('Mackerel', 'Atlantic mackerel, raw', 858, 205, 'fish'),
23('Egg', 'Whole chicken egg, raw', 598, 143, 'other'),
24('Cow milk', 'Pasteurised cow milk, 3.2 percent fat', 255, 61, 'dairy'),
25('Yogurt', 'Plain yogurt, 3.5 percent fat', 264, 63, 'dairy'),
26('White brine cheese', 'Traditional white brine cheese (sirenje)', 1105, 264, 'dairy'),
27('Kashkaval', 'Semi-hard yellow cheese (kashkaval)', 1490, 356, 'dairy'),
28('Butter', 'Unsalted cow butter, 82 percent fat', 3000, 717, 'dairy'),
29('Sunflower oil', 'Refined sunflower oil', 3699, 884, 'oil'),
30('Olive oil', 'Extra virgin olive oil', 3699, 884, 'oil'),
31('White rice', 'Long grain white rice, uncooked', 1527, 365, 'carb'),
32('Bulgur', 'Coarse bulgur wheat, uncooked', 1431, 342, 'carb'),
33('White flour', 'Wheat flour type 500, all purpose', 1523, 364, 'carb'),
34('Spaghetti', 'Durum wheat spaghetti, dry', 1552, 371, 'carb'),
35('White bread', 'White wheat bread, baked', 1109, 265, 'carb'),
36('Oats', 'Rolled oat flakes', 1628, 389, 'carb'),
37('Potato', 'White potato, raw, peeled', 322, 77, 'vegetable'),
38('Dry white beans', 'Dry white beans (tetovec variety)', 1393, 333, 'legume'),
39('Lentils', 'Dry brown lentils', 1473, 352, 'legume'),
40('Chickpeas', 'Dry chickpeas', 1523, 364, 'legume'),
41('Tomato', 'Fresh ripe tomato', 75, 18, 'vegetable'),
42('Red bell pepper', 'Fresh red bell pepper (kapija)', 130, 31, 'vegetable'),
43('Onion', 'Yellow onion, raw', 167, 40, 'vegetable'),
44('Garlic', 'Fresh garlic cloves', 623, 149, 'vegetable'),
45('Cucumber', 'Fresh cucumber with skin', 63, 15, 'vegetable'),
46('Aubergine', 'Fresh aubergine (eggplant)', 105, 25, 'vegetable'),
47('Zucchini', 'Fresh green zucchini', 71, 17, 'vegetable'),
48('Carrot', 'Fresh carrot, raw', 172, 41, 'vegetable'),
49('Spinach', 'Fresh spinach leaves', 96, 23, 'vegetable'),
50('Cabbage', 'White cabbage, raw', 105, 25, 'vegetable'),
51('Leek', 'Fresh leek, raw', 255, 61, 'vegetable'),
52('Apple', 'Fresh apple with skin', 218, 52, 'fruit'),
53('Banana', 'Fresh banana, peeled', 372, 89, 'fruit'),
54('Strawberry', 'Fresh strawberries', 134, 32, 'fruit'),
55('Grapes', 'Fresh table grapes', 288, 69, 'fruit'),
56('Lemon', 'Fresh lemon, peeled', 121, 29, 'fruit'),
57('Walnut', 'Shelled walnut kernels', 2736, 654, 'nut'),
58('Almond', 'Raw almonds', 2423, 579, 'nut'),
59('Sunflower seed', 'Hulled sunflower seeds', 2444, 584, 'seed'),
60('Chia seed', 'Whole chia seeds', 2033, 486, 'seed'),
61('Parsley', 'Fresh flat leaf parsley', 151, 36, 'herb'),
62('Dill', 'Fresh dill weed', 180, 43, 'herb'),
63('Salt', 'Refined table salt, iodised', 0, 0, 'spice'),
64('Black pepper', 'Ground black pepper', 1050, 251, 'spice'),
65('Paprika powder', 'Sweet red paprika powder', 1180, 282, 'spice'),
66('Honey', 'Raw flower honey', 1272, 304, 'other'),
67('Water', 'Drinking water', 0, 0, 'beverage');
68
69INSERT INTO nutrient (description, quantity, unit, type) VALUES
70('Protein', 50, 'gram', 'macro'),
71('Total carbohydrate', 260, 'gram', 'macro'),
72('Total fat', 70, 'gram', 'macro'),
73('Saturated fat', 20, 'gram', 'macro'),
74('Dietary fibre', 30, 'gram', 'macro'),
75('Sugars', 90, 'gram', 'macro'),
76('Vitamin C', 80, 'milligram', 'micro'),
77('Vitamin A', 800, 'microgram', 'micro'),
78('Vitamin D', 5, 'microgram', 'micro'),
79('Vitamin E', 12, 'milligram', 'micro'),
80('Vitamin K', 75, 'microgram', 'micro'),
81('Vitamin B12', 2.5, 'microgram', 'micro'),
82('Folate', 200, 'microgram', 'micro'),
83('Calcium', 800, 'milligram', 'micro'),
84('Iron', 14, 'milligram', 'micro'),
85('Magnesium', 375, 'milligram', 'micro'),
86('Potassium', 2000, 'milligram', 'micro'),
87('Zinc', 10, 'milligram', 'micro'),
88('Sodium', 2000, 'milligram', 'micro'),
89('Phosphorus', 700, 'milligram', 'micro'),
90('Selenium', 55, 'microgram', 'micro'),
91('Iodine', 150, 'microgram', 'micro');
92
93INSERT INTO restriction (description, type) VALUES
94('Contains no ingredient of animal origin', 'vegan'),
95('Contains no meat and no fish', 'vegetarian'),
96('Prepared according to Islamic dietary law', 'halal'),
97('Prepared according to Jewish dietary law', 'kosher'),
98('Fish and seafood allowed, no other meat', 'pescatarian'),
99('Contains gluten from wheat, barley or rye', 'allergen'),
100('Contains milk protein and lactose', 'allergen'),
101('Contains tree nuts', 'allergen'),
102('Contains egg', 'allergen'),
103('Contains fish', 'allergen'),
104('Low carbohydrate and high fat, ketogenic', 'keto'),
105('No grains, legumes or processed food', 'paleo'),
106('Free of gluten containing cereals', 'gluten_free'),
107('Free of lactose', 'lactose_free'),
108('Suitable for a diabetic meal plan, low glycaemic load', 'diabetic'),
109('Reduced sodium content, under 120 mg per 100 g', 'low_sodium');
110
111INSERT INTO ingredient_contains_nutrient (ingredient_name, nutrient_id, quantity)
112SELECT m.ing, n.id, v.qty
113FROM (VALUES
114 ('Chicken breast', 31.0, 0.0, 3.6, 0.0),
115 ('Beef mince', 26.0, 0.0, 15.0, 0.0),
116 ('Pork shoulder', 20.0, 0.0, 18.0, 0.0),
117 ('Trout fillet', 20.5, 0.0, 6.6, 0.0),
118 ('Mackerel', 19.0, 0.0, 13.9, 0.0),
119 ('Egg', 12.6, 0.7, 9.5, 0.0),
120 ('Cow milk', 3.3, 4.8, 3.2, 0.0),
121 ('Yogurt', 3.5, 4.7, 3.5, 0.0),
122 ('White brine cheese', 14.0, 2.0, 21.0, 0.0),
123 ('Kashkaval', 25.0, 1.5, 28.0, 0.0),
124 ('Butter', 0.9, 0.1, 81.0, 0.0),
125 ('Sunflower oil', 0.0, 0.0, 100.0, 0.0),
126 ('Olive oil', 0.0, 0.0, 100.0, 0.0),
127 ('White rice', 7.0, 80.0, 0.7, 1.3),
128 ('Bulgur', 12.0, 76.0, 1.3, 18.0),
129 ('White flour', 10.0, 76.0, 1.0, 2.7),
130 ('Spaghetti', 13.0, 75.0, 1.5, 3.2),
131 ('White bread', 9.0, 49.0, 3.2, 2.7),
132 ('Oats', 17.0, 66.0, 7.0, 11.0),
133 ('Potato', 2.0, 17.0, 0.1, 2.2),
134 ('Dry white beans', 21.0, 60.0, 1.2, 15.0),
135 ('Lentils', 25.0, 60.0, 1.1, 30.0),
136 ('Chickpeas', 19.0, 61.0, 6.0, 17.0),
137 ('Tomato', 0.9, 3.9, 0.2, 1.2),
138 ('Red bell pepper', 1.0, 6.0, 0.3, 2.1),
139 ('Onion', 1.1, 9.3, 0.1, 1.7),
140 ('Garlic', 6.4, 33.0, 0.5, 2.1),
141 ('Cucumber', 0.7, 3.6, 0.1, 0.5),
142 ('Aubergine', 1.0, 5.9, 0.2, 3.0),
143 ('Zucchini', 1.2, 3.1, 0.3, 1.0),
144 ('Carrot', 0.9, 9.6, 0.2, 2.8),
145 ('Spinach', 2.9, 3.6, 0.4, 2.2),
146 ('Cabbage', 1.3, 5.8, 0.1, 2.5),
147 ('Leek', 1.5, 14.0, 0.3, 1.8),
148 ('Apple', 0.3, 14.0, 0.2, 2.4),
149 ('Banana', 1.1, 23.0, 0.3, 2.6),
150 ('Strawberry', 0.7, 7.7, 0.3, 2.0),
151 ('Grapes', 0.7, 18.0, 0.2, 0.9),
152 ('Lemon', 1.1, 9.3, 0.3, 2.8),
153 ('Walnut', 15.0, 14.0, 65.0, 6.7),
154 ('Almond', 21.0, 22.0, 50.0, 12.5),
155 ('Sunflower seed', 21.0, 20.0, 51.0, 8.6),
156 ('Chia seed', 17.0, 42.0, 31.0, 34.0),
157 ('Parsley', 3.0, 6.3, 0.8, 3.3),
158 ('Dill', 3.5, 7.0, 1.1, 2.1),
159 ('Paprika powder', 14.0, 54.0, 13.0, 35.0),
160 ('Black pepper', 10.0, 64.0, 3.3, 25.0),
161 ('Honey', 0.3, 82.0, 0.0, 0.2)
162) AS m(ing, protein, carbohydrate, fat, fibre)
163CROSS JOIN LATERAL (VALUES
164 ('Protein', m.protein),
165 ('Total carbohydrate', m.carbohydrate),
166 ('Total fat', m.fat),
167 ('Dietary fibre', m.fibre)
168) AS v(nut, qty)
169JOIN nutrient n ON n.description = v.nut;
170
171-- 5.b Selected micronutrients (per 100 g, in the unit of the nutrient)
172INSERT INTO ingredient_contains_nutrient (ingredient_name, nutrient_id, quantity)
173SELECT v.ing, n.id, v.qty
174FROM (VALUES
175 ('Red bell pepper', 'Vitamin C', 128.0),
176 ('Parsley', 'Vitamin C', 133.0),
177 ('Spinach', 'Vitamin C', 28.0),
178 ('Tomato', 'Vitamin C', 14.0),
179 ('Cabbage', 'Vitamin C', 36.0),
180 ('Strawberry', 'Vitamin C', 59.0),
181 ('Potato', 'Vitamin C', 20.0),
182 ('Lemon', 'Vitamin C', 53.0),
183 ('Carrot', 'Vitamin A', 835.0),
184 ('Spinach', 'Vitamin A', 469.0),
185 ('Red bell pepper', 'Vitamin A', 157.0),
186 ('Butter', 'Vitamin A', 684.0),
187 ('Trout fillet', 'Vitamin D', 6.5),
188 ('Mackerel', 'Vitamin D', 16.0),
189 ('Egg', 'Vitamin D', 2.0),
190 ('Beef mince', 'Vitamin B12', 2.6),
191 ('Trout fillet', 'Vitamin B12', 4.5),
192 ('Mackerel', 'Vitamin B12', 8.7),
193 ('Egg', 'Vitamin B12', 1.1),
194 ('Cow milk', 'Vitamin B12', 0.5),
195 ('Spinach', 'Folate', 194.0),
196 ('Lentils', 'Folate', 479.0),
197 ('Dry white beans', 'Folate', 388.0),
198 ('Parsley', 'Folate', 152.0),
199 ('Cow milk', 'Calcium', 120.0),
200 ('Yogurt', 'Calcium', 121.0),
201 ('White brine cheese','Calcium', 493.0),
202 ('Kashkaval', 'Calcium', 700.0),
203 ('Spinach', 'Calcium', 99.0),
204 ('Almond', 'Calcium', 269.0),
205 ('Chia seed', 'Calcium', 631.0),
206 ('Spinach', 'Iron', 2.7),
207 ('Lentils', 'Iron', 7.5),
208 ('Dry white beans', 'Iron', 5.5),
209 ('Beef mince', 'Iron', 2.6),
210 ('Oats', 'Iron', 4.7),
211 ('Chia seed', 'Iron', 7.7),
212 ('Almond', 'Magnesium', 270.0),
213 ('Oats', 'Magnesium', 177.0),
214 ('Spinach', 'Magnesium', 79.0),
215 ('Walnut', 'Magnesium', 158.0),
216 ('Potato', 'Potassium', 425.0),
217 ('Banana', 'Potassium', 358.0),
218 ('Spinach', 'Potassium', 558.0),
219 ('Tomato', 'Potassium', 237.0),
220 ('Dry white beans', 'Potassium', 1795.0),
221 ('Beef mince', 'Zinc', 4.8),
222 ('Lentils', 'Zinc', 3.3),
223 ('Oats', 'Zinc', 4.0),
224 ('Salt', 'Sodium', 38758.0),
225 ('White brine cheese','Sodium', 1200.0),
226 ('White bread', 'Sodium', 490.0),
227 ('Kashkaval', 'Sodium', 800.0),
228 ('Salt', 'Iodine', 2500.0),
229 ('Trout fillet', 'Selenium', 12.6),
230 ('Mackerel', 'Selenium', 44.1),
231 ('Chicken breast', 'Selenium', 22.8),
232 ('Chicken breast', 'Phosphorus', 210.0),
233 ('Cow milk', 'Phosphorus', 92.0),
234 ('Almond', 'Vitamin E', 25.6),
235 ('Sunflower oil', 'Vitamin E', 41.1),
236 ('Olive oil', 'Vitamin E', 14.4),
237 ('Spinach', 'Vitamin K', 483.0),
238 ('Parsley', 'Vitamin K', 1640.0)
239) AS v(ing, nut, qty)
240JOIN nutrient n ON n.description = v.nut;
241
242INSERT INTO recipe (name, guide, kcal_sum, servings, user_email) VALUES
243('Tavce Gravce',
244 E'1. Soak the beans overnight, then boil until soft.\n2. Fry the chopped onion in oil, add paprika off the heat.\n3. Combine everything in a clay pan, season and bake at 200 C for 40 minutes.\n4. Serve hot with fresh bread.',
245 0, 6, 'marija.trajkovska@gmail.com'),
246
247('Shopska Salad',
248 E'1. Dice the tomatoes and cucumbers, slice the onion thinly.\n2. Season with salt and olive oil and toss.\n3. Grate the white brine cheese generously over the top and serve immediately.',
249 0, 4, 'stefan.petrov@gmail.com'),
250
251('Turli Tava',
252 E'1. Brown the beef mince with the onion.\n2. Cut all vegetables into large chunks and layer them in a deep tray.\n3. Add the meat, oil, spices and a cup of water.\n4. Bake covered at 190 C for 60 minutes, then 15 minutes uncovered.',
253 0, 6, 'ana.jovanovska@yahoo.com'),
254
255('Homemade Ajvar',
256 E'1. Roast the peppers and aubergine until the skins blister, then peel them.\n2. Mince the flesh and cook it slowly in oil, stirring constantly for about two hours.\n3. Add garlic and salt near the end and jar while hot.',
257 0, 20, 'elena.dimitrova@hotmail.com'),
258
259('Grilled Trout with Parsley',
260 E'1. Clean the fillets and pat them dry.\n2. Rub with olive oil, crushed garlic, salt and pepper.\n3. Grill 4 minutes per side and finish with chopped parsley and lemon.',
261 0, 2, 'nikola.stojanov@gmail.com'),
262
263('Chicken Bulgur Bowl',
264 E'1. Boil the bulgur in twice its volume of water for 12 minutes.\n2. Grill the seasoned chicken breast and slice it.\n3. Assemble with diced tomato and cucumber, finish with olive oil.',
265 0, 3, 'viktor.ilievski@nutritioneer.mk'),
266
267('Oat Porridge with Banana and Walnuts',
268 E'1. Simmer the oats in milk for 5 minutes, stirring.\n2. Pour into a bowl and top with sliced banana and crushed walnuts.\n3. Drizzle honey over the top.',
269 0, 2, 'marija.trajkovska@gmail.com'),
270
271('Lentil Soup',
272 E'1. Saute the diced onion, carrot and garlic in olive oil.\n2. Add the rinsed lentils and 1.5 litres of water.\n3. Simmer for 35 minutes, season and blend half of the soup for a thicker texture.',
273 0, 5, 'bojan.kostov@gmail.com'),
274
275('Zelnik Spinach and Cheese Filling',
276 E'1. Wilt the washed spinach and squeeze out all excess water.\n2. Crumble in the white brine cheese and mix with beaten egg and oil.\n3. Season lightly - the cheese is already salty. Use as filling for zelnik.',
277 0, 8, 'elena.dimitrova@hotmail.com'),
278
279('Tarator',
280 E'1. Grate the cucumber and drain it briefly.\n2. Whisk the yogurt with crushed garlic, chopped dill, salt and olive oil.\n3. Fold in the cucumber and crushed walnuts, chill for one hour before serving.',
281 0, 4, 'ana.jovanovska@yahoo.com'),
282
283('Baked Mackerel with Vegetables',
284 E'1. Slice the potatoes, carrots and onion and spread them on a tray with oil and salt.\n2. Bake at 200 C for 25 minutes.\n3. Place the cleaned mackerel on top and bake for another 20 minutes.',
285 0, 3, 'nikola.stojanov@gmail.com'),
286
287('Protein Pancakes',
288 E'1. Blend the oats, eggs, milk and banana into a smooth batter.\n2. Rest the batter for 10 minutes.\n3. Fry small pancakes in a little butter over medium heat, about 2 minutes per side.',
289 0, 3, 'teodora.mihajlova@nutritioneer.mk'),
290
291('Grilled Chicken Salad',
292 E'1. Grill the seasoned chicken breast and let it rest, then slice.\n2. Toss the spinach, tomato and cucumber with olive oil and salt.\n3. Arrange the chicken on top and serve at room temperature.',
293 0, 2, 'stefan.petrov@gmail.com'),
294
295('Stuffed Peppers with Rice',
296 E'1. Core the peppers carefully without splitting them.\n2. Mix the rice, browned mince, onion, oil and spices.\n3. Fill the peppers, stand them upright in a pot with water and simmer for 50 minutes.',
297 0, 4, 'filip.angelov@gmail.com');
298
299INSERT INTO recipe_contains_ingredient (recipe_id, ingredient_name, ingredient_quantity)
300SELECT r.id, v.ing, v.qty
301FROM (VALUES
302 -- Tavce Gravce
303 ('Tavce Gravce','Dry white beans',500), ('Tavce Gravce','Onion',200),
304 ('Tavce Gravce','Red bell pepper',100), ('Tavce Gravce','Sunflower oil',60),
305 ('Tavce Gravce','Paprika powder',10), ('Tavce Gravce','Salt',8),
306 ('Tavce Gravce','Black pepper',2), ('Tavce Gravce','Parsley',15),
307 -- Shopska Salad
308 ('Shopska Salad','Tomato',400), ('Shopska Salad','Cucumber',300),
309 ('Shopska Salad','Onion',80), ('Shopska Salad','White brine cheese',150),
310 ('Shopska Salad','Olive oil',30), ('Shopska Salad','Salt',5),
311 -- Turli Tava
312 ('Turli Tava','Beef mince',300), ('Turli Tava','Potato',400),
313 ('Turli Tava','Aubergine',300), ('Turli Tava','Zucchini',250),
314 ('Turli Tava','Red bell pepper',200), ('Turli Tava','Tomato',250),
315 ('Turli Tava','Onion',150), ('Turli Tava','Sunflower oil',50),
316 ('Turli Tava','Salt',10), ('Turli Tava','Black pepper',3),
317 ('Turli Tava','Paprika powder',8),
318 -- Homemade Ajvar
319 ('Homemade Ajvar','Red bell pepper',2000), ('Homemade Ajvar','Aubergine',500),
320 ('Homemade Ajvar','Garlic',30), ('Homemade Ajvar','Sunflower oil',200),
321 ('Homemade Ajvar','Salt',20),
322 -- Grilled Trout with Parsley
323 ('Grilled Trout with Parsley','Trout fillet',400), ('Grilled Trout with Parsley','Olive oil',20),
324 ('Grilled Trout with Parsley','Garlic',10), ('Grilled Trout with Parsley','Parsley',20),
325 ('Grilled Trout with Parsley','Lemon',60), ('Grilled Trout with Parsley','Salt',5),
326 ('Grilled Trout with Parsley','Black pepper',2),
327 -- Chicken Bulgur Bowl
328 ('Chicken Bulgur Bowl','Chicken breast',400), ('Chicken Bulgur Bowl','Bulgur',200),
329 ('Chicken Bulgur Bowl','Tomato',150), ('Chicken Bulgur Bowl','Cucumber',100),
330 ('Chicken Bulgur Bowl','Olive oil',20), ('Chicken Bulgur Bowl','Salt',5),
331 -- Oat Porridge with Banana and Walnuts
332 ('Oat Porridge with Banana and Walnuts','Oats',80), ('Oat Porridge with Banana and Walnuts','Cow milk',250),
333 ('Oat Porridge with Banana and Walnuts','Banana',120),('Oat Porridge with Banana and Walnuts','Walnut',30),
334 ('Oat Porridge with Banana and Walnuts','Honey',20),
335 -- Lentil Soup
336 ('Lentil Soup','Lentils',300), ('Lentil Soup','Carrot',150),
337 ('Lentil Soup','Onion',120), ('Lentil Soup','Garlic',15),
338 ('Lentil Soup','Olive oil',30), ('Lentil Soup','Salt',8),
339 ('Lentil Soup','Black pepper',2),
340 -- Zelnik Spinach and Cheese Filling
341 ('Zelnik Spinach and Cheese Filling','Spinach',500), ('Zelnik Spinach and Cheese Filling','White brine cheese',300),
342 ('Zelnik Spinach and Cheese Filling','Egg',120), ('Zelnik Spinach and Cheese Filling','Sunflower oil',40),
343 ('Zelnik Spinach and Cheese Filling','Salt',6),
344 -- Tarator
345 ('Tarator','Yogurt',500), ('Tarator','Cucumber',300),
346 ('Tarator','Garlic',10), ('Tarator','Dill',15),
347 ('Tarator','Olive oil',15), ('Tarator','Walnut',25),
348 ('Tarator','Salt',4),
349 -- Baked Mackerel with Vegetables
350 ('Baked Mackerel with Vegetables','Mackerel',500), ('Baked Mackerel with Vegetables','Potato',300),
351 ('Baked Mackerel with Vegetables','Carrot',150), ('Baked Mackerel with Vegetables','Onion',100),
352 ('Baked Mackerel with Vegetables','Olive oil',25), ('Baked Mackerel with Vegetables','Salt',6),
353 ('Baked Mackerel with Vegetables','Black pepper',2),
354 -- Protein Pancakes
355 ('Protein Pancakes','Oats',100), ('Protein Pancakes','Egg',150),
356 ('Protein Pancakes','Cow milk',150),('Protein Pancakes','Banana',100),
357 ('Protein Pancakes','Butter',15),
358 -- Grilled Chicken Salad
359 ('Grilled Chicken Salad','Chicken breast',300), ('Grilled Chicken Salad','Spinach',150),
360 ('Grilled Chicken Salad','Tomato',150), ('Grilled Chicken Salad','Cucumber',100),
361 ('Grilled Chicken Salad','Olive oil',20), ('Grilled Chicken Salad','Salt',4),
362 -- Stuffed Peppers with Rice
363 ('Stuffed Peppers with Rice','Red bell pepper',600), ('Stuffed Peppers with Rice','White rice',200),
364 ('Stuffed Peppers with Rice','Beef mince',250), ('Stuffed Peppers with Rice','Onion',100),
365 ('Stuffed Peppers with Rice','Sunflower oil',40), ('Stuffed Peppers with Rice','Salt',8),
366 ('Stuffed Peppers with Rice','Paprika powder',5)
367) AS v(recipe_name, ing, qty)
368JOIN recipe r ON r.name = v.recipe_name;
369
370INSERT INTO recipe_contains_restriction (recipe_id, restriction_id)
371SELECT r.id, res.id
372FROM (VALUES
373 ('Tavce Gravce','Contains no ingredient of animal origin'),
374 ('Tavce Gravce','Contains no meat and no fish'),
375 ('Tavce Gravce','Free of gluten containing cereals'),
376 ('Tavce Gravce','Free of lactose'),
377 ('Shopska Salad','Contains no meat and no fish'),
378 ('Shopska Salad','Contains milk protein and lactose'),
379 ('Shopska Salad','Free of gluten containing cereals'),
380 ('Turli Tava','Free of gluten containing cereals'),
381 ('Turli Tava','Free of lactose'),
382 ('Homemade Ajvar','Contains no ingredient of animal origin'),
383 ('Homemade Ajvar','Contains no meat and no fish'),
384 ('Homemade Ajvar','Free of gluten containing cereals'),
385 ('Homemade Ajvar','Free of lactose'),
386 ('Grilled Trout with Parsley','Fish and seafood allowed, no other meat'),
387 ('Grilled Trout with Parsley','Contains fish'),
388 ('Grilled Trout with Parsley','Free of gluten containing cereals'),
389 ('Grilled Trout with Parsley','Low carbohydrate and high fat, ketogenic'),
390 ('Chicken Bulgur Bowl','Contains gluten from wheat, barley or rye'),
391 ('Chicken Bulgur Bowl','Free of lactose'),
392 ('Oat Porridge with Banana and Walnuts','Contains no meat and no fish'),
393 ('Oat Porridge with Banana and Walnuts','Contains milk protein and lactose'),
394 ('Oat Porridge with Banana and Walnuts','Contains tree nuts'),
395 ('Lentil Soup','Contains no ingredient of animal origin'),
396 ('Lentil Soup','Contains no meat and no fish'),
397 ('Lentil Soup','Free of gluten containing cereals'),
398 ('Lentil Soup','Free of lactose'),
399 ('Zelnik Spinach and Cheese Filling','Contains no meat and no fish'),
400 ('Zelnik Spinach and Cheese Filling','Contains milk protein and lactose'),
401 ('Zelnik Spinach and Cheese Filling','Contains egg'),
402 ('Tarator','Contains no meat and no fish'),
403 ('Tarator','Contains milk protein and lactose'),
404 ('Tarator','Contains tree nuts'),
405 ('Tarator','Free of gluten containing cereals'),
406 ('Baked Mackerel with Vegetables','Fish and seafood allowed, no other meat'),
407 ('Baked Mackerel with Vegetables','Contains fish'),
408 ('Baked Mackerel with Vegetables','Free of lactose'),
409 ('Protein Pancakes','Contains no meat and no fish'),
410 ('Protein Pancakes','Contains milk protein and lactose'),
411 ('Protein Pancakes','Contains egg'),
412 ('Grilled Chicken Salad','Free of gluten containing cereals'),
413 ('Grilled Chicken Salad','Free of lactose'),
414 ('Grilled Chicken Salad','Suitable for a diabetic meal plan, low glycaemic load'),
415 ('Stuffed Peppers with Rice','Free of gluten containing cereals'),
416 ('Stuffed Peppers with Rice','Free of lactose')
417) AS v(recipe_name, restriction_description)
418JOIN recipe r ON r.name = v.recipe_name
419JOIN restriction res ON res.description = v.restriction_description;
420
421INSERT INTO post (created_by, recipe_id, is_private, is_favourite, status, created_at, media, media_type, media_name)
422VALUES ('marija.trajkovska@gmail.com', NULL, TRUE, FALSE, 'published', '2026-01-30 07:30:00',
423 decode('iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVR42mP8z8BQDwAEhQGAhKmMIQAAAABJRU5ErkJggg==', 'base64'),
424 'image/png', 'progress_week4.png');
425
426INSERT INTO post (created_by, recipe_id, is_private, is_favourite, status, created_at, media, media_type, media_name)
427SELECT v.author, r.id, v.is_private, v.is_favourite, v.status::post_status, v.created_at::timestamp, NULL, NULL, v.media_name
428FROM (VALUES
429 ('marija.trajkovska@gmail.com', 'Tavce Gravce', FALSE, TRUE, 'published', '2026-01-12 18:20:00', 'tavce_gravce.jpg'),
430 ('stefan.petrov@gmail.com', 'Shopska Salad', FALSE, FALSE, 'published', '2026-01-14 12:05:00', 'shopska.jpg'),
431 ('ana.jovanovska@yahoo.com', 'Turli Tava', FALSE, TRUE, 'published', '2026-01-15 19:40:00', 'turli_tava.jpg'),
432 ('elena.dimitrova@hotmail.com', 'Homemade Ajvar', FALSE, TRUE, 'published', '2026-01-18 16:10:00', 'ajvar_jars.jpg'),
433 ('nikola.stojanov@gmail.com', 'Grilled Trout with Parsley', FALSE, FALSE, 'published', '2026-01-20 20:00:00', 'trout.jpg'),
434 ('viktor.ilievski@nutritioneer.mk', 'Chicken Bulgur Bowl', FALSE, TRUE, 'published', '2026-01-21 13:30:00', 'bulgur_bowl.jpg'),
435 ('marija.trajkovska@gmail.com', 'Oat Porridge with Banana and Walnuts', TRUE, FALSE, 'published', '2026-01-22 08:15:00', 'porridge.jpg'),
436 ('bojan.kostov@gmail.com', 'Lentil Soup', FALSE, FALSE, 'draft', '2026-01-23 17:45:00', NULL),
437 ('elena.dimitrova@hotmail.com', 'Zelnik Spinach and Cheese Filling', FALSE, FALSE, 'archived', '2026-01-05 11:00:00', 'zelnik.jpg'),
438 ('ana.jovanovska@yahoo.com', 'Tarator', FALSE, TRUE, 'published', '2026-01-25 14:20:00', 'tarator.jpg'),
439 ('nikola.stojanov@gmail.com', 'Baked Mackerel with Vegetables', FALSE, FALSE, 'published', '2026-01-26 19:10:00', 'mackerel.jpg'),
440 ('teodora.mihajlova@nutritioneer.mk','Protein Pancakes', FALSE, TRUE, 'published', '2026-01-27 09:00:00', 'pancakes.jpg'),
441 ('stefan.petrov@gmail.com', 'Grilled Chicken Salad', TRUE, FALSE, 'draft', '2026-01-28 12:40:00', NULL),
442 ('filip.angelov@gmail.com', 'Stuffed Peppers with Rice', FALSE, FALSE, 'published', '2026-01-29 18:55:00', 'peppers.jpg')
443) AS v(author, recipe_name, is_private, is_favourite, status, created_at, media_name)
444JOIN recipe r ON r.name = v.recipe_name;
445
446INSERT INTO comment (description, created_at, post_id, created_by)
447SELECT v.body, v.created_at::timestamp, p.id, v.author
448FROM (VALUES
449 ('Tavce Gravce', 'stefan.petrov@gmail.com', 'Exactly how my grandmother made it. Adding it to my planner.', '2026-01-12 19:02:00'),
450 ('Tavce Gravce', 'ana.jovanovska@yahoo.com', 'How long do the beans need to soak?', '2026-01-13 09:11:00'),
451 ('Tavce Gravce', 'marija.trajkovska@gmail.com', 'At least eight hours, overnight is best.', '2026-01-13 09:40:00'),
452 ('Shopska Salad', 'elena.dimitrova@hotmail.com', 'Simple and perfect. I use half the cheese to cut the sodium.', '2026-01-14 13:20:00'),
453 ('Turli Tava', 'bojan.kostov@gmail.com', 'Made it without the mince, still excellent.', '2026-01-16 20:15:00'),
454 ('Turli Tava', 'viktor.ilievski@nutritioneer.mk', 'Good macro balance for a rest day.', '2026-01-16 21:00:00'),
455 ('Homemade Ajvar', 'marija.trajkovska@gmail.com', 'Two hours of stirring is no joke but worth every minute.', '2026-01-19 10:05:00'),
456 ('Homemade Ajvar', 'filip.angelov@gmail.com', 'How many jars does this quantity fill?', '2026-01-19 11:30:00'),
457 ('Grilled Trout with Parsley', 'teodora.mihajlova@nutritioneer.mk','Very clean protein source, great for a cutting phase.', '2026-01-21 08:45:00'),
458 ('Chicken Bulgur Bowl', 'stefan.petrov@gmail.com', 'This is now my default meal prep for the week.', '2026-01-21 18:10:00'),
459 ('Chicken Bulgur Bowl', 'nikola.stojanov@gmail.com', 'Swapped bulgur for rice to keep it gluten free.', '2026-01-22 12:25:00'),
460 ('Tarator', 'stefan.petrov@gmail.com', 'Draining the cucumber really does make a difference.', '2026-01-25 16:00:00'),
461 ('Baked Mackerel with Vegetables','ana.jovanovska@yahoo.com', 'The vitamin D content on this one is impressive.', '2026-01-27 08:20:00'),
462 ('Protein Pancakes', 'marija.trajkovska@gmail.com', 'Thirty grams of protein in a breakfast, finally.', '2026-01-27 10:15:00'),
463 ('Stuffed Peppers with Rice', 'elena.dimitrova@hotmail.com', 'Try it with a spoon of ajvar mixed into the filling.', '2026-01-30 09:05:00')
464) AS v(recipe_name, author, body, created_at)
465JOIN recipe r ON r.name = v.recipe_name
466JOIN post p ON p.recipe_id = r.id;
467
468
469INSERT INTO grocery_list (date_time, notes, is_bought, kcal, user_email) VALUES
470('2026-01-11 10:00:00', 'Weekly shopping - Sunday cooking', TRUE, 0, 'marija.trajkovska@gmail.com'),
471('2026-01-14 17:30:00', 'Vegetables for the tray bake', TRUE, 0, 'ana.jovanovska@yahoo.com'),
472('2026-01-17 09:15:00', 'Ajvar season - green market', TRUE, 0, 'elena.dimitrova@hotmail.com'),
473('2026-01-19 18:00:00', 'Fish market on Saturday morning', FALSE, 0, 'nikola.stojanov@gmail.com'),
474('2026-01-22 11:45:00', 'Cheap protein week', FALSE, 0, 'bojan.kostov@gmail.com');
475
476-- Bulk add
477INSERT INTO grocery_list_bulk_add_ingredient (list_id, recipe_id)
478SELECT g.id, r.id
479FROM (VALUES
480 ('marija.trajkovska@gmail.com', '2026-01-11 10:00:00', 'Tavce Gravce'),
481 ('marija.trajkovska@gmail.com', '2026-01-11 10:00:00', 'Oat Porridge with Banana and Walnuts'),
482 ('ana.jovanovska@yahoo.com', '2026-01-14 17:30:00', 'Turli Tava'),
483 ('ana.jovanovska@yahoo.com', '2026-01-14 17:30:00', 'Tarator'),
484 ('elena.dimitrova@hotmail.com', '2026-01-17 09:15:00', 'Homemade Ajvar'),
485 ('nikola.stojanov@gmail.com', '2026-01-19 18:00:00', 'Grilled Trout with Parsley'),
486 ('nikola.stojanov@gmail.com', '2026-01-19 18:00:00', 'Baked Mackerel with Vegetables'),
487 ('bojan.kostov@gmail.com', '2026-01-22 11:45:00', 'Lentil Soup')
488) AS v(user_email, dt, recipe_name)
489JOIN grocery_list g ON g.user_email = v.user_email AND g.date_time = v.dt::timestamp
490JOIN recipe r ON r.name = v.recipe_name;
491
492-- Single add
493INSERT INTO grocery_list_single_add_ingredient (list_id, ingredient_name, buy_quantity)
494SELECT g.id, v.ing, v.qty
495FROM (VALUES
496 ('marija.trajkovska@gmail.com', '2026-01-11 10:00:00', 'Sunflower oil', 1000),
497 ('marija.trajkovska@gmail.com', '2026-01-11 10:00:00', 'Salt', 500),
498 ('marija.trajkovska@gmail.com', '2026-01-11 10:00:00', 'Apple', 1500),
499 ('ana.jovanovska@yahoo.com', '2026-01-14 17:30:00', 'Potato', 2000),
500 ('ana.jovanovska@yahoo.com', '2026-01-14 17:30:00', 'White bread', 500),
501 ('elena.dimitrova@hotmail.com', '2026-01-17 09:15:00', 'Red bell pepper', 5000),
502 ('elena.dimitrova@hotmail.com', '2026-01-17 09:15:00', 'Garlic', 150),
503 ('nikola.stojanov@gmail.com', '2026-01-19 18:00:00', 'Olive oil', 750),
504 ('nikola.stojanov@gmail.com', '2026-01-19 18:00:00', 'Lemon', 300),
505 ('bojan.kostov@gmail.com', '2026-01-22 11:45:00', 'Chickpeas', 1000),
506 ('bojan.kostov@gmail.com', '2026-01-22 11:45:00', 'Carrot', 800),
507 ('bojan.kostov@gmail.com', '2026-01-22 11:45:00', 'Onion', 700)
508) AS v(user_email, dt, ing, qty)
509JOIN grocery_list g ON g.user_email = v.user_email AND g.date_time = v.dt::timestamp;
510
511INSERT INTO intake_planner (date_time, notes, kcal, is_consumed, user_email) VALUES
512('2026-01-22 08:30:00', 'Breakfast', 0, TRUE, 'marija.trajkovska@gmail.com'),
513('2026-01-22 13:00:00', 'Lunch at home', 0, TRUE, 'marija.trajkovska@gmail.com'),
514('2026-01-22 19:30:00', 'Light dinner', 0, TRUE, 'marija.trajkovska@gmail.com'),
515('2026-01-23 08:30:00', 'Breakfast', 0, TRUE, 'marija.trajkovska@gmail.com'),
516('2026-01-23 13:15:00', 'Lunch', 0, TRUE, 'marija.trajkovska@gmail.com'),
517('2026-01-24 09:00:00', 'Late breakfast after training', 0, TRUE, 'marija.trajkovska@gmail.com'),
518('2026-01-24 20:00:00', 'Dinner with family', 0, TRUE, 'marija.trajkovska@gmail.com'),
519('2026-01-25 12:30:00', 'Planned lunch - not eaten yet', 0, FALSE, 'marija.trajkovska@gmail.com'),
520('2026-01-22 12:45:00', 'Meal prep box 1', 0, TRUE, 'stefan.petrov@gmail.com'),
521('2026-01-23 12:45:00', 'Meal prep box 2', 0, TRUE, 'stefan.petrov@gmail.com'),
522('2026-01-26 19:45:00', 'Dinner', 0, TRUE, 'nikola.stojanov@gmail.com'),
523('2026-01-27 09:30:00', 'Post workout breakfast', 0, TRUE, 'teodora.mihajlova@nutritioneer.mk'),
524('2026-01-28 13:00:00', 'Lunch', 0, TRUE, 'ana.jovanovska@yahoo.com'),
525('2026-01-29 10:00:00', 'Coffee and fruit only', 180, TRUE, 'bojan.kostov@gmail.com');
526
527INSERT INTO intake_planner_save_to_list_post (planner_id, post_id)
528SELECT ip.id, p.id
529FROM (VALUES
530 ('marija.trajkovska@gmail.com', '2026-01-22 08:30:00', 'Oat Porridge with Banana and Walnuts'),
531 ('marija.trajkovska@gmail.com', '2026-01-22 13:00:00', 'Tavce Gravce'),
532 ('marija.trajkovska@gmail.com', '2026-01-22 19:30:00', 'Shopska Salad'),
533 ('marija.trajkovska@gmail.com', '2026-01-23 08:30:00', 'Protein Pancakes'),
534 ('marija.trajkovska@gmail.com', '2026-01-23 13:15:00', 'Chicken Bulgur Bowl'),
535 ('marija.trajkovska@gmail.com', '2026-01-24 09:00:00', 'Oat Porridge with Banana and Walnuts'),
536 ('marija.trajkovska@gmail.com', '2026-01-24 20:00:00', 'Turli Tava'),
537 ('marija.trajkovska@gmail.com', '2026-01-25 12:30:00', 'Grilled Trout with Parsley'),
538 ('stefan.petrov@gmail.com', '2026-01-22 12:45:00', 'Chicken Bulgur Bowl'),
539 ('stefan.petrov@gmail.com', '2026-01-23 12:45:00', 'Chicken Bulgur Bowl'),
540 ('nikola.stojanov@gmail.com', '2026-01-26 19:45:00', 'Baked Mackerel with Vegetables'),
541 ('teodora.mihajlova@nutritioneer.mk','2026-01-27 09:30:00','Protein Pancakes'),
542 ('ana.jovanovska@yahoo.com', '2026-01-28 13:00:00', 'Tarator'),
543 ('ana.jovanovska@yahoo.com', '2026-01-28 13:00:00', 'Stuffed Peppers with Rice')
544) AS v(user_email, dt, recipe_name)
545JOIN intake_planner ip ON ip.user_email = v.user_email AND ip.date_time = v.dt::timestamp
546JOIN recipe r ON r.name = v.recipe_name
547JOIN post p ON p.recipe_id = r.id;
548
549INSERT INTO biometrics (date, weight, height, age, muscle_fat_ratio, user_email) VALUES
550('2026-01-05', 74.5, 168, 29, 1.80, 'marija.trajkovska@gmail.com'),
551('2026-01-12', 73.8, 168, 29, 1.84, 'marija.trajkovska@gmail.com'),
552('2026-01-19', 73.1, 168, 29, 1.90, 'marija.trajkovska@gmail.com'),
553('2026-01-26', 72.4, 168, 29, 1.95, 'marija.trajkovska@gmail.com'),
554('2026-01-06', 88.2, 182, 34, 2.10, 'stefan.petrov@gmail.com'),
555('2026-01-20', 87.0, 182, 34, 2.18, 'stefan.petrov@gmail.com'),
556('2026-01-27', 86.3, 182, 34, 2.24, 'stefan.petrov@gmail.com'),
557('2026-01-10', 61.0, 163, 41, 1.65, 'ana.jovanovska@yahoo.com'),
558('2026-01-24', 60.4, 163, 41, 1.70, 'ana.jovanovska@yahoo.com'),
559('2026-01-08', 95.7, 190, 27, 2.35, 'nikola.stojanov@gmail.com'),
560('2026-01-22', 94.9, 190, 27, 2.40, 'nikola.stojanov@gmail.com'),
561('2026-01-15', 68.3, 171, 36, 1.72, 'elena.dimitrova@hotmail.com'),
562('2026-01-29', 67.9, 171, 36, 1.75, 'elena.dimitrova@hotmail.com'),
563('2026-01-18', 79.0, 176, 31, 1.60, 'bojan.kostov@gmail.com');
Note: See TracBrowser for help on using the repository browser.