from pathlib import Path
import pandas as pd

def load_kv_excel(path: Path) -> dict:
    df = pd.read_excel(path)
    # Header-Zeile suchen (enthält "Pos." & "Preis (CHF)")
    header_idx = None
    for i,row in df.iterrows():
        cells = [str(x) for x in row.tolist()]
        if ("Pos." in cells) and any("Preis" in c for c in cells) and any("Summe" in c for c in cells):
            header_idx = i; break
    if header_idx is None:
        return {}
    df2 = df.iloc[header_idx+1:].copy()
    df2.columns = df.iloc[header_idx]
    # Normalize col names
    df2 = df2.rename(columns={"Pos.":"Pos","Preis (CHF)":"Preis","Summe (CHF)":"Summe"})
    # Nur sinnvolle Zeilen
    df2 = df2[~df2["Pos"].isna()]
    # Map
    kv = {}
    for _,r in df2.iterrows():
        pos = str(r["Pos"]).strip()
        if pos and pos.isdigit():
            kv[pos] = {"EP": _to_num(r.get("Preis")), "GP": _to_num(r.get("Summe")), "text": str(r.get("Text") or "").strip()}
    return kv

def _to_num(x):
    s = str(x)
    s = s.replace("'", "").replace(" ", "").replace("CHF", "").replace("Fr.", "").replace(",", ".")
    try:
        return float(s)
    except:
        return None