import io
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from services.pdf_extractor.config import OUTPUT_COLS

def build_excel(all_data: dict) -> io.BytesIO:
    wb = Workbook()
    wb.remove(wb.active)
    thin = Side(style='thin', color='D0D0D0')
    border = Border(left=thin, right=thin, top=thin, bottom=thin)
    COLORS = ["1B4F8A","145A32","6E2C00","4A235A","1A5276","7B241C","0B5345","2E4057","4D4D4D","7D6608"]
    KEY_MAP = {"Invoice No":"invoice_no","Invoice Date":"invoice_date","PO#":"po_no","Line#":"line_no","Model / Item#":"model_item_no","Description":"description","Qty":"qty","Unit Price":"unit_price","Amount":"amount","COO":"coo"}
    COL_W = {"Invoice No":18,"Invoice Date":14,"PO#":16,"Line#":7,"Model / Item#":18,"Description":42,"Qty":8,"Unit Price":12,"Amount":14,"COO":8}

    for idx,(company,rows) in enumerate(all_data.items()):
        ws = wb.create_sheet(title=company[:31])
        color = COLORS[idx % len(COLORS)]
        hdr_fill = PatternFill('solid', start_color=color)
        hdr_font = Font(name='Calibri', bold=True, color='FFFFFF', size=10)
        for ci,col in enumerate(OUTPUT_COLS,1):
            c = ws.cell(row=1, column=ci, value=col)
            c.font = hdr_font; c.fill = hdr_fill
            c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
            c.border = border
        ws.row_dimensions[1].height = 28
        alt = PatternFill('solid', start_color='F5F8FF')
        norm = PatternFill('solid', start_color='FFFFFF')
        dfont = Font(name='Calibri', size=10)
        for ri,row in enumerate(rows,2):
            fill = alt if ri%2==0 else norm
            for ci,col in enumerate(OUTPUT_COLS,1):
                val = row.get(KEY_MAP[col], "")
                if col in ("Qty","Unit Price","Amount") and val != "":
                    try: val = float(str(val).replace(',',''))
                    except: pass
                c = ws.cell(row=ri, column=ci, value=val)
                c.font = dfont; c.fill = fill; c.border = border
                c.alignment = Alignment(vertical='center', wrap_text=(col=="Description"))
                if col in ("Unit Price","Amount"): c.number_format = '#,##0.00'
                if col == "Qty": c.number_format = '#,##0'
        for ci,col in enumerate(OUTPUT_COLS,1):
            ws.column_dimensions[get_column_letter(ci)].width = COL_W.get(col,14)
        ws.freeze_panes = "A2"
        ws.auto_filter.ref = f"A1:{get_column_letter(len(OUTPUT_COLS))}1"
        ws.cell(row=len(rows)+2, column=1, value=f"Total: {len(rows)} line items").font = Font(name='Calibri', bold=True, size=10, italic=True, color='888888')

    # Summary sheet
    ws_s = wb.create_sheet(title="Summary", index=0)
    for ci,title in enumerate(["Vendor","Line Items","Sheet"],1):
        c = ws_s.cell(row=1, column=ci, value=title)
        c.font = Font(name='Calibri', bold=True, color='FFFFFF', size=10)
        c.fill = PatternFill('solid', start_color='2C3E50')
        c.alignment = Alignment(horizontal='center'); c.border = border
    for ri,(company,rows) in enumerate(all_data.items(),2):
        ws_s.cell(row=ri, column=1, value=company).font = Font(name='Calibri', size=10)
        ws_s.cell(row=ri, column=2, value=len(rows)).font = Font(name='Calibri', size=10)
        ws_s.cell(row=ri, column=3, value=company[:31]).font = Font(name='Calibri', size=10, color='1B4F8A', underline='single')
        for ci in range(1,4):
            ws_s.cell(row=ri, column=ci).border = border
            ws_s.cell(row=ri, column=ci).alignment = Alignment(horizontal='center' if ci==2 else 'left')
    ws_s.column_dimensions['A'].width = 30
    ws_s.column_dimensions['B'].width = 14
    ws_s.column_dimensions['C'].width = 20
    ws_s.row_dimensions[1].height = 24

    buf = io.BytesIO(); wb.save(buf); buf.seek(0)
    return buf