from __future__ import annotations

import hashlib
import ipaddress
import json
import re
import socket
from collections import defaultdict
from datetime import datetime, timezone
from decimal import Decimal, InvalidOperation
from pathlib import Path
from sqlite3 import Connection
from typing import Any
from urllib.parse import quote_plus, urlparse

from jarvis_finance.config.settings import find_repo_root, load_settings
from jarvis_finance.services.budget_common import new_id, row_to_dict

SAFE_RETAILERS = {"Coop", "Aldi Suisse", "Lidl", "Denner", "Otto's", "Migros"}
SAFE_MATCH_TYPES = {"exact_match", "close_match", "cheaper_private_label", "manual_match"}
SAFE_FLAGS = {"exact_match", "close_match", "cheaper_private_label", "manual_match", "manual_price"}
UNCERTAIN_FLAGS = {"different_pack_size", "needs_review", "bio_vs_non_bio", "brand_vs_private_label", "no_price", "no_unit_price"}
GROCERY_LLM_POLICY = {
    "allowed": ["Produktnamen normalisieren", "Einheiten erkennen", "Produktkandidaten vergleichen", "ähnliche Produkte clustern", "Barcode/Produkttext interpretieren", "Review-Vorschläge formulieren"],
    "forbidden": ["Preise erfinden", "als Preisquelle dienen", "sichere Ersparnis entscheiden", "Mapping ohne User Review bestätigen"],
}
PROVIDER_MATRIX = [
    {"provider": "Rappn.ch", "data_source": "Web-App / keine öffentliche API bestätigt", "price": "manuell prüfbar", "unit_price": "manuell prüfbar", "package_size": "manuell prüfbar", "official_api": False, "scraping_risk": "mittel", "stability": "nicht automatisiert", "recommendation": "nur Link-Out und manuelle Erfassung; später API-Anfrage"},
    {"provider": "Open Food Facts", "data_source": "öffentliche Produktdaten-API", "price": "nein", "unit_price": "nein", "package_size": "teilweise", "official_api": True, "scraping_risk": "niedrig", "stability": "mittel", "recommendation": "Produkt-/Barcode-Mapping, nicht Preisquelle"},
    {"provider": "Open Prices", "data_source": "öffentliche Open-Prices-API", "price": "teilweise", "unit_price": "teilweise/ableitbar", "package_size": "über Produktdaten teilweise", "official_api": True, "scraping_risk": "niedrig", "stability": "mittel", "recommendation": "Spike als unsicherer Kandidat mit Quelle/Datum; sichere Ersparnis nur bei vollständigen Daten"},
    {"provider": "FoodRepo", "data_source": "Schweizer Produktdaten, zu Open Food Facts migriert/redirect", "price": "nein", "unit_price": "nein", "package_size": "teilweise", "official_api": "legacy/unklar", "scraping_risk": "niedrig", "stability": "niedrig", "recommendation": "nicht als Preisquelle; ggf. Produktdaten über OFF"},
    {"provider": "Pepesto", "data_source": "kommerzielle Grocery API", "price": "möglich", "unit_price": "möglich", "package_size": "möglich", "official_api": True, "scraping_risk": "niedrig", "stability": "unklar/kostenpflichtig", "recommendation": "spätere Option, nicht im MVP"},
    {"provider": "Migros/Coop/Denner/Aldi/Lidl/Otto's direkt", "data_source": "Händler-Webseiten", "price": "instabil", "unit_price": "instabil", "package_size": "instabil", "official_api": False, "scraping_risk": "mittel/hoch", "stability": "niedrig", "recommendation": "keine stillen Scrapes; höchstens experimentell und nie sichere Ersparnis"},
]


def _now() -> str:
    return datetime.now(timezone.utc).isoformat()


def _money(value: Any) -> Decimal:
    try:
        text = str(value or "0").replace("CHF", "").replace("'", "").replace("’", "").strip().replace(",", ".")
        return Decimal(text or "0").quantize(Decimal("0.01"))
    except (InvalidOperation, ValueError):
        return Decimal("0.00")


def _fmt(value: Decimal | Any) -> str:
    return f"{_money(value):.2f}"


def _audit(conn: Connection, *, action: str, source: str, payload: dict[str, Any], mapping_id: str | None = None, product_item_id: str | None = None) -> None:
    conn.execute(
        "INSERT INTO grocery_mapping_audit_events(audit_id,mapping_id,product_item_id,action,source,payload_json,created_at) VALUES (?, ?, ?, ?, ?, ?, ?)",
        (new_id("gaudit"), mapping_id, product_item_id, action, source, json.dumps(payload, ensure_ascii=False, sort_keys=True), _now()),
    )


def get_grocery_provider_strategy() -> dict[str, Any]:
    return {
        "layers": {
            "A_product_mapping": "Migros-Bon-Produkt → normalisiertes Produkt → mögliche Alternativen; keine Preise nötig.",
            "B_price_source": "Preis/Einheitspreis mit Datum und Quelle; Open Prices/manuell/Link-Out/offizielle API.",
            "C_savings": "Ersparnis nur bei Preis, Einheitspreis oder Packungsgröße, vergleichbarer Einheit, Preisdatum, Quelle und hoher Confidence.",
        },
        "provider_matrix": PROVIDER_MATRIX,
        "llm_policy": GROCERY_LLM_POLICY,
        "rappn": {"official_public_api_found": False, "mode": "link_out_only", "api_base_observed": "https://api.rappn.ch returned 403/500 during public docs check"},
        "safety": ["Keine Provider-Abfrage beim normalen Rendern", "Keine Preisannahmen durch LLM", "Unsichere Preise zählen nie als sichere Ersparnis"],
    }


def build_rappn_link_out(product_name: str) -> dict[str, Any]:
    query = str(product_name or "").strip()
    return {"provider": "Rappn.ch", "mode": "link_out_only", "url": f"https://rappn.ch/en/search?q={quote_plus(query)}", "query": query, "manual_source": "manual/rappn_user_checked", "official_public_api_found": False}


def normalize_product_name(raw_product_name: str) -> dict[str, str]:
    raw = raw_product_name
    text = re.sub(r"\b(aktion|rabatt|bonus|cumulus|hit|promo)\b", " ", raw, flags=re.I)
    quantity = re.search(r"\b\d+(?:[.,]\d+)?\s*(?:kg|g|l|ml|stk|stück|x)\b", text, flags=re.I)
    brand = "M-Budget" if re.search(r"m[- ]?budget", text, flags=re.I) else None
    text = re.sub(r"m[- ]?budget|bio", " ", text, flags=re.I)
    text = re.sub(r"\b\d+(?:[.,]\d+)?\s*(?:kg|g|l|ml|stk|stück|x)\b", " ", text, flags=re.I)
    words = [w.lower() for w in re.findall(r"[A-Za-zÄÖÜäöüß]+", text) if len(w) > 1]
    core_map = {"milch": "milch", "eier": "eier", "ei": "eier", "reis": "reis", "poulet": "poulet", "joghurt": "joghurt", "yogurt": "joghurt", "käse": "käse", "kaese": "käse", "waschmittel": "waschmittel", "katzenfutter": "katzenfutter"}
    core = next((core_map[w] for w in words if w in core_map), " ".join(words[:3]).strip() or raw.strip().lower())
    return {"raw_product_name": raw, "normalized_product_name": core, "brand_hint": brand or "", "quantity_hint": quantity.group(0).replace(" ", "") if quantity else "", "confidence": "0.82" if core else "0.50"}


def add_demo_migros_receipt(conn: Connection, *, purchase_date: str, store_name: str, items: list[dict[str, Any]], receipt_id: str | None = None) -> dict[str, Any]:
    rid = receipt_id or new_id("mreceipt")
    out_items: list[dict[str, Any]] = []
    for idx, item in enumerate(items, start=1):
        norm = normalize_product_name(str(item.get("raw_product_name") or ""))
        pid = new_id("gitem")
        total = _fmt(item.get("total_price_text") or item.get("total") or "0")
        conn.execute(
            """INSERT INTO grocery_product_items(product_item_id,source_type,receipt_id,receipt_key,source_line_id,purchase_date,store_name,raw_product_name,normalized_product_name,normalization_confidence,brand_hint,quantity_hint,quantity_text,unit,unit_price_text,total_price_text,currency,action_label,include_in_analysis,notes,health_analysis_status,health_flags_json,health_notes,created_at)
               VALUES (?, 'migros_receipt', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'CHF', ?, 1, ?, 'prepared_not_run', '{}', ?, ?)""",
            (pid, rid, f"{purchase_date}:{store_name}", str(idx), purchase_date, store_name, item.get("raw_product_name"), norm["normalized_product_name"], norm["confidence"], norm["brand_hint"], norm["quantity_hint"], item.get("quantity_text"), item.get("unit"), item.get("unit_price_text"), total, item.get("action_label"), item.get("notes"), "Gesundheitsanalyse später", _now()),
        )
        row = row_to_dict(conn.execute("SELECT * FROM grocery_product_items WHERE product_item_id=?", (pid,)).fetchone())
        out_items.append(row)
    conn.commit()
    return {"receipt_id": rid, "purchase_date": purchase_date, "store_name": store_name, "items": out_items}


def _items_for_receipt(conn: Connection, receipt_id: str) -> list[dict[str, Any]]:
    rows = conn.execute("SELECT * FROM grocery_product_items WHERE receipt_id=? ORDER BY source_line_id, product_item_id", (receipt_id,)).fetchall()
    return [row_to_dict(r) for r in rows]


def get_grocery_optimizer_dashboard(conn: Connection) -> dict[str, Any]:
    rows = conn.execute("""SELECT receipt_id, purchase_date, store_name, COUNT(*) AS article_count, COALESCE(SUM(CAST(total_price_text AS REAL)),0) AS total_value FROM grocery_product_items GROUP BY receipt_id,purchase_date,store_name ORDER BY purchase_date DESC, receipt_id DESC""").fetchall()
    receipts = []
    for r in rows:
        items = _items_for_receipt(conn, r["receipt_id"])
        for item in items:
            item["known_mappings"] = known_mappings_for_item(conn, item, include_rejected=True)
        receipts.append({"receipt_id": r["receipt_id"], "purchase_date": r["purchase_date"], "store_name": r["store_name"], "article_count": int(r["article_count"] or 0), "total_text": _fmt(r["total_value"]), "items": items})
    return {"purpose": "migros_product_savings_grocery_optimizer_v1", "receipts": receipts, "supported_retailers": sorted(SAFE_RETAILERS - {"Migros"}), "health_analysis_status": "prepared_not_run", "disclaimer": "Keine medizinische Bewertung; keine automatische Bestellung."}


def _validate_product_url(url: str) -> None:
    parsed = urlparse(url)
    if parsed.scheme not in {"http", "https"} or not parsed.netloc:
        raise ValueError("invalid_product_url")
    host = (parsed.hostname or "").strip().lower().rstrip(".")
    if not host or host == "localhost" or host.endswith(".localhost"):
        raise ValueError("unsafe_product_url")
    try:
        ip = ipaddress.ip_address(host)
    except ValueError:
        if re.fullmatch(r"[0-9.]+", host):
            try:
                ip = ipaddress.ip_address(socket.inet_ntoa(socket.inet_aton(host)))
            except OSError:
                return
        else:
            return
    if ip.is_loopback or ip.is_private or ip.is_link_local or ip.is_multicast or ip.is_unspecified or ip.is_reserved:
        raise ValueError("unsafe_product_url")


def upsert_product_match(conn: Connection, product_item_id: str, *, retailer: str, candidate_product_name: str, candidate_url: str, candidate_price_text: str | None, candidate_unit_price_text: str | None = None, quality_flags: list[str] | None = None, match_confidence: str = "0.60", status: str = "suggested", source: str = "manual", candidate_brand: str | None = None, candidate_package_size: str | None = None, candidate_unit: str | None = None) -> dict[str, Any]:
    if retailer not in SAFE_RETAILERS:
        raise ValueError("unsupported_retailer")
    _validate_product_url(candidate_url)
    mid = new_id("gmatch")
    flags = quality_flags or ["needs_review"]
    fetched = _now()
    source_hash = hashlib.sha256((retailer + candidate_url + candidate_product_name + str(candidate_price_text)).encode()).hexdigest()
    conn.execute(
        """INSERT INTO grocery_product_matches(match_id,product_item_id,retailer,candidate_product_name,candidate_url,candidate_brand,candidate_package_size,candidate_unit,candidate_price_text,candidate_unit_price_text,price_currency,match_confidence,match_reason,quality_flags_json,fetched_at,source,status)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'CHF', ?, ?, ?, ?, ?, ?)""",
        (mid, product_item_id, retailer, candidate_product_name, candidate_url, candidate_brand, candidate_package_size, candidate_unit, _fmt(candidate_price_text) if candidate_price_text is not None else None, candidate_unit_price_text, match_confidence, "reviewable sourced candidate", json.dumps(flags), fetched, source, status),
    )
    if source not in {"web_fetch", "cache"}:
        conn.execute(
            """INSERT OR REPLACE INTO grocery_product_details_cache(detail_id,retailer,product_url,product_name,price_text,unit_price_text,package_size,ingredients_text,nutrition_json,fetched_at,cache_status,source_hash)
               VALUES (COALESCE((SELECT detail_id FROM grocery_product_details_cache WHERE retailer=? AND product_url=?), ?), ?, ?, ?, ?, ?, ?, NULL, '{}', ?, 'cached', ?)""",
            (retailer, candidate_url, new_id("gdetail"), retailer, candidate_url, candidate_product_name, _fmt(candidate_price_text) if candidate_price_text is not None else None, candidate_unit_price_text, candidate_package_size, fetched, source_hash),
        )
    conn.commit()
    return row_to_dict(conn.execute("SELECT * FROM grocery_product_matches WHERE match_id=?", (mid,)).fetchone())


def _matches(conn: Connection, product_item_id: str) -> list[dict[str, Any]]:
    return [row_to_dict(r) for r in conn.execute("SELECT * FROM grocery_product_matches WHERE product_item_id=? ORDER BY CAST(match_confidence AS REAL) DESC", (product_item_id,)).fetchall()]


def _cache_status_for_url(conn: Connection, retailer: str, url: str) -> dict[str, Any]:
    row = conn.execute("SELECT * FROM grocery_product_details_cache WHERE retailer=? AND product_url=? ORDER BY fetched_at DESC LIMIT 1", (retailer, url)).fetchone()
    if not row:
        return {"cache_status": "fehlt", "last_price_checked_at": None}
    data = row_to_dict(row)
    try:
        fetched = datetime.fromisoformat(str(data.get("fetched_at")))
        if fetched.tzinfo is None:
            fetched = fetched.replace(tzinfo=timezone.utc)
        age = (datetime.now(timezone.utc) - fetched).total_seconds()
        status = "frisch" if age <= 86400 else "veraltet"
    except Exception:
        status = "fehler"
    return {"cache_status": status, "last_price_checked_at": data.get("fetched_at"), "price_text": data.get("price_text"), "unit_price_text": data.get("unit_price_text")}


def known_mappings_for_item(conn: Connection, item: dict[str, Any], include_rejected: bool = False) -> list[dict[str, Any]]:
    statuses = ("accepted", "needs_review", "rejected") if include_rejected else ("accepted", "needs_review")
    rows = conn.execute(
        f"SELECT * FROM grocery_product_mappings WHERE source_retailer='Migros' AND source_product_normalized_name=? AND status IN ({','.join('?' for _ in statuses)}) ORDER BY status='accepted' DESC, CAST(confidence AS REAL) DESC, updated_at DESC",
        (item.get("normalized_product_name"), *statuses),
    ).fetchall()
    mappings = []
    for row in rows:
        d = row_to_dict(row)
        cache = _cache_status_for_url(conn, d["target_retailer"], d["target_product_url"])
        price_source = str(d.get("target_price_source") or "")
        d["cache_status"] = "manuell" if price_source.startswith("manual") and d.get("target_price_text") else cache["cache_status"]
        d["cache_last_price_checked_at"] = cache.get("last_price_checked_at")
        d["cache_price_text"] = cache.get("price_text")
        d["cache_unit_price_text"] = cache.get("unit_price_text")
        if cache.get("last_price_checked_at") and not d.get("last_price_checked_at"):
            d["last_price_checked_at"] = cache.get("last_price_checked_at")
        mappings.append(d)
    return mappings


def _insert_mapping(conn: Connection, *, item: dict[str, Any], target_retailer: str, target_product_name: str, target_product_url: str, match_type: str, status: str, confidence: str = "0.80", user_note: str | None = None, source_match_id: str | None = None, target_brand: str | None = None, target_package_size: str | None = None, target_unit: str | None = None, target_price_text: str | None = None, target_unit_price_text: str | None = None, target_price_date: str | None = None, target_price_source: str | None = None, quality_flags: list[str] | None = None, last_price_checked_at: str | None = None) -> dict[str, Any]:
    if target_retailer not in SAFE_RETAILERS:
        raise ValueError("unsupported_retailer")
    if match_type not in SAFE_MATCH_TYPES:
        raise ValueError("unsupported_match_type")
    _validate_product_url(target_product_url)
    now = _now()
    mapping_id = new_id("gmap")
    conn.execute(
        """INSERT INTO grocery_product_mappings(mapping_id,source_product_normalized_name,source_product_raw_name,source_retailer,target_retailer,target_product_name,target_product_url,target_brand,target_package_size,target_unit,target_price_text,target_unit_price_text,price_currency,target_price_date,target_price_source,match_type,status,confidence,user_note,source_match_id,quality_flags_json,health_analysis_status,health_flags_json,health_notes,created_at,updated_at,last_price_checked_at)
           VALUES (?, ?, ?, 'Migros', ?, ?, ?, ?, ?, ?, ?, ?, 'CHF', ?, ?, ?, ?, ?, ?, ?, ?, 'prepared_not_run', '{}', ?, ?, ?, ?)""",
        (mapping_id, item.get("normalized_product_name"), item.get("raw_product_name"), target_retailer, target_product_name, target_product_url, target_brand, target_package_size, target_unit, _fmt(target_price_text) if target_price_text else None, target_unit_price_text, target_price_date, target_price_source, match_type, status, confidence, user_note, source_match_id, json.dumps(quality_flags or [match_type]), "Gesundheitsanalyse später", now, now, last_price_checked_at),
    )
    conn.commit()
    mapping = row_to_dict(conn.execute("SELECT * FROM grocery_product_mappings WHERE mapping_id=?", (mapping_id,)).fetchone())
    _audit(conn, action="mapping_inserted", source=target_price_source or "grocery_optimizer", mapping_id=mapping_id, product_item_id=item.get("product_item_id"), payload={"status": status, "target_retailer": target_retailer, "has_price": bool(target_price_text), "has_unit_price": bool(target_unit_price_text), "price_date": target_price_date})
    conn.commit()
    return mapping


def accept_grocery_product_match(conn: Connection, match_id: str, *, user_note: str | None = None, save_as_preferred: bool = True) -> dict[str, Any]:
    row = conn.execute("SELECT * FROM grocery_product_matches WHERE match_id=?", (match_id,)).fetchone()
    if not row:
        raise ValueError("match_not_found")
    match = row_to_dict(row)
    item = row_to_dict(conn.execute("SELECT * FROM grocery_product_items WHERE product_item_id=?", (match["product_item_id"],)).fetchone())
    conn.execute("UPDATE grocery_product_matches SET status='accepted' WHERE match_id=?", (match_id,))
    flags = json.loads(match.get("quality_flags_json") or "[]")
    match_type = "manual_match" if not match.get("candidate_price_text") else next((flag for flag in flags if flag in SAFE_MATCH_TYPES), "manual_match")
    mapping = _insert_mapping(conn, item=item, target_retailer=match["retailer"], target_product_name=match["candidate_product_name"], target_product_url=match["candidate_url"], target_brand=match.get("candidate_brand"), target_package_size=match.get("candidate_package_size"), target_unit=match.get("candidate_unit"), target_price_text=match.get("candidate_price_text"), target_unit_price_text=match.get("candidate_unit_price_text"), match_type=match_type, status="accepted" if save_as_preferred else "needs_review", confidence=match.get("match_confidence") or "0.80", user_note=user_note, source_match_id=match_id, quality_flags=flags or [match_type], last_price_checked_at=match.get("fetched_at") if match.get("candidate_price_text") else None)
    return mapping


def reject_grocery_product_match(conn: Connection, match_id: str, *, user_note: str | None = None) -> dict[str, Any]:
    row = conn.execute("SELECT * FROM grocery_product_matches WHERE match_id=?", (match_id,)).fetchone()
    if not row:
        raise ValueError("match_not_found")
    match = row_to_dict(row)
    item = row_to_dict(conn.execute("SELECT * FROM grocery_product_items WHERE product_item_id=?", (match["product_item_id"],)).fetchone())
    conn.execute("UPDATE grocery_product_matches SET status='rejected' WHERE match_id=?", (match_id,))
    mapping = _insert_mapping(conn, item=item, target_retailer=match["retailer"], target_product_name=match["candidate_product_name"], target_product_url=match["candidate_url"], target_brand=match.get("candidate_brand"), target_package_size=match.get("candidate_package_size"), target_unit=match.get("candidate_unit"), target_price_text=match.get("candidate_price_text"), target_unit_price_text=match.get("candidate_unit_price_text"), match_type="manual_match", status="rejected", confidence=match.get("match_confidence") or "0.50", user_note=user_note, source_match_id=match_id, quality_flags=json.loads(match.get("quality_flags_json") or "[]"), last_price_checked_at=match.get("fetched_at"))
    return mapping


def preview_manual_grocery_mapping(conn: Connection, *, product_item_id: str, target_retailer: str, target_product_name: str, target_product_url: str, candidate_price_text: str | None = None, candidate_unit_price_text: str | None = None, target_package_size: str | None = None, target_unit: str | None = None, target_price_date: str | None = None, target_price_source: str | None = None, user_note: str | None = None) -> dict[str, Any]:
    item = row_to_dict(conn.execute("SELECT * FROM grocery_product_items WHERE product_item_id=?", (product_item_id,)).fetchone())
    if not item:
        raise ValueError("product_item_not_found")
    _validate_product_url(target_product_url)
    price = _fmt(candidate_price_text) if candidate_price_text else None
    unit_price = candidate_unit_price_text or (f"{price}/{target_unit}" if price and target_unit else None)
    complete = all([price, unit_price, target_price_date, target_price_source, target_product_url])
    quality_flags = ["manual_match"] + (["manual_price"] if price else ["no_price"]) + ([] if unit_price else ["no_unit_price"])
    if not complete:
        quality_flags.append("needs_review")
    preview = {"preview_id": new_id("gprev"), "product_item_id": product_item_id, "source_product": item.get("raw_product_name"), "target_retailer": target_retailer, "target_product_name": target_product_name, "target_product_url": target_product_url, "target_package_size": target_package_size, "target_unit": target_unit, "candidate_price_text": price, "candidate_unit_price_text": unit_price, "target_price_date": target_price_date, "target_price_source": target_price_source, "user_note": user_note, "quality_flags": quality_flags, "can_count_as_secure_saving": bool(complete), "requires_confirm": True}
    _audit(conn, action="manual_mapping_preview", source=target_price_source or "manual", product_item_id=product_item_id, payload={k: v for k, v in preview.items() if k not in {"source_product"}})
    conn.commit()
    return preview


def confirm_manual_grocery_mapping(conn: Connection, preview: dict[str, Any]) -> dict[str, Any]:
    required = ["product_item_id", "target_retailer", "target_product_name", "target_product_url"]
    if any(not preview.get(k) for k in required):
        raise ValueError("manual_mapping_preview_incomplete")
    return create_manual_grocery_mapping(
        conn,
        product_item_id=preview["product_item_id"],
        target_retailer=preview["target_retailer"],
        target_product_name=preview["target_product_name"],
        target_product_url=preview["target_product_url"],
        candidate_price_text=preview.get("candidate_price_text"),
        candidate_unit_price_text=preview.get("candidate_unit_price_text"),
        target_brand=preview.get("target_brand"),
        target_package_size=preview.get("target_package_size"),
        target_unit=preview.get("target_unit"),
        target_price_date=preview.get("target_price_date"),
        target_price_source=preview.get("target_price_source") or "manual",
        user_note=preview.get("user_note"),
    )


def create_manual_grocery_mapping(conn: Connection, *, product_item_id: str, target_retailer: str, target_product_name: str, target_product_url: str, match_type: str = "manual_match", candidate_price_text: str | None = None, candidate_unit_price_text: str | None = None, target_brand: str | None = None, target_package_size: str | None = None, target_unit: str | None = None, target_price_date: str | None = None, target_price_source: str | None = None, user_note: str | None = None, confidence: str = "0.95") -> dict[str, Any]:
    item = row_to_dict(conn.execute("SELECT * FROM grocery_product_items WHERE product_item_id=?", (product_item_id,)).fetchone())
    flags = [match_type]
    if candidate_price_text:
        flags.append("manual_price")
    else:
        flags.append("no_price")
    if not candidate_unit_price_text:
        flags.append("no_unit_price")
    if candidate_price_text and (not target_price_date or not target_price_source):
        flags.append("needs_review")
    mapping = _insert_mapping(conn, item=item, target_retailer=target_retailer, target_product_name=target_product_name, target_product_url=target_product_url, target_brand=target_brand, target_package_size=target_package_size, target_unit=target_unit, target_price_text=candidate_price_text, target_unit_price_text=candidate_unit_price_text, target_price_date=target_price_date, target_price_source=target_price_source, match_type=match_type, status="accepted", confidence=confidence, user_note=user_note, quality_flags=flags, last_price_checked_at=target_price_date)
    return mapping


def refresh_grocery_mapping_price(conn: Connection, mapping_id: str) -> dict[str, Any]:
    row = conn.execute("SELECT * FROM grocery_product_mappings WHERE mapping_id=?", (mapping_id,)).fetchone()
    if not row:
        raise ValueError("mapping_not_found")
    mapping = row_to_dict(row)
    cache = _cache_status_for_url(conn, mapping["target_retailer"], mapping["target_product_url"])
    if cache.get("price_text"):
        flags = [f for f in json.loads(mapping.get("quality_flags_json") or "[]") if f not in {"manual_price", "needs_review", "no_price", "no_unit_price"}]
        if "manual_match" not in flags:
            flags.append("manual_match")
        conn.execute(
            """UPDATE grocery_product_mappings
               SET target_price_text=?, target_unit_price_text=?, target_price_date=?, target_price_source=?, quality_flags_json=?, last_price_checked_at=?, updated_at=?
               WHERE mapping_id=?""",
            (cache.get("price_text"), cache.get("unit_price_text"), (cache.get("last_price_checked_at") or "")[:10] or None, "cache", json.dumps(flags), cache.get("last_price_checked_at"), _now(), mapping_id),
        )
        conn.commit()
        mapping.update({"target_price_text": cache.get("price_text"), "target_unit_price_text": cache.get("unit_price_text"), "last_price_checked_at": cache.get("last_price_checked_at")})
    mapping.update({"cache_status": cache["cache_status"], "price_refresh_result": "cache_price_applied" if cache.get("price_text") else "no_sourced_price_available"})
    return mapping


def _safe_match(match: dict[str, Any]) -> bool:
    flags = set(json.loads(match.get("quality_flags_json") or "[]"))
    has_required_price_data = bool(match.get("candidate_price_text")) and bool(match.get("candidate_unit_price_text")) and bool(match.get("candidate_url")) and bool(match.get("fetched_at") or match.get("target_price_date") or match.get("last_price_checked_at")) and bool(match.get("source") or match.get("target_price_source"))
    if not has_required_price_data:
        return False
    if flags & UNCERTAIN_FLAGS:
        return False
    return match.get("status") in {"suggested", "accepted"} and bool(flags & SAFE_FLAGS)


def run_grocery_optimization(conn: Connection, receipt_id: str, *, selected_retailers: list[str], max_store_count: int = 3, included_product_item_ids: list[str] | None = None) -> dict[str, Any]:
    max_store_count = min(max(1, int(max_store_count or 3)), 3)
    selected = [r for r in selected_retailers if r in SAFE_RETAILERS]
    items = _items_for_receipt(conn, receipt_id)
    if included_product_item_ids is not None:
        include = set(included_product_item_ids)
        items = [i for i in items if i["product_item_id"] in include]
    else:
        items = [i for i in items if int(i.get("include_in_analysis") or 0) == 1]
    original = sum((_money(i["total_price_text"]) for i in items), Decimal("0.00"))

    # First pass: determine each item's best safe alternative and potential saving.
    item_options: list[dict[str, Any]] = []
    retailer_savings: dict[str, Decimal] = defaultdict(lambda: Decimal("0.00"))
    uncertain = 0
    for item in items:
        item_price = _money(item["total_price_text"])
        candidates = [m for m in _matches(conn, item["product_item_id"]) if m["retailer"] in selected]
        for mapping in known_mappings_for_item(conn, item, include_rejected=False):
            if mapping["status"] == "accepted" and mapping["target_retailer"] in selected and mapping.get("target_price_text") and mapping.get("last_price_checked_at"):
                candidates.append({"match_id": mapping["mapping_id"], "product_item_id": item["product_item_id"], "retailer": mapping["target_retailer"], "candidate_product_name": mapping["target_product_name"], "candidate_url": mapping["target_product_url"], "candidate_brand": mapping.get("target_brand"), "candidate_package_size": mapping.get("target_package_size"), "candidate_unit": mapping.get("target_unit"), "candidate_price_text": mapping.get("target_price_text"), "candidate_unit_price_text": mapping.get("target_unit_price_text"), "match_confidence": mapping.get("confidence") or "0.95", "quality_flags_json": mapping.get("quality_flags_json") or json.dumps([mapping.get("match_type") or "manual_match"]), "status": "accepted", "source": mapping.get("target_price_source") or "known_mapping", "target_price_source": mapping.get("target_price_source"), "target_price_date": mapping.get("target_price_date"), "last_price_checked_at": mapping.get("last_price_checked_at")})
        rejected_mappings = [m for m in conn.execute("SELECT * FROM grocery_product_mappings WHERE source_product_normalized_name=? AND status='rejected'", (item.get("normalized_product_name"),)).fetchall()]
        safe = [m for m in candidates if _safe_match(m)]
        best = min(safe, key=lambda m: _money(m["candidate_price_text"]), default=None)
        if best:
            retailer_savings[best["retailer"]] += max(Decimal("0.00"), item_price - _money(best["candidate_price_text"]))
        elif candidates:
            uncertain += 1
        item_options.append({"item": item, "item_price": item_price, "candidates": candidates, "safe": safe, "best": best, "has_rejected": bool(rejected_mappings)})

    allowed_retailers = set(r for r, _saving in sorted(retailer_savings.items(), key=lambda kv: kv[1], reverse=True)[:max_store_count])
    comparisons = []
    by_store: dict[str, list[dict[str, Any]]] = defaultdict(list)
    optimized = Decimal("0.00")
    replaceable = 0
    for option in item_options:
        item = option["item"]; item_price = option["item_price"]
        safe_in_allowed = [m for m in option["safe"] if m["retailer"] in allowed_retailers]
        best = min(safe_in_allowed, key=lambda m: _money(m["candidate_price_text"]), default=None)
        original_best = option["best"]
        status = "not_found"
        savings = Decimal("0.00")
        flags: list[str] = []
        if best:
            alt = _money(best["candidate_price_text"])
            savings = max(Decimal("0.00"), item_price - alt)
            optimized += alt
            replaceable += 1
            status = "suggested"
            flags = json.loads(best.get("quality_flags_json") or "[]")
            by_store[best["retailer"]].append({"product": item["raw_product_name"], "candidate_product_name": best["candidate_product_name"], "price_text": _fmt(alt), "source_url": best["candidate_url"]})
        else:
            optimized += item_price
            if original_best and original_best.get("retailer") not in allowed_retailers:
                status = "not_selected_due_to_store_limit"
                flags = json.loads(original_best.get("quality_flags_json") or "[]")
            elif option.get("has_rejected"):
                status = "rejected"
            elif option["candidates"]:
                status = "needs_review"
                flags = json.loads(option["candidates"][0].get("quality_flags_json") or "[]")
        comparisons.append({"product_item_id": item["product_item_id"], "migros_product": item["raw_product_name"], "migros_price_text": _fmt(item_price), "best_alternative": best["candidate_product_name"] if best else None, "retailer": best["retailer"] if best else None, "alternative_price_text": _fmt(best["candidate_price_text"]) if best else None, "unit_price_text": best.get("candidate_unit_price_text") if best else None, "savings_text": _fmt(savings), "confidence": best.get("match_confidence") if best else "0", "status": status, "quality_flags": flags, "source_url": best.get("candidate_url") if best else None})
    stores = sorted(by_store.items(), key=lambda kv: len(kv[1]), reverse=True)
    optimized_lists = [{"variant": "max_3_stores", "store_count": len(stores), "stores": [{"retailer": retailer, "items": rows} for retailer, rows in stores], "not_found_products": [c["migros_product"] for c in comparisons if c["status"] == "not_found"], "needs_review_products": [c["migros_product"] for c in comparisons if c["status"] == "needs_review"], "rejected_products": [c["migros_product"] for c in comparisons if c["status"] == "rejected"], "store_limit_omitted_products": [c["migros_product"] for c in comparisons if c["status"] == "not_selected_due_to_store_limit"]}]
    savings_total = max(Decimal("0.00"), original - optimized)
    quality = "review_required" if uncertain or any(c["status"] != "suggested" for c in comparisons) else "ok"
    run_id = new_id("grun")
    summary = {"replaceable_product_count": replaceable, "uncertain_product_count": uncertain, "estimated_savings_text": _fmt(savings_total), "optimized_lists": optimized_lists, "product_comparisons": comparisons}
    conn.execute("""INSERT INTO grocery_optimization_runs(run_id,receipt_id,run_date,selected_retailers_json,max_store_count,original_total_text,optimized_total_text,estimated_savings_text,quality_status,summary_json,created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""", (run_id, receipt_id, _now(), json.dumps(selected), max_store_count, _fmt(original), _fmt(optimized), _fmt(savings_total), quality, json.dumps(summary), _now()))
    conn.commit()
    return {"run_id": run_id, "receipt_id": receipt_id, "max_store_count": max_store_count, "original_total_text": _fmt(original), "optimized_total_text": _fmt(optimized), "quality_status": quality, "summary": summary, "optimized_lists": optimized_lists, "product_comparisons": comparisons, "health_analysis_status": "prepared_not_run"}

def _outside_repo(path: Path, repo_root: Path) -> bool:
    try:
        path.resolve().relative_to(repo_root.resolve())
        return False
    except ValueError:
        return True


def generate_grocery_optimization_report(conn: Connection, run_id: str, *, reports_dir: Path | None = None, repo_root: Path | None = None) -> dict[str, Any]:
    repo = (repo_root or find_repo_root()).resolve()
    if reports_dir is None:
        reports_dir = load_settings(repo_root=repo).runtime_paths.reports_dir / "grocery_optimizer"
    reports_dir = reports_dir.resolve()
    if not _outside_repo(reports_dir, repo):
        raise ValueError("reports_dir_must_be_outside_repo")
    row = conn.execute("SELECT * FROM grocery_optimization_runs WHERE run_id=?", (run_id,)).fetchone()
    if not row:
        raise ValueError("run_not_found")
    run = row_to_dict(row); summary = json.loads(run["summary_json"])
    items = _items_for_receipt(conn, run["receipt_id"])
    date = items[0].get("purchase_date") if items else run["run_date"][:10]
    reports_dir.mkdir(parents=True, exist_ok=True)
    path = (reports_dir / f"migros_einkaufsoptimierung_{date}_{run_id}.md").resolve()
    if reports_dir not in path.parents:
        raise ValueError("report_path_must_stay_under_runtime_reports_dir")
    lines = [
        f"# Migros Einkaufsoptimierung – {date}",
        "",
        "## Bon-Auswahl",
        f"- Bon: {run['receipt_id']}",
        f"- Datum: {date}",
        "",
        "## Analysierte Produkte",
        f"- Artikel: {len(items)}",
        f"- Originaltotal: CHF {run['original_total_text']}",
        "",
        "## Optimierung",
        f"- Optimiertes Total: CHF {run['optimized_total_text']}",
        f"- Geschätzte Ersparnis nur aus sicheren Matches: CHF {run['estimated_savings_text']}",
        f"- Qualität: {run['quality_status']}",
        "",
        "## Akzeptierte Alternativen",
    ]
    accepted = [c for c in summary.get("product_comparisons", []) if c.get("best_alternative") and c.get("status") in {"suggested", "accepted"}]
    lines += [f"- {c['migros_product']} → {c['retailer']} · {c['best_alternative']} · Quelle {c.get('source_url') or 'fehlt'} · Preiszeitpunkt siehe Cache" for c in accepted] or ["- keine"]
    lines += ["", "## Abgelehnte Alternativen"]
    lines += [f"- {p}" for p in summary.get("optimized_lists", [{}])[0].get("rejected_products", [])] or ["- keine"]
    lines += ["", "## Unsichere Produkte"]
    lines += [f"- {p}" for p in summary.get("optimized_lists", [{}])[0].get("needs_review_products", [])] or ["- keine"]
    lines += ["", "## Nicht gefundene Produkte"]
    lines += [f"- {p}" for p in summary.get("optimized_lists", [{}])[0].get("not_found_products", [])] or ["- keine"]
    lines += ["", "## Bekannte Mappings"]
    mappings = conn.execute("SELECT * FROM grocery_product_mappings WHERE source_product_normalized_name IN (SELECT normalized_product_name FROM grocery_product_items WHERE receipt_id=?) ORDER BY status, target_retailer", (run["receipt_id"],)).fetchall()
    lines += [f"- {m['source_product_raw_name'] or m['source_product_normalized_name']} → {m['target_retailer']} · {m['target_product_name']} · {m['target_product_url']} · Status {m['status']} · Preiszeitpunkt {m['last_price_checked_at'] or 'fehlt'}" for m in mappings] or ["- keine"]
    lines += ["", "## Quellen", "Alle Alternativen enthalten Quellen-URLs und Zeitstempel im lokalen Cache.", "", "## Disclaimer", "Preise können sich ändern. Produktgleichheit nicht garantiert. Verfügbarkeit nicht garantiert. Dies ist keine medizinische Empfehlung. Keine automatische Bestellung."]
    path.write_text("\n".join(lines), encoding="utf-8")
    conn.execute("UPDATE grocery_optimization_runs SET report_path=? WHERE run_id=?", (str(path), run_id)); conn.commit()
    return {"run_id": run_id, "format": "markdown", "report_path": str(path), "runtime_only": True}