#!/usr/bin/env python3
"""Convertit l'onglet CONTRATS du fichier facturation en JSON pour app:devis:import.
Réutilise le parsing stdlib de scripts/dump_xlsx.py (un .xlsx = zip de XML)."""
import json, sys, re, datetime
sys.path.insert(0, "scripts")
import dump_xlsx as dx
import zipfile
from xml.etree import ElementTree as ET

NS = dx.NS

POLE_MAP = {
    "STRATÉGIE À IMPACT": "strategie_a_impact",
    "INNOVATION EXPÉRIMENTATION": "innovation_experimentation",
    "UX DESIGN RESPONSABLE": "ux_ui_responsable",
    "DESIGN DES VULNÉRABILITÉS": "design_des_vulnerabilites",
    "FORMATION": "formation",
}

def to_date(v):
    if v is None:
        return None
    s = str(v).strip()
    if s == "":
        return None
    try:  # série Excel
        n = float(s)
        d = datetime.date(1899, 12, 30) + datetime.timedelta(days=int(n))
        return d.isoformat()
    except ValueError:
        pass
    m = re.match(r"^(\d{1,2})/(\d{1,3})/(\d{4})$", s)  # d/m/Y éventuellement malformé
    if m:
        dd, mm, yy = int(m.group(1)), int(m.group(2)), int(m.group(3))
        if 1 <= mm <= 12 and 1 <= dd <= 31:
            try:
                return datetime.date(yy, mm, dd).isoformat()
            except ValueError:
                return None
    return None

def to_float(v):
    try:
        return round(float(str(v).strip()), 2)
    except (ValueError, TypeError):
        return None

def sign_date(v):
    """Date de signature extraite du commentaire : « …signé le JJ/MM/AAAA »."""
    if not v:
        return None
    m = re.search(r"signé\s+le\s+(\d{1,2})/(\d{1,2})/(\d{4})", str(v), re.I)
    if not m:
        return None
    dd, mm, yy = int(m.group(1)), int(m.group(2)), int(m.group(3))
    if 1 <= mm <= 12 and 1 <= dd <= 31:
        try:
            return datetime.date(yy, mm, dd).isoformat()
        except ValueError:
            return None
    return None

def rows(path):
    z = zipfile.ZipFile(path)
    shared = dx.load_shared_strings(z)
    for name, target in dx.sheet_targets(z):
        if name != "CONTRATS":
            continue
        sx = ET.fromstring(z.read(target))
        sd = sx.find(f"{NS}sheetData")
        for row in sd.findall(f"{NS}row"):
            cells = {}
            for c in row.findall(f"{NS}c"):
                ref = c.get("r", ""); t = c.get("t")
                v = c.find(f"{NS}v"); inline = c.find(f"{NS}is")
                if t == "s" and v is not None and v.text is not None:
                    val = shared[int(v.text)]
                elif t == "inlineStr" and inline is not None:
                    val = "".join(x.text or "" for x in inline.iter(f"{NS}t"))
                elif v is not None:
                    val = v.text
                else:
                    val = None
                if val is not None and str(val).strip() != "":
                    cells[dx.col_letter(ref)] = str(val).strip()
            yield cells

def main():
    path = sys.argv[1]
    out = []
    skipped = []
    for cells in rows(path):
        b = cells.get("B", "").strip()
        if not re.match(r"^D\d{5,6}$", b):  # devis D26NNN uniquement
            if cells.get("A") and cells.get("A") != "A":
                skipped.append(cells.get("B") or cells.get("C") or cells.get("D"))
            continue
        a = cells.get("A", "")
        client = re.sub(r"^\s*\d+\s*-\s*", "", a).strip()
        e = cells.get("E", "").strip()
        q = cells.get("Q", "").strip().lower()
        m = cells.get("M", "").strip().lower()
        out.append({
            "number": b,
            "of_number": "OF" + b[1:],
            "client": client or None,
            "name": cells.get("D", "").strip() or b,
            "pole": POLE_MAP.get(e, e.lower() or None),
            "amount_ht_signed": to_float(cells.get("F")),
            "sold_days": to_float(cells.get("L")),
            "starts_at": to_date(cells.get("J")),
            "ends_at": to_date(cells.get("K")),
            "subcontracting": cells.get("G", "").strip().upper() == "OUI",
            "fees_included": cells.get("H", "").strip().upper() == "OUI",
            "status": "signe" if (q.startswith("oui") or m == "oui") else "envoye",
            "contract_number": cells.get("C", "").strip() or None,
            "signed_at": sign_date(cells.get("R")),
            "notes": cells.get("R", "").strip() or None,
        })
    json.dump({"devis": out}, open(sys.argv[2], "w"), ensure_ascii=False, indent=2)
    print(f"{len(out)} devis écrits dans {sys.argv[2]}")
    if skipped:
        print(f"{len(skipped)} ligne(s) sans n° de devis D ignorée(s) : {', '.join(str(s) for s in skipped if s)}")

if __name__ == "__main__":
    main()