#!/usr/bin/env python3
"""Build a schlib_write params.json from a part's per-typeid Excel sheet, so the .SchLib symbol
carries **every parameter that was filled from the datasheet** — not only the fixed SOP §5 set.

Vecmocon's rule (per the engineer): the schematic symbol's Altium properties must mirror the
per-part workbook. So this reads the one data row of the part's typeid sheet, keeps every
**non-empty** datasheet parameter, drops the bookkeeping/identity/model-link columns that don't
belong in the symbol's parameter list, renames the few columns whose Altium/SOP name differs,
and merges in the SOP-only fields that don't live in the sheet (Manufacturer Part = MPN, Process,
Datasheet, Value, Vecmocon Part Code). The result is a params.json consumed by schlib_write.py.

Columns never written as symbol parameters (identity / versioning / Altium model links — those
are the symbol's Library Ref and the linked PCB footprint, not text properties):
  MPN_make_type, Skill Version, Template Version,
  Library Ref, Library Path, Footprint Ref, Footprint Path

Column-name -> Altium/SOP parameter-name renames (value copied through unchanged):
  "Rohs compliance"    -> "ROHS"
  "Operating Temp(°C)" -> "Operating Temperature"
Everything else keeps its exact sheet header as the parameter name (e.g. "Rated Current(A)",
"ESD Withstand Voltage(kV)", "Package", "Description", "Manufacturer") so the symbol and the
workbook stay traceably identical.

Usage:
  python schlib_params_from_xlsx.py --xlsx PART.xlsx [--component LIBREF] \
      [--sop sop.json] [--set "Process=Reflow" --set "Datasheet=onsemi EMI8141/D"] \
      [--out params.json]

--sop / --set carry the SOP-only fields the sheet doesn't hold. --set wins over --sop; both are
overlaid on top of the sheet-derived params, but a sheet value is only overridden by a non-empty
override. Empty override values are ignored (SOP hides blanks — a gap simply stays empty).
"""
import argparse, json, os, sys
import openpyxl

# Columns that must never become symbol text parameters.
SKIP_COLS = {
    "MPN_make_type", "Skill Version", "Template Version",
    "Library Ref", "Library Path", "Footprint Ref", "Footprint Path",
}
# Sheet header -> Altium/SOP parameter name (value unchanged).
RENAME = {
    "Rohs compliance": "ROHS",
    "Operating Temp(°C)": "Operating Temperature",
}
DEFAULT_REMOVE = ["Manufacturer_Name", "Manufacturer_Part_Number"]


def _clean(v):
    if v is None:
        return ""
    return str(v).strip()


def _mpn_from_tag(tag):
    """tag = <MPN>_<make>_<typeid>; typeid + make are the last two tokens, so the MPN is the
    remainder — robust even when the MPN itself contains underscores."""
    parts = tag.rsplit("_", 2)
    return parts[0] if len(parts) == 3 else tag


def build(xlsx, component=None, sop=None, sets=None):
    wb = openpyxl.load_workbook(xlsx, read_only=True, data_only=True)
    # the parameter sheet is the typeid sheet (first sheet that isn't Version History)
    sheet = next((s for s in wb.sheetnames if s != "Version History"), wb.sheetnames[0])
    ws = wb[sheet]
    rows = list(ws.iter_rows(values_only=True))
    if len(rows) < 2:
        sys.exit(f"{xlsx}: sheet '{sheet}' has no data row")
    headers = [_clean(h) for h in rows[0]]
    values = list(rows[1])
    row = {h: _clean(values[i]) if i < len(values) else "" for i, h in enumerate(headers) if h}

    tag = row.get("MPN_make_type", "")
    mpn = _mpn_from_tag(tag) if tag else ""

    params = {}
    for h in headers:
        if not h or h in SKIP_COLS:
            continue
        val = row.get(h, "")
        if val == "":
            continue                       # honest blank — leave it out
        params[RENAME.get(h, h)] = val

    # SOP-only fields the sheet doesn't carry. Manufacturer Part = MPN by default.
    overlay = {}
    if mpn:
        overlay["Manufacturer Part"] = mpn
    if sop:
        overlay.update({k: _clean(v) for k, v in sop.items()})
    for kv in (sets or []):
        if "=" in kv:
            k, v = kv.split("=", 1)
            overlay[k.strip()] = v.strip()
    for k, v in overlay.items():
        if v != "":                        # non-empty override wins; empty is ignored
            params[k] = v

    out = {"comment": mpn, "parameters": params, "remove": DEFAULT_REMOVE}
    if component:
        out["component"] = component
    return out


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--xlsx", required=True, help="the part's per-typeid .xlsx")
    ap.add_argument("--component", help="symbol Library Ref (targets one component; omit -> all)")
    ap.add_argument("--sop", help="JSON file of SOP-only fields (Process, Datasheet, Value, ...)")
    ap.add_argument("--set", dest="sets", action="append", default=[],
                    help='SOP-only field as "Name=Value" (repeatable; overrides --sop)')
    ap.add_argument("--out", help="write params.json here (default: stdout)")
    a = ap.parse_args()
    sop = json.load(open(a.sop, encoding="utf-8")) if a.sop else None
    res = build(a.xlsx, component=a.component, sop=sop, sets=a.sets)
    text = json.dumps(res, indent=2, ensure_ascii=False)
    if a.out:
        open(a.out, "w", encoding="utf-8").write(text + "\n")
        print(f"wrote {a.out}  ({len(res['parameters'])} parameters"
              + (f", component={a.component}" if a.component else "") + ")")
    else:
        print(text)


if __name__ == "__main__":
    main()