database.sql

CREATE DATABASE IF NOT EXISTS booksocial
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

CREATE USER IF NOT EXISTS 'NOME_UTENTE'@'localhost' IDENTIFIED BY 'PASSWORD_UTENTE';
GRANT ALL PRIVILEGES ON booksocial.* TO 'NOME_UTENTE'@'localhost';
FLUSH PRIVILEGES;

USE booksocial;

-- USERS
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    profile_image VARCHAR(255),
    bio TEXT,
    role ENUM('user','admin') DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ROOMS
CREATE TABLE IF NOT EXISTS rooms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE,
    display_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- MESSAGES
CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room VARCHAR(100),
    username VARCHAR(50),
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- POSTS
CREATE TABLE IF NOT EXISTS posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    book_title VARCHAR(200),
    author VARCHAR(200),
    review TEXT,
    rating INT,
    cover_image VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_user_book (username, book_title, author)
);

-- ADMIN DEFAULT
INSERT IGNORE INTO users (username, password, role)
VALUES ('admin', 'admin', 'admin');

-- DEFAULT ROOMS
INSERT IGNORE INTO rooms (name, display_name) VALUES
('fantasy', '🧙 Fantasy Books'),
('scifi', '🚀 Sci-Fi Books'),
('horror', '👻 Horror Books'),
('romance', '💝 Romance Books'),
('thriller', '🔎 Thriller & Mystery'),
('classics', '📜 Classic'),
('ya', '🧒 Young Adult'),
('darkfantasy', '🧙‍♂️ Dark Fantasy'),
('history', '🌍 Historical Fiction'),
('comedy', '😂 Comedy'),
('philosophy', '🧠 Philosophy'),
('manga', '📚 Manga & Light Novel');