# backend/app/plugins/advanced_stats/export.py
"""报表导出。复用已装的 openpyxl，不引新依赖。

PDF 不在此处实现——项目已有基于打印模板的 HTML 打印体系，报表 PDF 应走那条路。
"""
from __future__ import annotations

import csv
import io
from typing import Any, Sequence

EXPORT_FORMATS = ("xlsx", "csv")

# 以这些字符开头的单元格会被 Excel/Sheets 当公式执行（CSV 注入）
_FORMULA_PREFIXES = ("=", "+", "-", "@", "\t", "\r")


def _safe_cell(v: Any) -> Any:
    if isinstance(v, str) and v.startswith(_FORMULA_PREFIXES):
        return "'" + v
    return v


def rows_to_csv(headers: Sequence[str], rows: Sequence[Sequence[Any]]) -> bytes:
    """UTF-8 with BOM——不带 BOM 的话中文 Windows 的 Excel 打开是乱码。"""
    buf = io.StringIO()
    w = csv.writer(buf, lineterminator="\n")
    w.writerow([_safe_cell(h) for h in headers])
    for r in rows:
        w.writerow([_safe_cell(c) for c in r])
    return b"\xef\xbb\xbf" + buf.getvalue().encode("utf-8")


def rows_to_xlsx(
    headers: Sequence[str], rows: Sequence[Sequence[Any]], *, sheet_title: str = "Report"
) -> bytes:
    from openpyxl import Workbook
    from openpyxl.styles import Font

    wb = Workbook()
    ws = wb.active
    # 工作表名不能含 : \ / ? * [ ]，且最长 31 字符
    ws.title = "".join(c for c in sheet_title if c not in r':\/?*[]')[:31] or "Report"

    ws.append([_safe_cell(h) for h in headers])
    for cell in ws[1]:
        cell.font = Font(bold=True)
    for r in rows:
        ws.append([_safe_cell(c) for c in r])

    ws.freeze_panes = "A2"
    for i, h in enumerate(headers, start=1):
        ws.column_dimensions[ws.cell(row=1, column=i).column_letter].width = max(12, len(str(h)) + 4)

    out = io.BytesIO()
    wb.save(out)
    return out.getvalue()