"""冰箱食材管理 — 数据库层"""
import sqlite_utils
import os
import json
from datetime import date

DB_PATH = os.path.join(os.path.dirname(__file__), "fridge.db")

def get_db():
    return sqlite_utils.Database(DB_PATH)

def init_db():
    db = get_db()

    # 家庭组
    if "families" not in db.table_names():
        db["families"].create({
            "id": str,
            "name": str,
            "invite_code": str,
            "created_at": str,
        }, pk="id")

    # 用户
    if "users" not in db.table_names():
        db["users"].create({
            "openid": str,
            "family_id": str,
            "nickname": str,
            "avatar_url": str,
            "created_at": str,
        }, pk="openid")
        db["users"].add_foreign_key("family_id", "families", "id")

    # 食材
    if "foods" not in db.table_names():
        db["foods"].create({
            "id": int,
            "family_id": str,
            "name": str,
            "category": str,
            "quantity": float,
            "unit": str,
            "purchase_date": str,
            "expiry_date": str,
            "added_by": str,
            "barcode": str,
            "notes": str,
            "price": float,
            "storage": str,
            "status": str,
            "created_at": str,
        }, pk="id")
        db["foods"].add_foreign_key("family_id", "families", "id")

    # 菜谱
    if "recipes" not in db.table_names():
        db["recipes"].create({
            "id": int,
            "name": str,
            "description": str,
            "recipe_emoji": str,    # emoji icon
            "ingredients": str,     # JSON array of required ingredients
            "ingredient_amounts": str,  # JSON array of amounts per ingredient
            "pantry_items": str,     # JSON array of always-available staples
            "steps": str,            # JSON array of cooking steps
            "step_times": str,       # JSON array of time per step
            "cooking_time": int,
            "difficulty": int,
            "tags": str,             # JSON array
        }, pk="id")

    # 食材相冲
    if "food_conflicts" not in db.table_names():
        db["food_conflicts"].create({
            "id": int,
            "food_a": str,
            "food_b": str,
            "severity": str,
            "description": str,
        }, pk="id")
        db["food_conflicts"].create_index(["food_a", "food_b"], unique=True)

    # 采购清单
    if "shopping_list" not in db.table_names():
        db["shopping_list"].create({
            "id": int,
            "family_id": str,
            "name": str,
            "quantity": float,
            "unit": str,
            "added_by": str,
            "purchased": bool,
            "created_at": str,
        }, pk="id")
        db["shopping_list"].add_foreign_key("family_id", "families", "id")

    # 小贴士
    if "tips" not in db.table_names():
        db["tips"].create({
            "id": int,
            "content": str,
            "category": str,
        }, pk="id")

    # 点菜/许愿
    if "orders" not in db.table_names():
        db["orders"].create({
            "id": int,
            "family_id": str,
            "recipe_name": str,
            "requested_by": str,
            "note": str,
            "fulfilled": bool,
            "created_at": str,
        }, pk="id")
        db["orders"].add_foreign_key("family_id", "families", "id")

    return db