# -----------------------------------------------------------------------------
# Lou-Ka — Location court terme
# web.py : routeur API /api/ct/* (liste, geojson, détail, facettes, stats)
#          — inclus par louka/web.py, entièrement séparé du long terme.
# -----------------------------------------------------------------------------
from __future__ import annotations

import json
import math
import threading
from pathlib import Path

from fastapi import APIRouter, BackgroundTasks, HTTPException, Query

from . import db

router = APIRouter(prefix="/api/ct", tags=["court-terme"])

SOURCES_CT_PATH = (Path(__file__).resolve().parent.parent.parent
                   / "data" / "sources_ct.json")
_sync_lock = threading.Lock()

_SORTS = {
    "recent": " ORDER BY first_seen DESC, price_night IS NULL, price_night ASC",
    "prix": " ORDER BY price_night IS NULL, price_night ASC",
    "prix_desc": " ORDER BY price_night IS NULL, price_night DESC",
    "note": " ORDER BY rating IS NULL, rating DESC, reviews DESC",
}


def _row_to_dict(row) -> dict:
    d = dict(row)
    d["amenities"] = json.loads(d.get("amenities") or "[]")
    d["images"] = json.loads(d.get("images") or "[]")
    d["details"] = json.loads(d.get("details") or "{}")
    return d


def _apply_filters(sql: str, args: list,
                   region: str | None, city: str | None, type_: str | None,
                   source: str | None, price_min: float | None,
                   price_max: float | None, capacity_min: float | None,
                   bedrooms_min: float | None, pets: str | None,
                   spa: int | None, waterfront: int | None,
                   q: str | None) -> str:
    if region:
        sql += " AND region=?"; args.append(region)
    if city:
        sql += " AND city LIKE ?"; args.append(f"%{city}%")
    if type_:
        sql += " AND property_type=?"; args.append(type_)
    if source:
        sql += " AND source=?"; args.append(source)
    if price_min is not None:
        sql += " AND price_night IS NOT NULL AND price_night>=?"
        args.append(price_min)
    if price_max is not None:
        sql += " AND price_night IS NOT NULL AND price_night<=?"
        args.append(price_max)
    if capacity_min is not None:
        sql += " AND capacity IS NOT NULL AND capacity>=?"
        args.append(capacity_min)
    if bedrooms_min is not None:
        sql += " AND bedrooms IS NOT NULL AND bedrooms>=?"
        args.append(bedrooms_min)
    if pets == "oui":
        sql += " AND pets IN ('oui','conditions')"
    if spa == 1:
        sql += " AND json_extract(details,'$.spa')=1"
    if waterfront == 1:
        sql += " AND json_extract(details,'$.waterfront')=1"
    if q:
        sql += " AND (title LIKE ? OR city LIKE ? OR region LIKE ?)"
        args += [f"%{q}%"] * 3
    return sql


@router.get("/listings")
def ct_listings(
    region: str | None = None,
    city: str | None = None,
    type: str | None = None,
    source: str | None = None,
    price_min: float | None = None,
    price_max: float | None = None,
    capacity_min: float | None = None,
    bedrooms_min: float | None = None,
    pets: str | None = None,
    spa: int | None = None,
    waterfront: int | None = None,
    q: str | None = None,
    sort: str = "recent",
    limit: int = Query(24, le=200),
    offset: int = 0,
):
    if sort not in _SORTS:
        raise HTTPException(400, f"sort inconnu : {sort}")
    con = db.connect()
    sql = "SELECT * FROM st_listings WHERE active=1"
    args: list = []
    sql = _apply_filters(sql, args, region, city, type, source, price_min,
                         price_max, capacity_min, bedrooms_min, pets, spa,
                         waterfront, q)
    total = con.execute(f"SELECT COUNT(*) c FROM ({sql})", args).fetchone()["c"]
    sql += _SORTS[sort] + " 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}


@router.get("/listings.geojson")
def ct_geojson(
    region: str | None = None,
    city: str | None = None,
    type: str | None = None,
    source: str | None = None,
    price_min: float | None = None,
    price_max: float | None = None,
    capacity_min: float | None = None,
    bedrooms_min: float | None = None,
    pets: str | None = None,
    spa: int | None = None,
    waterfront: int | None = None,
    q: str | None = None,
    bbox: str | None = None,
    limit: int = Query(4000, le=10000),
):
    con = db.connect()
    sql = ("SELECT uid, title, property_type, city, region, price_night,"
           " price_label, capacity, bedrooms, rating, source, images, lat, lng"
           " FROM st_listings WHERE active=1"
           " AND lat IS NOT NULL AND lng IS NOT NULL")
    args: list = []
    if bbox:
        try:
            west, south, east, north = (float(v) for v in bbox.split(","))
        except ValueError:
            raise HTTPException(400, "bbox attendu : ouest,sud,est,nord")
        sql += " AND lat BETWEEN ? AND ? AND lng BETWEEN ? AND ?"
        args += [south, north, west, east]
    sql = _apply_filters(sql, args, region, city, type, source, price_min,
                         price_max, capacity_min, bedrooms_min, pets, spa,
                         waterfront, q)
    total_geo = con.execute(f"SELECT COUNT(*) c FROM ({sql})",
                            args).fetchone()["c"]
    sql += " ORDER BY first_seen DESC LIMIT ?"
    args.append(limit)
    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"],
                "property_type": r["property_type"], "city": r["city"],
                "region": r["region"], "price_night": r["price_night"],
                "price_label": r["price_label"], "capacity": r["capacity"],
                "bedrooms": r["bedrooms"], "rating": r["rating"],
                "source": r["source"],
                "image": images[0] if images else None,
            },
        })
    con.close()
    return {"type": "FeatureCollection", "features": features,
            "totalGeocoded": total_geo}


@router.get("/facets")
def ct_facets():
    con = db.connect()
    out = {
        "regions": [dict(r) for r in con.execute(
            "SELECT region, COUNT(*) n FROM st_listings WHERE active=1"
            " AND region<>'' GROUP BY region ORDER BY n DESC")],
        "types": [dict(r) for r in con.execute(
            "SELECT property_type AS type, COUNT(*) n FROM st_listings"
            " WHERE active=1 AND property_type<>''"
            " GROUP BY property_type ORDER BY n DESC")],
        "sources": [dict(r) for r in con.execute(
            "SELECT source, COUNT(*) n FROM st_listings WHERE active=1"
            " GROUP BY source ORDER BY n DESC")],
    }
    con.close()
    return out


@router.get("/stats")
def ct_stats():
    con = db.connect()
    row = con.execute(
        """SELECT COUNT(*) total, COUNT(DISTINCT source) sources,
                  COUNT(DISTINCT region) regions,
                  AVG(price_night) avg_night,
                  SUM(CASE WHEN lat IS NOT NULL THEN 1 ELSE 0 END) geocoded
           FROM st_listings WHERE active=1""").fetchone()
    log = [dict(r) for r in con.execute(
        "SELECT * FROM st_sync_log ORDER BY ts DESC LIMIT 20")]
    con.close()
    return {**dict(row), "recent_syncs": log}


@router.get("/sources")
def ct_sources():
    from .connectors import ST_CONNECTORS
    try:
        registry = json.loads(
            SOURCES_CT_PATH.read_text(encoding="utf-8"))["sources"]
    except (OSError, ValueError, KeyError):
        registry = []
    con = db.connect()
    counts = {r["source"]: r["n"] for r in con.execute(
        "SELECT source, COUNT(*) n FROM st_listings WHERE active=1"
        " GROUP BY source")}
    last = {r["source"]: r["ts"] for r in con.execute(
        "SELECT source, MAX(ts) ts FROM st_sync_log WHERE ok=1"
        " GROUP BY source")}
    con.close()
    for s in registry:
        s["connector"] = s["id"] in ST_CONNECTORS
        s["active_listings"] = counts.get(s["id"], 0)
        s["last_sync"] = last.get(s["id"])
    return {"sources": registry}


def _percentile(sorted_vals: list[float], q: float) -> float:
    if not sorted_vals:
        return 0.0
    pos = (len(sorted_vals) - 1) * q
    lo, hi = int(pos), min(int(pos) + 1, len(sorted_vals) - 1)
    return sorted_vals[lo] + (sorted_vals[hi] - sorted_vals[lo]) * (pos - lo)


def _haversine_km(lat1, lng1, lat2, lng2) -> float:
    rl1, rl2 = math.radians(lat1), math.radians(lat2)
    dlat, dlng = rl2 - rl1, math.radians(lng2 - lng1)
    a = (math.sin(dlat / 2) ** 2
         + math.cos(rl1) * math.cos(rl2) * math.sin(dlng / 2) ** 2)
    return 6371.0 * 2 * math.asin(math.sqrt(a))


@router.get("/listings/{uid}/context")
def ct_listing_context(uid: str):
    """Contexte d'une fiche : analyse du prix/nuit vs segment comparable
    (région → + type → + capacité) et hébergements similaires à proximité."""
    con = db.connect()
    row = con.execute("SELECT * FROM st_listings WHERE uid=?",
                      (uid,)).fetchone()
    if row is None:
        con.close()
        raise HTTPException(404, "Hébergement introuvable")
    l = dict(row)

    # --- analyse de prix : segment le plus précis avec ≥ 12 comparables ------
    price_block = None
    if l["price_night"] is not None and l["region"]:
        candidates: list[tuple[str, str, list]] = []
        base_sql = (" AND region=?")
        base_args: list = [l["region"]]
        if l["property_type"] and l["capacity"]:
            candidates.append((
                f"{l['property_type']} · {int(l['capacity'])}±2 pers. · {l['region']}",
                base_sql + " AND property_type=? AND capacity BETWEEN ? AND ?",
                base_args + [l["property_type"], l["capacity"] - 2,
                             l["capacity"] + 2]))
        if l["property_type"]:
            candidates.append((
                f"{l['property_type']} · {l['region']}",
                base_sql + " AND property_type=?",
                base_args + [l["property_type"]]))
        candidates.append((l["region"], base_sql, base_args))

        for label, extra, args in candidates:
            vals = [r["p"] for r in con.execute(
                "SELECT price_night p FROM st_listings WHERE active=1"
                " AND price_night IS NOT NULL AND uid<>?" + extra
                + " ORDER BY price_night", [uid] + args)]
            if len(vals) < 12:
                continue
            med = _percentile(vals, 0.5)
            deviation = (l["price_night"] - med) / med if med else None
            verdict = None
            if deviation is not None:
                verdict = ("sous" if deviation <= -0.15
                           else "dans" if deviation < 0.12 else "dessus")
            # histogramme 12 classes entre p5 et p95 (queues écrasées)
            lo, hi = _percentile(vals, 0.05), _percentile(vals, 0.95)
            bins = []
            if hi > lo:
                step = (hi - lo) / 12
                edges = [lo + i * step for i in range(13)]
                counts = [0] * 12
                for v in vals:
                    i = min(11, max(0, int((v - lo) / step)))
                    counts[i] += 1
                bins = [{"x0": round(edges[i]), "x1": round(edges[i + 1]),
                         "n": counts[i]} for i in range(12)]
            rank = sum(1 for v in vals if v <= l["price_night"])
            price_block = {
                "segment": label, "n": len(vals),
                "median": round(med), "p25": round(_percentile(vals, 0.25)),
                "p75": round(_percentile(vals, 0.75)),
                "deviation": round(deviation, 3) if deviation is not None else None,
                "verdict": verdict,
                "percentile": round(100 * rank / len(vals)),
                "histogram": bins,
            }
            break

    # --- hébergements similaires ---------------------------------------------
    similar: list[dict] = []
    if l["lat"] is not None and l["lng"] is not None:
        dlat = 0.45   # ≈ 50 km
        dlng = dlat / max(0.2, math.cos(math.radians(l["lat"])))
        rows = con.execute(
            "SELECT * FROM st_listings WHERE active=1 AND uid<>?"
            " AND lat BETWEEN ? AND ? AND lng BETWEEN ? AND ?"
            " AND images IS NOT NULL AND images<>'[]' LIMIT 400",
            (uid, l["lat"] - dlat, l["lat"] + dlat,
             l["lng"] - dlng, l["lng"] + dlng)).fetchall()
        scored = []
        for r in rows:
            km = _haversine_km(l["lat"], l["lng"], r["lat"], r["lng"])
            if km > 50:
                continue
            same_type = (l["property_type"]
                         and r["property_type"] == l["property_type"])
            scored.append((0 if same_type else 1, km, r))
        scored.sort(key=lambda t: (t[0], t[1]))
        for _, km, r in scored[:8]:
            d = _row_to_dict(r)
            d["distance_km"] = round(km, 1)
            similar.append(d)
    if not similar and l["region"]:
        sql = ("SELECT * FROM st_listings WHERE active=1 AND uid<>?"
               " AND region=? AND images IS NOT NULL AND images<>'[]'")
        args = [uid, l["region"]]
        if l["property_type"]:
            sql += " AND property_type=?"
            args.append(l["property_type"])
        sql += " ORDER BY rating IS NULL, rating DESC, first_seen DESC LIMIT 8"
        similar = [_row_to_dict(r) for r in con.execute(sql, args)]

    con.close()
    return {"price": price_block, "similar": similar}


@router.get("/listings/{uid}")
def ct_listing(uid: str):
    con = db.connect()
    row = con.execute("SELECT * FROM st_listings WHERE uid=?",
                      (uid,)).fetchone()
    con.close()
    if row is None:
        raise HTTPException(404, "Hébergement introuvable")
    return _row_to_dict(row)


@router.post("/sync")
def ct_trigger_sync(background: BackgroundTasks, source: str | None = None):
    from . import ingest
    def _job():
        with _sync_lock:
            ingest.run([source] if source else None)
    background.add_task(_job)
    return {"status": "démarré", "source": source or "toutes"}