intelligent_csv_agent.py

"""
Truly Intelligent CSV AI Agent
Uses AI to understand natural language and analyze CSV data without hardcoded rules
"""

import pandas as pd
import numpy as np
from fastapi import FastAPI, HTTPException
from fastapi.responses import HTMLResponse
from typing import Dict, List, Any
import uvicorn
import re
from collections import Counter
import json

class IntelligentCSVAnalyzer:
    def __init__(self):
        """Initialize with CSV data and create intelligent analysis capabilities"""
        try:
            self.trainer_df = pd.read_csv('trainer_details.csv')
            self.session_df = pd.read_csv('trainer_session_details.csv')
            print(f"✅ Loaded {len(self.trainer_df)} trainers and {len(self.session_df)} sessions")
            
            # Create comprehensive data understanding
            self.analyze_data_structure()
            
        except Exception as e:
            print(f"❌ Error loading data: {e}")
            self.trainer_df = pd.DataFrame()
            self.session_df = pd.DataFrame()
    
    def analyze_data_structure(self):
        """Analyze the data to understand what information is available"""
        self.data_insights = {
            'trainer_columns': list(self.trainer_df.columns),
            'session_columns': list(self.session_df.columns),
            'domains': list(self.trainer_df['Domain'].unique()) if 'Domain' in self.trainer_df.columns else [],
            'locations': list(self.session_df['Location'].unique()) if 'Location' in self.session_df.columns else [],
            'topics': list(self.session_df['Topic'].unique()) if 'Topic' in self.session_df.columns else [],
            'modes': list(self.session_df['Mode'].unique()) if 'Mode' in self.session_df.columns else [],
            'trainer_names': list(self.trainer_df['Name'].unique()) if 'Name' in self.trainer_df.columns else []
        }
        
        print(f"📊 Data Analysis Complete:")
        print(f"   Domains: {len(self.data_insights['domains'])}")
        print(f"   Locations: {len(self.data_insights['locations'])}")
        print(f"   Topics: {len(self.data_insights['topics'])}")
    
    def understand_question_intent(self, question: str) -> Dict[str, Any]:
        """Use AI-like logic to understand what the user is asking"""
        question_lower = question.lower().strip()
        
        # Analyze question structure and intent
        intent = {
            'type': 'unknown',
            'target': None,  # trainers, sessions, specific person
            'filter_by': {},  # location, domain, experience, etc.
            'action': None,  # count, list, find, compare
            'keywords': []
        }
        
        # Extract keywords from question
        words = re.findall(r'\b\w+\b', question_lower)
        intent['keywords'] = [w for w in words if len(w) > 2]
        
        # Determine action type
        if any(word in question_lower for word in ['how many', 'count', 'total', 'number']):
            intent['action'] = 'count'
        elif any(word in question_lower for word in ['show', 'list', 'display', 'all']):
            intent['action'] = 'list'
        elif any(word in question_lower for word in ['find', 'who', 'which', 'search']):
            intent['action'] = 'find'
        elif any(word in question_lower for word in ['compare', 'vs', 'versus', 'difference']):
            intent['action'] = 'compare'
        else:
            intent['action'] = 'find'  # default
        
        # Determine target
        if any(word in question_lower for word in ['trainer', 'trainers', 'instructor', 'teacher', 'people']):
            intent['target'] = 'trainers'
        elif any(word in question_lower for word in ['session', 'sessions', 'class', 'course']):
            intent['target'] = 'sessions'
        else:
            # Try to infer from context
            if intent['action'] == 'count':
                intent['target'] = 'trainers'  # Most count questions are about trainers
            else:
                intent['target'] = 'both'
        
        # Extract filters intelligently
        self.extract_filters(question_lower, intent)
        
        return intent
    
    def extract_filters(self, question: str, intent: Dict[str, Any]):
        """Intelligently extract filters from the question"""
        
        # Location filters - check against known locations
        for location in self.data_insights['locations']:
            if location.lower() in question:
                intent['filter_by']['location'] = location
                break
        
        # Also check for common location variations
        location_variations = {
            'tamilnadu': ['Trichy', 'Chennai'],
            'tamil nadu': ['Trichy', 'Chennai'],
            'tn': ['Trichy', 'Chennai'],
            'mumbai': ['Mumbai'],
            'bangalore': ['Bangalore'],
            'delhi': ['Delhi'],
            'pune': ['Pune'],
            'kolkata': ['Kolkata']
        }
        
        for variation, actual_locations in location_variations.items():
            if variation in question:
                intent['filter_by']['location_group'] = actual_locations
                break
        
        # Domain filters - check against known domains
        for domain in self.data_insights['domains']:
            domain_words = domain.lower().split()
            if any(word in question for word in domain_words):
                intent['filter_by']['domain'] = domain
                break
        
        # Topic filters - check against known topics
        for topic in self.data_insights['topics']:
            if topic.lower() in question:
                intent['filter_by']['topic'] = topic
                break
        
        # Experience filters
        if any(word in question for word in ['experienced', 'senior', 'expert']):
            intent['filter_by']['experience'] = 'high'
        elif any(word in question for word in ['junior', 'beginner', 'new']):
            intent['filter_by']['experience'] = 'low'
        
        # Cost filters
        if any(word in question for word in ['cheap', 'affordable', 'low cost']):
            intent['filter_by']['cost'] = 'low'
        elif any(word in question for word in ['expensive', 'premium', 'high cost']):
            intent['filter_by']['cost'] = 'high'
        
        # Mode filters
        if any(word in question for word in ['online', 'remote', 'virtual']):
            intent['filter_by']['mode'] = 'Online'
        elif any(word in question for word in ['offline', 'in-person', 'physical']):
            intent['filter_by']['mode'] = 'Offline'
    
    def execute_intelligent_query(self, intent: Dict[str, Any]) -> str:
        """Execute the query based on understood intent"""
        
        try:
            if intent['action'] == 'count':
                return self.handle_count_query(intent)
            elif intent['action'] == 'list':
                return self.handle_list_query(intent)
            elif intent['action'] == 'find':
                return self.handle_find_query(intent)
            elif intent['action'] == 'compare':
                return self.handle_compare_query(intent)
            else:
                return self.handle_general_query(intent)
        
        except Exception as e:
            return f"❌ Error analyzing your question: {str(e)}"
    
    def handle_count_query(self, intent: Dict[str, Any]) -> str:
        """Handle counting queries intelligently"""
        
        if intent['target'] == 'trainers':
            df = self.trainer_df.copy()
            
            # Apply filters
            if 'location_group' in intent['filter_by']:
                # Count trainers who conduct sessions in these locations
                locations = intent['filter_by']['location_group']
                location_sessions = self.session_df[
                    self.session_df['Location'].isin(locations)
                ]
                unique_trainers = location_sessions['Trainer Name'].nunique()
                
                response = f"📊 **Count Result:**\n\n"
                response += f"🧑‍🏫 **{unique_trainers} trainers** conduct sessions in {', '.join(locations)}\n\n"
                
                # Add breakdown by location
                response += "**Breakdown by location:**\n```\n"
                for loc in locations:
                    loc_count = location_sessions[location_sessions['Location'] == loc]['Trainer Name'].nunique()
                    session_count = len(location_sessions[location_sessions['Location'] == loc])
                    response += f"{loc:<12}: {loc_count} trainers, {session_count} sessions\n"
                response += "```"
                
                return response
            
            elif 'domain' in intent['filter_by']:
                domain = intent['filter_by']['domain']
                count = len(df[df['Domain'] == domain])
                return f"📊 **{count} trainers** specialize in {domain}"
            
            else:
                total = len(df)
                return f"📊 **Total: {total} trainers** in the database"
        
        elif intent['target'] == 'sessions':
            df = self.session_df.copy()
            
            # Apply filters
            if 'location' in intent['filter_by']:
                location = intent['filter_by']['location']
                count = len(df[df['Location'] == location])
                return f"📊 **{count} sessions** in {location}"
            
            elif 'topic' in intent['filter_by']:
                topic = intent['filter_by']['topic']
                count = len(df[df['Topic'] == topic])
                return f"📊 **{count} sessions** on {topic}"
            
            else:
                total = len(df)
                return f"📊 **Total: {total} sessions** in the database"
        
        return "❌ Could not determine what to count. Please be more specific."
    
    def handle_find_query(self, intent: Dict[str, Any]) -> str:
        """Handle find/search queries intelligently"""
        
        results = []
        
        if intent['target'] in ['trainers', 'both']:
            trainer_results = self.filter_trainers(intent['filter_by'])
            if not trainer_results.empty:
                results.append(('trainers', trainer_results))
        
        if intent['target'] in ['sessions', 'both']:
            session_results = self.filter_sessions(intent['filter_by'])
            if not session_results.empty:
                results.append(('sessions', session_results))
        
        if not results:
            return f"❌ No results found matching your criteria. Available options:\n" + \
                   f"Domains: {', '.join(self.data_insights['domains'][:5])}\n" + \
                   f"Locations: {', '.join(self.data_insights['locations'][:5])}"
        
        return self.format_results(results, intent)
    
    def filter_trainers(self, filters: Dict[str, Any]) -> pd.DataFrame:
        """Apply filters to trainer data"""
        df = self.trainer_df.copy()
        
        if 'domain' in filters:
            df = df[df['Domain'] == filters['domain']]
        
        if 'experience' in filters:
            if filters['experience'] == 'high':
                df = df[df['Experience (yrs)'] >= 10]
            elif filters['experience'] == 'low':
                df = df[df['Experience (yrs)'] <= 5]
        
        if 'cost' in filters:
            if filters['cost'] == 'low':
                df = df[df['Per Day Charges (₹)'] <= df['Per Day Charges (₹)'].median()]
            elif filters['cost'] == 'high':
                df = df[df['Per Day Charges (₹)'] >= df['Per Day Charges (₹)'].median()]
        
        # Location filter for trainers (based on where they conduct sessions)
        if 'location_group' in filters:
            locations = filters['location_group']
            session_trainers = self.session_df[
                self.session_df['Location'].isin(locations)
            ]['Trainer Name'].unique()
            df = df[df['Name'].isin(session_trainers)]
        
        return df
    
    def filter_sessions(self, filters: Dict[str, Any]) -> pd.DataFrame:
        """Apply filters to session data"""
        df = self.session_df.copy()
        
        if 'location' in filters:
            df = df[df['Location'] == filters['location']]
        
        if 'location_group' in filters:
            df = df[df['Location'].isin(filters['location_group'])]
        
        if 'topic' in filters:
            df = df[df['Topic'] == filters['topic']]
        
        if 'mode' in filters:
            df = df[df['Mode'] == filters['mode']]
        
        return df
    
    def format_results(self, results: List, intent: Dict[str, Any]) -> str:
        """Format results in a user-friendly way"""
        response = "📊 **Search Results:**\n\n"
        
        for result_type, df in results:
            if result_type == 'trainers':
                response += f"🧑‍🏫 **TRAINERS ({len(df)} found):**\n"
                response += "```\n"
                response += f"{'Name':<15} {'Domain':<15} {'Experience':<12} {'Daily Rate':<12}\n"
                response += "-" * 60 + "\n"
                
                for _, row in df.head(5).iterrows():
                    name = str(row['Name'])[:14]
                    domain = str(row['Domain'])[:14]
                    exp = f"{row['Experience (yrs)']} yrs"
                    rate = f"₹{row['Per Day Charges (₹)']}"
                    response += f"{name:<15} {domain:<15} {exp:<12} {rate:<12}\n"
                
                response += "```\n\n"
            
            elif result_type == 'sessions':
                response += f"📅 **SESSIONS ({len(df)} found):**\n"
                response += "```\n"
                response += f"{'Topic':<20} {'Trainer':<15} {'Location':<12} {'Mode':<8}\n"
                response += "-" * 60 + "\n"
                
                for _, row in df.head(5).iterrows():
                    topic = str(row['Topic'])[:19]
                    trainer = str(row['Trainer Name'])[:14]
                    location = str(row['Location'])[:11]
                    mode = str(row['Mode'])[:7]
                    response += f"{topic:<20} {trainer:<15} {location:<12} {mode:<8}\n"
                
                response += "```\n\n"
        
        return response.strip()
    
    def handle_list_query(self, intent: Dict[str, Any]) -> str:
        """Handle listing queries"""
        return self.handle_find_query(intent)  # Same logic
    
    def handle_compare_query(self, intent: Dict[str, Any]) -> str:
        """Handle comparison queries"""
        return "🔄 Comparison feature coming soon! Try asking about specific trainers or domains."
    
    def handle_general_query(self, intent: Dict[str, Any]) -> str:
        """Handle general queries"""
        return self.handle_find_query(intent)
    
    def answer_question(self, question: str) -> str:
        """Main method to answer any question using AI analysis"""
        print(f"🧠 AI Processing: {question}")
        
        if not question or question.strip() == "":
            return "❌ Please ask a question about trainers or sessions."
        
        # Handle greetings
        if question.lower().strip() in ['hello', 'hi', 'hey']:
            return f"👋 Hello! I can help you analyze your trainer data. Try asking:\n" + \
                   f"• 'How many trainers are from Tamil Nadu?'\n" + \
                   f"• 'Show me blockchain experts'\n" + \
                   f"• 'Find affordable DevOps trainers'\n" + \
                   f"• 'List all online sessions'"
        
        # Use AI to understand the question
        intent = self.understand_question_intent(question)
        print(f"🎯 Understood intent: {intent}")
        
        # Execute intelligent query
        result = self.execute_intelligent_query(intent)
        
        return result

# Initialize the intelligent agent
agent = IntelligentCSVAnalyzer()

# FastAPI app
app = FastAPI(title="Intelligent CSV AI Agent", version="2.0.0")

@app.get("/", response_class=HTMLResponse)
async def serve_index():
    """Serve the main page"""
    return HTMLResponse("""
    <!DOCTYPE html>
    <html>
    <head>
        <title>🧠 Intelligent CSV AI Agent</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <style>
            * { margin: 0; padding: 0; box-sizing: border-box; }
            body { 
                font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; 
                background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
                min-height: 100vh; 
                padding: 20px; 
            }
            .container { 
                max-width: 900px; 
                margin: 0 auto; 
                background: white; 
                border-radius: 15px; 
                box-shadow: 0 10px 30px rgba(0,0,0,0.2); 
                overflow: hidden; 
            }
            .header { 
                background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); 
                color: white; 
                padding: 30px; 
                text-align: center; 
            }
            .header h1 { font-size: 2.2rem; margin-bottom: 10px; }
            .header p { font-size: 1.1rem; opacity: 0.9; }
            .features { 
                background: #f8f9fa; 
                padding: 20px; 
                border-bottom: 1px solid #eee; 
            }
            .feature-grid { 
                display: grid; 
                grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); 
                gap: 15px; 
            }
            .feature-item { 
                background: white; 
                padding: 15px; 
                border-radius: 10px; 
                text-align: center; 
                box-shadow: 0 2px 5px rgba(0,0,0,0.1); 
            }
            .chat-container { 
                height: 400px; 
                overflow-y: auto; 
                padding: 20px; 
                background: #f8f9fa; 
            }
            .message { 
                margin: 15px 0; 
                padding: 15px; 
                border-radius: 10px; 
                max-width: 85%; 
            }
            .user-message { 
                background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); 
                color: white; 
                margin-left: auto; 
                text-align: right; 
            }
            .bot-message { 
                background: white; 
                border: 1px solid #ddd; 
                box-shadow: 0 2px 5px rgba(0,0,0,0.1); 
            }
            .input-section { 
                padding: 20px; 
                background: white; 
                border-top: 1px solid #eee; 
            }
            .input-container { 
                display: flex; 
                gap: 10px; 
            }
            input { 
                flex: 1; 
                padding: 15px; 
                border: 2px solid #ddd; 
                border-radius: 25px; 
                font-size: 16px; 
                outline: none; 
            }
            input:focus { 
                border-color: #667eea; 
            }
            button { 
                padding: 15px 25px; 
                background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); 
                color: white; 
                border: none; 
                border-radius: 25px; 
                cursor: pointer; 
                font-weight: bold; 
            }
            button:hover { 
                transform: translateY(-2px); 
            }
            pre { 
                background: #f8f9fa; 
                padding: 10px; 
                border-radius: 5px; 
                overflow-x: auto; 
                font-family: 'Courier New', monospace; 
                font-size: 12px; 
            }
        </style>
    </head>
    <body>
        <div class="container">
            <div class="header">
                <h1>🧠 Intelligent CSV AI Agent</h1>
                <p>Ask me anything in natural language - I'll understand and analyze your data!</p>
            </div>
            
            <div class="features">
                <div class="feature-grid">
                    <div class="feature-item">
                        <strong>🎯 Smart Understanding</strong><br>
                        <small>No hardcoded rules - I understand your intent</small>
                    </div>
                    <div class="feature-item">
                        <strong>📊 Data Analysis</strong><br>
                        <small>Intelligent filtering and counting</small>
                    </div>
                    <div class="feature-item">
                        <strong>🗣️ Natural Language</strong><br>
                        <small>Ask questions like you would to a human</small>
                    </div>
                    <div class="feature-item">
                        <strong>🔍 Context Aware</strong><br>
                        <small>Understands locations, domains, and more</small>
                    </div>
                </div>
            </div>
            
            <div class="chat-container" id="chatContainer">
                <div class="message bot-message">
                    <strong>🧠 AI Agent:</strong> Hello! I'm an intelligent AI that can understand your questions and analyze your CSV data. Ask me anything about trainers and sessions - I'll figure out what you mean!
                </div>
            </div>
            
            <div class="input-section">
                <div class="input-container">
                    <input type="text" id="questionInput" placeholder="Ask me anything about your data..." onkeypress="handleKeyPress(event)">
                    <button onclick="sendQuestion()">Ask AI</button>
                </div>
            </div>
        </div>
        
        <script>
            function addMessage(message, isUser = false) {
                const container = document.getElementById('chatContainer');
                const messageDiv = document.createElement('div');
                messageDiv.className = 'message ' + (isUser ? 'user-message' : 'bot-message');
                
                if (isUser) {
                    messageDiv.innerHTML = '<strong>👤 You:</strong> ' + message;
                } else {
                    messageDiv.innerHTML = '<strong>🧠 AI Agent:</strong><br>' + 
                        message.replace(/\\n/g, '<br>')
                              .replace(/```([\\s\\S]*?)```/g, '<pre>$1</pre>')
                              .replace(/\\*\\*(.*?)\\*\\*/g, '<strong>$1</strong>');
                }
                
                container.appendChild(messageDiv);
                container.scrollTop = container.scrollHeight;
            }
            
            function handleKeyPress(event) {
                if (event.key === 'Enter') {
                    sendQuestion();
                }
            }
            
            async function sendQuestion() {
                const input = document.getElementById('questionInput');
                const question = input.value.trim();
                
                if (!question) return;
                
                addMessage(question, true);
                input.value = '';
                
                try {
                    const response = await fetch('/ask', {
                        method: 'POST',
                        headers: { 'Content-Type': 'application/json' },
                        body: JSON.stringify({ question: question })
                    });
                    
                    const data = await response.json();
                    if (data && data.answer) {
                        addMessage(data.answer);
                    } else {
                        addMessage('❌ No response received from server');
                    }
                } catch (error) {
                    addMessage('❌ Error: ' + error.message);
                }
            }
        </script>
    </body>
    </html>
    """)

@app.post("/ask")
async def ask_question(request: Dict[str, str]):
    """Answer questions using intelligent analysis"""
    question = request.get("question", "").strip()
    
    if not question:
        raise HTTPException(status_code=400, detail="No question provided")
    
    try:
        answer = agent.answer_question(question)
        return {"question": question, "answer": answer, "status": "success"}
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error: {str(e)}")

@app.get("/health")
async def health_check():
    """Health check endpoint"""
    return {
        "status": "healthy",
        "agent_type": "intelligent_ai",
        "trainers_loaded": len(agent.trainer_df),
        "sessions_loaded": len(agent.session_df),
        "domains": len(agent.data_insights['domains']),
        "locations": len(agent.data_insights['locations'])
    }

if __name__ == "__main__":
    print("🧠 Starting Intelligent CSV AI Agent...")
    print("🎯 AI-powered natural language understanding")
    print("📊 Smart data analysis without hardcoded rules")
    print("🌐 Access at: http://localhost:8005")
    
    uvicorn.run(app, host="0.0.0.0", port=8005, log_level="error")