-- 初始化数据库
CREATE DATABASE IF NOT EXISTS memora DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE memora;

-- 词库表（对应 JSON 顶层 bookId）
CREATE TABLE IF NOT EXISTS word_books (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(64) NOT NULL UNIQUE COMMENT '系统词库编码',
    name VARCHAR(120) NOT NULL COMMENT '词库名称',
    source_json_filename VARCHAR(255) NULL UNIQUE COMMENT '来源词库json文件名，如 IELTS_3',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO word_books (id, code, name, source_book_id)
VALUES
    (1, 'default', '默认词库', 'DEFAULT'),
    (2, 'cet4', '四级词库', 'CET4'),
    (3, 'ielts', '雅思词库', 'IELTS'),
    (4, 'toefl', '托福词库', 'TOEFL')
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    source_book_id = VALUES(source_book_id);

-- 主单词表（覆盖 JSON 顶层与 content.word 核心字段）
CREATE TABLE IF NOT EXISTS words (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    book_id BIGINT NOT NULL DEFAULT 1 COMMENT '关联词库ID',
    word_rank INT DEFAULT 0 COMMENT 'JSON: wordRank',
    head_word VARCHAR(120) COMMENT 'JSON: headWord',
    word VARCHAR(120) NOT NULL UNIQUE COMMENT 'JSON: content.word.wordHead',
    source_word_id VARCHAR(64) COMMENT 'JSON: content.word.wordId',
    phonetic_uk VARCHAR(255) COMMENT 'JSON: ukphone',
    phonetic_us VARCHAR(255) COMMENT 'JSON: usphone',
    audio_uk VARCHAR(500) COMMENT '英式音频文件路径(本地缓存)',
    audio_us VARCHAR(500) COMMENT '美式音频文件路径(本地缓存)',
    uk_speech VARCHAR(255) COMMENT 'JSON: ukspeech',
    us_speech VARCHAR(255) COMMENT 'JSON: usspeech',
    part_of_speech VARCHAR(50) COMMENT '兼容旧结构',
    definition TEXT COMMENT '兼容旧结构（可由 trans 聚合）',
    example_sentence TEXT COMMENT '兼容旧结构（可由 sentence 聚合）',
    raw_payload LONGTEXT COMMENT '完整原始JSON',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_word (word),
    INDEX idx_book_id (book_id),
    INDEX idx_word_rank (word_rank),
    INDEX idx_source_word_id (source_word_id),
    FOREIGN KEY (book_id) REFERENCES word_books(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 例句表：content.word.content.sentence.sentences
CREATE TABLE IF NOT EXISTS word_sentences (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    word_id BIGINT NOT NULL,
    sort_order INT DEFAULT 0,
    s_content TEXT COMMENT 'JSON: sContent',
    s_cn TEXT COMMENT 'JSON: sCn',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_word_sentence_word_id (word_id),
    INDEX idx_word_sentence_sort (sort_order),
    FOREIGN KEY (word_id) REFERENCES words(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 释义表：content.word.content.trans
CREATE TABLE IF NOT EXISTS word_translations (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    word_id BIGINT NOT NULL,
    sort_order INT DEFAULT 0,
    pos VARCHAR(32),
    tran_cn TEXT,
    tran_other TEXT,
    desc_cn VARCHAR(64),
    desc_other VARCHAR(64),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_word_trans_word_id (word_id),
    INDEX idx_word_trans_sort (sort_order),
    FOREIGN KEY (word_id) REFERENCES words(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 同近组表：content.word.content.syno.synos
CREATE TABLE IF NOT EXISTS word_synonyms (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    word_id BIGINT NOT NULL,
    sort_order INT DEFAULT 0,
    pos VARCHAR(32),
    tran TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_word_syn_word_id (word_id),
    INDEX idx_word_syn_sort (sort_order),
    FOREIGN KEY (word_id) REFERENCES words(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 同近词项表：content.word.content.syno.synos[*].hwds[*]
CREATE TABLE IF NOT EXISTS word_synonym_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    synonym_id BIGINT NOT NULL,
    sort_order INT DEFAULT 0,
    word VARCHAR(120) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_word_syn_item_syn_id (synonym_id),
    INDEX idx_word_syn_item_sort (sort_order),
    FOREIGN KEY (synonym_id) REFERENCES word_synonyms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 短语表：content.word.content.phrase.phrases
CREATE TABLE IF NOT EXISTS word_phrases (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    word_id BIGINT NOT NULL,
    sort_order INT DEFAULT 0,
    p_content TEXT,
    p_cn TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_word_phrase_word_id (word_id),
    INDEX idx_word_phrase_sort (sort_order),
    FOREIGN KEY (word_id) REFERENCES words(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 同根组表：content.word.content.relWord.rels
CREATE TABLE IF NOT EXISTS word_rels (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    word_id BIGINT NOT NULL,
    sort_order INT DEFAULT 0,
    pos VARCHAR(32),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_word_rel_word_id (word_id),
    INDEX idx_word_rel_sort (sort_order),
    FOREIGN KEY (word_id) REFERENCES words(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 同根词项表：content.word.content.relWord.rels[*].words[*]
CREATE TABLE IF NOT EXISTS word_rel_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    rel_id BIGINT NOT NULL,
    sort_order INT DEFAULT 0,
    hwd VARCHAR(120),
    tran TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_word_rel_item_rel_id (rel_id),
    INDEX idx_word_rel_item_sort (sort_order),
    FOREIGN KEY (rel_id) REFERENCES word_rels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 记忆记录表
CREATE TABLE IF NOT EXISTS memory_records (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    word_id BIGINT NOT NULL COMMENT '关联words表',
    user_id BIGINT DEFAULT 1 COMMENT '用户ID',
    correct_count INT DEFAULT 0 COMMENT '正确次数',
    total_count INT DEFAULT 0 COMMENT '总复习次数',
    mastery_level INT DEFAULT 0 COMMENT '掌握程度 0-5',
    last_reviewed_at TIMESTAMP NULL COMMENT '上次复习时间',
    next_review_at TIMESTAMP NULL COMMENT '下次复习时间',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_word_id (word_id),
    INDEX idx_user_id (user_id),
    INDEX idx_next_review (next_review_at),
    FOREIGN KEY (word_id) REFERENCES words(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;