# QC Élection Forecast — Plateforme de prévision électorale du Québec 2026
# Auteur : Simon-Pierre Boucher
# Contact : contact@spboucher.ai
# https://www.qc-election.com
"""Feature store démographique des 127 circonscriptions (Phase D).

SOURCE TIER 1 IDÉALE : Élections Québec publie, pour chacune des 127
circonscriptions 2026, un « Portrait socioéconomique » — produits personnalisés
du Recensement 2021 de Statistique Canada calculés SUR LES LIMITES 2026
(aucune interpolation d'aires de diffusion requise) :
âge × genre, langue parlée à la maison, diplomation, revenu des ménages,
immigration, identité autochtone, minorités visibles, type de logement.

Sorties :
  * data/population/riding_2026_demographics.csv — 1 ligne / circonscription
    (variables clés + z-scores standardisés sur les 127) avec provenance;
  * data/population/riding_similarity.json — top 10 de circonscriptions
    SEMBLABLES par distance cosinus sur les features standardisées (§29 :
    partial pooling, transferts du soir d'élection, comparaisons);
  * data/population/portraits/{slug}.json — détail complet par circonscription
    (fiches locales, cellules MRP marginales âge×genre×langue).
"""
from __future__ import annotations

import json
import logging
import re
import unicodedata
from concurrent.futures import ThreadPoolExecutor
from datetime import date

import numpy as np
from sqlalchemy.orm import Session

from ..config import DATA_DIR, settings
from .. import models as Mo

log = logging.getLogger("eq-socio")

POP_DIR = DATA_DIR / "population"
PORTRAIT_URL = ("https://www.electionsquebec.qc.ca/cartes-electorales/"
                "circonscriptions-provinciales/portrait-socioeconomique/{slug}/")

AGE_GROUPS = ["0 à 9", "10 à 19", "20 à 29", "30 à 39", "40 à 49",
              "50 à 59", "60 à 69", "70 à 79", "80 ans et plus"]

# variables retenues pour la standardisation / similarité
Z_FEATURES = ["pct_60_plus", "pct_locataires", "pct_francais", "pct_anglais",
              "pct_universitaire", "pct_revenu_100k_plus", "pct_immigrants",
              "pct_autochtone", "pct_appartements"]


def slug_of(name: str) -> str:
    s = re.sub(r"[–—]", "-", name)          # tirets cadratins AVANT l'ascii-fold
    s = unicodedata.normalize("NFKD", s).encode("ascii", "ignore").decode()
    s = s.replace("'", "").replace("’", "")
    s = re.sub(r"[\s-]+", "-", s).lower()
    return re.sub(r"[^a-z0-9-]", "", s)


def _table_after(md: str, header_kw: str, span: int = 2600) -> dict[str, float]:
    """Première table markdown après un titre contenant `header_kw` →
    {libellé: valeur de la circonscription (1re colonne de données)}."""
    i = md.lower().find(header_kw.lower())
    if i < 0:
        return {}
    window = md[i:i + span]
    out = {}
    for m in re.finditer(r"^\|\s*([^|]{2,80}?)\s*\|\s*([\d\s ,.$%]+?)\s*\|", window, re.M):
        label = m.group(1).strip().strip("*")
        raw = m.group(2).replace("%", "").replace("$", "")
        raw = re.sub(r"[\s ]", "", raw).replace(",", ".")
        try:
            v = float(raw)
        except ValueError:
            continue
        # les tables successives réutilisent les mêmes libellés (âge en %,
        # puis effectifs Hommes/Femmes, puis Province) : la PREMIÈRE table
        # après le titre fait foi — jamais d'écrasement.
        if label not in out:
            out[label] = v
    return out


def _riding_population(md: str, name: str) -> float | None:
    """Population = total Hommes + Femmes du bloc de LA circonscription
    (le bloc « Province » suit — on s'arrête avant)."""
    i = md.find("Répartition des femmes et des hommes selon l'âge")
    if i < 0:
        return None
    block = md[i:i + 2600]
    j = block.find("**Province**")
    if j > 0:
        block = block[:j]
    m = re.search(r"\|\s*\*\*Total\*\*\s*\|\s*([\d\s , ]+)\|\s*([\d\s , ]+)\|",
                  block)
    if not m:
        return None
    def n(s):
        return float(re.sub(r"[^\d]", "", s) or 0)
    return n(m.group(1)) + n(m.group(2))


def parse_portrait(md: str, name: str = "") -> dict | None:
    """Markdown du portrait → variables clés (avec les tables brutes utiles)."""
    ages = _table_after(md, "Population totale selon les groupes d'âge", 1000)
    ages = {k: v for k, v in ages.items()
            if any(k.startswith(g) for g in AGE_GROUPS) and v <= 100}
    if len(ages) < 7:
        return None
    pop_total = _riding_population(md, name)
    langue = _table_after(md, "langue la plus souvent parlée", 900)
    diplo = _table_after(md, "plus haut diplôme ou grade", 2200)
    revenu = _table_after(md, "Revenu des ménages", 2600)
    immig = _table_after(md, "Citoyenneté et immigration", 1600)
    autoch = _table_after(md, "identité autochtone", 700)
    logement = _table_after(md, "type de construction résidentielle", 1800)
    minor = _table_after(md, "Minorités visibles", 900)

    def find(d, *kws):
        for k, v in d.items():
            if all(kw.lower() in k.lower() for kw in kws):
                return v
        return None

    g60 = sum(v for k, v in ages.items()
              if k.startswith(("60 à 69", "70 à 79", "80 ans")))
    g2039 = sum(v for k, v in ages.items() if k.startswith(("20 à 29", "30 à 39")))
    uni = find(diplo, "universitaire") or find(diplo, "baccalauréat")
    rev100 = sum(v for k, v in revenu.items()
                 if re.match(r"(100 000|125 000|150 000|200 000)", k))
    return {
        "population": pop_total,
        "age_groups_pct": ages,
        "pct_60_plus": round(g60, 1),
        "pct_20_39": round(g2039, 1),
        "pct_francais": find(langue, "français"),
        "pct_anglais": find(langue, "anglais"),
        "pct_universitaire": uni,
        "pct_revenu_100k_plus": round(rev100, 1) if revenu else None,
        "pct_immigrants": find(immig, "immigrant"),
        "pct_autochtone": find(autoch, "autochtone"),
        "pct_minorites_visibles": find(minor, "minorit") or find(minor, "total"),
        "pct_appartements": find(logement, "appartement") or
                            find(logement, "cinq étages"),
        "tables": {"langue": langue, "revenu": revenu, "diplome": diplo,
                   "logement": logement},
    }


XLS_URL = ("https://docs.electionsquebec.qc.ca/PRO/6a58edde4eab9/"
           "statistiques-recensement-2021-CEP2026.xls")
XLS_PATH = POP_DIR / "statistiques-recensement-2021-CEP2026.xls"

# Codes de variables stables du classeur officiel (feuille « 127 CEP 2026  »)
XLS_CODES = {
    "population": "TAB1CH_0",
    "pct_65_plus": "TAB1CH_20b",
    "pct_revenu_100k_plus": "TAB2C2247_REV",
    "pct_autochtone": "TAB1CH_lan_N_9",
    "pct_locataires": "TAB2C2515a",
    "n_immigrants": "TAB2C18",
    "n_immigration_total": "TAB2C16",
    "n_uni_bacc_plus": "TAB3C706",
    "pct_fr_maison": "TAB1CH_580a",   # langue la plus parlée à la MAISON
    "pct_en_maison": "TAB1CH_579a",
    "n_app_duplex": "TAB1CH_209",
    "n_app_moins5": "TAB1CH_210",
    "n_app_5plus": "TAB1CH_211",
    "n_logements_occ": "TAB1CH_0b",
}


def build_from_xls(db: Session) -> dict:
    """SOURCE PRINCIPALE : le classeur officiel d'Élections Québec
    (Recensement 2021, 2 917 variables × 127 circonscriptions 2026, codes
    stables) — déterministe, complet, sans scraping."""
    import httpx
    import pandas as pd
    POP_DIR.mkdir(parents=True, exist_ok=True)
    if not XLS_PATH.exists():
        r = httpx.get(XLS_URL, timeout=180, follow_redirects=True)
        r.raise_for_status()
        XLS_PATH.write_bytes(r.content)
    raw = pd.ExcelFile(XLS_PATH).parse("127 CEP 2026 ", header=None)
    # noms de circonscription sur deux lignes (L1 + L2)
    names = []
    for c in range(3, raw.shape[1]):
        l1 = str(raw.iat[1, c]) if pd.notna(raw.iat[1, c]) else ""
        l2 = str(raw.iat[2, c]) if pd.notna(raw.iat[2, c]) else ""
        names.append((l1 + l2).strip())
    code_row = {str(raw.iat[i, 0]).strip(): i for i in range(raw.shape[0])
                if pd.notna(raw.iat[i, 0])}
    # % minorités visibles : repérée par libellé (code variable selon éditions)
    minor_row = next((i for i in range(raw.shape[0])
                      if pd.notna(raw.iat[i, 1]) and "total des minorités visibles"
                      in str(raw.iat[i, 1]).lower()
                      and "pourcentage" in str(raw.iat[i, 1]).lower()), None)
    uni_denom_row = next((i for i in range(raw.shape[0])
                          if pd.notna(raw.iat[i, 1]) and
                          "15 ans et plus dans les ménages privés selon le plus haut"
                          in str(raw.iat[i, 1])), None)

    def val(code_or_row, col):
        i = code_row.get(code_or_row) if isinstance(code_or_row, str) else code_or_row
        if i is None:
            return None
        v = raw.iat[i, col]
        try:
            return float(v)
        except (TypeError, ValueError):
            return None

    # alignement noms XLS ↔ noms de la base (accents/tirets)
    db_names = {slug_of(d.name): d.name for d in db.query(Mo.District).all()}
    rows = []
    for k, xls_name in enumerate(names):
        col = 3 + k
        name = db_names.get(slug_of(xls_name))
        if name is None:
            log.warning("circonscription XLS non appariée: %s", xls_name)
            continue
        cv = lambda k: val(XLS_CODES[k], col)
        pop = cv("population")
        apps = sum((cv(k2) or 0.0)
                   for k2 in ("n_app_duplex", "n_app_moins5", "n_app_5plus"))
        log_tot = cv("n_logements_occ") or 1.0
        imm_tot = cv("n_immigration_total") or 1.0
        uni_den = val(uni_denom_row, col) or 1.0
        pct = lambda x: round(x * 100, 1) if x is not None else None
        rows.append({
            "district": name, "population": pop,
            "pct_60_plus": pct(cv("pct_65_plus")),   # 65+ (libellé officiel)
            "pct_francais": pct(cv("pct_fr_maison")),
            "pct_anglais": pct(cv("pct_en_maison")),
            "pct_universitaire": round((cv("n_uni_bacc_plus") or 0)
                                       / uni_den * 100, 1),
            "pct_revenu_100k_plus": pct(cv("pct_revenu_100k_plus")),
            "pct_immigrants": round((cv("n_immigrants") or 0)
                                    / imm_tot * 100, 1),
            "pct_autochtone": pct(cv("pct_autochtone")),
            "pct_locataires": pct(cv("pct_locataires")),
            "pct_appartements": round(apps / log_tot * 100, 1),
            "pct_minorites_visibles": pct(val(minor_row, col)),
            "source_url": XLS_URL,
            "source": ("Élections Québec — Statistiques du Recensement 2021 "
                       "par circonscription 2026 (classeur officiel)"),
            "harvested": date.today().isoformat(),
        })
    (POP_DIR / "portraits").mkdir(parents=True, exist_ok=True)
    for r in rows:
        (POP_DIR / "portraits" / f"{slug_of(r['district'])}.json").write_text(
            json.dumps(r, ensure_ascii=False))
    out = build_feature_store(db)
    return {"circonscriptions_xls": len(rows), **out}


def harvest(db: Session, max_workers: int = 4,
            refresh: bool = False) -> dict:
    """Moissonne les 127 portraits (idempotent : saute les JSON déjà présents)."""
    from .firecrawl_watch import _enabled, _post
    if not _enabled():
        return {"skipped": "Firecrawl requis"}
    (POP_DIR / "portraits").mkdir(parents=True, exist_ok=True)
    names = [d.name for d in db.query(Mo.District).all()]

    def one(name: str) -> tuple[str, str]:
        slug = slug_of(name)
        f = POP_DIR / "portraits" / f"{slug}.json"
        if f.exists() and not refresh:
            return name, "cache"
        data = _post("/scrape", {"url": PORTRAIT_URL.format(slug=slug),
                                 "formats": ["markdown"]}, timeout=90.0)
        md = ((data or {}).get("data") or {}).get("markdown") or ""
        parsed = parse_portrait(md, name) if md else None
        if parsed is None:
            return name, "échec"
        parsed["markdown"] = md[:60000]   # conservé pour re-parse sans réseau
        parsed["district"] = name
        parsed["source_url"] = PORTRAIT_URL.format(slug=slug)
        parsed["source"] = ("Élections Québec — portrait socioéconomique "
                            "(Recensement 2021, limites 2026)")
        parsed["harvested"] = date.today().isoformat()
        f.write_text(json.dumps(parsed, ensure_ascii=False))
        return name, "ok"

    results: dict[str, int] = {"ok": 0, "cache": 0, "échec": 0}
    failed = []
    with ThreadPoolExecutor(max_workers=max_workers) as ex:
        for name, status in ex.map(one, names):
            results[status] += 1
            if status == "échec":
                failed.append(name)
    build_feature_store(db)
    return {**results, "échecs": failed[:10]}


def build_feature_store(db: Session) -> dict:
    """Portraits JSON → CSV standardisé + graphe de similarité (top 10)."""
    import pandas as pd
    rows = []
    for f in sorted((POP_DIR / "portraits").glob("*.json")):
        d = json.loads(f.read_text())
        rows.append({k: d.get(k) for k in
                     ["district", "population", *Z_FEATURES,
                      "pct_minorites_visibles", "source_url", "harvested"]})
    if len(rows) < 60:
        return {"skipped": f"seulement {len(rows)} portraits"}
    df = pd.DataFrame(rows)
    for c in Z_FEATURES:
        col = pd.to_numeric(df[c], errors="coerce")
        mu, sd = col.mean(), col.std() or 1.0
        df[f"z_{c}"] = ((col - mu) / sd).round(3)
    POP_DIR.mkdir(parents=True, exist_ok=True)
    df.to_csv(POP_DIR / "riding_2026_demographics.csv", index=False)
    # similarité cosinus sur les z-features (valeurs manquantes → 0)
    Z = df[[f"z_{c}" for c in Z_FEATURES]].fillna(0.0).to_numpy()
    norms = np.linalg.norm(Z, axis=1, keepdims=True)
    norms[norms == 0] = 1.0
    S = (Z / norms) @ (Z / norms).T
    sim = {}
    names = df["district"].tolist()
    for i, n in enumerate(names):
        order = np.argsort(-S[i])
        sim[n] = [{"district": names[j], "similarity": round(float(S[i, j]), 3)}
                  for j in order[1:11]]
    (POP_DIR / "riding_similarity.json").write_text(
        json.dumps(sim, ensure_ascii=False))
    return {"circonscriptions": len(df)}