// 为 UserID=2 的雅思卡片补充 ContentType=3 例句
// 逻辑：从 ContentType=2 提取单词 -> 在雅思 Words(BookID 212-217) 取 ExampleSentence JSON -> 写入 ContentType=3
// 用法：
//   DRY=1 node update_card_examples.js   # 预检，打印匹配情况，不写库
//   node update_card_examples.js         # 正式写入

import { query } from '../../../src/util/db.js';

const DRY = process.env.DRY === '1';
const USER_ID = 2;
const MIN_CARD_ID = 1671933;
const BOOK_IDS = [212, 213, 214, 215, 216, 217];

function extractWord(content) {
  if (!content) return null;
  // 1) [读 src='...text=WORD&...'] 中的 text= 参数
  const m1 = content.match(/text=([^&\s'"]+)/);
  if (m1) {
    try {
      const w = decodeURIComponent(m1[1]).trim();
      if (w) return w;
    } catch {
      /* ignore */
    }
  }
  // 2) 兜底：行首 "WORD 的基本中文释义"
  const m2 = content.match(/^([A-Za-z][A-Za-z'’.\-\s]*?)\s*的基本中文释义/);
  if (m2) return m2[1].trim();
  return null;
}

async function main() {
  const cards = await query(
    `select MiaoguoCardID from kylx365_db.MiaoguoCard where UserID=? and MiaoguoCardID>=? order by MiaoguoCardID`,
    [USER_ID, MIN_CARD_ID]
  );
  console.log(`目标卡片数: ${cards.length}`);
  if (!cards.length) return;

  const cardIds = cards.map((c) => c.MiaoguoCardID);
  const inSql = '(' + cardIds.join(',') + ')';
  const contents = await query(
    `select MiaoguoContentID, MiaoguoCardID, ContentType, Content from kylx365_db.MiaoguoContent where MiaoguoCardID in ${inSql} and ContentType in (2,3) order by MiaoguoCardID, ContentType`
  );

  // 建立 card -> {2: row, 3: row}
  const map = new Map();
  for (const r of contents) {
    if (!map.has(r.MiaoguoCardID)) map.set(r.MiaoguoCardID, {});
    map.get(r.MiaoguoCardID)[r.ContentType] = r;
  }

  // 收集所有要查的单词
  const wordToCards = new Map(); // lowerWord -> [{cardId, content3Id}]
  for (const [cardId, rec] of map) {
    const c2 = rec[2];
    if (!c2) continue;
    const w = extractWord(c2.Content);
    if (!w) {
      console.warn(`  ✗ 无法提取单词 card=${cardId}`);
      continue;
    }
    const c3 = rec[3];
    if (!c3) {
      console.warn(`  ✗ 缺少 ContentType=3 行 card=${cardId}`);
      continue;
    }
    const key = w.toLowerCase();
    if (!wordToCards.has(key)) wordToCards.set(key, []);
    wordToCards.get(key).push({ cardId, content3Id: c3.MiaoguoContentID, word: w });
  }

  // 一次性查雅思例句
  const words = [...wordToCards.keys()];
  const examples = new Map();
  if (words.length) {
    const rows = await query(
      `select Word, ExampleSentence from kylx365_db.Words where BookID in (${BOOK_IDS.join(',')}) and LOWER(Word) in (?)`,
      [words]
    );
    for (const r of rows) examples.set(r.Word.toLowerCase(), r.ExampleSentence);
  }

  let matched = 0,
    unmatched = 0,
    updated = 0,
    skipped = 0;
  for (const [, list] of wordToCards) {
    for (const item of list) {
      const ex = examples.get(item.word.toLowerCase());
      if (!ex) {
        unmatched++;
        console.warn(`  ✗ 雅思词库无例句: "${item.word}" (card=${item.cardId})`);
        continue;
      }
      matched++;
      if (DRY) {
        console.log(`  · ${item.word} (card=${item.cardId}) -> 例句 ${ex.length} 字符`);
        continue;
      }
      await query('update kylx365_db.MiaoguoContent set Content=? where MiaoguoContentID=?', [
        ex,
        item.content3Id,
      ]);
      updated++;
    }
  }

  console.log(
    `\n预检=${DRY ? '是' : '否'} | 卡片派生词 ${wordToCards.size} | 匹配例句 ${matched} | 无例句 ${unmatched} | 已写入 ${updated}`
  );
}

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