#!/usr/bin/env python3
"""Profile bidding/tender award CSV data.

This script intentionally uses only Python standard library modules. For .xls
input it tries to call LibreOffice/soffice to convert the first sheet to CSV.
"""

from __future__ import annotations

import argparse
import csv
import shutil
import statistics
import subprocess
import sys
import tempfile
from collections import Counter
from pathlib import Path


EXPECTED_COLUMNS = [
    "关键词",
    "项目名称",
    "信息发布时间",
    "项目编号",
    "发布省份",
    "发布市级",
    "发布区级",
    "中标阶段",
    "中标金额（元）",
    "招标单位",
    "中标单位",
    "合同工期",
    "官网查看地址",
]


def convert_xls_to_csv(path: Path) -> Path:
    soffice = shutil.which("soffice") or shutil.which("libreoffice")
    if not soffice:
        raise RuntimeError("Need soffice/libreoffice to convert .xls input.")

    tmpdir = Path(tempfile.mkdtemp(prefix="bid-analysis-"))
    subprocess.run(
        [soffice, "--headless", "--convert-to", "csv", "--outdir", str(tmpdir), str(path)],
        check=True,
        stdout=subprocess.PIPE,
        stderr=subprocess.PIPE,
        text=True,
    )
    csv_files = list(tmpdir.glob("*.csv"))
    if not csv_files:
        csv_files = list(tmpdir.glob("*.CSV"))
    if not csv_files:
        csv_files = list(tmpdir.rglob("*.csv")) + list(tmpdir.rglob("*.CSV"))
    if not csv_files:
        raise RuntimeError("soffice conversion completed but no CSV was produced.")
    return csv_files[0]


def input_to_csv(path: Path) -> Path:
    suffix = path.suffix.lower()
    if suffix == ".csv":
        return path
    if suffix in {".xls", ".xlsx"}:
        return convert_xls_to_csv(path)
    raise RuntimeError(f"Unsupported file type: {suffix}")


def parse_amount(value: str | None) -> float | None:
    if value is None:
        return None
    text = str(value).strip().replace(",", "")
    if not text or text in {"--", "暂未公布", "nan"}:
        return None
    try:
        return float(text)
    except ValueError:
        return None


def read_rows(csv_path: Path) -> list[dict[str, str]]:
    with csv_path.open("r", encoding="utf-8-sig", newline="") as f:
        reader = csv.DictReader(f)
        rows = []
        for row in reader:
            if row.get("项目名称") == "项目名称":
                continue
            rows.append(row)
    return rows


def top_values(rows: list[dict[str, str]], column: str, limit: int) -> list[tuple[str, int]]:
    values = [row.get(column) or "未填" for row in rows]
    return Counter(values).most_common(limit)


def markdown_table(headers: list[str], data: list[list[object]]) -> str:
    lines = [
        "| " + " | ".join(headers) + " |",
        "| " + " | ".join(["---"] * len(headers)) + " |",
    ]
    for row in data:
        lines.append("| " + " | ".join(str(cell) for cell in row) + " |")
    return "\n".join(lines)


def main() -> int:
    parser = argparse.ArgumentParser(description="Profile bidding award CSV/XLS data.")
    parser.add_argument("input", type=Path, help="Input .csv/.xls/.xlsx file")
    parser.add_argument("--top", type=int, default=10, help="Top N values to show")
    args = parser.parse_args()

    csv_path = input_to_csv(args.input)
    rows = read_rows(csv_path)
    if not rows:
        print("No rows found.", file=sys.stderr)
        return 1

    columns = list(rows[0].keys())
    missing_expected = [col for col in EXPECTED_COLUMNS if col not in columns]
    amounts = [parse_amount(row.get("中标金额（元）")) for row in rows]
    valid_amounts = [amount for amount in amounts if amount is not None]

    print("# 数据概览")
    print()
    print(f"- 文件: `{args.input}`")
    print(f"- 记录数: {len(rows)}")
    print(f"- 字段数: {len(columns)}")
    if missing_expected:
        print(f"- 缺少预期字段: {', '.join(missing_expected)}")
    else:
        print("- 预期字段: 完整")

    print()
    print("## 金额")
    if valid_amounts:
        print(f"- 可解析金额记录数: {len(valid_amounts)}")
        print(f"- 金额合计: {sum(valid_amounts):,.2f}")
        print(f"- 金额中位数: {statistics.median(valid_amounts):,.2f}")
        print(f"- 最大金额: {max(valid_amounts):,.2f}")
    else:
        print("- 没有可解析金额。")

    for column in ["关键词", "发布省份", "发布市级", "中标阶段", "招标单位", "中标单位"]:
        if column not in columns:
            continue
        print()
        print(f"## Top {args.top}: {column}")
        print(markdown_table([column, "数量"], [[k, v] for k, v in top_values(rows, column, args.top)]))

    if "项目名称" in columns and valid_amounts:
        indexed = []
        for row, amount in zip(rows, amounts):
            if amount is not None:
                indexed.append((amount, row))
        indexed.sort(key=lambda item: item[0], reverse=True)
        print()
        print(f"## Top {args.top}: 金额项目")
        top_rows = []
        for amount, row in indexed[: args.top]:
            top_rows.append(
                [
                    row.get("项目名称", ""),
                    row.get("信息发布时间", ""),
                    row.get("发布省份", ""),
                    row.get("发布市级", ""),
                    f"{amount:,.2f}",
                    row.get("招标单位", ""),
                    row.get("中标单位", ""),
                ]
            )
        print(markdown_table(["项目名称", "发布时间", "省", "市", "金额", "招标单位", "中标单位"], top_rows))

    return 0


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