#!/usr/bin/env python3
"""Build an Excel extraction workbook for the Haidian smart canteen materials."""

from __future__ import annotations

import re
from datetime import datetime
from pathlib import Path
from typing import Any

import pdfplumber
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]
BASE_DIR = ROOT / "docs" / "deliverables" / "20260615-haidian-zhihui-canteen-source-materials"
SOURCE_DIR = BASE_DIR / "source-documents"
OUTPUT_DIR = BASE_DIR / "output"
TENDER_PDF = SOURCE_DIR / "1包招标文件.pdf"
CONTRACT_PDF = SOURCE_DIR / "合同-公示.pdf"
INTENTION_IMAGE = SOURCE_DIR / "采购意向截图-乙方宝.jpg"
AWARD_IMAGE = SOURCE_DIR / "中标公告截图-乙方宝.png"
CONTRACT_OCR_DIR = ROOT / "tmp" / "pdfs" / "haidian-contract-render"

FONT = "Microsoft YaHei"
NAVY = "0B4F71"
BLUE = "D9EAF7"
GREEN = "E2F0D9"
YELLOW = "FFF2CC"
RED = "FCE4D6"
GRAY = "F4F6F8"
WHITE = "FFFFFF"
THIN = Side(style="thin", color="B7C9DA")
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)


def clean(value: Any) -> str:
    if value is None:
        return ""
    text = str(value).replace("\xa0", " ").replace("\n", "")
    text = re.sub(r"\s+", " ", text)
    return text.strip()


def style_cell(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=NAVY)
    cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    cell.border = BORDER
    ws.row_dimensions[row].height = 34


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(cell, BLUE, bold=True, color="14344A")
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    row_num = header_row
    for row in rows:
        row_num += 1
        fill = None
        if row and isinstance(row[0], str) and (row[0].startswith("小计") or row[0].startswith("注意")):
            fill = YELLOW
        for col, value in enumerate(row, 1):
            cell = ws.cell(row_num, col, value)
            style_cell(cell, fill)
    if rows:
        ws.auto_filter.ref = f"A{header_row}:{get_column_letter(len(headers))}{row_num}"
    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


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


def package_from_text(text: str, current: str) -> str:
    if "（第一包）" in text:
        return "第一包"
    if "（第二包）" in text:
        return "第二包"
    if "（第三包）" in text:
        return "第三包"
    return current


def extract_tender_tables() -> tuple[list[dict[str, str]], list[dict[str, str]], list[dict[str, str]]]:
    equipment: list[dict[str, str]] = []
    schools: list[dict[str, str]] = []
    tech_rows: list[dict[str, str]] = []
    package = "第一包"
    current_school: dict[str, str] | None = None
    current_device: dict[str, str] | None = None

    with pdfplumber.open(TENDER_PDF) as pdf:
        for page_index, page in enumerate(pdf.pages, start=1):
            if page_index < 34 or page_index > 55:
                continue
            text = page.extract_text() or ""
            package = package_from_text(text, package)
            for table in page.extract_tables() or []:
                rows = [[clean(cell) for cell in row] for row in table if row]
                if not rows:
                    continue
                max_cols = max(len(row) for row in rows)
                rows = [row + [""] * (max_cols - len(row)) for row in rows]
                header = rows[0]

                if max_cols == 5 and header[:4] == ["序号", "货物名称", "数量", "单位"]:
                    for row in rows[1:]:
                        if not row[0].isdigit():
                            continue
                        equipment.append(
                            {
                                "标包": package,
                                "序号": row[0],
                                "货物名称": row[1].replace("广温型", "高温型"),
                                "数量": row[2],
                                "单位": row[3],
                                "备注": row[4].replace("广温型", "高温型"),
                                "来源页码": str(page_index),
                            }
                        )
                    continue

                if max_cols == 4 and 35 <= page_index <= 43:
                    data_rows = rows[1:] if header[:4] == ["序号", "学校名称", "分校址", "地址"] else rows
                    for row in data_rows:
                        if row[0].isdigit():
                            current_school = {
                                "标包": package,
                                "序号": row[0],
                                "学校名称": row[1],
                                "分校址": row[2],
                                "地址": row[3],
                                "来源页码": str(page_index),
                            }
                            schools.append(current_school)
                        elif current_school:
                            current_school["学校名称"] += row[1]
                            current_school["分校址"] += row[2]
                            current_school["地址"] += row[3]
                            current_school["来源页码"] += f",{page_index}"
                    continue

                if max_cols == 4 and page_index >= 44:
                    data_rows = rows[1:] if ("设备名称" in header[1] or "产品参数" in header[2]) else rows
                    for row in data_rows:
                        seq, device, params, note = row[:4]
                        if seq == "序号":
                            continue
                        if seq.isdigit() and device:
                            current_device = {
                                "设备序号": seq,
                                "设备名称": device.replace("广温型", "高温型"),
                                "产品参数及功能要求原文": params.replace("广温型", "高温型"),
                                "备注": note,
                                "来源页码": str(page_index),
                            }
                            tech_rows.append(current_device)
                        elif current_device and params:
                            current_device["产品参数及功能要求原文"] += "\n" + params.replace("广温型", "高温型")
                            if note:
                                current_device["备注"] += "\n" + note
                            current_device["来源页码"] += f",{page_index}"

    for row in schools:
        for key in ("学校名称", "分校址", "地址"):
            row[key] = clean(row[key])
    return equipment, schools, tech_rows


def split_hardware_software(params: str) -> tuple[str, str]:
    text = params.replace("\r", "\n")
    software_markers = ["二、软件功能", "软件功能"]
    hardware = text
    software = ""
    for marker in software_markers:
        if marker in text:
            before, after = text.split(marker, 1)
            hardware = before
            software = marker + after
            break
    return hardware.strip(), software.strip()


def first_matching(text: str, patterns: list[str]) -> str:
    for pattern in patterns:
        match = re.search(pattern, text, flags=re.I)
        if match:
            return clean(match.group(0))
    return ""


def summarize_params(device: str, params: str) -> str:
    hw, sw = split_hardware_software(params)
    facts: list[str] = []
    for pattern in [
        r"CPU[^。；\n]{0,50}",
        r"内存[^。；\n]{0,35}",
        r"屏幕[^。；\n]{0,45}",
        r"测温[^。；\n]{0,60}",
        r"温度量程范围[:：][^。；\n]{0,30}",
        r"湿度量程范围[:：][^。；\n]{0,30}",
        r"防水等级[:：][^。；\n]{0,40}",
        r"工作频段[:：][^。；\n]{0,60}",
        r"开放接口[:：][^。；\n]{0,55}",
        r"功率≥[^。；\n]{0,20}",
        r"设备规格[^。；\n]{0,50}",
        r"最大称重重量≥[^。；\n]{0,30}",
        r"称重精度[^。；\n]{0,40}",
    ]:
        found = first_matching(hw, [pattern])
        if found and found not in facts:
            facts.append(found)
    if not facts:
        lines = [clean(line) for line in hw.splitlines() if clean(line)]
        facts = lines[:4]
    return "；".join(facts[:7]) or f"{device}参数见原文"


def summarize_software(params: str) -> str:
    _hw, sw = split_hardware_software(params)
    if not sw:
        return "未单列软件功能；按硬件/物联接口能力验收"
    keywords = []
    for keyword in [
        "晨检记录",
        "识别",
        "健康问询",
        "人员管理",
        "验货",
        "票证",
        "入库",
        "出库",
        "盘点",
        "库存预警",
        "保质期预警",
        "商品条码",
        "留样记录",
        "留样设置",
        "销样",
        "网络设置",
        "当前版本",
    ]:
        if keyword in sw and keyword not in keywords:
            keywords.append(keyword)
    return "、".join(keywords) if keywords else clean(sw[:220])


def read_contract_ocr() -> str:
    if not CONTRACT_OCR_DIR.exists():
        return ""
    parts: list[str] = []
    for path in sorted(CONTRACT_OCR_DIR.glob("page-*.txt")):
        parts.append(f"--- {path.stem} ---\n{path.read_text(encoding='utf-8', errors='ignore').strip()}")
    return "\n\n".join(parts)


def add_overview(wb: Workbook) -> None:
    ws = add_sheet(wb, "材料总览")
    rows = [
        ["招标文件 PDF", "1包招标文件.pdf", "已解析", "[ID40203]资核中心中小学智慧食堂项目；采购人北京市海淀区教育装备资产和财务核算中心；采购编号 11010825210200047551-XM001/TXJ-010-20250267。"],
        ["合同公示 PDF", "合同-公示.pdf", "已 OCR/人工校验", "对应另一个项目：海淀区校园智慧食堂食安管理系统建设项目；甲方北京市海淀区教育科学研究院；乙方北京鼎合思锐软件技术有限公司。"],
        ["乙方宝采购意向截图", "采购意向截图-乙方宝.jpg", "已录入线索", "[ID40152]海淀区校园智慧食堂食安管理系统建设；预算 1805.786791 万元；预计采购时间 2025-07。"],
        ["乙方宝中标公告截图", "中标公告截图-乙方宝.png", "已录入线索", "[ID40203]资核中心中小学智慧食堂项目中标公告；中标金额 2029.38 万元；中标单位北京金丽厨厨房设备有限公司；项目编号 11010825210200047551-XM001。"],
        ["网络抓取状态", "乙方宝/搜索引擎", "当前未触发扫码", "本地 PDF 与截图已能覆盖评分标准、设备清单、技术参数和中标线索；如要下载乙方宝页面底部附件，可能需要扫码登录。"],
    ]
    write_table(ws, 1, "材料总览与项目对应关系", ["材料类型", "文件/渠道", "处理状态", "结论"], rows)
    set_widths(ws, [22, 36, 18, 88])
    ws.freeze_panes = "A3"


def add_project_overview(wb: Workbook) -> None:
    ws = add_sheet(wb, "ID40203项目概览")
    rows = [
        ["项目名称", "[ID40203]资核中心中小学智慧食堂项目", "招标文件封面/第五章", "确定"],
        ["采购人", "北京市海淀区教育装备资产和财务核算中心", "招标文件封面", "确定"],
        ["采购编号", "11010825210200047551-XM001/TXJ-010-20250267", "招标文件封面", "确定"],
        ["招标代理", "天行健项目管理咨询（北京）有限公司", "招标文件封面", "确定"],
        ["项目预算/最高限价", "2092.894 万元；第一包 697.203 万元，第二包 696.005 万元，第三包 699.686 万元", "招标文件第 3、34、37、40 页", "确定"],
        ["采购内容", "区属中小学智慧食堂相关设备，共三个标包。设备包括管理一体机、智能验货机、库管手持机、智能留样仪、无线温湿度/温度监测、中心温度计、无线智能开关面板、万能蒸烤箱等。", "招标文件第五章", "确定"],
        ["交货期", "接到采购人通知后，中标人组织检测，25 日历天内完成供货。", "招标文件第 34 页", "确定"],
        ["质保期", "验收合格或视为验收合格后 36 个月。", "招标文件第 56 页", "确定"],
        ["中标公告线索", "北京金丽厨厨房设备有限公司；中标金额 2029.38 万元。", "乙方宝截图", "截图线索，需以官方中标公告附件复核"],
        ["投标公司名单", "当前附件未披露完整 3 家投标公司名单。招标文件规定各采购包推荐 3 名中标候选人，但名单需中标候选人/评标报告/开标记录附件。", "招标文件第 31 页+乙方宝截图", "待下载附件补全"],
    ]
    write_table(ws, 1, "ID40203 项目概览", ["字段", "内容", "来源", "状态"], rows)
    set_widths(ws, [24, 82, 34, 24])
    ws.freeze_panes = "A3"


def add_scoring(wb: Workbook) -> None:
    ws = add_sheet(wb, "ID40203评分标准")
    rows = [
        ["投标报价", 30, "价格公式", "满足招标文件要求且投标价格最低的投标报价为评标基准价，价格分为满分。其他投标人的价格分=评标基准价/投标报价×30。", "招标文件第 32 页"],
        ["商务部分-业绩", 2, "客观材料", "近三年（2022-08-01 至开标前一日，以合同签订日期为准）类似项目业绩，每提供一份有效业绩得 1 分，最多 2 分。需提供合同首页、货物内容页、合同金额页、签字盖章页。", "招标文件第 32 页"],
        ["商务部分-项目团队", 4, "分档评分", "项目团队人员组织机构合理、人员数量充足、专业齐全、安排得当且完全满足项目需要得 4 分；组织机构不完善、人员较少、专业有欠缺、安排混乱得 2 分；未提供或严重缺陷得 0 分。", "招标文件第 32 页"],
        ["技术服务-技术参数", 30, "扣分制", "全部满足招标需求全部指标得 30 分。# 重要指标每项不满足扣 0.8 分；一般指标每项不满足扣 0.4 分，扣完为止。★为实质性响应指标，不满足按废标处理。", "招标文件第 32、34 页"],
        ["技术服务-技术方案", 15, "分档评分", "围绕产品技术方案、接口方案、功能方案：完整详细可靠且无缺项得 15；相对完整可靠得 12；完整性一般、有缺项得 9；简单且有偏差得 6；缺项多偏差大得 3；基本不符合或未提供得 0。", "招标文件第 32 页"],
        ["技术服务-实施方案", 10, "分档评分", "围绕项目实施、交货、安装、应急预案：完全满足得 10；基本满足得 7；完整性一般得 5；完整性较低且常规通用得 3；不符合或未提供得 0。", "招标文件第 33 页"],
        ["技术服务-培训方案", 5, "分档评分", "围绕人员晨检、食材验收、出入库操作、食品出锅测温、紫外线灯控制和烹饪培训：完全符合得 5；部分符合得 3；通用方案得 1；逻辑漏洞或未提供得 0。", "招标文件第 33 页"],
        ["技术服务-售后服务方案", 4, "分档评分", "售后服务安排、维修反应速度、服务措施完整详细、可靠、响应快且针对本项目得 4；简单、有缺项、响应慢、通用得 2；缺项严重或未提供得 0。", "招标文件第 33 页"],
    ]
    write_table(ws, 1, "综合评分标准明细（总分 100）", ["评分项", "分值", "计算/评审方式", "具体标准", "来源"], rows)
    write_table(
        ws,
        ws.max_row + 2,
        "报价分计算示例",
        ["场景", "计算方式", "说明"],
        [
            ["最低有效报价供应商", "报价分=30", "只要满足招标文件要求且价格最低，该供应商报价分为满分。"],
            ["其他供应商", "报价分=最低有效报价/该供应商报价×30", "例如最低有效报价 650 万、某供应商报价 697.203 万，则报价分约 650/697.203×30=27.97。"],
            ["注意", "本项目招标文件价格满分是 30 分", "你之前南京项目截图中的 42 分/35 分属于南京项目自己的评分体系，不能套到本海淀项目。"],
        ],
    )
    set_widths(ws, [26, 10, 22, 96, 26])
    ws.freeze_panes = "A3"


def add_equipment(wb: Workbook, equipment: list[dict[str, str]]) -> None:
    ws = add_sheet(wb, "ID40203设备清单")
    rows = [[r["标包"], r["序号"], r["货物名称"], r["数量"], r["单位"], r["备注"], r["来源页码"]] for r in equipment]
    totals: dict[str, int] = {}
    for row in equipment:
        key = f"{row['货物名称']}（{row['单位']}）"
        totals[key] = totals.get(key, 0) + int(row["数量"])
    rows.extend([["小计-三包合计", "", name, qty, "", "", "招标文件第 34/37/40 页"] for name, qty in totals.items()])
    write_table(ws, 1, "三包设备采购清单", ["标包", "序号", "货物名称", "数量", "单位", "备注/用途", "来源页码"], rows)
    set_widths(ws, [14, 10, 34, 12, 10, 34, 18])
    ws.freeze_panes = "A3"


def add_technical(wb: Workbook, tech_rows: list[dict[str, str]]) -> None:
    ws = add_sheet(wb, "ID40203硬件设备参数")
    rows = []
    for item in tech_rows:
        hw, _sw = split_hardware_software(item["产品参数及功能要求原文"])
        rows.append(
            [
                item["设备序号"],
                item["设备名称"],
                summarize_params(item["设备名称"], item["产品参数及功能要求原文"]),
                hw,
                item["来源页码"],
            ]
        )
    write_table(ws, 1, "硬件设备参数（含原文）", ["序号", "设备名称", "关键参数提炼", "招标文件参数原文", "来源页码"], rows)
    set_widths(ws, [8, 28, 64, 120, 18])
    ws.freeze_panes = "A3"

    ws2 = add_sheet(wb, "ID40203软件功能")
    software_rows = []
    for item in tech_rows:
        _hw, sw = split_hardware_software(item["产品参数及功能要求原文"])
        software_rows.append(
            [
                item["设备序号"],
                item["设备名称"],
                summarize_software(item["产品参数及功能要求原文"]),
                sw or "该设备未在参数表中单列软件功能；主要按硬件参数、接口和平台对接能力验收。",
                item["来源页码"],
            ]
        )
    write_table(ws2, 1, "软件功能与设备端功能要求", ["序号", "设备/模块", "功能提炼", "功能原文", "来源页码"], software_rows)
    set_widths(ws2, [8, 28, 54, 120, 18])
    ws2.freeze_panes = "A3"


def add_schools(wb: Workbook, schools: list[dict[str, str]]) -> None:
    ws = add_sheet(wb, "ID40203建设范围")
    rows = [[r["标包"], r["序号"], r["学校名称"], r["分校址"], r["地址"], r["来源页码"]] for r in schools]
    write_table(ws, 1, "建设范围学校清单", ["标包", "序号", "学校名称", "分校址", "地址", "来源页码"], rows)
    set_widths(ws, [14, 10, 46, 28, 64, 16])
    ws.freeze_panes = "A3"


def add_contract(wb: Workbook) -> None:
    ws = add_sheet(wb, "ID40152合同公示")
    rows = [
        ["项目名称", "海淀区校园智慧食堂食安管理系统建设项目", "合同封面/OCR/乙方宝采购意向截图", "与 ID40203 不是同一项目；属于同一海淀智慧食堂方向。"],
        ["甲方", "北京市海淀区教育科学研究院", "合同封面/采购意向截图", "截图显示招标单位为北京市海淀区教育科学研究院。"],
        ["乙方", "北京鼎合思锐软件技术有限公司", "合同封面人工识别", "OCR 对公司名有误读，需以原 PDF 图像和官方合同公示为准。"],
        ["采购意向金额", "1805.786791 万元", "乙方宝截图", "采购意向金额，不等同于合同最终金额。"],
        ["预计采购时间", "2025-07", "乙方宝截图", "采购意向披露字段。"],
        ["采购目标", "通过海淀区校园智慧食堂食安管理系统平台，提高监管效率，实现数据快速采集、整合、分析，支撑违规发现和应对。", "乙方宝截图", "截图可见完整需求摘要。"],
    ]
    write_table(ws, 1, "ID40152 合同公示/采购意向线索", ["字段", "内容", "来源", "备注"], rows)
    ocr = read_contract_ocr()
    if ocr:
        write_table(ws, ws.max_row + 2, "合同 OCR 原始摘录（仅作检索，不作为最终校验文本）", ["OCR文本"], [[ocr[:30000]]])
    set_widths(ws, [24, 72, 34, 60])
    ws.freeze_panes = "A3"


def add_company_qualifications(wb: Workbook) -> None:
    ws = add_sheet(wb, "公司资质与投标方")
    rows = [
        [
            "北京金丽厨厨房设备有限公司",
            "ID40203 中标单位",
            "中标金额 2029.38 万元；经营状态存续；法定代表人/负责人夏帅华；注册资本 8000 万；人员规模小于 50 人；截图标签显示“芝麻企业信用、拥有发明专利、拥有节能环保技术”。",
            "乙方宝中标公告截图",
            "中标单位和资质标签已取得；营业执照、资质证书、发明专利明细、节能环保技术证书编号需通过乙方宝/天眼查/爱企查登录后复核。",
        ],
        [
            "北京市海淀区教育装备资产和财务核算中心",
            "ID40203 招标单位",
            "北京市海淀区教育装备资产和财务核算中心为该中标公告截图中的招标单位。",
            "乙方宝中标公告截图/招标文件封面",
            "采购人信息已确认。",
        ],
        [
            "北京鼎合思锐软件技术有限公司",
            "ID40152 合同乙方",
            "合同封面显示其为“海淀区校园智慧食堂食安管理系统建设项目”的乙方。",
            "合同-公示.pdf",
            "该公司对应 ID40152，不是 ID40203 中标单位；合同 PDF 为扫描件，企业名称以原件图像和官方合同公示为准。",
        ],
        [
            "3 家投标公司名单",
            "ID40203 待补",
            "当前招标文件和用户截图只披露中标单位，未披露完整 3 家投标公司/候选人名单。",
            "当前附件",
            "需要下载乙方宝底部附件、官方中标公告附件、开标记录或评标报告。若乙方宝附件需要登录，可以让用户扫码后继续抓取。",
        ],
    ]
    write_table(ws, 1, "公司资质与投标方线索", ["公司/对象", "角色", "已抓到信息", "来源", "缺口/下一步"], rows)

    checklist = [
        ["评分标准", "已完成", "价格 30、商务 6、技术服务 64，含报价公式和各分项标准。"],
        ["三包设备清单", "已完成", "第一包 11 项、第二包 11 项、第三包 11 项，并按设备合并小计。"],
        ["硬件设备参数", "已完成", "11 类设备参数均已按 PDF 原文抽取，含页码。"],
        ["软件/设备端功能", "已完成", "管理一体机、智能验货机、库管手持机、智能留样仪等软件功能已抽取；纯传感器类按接口/监测能力说明。"],
        ["建设范围", "已完成", "第一包 63、第二包 64、第三包 61，合计 188 个建设点。"],
        ["中标单位", "部分完成", "截图显示北京金丽厨厨房设备有限公司，中标金额 2029.38 万元。"],
        ["中标单位资质证书", "部分完成", "截图仅有企业信用/专利/节能环保标签，未取得证书编号和原件。"],
        ["3 家投标公司名单", "未完成", "当前材料未披露，需下载中标附件/候选人公示/评标报告。"],
        ["合同公示", "部分完成", "合同 PDF 为 ID40152 项目，已 OCR 并标注项目差异。"],
    ]
    write_table(ws, ws.max_row + 2, "资料完整度检查", ["信息项", "状态", "说明"], checklist)
    set_widths(ws, [34, 22, 86, 34, 68])
    ws.freeze_panes = "A3"


def add_source_links(wb: Workbook) -> None:
    ws = add_sheet(wb, "来源链接与附件")
    rows = [
        ["北京市政府采购电子交易平台", "http://zbcg-bjzc.zhongcy.com/bjczj-portal-site/index.html#/home", "招标文件写明的获取招标文件平台。"],
        ["乙方宝采购意向截图", str(INTENTION_IMAGE), "用户提供截图，作为 ID40152 采购意向线索。"],
        ["乙方宝中标公告截图", str(AWARD_IMAGE), "用户提供截图，作为 ID40203 中标线索。"],
        ["招标文件原件", str(TENDER_PDF), "本次解析的 ID40203 招标文件。"],
        ["合同公示原件", str(CONTRACT_PDF), "本次解析的 ID40152 合同公示扫描件。"],
    ]
    write_table(ws, 1, "来源链接与附件位置", ["来源", "链接/路径", "用途"], rows)
    set_widths(ws, [32, 110, 60])
    ws.freeze_panes = "A3"


def build() -> Path:
    OUTPUT_DIR.mkdir(parents=True, exist_ok=True)
    equipment, schools, tech_rows = extract_tender_tables()
    wb = Workbook()
    wb.remove(wb.active)
    add_overview(wb)
    add_project_overview(wb)
    add_scoring(wb)
    add_equipment(wb, equipment)
    add_technical(wb, tech_rows)
    add_schools(wb, schools)
    add_contract(wb)
    add_company_qualifications(wb)
    add_source_links(wb)
    wb.properties.creator = "Codex"
    wb.properties.title = "海淀智慧食堂项目资料抽取"
    wb.properties.created = datetime.now()
    output = OUTPUT_DIR / "海淀智慧食堂项目资料抽取.xlsx"
    wb.save(output)
    return output


if __name__ == "__main__":
    print(build())