miaoguo_system_server/.vscode/秒过知识库更新/update_knowledge_base_2026_fall.mjs

#!/usr/bin/env node

import assert from "node:assert/strict";
import fs from "node:fs";
import path from "node:path";
import { fileURLToPath } from "node:url";
import mysql from "mysql2/promise";
import config from "../../src/config/dev.js";

const taskDir = path.dirname(fileURLToPath(import.meta.url));
const chineseSourcePath = path.join(taskDir, "source_chinese_2026_fall.json");
const englishSourcePath = path.join(taskDir, "source_english_2026_fall.json");
const chineseSources = JSON.parse(fs.readFileSync(chineseSourcePath, "utf8"));
const englishSources = JSON.parse(fs.readFileSync(englishSourcePath, "utf8"));

const flags = new Set(process.argv.slice(2));
const knownFlags = new Set(["--dry-run", "--apply", "--audit"]);
for (const flag of flags) {
  assert(knownFlags.has(flag), `不支持的参数：${flag}`);
}
assert(
  ["--dry-run", "--apply", "--audit"].filter((flag) => flags.has(flag)).length <= 1,
  "--dry-run、--apply、--audit 只能选择一个",
);
const mode = flags.has("--apply") ? "apply" : flags.has("--audit") ? "audit" : "dry-run";

const targetMiaoguoBookIds = [...chineseSources, ...englishSources].map(
  (source) => source.miaoguoBookId,
);
const targetHanziBookIds = chineseSources.map((source) => source.bookIdOld);
const targetEnglishBookIds = englishSources.map((source) => source.bookIdOld);

function nowForFile() {
  return new Date().toISOString().replaceAll(":", "-").replaceAll(".", "-");
}

function writeJson(filePath, data) {
  fs.writeFileSync(filePath, `${JSON.stringify(data, null, 2)}\n`, "utf8");
}

function countChineseWords(source) {
  if (source.unitType === 2) {
    return source.units.reduce(
      (total, unit) => total + unit.example.split(",").filter(Boolean).length,
      0,
    );
  }
  return source.units.reduce((total, unit) => total + Array.from(unit.example).length, 0);
}

function flattenEnglish(source) {
  return source.units.flatMap((unit) =>
    unit.entries.map((entry, index) => ({
      Word: entry.word,
      WordRemark: null,
      Level: null,
      BookID: source.bookIdOld,
      LessonID: unit.lessonId,
      LessonName: unit.lessonName,
      Soundmark: entry.soundmark ?? null,
      Translate: entry.translate,
      EnglishExplanation: null,
      ExampleSentence: null,
      Sort: index + 1,
    })),
  );
}

function validateSources() {
  assert.equal(chineseSources.length, 8, "本批次应有 8 个语文数据集");
  assert.equal(englishSources.length, 2, "本批次应有 2 个英语数据集");
  assert.equal(
    new Set(targetMiaoguoBookIds).size,
    targetMiaoguoBookIds.length,
    "MiaoguoBook ID 不能重复",
  );
  assert.equal(
    new Set([...targetHanziBookIds, ...targetEnglishBookIds]).size,
    targetHanziBookIds.length + targetEnglishBookIds.length,
    "BookIDOld 不能重复",
  );

  for (const source of chineseSources) {
    assert([1, 2, 5].includes(source.unitType), `${source.label} UnitType 不正确`);
    assert(source.units.length > 0, `${source.label} 没有单元`);
    for (const unit of source.units) {
      assert(unit.name?.length > 0, `${source.label} 存在空 Name`);
      assert(unit.example?.length > 0, `${source.label}/${unit.name} 存在空 Example`);
    }
    assert.equal(
      countChineseWords(source),
      source.wordNum,
      `${source.label} 由 Example 计算出的字/词数与 WordNum 不一致`,
    );
    if (source.unitType === 1) {
      assert.equal(
        source.officialWordNum + source.blueCharacterCount,
        source.wordNum,
        `${source.label} 黑字数 + 蓝色多音字数与 WordNum 不一致`,
      );
    }
  }

  for (const source of englishSources) {
    assert(source.units.length > 0, `${source.label} 没有单元`);
    assert.deepEqual(
      source.units.map((unit) => unit.lessonId),
      source.units.map((_, index) => index + 1),
      `${source.label} LessonID 必须从 1 连续递增`,
    );
    for (const unit of source.units) {
      assert.equal(unit.lessonName, `Unit ${unit.lessonId}`, `${source.label} LessonName 不正确`);
      for (const entry of unit.entries) {
        assert(entry.word?.length > 0, `${source.label}/${unit.lessonName} 存在空 Word`);
        assert(entry.translate?.length > 0, `${source.label}/${unit.lessonName} 存在空 Translate`);
        if (entry.soundmark != null) {
          assert(
            entry.soundmark.startsWith("[") && entry.soundmark.endsWith("]"),
            `${source.label}/${entry.word} 音标必须使用方括号`,
          );
        }
      }
    }
    assert.equal(
      flattenEnglish(source).length,
      source.wordNum,
      `${source.label} 条目数与 WordNum 不一致`,
    );
  }
}

async function readState(connection, forUpdate = false) {
  const lock = forUpdate ? " FOR UPDATE" : "";
  const [books] = await connection.query(
    `SELECT * FROM MiaoguoBook WHERE ID IN (?) ORDER BY ID${lock}`,
    [targetMiaoguoBookIds],
  );
  const [hanziRows] = await connection.query(
    `SELECT * FROM HanziUnit WHERE HanziBookID IN (?) ORDER BY HanziBookID,OrderID,ID${lock}`,
    [targetHanziBookIds],
  );
  const [englishRows] = await connection.query(
    `SELECT * FROM Words WHERE BookID IN (?) ORDER BY BookID,LessonID,Sort,ID${lock}`,
    [targetEnglishBookIds],
  );
  return { books, hanziRows, englishRows };
}

function validateDatabaseMappings(state) {
  assert.equal(state.books.length, targetMiaoguoBookIds.length, "目标 MiaoguoBook 记录不完整");
  for (const source of [...chineseSources, ...englishSources]) {
    const book = state.books.find((row) => row.ID === source.miaoguoBookId);
    assert(book, `MiaoguoBook.ID=${source.miaoguoBookId} 不存在`);
    assert.equal(
      book.BookIDOld,
      source.bookIdOld,
      `${source.label} 的 BookIDOld 与源数据映射不一致`,
    );
  }
}

async function validateHanziDictionary(connection) {
  const chars = [
    ...new Set(
      chineseSources
        .filter((source) => source.unitType === 1 || source.unitType === 5)
        .flatMap((source) => source.units.flatMap((unit) => Array.from(unit.example))),
    ),
  ];
  const [rows] = await connection.query("SELECT DISTINCT Name FROM HanziWord WHERE Name IN (?)", [
    chars,
  ]);
  const found = new Set(rows.map((row) => row.Name));
  const missing = chars.filter((char) => !found.has(char));
  assert.equal(missing.length, 0, `HanziWord 缺少 ${missing.length} 个字：${missing.join("")}`);
  return { checkedDistinctCharacters: chars.length, missing: [] };
}

function buildChinesePlan(source, state) {
  const existing = state.hanziRows.filter((row) => row.HanziBookID === source.bookIdOld);
  assert(
    existing.length <= source.units.length,
    `${source.label} 现有 ${existing.length} 行、新版只有 ${source.units.length} 行；语文禁止删除，不能自动执行`,
  );

  const matched = Array(source.units.length).fill(null);
  const usedIds = new Set();
  const buckets = new Map();
  for (const row of existing) {
    const rows = buckets.get(row.Name) ?? [];
    rows.push(row);
    buckets.set(row.Name, rows);
  }

  for (let index = 0; index < source.units.length; index += 1) {
    const candidates = buckets.get(source.units[index].name) ?? [];
    const row = candidates.find((candidate) => !usedIds.has(candidate.ID));
    if (row) {
      matched[index] = row;
      usedIds.add(row.ID);
    }
  }

  const unusedRows = existing.filter((row) => !usedIds.has(row.ID));
  let unusedIndex = 0;
  for (let index = 0; index < source.units.length; index += 1) {
    if (!matched[index] && unusedIndex < unusedRows.length) {
      matched[index] = unusedRows[unusedIndex];
      usedIds.add(unusedRows[unusedIndex].ID);
      unusedIndex += 1;
    }
  }

  const operations = source.units.map((unit, index) => {
    const old = matched[index];
    return {
      action: old ? (old.Name === unit.name ? "UPDATE_SAME_NAME" : "UPDATE_REPURPOSE") : "INSERT",
      id: old?.ID ?? null,
      oldName: old?.Name ?? null,
      oldExample: old?.Example ?? null,
      oldIsLocked: old?.IsLocked ?? null,
      name: unit.name,
      example: unit.example,
      unitType: source.unitType,
      orderId: index + 1,
      isLocked: old?.IsLocked ?? 1,
    };
  });

  return {
    label: source.label,
    miaoguoBookId: source.miaoguoBookId,
    bookIdOld: source.bookIdOld,
    currentRows: existing.length,
    targetRows: source.units.length,
    currentWordNum: state.books.find((book) => book.ID === source.miaoguoBookId).WordNum,
    targetWordNum: source.wordNum,
    currentUnitNum: state.books.find((book) => book.ID === source.miaoguoBookId).UnitNum,
    targetUnitNum: source.units.length,
    sameNameUpdates: operations.filter((item) => item.action === "UPDATE_SAME_NAME").length,
    repurposedUpdates: operations.filter((item) => item.action === "UPDATE_REPURPOSE").length,
    inserts: operations.filter((item) => item.action === "INSERT").length,
    deletes: 0,
    operations,
  };
}

function buildPlan(state, dictionaryCheck) {
  const chinese = chineseSources.map((source) => buildChinesePlan(source, state));
  const english = englishSources.map((source) => {
    const currentRows = state.englishRows.filter((row) => row.BookID === source.bookIdOld);
    const book = state.books.find((row) => row.ID === source.miaoguoBookId);
    return {
      label: source.label,
      miaoguoBookId: source.miaoguoBookId,
      bookIdOld: source.bookIdOld,
      currentRows: currentRows.length,
      deleteRows: currentRows.length,
      insertRows: flattenEnglish(source).length,
      currentUnitNum: book.UnitNum,
      targetUnitNum: source.units.length,
      currentWordNum: book.WordNum,
      targetWordNum: source.wordNum,
      perUnit: source.units.map((unit) => ({
        lessonId: unit.lessonId,
        lessonName: unit.lessonName,
        count: unit.entries.length,
      })),
      sortPolicy: "每单元从 1 连续递增，接口按 LessonID,Sort,ID 排序",
    };
  });
  return {
    generatedAt: new Date().toISOString(),
    mode,
    safetyRules: {
      chinese: "只执行 UPDATE/INSERT，永不 DELETE",
      english: "事务内按 BookID 删除旧数据并重建；事务前先落盘备份",
      metadata: "同步更新 MiaoguoBook.UnitNum 和 WordNum",
      transaction: "任一写入或逐条核验失败即 ROLLBACK",
    },
    dictionaryCheck,
    chinese,
    english,
  };
}

async function applyChinese(connection, plan) {
  for (const bookPlan of plan.chinese) {
    for (const operation of bookPlan.operations) {
      if (operation.action === "INSERT") {
        const [result] = await connection.execute(
          `INSERT INTO HanziUnit
             (HanziBookID,UnitType,Name,Example,IsLocked,OrderID)
           VALUES (?,?,?,?,?,?)`,
          [
            bookPlan.bookIdOld,
            operation.unitType,
            operation.name,
            operation.example,
            operation.isLocked,
            operation.orderId,
          ],
        );
        operation.id = result.insertId;
      } else {
        const [result] = await connection.execute(
          `UPDATE HanziUnit
              SET HanziBookID=?,UnitType=?,Name=?,Example=?,IsLocked=?,OrderID=?
            WHERE ID=?`,
          [
            bookPlan.bookIdOld,
            operation.unitType,
            operation.name,
            operation.example,
            operation.isLocked,
            operation.orderId,
            operation.id,
          ],
        );
        assert.equal(result.affectedRows, 1, `HanziUnit.ID=${operation.id} 更新失败`);
      }
    }
    const [result] = await connection.execute(
      "UPDATE MiaoguoBook SET UnitNum=?,WordNum=? WHERE ID=? AND BookIDOld=?",
      [bookPlan.targetUnitNum, bookPlan.targetWordNum, bookPlan.miaoguoBookId, bookPlan.bookIdOld],
    );
    assert.equal(result.affectedRows, 1, `${bookPlan.label} MiaoguoBook 更新失败`);
  }
}

async function applyEnglish(connection, plan) {
  for (const source of englishSources) {
    await connection.execute("DELETE FROM Words WHERE BookID=?", [source.bookIdOld]);
    for (const row of flattenEnglish(source)) {
      await connection.execute(
        `INSERT INTO Words
           (Word,WordRemark,Level,BookID,LessonID,LessonName,Soundmark,Translate,
            EnglishExplanation,ExampleSentence,Sort)
         VALUES (?,?,?,?,?,?,?,?,?,?,?)`,
        [
          row.Word,
          row.WordRemark,
          row.Level,
          row.BookID,
          row.LessonID,
          row.LessonName,
          row.Soundmark,
          row.Translate,
          row.EnglishExplanation,
          row.ExampleSentence,
          row.Sort,
        ],
      );
    }
    const englishPlan = plan.english.find((item) => item.bookIdOld === source.bookIdOld);
    const [result] = await connection.execute(
      "UPDATE MiaoguoBook SET UnitNum=?,WordNum=? WHERE ID=? AND BookIDOld=?",
      [
        englishPlan.targetUnitNum,
        englishPlan.targetWordNum,
        englishPlan.miaoguoBookId,
        englishPlan.bookIdOld,
      ],
    );
    assert.equal(result.affectedRows, 1, `${source.label} MiaoguoBook 更新失败`);
  }
}

function assertEnglishRow(actual, expected, label, index) {
  for (const field of [
    "Word",
    "WordRemark",
    "Level",
    "BookID",
    "LessonID",
    "LessonName",
    "Soundmark",
    "Translate",
    "EnglishExplanation",
    "ExampleSentence",
    "Sort",
  ]) {
    assert.equal(actual[field], expected[field], `${label} 第 ${index + 1} 行 ${field} 不一致`);
  }
}

async function auditDatabase(connection) {
  const state = await readState(connection);
  validateDatabaseMappings(state);
  const booksById = new Map(state.books.map((book) => [book.ID, book]));
  const result = { passed: true, books: [], chinese: [], english: [] };

  for (const source of chineseSources) {
    const rows = state.hanziRows.filter((row) => row.HanziBookID === source.bookIdOld);
    assert.equal(rows.length, source.units.length, `${source.label} HanziUnit 行数不一致`);
    rows.forEach((row, index) => {
      assert.equal(row.HanziBookID, source.bookIdOld, `${source.label} 第 ${index + 1} 行 BookID 不一致`);
      assert.equal(row.UnitType, source.unitType, `${source.label} 第 ${index + 1} 行 UnitType 不一致`);
      assert.equal(row.Name, source.units[index].name, `${source.label} 第 ${index + 1} 行 Name 不一致`);
      assert.equal(
        row.Example,
        source.units[index].example,
        `${source.label} 第 ${index + 1} 行 Example 不一致`,
      );
      assert.equal(row.OrderID, index + 1, `${source.label} 第 ${index + 1} 行 OrderID 不一致`);
    });
    const book = booksById.get(source.miaoguoBookId);
    assert.equal(book.UnitNum, source.units.length, `${source.label} UnitNum 不一致`);
    assert.equal(book.WordNum, source.wordNum, `${source.label} WordNum 不一致`);
    result.chinese.push({
      label: source.label,
      miaoguoBookId: source.miaoguoBookId,
      bookIdOld: source.bookIdOld,
      rows: rows.length,
      unitNum: book.UnitNum,
      wordNum: book.WordNum,
      orderIdRange: rows.length ? [rows[0].OrderID, rows.at(-1).OrderID] : [],
      duplicateOrderIds: rows.length - new Set(rows.map((row) => row.OrderID)).size,
    });
  }

  for (const source of englishSources) {
    const actual = state.englishRows.filter((row) => row.BookID === source.bookIdOld);
    const expected = flattenEnglish(source);
    assert.equal(actual.length, expected.length, `${source.label} Words 行数不一致`);
    actual.forEach((row, index) => assertEnglishRow(row, expected[index], source.label, index));
    const book = booksById.get(source.miaoguoBookId);
    assert.equal(book.UnitNum, source.units.length, `${source.label} UnitNum 不一致`);
    assert.equal(book.WordNum, source.wordNum, `${source.label} WordNum 不一致`);
    result.english.push({
      label: source.label,
      miaoguoBookId: source.miaoguoBookId,
      bookIdOld: source.bookIdOld,
      rows: actual.length,
      unitNum: book.UnitNum,
      wordNum: book.WordNum,
      perUnit: source.units.map((unit) => ({
        lessonId: unit.lessonId,
        rows: actual.filter((row) => row.LessonID === unit.lessonId).length,
      })),
    });
  }

  result.books = [...chineseSources, ...englishSources].map((source) => {
    const book = booksById.get(source.miaoguoBookId);
    return {
      id: book.ID,
      bookName: book.BookName,
      bookIdOld: book.BookIDOld,
      unitNum: book.UnitNum,
      wordNum: book.WordNum,
    };
  });
  return result;
}

async function auditChineseIdPreservation(connection, backupHanziRows) {
  const oldIds = backupHanziRows.map((row) => row.ID);
  const [rows] = await connection.query("SELECT ID FROM HanziUnit WHERE ID IN (?)", [oldIds]);
  const existingIds = new Set(rows.map((row) => row.ID));
  const missingIds = oldIds.filter((id) => !existingIds.has(id));
  assert.equal(missingIds.length, 0, `有 ${missingIds.length} 个语文旧 ID 被删除：${missingIds.join(",")}`);
  return {
    oldRows: oldIds.length,
    oldIdsStillPresent: rows.length,
    missingIds,
    newRows: chineseSources.reduce((total, source) => total + source.units.length, 0) - oldIds.length,
  };
}

async function main() {
  validateSources();
  const connection = await mysql.createConnection(config.database);
  try {
    const dictionaryCheck = await validateHanziDictionary(connection);
    const initialState = await readState(connection);
    validateDatabaseMappings(initialState);

    if (mode === "audit") {
      const audit = await auditDatabase(connection);
      const applyReportPath = path.join(taskDir, "apply_report_2026_fall.json");
      assert(fs.existsSync(applyReportPath), `找不到执行报告：${applyReportPath}`);
      const applyReport = JSON.parse(fs.readFileSync(applyReportPath, "utf8"));
      assert(fs.existsSync(applyReport.backupPath), `找不到更新前备份：${applyReport.backupPath}`);
      const backup = JSON.parse(fs.readFileSync(applyReport.backupPath, "utf8"));
      const chineseIdPreservation = await auditChineseIdPreservation(connection, backup.hanziRows);
      const report = {
        generatedAt: new Date().toISOString(),
        mode,
        dictionaryCheck,
        chineseIdPreservation,
        audit,
      };
      const reportPath = path.join(taskDir, "audit_report_2026_fall.json");
      writeJson(reportPath, report);
      console.log(`AUDIT PASS: ${reportPath}`);
      return;
    }

    let plan = buildPlan(initialState, dictionaryCheck);
    if (mode === "dry-run") {
      const reportPath = path.join(taskDir, "dry_run_report_2026_fall.json");
      writeJson(reportPath, plan);
      console.log(`DRY RUN PASS: ${reportPath}`);
      console.log(
        JSON.stringify(
          {
            chinese: plan.chinese.map((item) => ({
              label: item.label,
              rows: `${item.currentRows}->${item.targetRows}`,
              unitNum: `${item.currentUnitNum}->${item.targetUnitNum}`,
              wordNum: `${item.currentWordNum}->${item.targetWordNum}`,
              sameNameUpdates: item.sameNameUpdates,
              repurposedUpdates: item.repurposedUpdates,
              inserts: item.inserts,
              deletes: item.deletes,
            })),
            english: plan.english,
            dictionaryCheck,
          },
          null,
          2,
        ),
      );
      return;
    }

    await connection.beginTransaction();
    try {
      const lockedState = await readState(connection, true);
      validateDatabaseMappings(lockedState);
      plan = buildPlan(lockedState, dictionaryCheck);

      const backupDir = path.join(taskDir, "backups");
      fs.mkdirSync(backupDir, { recursive: true });
      const backupPath = path.join(backupDir, `before_2026_fall_${nowForFile()}.json`);
      writeJson(backupPath, {
        createdAt: new Date().toISOString(),
        note: "所有目标表写入前、事务锁定后的完整备份",
        targetMiaoguoBookIds,
        targetHanziBookIds,
        targetEnglishBookIds,
        ...lockedState,
      });

      await applyChinese(connection, plan);
      await applyEnglish(connection, plan);
      const transactionAudit = await auditDatabase(connection);
      await connection.commit();

      const committedAudit = await auditDatabase(connection);
      const chineseIdPreservation = await auditChineseIdPreservation(
        connection,
        lockedState.hanziRows,
      );
      const report = {
        generatedAt: new Date().toISOString(),
        mode,
        backupPath,
        plan,
        transactionAudit,
        committedAudit,
        chineseIdPreservation,
      };
      const reportPath = path.join(taskDir, "apply_report_2026_fall.json");
      writeJson(reportPath, report);
      console.log(`APPLY AND AUDIT PASS: ${reportPath}`);
      console.log(`BACKUP: ${backupPath}`);
    } catch (error) {
      await connection.rollback();
      throw error;
    }
  } finally {
    await connection.end();
  }
}

main().catch((error) => {
  console.error(error);
  process.exitCode = 1;
});