"""从 GitHub (lilinji/English) 下载沪版牛津与商务英语 xlsx，生成词书 JSON。

用法:
  python -m scripts.fetch_hujiao_books
  python -m scripts.fetch_hujiao_books --import   # 生成后直接写入数据库
"""

from __future__ import annotations

import argparse
import json
import re
import sys
import urllib.parse
import urllib.request
from pathlib import Path

BACKEND_DIR = Path(__file__).resolve().parents[1]
OUT_DIR = BACKEND_DIR / "data" / "books"

GITHUB_RAW = "https://raw.githubusercontent.com/lilinji/English/main"
OXFORD_DIR = (
    "1.全国各大教材版本中小学同步/牛津版"
)
BUSINESS_DIR = "8.商务英语"

PRIMARY_GRADES = [
    (1, 11, "一年级", ["牛津上海版一年级上册.xlsx", "牛津上海版一年级下册.xlsx"]),
    (2, 12, "二年级", ["牛津上海版二年级上册.xlsx", "牛津上海版二年级下册.xlsx"]),
    (3, 13, "三年级", ["牛津上海版三年级上册.xlsx", "牛津上海版三年级下册.xlsx"]),
    (4, 14, "四年级", ["牛津上海版四年级上册.xlsx", "牛津上海版四年级下册.xlsx"]),
    (5, 15, "五年级", ["牛津上海版五年级上册.xlsx", "牛津上海版五年级下册.xlsx"]),
]

JUNIOR_GRADES = [
    (6, 21, "六年级", ["牛津上海版六年级上册.xlsx", "牛津上海版六年级下册.xlsx"]),
    (7, 22, "七年级", ["牛津上海版七年级上册.xlsx", "牛津上海版七年级下册.xlsx"]),
    (8, 23, "八年级", ["牛津上海版八年级上册.xlsx", "牛津上海版八年级下册.xlsx"]),
    (9, 24, "九年级", ["牛津上海版九年级上册.xlsx", "牛津上海版九年级下册.xlsx"]),
]

SENIOR_GRADES = [
    (1, 31, "高一", ["牛津上海版高一上.xlsx", "牛津上海版高一下.xlsx"]),
    (2, 32, "高二", ["牛津上海版高二上.xlsx", "牛津上海版高二下.xlsx"]),
    (3, 33, "高三", ["牛津上海版高三上.xlsx", "牛津上海版高三下.xlsx"]),
]


BOOK_SPECS = [
    *[
        {
            "slug": f"hujiao-primary-{grade}",
            "title": f"沪版小学英语 · {label}",
            "description": f"牛津上海版{label}词汇（上下册）",
            "level": "primary",
            "sort_order": order,
            "daily_target": 10 + grade,
            "master_required_count": 3,
            "weak_wrong_threshold": 2,
            "files": files,
        }
        for grade, order, label, files in PRIMARY_GRADES
    ],
    *[
        {
            "slug": f"hujiao-junior-{grade}",
            "title": f"沪版初中英语 · {label}",
            "description": f"牛津上海版{label}词汇（上下册）",
            "level": "junior",
            "sort_order": order,
            "daily_target": 11 + grade - 6,
            "master_required_count": 4,
            "weak_wrong_threshold": 2,
            "files": files,
        }
        for grade, order, label, files in JUNIOR_GRADES
    ],
    *[
        {
            "slug": f"hujiao-senior-{grade}",
            "title": f"沪版高中英语 · {label}",
            "description": f"牛津上海版{label}词汇（上下册）",
            "level": "senior",
            "sort_order": order,
            "daily_target": 15 + grade,
            "master_required_count": 4,
            "weak_wrong_threshold": 2,
            "files": files,
        }
        for grade, order, label, files in SENIOR_GRADES
    ],
    {
        "slug": "business-daily",
        "title": "日常商务英语",
        "description": "BEC 初/中级核心商务词汇",
        "level": "business",
        "sort_order": 40,
        "daily_target": 15,
        "master_required_count": 4,
        "weak_wrong_threshold": 2,
        "files": [
            "BEC初级词汇精选.xlsx",
            "BEC中级词汇精选.xlsx",
        ],
        "base_dir": BUSINESS_DIR,
    },
]


def download_file(rel_path: str, cache_dir: Path) -> Path:
    cache_dir.mkdir(parents=True, exist_ok=True)
    local = cache_dir / Path(rel_path).name
    if local.exists() and local.stat().st_size > 0:
        return local
    url = f"{GITHUB_RAW}/{urllib.parse.quote(rel_path)}"
    print(f"  下载 {Path(rel_path).name} ...")
    urllib.request.urlretrieve(url, local)
    return local


def parse_xlsx(path: Path) -> list[dict]:
    import openpyxl

    wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
    ws = wb.active
    rows = list(ws.iter_rows(values_only=True))
    if not rows:
        return []
    header = [str(c).strip() if c else "" for c in rows[0]]
    col_word = next((i for i, h in enumerate(header) if h in ("单词", "word", "英文")), 0)
    col_zh = next((i for i, h in enumerate(header) if h in ("释义", "中文", "意思")), -1)
    col_phonetic = next((i for i, h in enumerate(header) if "英音" in h or h == "音标"), -1)

    out: list[dict] = []
    seen: set[str] = set()
    for row in rows[1:]:
        if not row or not row[col_word]:
            continue
        lemma = str(row[col_word]).strip().lower()
        if not lemma or lemma in seen:
            continue
        seen.add(lemma)
        zh_raw = str(row[col_zh]).strip() if col_zh >= 0 and row[col_zh] else lemma
        zh = _simplify_zh(zh_raw)
        phonetic = None
        if col_phonetic >= 0 and row[col_phonetic]:
            phonetic = str(row[col_phonetic]).strip()[:128] or None
        out.append({"lemma_en": lemma[:128], "zh": zh[:256], "phonetic": phonetic})
    wb.close()
    return out


def _simplify_zh(raw: str) -> str:
    text = raw.strip()
    first = text.split("\n")[0].strip()
    first = re.sub(r"^[a-z]+\.\s*", "", first, flags=re.I)
    for sep in ("；", ";", "，", ","):
        if sep in first:
            first = first.split(sep)[0].strip()
    return first or raw[:64]


def unit_title_from_filename(name: str) -> str:
    base = name.replace(".xlsx", "").replace("牛津上海版", "").replace("BEC", "BEC ")
    return base.strip()


def build_book(spec: dict, cache_dir: Path) -> dict:
    base_dir = spec.get("base_dir", OXFORD_DIR)
    units = []
    global_seen: set[str] = set()
    sort_index = 0

    for unit_no, fname in enumerate(spec["files"], start=1):
        rel = f"{base_dir}/{fname}"
        path = download_file(rel, cache_dir)
        words = parse_xlsx(path)
        unit_words = []
        for w in words:
            key = w["lemma_en"]
            if key in global_seen:
                continue
            global_seen.add(key)
            sort_index += 1
            unit_words.append({**w, "sort_index": sort_index})
        if unit_words:
            units.append(
                {
                    "unit": unit_no,
                    "title": unit_title_from_filename(fname),
                    "words": unit_words,
                }
            )

    return {
        "slug": spec["slug"],
        "title": spec["title"],
        "description": spec["description"],
        "level": spec["level"],
        "sort_order": spec["sort_order"],
        "daily_target": spec["daily_target"],
        "master_required_count": spec["master_required_count"],
        "weak_wrong_threshold": spec["weak_wrong_threshold"],
        "learn_mode": "sequential",
        "units": units,
    }


def main() -> None:
    parser = argparse.ArgumentParser(description="下载沪版词书并生成 JSON")
    parser.add_argument("--import-db", action="store_true", help="生成后导入数据库")
    args = parser.parse_args()

    try:
        import openpyxl  # noqa: F401
    except ImportError:
        print("请先安装: pip install openpyxl", file=sys.stderr)
        sys.exit(1)

    OUT_DIR.mkdir(parents=True, exist_ok=True)
    cache_dir = BACKEND_DIR / "data" / "cache" / "word_books"
    books = []
    for spec in BOOK_SPECS:
        print(f"处理 {spec['title']} ...")
        book = build_book(spec, cache_dir)
        word_count = sum(len(u["words"]) for u in book["units"])
        book["word_count"] = word_count
        book["unit_count"] = len(book["units"])
        out_path = OUT_DIR / f"{spec['slug']}.json"
        out_path.write_text(json.dumps(book, ensure_ascii=False, indent=2), encoding="utf-8")
        print(f"  -> {out_path.name} ({word_count} 词, {book['unit_count']} 单元)")
        books.append(book)

    manifest = OUT_DIR / "manifest.json"
    manifest.write_text(
        json.dumps([{"slug": b["slug"], "title": b["title"], "word_count": b["word_count"]} for b in books],
                   ensure_ascii=False, indent=2),
        encoding="utf-8",
    )
    print(f"已生成 {len(books)} 本词书 -> {OUT_DIR}")

    if args.import_db:
        from scripts.import_word_books import import_all

        import_all(OUT_DIR)


if __name__ == "__main__":
    main()