-- Создание базы данных для Avatar Bot
CREATE DATABASE IF NOT EXISTS avatar_bot CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE avatar_bot;

-- Таблица пользователей
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    telegram_id BIGINT UNIQUE NOT NULL,
    username VARCHAR(255),
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    coins INT DEFAULT 0,
    user_type ENUM('beginner', 'experienced') DEFAULT NULL,
    bot_branch ENUM('main', 'webinar') DEFAULT 'main',
    is_active BOOLEAN DEFAULT TRUE,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_telegram_id (telegram_id),
    INDEX idx_user_type (user_type),
    INDEX idx_bot_branch (bot_branch),
    INDEX idx_is_active (is_active),
    INDEX idx_last_activity (last_activity)
) ENGINE=InnoDB;

-- Таблица сообщений (для истории и аналитики)
CREATE TABLE messages (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    message_type ENUM('incoming', 'outgoing') NOT NULL,
    content TEXT,
    telegram_message_id BIGINT,
    coins_awarded INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_message_type (message_type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

-- Таблица рассылок
CREATE TABLE broadcasts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    message_text TEXT NOT NULL,
    image_path VARCHAR(500),
    target_type ENUM('all', 'beginner', 'experienced') DEFAULT 'all',
    target_branch ENUM('all', 'main', 'webinar') DEFAULT 'all',
    status ENUM('draft', 'in_progress', 'completed', 'failed') DEFAULT 'draft',
    total_users INT DEFAULT 0,
    sent_count INT DEFAULT 0,
    failed_count INT DEFAULT 0,
    created_by VARCHAR(255),
    scheduled_at TIMESTAMP NULL,
    started_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_status (status),
    INDEX idx_target_type (target_type),
    INDEX idx_target_branch (target_branch),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

-- Таблица логов рассылок
CREATE TABLE broadcast_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    broadcast_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    status ENUM('sent', 'failed', 'blocked') NOT NULL,
    error_message TEXT,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (broadcast_id) REFERENCES broadcasts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_broadcast_id (broadcast_id),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- Таблица активности пользователей
CREATE TABLE user_activity (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    action_type VARCHAR(100) NOT NULL,
    action_data JSON,
    coins_awarded INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_action_type (action_type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

-- Таблица состояний пользователей (для диалогов)
CREATE TABLE user_states (
    user_id BIGINT PRIMARY KEY,
    current_state VARCHAR(100) DEFAULT 'start',
    state_data JSON,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Таблица кэша (для оптимизации)
CREATE TABLE cache (
    cache_key VARCHAR(255) PRIMARY KEY,
    cache_value LONGTEXT,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB;

-- Создание администратора по умолчанию (можно удалить после первого входа)
INSERT INTO users (telegram_id, username, first_name, coins, user_type, bot_branch) 
VALUES (123456789, 'admin', 'Admin', 1000000, 'experienced', 'main')
ON DUPLICATE KEY UPDATE coins = coins;
