Changes between Initial Version and Version 1 of DatabaseProgramming

Timestamp:

05/26/26 11:11:34 (14 hours ago)

Author:

231550

Comment:

--

DatabaseProgramming

@@ +1 @@
+== Procedures/Functions/Triggers ==
+
+-- 1. FUNCTION: Calculate total calories for a user on a specific date
+CREATE OR REPLACE FUNCTION get_user_daily_calories(
+    p_user_id BIGINT,
+    p_date DATE
+)
+RETURNS NUMERIC AS $$
+DECLARE
+    total_calories NUMERIC := 0;
+BEGIN
+    SELECT COALESCE(SUM(f.calories_per_100g * fl.quantity / 100), 0)
+    INTO total_calories
+    FROM FoodLogs fl
+    JOIN Food f ON fl.FoodId = f.id
+    WHERE fl.UserId = p_user_id
+    AND DATE(fl.dateTime) >= p_date AND DATE(fl.dateTime) < (p_date + 1);
+
+    RETURN total_calories;
+END;
+$$ LANGUAGE plpgsql;
+
+-- 2. PROCEDURE: Log a food entry for a user
+CREATE OR REPLACE PROCEDURE log_food_entry(
+    p_user_id   BIGINT,
+    p_food_id   BIGINT,
+    p_quantity  NUMERIC,
+    p_unit      VARCHAR(10),
+    p_meal_type VARCHAR(20)
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+    IF p_meal_type NOT IN ('breakfast', 'lunch', 'dinner', 'snack') THEN
+        RAISE EXCEPTION 'Invalid meal type: %. Must be breakfast, lunch, dinner or snack.', p_meal_type;
+    END IF;
+
+    IF p_quantity <= 0 THEN
+        RAISE EXCEPTION 'Quantity must be greater than 0.';
+    END IF;
+
+    IF NOT EXISTS (SELECT 1 FROM "User" WHERE id = p_user_id) THEN
+        RAISE EXCEPTION 'User with id % does not exist.', p_user_id;
+    END IF;
+
+    IF NOT EXISTS (SELECT 1 FROM Food WHERE id = p_food_id) THEN
+        RAISE EXCEPTION 'Food with id % does not exist.', p_food_id;
+    END IF;
+
+    INSERT INTO FoodLogs (quantity, unit, meal_type, dateTime, UserId, FoodId)
+    VALUES (p_quantity, p_unit, p_meal_type, NOW(), p_user_id, p_food_id);
+
+    RAISE NOTICE 'Food log entry created for user % at %', p_user_id, NOW();
+END;
+$$;
+
+-- 3. TRIGGER: Automatically update calories_per_100g when nutrients are inserted
+CREATE OR REPLACE FUNCTION update_food_calories()
+RETURNS TRIGGER AS $$
+DECLARE
+    kcal_nutrient_id BIGINT;
+BEGIN
+    SELECT id INTO kcal_nutrient_id
+    FROM Nutrient
+    WHERE LOWER(nutrient) LIKE '%energy-kcal_100g%'
+    LIMIT 1;
+
+    IF NEW.NutrientId = kcal_nutrient_id THEN
+        UPDATE Food
+        SET calories_per_100g = NEW.amount::INT
+        WHERE id = NEW.FoodId;
+
+        RAISE NOTICE 'Updated calories_per_100g for food % to %', NEW.FoodId, NEW.amount;
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE TRIGGER trg_update_food_calories
+AFTER INSERT OR UPDATE ON Food_nutrients
+FOR EACH ROW
+EXECUTE FUNCTION update_food_calories();
+
+-- 4. FUNCTION: Calculate total calories burned for a user on a specific date
+CREATE OR REPLACE FUNCTION get_user_daily_calories_burned(
+    p_user_id BIGINT,
+    p_date    DATE
+)
+RETURNS NUMERIC AS $$
+DECLARE
+    total_burned NUMERIC := 0;
+BEGIN
+    SELECT COALESCE(SUM(al.Calories_burned), 0)
+    INTO total_burned
+    FROM ActivityLogs al
+    WHERE al.UserId = p_user_id
+    AND DATE(al.dateTime) = p_date;
+
+    RETURN total_burned;
+END;
+$$ LANGUAGE plpgsql;
+
+-- 5. PROCEDURE: Log an activity entry for a user
+CREATE OR REPLACE PROCEDURE log_activity_entry(
+    p_user_id     BIGINT,
+    p_exercise_id BIGINT,
+    p_duration    INT
+)
+LANGUAGE plpgsql AS $$
+DECLARE
+    v_calories_per_minute NUMERIC;
+    v_calories_burned     NUMERIC;
+BEGIN
+    IF p_duration <= 0 THEN
+        RAISE EXCEPTION 'Duration must be greater than 0.';
+    END IF;
+
+    IF NOT EXISTS (SELECT 1 FROM "User" WHERE id = p_user_id) THEN
+        RAISE EXCEPTION 'User with id % does not exist.', p_user_id;
+    END IF;
+
+    SELECT calories_per_minute INTO v_calories_per_minute
+    FROM Exercise WHERE id = p_exercise_id;
+
+    IF NOT FOUND THEN
+        RAISE EXCEPTION 'Exercise with id % does not exist.', p_exercise_id;
+    END IF;
+
+    v_calories_burned := v_calories_per_minute * p_duration;
+
+    INSERT INTO ActivityLogs (dateTime, UserId, Duration_minutes, Calories_burned, ExerciseId)
+    VALUES (NOW(), p_user_id, p_duration, v_calories_burned, p_exercise_id);
+
+    RAISE NOTICE 'Activity logged for user %. Duration: % min, Calories burned: %',
+        p_user_id, p_duration, v_calories_burned;
+END;
+$$;
+
+-- 6. TRIGGER: Automatically update streak when activity log is inserted
+CREATE OR REPLACE FUNCTION update_activity_streak()
+RETURNS TRIGGER AS $$
+DECLARE
+    v_streak_type_id BIGINT;
+    v_last_updated   DATE;
+    v_current_count  INT;
+BEGIN
+    SELECT id INTO v_streak_type_id
+    FROM Streak_type
+    WHERE LOWER(metric_type) = 'workouts'
+    LIMIT 1;
+
+    IF v_streak_type_id IS NULL THEN
+        RETURN NEW;
+    END IF;
+
+    SELECT last_updated, current_count
+    INTO v_last_updated, v_current_count
+    FROM Streak
+    WHERE User_id = NEW.UserId
+    AND Streak_type_id = v_streak_type_id;
+
+    IF NOT FOUND THEN
+        INSERT INTO Streak (User_id, Streak_type_id, current_count, longest_count, last_updated)
+        VALUES (NEW.UserId, v_streak_type_id, 1, 1, CURRENT_DATE);
+
+    ELSIF v_last_updated = CURRENT_DATE THEN
+        RETURN NEW;
+
+    ELSIF v_last_updated = CURRENT_DATE - INTERVAL '1 day' THEN
+        UPDATE Streak
+        SET current_count = current_count + 1,
+            longest_count = GREATEST(longest_count, current_count + 1),
+            last_updated  = CURRENT_DATE
+        WHERE User_id = NEW.UserId
+        AND Streak_type_id = v_streak_type_id;
+
+    ELSE
+        UPDATE Streak
+        SET current_count = 1,
+            last_updated  = CURRENT_DATE
+        WHERE User_id = NEW.UserId
+        AND Streak_type_id = v_streak_type_id;
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE TRIGGER trg_update_activity_streak
+AFTER INSERT ON ActivityLogs
+FOR EACH ROW
+EXECUTE FUNCTION update_activity_streak();
+
+-- 7. FUNCTION: Check if user has met their daily calorie target
+CREATE OR REPLACE FUNCTION check_calorie_target(p_user_id BIGINT, p_date DATE)
+RETURNS TABLE (
+    calorie_target INT,
+    actual_calories NUMERIC,
+    difference NUMERIC,
+    target_met BOOLEAN
+              ) AS $$
+BEGIN
+    RETURN QUERY
+    WITH daily_stats AS (
+        SELECT get_user_daily_calories(p_user_id, p_date) as actual
+    )
+    SELECT
+        d.calorie_target,
+        s.actual,
+        s.actual - d.calorie_target,
+        s.actual >= d.calorie_target
+    FROM User_Diets ud
+    CROSS JOIN daily_stats s
+    JOIN Diet d ON ud.Diet_id = d.id
+    WHERE ud.user_id = p_user_id
+    AND p_date BETWEEN ud.start_date AND ud.end_date
+    LIMIT 1;
+END;
+$$ LANGUAGE plpgsql;
+
+-- 8. PROCEDURE: Assign a diet to a user
+CREATE OR REPLACE PROCEDURE assign_diet_to_user(
+    p_user_id    BIGINT,
+    p_diet_id    BIGINT,
+    p_start_date DATE,
+    p_end_date   DATE
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+    IF p_end_date < p_start_date THEN
+        RAISE EXCEPTION 'End date cannot be before start date.';
+    END IF;
+
+    IF NOT EXISTS (SELECT 1 FROM "User" WHERE id = p_user_id) THEN
+        RAISE EXCEPTION 'User with id % does not exist.', p_user_id;
+    END IF;
+
+    IF NOT EXISTS (SELECT 1 FROM Diet WHERE id = p_diet_id) THEN
+        RAISE EXCEPTION 'Diet with id % does not exist.', p_diet_id;
+    END IF;
+
+    IF EXISTS (
+        SELECT 1 FROM User_Diets
+        WHERE user_id = p_user_id
+        AND (p_start_date, p_end_date) OVERLAPS (start_date, end_date)
+    ) THEN
+        RAISE EXCEPTION 'User already has an active diet in this period.';
+    END IF;
+
+    INSERT INTO User_Diets (user_id, Diet_id, start_date, end_date)
+    VALUES (p_user_id, p_diet_id, p_start_date, p_end_date);
+
+    RAISE NOTICE 'Diet % assigned to user % from % to %',
+        p_diet_id, p_user_id, p_start_date, p_end_date;
+END;
+$$;
+
+-- 9. PROCEDURE: Add body measurements
+CREATE OR REPLACE PROCEDURE add_body_measurement(
+    p_user_id  BIGINT,
+    p_weight   NUMERIC,
+    p_waist    NUMERIC,
+    p_body_fat NUMERIC
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+    IF NOT EXISTS (SELECT 1 FROM "User" WHERE id = p_user_id) THEN
+        RAISE EXCEPTION 'User with id % does not exist.', p_user_id;
+    END IF;
+
+    IF EXISTS (
+        SELECT 1 FROM BodyMeasurements
+        WHERE User_id = p_user_id
+        AND DATE(dateTime) = CURRENT_DATE
+    ) THEN
+        UPDATE BodyMeasurements
+        SET weight   = p_weight,
+            waist    = p_waist,
+            body_fat = p_body_fat,
+            dateTime = NOW()
+        WHERE User_id = p_user_id
+        AND DATE(dateTime) = CURRENT_DATE;
+
+        RAISE NOTICE 'Updated existing measurement for user % today.', p_user_id;
+    ELSE
+        INSERT INTO BodyMeasurements (User_id, weight, waist, body_fat, dateTime)
+        VALUES (p_user_id, p_weight, p_waist, p_body_fat, NOW());
+
+        RAISE NOTICE 'New measurement added for user %.', p_user_id;
+    END IF;
+END;
+$$;
+
+-- 10. FUNCTION: Get daily nutrition summary
+CREATE OR REPLACE FUNCTION get_user_nutrition_summary(
+    p_user_id BIGINT,
+    p_date    DATE
+)
+RETURNS TABLE (
+    total_protein   NUMERIC,
+    total_carbs     NUMERIC,
+    total_fat       NUMERIC,
+    total_fiber     NUMERIC
+) AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        COALESCE(SUM(CASE WHEN LOWER(n.nutrient) LIKE '%protein%'
+            THEN fn.amount * fl.quantity / 100 ELSE 0 END), 0) AS total_protein,
+        COALESCE(SUM(CASE WHEN LOWER(n.nutrient) LIKE '%carbohydrat%'
+            THEN fn.amount * fl.quantity / 100 ELSE 0 END), 0) AS total_carbs,
+        COALESCE(SUM(CASE WHEN LOWER(n.nutrient) LIKE '%fat%'
+            AND LOWER(n.nutrient) NOT LIKE '%saturated%'
+            THEN fn.amount * fl.quantity / 100 ELSE 0 END), 0) AS total_fat,
+        COALESCE(SUM(CASE WHEN LOWER(n.nutrient) LIKE '%fiber%'
+            OR LOWER(n.nutrient) LIKE '%fibre%'
+            THEN fn.amount * fl.quantity / 100 ELSE 0 END), 0) AS total_fiber
+    FROM FoodLogs fl
+    JOIN Food_nutrients fn ON fl.FoodId = fn.FoodId
+    JOIN Nutrient n ON fn.NutrientId = n.id
+    WHERE fl.UserId = p_user_id
+    AND DATE(fl.dateTime) >= p_date AND DATE(fl.dateTime) < (p_date + 1);
+END;
+$$ LANGUAGE plpgsql;