#!/usr/bin/env python3

import gradio as gr
import sys
import os
from typing import List, Tuple
from sqlalchemy import text

# Add the src directory to the path to import existing modules
sys.path.append(os.path.join(os.path.dirname(__file__), '..', 'src'))

from chatbot import Chatbot
from models import ChatbotRequest

class GradioInterface:
    """Gradio GUI interface for the LLM Chatbot."""
    
    def __init__(self):
        """Initialize the Gradio interface with the existing chatbot."""
        self.chatbot = Chatbot()
        self.conversation_history = []
    
    def process_message(self, message: str, history: List[Tuple[str, str]]) -> Tuple[str, List[Tuple[str, str]]]:
        """
        Process a user message and return the response with updated history.
        
        Args:
            message: User input message
            history: Chat history as list of (user_msg, bot_response) tuples
            
        Returns:
            Tuple of (empty_string_for_input, updated_history)
        """
        if not message.strip():
            return "", history
        
        # Handle quit/exit commands
        if message.lower().strip() in ['quit', 'exit', 'bye']:
            bot_response = "👋 Goodbye! Refresh the page to start a new session."
            history.append((message, bot_response))
            return "", history
        
        try:
            # Process the message using the existing chatbot
            request = ChatbotRequest(message=message)
            response = chatbot_response = self.chatbot.process_message(request)
            
            # Build the response with additional information
            response_text = f"🤖 {response.response}"
            
            # Add extracted entities information
            if response.entities_extracted:
                entities_info = (
                    f"\n\n📊 **Extracted Information:**\n"
                    f"- Type: {response.entities_extracted.transaction_type}\n"
                    f"- Product: {response.entities_extracted.product}\n"
                    f"- Quantity: {response.entities_extracted.quantity}\n"
                    f"- Total Amount: €{response.entities_extracted.total_amount}"
                )
                response_text += entities_info
            
            # Add vector storage confirmation
            if response.vector_stored:
                response_text += "\n\n💾 Information stored in vector database for future semantic search"
            
            # Add intent detection information
            if response.intent_detected:
                response_text += f"\n\n🎯 **Intent Detected:** {response.intent_detected} (confidence: {response.intent_confidence:.2f})"
            
            # Add clarification prompt
            if response.awaiting_clarification:
                response_text += "\n\n⏳ **Waiting for your response to complete the transaction...**"
            
            # Update history
            history.append((message, response_text))
            
        except Exception as e:
            error_response = f"❌ Error processing message: {str(e)}"
            history.append((message, error_response))
        
        return "", history
    
    def clear_chat(self) -> Tuple[str, List]:
        """Clear the chat history and reset the conversation."""
        return "", []
    
    def get_dashboard_data(self):
        """Get dashboard data using direct SQL queries."""
        try:
            # Access the database manager directly
            db_manager = self.chatbot.db_manager
            
            # Get basic statistics
            total_purchases = db_manager.session.execute(
                text("SELECT COUNT(*) FROM purchases")
            ).scalar() or 0
            
            total_sales = db_manager.session.execute(
                text("SELECT COUNT(*) FROM sales")
            ).scalar() or 0
            
            total_revenue = db_manager.session.execute(
                text("SELECT SUM(total_amount) FROM sales")
            ).scalar() or 0
            
            total_expenses = db_manager.session.execute(
                text("SELECT SUM(total_cost) FROM purchases")
            ).scalar() or 0
            
            # Get recent transactions (last 5) - combining purchases and sales
            recent_transactions = db_manager.session.execute(
                text("""
                SELECT 'purchase' as transaction_type, p.name as product, pu.quantity, 
                       pu.total_cost as total_amount, s.name as partner, pu.purchase_date as created_at
                FROM purchases pu
                LEFT JOIN products p ON pu.product_id = p.id  
                LEFT JOIN suppliers s ON pu.supplier_id = s.id
                UNION ALL
                SELECT 'sale' as transaction_type, p.name as product, sa.quantity,
                       sa.total_amount, c.name as partner, sa.sale_date as created_at
                FROM sales sa
                LEFT JOIN products p ON sa.product_id = p.id
                LEFT JOIN customers c ON sa.customer_id = c.id
                ORDER BY created_at DESC 
                LIMIT 5
                """)
            ).fetchall()
            
            # Get top products - combining from both tables
            top_products = db_manager.session.execute(
                text("""
                SELECT p.name as product, SUM(combined.quantity) as total_qty, COUNT(*) as transaction_count
                FROM (
                    SELECT product_id, quantity FROM purchases
                    UNION ALL 
                    SELECT product_id, quantity FROM sales
                ) combined
                LEFT JOIN products p ON combined.product_id = p.id
                GROUP BY p.name 
                ORDER BY total_qty DESC 
                LIMIT 5
                """)
            ).fetchall()
            
            return {
                'total_purchases': total_purchases,
                'total_sales': total_sales, 
                'total_revenue': round(total_revenue, 2),
                'total_expenses': round(total_expenses, 2),
                'profit': round(total_revenue - total_expenses, 2),
                'recent_transactions': recent_transactions,
                'top_products': top_products
            }
            
        except Exception as e:
            return {
                'total_purchases': 0,
                'total_sales': 0,
                'total_revenue': 0.0,
                'total_expenses': 0.0,
                'profit': 0.0,
                'recent_transactions': [],
                'top_products': []
            }
    
    def create_revenue_chart(self, data):
        """Create revenue vs expenses chart."""
        import plotly.graph_objects as go
        
        fig = go.Figure(data=[
            go.Bar(name='Revenue', x=['Financial Summary'], y=[data['total_revenue']], marker_color='green'),
            go.Bar(name='Expenses', x=['Financial Summary'], y=[data['total_expenses']], marker_color='red'),
            go.Bar(name='Profit', x=['Financial Summary'], y=[data['profit']], marker_color='blue')
        ])
        
        fig.update_layout(
            title='Financial Overview',
            barmode='group',
            height=300
        )
        
        return fig
    
    def create_transaction_chart(self, data):
        """Create transaction count pie chart."""
        import plotly.graph_objects as go
        
        fig = go.Figure(data=[go.Pie(
            labels=['Purchases', 'Sales'], 
            values=[data['total_purchases'], data['total_sales']],
            marker_colors=['lightcoral', 'lightgreen']
        )])
        
        fig.update_layout(
            title='Transaction Distribution',
            height=300
        )
        
        return fig
    
    def create_top_products_chart(self, data):
        """Create top products bar chart."""
        import plotly.graph_objects as go
        
        if not data['top_products']:
            fig = go.Figure()
            fig.add_annotation(text="No product data available", 
                             xref="paper", yref="paper", x=0.5, y=0.5, showarrow=False)
            fig.update_layout(title='Top Products', height=300)
            return fig
        
        products = [row[0] for row in data['top_products']]
        quantities = [row[1] for row in data['top_products']]
        
        fig = go.Figure(data=[
            go.Bar(x=products, y=quantities, marker_color='skyblue')
        ])
        
        fig.update_layout(
            title='Top Products by Quantity',
            xaxis_title='Products',
            yaxis_title='Total Quantity',
            height=300
        )
        
        return fig
    
    def structured_purchase(self, product, quantity, supplier, unit_price):
        """Handle structured purchase entry."""
        if not all([product, quantity, supplier, unit_price]):
            return "", [("System", "⚠️ Please fill in all fields for the purchase.")], ""
        
        message = f"Add a purchase of {quantity} {product} from {supplier} at €{unit_price} each"
        request = ChatbotRequest(message=message)
        response = self.chatbot.process_message(request)
        
        history = [("Purchase Entry", message), ("System", f"✅ {response.response}")]
        return "", history, "Purchase recorded successfully!"
    
    def structured_sale(self, product, quantity, customer, unit_price):
        """Handle structured sale entry."""
        if not all([product, quantity, customer, unit_price]):
            return "", [("System", "⚠️ Please fill in all fields for the sale.")], ""
        
        message = f"Sold {quantity} {product} to {customer} at €{unit_price} each"
        request = ChatbotRequest(message=message)
        response = self.chatbot.process_message(request)
        
        history = [("Sale Entry", message), ("System", f"✅ {response.response}")]
        return "", history, "Sale recorded successfully!"
    
    def search_records(self, search_query, search_type):
        """Handle structured search."""
        if not search_query:
            return [("System", "⚠️ Please enter a search query.")]
        
        if search_type == "Products":
            message = f"Find {search_query}"
        elif search_type == "Suppliers":
            message = f"Search supplier {search_query}"
        elif search_type == "Customers":
            message = f"Search customer {search_query}"
        else:
            message = f"Search {search_query}"
        
        request = ChatbotRequest(message=message)
        response = self.chatbot.process_message(request)
        
        return [("Search Query", message), ("Results", response.response)]
    
    def create_interface(self) -> gr.Interface:
        """Create and configure the Gradio interface."""
        
        with gr.Blocks(
            title="Business AI Assistant",
            theme=gr.themes.Default()
        ) as interface:
            
            # Header
            gr.Markdown("# 💼 Business AI Assistant")
            gr.Markdown("**Intelligent transaction management and business intelligence platform**")
            
            # Main tabbed interface
            with gr.Tabs() as tabs:
                
                # Dashboard Tab
                with gr.Tab("📊 Dashboard"):
                    # Key Metrics Row
                    with gr.Row():
                        metrics_purchases = gr.Number(label="Total Purchases", interactive=False)
                        metrics_sales = gr.Number(label="Total Sales", interactive=False)  
                        metrics_revenue = gr.Number(label="Revenue (€)", interactive=False)
                        metrics_profit = gr.Number(label="Profit (€)", interactive=False)
                    
                    # Charts Row
                    with gr.Row():
                        with gr.Column():
                            financial_chart = gr.Plot(label="Financial Overview")
                        with gr.Column():
                            transaction_chart = gr.Plot(label="Transaction Distribution")
                    
                    with gr.Row():
                        with gr.Column():
                            products_chart = gr.Plot(label="Top Products")
                        with gr.Column():
                            # Recent Transactions Table
                            recent_table = gr.Dataframe(
                                headers=["Type", "Product", "Qty", "Amount (€)", "Partner"],
                                datatype=["str", "str", "number", "number", "str"],
                                label="Recent Transactions",
                            )
                    
                    # Action Buttons
                    with gr.Row():
                        refresh_dashboard = gr.Button("🔄 Refresh Data", variant="secondary")
                        dash_new_purchase = gr.Button("➕ New Purchase", variant="primary")
                        dash_new_sale = gr.Button("💰 New Sale", variant="primary")
                        dash_search = gr.Button("🔍 Search Records", variant="outline")
                
                # Chat Tab
                with gr.Tab("💬 AI Chat"):
                    gr.Markdown("### Conversational Business Assistant")
                    gr.Markdown("*Ask questions, add transactions, search records, or get insights in natural language*")
                    
                    chatbot_ui = gr.Chatbot(
                        value=[],
                        height=500,
                        label="Conversation",
                        show_label=False,
                        container=True,
                        show_copy_button=True
                    )
                    
                    with gr.Row():
                        msg_input = gr.Textbox(
                            placeholder="Ask me anything about your business... (e.g., 'Show recent sales', 'Add 10 laptops from TechMart')",
                            label="Message",
                            lines=2,
                            max_lines=4,
                            scale=5
                        )
                        send_btn = gr.Button("Send", variant="primary", scale=1)
                    
                    with gr.Row():
                        clear_chat_btn = gr.Button("Clear Chat", variant="secondary")
                        
                        # Example prompts
                        example_1 = gr.Button("💡 Example: Add Purchase", variant="outline", size="sm")
                        example_2 = gr.Button("💡 Example: Search Products", variant="outline", size="sm")
                        example_3 = gr.Button("💡 Example: View Transactions", variant="outline", size="sm")
                
                # Transactions Tab
                with gr.Tab("📝 Transactions"):
                    with gr.Row():
                        # Purchase Form
                        with gr.Column():
                            gr.Markdown("### ➕ Add Purchase")
                            purchase_product = gr.Textbox(label="Product", placeholder="e.g., Laptops")
                            purchase_quantity = gr.Number(label="Quantity", value=1, minimum=1)
                            purchase_supplier = gr.Textbox(label="Supplier", placeholder="e.g., TechMart")
                            purchase_price = gr.Number(label="Unit Price (€)", value=0.00, minimum=0)
                            purchase_btn = gr.Button("Add Purchase", variant="primary")
                            purchase_status = gr.Markdown("")
                        
                        # Sale Form  
                        with gr.Column():
                            gr.Markdown("### 💰 Add Sale")
                            sale_product = gr.Textbox(label="Product", placeholder="e.g., USB Drives")
                            sale_quantity = gr.Number(label="Quantity", value=1, minimum=1)
                            sale_customer = gr.Textbox(label="Customer", placeholder="e.g., ABC Corp")
                            sale_price = gr.Number(label="Unit Price (€)", value=0.00, minimum=0)
                            sale_btn = gr.Button("Add Sale", variant="primary")
                            sale_status = gr.Markdown("")
                    
                    # Transaction Results
                    gr.Markdown("### Transaction Results")
                    transaction_results = gr.Chatbot(
                        value=[],
                        height=300,
                        label="Transaction Log",
                        show_copy_button=True
                    )
                
                # Search & Reports Tab
                with gr.Tab("🔍 Search & Reports"):
                    gr.Markdown("### Advanced Search")
                    
                    with gr.Row():
                        search_query = gr.Textbox(
                            label="Search Query",
                            placeholder="Enter product name, supplier, customer, or keywords...",
                            scale=3
                        )
                        search_type = gr.Dropdown(
                            choices=["All Records", "Products", "Suppliers", "Customers", "Transactions"],
                            value="All Records",
                            label="Search Type",
                            scale=1
                        )
                        search_btn = gr.Button("Search", variant="primary", scale=1)
                    
                    # Search Results
                    search_results = gr.Chatbot(
                        value=[],
                        height=400,
                        label="Search Results",
                        show_copy_button=True
                    )
                    
                    # Quick Search Buttons
                    with gr.Row():
                        gr.Markdown("### Quick Searches")
                    with gr.Row():
                        recent_purchases = gr.Button("Recent Purchases", variant="outline")
                        recent_sales = gr.Button("Recent Sales", variant="outline")
                        top_products = gr.Button("Top Products", variant="outline")
                        supplier_summary = gr.Button("Supplier Summary", variant="outline")
                
                # Help & Settings Tab
                with gr.Tab("❓ Help & Settings"):
                    with gr.Row():
                        with gr.Column():
                            gr.Markdown("""
                            ### 📖 User Guide
                            
                            - Use the **Dashboard** for quick overview and actions
                            - **AI Chat** for natural language interactions
                            - **Transactions** for structured data entry
                            - **Search & Reports** for finding information
                            
                            **Chat Examples**
                            - "Add a purchase of 20 USB drives from TechMart at €5 each"
                            - "Show me recent sales to ABC Corp"
                            - "Find all laptop transactions"
                            - "What's my total revenue this month?"
                            
                            **Features**
                            - Entity recognition with Spacy
                            - Vector store index and NLP2SQL queries
                            - Smart intent classification
                            """)
                        
                        with gr.Column():
                            gr.Markdown("""
                            ### ⚙️ System Information
                            
                            **Status**: 🟢 Online and Ready
                            
                            **Supported Operations**:
                            - Purchase tracking
                            - Sales recording  
                            - Inventory searches
                            - Supplier management
                            - Customer records
                            - Financial reporting
                            
                            """)
                    
                    gr.Markdown("---")
                    gr.Markdown("*Business AI Assistant v1.0 • Built with Gradio • Powered by OpenAI*")
            
            # Event Handlers
            
            # Dashboard events
            def load_dashboard():
                data = self.get_dashboard_data()
                
                # Create charts
                financial_fig = self.create_revenue_chart(data)
                transaction_fig = self.create_transaction_chart(data)
                products_fig = self.create_top_products_chart(data)
                
                # Prepare recent transactions table
                recent_data = []
                for row in data['recent_transactions']:
                    recent_data.append([
                        row[0].title(),  # transaction_type
                        row[1],          # product
                        row[2],          # quantity  
                        f"€{row[3]:.2f}", # total_amount
                        row[4] or "N/A"  # partner (supplier/customer)
                    ])
                
                return (
                    data['total_purchases'],
                    data['total_sales'], 
                    data['total_revenue'],
                    data['profit'],
                    financial_fig,
                    transaction_fig,
                    products_fig,
                    recent_data
                )
            
            refresh_dashboard.click(
                fn=load_dashboard,
                outputs=[
                    metrics_purchases, metrics_sales, metrics_revenue, metrics_profit,
                    financial_chart, transaction_chart, products_chart, recent_table
                ]
            )
            
            # Chat events
            msg_input.submit(
                fn=self.process_message,
                inputs=[msg_input, chatbot_ui],
                outputs=[msg_input, chatbot_ui]
            )
            
            send_btn.click(
                fn=self.process_message,
                inputs=[msg_input, chatbot_ui],
                outputs=[msg_input, chatbot_ui]
            )
            
            clear_chat_btn.click(
                fn=self.clear_chat,
                outputs=[msg_input, chatbot_ui]
            )
            
            # Example prompts
            example_1.click(
                fn=lambda: ("Add a purchase of 10 laptops from TechMart at €800 each", []),
                outputs=[msg_input, chatbot_ui]
            )
            
            example_2.click(
                fn=lambda: ("Find all USB drive transactions", []),
                outputs=[msg_input, chatbot_ui]
            )
            
            example_3.click(
                fn=lambda: ("Show recent transactions", []),
                outputs=[msg_input, chatbot_ui]
            )
            
            # Transaction events
            purchase_btn.click(
                fn=self.structured_purchase,
                inputs=[purchase_product, purchase_quantity, purchase_supplier, purchase_price],
                outputs=[purchase_product, transaction_results, purchase_status]
            )
            
            sale_btn.click(
                fn=self.structured_sale,
                inputs=[sale_product, sale_quantity, sale_customer, sale_price],
                outputs=[sale_product, transaction_results, sale_status]
            )
            
            # Search events
            search_btn.click(
                fn=self.search_records,
                inputs=[search_query, search_type],
                outputs=[search_results]
            )
            
            # Quick search events
            recent_purchases.click(
                fn=lambda: self.search_records("recent purchases", "Transactions"),
                outputs=[search_results]
            )
            
            recent_sales.click(
                fn=lambda: self.search_records("recent sales", "Transactions"),
                outputs=[search_results]
            )
            
            # Dashboard navigation events
            dash_new_purchase.click(fn=lambda: gr.Tabs.update(selected=2))
            dash_new_sale.click(fn=lambda: gr.Tabs.update(selected=2))
            dash_search.click(fn=lambda: gr.Tabs.update(selected=3))
            
            # Load initial dashboard data
            interface.load(
                fn=load_dashboard,
                outputs=[
                    metrics_purchases, metrics_sales, metrics_revenue, metrics_profit,
                    financial_chart, transaction_chart, products_chart, recent_table
                ]
            )
        
        return interface
    
    def launch(self, **kwargs):
        """Launch the Gradio interface."""
        interface = self.create_interface()
        
        # Default launch configuration
        launch_config = {
            'server_name': '0.0.0.0',
            'server_port': 7860,
            'share': False,
            'debug': False,
            'show_error': True,
            'quiet': False
        }
        
        # Update with any provided kwargs
        launch_config.update(kwargs)
        
        print("🚀 Starting Gradio GUI for Business Chatbot...")
        print(f"📱 Access the interface at: http://localhost:{launch_config['server_port']}")
        print("💡 Press Ctrl+C to stop the server")
        
        try:
            interface.launch(**launch_config)
        finally:
            # Clean up chatbot resources
            self.chatbot.close()

def main():
    """Main function to launch the Gradio interface."""
    gui = GradioInterface()
    gui.launch()

if __name__ == "__main__":
    main()