spb/lou-ka

1# -----------------------------------------------------------------------------2# Lou-Ka — génère docs/FAIRVALUE-VALIDATION.md depuis la base (données réelles)3#   Usage : .venv/bin/python scripts/fv_validation_report.py4# -----------------------------------------------------------------------------5import sys6import time7from pathlib import Path89sys.path.insert(0, str(Path(__file__).resolve().parent.parent))10from louka import db  # noqa: E4021112con = db.connect()13q = lambda sql, *a: con.execute(sql, a).fetchall()   # noqa: E73114one = lambda sql, *a: con.execute(sql, a).fetchone()  # noqa: E73115L: list[str] = []16p = L.append1718p("# Rapport de validation — juste valeur Lou-Ka (fv-logement-1.0)\n")19p(f"Généré le {time.strftime('%Y-%m-%d %H:%M')} sur le parc publié de www.lou-ka.com.\n")2021p("## 1. Distribution des classifications\n")22tot = one("SELECT COUNT(*) n FROM fairvalue")["n"]23p("| Classification | Annonces | Part | Écart moyen |")24p("|---|---|---|---|")25for r in q("SELECT COALESCE(verdict,'sans verdict (prudence)') v, COUNT(*) n,"26           " ROUND(AVG(deviation)*100,1) d FROM fairvalue"27           " GROUP BY verdict ORDER BY verdict IS NULL, verdict"):28    p(f"| {r['v']} | {r['n']:,} | {100 * r['n'] / tot:.1f} % | {r['d']} % |")29p("")30p("| Confiance | Annonces |")31p("|---|---|")32for r in q("SELECT confidence c, COUNT(*) n FROM fairvalue"33           " GROUP BY confidence ORDER BY n DESC"):34    p(f"| {r['c']} | {r['n']:,} |")35d0 = one("SELECT ROUND(AVG(deviation)*100,2) d FROM fairvalue")["d"]36p(f"\nCentrage global : écart moyen {d0} % (cible ≈ 0 : pas de biais systématique).\n")3738p("## 2. Plausibilité par segment (juste valeur vs loyers réels)\n")39p("| Segment (ville, chambres) | n | FV moyenne | Loyer moyen réel | Écart |")40p("|---|---|---|---|---|")41for r in q("""SELECT f.segment s, COUNT(*) n, ROUND(AVG(f.fv)) fv, ROUND(AVG(l.price)) pr42              FROM fairvalue f JOIN listings l USING(uid) WHERE f.segment LIKE '2|%'43              GROUP BY f.segment ORDER BY n DESC LIMIT 14"""):44    seg = r["s"].split("|", 1)[1].replace("|", ", ")45    ec = 100 * (r["fv"] - r["pr"]) / r["pr"]46    p(f"| {seg} | {r['n']} | {r['fv']:.0f} $ | {r['pr']:.0f} $ | {ec:+.1f} % |")47p("")4849p("## 3. Exemples par catégorie (confiance forte)\n")50LIB = {"sous": "Sous le marché", "marche": "Dans le marché", "sur": "Au-dessus du marché"}51for v in ("sous", "marche", "sur"):52    p(f"### {LIB[v]}")53    for r in q("""SELECT l.price pr, f.fv, ROUND(f.deviation*100) d, l.city,54                         l.unit_type u, substr(l.title,1,50) t55                  FROM fairvalue f JOIN listings l USING(uid)56                  WHERE f.verdict=? AND f.confidence='fort'57                  ORDER BY l.last_seen DESC LIMIT 3""", v):58        p(f"- {r['t']} ({r['u'] or '—'}, {r['city'] or '—'}) : {r['pr']:.0f} $ "59          f"demandé vs {r['fv']:.0f} $ estimé ({r['d']:+.0f} %)")60    p("")6162p("## 4. Calibration temporelle (à re-mesurer)\n")63for r in q("""SELECT f.verdict v, ROUND(AVG((strftime('%s','now')-l.first_seen)/86400.0),1) j,64                     COUNT(*) n65              FROM fairvalue f JOIN listings l USING(uid)66              WHERE f.verdict IS NOT NULL GROUP BY f.verdict"""):67    p(f"- {LIB[r['v']]} : {r['j']} jours en ligne en moyenne ({r['n']:,} annonces)")68p("")69p("La base d'annonces est encore jeune : la durée de présence ne discrimine pas")70p("encore les verdicts. Re-mesurer après quelques semaines d'historique et")71p("recalibrer les seuils ±8 % (procédure : docs/FAIRVALUE.md §6).")7273out = Path(__file__).resolve().parent.parent / "docs" / "FAIRVALUE-VALIDATION.md"74out.write_text("\n".join(L).replace(",", ",").replace(",", ","), encoding="utf-8")75print(f"écrit: {out}")76