"""
Initial Suggested Questions Routes
"""
from datetime import datetime, timedelta
from fastapi import APIRouter, HTTPException, Depends, Query, Form, File, UploadFile
from fastapi.concurrency import run_in_threadpool
from sqlalchemy.orm import Session
from sqlalchemy import text, or_, and_
from pydantic import BaseModel, Field
from typing import Optional, List, Dict, Any
import pandas as pd
import io
import traceback
from typing import Optional, List, Dict, Any, Union
import random

from app.core.config import settings
from app.core.database import get_db
from app.models.agent import Training
from app.models.question import Question, InitialSuggestionQuestion
from app.services.askme_service import get_askme_service, AskmeService
from app.services.llm_service import (
    claude_direct_api_enabled,
    oic_anthropic_integration_enabled,
)
from fastapi.concurrency import run_in_threadpool


router = APIRouter(prefix="/initial-suggested-questions", tags=["Initial Suggested Questions"])

# Monkey patch to fix missing constant in askme_service
import app.services.askme_service
app.services.askme_service.USER_MANAGEMENT_AVAILABLE = True

# ---------------------------------------------------------------------------
# Schemas
# ---------------------------------------------------------------------------

class QuestionDetail(BaseModel):
    question: str
    sql: Optional[str] = None
    chart: Optional[str] = None
    api_endpoint: Optional[str] = None
    api_params: Optional[str] = None
    api_response_validator: Optional[str] = None
    agent_id: Optional[str] = None


class SuggestedQuestionDetails(BaseModel):
    questions: List[str]
    question_details: List[QuestionDetail]
    suggestion_diagnostics: Optional[Dict[str, Any]] = None


class SuggestedQuestionResponse(BaseModel):
    success: bool = True
    message: str = "Suggested questions retrieved successfully"
    details: SuggestedQuestionDetails


class MyPromptsResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


class InitialSuggestedQuestionRequest(BaseModel):
    """
    ``datasource``: use ``"api"`` or ``"database"``. If omitted, the agent’s
    ``default_data_source_type`` from config is used (so API agents work without
    the client sending datasource on every call).
    """
    agent_id: Union[str, List[str]]
    limit: int = Field(default=5, ge=0, le=200)
    datasource: Optional[str] = None


class GetInitialQuestionsRequest(BaseModel):
    agent_id: str


class InitialQuestionItem(BaseModel):
    question: str
    sql: Optional[str] = None


class GetInitialQuestionsResponse(BaseModel):
    success: bool = True
    message: str = "Initial questions retrieved successfully"
    details: Dict[str, Any]  # questions: List[InitialQuestionItem]


def _normalize_single_agent_id(raw: Union[str, List[str], None]) -> str:
    if raw is None:
        return ""
    if isinstance(raw, list):
        if not raw:
            return ""
        return str(raw[0]).strip()
    return str(raw).strip()


def _resolve_suggested_datasource(
    request: "InitialSuggestedQuestionRequest", agent_id: str
) -> str:
    """
    Resolve ``"api"`` vs ``"database"`` for this router.

    If the client omits ``datasource`` or sends ``"auto"``, ``"all"``, ``"any"``, or
    ``"default"``, use the agent’s ``default_data_source_type`` from ``agent_management``.

    Note: ``"all"`` does **not** mean “query every datasource at once” here — it means
    “use the agent’s primary/default datasource” (same as ``auto``), so API agents
    resolve to ``api`` when that is their default.
    """
    raw = getattr(request, "datasource", None)
    s = (raw if raw is not None else "").strip().lower()

    if s in ("api", "rest", "oic", "http", "https"):
        return "api"
    if s in (
        "database",
        "db",
        "sql",
        "mysql",
        "mssql",
        "oracle",
        "postgres",
        "postgresql",
    ):
        return "database"
    if s in ("file", "document", "doc"):
        return "database"

    # Omitted or "follow agent default" — do not treat unknown tokens as database.
    if not s or s in ("auto", "all", "any", "default"):
        from app.services.agent_config import get_agent_config

        cfg = get_agent_config(agent_id=agent_id)
        if not cfg:
            return "database"
        d = (cfg.get("default_data_source_type") or "Database").strip().lower()
        if d in ("api", "rest", "oic"):
            return "api"
        if d in ("file", "document"):
            return "database"
        return "database"

    if s.startswith("api"):
        return "api"
    return "database"


def _build_suggestion_diagnostics(
    db: Session,
    agent_id: str,
    service: Any,
    datasource_resolved: str,
    datasource_in_request: Optional[str] = None,
) -> Dict[str, Any]:
    """
    Server-side facts for empty suggested-question responses (OIC vs OpenAI vs no training data).
    """
    from app.services.agent_config import get_agent_config

    oic = oic_anthropic_integration_enabled()
    c_direct = claude_direct_api_enabled()
    key_raw = (getattr(settings, "OPENAI_API_KEY", None) or "")
    key_ok = bool(key_raw and str(key_raw).strip())
    cfg = get_agent_config(agent_id=agent_id)
    ck_agent = (cfg.get("claude_api_key") or "").strip() if cfg else ""
    c_direct_agent = claude_direct_api_enabled(ck_agent) if ck_agent else c_direct
    try:
        n_tr = (
            db.query(Training)
            .filter(Training.agent_id == agent_id)
            .count()
        )
    except Exception:
        n_tr = -1
    try:
        n_init = (
            db.query(InitialSuggestionQuestion)
            .filter(InitialSuggestionQuestion.agent_id == agent_id)
            .count()
        )
    except Exception:
        n_init = -1
    if oic and key_ok:
        route = "oic (primary; OpenAI key also present for non-OIC code paths)"
    elif oic:
        route = "oic (oic_call + URL + OIC HTTP Basic auth)"
    elif c_direct_agent:
        route = "anthropic_direct (use_claude + Claude API key; oic_call off)"
    elif key_ok:
        route = "openai"
    else:
        route = "none (configure oic_call+OIC, OPENAI_API_KEY, or use_claude+Claude key; LLM init for API init questions may fail)"
    return {
        "oic_anthropic_integration_enabled": oic,
        "claude_direct_api_enabled": c_direct_agent,
        "openai_api_key_configured": key_ok,
        "llm_route_hint": route,
        "agent_config_found": bool(cfg),
        "training_table_row_count_for_agent": n_tr,
        "initial_suggestion_question_row_count": n_init,
        "askme_service_instantiated": service is not None,
        "datasource_in_request": datasource_in_request,
        "datasource_resolved": datasource_resolved,
        "datasource_requested": datasource_resolved,
    }


def _merge_api_initial_suggestions_from_orm(
    db: Session,
    agent_id: str,
    still_needed: int,
    questions_list: list,
    questions_details: dict,
) -> None:
    """Read initial_suggestion_question for API datasource without AskmeService (e.g. no agent config)."""
    if still_needed <= 0:
        return
    added = 0
    rows = (
        db.query(InitialSuggestionQuestion)
        .filter(InitialSuggestionQuestion.agent_id == agent_id)
        .order_by(InitialSuggestionQuestion.created_at.asc())
        .limit(500)
        .all()
    )
    for r in rows:
        if not AskmeService._row_matches_suggestion_datasource(
            r.question_data_source, "api"
        ):
            continue
        if r.question in questions_details:
            continue
        questions_list.append(r.question)
        questions_details[r.question] = {
            "id": r.id,
            "sql": r.sql,
            "chart": None,
            "api_endpoint": r.api_endpoint,
            "api_params": r.api_params,
            "api_response_validator": r.api_response_validator,
            "agent_id": agent_id,
        }
        added += 1
        if added >= still_needed:
            break


@router.post("/get-initial-questions")
async def get_initial_questions(
    request: GetInitialQuestionsRequest,
    db: Session = Depends(get_db)
):
    """
    Get initial questions for an agent by agent_id.
    Returns questions saved from Excel or from generate-initial-questions API,
    each with its associated SQL.
    """
    try:
        rows = db.query(InitialSuggestionQuestion).filter(
            InitialSuggestionQuestion.agent_id == request.agent_id
        ).order_by(InitialSuggestionQuestion.id).all()
        questions = [
            {
                "question": q.question or "",
                "sql": q.sql or None
            }
            for q in rows
        ]
        return {
            "success": True,
            "message": "Initial questions retrieved successfully",
            "details": {
                "questions": questions
            }
        }
    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=str(e))


# ---------------------------------------------------------------------------
# Single-agent endpoint
# ---------------------------------------------------------------------------

@router.post("/by-agent", response_model=SuggestedQuestionResponse)
async def get_initial_suggested_questions(
    request: InitialSuggestedQuestionRequest,
    db: Session = Depends(get_db)
):
    """
    Get initial suggested questions for a specific (single) agent based on datasource.
    """
    try:
        agent_id = _normalize_single_agent_id(request.agent_id)
        if not agent_id:
            return {
                "success": False,
                "message": "No agent_id provided",
                "details": {"questions": [], "question_details": []}
            }

        ds = _resolve_suggested_datasource(request, agent_id)

        # 1. Fetch saved questions for this agent
        query = db.query(Question).filter(Question.agent_id == agent_id)

        if ds == "api":
            query = query.filter(
                or_(
                    and_(Question.api_endpoint != None, Question.api_endpoint != ""),
                    and_(Question.api_params != None, Question.api_params != ""),
                    and_(Question.api_response_validator != None, Question.api_response_validator != "")
                )
            )
        else:
            query = query.filter(
                or_(Question.api_endpoint == None, Question.api_endpoint == ""),
                or_(Question.api_params == None, Question.api_params == ""),
                or_(Question.api_response_validator == None, Question.api_response_validator == "")
            )

        saved_questions = query.order_by(text("timestamp DESC")).limit(request.limit).all()

        questions_list = []
        questions_details = {}

        for q in saved_questions:
            questions_list.append(q.question)
            questions_details[q.question] = {
                "id": q.id,
                "sql": q.sql,
                "chart": q.chart,
                "api_endpoint": q.api_endpoint,
                "api_params": q.api_params,
                "api_response_validator": q.api_response_validator,
                "agent_id": q.agent_id
            }

        # 2. If we need more questions, generate/fetch from service
        needed = max(0, request.limit - len(saved_questions))
        service = None
        if needed > 0:
            service = get_askme_service(agent_id=agent_id)
            if service:
                if ds == "api":
                    api_questions_str = service.generate_all_api_init_questions(
                        count_per_api=needed,
                        agent_id=agent_id
                    )
                    if api_questions_str:
                        generated = [q.strip() for q in str(api_questions_str).split('\n') if q.strip()]
                        for q_text in generated[:needed]:
                            questions_list.append(q_text)
                            questions_details[q_text] = {
                                "id": None, "sql": None, "chart": None,
                                "api_endpoint": None, "api_params": None,
                                "api_response_validator": None, "agent_id": agent_id
                            }
                    # Same table as the database path: /generate-initial-questions stores API
                    # suggestions in initial_suggestion_question, while generate_all_api_init_questions
                    # needs rows in `training` — so fill the gap from pre-generated API rows when needed.
                    still_needed = max(0, request.limit - len(questions_list))
                    if still_needed > 0:
                        iq_list, iq_details = service.get_agent_questions_from_db(
                            agent_id=agent_id,
                            limit=still_needed,
                            datasource_filter="api"
                        )
                        if iq_list:
                            for q_text in iq_list:
                                questions_list.append(q_text)
                                detail = iq_details.get(q_text, {})
                                detail["agent_id"] = agent_id
                                questions_details[q_text] = detail
                else:
                    iq_list, iq_details = service.get_agent_questions_from_db(
                        agent_id=agent_id,
                        limit=needed
                    )
                    if iq_list:
                        for q_text in iq_list:
                            questions_list.append(q_text)
                            detail = iq_details.get(q_text, {})
                            detail["agent_id"] = agent_id
                            questions_details[q_text] = detail
            elif ds == "api" and not service:
                # No agent config → get_askme_service is None: still return pre-stored API suggestions if any
                _merge_api_initial_suggestions_from_orm(
                    db,
                    str(agent_id),
                    max(0, request.limit - len(questions_list)),
                    questions_list,
                    questions_details,
                )

        # Combine saved + initial with no ordering preference
        if questions_list:
            random.shuffle(questions_list)

        if not questions_list:
            if service is None:
                service = get_askme_service(agent_id=agent_id)
            return {
                "success": True,
                "message": "Suggested questions are not available",
                "details": {
                    "questions": [],
                    "question_details": [],
                    "suggestion_diagnostics": _build_suggestion_diagnostics(
                        db,
                        str(agent_id),
                        service,
                        ds,
                        getattr(request, "datasource", None),
                    ),
                },
            }

        question_details_list = []
        for q in questions_list:
            detail = questions_details.get(q, {})
            question_details_list.append({
                "question": q,
                "sql": detail.get("sql"),
                "chart": detail.get("chart"),
                "api_endpoint": detail.get("api_endpoint"),
                "api_params": detail.get("api_params"),
                "api_response_validator": detail.get("api_response_validator"),
                "agent_id": detail.get("agent_id")
            })

        return {
            "success": True,
            "message": "Suggested questions retrieved successfully",
            "details": {
                "questions": questions_list,
                "question_details": question_details_list
            }
        }

    except Exception as e:
        return {
            "success": False,
            "message": f"Error retrieving suggested questions: {str(e)}",
            "details": {"questions": [], "question_details": []}
        }


# ---------------------------------------------------------------------------
# Multi-agent endpoint
# ---------------------------------------------------------------------------

@router.post("/multi-agent-by-agent", response_model=SuggestedQuestionResponse)
async def get_multi_agent_initial_suggested_questions(
    request: InitialSuggestedQuestionRequest,
    db: Session = Depends(get_db)
):
    """
    Get initial suggested questions for multiple agents based on datasource.
    Questions are distributed equally among agents (remainder goes to the first agents).
    """
    try:
        # Normalize agent_id to list
        if isinstance(request.agent_id, list):
            agent_ids = request.agent_id
        else:
            agent_ids = [request.agent_id]

        if not agent_ids:
            return {
                "success": False,
                "message": "No agent_id provided",
                "details": {"questions": [], "question_details": []}
            }

        agent_ids = [_normalize_single_agent_id(a) for a in agent_ids if _normalize_single_agent_id(a)]
        if not agent_ids:
            return {
                "success": False,
                "message": "No agent_id provided",
                "details": {"questions": [], "question_details": []}
            }

        ds = _resolve_suggested_datasource(request, agent_ids[0])

        num_agents = len(agent_ids)
        base_count = request.limit // num_agents
        remainder = request.limit % num_agents

        # Distribute: first `remainder` agents get one extra question
        distribution = [base_count + 1 if i < remainder else base_count for i in range(num_agents)]

        questions_list = []
        questions_details = {}

        for aid, count in zip(agent_ids, distribution):
            if count <= 0:
                continue

            n_len_before_agent = len(questions_list)

            # 1. Fetch saved questions for this agent
            query = db.query(Question).filter(Question.agent_id == aid)

            if ds == "api":
                query = query.filter(
                    or_(
                        and_(Question.api_endpoint != None, Question.api_endpoint != ""),
                        and_(Question.api_params != None, Question.api_params != ""),
                        and_(Question.api_response_validator != None, Question.api_response_validator != "")
                    )
                )
            else:
                query = query.filter(
                    or_(Question.api_endpoint == None, Question.api_endpoint == ""),
                    or_(Question.api_params == None, Question.api_params == ""),
                    or_(Question.api_response_validator == None, Question.api_response_validator == "")
                )

            saved_questions = query.order_by(text("timestamp DESC")).limit(count).all()

            for q in saved_questions:
                questions_list.append(q.question)
                questions_details[q.question] = {
                    "id": q.id,
                    "sql": q.sql,
                    "chart": q.chart,
                    "api_endpoint": q.api_endpoint,
                    "api_params": q.api_params,
                    "api_response_validator": q.api_response_validator,
                    "agent_id": q.agent_id
                }

            # 2. If we need more questions, generate/fetch from service
            needed = max(0, count - len(saved_questions))
            if needed > 0:
                service = get_askme_service(agent_id=aid)
                if service:
                    if ds == "api":
                        api_questions_str = service.generate_all_api_init_questions(
                            count_per_api=needed,
                            agent_id=aid
                        )
                        if api_questions_str:
                            generated = [q.strip() for q in str(api_questions_str).split('\n') if q.strip()]
                            for q_text in generated[:needed]:
                                questions_list.append(q_text)
                                questions_details[q_text] = {
                                    "id": None, "sql": None, "chart": None,
                                    "api_endpoint": None, "api_params": None,
                                    "api_response_validator": None, "agent_id": aid
                                }
                        still_needed = count - (len(questions_list) - n_len_before_agent)
                        if still_needed > 0:
                            iq_list, iq_details = service.get_agent_questions_from_db(
                                agent_id=aid,
                                limit=still_needed,
                                datasource_filter="api"
                            )
                            if iq_list:
                                for q_text in iq_list:
                                    questions_list.append(q_text)
                                    detail = iq_details.get(q_text, {})
                                    detail["agent_id"] = aid
                                    questions_details[q_text] = detail
                    else:
                        iq_list, iq_details = service.get_agent_questions_from_db(
                            agent_id=aid,
                            limit=needed
                        )
                        if iq_list:
                            for q_text in iq_list:
                                questions_list.append(q_text)
                                detail = iq_details.get(q_text, {})
                                detail["agent_id"] = aid
                                questions_details[q_text] = detail
                elif ds == "api" and not service:
                    still_needed = count - (len(questions_list) - n_len_before_agent)
                    _merge_api_initial_suggestions_from_orm(
                        db, str(aid), max(0, still_needed), questions_list, questions_details
                    )

            # Per agent: shuffle saved + initial together (independent order per agent)
            tail = questions_list[n_len_before_agent:]
            if tail:
                random.shuffle(tail)
                questions_list[n_len_before_agent:] = tail

        if not questions_list:
            s0 = get_askme_service(agent_id=agent_ids[0]) if agent_ids else None
            return {
                "success": True,
                "message": "Suggested questions are not available",
                "details": {
                    "questions": [],
                    "question_details": [],
                    "suggestion_diagnostics": _build_suggestion_diagnostics(
                        db,
                        str(agent_ids[0]),
                        s0,
                        ds,
                        getattr(request, "datasource", None),
                    )
                    if agent_ids
                    else None,
                },
            }

        question_details_list = []
        for q in questions_list:
            detail = questions_details.get(q, {})
            question_details_list.append({
                "question": q,
                "sql": detail.get("sql"),
                "chart": detail.get("chart"),
                "api_endpoint": detail.get("api_endpoint"),
                "api_params": detail.get("api_params"),
                "api_response_validator": detail.get("api_response_validator"),
                "agent_id": detail.get("agent_id")
            })

        return {
            "success": True,
            "message": "Suggested questions retrieved successfully",
            "details": {
                "questions": questions_list,
                "question_details": question_details_list
            }
        }

    except Exception as e:
        return {
            "success": False,
            "message": f"Error retrieving suggested questions: {str(e)}",
            "details": {"questions": [], "question_details": []}
        }


# ---------------------------------------------------------------------------
# My Prompts
# ---------------------------------------------------------------------------

class MyPromptsRequest(BaseModel):
    user_id: str
    agent_id: str


@router.post("/my-prompts", response_model=MyPromptsResponse)
async def get_my_prompts(
    request: MyPromptsRequest,
    db: Session = Depends(get_db)
):
    """
    Get saved questions (my prompts) for a user and agent.
    Replicates logic from askme_main.py: get_saved_questions + date categorization
    """
    try:
        query = db.query(Question)

        if request.user_id != "1":
            query = query.filter(Question.user_id == request.user_id, Question.agent_id == request.agent_id)
        else:
            # Admin user 1 sees all questions for the agent
            query = query.filter(Question.agent_id == request.agent_id)

        query = query.order_by(text("timestamp DESC"))
        questions = await run_in_threadpool(query.all)

        results = []
        for q in questions:
            q_dict = {
                "id": q.id,
                "user_id": q.user_id,
                "agent_id": q.agent_id,
                "username": q.username,
                "question": q.question,
                "sql": q.sql,
                "chart": q.chart,
                "timestamp": q.timestamp,
                "api_endpoint": q.api_endpoint,
                "api_params": q.api_params,
                "api_response_validator": q.api_response_validator,
                "created_at": q.created_at,
                "updated_at": q.updated_at,
            }
            results.append(q_dict)

        return {
            "success": True,
            "message": "My prompts retrieved successfully",
            "details": {"questions": results}
        }

    except Exception as e:
        return {
            "success": False,
            "message": f"Error retrieving my prompts: {str(e)}",
            "details": {}
        }


# ==================== GENERATE INITIAL QUESTIONS API ====================

class GenerateQuestionsResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


def _extract_questions_from_excel(df) -> tuple:
    """
    Extract questions from an Excel DataFrame with strict column validation.
    First column must be 'id', second column must be 'question' (case-insensitive).
    
    Returns:
        tuple: (success: bool, result: list|str)
            - On success: (True, list of dicts [{'id': ..., 'question': ...}, ...])
            - On failure: (False, error_message_string)
    """
    if len(df.columns) < 2:
        return (False, "Excel file must have at least 2 columns: 'id' and 'question'")
    
    # Validate column names (case-insensitive)
    first_col = df.columns[0].strip().lower()
    second_col = df.columns[1].strip().lower()
    
    if first_col != 'id':
        return (False, f"First column must be 'id', found '{df.columns[0]}'")
    
    # Allow 'question' or 'questions'
    if second_col not in ['question', 'questions']:
        return (False, f"Second column must be 'question' or 'questions', found '{df.columns[1]}'")
    
    # Use actual column names (preserve original case for access)
    id_col = df.columns[0]
    question_col = df.columns[1]
    
    # Extract rows with non-empty questions
    questions = []
    for _, row in df.iterrows():
        q_text = str(row[question_col]).strip() if pd.notna(row[question_col]) else ""
        q_id = str(row[id_col]).strip() if pd.notna(row[id_col]) else ""
        
        if q_text and q_text.lower() != 'nan':
            questions.append({
                'id': q_id,
                'question': q_text
            })
    
    return (True, questions)


@router.post("/generate-initial-questions", response_model=GenerateQuestionsResponse)
async def generate_initial_questions_api(
    agent_id: str = Form(...),
    file: Optional[UploadFile] = File(None),
    db: Session = Depends(get_db)
):
    """
    Generate initial suggested questions for an agent.
    
    Supports two modes:
    - **With Excel file**: Reads questions from the uploaded Excel file (.xlsx/.xls),
      generates SQL for each question using LLM with validation (1 retry),
      and saves all questions to the database.
    - **Without file**: Uses the existing default question generation logic
      (LLM generates both questions and SQL from training data).
    
    Args:
        agent_id: The agent ID to generate questions for
        file: Optional Excel file (.xlsx/.xls) containing questions
    """
    try:
        # Initialize AskmeService via the mediator
        service = get_askme_service(agent_id=agent_id)
        if not service:
            return {
                "success": False,
                "message": f"Could not initialize service for agent {agent_id}",
                "details": {
                    "questions_saved": 0,
                    "questions_failed": 0,
                    "questions": []
                }
            }
        
        # Get agent config for metadata (question_data_source, init_training_data)
        from app.services.agent_config import get_agent_config
        agent_conf = get_agent_config(agent_id=agent_id)
        if not agent_conf:
            return {
                "success": False,
                "message": f"Could not load agent configuration for agent {agent_id}",
                "details": {
                    "questions_saved": 0,
                    "questions_failed": 0,
                    "questions": []
                }
            }
        
        default_data_source_type = agent_conf.get("default_data_source_type", "database")
        
        # CRITICAL: Ensure the agent has the DDL/Metadata context for SQL generation
        # Get training data from agent configuration
        init_training_data = agent_conf.get("traning_data", agent_conf.get("init_training_data", ""))
        if init_training_data:
            print(f"📋 Loading metadata context for agent {agent_id}...")
            # Use run_in_threadpool because loading/embedding metadata can be blocking
            await run_in_threadpool(service.add_ddl, init_training_data)
            print(f"✓ Metadata context loaded successfully")
        
        saved_count = 0
        failed_count = 0
        questions_detail_list = []
        
        # ============================================================
        # BRANCH: Excel file uploaded → read questions, generate SQL
        # ============================================================
        if file is not None and hasattr(file, 'filename') and file.filename:
            # Validate file extension
            filename = file.filename.lower()
            if not (filename.endswith('.xlsx') or filename.endswith('.xls')):
                return {
                    "success": False,
                    "message": "Invalid file format. Please upload an Excel file (.xlsx or .xls)",
                    "details": {
                        "questions_saved": 0,
                        "questions_failed": 0,
                        "questions": []
                    }
                }
            
            # Read the Excel file (file.read is async; pd.read_excel is blocking → threadpool)
            contents = await file.read()
            try:
                def _read_excel():
                    return pd.read_excel(io.BytesIO(contents))
                excel_df = await run_in_threadpool(_read_excel)
            except Exception as read_err:
                return {
                    "success": False,
                    "message": f"Error reading Excel file: {str(read_err)}",
                    "details": {
                        "questions_saved": 0,
                        "questions_failed": 0,
                        "questions": []
                    }
                }
            
            # Extract questions from the Excel file (blocking → threadpool)
            extract_success, extract_result = await run_in_threadpool(
                _extract_questions_from_excel, excel_df
            )
            
            if not extract_success:
                return {
                    "success": False,
                    "message": f"Invalid Excel format: {extract_result}. Expected first column 'id' and second column 'question' or 'questions'.",
                    "details": {
                        "questions_saved": 0,
                        "questions_failed": 0,
                        "questions": []
                    }
                }
            
            questions_from_excel = extract_result
            
            # Remove questions that already exist in the database for this agent
            existing = db.query(InitialSuggestionQuestion).filter(
                InitialSuggestionQuestion.agent_id == agent_id
            ).all()
            existing_texts = {q.question.strip().lower() for q in existing if q.question}
            original_count = len(questions_from_excel)
            questions_from_excel = [
                q for q in questions_from_excel
                if (q.get("question") or "").strip().lower() not in existing_texts
            ]
            skipped_duplicates = original_count - len(questions_from_excel)
            if skipped_duplicates > 0:
                print(f"📋 Skipped {skipped_duplicates} questions already present for agent {agent_id}")
            
            if not questions_from_excel:
                dup_msg = f" All {original_count} question(s) were already present for this agent." if skipped_duplicates > 0 else ""
                return {
                    "success": False,
                    "message": f"No new questions to process.{dup_msg} Ensure there are rows with data in the 'id' and 'question/questions' columns that are not already in the database.",
                    "details": {
                        "questions_saved": 0,
                        "questions_failed": 0,
                        "questions": []
                    }
                }
            
            # Cap at 100 questions to avoid excessive LLM calls
            if len(questions_from_excel) > 100:
                questions_from_excel = questions_from_excel[:100]
                print(f"⚠ Capped questions to 100 (from {len(questions_from_excel)} in Excel)")
            
            print(f"📋 Processing {len(questions_from_excel)} questions from Excel for agent {agent_id}")
            
            # For each question, generate SQL with validation + rephrase fallback via service mediator
            for idx, q_item in enumerate(questions_from_excel):
                q_text = q_item['question']
                q_id = q_item['id']
                print(f"\n[{idx + 1}/{len(questions_from_excel)}] (id={q_id}) Processing: {q_text[:80]}...")
                
                result = await run_in_threadpool(
                    service.generate_sql_for_question,
                    question=q_text,
                    max_retries=0
                )
                
                # Determine SQL and question_type based on datasource
                if default_data_source_type.lower() == 'database':
                    question_type = ""
                    sql_value = result.get('sql', '') if result.get('success') else ""
                elif default_data_source_type.lower() == 'api':
                    question_type = ""
                    sql_value = ""
                else:  # File or other
                    question_type = ""
                    sql_value = ""
                
                # Save to InitialSuggestionQuestion table (save ONLY if SQL validation succeeded)
                try:
                    if result.get('success'):
                        question_entry = InitialSuggestionQuestion(
                            question=q_text,
                            agent_id=agent_id,
                            question_data_source=default_data_source_type,
                            question_type=question_type,
                            sql=sql_value,
                            api_endpoint="",
                            api_params="",
                            api_response_validator=""
                        )
                        db.add(question_entry)
                        saved_count += 1
                        print(f"  ✓ Saved to DB")
                    else:
                        failed_count += 1
                        print(f"  ✗ Skipping DB save (SQL failed)")
                    
                    questions_detail_list.append({
                        "excel_id": q_id,
                        "question": q_text,
                        "rephrased_question": result.get('rephrased_question', None),
                        "sql": sql_value,
                        "success": result.get('success', False),
                        "error": result.get('error', None),
                        "source": "excel"
                    })
                    
                except Exception as save_err:
                    print(f"  ✗ Error during save logic: {save_err}")
                    failed_count += 1
                    questions_detail_list.append({
                        "excel_id": q_id,
                        "question": q_text,
                        "rephrased_question": None,
                        "sql": "",
                        "success": False,
                        "error": str(save_err),
                        "source": "excel"
                    })
                    continue
            
            db.commit()
            
            success_count = saved_count
            return {
                "success": True,
                "message": f"Processed {original_count} questions from Excel. {success_count} with validated SQL, {failed_count} without SQL.{f' Skipped {skipped_duplicates} duplicates.' if skipped_duplicates > 0 else ''}",
                "details": {
                    "questions_saved": saved_count,
                    "questions_with_sql": success_count,
                    "questions_failed": failed_count,
                    "skipped_duplicates": skipped_duplicates,
                    "questions": questions_detail_list
                }
            }
        
        # ============================================================
        # BRANCH: No file → use default generation logic
        # ============================================================
        else:
            num_questions = 25
                
            init_training_data = agent_conf.get("traning_data", agent_conf.get("init_training_data", ""))
            
            print(f"📋 Generating {num_questions} questions using default logic for agent {agent_id}")
            
            # Use existing generate_initial_questions via service mediator (LLM blocking → threadpool)
            questions_with_sql = await run_in_threadpool(
                service.generate_initial_questions,
                init_training_data,
                num_questions
            )
            
            if not questions_with_sql:
                return {
                    "success": True,
                    "message": "No questions could be generated from training data",
                    "details": {
                        "questions_saved": 0,
                        "questions_failed": 0,
                        "questions": []
                    }
                }
            
            # Save each generated question
            for q in questions_with_sql:
                try:
                    if default_data_source_type.lower() == 'database':
                        question_type = ""
                        sql_value = q.get('sql', '')
                    elif default_data_source_type.lower() == 'api':
                        question_type = ""
                        sql_value = ""
                    else:
                        question_type = ""
                        sql_value = ""
                    
                    question_entry = InitialSuggestionQuestion(
                        question=q['question'],
                        agent_id=agent_id,
                        question_data_source=default_data_source_type,
                        question_type=question_type,
                        sql=sql_value if sql_value else "",
                        api_endpoint="",
                        api_params="",
                        api_response_validator=""
                    )
                    db.add(question_entry)
                    saved_count += 1
                    
                    questions_detail_list.append({
                        "question": q['question'],
                        "sql": sql_value if sql_value else "",
                        "success": True,
                        "source": "training_data"
                    })
                    
                except Exception as save_err:
                    print(f"Error saving question: {save_err}")
                    failed_count += 1
                    continue
            
            db.commit()
            
            return {
                "success": True,
                "message": f"Generated {saved_count} initial questions successfully",
                "details": {
                    "questions_saved": saved_count,
                    "questions_failed": failed_count,
                    "questions": questions_detail_list
                }
            }
    
    except Exception as e:
        traceback.print_exc()
        return {
            "success": False,
            "message": f"Error generating initial questions: {str(e)}",
            "details": {
                "questions_saved": 0,
                "questions_failed": 0,
                "questions": []
            }
        }


