#!/usr/bin/env python3
"""Build compact template-style workbooks for the three candidate projects."""

from __future__ import annotations

import importlib.util
from pathlib import Path
from typing import Any

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


ROOT = Path(__file__).resolve().parents[1]
DETAIL_SCRIPT = ROOT / "scripts" / "build_full_candidate_tender_excels.py"
OUTPUT_DIR = ROOT / "docs" / "deliverables" / "20260615-full-candidate-tender-details-compact"

FONT = "Microsoft YaHei"
BLUE = "1F4E79"
LIGHT_BLUE = "D9EAF7"
GREEN = "E2F0D9"
YELLOW = "FFF2CC"
RED = "FCE4D6"
WHITE = "FFFFFF"
BORDER = Border(
    left=Side(style="thin", color="B7C9DA"),
    right=Side(style="thin", color="B7C9DA"),
    top=Side(style="thin", color="B7C9DA"),
    bottom=Side(style="thin", color="B7C9DA"),
)


def load_detail_module() -> Any:
    spec = importlib.util.spec_from_file_location("full_candidate_builder", DETAIL_SCRIPT)
    if spec is None or spec.loader is None:
        raise RuntimeError(f"Cannot load {DETAIL_SCRIPT}")
    module = importlib.util.module_from_spec(spec)
    spec.loader.exec_module(module)
    return module


def style(cell, fill: str | None = None, bold: bool = False, color: str = "1F1F1F") -> None:
    cell.font = Font(name=FONT, size=10, bold=bold, color=color)
    cell.alignment = Alignment(wrap_text=True, vertical="top")
    cell.border = BORDER
    if fill:
        cell.fill = PatternFill("solid", fgColor=fill)


def title_row(ws, row: int, title: str, cols: int) -> None:
    ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=cols)
    cell = ws.cell(row, 1, title)
    cell.font = Font(name=FONT, size=15, bold=True, color=WHITE)
    cell.fill = PatternFill("solid", fgColor=BLUE)
    cell.alignment = Alignment(horizontal="center", vertical="center")
    cell.border = BORDER
    ws.row_dimensions[row].height = 32


def write_table(ws, start_row: int, title: str, headers: list[str], rows: list[list[Any]]) -> int:
    title_row(ws, start_row, title, len(headers))
    header_row = start_row + 1
    for col, header in enumerate(headers, 1):
        cell = ws.cell(header_row, col, header)
        style(cell, LIGHT_BLUE, True, "14344A")
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    row_num = header_row
    for row in rows:
        row_num += 1
        for col, value in enumerate(row, 1):
            fill = None
            if value == "✓":
                fill = GREEN
            elif value in {"△", "部分", "待登录"}:
                fill = YELLOW
            elif value == "✗":
                fill = RED
            style(ws.cell(row_num, col, value), fill)
    return row_num + 2


def set_widths(ws, widths: list[int]) -> None:
    for idx, width in enumerate(widths, 1):
        ws.column_dimensions[get_column_letter(idx)].width = width
    for row in range(1, ws.max_row + 1):
        ws.row_dimensions[row].height = 42
    ws.freeze_panes = "A3"
    for table in ws.tables.values():
        table.ref = table.ref


def add_sheet(wb: Workbook, name: str) -> Any:
    ws = wb.create_sheet(name)
    ws.sheet_view.showGridLines = False
    return ws


def find_extra(spec: dict[str, Any], name: str) -> dict[str, Any] | None:
    for sheet in spec.get("extra_sheets", []):
        if sheet["name"] == name:
            return sheet
    return None


def normalize_row(row: list[Any], width: int) -> list[Any]:
    return (row + [""] * width)[:width]


def score_rows(spec: dict[str, Any]) -> list[list[Any]]:
    rows: list[list[Any]] = []
    for row in spec["scores"]:
        values = normalize_row(row, 6)
        rows.append(["公开评分/候选", values[1], f"{values[2]}；{values[3]}", f"{values[4]}；{values[5]}", "原评分/候选公示"])
    explanation = find_extra(spec, "得分解释")
    if explanation:
        for question, answer, data, conclusion in explanation["rows"]:
            rows.append(["得分解释", question, answer, data, conclusion])
    nj_formula = find_extra(spec, "报价分公式追踪")
    if nj_formula:
        for subject, data, analysis, conclusion in nj_formula["rows"][:5]:
            rows.append(["报价分/公式", subject, data, analysis, conclusion])
    citic_prices = find_extra(spec, "候选公司价格画像")
    if citic_prices:
        for row in citic_prices["rows"]:
            rows.append(["价格画像", row[2], f"16项单价简单合计：{row[3]}", f"最低/并列最低项数：{row[4]}", row[6]])
    abchina_prices = find_extra(spec, "候选报价排序对应")
    if abchina_prices:
        for row in abchina_prices["rows"]:
            rows.append(["报价排序", row[2], f"投标单价：{row[3]}；价格排名：{row[4]}", f"较最低价高出：{row[5]}", row[6]])
    return rows


def qualification_rows(spec: dict[str, Any]) -> list[list[Any]]:
    rows = [normalize_row(row, 5) for row in spec["qualifications"]]
    supplier_sheet = find_extra(spec, "供应商公开资质证书")
    if supplier_sheet:
        start = len(rows) + 1
        for idx, row in enumerate(supplier_sheet["rows"], start):
            rows.append([idx, f"{row[0]}｜{row[1]}", row[2], "△", f"{row[3]}：{row[4]}；{row[5]}"])
    return rows


def overview_next_actions(spec: dict[str, Any]) -> list[list[Any]]:
    rows: list[list[Any]] = []
    for idx, row in enumerate(spec["source_channel_rows"], 1):
        rows.append([idx, row[0], row[1], row[3], row[4]])
    return rows


def write_compact_workbook(spec: dict[str, Any]) -> Path:
    wb = Workbook()
    wb.remove(wb.active)

    ws = add_sheet(wb, "项目概览")
    next_row = write_table(ws, 1, "项目基本信息", ["字段", "值", "状态", "来源"], spec["overview"])
    write_table(ws, next_row, "来源与下一步", ["序号", "渠道", "要抓的信息", "登录/扫码状态", "写入规则"], overview_next_actions(spec))
    set_widths(ws, [10, 32, 70, 34, 62])

    ws = add_sheet(wb, "产品清单")
    write_table(
        ws,
        1,
        "产品清单（精简模板口径）",
        ["序号", "分项名称", "技术参数", "供应商", "产地", "统一信用代码", "规模", "品牌", "规格型号", "金额/单价", "数量/范围", "备注"],
        spec["products"],
    )
    set_widths(ws, [8, 28, 74, 28, 12, 22, 12, 18, 26, 18, 20, 42])

    ws = add_sheet(wb, "软件端详细技术参数表")
    write_table(ws, 1, "软件端详细技术参数表", ["序号", "功能模块", "功能要求", "披露状态", "来源/判断"], spec["software"])
    set_widths(ws, [8, 30, 86, 26, 54])

    ws = add_sheet(wb, "硬件设备参数")
    next_row = write_table(ws, 1, "硬件设备参数", ["序号", "设备/范围", "型号/参数", "数量/范围", "价格/权重", "说明", "状态"], spec["hardware"])
    package_rows = [normalize_row(row, 6) for row in spec.get("package_rows", [])]
    write_table(ws, next_row, "分包与设备清单", ["设备/货物", "候选1/限价", "候选2/权重", "候选3", "候选4", "候选5"], package_rows)
    set_widths(ws, [8, 34, 56, 24, 24, 78, 14])

    ws = add_sheet(wb, "公司资质")
    write_table(ws, 1, "公司/供应商资质", ["序号", "资质/条件", "详细要求", "状态", "来源/说明"], qualification_rows(spec))
    set_widths(ws, [8, 36, 92, 14, 82])

    ws = add_sheet(wb, "评分标准与评分表")
    write_table(ws, 1, "评分标准与评分表", ["类型", "对象", "公开内容/公式", "数据/解释", "结论/来源"], score_rows(spec))
    set_widths(ws, [18, 42, 86, 72, 68])

    ws = add_sheet(wb, "建设范围")
    write_table(ws, 1, "建设范围", ["序号", "范围", "区域", "地址/点位", "建设内容", "状态", "来源"], spec["scope"])
    set_widths(ws, [8, 34, 18, 34, 78, 14, 72])

    ws = add_sheet(wb, "3家投标公司与中标原因")
    next_row = write_table(ws, 1, "3家投标公司分析", ["排名", "公司", "报价/金额", "得分", "角色", "分析"], spec["bidders"])
    write_table(ws, next_row, "中标原因/缺口", ["类型", "结论"], spec["reason"])
    set_widths(ws, [14, 46, 26, 18, 24, 86])

    output = OUTPUT_DIR / spec["filename"]
    wb.save(output)
    return output


def main() -> None:
    OUTPUT_DIR.mkdir(parents=True, exist_ok=True)
    module = load_detail_module()
    for spec in module.build_specs():
        print(write_compact_workbook(spec))


if __name__ == "__main__":
    main()