import os import requests import psycopg from psycopg.rows import dict_row from fastapi import FastAPI, HTTPException from pydantic import BaseModel, Field from decimal import Decimal app = FastAPI(title="MealPlanner API", version="0.3.0") def connect(): return psycopg.connect( host=os.getenv("DB_HOST", "db"), port=int(os.getenv("DB_PORT", "5432")), dbname=os.getenv("DB_NAME", "mealplanner"), user=os.getenv("DB_USER", "mealplanner"), password=os.environ["DB_PASSWORD"], row_factory=dict_row, connect_timeout=5, ) def ensure_schema(): # Migration idempotente pour les bases existantes de la V1. with connect() as conn: conn.execute("ALTER TABLE stores ADD COLUMN IF NOT EXISTS address TEXT") conn.execute("ALTER TABLE stores ADD COLUMN IF NOT EXISTS postal_code TEXT") conn.execute("ALTER TABLE stores ADD COLUMN IF NOT EXISTS store_code TEXT") conn.execute("ALTER TABLE stores ADD COLUMN IF NOT EXISTS flyer_page_url TEXT") conn.execute(''' CREATE TABLE IF NOT EXISTS flyer_sources ( id SERIAL PRIMARY KEY, store_id INTEGER NOT NULL REFERENCES stores(id) ON DELETE CASCADE, source_url TEXT NOT NULL, active BOOLEAN NOT NULL DEFAULT TRUE, last_checked_at TIMESTAMPTZ, last_success_at TIMESTAMPTZ, last_error TEXT, UNIQUE(store_id, source_url) ) ''') conn.execute(''' CREATE TABLE IF NOT EXISTS flyers ( id SERIAL PRIMARY KEY, store_id INTEGER NOT NULL REFERENCES stores(id) ON DELETE CASCADE, source_id INTEGER REFERENCES flyer_sources(id) ON DELETE SET NULL, title TEXT NOT NULL, source_url TEXT NOT NULL, local_path TEXT NOT NULL, checksum TEXT NOT NULL UNIQUE, file_size BIGINT NOT NULL DEFAULT 0, page_count INTEGER NOT NULL DEFAULT 0, extracted_text TEXT NOT NULL DEFAULT '', extraction_status TEXT NOT NULL DEFAULT 'pending', valid_from DATE, valid_to DATE, downloaded_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ) ''') conn.execute(''' CREATE TABLE IF NOT EXISTS flyer_import_runs ( id SERIAL PRIMARY KEY, store_id INTEGER REFERENCES stores(id) ON DELETE SET NULL, source_url TEXT NOT NULL, started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), finished_at TIMESTAMPTZ, status TEXT NOT NULL DEFAULT 'running', message TEXT ) ''') conn.execute(''' INSERT INTO stores (name, address, postal_code, store_code, flyer_page_url, enabled) VALUES ('IGA', %s, 'J7P 2B1', 'IGA EXTRA Lamoureux', %s, TRUE), ('Metro', %s, 'J7R 4K3', 'Marché Arthur-Sauvé inc. (magasin 125)', %s, TRUE) ON CONFLICT (name) DO UPDATE SET address = EXCLUDED.address, postal_code = EXCLUDED.postal_code, store_code = EXCLUDED.store_code, flyer_page_url = EXCLUDED.flyer_page_url, enabled = TRUE ''', ( "379, boulevard Arthur-Sauvé, Saint-Eustache", os.getenv("IGA_FLYER_PAGE", "https://www.iga.ca/fr/circulaire"), "753, boulevard Arthur-Sauvé, Saint-Eustache", os.getenv("METRO_FLYER_PAGE", "https://www.metro.ca/trouver-une-epicerie/125"), )) conn.execute(''' INSERT INTO flyer_sources (store_id, source_url) SELECT id, flyer_page_url FROM stores WHERE name IN ('IGA', 'Metro') AND flyer_page_url IS NOT NULL ON CONFLICT (store_id, source_url) DO NOTHING ''') @app.on_event("startup") def startup(): ensure_schema() class Settings(BaseModel): household_size: int = Field(ge=1, le=30) weekly_budget: Decimal = Field(ge=0, le=100000) dinners_per_week: int = Field(ge=1, le=7) @app.get("/health") def health(): try: with connect() as conn: conn.execute("SELECT 1") return {"status": "ok", "database": "connected"} except Exception as exc: raise HTTPException(status_code=503, detail="Database unavailable") from exc @app.get("/api/settings") def get_settings(): with connect() as conn: row = conn.execute( "SELECT household_size, weekly_budget, dinners_per_week, currency " "FROM household_settings WHERE id = 1" ).fetchone() if row is None: raise HTTPException(status_code=404, detail="Settings not initialized") return dict(row) @app.put("/api/settings") def update_settings(data: Settings): with connect() as conn: row = conn.execute( "UPDATE household_settings SET household_size=%s, weekly_budget=%s, " "dinners_per_week=%s WHERE id=1 RETURNING household_size, weekly_budget, " "dinners_per_week, currency", (data.household_size, data.weekly_budget, data.dinners_per_week), ).fetchone() if row is None: raise HTTPException(status_code=404, detail="Settings not initialized") return dict(row) @app.get("/api/stores") def get_stores(): with connect() as conn: rows = conn.execute( "SELECT id, name, enabled, address, postal_code, store_code, flyer_page_url " "FROM stores ORDER BY name" ).fetchall() return [dict(row) for row in rows] @app.get("/api/flyers") def get_flyers(): with connect() as conn: rows=conn.execute(""" SELECT f.id,f.title,f.valid_from,f.valid_to,f.imported_at AS downloaded_at, 0 AS page_count,'flipp_imported' AS extraction_status, (SELECT COUNT(*) FROM flipp_offers o WHERE o.flyer_id=f.id) AS offer_count, f.thumbnail_url,f.external_flyer_id,f.merchant,f.postal_code, s.name AS store_name,s.address,'https://www.flipp.com/' AS source_url FROM flipp_flyers f JOIN stores s ON s.id=f.store_id WHERE f.valid_to IS NULL OR f.valid_to>=CURRENT_DATE ORDER BY f.valid_from DESC NULLS LAST,s.name,f.title LIMIT 30 """).fetchall() return [dict(x) for x in rows] @app.get("/api/offers") def get_offers(): with connect() as conn: rows=conn.execute(""" SELECT o.id,o.item_name,o.brand,o.price,o.price_text,o.image_url,o.valid_from,o.valid_to, f.merchant,f.title AS flyer_title,s.name AS store_name FROM flipp_offers o JOIN flipp_flyers f ON f.id=o.flyer_id JOIN stores s ON s.id=o.store_id WHERE o.valid_to IS NULL OR o.valid_to>=CURRENT_DATE ORDER BY s.name,o.price,o.item_name LIMIT 250 """).fetchall() return [dict(x) for x in rows] @app.get("/api/flyer-import/status") def flyer_import_status(): with connect() as conn: rows=conn.execute(""" SELECT s.name AS store_name,s.address,fs.source_url,fs.last_checked_at,fs.last_success_at, fs.last_error, (SELECT COUNT(*) FROM flipp_flyers f WHERE f.store_id=s.id AND (f.valid_to IS NULL OR f.valid_to>=CURRENT_DATE)) AS flyer_count, (SELECT COUNT(*) FROM flipp_offers o WHERE o.store_id=s.id AND (o.valid_to IS NULL OR o.valid_to>=CURRENT_DATE)) AS offer_count FROM flyer_sources fs JOIN stores s ON s.id=fs.store_id WHERE s.name IN ('IGA','Metro') AND fs.source_url='https://www.flipp.com/' ORDER BY s.name """).fetchall() runs=conn.execute(""" SELECT r.id,s.name AS store_name,r.started_at,r.finished_at,r.status,r.message FROM flyer_import_runs r LEFT JOIN stores s ON s.id=r.store_id ORDER BY r.started_at DESC LIMIT 6 """).fetchall() return {"sources":[dict(x) for x in rows],"recent_runs":[dict(x) for x in runs]} @app.post("/api/flyer-import/run") def trigger_flyer_import(): try: response = requests.post( os.getenv("IMPORTER_URL", "http://importer:8001") + "/run", timeout=8 ) response.raise_for_status() return response.json() except Exception as exc: raise HTTPException(status_code=502, detail="Importateur indisponible") from exc # Catalogue de recettes de base. Les coûts sont des estimations pour 4 portions, # non des prix garantis. Les promotions Flipp servent au classement des recettes. RECIPE_CATALOG = [ { "name": "Poulet citron et pommes de terre au four", "description": "Poulet rôti avec pommes de terre, carottes et citron.", "servings": 4, "estimated_cost": 17.00, "instructions": "Chauffer le four à 200 °C. Couper les pommes de terre et les carottes. Mélanger avec huile, citron et assaisonnements. Ajouter le poulet et cuire jusqu'à ce qu'il soit bien cuit (74 °C au centre).", "ingredients": [("poitrine de poulet", 700, "g"), ("pommes de terre", 700, "g"), ("carottes", 3, "unités"), ("citron", 1, "unité")] }, { "name": "Boulettes de bœuf et riz", "description": "Boulettes maison dans une sauce tomate, servies avec du riz.", "servings": 4, "estimated_cost": 16.00, "instructions": "Former des boulettes avec le bœuf, l'œuf et la chapelure. Les faire dorer, ajouter la sauce tomate et laisser mijoter jusqu'à cuisson complète. Servir sur le riz cuit.", "ingredients": [("bœuf haché", 500, "g"), ("riz", 250, "g"), ("sauce tomate", 1, "pot"), ("œufs", 1, "unité")] }, { "name": "Pâtes crémeuses au poulet et brocoli", "description": "Un souper rapide avec poulet, brocoli et sauce crémeuse.", "servings": 4, "estimated_cost": 15.00, "instructions": "Cuire les pâtes. Faire cuire le poulet en morceaux, ajouter le brocoli puis la crème. Mélanger avec les pâtes et le fromage.", "ingredients": [("poitrine de poulet", 500, "g"), ("pâtes", 350, "g"), ("brocoli", 1, "tête"), ("crème", 250, "ml"), ("fromage râpé", 100, "g")] }, { "name": "Tacos de porc et salade croquante", "description": "Tacos garnis de porc assaisonné, laitue et tomates.", "servings": 4, "estimated_cost": 16.00, "instructions": "Cuire le porc haché avec les épices. Réchauffer les tortillas et garnir de porc, laitue, tomates et fromage.", "ingredients": [("porc haché", 500, "g"), ("tortillas", 8, "unités"), ("laitue", 1, "petite"), ("tomates", 2, "unités"), ("fromage râpé", 100, "g")] }, { "name": "Saumon au miel et légumes", "description": "Filets de saumon glacés au miel avec légumes rôtis.", "servings": 4, "estimated_cost": 24.00, "instructions": "Déposer le saumon et les légumes sur une plaque. Badigeonner le saumon de miel, citron et moutarde. Cuire au four à 200 °C jusqu'à cuisson désirée et sécuritaire.", "ingredients": [("saumon", 600, "g"), ("brocoli", 1, "tête"), ("carottes", 3, "unités"), ("citron", 1, "unité")] }, { "name": "Chili rapide au bœuf", "description": "Chili nourrissant aux haricots, tomates et bœuf haché.", "servings": 4, "estimated_cost": 14.00, "instructions": "Faire revenir le bœuf et l'oignon. Ajouter les haricots, les tomates et les épices. Laisser mijoter 25 à 30 minutes.", "ingredients": [("bœuf haché", 400, "g"), ("haricots", 1, "boîte"), ("tomates en dés", 1, "boîte"), ("oignon", 1, "unité")] }, { "name": "Saucisses et légumes sur plaque", "description": "Un repas simple avec saucisses, pommes de terre et poivrons.", "servings": 4, "estimated_cost": 15.00, "instructions": "Couper les légumes, mélanger avec huile et épices. Ajouter les saucisses et cuire sur une plaque à 200 °C jusqu'à ce que tout soit bien cuit.", "ingredients": [("saucisses", 500, "g"), ("pommes de terre", 600, "g"), ("poivrons", 2, "unités"), ("oignon", 1, "unité")] }, { "name": "Poulet teriyaki et riz", "description": "Poulet sauté dans une sauce teriyaki, accompagné de riz.", "servings": 4, "estimated_cost": 16.00, "instructions": "Cuire le riz. Faire revenir le poulet en lanières, ajouter la sauce teriyaki et laisser épaissir. Servir avec des légumes et le riz.", "ingredients": [("poitrine de poulet", 600, "g"), ("riz", 250, "g"), ("brocoli", 1, "tête"), ("sauce teriyaki", 100, "ml")] }, { "name": "Frittata aux légumes et fromage", "description": "Omelette épaisse au four, idéale pour un souper économique.", "servings": 4, "estimated_cost": 11.00, "instructions": "Faire revenir les légumes. Battre les œufs avec un peu de lait et le fromage. Verser dans une poêle allant au four et cuire à 180 °C jusqu'à ce que le centre soit pris.", "ingredients": [("œufs", 8, "unités"), ("poivrons", 1, "unité"), ("épinards", 100, "g"), ("fromage râpé", 100, "g")] }, { "name": "Porc sauté aux légumes et nouilles", "description": "Lanières de porc sautées avec légumes et nouilles.", "servings": 4, "estimated_cost": 17.00, "instructions": "Cuire les nouilles. Saisir le porc en lanières, ajouter les légumes et la sauce soya. Servir avec les nouilles.", "ingredients": [("porc", 500, "g"), ("nouilles", 300, "g"), ("poivrons", 1, "unité"), ("brocoli", 1, "tête")] }, { "name": "Pizza maison jambon et légumes", "description": "Pizza facile avec sauce tomate, fromage, jambon et légumes.", "servings": 4, "estimated_cost": 13.00, "instructions": "Étendre la sauce tomate sur la pâte, ajouter le fromage, le jambon et les légumes. Cuire selon les indications de la pâte jusqu'à ce que la croûte soit dorée.", "ingredients": [("pâte à pizza", 1, "unité"), ("sauce tomate", 150, "ml"), ("fromage râpé", 200, "g"), ("jambon", 150, "g"), ("poivrons", 1, "unité")] }, { "name": "Bols burrito au poulet", "description": "Bols de riz avec poulet, haricots, maïs et tomates.", "servings": 4, "estimated_cost": 16.00, "instructions": "Cuire le riz et le poulet avec les épices. Répartir dans des bols avec les haricots, le maïs et les tomates.", "ingredients": [("poitrine de poulet", 500, "g"), ("riz", 250, "g"), ("haricots", 1, "boîte"), ("maïs", 1, "boîte"), ("tomates", 2, "unités")] } ] def seed_recipes(): with connect() as conn: # Migration des recettes V4 : la mention générique « poulet » devenait # une correspondance trop large pouvant confondre poitrine et cuisse. conn.execute(""" UPDATE recipe_ingredients ri SET ingredient_name='poitrine de poulet' FROM recipes r WHERE ri.recipe_id=r.id AND ri.ingredient_name='poulet' AND r.name IN ('Poulet citron et pommes de terre au four', 'Pâtes crémeuses au poulet et brocoli', 'Poulet teriyaki et riz', 'Bols burrito au poulet') """) for recipe in RECIPE_CATALOG: row = conn.execute("SELECT id FROM recipes WHERE name=%s ORDER BY id LIMIT 1", (recipe["name"],)).fetchone() if row is None: row = conn.execute( "INSERT INTO recipes (name,description,servings,instructions,estimated_cost) " "VALUES (%s,%s,%s,%s,%s) RETURNING id", (recipe["name"],recipe["description"],recipe["servings"],recipe["instructions"],recipe["estimated_cost"]) ).fetchone() recipe_id = row["id"] count = conn.execute("SELECT COUNT(*) AS n FROM recipe_ingredients WHERE recipe_id=%s", (recipe_id,)).fetchone()["n"] if count == 0: for name, quantity, unit in recipe["ingredients"]: conn.execute( "INSERT INTO recipe_ingredients (recipe_id,ingredient_name,quantity,unit) VALUES (%s,%s,%s,%s)", (recipe_id,name,quantity,unit) ) def normalize_text(value): import unicodedata, re value = unicodedata.normalize("NFKD", str(value or "").lower()) value = "".join(ch for ch in value if not unicodedata.combining(ch)) return re.sub(r"[^a-z0-9 ]+", " ", value).strip() INGREDIENT_TERMS = { "poitrine de poulet": ["poitrine de poulet", "poulet poitrine", "chicken breast", "breast"], "cuisse de poulet": ["cuisse de poulet", "haut de cuisse", "chicken thigh", "thigh", "drumstick"], "bœuf haché": ["boeuf hache", "ground beef"], "porc haché": ["porc hache", "ground pork"], "porc": ["porc", "pork"], "saumon": ["saumon", "salmon"], "saucisses": ["saucisse", "sausage"], "pommes de terre": ["pomme de terre", "patate", "potato"], "carottes": ["carotte", "carrot"], "brocoli": ["brocoli", "broccoli"], "tomates": ["tomate", "tomato"], "tomates en dés": ["tomate", "diced tomato"], "sauce tomate": ["sauce tomate", "tomato sauce"], "fromage râpé": ["fromage", "cheddar", "mozzarella"], "œufs": ["oeuf", "egg"], "poivrons": ["poivron", "pepper"], "laitue": ["laitue", "lettuce"], "épinards": ["epinard", "spinach"], "haricots": ["haricot", "bean"], "maïs": ["mais", "corn"], "jambon": ["jambon", "ham"], "crème": ["creme", "cream"], "riz": ["riz", "rice"], "pâtes": ["pate", "pasta"], "nouilles": ["nouille", "noodle"], "tortillas": ["tortilla"], "citron": ["citron", "lemon"], } def match_offer(ingredient, offer_name): text = normalize_text(offer_name) ingredient_norm = normalize_text(ingredient) # Les coupes de poulet ne sont jamais interchangeables. if "poulet" in ingredient_norm or "chicken" in ingredient_norm: wants_breast = any(x in ingredient_norm for x in ["poitrine", "breast"]) wants_thigh = any(x in ingredient_norm for x in ["cuisse", "thigh"]) has_breast = any(x in text for x in ["poitrine", "breast", "supreme"]) has_thigh = any(x in text for x in ["cuisse", "thigh", "drumstick", "pilons"]) if wants_breast and (has_thigh or not has_breast): return False if wants_thigh and (has_breast or not has_thigh): return False if not wants_breast and not wants_thigh: return False terms = INGREDIENT_TERMS.get(ingredient, [normalize_text(ingredient)]) return any(term in text for term in terms) def ingredient_cost(ingredient, offer, household_size, servings): """Retourne un coût calculable seulement si l'unité de vente correspond.""" qty = float(ingredient.get("quantity") or 0) * household_size / max(int(servings or 4), 1) unit = normalize_text(ingredient.get("unit") or "") basis = offer.get("price_unit") or "each" price = float(offer["price"]) if basis == "lb" and unit in ("g", "gramme", "grammes"): return price * qty / 453.59237 if basis == "kg" and unit in ("g", "gramme", "grammes"): return price * qty / 1000 if basis == "100g" and unit in ("g", "gramme", "grammes"): return price * qty / 100 if basis == "each" and unit in ("unite", "unites", "unité", "unités"): return price * qty return None @app.on_event("startup") def seed_recipe_catalog(): seed_recipes() @app.get("/api/meal-suggestions") def meal_suggestions(): with connect() as conn: recipes = conn.execute(""" SELECT r.id,r.name,r.description,r.servings,r.instructions,r.estimated_cost, COALESCE(json_agg(json_build_object('name',ri.ingredient_name,'quantity',ri.quantity,'unit',ri.unit)) FILTER (WHERE ri.id IS NOT NULL),'[]') AS ingredients FROM recipes r LEFT JOIN recipe_ingredients ri ON ri.recipe_id=r.id GROUP BY r.id ORDER BY r.name """).fetchall() offers = conn.execute(""" SELECT o.item_name,o.brand,o.price,o.price_text,o.price_unit,o.product_cut,o.valid_to,s.name AS store_name FROM flipp_offers o JOIN stores s ON s.id=o.store_id WHERE o.valid_to IS NULL OR o.valid_to>=CURRENT_DATE ORDER BY o.price LIMIT 500 """).fetchall() settings = conn.execute("SELECT household_size,dinners_per_week,weekly_budget FROM household_settings WHERE id=1").fetchone() output=[] household_size = settings["household_size"] if settings else 4 for recipe in recipes: matches=[] seen=set() calculable_cost=0.0 calculated_ingredients=0 for ingredient in recipe["ingredients"]: for offer in offers: key=(ingredient["name"],offer["item_name"],offer["store_name"]) if key not in seen and match_offer(ingredient["name"],offer["item_name"]): seen.add(key) cost=ingredient_cost(ingredient,offer,household_size,recipe["servings"]) matches.append({ "ingredient":ingredient["name"],"item_name":offer["item_name"], "price":float(offer["price"]),"price_text":offer["price_text"], "price_unit":offer["price_unit"],"calculated_cost":round(cost,2) if cost is not None else None, "store_name":offer["store_name"],"valid_to":str(offer["valid_to"]) if offer["valid_to"] else None }) if cost is not None: calculable_cost += cost calculated_ingredients += 1 break # Les autres ingrédients ne sont pas chiffrés arbitrairement à partir d'un prix/unité. # On indique le coût des ingrédients effectivement calculables. output.append({ **dict(recipe), "estimated_cost":round(calculable_cost,2) if calculated_ingredients else None, "cost_is_partial":calculated_ingredients < len(recipe["ingredients"]), "promotion_matches":matches,"promotion_count":len(matches) }) output.sort(key=lambda x:(-x["promotion_count"],x["estimated_cost"],x["name"])) return {"suggestions":output,"household_size":household_size, "dinners_per_week":settings["dinners_per_week"] if settings else 7, "weekly_budget":float(settings["weekly_budget"]) if settings else 150.0} class MealPlanInput(BaseModel): week_start: str recipe_ids: list[int] = Field(min_length=1, max_length=7) @app.post("/api/meal-plan") def save_meal_plan(data: MealPlanInput): from datetime import date, timedelta try: start=date.fromisoformat(data.week_start) except ValueError: raise HTTPException(status_code=400, detail="Date de début invalide") with connect() as conn: plan=conn.execute( "INSERT INTO meal_plans (week_start) VALUES (%s) ON CONFLICT (week_start) DO UPDATE SET week_start=EXCLUDED.week_start RETURNING id", (start,) ).fetchone() plan_id=plan["id"] conn.execute("DELETE FROM planned_meals WHERE meal_plan_id=%s",(plan_id,)) for index,recipe_id in enumerate(data.recipe_ids): exists=conn.execute("SELECT id FROM recipes WHERE id=%s",(recipe_id,)).fetchone() if not exists: raise HTTPException(status_code=400,detail="Recette introuvable") conn.execute( "INSERT INTO planned_meals (meal_plan_id,meal_date,recipe_id) VALUES (%s,%s,%s)", (plan_id,start+timedelta(days=index),recipe_id) ) return {"saved":True,"week_start":start.isoformat(),"meals_saved":len(data.recipe_ids)} @app.get("/api/meal-plan") def get_meal_plan(week_start: str): from datetime import date try: start=date.fromisoformat(week_start) except ValueError: raise HTTPException(status_code=400,detail="Date de début invalide") with connect() as conn: rows=conn.execute(""" SELECT pm.id,pm.meal_date,r.id AS recipe_id,r.name,r.description,r.estimated_cost FROM meal_plans mp JOIN planned_meals pm ON pm.meal_plan_id=mp.id LEFT JOIN recipes r ON r.id=pm.recipe_id WHERE mp.week_start=%s ORDER BY pm.meal_date """,(start,)).fetchall() return [dict(x) for x in rows] @app.get("/api/shopping-list") def get_shopping_list(week_start: str): from datetime import date try: start=date.fromisoformat(week_start) except ValueError: raise HTTPException(status_code=400,detail="Date de début invalide") with connect() as conn: rows=conn.execute(""" SELECT ri.ingredient_name,ri.unit,SUM(ri.quantity) AS quantity, COUNT(DISTINCT pm.id) AS recipe_count FROM meal_plans mp JOIN planned_meals pm ON pm.meal_plan_id=mp.id JOIN recipe_ingredients ri ON ri.recipe_id=pm.recipe_id WHERE mp.week_start=%s GROUP BY ri.ingredient_name,ri.unit ORDER BY ri.ingredient_name """,(start,)).fetchall() return [dict(x) for x in rows]