-- 添加词汇扩展字段
-- 为ai_vocabulary表添加同义词、反义词、派生词等字段

USE ai_english_learning;

-- 添加扩展字段到词汇表
ALTER TABLE ai_vocabulary 
ADD COLUMN IF NOT EXISTS synonyms JSON COMMENT '同义词列表 ["happy", "joyful"]',
ADD COLUMN IF NOT EXISTS antonyms JSON COMMENT '反义词列表 ["sad", "unhappy"]',
ADD COLUMN IF NOT EXISTS derivatives JSON COMMENT '派生词列表 [{"word": "happiness", "meaning": "幸福"}]',
ADD COLUMN IF NOT EXISTS collocations JSON COMMENT '词组搭配 [{"phrase": "look forward to", "meaning": "期待"}]',
ADD COLUMN IF NOT EXISTS word_root TEXT COMMENT '词根信息';

-- 添加索引
ALTER TABLE ai_vocabulary
ADD INDEX idx_phonetic_us (phonetic_us(50)),
ADD INDEX idx_phonetic_uk (phonetic_uk(50));

-- 更新现有数据的默认值
UPDATE ai_vocabulary 
SET 
    synonyms = JSON_ARRAY() WHERE synonyms IS NULL,
    antonyms = JSON_ARRAY() WHERE antonyms IS NULL,
    derivatives = JSON_ARRAY() WHERE derivatives IS NULL,
    collocations = JSON_ARRAY() WHERE collocations IS NULL;