SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS rooms(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) UNIQUE NOT NULL,is_default TINYINT(1) DEFAULT 0) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO rooms(id,name,is_default) VALUES (1,'global',1);
CREATE TABLE IF NOT EXISTS users(id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(100) UNIQUE,email VARCHAR(190) NULL,password_hash VARCHAR(255) NULL,role ENUM('guest','member','moderator','supermod','admin') DEFAULT 'member',status ENUM('online','busy','away','offline') DEFAULT 'online',is_invisible TINYINT(1) DEFAULT 0,location VARCHAR(10) NULL,announcement TEXT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,last_seen DATETIME NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS messages(id BIGINT AUTO_INCREMENT PRIMARY KEY,room_id INT NOT NULL,sender_name VARCHAR(100) NOT NULL,content TEXT NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX(room_id),CONSTRAINT messages_ibfk_1 FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS private_messages(id BIGINT AUTO_INCREMENT PRIMARY KEY,sender VARCHAR(100) NOT NULL,receiver VARCHAR(100) NOT NULL,content TEXT NOT NULL,is_read TINYINT(1) DEFAULT 0,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX(receiver), INDEX(sender)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS guest_sessions(session_id VARCHAR(128) PRIMARY KEY,pseudo VARCHAR(100) NOT NULL,announcement TEXT NULL,location VARCHAR(10) NULL,last_seen DATETIME NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS message_logs(id BIGINT AUTO_INCREMENT PRIMARY KEY,type ENUM('public','private') NOT NULL,room VARCHAR(100) NULL,sender VARCHAR(100) NOT NULL,receiver VARCHAR(100) NULL,sender_ip VARCHAR(64) NULL,content TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS settings(id TINYINT PRIMARY KEY,site_name VARCHAR(190) NULL,site_description VARCHAR(255) NULL,https_enabled TINYINT(1) DEFAULT 0,flood_limit INT DEFAULT 3,flood_window INT DEFAULT 10) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;