# -----------------------------------------------------------------------------
# Lou-Ka — génère docs/FAIRVALUE-VALIDATION.md depuis la base (données réelles)
#   Usage : .venv/bin/python scripts/fv_validation_report.py
# -----------------------------------------------------------------------------
import sys
import time
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent.parent))
from louka import db  # noqa: E402

con = db.connect()
q = lambda sql, *a: con.execute(sql, a).fetchall()   # noqa: E731
one = lambda sql, *a: con.execute(sql, a).fetchone()  # noqa: E731
L: list[str] = []
p = L.append

p("# Rapport de validation — juste valeur Lou-Ka (fv-logement-1.0)\n")
p(f"Généré le {time.strftime('%Y-%m-%d %H:%M')} sur le parc publié de www.lou-ka.com.\n")

p("## 1. Distribution des classifications\n")
tot = one("SELECT COUNT(*) n FROM fairvalue")["n"]
p("| Classification | Annonces | Part | Écart moyen |")
p("|---|---|---|---|")
for r in q("SELECT COALESCE(verdict,'sans verdict (prudence)') v, COUNT(*) n,"
           " ROUND(AVG(deviation)*100,1) d FROM fairvalue"
           " GROUP BY verdict ORDER BY verdict IS NULL, verdict"):
    p(f"| {r['v']} | {r['n']:,} | {100 * r['n'] / tot:.1f} % | {r['d']} % |")
p("")
p("| Confiance | Annonces |")
p("|---|---|")
for r in q("SELECT confidence c, COUNT(*) n FROM fairvalue"
           " GROUP BY confidence ORDER BY n DESC"):
    p(f"| {r['c']} | {r['n']:,} |")
d0 = one("SELECT ROUND(AVG(deviation)*100,2) d FROM fairvalue")["d"]
p(f"\nCentrage global : écart moyen {d0} % (cible ≈ 0 : pas de biais systématique).\n")

p("## 2. Plausibilité par segment (juste valeur vs loyers réels)\n")
p("| Segment (ville, chambres) | n | FV moyenne | Loyer moyen réel | Écart |")
p("|---|---|---|---|---|")
for r in q("""SELECT f.segment s, COUNT(*) n, ROUND(AVG(f.fv)) fv, ROUND(AVG(l.price)) pr
              FROM fairvalue f JOIN listings l USING(uid) WHERE f.segment LIKE '2|%'
              GROUP BY f.segment ORDER BY n DESC LIMIT 14"""):
    seg = r["s"].split("|", 1)[1].replace("|", ", ")
    ec = 100 * (r["fv"] - r["pr"]) / r["pr"]
    p(f"| {seg} | {r['n']} | {r['fv']:.0f} $ | {r['pr']:.0f} $ | {ec:+.1f} % |")
p("")

p("## 3. Exemples par catégorie (confiance forte)\n")
LIB = {"sous": "Sous le marché", "marche": "Dans le marché", "sur": "Au-dessus du marché"}
for v in ("sous", "marche", "sur"):
    p(f"### {LIB[v]}")
    for r in q("""SELECT l.price pr, f.fv, ROUND(f.deviation*100) d, l.city,
                         l.unit_type u, substr(l.title,1,50) t
                  FROM fairvalue f JOIN listings l USING(uid)
                  WHERE f.verdict=? AND f.confidence='fort'
                  ORDER BY l.last_seen DESC LIMIT 3""", v):
        p(f"- {r['t']} ({r['u'] or '—'}, {r['city'] or '—'}) : {r['pr']:.0f} $ "
          f"demandé vs {r['fv']:.0f} $ estimé ({r['d']:+.0f} %)")
    p("")

p("## 4. Calibration temporelle (à re-mesurer)\n")
for r in q("""SELECT f.verdict v, ROUND(AVG((strftime('%s','now')-l.first_seen)/86400.0),1) j,
                     COUNT(*) n
              FROM fairvalue f JOIN listings l USING(uid)
              WHERE f.verdict IS NOT NULL GROUP BY f.verdict"""):
    p(f"- {LIB[r['v']]} : {r['j']} jours en ligne en moyenne ({r['n']:,} annonces)")
p("")
p("La base d'annonces est encore jeune : la durée de présence ne discrimine pas")
p("encore les verdicts. Re-mesurer après quelques semaines d'historique et")
p("recalibrer les seuils ±8 % (procédure : docs/FAIRVALUE.md §6).")

out = Path(__file__).resolve().parent.parent / "docs" / "FAIRVALUE-VALIDATION.md"
out.write_text("\n".join(L).replace(",", ",").replace(",", ","), encoding="utf-8")
print(f"écrit: {out}")