-- 用户背单词计划表
CREATE TABLE user_word_plan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL, -- 员工ID
    word_id INT NOT NULL, -- 单词ID
    added_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 加入计划时间
    status VARCHAR(20) DEFAULT 'pending', -- 例如 pending / reviewed / mastered
    FOREIGN KEY (user_id) REFERENCES user(id),
    FOREIGN KEY (word_id) REFERENCES words(id),
    UNIQUE(user_id, word_id) -- 一个用户不能重复添加同一个单词
);
-- 基础词库表，系统内置或导入的所有单词信息
CREATE TABLE words (
    id INT AUTO_INCREMENT PRIMARY KEY,                    -- 单词唯一ID
    word VARCHAR(100) NOT NULL UNIQUE,                     -- 单词文本，唯一
    definition TEXT,                                       -- 单词释义
    pronunciation VARCHAR(100),                            -- 发音（音标或音频链接）
    custom_content TEXT,                                   -- 例句
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP         -- 记录创建时间
);
CREATE TABLE user_word_memory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    word_id INT NOT NULL,
    score INT DEFAULT 0,
    last_seen INT DEFAULT -1,
    FOREIGN KEY (user_id) REFERENCES user(id),
    FOREIGN KEY (word_id) REFERENCES words(id),
    UNIQUE(user_id, word_id)
);
CREATE TABLE feedback (
    id INT AUTO_INCREMENT PRIMARY KEY,
    feedback_type VARCHAR(20) NOT NULL, -- 反馈类型：系统故障、优化意见
    phone VARCHAR(11) NOT NULL,          -- 用户手机号（可用于查询）
    content TEXT NOT NULL,                -- 用户反馈内容
    reply TEXT,                          -- 管理员回复内容
    user_id INT,                         -- 反馈用户的id，方便权限校验等
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
论坛模块：
-- 帖子表：用户发表的帖子，每条记录对应一个帖子
CREATE TABLE post (
    id INT AUTO_INCREMENT PRIMARY KEY,               -- 帖子唯一ID
    user_id INT NOT NULL,                             -- 发帖用户ID，外键（只指普通用户）
    title VARCHAR(255) NOT NULL,                      -- 帖子标题
    content TEXT NOT NULL,                            -- 帖子正文内容
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 帖子创建时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 更新时间
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE -- 普通用户删除，帖子连带删除
);

-- 评论表：帖子下的评论与回复，支持多层回复（通过parent_comment_id）
CREATE TABLE comment (
    id INT AUTO_INCREMENT PRIMARY KEY,               -- 评论唯一ID
    post_id INT NOT NULL,                             -- 所属帖子ID，外键
    user_id INT NOT NULL,                             -- 评论者用户ID，外键
    parent_comment_id INT DEFAULT NULL,               -- 父评论ID，若为空表示一级评论
    content TEXT NOT NULL,                            -- 评论内容
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 评论时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 更新时间
    FOREIGN KEY (post_id) REFERENCES post(id) ON DELETE CASCADE,  -- 帖子删除，评论删除
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,  -- 用户删除，评论删除
    FOREIGN KEY (parent_comment_id) REFERENCES comment(id) ON DELETE CASCADE  -- 父评论删除，子评论删除
);


-- 点赞表：记录用户给帖子点赞情况，每个用户对每个帖子只能点赞一次
CREATE TABLE like_post (
    id INT AUTO_INCREMENT PRIMARY KEY,               -- 点赞记录唯一ID
    user_id INT NOT NULL,                             -- 点赞的用户ID
    post_id INT NOT NULL,                             -- 被点赞帖子ID
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 点赞时间
    UNIQUE KEY user_post_unique (user_id, post_id),  -- 保证每用户对每帖只点赞一次
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
    FOREIGN KEY (post_id) REFERENCES post(id) ON DELETE CASCADE
);


-- 收藏表：记录用户收藏帖子信息，每个用户对每个帖子只能收藏一次
CREATE TABLE favorite_post (
    id INT AUTO_INCREMENT PRIMARY KEY,               -- 收藏记录唯一ID
    user_id INT NOT NULL,                             -- 收藏用户ID
    post_id INT NOT NULL,                             -- 被收藏帖子ID
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 收藏时间
    UNIQUE KEY user_post_unique (user_id, post_id),  -- 每用户每帖只收藏一次
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
    FOREIGN KEY (post_id) REFERENCES post(id) ON DELETE CASCADE
);


-- 举报表：用户举报帖子，记录举报人ID、帖子ID及举报原因
CREATE TABLE report_post (
    id INT AUTO_INCREMENT PRIMARY KEY,               -- 举报记录唯一ID
    reporter_id INT NOT NULL,                         -- 举报人ID，外键
    post_id INT NOT NULL,                             -- 被举报帖子ID，外键
    reason VARCHAR(500) NOT NULL,                     -- 举报原因说明
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 举报时间
    FOREIGN KEY (reporter_id) REFERENCES user(id) ON DELETE CASCADE,
    FOREIGN KEY (post_id) REFERENCES post(id) ON DELETE CASCADE
);

CREATE TABLE `notice` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL COMMENT '发布标题',
  `content` TEXT NOT NULL COMMENT '发布内容',
  `create_time` DATETIME NOT NULL COMMENT '发布时间',
  `update_time` DATETIME NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统通知公告表';

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    phone VARCHAR(11) NOT NULL UNIQUE
);

CREATE TABLE long_term_memory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    word_id INT NOT NULL,
    review_count INT DEFAULT 0,          -- 总复习次数
    interval_days INT DEFAULT 0,         -- 下次复习间隔（单位：天）
    next_review_date DATETIME NOT NULL,  -- 下一次复习目标日期（精确时间）
    last_review_date DATETIME NOT NULL,  -- 上一次复习日期
    longmemory_score INT DEFAULT 0,
    longmemory_seen INT DEFAULT 0,
    pass_count INT DEFAULT 0,             -- 新增字段，复习通过计数
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES user(id),
    FOREIGN KEY (word_id) REFERENCES words(id),
    UNIQUE KEY uniq_user_word (user_id, word_id)
);
实体类的关系是什么