# -----------------------------------------------------------------------------
# Auto-Ka — Agrégateur de voitures usagées à vendre (province de Québec)
# Auteur : Simon-Pierre Boucher — contact@spboucher.ai
# marketstats.py : agrégats du marché — source unique pour /api/stats/detailed
#                  et le rapport PDF (autoka/pdfgen.py).
# -----------------------------------------------------------------------------
from __future__ import annotations

import statistics
import time

from . import db

# bornes des classes de prix (k$) — dernière classe ouverte
PRICE_BUCKETS = [0, 5, 10, 15, 20, 25, 30, 40, 50, 75, 100]
KM_BUCKETS = [0, 20, 40, 60, 80, 100, 130, 160, 200]      # en milliers de km


def compute() -> dict:
    """Tous les agrégats du marché sur l'inventaire actif."""
    con = db.connect()
    now = time.time()

    prices = [r["price"] for r in con.execute(
        "SELECT price FROM vehicles WHERE active=1 AND kind='auto' AND price IS NOT NULL")]
    kms = [r["mileage_km"] for r in con.execute(
        "SELECT mileage_km FROM vehicles WHERE active=1 AND kind='auto' AND mileage_km IS NOT NULL")]

    head = con.execute(
        """SELECT COUNT(*) total, COUNT(DISTINCT source) sources,
                  COUNT(DISTINCT region) regions, AVG(price) avg_price,
                  AVG(mileage_km) avg_km, AVG(year) avg_year,
                  SUM(CASE WHEN first_seen > ? THEN 1 ELSE 0 END) new_7d,
                  SUM(CASE WHEN fuel IN ('Électrique') THEN 1 ELSE 0 END) ev,
                  SUM(CASE WHEN fuel LIKE 'Hybride%' THEN 1 ELSE 0 END) hybrid
           FROM vehicles WHERE active=1 AND kind='auto'""", (now - 7 * 86400,)).fetchone()

    # histogramme des prix
    price_hist = []
    for i, lo in enumerate(PRICE_BUCKETS):
        hi = PRICE_BUCKETS[i + 1] if i + 1 < len(PRICE_BUCKETS) else None
        n = sum(1 for p in prices
                if p >= lo * 1000 and (hi is None or p < hi * 1000))
        label = f"{lo}–{hi}k" if hi else f"{lo}k+"
        price_hist.append({"label": label, "n": n})

    # histogramme kilométrage
    km_hist = []
    for i, lo in enumerate(KM_BUCKETS):
        hi = KM_BUCKETS[i + 1] if i + 1 < len(KM_BUCKETS) else None
        n = sum(1 for k in kms
                if k >= lo * 1000 and (hi is None or k < hi * 1000))
        label = f"{lo}–{hi}k" if hi else f"{lo}k+"
        km_hist.append({"label": label, "n": n})

    # répartition par année (2010+ ; avant regroupé)
    year_rows = con.execute(
        "SELECT year, COUNT(*) n FROM vehicles WHERE active=1 AND kind='auto'"
        " AND year IS NOT NULL GROUP BY year ORDER BY year").fetchall()
    year_hist, older = [], 0
    for r in year_rows:
        if r["year"] < 2012:
            older += r["n"]
        else:
            year_hist.append({"label": str(r["year"]), "n": r["n"]})
    if older:
        year_hist.insert(0, {"label": "≤2011", "n": older})

    def _rows(sql, args=()):
        return [dict(r) for r in con.execute(sql, args).fetchall()]

    out = {
        "generated_at": now,
        "total": head["total"],
        "sources": head["sources"],
        "regions": head["regions"],
        "avg_price": head["avg_price"],
        "median_price": statistics.median(prices) if prices else None,
        "avg_km": head["avg_km"],
        "median_km": statistics.median(kms) if kms else None,
        "avg_year": head["avg_year"],
        "new_7d": head["new_7d"],
        "electrified_pct": round(100.0 * (head["ev"] + head["hybrid"])
                                 / head["total"], 1) if head["total"] else 0,
        "ev": head["ev"], "hybrid": head["hybrid"],
        "price_hist": price_hist,
        "km_hist": km_hist,
        "year_hist": year_hist,
        "by_make": _rows(
            "SELECT make label, COUNT(*) n, ROUND(AVG(price)) avg_price"
            " FROM vehicles WHERE active=1 AND kind='auto' AND make<>''"
            " GROUP BY make ORDER BY n DESC LIMIT 14"),
        "by_region": _rows(
            "SELECT region label, COUNT(*) n, ROUND(AVG(price)) avg_price"
            " FROM vehicles WHERE active=1 AND kind='auto' AND region<>''"
            " GROUP BY region ORDER BY n DESC"),
        "by_body": _rows(
            "SELECT body_type label, COUNT(*) n FROM vehicles WHERE active=1 AND kind='auto'"
            " AND body_type<>'' GROUP BY body_type ORDER BY n DESC"),
        "by_fuel": _rows(
            "SELECT fuel label, COUNT(*) n FROM vehicles WHERE active=1 AND kind='auto'"
            " AND fuel<>'' GROUP BY fuel ORDER BY n DESC"),
        "top_models": _rows(
            "SELECT make || ' ' || model label, COUNT(*) n,"
            " ROUND(AVG(price)) avg_price, ROUND(AVG(mileage_km)) avg_km"
            " FROM vehicles WHERE active=1 AND kind='auto' AND make<>'' AND model<>''"
            " GROUP BY make, model ORDER BY n DESC LIMIT 15"),
        "top_dealers": _rows(
            "SELECT dealer_name label, COUNT(*) n, ROUND(AVG(price)) avg_price"
            " FROM vehicles WHERE active=1 AND kind='auto' AND dealer_name<>''"
            " GROUP BY dealer_name ORDER BY n DESC LIMIT 12"),
        "avg_price_by_year": _rows(
            "SELECT year label, ROUND(AVG(price)) n FROM vehicles"
            " WHERE active=1 AND kind='auto' AND year>=2012 AND price IS NOT NULL"
            " GROUP BY year ORDER BY year"),
        "price_drops": _rows(
            """SELECT v.uid, v.title, v.year, v.price, v.dealer_name, v.city,
                      p.prev_price
               FROM vehicles v JOIN (
                 SELECT uid, price prev_price,
                        ROW_NUMBER() OVER (PARTITION BY uid ORDER BY ts DESC) rn
                 FROM price_log) p ON p.uid=v.uid AND p.rn=2
               WHERE v.active=1 AND kind='auto' AND v.price IS NOT NULL
                 AND p.prev_price > v.price
               ORDER BY (p.prev_price - v.price) DESC LIMIT 12"""),
    }
    con.close()
    return out