# -----------------------------------------------------------------------------
# Fabri-Ka — Agrégateur de produits québécois
# Auteur : Simon-Pierre Boucher — contact@spboucher.ai
# web.py : API FastAPI + service du frontend React (dist/)
# -----------------------------------------------------------------------------
from __future__ import annotations

import json
import sqlite3
import threading
from datetime import date
from pathlib import Path

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

from . import db
from .schema import CATEGORIES, REGIONS

app = FastAPI(title="Fabri-Ka API", docs_url="/api/docs", openapi_url="/api/openapi.json")

FRONT_DIST = Path(__file__).resolve().parent.parent / "frontend" / "dist"


def q(con: sqlite3.Connection, sql: str, args=()) -> list[dict]:
    return [dict(r) for r in con.execute(sql, args)]


def _product_out(r: dict) -> dict:
    r["images"] = json.loads(r.get("images") or "[]")
    r["tags"] = json.loads(r.get("tags") or "[]")
    return r


_FTS_SAFE = set("abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789àâäéèêëîïôöùûüçÀÂÄÉÈÊËÎÏÔÖÙÛÜÇ- '")


def fts_query(text: str) -> str:
    words = "".join(c if c in _FTS_SAFE else " " for c in text).split()
    return " ".join(f'"{w}"*' for w in words[:8])


@app.get("/api/products")
def products(q_text: str | None = Query(None, alias="q"),
             category: str | None = None,
             region: str | None = None,
             store: str | None = None,
             origin: str | None = None,
             price_min: float | None = None,
             price_max: float | None = None,
             available: bool | None = None,
             sort: str = "recent",
             page: int = 1,
             per_page: int = Query(24, le=100)):
    con = db.connect()
    try:
        where, args = ["p.active=1"], []
        joins = "FROM products p JOIN stores s ON s.id = p.store_id"
        if q_text:
            joins += " JOIN products_fts f ON f.uid = p.uid"
            where.append("products_fts MATCH ?")
            args.append(fts_query(q_text))
        if category:
            where.append("p.category=?"); args.append(category)
        if region:
            where.append("s.region=?"); args.append(region)
        if store:
            where.append("p.store_id=?"); args.append(store)
        if origin:
            where.append("s.origin_class IN (%s)" % ",".join("?" * len(origin.split(","))))
            args += origin.split(",")
        if price_min is not None:
            where.append("p.price>=?"); args.append(price_min)
        if price_max is not None:
            where.append("p.price<=?"); args.append(price_max)
        if available is not None:
            where.append("p.available=?"); args.append(int(available))
        wsql = " AND ".join(where)
        order = {"recent": "p.first_seen DESC",
                 "price_asc": "p.price IS NULL, p.price ASC",
                 "price_desc": "p.price IS NULL, p.price DESC",
                 "title": "p.title COLLATE NOCASE"}.get(sort, "p.first_seen DESC")
        total = con.execute(f"SELECT COUNT(*) {joins} WHERE {wsql}", args).fetchone()[0]
        rows = q(con, f"""SELECT p.*, s.name AS store_name, s.region AS store_region,
                                 s.city AS store_city, s.origin_class
                          {joins} WHERE {wsql} ORDER BY {order}
                          LIMIT ? OFFSET ?""", args + [per_page, (page - 1) * per_page])
        return {"total": total, "page": page, "per_page": per_page,
                "items": [_product_out(r) for r in rows]}
    finally:
        con.close()


@app.get("/api/products/{uid}")
def product(uid: str):
    con = db.connect()
    try:
        rows = q(con, """SELECT p.*, s.name AS store_name, s.region AS store_region,
                                s.city AS store_city, s.origin_class, s.url AS store_url
                         FROM products p JOIN stores s ON s.id=p.store_id WHERE p.uid=?""", (uid,))
        if not rows:
            raise HTTPException(404)
        out = _product_out(rows[0])
        out["related"] = [_product_out(r) for r in q(con, """
            SELECT p.*, s.name AS store_name FROM products p JOIN stores s ON s.id=p.store_id
            WHERE p.store_id=? AND p.uid<>? AND p.active=1 ORDER BY RANDOM() LIMIT 8""",
            (rows[0]["store_id"], uid))]
        return out
    finally:
        con.close()


@app.get("/api/stores")
def stores(region: str | None = None, platform: str | None = None,
           origin: str | None = None, q_text: str | None = Query(None, alias="q"),
           with_products: bool = False):
    con = db.connect()
    try:
        where, args = ["1=1"], []
        if region:
            where.append("region=?"); args.append(region)
        if platform:
            where.append("platform=?"); args.append(platform)
        if origin:
            where.append("origin_class=?"); args.append(origin)
        if q_text:
            where.append("(name LIKE ? OR id LIKE ?)"); args += [f"%{q_text}%", f"%{q_text}%"]
        if with_products:
            where.append("product_count>0")
        rows = q(con, f"""SELECT id, name, url, platform, city, region, origin_class,
                                 origin_confidence, categories, language, product_count,
                                 last_sync, last_status, logo_url, cover_url,
                                 description_meta
                          FROM stores WHERE {' AND '.join(where)}
                          ORDER BY product_count DESC, name""", args)
        for r in rows:
            r["categories"] = json.loads(r.get("categories") or "[]")
        return {"total": len(rows), "items": rows}
    finally:
        con.close()


@app.get("/api/stores/{store_id}")
def store_detail(store_id: str):
    con = db.connect()
    try:
        rows = q(con, "SELECT * FROM stores WHERE id=?", (store_id,))
        if not rows:
            raise HTTPException(404)
        s = rows[0]
        for k in ("categories", "socials", "discovery_sources"):
            s[k] = json.loads(s.get(k) or "[]")
        # statistiques produits
        stats = q(con, """SELECT COUNT(*) AS n, MIN(price) AS price_min,
                                 MAX(price) AS price_max, AVG(price) AS price_avg
                          FROM products WHERE store_id=? AND active=1 AND price>0""",
                  (store_id,))
        s["product_stats"] = stats[0] if stats else {}
        s["category_breakdown"] = q(con, """SELECT category AS key, COUNT(*) AS n
            FROM products WHERE store_id=? AND active=1 GROUP BY category
            ORDER BY n DESC LIMIT 8""", (store_id,))
        for c in s["category_breakdown"]:
            c["label"] = CATEGORIES.get(c["key"], (c["key"], []))[0]
        # boutiques similaires : même catégorie dominante ou même région, avec produits
        topcat = s["category_breakdown"][0]["key"] if s["category_breakdown"] else None
        s["similar"] = q(con, """
            SELECT DISTINCT st.id, st.name, st.region, st.origin_class, st.logo_url,
                   st.product_count
            FROM stores st
            LEFT JOIN products p ON p.store_id=st.id AND p.active=1 AND p.category=?
            WHERE st.id<>? AND st.product_count>0
              AND (p.uid IS NOT NULL OR (st.region<>'' AND st.region=?))
            ORDER BY (st.region=?) DESC, st.product_count DESC LIMIT 8""",
            (topcat, store_id, s.get("region") or "", s.get("region") or ""))
        return s
    finally:
        con.close()


@app.get("/api/facets")
def facets():
    con = db.connect()
    try:
        cats = q(con, """SELECT p.category AS key, COUNT(*) AS n FROM products p
                         WHERE p.active=1 GROUP BY p.category ORDER BY n DESC""")
        for c in cats:
            c["label"] = CATEGORIES.get(c["key"], (c["key"], []))[0]
        regions = q(con, """SELECT s.region AS key, COUNT(*) AS n FROM products p
                            JOIN stores s ON s.id=p.store_id
                            WHERE p.active=1 AND s.region<>'' GROUP BY s.region ORDER BY n DESC""")
        origins = q(con, """SELECT s.origin_class AS key, COUNT(*) AS n FROM products p
                            JOIN stores s ON s.id=p.store_id WHERE p.active=1
                            GROUP BY s.origin_class ORDER BY n DESC""")
        top_stores = q(con, """SELECT id AS key, name, product_count AS n FROM stores
                               WHERE product_count>0 ORDER BY n DESC LIMIT 40""")
        return {"categories": cats, "regions": regions, "origins": origins,
                "stores": top_stores, "all_regions": REGIONS}
    finally:
        con.close()


_stats_cache: dict = {"ts": 0.0, "data": None}


@app.get("/api/stats/extended")
def stats_extended():
    """Statistiques riches pour la page /stats : prix, catégories, croissance.
    Coûteux (médianes) → cache mémoire 10 minutes."""
    import time as _time
    if _stats_cache["data"] is not None and _time.time() - _stats_cache["ts"] < 600:
        return _stats_cache["data"]
    con = db.connect()
    try:
        def median_price(where_sql, args):
            n = con.execute(f"SELECT COUNT(*) FROM products WHERE {where_sql} AND price>0", args).fetchone()[0]
            if not n:
                return None
            row = con.execute(f"""SELECT price FROM products WHERE {where_sql} AND price>0
                                  ORDER BY price LIMIT 1 OFFSET ?""", args + [n // 2]).fetchone()
            return row[0] if row else None

        by_category = []
        for r in q(con, """SELECT category AS key, COUNT(*) AS products,
                                  COUNT(DISTINCT store_id) AS stores,
                                  MIN(CASE WHEN price>0 THEN price END) AS price_min,
                                  ROUND(AVG(CASE WHEN price>0 AND price<=500000 THEN price END),2) AS price_avg,
                                  MAX(CASE WHEN price<=500000 THEN price END) AS price_max
                           FROM products WHERE active=1 GROUP BY category
                           ORDER BY products DESC"""):
            r["label"] = CATEGORIES.get(r["key"], (r["key"], []))[0]
            r["price_median"] = median_price("active=1 AND category=?", [r["key"]])
            by_category.append(r)

        global_median = median_price("active=1", [])
        price_buckets = q(con, """SELECT CASE
                WHEN price < 10 THEN '0-10'
                WHEN price < 25 THEN '10-25'
                WHEN price < 50 THEN '25-50'
                WHEN price < 100 THEN '50-100'
                WHEN price < 250 THEN '100-250'
                WHEN price < 1000 THEN '250-1000'
                ELSE '1000+' END AS bucket, COUNT(*) AS n
            FROM products WHERE active=1 AND price>0 GROUP BY bucket""")
        order = ['0-10', '10-25', '25-50', '50-100', '100-250', '250-1000', '1000+']
        price_buckets.sort(key=lambda b: order.index(b["bucket"]))

        by_origin = q(con, """SELECT s.origin_class AS key, COUNT(DISTINCT s.id) AS stores,
                                     COUNT(p.uid) AS products
                              FROM stores s LEFT JOIN products p
                                   ON p.store_id=s.id AND p.active=1
                              GROUP BY s.origin_class ORDER BY products DESC""")
        by_platform = q(con, """SELECT platform AS key, COUNT(*) AS stores,
                                       SUM(product_count) AS products
                                FROM stores WHERE platform<>'' GROUP BY platform
                                ORDER BY products DESC""")
        by_region = q(con, """SELECT s.region AS key, COUNT(DISTINCT s.id) AS stores,
                                     COUNT(p.uid) AS products,
                                     ROUND(AVG(CASE WHEN p.price>0 AND p.price<=500000 THEN p.price END),2) AS price_avg
                              FROM stores s LEFT JOIN products p
                                   ON p.store_id=s.id AND p.active=1
                              WHERE s.region<>'' GROUP BY s.region ORDER BY products DESC""")
        growth = q(con, """SELECT date(first_seen,'unixepoch') AS day, COUNT(*) AS n
                           FROM products WHERE active=1
                           GROUP BY day ORDER BY day DESC LIMIT 30""")
        top_stores = q(con, """SELECT id, name, region, origin_class, logo_url,
                                      product_count AS products
                               FROM stores WHERE product_count>0
                               ORDER BY product_count DESC LIMIT 15""")
        most_expensive = q(con, """SELECT p.uid, p.title, p.price, s.name AS store_name
                                   FROM products p JOIN stores s ON s.id=p.store_id
                                   WHERE p.active=1 AND p.price>0
                                   ORDER BY p.price DESC LIMIT 8""")
        newest = q(con, """SELECT p.uid, p.title, p.price, p.category, s.name AS store_name
                           FROM products p JOIN stores s ON s.id=p.store_id
                           WHERE p.active=1 ORDER BY p.first_seen DESC LIMIT 12""")
        for r in newest:
            r["category_label"] = CATEGORIES.get(r["category"], (r["category"], []))[0]
        availability = q(con, """SELECT CASE WHEN available=1 THEN 'en stock'
                                             WHEN available=0 THEN 'rupture'
                                             ELSE 'inconnu' END AS key, COUNT(*) AS n
                                 FROM products WHERE active=1 GROUP BY key""")
        coverage = q(con, """SELECT
            (SELECT COUNT(*) FROM products WHERE active=1 AND images<>'[]' AND images IS NOT NULL) AS with_image,
            (SELECT COUNT(*) FROM products WHERE active=1 AND description<>'') AS with_desc,
            (SELECT COUNT(*) FROM stores WHERE logo_url IS NOT NULL AND logo_url<>'') AS with_logo,
            (SELECT COUNT(*) FROM stores WHERE region<>'') AS with_region
        """)[0]
        totals = q(con, """SELECT
            (SELECT COUNT(*) FROM products WHERE active=1) AS products,
            (SELECT COUNT(*) FROM products WHERE active=1 AND price>0) AS products_priced,
            (SELECT COUNT(*) FROM stores WHERE product_count>0) AS stores_live,
            (SELECT COUNT(*) FROM stores) AS stores_registry,
            (SELECT COUNT(DISTINCT region) FROM stores WHERE region<>'') AS regions,
            (SELECT ROUND(AVG(price),2) FROM products WHERE active=1 AND price>0 AND price<=500000) AS price_avg
        """)[0]
        totals["price_median"] = global_median
        data = {"totals": totals, "by_category": by_category,
                "price_buckets": price_buckets, "by_origin": by_origin,
                "by_platform": by_platform, "by_region": by_region,
                "growth": growth, "top_stores": top_stores,
                "most_expensive": most_expensive, "newest": newest,
                "availability": availability, "coverage": coverage}
        _stats_cache.update(ts=_time.time(), data=data)
        return data
    finally:
        con.close()


@app.get("/api/report.pdf")
def report_pdf():
    """Rapport de marché Fabri-Ka en PDF (logo, KPIs, prix, catégories,
    régions, origine, plateformes, top boutiques)."""
    from fastapi.responses import Response
    from . import report
    stats = stats_extended()
    pdf = report.build_report(stats)
    fname = f"rapport-marche-fabri-ka-{date.today()}.pdf"
    return Response(content=pdf, media_type="application/pdf",
                    headers={"Content-Disposition": f'attachment; filename="{fname}"'})


@app.get("/api/stats")
def stats():
    con = db.connect()
    try:
        totals = q(con, """SELECT
            (SELECT COUNT(*) FROM products WHERE active=1) AS products,
            (SELECT COUNT(*) FROM stores WHERE product_count>0) AS stores_live,
            (SELECT COUNT(*) FROM stores) AS stores_registry,
            (SELECT COUNT(DISTINCT region) FROM stores WHERE region<>'' AND product_count>0) AS regions
        """)[0]
        by_region = q(con, """SELECT region, COUNT(*) AS stores, SUM(product_count) AS products
                              FROM stores WHERE region<>'' GROUP BY region ORDER BY products DESC""")
        recent = q(con, """SELECT ts, store_id, found, added, updated, removed, status
                           FROM sync_log ORDER BY ts DESC LIMIT 30""")
        return {"totals": totals, "by_region": by_region, "sync_log": recent}
    finally:
        con.close()


_sync_running = threading.Lock()


@app.post("/api/sync")
def trigger_sync(background: BackgroundTasks):
    if _sync_running.locked():
        return {"status": "already-running"}

    def _job():
        with _sync_running:
            from . import ingest
            ingest.run()

    background.add_task(_job)
    return {"status": "started"}


# ---- frontend ---------------------------------------------------------------
if FRONT_DIST.exists():
    app.mount("/assets", StaticFiles(directory=FRONT_DIST / "assets"), name="assets")

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