import gradio as gr
from controller import Controller

controller = Controller()

# Custom CSS for modern styling
custom_css = """
.main-container {
    background: linear-gradient(135deg, #0f1419 0%, #1a1f2e 100%);
    min-height: 100vh;
}

.header-section {
    background: linear-gradient(135deg, #1e2a3a 0%, #2d3e50 100%);
    padding: 2rem;
    border-radius: 16px;
    margin-bottom: 2rem;
    border: 1px solid rgba(52, 152, 219, 0.2);
    box-shadow: 0 8px 32px rgba(0, 0, 0, 0.3);
}

.stats-card {
    background: rgba(30, 42, 58, 0.8);
    border: 1px solid rgba(52, 152, 219, 0.3);
    border-radius: 12px;
    padding: 1.5rem;
    backdrop-filter: blur(10px);
    transition: all 0.3s ease;
}

.stats-card:hover {
    border-color: rgba(52, 152, 219, 0.6);
    box-shadow: 0 4px 20px rgba(52, 152, 219, 0.2);
}

.query-section {
    background: rgba(30, 42, 58, 0.6);
    border-radius: 12px;
    padding: 1.5rem;
    border: 1px solid rgba(52, 152, 219, 0.2);
}

.tips-section {
    background: linear-gradient(135deg, rgba(46, 204, 113, 0.1) 0%, rgba(26, 188, 156, 0.1) 100%);
    border: 1px solid rgba(46, 204, 113, 0.3);
    border-radius: 12px;
    padding: 1.5rem;
}

.example-queries {
    background: rgba(155, 89, 182, 0.1);
    border: 1px solid rgba(155, 89, 182, 0.3);
    border-radius: 8px;
    padding: 1rem;
    margin-top: 1rem;
}

.feature-grid {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
    gap: 1rem;
    margin-top: 1rem;
}

.feature-item {
    background: rgba(52, 73, 94, 0.5);
    padding: 1rem;
    border-radius: 8px;
    border: 1px solid rgba(52, 152, 219, 0.2);
    text-align: center;
}

/* Button styling */
.primary-btn {
    background: linear-gradient(135deg, #3498db 0%, #2980b9 100%);
    border: none;
    border-radius: 8px;
    padding: 0.75rem 1.5rem;
    color: white;
    font-weight: 600;
    transition: all 0.3s ease;
}

.primary-btn:hover {
    background: linear-gradient(135deg, #2980b9 0%, #1f5582 100%);
    transform: translateY(-1px);
    box-shadow: 0 4px 12px rgba(52, 152, 219, 0.3);
}

/* File upload styling */
.file-upload-area {
    border: 2px dashed rgba(52, 152, 219, 0.5);
    border-radius: 12px;
    background: rgba(30, 42, 58, 0.3);
    transition: all 0.3s ease;
}

.file-upload-area:hover {
    border-color: rgba(52, 152, 219, 0.8);
    background: rgba(30, 42, 58, 0.5);
}
"""

def create_example_button(question_text):
    return question_text

with gr.Blocks(title="AI CSV → SQL Agent", css=custom_css, theme=gr.themes.Soft()) as demo:
    # Header
    with gr.Row():
        gr.HTML("""
        <div class="header-section">
            <div style="display: flex; align-items: center; gap: 16px; margin-bottom: 1rem;">
                <div style="font-size: 48px; background: linear-gradient(135deg, #3498db, #9b59b6); 
                           background-clip: text; -webkit-background-clip: text; -webkit-text-fill-color: transparent;">
                    📊
                </div>
                <div>
                    <h1 style="margin: 0; color: #ecf0f1; font-size: 2.5rem; font-weight: 700;">
                        AI CSV → SQL Agent
                    </h1>
                    <p style="color: #bdc3c7; font-size: 1.1rem; margin: 0.5rem 0;">
                        Upload your CSV, ask questions in natural language, and get instant insights powered by AI
                    </p>
                </div>
            </div>
            
            <div class="feature-grid">
                <div class="feature-item">
                    <div style="font-size: 1.5rem; margin-bottom: 0.5rem;">🤖</div>
                    <div style="color: #ecf0f1; font-weight: 600;">AI-Powered</div>
                    <div style="color: #95a5a6; font-size: 0.9rem;">Natural language to SQL</div>
                </div>
                <div class="feature-item">
                    <div style="font-size: 1.5rem; margin-bottom: 0.5rem;">⚡</div>
                    <div style="color: #ecf0f1; font-weight: 600;">Lightning Fast</div>
                    <div style="color: #95a5a6; font-size: 0.9rem;">Instant query execution</div>
                </div>
                <div class="feature-item">
                    <div style="font-size: 1.5rem; margin-bottom: 0.5rem;">📈</div>
                    <div style="color: #ecf0f1; font-weight: 600;">Smart Analysis</div>
                    <div style="color: #95a5a6; font-size: 0.9rem;">Intelligent data insights</div>
                </div>
                <div class="feature-item">
                    <div style="font-size: 1.5rem; margin-bottom: 0.5rem;">🔒</div>
                    <div style="color: #ecf0f1; font-weight: 600;">Secure</div>
                    <div style="color: #95a5a6; font-size: 0.9rem;">In-memory processing</div>
                </div>
            </div>
        </div>
        """)
    
    # Main content area
    with gr.Row():
        with gr.Column(scale=3):
            # File upload section
            with gr.Group():
                gr.HTML("<h3 style='color: #3498db; margin-bottom: 1rem;'>📁 Data Upload</h3>")
                file_input = gr.File(
                    label="Choose your CSV file", 
                    file_types=[".csv"],
                    elem_classes=["file-upload-area"]
                )
                load_status = gr.Textbox(
                    label="Upload Status", 
                    interactive=False,
                    show_label=False
                )
            
            # Dataset info section
            with gr.Group():
                gr.HTML("<h3 style='color: #2ecc71; margin-bottom: 1rem;'>📋 Dataset Information</h3>")
                dataset_info = gr.HTML("<div style='color: #95a5a6; padding: 1rem;'>Upload a CSV to see dataset information</div>")
            
            # Query section
            with gr.Group():
                gr.HTML("<h3 style='color: #e74c3c; margin-bottom: 1rem;'>❓ Ask Your Question</h3>")
                q = gr.Textbox(
                    label="", 
                    placeholder="e.g., What is the average age of employees in the Marketing department?",
                    lines=2,
                    show_label=False
                )
                
                with gr.Row():
                    ask_btn = gr.Button("🚀 Ask Question", variant="primary", size="lg")
                    clear_btn = gr.Button("🗑️ Clear", variant="secondary", size="lg")
        
        with gr.Column(scale=2):
            # Tips and examples section
            with gr.Group():
                gr.HTML("""
                <div class="tips-section">
                    <h3 style="color: #2ecc71; margin-top: 0;">💡 Tips for Better Results</h3>
                    <ul style="color: #ecf0f1; line-height: 1.6;">
                        <li>Use specific column names when known</li>
                        <li>Ask for aggregations (sum, average, count, etc.)</li>
                        <li>Try filtering queries (where, having)</li>
                        <li>Use comparison operators (>, <, =, between)</li>
                        <li>Request top N results for rankings</li>
                    </ul>
                </div>
                """)
            
            # Example queries
            with gr.Group():
                gr.HTML("<h3 style='color: #9b59b6; margin-bottom: 1rem;'>🎯 Example Queries</h3>")
                
                example_queries = [
                    "Show me the top 5 highest values",
                    "What's the average by category?", 
                    "Count records by status",
                    "Find records where amount > 1000",
                    "Show latest 10 entries"
                ]
                
                for i, query in enumerate(example_queries):
                    example_btn = gr.Button(
                        f"📝 {query}", 
                        variant="secondary", 
                        size="sm",
                        elem_id=f"example_{i}"
                    )
                    example_btn.click(
                        lambda x, q=query: q,
                        outputs=q
                    )
            
            # Query history section
            with gr.Group():
                gr.HTML("<h3 style='color: #f39c12; margin-bottom: 1rem;'>📚 Recent Queries</h3>")
                query_history = gr.HTML("<div style='color: #95a5a6; padding: 1rem;'>Your recent queries will appear here</div>")
    
    # Results section
    with gr.Row():
        with gr.Column():
            gr.HTML("<h3 style='color: #3498db; margin-bottom: 1rem;'>📊 Results</h3>")
            result_html = gr.HTML(
                """<div style='background: rgba(30, 42, 58, 0.6); border: 1px solid rgba(52, 152, 219, 0.2); 
                   border-radius: 12px; padding: 2rem; text-align: center; color: #95a5a6;'>
                   🎯 Upload a CSV and ask a question to see results here
                   </div>"""
            )
    
    # SQL Query Display
    with gr.Row():
        with gr.Column():
            gr.HTML("<h3 style='color: #e67e22; margin-bottom: 1rem;'>🔍 Generated SQL Query</h3>")
            sql_display = gr.Code(
                label="", 
                language="sql",
                show_label=False,
                interactive=False
            )
    
    # Footer
    gr.HTML("""
    <div style="text-align: center; margin-top: 3rem; padding: 2rem; 
               border-top: 1px solid rgba(52, 152, 219, 0.2);">
        <p style="color: #95a5a6; margin: 0;">
            Built with ❤️ using Gradio • Powered by AI • 
            <span style="color: #3498db;">Secure & Private Processing</span>
        </p>
    </div>
    """)
    
    # Event handlers
    def enhanced_load_csv(file):
        status = controller.load_csv(file)
        info = controller.get_dataset_info()
        return status, info
    
    def enhanced_question_handler(question):
        if not question.strip():
            return "Please enter a question", "", ""
        
        result_html_content, sql_query = controller.handle_question_with_sql(question)
        return result_html_content, sql_query, controller.add_to_history(question)
    
    def clear_inputs():
        return "", ""
    
    file_input.upload(
        enhanced_load_csv, 
        inputs=file_input, 
        outputs=[load_status, dataset_info]
    )
    
    ask_btn.click(
        enhanced_question_handler,
        inputs=q,
        outputs=[result_html, sql_display, query_history]
    )
    
    q.submit(
        enhanced_question_handler,
        inputs=q,
        outputs=[result_html, sql_display, query_history]
    )
    
    clear_btn.click(
        clear_inputs,
        outputs=[q, sql_display]
    )

if __name__ == "__main__":
    demo.launch(
        server_name="0.0.0.0",
        server_port=7860,
        share=False,
        show_error=True,
        quiet=False
    )