== !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.

4. get_user_daily_calories_burned (Function)

* This function aggregates the sum of all tracked physical output metrics for a targeted individual across a single calendar day. It extracts raw data directly from activity history tables to return a single numeric metric. Used directly alongside the food tracking functions to calculate the net daily energy balance for a user profile. It encapsulates simple temporal filters behind a clean, readable retrieval interface.

5. log_activity_entry (Procedure)

* This routine validates a workout submission and dynamically computes total energy expenditures by multiplying duration against a specific exercise's calories burned per minute. It handles foreign key checks procedurally before inserting the finalized metrics into activity history. Used to centralize business calculations during a write transaction. It removes the mathematical burden from the frontend application layer, ensuring consistent caloric burn logic regardless of where the entry is submitted.

6. trg_update_activity_streak (Trigger & Function)

* This trigger listens for incoming workout records and processes conditional state logic to maintain user behavioral metrics. It handles three distinct states: initializing a new track, skipping identical-day duplicate increments, or breaking and resetting stagnant counts. Used to power the application's gamification features completely on the data layer. It provides instant, reactive state changes to profile records the exact millisecond a user logs an active workout.

7. check_calorie_target (Function)

* This function evaluates current daily consumption metrics against the parameters of a user's actively assigned dietary phase using an integrated common table expression (CTE). It outputs a virtual table showing targets, current metrics, remaining discrepancies, and success states. It compares how many calories a user ate today against their diet goals for that specific date. It combines their goal, their actual intake, the mathematical difference, and a true/false success flag into one organized row that the app can instantly use to draw progress charts.

8. assign_diet_to_user (Procedure)

* This administrative routine manages user configuration changes while actively preventing chronological schedule conflicts. It uses PostgreSQL's native OVERLAPS operator to strictly block the registration of overlapping diet windows. Used to securely handle user diet changes. It establishes strict date-range validations directly at the transactional layer to prevent logical data corruption that simple interface validation might miss.

9. add_body_measurement (Procedure)

* This conditional routine records user physical tracking metrics like weight and body fat percentages. It detects if an entry already exists for the current calendar date and seamlessly shifts its operational behavior from an insert to an update. Implements standard Update/Insert data logic for tracking historical progress. It ensures a clean timeline of metrics by restricting users to a single, easily updated entry per day.

10. get_user_nutrition_summary (Function)

* This function processes a daily log timeline and dynamically separates text-heavy nutrient criteria using filtered aggregate patterns. It filters and computes independent tracking tallies for foundational macronutrients (protein, carbs, fats, and fiber) simultaneously. Serves as the primary data source for rendering nutritional charts and breakdown widgets.