#!/usr/bin/env python3
"""
Excel 导出脚本 - excel-exporter skill
用法（单 sheet）:
  python3 export_excel.py --title "标题" --data '[{...}]' --output "输出路径"
用法（多 sheet）:
  python3 export_excel.py --sheets '[{"title":"Sheet1","data":[...]},...]' --output "输出路径"
"""
import sys
import os
import argparse
import json

sys.path.insert(0, '/workspace/.local/lib/python3.11/site-packages')

import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter


# ─────────────────────────────────────────────────────────────────────────────
# 宽高计算工具
# ─────────────────────────────────────────────────────────────────────────────

def cell_display_width(text: str) -> float:
    """计算字符串在 Excel 中的显示宽度（中文≈2.2，英文≈1）"""
    width = 0.0
    for c in str(text):
        width += 2.2 if ord(c) > 127 else 1.0
    return width


def cell_display_height(text: str, col_width: float, base_height: float = 15.0) -> float:
    """
    根据内容长度和列宽估算行高（支持换行内容）。
    col_width 单位为 Excel 列宽字符数。
    """
    if not text:
        return base_height
    lines = str(text).split('\n')
    total_lines = 0
    for line in lines:
        w = cell_display_width(line)
        # 每 col_width 个字符宽度折一行
        total_lines += max(1, int(w / max(col_width, 1)) + 1) if w > col_width else 1
    return max(base_height, total_lines * base_height)


# ─────────────────────────────────────────────────────────────────────────────
# 主导出函数
# ─────────────────────────────────────────────────────────────────────────────

def export_excel(title: str, data: list, output_path: str) -> str:
    """单 sheet 导出（向后兼容）"""
    return export_excel_multi([{"title": title, "data": data}], output_path)


def export_excel_multi(sheets: list, output_path: str) -> str:
    """多 sheet 导出"""
    wb = openpyxl.Workbook()
    wb.remove(wb.active)  # 删除默认空 sheet

    for sheet_def in sheets:
        title = sheet_def.get("title", "Sheet")[:31]
        data  = sheet_def.get("data", [])
        ws    = wb.create_sheet(title=title)
        _write_sheet(ws, data)

    _save(wb, output_path)
    return output_path


def _write_sheet(ws, data: list):
    if not data:
        return

    headers = list(data[0].keys())

    header_fill  = PatternFill(start_color='366092', end_color='366092', fill_type='solid')
    header_font  = Font(color='FFFFFF', bold=True, size=11)
    even_fill    = PatternFill(start_color='EBF1DE', end_color='EBF1DE', fill_type='solid')
    center_align = Alignment(horizontal='center', vertical='center', wrap_text=True)
    left_align   = Alignment(horizontal='left',   vertical='center', wrap_text=True)

    # 表头
    for col_idx, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.fill      = header_fill
        cell.font      = header_font
        cell.alignment = center_align

    # 数据行
    for row_idx, row_data in enumerate(data, 2):
        for col_idx, header in enumerate(headers, 1):
            val  = row_data.get(header, '')
            cell = ws.cell(row=row_idx, column=col_idx, value=val)
            cell.alignment = left_align
            if row_idx % 2 == 0:
                cell.fill = even_fill

    # 自适应列宽
    col_widths = {}
    for col_idx, header in enumerate(headers, 1):
        max_w = cell_display_width(header) + 2
        for row_data in data:
            val = row_data.get(header, '')
            for line in str(val).split('\n'):
                max_w = max(max_w, cell_display_width(line) + 2)
        col_w = min(max(max_w, 8), 60)
        ws.column_dimensions[get_column_letter(col_idx)].width = col_w
        col_widths[col_idx] = col_w

    # 自适应行高
    ws.row_dimensions[1].height = 20
    for row_idx, row_data in enumerate(data, 2):
        max_h = 15.0
        for col_idx, header in enumerate(headers, 1):
            val = str(row_data.get(header, ''))
            h = cell_display_height(val, col_widths.get(col_idx, 15))
            max_h = max(max_h, h)
        ws.row_dimensions[row_idx].height = min(max_h, 120)

    # 冻结首行 + 自动筛选
    ws.freeze_panes = 'A2'
    ws.auto_filter.ref = ws.dimensions

    # 签名行
    sig_row = len(data) + 3
    sig_cell = ws.cell(row=sig_row, column=1, value='以上内容由 ECclaw 生成')
    sig_cell.font = Font(color='AAAAAA', size=9, italic=True)
    sig_cell.alignment = Alignment(horizontal='center')


def _save(wb, output_path: str):
    dirpath = os.path.dirname(os.path.abspath(output_path))
    if dirpath:
        os.makedirs(dirpath, exist_ok=True)
    wb.save(output_path)


# ─────────────────────────────────────────────────────────────────────────────
# CLI
# ─────────────────────────────────────────────────────────────────────────────

if __name__ == '__main__':
    parser = argparse.ArgumentParser(description='导出 Excel 文档')
    parser.add_argument('--title',  default='Sheet1', help='工作表标题（单 sheet 用）')
    parser.add_argument('--data',   default=None,     help='JSON 格式的数据列表（单 sheet 用）')
    parser.add_argument('--sheets', default=None,     help='多 sheet JSON：[{"title":...,"data":[...]},...]')
    parser.add_argument('--output', required=True,    help='输出文件路径')
    args = parser.parse_args()

    if args.sheets:
        sheets = json.loads(args.sheets)
        output = export_excel_multi(sheets, args.output)
    elif args.data:
        data   = json.loads(args.data)
        output = export_excel(args.title, data, args.output)
    else:
        print('错误：必须提供 --data 或 --sheets 参数')
        sys.exit(1)

    print(f'output_path: {output}')
    print('Excel 文档导出成功')