From 1315d528626e43616b7983ec6c29f365018c385d Mon Sep 17 00:00:00 2001
From: studyhill <studyhill@local>
Date: Sat, 29 Aug 2026 19:40:28 +0800
Subject: [PATCH] =?utf8?q?=E6=96=B0=E5=A2=9E=E9=87=8D=E7=82=B9=E8=AF=8D?=
 =?utf8?q?=E4=BF=AE=E8=AE=A2=E8=84=9A=E6=9C=AC=EF=BC=88=E6=8C=89=20Excel?=
 =?utf8?q?=20=E5=9B=9E=E5=A1=AB=20is=5Fkey=EF=BC=89?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

从修订版 Excel 读回 wid → 是否重点词，更新 lessons.payload 的 is_key。
与导入脚本不同：只改 payload，不重建课程，因此进度、完成记录、复习队列、
每日任务全部保留。默认预演，需显式 --apply 才写库。

本次采用人工修订版词表：每组 10 个、74 组共 740 个重点词，
较原自动选取翻转 916 处（新增 458 / 取消 458）。
---
 backend/imports/apply_key_revision.py | 109 ++++++++++++++++++++++++++
 1 file changed, 109 insertions(+)
 create mode 100644 backend/imports/apply_key_revision.py

diff --git a/backend/imports/apply_key_revision.py b/backend/imports/apply_key_revision.py
new file mode 100644
index 0000000..b755784
--- /dev/null
+++ b/backend/imports/apply_key_revision.py
@@ -0,0 +1,109 @@
+"""按修订版 Excel 更新 lessons.payload 里的重点词（is_key）标记。
+
+与 ielts_vocab.py 的区别：本脚本**只改 payload，不重建课程**，
+因此 course_progress / lesson_completions / review_queue / daily_tasks 全部原样保留。
+
+用法：
+    python3 imports/apply_key_revision.py --xlsx <修订版.xlsx>            # 预演
+    python3 imports/apply_key_revision.py --xlsx <修订版.xlsx> --apply    # 落库
+"""
+
+import argparse
+import json
+import os
+import sys
+
+sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
+
+import db  # noqa: E402
+
+DEFAULT_XLSX = "/home/Codebuddy-web/data/pastes/雅思词汇真经-分组词汇表_重点词修订版 (1).xlsx"
+SHEET = "分组词汇表"
+C_WID, C_IS_KEY = 5, 10  # 列索引：单词ID / 重点词
+
+
+def load_revision(xlsx_path):
+    """返回 {wid: is_key}。以单词ID 为键，避免跨章重名混淆。"""
+    import openpyxl
+
+    wb = openpyxl.load_workbook(xlsx_path, data_only=True)
+    if SHEET not in wb.sheetnames:
+        raise SystemExit(f"找不到 sheet「{SHEET}」，现有：{wb.sheetnames}")
+    ws = wb[SHEET]
+
+    mapping = {}
+    for r in ws.iter_rows(min_row=2, values_only=True):
+        if r[C_WID] is None:
+            continue
+        mapping[str(r[C_WID]).strip()] = str(r[C_IS_KEY]).strip() == "是"
+    return mapping
+
+
+def apply(xlsx_path, do_apply=False):
+    revision = load_revision(xlsx_path)
+
+    conn = db.get_conn()
+    lessons = conn.execute("SELECT id, lesson_no, payload FROM lessons ORDER BY lesson_no").fetchall()
+
+    matched = 0      # Excel 里能对应上的词条数
+    changed = 0      # 实际翻转标记的词条数
+    lessons_touched = 0
+    unknown = set()
+
+    for lesson in lessons:
+        payload = json.loads(lesson["payload"])
+        dirty = False
+        for w in payload.get("words", []):
+            wid = str(w.get("wid") or "").strip()
+            if wid not in revision:
+                unknown.add(wid)
+                continue
+            matched += 1
+            if bool(w.get("is_key")) != revision[wid]:
+                w["is_key"] = revision[wid]
+                changed += 1
+                dirty = True
+        if dirty:
+            lessons_touched += 1
+            if do_apply:
+                conn.execute(
+                    "UPDATE lessons SET payload=?, updated_at=? WHERE id=?",
+                    (json.dumps(payload, ensure_ascii=False), db.now(), lesson["id"]),
+                )
+
+    if do_apply:
+        conn.commit()
+    conn.close()
+
+    return {
+        "revised_total": len(revision),
+        "revised_keys": sum(1 for v in revision.values() if v),
+        "matched": matched,
+        "changed": changed,
+        "lessons_touched": lessons_touched,
+        "unknown": unknown,
+    }
+
+
+def main():
+    ap = argparse.ArgumentParser()
+    ap.add_argument("--xlsx", default=DEFAULT_XLSX)
+    ap.add_argument("--apply", action="store_true", help="真正写库，缺省为预演")
+    args = ap.parse_args()
+
+    if not os.path.exists(args.xlsx):
+        raise SystemExit(f"找不到文件：{args.xlsx}")
+
+    db.init_db()
+    info = apply(args.xlsx, do_apply=args.apply)
+
+    print(f"修订版：{info['revised_total']} 词，其中重点词 {info['revised_keys']}")
+    print(f"匹配上：{info['matched']} 词")
+    print(f"需翻转：{info['changed']} 词，涉及 {info['lessons_touched']} 个课时")
+    if info["unknown"]:
+        print(f"!! 库中有 {len(info['unknown'])} 个词在修订版里找不到：{list(info['unknown'])[:5]}")
+    print("预演完成（未写库）" if not args.apply else "已写入数据库")
+
+
+if __name__ == "__main__":
+    main()
-- 
2.43.0