import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
from django.http import HttpResponse
import io


def generate_excel_file(column_names, data, file_name, style=None, style_columns=None, style_rows=None):
    wb = openpyxl.Workbook()
    ws = wb.active

    # Add bold font to the column headers
    bold_font = Font(bold=True)
    for col_num, column_name in enumerate(column_names, 1):
        cell = ws.cell(row=1, column=col_num)
        cell.value = column_name
        cell.font = bold_font

    # Add data to the worksheet
    for row_num, row_data in enumerate(data, 2):
        for col_num, cell_value in enumerate(row_data.values(), 1):
            cell = ws.cell(row=row_num, column=col_num)
            cell.value = cell_value

            # Apply styles if provided
            if style and isinstance(style, dict):
                cell_address = get_column_letter(col_num) + str(row_num)
                cell_style = style.get(cell_address)
                if cell_style:
                    if 'font' in cell_style:
                        cell.font = Font(**cell_style['font'])
                    if 'fill' in cell_style:
                        cell.fill = PatternFill(**cell_style['fill'])
                    if 'number_format' in cell_style:
                        cell.number_format = cell_style['number_format']
                    if 'date_format' in cell_style:
                        cell.number_format = cell_style['date_format']



    # Apply column styles if provided
    if style_columns:
        for col_num, column_name in enumerate(column_names, 1):
            column_letter = get_column_letter(col_num)
            if column_letter in style_columns:
                col_style = style_columns[column_letter]
                for cell in ws[column_letter]:
                    if 'font' in col_style:
                        cell.font = Font(**col_style['font'])
                    if 'fill' in col_style:
                        cell.fill = PatternFill(**col_style['fill'])
                    if 'number_format' in col_style:
                        cell.number_format = col_style['number_format']
                    if 'date_format' in col_style:
                        cell.number_format = col_style['date_format']
                    if 'alignment' in col_style:
                        cell.alignment = Alignment(**col_style['alignment'])  

    # Apply row styles if provided
    if style_rows:
        for row_num, row_data in enumerate(data, 2):
            row_index = str(row_num)
            if row_index in style_rows:
                row_style = style_rows[row_index]
                for col_num in range(1, len(column_names) + 1):
                    cell = ws.cell(row=row_num, column=col_num)
                    if 'font' in row_style:
                        cell.font = Font(**row_style['font'])
                    if 'fill' in row_style:
                        cell.fill = PatternFill(**row_style['fill'])
                    if 'number_format' in row_style:
                        cell.number_format = row_style['number_format']

    # Set column widths to fit the data
    for col_num, column_name in enumerate(column_names, 1):
        column_letter = openpyxl.utils.get_column_letter(col_num)
        max_len = max(len(str(cell.value)) for cell in ws[column_letter])
        ws.column_dimensions[column_letter].width = max_len + 2

    # Freeze the top row
    ws.freeze_panes = ws['A2']

    # Generate the response with the Excel file
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename="{}.xlsx"'.format(file_name)
    wb.save(response)

    return response