spb/ora-ka Public

1# -----------------------------------------------------------------------------2# Auto-Ka — Agrégateur de voitures usagées à vendre (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# marketstats.py : agrégats du marché — source unique pour /api/stats/detailed5#                  et le rapport PDF (autoka/pdfgen.py).6# -----------------------------------------------------------------------------7from __future__ import annotations89import statistics10import time1112from . import db1314# bornes des classes de prix (k$) — dernière classe ouverte15PRICE_BUCKETS = [0, 5, 10, 15, 20, 25, 30, 40, 50, 75, 100]16KM_BUCKETS = [0, 20, 40, 60, 80, 100, 130, 160, 200]      # en milliers de km171819def compute() -> dict:20    """Tous les agrégats du marché sur l'inventaire actif."""21    con = db.connect()22    now = time.time()2324    prices = [r["price"] for r in con.execute(25        "SELECT price FROM vehicles WHERE active=1 AND kind='auto' AND price IS NOT NULL")]26    kms = [r["mileage_km"] for r in con.execute(27        "SELECT mileage_km FROM vehicles WHERE active=1 AND kind='auto' AND mileage_km IS NOT NULL")]2829    head = con.execute(30        """SELECT COUNT(*) total, COUNT(DISTINCT source) sources,31                  COUNT(DISTINCT region) regions, AVG(price) avg_price,32                  AVG(mileage_km) avg_km, AVG(year) avg_year,33                  SUM(CASE WHEN first_seen > ? THEN 1 ELSE 0 END) new_7d,34                  SUM(CASE WHEN fuel IN ('Électrique') THEN 1 ELSE 0 END) ev,35                  SUM(CASE WHEN fuel LIKE 'Hybride%' THEN 1 ELSE 0 END) hybrid36           FROM vehicles WHERE active=1 AND kind='auto'""", (now - 7 * 86400,)).fetchone()3738    # histogramme des prix39    price_hist = []40    for i, lo in enumerate(PRICE_BUCKETS):41        hi = PRICE_BUCKETS[i + 1] if i + 1 < len(PRICE_BUCKETS) else None42        n = sum(1 for p in prices43                if p >= lo * 1000 and (hi is None or p < hi * 1000))44        label = f"{lo}–{hi}k" if hi else f"{lo}k+"45        price_hist.append({"label": label, "n": n})4647    # histogramme kilométrage48    km_hist = []49    for i, lo in enumerate(KM_BUCKETS):50        hi = KM_BUCKETS[i + 1] if i + 1 < len(KM_BUCKETS) else None51        n = sum(1 for k in kms52                if k >= lo * 1000 and (hi is None or k < hi * 1000))53        label = f"{lo}–{hi}k" if hi else f"{lo}k+"54        km_hist.append({"label": label, "n": n})5556    # répartition par année (2010+ ; avant regroupé)57    year_rows = con.execute(58        "SELECT year, COUNT(*) n FROM vehicles WHERE active=1 AND kind='auto'"59        " AND year IS NOT NULL GROUP BY year ORDER BY year").fetchall()60    year_hist, older = [], 061    for r in year_rows:62        if r["year"] < 2012:63            older += r["n"]64        else:65            year_hist.append({"label": str(r["year"]), "n": r["n"]})66    if older:67        year_hist.insert(0, {"label": "≤2011", "n": older})6869    def _rows(sql, args=()):70        return [dict(r) for r in con.execute(sql, args).fetchall()]7172    out = {73        "generated_at": now,74        "total": head["total"],75        "sources": head["sources"],76        "regions": head["regions"],77        "avg_price": head["avg_price"],78        "median_price": statistics.median(prices) if prices else None,79        "avg_km": head["avg_km"],80        "median_km": statistics.median(kms) if kms else None,81        "avg_year": head["avg_year"],82        "new_7d": head["new_7d"],83        "electrified_pct": round(100.0 * (head["ev"] + head["hybrid"])84                                 / head["total"], 1) if head["total"] else 0,85        "ev": head["ev"], "hybrid": head["hybrid"],86        "price_hist": price_hist,87        "km_hist": km_hist,88        "year_hist": year_hist,89        "by_make": _rows(90            "SELECT make label, COUNT(*) n, ROUND(AVG(price)) avg_price"91            " FROM vehicles WHERE active=1 AND kind='auto' AND make<>''"92            " GROUP BY make ORDER BY n DESC LIMIT 14"),93        "by_region": _rows(94            "SELECT region label, COUNT(*) n, ROUND(AVG(price)) avg_price"95            " FROM vehicles WHERE active=1 AND kind='auto' AND region<>''"96            " GROUP BY region ORDER BY n DESC"),97        "by_body": _rows(98            "SELECT body_type label, COUNT(*) n FROM vehicles WHERE active=1 AND kind='auto'"99            " AND body_type<>'' GROUP BY body_type ORDER BY n DESC"),100        "by_fuel": _rows(101            "SELECT fuel label, COUNT(*) n FROM vehicles WHERE active=1 AND kind='auto'"102            " AND fuel<>'' GROUP BY fuel ORDER BY n DESC"),103        "top_models": _rows(104            "SELECT make || ' ' || model label, COUNT(*) n,"105            " ROUND(AVG(price)) avg_price, ROUND(AVG(mileage_km)) avg_km"106            " FROM vehicles WHERE active=1 AND kind='auto' AND make<>'' AND model<>''"107            " GROUP BY make, model ORDER BY n DESC LIMIT 15"),108        "top_dealers": _rows(109            "SELECT dealer_name label, COUNT(*) n, ROUND(AVG(price)) avg_price"110            " FROM vehicles WHERE active=1 AND kind='auto' AND dealer_name<>''"111            " GROUP BY dealer_name ORDER BY n DESC LIMIT 12"),112        "avg_price_by_year": _rows(113            "SELECT year label, ROUND(AVG(price)) n FROM vehicles"114            " WHERE active=1 AND kind='auto' AND year>=2012 AND price IS NOT NULL"115            " GROUP BY year ORDER BY year"),116        "price_drops": _rows(117            """SELECT v.uid, v.title, v.year, v.price, v.dealer_name, v.city,118                      p.prev_price119               FROM vehicles v JOIN (120                 SELECT uid, price prev_price,121                        ROW_NUMBER() OVER (PARTITION BY uid ORDER BY ts DESC) rn122                 FROM price_log) p ON p.uid=v.uid AND p.rn=2123               WHERE v.active=1 AND kind='auto' AND v.price IS NOT NULL124                 AND p.prev_price > v.price125               ORDER BY (p.prev_price - v.price) DESC LIMIT 12"""),126    }127    con.close()128    return out129