#!/usr/bin/env python3
"""Enrich Yifangbao award export with public contact phone checks.

The input from Yifangbao is an old binary .xls workbook. This script reads it
with xlrd, writes a reviewable .xlsx copy, and marks newly filled phone cells:

- yellow: confidently filled from an internal duplicate or public search result
- red: unresolved or suspicious, needs human verification
"""

from __future__ import annotations

import argparse
import html
import json
import re
import time
from collections import Counter, defaultdict
from dataclasses import dataclass
from pathlib import Path
from typing import Iterable
from urllib.parse import urlencode
from urllib.request import Request, urlopen

import xlrd
from openpyxl import Workbook
from openpyxl.comments import Comment
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo


PHONE_RE = re.compile(r"(?:0\d{2,3}-?\d{7,8}|1[3-9]\d{9}|400-?\d{3}-?\d{4})")
MASKED_PHONE_RE = re.compile(r"(?:0\d{2,3}-?\d{3,4}\*{2,}|1[3-9]\d{4,}\*{2,}|400-?\d{2,}\*{2,})")
PHONE_CONTEXT_RE = re.compile(r"(电话|联系电话|联系方式|手机|热线|客服|联系)")
BAD_PHONE_PREFIXES = (
    "0123456789",
    "04000079158",
    "4006080000",  # Tianyancha footer customer service, not target entity.
)
SCRIPT_NOISE_PREFIXES = ("1784185", "1784029", "178419", "178418")


@dataclass
class PhoneFinding:
    phone: str
    confidence: str
    source: str
    note: str


def clean_value(value):
    if isinstance(value, float) and value.is_integer():
        return int(value)
    return value


def clean_text(value) -> str:
    return str(value or "").strip()


def normalize_phone(phone: str) -> str:
    return re.sub(r"\D", "", phone or "")


def is_bad_phone(phone: str) -> bool:
    normalized = normalize_phone(phone)
    if not normalized:
        return True
    if any(normalized.startswith(prefix) for prefix in BAD_PHONE_PREFIXES):
        return True
    if any(normalized.startswith(prefix) for prefix in SCRIPT_NOISE_PREFIXES):
        return True
    return False


def unique_preserve(values: Iterable[str]) -> list[str]:
    seen = set()
    result = []
    for value in values:
        if value and value not in seen:
            seen.add(value)
            result.append(value)
    return result


def fetch_360_search(query: str, pause: float = 0.05) -> str:
    url = "https://www.so.com/s?" + urlencode({"q": query})
    req = Request(
        url,
        headers={
            "User-Agent": (
                "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) "
                "AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126 Safari/537.36"
            ),
            "Accept-Language": "zh-CN,zh;q=0.9",
        },
    )
    with urlopen(req, timeout=15) as response:
        text = response.read().decode("utf-8", "ignore")
    if pause:
        time.sleep(pause)
    return text


def parse_result_blocks(search_html: str) -> list[tuple[str, str]]:
    blocks = []
    for match in re.finditer(r'<li[^>]+class="res-list.*?</li>', search_html, re.S):
        block = match.group(0)
        urls = re.findall(r'https?://[^"\s<>]+', block)
        direct_urls = [
            html.unescape(url)
            for url in urls
            if "www.so.com/link" not in url and "so.com" not in url
        ]
        text = html.unescape(re.sub(r"<.*?>", " ", block))
        text = re.sub(r"\s+", " ", text).strip()
        blocks.append((text, direct_urls[0] if direct_urls else "https://www.so.com/"))
    return blocks


def phones_from_block(company_name: str, block_text: str, url: str) -> list[PhoneFinding]:
    if company_name not in block_text:
        return []

    findings: list[PhoneFinding] = []
    phones = unique_preserve(PHONE_RE.findall(block_text))
    for phone in phones:
        if is_bad_phone(phone):
            continue
        idx = block_text.find(phone)
        window = block_text[max(0, idx - 90) : idx + len(phone) + 90]
        if not PHONE_CONTEXT_RE.search(window):
            continue
        findings.append(
            PhoneFinding(
                phone=phone,
                confidence="public-search",
                source=url,
                note=f"360搜索结果片段：{window[:180]}",
            )
        )
    return findings


def search_public_phone(company_name: str, max_queries: int = 1) -> PhoneFinding | None:
    queries = [
        f'"{company_name}" 电话',
        f'"{company_name}" 联系方式',
        f'site:aiqicha.baidu.com "{company_name}"',
        f'site:11467.com "{company_name}"',
    ]
    candidates: list[PhoneFinding] = []
    saw_masked = False
    for query in queries[:max_queries]:
        try:
            page = fetch_360_search(query)
        except Exception:
            continue
        for block_text, url in parse_result_blocks(page):
            if company_name not in block_text:
                continue
            if MASKED_PHONE_RE.search(block_text):
                saw_masked = True
            candidates.extend(phones_from_block(company_name, block_text, url))
        if candidates:
            break
    if not candidates:
        if saw_masked:
            return PhoneFinding("", "masked-only", "360搜索", "搜索结果仅显示脱敏电话，未自动回填。")
        return None

    by_phone = Counter(f.phone for f in candidates)
    best = sorted(candidates, key=lambda f: (-by_phone[f.phone], f.source))[0]
    if by_phone[best.phone] > 1:
        best.note += f"；同号命中 {by_phone[best.phone]} 次。"
    return best


def build_internal_maps(rows: list[list], specs: list[tuple[str, int, int]]) -> dict[tuple[str, str], set[str]]:
    maps: dict[tuple[str, str], set[str]] = defaultdict(set)
    for role, name_col, phone_col in specs:
        for row in rows[2:]:
            name = clean_text(row[name_col])
            phone = clean_text(row[phone_col])
            if name and PHONE_RE.search(phone) and not is_bad_phone(phone):
                maps[(role, name)].add(phone)
    return maps


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 = [
        [clean_value(sheet.cell_value(r, c)) for c in range(sheet.ncols)]
        for r in range(sheet.nrows)
    ]
    return sheet.name, rows


def enrich_rows(rows: list[list], use_external: bool, max_search_queries: int, verbose: bool) -> tuple[list[list], dict, list[dict]]:
    specs = [
        ("招标单位", 9, 11),
        ("中标单位", 12, 14),
    ]
    internal_maps = build_internal_maps(rows, specs)
    search_cache: dict[str, PhoneFinding | None] = {}
    audit: list[dict] = []
    summary = Counter()

    new_rows = [list(row) for row in rows]
    new_rows[0] = new_rows[0] + ["", "", "", ""]
    new_rows[1] = new_rows[1] + [
        "招标电话补全状态",
        "招标电话来源",
        "中标电话补全状态",
        "中标电话来源",
    ]

    for idx in range(2, len(new_rows)):
        row = new_rows[idx] + ["", "", "", ""]
        row_status = {}
        buyer_phone_now = clean_text(row[11])

        for role, name_col, phone_col in specs:
            status_col, source_col = (19, 20) if role == "招标单位" else (21, 22)
            name = clean_text(row[name_col])
            existing_phone = clean_text(row[phone_col])
            if not name:
                row[status_col] = "无单位名称"
                continue

            if existing_phone:
                row[status_col] = "原表已有"
                summary[f"{role}:原表已有"] += 1
                continue

            finding: PhoneFinding | None = None
            internal_candidates = sorted(internal_maps.get((role, name), []))
            if internal_candidates:
                candidate = internal_candidates[0]
                if role == "中标单位" and buyer_phone_now and normalize_phone(candidate) == normalize_phone(buyer_phone_now):
                    finding = PhoneFinding(
                        "",
                        "suspicious",
                        "表内复用被拦截",
                        "候选号码与同项目采购方电话一致，疑似采购方电话误填，未自动回填。",
                    )
                else:
                    finding = PhoneFinding(
                        candidate,
                        "internal-duplicate",
                        "原表同单位其他行",
                        "同一单位在原表其他行已有电话，按同角色复用。",
                    )
            elif use_external:
                if name not in search_cache:
                    if verbose and len(search_cache) % 10 == 0:
                        print(f"[search] checked {len(search_cache)} unique names...", flush=True)
                    search_cache[name] = search_public_phone(name, max_queries=max_search_queries)
                finding = search_cache[name]

            if finding and finding.phone:
                row[phone_col] = finding.phone
                if finding.confidence == "internal-duplicate":
                    row[status_col] = "已补全-表内复用"
                else:
                    row[status_col] = "已补全-公开检索"
                row[source_col] = finding.source
                summary[f"{role}:{row[status_col]}"] += 1
                audit.append(
                    {
                        "row": idx + 1,
                        "role": role,
                        "name": name,
                        "phone": finding.phone,
                        "status": row[status_col],
                        "source": finding.source,
                        "note": finding.note,
                    }
                )
                row_status[f"{role}:{phone_col}"] = "yellow"
            else:
                if finding and finding.confidence == "masked-only":
                    row[status_col] = "疑问-仅见脱敏电话"
                    row[source_col] = finding.source
                    note = finding.note
                elif finding and finding.confidence == "suspicious":
                    row[status_col] = "疑问-疑似误填"
                    row[source_col] = finding.source
                    note = finding.note
                else:
                    row[status_col] = "待人工核实"
                    row[source_col] = "未找到公司名同页明确电话"
                    note = "公开检索未获得可自动回填的完整电话。"
                summary[f"{role}:{row[status_col]}"] += 1
                audit.append(
                    {
                        "row": idx + 1,
                        "role": role,
                        "name": name,
                        "phone": "",
                        "status": row[status_col],
                        "source": row[source_col],
                        "note": note,
                    }
                )
                row_status[f"{role}:{phone_col}"] = "red"

        row.append(json.dumps(row_status, ensure_ascii=False))
        new_rows[idx] = row

    return new_rows, dict(summary), audit


def write_workbook(sheet_name: str, rows: list[list], summary: dict, audit: list[dict], output: Path) -> None:
    wb = Workbook()
    ws = wb.active
    ws.title = sheet_name[:31]

    marker_col = max(len(row) for row in rows)
    phone_fill = PatternFill("solid", fgColor="FFF2CC")
    issue_fill = PatternFill("solid", fgColor="F4CCCC")
    header_fill = PatternFill("solid", fgColor="D9EAF7")
    group_fill = PatternFill("solid", fgColor="EAF2F8")
    thin_gray = Side(style="thin", color="D9E2EC")
    border = Border(bottom=thin_gray)

    for r_idx, row in enumerate(rows, start=1):
        visible_row = row[: marker_col - 1] if r_idx >= 3 else row[: marker_col - 1]
        ws.append(visible_row)
        if r_idx >= 3:
            marker = json.loads(row[marker_col - 1] or "{}")
            for key, fill_name in marker.items():
                _role, col_idx_text = key.rsplit(":", 1)
                col_idx = int(col_idx_text) + 1
                cell = ws.cell(r_idx, col_idx)
                cell.fill = phone_fill if fill_name == "yellow" else issue_fill
                status = ws.cell(r_idx, 20 if col_idx == 12 else 22).value
                source = ws.cell(r_idx, 21 if col_idx == 12 else 23).value
                if fill_name == "red":
                    cell.comment = Comment(f"{status or ''}\n{source or ''}", "Codex")

    max_col = ws.max_column
    max_row = ws.max_row
    for cell in ws[1]:
        cell.fill = group_fill
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal="center")
    for cell in ws[2]:
        cell.fill = header_fill
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    for row in ws.iter_rows(min_row=1, max_row=max_row, max_col=max_col):
        for cell in row:
            cell.border = border
            cell.alignment = Alignment(vertical="top", wrap_text=True)
    ws.freeze_panes = "A3"
    ws.auto_filter.ref = f"A2:{get_column_letter(max_col)}{max_row}"

    width_overrides = {
        2: 42,
        10: 26,
        11: 16,
        12: 18,
        13: 26,
        14: 16,
        15: 18,
        19: 48,
        20: 18,
        21: 34,
        22: 18,
        23: 34,
    }
    for col_idx in range(1, max_col + 1):
        ws.column_dimensions[get_column_letter(col_idx)].width = width_overrides.get(col_idx, 14)
    ws.row_dimensions[1].height = 22
    ws.row_dimensions[2].height = 34

    try:
        table = Table(displayName="YifangbaoPhoneEnrichment", ref=f"A2:{get_column_letter(max_col)}{max_row}")
        table.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showRowStripes=True)
        ws.add_table(table)
    except Exception:
        pass

    summary_ws = wb.create_sheet("补全说明")
    summary_ws.append(["项目", "数量"])
    for key, value in sorted(summary.items()):
        summary_ws.append([key, value])
    summary_ws.append([])
    summary_ws.append(["颜色说明", "含义"])
    summary_ws.append(["黄色", "本次自动补全或确认写入的电话"])
    summary_ws.append(["红色", "未找到可靠电话、仅见脱敏电话或疑似误填，需人工核实"])
    summary_ws.append(["来源规则", "公开检索仅接受公司名与完整电话在同一搜索结果片段中出现，且电话附近出现联系电话/手机等上下文"])
    for row in summary_ws.iter_rows():
        for cell in row:
            cell.border = border
            cell.alignment = Alignment(vertical="top", wrap_text=True)
    summary_ws.column_dimensions["A"].width = 34
    summary_ws.column_dimensions["B"].width = 80
    summary_ws["A1"].fill = header_fill
    summary_ws["B1"].fill = header_fill
    summary_ws["A1"].font = Font(bold=True)
    summary_ws["B1"].font = Font(bold=True)

    audit_ws = wb.create_sheet("检索审核")
    audit_headers = ["原表行号", "角色", "单位名称", "电话", "状态", "来源", "说明"]
    audit_ws.append(audit_headers)
    for item in audit:
        audit_ws.append([item["row"], item["role"], item["name"], item["phone"], item["status"], item["source"], item["note"]])
    for cell in audit_ws[1]:
        cell.fill = header_fill
        cell.font = Font(bold=True)
    for row in audit_ws.iter_rows():
        for cell in row:
            cell.border = border
            cell.alignment = Alignment(vertical="top", wrap_text=True)
    for idx, width in enumerate([10, 12, 34, 18, 18, 42, 80], start=1):
        audit_ws.column_dimensions[get_column_letter(idx)].width = width
    audit_ws.freeze_panes = "A2"

    output.parent.mkdir(parents=True, exist_ok=True)
    wb.save(output)


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--input", required=True, type=Path)
    parser.add_argument("--output", required=True, type=Path)
    parser.add_argument("--summary-json", type=Path)
    parser.add_argument("--no-external", action="store_true")
    parser.add_argument("--max-search-queries", type=int, default=1)
    parser.add_argument("--verbose", action="store_true")
    args = parser.parse_args()

    sheet_name, rows = read_xls(args.input)
    enriched_rows, summary, audit = enrich_rows(
        rows,
        use_external=not args.no_external,
        max_search_queries=max(1, args.max_search_queries),
        verbose=args.verbose,
    )
    write_workbook(sheet_name, enriched_rows, summary, audit, args.output)

    payload = {"summary": summary, "audit_count": len(audit), "output": str(args.output)}
    if args.summary_json:
        args.summary_json.parent.mkdir(parents=True, exist_ok=True)
        args.summary_json.write_text(json.dumps(payload, ensure_ascii=False, indent=2), encoding="utf-8")
    print(json.dumps(payload, ensure_ascii=False, indent=2))
    return 0


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