"""Excel exports with live formulas, so totals keep working when people edit the file."""
import re
from datetime import datetime
from io import BytesIO

from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter

from ..summary import YearView
from .i18n import T

MONEY = '#,##0.00;[Red]-#,##0.00'
PCT = '0.00%;[Red]-0.00%'
HEAD_FILL = PatternFill("solid", fgColor="1F3B63")
CAT_FILL = PatternFill("solid", fgColor="EEF2F8")
WHITE_BOLD = Font(bold=True, color="FFFFFF")
BOLD = Font(bold=True)
THIN_TOP = Border(top=Side(style="thin", color="999999"))


def _safe_sheet_name(name: str, used: set[str]) -> str:
    base = re.sub(r"[\[\]:*?/\\]", "-", name)[:31] or "Sheet"
    candidate, n = base, 2
    while candidate.lower() in used:
        suffix = f" ({n})"
        candidate, n = base[: 31 - len(suffix)] + suffix, n + 1
    used.add(candidate.lower())
    return candidate


def _header(ws, row: int, labels: list[str]) -> None:
    for i, label in enumerate(labels, start=1):
        c = ws.cell(row, i, label)
        c.font, c.fill = WHITE_BOLD, HEAD_FILL
        c.alignment = Alignment(horizontal="left" if i <= 2 else "right", vertical="center")


def _title(ws, title: str, sub: str) -> None:
    ws["A1"] = title
    ws["A1"].font = Font(bold=True, size=14)
    ws["A2"] = sub
    ws["A2"].font = Font(color="666666")


def _out(wb: Workbook) -> bytes:
    buf = BytesIO()
    wb.save(buf)
    return buf.getvalue()


def _meta(view: YearView, t: T, site: str | None) -> str:
    bits = [f"{t('status')}: {t(view.year.status)}", f"{t('exported')}: {datetime.now():%Y-%m-%d %H:%M}"]
    if site:
        bits.insert(0, t("site_filter", site=site))
    return " · ".join(bits)


def budget_xlsx(view: YearView, lang: str, site: str | None = None, include_empty: bool = True) -> bytes:
    t = T(lang, view.year.year, view.year.department.name)
    wb = Workbook()
    ws = wb.active
    ws.title = _safe_sheet_name(t("budget_title"), set())
    _title(ws, t("budget_title"), t("budget_sub") + " · " + _meta(view, t, site))
    months = t("months")
    _header(ws, 4, [t("line"), t("site"), *months, t("total")])
    row = 5
    cost_subtotals = []
    cats = sorted([c for c in view.categories if c.lines or include_empty],
                  key=lambda c: 0 if c.category.section == "revenue" else 1)
    for cv in cats:
        ws.cell(row, 1, cv.name(lang)).font = BOLD
        for col in range(1, 16):
            ws.cell(row, col).fill = CAT_FILL
        row += 1
        first = row
        for lv in cv.lines:
            ws.cell(row, 1, lv.line.name)
            ws.cell(row, 2, lv.line.site.name if lv.line.site else t("group"))
            for m, value in enumerate(lv.figures.months):
                ws.cell(row, 3 + m, float(round(value, 2))).number_format = MONEY
            ws.cell(row, 15, f"=SUM(C{row}:N{row})").number_format = MONEY
            row += 1
        label = t("revenue_total") if cv.category.section == "revenue" else t("sub_total", name=cv.name(lang))
        ws.cell(row, 1, label).font = BOLD
        for col in range(3, 16):
            letter = get_column_letter(col)
            formula = f"=SUM({letter}{first}:{letter}{row - 1})" if cv.lines else 0
            c = ws.cell(row, col, formula)
            c.number_format, c.font, c.border = MONEY, BOLD, THIN_TOP
        if cv.category.section != "revenue":
            cost_subtotals.append(row)
        row += 2
    ws.cell(row, 1, t("total_costs")).font = WHITE_BOLD
    for col in range(1, 16):
        ws.cell(row, col).fill = HEAD_FILL
    for col in range(3, 16):
        letter = get_column_letter(col)
        formula = "=" + "+".join(f"{letter}{r}" for r in cost_subtotals) if cost_subtotals else 0
        c = ws.cell(row, col, formula)
        c.number_format, c.font = MONEY, WHITE_BOLD
    ws.column_dimensions["A"].width = 52
    ws.column_dimensions["B"].width = 12
    for col in range(3, 16):
        ws.column_dimensions[get_column_letter(col)].width = 13
    ws.freeze_panes = "C5"
    _inputs_sheet(wb.create_sheet(_safe_sheet_name(t("inputs"), {ws.title.lower()})), view, t, site)
    return _out(wb)


def _inputs_sheet(ws, view: YearView, t: T, site: str | None) -> None:
    """The Sheet1 layout: actual, base, factor, adjusted, change, remarks."""
    _title(ws, t("inputs") + f" {view.year.year}", _meta(view, t, site))
    _header(ws, 4, [t("category"), t("line"), t("site"), t("prior_actual"), t("base"), t("factor"),
                    t("adjusted"), t("change_eur"), t("change_pct"), t("reason"), t("remarks")])
    row = 5
    for cv in view.with_lines():
        for lv in cv.lines:
            f, line = lv.figures, lv.line
            ws.cell(row, 1, cv.name(t.lang))
            ws.cell(row, 2, line.name)
            ws.cell(row, 3, line.site.name if line.site else t("group"))
            ws.cell(row, 4, float(f.prior)).number_format = MONEY
            ws.cell(row, 5, float(f.base)).number_format = MONEY
            ws.cell(row, 6, float(f.factor)).number_format = "0.0000"
            ws.cell(row, 7, f"=ROUND(E{row}*F{row},2)").number_format = MONEY
            ws.cell(row, 8, f"=G{row}-D{row}").number_format = MONEY
            ws.cell(row, 9, f'=IF(D{row}=0,"",H{row}/D{row})').number_format = PCT
            ws.cell(row, 10, line.adj_reason)
            ws.cell(row, 11, line.remarks)
            row += 1
    ws.cell(row, 1, t("total")).font = BOLD
    for col, letter in ((4, "D"), (5, "E"), (7, "G"), (8, "H")):
        c = ws.cell(row, col, f"=SUM({letter}5:{letter}{row - 1})")
        c.number_format, c.font, c.border = MONEY, BOLD, THIN_TOP
    c = ws.cell(row, 9, f'=IF(D{row}=0,"",H{row}/D{row})')
    c.number_format, c.font = PCT, BOLD
    for letter, width in zip("ABCDEFGHIJK", (34, 46, 12, 16, 16, 9, 18, 15, 12, 30, 40)):
        ws.column_dimensions[letter].width = width
    ws.freeze_panes = "C5"


def cost_report_xlsx(view: YearView, lang: str, site: str | None = None) -> bytes:
    t = T(lang, view.year.year, view.year.department.name)
    wb = Workbook()
    ws = wb.active
    used: set[str] = set()
    ws.title = _safe_sheet_name(t("summary"), used)
    _title(ws, t("cost_report_title"), t("summary_sub") + " · " + _meta(view, t, site))
    _header(ws, 4, [t("category"), t("prior_actual"), t("adjusted"), t("change_eur"), t("change_pct")])
    cats = view.with_lines("cost")
    row = 5
    for cv in cats:
        sheet = wb.create_sheet(_safe_sheet_name(cv.name(lang), used))
        total_row = _category_sheet(sheet, cv, t)
        ref = f"'{sheet.title}'"
        ws.cell(row, 1, cv.name(lang))
        ws.cell(row, 2, f"={ref}!B{total_row}").number_format = MONEY
        ws.cell(row, 3, f"={ref}!C{total_row}").number_format = MONEY
        ws.cell(row, 4, f"=C{row}-B{row}").number_format = MONEY
        ws.cell(row, 5, f'=IF(B{row}=0,"",D{row}/B{row})').number_format = PCT
        row += 1
    ws.cell(row, 1, t("total")).font = BOLD
    for col, letter in ((2, "B"), (3, "C")):
        c = ws.cell(row, col, f"=SUM({letter}5:{letter}{row - 1})")
        c.number_format, c.font, c.border = MONEY, BOLD, THIN_TOP
    ws.cell(row, 4, f"=C{row}-B{row}").number_format = MONEY
    ws.cell(row, 5, f'=IF(B{row}=0,"",D{row}/B{row})').number_format = PCT
    ws.cell(row, 4).font = ws.cell(row, 5).font = BOLD
    for letter, width in zip("ABCDE", (40, 20, 22, 18, 14)):
        ws.column_dimensions[letter].width = width
    return _out(wb)


def _category_sheet(ws, cv, t: T) -> int:
    _title(ws, cv.name(t.lang), t("cost_lines", n=len(cv.lines)))
    _header(ws, 4, [t("line"), t("prior_actual"), t("adjusted"), t("change_eur"), t("change_pct"), t("site"), t("remarks")])
    row = 5
    for lv in cv.lines:
        ws.cell(row, 1, lv.line.name)
        ws.cell(row, 2, float(lv.figures.prior)).number_format = MONEY
        ws.cell(row, 3, float(lv.figures.adjusted)).number_format = MONEY
        ws.cell(row, 4, f"=C{row}-B{row}").number_format = MONEY
        ws.cell(row, 5, f'=IF(B{row}=0,"",D{row}/B{row})').number_format = PCT
        ws.cell(row, 6, lv.line.site.name if lv.line.site else t("group"))
        ws.cell(row, 7, lv.line.remarks)
        row += 1
    ws.cell(row, 1, t("total")).font = BOLD
    for col, letter in ((2, "B"), (3, "C")):
        c = ws.cell(row, col, f"=SUM({letter}5:{letter}{row - 1})")
        c.number_format, c.font, c.border = MONEY, BOLD, THIN_TOP
    ws.cell(row, 4, f"=C{row}-B{row}").number_format = MONEY
    ws.cell(row, 5, f'=IF(B{row}=0,"",D{row}/B{row})').number_format = PCT
    for letter, width in zip("ABCDEFG", (46, 18, 20, 16, 12, 12, 40)):
        ws.column_dimensions[letter].width = width
    return row