# -----------------------------------------------------------------------------
# Immo-Ka — Agrégateur de maisons à vendre (province de Québec)
# Auteur : Simon-Pierre Boucher — contact@spboucher.ai
# quality.py : contrôle qualité des annonces — score de complétude, contrôles de
#   cohérence immobiliers, seuil de publication (quarantaine sous le seuil).
#
#   `refresh(con)` recalcule pour toutes les annonces actives :
#     - quality_score  : complétude 0-100 (pondération des champs décisifs)
#     - quality_issues : JSON (anomalies détectées, dures ou informatives)
#     - published      : 1 = affichable sur le site, 0 = quarantaine
#   et les champs dérivés dans details : prix_pi2 (prix/superficie) et
#   transaction (vente | location, détectée du libellé de prix).
#
#   Règle de publication : prix plausible + ville + type de bien + au moins une
#   image + contenu exploitable (description ou caractéristiques). Une annonce
#   sous le seuil reste en base (re-synchronisée/enrichie aux prochains cycles)
#   mais n'est pas affichée — elle sort de quarantaine dès qu'elle est complétée.
# -----------------------------------------------------------------------------
from __future__ import annotations

import json
import os
import re
import sqlite3
import time

from .normalize import strip_accents

# bornes de plausibilité (marché québécois)
PRICE_SALE_MIN, PRICE_SALE_MAX = 20_000, 80_000_000
PRICE_RENT_MIN, PRICE_RENT_MAX = 300, 25_000
AREA_MIN, AREA_MAX = 120, 25_000            # superficie habitable (pi²)
LOT_MAX = 200_000_000                        # terrain (pi²) — grandes terres
YEAR_MIN = 1600

_RENT_RE = re.compile(r"/\s*mois|par mois|/\s*mth|/\s*month|\bmensuel|\ba louer\b|"
                      r"\blouer\b|\blocation\b|\blease\b|\bfor rent\b")


def _transaction(price, price_label: str, title: str) -> str:
    """vente | location — détectée du libellé (jamais confondre loyer et prix)."""
    key = strip_accents(f"{price_label} {title}".lower())
    if _RENT_RE.search(key):
        return "location"
    if price is not None and price < PRICE_RENT_MAX:
        # montant de loyer sans mot-clé : trop bas pour une vente au Québec
        return "location"
    return "vente"


def assess(row: dict) -> tuple[int, list[str], int, dict]:
    """(score 0-100, anomalies, publiable 0/1, champs dérivés) pour une annonce.

    `row` : dict aux clés des colonnes listings (features/details/images
    peuvent être des chaînes JSON ou déjà décodés)."""

    def _json(v, default):
        if isinstance(v, (list, dict)):
            return v
        try:
            return json.loads(v) if v else default
        except (TypeError, ValueError):
            return default

    images = _json(row.get("images"), [])
    features = _json(row.get("features"), [])
    details = _json(row.get("details"), {})
    desc = (row.get("description") or "").strip()
    price = row.get("price")
    issues: list[str] = []
    derived: dict = {}

    tx = _transaction(price, row.get("price_label") or "", row.get("title") or "")
    derived["transaction"] = tx

    # -- cohérence : prix plausible pour le type de transaction ----------------
    price_ok = price is not None and price > 0
    if price_ok:
        lo, hi = ((PRICE_RENT_MIN, PRICE_RENT_MAX) if tx == "location"
                  else (PRICE_SALE_MIN, PRICE_SALE_MAX))
        if not (lo <= price <= hi):
            issues.append(f"prix_hors_bornes:{price:.0f}$ ({tx})")
            price_ok = False

    # -- cohérence : superficies plausibles -------------------------------------
    area = row.get("area_sqft")
    area_ok = area is not None and AREA_MIN <= area <= AREA_MAX
    if area is not None and not area_ok:
        issues.append(f"superficie_improbable:{area:.0f}pi2")
    lot = row.get("lot_sqft")
    if lot is not None and not (0 < lot <= LOT_MAX):
        issues.append(f"terrain_improbable:{lot:.0f}pi2")

    # -- cohérence : pièces / chambres ------------------------------------------
    beds, baths = row.get("bedrooms"), row.get("bathrooms")
    if beds is not None and not (0 <= beds <= 30):
        issues.append(f"chambres_improbables:{beds}")
        beds = None
    if baths is not None and not (0 <= baths <= 20):
        issues.append(f"sdb_improbables:{baths}")
        baths = None
    ptype = (row.get("property_type") or "").strip()
    if beds and ptype == "Condo" and beds > 8:
        issues.append(f"chambres_vs_type:{beds}ch_condo")

    # -- cohérence : année de construction ---------------------------------------
    year = row.get("year_built")
    current_year = time.gmtime().tm_year
    if year is not None and not (YEAR_MIN <= year <= current_year + 3):
        issues.append(f"annee_invalide:{year}")

    # -- champ dérivé : prix au pi² (et m²) ---------------------------------------
    if price_ok and area_ok and tx == "vente":
        ppsf = price / area
        derived["prix_pi2"] = round(ppsf)
        derived["prix_m2"] = round(ppsf * 10.7639)
        if not (30 <= ppsf <= 3500):
            issues.append(f"prix_pi2_extreme:{ppsf:.0f}")

    # -- score de complétude (0-100) ----------------------------------------------
    n_img = len(images)
    has_contact = bool(row.get("broker_name") or row.get("broker_phone"))
    score = 0
    score += 15 if price_ok else 0
    score += 8 if (row.get("city") or "").strip() else 0
    score += 7 if (row.get("address") or "").strip() else 0
    score += 8 if ptype else 0
    score += 10 if n_img >= 1 else 0
    score += 5 if n_img >= 8 else 0
    score += 12 if len(desc) >= 300 else 8 if len(desc) >= 80 else 4 if desc else 0
    score += 6 if beds is not None else 0
    score += 5 if baths is not None else 0
    score += 8 if area_ok else 0
    score += 4 if year is not None else 0
    score += 7 if row.get("lat") is not None else 0
    score += 3 if has_contact else 0
    score += 2 if lot is not None else 0

    # -- seuil de publication ---------------------------------------------------
    # (une annonce SANS image reste publiable : le frontend applique l'image de
    # secours par type de bien ; le drapeau sans_image la marque à re-vérifier)
    if n_img < 1:
        issues.append("sans_image")
    # vendue/louée à la source : archivée (plus jamais affichée en résultats)
    statut = (row.get("status") or "").strip().lower()
    vendue = statut in ("vendu", "vendue", "loue", "louee", "loué", "louée",
                        "sold", "rented", "retire", "retiré")
    if vendue:
        issues.append(f"statut:{statut}")
    # House-Ka : les cartes DDF (liste) n'ont ni description ni type — la
    # fiche s'enrichit au fil des passes de détail. Publication dès que le
    # prix est plausible et la ville connue ; type/description comptent dans
    # le score seulement.
    publishable = (
        not vendue
        and price_ok
        and bool((row.get("city") or "").strip())
    )
    if not publishable:
        why = []
        if not price_ok:
            why.append("prix")
        if not (row.get("city") or "").strip():
            why.append("ville")
        issues.append("quarantaine:" + "+".join(why))

    return score, issues, int(publishable), derived


def refresh(con: sqlite3.Connection, sources: list[str] | None = None) -> dict:
    """Recalcule score/anomalies/publication pour les annonces actives.

    Appelé après chaque synchronisation (ingest.run) — quelques secondes pour
    ~80 k lignes. Retourne un résumé {actives, publiees, quarantaine}."""
    _ensure_columns(con)
    sql = ("SELECT uid, source, title, price, price_label, city, address,"
           " property_type,"
           " bedrooms, bathrooms, area_sqft, lot_sqft, year_built, lat, status,"
           " broker_name, broker_phone, description, features, details, images,"
           " quality_score, quality_issues, published"
           " FROM listings WHERE active=1")
    args: list = []
    if sources:
        sql += f" AND source IN ({','.join('?' * len(sources))})"
        args = list(sources)
    updates = []
    n = pub = 0
    for r in con.execute(sql, args):
        row = dict(r)
        score, issues, publishable, derived = assess(row)
        n += 1
        pub += publishable
        details = {}
        try:
            details = json.loads(row.get("details") or "{}")
        except ValueError:
            pass
        changed_details = any(details.get(k) != v for k, v in derived.items())
        issues_json = json.dumps(issues, ensure_ascii=False) if issues else None
        if (score != row.get("quality_score") or publishable != row.get("published")
                or issues_json != row.get("quality_issues") or changed_details):
            details.update(derived)
            updates.append((score, issues_json, publishable,
                            json.dumps(details, ensure_ascii=False), row["uid"]))
    if updates:
        con.executemany(
            "UPDATE listings SET quality_score=?, quality_issues=?, published=?,"
            " details=? WHERE uid=?", updates)
        con.commit()
    return {"actives": n, "publiees": pub, "quarantaine": n - pub,
            "recalculees": len(updates)}


def _ensure_columns(con: sqlite3.Connection) -> None:
    cols = {r["name"] for r in con.execute("PRAGMA table_info(listings)")}
    if "quality_score" not in cols:
        con.execute("ALTER TABLE listings ADD COLUMN quality_score INTEGER")
    if "quality_issues" not in cols:
        con.execute("ALTER TABLE listings ADD COLUMN quality_issues TEXT")
    if "published" not in cols:
        # 1 par défaut : la 1re passe refresh() met la vraie valeur partout
        con.execute("ALTER TABLE listings ADD COLUMN published INTEGER DEFAULT 1")
    con.execute("CREATE INDEX IF NOT EXISTS idx_listings_published"
                " ON listings(published)")
    con.commit()


def summary(con: sqlite3.Connection) -> dict:
    """Statistiques qualité pour /api/stats : complétude, quarantaine, anomalies."""
    _ensure_columns(con)
    row = con.execute(
        "SELECT COUNT(*) actives, SUM(published) publiees,"
        " ROUND(AVG(quality_score),1) completude_moyenne"
        " FROM listings WHERE active=1 AND dup_hidden=0").fetchone()
    per_source = [dict(r) for r in con.execute(
        "SELECT source, COUNT(*) n, SUM(published) publiees,"
        " ROUND(AVG(quality_score),1) completude,"
        " SUM(CASE WHEN quality_issues IS NOT NULL THEN 1 ELSE 0 END) anomalies"
        " FROM listings WHERE active=1 AND dup_hidden=0"
        " GROUP BY source ORDER BY n DESC")]
    anomalies: dict[str, int] = {}
    for r in con.execute(
            "SELECT quality_issues FROM listings WHERE active=1 AND dup_hidden=0"
            " AND quality_issues IS NOT NULL"):
        try:
            for issue in json.loads(r["quality_issues"]):
                anomalies[issue.split(":")[0]] = anomalies.get(issue.split(":")[0], 0) + 1
        except ValueError:
            continue
    d = dict(row)
    d["quarantaine"] = (d.get("actives") or 0) - (d.get("publiees") or 0)
    d["anomalies"] = dict(sorted(anomalies.items(), key=lambda kv: -kv[1]))
    d["par_source"] = per_source
    return d