miaoguo_system_server/.vscode/秒过知识库更新/update_miaoguo_literacy_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 sourcePath = path.join(taskDir, "source_miaoguo_literacy_2026_fall.json");
const chineseSourcePath = path.join(taskDir, "source_chinese_2026_fall.json");
const sources = JSON.parse(fs.readFileSync(sourcePath, "utf8"));
const chineseSources = JSON.parse(fs.readFileSync(chineseSourcePath, "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";

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

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

function splitWords(example) {
  return example.split(",").filter(Boolean);
}

function finalVocabularyWords() {
  return [
    ...new Set(
      chineseSources
        .filter((source) => source.unitType === 2)
        .flatMap((source) => source.units.flatMap((unit) => splitWords(unit.example))),
    ),
  ];
}

function buildTianKong(word, pinyin) {
  const chars = Array.from(word);
  const syllables = pinyin.split(" ");
  assert.equal(chars.length, syllables.length, `${word} 的字数与拼音音节数不一致`);
  const seen = new Set();
  const result = [];
  for (let index = 0; index < chars.length; index += 1) {
    const char = chars[index];
    if (seen.has(char)) continue;
    seen.add(char);
    result.push(
      chars
        .map((current, currentIndex) => (current === char ? syllables[currentIndex] : current))
        .join(""),
    );
  }
  return result;
}

function validateSources() {
  assert.equal(sources.length, 20, "本批次应补录 20 个词语");
  assert.equal(new Set(sources.map((source) => source.word)).size, sources.length, "补录词语不能重复");
  const vocabulary = new Set(finalVocabularyWords());
  for (const source of sources) {
    assert(vocabulary.has(source.word), `${source.word} 不在新版词语表中`);
    assert(source.templateWord?.length > 0, `${source.word} 缺少模板词`);
    assert(source.pinyin?.length > 0, `${source.word} 缺少拼音`);
    assert(source.explain?.length > 0, `${source.word} 缺少释义`);
    assert(source.english?.length > 0, `${source.word} 缺少英文释义`);
    assert(Array.isArray(source.synonym), `${source.word} synonym 必须是数组`);
    assert(Array.isArray(source.antonym), `${source.word} antonym 必须是数组`);
    buildTianKong(source.word, source.pinyin);
  }
}

async function loadCharacterAssets(connection) {
  const chars = [...new Set(sources.flatMap((source) => Array.from(source.word)))];
  const [rows] = await connection.query(
    `SELECT ID,Name,KaitiUrl,BiShunUrl
       FROM HanziWord
      WHERE Name IN (?)
        AND KaitiUrl IS NOT NULL AND KaitiUrl<>''
        AND BiShunUrl IS NOT NULL AND BiShunUrl<>''
      ORDER BY Name,ID`,
    [chars],
  );
  const assets = new Map();
  for (const row of rows) {
    if (!assets.has(row.Name)) {
      assets.set(row.Name, { kaitiUrl: row.KaitiUrl, biShunUrl: row.BiShunUrl });
    }
  }
  const missing = chars.filter((char) => !assets.has(char));
  assert.equal(missing.length, 0, `HanziWord 缺少字形资源：${missing.join("")}`);
  return { assets, distinctCharacters: chars.length };
}

async function validateTemplates(connection) {
  const templateWords = [...new Set(sources.map((source) => source.templateWord))];
  const [rows] = await connection.query(
    `SELECT ID,Word,SearchType,JSONString
       FROM MiaoguoLiteracy
      WHERE Word IN (?) AND SearchType='zici'
      ORDER BY Word,ID`,
    [templateWords],
  );
  const templates = new Map();
  for (const row of rows) {
    if (templates.has(row.Word) || !row.JSONString) continue;
    try {
      const json = JSON.parse(row.JSONString);
      if (json.CHN?.PinYin?.[0]) templates.set(row.Word, row);
    } catch {
      // 继续寻找同词的下一条可用记录。
    }
  }
  const missing = templateWords.filter((word) => !templates.has(word));
  assert.equal(missing.length, 0, `找不到具有完整 CHN 的模板词：${missing.join("、")}`);
  return templates;
}

function buildPayload(source, assets) {
  const chars = Array.from(source.word);
  const payload = {
    ENG: {
      Word: source.word,
      Soundmark: "",
      Paraphrase: [{ PartOfSpeech: "", ParaphraseList: source.english }],
    },
    CHN: {
      HanZi: source.word,
      HanZiImageUrl: "",
      BiShunUrl: "",
      KaitiArr: chars.map((char) => assets.get(char).kaitiUrl),
      BiShunArr2: chars.map((char) => assets.get(char).biShunUrl),
      PinYin: [{ pinyin: source.pinyin, explain: source.explain }],
      CombineWords: {},
      Antonym: source.antonym,
      Synonym: source.synonym,
      TianKong: buildTianKong(source.word, source.pinyin),
      PinyinTone: source.pinyin,
    },
  };
  assert.equal(payload.CHN.KaitiArr.length, chars.length, `${source.word} 楷体图数量不正确`);
  assert.equal(payload.CHN.BiShunArr2.length, chars.length, `${source.word} 笔顺图数量不正确`);
  return payload;
}

function inspectLiteracyRow(row) {
  if (!row?.JSONString) return { ok: false, reason: row ? "EMPTY_JSON" : "NO_ROW" };
  try {
    const json = JSON.parse(row.JSONString);
    if (!json.CHN) return { ok: false, reason: "MISSING_CHN" };
    if (!json.CHN.PinYin?.[0]?.pinyin || !json.CHN.PinYin?.[0]?.explain) {
      return { ok: false, reason: "INVALID_CHN_STRUCTURE" };
    }
    return { ok: true, reason: "OK", json };
  } catch {
    return { ok: false, reason: "INVALID_JSON" };
  }
}

async function readTargetRows(connection, forUpdate = false) {
  const lock = forUpdate ? " FOR UPDATE" : "";
  const [rows] = await connection.query(
    `SELECT * FROM MiaoguoLiteracy WHERE Word IN (?) ORDER BY Word,SearchType DESC,ID${lock}`,
    [sources.map((source) => source.word)],
  );
  return rows;
}

async function auditAllVocabulary(connection) {
  const words = finalVocabularyWords();
  const [rows] = await connection.query(
    `SELECT ID,Word,SearchType,JSONString
       FROM MiaoguoLiteracy
      WHERE Word IN (?)
        AND SearchType<>'shici'
        AND SearchType<>'eng'
      ORDER BY Word,SearchType DESC,ID`,
    [words],
  );
  const selectedRows = new Map();
  for (const row of rows) {
    if (!selectedRows.has(row.Word)) selectedRows.set(row.Word, row);
  }
  const problems = [];
  for (const word of words) {
    const row = selectedRows.get(word);
    const check = inspectLiteracyRow(row);
    if (!check.ok) problems.push({ word, reason: check.reason, selectedRowId: row?.ID ?? null });
  }
  return {
    passed: problems.length === 0,
    finalVocabularyWords: words.length,
    selectedUsableRows: words.length - problems.length,
    problems,
  };
}

async function auditInsertedRows(connection, expectedRows) {
  const rows = await readTargetRows(connection);
  assert.equal(rows.length, sources.length, "补录词语行数不等于 20");
  const actualByWord = new Map(rows.map((row) => [row.Word, row]));
  const checks = [];
  for (const expected of expectedRows) {
    const actual = actualByWord.get(expected.word);
    assert(actual, `${expected.word} 写入后不存在`);
    assert.equal(actual.SearchType, "zici", `${expected.word} SearchType 不正确`);
    assert.equal(actual.Author, "", `${expected.word} Author 不正确`);
    assert.equal(actual.ShiciUrl, "", `${expected.word} ShiciUrl 不正确`);
    const actualJson = JSON.parse(actual.JSONString);
    assert.deepEqual(actualJson, expected.payload, `${expected.word} JSONString 与拟写数据不一致`);
    checks.push({
      id: actual.ID,
      word: actual.Word,
      searchType: actual.SearchType,
      pinyin: actualJson.CHN.PinYin[0].pinyin,
      explain: actualJson.CHN.PinYin[0].explain,
      kaitiImages: actualJson.CHN.KaitiArr.length,
      strokeAnimations: actualJson.CHN.BiShunArr2.length,
    });
  }
  return checks;
}

async function main() {
  validateSources();
  const connection = await mysql.createConnection(config.database);
  try {
    const { assets, distinctCharacters } = await loadCharacterAssets(connection);
    const templates = await validateTemplates(connection);
    const expectedRows = sources.map((source) => ({
      word: source.word,
      templateWord: source.templateWord,
      templateId: templates.get(source.templateWord).ID,
      pinyin: source.pinyin,
      explain: source.explain,
      sourceUrl: source.sourceUrl,
      payload: buildPayload(source, assets),
    }));

    if (mode === "audit") {
      const insertedRows = await auditInsertedRows(connection, expectedRows);
      const vocabularyAudit = await auditAllVocabulary(connection);
      assert(vocabularyAudit.passed, `全部词语复查仍有 ${vocabularyAudit.problems.length} 个问题`);
      const report = {
        generatedAt: new Date().toISOString(),
        mode,
        passed: true,
        insertedRows,
        vocabularyAudit,
      };
      const reportPath = path.join(taskDir, "miaoguo_literacy_post_apply_audit_2026_fall.json");
      writeJson(reportPath, report);
      console.log(`AUDIT PASS: ${reportPath}`);
      return;
    }

    const existingRows = await readTargetRows(connection);
    assert.equal(existingRows.length, 0, "目标词语已经存在记录，拒绝重复插入");
    const dryRun = {
      generatedAt: new Date().toISOString(),
      mode,
      targetRows: expectedRows.length,
      existingRows: existingRows.length,
      distinctCharacters,
      templateWords: [...new Set(sources.map((source) => source.templateWord))],
      proposedRows: expectedRows,
    };

    if (mode === "dry-run") {
      const reportPath = path.join(taskDir, "miaoguo_literacy_dry_run_2026_fall.json");
      writeJson(reportPath, dryRun);
      console.log(`DRY RUN PASS: ${reportPath}`);
      console.log(
        JSON.stringify(
          expectedRows.map((row) => ({
            word: row.word,
            templateWord: row.templateWord,
            pinyin: row.pinyin,
            explain: row.explain,
            kaitiImages: row.payload.CHN.KaitiArr.length,
            strokeAnimations: row.payload.CHN.BiShunArr2.length,
          })),
          null,
          2,
        ),
      );
      return;
    }

    await connection.beginTransaction();
    try {
      const lockedTargetRows = await readTargetRows(connection, true);
      assert.equal(lockedTargetRows.length, 0, "事务开始后发现目标词语已有记录，拒绝重复插入");
      const templateRows = [...templates.values()];
      const characterRows = [...assets.entries()].map(([name, value]) => ({ name, ...value }));
      const backupDir = path.join(taskDir, "backups");
      fs.mkdirSync(backupDir, { recursive: true });
      const backupPath = path.join(
        backupDir,
        `before_miaoguo_literacy_2026_fall_${nowForFile()}.json`,
      );
      writeJson(backupPath, {
        createdAt: new Date().toISOString(),
        note: "20 个目标词写入前的目标记录、模板记录和汉字资源备份",
        targetWords: sources.map((source) => source.word),
        targetRows: lockedTargetRows,
        templateRows,
        characterRows,
      });

      const insertIds = [];
      for (const row of expectedRows) {
        const [result] = await connection.execute(
          `INSERT INTO MiaoguoLiteracy
             (Word,SearchType,Author,ShiciUrl,CreateTime,JSONString)
           VALUES (?,?,'','',CURRENT_TIMESTAMP,?)`,
          [row.word, "zici", JSON.stringify(row.payload)],
        );
        insertIds.push({ word: row.word, id: result.insertId });
      }

      const transactionAudit = await auditInsertedRows(connection, expectedRows);
      const transactionVocabularyAudit = await auditAllVocabulary(connection);
      assert(
        transactionVocabularyAudit.passed,
        `事务内全部词语复查仍有 ${transactionVocabularyAudit.problems.length} 个问题`,
      );
      await connection.commit();

      const committedAudit = await auditInsertedRows(connection, expectedRows);
      const committedVocabularyAudit = await auditAllVocabulary(connection);
      assert(
        committedVocabularyAudit.passed,
        `提交后全部词语复查仍有 ${committedVocabularyAudit.problems.length} 个问题`,
      );
      const report = {
        generatedAt: new Date().toISOString(),
        mode,
        backupPath,
        insertIds,
        proposedRows: expectedRows,
        transactionAudit,
        transactionVocabularyAudit,
        committedAudit,
        committedVocabularyAudit,
      };
      const reportPath = path.join(taskDir, "miaoguo_literacy_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;
});