== !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 ($(\text{Calories} \times \text{Quantity}) / 100$) entirely within the database layer.