-- GodSpeed Bot Database Schema
-- Оптимизировано для до 100,000 пользователей

-- Users table - основная таблица пользователей
CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT UNSIGNED NOT NULL UNIQUE,
    username VARCHAR(255) NULL,
    first_name VARCHAR(255) NULL,
    last_name VARCHAR(255) NULL,
    language_code VARCHAR(10) NOT NULL DEFAULT 'en',
    religion VARCHAR(50) NULL,
    dream TEXT,
    points INT UNSIGNED DEFAULT 0,
    state VARCHAR(100) NULL DEFAULT 'language_selection',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    INDEX idx_telegram_id (telegram_id),
    INDEX idx_language (language_code),
    INDEX idx_state (state),
    INDEX idx_last_activity (last_activity),
    INDEX idx_points (points)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User goals - выбранные цели пользователя
CREATE TABLE IF NOT EXISTS user_goals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    goal VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_goal (goal),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User temporary goals - временные выбранные цели для множественного выбора
CREATE TABLE IF NOT EXISTS user_temp_goals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    goal VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_goal (goal),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Challenges - челленджи
CREATE TABLE IF NOT EXISTS challenges (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    religion VARCHAR(50) NULL,
    goals JSON NULL, -- массив целей, для которых подходит челлендж
    duration_days INT UNSIGNED NOT NULL DEFAULT 7,
    is_premium BOOLEAN DEFAULT FALSE,
    points_reward INT UNSIGNED DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_religion (religion),
    INDEX idx_is_premium (is_premium),
    INDEX idx_is_active (is_active),
    INDEX idx_duration (duration_days)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Challenge tasks - задания в челленджах
CREATE TABLE IF NOT EXISTS challenge_tasks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    challenge_id BIGINT UNSIGNED NOT NULL,
    day_number INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    task_type ENUM('meditation', 'reading', 'practice', 'reflection', 'action') NOT NULL,
    duration_minutes INT UNSIGNED NULL,
    points INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_challenge_day (challenge_id, day_number),
    INDEX idx_task_type (task_type),
    FOREIGN KEY (challenge_id) REFERENCES challenges(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User challenges - участие пользователей в челленджах
CREATE TABLE IF NOT EXISTS user_challenges (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    challenge_id BIGINT UNSIGNED NOT NULL,
    status ENUM('active', 'completed', 'abandoned') DEFAULT 'active',
    current_day INT UNSIGNED DEFAULT 1,
    start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    end_date TIMESTAMP NULL,
    completed_tasks JSON NULL, -- массив выполненных заданий
    total_points_earned INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_status (user_id, status),
    INDEX idx_challenge_status (challenge_id, status),
    INDEX idx_start_date (start_date),
    UNIQUE KEY unique_user_challenge (user_id, challenge_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (challenge_id) REFERENCES challenges(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User challenge progress - прогресс по заданиям
CREATE TABLE IF NOT EXISTS user_challenge_progress (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    challenge_id BIGINT UNSIGNED NOT NULL,
    task_id BIGINT UNSIGNED NOT NULL,
    day_number INT UNSIGNED NOT NULL,
    status ENUM('pending', 'completed', 'skipped') DEFAULT 'pending',
    completed_at TIMESTAMP NULL,
    notes TEXT NULL,
    points_earned INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_challenge_day (user_id, challenge_id, day_number),
    INDEX idx_task_status (task_id, status),
    INDEX idx_completed_at (completed_at),
    UNIQUE KEY unique_user_task (user_id, task_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (challenge_id) REFERENCES challenges(id) ON DELETE CASCADE,
    FOREIGN KEY (task_id) REFERENCES challenge_tasks(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Challenge circles - групповые челленджи
CREATE TABLE IF NOT EXISTS challenge_circles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    challenge_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    max_participants INT UNSIGNED DEFAULT 10,
    current_participants INT UNSIGNED DEFAULT 0,
    start_date TIMESTAMP NULL,
    end_date TIMESTAMP NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_challenge_active (challenge_id, is_active),
    INDEX idx_start_date (start_date),
    FOREIGN KEY (challenge_id) REFERENCES challenges(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Circle participants - участники групповых челленджей
CREATE TABLE IF NOT EXISTS circle_participants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    circle_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_leader BOOLEAN DEFAULT FALSE,
    INDEX idx_circle_user (circle_id, user_id),
    UNIQUE KEY unique_circle_user (circle_id, user_id),
    FOREIGN KEY (circle_id) REFERENCES challenge_circles(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User sessions - для кэширования состояния пользователя
CREATE TABLE IF NOT EXISTS user_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    session_data JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_expires_at (expires_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Messages log - для аналитики и отладки
CREATE TABLE IF NOT EXISTS messages_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    message_type ENUM('text', 'callback', 'command', 'photo', 'voice') NOT NULL,
    content TEXT NULL,
    callback_data VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_message_type (message_type),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User preferences - настройки пользователя
CREATE TABLE IF NOT EXISTS user_preferences (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL UNIQUE,
    notification_enabled BOOLEAN DEFAULT TRUE,
    daily_reminder_time TIME DEFAULT '09:00:00',
    timezone VARCHAR(50) DEFAULT 'UTC',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Delayed messages - для отложенной отправки сообщений
CREATE TABLE IF NOT EXISTS delayed_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT NOT NULL,
    message_text TEXT NOT NULL,
    message_buttons JSON NULL,
    send_at TIMESTAMP NOT NULL,
    sent BOOLEAN DEFAULT FALSE,
    sent_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_send_at (send_at),
    INDEX idx_sent (sent),
    INDEX idx_chat_id (chat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rate limiting - для защиты от спама
CREATE TABLE IF NOT EXISTS rate_limits (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    count INT UNSIGNED DEFAULT 1,
    window_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_action (user_id, action_type),
    INDEX idx_window_start (window_start),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Statistics table - для мониторинга
CREATE TABLE IF NOT EXISTS bot_statistics (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    total_users INT UNSIGNED DEFAULT 0,
    active_users INT UNSIGNED DEFAULT 0,
    new_users INT UNSIGNED DEFAULT 0,
    total_messages INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_date (date),
    INDEX idx_date (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- История диалогов пользователя с ИИ
CREATE TABLE IF NOT EXISTS ai_dialog (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    telegram_id BIGINT UNSIGNED NOT NULL,
    challenge_id INT UNSIGNED DEFAULT NULL,
    day_number INT UNSIGNED DEFAULT NULL,
    user_message TEXT NOT NULL,
    bot_response TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_telegram (telegram_id),
    INDEX idx_challenge_day (challenge_id, day_number),
    INDEX idx_created (created_at)
);

-- Оценка пользователя после ответа ИИ
CREATE TABLE IF NOT EXISTS ai_feedback (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    telegram_id BIGINT UNSIGNED NOT NULL,
    dialog_id BIGINT UNSIGNED NOT NULL,
    feedback ENUM('positive','neutral','negative') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_telegram (telegram_id),
    INDEX idx_dialog (dialog_id),
    INDEX idx_created (created_at)
);

-- Create indexes for better performance
CREATE INDEX idx_users_telegram_language ON users(telegram_id, language_code);
CREATE INDEX idx_users_state_language ON users(state, language_code);
CREATE INDEX idx_messages_user_type ON messages_log(user_id, message_type); 

-- Таблица: challenge_user_requests
CREATE TABLE IF NOT EXISTS challenge_user_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    challenge_id BIGINT UNSIGNED NOT NULL,
    day INT UNSIGNED NOT NULL,
    user_text TEXT NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_user_challenge_day (user_id, challenge_id, day),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Таблица: challenge_bot_responses
CREATE TABLE IF NOT EXISTS challenge_bot_responses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    challenge_id BIGINT UNSIGNED NOT NULL,
    day INT UNSIGNED NOT NULL,
    bot_text TEXT NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_user_challenge_day (user_id, challenge_id, day),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Таблица: challenge_user_feedback
CREATE TABLE IF NOT EXISTS challenge_user_feedback (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    challenge_id BIGINT UNSIGNED NOT NULL,
    day INT UNSIGNED NOT NULL,
    feedback VARCHAR(32) NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_user_challenge_day (user_id, challenge_id, day),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Все поля индексированы для быстрой работы с 100 000+ пользователей. 