# -----------------------------------------------------------------------------
# Forma-Ka — Agrégateur de formations (province de Québec)
# Auteur : Simon-Pierre Boucher — contact@spboucher.ai
# web.py : API FastAPI (JSON) + service du frontend React (frontend/dist)
# -----------------------------------------------------------------------------
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_DIST = ROOT / "frontend" / "dist"

app = FastAPI(title="Forma-Ka API", version="1.0",
              description="Agrégateur de formations — province de Québec")
app.add_middleware(CORSMiddleware, allow_origins=["*"],
                   allow_methods=["*"], allow_headers=["*"])

_sync_lock = threading.Lock()

_JSON_COLS = ("sessions", "objectives", "program", "tags", "details", "images")


def _row_to_dict(row) -> dict:
    d = dict(row)
    for col in _JSON_COLS:
        default = "{}" if col == "details" else "[]"
        d[col] = json.loads(d.get(col) or default)
    if d.get("is_free") is not None:
        d["is_free"] = bool(d["is_free"])
    return d


def _apply_filters(sql: str, args: list,
                   training_type: str | None, category: str | None,
                   mode: str | None, city: str | None, language: str | None,
                   level: str | None, source: str | None,
                   free: int | None, price_max: float | None,
                   credential: str | None, starts_after: str | None,
                   q: str | None) -> str:
    if training_type:
        sql += " AND training_type=?"; args.append(training_type)
    if category:
        sql += " AND category LIKE ?"; args.append(f"%{category}%")
    if mode:
        sql += " AND mode=?"; args.append(mode)
    if city:
        sql += " AND city LIKE ?"; args.append(f"%{city}%")
    if language:
        sql += " AND language=?"; args.append(language)
    if level:
        sql += " AND level=?"; args.append(level)
    if source:
        sql += " AND source=?"; args.append(source)
    if free == 1:
        sql += " AND is_free=1"
    elif free == 0:
        sql += " AND is_free=0"
    if price_max is not None:
        sql += " AND price IS NOT NULL AND price<=?"; args.append(price_max)
    if credential:
        sql += " AND credential LIKE ?"; args.append(f"%{credential}%")
    if starts_after:
        sql += " AND start_date IS NOT NULL AND start_date>=?"
        args.append(starts_after)
    if q:
        sql += (" AND (title LIKE ? OR description LIKE ? OR category LIKE ?"
                " OR code LIKE ? OR tags LIKE ?)")
        args += [f"%{q}%"] * 5
    return sql


@app.get("/api/formations")
def list_formations(
    training_type: str | None = None,   # Cours universitaire, Séminaire…
    category: str | None = None,        # domaine (LIKE)
    mode: str | None = None,            # en ligne | présentiel | hybride | asynchrone
    city: str | None = None,
    language: str | None = None,        # fr | en | fr/en
    level: str | None = None,           # débutant | intermédiaire | avancé
    source: str | None = None,
    free: int | None = None,            # 1 = gratuites seulement
    price_max: float | None = None,
    credential: str | None = None,      # attestation, UEC, certificat…
    starts_after: str | None = None,    # ISO : prochaine séance à partir de
    q: str | None = None,
    active: int = 1,
    sort: str = "recent",               # recent | price | title | start
    limit: int = Query(500, le=2000),
    offset: int = 0,
):
    con = db.connect()
    sql = "SELECT * FROM formations WHERE 1=1"
    args: list = []
    if active in (0, 1):
        sql += " AND active=?"; args.append(active)
    sql = _apply_filters(sql, args, training_type, category, mode, city,
                         language, level, source, free, price_max,
                         credential, starts_after, q)
    total = con.execute(f"SELECT COUNT(*) c FROM ({sql})", args).fetchone()["c"]
    order = {
        "price": " ORDER BY price IS NULL, price ASC",
        "title": " ORDER BY title COLLATE NOCASE ASC",
        "start": " ORDER BY start_date IS NULL, start_date ASC",
        "recent": " ORDER BY first_seen DESC",
    }.get(sort, " ORDER BY first_seen DESC")
    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), "formations": rows}


@app.get("/api/formations/{uid:path}")
def get_formation(uid: str):
    con = db.connect()
    row = con.execute("SELECT * FROM formations WHERE uid=?", (uid,)).fetchone()
    d = None
    if row is not None:
        d = _row_to_dict(row)
        d["price_history"] = [dict(r) for r in con.execute(
            "SELECT ts, price FROM price_log WHERE uid=? ORDER BY ts DESC LIMIT 6",
            (uid,)).fetchall()]
        # formations similaires : même source ou même catégorie
        d["similar"] = [
            {"uid": r["uid"], "title": r["title"], "training_type": r["training_type"],
             "source": r["source"], "price": r["price"], "mode": r["mode"],
             "duration": r["duration"]}
            for r in con.execute(
                "SELECT uid, title, training_type, source, price, mode, duration"
                " FROM formations WHERE active=1 AND uid<>? AND"
                " (category=? OR source=?) ORDER BY RANDOM() LIMIT 6",
                (uid, d.get("category") or "-", d["source"])).fetchall()]
    con.close()
    if d is None:
        raise HTTPException(404, "Formation introuvable")
    return d


@app.get("/api/facets")
def facets(training_type: str | None = None):
    """Valeurs distinctes pour construire les filtres du frontend."""
    con = db.connect()
    cat_sql = "SELECT category, COUNT(*) n FROM formations WHERE active=1 AND category<>''"
    cat_args: list = []
    if training_type:
        cat_sql += " AND training_type=?"
        cat_args.append(training_type)
    out = {
        "types": [dict(r) for r in con.execute(
            "SELECT training_type t, COUNT(*) n FROM formations"
            " WHERE active=1 AND training_type<>'' GROUP BY training_type ORDER BY n DESC")],
        "categories": [dict(r) for r in con.execute(
            cat_sql + " GROUP BY category ORDER BY n DESC LIMIT 60", cat_args)],
        "modes": [r["mode"] for r in con.execute(
            "SELECT DISTINCT mode FROM formations WHERE active=1 AND mode<>'' ORDER BY mode")],
        "cities": [r["city"] for r in con.execute(
            "SELECT DISTINCT city FROM formations WHERE active=1 AND city<>'' ORDER BY city")],
        "languages": [r["language"] for r in con.execute(
            "SELECT DISTINCT language FROM formations WHERE active=1 AND language<>'' ORDER BY language")],
        "levels": [r["level"] for r in con.execute(
            "SELECT DISTINCT level FROM formations WHERE active=1 AND level<>'' ORDER BY level")],
        "sources": [dict(r) for r in con.execute(
            "SELECT source, COUNT(*) n FROM formations WHERE active=1"
            " 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 formations 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_formations"] = counts.get(s["id"], 0)
        s["last_sync"] = last.get(s["id"])
    return {"sources": registry}


@app.get("/api/stats")
def stats():
    con = db.connect()
    row = con.execute(
        """SELECT COUNT(*) total,
                  SUM(CASE WHEN is_free=1 THEN 1 ELSE 0 END) gratuites,
                  SUM(CASE WHEN mode='en ligne' OR mode='asynchrone'
                      THEN 1 ELSE 0 END) en_ligne,
                  SUM(CASE WHEN training_type='Cours universitaire'
                      THEN 1 ELSE 0 END) universitaires,
                  COUNT(DISTINCT source) sources,
                  AVG(price) avg_price,
                  AVG(duration_hours) avg_hours
           FROM formations WHERE active=1""").fetchone()
    par_type = [dict(r) for r in con.execute(
        "SELECT training_type t, COUNT(*) n FROM formations WHERE active=1"
        " GROUP BY training_type ORDER BY n DESC")]
    log = [dict(r) for r in con.execute(
        "SELECT * FROM sync_log ORDER BY ts DESC LIMIT 20")]
    con.close()
    return {**dict(row), "par_type": par_type, "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 React (build Vite) --------------------------------------------
if FRONTEND_DIST.exists():
    app.mount("/assets", StaticFiles(directory=FRONTEND_DIST / "assets"), name="assets")

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