"""Generate a real .xlsx file from the Excel artifact JSON."""

import io
from typing import Any

import xlsxwriter


def _get_cell_value(cell_data, columns: list[str], col_idx: int) -> Any:
    """Extract display value from a cell (handles both raw values and cell objects)."""
    if cell_data is None:
        return ""
    if isinstance(cell_data, dict):
        return cell_data.get("value", cell_data.get("formula", ""))
    return cell_data


def _get_cell_format(cell_data) -> dict | None:
    """Get xlsxwriter format options from cell object."""
    if not isinstance(cell_data, dict):
        return None
    opts = {}
    if cell_data.get("bold"):
        opts["bold"] = True
    if cell_data.get("italic"):
        opts["italic"] = True
    if cell_data.get("format") == "currency":
        opts["num_format"] = "$#,##0.00"
    elif cell_data.get("format") == "percent":
        opts["num_format"] = "0.0%"
    elif cell_data.get("format") == "number":
        opts["num_format"] = "#,##0.00"
    return opts if opts else None


def _row_to_list(row_data, columns: list[str]) -> tuple[list[Any], list[dict | None]]:
    """Convert a row (array or dict) to (values, formats) for writing."""
    values = []
    formats = []
    if isinstance(row_data, list):
        for i, cell in enumerate(row_data):
            values.append(_get_cell_value(cell, columns, i))
            formats.append(_get_cell_format(cell) if isinstance(cell, dict) else None)
    else:
        for i, col in enumerate(columns):
            cell = row_data.get(col, row_data.get(i, ""))
            values.append(_get_cell_value(cell, columns, i))
            formats.append(_get_cell_format(cell) if isinstance(cell, dict) else None)
    return values, formats


def generate_xlsx(artifact: dict[str, Any]) -> bytes:
    """Return the bytes of a .xlsx file generated from the artifact JSON."""
    buffer = io.BytesIO()
    workbook = xlsxwriter.Workbook(buffer, {"in_memory": True})

    sheets_data = artifact.get("sheets", [])
    if not sheets_data:
        ws = workbook.add_worksheet("Sheet1")
        ws.write(0, 0, "No data")
        workbook.close()
        buffer.seek(0)
        return buffer.getvalue()

    for sheet_data in sheets_data:
        name = (sheet_data.get("name") or "Sheet").replace("/", "-")[:31]
        columns = sheet_data.get("columns", [])
        rows = sheet_data.get("rows", [])

        ws = workbook.add_worksheet(name)

        # Header row
        for ci, col in enumerate(columns):
            ws.write(0, ci, str(col), workbook.add_format({"bold": True}))

        # Data rows
        for ri, row_data in enumerate(rows):
            values, formats = _row_to_list(row_data, columns)
            for ci, (val, fmt) in enumerate(zip(values, formats)):
                if fmt:
                    cell_fmt = workbook.add_format(fmt)
                    ws.write(ri + 1, ci, val, cell_fmt)
                else:
                    ws.write(ri + 1, ci, val)

    workbook.close()
    buffer.seek(0)
    return buffer.getvalue()