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;

1. get_user_daily_calories (Function)

• This function calculates a user's total caloric intake for a specific date by joining logged food portions with their respective nutritional content. It handles edge cases by converting empty log results into a baseline value of zero using COALESCE. Its main purpose is to serve as a reusable calculator for the application's dashboard. It isolates the mathematical conversion formula (Calories * Quantity) / 100) entirely within the database layer.

2. log_food_entry (Procedure)

• This procedure manages the creation of a new meal record by validating user inputs against business rules before inserting data into the logs. It explicitly blocks invalid meal types, zero or negative quantities, and non-existent user or food identifiers. Used as a safe write-API for application data entry. It protects database integrity by throwing explicit procedural exceptions to reject malformed requests before they hit storage.

3. !trg_update_food_calories (Trigger & Function)

• This trigger intercepts inserts or updates on the food nutrient mapping table and automatically extracts energy data. If the nutrient matches the specific !kilocalorie identifier, it modifies the main food item's summary column. Used to enforce automated data !denormalization. It ensures that the frequently read calories_per_100g summary column on the main table remains perfectly synchronized with raw, deeply nested nutrient records without manual !backend updates.