-- Дополнительные таблицы для аналитики

-- Таблица для отслеживания ключевых этапов пользователей
CREATE TABLE IF NOT EXISTS user_milestones (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    milestone_type VARCHAR(100) NOT NULL,
    milestone_data JSON,
    reached_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_milestone_type (milestone_type),
    INDEX idx_reached_at (reached_at),
    UNIQUE KEY unique_user_milestone (user_id, milestone_type)
) ENGINE=InnoDB;

-- Таблица для отслеживания расходов на OpenAI токены
CREATE TABLE IF NOT EXISTS openai_usage (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    request_type VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    prompt_tokens INT DEFAULT 0,
    completion_tokens INT DEFAULT 0,
    total_tokens INT DEFAULT 0,
    estimated_cost DECIMAL(10,6) DEFAULT 0,
    request_data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_request_type (request_type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

-- Таблица для отслеживания активности пользователей (дополненная версия)
CREATE TABLE IF NOT EXISTS user_journey (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    step_name VARCHAR(100) NOT NULL,
    step_data JSON,
    completed BOOLEAN DEFAULT FALSE,
    completed_at TIMESTAMP NULL,
    last_interaction TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_step_name (step_name),
    INDEX idx_completed (completed),
    INDEX idx_completed_at (completed_at),
    UNIQUE KEY unique_user_step (user_id, step_name)
) ENGINE=InnoDB;

-- Добавляем основные этапы путешествия пользователя
INSERT IGNORE INTO user_journey (user_id, step_name, completed) 
SELECT id, 'start', FALSE FROM users;

INSERT IGNORE INTO user_journey (user_id, step_name, completed) 
SELECT id, 'income_entered', FALSE FROM users;

INSERT IGNORE INTO user_journey (user_id, step_name, completed) 
SELECT id, 'goal_set', FALSE FROM users;

INSERT IGNORE INTO user_journey (user_id, step_name, completed) 
SELECT id, 'percentage_set', FALSE FROM users;

INSERT IGNORE INTO user_journey (user_id, step_name, completed) 
SELECT id, 'profitability_calculated', FALSE FROM users;

INSERT IGNORE INTO user_journey (user_id, step_name, completed) 
SELECT id, 'example_analyzed', FALSE FROM users;

INSERT IGNORE INTO user_journey (user_id, step_name, completed) 
SELECT id, 'own_example_sent', FALSE FROM users;

INSERT IGNORE INTO user_journey (user_id, step_name, completed) 
SELECT id, 'lesson_reached', FALSE FROM users;

INSERT IGNORE INTO user_journey (user_id, step_name, completed) 
SELECT id, 'neytiri_jake_together', FALSE FROM users;
