crew.py

# Enhanced crew.py
from crewai import Agent, Crew, Process, Task, LLM
from crewai.project import CrewBase, agent, crew, task
import os

# Set your OpenAI API key
os.environ[
    "OPENAI_API_KEY"] = "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"

# Get the project root directory
project_root = os.path.dirname(os.path.abspath(__file__))

# Construct the relative path to tickets.db
db_path = os.path.join(project_root, "tickets.db")

# Use db_path wherever the database path is required
print(f"Database Path: {db_path}")

# Use OpenAI for testing
llm = LLM(model="gpt-3.5-turbo")

# Import all the enhanced tools
from sql_query_tool import (
    ticket_query_tool,
    status_query_tool,
    severity_query_tool,
    tag_query_tool,
    keyword_search_tool,
    advanced_ticket_search
)


@CrewBase
class EnhancedTicketSupportCrew():
    """Enhanced crew with comprehensive ticket search capabilities"""

    @agent
    def smart_support_agent(self) -> Agent:
        return Agent(
            role="Smart IT Support Agent",
            goal="""
            Analyze user queries and intelligently choose the best tool to find ticket information.

            Tool Selection Rules:
            1. For specific ticket numbers (TKT-XXXX): use ticket_query_tool
            2. For status queries (open, closed, in progress): use status_query_tool  
            3. For severity queries (low, medium, high, critical): use severity_query_tool
            4. For tag-based queries (backend, API, frontend): use tag_query_tool
            5. For keyword/symptom searches: use keyword_search_tool
            6. For complex queries with multiple criteria: use advanced_ticket_search

            Always provide comprehensive, well-formatted responses with:
            - Clear bullet points for multiple tickets
            - All relevant ticket details
            - Professional formatting
            """,
            backstory="""
            You are an intelligent IT support agent with deep knowledge of ticketing systems.
            You can automatically determine the best search approach based on user queries and
            provide comprehensive ticket information. You understand different query types and
            can extract relevant search criteria from natural language requests.
            """,
            llm=llm,
            tools=[
                ticket_query_tool,
                status_query_tool,
                severity_query_tool,
                tag_query_tool,
                keyword_search_tool,
                advanced_ticket_search
            ],
            verbose=True,
            max_iter=3
        )


    @task
    def enhanced_support_task(self) -> Task:
        return Task(
            description="""
            Process the user query: {query}

            Analysis Steps:
            1. Parse the query to identify search criteria:
               - Ticket numbers (TKT-XXXX format)
               - Status keywords (open, closed, in progress, resolved)
               - Severity levels (low, medium, high, critical)
               - Tags (backend, API, frontend, database, etc.)
               - Keywords/symptoms for description search
               - Assignee names

            2. Choose the most appropriate tool:
               - ticket_query_tool: For specific ticket numbers
               - status_query_tool: For status-based searches
               - severity_query_tool: For severity-based searches
               - tag_query_tool: For tag-based searches
               - keyword_search_tool: For keyword/symptom searches
               - advanced_ticket_search: For complex multi-criteria searches

            3. Format the response professionally:
               - Use bullet points for multiple tickets
               - Include all relevant details (status, severity, assignee, tags, etc.)
               - Provide clear, actionable information
               - Show ticket descriptions appropriately truncated

            4. Handle edge cases:
               - No results found
               - Database errors
               - Ambiguous queries
            """,
            expected_output="""
            Professional response with:
            - Clear formatting with bullet points for multiple results
            - Complete ticket information including:
              * Ticket number
              * Status and severity
              * Description (truncated if long)
              * Assignee and reporter
              * Tags and dates
              * Resolution information (if closed)
              Please provide a comprehensive response that includes:

                1. **Executive Summary**: Clear, concise answer to the user's question
            
                2. **Key Findings**: Most important insights from the data
            
                3. **Metrics & Statistics**: Relevant numbers, trends, and patterns
            
                4. **Recommendations**: Actionable suggestions based on the analysis
            
                5. **Risk Assessment**: Any concerns or potential issues identified
            
                6. **Next Steps**: Recommended actions for the user
            
                Format your response professionally and make it easy to understand. If the crew data contains errors or is incomplete, acknowledge this and provide what insights you can.
            
                Focus on being helpful, accurate, and actionable in your response.
            - Helpful context about the search results
            - User-friendly presentation
            """,
            agent=self.smart_support_agent()
        )

    @crew
    def crew(self) -> Crew:
        return Crew(
            agents=[self.smart_support_agent()],
            tasks=[self.enhanced_support_task()],
            process=Process.sequential,
            verbose=True,
            memory=True  # Enable memory for better context
        )