miaoguo_system_server/.vscode/秒过知识库更新/update_ancient_daily_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_ancient_daily_2026_fall.json");
const source = JSON.parse(fs.readFileSync(sourcePath, "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 stringifyContent(content) {
  return JSON.stringify(content);
}

function normalized(value) {
  return value == null ? null : value;
}

function tagFor(book, entry) {
  return book.type === "daily"
    ? `${book.label},日积月累,${entry.kind}`
    : `${book.label},${entry.kind}`;
}

function categoryFor(book, entry) {
  if (book.type === "ancient") return 0;
  return entry.kind === "诗词" ? 0 : 1;
}

function validateSource() {
  assert.equal(source.version, "2026-fall");
  assert.equal(source.sortBase, 0);
  assert.equal(source.books.length, 12, "应包含六册古诗文和六册日积月累");

  const bookIds = source.books.map((book) => book.miaoguoBookId);
  const bookIdsOld = source.books.map((book) => book.bookIdOld);
  assert.equal(new Set(bookIds).size, bookIds.length, "MiaoguoBook.ID 重复");
  assert.equal(new Set(bookIdsOld).size, bookIdsOld.length, "目标 BookIDOld 重复");

  const existingIds = [];
  const newTitles = [];
  for (const book of source.books) {
    assert(["ancient", "daily"].includes(book.type), `${book.label} type 错误`);
    assert(book.entries.length > 0, `${book.label}/${book.type} 没有条目`);
    book.entries.forEach((entry, index) => {
      assert(entry.kind, `${book.label}/${book.type}/Sort=${index} 缺少 kind`);
      const tag = tagFor(book, entry);
      assert(tag.length <= 45, `${tag} 超过 Tag 长度`);
      if (entry.id != null) {
        existingIds.push(entry.id);
      } else {
        assert(entry.title, `${book.label}/${book.type}/Sort=${index} 新增项缺少标题`);
        assert(entry.content, `${book.label}/${book.type}/${entry.title} 新增项缺少正文`);
        newTitles.push(entry.title);
      }
      if (entry.title != null) assert(entry.title.length <= 200, `${entry.title} 标题过长`);
      if (entry.author != null) assert(entry.author.length <= 45, `${entry.title} Author 过长`);
      if (entry.dynasty != null) assert(entry.dynasty.length <= 45, `${entry.title} Dynasty 过长`);
      if (entry.content != null) {
        const content = stringifyContent(entry.content);
        assert(content.length <= 2000, `${entry.title ?? entry.id} PeomContent 超过 2000 字符`);
        assert.deepEqual(JSON.parse(content), entry.content);
      }
    });
  }

  assert.equal(new Set(existingIds).size, existingIds.length, "来源中的 AncientPoetry.ID 重复");
  assert.equal(new Set(newTitles).size, newTitles.length, "新增标题重复");
  const detachIds = source.detach.map((entry) => entry.id);
  assert.equal(new Set(detachIds).size, detachIds.length, "detach ID 重复");
  assert(
    detachIds.every((id) => !existingIds.includes(id)),
    "同一 ID 不能既出现在最终清单又出现在 detach",
  );

  const metadataIds = source.metadata.map((entry) => entry.miaoguoBookId);
  assert.equal(new Set(metadataIds).size, metadataIds.length, "metadata MiaoguoBook.ID 重复");
  for (const book of source.books) {
    const metadata = source.metadata.find((entry) => entry.miaoguoBookId === book.miaoguoBookId);
    assert(metadata, `${book.label}/${book.type} 缺少 metadata`);
    assert.equal(metadata.bookIdOld, book.bookIdOld, `${book.label}/${book.type} metadata 映射错误`);
    assert.equal(metadata.wordNum, book.entries.length, `${book.label}/${book.type} WordNum 不等于最终行数`);
  }
}

async function readState(connection, lock = false) {
  const existingIds = source.books.flatMap((book) => book.entries)
    .filter((entry) => entry.id != null)
    .map((entry) => entry.id);
  const affectedIds = [...new Set([...existingIds, ...source.detach.map((entry) => entry.id)])];
  const metadataIds = source.metadata.map((entry) => entry.miaoguoBookId);
  const targetBookIdsOld = source.books.map((book) => book.bookIdOld);
  const lockSuffix = lock ? " FOR UPDATE" : "";

  const [rows] = await connection.query(
    `SELECT ID, HanziBookID, Sort, Category, Tag, Title, ImgUrl, Author, Dynasty,
            PeomContent, Translation, Flag
       FROM AncientPoetry
      WHERE ID IN (?)${lockSuffix}`,
    [affectedIds],
  );
  const [allRows] = await connection.query(
    `SELECT ID, HanziBookID, Sort, Category, Tag, Title, ImgUrl, Author, Dynasty,
            PeomContent, Translation, Flag
       FROM AncientPoetry${lockSuffix}`,
  );
  const [books] = await connection.query(
    `SELECT ID, BookIDOld, BookName, UnitNum, WordNum
       FROM MiaoguoBook
      WHERE ID IN (?)${lockSuffix}`,
    [metadataIds],
  );
  const [targetRows] = await connection.query(
    `SELECT ID, HanziBookID, Sort, Category, Tag, Title, ImgUrl, Author, Dynasty,
            PeomContent, Translation, Flag
       FROM AncientPoetry
      WHERE HanziBookID IN (?)${lockSuffix}`,
    [targetBookIdsOld],
  );
  return { rows, allRows, books, targetRows };
}

function materialize(state) {
  const allById = new Map(state.allRows.map((row) => [row.ID, row]));
  const allByTitle = new Map();
  for (const row of state.allRows) {
    const matches = allByTitle.get(row.Title) ?? [];
    matches.push(row);
    allByTitle.set(row.Title, matches);
  }

  const referencedIds = source.books.flatMap((book) => book.entries)
    .filter((entry) => entry.id != null)
    .map((entry) => entry.id);
  for (const id of [...referencedIds, ...source.detach.map((entry) => entry.id)]) {
    assert(allById.has(id), `AncientPoetry.ID=${id} 不存在`);
  }

  const desiredBooks = source.books.map((book) => ({
    ...book,
    entries: book.entries.map((entry, index) => {
      let current = null;
      if (entry.id != null) {
        current = allById.get(entry.id);
      } else {
        const titleMatches = allByTitle.get(entry.title) ?? [];
        assert(titleMatches.length <= 1, `新增标题“${entry.title}”在数据库中存在多条记录`);
        if (titleMatches.length === 1) {
          current = titleMatches[0];
          assert.equal(
            current.HanziBookID,
            book.bookIdOld,
            `新增标题“${entry.title}”已存在，但位于 BookIDOld=${current.HanziBookID}`,
          );
        }
      }

      return {
        source: entry,
        current,
        desired: {
          ID: current?.ID ?? null,
          HanziBookID: book.bookIdOld,
          Sort: source.sortBase + index,
          Category: categoryFor(book, entry),
          Tag: tagFor(book, entry),
          Title: entry.title ?? current?.Title,
          ImgUrl: current?.ImgUrl ?? null,
          Author: Object.hasOwn(entry, "author") ? entry.author : normalized(current?.Author),
          Dynasty: Object.hasOwn(entry, "dynasty") ? entry.dynasty : normalized(current?.Dynasty),
          PeomContent: entry.content != null
            ? stringifyContent(entry.content)
            : current?.PeomContent,
          Translation: current?.Translation ?? null,
          Flag: current?.Flag ?? 0,
        },
      };
    }),
  }));

  for (const book of desiredBooks) {
    for (const entry of book.entries) {
      assert(entry.desired.Title, `${book.label}/${book.type} 存在空标题`);
      assert(entry.desired.PeomContent, `${book.label}/${book.type}/${entry.desired.Title} 存在空正文`);
      JSON.parse(entry.desired.PeomContent);
    }
  }

  const currentTargetIds = new Set(state.targetRows.map((row) => row.ID));
  const desiredExistingIds = new Set(
    desiredBooks.flatMap((book) => book.entries)
      .filter((entry) => entry.current)
      .map((entry) => entry.current.ID),
  );
  const detachIds = new Set(source.detach.map((entry) => entry.id));
  const unexplainedTargetIds = [...currentTargetIds]
    .filter((id) => !desiredExistingIds.has(id) && !detachIds.has(id));
  assert.deepEqual(unexplainedTargetIds, [], `目标书存在未处理旧记录：${unexplainedTargetIds.join(",")}`);

  const finalBookByExistingId = new Map(state.allRows.map((row) => [row.ID, row.HanziBookID]));
  for (const book of desiredBooks) {
    for (const entry of book.entries) {
      if (entry.current) finalBookByExistingId.set(entry.current.ID, book.bookIdOld);
    }
  }
  for (const entry of source.detach) finalBookByExistingId.set(entry.id, 0);

  const expectedCounts = new Map();
  for (const bookIdOld of finalBookByExistingId.values()) {
    expectedCounts.set(bookIdOld, (expectedCounts.get(bookIdOld) ?? 0) + 1);
  }
  for (const book of desiredBooks) {
    const inserts = book.entries.filter((entry) => !entry.current).length;
    expectedCounts.set(book.bookIdOld, (expectedCounts.get(book.bookIdOld) ?? 0) + inserts);
  }
  for (const metadata of source.metadata) {
    assert.equal(
      expectedCounts.get(metadata.bookIdOld) ?? 0,
      metadata.wordNum,
      `BookIDOld=${metadata.bookIdOld} 模拟行数与 metadata.WordNum 不一致`,
    );
  }

  return desiredBooks;
}

const compareFields = [
  "HanziBookID",
  "Sort",
  "Category",
  "Tag",
  "Title",
  "Author",
  "Dynasty",
  "PeomContent",
  "Translation",
  "Flag",
];

function buildPlan(state, desiredBooks) {
  const changes = [];
  for (const book of desiredBooks) {
    for (const entry of book.entries) {
      if (!entry.current) {
        changes.push({
          action: "insert",
          bookIdOld: book.bookIdOld,
          sort: entry.desired.Sort,
          title: entry.desired.Title,
        });
        continue;
      }
      const changedFields = compareFields.filter(
        (field) => normalized(entry.current[field]) !== normalized(entry.desired[field]),
      );
      if (changedFields.length > 0) {
        changes.push({
          action: entry.current.HanziBookID === entry.desired.HanziBookID ? "update" : "move",
          id: entry.current.ID,
          fromBookIdOld: entry.current.HanziBookID,
          toBookIdOld: entry.desired.HanziBookID,
          sort: entry.desired.Sort,
          fromTitle: entry.current.Title,
          toTitle: entry.desired.Title,
          changedFields,
        });
      }
    }
  }

  for (const entry of source.detach) {
    const current = state.allRows.find((row) => row.ID === entry.id);
    if (current.HanziBookID !== 0) {
      changes.push({
        action: "detach",
        id: entry.id,
        fromBookIdOld: current.HanziBookID,
        toBookIdOld: 0,
        title: current.Title,
        changedFields: ["HanziBookID"],
      });
    }
  }

  const booksById = new Map(state.books.map((book) => [book.ID, book]));
  const metadataChanges = source.metadata.map((desired) => {
    const current = booksById.get(desired.miaoguoBookId);
    assert(current, `MiaoguoBook.ID=${desired.miaoguoBookId} 不存在`);
    assert.equal(current.BookIDOld, desired.bookIdOld, `MiaoguoBook.ID=${desired.miaoguoBookId} 映射不一致`);
    return {
      miaoguoBookId: desired.miaoguoBookId,
      bookIdOld: desired.bookIdOld,
      from: { unitNum: current.UnitNum, wordNum: current.WordNum },
      to: { unitNum: desired.unitNum, wordNum: desired.wordNum },
      changed: current.UnitNum !== desired.unitNum || current.WordNum !== desired.wordNum,
    };
  });

  return {
    generatedAt: new Date().toISOString(),
    mode,
    sourceVersion: source.version,
    summary: {
      inserts: changes.filter((entry) => entry.action === "insert").length,
      updates: changes.filter((entry) => entry.action === "update").length,
      moves: changes.filter((entry) => entry.action === "move").length,
      detaches: changes.filter((entry) => entry.action === "detach").length,
      metadataUpdates: metadataChanges.filter((entry) => entry.changed).length,
      databaseDeletes: 0,
    },
    books: desiredBooks.map((book) => ({
      miaoguoBookId: book.miaoguoBookId,
      bookIdOld: book.bookIdOld,
      label: book.label,
      type: book.type,
      finalRows: book.entries.length,
      titles: book.entries.map((entry) => entry.desired.Title),
    })),
    changes,
    metadataChanges,
  };
}

async function applyPlan(connection, desiredBooks) {
  const inserted = [];
  const updated = [];
  const detached = [];

  for (const entry of source.detach) {
    const [result] = await connection.query(
      "UPDATE AncientPoetry SET HanziBookID=0 WHERE ID=? AND HanziBookID<>0",
      [entry.id],
    );
    if (result.affectedRows > 0) detached.push(entry.id);
  }

  for (const book of desiredBooks) {
    for (const entry of book.entries) {
      const row = entry.desired;
      if (entry.current) {
        const [result] = await connection.query(
          `UPDATE AncientPoetry
              SET HanziBookID=?, Sort=?, Category=?, Tag=?, Title=?, Author=?, Dynasty=?,
                  PeomContent=?, Translation=?, Flag=?
            WHERE ID=?`,
          [
            row.HanziBookID,
            row.Sort,
            row.Category,
            row.Tag,
            row.Title,
            row.Author,
            row.Dynasty,
            row.PeomContent,
            row.Translation,
            row.Flag,
            entry.current.ID,
          ],
        );
        assert.equal(result.affectedRows, 1, `更新 AncientPoetry.ID=${entry.current.ID} 失败`);
        updated.push(entry.current.ID);
      } else {
        const [result] = await connection.query(
          `INSERT INTO AncientPoetry
             (HanziBookID, Sort, Category, Tag, Title, ImgUrl, Author, Dynasty,
              PeomContent, Translation, Flag)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            row.HanziBookID,
            row.Sort,
            row.Category,
            row.Tag,
            row.Title,
            row.ImgUrl,
            row.Author,
            row.Dynasty,
            row.PeomContent,
            row.Translation,
            row.Flag,
          ],
        );
        inserted.push({ id: result.insertId, bookIdOld: row.HanziBookID, title: row.Title });
      }
    }
  }

  for (const metadata of source.metadata) {
    const [result] = await connection.query(
      "UPDATE MiaoguoBook SET UnitNum=?, WordNum=? WHERE ID=? AND BookIDOld=?",
      [metadata.unitNum, metadata.wordNum, metadata.miaoguoBookId, metadata.bookIdOld],
    );
    assert.equal(result.affectedRows, 1, `更新 MiaoguoBook.ID=${metadata.miaoguoBookId} 失败`);
  }

  return { inserted, updated, detached };
}

async function audit(connection) {
  const failures = [];
  const bookResults = [];
  const entryByExistingId = new Map(
    source.books.flatMap((book) => book.entries)
      .filter((entry) => entry.id != null)
      .map((entry) => [entry.id, entry]),
  );

  for (const book of source.books) {
    const [rows] = await connection.query(
      `SELECT ID, HanziBookID, Sort, Category, Tag, Title, ImgUrl, Author, Dynasty,
              PeomContent, Translation, Flag
         FROM AncientPoetry
        WHERE HanziBookID=?
        ORDER BY Sort, ID`,
      [book.bookIdOld],
    );
    const expectedSorts = book.entries.map((_, index) => source.sortBase + index);
    const actualSorts = rows.map((row) => row.Sort);
    if (rows.length !== book.entries.length) {
      failures.push(`${book.label}/${book.type} 行数 ${rows.length} != ${book.entries.length}`);
    }
    if (JSON.stringify(actualSorts) !== JSON.stringify(expectedSorts)) {
      failures.push(`${book.label}/${book.type} Sort 不连续：${actualSorts.join(",")}`);
    }

    const rowById = new Map(rows.map((row) => [row.ID, row]));
    const rowByTitle = new Map(rows.map((row) => [row.Title, row]));
    const checked = [];
    book.entries.forEach((entry, index) => {
      const row = entry.id != null ? rowById.get(entry.id) : rowByTitle.get(entry.title);
      if (!row) {
        failures.push(`${book.label}/${book.type} 缺少 ${entry.title ?? `ID=${entry.id}`}`);
        return;
      }
      const expectedTitle = entry.title ?? row.Title;
      const expectedCategory = categoryFor(book, entry);
      const expectedTag = tagFor(book, entry);
      if (row.HanziBookID !== book.bookIdOld) failures.push(`ID=${row.ID} HanziBookID 错误`);
      if (row.Sort !== source.sortBase + index) failures.push(`ID=${row.ID} Sort 错误`);
      if (row.Category !== expectedCategory) failures.push(`ID=${row.ID} Category 错误`);
      if (row.Tag !== expectedTag) failures.push(`ID=${row.ID} Tag 错误`);
      if (row.Title !== expectedTitle) failures.push(`ID=${row.ID} Title 错误`);
      try {
        JSON.parse(row.PeomContent);
      } catch {
        failures.push(`ID=${row.ID} PeomContent 不是合法 JSON`);
      }
      if (entry.content != null && row.PeomContent !== stringifyContent(entry.content)) {
        failures.push(`ID=${row.ID} PeomContent 与来源不一致`);
      }
      if (Object.hasOwn(entry, "author") && normalized(row.Author) !== normalized(entry.author)) {
        failures.push(`ID=${row.ID} Author 与来源不一致`);
      }
      if (Object.hasOwn(entry, "dynasty") && normalized(row.Dynasty) !== normalized(entry.dynasty)) {
        failures.push(`ID=${row.ID} Dynasty 与来源不一致`);
      }
      if (entry.id == null && row.Translation != null) failures.push(`新增 ID=${row.ID} Translation 应为空`);
      checked.push({ id: row.ID, sort: row.Sort, title: row.Title });
    });
    if (new Set(rows.map((row) => row.Title)).size !== rows.length) {
      failures.push(`${book.label}/${book.type} 存在重复标题`);
    }
    bookResults.push({
      miaoguoBookId: book.miaoguoBookId,
      bookIdOld: book.bookIdOld,
      label: book.label,
      type: book.type,
      rows: rows.length,
      checked,
    });
  }

  const [detachedRows] = await connection.query(
    "SELECT ID, HanziBookID, Title FROM AncientPoetry WHERE ID IN (?) ORDER BY ID",
    [source.detach.map((entry) => entry.id)],
  );
  for (const entry of source.detach) {
    const row = detachedRows.find((candidate) => candidate.ID === entry.id);
    if (!row) failures.push(`detach ID=${entry.id} 不存在`);
    else if (row.HanziBookID !== 0) failures.push(`detach ID=${entry.id} HanziBookID=${row.HanziBookID}`);
  }

  const [books] = await connection.query(
    "SELECT ID, BookIDOld, UnitNum, WordNum FROM MiaoguoBook WHERE ID IN (?) ORDER BY ID",
    [source.metadata.map((entry) => entry.miaoguoBookId)],
  );
  for (const expected of source.metadata) {
    const book = books.find((candidate) => candidate.ID === expected.miaoguoBookId);
    if (!book) failures.push(`MiaoguoBook.ID=${expected.miaoguoBookId} 不存在`);
    else {
      if (book.BookIDOld !== expected.bookIdOld) failures.push(`MiaoguoBook.ID=${book.ID} BookIDOld 错误`);
      if (book.UnitNum !== expected.unitNum) failures.push(`MiaoguoBook.ID=${book.ID} UnitNum 错误`);
      if (book.WordNum !== expected.wordNum) failures.push(`MiaoguoBook.ID=${book.ID} WordNum 错误`);
      const [[countRow]] = await connection.query(
        "SELECT COUNT(*) AS count FROM AncientPoetry WHERE HanziBookID=?",
        [expected.bookIdOld],
      );
      if (countRow.count !== expected.wordNum) {
        failures.push(`BookIDOld=${expected.bookIdOld} 实际行数 ${countRow.count} != WordNum ${expected.wordNum}`);
      }
    }
  }

  return {
    generatedAt: new Date().toISOString(),
    sourceVersion: source.version,
    ok: failures.length === 0,
    failures,
    books: bookResults,
    detached: detachedRows,
    metadata: books,
    sourceExistingIds: [...entryByExistingId.keys()].length,
  };
}

validateSource();
const connection = await mysql.createConnection({ ...config.database, charset: "utf8mb4" });

try {
  if (mode === "audit") {
    const auditResult = await audit(connection);
    const auditPath = path.join(taskDir, "audit_ancient_daily_2026_fall.json");
    writeJson(auditPath, auditResult);
    assert(auditResult.ok, auditResult.failures.join("\n"));
    console.log(JSON.stringify({ mode, auditPath, ok: true, books: auditResult.books.length }));
    process.exitCode = 0;
  } else if (mode === "dry-run") {
    const state = await readState(connection);
    const desiredBooks = materialize(state);
    const plan = buildPlan(state, desiredBooks);
    const reportPath = path.join(taskDir, "dry_run_ancient_daily_2026_fall.json");
    writeJson(reportPath, plan);
    console.log(JSON.stringify({ mode, reportPath, summary: plan.summary }));
  } else {
    const backupState = await readState(connection);
    const backupPath = path.join(taskDir, "backups", `before_ancient_daily_2026_fall_${nowForFile()}.json`);
    fs.mkdirSync(path.dirname(backupPath), { recursive: true });
    writeJson(backupPath, { capturedAt: new Date().toISOString(), sourceVersion: source.version, ...backupState });

    await connection.beginTransaction();
    try {
      const lockedState = await readState(connection, true);
      const desiredBooks = materialize(lockedState);
      const plan = buildPlan(lockedState, desiredBooks);
      const applied = await applyPlan(connection, desiredBooks);
      const auditResult = await audit(connection);
      assert(auditResult.ok, auditResult.failures.join("\n"));
      await connection.commit();

      const reportPath = path.join(taskDir, "apply_ancient_daily_2026_fall.json");
      writeJson(reportPath, {
        appliedAt: new Date().toISOString(),
        sourceVersion: source.version,
        backupPath,
        plan,
        applied,
        audit: auditResult,
      });
      console.log(JSON.stringify({
        mode,
        reportPath,
        backupPath,
        summary: plan.summary,
        insertedIds: applied.inserted.map((entry) => entry.id),
        auditOk: auditResult.ok,
      }));
    } catch (error) {
      await connection.rollback();
      throw error;
    }
  }
} finally {
  await connection.end();
}