-- sql/setup.sql

-- ON SHARED HOSTING: Import this file into your ALREADY CREATED database.
-- CREATE DATABASE IF NOT EXISTS `chat_system`;
-- USE `chat_system`;

-- Table: users (Optional, but good for tracking sessions if needed, or we just rely on session_ids in rooms)
-- For this simple requirements, we might not strictly need a persistent users table for guests, 
-- but let's keep it simple and just store session identifiers in the room participants.

-- Table: admin
CREATE TABLE IF NOT EXISTS `admin` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table: rooms
CREATE TABLE IF NOT EXISTS `rooms` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `room_code` VARCHAR(10) NOT NULL UNIQUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `participant_count` INT DEFAULT 0
);

-- Table: room_users (To track who is in which room to enforce max 2 limit)
CREATE TABLE IF NOT EXISTS `room_users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `room_id` INT NOT NULL,
    `session_id` VARCHAR(255) NOT NULL,
    `joined_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`room_id`) REFERENCES `rooms`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `room_user_unique` (`room_id`, `session_id`)
);

-- Table: messages
CREATE TABLE IF NOT EXISTS `messages` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `room_id` INT NOT NULL,
    `sender_session_id` VARCHAR(255) NOT NULL, 
    `sender_alias` VARCHAR(50) DEFAULT 'User', -- Or 'Admin'
    `message` TEXT NOT NULL,
    `file_type` ENUM('text','image','video') DEFAULT 'text',
    `file_url` VARCHAR(255) DEFAULT NULL,
    `file_name` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`room_id`) REFERENCES `rooms`(`id`) ON DELETE CASCADE
);

-- Insert default admin (username: admin, password: password123)
-- Hash generated via password_hash('password123', PASSWORD_BCRYPT)
INSERT IGNORE INTO `admin` (`username`, `password`) VALUES 
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');
