-- 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;