"""Open, inspect and modify existing workbooks (uploads or generated artifacts)."""

from __future__ import annotations

import io
import re
from typing import Any

from openpyxl import load_workbook
from openpyxl.chart import BarChart, LineChart, PieChart, Reference, ScatterChart, Series
from openpyxl.comments import Comment
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
from openpyxl.utils.cell import column_index_from_string, coordinate_from_string, range_boundaries
from openpyxl.workbook.defined_name import DefinedName

from app.tools.coerce import cell as coerce_cell
from app.tools.create_excel import (
    FORMATS,
    HEADER_FILL,
    HEADER_FONT,
    INPUT_FILL,
    INPUT_FONT,
    _readme,
)

UQO_BLUE_DARK = "0A4A63"
TOTAL_BORDER = Border(top=Side(style="medium", color=UQO_BLUE_DARK))


class ExcelOpError(ValueError):
    pass


# ------------------------------------------------------------------ helpers
def _ws(wb: Any, name: str | None) -> Any:
    if not name:
        # first non "Lisez-moi" sheet
        for ws in wb.worksheets:
            if ws.title != "Lisez-moi":
                return ws
        return wb.worksheets[0]
    if name in wb.sheetnames:
        return wb[name]
    low = {s.lower(): s for s in wb.sheetnames}
    if name.lower() in low:
        return wb[low[name.lower()]]
    if name.isdigit() and 0 < int(name) <= len(wb.sheetnames):
        return wb.worksheets[int(name) - 1]
    raise ExcelOpError(f"Feuille introuvable : « {name} ». Feuilles : {', '.join(wb.sheetnames)}.")


def _apply_format(c: Any, fmt: str | None) -> None:
    if fmt and fmt in FORMATS and fmt != "text":
        c.number_format = FORMATS[fmt]


def _header_row(ws: Any) -> int:
    best, best_n = 1, -1
    for r in range(1, min(ws.max_row, 20) + 1):
        n = sum(1 for c in ws[r] if isinstance(c.value, str) and c.value.strip() and not c.value.startswith("="))
        if n > best_n:
            best, best_n = r, n
    return best


def _value(v: Any) -> Any:
    if isinstance(v, str) and v.startswith("="):
        return v
    return coerce_cell(v)


# ------------------------------------------------------------------ inspect
def inspect(data: bytes, sheet: str | None = None, max_rows: int = 60, max_cols: int = 16) -> dict[str, Any]:
    wb = load_workbook(io.BytesIO(data))
    sheets = [_ws(wb, sheet)] if sheet else wb.worksheets
    out: dict[str, Any] = {"sheets": [], "defined_names": sorted(wb.defined_names.keys())[:40]}
    for ws in sheets:
        lines = []
        for row in ws.iter_rows(min_row=1, max_row=min(ws.max_row, max_rows), max_col=min(ws.max_column, max_cols)):
            cells = [f"{c.coordinate}={c.value!r}" for c in row if c.value is not None]
            if cells:
                lines.append("  ".join(cells))
        out["sheets"].append({"name": ws.title, "dims": ws.dimensions, "max_row": ws.max_row,
                              "max_col": ws.max_column, "cells": lines,
                              "charts": len(getattr(ws, "_charts", []))})
    return out


def preview(data: bytes, max_rows: int = 16, max_cols: int = 10) -> dict[str, Any]:
    wb = load_workbook(io.BytesIO(data))
    sheets = []
    for ws in wb.worksheets[:8]:
        rows = []
        for r in ws.iter_rows(min_row=1, max_row=min(ws.max_row, max_rows), max_col=min(max(ws.max_column, 1), max_cols), values_only=True):
            rows.append([("" if v is None else v) for v in r])
        sheets.append({"name": ws.title, "rows": rows, "max_row": ws.max_row, "max_col": ws.max_column})
    first = next((s for s in sheets if s["name"] != "Lisez-moi"), sheets[0] if sheets else {"name": "", "rows": []})
    return {"sheet": first["name"], "rows": first["rows"], "sheets": [s["name"] for s in sheets], "all": sheets}


# ------------------------------------------------------------------ operations
def apply_operations(data: bytes, operations: list[dict[str, Any]]) -> tuple[bytes, list[str]]:
    wb = load_workbook(io.BytesIO(data))
    log: list[str] = []
    for i, op in enumerate(operations, 1):
        kind = str(op.get("op") or op.get("type") or "").lower()
        try:
            msg = _apply_one(wb, kind, op)
        except ExcelOpError:
            raise
        except Exception as exc:  # noqa: BLE001
            raise ExcelOpError(f"Opération {i} ({kind}) : {type(exc).__name__}: {exc}") from exc
        log.append(f"{i}. {msg}")
    if "Lisez-moi" not in wb.sheetnames:
        _readme(wb, {"objective": "Classeur modifié avec UQO-Chat", "sheets": []},
                [{"name": s} for s in wb.sheetnames])
    buf = io.BytesIO()
    wb.save(buf)
    return buf.getvalue(), log


def _apply_one(wb: Any, kind: str, op: dict[str, Any]) -> str:  # noqa: PLR0911, PLR0912, PLR0915
    if kind in {"set_cell", "set"}:
        ws = _ws(wb, op.get("sheet"))
        cell = str(op["cell"]).upper()
        c = ws[cell]
        c.value = _value(op.get("value"))
        _apply_format(c, op.get("format"))
        if op.get("bold"):
            c.font = Font(bold=True)
        if op.get("input"):
            c.fill, c.font = INPUT_FILL, INPUT_FONT
        if op.get("name"):
            dn = DefinedName(re.sub(r"[^A-Za-z0-9_]", "_", str(op["name"]))[:60],
                             attr_text=f"'{ws.title}'!${''.join(ch for ch in cell if ch.isalpha())}${''.join(ch for ch in cell if ch.isdigit())}")
            wb.defined_names[dn.name] = dn
        return f"{ws.title}!{cell} ← {op.get('value')!r}"

    if kind in {"set_cells", "set_many"}:
        ws = _ws(wb, op.get("sheet"))
        n = 0
        for cell, v in (op.get("cells") or {}).items():
            ws[str(cell).upper()].value = _value(v)
            n += 1
        return f"{ws.title}: {n} cellule(s) modifiée(s)"

    if kind in {"set_range", "write_rows", "rows"}:
        ws = _ws(wb, op.get("sheet"))
        col0, row0 = coordinate_from_string(str(op.get("anchor", "A1")).upper())
        c0 = column_index_from_string(col0)
        rows = op.get("rows") or []
        formats = op.get("formats") or []
        for i, row in enumerate(rows):
            for j, v in enumerate(row if isinstance(row, (list, tuple)) else [row]):
                c = ws.cell(row=row0 + i, column=c0 + j, value=_value(v))
                if j < len(formats):
                    _apply_format(c, formats[j])
        if op.get("header"):
            for j, h in enumerate(op["header"]):
                c = ws.cell(row=row0 - 1 if row0 > 1 else row0, column=c0 + j, value=str(h))
                c.fill, c.font = HEADER_FILL, HEADER_FONT
        return f"{ws.title}: plage écrite depuis {op.get('anchor', 'A1')} ({len(rows)} ligne(s))"

    if kind in {"add_column", "append_column"}:
        ws = _ws(wb, op.get("sheet"))
        hr = int(op.get("header_row") or _header_row(ws))
        col = op.get("column")
        if col:
            cidx = column_index_from_string(str(col).upper())
            ws.insert_cols(cidx)
        else:
            cidx = 1
            for c in ws[hr]:
                if c.value is not None:
                    cidx = max(cidx, c.column + 1)
        head = ws.cell(row=hr, column=cidx, value=str(op.get("header", "Nouvelle colonne")))
        head.fill, head.font = HEADER_FILL, HEADER_FONT
        head.alignment = Alignment(horizontal="center", wrap_text=True)
        last = int(op.get("last_row") or _last_data_row(ws, hr))
        values = op.get("values")
        formula = op.get("formula")
        n = 0
        for r in range(hr + 1, last + 1):
            if values is not None:
                idx = r - hr - 1
                if idx >= len(values):
                    break
                v = _value(values[idx])
            elif formula:
                v = str(formula).replace("{r}", str(r)).replace("{row}", str(r))
            else:
                v = None
            c = ws.cell(row=r, column=cidx, value=v)
            _apply_format(c, op.get("format"))
            n += 1
        ws.column_dimensions[get_column_letter(cidx)].width = 17
        return f"{ws.title}: colonne « {op.get('header')} » ajoutée en {get_column_letter(cidx)} ({n} lignes)"

    if kind in {"add_row", "append_row"}:
        ws = _ws(wb, op.get("sheet"))
        hr = int(op.get("header_row") or _header_row(ws))
        r = int(op.get("row") or _last_data_row(ws, hr) + 1)
        if op.get("row"):
            ws.insert_rows(r)
        for j, v in enumerate(op.get("values") or []):
            vv = _value(v)
            if isinstance(vv, str) and vv.startswith("="):
                vv = vv.replace("{r}", str(r)).replace("{row}", str(r))
            ws.cell(row=r, column=1 + j, value=vv)
        return f"{ws.title}: ligne ajoutée en {r}"

    if kind in {"insert_rows", "delete_rows", "insert_cols", "delete_cols"}:
        ws = _ws(wb, op.get("sheet"))
        at = int(op.get("at", 1))
        count = int(op.get("count", 1))
        getattr(ws, kind)(at, count)
        return f"{ws.title}: {kind} {at} ×{count}"

    if kind in {"format", "style"}:
        ws = _ws(wb, op.get("sheet"))
        rng = str(op.get("range") or op.get("cell") or "A1").upper()
        min_col, min_row, max_col, max_row = range_boundaries(rng if ":" in rng else f"{rng}:{rng}")
        for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):
            for c in row:
                _apply_format(c, op.get("format"))
                if op.get("bold") is not None or op.get("color"):
                    c.font = Font(bold=bool(op.get("bold")), color=str(op.get("color", "1A2B3C")).lstrip("#"))
                if op.get("fill") == "input":
                    c.fill, c.font = INPUT_FILL, INPUT_FONT
                elif op.get("fill") == "header":
                    c.fill, c.font = HEADER_FILL, HEADER_FONT
                elif op.get("fill"):
                    c.fill = PatternFill("solid", fgColor=str(op["fill"]).lstrip("#"))
                if op.get("total"):
                    c.border = TOTAL_BORDER
                    c.font = Font(bold=True, color=UQO_BLUE_DARK)
                if op.get("wrap"):
                    c.alignment = Alignment(wrap_text=True, vertical="top")
        return f"{ws.title}!{rng} formaté"

    if kind in {"add_sheet", "new_sheet"}:
        name = re.sub(r"[\[\]\*\?/\\:]", " ", str(op.get("name", "Feuille")))[:31]
        if name in wb.sheetnames:
            name = (name[:27] + " (2)")
        ws = wb.create_sheet(name)
        ws.sheet_view.showGridLines = False
        ws.column_dimensions["A"].width = 40
        if op.get("title"):
            ws["A1"] = str(op["title"])
            ws["A1"].font = Font(bold=True, size=14, color=UQO_BLUE_DARK)
        table = op.get("table") or {}
        if table.get("rows") or table.get("columns"):
            from app.tools.create_excel import _render_sheet
            from app.tools.excel_spec import normalise_spec

            spec = normalise_spec({"sheets": [{"name": name, "title": op.get("title") or name, "tables": [table],
                                               "notes": op.get("notes") or []}]})
            _render_sheet(wb, ws, spec["sheets"][0])
        return f"feuille « {name} » ajoutée"

    if kind == "rename_sheet":
        ws = _ws(wb, op.get("sheet"))
        old = ws.title
        ws.title = re.sub(r"[\[\]\*\?/\\:]", " ", str(op.get("name", old)))[:31]
        return f"feuille « {old} » renommée « {ws.title} »"

    if kind == "delete_sheet":
        ws = _ws(wb, op.get("sheet"))
        if len(wb.worksheets) <= 1:
            raise ExcelOpError("Impossible de supprimer la dernière feuille.")
        wb.remove(ws)
        return "feuille supprimée"

    if kind == "add_chart":
        ws = _ws(wb, op.get("sheet"))
        ctype = str(op.get("type", "bar")).lower()
        chart: Any = {"line": LineChart, "pie": PieChart, "scatter": ScatterChart}.get(ctype, BarChart)()
        if ctype == "bar":
            chart.type = "col"
        chart.title = op.get("title", "")
        chart.height, chart.width = 8, 14
        vr = str(op.get("values_range") or op.get("data_range"))
        vmin_col, vmin_row, vmax_col, vmax_row = range_boundaries(vr)
        values = Reference(ws, min_col=vmin_col, min_row=vmin_row, max_col=vmax_col, max_row=vmax_row)
        if ctype == "scatter" and op.get("categories_range"):
            cmin_col, cmin_row, cmax_col, cmax_row = range_boundaries(str(op["categories_range"]))
            xs = Reference(ws, min_col=cmin_col, min_row=cmin_row, max_col=cmax_col, max_row=cmax_row)
            chart.series.append(Series(values, xs, title=op.get("series_title", "")))
        else:
            chart.add_data(values, titles_from_data=bool(op.get("titles_from_data", False)))
            if op.get("categories_range"):
                cmin_col, cmin_row, cmax_col, cmax_row = range_boundaries(str(op["categories_range"]))
                chart.set_categories(Reference(ws, min_col=cmin_col, min_row=cmin_row, max_col=cmax_col, max_row=cmax_row))
        if ctype != "pie":
            chart.legend = None
        ws.add_chart(chart, str(op.get("anchor", "H2")))
        return f"{ws.title}: graphique {ctype} ajouté"

    if kind in {"add_note", "comment"}:
        ws = _ws(wb, op.get("sheet"))
        c = ws[str(op.get("cell", "A1")).upper()]
        c.comment = Comment(str(op.get("text", ""))[:1000], "UQO-Chat")
        return f"{ws.title}!{c.coordinate}: commentaire ajouté"

    if kind in {"add_text", "write_text", "note"}:
        ws = _ws(wb, op.get("sheet"))
        c = ws[str(op.get("cell", f"A{ws.max_row + 2}")).upper()]
        c.value = str(op.get("text", ""))
        c.alignment = Alignment(wrap_text=True, vertical="top")
        if op.get("bold"):
            c.font = Font(bold=True, color=UQO_BLUE_DARK)
        return f"{ws.title}!{c.coordinate}: texte écrit"

    if kind in {"set_column_width", "column_width"}:
        ws = _ws(wb, op.get("sheet"))
        ws.column_dimensions[str(op.get("column", "A")).upper()].width = float(op.get("width", 18))
        return f"{ws.title}: largeur {op.get('column')} = {op.get('width')}"

    if kind in {"clear", "clear_range"}:
        ws = _ws(wb, op.get("sheet"))
        rng = str(op.get("range") or op.get("cell")).upper()
        min_col, min_row, max_col, max_row = range_boundaries(rng if ":" in rng else f"{rng}:{rng}")
        for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):
            for c in row:
                c.value = None
        return f"{ws.title}!{rng} effacé"

    if kind in {"freeze", "freeze_panes"}:
        ws = _ws(wb, op.get("sheet"))
        ws.freeze_panes = str(op.get("cell", "A2")).upper()
        return f"{ws.title}: volets figés à {op.get('cell', 'A2')}"

    raise ExcelOpError(
        f"Opération inconnue « {kind} ». Opérations : set_cell, set_cells, set_range, add_column, add_row, "
        "insert_rows, delete_rows, insert_cols, delete_cols, format, add_sheet, rename_sheet, delete_sheet, "
        "add_chart, add_note, add_text, set_column_width, clear, freeze.")


def _last_data_row(ws: Any, header_row: int) -> int:
    last = header_row
    for r in range(header_row + 1, ws.max_row + 1):
        if any(c.value is not None for c in ws[r][: max(1, min(ws.max_column, 30))]):
            last = r
        else:
            break
    return last