# ============================================================
# Rapport UQO — SPID : génération des figures et tableaux LaTeX
#   python3 docs/rapport_uqo/make_figures.py
# Lit uniquement les tables physiques de data/db/spid.duckdb
# (les vues src_* pointent vers un ancien chemin et sont ignorées).
# ============================================================
from __future__ import annotations

import json
from pathlib import Path

import duckdb
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
import numpy as np

HERE = Path(__file__).resolve().parent
ROOT = HERE.parent.parent
DB = ROOT / "data" / "db" / "spid.duckdb"
FIG = HERE / "figures"
TAB = HERE / "tables"
FIG.mkdir(exist_ok=True)
TAB.mkdir(exist_ok=True)

plt.rcParams.update({
    "font.family": "sans-serif",
    "font.sans-serif": ["Fira Sans", "Helvetica", "Arial", "DejaVu Sans"],
    "font.size": 9.5, "axes.titlesize": 11, "axes.titleweight": "bold",
    "axes.spines.top": False, "axes.spines.right": False,
    "figure.dpi": 160, "savefig.bbox": "tight", "axes.grid": True,
    "grid.alpha": 0.25, "grid.linewidth": 0.5, "legend.frameon": False,
})
BLEU = "#003E7E"; BLEUC = "#0066B3"; OR = "#C6A300"; GRIS = "#58595B"
VERT = "#008046"; ROUGE = "#B42318"; ORANGE = "#D67A00"; VIOLET = "#5E35B1"; TEAL = "#007979"
PAL = [BLEU, OR, TEAL, ORANGE, VERT, VIOLET, ROUGE, BLEUC, GRIS, "#8DA9C4", "#E0C36B"]

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",
}
SECT = {
    "Industrials": "Industrie", "Health Care": "Santé", "Information Technology": "Technologies de l'information",
    "Consumer Discretionary": "Consommation discrétionnaire", "Utilities": "Services publics",
    "Financials": "Finance", "Consumer Staples": "Consommation de base", "Real Estate": "Immobilier",
    "Energy": "Énergie", "Materials": "Matériaux", "Communication Services": "Services de communication",
}
STATUT = {"in_progress": "En cours", "completed": "Terminé", "planned": "Planifié", "mentioned": "Mentionné"}

con = duckdb.connect(str(DB), read_only=True)
q = lambda sql: con.execute(sql).fetchall()
stats: dict = {}


T = lambda s: s.replace("&", "\\&")


def fr(n: float, dec: int = 0) -> str:
    """Format nombre à la française pour LaTeX : 19\\,227 ; 4\\,602{,}6."""
    if dec == 0:
        s = f"{int(round(n)):,}".replace(",", "\\,")
    else:
        s = f"{n:,.{dec}f}".replace(",", "\\,").replace(".", "{,}")
    return s


def save(fig, name):
    fig.savefig(FIG / f"{name}.pdf")
    fig.savefig(FIG / f"{name}.png")
    plt.close(fig)
    print("  fig", name)


# ------------------------------------------------------------------ 1. entonnoir
def fig_entonnoir():
    # sections éligibles (>= 30 mots) réellement passées à l'extracteur : registre extracted_sections
    n_sec = q("select count(*) from extracted_sections")[0][0]
    n_sec_10k = q("select count(*) from extracted_sections e join v_sections v using(section_id) where v.form_type = '10-K'")[0][0]
    n_men = q("select count(*) from project_mentions")[0][0]
    n_proj = q("select count(*) from projects")[0][0]
    n_fil = q("select count(distinct accession_number) from project_mentions")[0][0]
    stats.update(n_sections=n_sec, n_sections_10k=n_sec_10k, n_mentions=n_men, n_projects=n_proj,
                 n_filings_with_mentions=n_fil)
    labels = ["Sections de\nfilings analysées", "Filings avec au\nmoins un projet", "Mentions de\nprojets extraites",
              "Projets\nrésolus"]
    vals = [n_sec, n_fil, n_men, n_proj]
    fig, ax = plt.subplots(figsize=(8, 3.6))
    bars = ax.barh(labels[::-1], vals[::-1], color=[OR, TEAL, BLEUC, BLEU])
    for b, v in zip(bars, vals[::-1]):
        ax.text(b.get_width() + n_sec * 0.01, b.get_y() + b.get_height() / 2, f"{v:,}".replace(",", " "),
                va="center", fontsize=10, fontweight="bold", color=GRIS)
    ax.set_xlim(0, n_sec * 1.18)
    ax.xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"{int(x/1000)} k"))
    ax.set_xlabel("nombre d'unités")
    ax.set_title("De la section de filing au projet résolu")
    save(fig, "fig_entonnoir")


# ------------------------------------------------------------------ 2. mentions par année / formulaire
def fig_mentions_annee():
    rows = q("""select year(filing_date) y, form_type, count(*) from project_mentions
                where filing_date between '2010-01-01' and '2026-12-31' group by 1,2 order by 1,2""")
    years = sorted({r[0] for r in rows})
    forms = ["10-K", "10-Q", "8-K"]
    data = {f: [0] * len(years) for f in forms}
    for y, f, n in rows:
        data[f][years.index(y)] = n
    fig, ax = plt.subplots(figsize=(8.5, 3.8))
    bottom = np.zeros(len(years))
    for f, c in zip(forms, [BLEU, OR, TEAL]):
        ax.bar(years, data[f], bottom=bottom, color=c, label=f, width=0.78)
        bottom += np.array(data[f])
    for x, t in zip(years, bottom):
        ax.text(x, t + 30, f"{int(t):,}".replace(",", " "), ha="center", fontsize=7, color=GRIS)
    ax.set_xticks(years); ax.tick_params(axis="x", labelrotation=45)
    ax.set_ylabel("mentions de projets"); ax.legend(title="Formulaire", ncol=3, loc="upper left")
    ax.set_title("Mentions de projets par année de dépôt et par formulaire")
    save(fig, "fig_mentions_annee")
    stats["mentions_by_form"] = {f: sum(v) for f, v in data.items()}


# ------------------------------------------------------------------ 3. types
def fig_types():
    rows = q("""select project_type, count(*), coalesce(sum(total_amount_usd),0), median(total_amount_usd)
                from projects group by 1 order by 2 desc""")
    lab = [LABELS[r[0]] for r in rows][::-1]
    n = [r[1] for r in rows][::-1]
    usd = [r[2] / 1e9 for r in rows][::-1]
    fig, (a1, a2) = plt.subplots(1, 2, figsize=(9.6, 6), sharey=True)
    a1.barh(lab, n, color=BLEU)
    for i, v in enumerate(n):
        a1.text(v + 40, i, f"{v:,}".replace(",", " "), va="center", fontsize=7.5, color=GRIS)
    a1.set_title("Nombre de projets"); a1.set_xlabel("projets résolus"); a1.set_xlim(0, max(n) * 1.15)
    a2.barh(lab, usd, color=OR)
    for i, v in enumerate(usd):
        a2.text(v + 100, i, f"{v:,.0f}".replace(",", " "), va="center", fontsize=7.5, color=GRIS)
    a2.set_title("Capital annoncé (G$ US)"); a2.set_xlabel("somme des montants divulgués"); a2.set_xlim(0, max(usd) * 1.18)
    fig.suptitle("Les 21 types de projets de la taxonomie SPID", fontweight="bold", y=1.0)
    save(fig, "fig_types")
    with open(TAB / "tab_types.tex", "w") as f:
        for r in rows:
            med = f"{r[3]/1e6:,.0f}".replace(",", "\\,") if r[3] else "---"
            f.write(f"\\code{{{r[0].replace('_', '\\_')}}} & {T(LABELS[r[0]])} & {fr(r[1])} & {fr(r[2]/1e9,1)} & {med} \\\\\n")


# ------------------------------------------------------------------ 4. secteurs
def fig_secteurs():
    rows = q("""select sector, count(*), coalesce(sum(total_amount_usd),0), count(distinct cik),
                       avg(total_amount_usd) from projects where sector is not null group by 1 order by 2 desc""")
    lab = [SECT.get(r[0], r[0]) for r in rows][::-1]
    n = [r[1] for r in rows][::-1]; usd = [r[2] / 1e9 for r in rows][::-1]
    fig, (a1, a2) = plt.subplots(1, 2, figsize=(9.6, 4.4), sharey=True)
    a1.barh(lab, n, color=BLEUC); a1.set_title("Projets par secteur GICS"); a1.set_xlabel("projets")
    for i, v in enumerate(n):
        a1.text(v + 30, i, f"{v:,}".replace(",", " "), va="center", fontsize=8, color=GRIS)
    a1.set_xlim(0, max(n) * 1.15)
    a2.barh(lab, usd, color=OR); a2.set_title("Capital annoncé par secteur (G$ US)"); a2.set_xlabel("G$ US")
    for i, v in enumerate(usd):
        a2.text(v + 50, i, f"{v:,.0f}".replace(",", " "), va="center", fontsize=8, color=GRIS)
    a2.set_xlim(0, max(usd) * 1.18)
    save(fig, "fig_secteurs")
    with open(TAB / "tab_secteurs.tex", "w") as f:
        for r in rows:
            f.write(f"{SECT.get(r[0], r[0])} & {fr(r[3])} & {fr(r[1])} & {fr(r[1]/r[3],1)} & {fr(r[2]/1e9,1)} \\\\\n")


# ------------------------------------------------------------------ 5. heatmap secteur x type
def fig_heatmap():
    rows = q("select sector, project_type, count(*) from projects where sector is not null group by 1,2")
    sectors = [r[0] for r in q("select sector from projects where sector is not null group by 1 order by count(*) desc")]
    types = [r[0] for r in q("select project_type from projects group by 1 order by count(*) desc")]
    M = np.zeros((len(sectors), len(types)))
    for s, t, n in rows:
        M[sectors.index(s), types.index(t)] = n
    share = M / M.sum(axis=1, keepdims=True) * 100
    fig, ax = plt.subplots(figsize=(10.5, 5.2))
    im = ax.imshow(share, cmap="Blues", aspect="auto")
    ax.set_xticks(range(len(types))); ax.set_xticklabels([LABELS[t] for t in types], rotation=55, ha="right", fontsize=8)
    ax.set_yticks(range(len(sectors))); ax.set_yticklabels([SECT.get(s, s) for s in sectors], fontsize=8.5)
    for i in range(len(sectors)):
        for j in range(len(types)):
            v = share[i, j]
            if v >= 2:
                ax.text(j, i, f"{v:.0f}", ha="center", va="center", fontsize=6.5,
                        color="white" if v > share.max() * 0.55 else GRIS)
    ax.grid(False)
    cb = fig.colorbar(im, ax=ax, fraction=0.025, pad=0.02); cb.set_label("% des projets du secteur")
    ax.set_title("Composition du portefeuille de projets par secteur (parts en % par ligne)")
    save(fig, "fig_heatmap_secteur_type")


# ------------------------------------------------------------------ 6. statut
def fig_statut():
    rows = q("select status, count(*) from projects group by 1 order by 2 desc")
    rows2 = q("""select project_type, status, count(*) from projects group by 1,2""")
    types = [r[0] for r in q("select project_type from projects group by 1 order by count(*) desc limit 12")]
    fig, (a1, a2) = plt.subplots(1, 2, figsize=(10, 4.2), gridspec_kw={"width_ratios": [1, 1.6]})
    vals = [r[1] for r in rows]; labs = [f"{STATUT[r[0]]}\n{r[1]:,}".replace(",", " ") for r in rows]
    a1.pie(vals, labels=labs, colors=[BLEU, VERT, OR, GRIS], startangle=90, counterclock=False,
           labeldistance=1.18, wedgeprops=dict(width=0.42, edgecolor="white"), textprops=dict(fontsize=8.5))
    a1.set_title("Statut des projets résolus"); a1.grid(False)
    order = ["planned", "in_progress", "completed", "mentioned"]
    cols = {"planned": OR, "in_progress": BLEU, "completed": VERT, "mentioned": GRIS}
    D = {t: {s: 0 for s in order} for t in types}
    for t, s, n in rows2:
        if t in D:
            D[t][s] = n
    left = np.zeros(len(types))
    for s in order:
        v = np.array([D[t][s] / sum(D[t].values()) * 100 for t in types])
        a2.barh([LABELS[t] for t in types], v, left=left, color=cols[s], label=STATUT[s])
        left += v
    a2.invert_yaxis(); a2.set_xlim(0, 100); a2.set_xlabel("% des projets du type")
    a2.legend(ncol=4, loc="lower center", bbox_to_anchor=(0.5, -0.32), fontsize=8)
    a2.set_title("Statut selon le type (12 types principaux)")
    save(fig, "fig_statut")
    stats["status"] = {r[0]: r[1] for r in rows}


# ------------------------------------------------------------------ 7. montants
def fig_montants():
    amts = np.array([r[0] for r in q("select total_amount_usd from projects where total_amount_usd > 0")])
    stats.update(n_with_amount=int(len(amts)), amount_median_musd=float(np.median(amts) / 1e6),
                 amount_mean_musd=float(amts.mean() / 1e6), amount_p90_musd=float(np.percentile(amts, 90) / 1e6),
                 amount_total_gusd=float(amts.sum() / 1e9), amount_max_gusd=float(amts.max() / 1e9))
    rows = q("""select project_type, quantile_cont(total_amount_usd, [0.25,0.5,0.75]) from projects
                where total_amount_usd>0 group by 1 order by median(total_amount_usd) desc""")
    fig, (a1, a2) = plt.subplots(1, 2, figsize=(10, 4.2), gridspec_kw={"width_ratios": [1, 1.3]})
    a1.hist(np.log10(amts), bins=45, color=BLEU, alpha=0.9)
    med = np.log10(np.median(amts))
    a1.axvline(med, color=OR, lw=2, label=f"médiane {np.median(amts)/1e6:,.0f} M$".replace(",", " "))
    a1.set_xticks(range(3, 12)); a1.set_xticklabels(["1 k", "10 k", "100 k", "1 M", "10 M", "100 M", "1 G", "10 G", "100 G"], fontsize=8)
    a1.set_xlabel("montant divulgué (US$, échelle log)"); a1.set_ylabel("projets"); a1.legend()
    a1.set_title(f"Distribution des montants ({len(amts):,} projets)".replace(",", " "))
    lab = [LABELS[r[0]] for r in rows]
    q1 = np.array([r[1][0] for r in rows]) / 1e6; q2 = np.array([r[1][1] for r in rows]) / 1e6; q3 = np.array([r[1][2] for r in rows]) / 1e6
    y = np.arange(len(lab))
    a2.hlines(y, q1, q3, color=BLEUC, lw=5, alpha=0.5, label="intervalle interquartile")
    a2.plot(q2, y, "o", color=BLEU, label="médiane")
    a2.set_yticks(y); a2.set_yticklabels(lab, fontsize=7.5); a2.invert_yaxis()
    a2.set_xscale("log"); a2.set_xlabel("montant divulgué par projet (M$ US, log)")
    a2.legend(loc="lower right", fontsize=8); a2.set_title("Montant médian et IQR par type")
    save(fig, "fig_montants")


# ------------------------------------------------------------------ 8. entreprises
def fig_entreprises():
    rows = q("""select ticker, company_name, sector, count(*), coalesce(sum(total_amount_usd),0)
                from projects group by 1,2,3 order by 4 desc limit 20""")
    fig, ax = plt.subplots(figsize=(8.5, 5.6))
    lab = [f"{r[1]} ({r[0]})" for r in rows][::-1]; n = [r[3] for r in rows][::-1]
    secs = [r[2] for r in rows][::-1]
    uniq = list(dict.fromkeys(secs)); cmap = {s: PAL[i % len(PAL)] for i, s in enumerate(uniq)}
    ax.barh(lab, n, color=[cmap[s] for s in secs])
    for i, v in enumerate(n):
        ax.text(v + 1, i, str(v), va="center", fontsize=8, color=GRIS)
    from matplotlib.patches import Patch
    ax.legend(handles=[Patch(color=cmap[s], label=SECT.get(s, s)) for s in uniq], fontsize=7.5, loc="lower right")
    ax.set_xlabel("projets résolus"); ax.set_title("Les 20 entreprises aux portefeuilles de projets les plus fournis")
    save(fig, "fig_entreprises")
    with open(TAB / "tab_entreprises.tex", "w") as f:
        for r in rows[:15]:
            f.write(f"{r[0]} & {T(r[1])} & {SECT.get(r[2], r[2])} & {fr(r[3])} & {fr(r[4]/1e9,1)} \\\\\n")
    dist = q("""with c as (select cik, count(*) n from projects group by 1)
                select min(n), median(n), avg(n), max(n) from c""")[0]
    stats["projects_per_firm"] = dict(min=dist[0], median=dist[1], mean=dist[2], max=dist[3])


# ------------------------------------------------------------------ 9. technologies + localisations
def fig_tech_loc():
    tech = q("""select lower(trim(t)) as t, count(*) from projects, unnest(technologies) as u(t)
                where t is not null and t <> '' group by 1 order by 2 desc limit 20""")
    loc = q("""select canonical_location, count(*) from projects
               where canonical_location is not null and canonical_location <> '' group by 1 order by 2 desc limit 20""")
    fig, (a1, a2) = plt.subplots(1, 2, figsize=(10.5, 5.4))
    a1.barh([r[0].capitalize() for r in tech][::-1], [r[1] for r in tech][::-1], color=TEAL)
    a1.set_title("20 technologies les plus citées"); a1.set_xlabel("projets")
    a2.barh([r[0] for r in loc][::-1], [r[1] for r in loc][::-1], color=ORANGE)
    a2.set_title("20 localisations canoniques les plus fréquentes"); a2.set_xlabel("projets")
    save(fig, "fig_tech_localisations")
    stats["n_tech_distinct"] = q("select count(distinct lower(trim(t))) from projects, unnest(technologies) as u(t)")[0][0]
    stats["n_loc_distinct"] = q("select count(distinct canonical_location) from projects where canonical_location<>''")[0][0]
    stats["share_with_location"] = q("select avg(case when canonical_location is not null and canonical_location<>'' then 1 else 0 end) from projects")[0][0]
    stats["share_with_tech"] = q("select avg(case when len(technologies)>0 then 1 else 0 end) from projects")[0][0]


# ------------------------------------------------------------------ 10. nouveaux projets par année + thèmes émergents
def fig_dynamique():
    rows = q("""select year(first_seen) y, count(*) from projects where first_seen between '2010-01-01' and '2026-12-31'
                group by 1 order by 1""")
    years = [r[0] for r in rows]; n = [r[1] for r in rows]
    themes = ["ai_initiative", "data_center", "cloud_migration", "digital_transformation", "sustainability", "energy_transition"]
    rows2 = q(f"""select year(filing_date) y, project_type, count(*) from project_mentions
                  where project_type in ({','.join(repr(t) for t in themes)}) and filing_date between '2010-01-01' and '2026-12-31'
                  group by 1,2 order by 1""")
    tot = dict(q("select year(filing_date), count(*) from project_mentions where filing_date between '2010-01-01' and '2026-12-31' group by 1"))
    fig, (a1, a2) = plt.subplots(1, 2, figsize=(10.5, 4))
    a1.bar(years, n, color=BLEU, width=0.78)
    for x, v in zip(years, n):
        a1.text(x, v - 40, f"{v:,}".replace(",", " "), ha="center", va="top", fontsize=6.5, color="white", rotation=90)
    a1.set_xticks(years); a1.tick_params(axis="x", labelrotation=45); a1.set_ylabel("projets")
    a1.set_title("Nouveaux projets par année de première mention")
    ys = sorted(tot)
    for t, c in zip(themes, [ROUGE, VIOLET, BLEUC, TEAL, VERT, OR]):
        d = {y: 0 for y in ys}
        for y, tt, k in rows2:
            if tt == t:
                d[y] = k
        a2.plot(ys, [d[y] / tot[y] * 100 for y in ys], marker="o", ms=3, color=c, label=LABELS[t])
    a2.set_ylabel("% des mentions de l'année"); a2.set_xticks(ys); a2.tick_params(axis="x", labelrotation=45)
    a2.legend(fontsize=7.5, ncol=2); a2.set_title("Thèmes émergents : part des mentions annuelles")
    save(fig, "fig_dynamique")


# ------------------------------------------------------------------ 11. durée de vie + mentions par projet + confiance
def fig_cycle():
    dur = np.array([r[0] for r in q("select last_year-first_year from projects where first_year is not null and last_year is not null and last_year>=first_year")])
    nm = np.array([r[0] for r in q("select n_mentions from projects")])
    conf = np.array([r[0] for r in q("select confidence from project_mentions where confidence is not null")])
    stats.update(dur_median=float(np.median(dur)), dur_mean=float(dur.mean()),
                 share_single_year=float((dur == 0).mean()), share_5y_plus=float((dur >= 5).mean()),
                 share_single_mention=float((nm == 1).mean()), conf_mean=float(conf.mean()),
                 conf_median=float(np.median(conf)), share_conf_ge_08=float((conf >= 0.8).mean()))
    fig, (a1, a2, a3) = plt.subplots(1, 3, figsize=(11, 3.4))
    b = np.bincount(np.clip(dur, 0, 15))
    a1.bar(range(len(b)), b, color=BLEU); a1.set_xticks(range(0, 16, 3)); a1.set_xticklabels([str(i) if i < 15 else "15+" for i in range(0, 16, 3)])
    a1.set_xlabel("années entre première et dernière mention"); a1.set_ylabel("projets"); a1.set_title("Durée de suivi des projets")
    b2 = np.bincount(np.clip(nm, 1, 12))[1:]
    a2.bar(range(1, 13), b2, color=TEAL); a2.set_xticks(range(1, 13)); a2.set_xticklabels([str(i) if i < 12 else "12+" for i in range(1, 13)], fontsize=8)
    a2.set_xlabel("mentions fusionnées dans le projet"); a2.set_title("Mentions par projet"); a2.set_yscale("log")
    a3.hist(conf, bins=22, range=(0.45, 1.0), color=OR); a3.axvline(0.45, color=ROUGE, ls="--", lw=1, label="seuil 0,45")
    a3.set_xlabel("confiance attribuée par le modèle"); a3.set_title("Confiance des mentions"); a3.legend(fontsize=8)
    save(fig, "fig_cycle")


# ------------------------------------------------------------------ 12. sections 10-K ingérées
def fig_sections():
    rows = q("select v.section_name, count(*), avg(v.word_count), median(v.word_count) from extracted_sections e "
             "join v_sections v using(section_id) where v.form_type = '10-K' group by 1 order by 2 desc")
    noms = {"risk_factors": "Item 1A — Facteurs de risque", "business": "Item 1 — Activités", "md_and_a": "Item 7 — Rapport de gestion (MD&A)",
            "properties": "Item 2 — Propriétés", "quantitative_disclosures": "Item 7A — Risques de marché", "full_body": "Corps complet (repli)"}
    fig, (a1, a2) = plt.subplots(1, 2, figsize=(10, 3.6), sharey=True)
    lab = [noms.get(r[0], r[0]) for r in rows][::-1]
    a1.barh(lab, [r[1] for r in rows][::-1], color=BLEU); a1.set_title("Sections 10-K ingérées par SPID"); a1.set_xlabel("sections")
    a2.barh(lab, [r[3] for r in rows][::-1], color=OR); a2.set_title("Longueur médiane (mots)"); a2.set_xlabel("mots")
    a2.xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"{int(x):,}".replace(",", " ")))
    save(fig, "fig_sections_10k")
    with open(TAB / "tab_sections.tex", "w") as f:
        for r in rows:
            f.write(f"\\code{{{r[0].replace('_','\\_')}}} & {T(noms.get(r[0], r[0]))} & {fr(r[1])} & {fr(r[2])} & {fr(r[3])} \\\\\n")
    s = q("select count(distinct v.accession_number), sum(v.word_count) from extracted_sections e "
          "join v_sections v using(section_id) where v.form_type = '10-K'")[0]
    stats.update(n_10k_filings_ingested=s[0], words_10k=s[1])


# ------------------------------------------------------------------ 13. graphe
def fig_graphe():
    nodes = q("select node_type, count(*) from kg_nodes group by 1 order by 2 desc")
    edges = q("select rel, count(*) from kg_edges group by 1 order by 2 desc")
    deg = q("""select d, count(*) from (select dst, count(*) d from kg_edges where rel in ('located_in','uses') group by 1)
               group by 1 order by 1""")
    fig, (a1, a2) = plt.subplots(1, 2, figsize=(10, 3.8), gridspec_kw={"width_ratios": [1, 1.1]})
    nl = {"Project": "Projet", "Partner": "Partenaire", "Location": "Localisation", "Technology": "Technologie", "Company": "Entreprise"}
    a1.barh([nl[r[0]] for r in nodes][::-1], [r[1] for r in nodes][::-1], color=[VIOLET, TEAL, ORANGE, OR, BLEU])
    for i, r in enumerate(nodes[::-1]):
        a1.text(r[1] + 200, i, f"{r[1]:,}".replace(",", " "), va="center", fontsize=8, color=GRIS)
    a1.set_xlim(0, nodes[0][1] * 1.2); a1.set_title("Nœuds du graphe par type")
    x = np.array([r[0] for r in deg]); y = np.array([r[1] for r in deg])
    a2.loglog(x, y, "o", ms=4, color=BLEU, alpha=0.8)
    a2.set_xlabel("degré entrant (projets rattachés)"); a2.set_ylabel("nombre de nœuds")
    a2.set_title("Distribution des degrés — localisations et technologies")
    save(fig, "fig_graphe")
    stats["kg_nodes"] = {r[0]: r[1] for r in nodes}; stats["kg_edges"] = {r[0]: r[1] for r in edges}


# ------------------------------------------------------------------ 14. tableau des plus grands projets
def tab_top_projets():
    rows = q("""select ticker, project_name, project_type, total_amount_usd, first_year, last_year, n_mentions
                from projects where total_amount_usd>0 and total_amount_usd < 3e11 and n_mentions>=3 and first_year is not null and project_type<>'capex_program' order by total_amount_usd desc limit 15""")
    with open(TAB / "tab_top_projets.tex", "w") as f:
        for r in rows:
            name = r[1].replace("&", "\\&").replace("%", "\\%").replace("$", "\\$").replace("_", "\\_")
            if len(name) > 52:
                name = name[:50] + "…"
            f.write(f"{r[0]} & {name} & {T(LABELS[r[2]])} & {fr(r[3]/1e9,1)} & {r[4]}--{r[5]} & {r[6]} \\\\\n")
    rows = q("""select ticker, project_name, project_type, status, first_year, last_year, n_mentions, n_filings
                from projects order by n_mentions desc limit 10""")
    with open(TAB / "tab_plus_suivis.tex", "w") as f:
        for r in rows:
            name = r[1].replace("&", "\\&").replace("%", "\\%").replace("$", "\\$").replace("_", "\\_")
            if len(name) > 48:
                name = name[:46] + "…"
            f.write(f"{r[0]} & {name} & {T(LABELS[r[2]])} & {STATUT[r[3]]} & {r[4]}--{r[5]} & {r[6]} & {r[7]} \\\\\n")


# ------------------------------------------------------------------ 15. exemples de mentions
def tab_exemples():
    rows = q("""select ticker, form_type, filing_date, project_type, project_name, description, amount_usd, confidence
                from project_mentions where confidence>=0.9 and amount_usd>1e8 and len(description)<230
                and project_type in ('data_center','ai_initiative','plant_construction','energy_transition','drug_pipeline','erp_implementation')
                order by hash(mention_id) limit 6""")
    esc = lambda s: s.replace("&", "\\&").replace("%", "\\%").replace("$", "\\$").replace("_", "\\_").replace("#", "\\#")
    with open(TAB / "tab_exemples.tex", "w") as f:
        for r in rows:
            f.write(f"{r[0]} & {r[1]} \\newline {r[2]} & {T(LABELS[r[3]])} & \\textbf{{{esc(r[4])}}} \\newline {esc(r[5])} & {fr(r[6]/1e6)} & {r[7]:.2f} \\\\\n")


def globals_stats():
    stats["n_ciks"] = q("select count(distinct cik) from projects")[0][0]
    stats["n_sectors"] = q("select count(distinct sector) from projects where sector is not null")[0][0]
    d = q("select min(filing_date), max(filing_date), count(distinct accession_number) from project_mentions")[0]
    stats.update(date_min=str(d[0]), date_max=str(d[1]), n_filings=d[2])
    stats["n_partners"] = q("select count(distinct lower(trim(p))) from project_mentions, unnest(partners) as u(p) where p<>''")[0][0]
    stats["embeddings"] = q("select model, dim, count(*) from project_embeddings group by 1,2")[0]
    # macros LaTeX
    with open(TAB / "macros.tex", "w") as f:
        f.write(f"\\newcommand{{\\nSections}}{{{fr(stats['n_sections'])}}}\n")
        f.write(f"\\newcommand{{\\nMentions}}{{{fr(stats['n_mentions'])}}}\n")
        f.write(f"\\newcommand{{\\nProjets}}{{{fr(stats['n_projects'])}}}\n")
        f.write(f"\\newcommand{{\\nFilings}}{{{fr(stats['n_filings'])}}}\n")
        f.write(f"\\newcommand{{\\nCiks}}{{{fr(stats['n_ciks'])}}}\n")
        f.write(f"\\newcommand{{\\nAvecMontant}}{{{fr(stats['n_with_amount'])}}}\n")
        f.write(f"\\newcommand{{\\pctAvecMontant}}{{{fr(100*stats['n_with_amount']/stats['n_projects'],1)}}}\n")
        f.write(f"\\newcommand{{\\medianeMontant}}{{{fr(stats['amount_median_musd'])}}}\n")
        f.write(f"\\newcommand{{\\moyenneMontant}}{{{fr(stats['amount_mean_musd'])}}}\n")
        f.write(f"\\newcommand{{\\totalMontant}}{{{fr(stats['amount_total_gusd'])}}}\n")
        f.write(f"\\newcommand{{\\nLoc}}{{{fr(stats['n_loc_distinct'])}}}\n")
        f.write(f"\\newcommand{{\\nTech}}{{{fr(stats['n_tech_distinct'])}}}\n")
        f.write(f"\\newcommand{{\\nPartenaires}}{{{fr(stats['n_partners'])}}}\n")
        f.write(f"\\newcommand{{\\nDixK}}{{{fr(stats['n_10k_filings_ingested'])}}}\n")
        f.write(f"\\newcommand{{\\nSectionsDixK}}{{{fr(stats['n_sections_10k'])}}}\n")
        f.write(f"\\newcommand{{\\motsDixK}}{{{fr(stats['words_10k']/1e6,0)}}}\n")
        f.write(f"\\newcommand{{\\confMoy}}{{{fr(stats['conf_mean'],2)}}}\n")
        f.write(f"\\newcommand{{\\pctConfHuit}}{{{fr(100*stats['share_conf_ge_08'],1)}}}\n")
        f.write(f"\\newcommand{{\\pctMonoMention}}{{{fr(100*stats['share_single_mention'],1)}}}\n")
        f.write(f"\\newcommand{{\\pctMonoAnnee}}{{{fr(100*stats['share_single_year'],1)}}}\n")
        f.write(f"\\newcommand{{\\pctCinqAns}}{{{fr(100*stats['share_5y_plus'],1)}}}\n")
        f.write(f"\\newcommand{{\\dureeMoy}}{{{fr(stats['dur_mean'],1)}}}\n")
        f.write(f"\\newcommand{{\\pctAvecLoc}}{{{fr(100*stats['share_with_location'],1)}}}\n")
        f.write(f"\\newcommand{{\\pctAvecTech}}{{{fr(100*stats['share_with_tech'],1)}}}\n")
        f.write(f"\\newcommand{{\\projParFirmeMed}}{{{fr(stats['projects_per_firm']['median'])}}}\n")
        f.write(f"\\newcommand{{\\projParFirmeMoy}}{{{fr(stats['projects_per_firm']['mean'],1)}}}\n")
        f.write(f"\\newcommand{{\\projParFirmeMax}}{{{fr(stats['projects_per_firm']['max'])}}}\n")
        f.write(f"\\newcommand{{\\nMentionsHuitK}}{{{fr(stats['mentions_by_form']['8-K'])}}}\n")
        f.write(f"\\newcommand{{\\nMentionsDixK}}{{{fr(stats['mentions_by_form']['10-K'])}}}\n")
        f.write(f"\\newcommand{{\\nMentionsDixQ}}{{{fr(stats['mentions_by_form']['10-Q'])}}}\n")


if __name__ == "__main__":
    print("DB :", DB)
    fig_entonnoir(); fig_mentions_annee(); fig_types(); fig_secteurs(); fig_heatmap(); fig_statut()
    fig_montants(); fig_entreprises(); fig_tech_loc(); fig_dynamique(); fig_cycle(); fig_sections(); fig_graphe()
    tab_top_projets(); tab_exemples(); globals_stats()
    (HERE / "stats.json").write_text(json.dumps(stats, indent=2, default=str, ensure_ascii=False))
    print(json.dumps(stats, indent=2, default=str, ensure_ascii=False))