miaoguo_system_server/秒过知识库更新/04-英语词书导入/ielts/generate_examples.js

// 为雅思英语单词批量补充例句（写入 Words.ExampleSentence）
// 策略：
//   1) 复用：97% 的雅思词在现有词典(BookID=110, 47401词)或其它词书中已有例句，
//      直接复用其 ExampleSentence，并按难度筛选为 2简单(A2)+2中等(B1/B2)+1难(C1)。
//   2) 生成：仅对完全没例句的少量词，用本机 Ollama(qwen3) 本地生成，不依赖外网。
// 输出格式对齐六级：{"word":..,"CEFR_Level":..,"Sentences":[{"Sentence":..,"Translate":..,"Level":..}]}
//
// 用法：
//   node generate_examples.js                 # 复用+本地生成
//   REUSE_ONLY=1 node generate_examples.js    # 只做复用（跳过 Ollama），可先跑这步看覆盖
//   LIMIT=50 node generate_examples.js        # 只处理前 N 个未生成词（验证）
//   OLLAMA_MODEL=qwen3.8:27b-mlx BATCH=8 node generate_examples.js

import { query } from '../../../src/util/db.js';
import axios from 'axios';
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const PROGRESS_FILE = path.join(__dirname, '.ielts_examples_progress.json');

const BOOK_IDS = [212, 213, 214, 215, 216, 217];
const OLLAMA_URL = process.env.OLLAMA_URL || 'http://localhost:11434/api/generate';
const OLLAMA_MODEL = process.env.OLLAMA_MODEL || 'qwen3.8:27b-mlx';
const BATCH = Number(process.env.BATCH || 8);        // Ollama 每批词数
const LIMIT = Number(process.env.LIMIT || 0);        // >0 只处理前 N 个
const REUSE_ONLY = process.env.REUSE_ONLY === '1';

const sleep = (ms) => new Promise((r) => setTimeout(r, ms));

// ---------- 难度筛选：2 A2 + 2 B1/B2 + 1 C1，不足则就近补 ----------
function pickSentences(sentences) {
  const valid = (sentences || []).filter((s) => s && s.Sentence);
  const byLevel = {};
  for (const s of valid) {
    const l = (s.Level || 'B1').toUpperCase();
    (byLevel[l] = byLevel[l] || []).push(s);
  }
  const chosen = [];
  const take = (levels, n) => {
    for (const lv of levels) {
      while (n > 0 && byLevel[lv] && byLevel[lv].length) {
        chosen.push(byLevel[lv].shift());
        n--;
      }
      if (n <= 0) break;
    }
    return n;
  };
  take(['A2', 'A1'], 2); // 简单
  take(['B1', 'B2'], 2); // 中等
  take(['C1', 'C2'], 1); // 难
  const order = ['A2', 'A1', 'B1', 'B2', 'C1', 'C2'];
  const rem = [];
  for (const lv of order) if (byLevel[lv]) rem.push(...byLevel[lv]);
  while (chosen.length < 5 && rem.length) chosen.push(rem.shift());
  return chosen.slice(0, 5).map((s) => ({
    Sentence: s.Sentence,
    Translate: s.Translate || '',
    Level: (s.Level || 'B1').toUpperCase(),
  }));
}

function buildExampleSentence(word, cefr, sentences) {
  return JSON.stringify({
    word,
    CEFR_Level: (cefr || 'B1').toUpperCase(),
    Sentences: sentences,
  });
}

// ---------- 复用源：优先 BookID=110 大词典 ----------
async function buildSourceMap() {
  const rows = await query(
    `select Word, ExampleSentence, BookID from kylx365_db.Words where ExampleSentence is not null and ExampleSentence!=''`
  );
  const map = new Map();
  for (const r of rows) {
    const key = r.Word.toLowerCase();
    const cur = map.get(key);
    if (!cur) map.set(key, r);
    else if (cur.BookID !== 110 && r.BookID === 110) map.set(key, r); // 大词典优先
  }
  return map;
}

function parseSourceExample(raw) {
  try {
    const o = JSON.parse(raw);
    const sents = o.Sentences || o.sentences || [];
    if (!sents.length) return null;
    return { cefr: o.CEFR_Level || o.cefr, sentences: sents };
  } catch {
    return null;
  }
}

// ---------- 本地 Ollama 生成 ----------
async function callOllama(userPrompt) {
  const resp = await axios.post(
    OLLAMA_URL,
    { model: OLLAMA_MODEL, format: 'json', stream: false, think: false, prompt: userPrompt },
    { timeout: 300000 }
  );
  const data = resp.data || {};
  let text = data.response || '';
  if (!text && data.thinking) text = data.thinking; // 思考模式兜底
  text = (text || '').replace(/^```(?:json)?\s*\n/i, '').replace(/\n```\s*$/i, '');
  return text;
}

function buildGenPrompt(words) {
  const lines = words.map((w, i) => `${i + 1}. ${w.Word} | ${w.Translate || ''}`);
  return `Generate example sentences for each English word (with Chinese meaning).
Per word: exactly 5 sentences — 2 at CEFR A2 (simple), 2 at B1-B2 (medium), 1 at C1 (relatively hard).
Each sentence must use the word naturally and correctly per its meaning. Add a concise Chinese translation. Also give the word's approximate CEFR level.
Return JSON only: {"items":[{"word":"emperor","cefr":"B1","sentences":[{"sentence":"...","translate":"...","level":"A2"}, ...5]}]}
The "word" field must exactly match the input word. Include every input word once.

Input words (word | meaning):
${lines.join('\n')}`;
}

function loadProgress() {
  try {
    return JSON.parse(fs.readFileSync(PROGRESS_FILE, 'utf8'));
  } catch {
    return { done: [] };
  }
}
function saveProgress(p) {
  fs.writeFileSync(PROGRESS_FILE, JSON.stringify(p));
}

async function main() {
  let words = await query(
    `select ID, Word, Translate from kylx365_db.Words where BookID in (${BOOK_IDS.join(',')}) and (ExampleSentence is null or ExampleSentence='') order by BookID, ID`
  );
  if (LIMIT > 0) words = words.slice(0, LIMIT);
  console.log(`待处理雅思词行数: ${words.length}`);

  const progress = loadProgress();
  const doneIds = new Set(progress.done);
  words = words.filter((w) => !doneIds.has(w.ID));
  console.log(`本次新增处理: ${words.length}（已跳过 ${doneIds.size}）`);

  const srcMap = await buildSourceMap();
  console.log(`复用源词条数: ${srcMap.size}`);

  let reuseOk = 0,
    genOk = 0,
    fail = 0;
  const genQueue = [];

  // 第一步：尽量复用
  for (const w of words) {
    const src = srcMap.get(w.Word.toLowerCase());
    const parsed = src ? parseSourceExample(src.ExampleSentence) : null;
    if (parsed && parsed.sentences.length) {
      const picked = pickSentences(parsed.sentences);
      if (picked.length >= 1) {
        const json = buildExampleSentence(w.Word, parsed.cefr, picked);
        await query('update kylx365_db.Words set ExampleSentence=? where ID=?', [json, w.ID]);
        reuseOk++;
        progress.done.push(w.ID);
        continue;
      }
    }
    if (!REUSE_ONLY) genQueue.push(w);
    else {
      fail++;
      console.warn(`  ✗ 无复用源: ${w.Word}`);
    }
  }
  saveProgress(progress);
  console.log(`复用完成: ${reuseOk}，待生成: ${genQueue.length}`);

  // 第二步：本地 Ollama 生成剩余
  if (genQueue.length) {
    const batches = [];
    for (let i = 0; i < genQueue.length; i += BATCH) batches.push(genQueue.slice(i, i + BATCH));
    console.log(`Ollama 生成批次数: ${batches.length}`);
    for (let bi = 0; bi < batches.length; bi++) {
      const b = batches[bi];
      const label = `gen batch ${bi + 1}/${batches.length} (${b.map((w) => w.Word).join(',')})`;
      let ok = false;
      for (let attempt = 1; attempt <= 3 && !ok; attempt++) {
        try {
          const raw = await callOllama(buildGenPrompt(b));
          let parsed = JSON.parse(raw);
          const items = parsed.items || parsed;
          const m = new Map();
          for (const it of items) if (it && it.word) m.set(it.word.toLowerCase(), it);
          for (const w of b) {
            const it = m.get(w.Word.toLowerCase());
            if (it && it.sentences && it.sentences.length >= 5) {
              const sents = it.sentences.slice(0, 5).map((s) => ({
                Sentence: s.sentence,
                Translate: s.translate || '',
                Level: (s.level || 'B1').toUpperCase(),
              }));
              const json = buildExampleSentence(w.Word, it.cefr, sents);
              await query('update kylx365_db.Words set ExampleSentence=? where ID=?', [json, w.ID]);
              progress.done.push(w.ID);
              genOk++;
            } else {
              console.warn(`  ✗ 生成缺句: ${w.Word} (got ${(it?.sentences || []).length})`);
            }
          }
          saveProgress(progress);
          ok = true;
          console.log(`✓ ${label} (genOk=${genOk})`);
        } catch (err) {
          console.error(`✗ ${label} 尝试${attempt}失败: ${err?.message || err}`);
          await sleep(3000 * attempt);
        }
      }
      if (!ok) fail += b.length;
      await sleep(500);
    }
  }

  console.log(`\n完成。reuseOk=${reuseOk}, genOk=${genOk}, fail=${fail}, 累计已生成=${progress.done.length}`);
}

main()
  .then(() => process.exit(0))
  .catch((e) => {
    console.error(e);
    process.exit(1);
  });