<?php
/**
 * ============================================================================
 * AIAssistant.php - Enhanced AI Assistant with Advanced Intelligence
 * ============================================================================
 * Features:
 * - Session management with context retention
 * - Multi-source knowledge integration (database, knowledge base, OpenAI)
 * - Self-learning capabilities
 * - Time-aware greetings and responses
 * - Contextual understanding
 * - Pattern recognition and improvement
 */

namespace App;

class AIAssistant {
    private $db;
    private $openaiApiKey;
    private $model;
    private $maxTokens = 1000;
    private $temperature = 0.7;
    private $timeout = 30;
    private $userId;
    
    private static $knowledgeBase = null;
    private static $learnedPatterns = [];
    
    public function __construct() {
        $this->db = Database::getInstance();
        $this->openaiApiKey = $_ENV['OPENAI_API_KEY'] ?? '';
        $this->model = $_ENV['OPENAI_MODEL'] ?? 'gpt-3.5-turbo';
        
        // Load learned patterns from database
        $this->loadLearnedPatterns();
    }
    
    /**
     * Main method to get AI response with enhanced intelligence
     */
    public function getResponse($message, $userId, $context = [], $sessionId = null) {
        try {
            $this->userId = $userId;
            
            // Validate input
            if (empty($message)) {
                throw new \Exception('Message cannot be empty');
            }
            
            if (strlen($message) > 2000) {
                throw new \Exception('Message too long. Maximum 2000 characters.');
            }
            
            // Generate or use existing session ID
            if (empty($sessionId)) {
                $sessionId = $this->generateSessionId($userId);
            }
            
            // Detect language
            $language = $this->detectLanguage($message);
            
            // Get time-aware greeting if applicable
            $timeGreeting = $this->getTimeBasedGreeting($language);
            
            // Get conversation history for context
            $conversationHistory = $this->getSessionHistory($sessionId, 5);
            
            // Enhance context with user profile data
            $enrichedContext = $this->enrichContext($userId, $context);
            
            // Check for special patterns (greetings, thanks, etc.)
            $specialResponse = $this->handleSpecialPatterns($message, $language, $timeGreeting);
            if ($specialResponse) {
                $this->saveConversation($userId, $sessionId, $message, $specialResponse, $language, 'pattern_match');
                return [
                    'success' => true,
                    'response' => $specialResponse,
                    'language' => $language,
                    'source' => 'pattern_match',
                    'session_id' => $sessionId,
                    'timestamp' => date('Y-m-d H:i:s')
                ];
            }
            
            // Try multi-source knowledge retrieval
            $knowledgeResponse = $this->getMultiSourceKnowledge($message, $language, $enrichedContext);
            
            if ($knowledgeResponse) {
                $response = $knowledgeResponse;
                $source = 'knowledge_base';
            } elseif ($this->isOpenAIConfigured()) {
                // Try OpenAI with enhanced prompt
                try {
                    error_log("Attempting OpenAI request for user: $userId");
                    
                    $response = $this->getOpenAIResponse(
                        $message, 
                        $language, 
                        $enrichedContext, 
                        $conversationHistory
                    );
                    
                    error_log("OpenAI response received successfully");
                    $source = 'openai';
                    
                    // Learn from successful OpenAI responses
                    $this->learnFromResponse($message, $response, $language);
                    
                } catch (\Exception $e) {
                    error_log('OpenAI Error: ' . $e->getMessage());
                    
                    // Fallback to enhanced rule-based
                    $response = $this->getEnhancedRuleBasedResponse($message, $language, $enrichedContext, $conversationHistory);
                    $source = 'enhanced_rules';
                }
            } else {
                // Enhanced rule-based system
                $response = $this->getEnhancedRuleBasedResponse($message, $language, $enrichedContext, $conversationHistory);
                $source = 'enhanced_rules';
            }
            
            // Save conversation
            $this->saveConversation($userId, $sessionId, $message, $response, $language, $source);
            
            // Update user interaction statistics
            $this->updateUserStats($userId, $language);
            
            return [
                'success' => true,
                'response' => $response,
                'language' => $language,
                'source' => $source,
                'session_id' => $sessionId,
                'timestamp' => date('Y-m-d H:i:s'),
                'context_used' => !empty($enrichedContext)
            ];
            
        } catch (\Exception $e) {
            error_log('AI Assistant Error: ' . $e->getMessage());
            
            return [
                'success' => false,
                'message' => $e->getMessage(),
                'response' => $this->getErrorResponse($language ?? 'en'),
                'language' => $language ?? 'en',
                'source' => 'error',
                'session_id' => $sessionId ?? null,
                'timestamp' => date('Y-m-d H:i:s')
            ];
        }
    }
    
    /**
     * Get time-based greeting
     */
    private function getTimeBasedGreeting($language = 'en') {
        $hour = date('G');
        
        if ($language === 'lusoga') {
            if ($hour >= 5 && $hour < 12) {
                return "Wasuze otya nno!";
            } elseif ($hour >= 12 && $hour < 17) {
                return "Osiibiile otya!";
            } elseif ($hour >= 17 && $hour < 22) {
                return "Oiwiire otya!";
            } else {
                return "Osula otya!";
            }
        } else {
            if ($hour >= 5 && $hour < 12) {
                return "Good morning!";
            } elseif ($hour >= 12 && $hour < 17) {
                return "Good afternoon!";
            } elseif ($hour >= 17 && $hour < 22) {
                return "Good evening!";
            } else {
                return "Hello!";
            }
        }
    }
    
    /**
     * Handle special patterns (greetings, thanks, etc.)
     */
    private function handleSpecialPatterns($message, $language, $timeGreeting) {
        $messageLower = mb_strtolower(trim($message), 'UTF-8');
        
        // Greeting patterns
        $greetingPatterns = [
            'en' => '/^(hi|hello|hey|good morning|good afternoon|good evening|greetings|howdy|sup|wassup)$/i',
            'lusoga' => '/^(wasuze|osiibiile|oiwiire|oli otya|suubi|hello|hi)$/i'
        ];
        
        if (preg_match($greetingPatterns[$language], $messageLower)) {
            return $this->getGreetingResponse($language, $timeGreeting);
        }
        
        // Thank you patterns
        $thanksPatterns = [
            'en' => '/^(thank you|thanks|thank|thx|appreciate|grateful)$/i',
            'lusoga' => '/^(webale|mwebale|nsonyiwa|thanks)$/i'
        ];
        
        if (preg_match($thanksPatterns[$language], $messageLower)) {
            return $this->getThanksResponse($language);
        }
        
        // How are you patterns
        $howAreYouPatterns = [
            'en' => '/how are you|how do you do|how is it going|whats up/i',
            'lusoga' => '/oli otya|biriwo|osiibiile otya/i'
        ];
        
        if (preg_match($howAreYouPatterns[$language], $messageLower)) {
            return $this->getHowAreYouResponse($language);
        }
        
        return null;
    }
    
    /**
     * Get greeting response
     */
    private function getGreetingResponse($language, $timeGreeting) {
        if ($language === 'lusoga') {
            return "{$timeGreeting} 👋\n\n" .
                   "Nze **AIM AI**, omuyambi wo mu by'obulimi owa Uganda. Ndi musanyufu okukuwulira!\n\n" .
                   "**Ndiyinza okukuyamba mu:**\n" .
                   "🌱 **Obulwadhe bw'ebirime** - Okuzuula n'okujjanjaba\n" .
                   "📅 **Obudde bw'okusimba** - Okumanyi essaawa entuufu\n" .
                   "💰 **Emiwendo** - Emiwendo egiriwo kaakati\n" .
                   "🌾 **Enkola ennungi** - Amagezi g'okulima\n" .
                   "🐔 **Ebisolo** - Okufuga enkoko, ente, embuzi\n\n" .
                   "Oyagala nkuyambe mu ki? 😊";
        } else {
            return "{$timeGreeting} 👋\n\n" .
                   "I'm **AIM AI**, your intelligent agricultural assistant for Uganda. Great to connect with you!\n\n" .
                   "**I can help you with:**\n" .
                   "🌱 **Crop Diseases** - Identify and treat problems\n" .
                   "📅 **Planting Schedules** - Best times to plant\n" .
                   "💰 **Market Prices** - Current prices and trends\n" .
                   "🌾 **Best Practices** - Modern farming techniques\n" .
                   "🐔 **Livestock Care** - Poultry, cattle, goats\n\n" .
                   "What would you like to know today? 😊";
        }
    }
    
    /**
     * Get thanks response
     */
    private function getThanksResponse($language) {
        if ($language === 'lusoga') {
            return "Webale nyo! 😊\n\n" .
                   "Ndi musanyufu okukuyamba. Ndi wano buli kaseera singa oyagala okumbuuza ekirungi ekirala!\n\n" .
                   "**Jjukira:**\n" .
                   "✅ Kebera ebirime byo buli kaseera\n" .
                   "✅ Kozesa AIMS marketplace okutunda\n" .
                   "✅ Teekawo alerts za miwendo\n" .
                   "✅ Yingira mu bibiina by'abalimi\n\n" .
                   "Kabisaana mu by'obulimi! 🌾🚜";
        } else {
            return "You're very welcome! 😊\n\n" .
                   "I'm happy to help. Feel free to ask me anything else about farming!\n\n" .
                   "**Quick Tips:**\n" .
                   "✅ Monitor your crops regularly\n" .
                   "✅ Use AIMS marketplace to sell\n" .
                   "✅ Set price alerts\n" .
                   "✅ Join farmer groups\n\n" .
                   "Happy farming! 🌾🚜";
        }
    }
    
    /**
     * Get how are you response
     */
    private function getHowAreYouResponse($language) {
        if ($language === 'lusoga') {
            return "Ndi bulungi nnyo, webale okubuuza! 😊\n\n" .
                   "Ndi mwetegefu okuyamba abalimi nga ggwe okufuna omukisa mu bulimi!\n\n" .
                   "**Oyinza okumbuuza ku:**\n" .
                   "- Obulwadhe bw'ebirime\n" .
                   "- Obudde bw'okusimba\n" .
                   "- Emiwendo mu bbabali\n" .
                   "- Okuddukanya omusimu\n\n" .
                   "Mbuuza kyonna! 🌾";
        } else {
            return "I'm doing great, thank you for asking! 😊\n\n" .
                   "I'm ready to help farmers like you succeed in agriculture!\n\n" .
                   "**You can ask me about:**\n" .
                   "- Crop diseases and pests\n" .
                   "- Planting schedules\n" .
                   "- Market prices\n" .
                   "- Farm management\n\n" .
                   "What would you like to know? 🌾";
        }
    }
    
    /**
     * Enrich context with user data from database
     */
    private function enrichContext($userId, $context = []) {
        try {
            // Get user profile data
            $sql = "SELECT u.*, up.*, r.region_name, d.district_name, s.subcounty_name
                    FROM users u
                    LEFT JOIN user_profiles up ON u.id = up.user_id
                    LEFT JOIN regions r ON u.region_id = r.id
                    LEFT JOIN districts d ON u.district_id = d.id
                    LEFT JOIN subcounties s ON u.subcounty_id = s.id
                    WHERE u.id = ?";
            
            $user = $this->db->fetchOne($sql, [$userId]);
            
            if ($user) {
                $context['user_name'] = $user['full_name'];
                $context['location'] = $user['district_name'] ?? 'Uganda';
                $context['region'] = $user['region_name'] ?? null;
                $context['subcounty'] = $user['subcounty_name'] ?? null;
                $context['farming_experience'] = $user['years_in_farming'] ?? 0;
                $context['farm_size'] = $user['farm_size_acres'] ?? 0;
                $context['farming_type'] = $user['farming_type'] ?? null;
                
                // Get crops from profile
                if (!empty($user['crops_grown'])) {
                    $context['current_crops'] = json_decode($user['crops_grown'], true);
                }
                
                // Get livestock from profile
                if (!empty($user['livestock_kept'])) {
                    $context['livestock'] = json_decode($user['livestock_kept'], true);
                }
            }
            
            // Get user's active enterprises
            $enterpriseSql = "SELECT e.* FROM enterprises e
                             INNER JOIN farms f ON e.farm_id = f.id
                             WHERE f.user_id = ? AND e.status != 'completed'
                             ORDER BY e.created_at DESC LIMIT 5";
            
            $enterprises = $this->db->fetchAll($enterpriseSql, [$userId]);
            if (!empty($enterprises)) {
                $context['enterprises'] = $enterprises;
            }
            
            // Get recent products
            $productSql = "SELECT * FROM products 
                          WHERE seller_id = ? AND status = 'available'
                          ORDER BY created_at DESC LIMIT 3";
            
            $products = $this->db->fetchAll($productSql, [$userId]);
            if (!empty($products)) {
                $context['products'] = $products;
            }
            
            return $context;
            
        } catch (\Exception $e) {
            error_log('Error enriching context: ' . $e->getMessage());
            return $context;
        }
    }
    
    /**
     * Get knowledge from multiple sources
     */
    private function getMultiSourceKnowledge($message, $language, $context) {
    $messageLower = mb_strtolower($message, 'UTF-8');
    
    // 1. Check learned patterns first
    $learnedResponse = $this->checkLearnedPatterns($messageLower, $language);
    if ($learnedResponse) {
        return $learnedResponse;
    }
    
    // 2. Detect if asking about specific crops/subjects
    $specificSubjects = ['cassava', 'tomato', 'maize', 'beans', 'cabbage', 
                        'coffee', 'potato', 'onion', 'chicken', 'poultry',
                        'muwogo', 'nyaanya', 'kasooli', 'bikooge', 'nakati', 'enkoko'];
    
    $hasSpecificSubject = false;
    foreach ($specificSubjects as $subject) {
        if (stripos($messageLower, $subject) !== false) {
            $hasSpecificSubject = true;
            break;
        }
    }
    
    // 3. If specific subject mentioned, check diseases/tips for THAT subject first
    if ($hasSpecificSubject) {
        // Try diseases first for specific crops
        $diseaseResponse = $this->searchCropDiseases($messageLower, $language);
        if ($diseaseResponse) {
            return $diseaseResponse;
        }
        
        // Then try agricultural tips for that specific crop
        $tipsResponse = $this->searchAgriculturalTips($messageLower, $language);
        if ($tipsResponse) {
            return $tipsResponse;
        }
    } else {
        // No specific subject - try general tips first
        $tipsResponse = $this->searchAgriculturalTips($messageLower, $language);
        if ($tipsResponse) {
            return $tipsResponse;
        }
        
        $diseaseResponse = $this->searchCropDiseases($messageLower, $language);
        if ($diseaseResponse) {
            return $diseaseResponse;
        }
    }
    
    // 4. Check farming schedules
    $scheduleResponse = $this->searchFarmingSchedules($messageLower, $language);
    if ($scheduleResponse) {
        return $scheduleResponse;
    }
    
    // 5. Check market prices
    $priceResponse = $this->searchMarketPrices($messageLower, $language, $context);
    if ($priceResponse) {
        return $priceResponse;
    }
    
    // 6. Check static knowledge base
    $kbResponse = $this->searchKnowledgeBase($messageLower, $language, $context);
    if ($kbResponse) {
        return $kbResponse;
    }
    
    return null;
}
    
    /**
     * Search agricultural tips database
     */
   private function searchAgriculturalTips($message, $language) {
    try {
        $keywords = $this->extractKeywords($message);
        
        if (empty($keywords)) {
            return null;
        }
        
        // FIRST: Try exact phrase match
        $exactSql = "SELECT * FROM agricultural_tips 
                     WHERE language = ? AND is_published = 1 
                     AND (title LIKE ? OR content LIKE ?)
                     LIMIT 1";
        
        $exactMatch = $this->db->fetchOne($exactSql, [
            $language, 
            "%{$message}%", 
            "%{$message}%"
        ]);
        
        if ($exactMatch) {
            return "**" . htmlspecialchars($exactMatch['title']) . "**\n\n" . 
                   $exactMatch['content'] . "\n\n" .
                   ($language === 'en' ? 
                    "💡 *Source: AIMS Agricultural Knowledge Base*" : 
                    "💡 *Ensibuko: AIMS Eby'obulimi*");
        }
        
        // SECOND: Try specific crop/subject match FIRST
        $specificSubjects = ['cassava', 'tomato', 'maize', 'beans', 'cabbage', 
                            'coffee', 'potato', 'onion', 'chicken', 'poultry',
                            'muwogo', 'nyaanya', 'kasooli', 'bikooge', 'nakati'];
        
        $foundSubject = null;
        foreach ($specificSubjects as $subject) {
            if (stripos($message, $subject) !== false) {
                $foundSubject = $subject;
                break;
            }
        }
        
        if ($foundSubject) {
            // Search ONLY for this specific subject
            $subjectSql = "SELECT * FROM agricultural_tips 
                          WHERE language = ? AND is_published = 1 
                          AND (title LIKE ? OR content LIKE ? OR crop_type LIKE ?)
                          LIMIT 1";
            
            $tip = $this->db->fetchOne($subjectSql, [
                $language,
                "%{$foundSubject}%",
                "%{$foundSubject}%",
                "%{$foundSubject}%"
            ]);
            
            if ($tip) {
                return "**" . htmlspecialchars($tip['title']) . "**\n\n" . 
                       $tip['content'] . "\n\n" .
                       ($language === 'en' ? 
                        "💡 *Source: AIMS Agricultural Knowledge Base*" : 
                        "💡 *Ensibuko: AIMS Eby'obulimi*");
            }
        }
        
        // THIRD: Keyword search as fallback (with better scoring)
        $conditions = [];
        $params = [$language];
        
        foreach ($keywords as $keyword) {
            $conditions[] = "(title LIKE ? OR crop_type LIKE ?)";
            $params[] = "%{$keyword}%";
            $params[] = "%{$keyword}%";
        }
        
        if (empty($conditions)) {
            return null;
        }
        
        $sql = "SELECT *, 
                CASE 
                    WHEN title LIKE ? THEN 1
                    WHEN crop_type LIKE ? THEN 2
                    ELSE 3
                END as match_priority
                FROM agricultural_tips 
                WHERE language = ? AND is_published = 1 
                AND (" . implode(' OR ', $conditions) . ")
                ORDER BY match_priority ASC, created_at DESC 
                LIMIT 1";
        
        // Add priority matching params
        array_unshift($params, "%{$keywords[0]}%", "%{$keywords[0]}%");
        
        $tip = $this->db->fetchOne($sql, $params);
        
        if ($tip) {
            return "**" . htmlspecialchars($tip['title']) . "**\n\n" . 
                   $tip['content'] . "\n\n" .
                   ($language === 'en' ? 
                    "💡 *Source: AIMS Agricultural Knowledge Base*" : 
                    "💡 *Ensibuko: AIMS Eby'obulimi*");
        }
        
        return null;
        
    } catch (\Exception $e) {
        error_log('Error searching agricultural tips: ' . $e->getMessage());
        return null;
    }
}

    
    /**
     * Search crop diseases database
     */
    private function searchCropDiseases($message, $language) {
    try {
        $keywords = $this->extractKeywords($message);
        
        if (empty($keywords)) {
            return null;
        }
        
        // Extract specific crop type
        $crops = ['tomato', 'cassava', 'maize', 'beans', 'cabbage', 'potato',
                  'nyaanya', 'muwogo', 'kasooli', 'bikooge', 'nakati'];
        
        $foundCrop = null;
        foreach ($crops as $crop) {
            if (stripos($message, $crop) !== false) {
                $foundCrop = $crop;
                break;
            }
        }
        
        // If we found a specific crop, ONLY search for that crop
        if ($foundCrop) {
            $sql = "SELECT * FROM crop_diseases 
                    WHERE language = ? AND is_published = 1 
                    AND crop_type LIKE ?
                    ORDER BY severity_level DESC 
                    LIMIT 1";
            
            $disease = $this->db->fetchOne($sql, [$language, "%{$foundCrop}%"]);
        } else {
            // No specific crop found, use general search
            $conditions = [];
            $params = [$language];
            
            foreach ($keywords as $keyword) {
                $conditions[] = "(disease_name LIKE ? OR symptoms LIKE ?)";
                $params[] = "%{$keyword}%";
                $params[] = "%{$keyword}%";
            }
            
            $sql = "SELECT * FROM crop_diseases 
                    WHERE language = ? AND is_published = 1 
                    AND (" . implode(' OR ', $conditions) . ")
                    ORDER BY severity_level DESC 
                    LIMIT 1";
            
            $disease = $this->db->fetchOne($sql, $params);
        }
        
        if ($disease) {
            $response = "**🦠 " . htmlspecialchars($disease['disease_name']) . "**\n";
            if ($disease['scientific_name']) {
                $response .= "*" . htmlspecialchars($disease['scientific_name']) . "*\n";
            }
            $response .= "\n**" . ($language === 'en' ? 'Crop:' : 'Ekirime:') . "** " . 
                        ucfirst($disease['crop_type']) . "\n";
            $response .= "**" . ($language === 'en' ? 'Severity:' : 'Obuzito:') . "** " . 
                        ucfirst($disease['severity_level']) . "\n\n";
            
            if (!empty($disease['symptoms'])) {
                $response .= "**" . ($language === 'en' ? 'Symptoms:' : 'Obubonero:') . "**\n" . 
                            $disease['symptoms'] . "\n\n";
            }
            
            if (!empty($disease['treatment'])) {
                $response .= "**" . ($language === 'en' ? 'Treatment:' : 'Okujjanjaba:') . "**\n" . 
                            $disease['treatment'] . "\n\n";
            }
            
            if (!empty($disease['prevention'])) {
                $response .= "**" . ($language === 'en' ? 'Prevention:' : 'Okuziyiza:') . "**\n" . 
                            $disease['prevention'] . "\n";
            }
            
            return $response;
        }
        
        return null;
        
    } catch (\Exception $e) {
        error_log('Error searching crop diseases: ' . $e->getMessage());
        return null;
    }
}

    
    /**
     * Search farming schedules database
     */
    private function searchFarmingSchedules($message, $language) {
        try {
            $keywords = $this->extractKeywords($message);
            
            if (empty($keywords)) {
                return null;
            }
            
            $conditions = [];
            $params = [$language];
            
            foreach ($keywords as $keyword) {
                $conditions[] = "(title LIKE ? OR crop_type LIKE ? OR activity_type LIKE ?)";
                $params[] = "%{$keyword}%";
                $params[] = "%{$keyword}%";
                $params[] = "%{$keyword}%";
            }
            
            $sql = "SELECT * FROM farming_schedules 
                    WHERE language = ? AND is_published = 1 
                    AND (" . implode(' OR ', $conditions) . ")
                    ORDER BY priority DESC, created_at DESC LIMIT 3";
            
            $schedules = $this->db->fetchAll($sql, $params);
            
            if (!empty($schedules)) {
                $response = "**📅 " . ($language === 'en' ? 
                                      'Farming Schedule' : 
                                      'Oludda lw\'Okulima') . "**\n\n";
                
                foreach ($schedules as $schedule) {
                    $response .= "**" . htmlspecialchars($schedule['title']) . "**\n";
                    $response .= "*" . ucfirst($schedule['crop_type']) . " - " . 
                                ucfirst(str_replace('_', ' ', $schedule['activity_type'])) . "*\n";
                    
                    if ($schedule['timing']) {
                        $response .= "⏰ " . htmlspecialchars($schedule['timing']) . "\n";
                    }
                    
                    $response .= $schedule['description'] . "\n\n";
                }
                
                return $response;
            }
            
            return null;
            
        } catch (\Exception $e) {
            error_log('Error searching farming schedules: ' . $e->getMessage());
            return null;
        }
    }
    
    /**
     * Search market prices
     */
    private function searchMarketPrices($message, $language, $context) {
        try {
            // Check if asking about prices
            $priceKeywords = ['price', 'cost', 'sell', 'market', 'miwendo', 'emiwendo', 'tunda'];
            $hasPrice = false;
            
            foreach ($priceKeywords as $keyword) {
                if (stripos($message, $keyword) !== false) {
                    $hasPrice = true;
                    break;
                }
            }
            
            if (!$hasPrice) {
                return null;
            }
            
            // Extract product type
            $products = ['maize', 'beans', 'cassava', 'coffee', 'sugarcane', 'sugar cane',
                        'kasooli', 'bikooge', 'muwogo', 'emmwanyi'];
            
            $productType = null;
            foreach ($products as $product) {
                if (stripos($message, $product) !== false) {
                    $productType = $product;
                    break;
                }
            }
            
            if (!$productType) {
                return null;
            }
            
            // Normalize product name
            $productMap = [
                'kasooli' => 'maize',
                'bikooge' => 'beans',
                'muwogo' => 'cassava',
                'emmwanyi' => 'coffee',
                'sugar cane' => 'sugarcane'
            ];
            
            $normalizedProduct = $productMap[$productType] ?? $productType;
            
            // Get recent prices
            $location = $context['location'] ?? 'Eastern Uganda';
            
            $sql = "SELECT * FROM market_prices 
                    WHERE product_type LIKE ? 
                    AND price_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                    ORDER BY price_date DESC, market_location LIKE ? DESC
                    LIMIT 5";
            
            $prices = $this->db->fetchAll($sql, ["%{$normalizedProduct}%", "%{$location}%"]);
            
            if (!empty($prices)) {
                $response = "**💰 " . ($language === 'en' ? 
                                       'Market Prices for ' . ucfirst($normalizedProduct) : 
                                       'Emiwendo gy\'e' . ucfirst($productType)) . "**\n\n";
                
                foreach ($prices as $price) {
                    $response .= "📍 **" . htmlspecialchars($price['market_location']) . "**\n";
                    $response .= "   UGX " . number_format($price['price'], 0) . " per " . 
                                htmlspecialchars($price['unit']) . "\n";
                    $response .= "   *" . date('M d, Y', strtotime($price['price_date'])) . "*\n\n";
                }
                
                // Add advice
                if ($language === 'en') {
                    $response .= "\n💡 **Tips:**\n";
                    $response .= "- Compare prices across markets\n";
                    $response .= "- Set price alerts in AIMS\n";
                    $response .= "- Consider transport costs\n";
                } else {
                    $response .= "\n💡 **Amagezi:**\n";
                    $response .= "- Geraageranya emiwendo mu bbabali\n";
                    $response .= "- Teekawo alerts za miwendo mu AIMS\n";
                    $response .= "- Lowooza ku musolo gw'entambula\n";
                }
                
                return $response;
            }
            
            return null;
            
        } catch (\Exception $e) {
            error_log('Error searching market prices: ' . $e->getMessage());
            return null;
        }
    }
    
    /**
     * Search static knowledge base
     */
    private function searchKnowledgeBase($message, $language, $context) {
        $knowledgeBase = $this->getKnowledgeBase($language);
        
        foreach ($knowledgeBase as $pattern => $response) {
            if (preg_match($pattern, $message)) {
                return $this->personalizeResponse($response, $context, $language);
            }
        }
        
        return null;
    }
    
    /**
     * Check learned patterns
     */
    private function checkLearnedPatterns($message, $language) {
        if (empty(self::$learnedPatterns[$language])) {
            return null;
        }
        
        foreach (self::$learnedPatterns[$language] as $pattern) {
            $similarity = 0;
            similar_text($message, $pattern['question'], $similarity);
            
            if ($similarity > 85) { // 85% similarity threshold
                $pattern['usage_count']++;
                $this->updatePatternUsage($pattern['id']);
                return $pattern['response'];
            }
        }
        
        return null;
    }
    
    /**
     * Extract keywords from message
     */
   private function extractKeywords($message) {
    // To Stop words INCLUDING generic farming terms that cause false matches
    $stopWords = [
        'the', 'is', 'at', 'which', 'on', 'a', 'an', 'as', 'are', 'was', 'were',
        'how', 'what', 'when', 'where', 'why', 'can', 'could', 'would', 'should',
        'my', 'your', 'our', 'their', 'i', 'you', 'we', 'they', 'mu', 'ku', 'oba',
        // ADDED THESE AFTER CHATBOT MISBEHAVING - they're too generic and cause false matches:
        'growing', 'planting', 'farm', 'farming', 'crop', 'crops', 'plant', 'plants',
        'disease', 'diseases', 'pest', 'pests', 'control', 'management'
    ];
    
    $words = preg_split('/\s+/', mb_strtolower($message, 'UTF-8'));
    $keywords = array_filter($words, function($word) use ($stopWords) {
        return strlen($word) > 2 && !in_array($word, $stopWords);
    });
    
    return array_values($keywords);
}

    /**
     * Get enhanced rule-based response with context awareness
     */
    private function getEnhancedRuleBasedResponse($message, $language, $context, $history) {
        $knowledgeBase = $this->getKnowledgeBase($language);
        $messageLower = mb_strtolower($message, 'UTF-8');
        
        // Check for context-specific responses
        if (!empty($context['enterprises'])) {
            $contextResponse = $this->getContextualResponse($message, $context, $language);
            if ($contextResponse) {
                return $contextResponse;
            }
        }
        
        // Use conversation history for context
        if (!empty($history)) {
            $historyResponse = $this->getHistoryAwareResponse($message, $history, $language);
            if ($historyResponse) {
                return $historyResponse;
            }
        }
        
        // Standard knowledge base search
        foreach ($knowledgeBase as $pattern => $response) {
            if (preg_match($pattern, $messageLower)) {
                return $this->personalizeResponse($response, $context, $language);
            }
        }
        
        return $this->getDefaultResponse($language);
    }
    
    /**
     * Get contextual response based on user's farm data
     */
    private function getContextualResponse($message, $context, $language) {
        $messageLower = mb_strtolower($message, 'UTF-8');
        
        // Check if asking about their own crops/enterprises
        $personalKeywords = ['my', 'mine', 'our', 'gange', 'waffe'];
        $isPersonal = false;
        
        foreach ($personalKeywords as $keyword) {
            if (stripos($message, $keyword) !== false) {
                $isPersonal = true;
                break;
            }
        }
        
        if ($isPersonal && !empty($context['enterprises'])) {
            $response = $language === 'en' ? 
                       "Based on your farm profile, here's what I found:\n\n" :
                       "Okusinziira ku farm yo, kino kye nzudde:\n\n";
            
            foreach ($context['enterprises'] as $enterprise) {
                $response .= "**" . ucfirst(str_replace('_', ' ', $enterprise['enterprise_type'])) . "**\n";
                $response .= ($language === 'en' ? "Status: " : "Embeera: ") . 
                            ucfirst($enterprise['status']) . "\n";
                
                if ($enterprise['planting_date']) {
                    $plantingDate = date('M d, Y', strtotime($enterprise['planting_date']));
                    $response .= ($language === 'en' ? "Planted: " : "Yasimbibwa: ") . 
                                $plantingDate . "\n";
                }
                
                if ($enterprise['expected_harvest_date']) {
                    $harvestDate = date('M d, Y', strtotime($enterprise['expected_harvest_date']));
                    $daysUntil = ceil((strtotime($enterprise['expected_harvest_date']) - time()) / 86400);
                    
                    if ($daysUntil > 0) {
                        $response .= ($language === 'en' ? "Harvest in: " : "Okungula mu: ") . 
                                    $daysUntil . ($language === 'en' ? " days" : " ennaku") . "\n";
                    }
                }
                
                $response .= "\n";
            }
            
            // Add relevant advice
            $response .= $this->getEnterpriseAdvice($context['enterprises'], $language);
            
            return $response;
        }
        
        return null;
    }
    
    /**
     * Get enterprise-specific advice
     */
    private function getEnterpriseAdvice($enterprises, $language) {
        $advice = $language === 'en' ? "\n**Recommendations:**\n" : "\n**Amagezi:**\n";
        
        foreach ($enterprises as $enterprise) {
            $type = $enterprise['enterprise_type'];
            $status = $enterprise['status'];
            
            // Check if harvest is near
            if ($enterprise['expected_harvest_date']) {
                $daysUntil = ceil((strtotime($enterprise['expected_harvest_date']) - time()) / 86400);
                
                if ($daysUntil > 0 && $daysUntil <= 14) {
                    $cropName = ucfirst(str_replace('_', ' ', $type));
                    if ($language === 'en') {
                        $advice .= "- Your {$cropName} harvest is approaching in {$daysUntil} days. Start preparing storage and finding buyers.\n";
                    } else {
                        $advice .= "- {$cropName} yaago ekumpi okungulwa mu nnaku {$daysUntil}. Tandika okutegeka okutereka n'okunoonya abagula.\n";
                    }
                }
            }
        }
        
        return $advice;
    }
    
    /**
     * Get history-aware response
     */
    private function getHistoryAwareResponse($message, $history, $language) {
        // Check if user is following up on previous topic
        if (count($history) > 0) {
            $lastConversation = $history[count($history) - 1];
            $lastMessage = mb_strtolower($lastConversation['message'], 'UTF-8');
            $currentMessage = mb_strtolower($message, 'UTF-8');
            
            // Check for follow-up indicators
            $followUpIndicators = ['more', 'also', 'what about', 'and', 'ate', 'n\'ate', 'era'];
            
            foreach ($followUpIndicators as $indicator) {
                if (stripos($currentMessage, $indicator) !== false) {
                    // This might be a follow-up question
                    return $this->generateFollowUpResponse($lastMessage, $currentMessage, $language);
                }
            }
        }
        
        return null;
    }
    
    /**
     * Generate follow-up response
     */
    private function generateFollowUpResponse($previousMessage, $currentMessage, $language) {
        // Extract topic from previous message
        $topics = ['maize', 'beans', 'cassava', 'price', 'plant', 'disease', 
                   'kasooli', 'bikooge', 'muwogo', 'simba', 'bulwadhe'];
        
        $topic = null;
        foreach ($topics as $t) {
            if (stripos($previousMessage, $t) !== false) {
                $topic = $t;
                break;
            }
        }
        
        if ($topic) {
            // Search for additional information about the same topic
            return $this->getMultiSourceKnowledge($topic . ' ' . $currentMessage, $language, []);
        }
        
        return null;
    }
    
    /**
     * Learn from successful OpenAI responses
     */
    private function learnFromResponse($question, $response, $language) {
        try {
            // Check if pattern already exists
            $checkSql = "SELECT id FROM ai_learned_patterns 
                        WHERE question_pattern = ? AND language = ?";
            $existing = $this->db->fetchOne($checkSql, [$question, $language]);
            
            if (!$existing) {
                // Save new learned pattern
                $sql = "INSERT INTO ai_learned_patterns 
                        (question_pattern, response_template, language, source, usage_count, success_rate)
                        VALUES (?, ?, ?, 'openai', 1, 1.0)";
                
                $this->db->execute($sql, [$question, $response, $language]);
                
                // Reload learned patterns
                $this->loadLearnedPatterns();
            }
            
        } catch (\Exception $e) {
            error_log('Error learning from response: ' . $e->getMessage());
        }
    }
    
    /**
     * Load learned patterns from database
     */
    private function loadLearnedPatterns() {
        try {
            $sql = "SELECT * FROM ai_learned_patterns 
                    WHERE is_active = 1 AND success_rate >= 0.7
                    ORDER BY usage_count DESC, success_rate DESC";
            
            $patterns = $this->db->fetchAll($sql);
            
            foreach ($patterns as $pattern) {
                $lang = $pattern['language'];
                if (!isset(self::$learnedPatterns[$lang])) {
                    self::$learnedPatterns[$lang] = [];
                }
                self::$learnedPatterns[$lang][] = $pattern;
            }
            
        } catch (\Exception $e) {
            error_log('Error loading learned patterns: ' . $e->getMessage());
        }
    }
    
    /**
     * Update pattern usage count
     */
    private function updatePatternUsage($patternId) {
        try {
            $sql = "UPDATE ai_learned_patterns 
                    SET usage_count = usage_count + 1, last_used = NOW()
                    WHERE id = ?";
            $this->db->execute($sql, [$patternId]);
        } catch (\Exception $e) {
            error_log('Error updating pattern usage: ' . $e->getMessage());
        }
    }
    
    /**
     * Enhanced OpenAI request with better error handling
     */
    private function getOpenAIResponse($message, $language, $context, $conversationHistory = []) {
        $systemPrompt = $this->buildEnhancedSystemPrompt($language, $context);
        
        // Build messages array with history
        $messages = [
            [
                'role' => 'system',
                'content' => $systemPrompt
            ]
        ];
        
        // Add conversation history for context (limit to last 5)
        $historyCount = 0;
        foreach ($conversationHistory as $hist) {
            if ($historyCount >= 5) break;
            
            $messages[] = [
                'role' => 'user',
                'content' => $hist['message']
            ];
            $messages[] = [
                'role' => 'assistant',
                'content' => $hist['response']
            ];
            $historyCount++;
        }
        
        // Add current message
        $messages[] = [
            'role' => 'user',
            'content' => $message
        ];
        
        $requestData = [
            'model' => $this->model,
            'messages' => $messages,
            'temperature' => $this->temperature,
            'max_tokens' => $this->maxTokens,
            'top_p' => 0.9,
            'frequency_penalty' => 0.3,
            'presence_penalty' => 0.3
        ];
        
        error_log("OpenAI Request Data: " . json_encode($requestData, JSON_PRETTY_PRINT));
        
        // Make API request with retry logic
        $maxRetries = 2;
        $retryCount = 0;
        $lastError = null;
        
        while ($retryCount < $maxRetries) {
            try {
                $ch = curl_init('https://api.openai.com/v1/chat/completions');
                curl_setopt_array($ch, [
                    CURLOPT_RETURNTRANSFER => true,
                    CURLOPT_POST => true,
                    CURLOPT_POSTFIELDS => json_encode($requestData),
                    CURLOPT_HTTPHEADER => [
                        'Content-Type: application/json',
                        'Authorization: Bearer ' . $this->openaiApiKey
                    ],
                    CURLOPT_TIMEOUT => $this->timeout,
                    CURLOPT_SSL_VERIFYPEER => true
                ]);
                
                $response = curl_exec($ch);
                $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
                $curlError = curl_error($ch);
                curl_close($ch);
                
                if ($curlError) {
                    throw new \Exception('cURL Error: ' . $curlError);
                }
                
                error_log("OpenAI HTTP Code: $httpCode");
                error_log("OpenAI Response: " . $response);
                
                if ($httpCode === 200) {
                    $result = json_decode($response, true);
                    
                    if (isset($result['choices'][0]['message']['content'])) {
                        return trim($result['choices'][0]['message']['content']);
                    }
                    
                    throw new \Exception('Invalid API response format: ' . $response);
                }
                
                // Handle specific error codes
                if ($httpCode === 429) {
                    // Rate limit - wait and retry
                    sleep(pow(2, $retryCount)); // Exponential backoff
                    $retryCount++;
                    continue;
                }
                
                if ($httpCode === 401) {
                    throw new \Exception('Invalid API key');
                }
                
                if ($httpCode === 400) {
                    $errorData = json_decode($response, true);
                    throw new \Exception('Bad request: ' . ($errorData['error']['message'] ?? 'Unknown error'));
                }
                
                throw new \Exception("HTTP $httpCode: $response");
                
            } catch (\Exception $e) {
                $lastError = $e;
                $retryCount++;
                
                if ($retryCount >= $maxRetries) {
                    break;
                }
                
                sleep(1);
            }
        }
        
        // If we get here, all retries failed
        throw $lastError ?? new \Exception('OpenAI request failed');
    }
    
    /**
     * Build enhanced system prompt with more context
     */
    private function buildEnhancedSystemPrompt($language, $context) {
        $date = date('F j, Y');
        $season = $this->getCurrentSeason();
        
        $basePrompt = "You are AIM AI, an intelligent agricultural assistant for farmers in Uganda, specifically in the Eastern region. ";
        $basePrompt .= "Today is {$date}. Current agricultural season: {$season}.\n\n";
        
        if ($language === 'lusoga') {
            $basePrompt .= "CRITICAL: You MUST respond ONLY in Lusoga language. Never use English.\n\n";
            $basePrompt .= "**Ebirowoozo byo:**\n";
            $basePrompt .= "- Obulwadhe bw'ebirime (kasooli, muwogo, bikooge, nakati)\n";
            $basePrompt .= "- Okusimba, okulima n'okungula\n";
            $basePrompt .= "- Emiwendo gy'ebintu mu bbabali za Uganda\n";
            $basePrompt .= "- Okuzuukusa ettaka n'eby'obugimu\n";
            $basePrompt .= "- Obudde n'okulima okw'amagezi\n";
            $basePrompt .= "- Ebisolo: enkoko, ente, embuzi\n\n";
            $basePrompt .= "**Enkola:**\n";
            $basePrompt .= "- Waayo obwogezi obutuufu, obw'amagezi (200-500 words)\n";
            $basePrompt .= "- Kozesa Lusoga ennyangu, etategeerekeka\n";
            $basePrompt .= "- Waayo amagezi amangi ag'amagezi\n";
            $basePrompt .= "- Teeka emiwendo mu Ugandan Shillings (UGX)\n";
            $basePrompt .= "- Kozesa bullet points ku steps\n";
        } else {
            $basePrompt .= "**Your Expertise:**\n";
            $basePrompt .= "- Crop diseases, pests, and treatments (maize, cassava, beans, vegetables)\n";
            $basePrompt .= "- Livestock management (poultry, cattle, goats, pigs)\n";
            $basePrompt .= "- Planting schedules for Uganda's climate (2 seasons: Mar-Apr, Aug-Sep)\n";
            $basePrompt .= "- Market prices and selling strategies\n";
            $basePrompt .= "- Soil health and organic farming\n";
            $basePrompt .= "- Weather-smart agriculture\n";
            $basePrompt .= "- Farm financial management\n\n";
            $basePrompt .= "**Response Guidelines:**\n";
            $basePrompt .= "- Keep responses practical and actionable (200-500 words)\n";
            $basePrompt .= "- Use simple, farmer-friendly language\n";
            $basePrompt .= "- Give specific recommendations with quantities and timing\n";
            $basePrompt .= "- Suggest affordable, locally available solutions\n";
            $basePrompt .= "- Include costs in Ugandan Shillings (UGX) when relevant\n";
            $basePrompt .= "- Use bullet points for steps and lists\n";
            $basePrompt .= "- Be encouraging and supportive\n";
        }
        
        // Add user context
        if (!empty($context['user_name'])) {
            $basePrompt .= "\n**User Profile:**\n";
            $basePrompt .= "- Name: " . $context['user_name'] . "\n";
        }
        
        if (!empty($context['location'])) {
            $basePrompt .= "- Location: " . $context['location'];
            if (!empty($context['region'])) {
                $basePrompt .= ", " . $context['region'];
            }
            $basePrompt .= "\n";
        }
        
        if (!empty($context['farming_experience'])) {
            $basePrompt .= "- Farming Experience: " . $context['farming_experience'] . " years\n";
        }
        
        if (!empty($context['farm_size'])) {
            $basePrompt .= "- Farm Size: " . $context['farm_size'] . " acres\n";
        }
        
        if (!empty($context['current_crops'])) {
            $crops = is_array($context['current_crops']) ? 
                    implode(', ', $context['current_crops']) : 
                    $context['current_crops'];
            $basePrompt .= "- Current Crops: " . $crops . "\n";
        }
        
        if (!empty($context['livestock'])) {
            $livestock = is_array($context['livestock']) ? 
                        implode(', ', $context['livestock']) : 
                        $context['livestock'];
            $basePrompt .= "- Livestock: " . $livestock . "\n";
        }
        
        if (!empty($context['enterprises'])) {
            $basePrompt .= "\n**Active Enterprises:**\n";
            foreach ($context['enterprises'] as $enterprise) {
                $basePrompt .= "- " . ucfirst(str_replace('_', ' ', $enterprise['enterprise_type']));
                if ($enterprise['size_acres']) {
                    $basePrompt .= " (" . $enterprise['size_acres'] . " acres)";
                }
                $basePrompt .= " - Status: " . $enterprise['status'] . "\n";
            }
        }
        
        return $basePrompt;
    }
    
    /**
     * Get current agricultural season
     */
    private function getCurrentSeason() {
        $month = date('n');
        
        if ($month >= 3 && $month <= 6) {
            return "First Season (Planting/Growing)";
        } elseif ($month >= 7 && $month <= 9) {
            return "Between Seasons";
        } elseif ($month >= 10 || $month <= 2) {
            return "Second Season (Planting/Growing)";
        }
        
        return "Transition Period";
    }
    
    /**
     * Generate a new session ID
     */
    private function generateSessionId($userId) {
        return 'session_' . $userId . '_' . time() . '_' . bin2hex(random_bytes(8));
    }
    
    /**
     * Get conversation history for a session
     */
    private function getSessionHistory($sessionId, $limit = 5) {
        try {
            $sql = "SELECT message, response, language 
                    FROM ai_conversations 
                    WHERE session_id = ? 
                    ORDER BY created_at DESC 
                    LIMIT ?";
            
            $history = $this->db->fetchAll($sql, [$sessionId, $limit]);
            return array_reverse($history);
            
        } catch (\Exception $e) {
            error_log('Error fetching session history: ' . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Check if OpenAI is properly configured
     */
    private function isOpenAIConfigured() {
        $isConfigured = !empty($this->openaiApiKey) && 
                       $this->openaiApiKey !== 'your_openai_api_key' &&
                       strpos($this->openaiApiKey, 'sk-') === 0;
        
        return $isConfigured;
    }
    
    /**
     * Detect language (English or Lusoga)
     */
    private function detectLanguage($message) {
        $lusogaPatterns = [
            'obulwadhe', 'okusimba', 'okuzuukusa', 'amasimu', 'ebirime',
            'omwezi', 'ditya', 'bwa', 'ndobulaba', 'ndisima', 'gange',
            'muwogo', 'emmere', 'okungula', 'omusaija', 'omukazi',
            'ebikoola', 'emiggo', 'ettaka', 'enkuba', 'obudde',
            'obulo', 'kasooli', 'bikooge', 'nakati', 'ensigo',
            'wasuze', 'osiibiile', 'oli otya', 'webale', 'mwebale',
            'nze', 'gwe', 'ye', 'ffe', 'mmwe', 'bo', 'ndiyinza'
        ];
        
        $messageLower = mb_strtolower($message, 'UTF-8');
        
        $matches = 0;
        foreach ($lusogaPatterns as $pattern) {
            if (mb_strpos($messageLower, $pattern) !== false) {
                $matches++;
            }
        }
        
        return $matches >= 2 ? 'lusoga' : 'en';
    }
    
    /**
     * Get knowledge base
     */
    private function getKnowledgeBase($language) {
        if (self::$knowledgeBase !== null && isset(self::$knowledgeBase[$language])) {
            return self::$knowledgeBase[$language];
        }
        
        if ($language === 'lusoga') {
            $kb = require __DIR__ . '/../config/knowledge-base-lusoga.php';
        } else {
            $kb = require __DIR__ . '/../config/knowledge-base-english.php';
        }
        
        self::$knowledgeBase[$language] = $kb;
        return $kb;
    }
    
    /**
     * Personalize response with user context
     */
    private function personalizeResponse($response, $context, $language) {
        // Add user name if available
        if (!empty($context['user_name'])) {
            $firstName = explode(' ', $context['user_name'])[0];
            if ($language === 'en') {
                $response = str_replace('{{name}}', $firstName, $response);
            }
        }
        
        // Add location context if relevant
        if (!empty($context['location']) && strpos($response, 'Uganda') === false) {
            $suffix = $language === 'lusoga' 
                ? "\n\n*Ebyo bigwanidde " . htmlspecialchars($context['location']) . "*"
                : "\n\n*This applies to the " . htmlspecialchars($context['location']) . " region*";
            $response .= $suffix;
        }
        
        return $response;
    }
    
    /**
     * Get default response
     */
    private function getDefaultResponse($language) {
        if ($language === 'lusoga') {
            return "Nsonyiwa, simanyi bulungi ekyo. 🤔\n\n" .
                   "Ndiyinza okukuyamba mu:\n" .
                   "- **Obulwadhe bw'ebirime** (kasooli, muwogo, bikooge)\n" .
                   "- **Okusimba n'okungula** ebirime\n" .
                   "- **Emiwendo** gy'ebintu mu bbabali\n" .
                   "- **Okuzuukusa ettaka**\n" .
                   "- **Obudde** n'okulima\n" .
                   "- **Ebisolo** (enkoko, ente, embuzi)\n\n" .
                   "Buuza ekirungi era ekirambudde! 😊";
        } else {
            return "I'm here to help with agricultural questions! 😊\n\n" .
                   "I can assist you with:\n" .
                   "- **Crop Diseases & Pests**\n" .
                   "- **Planting Schedules**\n" .
                   "- **Market Prices**\n" .
                   "- **Soil Management**\n" .
                   "- **Weather & Climate**\n" .
                   "- **Livestock Care**\n\n" .
                   "Please ask a specific question about farming! 🌾";
        }
    }
    
    /**
     * Get error response
     */
    private function getErrorResponse($language) {
        if ($language === 'lusoga') {
            return "Nsonyiwa, waliwo ekizibu. Gezaako nate. 🙏";
        } else {
            return "Sorry, I encountered an error. Please try again. 🙏";
        }
    }
    
    /**
     * Save conversation to database
     */
    private function saveConversation($userId, $sessionId, $message, $response, $language, $source) {
        try {
            $sql = "INSERT INTO ai_conversations 
                    (user_id, session_id, message, response, language, source, created_at)
                    VALUES (?, ?, ?, ?, ?, ?, NOW())";
            
            $this->db->execute($sql, [
                $userId,
                $sessionId,
                $message,
                $response,
                $language,
                $source
            ]);
            
        } catch (\Exception $e) {
            error_log('Error saving AI conversation: ' . $e->getMessage());
        }
    }
    
    /**
     * Update user interaction statistics
     */
    private function updateUserStats($userId, $language) {
        try {
            // This can be expanded to track user engagement
            $sql = "UPDATE users SET last_activity = NOW() WHERE id = ?";
            $this->db->execute($sql, [$userId]);
        } catch (\Exception $e) {
            error_log('Error updating user stats: ' . $e->getMessage());
        }
    }
    
    /**
     * Get user sessions
     */
    public function getUserSessions($userId, $limit = 20) {
        try {
            $sql = "SELECT 
                        session_id,
                        MIN(created_at) as started_at,
                        MAX(created_at) as last_message_at,
                        COUNT(*) as message_count,
                        (SELECT message FROM ai_conversations ac2 
                         WHERE ac2.session_id = ac.session_id 
                         ORDER BY created_at ASC LIMIT 1) as first_message
                    FROM ai_conversations ac
                    WHERE user_id = ?
                    GROUP BY session_id
                    ORDER BY last_message_at DESC
                    LIMIT ?";
            
            return $this->db->fetchAll($sql, [$userId, $limit]);
            
        } catch (\Exception $e) {
            error_log('Error fetching user sessions: ' . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Get session messages
     */
    public function getSessionMessages($sessionId) {
        try {
            $sql = "SELECT * FROM ai_conversations 
                    WHERE session_id = ? 
                    ORDER BY created_at ASC";
            
            return $this->db->fetchAll($sql, [$sessionId]);
            
        } catch (\Exception $e) {
            error_log('Error fetching session messages: ' . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Clear conversation history
     */
    public function clearHistory($userId) {
        try {
            $sql = "DELETE FROM ai_conversations WHERE user_id = ?";
            return $this->db->execute($sql, [$userId]);
        } catch (\Exception $e) {
            error_log('Error clearing conversation history: ' . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Get conversation statistics
     */
    public function getStats($userId) {
        try {
            $sql = "SELECT 
                        COUNT(DISTINCT session_id) as total_conversations,
                        COUNT(*) as total_messages,
                        SUM(CASE WHEN language = 'lusoga' THEN 1 ELSE 0 END) as lusoga_count,
                        SUM(CASE WHEN language = 'en' THEN 1 ELSE 0 END) as english_count,
                        SUM(CASE WHEN source = 'openai' THEN 1 ELSE 0 END) as openai_count,
                        SUM(CASE WHEN source = 'rule_based' THEN 1 ELSE 0 END) as rule_based_count,
                        MIN(created_at) as first_conversation,
                        MAX(created_at) as last_conversation
                    FROM ai_conversations 
                    WHERE user_id = ?";
            
            return $this->db->fetchOne($sql, [$userId]);
            
        } catch (\Exception $e) {
            error_log('Error fetching AI stats: ' . $e->getMessage());
            return null;
        }
    }
}