src/main/resources/schema.sql

CREATE DATABASE IF NOT EXISTS g8backend;
USE g8backend;

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
);

CREATE TABLE IF NOT EXISTS `torrents` (
    torrent_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    torrent_name VARCHAR(255) NOT NULL,
    info_hash BINARY(20) NOT NULL,
    file_size FLOAT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

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)
);

CREATE TABLE IF NOT EXISTS `posts` (
    post_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    hot_score DOUBLE DEFAULT 0.0,
    view_count INT DEFAULT 0,
    post_title VARCHAR(255) NOT NULL,
    post_content TEXT NOT NULL,
    post_type ENUM('resource', 'discussion') NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE IF NOT EXISTS `tags`(
  tag_id INT AUTO_INCREMENT PRIMARY KEY,
  tag_name VARCHAR(255) NOT NULL UNIQUE,
  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,      -- 评论ID
    post_id INT NOT NULL,                            -- 所属帖子ID
    user_id INT NOT NULL,                            -- 评论用户ID
    parent_comment_id INT DEFAULT NULL,             -- 父评论ID, 如果是顶级评论则为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) -- 关联父评论
);

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),
    CONSTRAINT fk_post FOREIGN KEY (post_id) REFERENCES posts(post_id),
    CONSTRAINT fk_user 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)
);