import Database from 'better-sqlite3';
import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const dataDir = path.join(__dirname, 'data');
if (!fs.existsSync(dataDir)) fs.mkdirSync(dataDir, { recursive: true });

const db = new Database(path.join(dataDir, 'app.db'));
db.pragma('journal_mode = WAL');

// 破坏性变更：重建表结构（旧数据已清除）
db.exec(`
CREATE TABLE IF NOT EXISTS history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  sentence TEXT NOT NULL,
  words TEXT NOT NULL,          -- 查询的单词，JSON 数组
  result TEXT NOT NULL,         -- LLM 结构化结果，JSON（含词根拆解）
  raw_result TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
);

-- 词库（按单词维度维护，跨句子累积）
CREATE TABLE IF NOT EXISTS words (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  word TEXT NOT NULL UNIQUE,
  phonetic TEXT,                -- 整词音标
  roots TEXT NOT NULL DEFAULT '[]',
  -- 词根拆解: [{part, type:前缀|词根|后缀|音节, phonetic, meaning}]
  senses TEXT NOT NULL DEFAULT '[]',
  -- 释义（同义项合并）: [{sense, examples: [{en, zh, from_sentence}]}]
  created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
);

-- 词根库（按词根维度维护，跨单词累积）
CREATE TABLE IF NOT EXISTS roots (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  root TEXT NOT NULL UNIQUE,    -- 规范化键：去连字符/空格、小写，如 'lysis'
  display TEXT NOT NULL,        -- 展示形（如 "lysis"、"-tion"），以最近出现为准
  type TEXT,                    -- 前缀 / 词根 / 后缀 / 音节
  phonetic TEXT,                -- 发音
  words TEXT NOT NULL DEFAULT '[]',
  -- 包含该词根的词: [{word, phonetic, meaning}]
  --   meaning = 该词中体现的词根含义（允许不同词里有差异，展示时并列，帮助大脑抽象）
  created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
);
`);

// 迁移：words 表补充 root_keys、search_text 列
// search_text = 单词 + 义项标题 + 词根拆解（不含例句正文，避免例句里的无关词干扰检索）
function buildWordSearchText(word, senses, roots) {
  return [
    word,
    ...(senses || []).map((s) => s.sense),
    ...(roots || []).flatMap((r) => [r.part, normalizeRootKey(r.part), r.meaning]),
  ]
    .filter(Boolean)
    .join(' ')
    .toLowerCase();
}
{
  const cols = db.prepare("PRAGMA table_info(words)").all().map((c) => c.name);
  if (!cols.includes('root_keys')) {
    db.exec("ALTER TABLE words ADD COLUMN root_keys TEXT NOT NULL DEFAULT '[]'");
  }
  if (!cols.includes('search_text')) {
    db.exec("ALTER TABLE words ADD COLUMN search_text TEXT NOT NULL DEFAULT ''");
  }
  // 回填历史数据的 search_text
  const rows = db.prepare('SELECT word, senses, roots FROM words').all();
  const upd = db.prepare('UPDATE words SET search_text = ? WHERE word = ?');
  for (const r of rows) {
    upd.run(
      buildWordSearchText(
        r.word,
        JSON.parse(r.senses),
        JSON.parse(r.roots)
      ),
      r.word
    );
  }
}

const stmts = {
  // ---- history ----
  insert: db.prepare(
    'INSERT INTO history (sentence, words, result, raw_result) VALUES (?, ?, ?, ?)'
  ),
  get: db.prepare('SELECT * FROM history WHERE id = ?'),
  update: db.prepare(
    `UPDATE history SET sentence = @sentence, words = @words, result = @result,
     updated_at = datetime('now', 'localtime') WHERE id = @id`
  ),
  del: db.prepare('DELETE FROM history WHERE id = ?'),

  // ---- words（词库）----
  getWord: db.prepare('SELECT * FROM words WHERE word = ?'),
  insertWord: db.prepare(
    `INSERT INTO words (word, phonetic, roots, senses, root_keys, search_text) VALUES (@word, @phonetic, @roots, @senses, @root_keys, @search_text)`
  ),
  updateWord: db.prepare(
    `UPDATE words SET phonetic = @phonetic, roots = @roots, senses = @senses,
     root_keys = @root_keys, search_text = @search_text,
     updated_at = datetime('now', 'localtime') WHERE id = @id`
  ),

  // ---- roots（词根库）----
  getRoot: db.prepare('SELECT * FROM roots WHERE root = ?'),
  insertRoot: db.prepare(
    `INSERT INTO roots (root, display, type, phonetic, words) VALUES (@root, @display, @type, @phonetic, @words)`
  ),
  updateRoot: db.prepare(
    `UPDATE roots SET display = @display, type = @type, phonetic = @phonetic, words = @words,
     updated_at = datetime('now', 'localtime') WHERE id = @id`
  ),
  delRoot: db.prepare('DELETE FROM roots WHERE id = ?'),
};

/* ---------- history ---------- */

export function saveRecord({ sentence, words, result, raw_result }) {
  const info = stmts.insert.run(
    sentence,
    JSON.stringify(words),
    JSON.stringify(result),
    raw_result ?? null
  );
  return getRecord(info.lastInsertRowid);
}

/* 搜索：LIKE 通配符转义（% _ 视为普通字符） */
function likeTerm(q) {
  return '%' + String(q || '').replace(/[%_\\]/g, '\\$&') + '%';
}

function pagedQuery({ baseCols, from, orderBy = 'id DESC', whereCols, page, size, q, postProcess }) {
  const hasSearch = q && String(q).trim();
  const where = hasSearch
    ? 'WHERE ' + whereCols.map((c) => `${c} LIKE @q ESCAPE '\\'`).join(' OR ')
    : '';
  const total = db
    .prepare(`SELECT COUNT(*) AS n FROM ${from} ${where}`)
    .get(hasSearch ? { q: likeTerm(q) } : {}).n;
  const items = db
    .prepare(
      `SELECT ${baseCols} FROM ${from} ${where} ORDER BY ${orderBy} LIMIT @limit OFFSET @offset`
    )
    .all({
      ...(hasSearch ? { q: likeTerm(q) } : {}),
      limit: size,
      offset: (page - 1) * size,
    })
    .map(postProcess);
  return {
    items,
    total,
    q: hasSearch ? String(q).trim() : '',
    page,
    size,
    total_pages: Math.max(1, Math.ceil(total / size)),
  };
}

export function listRecords(page = 1, size = 10, q = '') {
  return pagedQuery({
    baseCols: 'id, sentence, words, created_at, updated_at',
    from: 'history',
    whereCols: ['sentence', 'words'], // 匹配原句或查询的单词
    page,
    size,
    q,
    postProcess: (r) => ({ ...r, words: JSON.parse(r.words) }),
  });
}

export function getRecord(id) {
  const r = stmts.get.get(id);
  if (!r) return null;
  return { ...r, words: JSON.parse(r.words), result: JSON.parse(r.result) };
}

export function updateRecord(id, { sentence, words, result }) {
  const info = stmts.update.run({
    id,
    sentence,
    words: JSON.stringify(words),
    result: JSON.stringify(result),
  });
  if (info.changes === 0) return null;
  return getRecord(id);
}

export function deleteRecord(id) {
  const info = stmts.del.run(id);
  return info.changes > 0;
}

/* ---------- words（词库）---------- */

export function getWord(word) {
  const r = stmts.getWord.get(word);
  if (!r) return null;
  return { ...r, roots: JSON.parse(r.roots), senses: JSON.parse(r.senses) };
}

export function listWords(page = 1, size = 10, q = '') {
  return pagedQuery({
    baseCols: 'word, phonetic, roots, senses, created_at, updated_at',
    from: 'words',
    orderBy: 'updated_at DESC',
    whereCols: ['search_text'], // 匹配单词、释义或词根（不含例句正文）
    page,
    size,
    q,
    postProcess: (r) => {
      const senses = JSON.parse(r.senses);
      const occurrences = senses.reduce(
        (n, s) => n + (s.examples || []).filter((e) => e.from_sentence).length,
        0
      );
      return {
        ...r,
        roots: JSON.parse(r.roots),
        senses,
        sense_count: senses.length,
        occurrence_count: occurrences,
      };
    },
  });
}

/**
 * 按"单词维度"合并查询结果：
 * - 词根拆解：首次创建时写入，之后保留（除非原来为空）
 * - 原句语境对应已有释义（context_sense_id 有效）→ 该释义下追加"来自原句"的记录
 * - 语境为新释义 → 新增义项；其他新释义也并入（按释义文案去重）
 */
export function upsertWordData(w, sentence, sentenceTranslation) {
  if (!w?.word) return null;
  const existing = stmts.getWord.get(w.word);
  let senses = existing ? JSON.parse(existing.senses) : [];
  // 原句记录
  const occ = {
    en: sentence,
    zh: sentenceTranslation || '',
    from_sentence: true,
  };

  // 查询时生成的同义例句：与原句一起并入词库
  const generated = (w.context_examples || [])
    .map((e) => ({ en: (e.en || '').trim(), zh: (e.zh || '').trim() }))
    .filter((e) => e.en);

  // 按英文句子去重（原句与例句可重复，避免同一句重复堆积）
  const addUnique = (list, ex) => {
    if (!ex.en) return;
    if (!list.some((x) => x.en === ex.en)) list.push(ex);
  };

  const ctxId = w.context_sense_id;
  if (
    typeof ctxId === 'number' &&
    Number.isInteger(ctxId) &&
    ctxId >= 0 &&
    ctxId < senses.length
  ) {
    // 语境对应已有释义：原句 + 本次生成的同义例句一起并入
    const s = senses[ctxId];
    s.examples = s.examples || [];
    addUnique(s.examples, occ);
    generated.forEach((ex) => addUnique(s.examples, ex));
    if (w.context_sense) s.sense = w.context_sense; // 释义文案以最新为准
  } else if (w.context_sense) {
    senses.push({ sense: w.context_sense, examples: [occ, ...generated] });
  }

  // 并入其他释义：已有同文案释义 → 例句并入；否则新增释义
  for (const os of w.other_senses || []) {
    if (!os?.sense) continue;
    const osExamples = (os.examples || [])
      .map((e) => ({ en: (e.en || '').trim(), zh: (e.zh || '').trim() }))
      .filter((e) => e.en);
    const matched = senses.find((s) => s.sense === os.sense);
    if (matched) {
      matched.examples = matched.examples || [];
      osExamples.forEach((ex) => addUnique(matched.examples, ex));
    } else {
      senses.push({ sense: os.sense, examples: osExamples });
    }
  }
  
  // 词根拆解：优先用最新结果；新词必写，旧词仅在原来为空时补写

  // 词根拆解：优先用最新结果；新词必写，旧词仅在原来为空时补写
  const oldRoots = existing ? JSON.parse(existing.roots) : [];
  const roots = w.roots?.length ? w.roots : oldRoots;

  // ---- 词根维度同步 ----
  const newKeys = [...new Set(roots.map((r) => normalizeRootKey(r.part)).filter(Boolean))];
  if (existing) {
    // 单词重新拆解后，从不再包含的旧词根下摘除该词，避免幽灵条目
    const oldKeys = JSON.parse(existing.root_keys || '[]');
    for (const k of oldKeys) {
      if (newKeys.includes(k)) continue;
      const r = stmts.getRoot.get(k);
      if (!r) continue;
      const remaining = JSON.parse(r.words).filter((e) => e.word !== w.word);
      if (remaining.length === 0) stmts.delRoot.run(r.id);
      else stmts.updateRoot.run({ ...r, words: JSON.stringify(remaining) });
    }
  }
  for (const r of roots) {
    // 传入该词的全部释义，词根面板点击单词时一并揭晓
    upsertRootData(r, w.word, w.phonetic || '', senses.map((s) => s.sense));
  }

  const row = {
    word: w.word,
    phonetic: w.phonetic || existing?.phonetic || '',
    roots: JSON.stringify(roots),
    senses: JSON.stringify(senses),
    root_keys: JSON.stringify(newKeys),
    search_text: buildWordSearchText(w.word, senses, roots),
    id: existing?.id,
  };
  if (existing) stmts.updateWord.run(row);
  else stmts.insertWord.run(row);
  return getWord(w.word);
}

/* ---------- roots（词根库）---------- */

export function normalizeRootKey(part) {
  return String(part || '').replace(/[^a-zA-Z]/g, '').toLowerCase();
}

export function getRoot(rootKey) {
  const r = stmts.getRoot.get(rootKey);
  if (!r) return null;
  return { ...r, words: JSON.parse(r.words) };
}

/**
 * 按词根维度累积：同一词根下追加包含它的单词（含该词中体现的含义）。
 * 含义不强行统一——不同词中的含义变体并列展示，让学习者自行抽象。
 */
export function upsertRootData(
  { part, type, phonetic, meaning },
  word,
  wordPhonetic,
  wordSenses = []
) {
  const key = normalizeRootKey(part);
  if (!key) return null;
  const existing = stmts.getRoot.get(key);
  let words = existing ? JSON.parse(existing.words) : [];
  // word_senses: 该单词自身的释义列表（词根面板中点击单词时一并揭晓）
  const entry = {
    word,
    phonetic: wordPhonetic || '',
    meaning: meaning || '',
    word_senses: (wordSenses || []).filter(Boolean),
  };
  const idx = words.findIndex((e) => e.word === word);
  if (idx !== -1) words[idx] = entry; // 同一单词以最新拆解覆盖
  else words.push(entry);

  const row = {
    id: existing?.id,
    root: key,
    display: part,
    type: type || existing?.type || '',
    phonetic: phonetic || existing?.phonetic || '',
    words: JSON.stringify(words),
  };
  if (existing) stmts.updateRoot.run(row);
  else stmts.insertRoot.run(row);
  return getRoot(key);
}