"""
Consolidates computer_hardware_stock_alphanumeric_serials_fixed.xlsx into
one row per unique product (SKU), with all serial numbers comma-joined in
the Serial Number column.  Output: computer_hardware_consolidated.xlsx
"""
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment
from collections import defaultdict, OrderedDict

SRC  = r"c:\dev\cnperp-dimensions\computer_hardware_stock_alphanumeric_serials_fixed.xlsx"
DEST = r"c:\dev\cnperp-dimensions\computer_hardware_consolidated.xlsx"

# ── Read source ──────────────────────────────────────────────────────────────
wb_in = openpyxl.load_workbook(SRC, data_only=True, read_only=True)
ws_in = wb_in["Serialized Stock"]
rows  = list(ws_in.iter_rows(values_only=True))
headers = [str(c).strip() if c else "" for c in rows[0]]

# Column indices
hi = {h: i for i, h in enumerate(headers)}
sku_i    = hi.get("SKU Barcode (EAN-13)", 5)
serial_i = hi.get("Serial Number", 4)

# ── Aggregate rows by SKU ─────────────────────────────────────────────────────
products = OrderedDict()  # sku -> first full row (as dict) + accumulated serials
for r in rows[1:]:
    sku = str(r[sku_i]).strip() if r[sku_i] else ""
    if not sku:
        continue
    serial = str(r[serial_i]).strip() if serial_i < len(r) and r[serial_i] else ""
    if sku not in products:
        # Store the first row as the canonical product row
        products[sku] = {
            "row":     list(r),
            "serials": [],
        }
    if serial:
        products[sku]["serials"].append(serial)

# ── Write consolidated output ─────────────────────────────────────────────────
wb_out = openpyxl.Workbook()
ws_out = wb_out.active
ws_out.title = "Products"

# Header style
hdr_font = Font(bold=True, color="FFFFFF")
hdr_fill = PatternFill("solid", fgColor="2563EB")

# Write header row — keep all original columns, replace "Serial Number" header
out_headers = headers[:]
out_headers[serial_i] = "Serial Numbers (comma-separated)"
ws_out.append(out_headers)
for cell in ws_out[1]:
    cell.font  = hdr_font
    cell.fill  = hdr_fill
    cell.alignment = Alignment(horizontal="center")

# Set column widths
ws_out.column_dimensions[openpyxl.utils.get_column_letter(serial_i + 1)].width = 60
for col_idx in range(1, len(headers) + 1):
    if col_idx != serial_i + 1:
        ws_out.column_dimensions[openpyxl.utils.get_column_letter(col_idx)].width = 20

# Write one row per product — sorted alphabetically by (Name, Model)
name_i_w  = hi.get("Name", 1)
model_i_w = hi.get("Model", 2)
sorted_products = sorted(
    products.items(),
    key=lambda kv: (
        str(kv[1]["row"][name_i_w]  or "").lower(),
        str(kv[1]["row"][model_i_w] or "").lower(),
    )
)

for sku, data in sorted_products:
    row_out = data["row"][:]
    row_out[serial_i] = ",".join(data["serials"])
    # Update quantity to reflect actual serial count
    qty_i = next((i for i, h in enumerate(headers) if h.lower() == "quantity"), None)
    if qty_i is not None:
        row_out[qty_i] = len(data["serials"])
    ws_out.append(row_out)

wb_out.save(DEST)
print(f"Written: {DEST}")
print(f"Products (rows): {len(products)}")
for sku, data in sorted_products:
    name_i_p  = hi.get("Name", 1)
    model_i_p = hi.get("Model", 2)
    name  = data["row"][name_i_p]  if name_i_p  < len(data["row"]) else ""
    model = data["row"][model_i_p] if model_i_p < len(data["row"]) else ""
    print(f"  {sku}  {str(name):<10} {str(model):<30}  {len(data['serials'])} serials")
