# ui.py - Gradio User Interface
import gradio as gr
import pandas as pd
import asyncio
from datetime import datetime
import pytz
import re
from config import APP_TITLE, EXAMPLE_QUERIES
from database import get_sample_data

def format_timestamp_to_cet(iso_timestamp):
    """Format ISO timestamp to readable CET date and hour."""
    try:
        if not iso_timestamp or iso_timestamp == '':
            return 'N/A'

        # Handle different ISO timestamp formats
        timestamp_str = str(iso_timestamp)

        # Replace 'Z' with '+00:00' for proper ISO format
        if timestamp_str.endswith('Z'):
            timestamp_str = timestamp_str[:-1] + '+00:00'

        # Handle timestamps with more than 6 microsecond digits
        # Python's fromisoformat() only supports up to 6 digits
        # Match pattern: YYYY-MM-DDTHH:MM:SS.microseconds+timezone
        match = re.match(r'(\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2})\.(\d+)(.*)', timestamp_str)
        if match:
            date_time_part, microseconds, timezone_part = match.groups()
            # Truncate microseconds to 6 digits max
            microseconds = microseconds[:6]
            timestamp_str = f"{date_time_part}.{microseconds}{timezone_part}"

        # Parse ISO timestamp
        dt = datetime.fromisoformat(timestamp_str)

        # Convert to CET timezone
        cet = pytz.timezone('CET')
        dt_cet = dt.astimezone(cet)

        # Format as readable string: "2025-09-13 14:30 CET"
        return dt_cet.strftime('%Y-%m-%d %H:%M CET')

    except (ValueError, AttributeError, TypeError) as e:
        # Fallback to original timestamp if parsing fails
        print(f"[DEBUG] Failed to format timestamp '{iso_timestamp}': {e}")
        return str(iso_timestamp) if iso_timestamp else 'N/A'

def create_interface(agent):
    """Create Gradio interface for the AR Collection Agent."""
    
    with gr.Blocks(
        title=APP_TITLE,
        theme=gr.themes.Soft(),
        # Add viewport meta tag for better mobile/iframe handling
        head="""<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no">""",
        css="""
        /* Theme tokens */
        .gradio-container {
            --bg: #0b1220;
            --panel: #0f172a;
            --panel-elevated: #111827;
            --border: #1f2937;
            --text: #e5e7eb;
            --muted: #9ca3af;
            --accent: #6366f1;
            --accent-2: #7c3aed;
            --bot-accent: #06b6d4;
            --bot-accent-2: #3b82f6;
        }
        
        /* Critical HF Spaces fixes - Force proper container behavior */
        .gradio-container,
        .gradio-container > *,
        body {
            max-width: 100% !important;
            width: 100% !important;
            overflow-x: hidden !important;
        }
        
        /* Force iframe content to be scrollable */
        #root,
        .gradio-container {
            position: relative !important;
            height: auto !important;
            min-height: 100vh !important;
            max-height: 100vh !important;  /* Constrain to viewport to prevent infinite scrolling */
        }

        /* App background */
        .gradio-container {
            background: var(--bg) !important;
            color: var(--text) !important;
            font-family: 'Inter', sans-serif;
        }

        /* Header with same gradient as send button */
        .header {
            background: #f50082 !important;
            color: #ffffff !important;
            text-align: center;
            padding: 1rem;
            margin-bottom: 1rem;
            border-radius: 16px !important;
            border: 1px solid var(--border) !important;
        }
        .header h1 { margin: 0; font-size: 1.4rem; font-weight: 600; }
        .header p { margin: .5rem 0 0 0; color: rgba(255,255,255,0.9); font-size: .95rem; }

        /* Sidebar styling */
        .sidebar {
            background: var(--panel) !important;
            border-radius: 16px !important;
            border: 1px solid var(--border) !important;
            padding: 16px !important;
            margin-right: 16px !important;
            min-width: 280px !important;
        }

        /* Main chat area */
        .chat-main {
            display: flex !important;
            flex-direction: column !important;
        }

        /* Chat container - flexible height for HF Spaces */
        .chat-container {
            min-height: 1000px !important;
            max-height: 1200px !important;
            height: auto !important;
            background: var(--panel) !important;
            border-radius: 16px !important;
            border: 1px solid var(--border) !important;
            box-shadow: 0 2px 12px rgba(0,0,0,.35) !important;
            overflow-y: auto !important;
            flex: 1 !important;
        }

        /* Message row alignment */
        .gradio-container .message-row {
            display: flex !important;
            width: 100% !important;
            margin: 12px 0 !important;
            padding: 0 16px !important;
        }

        /* BOT messages - left aligned */
        .gradio-container [data-testid="bot"] {
            display: flex !important;
            justify-content: flex-start !important;
            align-items: flex-start !important;
            gap: 12px !important;
            width: 100% !important;
        }

        /* USER messages - right aligned */
        .gradio-container [data-testid="user"] {
            display: flex !important;
            justify-content: flex-end !important;
            align-items: flex-start !important;
            flex-direction: row-reverse !important;
            gap: 12px !important;
            width: 100% !important;
        }

        /* Message bubble base */
        .gradio-container [data-testid="bot"] .message,
        .gradio-container [data-testid="user"] .message {
            background: transparent !important;
            border: none !important;
            padding: 0 !important;
            margin: 0 !important;
            display: block !important;
        }

        /* BOT bubble */
        .gradio-container [data-testid="bot"] .message > * {
            display: inline-block !important;
            background: linear-gradient(135deg, var(--bot-accent) 0%, var(--bot-accent-2) 100%) !important;
            color: #ffffff !important;
            border-radius: 18px 18px 18px 4px !important;
            padding: 12px 16px !important;
            max-width: 70% !important;
            word-wrap: break-word !important;
            word-break: break-word !important;
            white-space: pre-wrap !important;
            box-shadow: 0 2px 8px rgba(0,0,0,.3) !important;
        }

        /* USER bubble */
        .gradio-container [data-testid="user"] .message > * {
            display: inline-block !important;
            background: linear-gradient(135deg, var(--accent) 0%, var(--accent-2) 100%) !important;
            color: #ffffff !important;
            border-radius: 18px 18px 4px 18px !important;
            padding: 12px 16px !important;
            max-width: 70% !important;
            word-wrap: break-word !important;
            word-break: break-word !important;
            white-space: pre-wrap !important;
            box-shadow: 0 2px 8px rgba(0,0,0,.3) !important;
        }

        /* Text inside bubbles */
        .gradio-container .message p,
        .gradio-container .message div,
        .gradio-container .message span {
            margin: 0 !important;
            padding: 0 !important;
            color: inherit !important;
            background: transparent !important;
            word-wrap: break-word !important;
            white-space: pre-wrap !important;
        }

        /* Avatar styling */
        .gradio-container img[alt="user"],
        .gradio-container img[alt="assistant"],
        .gradio-container .avatar img {
            width: 36px !important;
            height: 36px !important;
            border-radius: 50% !important;
            border: 2px solid rgba(255,255,255,0.2) !important;
            box-shadow: 0 2px 8px rgba(0,0,0,.3) !important;
            flex-shrink: 0 !important;
        }

        /* Hide duplicate wrappers */
        .gradio-container .message-row .message .message,
        .gradio-container .prose,
        [class*="markdown"] {
            background: transparent !important;
            border: none !important;
            padding: 0 !important;
            margin: 0 !important;
            max-width: 100% !important;
        }

        /* Input area */
        .gradio-container input[type="text"],
        .gradio-container textarea {
            background: var(--panel-elevated) !important;
            color: var(--text) !important;
            border: 1px solid var(--border) !important;
            border-radius: 12px !important;
            padding: 12px 16px !important;
            font-size: 15px !important;
        }

        .gradio-container input[type="text"]:focus,
        .gradio-container textarea:focus {
            outline: none !important;
            border-color: var(--accent) !important;
            box-shadow: 0 0 0 3px rgba(99,102,241,0.15) !important;
        }

        /* Query Section Buttons - Base Styling */
        .gradio-container .gr-button {
            border-radius: 12px !important;
            padding: 14px 20px !important;
            font-weight: 600 !important;
            font-size: 14px !important;
            cursor: pointer !important;
            transition: all 0.2s ease-in-out !important;
            margin: 4px !important;
            box-shadow: 0 2px 8px rgba(0,0,0,.15) !important;
            border: none !important;
            color: #fff !important;
        }

        .gradio-container .gr-button:hover {
            transform: translateY(-2px) !important;
        }

        /* Overdue Analysis Buttons (Blue) - Force all buttons to have proper styling */
        .gradio-container .gr-button[variant="primary"],
        .gradio-container button[data-variant="primary"],
        .gradio-container button.primary {
            background: linear-gradient(135deg, #6366f1 0%, #7c3aed 100%) !important;
            color: #fff !important;
            border: none !important;
        }

        .gradio-container .gr-button[variant="primary"]:hover,
        .gradio-container button[data-variant="primary"]:hover,
        .gradio-container button.primary:hover {
            box-shadow: 0 8px 20px rgba(99,102,241,0.4) !important;
        }

        /* Customer Segmentation Buttons (Teal) - Force styling */
        .gradio-container .gr-button[variant="secondary"],
        .gradio-container button[data-variant="secondary"],
        .gradio-container button.secondary {
            background: linear-gradient(135deg, #06b6d4 0%, #3b82f6 100%) !important;
            color: #fff !important;
            border: none !important;
        }

        .gradio-container .gr-button[variant="secondary"]:hover,
        .gradio-container button[data-variant="secondary"]:hover,
        .gradio-container button.secondary:hover {
            box-shadow: 0 8px 20px rgba(6,182,212,0.4) !important;
        }

        /* Action Buttons (Red) - Force styling */
        .gradio-container .gr-button[variant="stop"],
        .gradio-container button[data-variant="stop"],
        .gradio-container button.stop {
            background: linear-gradient(135deg, #dc2626 0%, #b91c1c 100%) !important;
            color: #fff !important;
            border: none !important;
        }

        .gradio-container .gr-button[variant="stop"]:hover,
        .gradio-container button[data-variant="stop"]:hover,
        .gradio-container button.stop:hover {
            box-shadow: 0 8px 20px rgba(220,38,38,0.4) !important;
        }

        /* Custom class-based styling */
        .gradio-container .overdue-btn {
            background: linear-gradient(135deg, #6366f1 0%, #7c3aed 100%) !important;
            color: #fff !important;
            border: none !important;
        }

        .gradio-container .overdue-btn:hover {
            box-shadow: 0 8px 20px rgba(99,102,241,0.4) !important;
        }

        .gradio-container .segment-btn {
            background: linear-gradient(135deg, #06b6d4 0%, #3b82f6 100%) !important;
            color: #fff !important;
            border: none !important;
        }

        .gradio-container .segment-btn:hover {
            box-shadow: 0 8px 20px rgba(6,182,212,0.4) !important;
        }

        .gradio-container .action-btn {
            background: linear-gradient(135deg, #dc2626 0%, #b91c1c 100%) !important;
            color: #fff !important;
            border: none !important;
        }

        .gradio-container .action-btn:hover {
            box-shadow: 0 8px 20px rgba(220,38,38,0.4) !important;
        }

        /* Section Headers */
        .gradio-container h3 {
            color: var(--text) !important;
            font-size: 1.3rem !important;
            font-weight: 700 !important;
            margin: 24px 0 8px 0 !important;
            border-bottom: 2px solid var(--border) !important;
            padding-bottom: 8px !important;
        }

        /* Section Descriptions */
        .gradio-container p em {
            color: var(--muted) !important;
            font-style: italic !important;
            font-size: 0.95rem !important;
            margin-bottom: 16px !important;
        }

        /* Clear Button */
        .gradio-container .gr-button:has-text("Clear Chat") {
            background: linear-gradient(135deg, #6b7280 0%, #4b5563 100%) !important;
            color: #fff !important;
            margin-bottom: 20px !important;
        }

        /* Mock email styling (preserved from original) */
        .mock-email {
            background-color: #fffbeb; 
            border: 2px dashed #f59e0b; 
            padding: 1rem; 
            border-radius: 0.5rem;
        }

        /* Scrollbar */
        ::-webkit-scrollbar {
            width: 8px;
        }

        ::-webkit-scrollbar-track {
            background: transparent;
        }

        ::-webkit-scrollbar-thumb {
            background: var(--border);
            border-radius: 8px;
        }

        ::-webkit-scrollbar-thumb:hover {
            background: var(--muted);
        }

        /* Responsive tables */
        .gradio-container .dataframe {
            overflow-x: auto !important;
            overflow-y: auto !important;
            max-width: 100% !important;
            max-height: calc(100vh - 300px) !important;  /* Constrain DataFrame height */
        }

        .gradio-container table {
            min-width: 600px !important; /* Ensure minimum width for readability */
            font-size: 14px !important;
        }

        /* Specific constraints for tab DataFrames */
        .gradio-container .tabitem .dataframe {
            max-height: 400px !important;  /* Fixed max height for DataFrames */
            overflow-y: auto !important;
        }

        /* Constrain textboxes in tabs */
        .gradio-container .tabitem textarea,
        .gradio-container .tabitem .textbox textarea {
            max-height: 300px !important;  /* Fixed max height for textboxes */
            overflow-y: auto !important;
        }

        /* Prevent tab content from expanding */
        .gradio-container .tabitem [role="tabpanel"] > div {
            max-height: none !important;  /* Reset any inherited max-height */
            height: auto !important;      /* Allow natural sizing */
        }

        @media (max-width: 768px) {
            .gradio-container table {
                font-size: 12px !important;
            }

            /* Tighter constraints for mobile */
            .gradio-container .tabitem .dataframe {
                max-height: 300px !important;  /* Fixed height on mobile */
            }

            .gradio-container .tabitem textarea,
            .gradio-container .tabitem .textbox textarea {
                max-height: 200px !important;  /* Fixed height on mobile */
            }
        }

        /* Hugging Face Spaces specific fixes */
        .gradio-container {
            min-height: 100vh !important;
            overflow-y: visible !important;  /* Let tab content handle its own scrolling */
        }
        
        /* Fix for HF iframe container */
        body, html {
            height: auto !important;
            min-height: 100% !important;
            overflow-x: hidden !important;
            overflow-y: auto !important;
        }
        
        /* Ensure proper scrolling in tabs - Critical for HF Spaces */
        .gradio-container .tabitem,
        .gradio-container [role="tabpanel"] {
            min-height: 0 !important;                      /* Allow natural height */
            max-height: calc(100vh - 150px) !important;   /* Prevent infinite expansion */
            height: auto !important;                       /* Let content determine height */
            overflow-y: auto !important;                   /* Enable internal scrolling */
            overflow-x: hidden !important;
            position: relative !important;
        }
        
        /* Make sure tab content can expand */
        .gradio-container .tabs,
        .gradio-container [role="tablist"] + div {
            height: auto !important;
            min-height: auto !important;  /* Don't force full height */
        }

        /* Responsive Design */
        @media (max-width: 1024px) {
            /* Tablet and small laptop adjustments */
            .sidebar {
                min-width: 240px !important;
                margin-right: 12px !important;
                padding: 12px !important;
            }

            .chat-container {
                min-height: 350px !important;
                max-height: 500px !important;
            }

            .header h1 {
                font-size: 1.3rem !important;
            }
        }

        @media (max-width: 768px) {
            /* Mobile landscape and small tablets */
            .gradio-container .tabitem [role="tabpanel"] {
                flex-direction: column !important;
                min-height: calc(100vh - 200px) !important;  /* Adjust for mobile header space */
                max-height: calc(100vh - 200px) !important;
                height: calc(100vh - 200px) !important;
            }

            .sidebar {
                width: 100% !important;
                margin-right: 0 !important;
                margin-bottom: 16px !important;
                order: 2 !important; /* Chat first on mobile */
            }

            .chat-main {
                order: 1 !important;
                width: 100% !important;
            }

            .chat-container {
                min-height: 300px !important;
                max-height: 400px !important;
            }

            .gradio-container [data-testid="bot"] .message > *,
            .gradio-container [data-testid="user"] .message > * {
                max-width: 85% !important;
            }

            .header h1 {
                font-size: 1.2rem !important;
            }

            .gradio-container .gr-button {
                font-size: 13px !important;
                padding: 12px 16px !important;
            }

            /* Email Activity tab: stack columns vertically on mobile */
            .email-activity-row {
                flex-direction: column !important;
            }

            .email-activity-row .gradio-column {
                width: 100% !important;
            }
        }

        @media (max-width: 480px) {
            /* Mobile portrait */
            .chat-container {
                min-height: 250px !important;
                max-height: 350px !important;
            }

            .gradio-container .tabitem,
            .gradio-container [role="tabpanel"] {
                min-height: calc(100vh - 220px) !important;  /* More space for mobile UI */
                max-height: calc(100vh - 220px) !important;
                height: calc(100vh - 220px) !important;
            }

            .gradio-container [data-testid="bot"] .message > *,
            .gradio-container [data-testid="user"] .message > * {
                max-width: 90% !important;
                padding: 10px 12px !important;
            }

            .sidebar {
                padding: 8px !important;
            }

            .gradio-container .gr-button {
                font-size: 12px !important;
                padding: 10px 14px !important;
                margin: 2px !important;
            }

            .header h1 {
                font-size: 1.1rem !important;
            }

            .header p {
                font-size: 0.9rem !important;
            }
        }
        """
    ) as demo:
        
        # Header
        gr.HTML("""
            <div class="header">
                <h1>🏢 AR Collection Agent Demo</h1>
                <p>Educational demonstration of an AI agent for accounts receivable collections</p>
            </div>
        """)
        
        # Main Chat Tab
        with gr.Tab("💬 Chat with Agent"):
            with gr.Row():
                # Left Sidebar - Buttons
                with gr.Column(scale=1, elem_classes=["sidebar"]):
                    # Clear chat button
                    clear_btn = gr.Button("🗑️ Clear Chat", variant="secondary", scale=1)

                    # Organized Query Sections
                    gr.Markdown("### 📊 Overdue Analysis")
                    gr.Markdown("*Analyze overdue accounts*")

                    overdue_btn1 = gr.Button("Show me all late-payment customers", elem_classes=["overdue-btn"], scale=1)
                    overdue_btn2 = gr.Button("Which invoices are more than 30 days overdue?", elem_classes=["overdue-btn"], scale=1)
                    overdue_btn3 = gr.Button("Top 5 customers at risk of default", elem_classes=["overdue-btn"], scale=1)
                    overdue_btn4 = gr.Button("What's the most overdue account?", elem_classes=["overdue-btn"], scale=1)

                    gr.Markdown("### 👥 Customer Segmentation")
                    gr.Markdown("*Explore customer segments*")

                    segment_btn1 = gr.Button("Who are the VIPs with unpaid invoices?", elem_classes=["segment-btn"], scale=1)
                    segment_btn2 = gr.Button("Show me all Swedish customers with overdue invoices", elem_classes=["segment-btn"], scale=1)
                    segment_btn3 = gr.Button("Which customers are repeat late-payers in the last 12 months?", elem_classes=["segment-btn"], scale=1)
                    segment_btn4 = gr.Button("How much total money is outstanding?", elem_classes=["segment-btn"], scale=1)

                    gr.Markdown("### ⚡ Action Examples")
                    gr.Markdown("*AI Agent Email Campaigns*")

                    action_btn1 = gr.Button("Send collection emails to all overdue customers", elem_classes=["action-btn"], scale=1)
                    action_btn2 = gr.Button("Generate bulk emails for VIP customers only", elem_classes=["action-btn"], scale=1)
                    action_btn3 = gr.Button("Create targeted collection campaign for Swedish customers", elem_classes=["action-btn"], scale=1)
                    action_btn4 = gr.Button("Send emails to high-risk accounts only", elem_classes=["action-btn"], scale=1)

                # Right Side - Chat Interface
                with gr.Column(scale=2, elem_classes=["chat-main"]):
                    chatbot = gr.Chatbot(
                        height=None,
                        show_label=False,
                        bubble_full_width=False,
                        type='messages',
                        elem_classes=["chat-container"],
                        container=True
                    )
        
        # Email Activity Log Tab
        with gr.Tab("📧 Email Activity"):
            gr.Markdown("""
            ### Simulated Email History
            All emails shown here are **mock emails** generated for demonstration purposes.
            """)

            with gr.Row(elem_classes=["email-activity-row"]):
                # Left side - Email log
                with gr.Column(scale=1):
                    email_log = gr.DataFrame(
                        headers=["Timestamp", "Recipient", "Subject", "Status", "Invoice ID"],
                        label="Mock Emails Generated (Not Sent)",
                        wrap=True
                    )

                    with gr.Row():
                        refresh_email_btn = gr.Button("🔄 Refresh Log", scale=1)
                        export_btn = gr.Button("📥 Export to CSV", scale=1)

                # Right side - Email preview
                with gr.Column(scale=1):
                    email_preview = gr.Textbox(
                        label="Email Preview (Click on a row to view)",
                        lines=15,
                        max_lines=25,
                        interactive=False
                    )
        
        # Database Explorer Tab
        with gr.Tab("📊 Database Explorer"):
            gr.Markdown("### AR Collection Data")
            
            with gr.Row():
                with gr.Column(scale=4):
                    search_box = gr.Textbox(
                        placeholder="Search by company name, email, invoice ID, country...",
                        label="Search AR Data"
                    )
                with gr.Column(scale=1):
                    search_btn = gr.Button("🔍 Search", variant="primary")
                    refresh_db_btn = gr.Button("🔄 Refresh")
            
            # Main data display
            database_table = gr.DataFrame(
                label="Database Records",
                wrap=True,
                interactive=False
            )
            
            # Export functionality
            with gr.Row():
                export_db_btn = gr.Button("📥 Export Current View to CSV")
                exported_file = gr.File(label="Downloaded File", visible=False)
        
        # How It Works Tab
        with gr.Tab("ℹ️ How It Works"):
            gr.Markdown("""
            ## Understanding AI Agents: The Perceive-Think-Act Pattern
            
            This demo showcases how modern AI agents operate through an intelligent cycle:
            
            ### 1. 📊 **PERCEIVE** - Data Gathering
            - **Database Queries**: The agent uses SQL to query customer and invoice data
            - **Context Awareness**: Understands current date for calculating overdue periods
            - **Information Synthesis**: Combines multiple data sources for complete picture
            
            ### 2. 🧠 **THINK** - Analysis & Decision Making
            - **Pattern Recognition**: Identifies payment patterns and risk factors
            - **Priority Assessment**: Determines which accounts need immediate attention
            - **Strategy Selection**: Chooses appropriate collection approach based on:
                - Days overdue
                - Customer segment (VIP status)
                - Payment history
                - Outstanding amount
            
            ### 3. ⚡ **ACT** - Execute Actions
            - **Email Generation**: Creates personalized collection emails
            - **Tone Adjustment**: Varies communication based on severity
            - **Activity Logging**: Records all actions for audit trail
            """)
        
        # Event Handlers
        def handle_button_message(button_text, history):
            """Handle button click by processing message and returning complete history."""
            if not button_text:
                return history or []
            
            # Initialize history if needed
            history = history or []
            
            # Add user message to history
            history.append({"role": "user", "content": button_text})
            
            # Get agent response (handle async call)
            import asyncio
            try:
                response = asyncio.run(agent.process_message(button_text))
                # Add assistant response to history
                history.append({"role": "assistant", "content": response})
            except Exception as e:
                # Add error message if agent fails
                error_msg = f"I apologize, but I encountered an error: {str(e)}. Please try again."
                history.append({"role": "assistant", "content": error_msg})
            
            return history
        
        def add_user_message_from_button(button_text, history):
            """Step 1: Add user message immediately and return updated history."""
            if not button_text:
                return history or []
            
            # Initialize history if needed
            history = history or []
            
            # Add user message immediately 
            history.append({"role": "user", "content": button_text})
            # Add placeholder for assistant response
            history.append({"role": "assistant", "content": "🤔 Processing your request..."})
            
            return history
        
        def stream_agent_response(history):
            """Step 2: Stream agent response using generator for the last user message."""
            if not history or len(history) < 2:
                yield history
                return
            
            # Get the last user message (second to last in history)
            user_message = history[-2]["content"] if history[-2]["role"] == "user" else ""
            
            if not user_message:
                yield history
                return
            
            # Process agent response with streaming
            import asyncio
            try:
                response = asyncio.run(agent.process_message(user_message))
                
                # Update the assistant message progressively using line-by-line streaming
                # This preserves markdown formatting (bullet points, etc.)
                lines = response.split('\n')
                current_response = ""
                
                for i, line in enumerate(lines):
                    current_response += line
                    if i < len(lines) - 1:  # Add newline except for the last line
                        current_response += "\n"
                    
                    # Update the last message (assistant response)
                    history[-1] = {"role": "assistant", "content": current_response}
                    yield history
                    
                    # Small delay to show streaming effect (only for first few lines)
                    if i < 5:  # Only delay for first 5 lines to show streaming effect
                        import time
                        time.sleep(0.1)  # Slightly longer delay for line-by-line
                
            except Exception as e:
                # Replace placeholder with error message
                error_msg = f"I apologize, but I encountered an error: {str(e)}. Please try again."
                history[-1] = {"role": "assistant", "content": error_msg}
                yield history
        
        def get_email_log():
            """Refresh email log display using database storage."""
            from database import get_email_activity
            
            # Get emails from database (persistent storage)
            result = get_email_activity(page=0, page_size=100)  # Get latest 100 emails
            
            if result["success"] and result["data"]:
                df = pd.DataFrame(result["data"])
                # Ensure we have the required columns
                required_columns = ["timestamp", "recipient", "subject", "status", "invoice_id"]
                for col in required_columns:
                    if col not in df.columns:
                        df[col] = ""
                
                # Format timestamps to readable CET format
                if not df.empty and "timestamp" in df.columns:
                    df["timestamp"] = df["timestamp"].apply(format_timestamp_to_cet)
                
                return df[required_columns]
            else:
                # Fallback to in-memory storage if database fails
                email_history = agent.get_email_history()
                if email_history:
                    df = pd.DataFrame(email_history)
                    # Format timestamps for fallback data too
                    if not df.empty and "timestamp" in df.columns:
                        df["timestamp"] = df["timestamp"].apply(format_timestamp_to_cet)
                    return df[["timestamp", "recipient", "subject", "status", "invoice_id"]]
            
            return pd.DataFrame(columns=["timestamp", "recipient", "subject", "status", "invoice_id"])
        
        def export_emails():
            """Export email log to CSV."""
            df = get_email_log()
            if not df.empty:
                return gr.File.update(value=df.to_csv(index=False), visible=True)
            return None
        
        def preview_email(evt: gr.SelectData, log_data):
            """Preview selected email from database storage."""
            from database import get_email_activity
            
            try:
                # Get email data from database
                result = get_email_activity(page=0, page_size=100)
                
                if result["success"] and result["data"] and evt.index[0] < len(result["data"]):
                    email = result["data"][evt.index[0]]
                    body = email.get("body", "No content available")
                    
                    # Format the email preview with headers
                    preview_text = f"""From: AR Collection Agent
To: {email.get('recipient', 'N/A')}
Subject: {email.get('subject', 'N/A')}
Date: {email.get('timestamp', 'N/A')}
Status: {email.get('status', 'N/A')}
Tone: {email.get('tone', 'N/A')}

{body}"""
                    return preview_text
                else:
                    # Fallback to in-memory storage
                    email_history = agent.get_email_history()
                    if email_history and evt.index[0] < len(email_history):
                        email = email_history[evt.index[0]]
                        return email.get("body", "No content available")
            except Exception as e:
                return f"Error loading email preview: {str(e)}"
            
            return "Select an email to preview"
        
        def clear_chat():
            """Clear chat and email history."""
            agent.clear_history()
            return []  # Return empty messages list
        
        # Database Explorer Functions
        def load_database_data(search_term=""):
            """Load AR data using simplified direct table approach."""
            from database import get_basic_ar_data
            
            try:
                print(f"[DEBUG] Loading basic AR data with search: '{search_term}'")
                
                # Use simplified function - direct table queries
                result = get_basic_ar_data(page=0, page_size=100, search=search_term)
                
                print(f"[DEBUG] Query success: {result.get('success', False)}")
                
                if result["success"]:
                    data = result["data"]
                    print(f"[DEBUG] Retrieved {len(data)} records")
                    
                    if data:
                        df = pd.DataFrame(data)
                        print(f"[DEBUG] DataFrame shape: {df.shape}, columns: {list(df.columns)}")
                        return df
                    else:
                        print("[DEBUG] No data returned - empty result set")
                        return pd.DataFrame(columns=['Invoice ID', 'Company Name', 'Email', 'Country', 'Amount', 'Due Date', 'Days Overdue', 'VIP', 'Status'])
                else:
                    error_msg = result.get("error", "Unknown error")
                    print(f"[DEBUG] Query failed: {error_msg}")
                    return pd.DataFrame([{
                        'Error': 'Database Query Failed',
                        'Details': error_msg,
                        'Action': 'Check database connection and table structure'
                    }])
                    
            except Exception as e:
                print(f"[DEBUG] Exception in load_database_data: {str(e)}")
                return pd.DataFrame([{
                    'Error': 'Critical Exception',
                    'Details': str(e),
                    'Action': 'Check error logs and database setup'
                }])
        
        def export_database_view(search_term):
            """Export current AR data to CSV."""
            print(f"[DEBUG] Exporting data with search term: '{search_term}'")
            df = load_database_data(search_term)
            
            if not df.empty and 'Error' not in df.columns:
                filename = "ar_data_export.csv"
                csv_content = df.to_csv(index=False)
                print(f"[DEBUG] Exported {len(df)} rows to CSV")
                return gr.File.update(value=csv_content, filename=filename, visible=True)
            else:
                print("[DEBUG] No data to export or error occurred")
                return gr.File.update(visible=False)
        
        # Connect event handlers for all buttons
        # Overdue Analysis Examples
        overdue_btn1.click(lambda h: add_user_message_from_button("Show me all late-payment customers", h), [chatbot], [chatbot]).then(
            stream_agent_response, [chatbot], [chatbot]
        )
        overdue_btn2.click(lambda h: add_user_message_from_button("Which invoices are more than 30 days overdue?", h), [chatbot], [chatbot]).then(
            stream_agent_response, [chatbot], [chatbot]
        )
        overdue_btn3.click(lambda h: add_user_message_from_button("Top 5 customers at risk of default", h), [chatbot], [chatbot]).then(
            stream_agent_response, [chatbot], [chatbot]
        )
        overdue_btn4.click(lambda h: add_user_message_from_button("What's the most overdue account?", h), [chatbot], [chatbot]).then(
            stream_agent_response, [chatbot], [chatbot]
        )
        
        # Customer Segmentation Examples  
        segment_btn1.click(lambda h: add_user_message_from_button("Who are the VIPs with unpaid invoices?", h), [chatbot], [chatbot]).then(
            stream_agent_response, [chatbot], [chatbot]
        )
        segment_btn2.click(lambda h: add_user_message_from_button("Show me all Swedish customers with overdue invoices", h), [chatbot], [chatbot]).then(
            stream_agent_response, [chatbot], [chatbot]
        )
        segment_btn3.click(lambda h: add_user_message_from_button("Which customers are repeat late-payers in the last 12 months?", h), [chatbot], [chatbot]).then(
            stream_agent_response, [chatbot], [chatbot]
        )
        segment_btn4.click(lambda h: add_user_message_from_button("How much total money is outstanding?", h), [chatbot], [chatbot]).then(
            stream_agent_response, [chatbot], [chatbot]
        )
        
        # Action Examples - Bulk Email Campaigns
        action_btn1.click(lambda h: add_user_message_from_button("Send collection emails to all overdue customers", h), [chatbot], [chatbot]).then(
            stream_agent_response, [chatbot], [chatbot]
        )
        action_btn2.click(lambda h: add_user_message_from_button("Generate bulk emails for VIP customers only", h), [chatbot], [chatbot]).then(
            stream_agent_response, [chatbot], [chatbot]
        )
        action_btn3.click(lambda h: add_user_message_from_button("Create targeted collection campaign for Swedish customers", h), [chatbot], [chatbot]).then(
            stream_agent_response, [chatbot], [chatbot]
        )
        action_btn4.click(lambda h: add_user_message_from_button("Send emails to high-risk accounts only", h), [chatbot], [chatbot]).then(
            stream_agent_response, [chatbot], [chatbot]
        )
        
        # Clear chat button
        clear_btn.click(clear_chat, outputs=[chatbot])
        
        refresh_email_btn.click(get_email_log, outputs=email_log)
        email_log.select(preview_email, inputs=[email_log], outputs=email_preview)
        
        # Database Explorer Event Handlers
        # Search functionality
        search_btn.click(
            load_database_data,
            inputs=[search_box],
            outputs=[database_table]
        )
        
        # Refresh button
        refresh_db_btn.click(
            load_database_data,
            inputs=[search_box],
            outputs=[database_table]
        )
        
        # Export functionality
        export_db_btn.click(
            export_database_view,
            inputs=[search_box],
            outputs=exported_file
        )
        
        # Auto-refresh email log on load
        demo.load(get_email_log, outputs=email_log)
        
        # Load initial database data with consolidated AR view
        demo.load(
            lambda: load_database_data(""),
            outputs=[database_table]
        )
    
    return demo