== DDL ==
-- Category
CREATE TABLE Category (
    category_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255),
    ParentCategory_id BIGINT,
    FOREIGN KEY (ParentCategory_id) REFERENCES Category(category_id) ON DELETE SET NULL ON UPDATE CASCADE
);

-- Nutrient
CREATE TABLE Nutrient (
    id BIGSERIAL PRIMARY KEY,
    nutrient VARCHAR(255),
    CONSTRAINT uq_nutrient_name UNIQUE (nutrient)
);

-- Food
CREATE TABLE Food (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255),
    calories_per_100g INT4,
    category_id BIGINT,
    CONSTRAINT chk_food_calories CHECK (calories_per_100g >= 0),
    FOREIGN KEY (category_id) REFERENCES Category(category_id) ON DELETE SET NULL ON UPDATE CASCADE
);

-- ServingSize
CREATE TABLE ServingSize (
    id BIGSERIAL PRIMARY KEY,
    label VARCHAR(50),
    amount NUMERIC(6, 2),
    unit VARCHAR(10),
    FoodId BIGINT,
    CONSTRAINT chk_servingsize_amount CHECK (amount > 0),
    FOREIGN KEY (FoodId) REFERENCES Food(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Food_nutrients
CREATE TABLE Food_nutrients (
    id BIGSERIAL PRIMARY KEY,
    FoodId BIGINT,
    NutrientId BIGINT,
    amount NUMERIC(8, 2),
    unit VARCHAR(10),
    CONSTRAINT chk_foodnutrients_amount CHECK (amount >= 0),
    FOREIGN KEY (FoodId) REFERENCES Food(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (NutrientId) REFERENCES Nutrient(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Activity
CREATE TABLE Activity (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255),
    CONSTRAINT uq_activity_name UNIQUE (name)
);

-- Muscle
CREATE TABLE Muscle (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(50),
    CONSTRAINT uq_muscle_name UNIQUE (name)
);

-- Exercise
CREATE TABLE Exercise (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100),
    calories_per_minute NUMERIC(5, 2),
    activity_id BIGINT,
    CONSTRAINT chk_exercise_calories CHECK (calories_per_minute >= 0),
    FOREIGN KEY (activity_id) REFERENCES Activity(id) ON DELETE SET NULL ON UPDATE CASCADE
);

-- Exercise_muscles
CREATE TABLE Exercise_muscles (
    id BIGSERIAL PRIMARY KEY,
    Exercise_id BIGINT,
    Muscle_id BIGINT,
    FOREIGN KEY (Exercise_id) REFERENCES Exercise(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (Muscle_id) REFERENCES Muscle(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Roles
CREATE TABLE Roles (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255),
    CONSTRAINT uq_roles_name UNIQUE (name)
);

-- User
CREATE TABLE "User" (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255),
    password VARCHAR(255),
    height INT4,
    weight INT4,
    gender VARCHAR(20),
    created_at TIMESTAMP,
    CONSTRAINT uq_user_email UNIQUE (email),
    CONSTRAINT chk_user_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT chk_user_gender CHECK (gender IN ('male', 'female'))
);

-- User_roles
CREATE TABLE User_roles (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    role_id BIGINT,
    FOREIGN KEY (user_id) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (role_id) REFERENCES Roles(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Achievements
CREATE TABLE Achievements (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255),
    requirement_type VARCHAR(50),
    target_value NUMERIC(8, 2),
    period_type VARCHAR(50),
    Activity_id BIGINT,
    CONSTRAINT chk_achievements_target CHECK (target_value > 0),
    CONSTRAINT chk_achievements_period CHECK (period_type IN ('daily', 'weekly', 'monthly', 'all_time')),
    CONSTRAINT chk_achievements_reqtype CHECK (requirement_type IN ('count', 'duration', 'calories', 'streak')),
    FOREIGN KEY (Activity_id) REFERENCES Activity(id) ON DELETE SET NULL ON UPDATE CASCADE
);

-- User_achievements
CREATE TABLE User_achievements (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    achievement_id BIGINT,
    dateEarned DATE,
    FOREIGN KEY (user_id) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (achievement_id) REFERENCES Achievements(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Reminder
CREATE TABLE Reminder (
    id BIGSERIAL PRIMARY KEY,
    dateTime TIMESTAMP,
    UserId BIGINT,
    type VARCHAR(20),
    enabled BOOL,
    CONSTRAINT chk_reminder_type CHECK (type IN ('meal', 'exercise', 'water')),
    FOREIGN KEY (UserId) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- BodyMeasurements
CREATE TABLE BodyMeasurements (
    id BIGSERIAL PRIMARY KEY,
    User_id BIGINT,
    weight NUMERIC(5, 2),
    waist NUMERIC(5, 2),
    body_fat NUMERIC(5, 2),
    dateTime TIMESTAMP,
    CONSTRAINT chk_bm_weight CHECK (weight > 0),
    CONSTRAINT chk_bm_bodyfat CHECK (body_fat BETWEEN 0 AND 100),
    CONSTRAINT chk_bm_waist CHECK (waist > 0),
    FOREIGN KEY (User_id) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- ActivityLogs
CREATE TABLE ActivityLogs (
    id BIGSERIAL PRIMARY KEY,
    dateTime TIMESTAMP,
    UserId BIGINT,
    Duration_minutes INT4,
    Calories_burned NUMERIC(8, 2),
    ExerciseId BIGINT,
    CONSTRAINT chk_activitylogs_duration CHECK (Duration_minutes > 0),
    CONSTRAINT chk_activitylogs_calories CHECK (Calories_burned >= 0),
    FOREIGN KEY (UserId) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (ExerciseId) REFERENCES Exercise(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- FoodLogs
CREATE TABLE FoodLogs (
    id BIGSERIAL PRIMARY KEY,
    quantity NUMERIC(10, 2),
    unit VARCHAR(10),
    meal_type VARCHAR(20),
    dateTime TIMESTAMP,
    UserId BIGINT,
    FoodId BIGINT,
    CONSTRAINT chk_foodlogs_quantity CHECK (quantity > 0),
    CONSTRAINT chk_foodlogs_mealtype CHECK (meal_type IN ('breakfast', 'lunch', 'dinner', 'snack')),
    FOREIGN KEY (UserId) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (FoodId) REFERENCES Food(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Streak_type
CREATE TABLE Streak_type (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255),
    description VARCHAR(255),
    metric_type VARCHAR(50),
    CONSTRAINT chk_streaktype_metric CHECK (metric_type IN ('steps', 'calories', 'workouts')),
    CONSTRAINT uq_streak_type_name UNIQUE (name)
);

-- Streak
CREATE TABLE Streak (
    id BIGSERIAL PRIMARY KEY,
    User_id BIGINT,
    Streak_type_id BIGINT,
    current_count INT4,
    longest_count INT4,
    last_updated DATE,
    CONSTRAINT chk_streak_current CHECK (current_count >= 0),
    CONSTRAINT chk_streak_longest CHECK (longest_count >= 0),
    CONSTRAINT chk_streak_counts CHECK (longest_count >= current_count),
    FOREIGN KEY (User_id) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (Streak_type_id) REFERENCES Streak_type(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Diet
CREATE TABLE Diet (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255),
    calorie_target INT4,
    protein_target INT4,
    carb_target INT4,
    fat_target INT4,
    CONSTRAINT chk_diet_calories CHECK (calorie_target > 0),
    CONSTRAINT chk_diet_protein CHECK (protein_target >= 0),
    CONSTRAINT chk_diet_carb CHECK (carb_target >= 0),
    CONSTRAINT chk_diet_fat CHECK (fat_target >= 0)
);

-- User_Diets
CREATE TABLE User_Diets (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    Diet_id BIGINT,
    start_date DATE,
    end_date DATE,
    CONSTRAINT chk_diet_dates CHECK (end_date >= start_date),
    FOREIGN KEY (user_id) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (Diet_id) REFERENCES Diet(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Diet_Exercise_Targets
CREATE TABLE Diet_Exercise_Targets (
    id BIGSERIAL PRIMARY KEY,
    frequency_per_week INT4,
    DietId BIGINT,
    ExerciseId BIGINT,
    CONSTRAINT chk_det_frequency CHECK (frequency_per_week BETWEEN 1 AND 7),
    FOREIGN KEY (DietId) REFERENCES Diet(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (ExerciseId) REFERENCES Exercise(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Diet_foods
CREATE TABLE Diet_foods (
    id BIGSERIAL PRIMARY KEY,
    unit VARCHAR(10),
    amount NUMERIC(10, 2),
    DietId BIGINT,
    FoodId BIGINT,
    CONSTRAINT chk_dietfoods_amount CHECK (amount > 0),
    FOREIGN KEY (DietId) REFERENCES Diet(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (FoodId) REFERENCES Food(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Goal
CREATE TABLE Goal (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255),
    diet_id BIGINT,
    FOREIGN KEY (diet_id) REFERENCES Diet(id) ON DELETE RESTRICT ON UPDATE CASCADE
);


-- User_goals
CREATE TABLE User_goals (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    GoalId BIGINT,
    start_date DATE,
    end_date DATE,
    CONSTRAINT chk_usergoals_dates CHECK (end_date >= start_date),
    FOREIGN KEY (user_id) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (GoalId) REFERENCES Goal(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

ALTER TABLE "User" ALTER COLUMN height SET NOT NULL;
ALTER TABLE "User" ALTER COLUMN weight SET NOT NULL;
ALTER TABLE "User" ALTER COLUMN gender SET NOT NULL;

ALTER TABLE Food ALTER COLUMN category_id SET NOT NULL;

ALTER TABLE FoodLogs ALTER COLUMN UserId SET NOT NULL;
ALTER TABLE FoodLogs ALTER COLUMN FoodId SET NOT NULL;

ALTER TABLE ActivityLogs ALTER COLUMN UserId SET NOT NULL;
ALTER TABLE ActivityLogs ALTER COLUMN ExerciseId SET NOT NULL;
ALTER TABLE ActivityLogs ALTER COLUMN Calories_burned SET NOT NULL;

ALTER TABLE BodyMeasurements ALTER COLUMN User_id SET NOT NULL;

ALTER TABLE Reminder ALTER COLUMN UserId SET NOT NULL;
ALTER TABLE Reminder ALTER COLUMN enabled SET NOT NULL;

ALTER TABLE Streak ALTER COLUMN User_id SET NOT NULL;
ALTER TABLE Streak ALTER COLUMN Streak_type_id SET NOT NULL;

ALTER TABLE User_achievements ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE User_achievements ALTER COLUMN achievement_id SET NOT NULL;
ALTER TABLE User_achievements ALTER COLUMN dateEarned SET NOT NULL;

ALTER TABLE User_roles ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE User_roles ALTER COLUMN role_id SET NOT NULL;

ALTER TABLE User_goals ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE User_goals ALTER COLUMN GoalId SET NOT NULL;

ALTER TABLE User_Diets ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE User_Diets ALTER COLUMN Diet_id SET NOT NULL;

ALTER TABLE Diet_Exercise_Targets ALTER COLUMN DietId SET NOT NULL;
ALTER TABLE Diet_Exercise_Targets ALTER COLUMN ExerciseId SET NOT NULL;
ALTER TABLE Diet_Exercise_Targets ALTER COLUMN frequency_per_week SET NOT NULL;

ALTER TABLE Diet_foods ALTER COLUMN DietId SET NOT NULL;
ALTER TABLE Diet_foods ALTER COLUMN FoodId SET NOT NULL;
ALTER TABLE Diet_foods ALTER COLUMN unit SET NOT NULL;

ALTER TABLE ServingSize ALTER COLUMN FoodId SET NOT NULL;
ALTER TABLE ServingSize ALTER COLUMN unit SET NOT NULL;

ALTER TABLE Food_nutrients ALTER COLUMN FoodId SET NOT NULL;
ALTER TABLE Food_nutrients ALTER COLUMN NutrientId SET NOT NULL;

ALTER TABLE Exercise_muscles ALTER COLUMN Exercise_id SET NOT NULL;
ALTER TABLE Exercise_muscles ALTER COLUMN Muscle_id SET NOT NULL;

ALTER TABLE Goal ALTER COLUMN diet_id SET NOT NULL;

ALTER TABLE Streak_type ALTER COLUMN description SET NOT NULL;

ALTER TABLE Exercise ALTER COLUMN activity_id SET NOT NULL;

== Views ==

-- 1. User Profile Overview
CREATE VIEW vw_user_profile AS
SELECT
    u.id,
    u.email,
    u.height,
    u.weight,
    u.gender,
    u.created_at,
    r.name AS role,
    d.name AS active_diet,
    ud.start_date AS diet_start,
    ud.end_date AS diet_end
FROM "User" u
LEFT JOIN User_roles ur ON u.id = ur.user_id
LEFT JOIN Roles r ON ur.role_id = r.id
LEFT JOIN User_Diets ud ON u.id = ud.user_id
LEFT JOIN Diet d ON ud.Diet_id = d.id;

-- 2. Daily Food Log Summary
CREATE VIEW vw_daily_food_summary AS
SELECT
    fl.UserId,
    DATE(fl.dateTime) AS log_date,
    fl.meal_type,
    SUM(CASE WHEN LOWER(n.nutrient) LIKE '%energy-kcal%'
        THEN fn.amount * fl.quantity / 100 ELSE 0 END) AS total_calories,
    SUM(CASE WHEN LOWER(n.nutrient) LIKE '%protein%'
        THEN fn.amount * fl.quantity / 100 ELSE 0 END) AS total_protein,
    SUM(CASE WHEN LOWER(n.nutrient) LIKE '%fat%'
        THEN fn.amount * fl.quantity / 100 ELSE 0 END) AS total_fat,
    SUM(CASE WHEN LOWER(n.nutrient) LIKE '%carbohydrate%'
        THEN fn.amount * fl.quantity / 100 ELSE 0 END) AS total_carbs,
    COUNT(fl.id) AS total_entries
FROM FoodLogs fl
JOIN Food_nutrients fn ON fl.FoodId = fn.FoodId
JOIN Nutrient n ON fn.NutrientId = n.id
GROUP BY fl.UserId, DATE(fl.dateTime), fl.meal_type;

-- 3. Exercise Calories Burned per User
CREATE VIEW vw_user_calories_burned AS
SELECT
    al.UserId,
    DATE(al.dateTime) AS log_date,
    SUM(al.Calories_burned) AS total_calories_burned,
    SUM(al.Duration_minutes) AS total_minutes,
    COUNT(al.id) AS total_sessions
FROM ActivityLogs al
GROUP BY al.UserId, DATE(al.dateTime);

-- 4. Food Nutritional Info
CREATE VIEW vw_food_nutrition AS
SELECT
    f.id AS food_id,
    f.name AS food_name,
    f.calories_per_100g,
    c.name AS category,
    n.nutrient AS nutrient_name,
    fn.amount,
    fn.unit
FROM Food f
LEFT JOIN Category c ON f.category_id = c.category_id
LEFT JOIN Food_nutrients fn ON f.id = fn.FoodId
LEFT JOIN Nutrient n ON fn.NutrientId = n.id;

-- 5. User Achievements Progress
CREATE VIEW vw_user_achievements AS
SELECT
    u.id AS user_id,
    u.email,
    a.name AS achievement_name,
    a.requirement_type,
    a.target_value,
    a.period_type,
    ua.dateEarned
FROM "User" u
JOIN User_achievements ua ON u.id = ua.user_id
JOIN Achievements a ON ua.achievement_id = a.id
ORDER BY ua.dateEarned DESC;

-- 6. User Streak Summary
CREATE VIEW vw_user_streaks AS
SELECT
    u.id AS user_id,
    u.email,
    st.name AS streak_type,
    st.metric_type,
    s.current_count,
    s.longest_count,
    s.last_updated
FROM "User" u
JOIN Streak s ON u.id = s.User_id
JOIN Streak_type st ON s.Streak_type_id = st.id;

-- 7. Body Measurements History
CREATE VIEW vw_body_measurements AS
SELECT
    u.id AS user_id,
    u.email,
    bm.weight,
    bm.waist,
    bm.body_fat,
    bm.dateTime AS measured_at
FROM "User" u
JOIN BodyMeasurements bm ON u.id = bm.User_id
ORDER BY bm.dateTime DESC;

-- 8. Diet Plan Details
CREATE VIEW vw_diet_plan AS
SELECT
    d.id AS diet_id,
    d.name AS diet_name,
    d.calorie_target,
    d.protein_target,
    d.carb_target,
    d.fat_target,
    f.name AS food_name,
    df.amount AS food_amount,
    df.unit AS food_unit,
    e.name AS exercise_name,
    det.frequency_per_week
FROM Diet d
LEFT JOIN Diet_foods df ON d.id = df.DietId
LEFT JOIN Food f ON df.FoodId = f.id
LEFT JOIN Diet_Exercise_Targets det ON d.id = det.DietId
LEFT JOIN Exercise e ON det.ExerciseId = e.id;

-- 9. Exercise Library
CREATE VIEW vw_exercise_library AS
SELECT
    e.id AS exercise_id,
    e.name AS exercise_name,
    e.calories_per_minute,
    a.name AS activity_type,
    STRING_AGG(m.name, ', ') AS muscles_targeted
FROM Exercise e
LEFT JOIN Activity a ON e.activity_id = a.id
LEFT JOIN Exercise_muscles em ON e.id = em.Exercise_id
LEFT JOIN Muscle m ON em.Muscle_id = m.id
GROUP BY e.id, e.name, e.calories_per_minute, a.name;