#!/usr/bin/env python3
"""Convertit la feuille CLIENTS de l'Excel de référence en JSON pour app:clients:import.

Entrée : 2026-Sensipode-clients.xlsx (feuille unique CLIENTS, mapping par position A→Z).
Sortie : config/fixtures/clients_from_excel.json (gitignoré).

Nettoyage de la référence appliqué ICI (pas dans le binaire, pour rejouer sur un ré-export) :
  1. AIA Architectes (#69) : la ligne dont le représentant est « BAUMELOU » est un doublon → supprimée
     (on garde celle de « PAKEY »).
  2. NANTES UNIVERSITÉ générique #19 (SIREN 194 409 843) → establishment « Pôle culture numérique ».
  3. NANTES UNIVERSITÉ #83 (SIREN 130 029 747) → establishment « UNIPAIX ».

Établissement (deux colonnes name/establishment) :
  - rempli automatiquement pour les familles partageant un même n° (le n° = le groupe) : le nom est
    coupé au PREMIER « - » → base + établissement (ex. #45 NANTES MÉTROPOLE - Énergie, #19 - SIO) ;
  - forcé manuellement pour les 2 collisions inter-n° ci-dessus ;
  - vide pour tout client à ligne unique (pas de split mécanique des tirets internes).

Sans dépendance : un .xlsx est un zip de XML, lu avec la stdlib.

Usage : python3 scripts/clients_convert.py <clients.xlsx> [sortie.json]
"""
from __future__ import annotations

import json
import re
import sys
import zipfile
from collections import defaultdict
from xml.etree import ElementTree as ET

NS = "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}"

# Colonne Excel -> champ JSON (mapping par position, feuille CLIENTS).
FIELD_COLS = {
    "C": "type", "D": "sector", "E": "legalForm", "F": "apeCode", "G": "vatNumber",
    "H": "siret", "I": "siren", "J": "headOfficeStreet", "K": "headOfficePostalCity",
    "L": "legalRepresentative", "M": "legalRepresentativeRole",
    "T": "billingReminderEmail", "U": "billingEmail", "V": "billingStreet", "W": "billingPostalCity",
    "X": "platformUrl", "Y": "issueNote", "Z": "specifics",
}
# Colonne Excel -> rôle ClientContact (une cellule peut contenir plusieurs noms, un par ligne).
CONTACT_COLS = {
    "N": "chef_projet_client", "O": "contact_1", "P": "contact_2",
    "Q": "contact_3", "R": "responsable_achat", "S": "compta",
}


def load_shared_strings(z: zipfile.ZipFile) -> list[str]:
    try:
        root = ET.fromstring(z.read("xl/sharedStrings.xml"))
    except KeyError:
        return []
    out = []
    for si in root.findall(f"{NS}si"):
        out.append("".join(t.text or "" for t in si.iter(f"{NS}t")))
    return out


def cell_value(c, ss: list[str]) -> str:
    v = c.find(f"{NS}v")
    if v is None:
        return ""
    if c.get("t") == "s":
        return ss[int(v.text)]
    return v.text or ""


def read_rows(path: str) -> list[dict]:
    z = zipfile.ZipFile(path)
    ss = load_shared_strings(z)
    # première feuille
    root = ET.fromstring(z.read("xl/worksheets/sheet1.xml"))
    rows = []
    for row in root.findall(f"{NS}sheetData/{NS}row")[1:]:  # saute l'en-tête
        d = {}
        for c in row.findall(f"{NS}c"):
            col = re.match(r"[A-Z]+", c.get("r")).group(0)
            d[col] = cell_value(c, ss).strip()
        if (d.get("B") or "").strip():  # ligne avec un nom de client
            rows.append(d)
    return rows


def clean(value: str) -> str | None:
    value = (value or "").strip()
    return value or None


def split_names(cell: str) -> list[str]:
    return [p.strip() for p in (cell or "").replace("\r", "\n").split("\n") if p.strip()]


def build(rows: list[dict]) -> list[dict]:
    # 1) règle AIA : supprimer le doublon « BAUMELOU ».
    kept = []
    for d in rows:
        if d.get("B", "").strip().upper().startswith("AIA ARCHITECTES") and "BAUMELOU" in d.get("L", "").upper():
            continue
        kept.append(d)

    # 2) établissement auto pour les n° partagés (familles).
    by_num = defaultdict(list)
    for d in kept:
        by_num[d.get("A", "").strip()].append(d)

    clients = []
    for d in kept:
        num = d.get("A", "").strip()
        name = d.get("B", "").strip()
        establishment = ""
        family = [x for x in by_num.get(num, []) if num]
        if len(family) > 1 and " - " in name:
            base, _, suffix = name.partition(" - ")
            name, establishment = base.strip(), suffix.strip()

        # 3) règles manuelles inter-n° (priment sur l'auto).
        if num == "19" and name.upper() == "NANTES UNIVERSITÉ" and establishment == "":
            establishment = "Pôle culture numérique"
        if num == "83" and name.upper() == "NANTES UNIVERSITÉ":
            establishment = "UNIPAIX"

        contacts = []
        for col, role in CONTACT_COLS.items():
            for person in split_names(d.get(col, "")):
                contacts.append({"role": role, "name": person})

        client = {
            "number": int(num) if num.isdigit() else None,
            "name": name,
            "establishment": establishment,
        }
        for col, field in FIELD_COLS.items():
            client[field] = clean(d.get(col, ""))
        client["contacts"] = contacts
        clients.append(client)

    return clients


def main() -> int:
    if len(sys.argv) < 2:
        print(__doc__)
        return 1
    src = sys.argv[1]
    out = sys.argv[2] if len(sys.argv) > 2 else "config/fixtures/clients_from_excel.json"
    clients = build(read_rows(src))

    # garde-fou : couple (name, establishment) unique.
    seen = {}
    collisions = []
    for c in clients:
        key = (c["name"].upper(), c["establishment"].upper())
        if key in seen:
            collisions.append(f'{c["name"]} / {c["establishment"] or "∅"}')
        seen[key] = True
    with open(out, "w", encoding="utf-8") as f:
        json.dump({"clients": clients}, f, ensure_ascii=False, indent=2)
    print(f"{len(clients)} clients -> {out}")
    if collisions:
        print("  ⚠ COLLISIONS de couple (name, establishment) :", collisions)
    else:
        print("  OK : tous les couples (name, establishment) sont uniques.")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())