"""
guichetoi.cms
=============
Fill the GuichetOI CMS IMMO 9 BANBOU spreadsheet from a `Verdict` produced
by `RecommendationEngine.evaluate_files(...)`.

Follows the consigne deck "Consignes AGILIS PAR de créations des IMB immo
neuf" (Marylène Sevre, 14/01/2026):
  - Onglet « création IMB »   → one row per IMB to create
  - Onglet « création syndic » → only for COLLECTIF projects (≥3 R els or
                                 ≥1 P els)
  - DLPI < 6 mois → push to today + 6 months
  - PreEquipe table (slide 14): PC=O / PA=N / DP=O for collectif; N for PIM
  - Détection table (slide 13): based on R/P logement counts + AU type
  - Zone Nouvelle = "Guichet Accueil OI" (fixed, do not modify)

Fields the engine extracts feed directly; fields that require external
systems (XY coords from Géoréso, Mondofi ref, IMB code, Siret of MOA …)
are intentionally left blank for the consultant to complete.

Returns the path to the saved xlsx.
"""
from __future__ import annotations

import re
import shutil
from datetime import datetime, timedelta
from pathlib import Path
from typing import Any

from openpyxl import load_workbook


# ────────────────────────────────────────────────────────────────────────────
# Domain logic — derived from the consigne deck
# ────────────────────────────────────────────────────────────────────────────
def _to_int(s: Any) -> int:
    if s is None:
        return 0
    try:
        return int(re.sub(r"[^\d]", "", str(s)) or 0)
    except (ValueError, TypeError):
        return 0


def parse_french_address(addr: str) -> dict:
    """
    Split a French postal address into (numero, complement, voie, cp_ville).

    Handles patterns like:
      "10 rue de Cotalard, 44240 La Chapelle-sur-Erdre."
      "350 BIS AVENUE J R G GAUTIER, 13290 AIX EN PROVENCE"
      "rue du Saint Blaise"                (no number, no postal — voie only)
    """
    if not addr:
        return {}
    addr = re.sub(r"\s+", " ", addr).strip().rstrip(".,;")
    m = re.match(
        r"^\s*(?P<num>\d+)\s*"
        r"(?P<comp>BIS|TER|QUATER|QUINQUIES)?\s+"
        r"(?P<voie>.+?)"
        r"(?:[,\s]+(?P<cp>\d{5})\s+(?P<ville>.+))?$",
        addr, re.IGNORECASE,
    )
    if m:
        out = {
            "numero": m.group("num"),
            "complement": (m.group("comp") or "").upper(),
            "voie": m.group("voie").strip().rstrip(",."),
        }
        if m.group("cp"):
            out["cp_ville"] = f"{m.group('cp')} {m.group('ville').strip().rstrip('.')}"
        return out
    return {"voie": addr}


def adjust_dlpi(dlpi_str: str) -> str:
    """
    Per consigne (slide 12): if the DLPI on the fiche is less than 6 months
    from today, push it to today + 6 months. Otherwise keep as-is. Output
    formatted JJ/MM/AAAA without spaces.
    """
    if not dlpi_str:
        return ""
    cleaned = re.sub(r"\s+", "", dlpi_str)
    d = None
    for fmt in ("%d/%m/%Y", "%d/%m/%y", "%d-%m-%Y", "%Y-%m-%d"):
        try:
            d = datetime.strptime(cleaned, fmt)
            break
        except ValueError:
            continue
    if d is None:
        return dlpi_str  # leave untouched if we can't parse
    threshold = datetime.now() + timedelta(days=180)
    if d < threshold:
        d = threshold
    return d.strftime("%d/%m/%Y")


def detect_au_type(ref: str) -> str:
    """Extract the AU type prefix (PC / PA / DP / CU) from a urbanism ref."""
    if not ref:
        return ""
    m = re.match(r"^\s*(PC|PA|DP|CU)(?:\s|\d|$)", ref.upper())
    return m.group(1) if m else ""


def compute_type_site(nb_res: int, nb_pro: int) -> str:
    """
    Slide 7. S = single house (1 or 2 R els). C = collectif (1+ P el, or
    3+ R els). Defaults to S for empty inputs.
    """
    if nb_pro >= 1:
        return "C"
    if nb_res >= 3:
        return "C"
    return "S"


def compute_project_type(nb_res: int, nb_pro: int) -> str:
    """Heuristic: small residential ≤2 R is PIM; everything else COLLECTIF."""
    return "PIM" if (nb_pro == 0 and nb_res <= 2) else "COLLECTIF"


def compute_pre_equipe(type_au: str, project_type: str) -> str:
    """
    Slide 14 table. O for Collectif PC and DP; N for Collectif PA and any
    PIM project.
    """
    if project_type == "PIM":
        return "N"
    if type_au in ("PC", "DP"):
        return "O"
    if type_au == "PA":
        return "N"
    return ""


# Detection codes used by the IMMO9 system (column G of Feuil1)
DETECTION_LABEL_TO_CODE: dict[str, int] = {
    "RAMI Fibre":                 9,
    "RAMI Fibre avec extension":  14,
    "Zlin 0% cuivre":             2,
    "ZLIN ProPur":                5,
    "MixteProL fibre":            17,
}


def compute_detection(
    nb_res: int, nb_pro: int, type_au: str, project_type: str
) -> str:
    """
    Slide 13 table. Returns a detection label whose code can be looked up
    in DETECTION_LABEL_TO_CODE.
    """
    total = nb_res + nb_pro
    # Special case: DP "lot individuel adduction sur rue" → MixteProL
    # Heuristic flag: DP + PIM-sized → MixteProL fibre
    if type_au == "DP" and project_type == "PIM":
        return "MixteProL fibre"

    if total <= 3:
        # 1 or 2 R, no P → RAMI Fibre
        if nb_pro == 0 and nb_res in (1, 2):
            return "RAMI Fibre"
        return "MixteProL fibre"

    # > 3 els
    if nb_pro == 0:
        return "Zlin 0% cuivre"
    if nb_res == 0:
        return "ZLIN ProPur"
    if nb_res >= nb_pro:
        return "Zlin 0% cuivre"
    return "ZLIN ProPur"


# ────────────────────────────────────────────────────────────────────────────
# Verdict → CMS mapping
# ────────────────────────────────────────────────────────────────────────────
def _field(d: dict, key: str) -> str:
    payload = d.get(key)
    if not payload:
        return ""
    return str(payload.get("value") or "").strip()


def _extract_pf_code(documents: list[dict]) -> str:
    """Pull the PF reference (Dossier ASOEIE) from any document filename."""
    for d in documents:
        m = re.search(r"PF\d{10,15}", d.get("file", ""), re.IGNORECASE)
        if m:
            return m.group(0).upper()
    return ""


def _pick_address(verdict: dict) -> str:
    """
    Per consigne (slide 6/31): prefer the address on the Certificat
    d'adressage when present; fall back to the fiche; then to ANY
    document that carries one (Autorisation, Mandat sometimes have the
    building address in their body and the model picks it up).
    """
    docs = verdict.get("documents", []) or []

    # 1. Certificat first (the consigne's preferred source)
    for d in docs:
        if d.get("doc_class") == "Certificat":
            v = _field(d.get("fields", {}), "Batiment_Adresse")
            if v:
                return v

    # 2. Fiche summary (rolled-up across all fiche pages)
    v = _field(verdict.get("fiche_summary", {}), "Batiment_Adresse")
    if v:
        return v

    # 3. Last resort: any other document carrying a Batiment_Adresse
    for d in docs:
        v = _field(d.get("fields", {}), "Batiment_Adresse")
        if v:
            return v

    return ""


def _pick_mandat_fields(verdict: dict) -> dict:
    """Find representative info from a Mandat doc, or fall back to fiche."""
    out = {"nom": "", "email": "", "tel": ""}
    for d in verdict.get("documents", []):
        if d.get("doc_class") == "Mandat":
            f = d.get("fields", {})
            out["nom"]   = _field(f, "Representant_Nom_Complet")
            out["email"] = _field(f, "Representant_Email")
            out["tel"]   = _field(f, "Representant_Telephone")
            if any(out.values()):
                return out
    f = verdict.get("fiche_summary", {})
    out["nom"]   = _field(f, "Representant_Nom_Complet")
    out["email"] = _field(f, "Representant_Email")
    out["tel"]   = _field(f, "Representant_Telephone")
    return out


def _split_name(full: str) -> tuple[str, str]:
    """Heuristic: 'FAURE Mael' → ('FAURE', 'Mael'). 'Mr. BRECHBIEHL Vivien' too."""
    s = re.sub(r"^\s*(M(?:r|me|lle|onsieur|adame)?\.?\s+)", "", full or "", flags=re.IGNORECASE).strip()
    parts = s.split()
    if len(parts) >= 2:
        # Convention: UPPERCASE part = NOM, others = prénom
        uppers = [w for w in parts if w.isupper()]
        if uppers:
            nom = " ".join(uppers)
            prenom = " ".join(w for w in parts if w not in uppers)
            return nom, prenom
        return parts[0], " ".join(parts[1:])
    return s, ""


# ────────────────────────────────────────────────────────────────────────────
# Sheet writer
# ────────────────────────────────────────────────────────────────────────────
# Row 1: section title (merged), Row 2: column codes, Row 3: descriptions
# Data starts at Row 4.
_DATA_ROW = 4


def _sheet(wb: Any, contains: str) -> Any:
    """Find the sheet whose name contains a substring (case/diacritic-insensitive)."""
    def norm(s: str) -> str:
        return (s.lower()
                  .replace("é", "e").replace("è", "e").replace("ê", "e")
                  .replace("à", "a").replace("ô", "o").replace("ç", "c"))
    target = norm(contains)
    for n in wb.sheetnames:
        if target in norm(n):
            return wb[n]
    raise KeyError(f"No sheet matching {contains!r} in {wb.sheetnames}")


def fill_cms(
    verdict: dict,
    output_path: Path,
    template_path: Path | None = None,
) -> dict:
    """
    Generate a filled CMS xlsx from a verdict dict.

    Returns a dict describing what was filled and what still needs the
    consultant's attention:

        {
            "output_path":          "<path to the saved xlsx>",
            "project_type":         "PIM" | "COLLECTIF",
            "missing_extractions":  [list of human-readable field names that
                                     SHOULD have been auto-filled but couldn't
                                     because the model/OCR didn't extract them],
            "manual_lookup":        [list of fields that always require a
                                     manual step — XY from Géoréso, Siret,
                                     Mondofi ref, etc.],
        }

    The xlsx is always written. The consultant uses the two lists to know
    which cells need a manual pass before submitting the CMS to Banbou.
    """
    if template_path is None:
        # src/guichetoi/cms.py → parents[2] = repo root → repo_root/assets/
        template_path = Path(__file__).resolve().parents[2] / "assets" / "cms_template.xlsx"
    if not template_path.exists():
        raise FileNotFoundError(f"CMS template not found: {template_path}")

    output_path = Path(output_path)
    output_path.parent.mkdir(parents=True, exist_ok=True)
    shutil.copy(template_path, output_path)

    # ── Gather inputs from the verdict ────────────────────────────────────
    fiche = verdict.get("fiche_summary", {}) or {}
    documents = verdict.get("documents", []) or []

    ref_au   = _field(fiche, "Reference_Urbanisme")
    dlpi_raw = _field(fiche, "DLPI")
    nb_total = _to_int(_field(fiche, "nb_log_totale"))
    nb_pro   = _to_int(_field(fiche, "Nb_log_pro"))
    nb_res   = _to_int(_field(fiche, "Nb_log_res"))
    if nb_res == 0 and nb_pro == 0 and nb_total > 0:
        # Convention: when only total is known, treat all as residential
        nb_res = nb_total

    pf_code  = _extract_pf_code(documents)
    addr_raw = _pick_address(verdict)
    addr     = parse_french_address(addr_raw)

    type_au   = detect_au_type(ref_au)
    proj_type = compute_project_type(nb_res, nb_pro)
    type_site = compute_type_site(nb_res, nb_pro)
    pre_eq    = compute_pre_equipe(type_au, proj_type)
    detection_lbl  = compute_detection(nb_res, nb_pro, type_au, proj_type)
    detection_code = DETECTION_LABEL_TO_CODE.get(detection_lbl, "")

    dlpi_out = adjust_dlpi(dlpi_raw)

    # ── Track what's missing or always-manual for the consultant ──────────
    missing_extractions: list[str] = []
    manual_lookup: list[str] = []

    # Things we WANTED to auto-fill but couldn't (extraction gap)
    if not ref_au:
        missing_extractions.append("Référence d'urbanisme (PermisConstruire) — colonne 13")
    if not pf_code:
        missing_extractions.append("Référence PF Agilis (DossierASOEIE) — colonne 14")
    if not dlpi_out:
        missing_extractions.append("Date de livraison du projet (DLPI) — colonne 15")
    if (nb_res + nb_pro) == 0:
        missing_extractions.append("Nombre de logements résidentiels / professionnels — colonnes 11-12")
    if not addr.get("numero"):
        missing_extractions.append("Numéro de voie — colonne 5")
    if not addr.get("voie"):
        missing_extractions.append("Nom de la voie — colonne 7")
    if not addr.get("cp_ville"):
        missing_extractions.append("Code postal et Commune — colonne 10")

    # Things that ALWAYS require a manual step (never come from the documents)
    manual_lookup.append(
        "Coordonnées XY + Projection (cols 2-4) — à récupérer dans Géoréso "
        "en fonction du territoire (Métropole / DOM-TOM)"
    )
    manual_lookup.append(
        "Bâtiment (col 8) — uniquement si plusieurs bâtiments sur le projet"
    )
    manual_lookup.append(
        "Présence DTA (col 22) — à renseigner par le consultant"
    )
    manual_lookup.append(
        "Identifiant Processus Mondofi (cols 18-19) — uniquement pour les dossiers OCC"
    )

    # ── Write to "création IMB" sheet ─────────────────────────────────────
    wb = load_workbook(output_path)
    ws = _sheet(wb, "creation imb")
    r = _DATA_ROW

    ws.cell(row=r, column=1,  value=type_site)
    # CoordX/Y/Projection (2,3,4): blank — to be filled from Géoréso manually
    if addr.get("numero"):     ws.cell(row=r, column=5,  value=addr["numero"])
    if addr.get("complement"): ws.cell(row=r, column=6,  value=addr["complement"])
    if addr.get("voie"):       ws.cell(row=r, column=7,  value=addr["voie"])
    # Batiment (8): leave blank unless multi-bldg detected
    ws.cell(row=r, column=9,  value="Guichet Accueil OI")
    if addr.get("cp_ville"):   ws.cell(row=r, column=10, value=addr["cp_ville"])
    if nb_res:                 ws.cell(row=r, column=11, value=nb_res)
    if nb_pro:                 ws.cell(row=r, column=12, value=nb_pro)
    if ref_au:                 ws.cell(row=r, column=13, value=ref_au)
    if pf_code:                ws.cell(row=r, column=14, value=pf_code)
    if dlpi_out:               ws.cell(row=r, column=15, value=dlpi_out)
    if detection_code:         ws.cell(row=r, column=16, value=detection_code)
    if pre_eq:                 ws.cell(row=r, column=17, value=pre_eq)
    # Type/Identifiant Processus (18-20): RAMI/MPL only, left blank
    # Typologie (21) — default OSA = 13. If filename hints at RIP, set 57.
    ws.cell(row=r, column=21, value=13)
    # PresenceDta (22), Commentaire Faisabilite (23-24): blank, manual

    comment_bits = [
        "Pré-rempli automatiquement (GuichetOI-ML)",
        f"Projet {proj_type} · Type site {type_site} · Détection {detection_lbl}",
        "À compléter manuellement : coordonnées XY (Géoréso), Identifiant Processus (Mondofi pour OCC)",
    ]
    ws.cell(row=r, column=25, value=" — ".join(comment_bits))

    # ── Onglet "création syndic" — clear the template's example row in
    # both cases, then fill it for COLLECTIF projects only (slides 16-17).
    # openpyxl's `cell(row, col, value=None)` is a no-op (the None default is
    # ignored), so we must set `.value = None` on the cell object directly.
    wss = _sheet(wb, "creation syndic")
    sr = _DATA_ROW
    for col in range(1, wss.max_column + 1):
        wss.cell(row=sr, column=col).value = None

    if proj_type == "COLLECTIF":
        cabinet = _field(fiche, "cabinet_conseil")
        mandat = _pick_mandat_fields(verdict)
        nom, prenom = _split_name(mandat["nom"]) if mandat["nom"] else ("", "")

        if cabinet:               wss.cell(row=sr, column=1,  value=cabinet)
        if addr.get("numero"):    wss.cell(row=sr, column=2,  value=addr["numero"])
        if addr.get("complement"):wss.cell(row=sr, column=3,  value=addr["complement"])
        if addr.get("voie"):      wss.cell(row=sr, column=4,  value=addr["voie"])
        if addr.get("cp_ville"):  wss.cell(row=sr, column=5,  value=addr["cp_ville"])
        # Siret (6): never extracted from the documents
        if nom:                   wss.cell(row=sr, column=7,  value=nom)
        if prenom:                wss.cell(row=sr, column=8,  value=prenom)
        if mandat["tel"]:         wss.cell(row=sr, column=9,  value=mandat["tel"])
        if mandat["email"]:       wss.cell(row=sr, column=10, value=mandat["email"])
        wss.cell(row=sr, column=11, value=18)  # 18 = Promoteur (default)

        # Track syndic-side extraction gaps for the consultant
        if not cabinet:
            missing_extractions.append(
                "Onglet Syndic · Raison sociale (Cabinet conseil) — colonne 1"
            )
        if not nom:
            missing_extractions.append(
                "Onglet Syndic · Nom du responsable — colonne 7"
            )
        if not prenom:
            missing_extractions.append(
                "Onglet Syndic · Prénom du responsable — colonne 8"
            )
        if not mandat["tel"]:
            missing_extractions.append(
                "Onglet Syndic · N° mobile — colonne 9"
            )
        if not mandat["email"]:
            missing_extractions.append(
                "Onglet Syndic · Email — colonne 10"
            )
        manual_lookup.append(
            "Onglet Syndic · N° SIRET (14 chiffres) — colonne 6"
        )

    wb.save(output_path)

    return {
        "output_path":         str(output_path),
        "project_type":        proj_type,
        "missing_extractions": missing_extractions,
        "manual_lookup":       manual_lookup,
    }


# ────────────────────────────────────────────────────────────────────────────
# Convenience helpers used by the Streamlit demo
# ────────────────────────────────────────────────────────────────────────────
def is_cms_eligible(verdict: dict) -> bool:
    """CMS is generated only when the demande is complète (with or without manual review)."""
    return (verdict.get("status") or "").startswith("complèt")


def summarise_cms_fields(verdict: dict) -> dict:
    """
    Pre-compute the derived values the Streamlit UI can show as a preview
    before the user downloads the xlsx.
    """
    fiche = verdict.get("fiche_summary", {}) or {}
    nb_total = _to_int(_field(fiche, "nb_log_totale"))
    nb_pro   = _to_int(_field(fiche, "Nb_log_pro"))
    nb_res   = _to_int(_field(fiche, "Nb_log_res"))
    if nb_res == 0 and nb_pro == 0 and nb_total > 0:
        nb_res = nb_total

    ref_au = _field(fiche, "Reference_Urbanisme")
    type_au   = detect_au_type(ref_au)
    proj_type = compute_project_type(nb_res, nb_pro)
    return {
        "Projet":        proj_type,
        "Type AU":       type_au or "?",
        "Type Site":     compute_type_site(nb_res, nb_pro),
        "Nb logements R": nb_res,
        "Nb logements P": nb_pro,
        "Détection":     compute_detection(nb_res, nb_pro, type_au, proj_type),
        "Pré-équipé":    compute_pre_equipe(type_au, proj_type),
        "Référence AU":  ref_au or "—",
        "PF Agilis":     _extract_pf_code(verdict.get("documents", [])) or "—",
        "DLPI (ajustée)": adjust_dlpi(_field(fiche, "DLPI")) or "—",
        "Adresse":       _pick_address(verdict) or "—",
    }