import openpyxl
from collections import Counter

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]]

# find column indices
sku_idx    = next((i for i, h in enumerate(headers) if "sku" in h.lower()), 5)
serial_idx = next((i for i, h in enumerate(headers) if "serial" in h.lower() and "number" in h.lower()), None)
name_idx   = next((i for i, h in enumerate(headers) if h.lower() in ("name", "product name", "item name")), None)

sku_name = Counter()
sku_serials = Counter()

for r in rows[1:]:
    sku    = str(r[sku_idx]).strip()    if sku_idx    is not None and sku_idx    < len(r) and r[sku_idx]    else ""
    serial = str(r[serial_idx]).strip() if serial_idx is not None and serial_idx < len(r) and r[serial_idx] else ""
    name   = str(r[name_idx]).strip()   if name_idx   is not None and name_idx   < len(r) and r[name_idx]   else ""
    if sku:
        sku_name[sku] = name or sku_name.get(sku, "")
        if serial:
            sku_serials[sku] += 1

print(f"Total data rows  : {len(rows) - 1}")
print(f"Unique SKUs      : {len(sku_name)}")
print(f"Total serials    : {sum(sku_serials.values())}")
print()
print(f"{'SKU':<20} {'Name':<35} {'Serials':>8}")
print("-" * 65)
for sku in sorted(sku_name):
    print(f"{sku:<20} {sku_name[sku]:<35} {sku_serials.get(sku, 0):>8}")
