#!/usr/bin/env python3
"""Create a Yifangbao phone enrichment workbook in the user's preferred 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,3}-?\d{7,8}|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 normalize_phone(phone: str) -> str:
    return re.sub(r"\D", "", phone or "")


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


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


def reference_phone_map(path: Path) -> dict[str, dict]:
    wb = load_workbook(path, data_only=True)
    ws = wb[wb.sheetnames[0]]
    result: dict[str, dict] = {}
    for row in range(3, ws.max_row + 1):
        name = clean(ws.cell(row, 13).value)
        phone = clean(ws.cell(row, 20).value or ws.cell(row, 21).value or ws.cell(row, 15).value)
        if not name or not valid_phone(phone):
            continue
        result[name] = {
            "phone": phone,
            "source_type": clean(ws.cell(row, 22).value) or "reference_workbook",
            "confidence": clean(ws.cell(row, 23).value) or "high",
            "source_url": clean(ws.cell(row, 24).value),
            "tyc_phone": clean(ws.cell(row, 25).value),
            "tyc_status": clean(ws.cell(row, 26).value),
            "legal_person": clean(ws.cell(row, 27).value),
            "email": clean(ws.cell(row, 28).value),
            "website": clean(ws.cell(row, 29).value),
            "address": clean(ws.cell(row, 30).value),
            "evidence": clean(ws.cell(row, 31).value),
            "note": clean(ws.cell(row, 32).value) or "来自用户提供的已补电话样表，同名中标单位复用。",
        }
    return result


def same_role_internal_map(rows: list[list]) -> dict[str, set[str]]:
    phones: dict[str, set[str]] = {}
    for row in rows[2:]:
        name = clean(row[12] if len(row) > 12 else "")
        phone = clean(row[14] if len(row) > 14 else "")
        if name and valid_phone(phone):
            phones.setdefault(name, set()).add(phone)
    return phones


def make_summary_rows(stats: dict, input_path: Path, output_path: Path, reference_path: Path) -> list[list]:
    return [
        ["项目", "乙方宝中标公示项目电话补全"],
        ["输入文件", str(input_path)],
        ["参考样表", str(reference_path)],
        ["输出文件", str(output_path)],
        ["原始记录数", stats["records"]],
        ["中标单位非空记录数", stats["winner_named"]],
        ["原表已有中标单位电话", stats["original_winner_phone"]],
        ["本次新增写回原电话列", stats["new_written"]],
        ["其中来自5月样表复用", stats["reference_reuse"]],
        ["其中来自当前表内复用", stats["internal_reuse"]],
        ["最终可用电话记录数", stats["final_available"]],
        ["缺口记录数", stats["gaps"]],
        ["颜色说明", "黄色为本次新增或补充信息区域；橙色为缺口/疑问说明。"],
        ["口径", "原表已有电话优先；同名中标单位在5月样表已核电话可复用；候选电话与同项目招标单位电话相同则不写入。"],
    ]


def style_cell_like(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 write_workbook(
    rows: list[list],
    output_path: Path,
    reference_path: Path,
    input_path: Path,
) -> dict:
    ref_wb = load_workbook(reference_path)
    ref_ws = ref_wb[ref_wb.sheetnames[0]]
    ref_map = reference_phone_map(reference_path)
    internal_map = same_role_internal_map(rows)

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

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

    # Headers: original A:S plus the preferred supplemental T:AF block.
    for c in range(1, 20):
        ws.cell(1, c).value = rows[0][c - 1] if c - 1 < len(rows[0]) else None
        ws.cell(2, c).value = rows[1][c - 1] if c - 1 < len(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(rows) - 2),
        "winner_named": 0,
        "original_winner_phone": 0,
        "new_written": 0,
        "reference_reuse": 0,
        "internal_reuse": 0,
        "final_available": 0,
        "gaps": 0,
    }
    gap_rows = [["Excel行号", "项目名称", "发布省份", "中标单位", "缺口说明"]]

    for r_idx, source_row in enumerate(rows[2:], start=3):
        values = list(source_row[:19]) + [None] * max(0, 19 - len(source_row))
        winner = clean(values[12])
        winner_phone = clean(values[14])
        buyer_phone = clean(values[11])
        supplement = [""] * len(SUPPLEMENT_HEADERS)
        gap_note = ""
        newly_written = False

        if winner:
            stats["winner_named"] += 1

        if winner_phone:
            stats["original_winner_phone"] += 1
            supplement[0] = winner_phone
            supplement[2] = "原表已有"
            supplement[3] = "high"
            stats["final_available"] += 1
        elif not winner:
            gap_note = "原表未给出中标单位，无法补中标单位电话。"
        elif winner in ref_map:
            info = ref_map[winner]
            phone = info["phone"]
            if buyer_phone and normalize_phone(phone) == normalize_phone(buyer_phone):
                gap_note = "候选号码与同项目招标单位电话一致，疑似采购方号码，未硬填。"
            else:
                values[14] = phone
                newly_written = True
                stats["new_written"] += 1
                stats["reference_reuse"] += 1
                stats["final_available"] += 1
                supplement = [
                    phone,
                    phone,
                    info["source_type"] or "reference_workbook",
                    info["confidence"] or "high",
                    info["source_url"],
                    info["tyc_phone"] or phone,
                    info["tyc_status"],
                    info["legal_person"],
                    info["email"],
                    info["website"],
                    info["address"],
                    info["evidence"],
                    "来自用户提供的5月已补电话样表，同名中标单位复用。",
                ]
        else:
            candidates = sorted(internal_map.get(winner, []))
            if candidates and not (buyer_phone and normalize_phone(candidates[0]) == normalize_phone(buyer_phone)):
                phone = candidates[0]
                values[14] = phone
                newly_written = True
                stats["new_written"] += 1
                stats["internal_reuse"] += 1
                stats["final_available"] += 1
                supplement = [
                    phone,
                    phone,
                    "current_workbook_duplicate",
                    "high",
                    "",
                    "",
                    "同表同名中标单位已有电话",
                    "",
                    "",
                    "",
                    "",
                    "同一中标单位在当前表其他行已有完整电话。",
                    "表内复用，建议抽查。",
                ]
            else:
                gap_note = "公开来源未找到可自动复用的完整电话，需人工核实。"

        if gap_note:
            stats["gaps"] += 1
            supplement[3] = supplement[3] or "none"
            supplement[-1] = gap_note
            gap_rows.append([r_idx, values[1], values[4], winner or None, gap_note])

        for c_idx, value in enumerate(values + supplement, start=1):
            ws.cell(r_idx, c_idx).value = value
        if newly_written:
            ws.cell(r_idx, 15).fill = yellow
            ws.cell(r_idx, 15).comment = Comment("本次新增写回中标单位联系人电话列。", "Codex")

    # Match preferred workbook look.
    for c in range(1, 33):
        letter = get_column_letter(c)
        ref_width = ref_ws.column_dimensions[letter].width
        ws.column_dimensions[letter].width = ref_width or 14
    ws.row_dimensions[1].height = 24
    ws.row_dimensions[2].height = 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 c in range(1, 33):
        ws.cell(1, c).fill = header_fill
        ws.cell(2, c).fill = header_fill
        ws.cell(1, c).font = Font(bold=True)
        ws.cell(2, c).font = Font(bold=True)
        ws.cell(1, c).alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        ws.cell(2, c).alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    for c in range(20, 33):
        ws.cell(1, c).fill = yellow
        ws.cell(2, c).fill = yellow
    for r in range(3, ws.max_row + 1):
        for c in range(20, 33):
            ws.cell(r, c).fill = yellow
        if ws.cell(r, 32).value and "需人工" in str(ws.cell(r, 32).value) or "无法补" in str(ws.cell(r, 32).value) or "未硬填" in str(ws.cell(r, 32).value):
            ws.cell(r, 32).fill = gap_fill

    sop = wb.create_sheet("补联SOP")
    for row in make_summary_rows(stats, input_path, output_path, reference_path):
        sop.append(row)
    for row in sop.iter_rows():
        for cell in row:
            cell.border = border
            cell.alignment = Alignment(vertical="top", wrap_text=True)
    sop.column_dimensions["A"].width = 28
    sop.column_dimensions["B"].width = 110
    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 row in gap_rows:
        gap.append(row)
    for row in gap.iter_rows():
        for cell in row:
            cell.border = border
            cell.alignment = Alignment(vertical="top", wrap_text=True)
    for c in range(1, 6):
        gap.cell(1, c).fill = gap_fill
        gap.cell(1, c).font = Font(bold=True)
    for idx, width in enumerate([10, 48, 12, 32, 58], start=1):
        gap.column_dimensions[get_column_letter(idx)].width = width
    gap.freeze_panes = "A2"

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


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--input", required=True, type=Path)
    parser.add_argument("--reference", required=True, type=Path)
    parser.add_argument("--output", required=True, type=Path)
    args = parser.parse_args()

    _sheet_name, rows = read_xls(args.input)
    stats = write_workbook(rows, args.output, args.reference, args.input)
    print(stats)
    return 0


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