import openpyxl
from collections import defaultdict

path = r"c:\dev\cnperp-dimensions\computer_hardware_stock_alphanumeric_serials_fixed.xlsx"
wb = openpyxl.load_workbook(path, data_only=True, read_only=True)
ws = wb["Serialized Stock"]
rows = list(ws.iter_rows(values_only=True))
headers = [str(c).strip() if c else "" for c in rows[0]]
print("Headers:", headers)
print()

# find key column indices
idx = {h: i for i, h in enumerate(headers)}
name_i  = next((i for i,h in enumerate(headers) if h.lower() == "name"), None)
model_i = next((i for i,h in enumerate(headers) if h.lower() == "model"), None)
sku_i   = next((i for i,h in enumerate(headers) if "sku" in h.lower()), 5)

print(f"name col  : {name_i} ({headers[name_i] if name_i is not None else 'none'})")
print(f"model col : {model_i} ({headers[model_i] if model_i is not None else 'none'})")
print(f"sku col   : {sku_i} ({headers[sku_i]})")
print()

# Group by (name, model) -> list of current SKUs
combos = defaultdict(set)
combo_count = defaultdict(int)
for r in rows[1:]:
    name  = str(r[name_i]).strip()  if name_i  is not None and r[name_i]  else ""
    model = str(r[model_i]).strip() if model_i is not None and r[model_i] else ""
    sku   = str(r[sku_i]).strip()   if r[sku_i] else ""
    key = (name, model)
    combos[key].add(sku)
    combo_count[key] += 1

print(f"Unique (Name, Model) combinations: {len(combos)}")
print()
print(f"{'Name':<20} {'Model':<35} {'Rows':>6}  Current SKU(s)")
print("-" * 90)
for (name, model), skus in sorted(combos.items()):
    print(f"{name:<20} {model:<35} {combo_count[(name,model)]:>6}  {', '.join(sorted(skus))}")
