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

/**
 * 雅思词汇导入秒过知识库
 * 用法:
 *   node ielts/import_ielts.js --dry-run   # 预检,不写库
 *   node ielts/import_ielts.js --import    # 正式导入
 *
 * 数据源: ielts/ielts-xdf-random.json (新东方3050词,有音标/词性/中文释义)
 *         ielts/ielts-4000.json (526词,仅英文释义)
 * 合并去重后 3346 词, 分6本书 (BookID 212-217)
 * 4000独有的296词: 优先从现有四六级/高考词库取中文释义+音标, 否则用英文释义
 */
import { getConnection, query } from '../../../src/util/db.js';
import fs from 'fs';

const DRY_RUN = process.argv.includes('--dry-run');
const IMPORT = process.argv.includes('--import');
if (!DRY_RUN && !IMPORT) {
    console.error('请指定 --dry-run 或 --import');
    process.exit(1);
}

const BOOK_COUNT = 6;
const WORDS_PER_LESSON = 10;
const START_BOOK_ID = 212;      // WordBooks 新ID (当前最大211)
const SORT = 14;                // 排在六级(13)之后
const CATEGORY = '雅思英语';
const CATEGORY2 = '雅思英语';
const CATEGORY3 = '雅思考试单词';
const GRADE = '大学';
const BOOK_IMAGE_NAME = 'ieltsWords';
const REMARK = '雅思备考核心词汇，提升考试通过率。';
const TEST_FUNCTION = JSON.stringify([
    { ID: 1, N: "read", N2: "念单词说含义", C: 0, Img: "picJygs_13", Remark: "题目为单词，答出它的发音，配释义等信息。" },
    { ID: 2, N: "write", N2: "听写单词", C: 0, Img: "picJygs_14", Remark: "题目为单词发音和释义，写出单词。" }
]);
const PART_NAMES = ['第一部分', '第二部分', '第三部分', '第四部分', '第五部分', '第六部分'];

const norm = (w) => String(w || '').trim().toLowerCase();

function normalizeSoundmark(p) {
    if (!p) return null;
    let s = String(p).trim();
    if (!s) return null;
    // 统一为 [xxx] 格式 (参照四级 "[əˈbændən]")
    if (s.startsWith('/') && s.endsWith('/')) s = s.slice(1, -1);
    else if (s.startsWith('[') && s.endsWith(']')) s = s.slice(1, -1);
    s = s.trim();
    if (!s) return null;
    return '[' + s + ']';
}

function buildTranslate(pos, zh) {
    let t = '';
    const p = String(pos || '').trim();
    const z = String(zh || '').trim();
    if (p && z) t = p + z;         // 参照四级 "vt.丢弃 放弃 抛弃"
    else t = z || p;
    return t || null;
}

async function main() {
    // ---------- 1. 读取并合并数据 ----------
    const xdf = JSON.parse(fs.readFileSync(new URL('./ielts-xdf-random.json', import.meta.url), 'utf8'))
        .filter(x => norm(x.word) !== 'word' && norm(x.word) !== '');   // 去掉表头行
    const awl = JSON.parse(fs.readFileSync(new URL('./ielts-4000.json', import.meta.url), 'utf8'));

    const merged = new Map(); // key: norm word -> record
    for (const item of xdf) {
        const key = norm(item.word);
        if (merged.has(key)) continue;
        merged.set(key, {
            word: String(item.word).trim(),
            soundmark: normalizeSoundmark(item.phonetic),
            translate: buildTranslate(item.pos, item.zh),
            source: 'xdf'
        });
    }
    const xdfCount = merged.size;

    const onlyAwl = [];
    for (const item of awl) {
        const key = norm(item.word);
        if (merged.has(key)) continue;
        onlyAwl.push({ word: String(item.word).trim(), def: String(item.def || '').trim() });
        merged.set(key, {
            word: String(item.word).trim(),
            soundmark: null,
            translate: null,
            def: String(item.def || '').trim(),
            source: 'awl'
        });
    }

    // ---------- 2. 从现有四六级/高考词库补释义和音标 ----------
    const dbRef = await query(
        "SELECT Word, Translate, Soundmark FROM kylx365_db.Words WHERE BookID BETWEEN 169 AND 183 AND LOWER(Word) IN (?)",
        [onlyAwl.map(x => norm(x.word))]
    );
    const refMap = new Map();
    for (const r of dbRef) {
        const k = norm(r.Word);
        if (!refMap.has(k)) refMap.set(k, r);
    }
    let refFilled = 0, defFilled = 0;
    for (const item of onlyAwl) {
        const rec = merged.get(norm(item.word));
        const ref = refMap.get(norm(item.word));
        if (ref) {
            rec.translate = ref.Translate;
            rec.soundmark = ref.Soundmark ? normalizeSoundmark(ref.Soundmark) : null;
            refFilled++;
        } else {
            rec.translate = rec.def;  // 英文释义填入
            defFilled++;
        }
    }

    // ---------- 3. 分书 ----------
    const ordered = [...xdf].map(x => merged.get(norm(x.word))).filter(Boolean); // 保持xdf乱序源顺序
    for (const item of onlyAwl) ordered.push(merged.get(norm(item.word)));       // 4000独有词按原字母序追加在后
    const total = ordered.length;

    const base = Math.floor(total / BOOK_COUNT);      // 557
    const extra = total - base * BOOK_COUNT;          // 4
    // 前 extra 本 base+1 词, 其余 base 词 → 558*4 + 557*2 = 3346
    const books = [];
    let idx = 0;
    for (let i = 0; i < BOOK_COUNT; i++) {
        const n = i < extra ? base + 1 : base;
        books.push({ part: i + 1, bookID: START_BOOK_ID + i, words: ordered.slice(idx, idx + n) });
        idx += n;
    }

    console.log(`合并统计: xdf=${xdfCount}, awl独有=${onlyAwl.length}(四六级补释义=${refFilled}, 英文释义=${defFilled}), 总计=${total}`);
    for (const b of books)
        console.log(`  BookID=${b.bookID} ${PART_NAMES[b.part - 1]}: ${b.words.length}词, ${Math.ceil(b.words.length / WORDS_PER_LESSON)}个Lesson`);

    // ---------- 4. 校验 ----------
    const [maxWB] = await query("SELECT MAX(ID) m FROM kylx365_db.WordBooks;");
    const [maxMG] = await query("SELECT MAX(ID) m FROM MiaoguoBook;");
    const [exist] = await query("SELECT COUNT(*) c FROM kylx365_db.WordBooks WHERE Category=?;", [CATEGORY]);
    console.log(`当前最大ID: WordBooks=${maxWB.m}, MiaoguoBook=${maxMG.m}; 已存在"${CATEGORY}"记录=${exist.c}`);
    if (maxWB.m >= START_BOOK_ID || exist.c > 0) {
        console.error('中止: ID已被占用或已存在雅思记录, 请人工检查!');
        process.exit(1);
    }

    if (DRY_RUN) {
        console.log('\n=== 预览: 每本书前3词 ===');
        for (const b of books) {
            console.log(`BookID=${b.bookID}:`);
            for (const w of b.words.slice(0, 3))
                console.log(`   ${w.word} ${w.soundmark || '(无音标)'} ${w.translate || '(无释义)'}`);
        }
        console.log('\n[dry-run] 校验通过, 未写库.');
        process.exit(0);
    }

    // ---------- 5. 导入 (事务) ----------
    const conn = await getConnection();
    try {
        await conn.beginTransaction();

        // 5.1 WordBooks
        for (const b of books) {
            const lessonNum = Math.ceil(b.words.length / WORDS_PER_LESSON);
            await conn.query(
                `INSERT INTO kylx365_db.WordBooks (ID,Sort,Category,Name,Name2,Total,Image,Grade,Category2,Category3,WordType,Remark,Package,BookImageName,WordNum,Flag)
                 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0)`,
                [b.bookID, SORT, CATEGORY, PART_NAMES[b.part - 1], `词汇${b.words.length}`, lessonNum,
                 `../images/examine_subject_a00${b.part}.png`, GRADE, CATEGORY2, CATEGORY3, '单词', REMARK,
                 PART_NAMES[b.part - 1], BOOK_IMAGE_NAME, b.words.length]
            );
        }

        // 5.2 MiaoguoBook
        for (const b of books) {
            const lessonNum = Math.ceil(b.words.length / WORDS_PER_LESSON);
            await conn.query(
                `INSERT INTO MiaoguoBook (BookIDOld,Category,LibraryName1,LibraryName2,Grade,Category2,BookName,Remark,WordType,UnitNum,StartID,WordNum,BookImageName,KnowledgeImageName,TestFunction,Sort,Flag)
                 VALUES (?,?,?,?,?,?,?,?,?,?,0,?,?,?,?,?,0)`,
                [b.bookID, 'English', CATEGORY2, CATEGORY3, GRADE, '课外拓展', PART_NAMES[b.part - 1], REMARK,
                 '单词', lessonNum, b.words.length, BOOK_IMAGE_NAME, '', TEST_FUNCTION, SORT]
            );
        }

        // 5.3 Words (批量插入, 每批500条)
        let inserted = 0;
        const BATCH = 500;
        const rows = [];
        for (const b of books) {
            for (let i = 0; i < b.words.length; i++) {
                const w = b.words[i];
                rows.push([w.word, b.bookID, Math.floor(i / WORDS_PER_LESSON) + 1, w.soundmark, w.translate]);
            }
        }
        for (let i = 0; i < rows.length; i += BATCH) {
            const chunk = rows.slice(i, i + BATCH);
            await conn.query(
                `INSERT INTO kylx365_db.Words (Word,BookID,LessonID,Soundmark,Translate,Sort)
                 VALUES ${chunk.map(() => '(?,?,?,?,?,0)').join(',')}`,
                [chunk.flat()]
            );
            inserted += chunk.length;
            console.log(`  Words 已插入 ${inserted}/${rows.length}`);
        }

        await conn.commit();
        console.log(`\n导入完成: WordBooks 6条, MiaoguoBook 6条, Words ${inserted}条.`);
    } catch (e) {
        await conn.rollback();
        console.error('导入失败, 已回滚:', e.message);
        process.exit(1);
    } finally {
        conn.release();
    }

    // ---------- 6. 验证 ----------
    const wb = await query("SELECT ID,Category,Name,Name2,WordNum,BookImageName,Sort,Flag FROM kylx365_db.WordBooks WHERE ID BETWEEN 212 AND 217;");
    const mg = await query("SELECT ID,BookIDOld,Category,LibraryName1,LibraryName2,BookName,UnitNum,WordNum,Sort,Flag FROM MiaoguoBook WHERE BookIDOld BETWEEN 212 AND 217;");
    const wc = await query("SELECT BookID,COUNT(*) c,COUNT(DISTINCT LOWER(Word)) d FROM kylx365_db.Words WHERE BookID BETWEEN 212 AND 217 GROUP BY BookID;");
    console.log('\n=== 验证 WordBooks ===\n' + JSON.stringify(wb));
    console.log('=== 验证 MiaoguoBook ===\n' + JSON.stringify(mg));
    console.log('=== 验证 Words ===\n' + JSON.stringify(wc));
    process.exit(0);
}

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