-- Minimal AI Chat Enhancement for PollinationsAI Provider
-- This fixes the existing AI chat system to support PollinationsAI provider
-- Run this if you get errors about missing columns

USE ashlya_academy;

-- Add missing columns to existing ai_chat_analytics table if they don't exist
ALTER TABLE `ai_chat_analytics` 
ADD COLUMN IF NOT EXISTS `mode_used` varchar(50) DEFAULT 'general' AFTER `error_message`,
ADD COLUMN IF NOT EXISTS `tokens_consumed` int(11) DEFAULT 0 AFTER `mode_used`,
ADD COLUMN IF NOT EXISTS `response_time` decimal(8,3) DEFAULT 0.000 AFTER `tokens_consumed`;

-- Add indexes for the new columns
CREATE INDEX IF NOT EXISTS idx_mode_used ON ai_chat_analytics (mode_used);
CREATE INDEX IF NOT EXISTS idx_analytics_user_date ON ai_chat_analytics (user_id, created_at, mode_used);

-- Create/Update the user chat statistics view with correct column names
DROP VIEW IF EXISTS user_chat_statistics;
CREATE VIEW user_chat_statistics AS
SELECT 
    u.id as user_id,
    u.fullname as username,
    u.email as user_email,
    COUNT(DISTINCT s.id) as total_sessions,
    COUNT(DISTINCT CASE WHEN s.is_active = 1 THEN s.id END) as active_sessions,
    COUNT(m.id) as total_messages,
    COUNT(CASE WHEN m.message_type = 'user' THEN 1 END) as user_messages_sent,
    COUNT(CASE WHEN m.message_type = 'assistant' THEN 1 END) as ai_responses_received,
    COUNT(f.id) as favorite_messages,
    COALESCE(SUM(a.tokens_consumed), 0) as total_tokens_used,
    MAX(s.updated_at) as last_chat_activity
FROM user u
LEFT JOIN ai_chat_sessions s ON u.id = s.user_id
LEFT JOIN ai_chat_messages m ON s.id = m.session_id
LEFT JOIN ai_chat_favorites f ON u.id = f.user_id
LEFT JOIN ai_chat_analytics a ON u.id = a.user_id
GROUP BY u.id, u.fullname, u.email;

-- Update procedure with correct column names
DROP PROCEDURE IF EXISTS GetUserChatSummary;

DELIMITER //
CREATE PROCEDURE GetUserChatSummary(IN p_user_id INT)
BEGIN
    SELECT 
        user_id,
        username,
        user_email,
        total_sessions,
        active_sessions,
        total_messages,
        user_messages_sent,
        ai_responses_received,
        favorite_messages,
        total_tokens_used,
        last_chat_activity
    FROM user_chat_statistics 
    WHERE user_id = p_user_id;
END //
DELIMITER ;

-- Success message
SELECT 
    'AI Chat System PollinationsAI Setup Complete!' as status,
    'Added mode_used, tokens_consumed, response_time columns' as changes,
    'Updated views and procedures with correct user table columns' as fixes,
    NOW() as completed_at;
