516 lines
25 KiB
Python
Executable File
516 lines
25 KiB
Python
Executable File
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]
|