1 files changed, 140 insertions, 0 deletions
diff --git a/app/db.py b/app/db.py
new file mode 100644
index 0000000..14b2287
--- /dev/null
+++ b/app/db.py
@@ -0,0 +1,140 @@
+import sqlite3
+import os
+from werkzeug.security import generate_password_hash
+from seed_data import RECIPES
+
+DATABASE = os.environ.get('DATABASE_PATH', '/data/menu.db')
+
+SCHEMA = """
+CREATE TABLE IF NOT EXISTS recipes (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    name TEXT NOT NULL,
+    cuisine TEXT NOT NULL,
+    description TEXT,
+    servings INTEGER DEFAULT 2,
+    calories_per_serving REAL,
+    carbs_per_serving REAL,
+    protein_per_serving REAL,
+    fat_per_serving REAL,
+    prep_time INTEGER,
+    cook_time INTEGER,
+    status TEXT DEFAULT 'candidate' CHECK(status IN ('candidate','favorited','ignored')),
+    instructions TEXT,
+    added_by TEXT
+);
+
+CREATE TABLE IF NOT EXISTS ingredients (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    recipe_id INTEGER NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
+    name TEXT NOT NULL,
+    quantity REAL NOT NULL,
+    unit TEXT NOT NULL,
+    category TEXT NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS meal_plan (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    plan_date DATE NOT NULL,
+    meal_type TEXT NOT NULL CHECK(meal_type IN ('breakfast','lunch','dinner')),
+    recipe_id INTEGER NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
+    servings INTEGER DEFAULT 2,
+    UNIQUE(plan_date, meal_type)
+);
+
+CREATE TABLE IF NOT EXISTS shopping_list (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    week_start DATE NOT NULL,
+    ingredient_name TEXT NOT NULL,
+    quantity REAL NOT NULL,
+    unit TEXT NOT NULL,
+    category TEXT NOT NULL,
+    recipe_sources TEXT,
+    checked INTEGER DEFAULT 0
+);
+
+CREATE TABLE IF NOT EXISTS users (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    username TEXT UNIQUE NOT NULL,
+    password_hash TEXT NOT NULL
+);
+"""
+
+CATEGORY_ORDER = [
+    "Meat & Poultry", "Seafood", "Dairy & Eggs",
+    "Produce", "Pantry", "Spices & Herbs",
+]
+
+SEED_USERS = [
+    ("ken",      "userliust"),
+    ("terri",    "Brit8334Bell"),
+    ("brittainy","sparkles"),
+    ("bella",    "dipperandmabelpines"),
+]
+
+INGREDIENT_UNITS = [
+    "whole", "oz", "lb", "cup", "cups", "tbsp", "tsp",
+    "cloves", "inch", "sprig", "sprigs", "leaves", "slices",
+    "bunch", "head", "can", "piece", "pieces", "strips",
+]
+
+INGREDIENT_CATEGORIES = [
+    "Meat & Poultry", "Seafood", "Dairy & Eggs",
+    "Produce", "Pantry", "Spices & Herbs",
+]
+
+
+def get_db():
+    conn = sqlite3.connect(DATABASE)
+    conn.row_factory = sqlite3.Row
+    conn.execute("PRAGMA foreign_keys = ON")
+    return conn
+
+
+def init_db():
+    os.makedirs(os.path.dirname(DATABASE), exist_ok=True)
+    conn = get_db()
+    conn.executescript(SCHEMA)
+    conn.commit()
+
+    if conn.execute("SELECT COUNT(*) FROM recipes").fetchone()[0] == 0:
+        _seed_recipes(conn)
+
+    _seed_users(conn)
+    conn.close()
+
+
+def _seed_recipes(conn):
+    for recipe in RECIPES:
+        cursor = conn.execute(
+            """INSERT INTO recipes
+               (name, cuisine, description, servings, calories_per_serving,
+                carbs_per_serving, protein_per_serving, fat_per_serving,
+                prep_time, cook_time, status, instructions)
+               VALUES (?,?,?,?,?,?,?,?,?,?,?,?)""",
+            (
+                recipe["name"], recipe["cuisine"], recipe["description"],
+                recipe["servings"], recipe["calories_per_serving"],
+                recipe["carbs_per_serving"], recipe.get("protein_per_serving"),
+                recipe.get("fat_per_serving"), recipe["prep_time"],
+                recipe["cook_time"], recipe.get("status", "candidate"),
+                recipe["instructions"],
+            ),
+        )
+        recipe_id = cursor.lastrowid
+        for ing in recipe["ingredients"]:
+            conn.execute(
+                "INSERT INTO ingredients (recipe_id, name, quantity, unit, category) VALUES (?,?,?,?,?)",
+                (recipe_id, ing["name"], ing["quantity"], ing["unit"], ing["category"]),
+            )
+    conn.commit()
+
+
+def _seed_users(conn):
+    for username, password in SEED_USERS:
+        existing = conn.execute("SELECT id FROM users WHERE username = ?", (username,)).fetchone()
+        if not existing:
+            conn.execute(
+                "INSERT INTO users (username, password_hash) VALUES (?, ?)",
+                (username, generate_password_hash(password)),
+            )
+    conn.commit()