-- AI System Tables for Mutalex Academy LMS
-- Add comprehensive AI usage tracking, analytics, and management

-- AI usage logs table for tracking all AI API calls
CREATE TABLE IF NOT EXISTS ai_usage_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL, -- NULL for system-level AI calls
    feature VARCHAR(50) NOT NULL, -- content_analysis, question_generation, etc.
    tokens_used INT DEFAULT 0,
    cost DECIMAL(8,4) DEFAULT 0.0000, -- cost in USD
    metadata JSON NULL, -- additional data like content length, parameters
    response_time_ms INT NULL, -- API response time
    success BOOLEAN DEFAULT TRUE,
    error_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user_feature (user_id, feature),
    INDEX idx_feature_date (feature, created_at),
    INDEX idx_created_at (created_at),
    INDEX idx_cost (cost)
);

-- AI settings table for configurable AI parameters
CREATE TABLE IF NOT EXISTS ai_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT NULL,
    setting_type ENUM('string', 'integer', 'float', 'boolean', 'json') DEFAULT 'string',
    description TEXT NULL,
    is_system BOOLEAN DEFAULT FALSE, -- system settings vs user configurable
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_key (setting_key),
    INDEX idx_system (is_system)
);

-- AI analytics table for daily/monthly usage statistics
CREATE TABLE IF NOT EXISTS ai_analytics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    feature VARCHAR(50) NOT NULL,
    total_requests INT DEFAULT 0,
    successful_requests INT DEFAULT 0,
    failed_requests INT DEFAULT 0,
    total_tokens INT DEFAULT 0,
    total_cost DECIMAL(10,4) DEFAULT 0.0000,
    average_response_time_ms INT DEFAULT 0,
    unique_users INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_analytics (date, feature),
    INDEX idx_date (date),
    INDEX idx_feature (feature),
    INDEX idx_date_feature (date, feature)
);

-- AI rate limiting table
CREATE TABLE IF NOT EXISTS ai_rate_limits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    feature VARCHAR(50) NOT NULL,
    requests_count INT DEFAULT 0,
    window_start TIMESTAMP NOT NULL,
    window_type ENUM('hour', 'day', 'month') DEFAULT 'hour',
    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,
    UNIQUE KEY unique_limit (user_id, feature, window_start, window_type),
    INDEX idx_user_feature (user_id, feature),
    INDEX idx_window (window_start, window_type)
);

-- AI content cache table for performance optimization
CREATE TABLE IF NOT EXISTS ai_content_cache (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cache_key VARCHAR(255) UNIQUE NOT NULL,
    feature VARCHAR(50) NOT NULL,
    input_hash VARCHAR(128) NOT NULL, -- hash of input parameters
    ai_response JSON NOT NULL,
    tokens_used INT DEFAULT 0,
    cost DECIMAL(8,4) DEFAULT 0.0000,
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_accessed TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    access_count INT DEFAULT 1,
    INDEX idx_key (cache_key),
    INDEX idx_feature (feature),
    INDEX idx_expires (expires_at),
    INDEX idx_accessed (last_accessed),
    INDEX idx_input_hash (input_hash)
);

-- AI feedback table for collecting user feedback on AI features
CREATE TABLE IF NOT EXISTS ai_feedback (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    feature VARCHAR(50) NOT NULL,
    rating TINYINT NULL, -- 1-5 rating
    feedback_text TEXT NULL,
    helpful BOOLEAN NULL,
    accuracy_rating TINYINT NULL, -- 1-5 accuracy rating
    speed_rating TINYINT NULL, -- 1-5 speed rating
    metadata JSON NULL, -- additional context
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_feature (feature),
    INDEX idx_rating (rating),
    INDEX idx_created_at (created_at)
);

-- Insert default AI settings
INSERT INTO ai_settings (setting_key, setting_value, setting_type, description, is_system) VALUES
('openai_api_key', '', 'string', 'OpenAI API Key for GPT-4o access', FALSE),
('openai_model', 'gpt-4o', 'string', 'OpenAI model to use for AI features', TRUE),
('max_tokens', '4000', 'integer', 'Maximum tokens per AI request', TRUE),
('temperature', '0.7', 'float', 'AI creativity level (0-2)', TRUE),
('daily_cost_limit', '50.00', 'float', 'Maximum daily AI cost in USD', FALSE),
('monthly_cost_limit', '1000.00', 'float', 'Maximum monthly AI cost in USD', FALSE),
('requests_per_hour', '100', 'integer', 'Maximum AI requests per hour per user', FALSE),
('content_analysis_enabled', '1', 'boolean', 'Enable AI content analysis feature', FALSE),
('question_generation_enabled', '1', 'boolean', 'Enable AI question generation feature', FALSE),
('essay_evaluation_enabled', '1', 'boolean', 'Enable AI essay evaluation feature', FALSE),
('content_summarization_enabled', '1', 'boolean', 'Enable AI content summarization feature', FALSE),
('learning_path_enabled', '1', 'boolean', 'Enable AI learning path recommendations', FALSE),
('performance_analysis_enabled', '1', 'boolean', 'Enable AI performance analysis feature', FALSE),
('accessibility_enabled', '1', 'boolean', 'Enable AI accessibility analysis feature', FALSE),
('translation_enabled', '1', 'boolean', 'Enable AI translation feature', FALSE),
('ai_cache_enabled', '1', 'boolean', 'Enable AI response caching', TRUE),
('ai_cache_ttl', '3600', 'integer', 'AI cache time-to-live in seconds', TRUE),
('ai_log_requests', '1', 'boolean', 'Log all AI API requests', TRUE),
('ai_log_costs', '1', 'boolean', 'Track AI usage costs', TRUE),
('ai_max_retries', '3', 'integer', 'Maximum retries for failed AI requests', TRUE),
('ai_retry_delay', '1000', 'integer', 'Delay between retries in milliseconds', TRUE);

-- Create indexes for better performance
CREATE INDEX idx_ai_usage_logs_user_date ON ai_usage_logs(user_id, DATE(created_at));
CREATE INDEX idx_ai_usage_logs_feature_success ON ai_usage_logs(feature, success);
CREATE INDEX idx_ai_rate_limits_window_expiry ON ai_rate_limits(window_start, window_type);
CREATE INDEX idx_ai_content_cache_expiry_access ON ai_content_cache(expires_at, last_accessed);
CREATE INDEX idx_ai_analytics_date_range ON ai_analytics(date, feature, total_cost);

-- Create a view for AI usage summary
CREATE OR REPLACE VIEW ai_usage_summary AS
SELECT
    DATE(created_at) as date,
    feature,
    COUNT(*) as total_requests,
    SUM(CASE WHEN success = 1 THEN 1 ELSE 0 END) as successful_requests,
    SUM(CASE WHEN success = 0 THEN 1 ELSE 0 END) as failed_requests,
    SUM(tokens_used) as total_tokens,
    SUM(cost) as total_cost,
    AVG(response_time_ms) as avg_response_time,
    COUNT(DISTINCT user_id) as unique_users
FROM ai_usage_logs
GROUP BY DATE(created_at), feature;

-- Create a view for current AI rate limits
CREATE OR REPLACE VIEW current_ai_rate_limits AS
SELECT
    user_id,
    feature,
    SUM(requests_count) as current_requests,
    window_type,
    MAX(window_start) as latest_window
FROM ai_rate_limits
WHERE window_start >= CASE
    WHEN window_type = 'hour' THEN DATE_SUB(NOW(), INTERVAL 1 HOUR)
    WHEN window_type = 'day' THEN DATE_SUB(NOW(), INTERVAL 1 DAY)
    WHEN window_type = 'month' THEN DATE_SUB(NOW(), INTERVAL 1 MONTH)
END
GROUP BY user_id, feature, window_type;