all_tables.sql

/*
数据库设计说明：
1.	核心表结构：
	users：存储用户信息，包含角色管理和账号状态
	posts：核心内容表，支持多模态内容（图文/视频/文档）
	behaviors：记录用户互动行为（点赞/收藏/浏览等）
	comments：评论系统，支持多级回复
	follows：用户社交关系
2.	推荐系统支持：
	posts.heat 字段存储动态计算的热度值
	behaviors 表记录用户行为用于协同过滤
	user_tags 表构建用户兴趣画像
	通过 post_tags 实现内容标签分类
3.	多模态内容处理：
	posts.media_urls 使用 JSON 类型存储多个资源 URL
	posts.type 区分不同类型的内容（图文/视频/文档）
4.	审核与安全：
	audits 表记录内容审核历史
	posts.status 管理内容生命周期状态
	logs 表记录系统操作和访问日志
5.	性能优化：
	为查询频繁字段添加索引（热度/行为类型/时间）
	使用 JSON 类型存储灵活数据（通知内容/媒体资源）
	通过 heat 字段预计算支持热门排序
6.	扩展性设计：
	用户画像系统通过 user_tags 表实现
	通知系统支持多种互动类型
	行为表设计支持未来扩展新行为类型
*/


-- 创建数据库
CREATE DATABASE IF NOT EXISTS redbook DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
USE redbook;

-- 用户表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '加密密码',
    email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
    avatar VARCHAR(255) COMMENT '头像URL',
    role ENUM('superadmin', 'user', 'admin') DEFAULT 'user' COMMENT '角色',
    bio VARCHAR(255) COMMENT '个人简介',
    status ENUM('active', 'banned', 'muted') DEFAULT 'active' COMMENT '账号状态',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB COMMENT='用户表';

-- 邮箱验证表
CREATE TABLE email_verifications (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '验证ID',
    email VARCHAR(100) NOT NULL COMMENT '邮箱地址',
    code VARCHAR(255) NOT NULL COMMENT '验证码',
    type ENUM('register', 'reset_password', 'email_change') NOT NULL COMMENT '验证类型',
    user_id INT DEFAULT NULL COMMENT '用户ID（找回密码时使用）',
    is_verified BOOLEAN DEFAULT FALSE COMMENT '是否已验证',
    expires_at TIMESTAMP NOT NULL COMMENT '过期时间',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    verified_at TIMESTAMP NULL DEFAULT NULL COMMENT '验证时间',
    INDEX idx_email_code (email, code),
    INDEX idx_email_type (email, type),
    INDEX idx_expires_at (expires_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='邮箱验证表';

-- 标签表
CREATE TABLE tags (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '标签ID',
    name VARCHAR(50) NOT NULL UNIQUE COMMENT '标签名称',
    description VARCHAR(255) COMMENT '标签描述',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB COMMENT='内容标签表';

-- 话题/超话表
CREATE TABLE topics (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '话题ID',
    name VARCHAR(100) NOT NULL UNIQUE COMMENT '话题名称',
    description TEXT COMMENT '话题描述',
    status ENUM('active', 'archived') DEFAULT 'active' COMMENT '状态',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB COMMENT='话题/超话表';

-- 内容帖子表
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '帖子ID',
    user_id INT NOT NULL COMMENT '作者ID',
    topic_id INT COMMENT '所属话题ID',
    type ENUM('text', 'image', 'video', 'document') DEFAULT 'text' COMMENT '内容类型',
    title VARCHAR(255) NOT NULL COMMENT '标题',
    content TEXT NOT NULL COMMENT '正文内容',
    media_urls JSON COMMENT '媒体资源URL数组',
    status ENUM('draft', 'pending', 'published', 'deleted', 'rejected') DEFAULT 'draft' COMMENT '状态',
    heat INT DEFAULT 0 COMMENT '热度值',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE SET NULL
) ENGINE=InnoDB COMMENT='内容帖子表';

-- 帖子标签关联表
CREATE TABLE post_tags (
    post_id INT NOT NULL COMMENT '帖子ID',
    tag_id INT NOT NULL COMMENT '标签ID',
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='帖子标签关联表';

-- 用户行为表
CREATE TABLE behaviors (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '行为ID',
    user_id INT NOT NULL COMMENT '用户ID',
    post_id INT NOT NULL COMMENT '帖子ID',
    type ENUM('like', 'comment', 'favorite', 'view', 'share') NOT NULL COMMENT '行为类型',
    value INT DEFAULT 1 COMMENT '行为值',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '行为时间',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='用户行为记录表';

-- 评论表
CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '评论ID',
    post_id INT NOT NULL COMMENT '帖子ID',
    user_id INT NOT NULL COMMENT '用户ID',
    parent_id INT DEFAULT NULL COMMENT '父评论ID',
    content TEXT NOT NULL COMMENT '评论内容',
    status ENUM('active', 'deleted') DEFAULT 'active' COMMENT '状态',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='评论表';

-- 用户关注关系表
CREATE TABLE follows (
    follower_id INT NOT NULL COMMENT '关注者ID',
    followee_id INT NOT NULL COMMENT '被关注者ID',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '关注时间',
    PRIMARY KEY (follower_id, followee_id),
    FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (followee_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='用户关注关系表';

-- 通知表
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '通知ID',
    user_id INT NOT NULL COMMENT '接收用户ID',
    type ENUM('like', 'comment', 'follow', 'system', 'audit') NOT NULL COMMENT '通知类型',
    content JSON NOT NULL COMMENT '通知内容',
    is_read BOOLEAN DEFAULT FALSE COMMENT '是否已读',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='用户通知表';

-- 审核记录表
CREATE TABLE audits (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '审核ID',
    post_id INT NOT NULL COMMENT '帖子ID',
    admin_id INT NOT NULL COMMENT '管理员ID',
    result ENUM('approved', 'rejected') NOT NULL COMMENT '审核结果',
    reason VARCHAR(255) COMMENT '审核原因',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '审核时间',
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='内容审核记录表';

-- 日志表
CREATE TABLE logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '日志ID',
    user_id INT DEFAULT NULL COMMENT '用户ID',
    type ENUM('access', 'error', 'behavior', 'system') NOT NULL COMMENT '日志类型',
    content TEXT NOT NULL COMMENT '日志内容',
    ip VARCHAR(45) COMMENT 'IP地址',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录时间',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB COMMENT='系统日志表';

-- 用户兴趣标签表（用户画像）
CREATE TABLE user_tags (
    user_id INT NOT NULL COMMENT '用户ID',
    tag_id INT NOT NULL COMMENT '标签ID',
    weight FLOAT DEFAULT 1.0 COMMENT '兴趣权重',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (user_id, tag_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='用户兴趣标签表';

-- 索引优化
CREATE INDEX idx_posts_heat ON posts(heat);
CREATE INDEX idx_behaviors_type ON behaviors(type);
CREATE INDEX idx_notifications_read ON notifications(is_read);
CREATE INDEX idx_logs_created ON logs(created_at);
CREATE INDEX idx_comments_post ON comments(post_id);