import io
from datetime import date
from django.http import HttpResponse


def excel_response(sheet_name, filename, headers, rows):
    """
    Build a styled .xlsx HttpResponse.

    sheet_name : worksheet tab label
    filename   : downloaded file name (without extension)
    headers    : list of column header strings
    rows       : iterable of tuples/lists of cell values
    """
    import openpyxl
    from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = sheet_name

    BRAND_BLUE = '0A3D62'
    LIGHT_BLUE = 'D6E4F0'

    header_font  = Font(bold=True, color='FFFFFF', size=11)
    header_fill  = PatternFill('solid', fgColor=BRAND_BLUE)
    header_align = Alignment(horizontal='center', vertical='center', wrap_text=True)
    thin_border  = Border(
        bottom=Side(style='thin', color='CCCCCC'),
        right=Side(style='thin', color='CCCCCC'),
    )
    alt_fill = PatternFill('solid', fgColor=LIGHT_BLUE)

    ws.row_dimensions[1].height = 22

    for col_idx, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.font  = header_font
        cell.fill  = header_fill
        cell.alignment = header_align
        cell.border = thin_border

    for row_idx, row in enumerate(rows, 2):
        fill = alt_fill if row_idx % 2 == 0 else None
        for col_idx, value in enumerate(row, 1):
            cell = ws.cell(row=row_idx, column=col_idx, value=value)
            cell.alignment = Alignment(vertical='center')
            cell.border = thin_border
            if fill:
                cell.fill = fill

    # Auto-width (capped at 60)
    for col in ws.columns:
        best = max(
            (len(str(c.value)) if c.value is not None else 0 for c in col),
            default=8,
        )
        ws.column_dimensions[col[0].column_letter].width = min(best + 4, 60)

    ws.freeze_panes = 'A2'

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

    safe = filename.replace(' ', '_')
    today = date.today().strftime('%Y%m%d')
    response = HttpResponse(
        buf.read(),
        content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    )
    response['Content-Disposition'] = f'attachment; filename="{safe}_{today}.xlsx"'
    return response