Changes between Version 1 and Version 2 of DatabaseCreation

Timestamp:

05/26/26 09:19:41 (17 hours ago)

Author:

231550

Comment:

--

DatabaseCreation

@@ +1 @@
+== DDL ==
 -- Category
 CREATE TABLE Category (
@@ -340 +341 @@
 
 ALTER TABLE Exercise ALTER COLUMN activity_id SET NOT NULL;
+
+== Views ==
+
+-- 1. User Profile Overview
+CREATE VIEW vw_user_profile AS
+SELECT
+    u.id,
+    u.email,
+    u.height,
+    u.weight,
+    u.gender,
+    u.created_at,
+    r.name AS role,
+    d.name AS active_diet,
+    ud.start_date AS diet_start,
+    ud.end_date AS diet_end
+FROM "User" u
+LEFT JOIN User_roles ur ON u.id = ur.user_id
+LEFT JOIN Roles r ON ur.role_id = r.id
+LEFT JOIN User_Diets ud ON u.id = ud.user_id
+LEFT JOIN Diet d ON ud.Diet_id = d.id;
+
+-- 2. Daily Food Log Summary
+CREATE VIEW vw_daily_food_summary AS
+SELECT
+    fl.UserId,
+    DATE(fl.dateTime) AS log_date,
+    fl.meal_type,
+    SUM(CASE WHEN LOWER(n.nutrient) LIKE '%energy-kcal%'
+        THEN fn.amount * fl.quantity / 100 ELSE 0 END) AS total_calories,
+    SUM(CASE WHEN LOWER(n.nutrient) LIKE '%protein%'
+        THEN fn.amount * fl.quantity / 100 ELSE 0 END) AS total_protein,
+    SUM(CASE WHEN LOWER(n.nutrient) LIKE '%fat%'
+        THEN fn.amount * fl.quantity / 100 ELSE 0 END) AS total_fat,
+    SUM(CASE WHEN LOWER(n.nutrient) LIKE '%carbohydrate%'
+        THEN fn.amount * fl.quantity / 100 ELSE 0 END) AS total_carbs,
+    COUNT(fl.id) AS total_entries
+FROM FoodLogs fl
+JOIN Food_nutrients fn ON fl.FoodId = fn.FoodId
+JOIN Nutrient n ON fn.NutrientId = n.id
+GROUP BY fl.UserId, DATE(fl.dateTime), fl.meal_type;
+
+-- 3. Exercise Calories Burned per User
+CREATE VIEW vw_user_calories_burned AS
+SELECT
+    al.UserId,
+    DATE(al.dateTime) AS log_date,
+    SUM(al.Calories_burned) AS total_calories_burned,
+    SUM(al.Duration_minutes) AS total_minutes,
+    COUNT(al.id) AS total_sessions
+FROM ActivityLogs al
+GROUP BY al.UserId, DATE(al.dateTime);
+
+-- 4. Food Nutritional Info
+CREATE VIEW vw_food_nutrition AS
+SELECT
+    f.id AS food_id,
+    f.name AS food_name,
+    f.calories_per_100g,
+    c.name AS category,
+    n.nutrient AS nutrient_name,
+    fn.amount,
+    fn.unit
+FROM Food f
+LEFT JOIN Category c ON f.category_id = c.category_id
+LEFT JOIN Food_nutrients fn ON f.id = fn.FoodId
+LEFT JOIN Nutrient n ON fn.NutrientId = n.id;
+
+-- 5. User Achievements Progress
+CREATE VIEW vw_user_achievements AS
+SELECT
+    u.id AS user_id,
+    u.email,
+    a.name AS achievement_name,
+    a.requirement_type,
+    a.target_value,
+    a.period_type,
+    ua.dateEarned
+FROM "User" u
+JOIN User_achievements ua ON u.id = ua.user_id
+JOIN Achievements a ON ua.achievement_id = a.id
+ORDER BY ua.dateEarned DESC;
+
+-- 6. User Streak Summary
+CREATE VIEW vw_user_streaks AS
+SELECT
+    u.id AS user_id,
+    u.email,
+    st.name AS streak_type,
+    st.metric_type,
+    s.current_count,
+    s.longest_count,
+    s.last_updated
+FROM "User" u
+JOIN Streak s ON u.id = s.User_id
+JOIN Streak_type st ON s.Streak_type_id = st.id;
+
+-- 7. Body Measurements History
+CREATE VIEW vw_body_measurements AS
+SELECT
+    u.id AS user_id,
+    u.email,
+    bm.weight,
+    bm.waist,
+    bm.body_fat,
+    bm.dateTime AS measured_at
+FROM "User" u
+JOIN BodyMeasurements bm ON u.id = bm.User_id
+ORDER BY bm.dateTime DESC;
+
+-- 8. Diet Plan Details
+CREATE VIEW vw_diet_plan AS
+SELECT
+    d.id AS diet_id,
+    d.name AS diet_name,
+    d.calorie_target,
+    d.protein_target,
+    d.carb_target,
+    d.fat_target,
+    f.name AS food_name,
+    df.amount AS food_amount,
+    df.unit AS food_unit,
+    e.name AS exercise_name,
+    det.frequency_per_week
+FROM Diet d
+LEFT JOIN Diet_foods df ON d.id = df.DietId
+LEFT JOIN Food f ON df.FoodId = f.id
+LEFT JOIN Diet_Exercise_Targets det ON d.id = det.DietId
+LEFT JOIN Exercise e ON det.ExerciseId = e.id;
+
+-- 9. Exercise Library
+CREATE VIEW vw_exercise_library AS
+SELECT
+    e.id AS exercise_id,
+    e.name AS exercise_name,
+    e.calories_per_minute,
+    a.name AS activity_type,
+    STRING_AGG(m.name, ', ') AS muscles_targeted
+FROM Exercise e
+LEFT JOIN Activity a ON e.activity_id = a.id
+LEFT JOIN Exercise_muscles em ON e.id = em.Exercise_id
+LEFT JOIN Muscle m ON em.Muscle_id = m.id
+GROUP BY e.id, e.name, e.calories_per_minute, a.name;
+