post&topic&forum IMP_Backend
Change-Id: I6510aa51e15b15c0e3232e3b8f3a16fe37ffa0c6
diff --git a/target/classes/CommunicationCenter.sql b/target/classes/CommunicationCenter.sql
new file mode 100644
index 0000000..040894a
--- /dev/null
+++ b/target/classes/CommunicationCenter.sql
@@ -0,0 +1,102 @@
+--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;
+
+--
\ No newline at end of file