recipes

import sqlite3

# Connect to DB with autocommit mode
conn = sqlite3.connect("meal_ingredients.db", isolation_level=None)

# Ensure tables exist
conn.execute("PRAGMA foreign_keys = ON")
conn.execute("CREATE TABLE IF NOT EXISTS meals (name TEXT) STRICT")
conn.execute(
    """
CREATE TABLE IF NOT EXISTS ingredients (
    name TEXT,
    meal_id INTEGER,
    FOREIGN KEY(meal_id) REFERENCES meals(rowid)
) STRICT
"""
)
res = conn.execute("SELECT * FROM sqlite_schema WHERE name = 'meals'").fetchone()
print(res)

# Main loop
while True:
    try:
        user_input = input("> ").strip()

        # Exit
        if user_input.lower() == "quit":
            break

        # Add new meal
        elif ":" in user_input:
            meal_name, ingredients_list = map(str.strip, user_input.split(":", 1))
            ingredients = [i.strip() for i in ingredients_list.split(",") if i.strip()]

            if not meal_name or not ingredients:
                print("Invalid format. Use: meal:ingredient1,ingredient2,...")
                continue

            # Insert meal
            conn.execute("INSERT INTO meals (name) VALUES (?)", (meal_name,))
            meal_id = conn.execute(
                "SELECT rowid FROM meals WHERE name = ?", (meal_name,)
            ).fetchone()[0]

            # Insert ingredients
            for ingredient in ingredients:
                conn.execute(
                    "INSERT INTO ingredients (name, meal_id) VALUES (?, ?)",
                    (ingredient, meal_id),
                )

            print(f"Meal added: {meal_name}")

        else:
            # Try to treat input as a meal name
            ingredients = conn.execute(
                """
                SELECT ingredients.name
                FROM meals
                INNER JOIN ingredients ON meals.rowid = ingredients.meal_id
                WHERE meals.name = ?
                """,
                (user_input,),
            ).fetchall()

            if ingredients:
                print(f"Ingredients of {user_input}:")
                for ing in ingredients:
                    print(f"  {ing[0]}")
                continue

            # Try to treat input as an ingredient
            meals = conn.execute(
                """
                SELECT meals.name
                FROM meals
                INNER JOIN ingredients ON meals.rowid = ingredients.meal_id
                WHERE ingredients.name = ?
                """,
                (user_input,),
            ).fetchall()

            if meals:
                print(f"Meals that use {user_input}:")
                for meal in meals:
                    print(f"  {meal[0]}")
            else:
                print("Not found.")

    except Exception as e:
        print(f"Error: {e}")