query_router.py

from fastapi import APIRouter, HTTPException, Query, Depends, Request
from pydantic import BaseModel, Field, EmailStr
from typing import Optional, List, Dict, Any, Union
from .text2sql import Text2SQL
from fastapi import APIRouter, UploadFile, File
from datetime import datetime
from typing import List, Optional
from pydantic import BaseModel
from private_gpt.server.utils.auth import authenticated
import os
from sqlalchemy import create_engine
import psycopg2
from private_gpt.core_logger import logger
from fastapi.responses import JSONResponse
import pytz
from private_gpt.server.chat.redis_connection import get_redis_client
from private_gpt.DataQuery.utils import convert_to_chartjs_format
from redis.asyncio import Redis
import requests
import urllib.parse
import json
import markdown2

router = APIRouter(prefix="/v1", tags=["text2sql"], dependencies=[Depends(authenticated)])

# Database configuration
ZEPTO_KEY = os.getenv('ZEPTO_KEY')
POSTGRES_PASSWORD = os.getenv('POSTGRES_PASSWORD')
POSTGRES_HOST = os.getenv('POSTGRES_HOST', 'localhost')
POSTGRES_DB = 'postgres'
DATABASE_URL = f'postgresql://postgres:{POSTGRES_PASSWORD}@{POSTGRES_HOST}/{POSTGRES_DB}'
engine = create_engine(DATABASE_URL)

class QueryHistoryItem(BaseModel):
    id: Optional[int]
    timestamp: Optional[datetime]
    original_query: Optional[str]
    sql_query: Optional[str]
    response: Optional[str]
    chart_data: Optional[dict]
    execution_time: Optional[float]

# Pydantic models for request/response
class QueryRequest(BaseModel):
    query: str
    follow_up: bool = False
    thread_key: Optional[str] = "12122312.2123105"

class QueryResponse(BaseModel):
    success: bool
    result: Optional[str]
    sql_query: Optional[str]
    clarifying_questions: Optional[List[str]]
    original_query: Optional[str]
    issues: Optional[List[str]]
    chart_data: Optional[Dict[str, Any]]

class InitializeRequest(BaseModel):
    csv_path: str
    tableinfo_dir: str
    nrows: Optional[int] = None
    load_existing: bool = False

class Suggestions(BaseModel):
    """Information regarding a structured table."""

    suggestions: List[str] = Field(
        ..., description="list of strings of questions generated"
    )
    
class EmailRecipient(BaseModel):
    address: EmailStr
    name: Optional[str] = None

class EmailRequest(BaseModel):
    to: List[EmailRecipient]
    subject: str
    content: Optional[str]
    query: str
    # chart_data: Dict[str, Any]
    chart_width: Optional[int] = 800  # Default width
    chart_height: Optional[int] = 400  # Default height
    from_address: Optional[EmailRecipient] = None


@router.post("/sql/upload")
async def upload_files(request: Request, 
                       user_id: str = Query(..., description="User ID to fetch query history for"), 
                       files: List[UploadFile] = File(...)):
    """Upload and process multiple CSV/XLSX/PDF files"""
    # Validate file extensions before proceeding
    for file in files:
        if not file.filename.endswith(('.csv', '.xlsx', '.xls', '.pdf')):
            raise HTTPException(
                status_code=400,
                detail=f"File {file.filename} is not a valid CSV/XLSX file"
            )

    try:
        file_paths = []  # List to store paths of all files
        uuid = user_id
        # Save files to temporary directory and collect their paths
        for file in files:
            # Read the file contents into memory
            file_contents = await file.read()

            # Save the file to a temporary location
            temp_file_path = f"/tmp/{file.filename}"
            with open(temp_file_path, "wb") as temp_file:
                temp_file.write(file_contents)

            # Append the temporary file path to the list
            file_paths.append(temp_file_path)

        # Inject Text2SQL instance and call retrieveSQL once
        text2sql_instance = request.state.injector.get(Text2SQL)

        if text2sql_instance is not None:
            # Pass the list of file paths to create_table
            text2sql_instance.create_table(file_paths, uuid)
            # text2sql_instance.retrieveSQL()
        else:
            raise HTTPException(
                status_code=500,
                detail="Failed to initialize Text2SQL"
            )
        text2sql_instance.store_query_history(query=" ", response=f"Files {file_paths} uploaded and processed successfully", uuid=uuid)
        return {
            "status": "success",
            "message": "Files uploaded and processed successfully",
            "files": file_paths,
            "initialized": True
        }

    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"An error occurred while processing the files: {str(e)}"
        )

@router.post("/sql/query", response_model=QueryResponse)
async def execute_query(query_request: QueryRequest, request: Request, 
                        user_id: str = Query(..., description="User ID to fetch query history for"),
                        redis: Redis = Depends(get_redis_client)):
    """Execute a natural language query"""
    uuid = user_id
    current_time_str = datetime.now(pytz.UTC).strftime("%Y-%m-%d %H:%M:%S.000Z")
    
    metadata = await request.json()
    
    if metadata.get('SQLQuery'):
        fetched_query = list(metadata.values())[1]
    else:
        fetched_query=""
        
    if query_request.thread_key:
        thread_key = query_request.thread_key
    else:
      thread_key = "12122312.2123105"  
    
    text2sql_instance = request.state.injector.get(Text2SQL)
    text2sql_instance.load_existing_database()
    text2sql_instance.retrieveSQL(uuid)
    if text2sql_instance is None:
        raise HTTPException(
            status_code=400,
            detail="Text2SQL not initialized. Call /initialize endpoint first."
        )
    
    try:
        # Execute query
        if fetched_query == "":
            is_safe, result, questions, suggestions, issues, chart_data = await text2sql_instance.execute_query(query_request.query, uuid, current_time_str, redis, thread_key)
            
        else:
            is_safe, processed_query, questions = text2sql_instance.process_query(query_request.query)
            result = text2sql_instance.generate_response(processed_query, fetched_query)
            chart_data = text2sql_instance.extract_chart_data(processed_query)
            text2sql_instance.store_query_history(
                query=processed_query,
                response=str(result),
                sql_query=fetched_query,
                chart_data=chart_data,
                uuid=uuid
            )
            
            
        return QueryResponse(
            success=is_safe,
            result=str(result),
            sql_query=text2sql_instance.sql_query if is_safe else None,
            clarifying_questions=questions,
            original_query=query_request.query,
            issues=None,
            chart_data=chart_data if chart_data else None
        )
        
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"Query execution failed: {str(e)}"
        )

@router.get("/sql/query-history", response_model=List[QueryHistoryItem])
async def get_query_history(
    request: Request,
    user_id: str = Query(..., description="User ID to fetch query history for"),
    limit: int = Query(50, description="Number of records to return", ge=0, le=100),
    offset: int = Query(0, description="Number of records to skip", ge=0)
):
    """
    Retrieve query history from the database for a specific user.
    
    Args:
        request (Request): FastAPI request object
        user_id (UUID): User ID to fetch query history for
        limit (int): Maximum number of records to return (default: 50)
        offset (int): Number of records to skip for pagination (default: 0)
        
    Returns:
        List[QueryHistoryItem]: List of query history records
    """
    text2sql_instance = request.state.injector.get(Text2SQL)
    
    if text2sql_instance is None:
        raise HTTPException(
            status_code=400,
            detail="Text2SQL not initialized. Call /initialize endpoint first."
        )
    
    try:
        # Sanitize UUID to ensure it's a valid PostgreSQL identifier
        # safe_uuid = text2sql_instance.sanitize_query(str(user_id)[:12])
        safe_uuid = str(user_id)
        table_name = f"query_history.queries_{safe_uuid}"
        with text2sql_instance.conn.cursor() as cursor:
            # Check if the schema and table exist
            cursor.execute("""
                SELECT EXISTS (
                    SELECT 1 
                    FROM information_schema.tables 
                    WHERE table_schema = 'query_history' 
                    AND table_name = %s
                )
            """, (f'queries_{safe_uuid}',))
            table_exists = cursor.fetchone()[0]
            
            if not table_exists:
                return []
            
            # Fetch query history with pagination
            if limit == 0:
                cursor.execute(f"""
                    SELECT 
                        id,
                        timestamp,
                        original_query,
                        sql_query,
                        response,
                        chart_data,
                        execution_time
                    FROM {table_name}
                    ORDER BY timestamp DESC
                """)    
            else:
                cursor.execute(f"""
                    SELECT 
                        id,
                        timestamp,
                        original_query,
                        sql_query,
                        response,
                        chart_data,
                        execution_time
                    FROM {table_name}
                    ORDER BY timestamp DESC
                    LIMIT %s OFFSET %s
                """, (limit, offset))
            
            records = cursor.fetchall()
            # Convert records to Pydantic models
            history_items = []
            for record in records:
                history_items.append(QueryHistoryItem(
                    id=record[0],
                    timestamp=record[1],
                    original_query=record[2],
                    sql_query=record[3],
                    response=record[4],
                    chart_data=record[5],
                    execution_time=float(record[6].total_seconds()) if record[6] else None
                ))
            
            return history_items
            
    except ValueError as e:
        raise HTTPException(
            status_code=400,
            detail=f"Invalid UUID format: {str(e)}"
        )
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"Failed to retrieve query history: {str(e)}"
        )

@router.post("/sql/dashboard")
async def get_dashboard_data(request: Request, 
                             user_id: str = Query(..., description="User ID to fetch query history for")):
    
    body_dict = await request.json()
    
    filtered_city_query = None
    filtered_store_query = None
    filtered_item_query = None
    
    if list(body_dict.values())[2]:
        time_range = list(body_dict.values())[2]
    else:
        time_range = '7'
    
    conn = psycopg2.connect(
    host=POSTGRES_HOST,
    password=POSTGRES_PASSWORD,
    user='postgres',
    database=POSTGRES_DB
    )
    
    # latest_tables_query = """
    #     SELECT filename as table_name 
    #     FROM csv_data.file_metadata
    #     WHERE user_id = %s
    # """
    # with conn.cursor() as cursor:
    #     cursor.execute(latest_tables_query, (user_id,))
    #     tables = [row[0] for row in cursor.fetchall()]
    
    # print(tables)
    
    distinct_stores = """SELECT DISTINCT store_name FROM csv_data."5_pizza_guy_data_test_set_extended_order_col" ORDER BY store_name;"""
    
    distinct_cities = """SELECT DISTINCT city FROM csv_data."5_pizza_guy_data_test_set_extended_order_col" ORDER BY city;"""
    
    distinct_items = """SELECT DISTINCT item FROM csv_data."5_pizza_guy_data_test_set_extended_item" ORDER BY item;"""
    
    results = {}
    with conn.cursor() as cur:
        
        cur.execute(distinct_cities)
        distinct_city_rows = cur.fetchall()
        # print(distinct_city_rows)
        
        results['distinct_cities'] = {
            'cities': [city[0] for city in distinct_city_rows]
        }
        # print(list(results['distinct_cities'].values())[0])
        
        cur.execute(distinct_stores)
        distinct_store_rows = cur.fetchall()
        
        results['distinct_stores'] = {
            'stores': [city[0] for city in distinct_store_rows]
        }
        
        cur.execute(distinct_items)
        distinct_item_rows = cur.fetchall()
        
        results['distinct_items'] = {
            'items': [city[0] for city in distinct_item_rows]
        }
        
    
    if list(body_dict.values())[0] == 'City':
        cities_list = list(body_dict.values())[1]
        # print(cities_list)
        if cities_list[0] == '':
            city_values = "'" + "','".join(list(results['distinct_cities'].values())[0]) + "'"
        else:
            city_values = "'" + "','".join(cities_list) + "'"
            
        print(city_values, int(time_range))
        
        filtered_city_query = f"""
        SELECT 
            CASE 
                WHEN date_col::timestamp >= CURRENT_DATE - INTERVAL '{int(time_range)} days' THEN 'This Week'
                ELSE 'Last Week'
            END AS week,
            DATE(date_col::timestamp) AS sale_date,
            SUM(net_sales) AS total_revenue
        FROM 
            csv_data."5_pizza_guy_data_test_set_extended_order_col"
        WHERE 
            city IN ({city_values})
            AND date_col::timestamp >= CURRENT_DATE - INTERVAL '{int(time_range)*2} days'
            AND date_col::timestamp < CURRENT_DATE
        GROUP BY 
            week,
            sale_date
        ORDER BY 
            sale_date DESC;
        """
        
    elif list(body_dict.values())[0] == 'Store':
        # Convert vals list to a comma-separated string of quoted values
        stores_list = list(body_dict.values())[1]
        if stores_list[0] == '':
            store_values = "'" + "','".join(list(results['distinct_stores'].values())[0]) + "'"
        else:
            store_values = "'" + "','".join(stores_list) + "'"
    
        print(store_values, int(time_range))
        filtered_store_query = f"""
        SELECT 
            DATE(date_col::timestamp) AS sale_date,
            SUM(net_sales) AS total_revenue,
            CASE 
                WHEN date_col::timestamp >= CURRENT_DATE - INTERVAL '{int(time_range)} days' THEN 'This Week'
                ELSE 'Last Week'
            END AS week_category
        FROM 
            csv_data."5_pizza_guy_data_test_set_extended_order_col"
        WHERE 
            store_name IN ({store_values})
            AND date_col::timestamp >= CURRENT_DATE - INTERVAL '{int(time_range)*2} days'
            AND date_col::timestamp < CURRENT_DATE
        GROUP BY 
            DATE(date_col::timestamp),
            week_category
        ORDER BY 
            sale_date DESC;
        """
        
    elif list(body_dict.values())[0] == 'Item':
        # Convert vals list to a comma-separated string of quoted values
        items_list = list(body_dict.values())[1]
        if items_list[0] == '':
            item_values = "'" + "','".join(list(results['distinct_items'].values())[0]) + "'"
        else:
            item_values = "'" + "','".join(items_list) + "'"
        
        print(item_values, int(time_range))
        
        filtered_item_query = f"""
        WITH pizza_sales AS (
        SELECT 
            oi.item,
            os.date_col::timestamp AS sale_date,
            SUM(oi.total_price) AS revenue
        FROM 
            csv_data."5_pizza_guy_data_test_set_extended_order_col" os
        JOIN 
            csv_data."5_pizza_guy_data_test_set_extended_item" oi ON os.order_id = oi.order_id
        WHERE 
            oi.item IN ({item_values})
            AND os.date_col::timestamp >= CURRENT_DATE - INTERVAL '{int(time_range)*2} days'
            AND os.date_col::timestamp <= CURRENT_DATE
        GROUP BY 
            oi.item, os.date_col::timestamp
        )
        SELECT 
        DATE(sale_date) AS date,
        SUM(revenue) AS total_revenue,
        CASE 
            WHEN sale_date >= CURRENT_DATE - INTERVAL '{int(time_range)} days' THEN 'This Week'
            ELSE 'Last Week'
        END AS week_category
        FROM 
        pizza_sales
        GROUP BY 
        DATE(sale_date),
        week_category
        ORDER BY 
        date DESC;
        """
    else:
        daily_revenue_query = f"""
        WITH this_week AS (
            SELECT 
                DATE_TRUNC('day', csv_data."5_pizza_guy_data_test_set_extended_order_col".date_col::timestamp) AS sale_date,
                SUM(csv_data."5_pizza_guy_data_test_set_extended_order_col".net_sales) AS daily_revenue,
                'this_week' AS period
            FROM csv_data."5_pizza_guy_data_test_set_extended_order_col"
            WHERE csv_data."5_pizza_guy_data_test_set_extended_order_col".date_col::timestamp >= CURRENT_DATE - INTERVAL '{int(time_range)} days'
            AND csv_data."5_pizza_guy_data_test_set_extended_order_col".date_col::timestamp <= CURRENT_DATE
            GROUP BY DATE_TRUNC('day', csv_data."5_pizza_guy_data_test_set_extended_order_col".date_col::timestamp)
        ),
        last_week AS (
            SELECT 
                DATE_TRUNC('day', csv_data."5_pizza_guy_data_test_set_extended_order_col".date_col::timestamp) AS sale_date,
                SUM(csv_data."5_pizza_guy_data_test_set_extended_order_col".net_sales) AS daily_revenue,
                'last_week' AS period
            FROM csv_data."5_pizza_guy_data_test_set_extended_order_col"
            WHERE csv_data."5_pizza_guy_data_test_set_extended_order_col".date_col::timestamp >= CURRENT_DATE - INTERVAL '{int(time_range)*2} days'
            AND csv_data."5_pizza_guy_data_test_set_extended_order_col".date_col::timestamp < CURRENT_DATE - INTERVAL '{int(time_range)} days'
            GROUP BY DATE_TRUNC('day', csv_data."5_pizza_guy_data_test_set_extended_order_col".date_col::timestamp)
        )
        SELECT * FROM this_week
        UNION ALL
        SELECT * FROM last_week
        ORDER BY sale_date DESC, period;
        """
    
    
    store_revenue_query = f"""
    WITH last_7_days AS ( SELECT store_name, city, SUM(net_sales) as revenue 
        FROM csv_data."5_pizza_guy_data_test_set_extended_order_col" 
        WHERE date_col::timestamp >= CURRENT_DATE - INTERVAL '{int(time_range)} days' 
        AND date_col::timestamp <= CURRENT_DATE GROUP BY store_name, city ), 
    previous_7_days AS ( SELECT store_name, city, SUM(net_sales) as revenue 
        FROM csv_data."5_pizza_guy_data_test_set_extended_order_col" 
        WHERE date_col::timestamp >= CURRENT_DATE - INTERVAL '{int(time_range)*2} days' 
        AND date_col::timestamp < CURRENT_DATE - INTERVAL '{int(time_range)} days' 
        GROUP BY store_name, city ) 
    SELECT l.store_name, l.city, l.revenue as last_7_days_revenue, p.revenue as previous_7_days_revenue, 
        ROUND(((l.revenue - p.revenue) / p.revenue * 100)::numeric, 2) as revenue_change_percentage 
        FROM last_7_days l LEFT JOIN previous_7_days p ON l.store_name = p.store_name 
        AND l.city = p.city ORDER BY l.revenue DESC LIMIT 3;
    """
    
    
    payment_revenue_query=f"""
    WITH last_7_days AS ( 
        SELECT payment_method, SUM(net_sales) 
        AS revenue FROM csv_data."5_pizza_guy_data_test_set_extended_order_col" 
        WHERE date_col::timestamp >= CURRENT_DATE - INTERVAL '{int(time_range)} days'
        AND date_col::timestamp < CURRENT_DATE GROUP BY payment_method ), 
    previous_7_days AS ( 
        SELECT payment_method, SUM(net_sales) AS revenue 
        FROM csv_data."5_pizza_guy_data_test_set_extended_order_col" 
        WHERE date_col::timestamp >= CURRENT_DATE - INTERVAL '{int(time_range)*2} days'
        AND date_col::timestamp < CURRENT_DATE - INTERVAL '{int(time_range)} days'
        GROUP BY payment_method ), top_3_payment_methods AS ( SELECT payment_method, revenue 
        FROM last_7_days ORDER BY revenue DESC LIMIT 3 ) 
    SELECT t.payment_method, t.revenue AS last_7_days_revenue, p.revenue AS previous_7_days_revenue, 
    ROUND(((t.revenue - p.revenue) / p.revenue * 100)::numeric, 2) AS revenue_change_percentage 
    FROM top_3_payment_methods t LEFT JOIN previous_7_days p ON t.payment_method = p.payment_method ORDER BY t.revenue DESC;
    """

    order_mode_revenue = f"""
    WITH last_7_days AS ( 
        SELECT order_mode, SUM(net_sales) AS revenue 
        FROM csv_data."5_pizza_guy_data_test_set_extended_order_col" 
        WHERE date_col::timestamp >= CURRENT_DATE - INTERVAL '{int(time_range)} days'
        AND date_col::timestamp <= CURRENT_DATE GROUP BY order_mode ), 
    previous_7_days AS ( SELECT order_mode, SUM(net_sales) AS revenue 
        FROM csv_data."5_pizza_guy_data_test_set_extended_order_col" 
        WHERE date_col::timestamp >= CURRENT_DATE - INTERVAL '{int(time_range)*2} days'
        AND date_col::timestamp < CURRENT_DATE - INTERVAL '{int(time_range)} days' GROUP BY order_mode ), 
    top_3_order_modes AS ( SELECT order_mode, revenue FROM last_7_days ORDER BY revenue DESC LIMIT 3 ) 
        SELECT t.order_mode, t.revenue AS last_7_days_revenue, p.revenue AS previous_7_days_revenue, 
        ROUND(((t.revenue - p.revenue) / p.revenue * 100)::numeric, 2) AS revenue_change_percentage 
        FROM top_3_order_modes t LEFT JOIN previous_7_days p ON t.order_mode = p.order_mode ORDER BY t.revenue DESC
    ;
    """
    
    with conn.cursor() as cur:
        if filtered_city_query:
            cur.execute(filtered_city_query)
            city_rows = cur.fetchall()
            
            results['daily_revenue'] = {
                'this_week': [],
                'last_week': []
            }
            
            for row in city_rows:
         
                week_data = {
                    'date': row[1].strftime('%Y-%m-%d'),
                    'revenue': float(row[2]) if row[2] else 0
                }
               
                if row[0] == 'This Week':
                    results['daily_revenue']['this_week'].append(week_data)
                else:
                    results['daily_revenue']['last_week'].append(week_data)
                
        elif filtered_store_query: 
            cur.execute(filtered_store_query)
            store_rows = cur.fetchall()
            
            results['daily_revenue'] = {
                'this_week': [],
                'last_week': []
            }
            
            for row in store_rows:
                week_data = {
                    'date': row[0].strftime('%Y-%m-%d'),
                    'revenue': float(row[1]) if row[1] else 0
                }
                if row[2] == 'This Week':
                    results['daily_revenue']['this_week'].append(week_data)
                else:
                    results['daily_revenue']['last_week'].append(week_data)
                
        elif filtered_item_query:
            cur.execute(filtered_item_query)
            item_rows = cur.fetchall()
            
            results['daily_revenue'] = {
                'this_week': [],
                'last_week': []
            }
            
            for row in item_rows:
                week_data = {
                    'date': row[0].strftime('%Y-%m-%d'),
                    'revenue': float(row[1]) if row[1] else 0
                }
                if row[2] == 'This Week':
                    results['daily_revenue']['this_week'].append(week_data)
                else:
                    results['daily_revenue']['last_week'].append(week_data)
                    
        else: 
            # Fetch daily revenue data
            cur.execute(daily_revenue_query)
            daily_rows = cur.fetchall()
            
            # Split daily revenue into this week and last week
            results['daily_revenue'] = {
                'this_week': [],
                'last_week': []
            }
            
            for row in daily_rows:
                week_data = {
                    'date': row[0].strftime('%Y-%m-%d'),
                    'revenue': float(row[1]) if row[1] else 0
                }
                if row[2] == 'this_week':
                    results['daily_revenue']['this_week'].append(week_data)
                else:
                    results['daily_revenue']['last_week'].append(week_data)  
                    
        cur.execute(store_revenue_query)
        store_rows = cur.fetchall()
        results['store_revenue'] = [
            {
                'store': row[0],
                'city': row[1],
                'revenue_7d': float(row[2]) if row[2] else 0,
                'revenue_change_percent': float(row[4]) if row[4] is not None else 0
            }
            for row in store_rows
        ]

        # Fetch payment method revenue data
        cur.execute(payment_revenue_query)
        payment_rows = cur.fetchall()
        results['payment_revenue'] = [
            {
                'payment_method': row[0],
                'revenue_7d': float(row[1]) if row[1] else 0,
                'revenue_change_percent': float(row[3]) if row[3] is not None else 0
            }
            for row in payment_rows
        ]

        cur.execute(order_mode_revenue)
        order_rows = cur.fetchall()
        results['order_mode'] = [
            {
                'order_mode': row[0],
                'revenue_7d': float(row[1]) if row[1] else 0,
                'revenue_change_percent': float(row[3]) if row[3] is not None else 0
            }
            for row in order_rows
        ]
        
    if conn:
        conn.close()
    
    return JSONResponse(content=results)

@router.get("/sql/suggestions")
async def get_query_suggestions(request: Request, 
                                user_id: str = Query(..., description="User ID to fetch query history for")):
    text2sql_instance = request.state.injector.get(Text2SQL)
    conn = psycopg2.connect(
        host=POSTGRES_HOST,
        password=POSTGRES_PASSWORD,
        user='postgres',
        database=POSTGRES_DB
    )
    
    try:
        cursor = conn.cursor()
        
        # Get all relevant tables
        latest_tables_query = """
            SELECT filename as table_name 
            FROM csv_data.file_metadata
            WHERE user_id = %s
        """
        cursor.execute(latest_tables_query, (user_id,))
        tables = [row[0] for row in cursor.fetchall()]
        # print(tables)
        # Get schema and sample data for each table
        database_context = []
        for table in tables:
            # Get column information
            column_query = """
                SELECT column_name, data_type
                FROM information_schema.columns
                WHERE table_schema = 'csv_data'
                AND table_name = %s;
            """
            cursor.execute(column_query, (table,))
            columns = cursor.fetchall()
            # print(columns)
            # Get sample data
            sample_query = f"""
                SELECT *
                FROM csv_data."{table}"
                LIMIT 10;
            """
            cursor.execute(sample_query)
            sample_data = cursor.fetchall()
            # print(sample_data)
            # Format table context
            table_context = f"\nTable: {table}\n"
            table_context += "Columns:\n"
            for col_name, col_type in columns:
                table_context += f"- {col_name} ({col_type})\n"
            
            table_context += "\nSample Data:\n"
            for row in sample_data[:5]:  # Only show first 3 rows to keep context manageable
                table_context += f"{row}\n"
            
            database_context.append(table_context)
        # print(database_context)
        # Create prompt for LLM
        prompt = f"""
        Given these database tables and their sample data:
        
        {' '.join(database_context)}
        
        Generate 15 insightful non-graphical questions and 15 graphical (line, pie and bar chart) questions that could be answered using this data.
        Focus on:
        1. Trends and patterns in the data
        2. Comparative analysis
        3. Business insights
        4. Potential correlations
        5. Performance metrics
        
        Response should only be a array of strings of professtional concise questions without mentionaing type of charts.
        """
        
        # Generate suggestions using LLM
        response = text2sql_instance.llm.as_structured_llm(Suggestions).complete(prompt)
        # print(response)
        # suggestions = response.choices[0].message.content.strip().split('\n')
        suggestions = str(response)
        
        return {
            "status": "success",
            "suggestions": suggestions,
            "tables": tables
        }
        
    except Exception as e:
        return {
            "status": "error",
            "message": str(e)
        }
        
    finally:
        if conn:
            conn.close()

def generate_chart_url(chart_data: Dict[str, Any], width: int = 800, height: int = 400) -> str:
    """Generate a QuickChart URL for the given chart configuration with dimensions."""
    chart_data["options"] = {
			"responsive": True,
			"scales": {
				"y": {
					"beginAtZero": True}}}
    chart_config_str = json.dumps(chart_data)
    encoded_config = urllib.parse.quote(chart_config_str)
    base_url = "https://quickchart.io/chart"
    return f"{base_url}?w={width}&h={height}&c={encoded_config}"

async def send_email(request: EmailRequest, user_id, query_request: Request, redis: Redis):
    """Core email sending function"""
    try:
        url = "https://api.zeptomail.in/v1.1/email"
        chart_width = 800
        # print(user_id)
        # print(request.query)
        if hasattr(request, 'query'):
            # print(request.query)
            current_time_str = datetime.now(pytz.UTC).strftime("%Y-%m-%d %H:%M:%S.000Z")
            text2sql_instance = query_request.state.injector.get(Text2SQL)
            text2sql_instance.load_existing_database()
            text2sql_instance.retrieveSQL(user_id)
            is_safe, result, questions, suggestions, issues, chart_data = await text2sql_instance.execute_query(request.query, user_id, current_time_str, redis, None)
        
            # Convert markdown to HTML
            result_content = markdown2.markdown(result, extras=['tables', 'fenced-code-blocks'])
            html_content = f"{request.content}\n{result_content}"
            # Generate chart URL if chart data is provided
            chart_html = ""
            logger.info(f"Chat data: {chart_data}")
            if chart_data and chart_data['data'] and chart_data['type']:
                formatted_chart_data = convert_to_chartjs_format(chart_data)
                chart_url = generate_chart_url(
                    formatted_chart_data
                )
                logger.info(f"Formatted data: {formatted_chart_data}")
                chart_html = f"""
                    <div style="margin: 20px 0;">
                        <img src="{chart_url}" alt="Chart" style="width: 100%; max-width: {chart_width}px;">
                    </div>
                """
            
            # Construct the HTML content with styling
            email_html = f"""
            <html>
                <head>
                    <style>
                        body {{
                            font-family: Arial, sans-serif;
                            line-height: 1.6;
                            color: #333;
                            max-width: 800px;
                            margin: 0 auto;
                            padding: 20px;
                        }}
                        h1, h2, h3, h4, h5, h6 {{
                            color: #2c3e50;
                            margin-top: 24px;
                            margin-bottom: 16px;
                        }}
                        p {{
                            margin-bottom: 16px;
                        }}
                        code {{
                            background-color: #f6f8fa;
                            padding: 2px 4px;
                            border-radius: 3px;
                            font-family: monospace;
                        }}
                        pre {{
                            background-color: #f6f8fa;
                            padding: 16px;
                            border-radius: 6px;
                            overflow-x: auto;
                        }}
                        table {{
                            border-collapse: collapse;
                            width: 100%;
                            margin-bottom: 16px;
                        }}
                        th, td {{
                            border: 1px solid #ddd;
                            padding: 8px;
                            text-align: left;
                        }}
                        th {{
                            background-color: #f6f8fa;
                        }}
                        blockquote {{
                            border-left: 4px solid #ddd;
                            margin: 0;
                            padding-left: 16px;
                            color: #666;
                        }}
                        img {{
                            max-width: 100%;
                            height: auto;
                        }}
                        ul, ol {{
                            padding-left: 20px;
                            margin-bottom: 16px;
                        }}
                    </style>
                </head>
                <body>
                    {html_content}
                    {chart_html}
                </body>
            </html>
            """

        
        else:
            # Convert markdown to HTML
            result_content = markdown2.markdown(result, extras=['tables', 'fenced-code-blocks'])
            html_content = f"{request.content}\n{result_content}"
            
            # Generate chart URL if chart data is provided
            chart_html = ""
            if request.chart_data:
                chart_url = generate_chart_url(
                    request.chart_data,
                )
                chart_html = f"""
                    <div style="margin: 20px 0;">
                        <img src="{chart_url}" alt="Chart" style="width: 100%; max-width: {chart_width}px;">
                    </div>
                """
            
            # Construct the HTML content with styling
            email_html = f"""
            <html>
                <head>
                    <style>
                        body {{
                            font-family: Arial, sans-serif;
                            line-height: 1.6;
                            color: #333;
                            max-width: 800px;
                            margin: 0 auto;
                            padding: 20px;
                        }}
                        h1, h2, h3, h4, h5, h6 {{
                            color: #2c3e50;
                            margin-top: 24px;
                            margin-bottom: 16px;
                        }}
                        p {{
                            margin-bottom: 16px;
                        }}
                        code {{
                            background-color: #f6f8fa;
                            padding: 2px 4px;
                            border-radius: 3px;
                            font-family: monospace;
                        }}
                        pre {{
                            background-color: #f6f8fa;
                            padding: 16px;
                            border-radius: 6px;
                            overflow-x: auto;
                        }}
                        table {{
                            border-collapse: collapse;
                            width: 100%;
                            margin-bottom: 16px;
                        }}
                        th, td {{
                            border: 1px solid #ddd;
                            padding: 8px;
                            text-align: left;
                        }}
                        th {{
                            background-color: #f6f8fa;
                        }}
                        blockquote {{
                            border-left: 4px solid #ddd;
                            margin: 0;
                            padding-left: 16px;
                            color: #666;
                        }}
                        img {{
                            max-width: 100%;
                            height: auto;
                        }}
                        ul, ol {{
                            padding-left: 20px;
                            margin-bottom: 16px;
                        }}
                    </style>
                </head>
                <body>
                    {html_content}
                    {chart_html}
                </body>
            </html>
            """
            
        # print(request.from_address)
        from_dict = {
            "address": request.from_address.address if request.from_address else "noreply@yourekai.com"
        }
        
        # print(from_dict)
        
        # Add name to from_dict if provided
        if request.from_address and request.from_address.name:
            from_dict["name"] = request.from_address.name
        payload = {
            "from": from_dict,
            "to": [
                {
                    "email_address": {
                        "address": recipient.address,
                    }
                }
                for recipient in request.to
            ],
            "subject": request.subject,
            "htmlbody": email_html
        }  
            
        headers = {
            'accept': "application/json",
            'content-type': "application/json",
            'authorization': ZEPTO_KEY
        }
        
        response = requests.post(url, json=payload, headers=headers)
        response.raise_for_status()
        
        return response.json()
        
    except requests.exceptions.RequestException as e:
        logger.error(f"Email send error: {str(e)}")
        raise HTTPException(
            status_code=500,
            detail=f"Failed to send email: {str(e)}"
        )

@router.post("/sql/send-email")
async def send_unified_email(request: EmailRequest,
                             query_request: Request,
                             redis: Redis = Depends(get_redis_client),
                             user_id: str = Query(..., description="User ID to fetch query history for")):
    """Endpoint for immediate email sending"""
    response = await send_email(request, user_id, query_request, redis)
    return {
        "status": "success",
        "message": "Email sent successfully",
        "response": response
    }
             
@router.get("/sql/health")
async def health_check(request: Request):
    """Health check endpoint"""
    text2sql_instance = request.state.injector.get(Text2SQL)
    return {
        "status": "healthy",
        "initialized": text2sql_instance is not None
    }