target/classes/CommunicationCenter.sql

--forums 表 – 论坛板块
CREATE TABLE `forums` (
                          `id` BIGINT NOT NULL AUTO_INCREMENT,
                          `slug` VARCHAR(255) NOT NULL UNIQUE,
                          `name` VARCHAR(255) NOT NULL,
                          `description` TEXT,
                          `parent_id` BIGINT DEFAULT NULL,
                          `sort_order` INT DEFAULT 0,
                          `is_locked` BOOLEAN DEFAULT FALSE,
                          PRIMARY KEY (`id`),
                          FOREIGN KEY (`parent_id`) REFERENCES `forums`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--topics 表 – 帖子主题（一个帖子）
CREATE TABLE `topics` (
                          `id` BIGINT NOT NULL AUTO_INCREMENT,
                          `forum_id` BIGINT NOT NULL,
                          `user_id` BIGINT NOT NULL,
                          `title` VARCHAR(255) NOT NULL,
                          `is_pinned` BOOLEAN DEFAULT FALSE,
                          `is_locked` BOOLEAN DEFAULT FALSE,
                          `created_at` DATETIME NOT NULL,
                          `updated_at` DATETIME NOT NULL,
                          PRIMARY KEY (`id`),
                          FOREIGN KEY (`forum_id`) REFERENCES `forums`(`id`) ON DELETE CASCADE,
                          FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);

--posts 表 – 帖子内容（主题 + 回复）
CREATE TABLE `posts` (
                         `id` BIGINT NOT NULL AUTO_INCREMENT,
                         `topic_id` BIGINT NOT NULL,
                         `user_id` BIGINT NOT NULL,
                         `content` TEXT NOT NULL,
                         `created_at` DATETIME NOT NULL,
                         `updated_at` DATETIME NOT NULL,
                         `is_deleted` BOOLEAN DEFAULT FALSE,
                         PRIMARY KEY (`id`),
                         FOREIGN KEY (`topic_id`) REFERENCES `topics`(`id`) ON DELETE CASCADE,
                         FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);

--post_likes 表 – 帖子点赞记录
CREATE TABLE `post_likes` (
                              `user_id` BIGINT NOT NULL,
                              `post_id` BIGINT NOT NULL,
                              `created_at` DATETIME NOT NULL,
                              PRIMARY KEY (`user_id`, `post_id`),
                              FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
                              FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE
);

--forum_tags 表 – 标签系统
CREATE TABLE `forum_tags` (
                              `id` BIGINT NOT NULL AUTO_INCREMENT,
                              `name` VARCHAR(50) NOT NULL UNIQUE,
                              `color` VARCHAR(10) DEFAULT '#ccc',
                              PRIMARY KEY (`id`)
);

--中间表：topic_tags
CREATE TABLE `topic_tags` (
                              `topic_id` BIGINT NOT NULL,
                              `tag_id` BIGINT NOT NULL,
                              PRIMARY KEY (`topic_id`, `tag_id`),
                              FOREIGN KEY (`topic_id`) REFERENCES `topics`(`id`) ON DELETE CASCADE,
                              FOREIGN KEY (`tag_id`) REFERENCES `forum_tags`(`id`) ON DELETE CASCADE
);

--topic_views 表 – 浏览记录
CREATE TABLE `topic_views` (
                               `topic_id` BIGINT NOT NULL,
                               `user_id` BIGINT NOT NULL,
                               `last_viewed_at` DATETIME NOT NULL,
                               PRIMARY KEY (`topic_id`, `user_id`),
                               FOREIGN KEY (`topic_id`) REFERENCES `topics`(`id`) ON DELETE CASCADE,
                               FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);

--topic_subscriptions 表 – 用户关注(收藏）的帖子
CREATE TABLE `topic_subscriptions` (
                                       `user_id` BIGINT NOT NULL,
                                       `topic_id` BIGINT NOT NULL,
                                       `subscribed_at` DATETIME NOT NULL,
                                       PRIMARY KEY (`user_id`, `topic_id`),
                                       FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
                                       FOREIGN KEY (`topic_id`) REFERENCES `topics`(`id`) ON DELETE CASCADE
);

--用户论坛活动记录表
CREATE TABLE `user_forum_history` (
                                      `id` BIGINT NOT NULL AUTO_INCREMENT,
                                      `user_id` BIGINT NOT NULL,
                                      `type` ENUM('topic', 'reply') NOT NULL COMMENT '操作类型：发主题或回复',
                                      `target_id` BIGINT NOT NULL COMMENT 'topic_id 或 post_id',
                                      `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                      PRIMARY KEY (`id`),
                                      INDEX `idx_user_id` (`user_id`),
                                      INDEX `idx_type_target` (`type`, `target_id`),
                                      CONSTRAINT `fk_ufh_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--