#!/usr/bin/env python3
"""Apply province based sales-owner assignment to a lead import workbook."""

from __future__ import annotations

import argparse
import re
from collections import Counter
from pathlib import Path

from openpyxl import load_workbook


PROVINCE_ALIASES = {
    "内蒙古": "内蒙",
    "内蒙古自治区": "内蒙",
    "广西壮族自治区": "广西",
    "宁夏回族自治区": "宁夏",
    "新疆维吾尔自治区": "新疆",
    "西藏自治区": "西藏",
    "北京市": "北京",
    "上海市": "上海",
    "天津市": "天津",
    "重庆市": "重庆",
}


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


def province_key(value: str) -> str:
    value = clean(value)
    if value in PROVINCE_ALIASES:
        return PROVINCE_ALIASES[value]
    return re.sub(r"(省|市|自治区|壮族自治区|回族自治区|维吾尔自治区)$", "", value)


def load_region_mapping(path: Path) -> dict[str, list[str]]:
    wb = load_workbook(path, data_only=True)
    ws = wb[wb.sheetnames[0]]
    mapping: dict[str, list[str]] = {}
    for row in range(2, ws.max_row + 1):
        province = province_key(ws.cell(row, 1).value)
        owners_raw = clean(ws.cell(row, 2).value)
        if not province or not owners_raw:
            continue
        owners = [item.strip() for item in re.split(r"[/、,，]", owners_raw) if item.strip()]
        mapping[province] = owners
    return mapping


def apply_mapping(workbook_path: Path, region_path: Path, output_path: Path) -> dict:
    mapping = load_region_mapping(region_path)
    wb = load_workbook(workbook_path)
    ws = wb["天眼查补全"] if "天眼查补全" in wb.sheetnames else wb[wb.sheetnames[0]]

    round_robin_index: Counter[str] = Counter()
    assigned: Counter[str] = Counter()
    missing: Counter[str] = Counter()

    for row in range(2, ws.max_row + 1):
        province = province_key(ws.cell(row, 10).value)
        owners = mapping.get(province)
        if not owners:
            missing[province or "(空)"] += 1
            continue
        owner = owners[round_robin_index[province] % len(owners)]
        round_robin_index[province] += 1
        ws.cell(row, 7).value = owner
        ws.cell(row, 6).value = f"=G{row}" if row == 2 else owner
        assigned[owner] += 1

    output_path.parent.mkdir(parents=True, exist_ok=True)
    wb.save(output_path)
    return {
        "rows": ws.max_row - 1,
        "assigned": dict(sorted(assigned.items())),
        "missing": dict(sorted(missing.items())),
        "output": str(output_path),
    }


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--workbook", required=True, type=Path)
    parser.add_argument("--regions", required=True, type=Path)
    parser.add_argument("--output", required=True, type=Path)
    args = parser.parse_args()
    print(apply_mapping(args.workbook, args.regions, args.output))
    return 0


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