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