-- Создание базы данных
CREATE DATABASE chat_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Таблица пользователей
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    unique_code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    avatar VARCHAR(255),
    is_online BOOLEAN DEFAULT FALSE,
    last_seen DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица чатов (диалоги)
CREATE TABLE chats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    unique_code VARCHAR(50) UNIQUE NOT NULL,
    user1_id INT NOT NULL,
    user2_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user1_id) REFERENCES users(id),
    FOREIGN KEY (user2_id) REFERENCES users(id)
);

-- Таблица групп
CREATE TABLE groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    unique_code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(150) NOT NULL,
    description TEXT,
    avatar VARCHAR(255),
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Таблица участников групп
CREATE TABLE group_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT NOT NULL,
    user_id INT NOT NULL,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (group_id) REFERENCES groups(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY unique_member (group_id, user_id)
);

-- Таблица сообщений
CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id INT,
    group_id INT,
    user_id INT NOT NULL,
    message_text TEXT,
    file_path VARCHAR(255),
    file_name VARCHAR(255),
    file_size INT,
    message_type ENUM('text', 'image', 'file', 'system') DEFAULT 'text',
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (chat_id) REFERENCES chats(id),
    FOREIGN KEY (group_id) REFERENCES groups(id),
    CHECK (chat_id IS NOT NULL OR group_id IS NOT NULL)
);

-- Индексы для поиска
CREATE FULLTEXT INDEX idx_messages_text ON messages(message_text);
CREATE INDEX idx_messages_created ON messages(created_at);
CREATE INDEX idx_users_online ON users(is_online, last_seen);