-- 创建用户单词学习进度表
CREATE TABLE IF NOT EXISTS ai_user_word_progress (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '进度ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    vocabulary_id BIGINT NOT NULL COMMENT '单词ID',
    status VARCHAR(20) DEFAULT 'not_started' COMMENT '学习状态',
    study_count INT DEFAULT 0 COMMENT '学习次数',
    correct_count INT DEFAULT 0 COMMENT '正确次数',
    wrong_count INT DEFAULT 0 COMMENT '错误次数',
    proficiency INT DEFAULT 0 COMMENT '熟练度(0-100)',
    next_review_at TIMESTAMP NULL COMMENT '下次复习时间',
    review_interval INT DEFAULT 1 COMMENT '复习间隔(天)',
    first_studied_at TIMESTAMP NOT NULL COMMENT '首次学习时间',
    last_studied_at TIMESTAMP NOT NULL COMMENT '最后学习时间',
    mastered_at TIMESTAMP NULL COMMENT '掌握时间',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    UNIQUE INDEX idx_user_vocab (user_id, vocabulary_id),
    INDEX idx_next_review (next_review_at),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户单词学习进度表';