db.sql

CREATE DATABASE IF NOT EXISTS recipe_keeper CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE recipe_keeper;


-- Categories
CREATE TABLE IF NOT EXISTS categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB;


INSERT IGNORE INTO categories (id, name) VALUES
(1, 'Breakfast'),
(2, 'Lunch'),
(3, 'Dinner'),
(4, 'Appetizer'),
(5, 'Snack'),
(6, 'Dessert'),
(7, 'Drink'),
(8, 'Other');


-- Recipes
CREATE TABLE IF NOT EXISTS recipes (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
category_id INT NULL,
image_path VARCHAR(512) NULL,
notes TEXT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT fk_recipe_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;


-- Ingredients
CREATE TABLE IF NOT EXISTS ingredients (
id INT AUTO_INCREMENT PRIMARY KEY,
recipe_id INT NOT NULL,
name VARCHAR(255) NOT NULL,
amount VARCHAR(32) NULL, -- allow fractions like 1/8
unit VARCHAR(32) NULL,
position INT NOT NULL DEFAULT 0,
CONSTRAINT fk_ing_recipe FOREIGN KEY (recipe_id) REFERENCES recipes(id) ON DELETE CASCADE
) ENGINE=InnoDB;


-- Steps
CREATE TABLE IF NOT EXISTS steps (
id INT AUTO_INCREMENT PRIMARY KEY,
recipe_id INT NOT NULL,
description TEXT NOT NULL,
position INT NOT NULL DEFAULT 0,
CONSTRAINT fk_step_recipe FOREIGN KEY (recipe_id) REFERENCES recipes(id) ON DELETE CASCADE
) ENGINE=InnoDB;