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()