# -----------------------------------------------------------------------------
# Immo-Ka — Agrégateur de maisons à vendre (province de Québec)
# Auteur : Simon-Pierre Boucher — contact@spboucher.ai
# web.py : API FastAPI (JSON) + service du frontend statique (frontend/)
# -----------------------------------------------------------------------------
from __future__ import annotations

import json
import threading
from pathlib import Path

from fastapi import BackgroundTasks, FastAPI, HTTPException, Query
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import FileResponse
from fastapi.staticfiles import StaticFiles

from . import db, ingest

ROOT = Path(__file__).resolve().parent.parent
SOURCES_PATH = ROOT / "data" / "sources.json"
# Frontend : build Vite (React) si présent, sinon la page statique de secours.
FRONTEND_DIST = ROOT / "frontend" / "dist"
FRONTEND_DIR = FRONTEND_DIST if FRONTEND_DIST.exists() else ROOT / "frontend"

app = FastAPI(title="Immo-Ka API", version="0.1",
              description="Agrégateur de maisons à vendre — province de Québec")
app.add_middleware(CORSMiddleware, allow_origins=["*"],
                   allow_methods=["*"], allow_headers=["*"])

_sync_lock = threading.Lock()

# Déduplication de la famille RE/MAX : le flux central (remax_quebec) et les
# ~42 connecteurs de sous-agences (remax_ag_*) décrivent les MÊMES inscriptions,
# identifiées de façon unique par leur numéro Centris (= external_id). On masque
# donc toute fiche de sous-agence dès qu'une fiche de plus haute priorité existe
# (le central enrichi d'abord, puis la sous-agence au plus petit uid). Les fiches
# du central et des autres agences ne sont jamais masquées. → aucun double-comptage,
# et les sous-agences prennent le relais automatiquement si le central disparaît.
# Déduplication PRÉ-CALCULÉE : la colonne `dup_hidden` (remplie par
# db.refresh_dedup après chaque sync) marque les doublons de sous-agences. La
# lecture est ainsi instantanée (index) au lieu d'un sous-select corrélé par
# ligne (~300 s sur 75 k lignes). Voir db.refresh_dedup pour la règle.
DEDUP_CLAUSE = " AND dup_hidden=0"


def _row_to_dict(row) -> dict:
    d = dict(row)
    d["features"] = json.loads(d.get("features") or "[]")
    d["images"] = json.loads(d.get("images") or "[]")
    d["details"] = json.loads(d.get("details") or "{}")
    if d.get("vraiprix"):
        try:
            d["vraiprix"] = json.loads(d["vraiprix"]) or None
        except (ValueError, TypeError):
            d["vraiprix"] = None
    return d


@app.get("/api/listings")
def list_listings(
    city: str | None = None,
    sector: str | None = None,
    region: str | None = None,
    property_type: str | None = None,
    source: str | None = None,
    price_max: float | None = None,
    price_min: float | None = None,
    bedrooms_min: int | None = None,
    bathrooms_min: int | None = None,
    area_min: float | None = None,       # superficie habitable minimale (pi²)
    q: str | None = None,
    active: int = 1,
    sort: str = "price_asc",             # price_asc | price_desc | recent
    limit: int = Query(500, le=2000),
    offset: int = 0,
):
    con = db.connect()
    sql = "SELECT * FROM listings WHERE 1=1"
    args: list = []
    if active in (0, 1):
        sql += " AND active=?"; args.append(active)
    if city:
        sql += " AND city=?"; args.append(city)
    if sector:
        sql += " AND sector LIKE ?"; args.append(f"%{sector}%")
    if region:
        sql += " AND region=?"; args.append(region)
    if property_type:
        sql += " AND property_type=?"; args.append(property_type)
    if source:
        sql += " AND source=?"; args.append(source)
    if price_max is not None:
        sql += " AND price IS NOT NULL AND price<=?"; args.append(price_max)
    if price_min is not None:
        sql += " AND price IS NOT NULL AND price>=?"; args.append(price_min)
    if bedrooms_min is not None:
        sql += " AND bedrooms IS NOT NULL AND bedrooms>=?"; args.append(bedrooms_min)
    if bathrooms_min is not None:
        sql += " AND bathrooms IS NOT NULL AND bathrooms>=?"; args.append(bathrooms_min)
    if area_min is not None:
        sql += " AND area_sqft IS NOT NULL AND area_sqft>=?"; args.append(area_min)
    if q:
        sql += " AND (title LIKE ? OR address LIKE ? OR city LIKE ? OR mls LIKE ?)"
        args += [f"%{q}%"] * 4
    sql += DEDUP_CLAUSE
    total = con.execute(f"SELECT COUNT(*) c FROM ({sql})", args).fetchone()["c"]
    order = {
        "price_asc": " ORDER BY price IS NULL, price ASC",
        "price_desc": " ORDER BY price IS NULL, price DESC",
        "recent": " ORDER BY first_seen DESC",
    }.get(sort, " ORDER BY price IS NULL, price ASC")
    sql += order + " LIMIT ? OFFSET ?"
    args += [limit, offset]
    rows = [_row_to_dict(r) for r in con.execute(sql, args).fetchall()]
    con.close()
    return {"total": total, "count": len(rows), "listings": rows}


@app.get("/api/listings/{uid}")
def get_listing(uid: str):
    con = db.connect()
    row = con.execute("SELECT * FROM listings WHERE uid=?", (uid,)).fetchone()
    d = None
    if row is not None:
        d = _row_to_dict(row)
        # historique de prix (baisses/hausses du prix demandé)
        d["price_history"] = [dict(r) for r in con.execute(
            "SELECT ts, price FROM price_log WHERE uid=? ORDER BY ts DESC LIMIT 10",
            (uid,)).fetchall()]
        # commodités de proximité (cache par immeuble, voir immoka/poi.py)
        if d.get("lat") is not None and d.get("lng") is not None:
            key = f"{round(d['lat'], 4)},{round(d['lng'], 4)}"
            poi_row = con.execute(
                "SELECT pois FROM poi_cache WHERE coord_key=?", (key,)).fetchone()
            d["poi"] = json.loads(poi_row["pois"]) if poi_row else []
        else:
            d["poi"] = []
        # statistiques de quartier (recensement, proximité, chaleur, criminalité)
        from . import quartier
        dauid = d.get("dauid")
        d["quartier"] = quartier.fiche_quartier(
            d.get("lat"), d.get("lng"), d.get("city") or "",
            dauid if dauid and dauid != "hors-zone" else None)
    con.close()
    if d is None:
        raise HTTPException(404, "Propriété introuvable")
    return d


@app.get("/api/listings.geojson")
def listings_geojson(
    city: str | None = None,
    property_type: str | None = None,
    source: str | None = None,
    price_max: float | None = None,
    price_min: float | None = None,
    bedrooms_min: int | None = None,
):
    """Propriétés géolocalisées (marqueurs de carte, champs allégés)."""
    con = db.connect()
    sql = ("SELECT uid, title, address, price, price_label, property_type,"
           " bedrooms, bathrooms, source, city, sector, images, lat, lng"
           " FROM listings WHERE active=1 AND lat IS NOT NULL AND lng IS NOT NULL")
    args: list = []
    if city:
        sql += " AND city=?"; args.append(city)
    if property_type:
        sql += " AND property_type=?"; args.append(property_type)
    if source:
        sql += " AND source=?"; args.append(source)
    if price_max is not None:
        sql += " AND price IS NOT NULL AND price<=?"; args.append(price_max)
    if price_min is not None:
        sql += " AND price IS NOT NULL AND price>=?"; args.append(price_min)
    if bedrooms_min is not None:
        sql += " AND bedrooms IS NOT NULL AND bedrooms>=?"; args.append(bedrooms_min)
    sql += DEDUP_CLAUSE
    features = []
    for r in con.execute(sql, args).fetchall():
        images = json.loads(r["images"] or "[]")
        features.append({
            "type": "Feature",
            "geometry": {"type": "Point", "coordinates": [r["lng"], r["lat"]]},
            "properties": {
                "uid": r["uid"], "title": r["title"], "address": r["address"],
                "price": r["price"], "price_label": r["price_label"],
                "property_type": r["property_type"], "bedrooms": r["bedrooms"],
                "bathrooms": r["bathrooms"], "source": r["source"],
                "city": r["city"], "sector": r["sector"],
                "image": images[0] if images else None,
            },
        })
    con.close()
    return {"type": "FeatureCollection", "features": features}


@app.get("/api/facets")
def facets(city: str | None = None):
    """Valeurs distinctes pour construire les filtres du frontend."""
    con = db.connect()
    sector_sql = "SELECT DISTINCT sector FROM listings WHERE active=1 AND sector<>''"
    sector_args: list = []
    if city:
        sector_sql += " AND city=?"
        sector_args.append(city)
    out = {
        "cities": [r["city"] for r in con.execute(
            "SELECT DISTINCT city FROM listings WHERE active=1 AND city<>'' ORDER BY city")],
        "sectors": [r["sector"] for r in con.execute(
            sector_sql + " ORDER BY sector", sector_args)],
        "property_types": [r["property_type"] for r in con.execute(
            "SELECT DISTINCT property_type FROM listings WHERE active=1"
            " AND property_type<>'' ORDER BY property_type")],
        "sources": [dict(r) for r in con.execute(
            "SELECT source, COUNT(*) n FROM listings WHERE active=1"
            + DEDUP_CLAUSE + " GROUP BY source ORDER BY n DESC")],
    }
    con.close()
    return out


@app.get("/api/sources")
def sources():
    registry = json.loads(SOURCES_PATH.read_text(encoding="utf-8"))["sources"]
    con = db.connect()
    counts = {r["source"]: r["n"] for r in con.execute(
        "SELECT source, COUNT(*) n FROM listings WHERE active=1 GROUP BY source")}
    last = {r["source"]: r["ts"] for r in con.execute(
        "SELECT source, MAX(ts) ts FROM sync_log WHERE ok=1 GROUP BY source")}
    con.close()
    for s in registry:
        s["active_listings"] = counts.get(s["id"], 0)
        s["last_sync"] = last.get(s["id"])
    return {"sources": registry}


# Rattachement d'une source à sa bannière (franchise) pour le regroupement.
_FRANCHISES = [
    ("RE/MAX", lambda s: s == "remax_quebec" or s.startswith("remax_ag_")),
    ("Via Capitale", lambda s: s == "via_capitale" or s.startswith("via_ag_")),
    ("Century 21", lambda s: s == "century21" or s.startswith("c21_ag_")),
    ("Royal LePage", lambda s: s == "royal_lepage"),
    ("Groupe Sutton", lambda s: s == "sutton"),
    ("Keller Williams", lambda s: s.startswith("kw_")),
    ("DuProprio", lambda s: s == "duproprio"),
]


def _franchise_of(source: str, source_names: dict) -> str:
    for name, match in _FRANCHISES:
        if match(source):
            return name
    return source_names.get(source, source)   # agence indépendante = elle-même


@app.get("/api/agencies")
def agencies():
    """Arbre bannière → sous-agences (bureaux) avec le nombre d'inscriptions.

    Alimente la page « Sources » de l'app : chaque bannière est éclatée par
    sous-agence via le champ `agency` (bureau). Dédupliqué (n° Centris)."""
    con = db.connect()
    registry = json.loads(SOURCES_PATH.read_text(encoding="utf-8"))["sources"]
    source_names = {s["id"]: s["name"] for s in registry}
    rows = con.execute(
        "SELECT source, COALESCE(NULLIF(agency,''), '') agency, COUNT(*) n"
        " FROM listings WHERE active=1" + DEDUP_CLAUSE +
        " GROUP BY source, agency").fetchall()
    con.close()
    tree: dict[str, dict] = {}
    for r in rows:
        fr = _franchise_of(r["source"], source_names)
        node = tree.setdefault(fr, {"franchise": fr, "total": 0, "agencies": {}})
        node["total"] += r["n"]
        # nom de sous-agence : le bureau (agency) sinon le nom de la source
        label = r["agency"] or source_names.get(r["source"], r["source"])
        a = node["agencies"].setdefault(label, {"name": label, "count": 0,
                                                 "sources": set()})
        a["count"] += r["n"]
        a["sources"].add(r["source"])
    out = []
    for node in tree.values():
        ags = sorted(node["agencies"].values(), key=lambda x: -x["count"])
        for a in ags:
            a["sources"] = sorted(a["sources"])
        out.append({"franchise": node["franchise"], "total": node["total"],
                    "sub_agencies": len(ags), "agencies": ags})
    out.sort(key=lambda x: -x["total"])
    return {"franchises": out}


@app.get("/api/stats")
def stats():
    con = db.connect()
    row = con.execute(
        """SELECT COUNT(*) total,
                  COUNT(DISTINCT source) sources,
                  COUNT(DISTINCT city) cities,
                  AVG(price) avg_price,
                  MIN(price) min_price,
                  MAX(price) max_price
           FROM listings WHERE active=1""" + DEDUP_CLAUSE).fetchone()
    log = [dict(r) for r in con.execute(
        "SELECT * FROM sync_log ORDER BY ts DESC LIMIT 20")]
    con.close()
    return {**dict(row), "recent_syncs": log}


@app.post("/api/sync")
def trigger_sync(background: BackgroundTasks, source: str | None = None):
    """Déclenche une synchronisation (équivalent d'un webhook entrant)."""
    def _job():
        with _sync_lock:
            ingest.run([source] if source else None)
    background.add_task(_job)
    return {"status": "démarré", "source": source or "toutes"}


# --- Frontend statique -------------------------------------------------------
if FRONTEND_DIR.exists():

    if (FRONTEND_DIR / "assets").is_dir():
        app.mount("/assets", StaticFiles(directory=FRONTEND_DIR / "assets"), name="assets")

    @app.get("/{full_path:path}")
    def spa(full_path: str):
        target = FRONTEND_DIR / full_path
        if full_path and target.is_file():
            return FileResponse(target)
        return FileResponse(FRONTEND_DIR / "index.html")