#!/usr/bin/env python3
"""Pull budget rates + per-SKU factory cost / gross KG / HS column out of Hardware Calculator.xlsx.
Usage: python3 read_calculator.py "Hardware Calculator.xlsx" calc.json"""
import json, sys
from openpyxl import load_workbook

wb = load_workbook(sys.argv[1], data_only=False)
inp = wb["Inputs"]
cols = [str(inp.cell(1, c).value).replace(".0", "") for c in range(2, 6)]      # 8471, 8528, Steel, Other HS Code
rowmap = {"base": 2, "s122": 3, "liberation": 4, "customs_fee": 5, "roger_lu": 8, "customs_agent": 9,
          "air": 11, "express": 12, "ocean": 13, "overhead": 15}
rates = {k: [float(inp.cell(r, c).value or 0) for c in range(2, 6)] for k, r in rowmap.items()}
rates["columns"] = ["8471", "8528", "Steel", "Other"]
rates["labels"] = {k: inp.cell(r, 1).value for k, r in rowmap.items() if k in ("base", "s122", "liberation", "customs_fee")}
rates["reseller_b_margin"] = inp["B17"].value; rates["msrp_margin"] = inp["B18"].value
col_of = lambda hs: {8471: "8471", 8528: "8528"}.get(int(float(hs)) if str(hs).replace(".", "").isdigit() else 0,
                                                     "Steel" if str(hs).lower().startswith("steel") else "Other")
skus = {}
pe = wb["Pricing Engine"]
for r in range(2, pe.max_row + 1):
    code = pe.cell(r, 2).value
    if code:
        skus[code.strip().upper()] = {"name": pe.cell(r, 3).value, "category": pe.cell(r, 4).value,
                                      "hs_col": col_of(pe.cell(r, 5).value or 0), "hs_code": pe.cell(r, 6).value,
                                      "factory_cost": pe.cell(r, 8).value, "gross_kg": pe.cell(r, 9).value,
                                      "source": f"Pricing Engine row {r}"}
models = {}
md = wb["Models"]
for r in range(2, md.max_row + 1):
    m = md.cell(r, 1).value
    if m:
        models[str(m).strip().upper()] = {"hs_col": col_of(md.cell(r, 2).value or "other"), "gross_kg": md.cell(r, 3).value,
                                          "factory_cost": md.cell(r, 4).value, "source": f"Models row {r}"}
json.dump({"rates": rates, "skus": skus, "models": models}, open(sys.argv[2], "w"), indent=1, default=str)
print(f"rates for {cols}; {len(skus)} SKUs ({sum(1 for s in skus.values() if s['factory_cost'])} with factory cost); {len(models)} models")