# -----------------------------------------------------------------------------
# Lou-Ka — Agrégateur de logements à louer (province de Québec)
# Auteur : Simon-Pierre Boucher — contact@spboucher.ai
# statsextra.py : panneaux « données du territoire » de l'onglet Statistiques
#   Trois blocs indépendants du marché des annonces, calculés sur nos jeux de
#   données ouverts et mis en cache :
#     · Registre des loyers (loyers réellement déclarés, data/rdl.db) ;
#     · Population & territoire (recensement des secteurs couverts,
#       quartier.db.da_stats joint aux annonces par aire de diffusion) ;
#     · Prix de l'essence (stations gazquebec.ca, data/gaz.db).
#   Chaque panneau réutilise les rendus existants du kit stats (kpis, donut/
#   barres, histogramme, tableau) — aucune donnée inventée : un bloc absent si
#   sa source ne répond pas.
# -----------------------------------------------------------------------------
from __future__ import annotations

import sqlite3
import time
from pathlib import Path
from statistics import median

ROOT = Path(__file__).resolve().parent.parent
DATA = ROOT / "data"
_CACHE: dict[str, tuple[float, list]] = {}
_TTL = 1800


def _ro(name: str) -> sqlite3.Connection | None:
    p = DATA / name
    if not p.exists():
        return None
    con = sqlite3.connect(f"file:{p}?mode=ro", uri=True)
    con.row_factory = sqlite3.Row
    return con


def _money(v) -> str:
    return f"{round(v):,}".replace(",", " ") + " $" if v is not None else "—"


def _q(vals: list[float], f: float) -> float:
    s = sorted(vals)
    if not s:
        return 0
    i = f * (len(s) - 1)
    lo = int(i)
    hi = min(lo + 1, len(s) - 1)
    return s[lo] + (s[hi] - s[lo]) * (i - lo)


# --- Panneau 1 : Registre des loyers -----------------------------------------
def _panel_rdl() -> dict | None:
    con = _ro("rdl.db")
    if con is None:
        return None
    try:
        rows = con.execute(
            "SELECT price, rooms, year, city FROM rdl_housings "
            "WHERE price > 100 AND price < 20000").fetchall()
    except sqlite3.Error:
        con.close()
        return None
    con.close()
    if len(rows) < 100:
        return None
    prices = [r["price"] for r in rows]
    recent = [r["price"] for r in rows if (r["year"] or 0) >= 2023]
    villes = {r["city"] for r in rows if r["city"]}

    kpis = [
        {"id": "rdl_n", "label": "Loyers déclarés", "value": len(rows)},
        {"id": "rdl_med", "label": "Loyer médian déclaré",
         "value": round(median(prices)), "unit": "$"},
        {"id": "rdl_rec", "label": "Médiane depuis 2023",
         "value": round(median(recent)) if recent else None, "unit": "$"},
        {"id": "rdl_villes", "label": "Villes représentées", "value": len(villes)},
    ]

    # médiane par nombre de chambres (barres)
    byr: dict[int, list[float]] = {}
    for r in rows:
        if r["rooms"] is not None and 0 <= r["rooms"] <= 6:
            byr.setdefault(r["rooms"], []).append(r["price"])
    bars = [{"label": f"{k} ch.", "value": round(median(v))}
            for k, v in sorted(byr.items()) if len(v) >= 10]

    # histogramme des loyers déclarés (récents de préférence)
    base = recent if len(recent) >= 500 else prices
    lo, hi = round(_q(base, 0.02)), round(_q(base, 0.98))
    step = max(50, round((hi - lo) / 14 / 50) * 50)
    bins = []
    x = lo
    while x < hi:
        n = sum(1 for p in base if x <= p < x + step)
        bins.append({"label": f"{x}", "value": n})
        x += step

    # top villes par nombre de déclarations
    vcount: dict[str, list[float]] = {}
    for r in rows:
        if r["city"]:
            vcount.setdefault(r["city"], []).append(r["price"])
    top = sorted(vcount.items(), key=lambda kv: -len(kv[1]))[:15]
    table = {"id": "rdl_villes_t",
             "title": "Loyers déclarés par ville (top 15)",
             "columns": ["Ville", "Déclarations", "Loyer médian"],
             "rows": [[v, len(p), _money(median(p))] for v, p in top]}

    return {
        "id": "registre_loyers",
        "title": "Registre des loyers — loyers réellement payés",
        "subtitle": "Loyers déclarés volontairement par des locataires au "
                    "Registre des loyers (Vivre en ville). Données citoyennes, "
                    "indicatives.",
        "kpis": [k for k in kpis if k.get("value") is not None],
        "breakdowns": [{"id": "rdl_rooms", "title": "Loyer médian déclaré par "
                        "nombre de chambres", "kind": "bar", "items": bars}]
                      if bars else [],
        "distributions": [{"id": "rdl_hist",
                           "title": "Distribution des loyers déclarés"
                           + (" (depuis 2023)" if base is recent else ""),
                           "unit": "$", "bins": bins}] if len(bins) >= 4 else [],
        "tables": [table],
    }


# --- Panneau 2 : Population & territoire --------------------------------------
def _active_where(con: sqlite3.Connection) -> str:
    """Filtre « annonces actives » adapté au schéma (lou-ka a dup_of, pas immo)."""
    cols = {r[1] for r in con.execute("PRAGMA table_info(listings)")}
    w = "active=1" if "active" in cols else "1=1"
    if "dup_of" in cols:
        w += " AND dup_of IS NULL"
    return w


def _panel_population(con: sqlite3.Connection) -> dict | None:
    qc = _ro("quartier.db")
    if qc is None:
        return None
    aw = _active_where(con)
    try:
        # aires de diffusion couvertes par au moins une annonce active
        dauids = [r[0] for r in con.execute(
            f"SELECT DISTINCT dauid FROM listings WHERE {aw} "
            "AND dauid IS NOT NULL AND dauid<>''")]
    except sqlite3.Error:
        dauids = []
    if len(dauids) < 20:
        qc.close()
        return None
    # charger le recensement des DA couvertes
    rows = []
    CH = 400
    for i in range(0, len(dauids), CH):
        chunk = dauids[i:i + CH]
        ph = ",".join("?" * len(chunk))
        rows += qc.execute(
            f"SELECT * FROM da_stats WHERE dauid IN ({ph})", chunk).fetchall()
    qc.close()
    if not rows:
        return None

    def med(col):
        v = [r[col] for r in rows if r[col] is not None]
        return median(v) if v else None

    pop_tot = sum(r["population"] or 0 for r in rows)
    kpis = [
        {"id": "pop_tot", "label": "Population des secteurs couverts",
         "value": round(pop_tot)},
        {"id": "pop_rev", "label": "Revenu médian des ménages (médiane)",
         "value": round(med("revenu_median")) if med("revenu_median") else None,
         "unit": "$"},
        {"id": "pop_loc", "label": "Part de locataires (médiane)",
         "value": round(med("pct_locataires")) if med("pct_locataires") is not None else None,
         "unit": "%"},
        {"id": "pop_age", "label": "Âge médian (médiane des secteurs)",
         "value": round(med("age_median")) if med("age_median") else None,
         "unit": "ans"},
        {"id": "pop_univ", "label": "Diplôme universitaire (médiane)",
         "value": round(med("pct_univ")) if med("pct_univ") is not None else None,
         "unit": "%"},
    ]

    # répartition de la population par quintile de défavorisation matérielle
    defav = _ro("quartier.db")
    donut_items = []
    try:
        dmap = {r["dauid"]: r["quintile_materiel"] for r in defav.execute(
            "SELECT dauid, quintile_materiel FROM da_defav")}
        buckets: dict[int, float] = {}
        for r in rows:
            qv = dmap.get(r["dauid"])
            if qv:
                buckets[qv] = buckets.get(qv, 0) + (r["population"] or 0)
        labels = {1: "Très favorisé", 2: "Favorisé", 3: "Moyen",
                  4: "Défavorisé", 5: "Très défavorisé"}
        donut_items = [{"label": labels.get(k, str(k)), "value": round(v)}
                       for k, v in sorted(buckets.items())]
    except sqlite3.Error:
        pass
    finally:
        if defav:
            defav.close()

    # tableau par ville : population, revenu médian, % locataires
    qc2 = _ro("quartier.db")
    table = None
    try:
        # villes avec le plus d'annonces actives
        top_cities = [r["city"] for r in con.execute(
            f"SELECT city, COUNT(*) n FROM listings WHERE {aw} "
            "AND city<>'' GROUP BY city ORDER BY n DESC LIMIT 15")]
        st = {r["dauid"]: r for r in qc2.execute("SELECT * FROM da_stats")}
        trows = []
        for city in top_cities:
            das = [r["dauid"] for r in con.execute(
                f"SELECT DISTINCT dauid FROM listings WHERE {aw} "
                "AND city=? AND dauid IS NOT NULL", (city,))]
            recs = [st[d] for d in das if d in st]
            if len(recs) < 3:
                continue
            pop = sum(x["population"] or 0 for x in recs)
            rev = [x["revenu_median"] for x in recs if x["revenu_median"]]
            loc = [x["pct_locataires"] for x in recs
                   if x["pct_locataires"] is not None]
            trows.append([city, f"{round(pop):,}".replace(",", " "),
                          _money(median(rev)) if rev else "—",
                          f"{round(median(loc))} %" if loc else "—"])
        if trows:
            table = {"id": "pop_villes",
                     "title": "Profil des secteurs couverts par ville",
                     "columns": ["Ville", "Population", "Revenu médian",
                                 "Locataires"],
                     "rows": trows}
    except sqlite3.Error:
        pass
    finally:
        qc2.close()

    return {
        "id": "population",
        "title": "Population & territoire",
        "subtitle": "Portrait sociodémographique des secteurs (aires de "
                    "diffusion) où se trouvent les annonces — recensement.",
        "kpis": [k for k in kpis if k.get("value") is not None],
        "breakdowns": [{"id": "pop_defav",
                        "title": "Population par niveau de défavorisation "
                        "matérielle", "kind": "donut", "items": donut_items}]
                      if donut_items else [],
        "tables": [table] if table else [],
    }


# --- Panneau 3 : Prix de l'essence -------------------------------------------
def _panel_gaz() -> dict | None:
    try:
        from . import gaz
        gcon = gaz._connect()
        row = gcon.execute("SELECT v FROM meta WHERE k='maj'").fetchone()
        if row is None or time.time() - float(row["v"]) > gaz.TTL:
            try:
                gaz.refresh(gcon)
            except Exception:
                pass
        rows = gcon.execute(
            "SELECT region, prix_regulier, prix_super, prix_diesel "
            "FROM gaz_stations WHERE prix_regulier > 50").fetchall()
        gcon.close()
    except Exception:
        return None
    if len(rows) < 30:
        return None
    reg = [r["prix_regulier"] for r in rows]
    sup = [r["prix_super"] for r in rows if r["prix_super"]]
    die = [r["prix_diesel"] for r in rows if r["prix_diesel"]]

    def c(v):
        return round(v, 1) if v is not None else None

    kpis = [
        {"id": "gaz_n", "label": "Stations suivies", "value": len(rows)},
        {"id": "gaz_reg", "label": "Régulier médian",
         "value": c(median(reg)), "unit": "¢/L"},
        {"id": "gaz_min", "label": "Meilleur prix (régulier)",
         "value": c(min(reg)), "unit": "¢/L"},
        {"id": "gaz_sup", "label": "Super médian",
         "value": c(median(sup)) if sup else None, "unit": "¢/L"},
        {"id": "gaz_die", "label": "Diesel médian",
         "value": c(median(die)) if die else None, "unit": "¢/L"},
    ]

    # prix régulier médian par région
    byreg: dict[str, list[float]] = {}
    for r in rows:
        if r["region"]:
            byreg.setdefault(r["region"], []).append(r["prix_regulier"])
    bars = sorted(
        ([{"label": k, "value": round(median(v), 1)}
          for k, v in byreg.items() if len(v) >= 5]),
        key=lambda x: x["value"])
    # tableau régions
    treg = sorted(([k, len(v), round(median(v), 1)]
                   for k, v in byreg.items() if len(v) >= 5),
                  key=lambda x: x[2])
    table = {"id": "gaz_regions",
             "title": "Prix de l'essence régulière par région",
             "columns": ["Région", "Stations", "Régulier médian (¢/L)"],
             "rows": [[r[0], r[1], f"{r[2]:.1f}"] for r in treg]}

    return {
        "id": "essence",
        "title": "Prix de l'essence au Québec",
        "subtitle": "Prix courants des stations-service (gazquebec.ca), toutes "
                    "régions confondues.",
        "kpis": [k for k in kpis if k.get("value") is not None],
        "breakdowns": [{"id": "gaz_reg_bar",
                        "title": "Essence régulière — médiane par région (¢/L)",
                        "kind": "bar", "items": bars}] if bars else [],
        "tables": [table],
    }


def panels(con: sqlite3.Connection) -> list[dict]:
    """Retourne les panneaux « territoire » (cache 30 min). `con` = base des
    annonces (pour la jointure recensement via l'aire de diffusion)."""
    hit = _CACHE.get("panels")
    if hit and time.time() - hit[0] < _TTL:
        return hit[1]
    out = []
    for fn in (_panel_rdl, lambda: _panel_population(con), _panel_gaz):
        try:
            p = fn()
            if p and (p.get("kpis") or p.get("tables") or p.get("breakdowns")):
                out.append(p)
        except Exception:
            continue
    _CACHE["panels"] = (time.time(), out)
    return out