"""PDB API — explorateur web + API REST de la base SPID (SEC Project Intelligence Database).

- /v1/*   : API publique, clé requise (en-tête `X-API-Key` ou `Authorization: Bearer …`).
- /app/*  : mêmes ressources pour l'interface web (même origine, sans clé — la clé n'est jamais envoyée au navigateur).
- /       : interface web (SPA statique dans web/).
- /docs   : documentation OpenAPI interactive.
"""
from __future__ import annotations

import csv
import io
import os
import threading
import time
from collections import defaultdict, deque
from pathlib import Path
from typing import Optional

from fastapi import APIRouter, Depends, FastAPI, HTTPException, Query, Request
from fastapi.responses import FileResponse, JSONResponse, StreamingResponse
from fastapi.staticfiles import StaticFiles
from pydantic import BaseModel, Field

from . import db

ROOT = Path(__file__).resolve().parent.parent
WEB = ROOT / "web"
API_KEY = os.environ.get("API_KEY", "").strip()
PUBLIC_URL = os.environ.get("PUBLIC_URL", "https://www.pdb-api.co").rstrip("/")
RATE_PER_MIN = int(os.environ.get("RATE_PER_MIN", "240"))

TYPE_LABELS = {
    "ma_integration": "Intégration M&A", "plant_construction": "Construction d'usine", "rd_program": "Programme de R&D",
    "product_launch": "Lancement de produit", "manufacturing_expansion": "Expansion manufacturière", "partnership": "Partenariat",
    "technology_deployment": "Déploiement technologique", "digital_transformation": "Transformation numérique",
    "infrastructure": "Infrastructure", "capex_program": "Programme de capex", "ai_initiative": "Initiative IA",
    "geographic_expansion": "Expansion géographique", "cost_reduction": "Réduction des coûts", "sustainability": "Durabilité",
    "energy_transition": "Transition énergétique", "supply_chain": "Chaîne d'approvisionnement", "drug_pipeline": "Pipeline de médicaments",
    "data_center": "Centre de données", "cloud_migration": "Migration infonuagique", "automation": "Automatisation",
    "erp_implementation": "Implantation ERP",
}
STATUSES = ["planned", "in_progress", "completed", "mentioned"]
PROJECT_SORTS = {
    "amount": "coalesce(total_amount_usd,0)", "mentions": "n_mentions", "filings": "n_filings", "first_seen": "first_seen",
    "last_seen": "last_seen", "confidence": "avg_confidence", "name": "project_name", "ticker": "ticker", "type": "project_type",
}
MENTION_SORTS = {"date": "filing_date", "amount": "coalesce(amount_usd,0)", "confidence": "confidence", "ticker": "ticker"}

app = FastAPI(
    title="PDB API — SEC Project Intelligence Database",
    version="1.0.0",
    description=(
        "API REST de la base SPID : 19 227 projets stratégiques et 39 930 mentions extraits des filings SEC "
        "(10-K, 10-Q, 8-K) de 500 sociétés du S&P 500, 2010–2026. Toutes les routes `/v1/*` exigent une clé d'API "
        "dans l'en-tête `X-API-Key` (ou `Authorization: Bearer <clé>`). Les réponses paginées renvoient "
        "`{total, limit, offset, items}`."
    ),
    docs_url="/docs", redoc_url="/redoc", openapi_url="/openapi.json",
)

# ---------------------------------------------------------------- sécurité
_buckets: dict[str, deque] = defaultdict(deque)
_bl = threading.Lock()


def _rate_limit(request: Request, scope: str):
    ip = request.headers.get("x-forwarded-for", request.client.host if request.client else "?").split(",")[0].strip()
    key = f"{scope}:{ip}"
    now = time.time()
    with _bl:
        q = _buckets[key]
        while q and q[0] < now - 60:
            q.popleft()
        if len(q) >= RATE_PER_MIN:
            raise HTTPException(429, "Trop de requêtes : limite de %d par minute." % RATE_PER_MIN)
        q.append(now)


def require_key(request: Request):
    _rate_limit(request, "v1")
    if not API_KEY:
        raise HTTPException(503, "API non configurée (clé absente côté serveur).")
    key = request.headers.get("x-api-key") or ""
    auth = request.headers.get("authorization") or ""
    if not key and auth.lower().startswith("bearer "):
        key = auth[7:].strip()
    if not key:
        key = request.query_params.get("api_key", "")
    if key != API_KEY:
        raise HTTPException(401, "Clé d'API manquante ou invalide (en-tête X-API-Key).")


def require_same_origin(request: Request):
    _rate_limit(request, "app")
    sfs = request.headers.get("sec-fetch-site")
    if sfs and sfs not in ("same-origin", "none"):
        raise HTTPException(403, "Accès réservé à l'interface web.")
    origin = request.headers.get("origin") or request.headers.get("referer")
    host = request.headers.get("x-forwarded-host") or request.headers.get("host") or ""
    if origin:
        from urllib.parse import urlparse
        if urlparse(origin).netloc.split(":")[0] != host.split(":")[0]:
            raise HTTPException(403, "Accès réservé à l'interface web.")


# ---------------------------------------------------------------- helpers
def _like(v: str) -> str:
    return f"%{v.lower().strip()}%"


def _page(limit: int, offset: int) -> tuple[int, int]:
    return max(1, min(limit, 500)), max(0, offset)


def _project_filters(q, type_, sector, status, ticker, cik, location, tech, partner, min_amount, max_amount,
                     year_from, year_to, min_conf, has_amount):
    where, params = [], []
    if q:
        where.append("(lower(project_name) like ? or lower(description) like ? or lower(company_name) like ? or lower(ticker) = ?)")
        params += [_like(q), _like(q), _like(q), q.lower().strip()]
    if type_:
        ts = [t.strip() for t in type_.split(",") if t.strip()]
        where.append("project_type in (" + ",".join("?" * len(ts)) + ")"); params += ts
    if sector:
        where.append("lower(sector) like ?"); params.append(_like(sector))
    if status:
        ss = [s.strip() for s in status.split(",") if s.strip()]
        where.append("status in (" + ",".join("?" * len(ss)) + ")"); params += ss
    if ticker:
        where.append("upper(ticker) = ?"); params.append(ticker.upper().strip())
    if cik:
        where.append("cik = ?"); params.append(cik.zfill(10))
    if location:
        where.append("lower(canonical_location) like ?"); params.append(_like(location))
    if tech:
        where.append("exists (select 1 from unnest(technologies) as u(t) where lower(t) like ?)"); params.append(_like(tech))
    if partner:
        where.append("project_id in (select md5(cik||'|'||project_type||'|'||coalesce(nullif(lower(coalesce(try(locations[1]),'')),''), regexp_extract(lower(regexp_replace(project_name,'[^A-Za-z ]','','g')),'([a-z]{4,})',1), 'general')) from project_mentions, unnest(partners) as u(p) where lower(p) like ?)")
        params.append(_like(partner))
    if min_amount is not None:
        where.append("total_amount_usd >= ?"); params.append(min_amount)
    if max_amount is not None:
        where.append("total_amount_usd <= ?"); params.append(max_amount)
    if year_from is not None:
        where.append("year(last_seen) >= ?"); params.append(year_from)
    if year_to is not None:
        where.append("year(first_seen) <= ?"); params.append(year_to)
    if min_conf is not None:
        where.append("avg_confidence >= ?"); params.append(min_conf)
    if has_amount:
        where.append("total_amount_usd > 0")
    return (" where " + " and ".join(where)) if where else "", params


def _mention_filters(q, type_, sector, status, ticker, cik, form, date_from, date_to, min_amount, min_conf, accession, section):
    where, params = [], []
    if q:
        where.append("(lower(project_name) like ? or lower(description) like ? or lower(company_name) like ?)")
        params += [_like(q)] * 3
    if type_:
        ts = [t.strip() for t in type_.split(",") if t.strip()]
        where.append("project_type in (" + ",".join("?" * len(ts)) + ")"); params += ts
    if sector:
        where.append("lower(sector) like ?"); params.append(_like(sector))
    if status:
        where.append("status = ?"); params.append(status)
    if ticker:
        where.append("upper(ticker) = ?"); params.append(ticker.upper().strip())
    if cik:
        where.append("cik = ?"); params.append(cik.zfill(10))
    if form:
        fs = [f.strip() for f in form.split(",") if f.strip()]
        where.append("form_type in (" + ",".join("?" * len(fs)) + ")"); params += fs
    if date_from:
        where.append("filing_date >= ?"); params.append(date_from)
    if date_to:
        where.append("filing_date <= ?"); params.append(date_to)
    if min_amount is not None:
        where.append("amount_usd >= ?"); params.append(min_amount)
    if min_conf is not None:
        where.append("confidence >= ?"); params.append(min_conf)
    if accession:
        where.append("accession_number = ?"); params.append(accession)
    if section:
        where.append("section_id = ?"); params.append(section)
    return (" where " + " and ".join(where)) if where else "", params


class SqlBody(BaseModel):
    sql: str = Field(..., description="Requête SQL DuckDB de lecture (SELECT / WITH / DESCRIBE / SUMMARIZE).")
    limit: int = Field(500, ge=1, le=5000, description="Nombre maximal de lignes renvoyées.")


# ---------------------------------------------------------------- routes (fabrique, montée deux fois)
def make_router(tag: str) -> APIRouter:
    r = APIRouter(tags=[tag])

    @r.get("/stats", summary="Vue d'ensemble : compteurs et répartitions")
    def stats():
        ov = db.one("""select count(*) projects, sum(n_mentions) mentions, count(distinct cik) companies,
                       count(distinct sector) sectors, count(*) filter (where total_amount_usd>0) with_amount,
                       median(total_amount_usd) median_amount_usd, sum(total_amount_usd) total_amount_usd,
                       min(first_seen) first_seen, max(last_seen) last_seen from projects""")
        ov["mentions"] = db.scalar("select count(*) from project_mentions")
        ov["filings"] = db.scalar("select count(distinct accession_number) from project_mentions")
        ov["kg_nodes"] = db.scalar("select count(*) from kg_nodes")
        ov["kg_edges"] = db.scalar("select count(*) from kg_edges")
        ov["sections_10k"] = db.scalar("select count(*) from spid_sections")
        by_type = db.rows("""select project_type, count(*) n, sum(total_amount_usd) amount_usd, median(total_amount_usd) median_usd,
                             round(avg(avg_confidence),3) confidence from projects group by 1 order by n desc""")
        for t in by_type:
            t["label"] = TYPE_LABELS.get(t["project_type"], t["project_type"])
        return {
            "overview": ov,
            "by_type": by_type,
            "by_sector": db.rows("select sector, count(*) n, count(distinct cik) companies, sum(total_amount_usd) amount_usd from projects where sector is not null group by 1 order by n desc"),
            "by_status": db.rows("select status, count(*) n from projects group by 1 order by n desc"),
            "by_form": db.rows("select form_type, count(*) n from project_mentions group by 1 order by n desc"),
            "mentions_by_year": db.rows("""select year(filing_date) as yr, form_type, count(*) n from project_mentions
                                           where filing_date >= '2010-01-01' group by 1,2 order by 1,2"""),
            "projects_by_year": db.rows("select year(first_seen) as yr, count(*) n, sum(total_amount_usd) amount_usd from projects where first_seen >= '2010-01-01' group by 1 order by 1"),
            "themes_by_year": db.rows("""select year(filing_date) as yr, project_type, count(*) n from project_mentions
                                         where project_type in ('ai_initiative','data_center','cloud_migration','digital_transformation','sustainability','energy_transition')
                                         and filing_date >= '2010-01-01' group by 1,2 order by 1,2"""),
            "sector_type": db.rows("select sector, project_type, count(*) n from projects where sector is not null group by 1,2"),
            "top_companies": db.rows("select ticker, any_value(company_name) company_name, any_value(sector) sector, count(*) n, sum(total_amount_usd) amount_usd from projects group by 1 order by n desc limit 20"),
            "top_technologies": db.rows("select lower(trim(t)) technology, count(*) n from projects, unnest(technologies) as u(t) where t<>'' group by 1 order by n desc limit 25"),
            "top_locations": db.rows("select canonical_location as loc, count(*) n from projects where canonical_location is not null and canonical_location<>'' group by 1 order by n desc limit 25"),
        }

    @r.get("/taxonomy", summary="Les 21 types de projets (libellés et effectifs)")
    def taxonomy():
        counts = {x["project_type"]: x for x in db.rows("select project_type, count(*) n, sum(total_amount_usd) amount_usd from projects group by 1")}
        return [{"project_type": k, "label": v, "n": counts.get(k, {}).get("n", 0), "amount_usd": counts.get(k, {}).get("amount_usd")} for k, v in TYPE_LABELS.items()]

    @r.get("/sectors", summary="Secteurs GICS")
    def sectors():
        return db.rows("select sector, count(*) n, count(distinct cik) companies, sum(total_amount_usd) amount_usd from projects where sector is not null group by 1 order by n desc")

    @r.get("/technologies", summary="Technologies citées")
    def technologies(q: Optional[str] = None, limit: int = Query(50, le=500)):
        w, p = ("where lower(t) like ?", [_like(q)]) if q else ("", [])
        return db.rows(f"select lower(trim(t)) technology, count(*) n from projects, unnest(technologies) as u(t) {w} {'and' if w else 'where'} t<>'' group by 1 order by n desc limit {int(limit)}", p)

    @r.get("/locations", summary="Localisations canoniques")
    def locations(q: Optional[str] = None, limit: int = Query(50, le=500)):
        w, p = ("and lower(canonical_location) like ?", [_like(q)]) if q else ("", [])
        return db.rows(f"select canonical_location as loc, count(*) n, sum(total_amount_usd) amount_usd from projects where canonical_location is not null and canonical_location<>'' {w} group by 1 order by n desc limit {int(limit)}", p)

    @r.get("/partners", summary="Partenaires cités dans les mentions")
    def partners(q: Optional[str] = None, limit: int = Query(50, le=500)):
        w, p = ("and lower(p) like ?", [_like(q)]) if q else ("", [])
        return db.rows(f"select lower(trim(p)) partner, any_value(p) as lbl, count(*) n, count(distinct cik) companies from project_mentions, unnest(partners) as u(p) where p<>'' {w} group by 1 order by n desc limit {int(limit)}", p)

    @r.get("/projects", summary="Rechercher des projets (filtres + pagination)")
    def projects(
        q: Optional[str] = Query(None, description="Texte libre : nom, description, entreprise, ticker"),
        type: Optional[str] = Query(None, description="project_type (liste séparée par des virgules)"),
        sector: Optional[str] = None, status: Optional[str] = Query(None, description="planned,in_progress,completed,mentioned"),
        ticker: Optional[str] = None, cik: Optional[str] = None, location: Optional[str] = None, tech: Optional[str] = None,
        partner: Optional[str] = None,
        min_amount: Optional[float] = None, max_amount: Optional[float] = None,
        year_from: Optional[int] = None, year_to: Optional[int] = None, min_confidence: Optional[float] = None,
        has_amount: bool = False,
        sort: str = Query("mentions", description="amount | mentions | filings | first_seen | last_seen | confidence | name | ticker | type"),
        order: str = Query("desc", pattern="^(asc|desc)$"),
        limit: int = Query(50, ge=1, le=500), offset: int = Query(0, ge=0),
    ):
        w, p = _project_filters(q, type, sector, status, ticker, cik, location, tech, partner, min_amount, max_amount, year_from, year_to, min_confidence, has_amount)
        limit, offset = _page(limit, offset)
        col = PROJECT_SORTS.get(sort, "n_mentions")
        total = db.scalar(f"select count(*) from projects{w}", p)
        items = db.rows(f"select * from projects{w} order by {col} {order} nulls last, project_id limit {limit} offset {offset}", p)
        for it in items:
            it["type_label"] = TYPE_LABELS.get(it["project_type"], it["project_type"])
        return {"total": total, "limit": limit, "offset": offset, "items": items}

    @r.get("/projects/export.csv", summary="Exporter les projets filtrés en CSV (max 50 000 lignes)")
    def projects_csv(
        q: Optional[str] = None, type: Optional[str] = None, sector: Optional[str] = None, status: Optional[str] = None,
        ticker: Optional[str] = None, cik: Optional[str] = None, location: Optional[str] = None, tech: Optional[str] = None,
        partner: Optional[str] = None, min_amount: Optional[float] = None, max_amount: Optional[float] = None,
        year_from: Optional[int] = None, year_to: Optional[int] = None, min_confidence: Optional[float] = None, has_amount: bool = False,
    ):
        w, p = _project_filters(q, type, sector, status, ticker, cik, location, tech, partner, min_amount, max_amount, year_from, year_to, min_confidence, has_amount)
        items = db.rows(f"select * from projects{w} order by n_mentions desc limit 50000", p)

        def gen():
            buf = io.StringIO(); wr = csv.writer(buf)
            cols = list(items[0].keys()) if items else ["project_id"]
            wr.writerow(cols); yield buf.getvalue(); buf.seek(0); buf.truncate()
            for it in items:
                wr.writerow(["|".join(map(str, v)) if isinstance(v, list) else v for v in it.values()])
                yield buf.getvalue(); buf.seek(0); buf.truncate()
        return StreamingResponse(gen(), media_type="text/csv", headers={"Content-Disposition": "attachment; filename=spid_projects.csv"})

    @r.get("/projects/{project_id}", summary="Fiche complète d'un projet (chronologie, mentions, graphe, similaires)")
    def project(project_id: str):
        pr = db.one("select * from projects where project_id = ?", [project_id])
        if not pr:
            raise HTTPException(404, "Projet introuvable.")
        pr["type_label"] = TYPE_LABELS.get(pr["project_type"], pr["project_type"])
        timeline = db.rows("select * from project_timeline where project_id = ? order by filing_date", [project_id])
        # mentions rattachées : même clé de résolution que spid/resolve.py
        mentions = db.rows("""with m as (select *, lower(coalesce(try(locations[1]),'')) loc1,
                              regexp_extract(lower(regexp_replace(project_name,'[^A-Za-z ]','','g')),'([a-z]{4,})',1) name_tok from project_mentions)
                              select * exclude (loc1, name_tok) from m
                              where md5(cik||'|'||project_type||'|'||coalesce(nullif(loc1,''), nullif(name_tok,''), 'general')) = ?
                              order by filing_date""", [project_id])
        edges = db.rows("""select e.rel, e.src, e.dst, n.node_type, n.label from kg_edges e join kg_nodes n on n.node_id = case when e.src = ? then e.dst else e.src end
                           where e.src = ? or e.dst = ?""", ["P:" + project_id] * 3)
        sims = db.similar_ids(project_id, 8)
        similar = []
        if sims:
            ids = [s[0] for s in sims]
            found = {x["project_id"]: x for x in db.rows("select project_id, ticker, company_name, project_type, project_name, status, total_amount_usd, first_seen from projects where project_id in (" + ",".join("?" * len(ids)) + ")", ids)}
            for pid, sc in sims:
                if pid in found:
                    found[pid]["score"] = round(sc, 4); found[pid]["type_label"] = TYPE_LABELS.get(found[pid]["project_type"]); similar.append(found[pid])
        return {"project": pr, "timeline": timeline, "mentions": mentions, "graph": edges, "similar": similar}

    @r.get("/projects/{project_id}/similar", summary="Projets sémantiquement proches (vecteurs MiniLM stockés)")
    def project_similar(project_id: str, k: int = Query(10, le=50)):
        sims = db.similar_ids(project_id, k)
        if not sims and not db.one("select 1 from projects where project_id=?", [project_id]):
            raise HTTPException(404, "Projet introuvable.")
        ids = [s[0] for s in sims]
        found = {x["project_id"]: x for x in db.rows("select * from projects where project_id in (" + ",".join("?" * len(ids)) + ")", ids)} if ids else {}
        return [dict(found[pid], score=round(sc, 4)) for pid, sc in sims if pid in found]

    @r.get("/mentions", summary="Rechercher des mentions (unité d'extraction, une par section et par projet)")
    def mentions(
        q: Optional[str] = None, type: Optional[str] = None, sector: Optional[str] = None, status: Optional[str] = None,
        ticker: Optional[str] = None, cik: Optional[str] = None, form: Optional[str] = Query(None, description="10-K,10-Q,8-K"),
        date_from: Optional[str] = None, date_to: Optional[str] = None, min_amount: Optional[float] = None,
        min_confidence: Optional[float] = None, accession: Optional[str] = None, section: Optional[str] = None,
        sort: str = Query("date", description="date | amount | confidence | ticker"), order: str = Query("desc", pattern="^(asc|desc)$"),
        limit: int = Query(50, ge=1, le=500), offset: int = Query(0, ge=0),
    ):
        w, p = _mention_filters(q, type, sector, status, ticker, cik, form, date_from, date_to, min_amount, min_confidence, accession, section)
        limit, offset = _page(limit, offset)
        col = MENTION_SORTS.get(sort, "filing_date")
        total = db.scalar(f"select count(*) from project_mentions{w}", p)
        items = db.rows(f"""select *, md5(cik||'|'||project_type||'|'||coalesce(nullif(lower(coalesce(try(locations[1]),'')),''),
                            nullif(regexp_extract(lower(regexp_replace(project_name,'[^A-Za-z ]','','g')),'([a-z]{{4,}})',1),''), 'general')) project_id
                            from project_mentions{w} order by {col} {order} nulls last, mention_id limit {limit} offset {offset}""", p)
        for it in items:
            it["type_label"] = TYPE_LABELS.get(it["project_type"], it["project_type"])
        return {"total": total, "limit": limit, "offset": offset, "items": items}

    @r.get("/mentions/{mention_id}", summary="Une mention et sa section source (10-K)")
    def mention(mention_id: str):
        m = db.one("select * from project_mentions where mention_id = ?", [mention_id])
        if not m:
            raise HTTPException(404, "Mention introuvable.")
        m["type_label"] = TYPE_LABELS.get(m["project_type"], m["project_type"])
        m["project_id"] = db.scalar("""select md5(cik||'|'||project_type||'|'||coalesce(nullif(lower(coalesce(try(locations[1]),'')),''),
                                       nullif(regexp_extract(lower(regexp_replace(project_name,'[^A-Za-z ]','','g')),'([a-z]{4,})',1),''), 'general'))
                                       from project_mentions where mention_id = ?""", [mention_id])
        sec = db.one("select section_id, accession_number, section_name, word_count from spid_sections where section_id = ?", [m["section_id"]])
        return {"mention": m, "section": sec, "section_text_url": f"/sections/{m['section_id']}" if sec else None}

    @r.get("/sections/{section_id}", summary="Texte d'une section 10-K ingérée par SPID")
    def section(section_id: str, highlight: Optional[str] = Query(None, description="Mot à repérer (renvoie les positions)")):
        s = db.one("select * from spid_sections where section_id = ?", [section_id])
        if not s:
            raise HTTPException(404, "Section introuvable (seules les sections 10-K sont stockées dans la base ; les sections 10-Q/8-K restent dans le corpus parquet amont).")
        if highlight:
            import re
            s["highlights"] = [m.start() for m in re.finditer(re.escape(highlight), s["text"], re.IGNORECASE)][:200]
        return s

    @r.get("/companies", summary="Entreprises et taille de leur portefeuille de projets")
    def companies(q: Optional[str] = None, sector: Optional[str] = None, sort: str = Query("projects", description="projects | amount | mentions | ticker"),
                  order: str = Query("desc", pattern="^(asc|desc)$"), limit: int = Query(100, ge=1, le=500), offset: int = Query(0, ge=0)):
        where, p = [], []
        if q:
            where.append("(lower(company_name) like ? or lower(ticker) like ?)"); p += [_like(q), _like(q)]
        if sector:
            where.append("lower(sector) like ?"); p.append(_like(sector))
        w = (" where " + " and ".join(where)) if where else ""
        col = {"projects": "n_projects", "amount": "coalesce(amount_usd,0)", "mentions": "n_mentions", "ticker": "ticker"}.get(sort, "n_projects")
        limit, offset = _page(limit, offset)
        base = f"""with c as (select cik, any_value(ticker) ticker, any_value(company_name) company_name, any_value(sector) sector,
                   count(*) n_projects, sum(n_mentions) n_mentions, sum(total_amount_usd) amount_usd, min(first_seen) first_seen, max(last_seen) last_seen,
                   count(distinct project_type) n_types from projects group by cik) select * from c{w}"""
        total = db.scalar(f"select count(*) from ({base})", p)
        items = db.rows(f"{base} order by {col} {order} nulls last limit {limit} offset {offset}", p)
        return {"total": total, "limit": limit, "offset": offset, "items": items}

    @r.get("/companies/{ident}", summary="Profil d'une entreprise (ticker ou CIK)")
    def company(ident: str):
        ident = ident.strip()
        cond, val = ("cik = ?", ident.zfill(10)) if ident.isdigit() else ("upper(ticker) = ?", ident.upper())
        prof = db.one(f"""select cik, any_value(ticker) ticker, any_value(company_name) company_name, any_value(sector) sector,
                          count(*) n_projects, sum(n_mentions) n_mentions, sum(total_amount_usd) amount_usd, min(first_seen) first_seen, max(last_seen) last_seen
                          from projects where {cond} group by cik""", [val])
        if not prof:
            raise HTTPException(404, "Entreprise introuvable.")
        cik = prof["cik"]
        return {
            "company": prof,
            "by_type": [dict(x, label=TYPE_LABELS.get(x["project_type"])) for x in db.rows("select project_type, count(*) n, sum(total_amount_usd) amount_usd from projects where cik=? group by 1 order by n desc", [cik])],
            "by_status": db.rows("select status, count(*) n from projects where cik=? group by 1", [cik]),
            "by_year": db.rows("select year(filing_date) as yr, count(*) n from project_mentions where cik=? and filing_date>='2010-01-01' group by 1 order by 1", [cik]),
            "by_form": db.rows("select form_type, count(*) n from project_mentions where cik=? group by 1", [cik]),
            "locations": db.rows("select canonical_location as loc, count(*) n from projects where cik=? and canonical_location<>'' group by 1 order by n desc limit 15", [cik]),
            "technologies": db.rows("select lower(t) technology, count(*) n from projects, unnest(technologies) as u(t) where cik=? group by 1 order by n desc limit 15", [cik]),
            "partners": db.rows("select any_value(p) partner, count(*) n from project_mentions, unnest(partners) as u(p) where cik=? and p<>'' group by lower(p) order by n desc limit 15", [cik]),
            "projects": [dict(x, type_label=TYPE_LABELS.get(x["project_type"])) for x in db.rows("select * from projects where cik=? order by n_mentions desc, total_amount_usd desc nulls last limit 500", [cik])],
        }

    @r.get("/graph/search", summary="Chercher un nœud du graphe (entreprise, projet, lieu, technologie, partenaire)")
    def graph_search(q: str, type: Optional[str] = Query(None, description="Company | Project | Location | Technology | Partner"), limit: int = Query(30, le=200)):
        w, p = "where lower(label) like ?", [_like(q)]
        if type:
            w += " and node_type = ?"; p.append(type)
        return db.rows(f"""select n.node_id, n.node_type, n.label, n.props, (select count(*) from kg_edges e where e.src=n.node_id or e.dst=n.node_id) degree
                           from kg_nodes n {w} order by degree desc limit {int(limit)}""", p)

    @r.get("/graph/node/{node_id:path}", summary="Un nœud et son voisinage")
    def graph_node(node_id: str, limit: int = Query(200, le=2000)):
        n = db.one("select * from kg_nodes where node_id = ?", [node_id])
        if not n:
            raise HTTPException(404, "Nœud introuvable.")
        edges = db.rows(f"""select e.rel, e.src, e.dst, e.props, case when e.src = ? then 'out' else 'in' end direction,
                            m.node_type neighbor_type, m.label neighbor_label, m.node_id neighbor_id, m.props neighbor_props
                            from kg_edges e join kg_nodes m on m.node_id = case when e.src = ? then e.dst else e.src end
                            where e.src = ? or e.dst = ? limit {int(limit)}""", [node_id] * 4)
        degree = db.scalar("select count(*) from kg_edges where src = ? or dst = ?", [node_id, node_id])
        return {"node": n, "degree": degree, "edges": edges}

    @r.get("/search/semantic", summary="Recherche sémantique en texte libre (modèle all-MiniLM-L6-v2 côté serveur)")
    def semantic(q: str, k: int = Query(20, le=100), type: Optional[str] = None, sector: Optional[str] = None):
        from . import semantic as sem
        try:
            vec = sem.encode(q)
        except sem.Unavailable as e:
            raise HTTPException(501, str(e))
        hits = db.nearest_to_vector(vec, k * 5)
        ids = [h[0] for h in hits]
        where, p = ["project_id in (" + ",".join("?" * len(ids)) + ")"], list(ids)
        if type:
            where.append("project_type = ?"); p.append(type)
        if sector:
            where.append("lower(sector) like ?"); p.append(_like(sector))
        found = {x["project_id"]: x for x in db.rows("select * from projects where " + " and ".join(where), p)}
        out = []
        for pid, sc in hits:
            if pid in found:
                out.append(dict(found[pid], score=round(sc, 4), type_label=TYPE_LABELS.get(found[pid]["project_type"])))
            if len(out) >= k:
                break
        return {"query": q, "items": out}

    @r.get("/schema", summary="Tables et colonnes de la base")
    def schema():
        return db.schema()

    @r.post("/sql", summary="Bac à sable SQL en lecture seule (DuckDB)")
    def sql(body: SqlBody):
        try:
            return db.run_sql(body.sql, body.limit)
        except TimeoutError as e:
            raise HTTPException(408, str(e))
        except ValueError as e:
            raise HTTPException(400, str(e))

    return r


app.include_router(make_router("API v1 (clé requise)"), prefix="/v1", dependencies=[Depends(require_key)])
app.include_router(make_router("Interface web (même origine)"), prefix="/app", dependencies=[Depends(require_same_origin)], include_in_schema=False)


@app.get("/health", include_in_schema=False)
@app.get("/v1/health", summary="État du service (sans clé)", tags=["Service"])
def health():
    try:
        n = db.scalar("select count(*) from projects")
        return {"status": "ok", "projects": n, "db": os.path.basename(db.DB_PATH), "semantic": _semantic_state()}
    except Exception as e:  # noqa: BLE001
        return JSONResponse({"status": "error", "detail": str(e)[:200]}, status_code=503)


def _semantic_state():
    try:
        from . import semantic as sem
        return sem.state()
    except Exception:
        return "unavailable"


@app.get("/v1/meta", summary="Métadonnées de l'API (routes, limites, exemples)", tags=["Service"])
def meta():
    return {
        "name": "PDB API", "version": app.version, "base_url": PUBLIC_URL + "/v1",
        "auth": "En-tête X-API-Key: <clé> (ou Authorization: Bearer <clé>). La clé est fournie par l'équipe UQO ; elle n'est publiée nulle part sur le site.",
        "rate_limit_per_minute": RATE_PER_MIN, "pagination": "limit (≤500) / offset ; réponses {total, limit, offset, items}",
        "routes": [f"{r.methods and list(r.methods)[0]} {r.path}" for r in app.routes if getattr(r, "path", "").startswith("/v1")],
        "docs": PUBLIC_URL + "/docs",
    }


@app.on_event("startup")
def _warm():
    def w():
        try:
            db.connect(); db.schema(); db.embeddings()
        except Exception as e:  # noqa: BLE001
            print("warmup:", e)
    threading.Thread(target=w, daemon=True).start()


# ---------------------------------------------------------------- statique
if (WEB / "report.pdf").exists():
    @app.get("/report.pdf", include_in_schema=False)
    def report():
        return FileResponse(WEB / "report.pdf", media_type="application/pdf")

app.mount("/", StaticFiles(directory=str(WEB), html=True), name="web")