src/main/resources/schema.sql

-- 用户表（保持不变）
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_name` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `passkey` VARCHAR(255) NOT NULL UNIQUE,
  `user_level` ENUM('lv1', 'lv2', 'lv3', 'vip') DEFAULT 'lv1',
  `signin_count` INT DEFAULT 0,
  `last_signin_date` DATE,
  `role` ENUM('USER', 'ADMIN') DEFAULT 'USER' COMMENT '用户角色',
  INDEX `idx_user_level` (`user_level`)  -- 按等级查询优化
);
-- 用户统计表
CREATE TABLE IF NOT EXISTS `user_stats` (
    user_id INT PRIMARY KEY,
    passkey VARCHAR(255) NOT NULL UNIQUE,
    total_upload FLOAT NOT NULL DEFAULT 0,
    total_download FLOAT NOT NULL DEFAULT 0,
    last_update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (passkey) REFERENCES users(passkey)
);
-- 种子表
CREATE TABLE IF NOT EXISTS `torrents` (
  `torrent_id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `torrent_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `info_hash` BINARY(20) NOT NULL,
  `file_size` FLOAT NOT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`)
);
-- Peer表（保持不变）
CREATE TABLE IF NOT EXISTS `peers` (
  `passkey` VARCHAR(255) NOT NULL,
  `info_hash` BINARY(20) NOT NULL,
  `peer_id` VARCHAR(20) NOT NULL,
  `ip_address` VARCHAR(128) NOT NULL,
  `port` INT NOT NULL,
  `uploaded` FLOAT NOT NULL,
  `downloaded` FLOAT NOT NULL,
  `last_seen` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`passkey`) REFERENCES `users`(`passkey`),
  PRIMARY KEY (`passkey`, `info_hash`, `peer_id`)
);
-- 帖子表（新增 hot_score 和 last_calculated 字段）
CREATE TABLE IF NOT EXISTS `posts` (
  `post_id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `hot_score` DOUBLE DEFAULT 5.0 COMMENT '热度评分',
  `view_count` INT DEFAULT 0 COMMENT '浏览数',
  `post_title` VARCHAR(255) NOT NULL,
  `post_content` TEXT NOT NULL,
  `torrent_id` INT DEFAULT NULL,
  `post_type` ENUM('resource', 'discussion') NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_calculated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后热度计算时间',
  `average_rating` DECIMAL(3,2) DEFAULT 0.00 COMMENT '帖子平均评分',
  `rating_count` INT DEFAULT 0 COMMENT '总评分人数',
  FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`),
  FOREIGN KEY (`torrent_id`) REFERENCES `torrents`(`torrent_id`),
  INDEX `idx_hot_score` (`hot_score`),          -- 新增热度索引
  INDEX `idx_post_type` (`post_type`)           -- 新增类型索引
);
-- 标签表（保持不变）
CREATE TABLE IF NOT EXISTS `tags`(
  `tag_id` INT PRIMARY KEY,
  `tag_name` VARCHAR(255) NOT NULL,
  `parent_id` INT DEFAULT NULL,
  FOREIGN KEY (`parent_id`) REFERENCES `tags`(`tag_id`)
);
-- 帖子标签关联表（保持不变）
CREATE TABLE IF NOT EXISTS `post_tag` (
  `post_id` INT NOT NULL,
  `tag_id` INT NOT NULL,
  FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`),
  FOREIGN KEY (`tag_id`) REFERENCES `tags`(`tag_id`),
  PRIMARY KEY (`post_id`, `tag_id`)
);
-- 用户关注表（保持不变）
CREATE TABLE IF NOT EXISTS `user_follows` (
  `follower_id` INT NOT NULL,
  `followed_id` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`follower_id`) REFERENCES `users`(`user_id`),
  FOREIGN KEY (`followed_id`) REFERENCES `users`(`user_id`),
  PRIMARY KEY (`follower_id`, `followed_id`)
);
-- 私信表（保持不变）
CREATE TABLE IF NOT EXISTS `private_messages` (
  `message_id` INT AUTO_INCREMENT PRIMARY KEY,
  `sender_id` INT NOT NULL,
  `receiver_id` INT NOT NULL,
  `content` TEXT NOT NULL,
  `sent_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `is_read` BOOLEAN DEFAULT false,
  FOREIGN KEY (`sender_id`) REFERENCES `users`(`user_id`),
  FOREIGN KEY (`receiver_id`) REFERENCES `users`(`user_id`)
);
-- 评论表（保持不变）
CREATE TABLE IF NOT EXISTS `comments` (
  `comment_id` INT AUTO_INCREMENT PRIMARY KEY,
  `post_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `parent_comment_id` INT DEFAULT NULL,
  `content` TEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`),
  FOREIGN KEY (`parent_comment_id`) REFERENCES `comments`(`comment_id`),
  INDEX `idx_post_id` (`post_id`)  -- 新增评论帖子索引
);
-- 帖子点赞表（保持不变）
CREATE TABLE IF NOT EXISTS `post_likes` (
  `user_id` INT NOT NULL,
  `post_id` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`, `post_id`),
  FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`)
);
-- 帖子浏览记录表（新增复合索引）
CREATE TABLE IF NOT EXISTS `post_views` (
  `view_id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `post_id` INT NOT NULL,
  `view_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`),
  FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`),
  INDEX `idx_user_view_time` (`user_id`, `view_time` DESC)  -- 新增用户浏览时间索引
);
CREATE TABLE IF NOT EXISTS user_tag_preference (
    user_id INT NOT NULL COMMENT '用户ID',
    tag_id INT NOT NULL COMMENT '标签ID',
    weight DOUBLE DEFAULT 1.0 COMMENT '偏好权重（浏览越多权重越高）',
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '最后更新时间',
    PRIMARY KEY (user_id, tag_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
);
CREATE TABLE IF NOT EXISTS `post_ratings` (
  `user_id` INT NOT NULL COMMENT '用户ID',
  `post_id` INT NOT NULL COMMENT '帖子ID',
  `rating` TINYINT NOT NULL CHECK (`rating` BETWEEN 1 AND 5) COMMENT '评分值（1-5）',
  `rated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '评分时间',
  PRIMARY KEY (`user_id`, `post_id`),  -- 确保每个用户对同一帖子只能评分一次
  FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`),
  FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`),
  INDEX idx_post_ratings_post_id (post_id)
);

CREATE TABLE IF NOT EXISTS `reports` (
    `report_id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '举报ID',
    `post_id` INT NOT NULL COMMENT '被举报的帖子ID',
    `user_id` INT NOT NULL COMMENT '举报人ID',
    `reason` TEXT NOT NULL COMMENT '举报原因',
    `status` ENUM('pending', 'resolved', 'rejected') DEFAULT 'pending' COMMENT '处理状态（待处理/已解决/已驳回）',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '举报时间',
    `resolved_by` INT DEFAULT NULL COMMENT '处理人ID（管理员）',
    `resolved_at` TIMESTAMP DEFAULT NULL COMMENT '处理时间',
    `resolution_notes` TEXT DEFAULT NULL COMMENT '处理备注',
    FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`),
    FOREIGN KEY (`resolved_by`) REFERENCES `users`(`user_id`)
);

CREATE TABLE IF NOT EXISTS `user_signin` (
    `signin_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '签到记录ID',
    `user_id` INT NOT NULL COMMENT '用户ID',
    `signin_date` DATE NOT NULL COMMENT '签到日期',
    FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_user_daily_signin` (`user_id`, `signin_date`)  -- 唯一约束：用户每日只能签到一次
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;