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 `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,
    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,
    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 `post_likes` (
    user_id INT NOT NULL,
    post_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (post_id) REFERENCES posts(post_id),
    PRIMARY KEY (user_id, post_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)
);