#!/usr/bin/env python3
"""Build the July/August Yifangbao phone workbook in the May sample style."""

from __future__ import annotations

import argparse
import re
from copy import copy
from pathlib import Path

import xlrd
from openpyxl import Workbook, load_workbook
from openpyxl.comments import Comment
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter


PHONE_RE = re.compile(r"(?:0\d{2,4}-?\d{6,8}(?:-\d{1,5})?|1[3-9]\d{9}|400-?\d{3}-?\d{4})")
SUPPLEMENT_HEADERS = [
    "最终可用电话（原表优先）",
    "本次新增中标单位电话",
    "本次来源类型",
    "本次置信度",
    "本次来源URL",
    "天眼查登记电话",
    "天眼查状态",
    "法定代表人（登记参考，不等同联系人）",
    "天眼查邮箱",
    "天眼查网址",
    "天眼查地址",
    "证据摘要",
    "缺口/说明",
]


def clean(value) -> str:
    if value is None:
        return ""
    if isinstance(value, float) and value.is_integer():
        return str(int(value))
    return str(value).strip()


def has_phone(value: str) -> bool:
    return bool(PHONE_RE.search(value or ""))


def read_xls(path: Path) -> list[list]:
    book = xlrd.open_workbook(str(path), formatting_info=False)
    sheet = book.sheet_by_index(0)
    rows = []
    for r in range(sheet.nrows):
        current = []
        for c in range(sheet.ncols):
            value = sheet.cell_value(r, c)
            if isinstance(value, float) and value.is_integer():
                value = int(value)
            current.append(value)
        rows.append(current)
    return rows


def read_audit(path: Path) -> dict[int, dict]:
    rows = read_xls(path)
    result = {}
    for row in rows[1:]:
        if not row or not clean(row[0]):
            continue
        excel_row = int(float(row[0]))
        result[excel_row] = {
            "winner": clean(row[1]),
            "phone": clean(row[2]),
            "status": clean(row[3]),
            "source_type": clean(row[4]),
            "source_url": clean(row[5]),
            "evidence": clean(row[6]),
        }
    return result


def copy_style(target, source) -> None:
    target.font = copy(source.font)
    target.fill = copy(source.fill)
    target.border = copy(source.border)
    target.alignment = copy(source.alignment)
    target.number_format = source.number_format
    target.protection = copy(source.protection)


def build(source_xls: Path, audit_xls: Path, sample_xlsx: Path, output_xlsx: Path) -> dict:
    source_rows = read_xls(source_xls)
    audit = read_audit(audit_xls)
    sample_wb = load_workbook(sample_xlsx)
    sample_ws = sample_wb["已补电话-新增标黄"]

    wb = Workbook()
    ws = wb.active
    ws.title = "已补电话-新增标黄"

    header_fill = PatternFill("solid", fgColor="D9EAF7")
    yellow = PatternFill("solid", fgColor="FFFF00")
    red = PatternFill("solid", fgColor="F4CCCC")
    gap_fill = PatternFill("solid", fgColor="FCE4D6")
    thin = Side(style="thin", color="D9E2EC")
    border = Border(bottom=thin)

    for col in range(1, 20):
        ws.cell(1, col).value = source_rows[0][col - 1] if col - 1 < len(source_rows[0]) else None
        ws.cell(2, col).value = source_rows[1][col - 1] if col - 1 < len(source_rows[1]) else None
    ws.cell(1, 20).value = "本次补充信息（新增内容，黄色区域）"
    ws.merge_cells(start_row=1, start_column=20, end_row=1, end_column=32)
    for idx, header in enumerate(SUPPLEMENT_HEADERS, start=20):
        ws.cell(2, idx).value = header

    stats = {
        "records": max(0, len(source_rows) - 2),
        "winner_named": 0,
        "original_winner_phone": 0,
        "new_written": 0,
        "confirmed_new": 0,
        "review_new": 0,
        "final_available": 0,
        "gaps": 0,
    }
    gap_rows = [["Excel行号", "项目名称", "发布省份", "中标单位", "缺口说明"]]

    for source_idx, row in enumerate(source_rows[2:], start=3):
        values = list(row[:19]) + [""] * max(0, 19 - len(row))
        winner = clean(values[12])
        winner_phone = clean(values[14])
        supplement = [""] * len(SUPPLEMENT_HEADERS)
        fill_for_added = None

        if winner:
            stats["winner_named"] += 1
        if has_phone(winner_phone):
            stats["original_winner_phone"] += 1
            stats["final_available"] += 1
            supplement[0] = winner_phone
            supplement[2] = "原表已有"
            supplement[3] = "high"
        elif source_idx in audit:
            info = audit[source_idx]
            phone = info["phone"]
            values[14] = phone
            stats["new_written"] += 1
            stats["final_available"] += 1
            if info["status"] == "review":
                stats["review_new"] += 1
                fill_for_added = red
                note = "来源需复核，已标红。"
            else:
                stats["confirmed_new"] += 1
                fill_for_added = yellow
                note = "本次确认新增，已标黄。"
            supplement = [
                phone,
                phone,
                info["source_type"],
                info["status"],
                info["source_url"],
                phone if info["status"] == "confirmed" else "",
                "",
                "",
                "",
                "",
                "",
                info["evidence"],
                note,
            ]
        elif not winner:
            stats["gaps"] += 1
            note = "原表未给出中标单位，无法补中标单位电话。"
            supplement[3] = "none"
            supplement[-1] = note
            gap_rows.append([source_idx, values[1], values[4], "", note])
        else:
            stats["gaps"] += 1
            note = "公开来源未找到可自动回填的完整电话，需人工核实。"
            supplement[3] = "none"
            supplement[-1] = note
            gap_rows.append([source_idx, values[1], values[4], winner, note])

        for col, value in enumerate(values + supplement, start=1):
            ws.cell(source_idx, col).value = value
        if fill_for_added:
            ws.cell(source_idx, 15).fill = fill_for_added
            ws.cell(source_idx, 15).comment = Comment("本次新增写回中标单位联系人电话列。", "Codex")
            for col in range(20, 33):
                ws.cell(source_idx, col).fill = fill_for_added if fill_for_added == red else yellow

    for col in range(1, 33):
        letter = get_column_letter(col)
        ws.column_dimensions[letter].width = sample_ws.column_dimensions[letter].width or 14
    ws.row_dimensions[1].height = sample_ws.row_dimensions[1].height or 24
    ws.row_dimensions[2].height = sample_ws.row_dimensions[2].height or 36
    ws.freeze_panes = "A3"
    ws.auto_filter.ref = f"A2:AF{ws.max_row}"

    for row in ws.iter_rows(min_row=1, max_row=ws.max_row, max_col=32):
        for cell in row:
            cell.border = border
            cell.alignment = Alignment(vertical="top", wrap_text=True)
    for col in range(1, 33):
        ws.cell(1, col).fill = header_fill
        ws.cell(2, col).fill = header_fill
        ws.cell(1, col).font = Font(bold=True)
        ws.cell(2, col).font = Font(bold=True)
        ws.cell(1, col).alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        ws.cell(2, col).alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    for col in range(20, 33):
        ws.cell(1, col).fill = yellow
        ws.cell(2, col).fill = yellow
    for row in range(3, ws.max_row + 1):
        if not ws.cell(row, 20).fill.fill_type:
            for col in range(20, 33):
                ws.cell(row, col).fill = yellow
        if ws.cell(row, 32).value and "需人工" in str(ws.cell(row, 32).value):
            ws.cell(row, 32).fill = gap_fill

    sop = wb.create_sheet("补联SOP")
    sop_rows = [
        ["项目", "7月乙方宝中标公示项目电话补全"],
        ["输入文件", str(source_xls)],
        ["参考样表", str(sample_xlsx)],
        ["输出文件", str(output_xlsx)],
        ["原始记录数", stats["records"]],
        ["中标单位非空记录数", stats["winner_named"]],
        ["原表已有中标单位电话", stats["original_winner_phone"]],
        ["本次新增写回原电话列", stats["new_written"]],
        ["其中确认新增标黄", stats["confirmed_new"]],
        ["其中疑问来源标红", stats["review_new"]],
        ["最终可用电话记录数", stats["final_available"]],
        ["缺口记录数", stats["gaps"]],
        ["颜色说明", "黄色为确认新增或补充信息区域；红色为来源需复核；橙色为缺口/疑问说明。"],
        ["口径", "原表已有电话优先；同表同名、历史已审核、官网/公告等公开资料可回填；集团号码、项目联系人、主体不完全直连的来源标红复核。"],
    ]
    for item in sop_rows:
        sop.append(item)
    sop.column_dimensions["A"].width = 28
    sop.column_dimensions["B"].width = 110
    for row in sop.iter_rows():
        for cell in row:
            cell.border = border
            cell.alignment = Alignment(vertical="top", wrap_text=True)
    sop["A1"].fill = gap_fill
    sop["B1"].fill = gap_fill
    sop["A1"].font = Font(bold=True)
    sop["B1"].font = Font(bold=True)

    gap = wb.create_sheet("缺口清单")
    for item in gap_rows:
        gap.append(item)
    for row in gap.iter_rows():
        for cell in row:
            cell.border = border
            cell.alignment = Alignment(vertical="top", wrap_text=True)
    for col in range(1, 6):
        gap.cell(1, col).fill = gap_fill
        gap.cell(1, col).font = Font(bold=True)
    for idx, width in enumerate([12, 56, 12, 32, 60], start=1):
        gap.column_dimensions[get_column_letter(idx)].width = width
    gap.freeze_panes = "A2"

    output_xlsx.parent.mkdir(parents=True, exist_ok=True)
    wb.save(output_xlsx)
    return stats


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--source-xls", required=True, type=Path)
    parser.add_argument("--audit-xls", required=True, type=Path)
    parser.add_argument("--sample-xlsx", required=True, type=Path)
    parser.add_argument("--output-xlsx", required=True, type=Path)
    args = parser.parse_args()
    print(build(args.source_xls, args.audit_xls, args.sample_xlsx, args.output_xlsx))
    return 0


if __name__ == "__main__":
    raise SystemExit(main())