# -*- coding: utf-8 -*-
"""Excel 导出器 - 双工作表，板块10(币)/151(钞)分别一个工作表"""

from __future__ import annotations

import json
import os
from pathlib import Path

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

from .daily_crawler import MatchedPost


# 帖子信息列（两表共享）
POST_INFO_COLUMNS = [
    ("板块ID", "board_id", 8),
    ("主题ID", "topic_id", 10),
    ("主题标题", "title", 40),
    ("作者", "author_name", 12),
    ("最后更新", "last_update_time", 20),
    ("命中关键字", "matched_keyword", 10),
    ("帖子URL", "url", 40),
    ("正文摘要", "post_text", 50),
]

# 板块10（纪念币）价格列
COIN_PRICE_COLUMNS = [
    ("品种名称", "name", 12),
    ("参考价", "ref_price", 10),
    ("盒价(元)", "盒价", 10),
    ("卷价(元)", "卷价", 10),
    ("散新价(元)", "散新价", 10),
    ("卡册价(元)", "卡册价", 10),
    ("单位", "unit", 8),
    ("需求数量", "quantity", 10),
    ("数量单位", "quantity_unit", 8),
    ("包装要求", "package_req", 20),
    ("品相要求", "quality_req", 20),
    ("其他备注", "other_notes", 30),
    ("原始文本", "raw_text", 40),
]

# 板块151（纪念钞）价格列 - 基础列，详细价格从 price_details 动态展开
BANKNOTE_PRICE_COLUMNS = [
    ("品种名称", "name", 12),
    ("参考价", "ref_price", 10),
    ("单位", "unit", 8),
    ("需求数量", "quantity", 10),
    ("数量单位", "quantity_unit", 8),
    ("包装要求", "package_req", 20),
    ("品相要求", "quality_req", 20),
    ("其他备注", "other_notes", 30),
    ("原始文本", "raw_text", 40),
]


def _make_header(ws, columns, start_col=1):
    """写表头：蓝底白字"""
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    for col_idx, (header, _, width) in enumerate(columns, start_col):
        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.font = header_font
        cell.fill = header_fill
        # 列宽：openpyxl 列字母
        from openpyxl.utils import get_column_letter
        ws.column_dimensions[get_column_letter(col_idx)].width = width


def _write_post_row(ws, post: MatchedPost, row: int, start_col=1):
    """写帖子信息行"""
    values = {
        "board_id": post.board_id,
        "topic_id": post.topic_id,
        "title": post.title,
        "author_name": post.author_name,
        "last_update_time": post.last_update_time.strftime("%Y-%m-%d %H:%M"),
        "matched_keyword": post.matched_keyword,
        "url": post.url,
        "post_text": post.post_text[:200],
    }
    for col_idx, (_, key, _) in enumerate(POST_INFO_COLUMNS, start_col):
        ws.cell(row=row, column=col_idx, value=values.get(key, ""))


def export_daily_excel(matched_posts: list[MatchedPost], trade_date: str, output_path: str) -> str:
    """导出双工作表 Excel。返回输出路径。"""
    os.makedirs(os.path.dirname(output_path), exist_ok=True)
    wb = Workbook()
    wb.remove(wb.active)

    # 板块10（纪念币）工作表
    coin_posts = [p for p in matched_posts if p.board_id == 10]
    ws_coin = wb.create_sheet(title="板块10")
    coin_columns = POST_INFO_COLUMNS + COIN_PRICE_COLUMNS
    _make_header(ws_coin, coin_columns)
    row = 2
    for post in coin_posts:
        if not post.coin_records:
            # 无解析记录，仅写帖子信息
            _write_post_row(ws_coin, post, row)
            row += 1
            continue
        for rec in post.coin_records:
            _write_post_row(ws_coin, post, row)
            # 写价格列
            price_values = {
                "name": rec.name,
                "ref_price": rec.ref_price,
                "盒价": rec.price_details.get("盒价", ""),
                "卷价": rec.price_details.get("卷价", ""),
                "散新价": rec.price_details.get("散新价", ""),
                "卡册价": rec.price_details.get("卡册价", ""),
                "unit": rec.unit,
                "quantity": rec.quantity,
                "quantity_unit": rec.quantity_unit,
                "package_req": rec.package_req,
                "quality_req": rec.quality_req,
                "other_notes": rec.other_notes,
                "raw_text": rec.raw_text,
            }
            start_price_col = len(POST_INFO_COLUMNS) + 1
            for col_idx, (_, key, _) in enumerate(COIN_PRICE_COLUMNS, start_price_col):
                ws_coin.cell(row=row, column=col_idx, value=price_values.get(key, ""))
            row += 1

    # 板块151（纪念钞）工作表
    banknote_posts = [p for p in matched_posts if p.board_id == 151]
    ws_bn = wb.create_sheet(title="板块151")
    # 收集所有出现的详细价格字段名（从 price_details）
    bn_detail_keys: list[str] = []
    seen_keys: set[str] = set()
    for post in banknote_posts:
        for rec in post.coin_records:
            for k in rec.price_details.keys():
                if k not in seen_keys:
                    seen_keys.add(k)
                    bn_detail_keys.append(k)
    # 动态构建钞价格列：基础列 + 动态详细价格列
    bn_price_columns = BANKNOTE_PRICE_COLUMNS[:2]  # 品种名称、参考价
    for k in bn_detail_keys:
        bn_price_columns.append((k, k, 12))
    bn_price_columns += BANKNOTE_PRICE_COLUMNS[2:]  # 单位、数量等

    bn_columns = POST_INFO_COLUMNS + bn_price_columns
    _make_header(ws_bn, bn_columns)
    row = 2
    for post in banknote_posts:
        if not post.coin_records:
            _write_post_row(ws_bn, post, row)
            row += 1
            continue
        for rec in post.coin_records:
            _write_post_row(ws_bn, post, row)
            price_values = {
                "name": rec.name,
                "ref_price": rec.ref_price,
                "unit": rec.unit,
                "quantity": rec.quantity,
                "quantity_unit": rec.quantity_unit,
                "package_req": rec.package_req,
                "quality_req": rec.quality_req,
                "other_notes": rec.other_notes,
                "raw_text": rec.raw_text,
            }
            # 合并动态详细价格
            for k in bn_detail_keys:
                price_values[k] = rec.price_details.get(k, "")
            start_price_col = len(POST_INFO_COLUMNS) + 1
            for col_idx, (_, key, _) in enumerate(bn_price_columns, start_price_col):
                ws_bn.cell(row=row, column=col_idx, value=price_values.get(key, ""))
            row += 1

    wb.save(output_path)
    return output_path