# Author: Simon-Pierre Boucher
# Contact: contact@spboucher.ai
# Project: Toit-Ka
# -----------------------------------------------------------------------------
# web.py : API FastAPI (JSON) + service du frontend + rendu SEO serveur.
#   Toit-Ka lit UNIQUEMENT toitka.db (construite par l'ETL depuis les répliques
#   Lou-Ka / Immo-Ka) — le paramètre `tx` (louer | acheter) filtre l'univers.
# -----------------------------------------------------------------------------
from __future__ import annotations

import json
import time
from pathlib import Path

from fastapi import FastAPI, HTTPException, Query, Request
from fastapi.middleware.cors import CORSMiddleware
from fastapi.middleware.gzip import GZipMiddleware
from fastapi.responses import FileResponse
from fastapi.staticfiles import StaticFiles

from . import auth, db, favorites, seo

ROOT = Path(__file__).resolve().parent.parent
FRONTEND_DIST = ROOT / "frontend" / "dist"
FRONTEND_DIR = FRONTEND_DIST if FRONTEND_DIST.exists() else ROOT / "frontend"

app = FastAPI(title="Toit-Ka API", version="1.0",
              description="Louer ou acheter un toit au Québec — un seul endroit")
app.add_middleware(CORSMiddleware, allow_origins=["*"],
                   allow_methods=["*"], allow_headers=["*"])
app.add_middleware(GZipMiddleware, minimum_size=1024)
app.include_router(auth.router)
app.include_router(favorites.router)

VISIBLE = "active=1"
TX = ("louer", "acheter")


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


def _filters_sql(tx: str | None, city: str | None, sector: str | None,
                 type_: str | None, source: str | None,
                 price_min: float | None, price_max: float | None,
                 bedrooms_min: int | None, bathrooms_min: int | None,
                 area_min: float | None, pets: str | None,
                 furnished: int | None, q: str | None) -> tuple[str, list]:
    sql, args = f" AND {VISIBLE}", []
    if tx in TX:
        sql += " AND transaction_type=?"; args.append(tx)
    if city:
        sql += " AND city=?"; args.append(city)
    if sector:
        sql += " AND sector LIKE ?"; args.append(f"%{sector}%")
    if type_:
        sql += " AND type=?"; args.append(type_)
    if source:
        sql += " AND source=?"; args.append(source)
    if price_min is not None:
        sql += " AND price IS NOT NULL AND price>=?"; args.append(price_min)
    if price_max is not None:
        sql += " AND price IS NOT NULL AND price<=?"; args.append(price_max)
    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 pets:
        sql += " AND pets IN ('oui','conditions')" if pets == "oui" else " AND pets=?"
        if pets != "oui":
            args.append(pets)
    if furnished is not None:
        sql += " AND furnished=?"; args.append(furnished)
    if q:
        sql += " AND (title LIKE ? OR address LIKE ? OR city LIKE ? OR sector LIKE ? OR mls LIKE ?)"
        args += [f"%{q}%"] * 5
    return sql, args


@app.get("/api/listings")
def list_listings(
    tx: str | None = None,
    city: str | None = None,
    sector: str | None = None,
    type: str | None = None,
    source: str | None = None,
    price_min: float | None = None,
    price_max: float | None = None,
    bedrooms_min: int | None = None,
    bathrooms_min: int | None = None,
    area_min: float | None = None,
    pets: str | None = None,
    furnished: int | None = None,
    q: str | None = None,
    sort: str = "recent",
    limit: int = Query(60, le=2000),
    offset: int = 0,
):
    where, args = _filters_sql(tx, city, sector, type, source, price_min,
                               price_max, bedrooms_min, bathrooms_min,
                               area_min, pets, furnished, q)
    con = db.connect()
    try:
        sql = "SELECT * FROM listings WHERE 1=1" + where
        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 first_seen DESC")
        rows = [_row_to_dict(r) for r in con.execute(
            sql + order + " LIMIT ? OFFSET ?", args + [limit, offset])]
    finally:
        con.close()
    return {"total": total, "count": len(rows), "listings": rows}


@app.get("/api/listings/{uid:path}")
def get_listing(uid: str):
    con = db.connect()
    try:
        row = con.execute("SELECT * FROM listings WHERE uid=?", (uid,)).fetchone()
    finally:
        con.close()
    if row is None:
        raise HTTPException(404, "Annonce introuvable")
    return _row_to_dict(row)


@app.get("/api/listings.geojson")
def listings_geojson(
    tx: str | None = None,
    city: str | None = None,
    sector: str | None = None,
    type: str | None = None,
    source: str | None = None,
    price_min: float | None = None,
    price_max: float | None = None,
    bedrooms_min: int | None = None,
    bathrooms_min: int | None = None,
    area_min: float | None = None,
    pets: str | None = None,
    furnished: int | None = None,
    q: str | None = None,
    bbox: str | None = None,
    limit: int = Query(3000, le=8000),
):
    """Marqueurs de carte (Ka Maps) — mêmes filtres que /api/listings + bbox."""
    where, args = _filters_sql(tx, city, sector, type, source, price_min,
                               price_max, bedrooms_min, bathrooms_min,
                               area_min, pets, furnished, q)
    sql = ("SELECT uid, transaction_type, title, address, price, price_label,"
           " type, bedrooms, bathrooms, source, city, sector, images, lat, lng"
           " FROM listings WHERE lat IS NOT NULL AND lng IS NOT NULL" + where)
    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]

    con = db.connect()
    try:
        total_geo = con.execute(f"SELECT COUNT(*) c FROM ({sql})", args).fetchone()["c"]
        sql_all = sql.replace(" WHERE lat IS NOT NULL AND lng IS NOT NULL", " WHERE 1=1")
        args_all = list(args)
        if bbox:
            sql_all = sql_all.replace(" AND lat BETWEEN ? AND ? AND lng BETWEEN ? AND ?", "")
            del args_all[-4:]
        total_all = con.execute(f"SELECT COUNT(*) c FROM ({sql_all})", args_all).fetchone()["c"]

        features = []
        for r in con.execute(sql + " ORDER BY price IS NULL, price ASC LIMIT ?",
                             args + [limit]):
            images = json.loads(r["images"] or "[]")
            features.append({
                "type": "Feature",
                "geometry": {"type": "Point", "coordinates": [r["lng"], r["lat"]]},
                "properties": {
                    "uid": r["uid"], "tx": r["transaction_type"],
                    "title": None if r["address"] else r["title"],
                    "address": r["address"],
                    "price": r["price"], "price_label": r["price_label"],
                    "type": r["type"], "bedrooms": r["bedrooms"],
                    "bathrooms": r["bathrooms"], "source": r["source"],
                    "city": r["city"], "sector": r["sector"],
                    "image": images[0] if images else None,
                },
            })
    finally:
        con.close()
    return {"type": "FeatureCollection", "features": features,
            "totalGeocoded": total_geo, "totalMatching": total_all}


@app.get("/api/facets")
def facets(tx: str | None = None, city: str | None = None):
    """Valeurs distinctes pour les filtres du frontend, par univers (tx)."""
    con = db.connect()
    txw, txa = ("", [])
    if tx in TX:
        txw, txa = " AND transaction_type=?", [tx]
    try:
        sector_sql = (f"SELECT DISTINCT sector FROM listings WHERE {VISIBLE}"
                      " AND sector<>''") + txw
        sector_args = list(txa)
        if city:
            sector_sql += " AND city=?"
            sector_args.append(city)
        out = {
            "cities": [dict(r) for r in con.execute(
                f"SELECT city, COUNT(*) n FROM listings WHERE {VISIBLE}"
                " AND city<>''" + txw + " GROUP BY city ORDER BY n DESC", txa)],
            "sectors": [r["sector"] for r in con.execute(
                sector_sql + " ORDER BY sector", sector_args)],
            "types": [dict(r) for r in con.execute(
                f"SELECT type, COUNT(*) n FROM listings WHERE {VISIBLE}"
                " AND type<>''" + txw + " GROUP BY type ORDER BY n DESC", txa)],
            "sources": [dict(r) for r in con.execute(
                f"SELECT source, COUNT(*) n FROM listings WHERE {VISIBLE}"
                + txw + " GROUP BY source ORDER BY n DESC", txa)],
        }
    finally:
        con.close()
    return out


@app.get("/api/stats")
def stats():
    """Statistiques des deux univers (louer / acheter) + top villes."""
    con = db.connect()
    try:
        out: dict = {}
        for tx in TX:
            row = con.execute(
                f"""SELECT COUNT(*) total, COUNT(DISTINCT source) sources,
                           COUNT(DISTINCT NULLIF(city,'')) cities,
                           AVG(price) avg_price, MIN(price) min_price,
                           MAX(price) max_price
                    FROM listings WHERE {VISIBLE} AND transaction_type=?""",
                (tx,)).fetchone()
            top = [dict(r) for r in con.execute(
                f"""SELECT city, COUNT(*) n, ROUND(AVG(price)) avg_price
                    FROM listings WHERE {VISIBLE} AND transaction_type=?
                      AND city<>'' AND price IS NOT NULL
                    GROUP BY city ORDER BY n DESC LIMIT 15""", (tx,))]
            types = [dict(r) for r in con.execute(
                f"""SELECT type, COUNT(*) n, ROUND(AVG(price)) avg_price
                    FROM listings WHERE {VISIBLE} AND transaction_type=?
                      AND type<>'' AND price IS NOT NULL
                    GROUP BY type ORDER BY n DESC LIMIT 15""", (tx,))]
            out[tx] = {**dict(row), "top_cities": top, "top_types": types}
        out["etl"] = [dict(r) for r in con.execute(
            "SELECT ts, origin, found, ok, message FROM etl_log"
            " ORDER BY ts DESC LIMIT 10")]
    finally:
        con.close()
    return out


@app.get("/api/health")
def health():
    con = db.connect()
    try:
        n = con.execute(f"SELECT COUNT(*) c FROM listings WHERE {VISIBLE}").fetchone()["c"]
        last = con.execute("SELECT MAX(ts) m FROM etl_log WHERE ok=1").fetchone()["m"]
    finally:
        con.close()
    return {"ok": n > 0, "listings": n, "last_etl": last, "ts": time.time()}


@app.get("/api/seo/resolve")
def seo_resolve(tx: str | None = None, ville: str | None = None,
                type: str | None = None):
    """Slug d'URL programmatique -> valeurs exactes (pages /louer /acheter du SPA)."""
    out = seo.resolve_slugs(tx, ville, type)
    if out is None:
        raise HTTPException(404, "Slug inconnu")
    return out


# --- Référencement : robots + sitemaps (voir toitka/seo.py) --------------------
@app.get("/robots.txt")
def robots():
    return seo.robots_txt()


@app.get("/sitemap.xml")
def sitemap():
    return seo.sitemap_index()


@app.get("/sitemaps/{name}")
def sitemap_part(name: str):
    return seo.sitemap_file(name)


# --- Frontend statique + SSR SEO ------------------------------------------------
if FRONTEND_DIR.exists():

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

    @app.middleware("http")
    async def _cache_headers(request: Request, call_next):
        resp = await call_next(request)
        if request.url.path.startswith("/assets/"):
            resp.headers["Cache-Control"] = "public, max-age=31536000, immutable"
        elif resp.headers.get("content-type", "").startswith("text/html"):
            resp.headers["Cache-Control"] = "no-cache"
        return resp

    @app.get("/{full_path:path}")
    def spa(full_path: str, request: Request):
        target = FRONTEND_DIR / full_path
        if full_path and ".." not in full_path and target.is_file():
            return FileResponse(target)
        # rendu SEO serveur : HTML complet par route (meta, JSON-LD, contenu
        # dans #root que React remplace au montage). Ne doit JAMAIS casser
        # l'app -> repli sur l'index brut à la moindre erreur.
        try:
            return seo.render_for_path("/" + full_path, dict(request.query_params))
        except Exception:
            return FileResponse(FRONTEND_DIR / "index.html")