Create

START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

-- Database: `fitbase`

CREATE DATABASE IF NOT EXISTS fitbase;
USE fitbase;

-- Tablas
CREATE TABLE ACTIVITY_LEVELS (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    value FLOAT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (value >= 0)
) COMMENT='Stores the Activity levels for the user';

CREATE TABLE USERS (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name_1 VARCHAR(50) NOT NULL,
    last_name_2 VARCHAR(50) NULL,
    height FLOAT NOT NULL,
    weight FLOAT NULL,
    birthday DATE NOT NULL,
    gender CHAR(1) NOT NULL,
    role VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password BLOB NOT NULL,
    nickname VARCHAR(255) NOT NULL UNIQUE,
    activity_level_id INT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (role = 'USER' OR role = 'NUTRITIONIST'),
    CHECK (gender IN ('H', 'M')),
    FOREIGN KEY (activity_level_id) REFERENCES ACTIVITY_LEVELS(id)
) COMMENT = 'Stores the users data';

CREATE TABLE FOOD (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    category VARCHAR(255) NOT NULL,
    calories INT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) COMMENT='Stores the food data.';

CREATE TABLE OBJECTIVES (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) COMMENT='Stores the objectives data.';

CREATE TABLE DIETS (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    description VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL,
    duration INT NULL,
    gender CHAR(1) NOT NULL,
    min_kcal INT NOT NULL,
    max_kcal INT NOT NULL,
    objective_id INT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (gender IN ('H', 'M')),
    CHECK (min_kcal >= 0 AND min_kcal <= max_kcal),
    FOREIGN KEY (objective_id) REFERENCES OBJECTIVES(id)
) COMMENT='Stores the objectives data.';

CREATE TABLE DISHES (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    cooking_time INT NOT NULL,
    instructions VARCHAR(1000) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) COMMENT='Stores the food data.';

-- Tablas auxiliares
CREATE TABLE USER_ALLERGIES (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    food_id INT NOT NULL,
    is_allergic BOOLEAN NOT NULL,
    is_favorite BOOLEAN NOT NULL,
    FOREIGN KEY (user_id) REFERENCES USERS(id),
    FOREIGN KEY (food_id) REFERENCES FOOD(id)
) COMMENT='Stores the user_allergies data.';

CREATE TABLE INGREDIENTS (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    food_id INT NOT NULL,
    dish_id INT NOT NULL,
    objective_id INT NOT NULL,
    grams INT NOT NULL,
    total_calories INT NOT NULL,
    FOREIGN KEY (food_id) REFERENCES FOOD(id),
    FOREIGN KEY (dish_id) REFERENCES DISHES(id),
    FOREIGN KEY (objective_id) REFERENCES OBJECTIVES(id)
) COMMENT='Stores the ingredients data.';

CREATE TABLE DISH_DETAILS (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    dish_id INT NOT NULL,
    diet_id INT NOT NULL,
    type VARCHAR(50) NOT NULL,
    FOREIGN KEY (dish_id) REFERENCES DISHES(id),
    FOREIGN KEY (diet_id) REFERENCES DIETS(id)
    CHECK (type IN ('DESAYUNO', 'COMIDA', 'CENA'))
) COMMENT='Stores the dish details data.';

CREATE TABLE SUMMARY (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    dish_details_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES USERS(id),
    FOREIGN KEY (dish_details_id) REFERENCES DISH_DETAILS(id)
) COMMENT='Stores the summary data.';

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;