Spaces:

Rivalcoder
/

AquaSense

Sleeping

File size: 79,771 Bytes

36e3763

import dash
from dash import dcc, html, Input, Output, State, callback_context, dash_table
from dash.dependencies import ALL, MATCH
import dash_bootstrap_components as dbc
import pandas as pd
from chatbot_backend import GroqRAGChatbot
import json
import folium
from folium import plugins
import geopandas as gpd
from shapely import wkt
import base64
from io import StringIO
import os
import plotly.express as px
import plotly.graph_objects as go
from flask import Flask
from dash.exceptions import PreventUpdate
from datetime import datetime

# Initialize Flask server and Dash app
server = Flask(__name__)

# Serve static files (images)
@server.route('/Assests/<path:filename>')
def serve_image(filename):
    return server.send_static_file(f'Assests/{filename}')

app = dash.Dash(
    __name__, 
    external_stylesheets=[dbc.themes.MORPH],  # Changed to a more modern theme
    server=server,
    meta_tags=[{"name": "viewport", "content": "width=device-width, initial-scale=1.0"}]
)
app.title = "India Groundwater AI Chatbot"

# Function to encode image to base64
def encode_image(image_path):
    if os.path.exists(image_path):
        with open(image_path, "rb") as image_file:
            encoded_string = base64.b64encode(image_file.read()).decode()
            return f"data:image/png;base64,{encoded_string}"
    return None

# Encode the water droplet image
water_droplet_image = encode_image("Assests/image.png")

# Custom CSS for additional styling
app.index_string = '''
<!DOCTYPE html>
<html>
    <head>
        {%metas%}
        <title>{%title%}</title>
        {%favicon%}
        {%css%}
        <style>
            :root {
                --primary: #2E86AB;
                --secondary: #A23B72;
                --accent: #F18F01;
                --light: #F7F7FF;
                --dark: #2B2D42;
                --success: #4CB944;
                --warning: #F18F01;
                --danger: #E71D36;
                --bg: #f5f7fb;
                --text: #111827;
            }
            
            /* Dark theme overrides */
            #app-root.dark-mode {
                --primary: #60a5fa;
                --secondary: #f472b6;
                --accent: #f59e0b;
                --light: #111827;
                --dark: #e5e7eb;
                --success: #34d399;
                --warning: #f59e0b;
                --danger: #ef4444;
                --bg: #0b1220;
                --text: #e5e7eb;
            }
            
            /* Page background and default text */
            #app-root { background-color: var(--bg); color: var(--text); }
            #app-root > .container-fluid { min-height: 100vh; }
            
            .gradient-bg {
                background: linear-gradient(135deg, var(--primary) 0%, var(--secondary) 100%);
            }
            
            .card-hover {
                transition: transform 0.3s ease, box-shadow 0.3s ease;
            }
            
            .card-hover:hover {
                transform: translateY(-5px);
                box-shadow: 0 10px 20px rgba(0,0,0,0.1);
            }
            
            .feature-icon {
                font-size: 2.5rem;
                margin-bottom: 1rem;
                color: var(--primary);
            }
            
            .stat-number {
                font-size: 2.5rem;
                font-weight: 700;
                color: var(--primary);
            }
            
            .stat-label {
                font-size: 0.9rem;
                color: var(--dark);
                opacity: 0.8;
            }
            
            /* Water droplet animations */
            .water-droplet {
                animation: float 3s ease-in-out infinite, pulse 2s ease-in-out infinite;
                width: 250px;
                height: 250px;
                object-fit: cover;
                border-radius: 50%;
                filter: drop-shadow(0 8px 16px rgba(0,0,0,0.3));
                border: 4px solid rgba(255, 255, 255, 0.3);
            }
            
            @keyframes float {
                0%, 100% { transform: translateY(0px) rotate(0deg); }
                50% { transform: translateY(-15px) rotate(3deg); }
            }
            
            @keyframes pulse {
                0%, 100% { transform: scale(1); }
                50% { transform: scale(1.08); }
            }
            
            .water-droplet-container {
                display: flex;
                align-items: center;
                justify-content: center;
                height: 100%;
                min-height: 300px;
                padding: 1rem;
            }
            
            .chat-bubble-user {
                background-color: var(--primary);
                color: white;
                border-radius: 18px 18px 0 18px;
                padding: 12px 16px;
                margin: 5px 0;
                max-width: 80%;
                margin-left: auto;
            }
            
            .chat-bubble-bot {
                background-color: #eef2ff;
                color: #111827;
                border-radius: 18px 18px 18px 0;
                padding: 12px 16px;
                margin: 5px 0;
                max-width: 80%;
                margin-right: auto;
            }
            
            /* Modern chat layout */
            .chat-container { display: flex; flex-direction: column; height: 65vh; }
            .chat-messages { flex: 1; overflow-y: auto; padding: 12px; background: var(--light); border-radius: 8px; border: 1px solid #e5e7eb; }
            .message-row { display: flex; gap: 10px; margin: 8px 0; align-items: flex-end; }
            .message-row.user { flex-direction: row-reverse; }
            .avatar { width: 36px; height: 36px; border-radius: 50%; display: flex; align-items: center; justify-content: center; background: #dbeafe; color: #1d4ed8; font-weight: 700; flex: 0 0 36px; }
            .avatar.bot { background: #fef3c7; color: #92400e; }
            .bubble { padding: 10px 14px; border-radius: 14px; max-width: 75%; box-shadow: 0 1px 2px rgba(0,0,0,0.05); }
            .bubble.user { background: var(--primary); color: white; border-top-right-radius: 4px; }
            .bubble.bot { background: #ffffff; color: #111827; border-top-left-radius: 4px; }
            .timestamp { font-size: 10px; opacity: 0.7; margin-top: 4px; }
            .input-area { margin-top: 12px; }
            
            #app-root.dark-mode .chat-messages { background: #0f172a; border-color: #1f2937; }
            #app-root.dark-mode .bubble.bot { background: #0b1220; color: var(--text); }
            
            .navbar-brand {
                font-weight: 700;
                font-size: 1.5rem;
            }
            
            .section-title {
                position: relative;
                padding-bottom: 15px;
                margin-bottom: 25px;
                font-weight: 700;
            }
            
            .section-title:after {
                content: '';
                position: absolute;
                bottom: 0;
                left: 0;
                width: 50px;
                height: 3px;
                background: var(--primary);
            }
            
            .btn-primary {
                background-color: var(--primary);
                border-color: var(--primary);
            }
            
            .btn-primary:hover {
                background-color: #1D6A8F;
                border-color: #1D6A8F;
            }
            
            .btn-accent {
                background-color: var(--accent);
                border-color: var(--accent);
                color: white;
            }
            
            .btn-accent:hover {
                background-color: #D97F00;
                border-color: #D97F00;
                color: white;
            }
            
            /* General readability and overflow safety */
            body, #app-root, .card, .navbar, .nav-link, .tab-content, .tab-pane, .card-text, .card-title {
                word-wrap: break-word;
                overflow-wrap: anywhere;
            }
            
            /* Light theme component backgrounds */
            .card { background-color: #ffffff; }
            .bg-light { background-color: #f1f5f9 !important; }
            .text-muted { color: #6b7280 !important; }
            
            /* Dark mode component overrides */
            #app-root.dark-mode .navbar, #app-root.dark-mode .navbar-light { background-color: #111827 !important; }
            #app-root.dark-mode .navbar-brand, #app-root.dark-mode .nav-link { color: var(--text) !important; }
            #app-root.dark-mode .card { background-color: #111827; color: var(--text); border-color: #1f2937; }
            #app-root.dark-mode .bg-light { background-color: #0f172a !important; color: var(--text) !important; }
            #app-root.dark-mode .text-muted { color: #9ca3af !important; }
            #app-root.dark-mode .dropdown-menu { background-color: #0f172a; color: var(--text); }
            #app-root.dark-mode .form-control, 
            #app-root.dark-mode .dbc-input, 
            #app-root.dark-mode .Select-control, 
            #app-root.dark-mode .Select-menu-outer { background-color: #0f172a; color: var(--text); }
            #app-root.dark-mode .chat-bubble-bot { background-color: #0f172a; color: var(--text); }
            #app-root.dark-mode .chat-bubble-user { background-color: var(--primary); color: #0b1220; }
            #app-root.dark-mode .tab-content, #app-root.dark-mode .tab-pane { background-color: transparent; }
            #app-root.dark-mode .table { color: var(--text); }
            
            /* Chat history container */
            #chat-history { background-color: #f8f9fa; }
            #app-root.dark-mode #chat-history { background-color: #0b1220; border-color: #1f2937; }
        </style>
    </head>
    <body>
        {%app_entry%}
        <footer>
            {%config%}
            {%scripts%}
            {%renderer%}
        </footer>
    </body>
</html>
'''

try:
    chatbot = GroqRAGChatbot()
    CHATBOT_READY = True
except Exception as e:
    print(f"Chatbot initialization error: {e}")
    CHATBOT_READY = False

# Default placeholder figure for Forecasts tab so it never renders blank
def build_placeholder_forecast_figure(title_suffix="No time-series data"):
    try:
        x_vals = list(range(1, 11))
        y_vals = list(range(1, 11))
        fig = go.Figure()
        fig.add_trace(go.Scatter(
            x=x_vals,
            y=y_vals,
            mode='lines+markers',
            name='Placeholder',
            marker=dict(color="#2563eb"),
            line=dict(color="#2563eb")
        ))
        fig.update_layout(
            title=f"Forecast Preview ({title_suffix})",
            height=360,
            margin=dict(l=10, r=10, t=50, b=10),
            plot_bgcolor="#ffffff",
            paper_bgcolor="#ffffff"
        )
        return fig
    except Exception:
        return go.Figure()

# App Layout
app.layout = html.Div([
    # Top Navbar - Modern Design
    dbc.Navbar(
        dbc.Container([
            html.A(
                dbc.Row([
                    dbc.Col(html.Span("💧", style={"fontSize": "28px"})),
                    dbc.Col(dbc.NavbarBrand("India Groundwater AI", className="ms-2")),
                ], align="center", className="g-0"),
                href="#",
                style={"textDecoration": "none"}
            ),
            dbc.NavbarToggler(id="navbar-toggler"),
            dbc.Collapse(
                dbc.Nav([], className="ms-auto", navbar=True),
                id="navbar-collapse",
                navbar=True,
            ),
            dbc.Switch(id="theme-switch", value=False, label="Dark", className="ms-3"),
        ], fluid=True),
        color="light",
        dark=False,
        sticky="top",
        className="mb-4 shadow-sm"
    ),
    
    # Main Content
    html.Div([
        # Top-level Tabs
        dcc.Tabs(id="main-tabs", value="tab-home", children=[
            # Landing Page - Completely Redesigned
            dcc.Tab(label="Home", value="tab-home", children=[
                dbc.Container(fluid=True, children=[
                    # Hero Section
                    dbc.Row([
                        dbc.Col([
                            html.Div([
                                html.Img(src=water_droplet_image if water_droplet_image else "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzAwIiBoZWlnaHQ9IjMwMCIgeG1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnIj4KICA8ZGVmcz4KICAgIDxsaW5lYXJHcmFkaWVudCBpZD0iZHJvcGxldCIgeDE9IjAlIiB5MT0iMCUiIHgyPSIwJSIgeTI9IjEwMCUiPgogICAgICA8c3RvcCBvZmZzZXQ9IjAlIiBzdHlsZT0ic3RvcC1jb2xvcjojZmZmZmZmO3N0b3Atb3BhY2l0eToxIiAvPgogICAgICA8c3RvcCBvZmZzZXQ9IjcwJSIgc3R5bGU9InN0b3AtY29sb3I6IzAwYjNjYztzdG9wLW9wYWNpdHk6MSIgLz4KICAgIDwvbGluZWFyR3JhZGllbnQ+CiAgPC9kZWZzPgogIDxwYXRoIGQ9Ik0xNTAgMTVMMTgwIDYwTDE1MCA5MEwxMjAgNjBaIiBmaWxsPSJ1cmwoI2Ryb3BsZXQpIi8+CiAgPHRleHQgeD0iMTUwIiB5PSIxMzUiIHRleHQtYW5jaG9yPSJtaWRkbGUiIGZpbGw9IndoaXRlIiBmb250LWZhbWlseT0iQXJpYWwsIHNhbnMtc2VyaWYiIGZvbnQtc2l6ZT0iMjAiPklOR1JFUzwvdGV4dD4KPC9zdmc+", 
                                        className="water-droplet",
                                        alt="Water Droplet Icon")
                            ], className="water-droplet-container")
                        ], md=5, lg=5, className="d-flex align-items-center justify-content-center"),
                        dbc.Col([
                            html.Div([
                                html.H1("India Groundwater Intelligence Platform", className="display-4 fw-bold mb-4"),
                                html.P("Advanced AI-powered insights for sustainable groundwater management across India", 
                                      className="lead mb-4 text-black"),
                                dbc.Button("Get Started", id="hero-cta", color="primary", size="lg", 
                                          className="me-2 text-white", href="#tab-explore"),
                                dbc.Button("Live Demo", id="hero-demo", color="black", size="lg", 
                                          outline=True, href="#tab-chat"),
                            ], className="p-5 rounded-3", 
                            style={"backgroundColor": "rgba(255,255,255,0.9)"})
                        ], md=7, lg=7)
                    ], className="gradient-bg py-5 mb-5 text-white align-items-center", 
                    style={"borderRadius": "0 0 30px 30px", "minHeight": "500px"}),
                    
                    # Stats Section
                    dbc.Row([
                        dbc.Col([
                            html.Div([
                                html.Div("🌍", className="feature-icon"),
                                html.Div(id="total-districts", children="--", className="stat-number"),
                                html.Div("Districts Covered", className="stat-label")
                            ], className="text-center p-4 bg-white shadow-sm rounded-3 card-hover")
                        ], md=3, className="mb-3"),
                        dbc.Col([
                            html.Div([
                                html.Div("💧", className="feature-icon"),
                                html.Div(id="avg-development", children="--", className="stat-number"),
                                html.Div("Avg Development %", className="stat-label")
                            ], className="text-center p-4 bg-white shadow-sm rounded-3 card-hover")
                        ], md=3, className="mb-3"),
                        dbc.Col([
                            html.Div([
                                html.Div("⚠️", className="feature-icon"),
                                html.Div(id="over-exploited", children="--", className="stat-number"),
                                html.Div("Over-exploited Areas", className="stat-label")
                            ], className="text-center p-4 bg-white shadow-sm rounded-3 card-hover")
                        ], md=3, className="mb-3"),
                        dbc.Col([
                            html.Div([
                                html.Div("🔍", className="feature-icon"),
                                html.Div(id="critical-districts", children="--", className="stat-number"),
                                html.Div("Critical Status", className="stat-label")
                            ], className="text-center p-4 bg-white shadow-sm rounded-3 card-hover")
                        ], md=3, className="mb-3"),
                    ], className="mb-5"),
                    
                    # Features Section
                    dbc.Row([
                        dbc.Col([
                            html.H2("Key Features", className="section-title")
                        ], width=12)
                    ], className="mb-4"),
                    
                    dbc.Row([
                        dbc.Col([
                            dbc.Card([
                                dbc.CardBody([
                                    html.Div("🗺️", style={"fontSize": "3rem", "textAlign": "center", "marginBottom": "1rem"}),
                                    html.H4("Interactive Maps", className="card-title"),
                                    html.P("Explore groundwater data through interactive visualizations with detailed district-level information.", 
                                          className="card-text text-black")
                                ])
                            ], className="h-100 card-hover border-0 shadow-sm")
                        ], md=4, className="mb-4"),
                        dbc.Col([
                            dbc.Card([
                                dbc.CardBody([
                                    html.Div("🤖", style={"fontSize": "3rem", "textAlign": "center", "marginBottom": "1rem"}),
                                    html.H4("AI-Powered Insights", className="card-title"),
                                    html.P("Get answers to complex groundwater questions using our advanced natural language processing capabilities.", 
                                          className="card-text")
                                ])
                            ], className="h-100 card-hover border-0 shadow-sm")
                        ], md=4, className="mb-4"),
                        dbc.Col([
                            dbc.Card([
                                dbc.CardBody([
                                    html.Div("📊", style={"fontSize": "3rem", "textAlign": "center", "marginBottom": "1rem"}),
                                    html.H4("Advanced Analytics", className="card-title"),
                                    html.P("Dive deep into trends, forecasts, and comprehensive reports on groundwater availability and usage patterns.", 
                                          className="card-text")
                                ])
                            ], className="h-100 card-hover border-0 shadow-sm")
                        ], md=4, className="mb-4"),
                    ], className="mb-5"),
                    
                    # How It Works Section
                    dbc.Row([
                        dbc.Col([
                            html.H2("How It Works", className="section-title")
                        ], width=12)
                    ], className="mb-4"),
                    
                    dbc.Row([
                        dbc.Col([
                            dbc.Card([
                                dbc.CardBody([
                                    html.Div("1", style={
                                        "width": "40px", 
                                        "height": "40px", 
                                        "backgroundColor": "var(--primary)", 
                                        "color": "white", 
                                        "borderRadius": "50%", 
                                        "display": "flex", 
                                        "alignItems": "center", 
                                        "justifyContent": "center",
                                        "marginBottom": "1rem",
                                        "fontWeight": "bold"
                                    }),
                                    html.H4("Ask a Question", className="card-title"),
                                    html.P("Type your question about groundwater data in natural language - no technical knowledge required.", 
                                          className="card-text")
                                ])
                            ], className="h-100 card-hover border-0 shadow-sm")
                        ], md=4, className="mb-4"),
                        dbc.Col([
                            dbc.Card([
                                dbc.CardBody([
                                    html.Div("2", style={
                                        "width": "40px", 
                                        "height": "40px", 
                                        "backgroundColor": "var(--primary)", 
                                        "color": "white", 
                                        "borderRadius": "50%", 
                                        "display": "flex", 
                                        "alignItems": "center", 
                                        "justifyContent": "center",
                                        "marginBottom": "1rem",
                                        "fontWeight": "bold"
                                    }),
                                    html.H4("Get AI Analysis", className="card-title"),
                                    html.P("Our AI processes your query, analyzes the groundwater database, and extracts relevant insights.", 
                                          className="card-text")
                                ])
                            ], className="h-100 card-hover border-0 shadow-sm")
                        ], md=4, className="mb-4"),
                        dbc.Col([
                            dbc.Card([
                                dbc.CardBody([
                                    html.Div("3", style={
                                        "width": "40px", 
                                        "height": "40px", 
                                        "backgroundColor": "var(--primary)", 
                                        "color": "white", 
                                        "borderRadius": "50%", 
                                        "display": "flex", 
                                        "alignItems": "center", 
                                        "justifyContent": "center",
                                        "marginBottom": "1rem",
                                        "fontWeight": "bold"
                                    }),
                                    html.H4("Explore Results", className="card-title"),
                                    html.P("View interactive maps, visualizations, and detailed reports based on the AI's findings.", 
                                          className="card-text")
                                ])
                            ], className="h-100 card-hover border-0 shadow-sm")
                        ], md=4, className="mb-4"),
                    ], className="mb-5"),
                    
                    # Call to Action
                    dbc.Row([
                        dbc.Col([
                            dbc.Card([
                                dbc.CardBody([
                                    html.H3("Ready to explore India's groundwater data?", className="text-center mb-4"),
                                    html.Div([
                                        dbc.Button("Start Exploring", color="primary", size="lg", className="me-3 text-white bg-blue", href="#tab-explore"),
                                        dbc.Button("Chat with AI", color="", size="lg", outline=True, className="text-gray-600", href="#tab-chat"),
                                    ], className="d-flex justify-content-center")
                                ])
                            ], className="border-0 shadow-sm bg-blue")
                        ], width=12)
                    ]),
                    
                    # Footer
                    dbc.Row([
                        dbc.Col([
                            html.Hr(),
                            html.P("India Groundwater AI Platform - Powered by Advanced Analytics and AI", 
                                  className="text-center text-muted mt-4")
                        ], width=12)
                    ], className="mt-5")
                ])
            ]),

            # Explore Page (Map, Viz, Results, Details)
            dcc.Tab(label="Explore", value="tab-explore", children=[
                dbc.Container(fluid=True, children=[
                    dbc.Row([
                        # Left Sidebar (Quick Stats)
                        dbc.Col([
                            dbc.Card([
                                dbc.CardHeader(html.H5("📊 Quick Stats", className="mb-0")),
                                dbc.CardBody([
                                    dbc.Row([
                                        dbc.Col([
                                            html.Div([
                                                html.Div("Total Districts", className="small text-muted"),
                                                html.H3(id="total-districts-explore", children="--", className="mb-0 text-primary")
                                            ], className="p-3 bg-light rounded-3")
                                        ], width=6, className="mb-3"),
                                        dbc.Col([
                                            html.Div([
                                                html.Div("Avg Development %", className="small text-muted"),
                                                html.H3(id="avg-development-explore", children="--", className="mb-0 text-warning")
                                            ], className="p-3 bg-light rounded-3")
                                        ], width=6, className="mb-3")
                                    ]),
                                    html.Hr(className="my-3"),
                                    dbc.Row([
                                        dbc.Col([
                                            html.Div([
                                                html.Div("Over-exploited", className="small text-muted"),
                                                html.H4(id="over-exploited-explore", children="--", className="mb-0 text-danger")
                                            ], className="p-3 bg-light rounded-3")
                                        ], width=6, className="mb-3"),
                                        dbc.Col([
                                            html.Div([
                                                html.Div("Critical Status", className="small text-muted"),
                                                html.H4(id="critical-districts-explore", children="--", className="mb-0 text-warning")
                                            ], className="p-3 bg-light rounded-3")
                                        ], width=6, className="mb-3")
                                    ])
                                ])
                            ], className="mb-4 shadow-sm"),
                        ], width=3, style={"position": "sticky", "top": "20px", "height": "calc(100vh - 120px)", "overflowY": "auto"}),

                        # Right Content Tabs
                        dbc.Col([
                            dbc.Tabs([
                                dbc.Tab(label="Map", tab_id="tab-map", children=[
                                    dbc.Card([
                                        dbc.CardHeader([
                                            html.H5("🗺️ Underground Water Coverage Map", className="mb-0"),
                                            dbc.Badge(id="map-status", children="No Data", color="secondary", className="float-end")
                                        ]),
                                        dbc.CardBody([
                                            dcc.Loading(
                                                id="map-loading",
                                                children=[
                                                    html.Div(
                                                        id="groundwater-map",
                                                        style={"height": "500px", "width": "100%", "borderRadius": "8px", "overflow": "hidden"}
                                                    )
                                                ],
                                                type="circle"
                                            ),
                                            html.Div([
                                                dbc.Button("Download Map (HTML)", id="download-map-html-btn", color="primary", size="sm", className="mt-3"),
                                                dcc.Download(id="download-map-html")
                                            ], className="mt-2")
                                        ])
                                    ], className="mb-4 shadow-sm")
                                ]),
                                dbc.Tab(label="Visualization", tab_id="tab-viz", children=[
                                    dbc.Card([
                                        dbc.CardHeader(html.H5("📈 Data Visualization", className="mb-0")),
                                        dbc.CardBody([
                                            dcc.Loading(
                                                id="viz-loading",
                                                type="circle",
                                                children=[
                                                    dcc.Graph(id="viz-graph", figure=go.Figure(), 
                                                             config={"displaylogo": False, "toImageButtonOptions": {"format": "png", "filename": "groundwater_visualization"}}),
                                                    html.Hr(),
                                                    dcc.Graph(id="viz-graph-2", figure=go.Figure(), 
                                                             config={"displaylogo": False, "toImageButtonOptions": {"format": "png", "filename": "groundwater_visualization_2"}})
                                                ]
                                            ),
                                            html.Div([
                                                dbc.Button("Download CSV", id="download-csv-btn", color="primary", size="sm", className="me-2"),
                                                dcc.Download(id="download-csv"),
                                                dbc.Button("Download PNG", id="download-png-btn", color="primary", size="sm")
                                            ], className="mt-3")
                                        ])
                                    ], className="mb-4 shadow-sm")
                                ]),
                                dbc.Tab(label="Results", tab_id="tab-results", children=[
                                    dbc.Card([
                                        dbc.CardHeader(html.H5("📋 Query Results", className="mb-0")),
                                        dbc.CardBody([
                                            html.Div(id="results-table")
                                        ])
                                    ], className="mb-4 shadow-sm")
                                ]),
                                dbc.Tab(label="Details", tab_id="tab-details", children=[
                                    dbc.Card([
                                        dbc.CardHeader(html.H5("🔍 Query Details", className="mb-0")),
                                        dbc.CardBody([
                                            dbc.Collapse([
                                                dbc.Card([
                                                    dbc.CardBody([
                                                        html.Pre(id="intent-display", style={"fontSize": "12px", "backgroundColor": "#f8f9fa", "padding": "15px", "borderRadius": "5px"})
                                                    ])
                                                ])
                                            ], id="details-collapse", is_open=False),
                                            dbc.Button("Show/Hide Details", id="toggle-details", color="primary", className="mt-3", size="sm")
                                        ])
                                    ], className="mb-4 shadow-sm")
                                ])
                            ])
                        ], width=9)
                    ])
                ])
            ]),

            # Visualizations Page (dedicated)
            dcc.Tab(label="Visualizations", value="tab-visualizations", children=[
                dbc.Container(fluid=True, children=[
                    dbc.Row([
                        dbc.Col([
                            dbc.Card([
                                dbc.CardHeader(html.H5("📈 Visual Insights", className="mb-0")),
                                dbc.CardBody([
                                    dcc.Loading(
                                        id="viz-loading-standalone",
                                        type="circle",
                                        children=[
                                            dcc.Graph(id="viz-graph-standalone", figure=go.Figure(), 
                                                     config={"displaylogo": False, "toImageButtonOptions": {"format": "png", "filename": "visualization_standalone"}}),
                                            html.Hr(),
                                            dcc.Graph(id="viz-graph-2-standalone", figure=go.Figure(), 
                                                     config={"displaylogo": False, "toImageButtonOptions": {"format": "png", "filename": "visualization_standalone_2"}})
                                        ]
                                    ),
                                    html.Div([
                                        dbc.Button("Download CSV", id="download-csv-btn-standalone", color="primary", size="sm", className="me-2"),
                                        dcc.Download(id="download-csv-standalone"),
                                        dbc.Button("Download PNG", id="download-png-btn-standalone", color="primary", size="sm")
                                    ], className="mt-3")
                                ])
                            ], className="mb-4 shadow-sm")
                        ], width=12)
                    ])
                ])
            ]),

            # Reports Page
            dcc.Tab(label="Reports", value="tab-reports", children=[
                dbc.Container(fluid=True, children=[
                    dbc.Row([
                        dbc.Col([
                            dbc.Card([
                                dbc.CardHeader(html.H5("📄 Generate Report", className="mb-0")),
                                dbc.CardBody([
                                    html.P("Download current results as CSV report."),
                                    dbc.Button("Download CSV Report", id="download-csv-btn-report",className="btn-primary text-white", color="blue"),
                                    dcc.Download(id="download-csv-report")
                                ])
                            ], className="shadow-sm")
                        ], width=6)
                    ], className="p-3")
                ])
            ]),

            # Forecasts Page
            dcc.Tab(label="Forecasts", value="tab-forecasts", children=[
                dbc.Container(fluid=True, children=[
                    dbc.Row([
                        dbc.Col([
                            dbc.Card([
                                dbc.CardHeader(html.H5("🔮 Forecast (Preview)", className="mb-0")),
                                dbc.CardBody([
                                    html.P("Forecasting requires time-series data. Showing a placeholder visualization of the current metric."),
                                    dcc.Graph(id="viz-graph-forecast", figure=build_placeholder_forecast_figure())
                                ])
                            ], className="shadow-sm")
                        ], width=12)
                    ], className="p-3")
                ])
            ]),

            # Knowledge Cards Page
            dcc.Tab(label="Knowledge", value="tab-knowledge", children=[
                dbc.Container(fluid=True, children=[
                    dbc.Row([
                        dbc.Col([
                            dbc.Card([
                                dbc.CardHeader(html.H5("🧠 Insights", className="mb-0 text-black")),
                                dbc.CardBody([
                                    html.Div(id="knowledge-cards", className="d-flex flex-column gap-2 text-black")
                                ])
                            ], className="shadow-sm")
                        ], width=12)
                    ], className="p-3")
                ])
            ]),

            # Chat Page
            dcc.Tab(label="Chat", value="tab-chat", children=[
                dbc.Container(fluid=True, children=[
                    dbc.Row([
                        dbc.Col([
                            dbc.Card([
                                dbc.CardHeader([
                                    html.H5("💬 Ask Your Question", className="mb-0"),
                                    dbc.Badge(id="status-indicator", children="Ready", 
                                             color="success", className="float-end")
                                ]),
                                dbc.CardBody([
                                    dbc.Row([
                                        dbc.Col([
                                            dbc.Label("Language"),
                                            dcc.Dropdown(id="language-select", options=[
                                                {"label": "English", "value": "en"},
                                                {"label": "Hindi", "value": "hi"},
                                                {"label": "Telugu", "value": "te"},
                                                {"label": "Tamil", "value": "ta"},
                                            ], value="en", clearable=False, className="mb-3")
                                        ])
                                    ]),
                                    dbc.InputGroup([
                                        dbc.Input(
                                            id="chat-input",
                                            placeholder="Ask about groundwater data...",
                                            type="text",
                                            className="form-control"
                                        ),
                                        dbc.Button("Send", id="send-btn", color="primary", n_clicks=0),
                                        dbc.Button("New Chat", id="new-chat-btn", color="secondary", n_clicks=0, className="ms-2")
                                    ], className="mb-3"),
                                    html.P("Quick examples:", className="small text-muted mb-2"),
                                    dbc.ButtonGroup([
                                        dbc.Button("Highest Draft", id="ex1", color="outline-primary", size="sm"),
                                        dbc.Button("Over-exploited", id="ex2", color="outline-primary", size="sm"),
                                        dbc.Button("Compare Districts", id="ex3", color="outline-primary", size="sm"),
                                        dbc.Button("Underground Coverage", id="ex4", color="outline-primary", size="sm")
                                    ], className="mb-3 flex-wrap"),
                                    html.Hr(),
                                    html.Div(id="chat-history", 
                                            style={"height": "60vh", "overflowY": "auto", "border": "1px solid #dee2e6", 
                                                   "borderRadius": "0.375rem", "padding": "10px", "backgroundColor": "#f8f9fa"})
                                ])
                            ], className="shadow-sm")
                        ], width=8, className="mx-auto")
                    ])
                ])
            ])
        ]),
    ]),
    
    # Toast container
    html.Div(id="toast-container"),
    
    # Data Stores
    dcc.Store(id="chat-data", data=[]),
    dcc.Store(id="current-result", data={}),
    dcc.Store(id="followup-store", data=None),
    
    # Auto-refresh interval for stats
    dcc.Interval(id="stats-interval", interval=30000, n_intervals=0)
], id="app-root", style={"minHeight": "100vh"})

# Theme toggle callback: apply dark-mode class to root
@app.callback(
    Output("app-root", "className"),
    Input("theme-switch", "value")
)
def toggle_theme(is_dark):
    if is_dark is None:
        raise PreventUpdate
    return "dark-mode" if is_dark else ""

# Follow-up buttons handler: write clicked text into followup-store
@app.callback(
    Output("followup-store", "data"),
    Input({"type": "followup", "index": ALL}, "n_clicks"),
    State({"type": "followup", "index": ALL}, "children"),
    prevent_initial_call=True
)
def handle_followup_click(n_clicks_list, labels):
    try:
        if not n_clicks_list:
            raise dash.exceptions.PreventUpdate
        # Find which button was clicked last
        for idx, n in enumerate(n_clicks_list):
            # Any positive click
            if n and n > 0:
                return labels[idx]
        raise dash.exceptions.PreventUpdate
    except Exception:
        raise dash.exceptions.PreventUpdate

# Callback for example buttons
@app.callback(
    Output("chat-input", "value"),
    [Input("ex1", "n_clicks"), Input("ex2", "n_clicks"), 
     Input("ex3", "n_clicks"), Input("ex4", "n_clicks")]
)
def set_example_query(btn1, btn2, btn3, btn4):
    ctx = callback_context
    if not ctx.triggered:
        return ""
    
    button_id = ctx.triggered[0]["prop_id"].split(".")[0]
    examples = {
        "ex1": "Which districts have the highest groundwater draft?",
        "ex2": "Show me over-exploited districts with development over 100%",
        "ex3": "Compare groundwater availability between Chennai and Coimbatore",
        "ex4": "Show districts with largest underground water coverage areas"
    }
    return examples.get(button_id, "")

# Main chat processing callback
@app.callback(
    [Output("chat-history", "children"),
     Output("groundwater-map", "children"),
     Output("results-table", "children"),
     Output("intent-display", "children"),
     Output("status-indicator", "children"),
     Output("status-indicator", "color"),
     Output("map-status", "children"),
     Output("map-status", "color"),
     Output("chat-data", "data"),
     Output("current-result", "data"),
     Output("viz-graph", "figure"),
     Output("viz-graph-2", "figure"),
     Output("toast-container", "children")],
    [Input("send-btn", "n_clicks"), Input("chat-input", "n_submit"), Input("new-chat-btn", "n_clicks"), Input("followup-store", "data")],
    [State("chat-input", "value"), State("chat-data", "data"), State("language-select", "value")]
)
def process_chat(n_clicks, n_submit, n_new_chat, followup_data, user_input, chat_data, language_value):
    ctx = callback_context
    # Handle New Chat reset
    if ctx.triggered and ctx.triggered[0]["prop_id"].startswith("new-chat-btn"):
        return ([], create_default_map(), html.P("No data to display"), "", 
                "Ready", "success", "No Data", "secondary", [], {}, go.Figure(), go.Figure(), None)

    # If a follow-up was clicked, treat it as the new user input
    if ctx.triggered and ctx.triggered[0]["prop_id"].startswith("followup-store") and followup_data:
        user_input = followup_data

    if not user_input or not user_input.strip() or not CHATBOT_READY:
        return ([], create_default_map(), html.P("No data to display"), "", 
                "Ready", "success", "No Data", "secondary", [], {}, go.Figure(), go.Figure(), None)
    
    try:
        # Process query
        result = chatbot.chat(user_input.strip())
        
        # Update chat history
        new_chat = chat_data + [
            {"type": "user", "message": user_input},
            {"type": "bot", "message": result["response"]}
        ]
        
        # Create chat display including summaries and follow-ups
        chat_display = []
        for i, msg in enumerate(new_chat[-10:]):  # Show last 10 messages
            if msg["type"] == "user":
                chat_display.append(
                    html.Div([
                        html.Strong("You: ", className="text-primary"),
                        html.Span(msg["message"])
                    ], className="chat-bubble-user")
                )
            else:
                chat_display.append(
                    html.Div([
                        html.Strong("🤖 Assistant: ", className="text-success"),
                        dcc.Markdown(msg["message"], dangerously_allow_html=False)
                    ], className="chat-bubble-bot")
                )
        
        # Add optional summary card and follow-ups from backend
        summary_text = result.get("summary")
        follow_ups = result.get("follow_ups", [])
        if summary_text or follow_ups:
            extras = []
            if summary_text:
                extras.append(
                    dbc.Alert([html.Strong("Summary: ", className="me-1"), html.Span(summary_text)], color="info", className="mb-2")
                )
            if follow_ups:
                extras.append(
                    html.Div([
                        html.Div("Try these:", className="small text-muted mb-1"),
                        dbc.ButtonGroup([
                            *[dbc.Button(q, id={"type": "followup", "index": i}, color="outline-primary", size="sm", className="me-1 mb-1") for i, q in enumerate(follow_ups)]
                        ], className="flex-wrap")
                    ], className="mb-3")
                )
            chat_display.extend(extras)

        # Create enhanced groundwater map
        groundwater_map = create_underground_water_map(result["results"])
        
        # Create results table
        results_table = create_results_table(result["results"])
        
        # Format intent details
        intent_display = json.dumps(result["intent_analysis"], indent=2)
        
        # Status updates
        status = f"Found {result['results_count']} results"
        status_color = "success" if result["success"] else "danger"
        
        # Enhanced map status with underground water info
        underground_districts = len([r for r in result['results'] if r.get('geometry') and r.get('st_area_shape')])
        map_status = f"Mapped {underground_districts} districts with underground coverage"
        map_color = "info" if underground_districts > 0 else "warning"
        
        # Build visualization figures if spec provided
        viz_fig = build_visualization_figure(result)
        viz_fig_2 = build_secondary_visualization_figure(result)

        # Toast notification (success)
        toast = dbc.Toast(
            [html.Div(f"{status}")],
            header="Query Processed",
            icon="success",
            duration=4000,
            is_open=True,
            style={"position": "fixed", "top": 10, "right": 10, "zIndex": 2000}
        )
        
        return (chat_display, groundwater_map, results_table, intent_display,
                status, status_color, map_status, map_color, new_chat, result, viz_fig, viz_fig_2, toast)
        
    except Exception as e:
        error_msg = f"Error processing query: {str(e)}"
        error_chat = chat_data + [
            {"type": "user", "message": user_input},
            {"type": "bot", "message": error_msg}
        ]
        
        chat_display = [
            html.Div([
                html.Strong("Error: ", className="text-danger"),
                html.Span(error_msg)
            ], className="mb-2 p-2 bg-danger text-white rounded")
        ]
        
        toast_err = dbc.Toast(
            [html.Div(error_msg)],
            header="Error",
            icon="danger",
            duration=6000,
            is_open=True,
            style={"position": "fixed", "top": 10, "right": 10, "zIndex": 2000}
        )
        return (chat_display, create_default_map(), html.P("Error occurred"), error_msg, 
                "Error", "danger", "Error", "danger", error_chat, {}, go.Figure(), go.Figure(), toast_err)

# Quick stats callback
@app.callback(
    [Output("total-districts", "children"),
     Output("avg-development", "children"),
     Output("over-exploited", "children"),
     Output("critical-districts", "children"),
     Output("total-districts-explore", "children"),
     Output("avg-development-explore", "children"),
     Output("over-exploited-explore", "children"),
     Output("critical-districts-explore", "children")],
    [Input("stats-interval", "n_intervals")]
)
def update_stats(n_intervals):
    if not CHATBOT_READY:
        return "--", "--", "--", "--", "--", "--", "--", "--"
    
    try:
        stats = chatbot.get_quick_stats()
        return (
            str(stats.get("total_districts", "--")),
            f"{stats.get('avg_development', '--')}%",
            str(stats.get("over_exploited", "--")),
            str(stats.get("critical", "--")),
            str(stats.get("total_districts", "--")),
            f"{stats.get('avg_development', '--')}%",
            str(stats.get("over_exploited", "--")),
            str(stats.get("critical", "--"))
        )
    except Exception:
        return "--", "--", "--", "--", "--", "--", "--", "--"

# Toggle details callback
@app.callback(
    Output("details-collapse", "is_open"),
    [Input("toggle-details", "n_clicks")],
    [State("details-collapse", "is_open")]
)
def toggle_details(n_clicks, is_open):
    if n_clicks:
        return not is_open
    return is_open

def create_default_map():
    """Create default India map"""
    try:
        # India center coordinates
        india_center = [20.5937, 78.9629]
        
        m = folium.Map(
            location=india_center,
            zoom_start=5,  # Zoom out for India view
            tiles='OpenStreetMap'
        )
        
        # Add a marker for India center
        folium.Marker(
            india_center,
            popup="India - Underground Water Data Center",
            tooltip="Click for more info",
            icon=folium.Icon(color='blue', icon='tint')
        ).add_to(m)
        
        # Add title
        title_html = '''
        <h3 align="center" style="font-size:16px"><b>India Underground Water Coverage</b></h3>
        '''
        m.get_root().html.add_child(folium.Element(title_html))
        
        return html.Iframe(
            srcDoc=m._repr_html_(),
            style={"width": "100%", "height": "450px", "border": "none"}
        )
    except Exception as e:
        return html.Div([
            html.H5("Map Loading Error", className="text-center text-muted"),
            html.P(f"Unable to load map: {str(e)}", className="text-center")
        ], style={"height": "450px", "display": "flex", "flexDirection": "column", 
                 "justifyContent": "center", "alignItems": "center"})
        
def extract_centroid(geometry):
    """Extract centroid from WKT geometry with proper coordinate handling"""
    try:
        # Parse the WKT geometry
        geom = wkt.loads(geometry)
        
        # Get the centroid
        centroid = geom.centroid
        
        # Extract coordinates and swap them (lat, lon instead of lon, lat)
        # Database stores coordinates as (longitude, latitude) but we need (latitude, longitude)
        coords = list(centroid.coords)[0]
        
        # Return as (latitude, longitude)
        return (coords[1], coords[0])
        
    except Exception as e:
        print(f"Error parsing geometry: {e}")
        return None

def create_underground_water_map(results):
    """Create interactive underground water coverage map with proper coordinate handling"""
    if not results:
        return create_default_map()
    
    try:
        # Convert results to dataframe
        df = pd.DataFrame(results)
        
        # Check if we have geometry data
        if 'geometry' not in df.columns:
            return create_simple_data_map(df)
        
        # Filter out rows without geometry or underground water data
        df_with_geo = df[(df['geometry'].notna()) & 
                        (df['st_area_shape'].notna()) & 
                        (df['st_length_shape'].notna())].copy()
        
        if len(df_with_geo) == 0:
            return create_simple_data_map(df)
        
        # India center coordinates
        india_center = [20.5937, 78.9629]
        
        # Create map
        m = folium.Map(
            location=india_center,
            zoom_start=5,
            tiles='CartoDB positron'
        )
        
        # Calculate underground water coverage intensity
        if len(df_with_geo) > 0:
            max_area = df_with_geo['st_area_shape'].max()
            min_area = df_with_geo['st_area_shape'].min()
        
        # Add districts with enhanced underground water visualization
        for idx, row in df_with_geo.iterrows():
            try:
                # Parse WKT geometry
                geom = wkt.loads(row['geometry'])
                
                # Calculate underground water coverage intensity
                area_intensity = (row['st_area_shape'] - min_area) / (max_area - min_area) if max_area > min_area else 0.5
                
                # Determine color based on development stage and underground coverage
                dev_stage = row.get('stage_of_development', 0)
                if pd.isna(dev_stage):
                    color = 'gray'
                elif dev_stage > 100:
                    color = 'red'  # Over-exploited
                elif dev_stage > 80:
                    color = 'orange'  # Critical
                elif dev_stage > 60:
                    color = 'yellow'  # Semi-critical
                else:
                    color = 'green'  # Safe
                
                # Adjust opacity based on underground coverage area
                fill_opacity = max(0.3, min(0.9, 0.3 + (area_intensity * 0.6)))
                
                # Create enhanced popup content with underground water info
                popup_content = create_underground_popup_content(row)
                
                # Add geometry to map with enhanced styling
                if geom.geom_type == 'MultiPolygon':
                    for polygon in geom.geoms:
                        # Extract coordinates and swap them (lat, lon instead of lon, lat)
                        coords = [[point[1], point[0]] for point in polygon.exterior.coords]
                        folium.Polygon(
                            locations=coords,
                            color='black',
                            weight=2,
                            fillColor=color,
                            fillOpacity=fill_opacity,
                            popup=folium.Popup(popup_content, max_width=500),
                            tooltip=f"{row.get('district', 'Unknown District')} - Coverage: {row.get('st_area_shape', 0):,.0f} sq.m"
                        ).add_to(m)
                elif geom.geom_type == 'Polygon':
                    # Extract coordinates and swap them (lat, lon instead of lon, lat)
                    coords = [[point[1], point[0]] for point in geom.exterior.coords]
                    folium.Polygon(
                        locations=coords,
                        color='black',
                        weight=2,
                        fillColor=color,
                        fillOpacity=fill_opacity,
                        popup=folium.Popup(popup_content, max_width=500),
                        tooltip=f"{row.get('district', 'Unknown District')} - Coverage: {row.get('st_area_shape', 0):,.0f} sq.m"
                    ).add_to(m)
                
            except Exception as e:
                print(f"Error processing geometry for {row.get('district', 'unknown')}: {e}")
                continue
        
        # Add enhanced legend
        add_underground_legend_to_map(m)
        
        # Add title
        title_html = f'''
        <h3 align="center" style="font-size:16px"><b>India Underground Water Coverage - {len(df_with_geo)} Districts</b></h3>
        <p align="center" style="font-size:12px; color: #666;">Opacity indicates underground water coverage area intensity</p>
        '''
        m.get_root().html.add_child(folium.Element(title_html))
        
        return html.Iframe(
            srcDoc=m._repr_html_(),
            style={"width": "100%", "height": "450px", "border": "none"}
        )
        
    except Exception as e:
        print(f"Error creating underground water map: {e}")
        return create_default_map()

def create_underground_popup_content(row):
    """Create enhanced HTML popup content with underground water details"""
    district = row.get('district', 'Unknown')
    
    content = f"<b>{district} District</b><br><hr>"
    
    # Underground Water Coverage Section
    content += "<b>🏔️ Underground Water Coverage:</b><br>"
    if 'st_area_shape' in row and not pd.isna(row['st_area_shape']):
        content += f"Coverage Area: <b>{row['st_area_shape']:,.0f} sq.m</b><br>"
    if 'st_length_shape' in row and not pd.isna(row['st_length_shape']):
        content += f"Perimeter: <b>{row['st_length_shape']:,.0f} m</b><br>"
    
    content += "<br><b>💧 Groundwater Metrics:</b><br>"
    
    # Add key groundwater metrics
    metrics = [
        ('Development Stage', 'stage_of_development', '%'),
        ('Total Draft', 'annual_gw_draft_total', ' HM'),
        ('Net Availability', 'net_gw_availability', ' HM'),
        ('Replenishable Resource', 'annual_replenishable_gw_resource', ' HM'),
        ('Irrigation Draft', 'annual_draft_irrigation', ' HM')
    ]
    
    for label, key, unit in metrics:
        if key in row and not pd.isna(row[key]):
            value = row[key]
            if isinstance(value, (int, float)):
                if unit == '%':
                    content += f"{label}: <b>{value:.1f}{unit}</b><br>"
                elif unit == ' HM':
                    content += f"{label}: <b>{value:,.0f}{unit}</b><br>"
                else:
                    content += f"{label}: <b>{value}{unit}</b><br>"
    
    # Add underground water assessment
    if 'st_area_shape' in row and not pd.isna(row['st_area_shape']):
        area = row['st_area_shape']
        if area > 3000000000:  # > 3 billion sq.m
            assessment = "🟢 Extensive underground coverage"
        elif area > 1500000000:  # > 1.5 billion sq.m
            assessment = "🟡 Moderate underground coverage"
        else:
            assessment = "🔴 Limited underground coverage"
        
        content += f"<br><b>Assessment:</b> {assessment}"
    
    return content

def add_underground_legend_to_map(m):
    """Add enhanced color legend for underground water coverage"""
    legend_html = '''
    <div style="position: fixed; 
                bottom: 50px; left: 50px; width: 200px; height: 160px; 
                background-color: white; border:2px solid grey; z-index:9999; 
                font-size:12px; padding: 10px; border-radius: 5px;">
    <p><b>Development Stage:</b></p>
    <p><i class="fa fa-square" style="color:green"></i> Safe (&lt;60%)</p>
    <p><i class="fa fa-square" style="color:yellow"></i> Semi-critical (60-80%)</p>
    <p><i class="fa fa-square" style="color:orange"></i> Critical (80-100%)</p>
    <p><i class="fa fa-square" style="color:red"></i> Over-exploited (&gt;100%)</p>
    <hr>
    <p><b>Underground Coverage:</b></p>
    <p>Opacity = Coverage area intensity</p>
    </div>
    '''
    m.get_root().html.add_child(folium.Element(legend_html))

def create_simple_data_map(df):
    """Create simple marker-based map when no geometry available"""
    try:
        # India center coordinates
        india_center = [20.5937, 78.9629]
        m = folium.Map(location=india_center, zoom_start=5, tiles='OpenStreetMap')
        
        # Add markers for districts in the data
        for idx, row in df.iterrows():
            district = row.get('district', '').strip()
            
            # Use a simple approach - just place markers at approximate locations
            # In a real implementation, you'd want to geocode district names
            # For now, we'll just use random coordinates around India
            import random
            lat = 20.5937 + random.uniform(-5, 5)
            lon = 78.9629 + random.uniform(-5, 5)
            
            # Determine marker color based on development stage
            dev_stage = row.get('stage_of_development', 0)
            if pd.isna(dev_stage):
                color = 'gray'
            elif dev_stage > 100:
                color = 'red'
            elif dev_stage > 80:
                color = 'orange'
            elif dev_stage > 60:
                color = 'blue'
            else:
                color = 'green'
            
            # Create popup with underground water info
            popup_content = create_underground_popup_content(row)
            
            folium.Marker(
                [lat, lon],
                popup=folium.Popup(popup_content, max_width=400),
                tooltip=f"{district} - Underground coverage available",
                icon=folium.Icon(color=color, icon='tint')
            ).add_to(m)
        
        # Add title
        title_html = f'''
        <h3 align="center" style="font-size:16px"><b>India Districts - {len(df)} Locations</b></h3>
        '''
        m.get_root().html.add_child(folium.Element(title_html))
        
        return html.Iframe(
            srcDoc=m._repr_html_(),
            style={"width": "100%", "height": "450px", "border": "none"}
        )
        
    except Exception as e:
        return create_default_map()

def create_results_table(results):
    """Create enhanced results table with underground water columns"""
    if not results:
        return html.P("No results to display", className="text-muted")
    
    df = pd.DataFrame(results)
    
    # Prioritize columns including underground water metrics
    display_cols = []
    priority_cols = ['district', 'st_area_shape', 'st_length_shape', 'annual_gw_draft_total', 
                    'stage_of_development', 'net_gw_availability']
    
    for col in priority_cols:
        if col in df.columns:
            display_cols.append(col)
    
    # Add other columns except geometry
    other_cols = [col for col in df.columns if col not in priority_cols and col != 'geometry']
    display_cols.extend(other_cols[:3])
    
    if display_cols:
        display_df = df[display_cols].head(10)
        
        # Format column names for better readability
        column_names = []
        for col in display_cols:
            if col == 'st_area_shape':
                column_names.append({"name": "Underground Coverage (sq.m)", "id": col, "type": "numeric", "format": {"specifier": ",.0f"}})
            elif col == 'st_length_shape':
                column_names.append({"name": "Underground Perimeter (m)", "id": col, "type": "numeric", "format": {"specifier": ",.0f"}})
            else:
                column_names.append({"name": col.replace('_', ' ').title(), "id": col})
        
        return dash_table.DataTable(
            data=display_df.to_dict('records'),
            columns=column_names,
            style_cell={'textAlign': 'left', 'fontSize': '12px', 'padding': '8px', 'whiteSpace': 'normal', 'height': 'auto'},
            style_header={'backgroundColor': 'rgb(230, 230, 230)', 'fontWeight': 'bold'},
            style_data_conditional=[
                {
                    'if': {
                        'filter_query': '{stage_of_development} > 100',
                        'column_id': 'stage_of_development'
                    },
                    'backgroundColor': '#ffebee',
                    'color': 'black',
                },
                {
                    'if': {
                        'filter_query': '{stage_of_development} > 80 && {stage_of_development} <= 100',
                        'column_id': 'stage_of_development'
                    },
                    'backgroundColor': '#fff3e0',
                    'color': 'black',
                },
                # Highlight large underground coverage areas
                {
                    'if': {
                        'filter_query': '{st_area_shape} > 3000000000',
                        'column_id': 'st_area_shape'
                    },
                    'backgroundColor': '#e8f5e8',
                    'color': 'black',
                }
            ],
            page_size=10,
            sort_action="native",
            filter_action="native",
            tooltip_data=[
                {
                    column: {'value': 'Underground water coverage area in square meters', 'type': 'markdown'}
                    if column == 'st_area_shape'
                    else {'value': 'Underground water perimeter in meters', 'type': 'markdown'}
                    if column == 'st_length_shape'
                    else {'value': str(value), 'type': 'text'}
                    for column, value in row.items()
                } for row in display_df.to_dict('records')
            ],
            tooltip_duration=None
        )
    
    return html.P("Unable to display results", className="text-muted")

def build_visualization_figure(result_dict):
    """Create a Plotly figure based on backend-provided visualization spec and results."""
    try:
        viz = result_dict.get("visualization", {}) if isinstance(result_dict, dict) else {}
        results = result_dict.get("results", []) if isinstance(result_dict, dict) else []
        if not viz or not viz.get("enabled") or not results:
            return go.Figure()

        df = pd.DataFrame(results)
        # Coerce numeric columns safely
        for col in [viz.get("y"), viz.get("x")]:
            if col and col in df.columns:
                try:
                    df[col] = pd.to_numeric(df[col], errors='coerce') if col != 'district' else df[col]
                except Exception:
                    pass

        chart_type = viz.get("chart_type", "bar")
        x_col = viz.get("x")
        y_col = viz.get("y")
        top_n = viz.get("top_n", 10)
        title = viz.get("title", "Data Visualization")

        # Reduce to top_n by y if possible
        plot_df = df.copy()
        if y_col in plot_df.columns and pd.api.types.is_numeric_dtype(plot_df[y_col]):
            plot_df = plot_df.sort_values(by=y_col, ascending=False).head(top_n)
        else:
            plot_df = plot_df.head(top_n)

        if chart_type == "histogram" and x_col and x_col in plot_df.columns:
            fig = px.histogram(plot_df, x=x_col, nbins=20, title=title)
        elif chart_type == "scatter" and x_col and y_col and x_col in plot_df.columns and y_col in plot_df.columns:
            fig = px.scatter(plot_df, x=x_col, y=y_col, hover_data=[c for c in plot_df.columns if c not in ['geometry']], title=title)
        else:
            # Default to bar; pick axis intelligently
            if (not x_col or x_col not in plot_df.columns) and 'district' in plot_df.columns:
                x_col = 'district'
            if not y_col or y_col not in plot_df.columns:
                # choose a numeric column fallback
                candidates = [c for c in [
                    'annual_gw_draft_total', 'stage_of_development', 'net_gw_availability',
                    'annual_replenishable_gw_resource', 'annual_draft_irrigation',
                    'st_area_shape', 'st_length_shape'
                ] if c in plot_df.columns]
                y_col = candidates[0] if candidates else None

            if x_col and y_col and y_col in plot_df.columns:
                fig = px.bar(plot_df, x=x_col, y=y_col, title=title, hover_data=[c for c in plot_df.columns if c not in ['geometry']])
            else:
                fig = go.Figure()

        fig.update_layout(margin=dict(l=10, r=10, t=50, b=10), height=400)
        return fig
    except Exception:
        return go.Figure()

def build_secondary_visualization_figure(result_dict):
    """Second complementary chart (e.g., perimeter vs area scatter)."""
    try:
        results = result_dict.get("results", []) if isinstance(result_dict, dict) else []
        if not results:
            return go.Figure()

        df = pd.DataFrame(results)
        if not {'st_area_shape', 'st_length_shape'}.issubset(df.columns):
            return go.Figure()

        # Coerce numeric
        for col in ['st_area_shape', 'st_length_shape']:
            if col in df.columns:
                df[col] = pd.to_numeric(df[col], errors='coerce')

        # Keep top 50 by area
        df_plot = df.sort_values(by='st_area_shape', ascending=False).head(50)
        if 'district' not in df_plot.columns:
            df_plot['district'] = [f"District {i+1}" for i in range(len(df_plot))]

        fig = px.scatter(
            df_plot,
            x='st_area_shape',
            y='st_length_shape',
            hover_name='district',
            title='Perimeter vs Area (Underground Coverage)',
            labels={'st_area_shape': 'Coverage Area (sq.m)', 'st_length_shape': 'Perimeter (m)'}
        )
        fig.update_layout(margin=dict(l=10, r=10, t=50, b=10), height=350)
        return fig
    except Exception:
        return go.Figure()

# Download current map as HTML
@app.callback(
    Output("download-map-html", "data"),
    Input("download-map-html-btn", "n_clicks"),
    State("current-result", "data"),
    prevent_initial_call=True
)
def download_map_html(n_clicks, result_dict):
    try:
        if not n_clicks:
            return dash.no_update
        # Rebuild the map HTML from current results for export
        results = (result_dict or {}).get("results", [])
        iframe = create_underground_water_map(results)
        if isinstance(iframe, html.Iframe):
            html_str = iframe.props.get("srcDoc") or ""
        else:
            html_str = ""
        if not html_str:
            return dash.no_update
        return dict(content=html_str, filename="groundwater_map.html")
    except Exception:
        return dash.no_update

# Download CSV of current results
@app.callback(
    Output("download-csv", "data"),
    Input("download-csv-btn", "n_clicks"),
    State("current-result", "data"),
    prevent_initial_call=True
)
def download_results_csv(n_clicks, result_dict):
    try:
        if not n_clicks:
            return dash.no_update
        results = (result_dict or {}).get("results", [])
        df = pd.DataFrame(results)
        if df.empty:
            return dash.no_update
        return dcc.send_data_frame(df.to_csv, "groundwater_results.csv", index=False)
    except Exception:
        return dash.no_update

# Standalone Visualizations page CSV download
@app.callback(
    Output("download-csv-standalone", "data"),
    Input("download-csv-btn-standalone", "n_clicks"),
    State("current-result", "data"),
    prevent_initial_call=True
)
def download_results_csv_standalone(n_clicks, result_dict):
    try:
        if not n_clicks:
            return dash.no_update
        results = (result_dict or {}).get("results", [])
        df = pd.DataFrame(results)
        if df.empty:
            return dash.no_update
        return dcc.send_data_frame(df.to_csv, "groundwater_results.csv", index=False)
    except Exception:
        return dash.no_update

# Sync standalone visualizations with current result
@app.callback(
    [Output("viz-graph-standalone", "figure"), Output("viz-graph-2-standalone", "figure")],
    [Input("current-result", "data")]
)
def populate_standalone_viz(result_dict):
    try:
        if not result_dict:
            return go.Figure(), go.Figure()
        return build_visualization_figure(result_dict), build_secondary_visualization_figure(result_dict)
    except Exception:
        return go.Figure(), go.Figure()

# Reports page CSV download
@app.callback(
    Output("download-csv-report", "data"),
    Input("download-csv-btn-report", "n_clicks"),
    State("current-result", "data"),
    prevent_initial_call=True
)
def download_results_csv_report(n_clicks, result_dict):
    try:
        if not n_clicks:
            return dash.no_update
        results = (result_dict or {}).get("results", [])
        df = pd.DataFrame(results)
        if df.empty:
            return dash.no_update
        return dcc.send_data_frame(df.to_csv, "groundwater_report.csv", index=False)
    except Exception:
        return dash.no_update

# Forecast and Knowledge Cards from current results
@app.callback(
    [Output("viz-graph-forecast", "figure"), Output("knowledge-cards", "children")],
    [Input("current-result", "data"), Input("main-tabs", "value")]
)
def build_forecast_and_cards(result_dict, active_tab):
    try:
        def _placeholder_figure(title_suffix="No time-series data"):
            x_vals = list(range(1, 11))
            y_vals = list(range(1, 11))
            fig = go.Figure()
            fig.add_trace(go.Scatter(
                x=x_vals,
                y=y_vals,
                mode='lines+markers',
                name='Placeholder',
                marker=dict(color="#2563eb"),
                line=dict(color="#2563eb")
            ))
            fig.update_layout(
                title=f"Forecast Preview ({title_suffix})",
                height=360,
                margin=dict(l=10, r=10, t=50, b=10),
                plot_bgcolor="#ffffff",
                paper_bgcolor="#ffffff"
            )
            return fig

        if not result_dict:
            # Minimal readable placeholder with hint
            placeholder = _placeholder_figure()
            hint = dbc.Alert("Run a query in Chat or Explore to populate insights.", color="secondary", className="mb-2")
            return placeholder, [hint]
        results = result_dict.get("results", [])
        viz = result_dict.get("visualization", {})
        insights = result_dict.get("insights", []) or []
        df = pd.DataFrame(results)
        if df.empty:
            # Even if results empty, still show insights if any
            insights_children = []
            if insights:
                insights_children.append(
                    dbc.Alert(
                        [html.Strong("Insights", className="me-2")] + [html.Div(f"• {i.get('title', '')}: {i.get('detail', '')}") for i in insights],
                        color="light",
                        className="mb-3"
                    )
                )
            placeholder = _placeholder_figure()
            if not insights_children:
                insights_children = [dbc.Alert("No insights available for current selection.", color="secondary", className="mb-2")]
            return placeholder, insights_children

        # Forecast placeholder: line over sorted top N by selected metric
        metric = viz.get("y") if viz else None
        if not metric or metric not in df.columns:
            for c in [
                'annual_gw_draft_total', 'stage_of_development', 'net_gw_availability',
                'annual_replenishable_gw_resource', 'annual_draft_irrigation', 'st_area_shape'
            ]:
                if c in df.columns:
                    metric = c
                    break
        plot_df = df.copy()
        if metric in plot_df.columns:
            with pd.option_context('mode.use_inf_as_na', True):
                plot_df[metric] = pd.to_numeric(plot_df[metric], errors='coerce')
            plot_df = plot_df.dropna(subset=[metric]).sort_values(by=metric, ascending=False).head(20)
            x_vals = list(range(1, len(plot_df) + 1))
            fig_forecast = go.Figure()
            fig_forecast.add_trace(go.Scatter(
                x=x_vals,
                y=plot_df[metric],
                mode='lines+markers',
                name='Metric',
                marker=dict(color="#2563eb"),
                line=dict(color="#2563eb")
            ))
            fig_forecast.update_layout(
                title=f"Forecast Preview for {metric.replace('_',' ').title()}",
                height=360,
                margin=dict(l=10, r=10, t=50, b=10),
                plot_bgcolor="#ffffff",
                paper_bgcolor="#ffffff"
            )
        else:
            fig_forecast = _placeholder_figure("No suitable metric found")

        # Knowledge: insights + cards
        knowledge_children = []
        if insights:
            knowledge_children.append(
                dbc.Alert(
                    [html.Strong("Insights", className="me-2 text-black")] + [html.Div(f"• {i.get('title', '')}: {i.get('detail', '')}") for i in insights],
                    color="light",
                    className="mb-3 text-black"
                )
            )

        cards = []
        top_df = plot_df.head(6) if metric in df.columns else df.head(6)
        for _, row in top_df.iterrows():
            title = str(row.get('district', 'District')).title()
            area = row.get('st_area_shape')
            perim = row.get('st_length_shape')
            dev = row.get('stage_of_development')
            body = [
                html.Div(f"Area: {area:,.0f} sq.m" if isinstance(area, (int, float)) else f"Area: {area}"),
                html.Div(f"Perimeter: {perim:,.0f} m" if isinstance(perim, (int, float)) else f"Perimeter: {perim}"),
                html.Div(f"Development: {dev:.1f}%" if isinstance(dev, (int, float)) else f"Development: {dev}")
            ]
            cards.append(
                dbc.Card([
                    dbc.CardHeader(html.Strong(title)),
                    dbc.CardBody(body)
                ], className="me-2 mb-2 shadow-sm", style={"minWidth": "220px"})
            )

        knowledge_children.extend(cards)
        return fig_forecast, knowledge_children
    except Exception:
        return go.Figure(), []

if __name__ == "__main__":
    if CHATBOT_READY:
        print("🌊 India Underground Water AI Chatbot")
        print("🚀 Starting server at http://localhost:8050")
        app.run(debug=True, host="0.0.0.0", port=8050)
    else:
        print("❌ Cannot start - Chatbot initialization failed")
        print("Please check your environment variables and database connection")