SQL.sql

CREATE TABLE `users` (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(255),
    phone VARCHAR(20),
    birth_day DATE,
    role VARCHAR(50)
);

-- Media Table
CREATE TABLE medias (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    type VARCHAR(50),
    link VARCHAR(255),
    alt_text VARCHAR(255),
    description TEXT
);-- RecipeCategory Table
CREATE TABLE recipe_categories (
    id INT PRIMARY KEY,
    category_type VARCHAR(50),
    category_name VARCHAR(255)
);
-- Recipe Table
CREATE TABLE recipes (
    id INT PRIMARY KEY,
    `source` VARCHAR(255),
    name VARCHAR(255),
    language VARCHAR(50),
    description TEXT,
    totalTime VARCHAR(50),
    cookTime VARCHAR(50),
    prepTime VARCHAR(50),
    yield VARCHAR(50),
    notes TEXT,
    mainImg VARCHAR(255),
    recipe_category INT,
    FOREIGN KEY (recipe_category) REFERENCES recipe_categories(id)
);
-- Cookbook Table
CREATE TABLE cookbooks (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    date_created DATE,
    user_id INT,
    image_id INT,
    description TEXT,
    FOREIGN KEY (user_id) REFERENCES `users`(id),
    FOREIGN KEY (image_id) REFERENCES medias(id)
);

-- CookbookRecipe Table
CREATE TABLE cookbook_recipies (
    id INT PRIMARY KEY,
    cookbook_id INT,
    recipe_id INT,
    FOREIGN KEY (cookbook_id) REFERENCES cookbooks(id),
    FOREIGN KEY (recipe_id) REFERENCES recipies(id)
);

-- GroceryList Table
CREATE TABLE grocery_lists (
    id INT PRIMARY KEY,
    user_id INT,
    date_created DATE,
    status VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES `users`(id)
);

-- GroceryListIngredients Table
CREATE TABLE grocery_list_ingredients (
    id INT PRIMARY KEY,
    groceryList_id INT,
    addedBy INT,
    ingredient_id INT,
    recipe_id INT,
    quantity INT,
    FOREIGN KEY (groceryList_id) REFERENCES grocery_lists(id),
    FOREIGN KEY (ingredient_id) REFERENCES ingredients(id),
    FOREIGN KEY (recipe_id) REFERENCES recipies(id)
);

-- MealPlanner Table
CREATE TABLE meal_planners (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES `users`(id)
);

-- MealPlannerMeal Table
CREATE TABLE meal_planner_meals (
    id INT PRIMARY KEY,
    mealPlanner_id INT,
    meal_date DATE,
    recipe_id INT,
    status VARCHAR(255),
    FOREIGN KEY (mealPlanner_id) REFERENCES meal_planners(id),
    FOREIGN KEY (recipe_id) REFERENCES recipies(id)
);

-- Measure Table
CREATE TABLE measures (
    id INT PRIMARY KEY,
    unit VARCHAR(50),
    unit_type VARCHAR(50)
);



-- MyCabinet Table
CREATE TABLE my_cabinets (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES `users`(id)
);

-- MyCabinetIngredients Table
CREATE TABLE my_cabinet_ingredients (
    id INT PRIMARY KEY,
    myCabinet_id INT,
    ingredient_id INT,
    ingredient_qty INT,
    FOREIGN KEY (myCabinet_id) REFERENCES my_cabinets(id),
    FOREIGN KEY (ingredient_id) REFERENCES ingredients(id)
);





-- RecipeIngredient Table
CREATE TABLE recipe_ingredients (
    id INT PRIMARY KEY,
    recipe_id INT,
    ingredient_id INT,
    quantity INT,
    FOREIGN KEY (recipe_id) REFERENCES recipies(id),
    FOREIGN KEY (ingredient_id) REFERENCES ingredients(id)
);

-- RecipeStep Table
CREATE TABLE recipe_steps (
    id INT PRIMARY KEY,
    recipe_id INT,
    step_order INT,
    text TEXT,
    media_id INT,
    FOREIGN KEY (recipe_id) REFERENCES recipies(id),
    FOREIGN KEY (media_id) REFERENCES medias(id)
);

-- Translation Table
CREATE TABLE translations (
    id INT PRIMARY KEY,
    `table` VARCHAR(255),
    `language` VARCHAR(50),
    item_id INT,
    translation TEXT,
    FOREIGN KEY (item_id) REFERENCES recipies(id)
);

-- UserSettings Table
CREATE TABLE user_settings (
    id INT PRIMARY KEY,
    user_id INT,
    theme VARCHAR(50),
    language VARCHAR(50),
    units VARCHAR(50),
    diet VARCHAR(50),
    preference VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES `users`(id)
);