# -----------------------------------------------------------------------------
# Food-Ka — Agrégateur de produits d'épicerie (province de Québec)
# Auteur : Simon-Pierre Boucher — contact@spboucher.ai
# marketstats.py : agrégats du marché — source unique de /api/stats/detailed
#   Métriques par bannière (prix médian, part de soldes, rabais moyen…),
#   matrice catégorie × bannière, panier comparatif, baisses de prix récentes.
# -----------------------------------------------------------------------------
from __future__ import annotations

import statistics
import time

from . import db

# Panier comparatif : produits courants repérés par mots-clés (fr + en).
# Pour chaque bannière on prend le PRIX MÉDIAN des produits correspondants —
# robuste face aux formats extrêmes (mini-format, caisse de grossiste).
BASKET = [
    ("Lait", ["lait", "milk"]),
    ("Œufs", ["oeuf", "œuf", "egg"]),
    ("Pain", ["pain", "bread"]),
    ("Beurre", ["beurre", "butter"]),
    ("Bananes", ["banane", "banana"]),
    ("Poulet", ["poulet", "chicken"]),
    ("Fromage", ["fromage", "cheese"]),
    ("Riz", ["riz", "rice"]),
    ("Pâtes", ["pâtes", "pates", "spaghetti", "pasta"]),
    ("Café", ["café", "cafe", "coffee"]),
]

_PRICE_SANE = "price IS NOT NULL AND price > 0 AND price <= 2000"


def _median(values: list[float]) -> float | None:
    return round(statistics.median(values), 2) if values else None


def compute() -> dict:
    con = db.connect()
    now = time.time()

    # -- par bannière ----------------------------------------------------------
    by_source: dict[str, dict] = {}
    for r in con.execute(
            f"""SELECT source, COUNT(*) n, SUM(on_sale) sales,
                       AVG(price) avg_price,
                       SUM(CASE WHEN unit_price IS NOT NULL THEN 1 ELSE 0 END) with_unit,
                       SUM(CASE WHEN {_PRICE_SANE} THEN 0 ELSE 1 END) no_price
                FROM products WHERE active=1 GROUP BY source"""):
        by_source[r["source"]] = {
            "source": r["source"], "n": r["n"], "sales": r["sales"] or 0,
            "sale_share": round((r["sales"] or 0) / r["n"], 3) if r["n"] else 0,
            "avg_price": round(r["avg_price"], 2) if r["avg_price"] else None,
            "with_unit_price": r["with_unit"], "no_price": r["no_price"],
        }
    # médiane des prix + rabais moyen (nécessite les valeurs)
    for src, d in by_source.items():
        prices = [r["price"] for r in con.execute(
            f"SELECT price FROM products WHERE active=1 AND source=? AND {_PRICE_SANE}",
            (src,))]
        d["median_price"] = _median(prices)
        discounts = [(r["regular_price"] - r["price"]) / r["regular_price"]
                     for r in con.execute(
                         f"""SELECT price, regular_price FROM products
                             WHERE active=1 AND source=? AND on_sale=1
                             AND regular_price IS NOT NULL AND {_PRICE_SANE}
                             AND regular_price > price""", (src,))]
        d["avg_discount_pct"] = round(100 * statistics.mean(discounts), 1) if discounts else None
        d["max_discount_pct"] = round(100 * max(discounts), 1) if discounts else None

    # -- matrice catégorie × bannière (prix médian) ----------------------------
    matrix: dict[str, dict[str, dict]] = {}
    rows = con.execute(
        f"""SELECT category, source, price FROM products
            WHERE active=1 AND category<>'' AND category<>'Autres' AND {_PRICE_SANE}"""
    ).fetchall()
    bucket: dict[tuple[str, str], list[float]] = {}
    for r in rows:
        bucket.setdefault((r["category"], r["source"]), []).append(r["price"])
    for (cat, src), prices in bucket.items():
        matrix.setdefault(cat, {})[src] = {
            "median_price": _median(prices), "n": len(prices)}

    # -- panier comparatif ------------------------------------------------------
    basket: list[dict] = []
    for label, keywords in BASKET:
        like = " OR ".join(["name LIKE ?"] * len(keywords))
        args = [f"%{k}%" for k in keywords]
        per_source: dict[str, dict] = {}
        for r in con.execute(
                f"""SELECT source, price FROM products
                    WHERE active=1 AND {_PRICE_SANE} AND ({like})""", args):
            per_source.setdefault(r["source"], []).append(r["price"])
        basket.append({
            "item": label,
            "by_source": {src: {"median_price": _median(v), "n": len(v)}
                          for src, v in per_source.items()},
        })
    # total du panier par bannière (bannières couvrant >= 6 items sur 10)
    basket_totals = []
    for src in by_source:
        items = [b["by_source"][src]["median_price"] for b in basket
                 if src in b["by_source"] and b["by_source"][src]["median_price"]]
        if len(items) >= 6:
            basket_totals.append({"source": src, "items": len(items),
                                  "total": round(sum(items), 2)})
    basket_totals.sort(key=lambda x: x["total"] / max(x["items"], 1))

    # -- baisses de prix récentes (7 jours) -------------------------------------
    week_ago = now - 7 * 86400
    drops = []
    for r in con.execute(
            """SELECT p.uid, p.name, p.source, p.images, l.ts, l.price new_price,
                      (SELECT l2.price FROM price_log l2
                       WHERE l2.uid = l.uid AND l2.ts < l.ts
                       ORDER BY l2.ts DESC LIMIT 1) old_price
               FROM price_log l JOIN products p ON p.uid = l.uid
               WHERE l.ts >= ? AND p.active = 1
               ORDER BY l.ts DESC LIMIT 400""", (week_ago,)):
        old, new = r["old_price"], r["new_price"]
        if old and new and new < old:
            drops.append({"uid": r["uid"], "name": r["name"], "source": r["source"],
                          "old_price": old, "new_price": new,
                          "drop_pct": round(100 * (old - new) / old, 1)})
    drops.sort(key=lambda d: -d["drop_pct"])
    price_changes_7d = con.execute(
        "SELECT COUNT(*) c FROM price_log WHERE ts >= ?", (week_ago,)).fetchone()["c"]

    # -- distribution des prix (paliers) ----------------------------------------
    buckets = [(0, 2), (2, 5), (5, 10), (10, 20), (20, 50), (50, 2000)]
    distribution = []
    for lo, hi in buckets:
        c = con.execute(
            f"SELECT COUNT(*) c FROM products WHERE active=1 AND {_PRICE_SANE}"
            " AND price >= ? AND price < ?", (lo, hi)).fetchone()["c"]
        label = f"{lo}–{hi} $" if hi <= 50 else f"{lo} $ +"
        distribution.append({"range": label, "n": c})

    # -- global ------------------------------------------------------------------
    g = con.execute(
        f"""SELECT COUNT(*) total, SUM(on_sale) on_sale,
                   COUNT(DISTINCT source) sources,
                   COUNT(DISTINCT category) categories,
                   AVG(price) avg_price,
                   COUNT(DISTINCT brand) brands
            FROM products WHERE active=1""").fetchone()
    all_prices = [r["price"] for r in con.execute(
        f"SELECT price FROM products WHERE active=1 AND {_PRICE_SANE}")]
    con.close()

    return {
        "global": {
            "total": g["total"], "on_sale": g["on_sale"] or 0,
            "sale_share": round((g["on_sale"] or 0) / g["total"], 3) if g["total"] else 0,
            "sources": g["sources"], "categories": g["categories"],
            "brands": g["brands"],
            "avg_price": round(g["avg_price"], 2) if g["avg_price"] else None,
            "median_price": _median(all_prices),
            "price_changes_7d": price_changes_7d,
        },
        "by_source": sorted(by_source.values(), key=lambda d: -d["n"]),
        "category_matrix": matrix,
        "basket": basket,
        "basket_totals": basket_totals,
        "price_drops": drops[:30],
        "price_distribution": distribution,
    }