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;