-- Chat App Database Schema
-- Created for MySQL

-- Create database
CREATE DATABASE IF NOT EXISTS chatapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE chatapp_db;

-- Table: users
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    google_id VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    avatar_url TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_online TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- Table: rooms
CREATE TABLE rooms (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- Table: messages
CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    room_id VARCHAR(50) NOT NULL,
    message TEXT,
    message_type ENUM('text', 'image') DEFAULT 'text',
    image_url TEXT NULL,
    image_name VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_at TIMESTAMP NULL,
    is_deleted BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
    INDEX idx_room_created (room_id, created_at),
    INDEX idx_user_created (user_id, created_at)
);

-- Table: user_rooms (track which users are in which rooms)
CREATE TABLE user_rooms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    room_id VARCHAR(50) NOT NULL,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_room (user_id, room_id)
);

-- Insert default rooms
INSERT INTO rooms (id, name, description) VALUES 
('general', 'General', 'Phòng chat chung cho mọi người'),
('random', 'Random', 'Phòng chat ngẫu nhiên'),
('tech', 'Technology', 'Thảo luận về công nghệ');

-- Create indexes for better performance
CREATE INDEX idx_messages_room_time ON messages(room_id, created_at DESC);
CREATE INDEX idx_users_google_id ON users(google_id);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_user_rooms_active ON user_rooms(user_id, is_active);

-- Create view for recent messages with user info
CREATE VIEW recent_messages AS
SELECT 
    m.id,
    m.message,
    m.message_type,
    m.image_url,
    m.image_name,
    m.room_id,
    m.created_at,
    u.id as user_id,
    u.name as username,
    u.email,
    u.avatar_url
FROM messages m
JOIN users u ON m.user_id = u.id
WHERE m.is_deleted = FALSE
ORDER BY m.created_at DESC;