import requests
import json
import time
import ast
import pandas as pd
from openai import OpenAI
from datetime import datetime

from app.services.llm_service import LLMService, oic_anthropic_integration_enabled
import sqlite3
from typing import Any, Dict, List, Optional, Tuple
from urllib.parse import urljoin, urlencode
import re
from difflib import get_close_matches
from app.core.config import settings, api_request_timeout_seconds


# --- Outbound API timeout helpers (5-minute policy) -----------------------------
# Centralised so REST and SAP OData paths emit a consistent error and all read
# the timeout from a single place. LLM (httpx) are
# intentionally NOT covered here — see TODOs in llm_service.py

_TIMEOUT_MESSAGE_PREFIX = "Request timed out after"


class APIRequestTimeoutError(Exception):
    """Raised when an outbound REST/SAP OData call exceeds the configured timeout.

    Used internally by ``call_sap_odata_api`` to abort multi-page fetches once
    the cumulative wall-clock exceeds ``api_request_timeout_seconds()``. Callers
    convert it to a user-facing message via ``_format_timeout_message``.
    """


def _format_timeout_message(endpoint: Optional[str], seconds: Optional[int] = None) -> str:
    """Return the canonical timeout error string used across the API layer.

    Format: ``"Request timed out after {N} seconds calling {endpoint}"`` so
    upstream callers (multi-API merge, FastAPI handlers, pipeline) can detect
    timeouts via ``message.startswith(_TIMEOUT_MESSAGE_PREFIX)``.
    """
    secs = int(seconds) if seconds is not None else api_request_timeout_seconds()
    ep = (endpoint or "").strip() or "<unknown endpoint>"
    return f"{_TIMEOUT_MESSAGE_PREFIX} {secs} seconds calling {ep}"


def _is_timeout_message(message: Any) -> bool:
    """True when *message* looks like a formatted timeout error from this module."""
    if not isinstance(message, str):
        return False
    return _TIMEOUT_MESSAGE_PREFIX in message


def _normalize_agent_id(agent_id: Any) -> Optional[str]:
    """Normalize agent identifier while preserving leading zeros (e.g. 0690)."""
    if agent_id is None:
        return None
    normalized = str(agent_id).strip()
    return normalized or None


def _normalize_api_name_token(value: Any) -> str:
    """Normalize API names for tolerant matching (space/underscore/hyphen insensitive)."""
    return re.sub(r"[\s_\-]+", "", str(value or "").lower()).strip()


def _parse_llm_json_object(raw_text: str, default: Optional[Dict[str, Any]] = None) -> Dict[str, Any]:
    """
    Parse model JSON robustly.
    Falls back through fenced-text cleanup, JSON-object extraction, and literal_eval.
    """
    default_obj: Dict[str, Any] = default.copy() if isinstance(default, dict) else {}
    if not raw_text or not isinstance(raw_text, str):
        return default_obj

    text = raw_text.strip()
    if text.startswith("```"):
        chunks = text.split("```")
        if len(chunks) >= 2:
            text = chunks[1]
        if text.startswith("json"):
            text = text[4:]
        text = text.strip()

    def _dict_or_default(candidate: Any) -> Dict[str, Any]:
        return candidate if isinstance(candidate, dict) else default_obj

    try:
        return _dict_or_default(json.loads(text))
    except json.JSONDecodeError:
        pass

    object_match = re.search(r"\{[\s\S]*\}", text)
    if object_match:
        try:
            return _dict_or_default(json.loads(object_match.group(0)))
        except json.JSONDecodeError:
            pass

    try:
        return _dict_or_default(ast.literal_eval(text))
    except (ValueError, SyntaxError):
        return default_obj


def _normalize_agent_id(agent_id: Any) -> Optional[str]:
    """Normalize agent identifier while preserving leading zeros (e.g. 0690)."""
    if agent_id is None:
        return None
    normalized = str(agent_id).strip()
    return normalized or None


def _normalize_api_name_token(value: Any) -> str:
    """Normalize API names for tolerant matching (space/underscore/hyphen insensitive)."""
    return re.sub(r"[\s_\-]+", "", str(value or "").lower()).strip()


def _parse_llm_json_object(raw_text: str, default: Optional[Dict[str, Any]] = None) -> Dict[str, Any]:
    """
    Parse model JSON robustly.
    Falls back through fenced-text cleanup, JSON-object extraction, and literal_eval.
    """
    default_obj: Dict[str, Any] = default.copy() if isinstance(default, dict) else {}
    if not raw_text or not isinstance(raw_text, str):
        return default_obj

    text = raw_text.strip()
    if text.startswith("```"):
        chunks = text.split("```")
        if len(chunks) >= 2:
            text = chunks[1]
        if text.startswith("json"):
            text = text[4:]
        text = text.strip()

    def _dict_or_default(candidate: Any) -> Dict[str, Any]:
        return candidate if isinstance(candidate, dict) else default_obj

    try:
        return _dict_or_default(json.loads(text))
    except json.JSONDecodeError:
        pass

    object_match = re.search(r"\{[\s\S]*\}", text)
    if object_match:
        try:
            return _dict_or_default(json.loads(object_match.group(0)))
        except json.JSONDecodeError:
            pass

    try:
        return _dict_or_default(ast.literal_eval(text))
    except (ValueError, SyntaxError):
        return default_obj


# --- SAP training row routing (extra1): OData URL 


def is_sap_odata_endpoint(endpoint: Optional[str]) -> bool:
    """
    Args:
        endpoint: Training ``endpoint`` (OData service root URL).

    Returns:
        True if the value looks like an HTTP(S) OData base URL.
    """
    e = (endpoint or "").strip().lower()
    return e.startswith("http://") or e.startswith("https://")


def use_sap_odata(is_sap: str, endpoint: Optional[str]) -> bool:
    """
    Args:
        is_sap: Training ``extra1`` value.
        endpoint: Training ``endpoint`` (OData URL or RFC function name).

    Returns:
        True when the row should use ``process_sap_query`` (OData).
    """
    if is_sap != "Y":
        return False
    ep = (endpoint or "").strip()
    if not ep:
        return True
    return is_sap_odata_endpoint(ep)





def ensure_unique_dataframe_columns(df: pd.DataFrame) -> pd.DataFrame:
    """
    Ensure column labels are unique so ``DataFrame.to_dict('records')`` does not omit keys.

    Pandas warns and drops duplicate keys when serializing rows to JSON-like dicts.
    """
    if df is None or not isinstance(df, pd.DataFrame) or df.empty:
        return df
    if df.columns.is_unique:
        return df
    out = df.copy()
    counts: Dict[str, int] = {}
    new_names: List[str] = []
    for c in out.columns:
        base = str(c)
        n = counts.get(base, 0)
        counts[base] = n + 1
        new_names.append(base if n == 0 else f"{base}_{n}")
    out.columns = new_names
    return out


def _extract_column_labels_from_hash_comments(text: str) -> Dict[str, str]:
    """
    Parse ``"FIELD": "type", # Human meaning`` lines (JSON-with-comments style).
    Meanings are taken from the ``#`` segment; field names and types are not hard-coded.
    """
    out: Dict[str, str] = {}
    if not text or not isinstance(text, str):
        return out
    # Line-based: training schemas usually put one property per line before ``#``.
    line_pat = re.compile(
        r'^\s*"([^"\\]+)"\s*:\s*"[^"]*"\s*,?\s*#\s*(.+?)\s*$'
    )
    for line in text.splitlines():
        m = line_pat.match(line)
        if m:
            label = m.group(2).strip()
            if label:
                out[m.group(1)] = label
    return out


def _looks_like_json_schema_type_token(value: str) -> bool:
    """True if ``value`` is a typical JSON-schema / OpenAPI type name, not a display label."""
    v = value.strip().lower()
    if not v:
        return False
    return v in {
        "string",
        "number",
        "integer",
        "int",
        "float",
        "double",
        "boolean",
        "bool",
        "object",
        "array",
        "null",
        "any",
    }


def _strip_trailing_hash_comments_for_json(text: str) -> str:
    """
    Remove ``# ...`` from end of each line when it follows a JSON property value
    (so ``json.loads`` can run). Does not hard-code field names.
    """
    if not text:
        return text
    out_lines: List[str] = []
    # After a quoted value, optional comma, optional spaces, then # comment to EOL
    strip_pat = re.compile(r'^(\s*"[^"]+"\s*:\s*"[^"]*"\s*,?)\s*#.*$')
    for line in text.splitlines():
        s = line.rstrip("\r\n")
        m = strip_pat.match(s)
        out_lines.append(m.group(1).rstrip() if m else s)
    return "\n".join(out_lines)


def _parse_response_schema_to_column_labels(response_schema: Any) -> Dict[str, str]:
    """
    Build technical column name -> human-readable label from training ``response_schema``.

    Supports: JSON-with-``#`` comments (``"KEY": "type", # meaning``); JSON string;
    JSON Schema ``properties``; ``columns`` array; list of
    ``{name|field|column, description|label|title|meaning}``; flat ``{FIELD: "Label"}``;
    OpenAPI-style ``{FIELD: {"description": "..."}}``.
    """
    out: Dict[str, str] = {}
    if response_schema is None:
        return out
    raw: Any = response_schema
    if isinstance(raw, str):
        s = raw.strip()
        if not s:
            return out
        hash_labels = _extract_column_labels_from_hash_comments(s)
        if hash_labels:
            return hash_labels
        cleaned = _strip_trailing_hash_comments_for_json(s)
        try:
            raw = json.loads(cleaned)
        except json.JSONDecodeError:
            try:
                raw = json.loads(s)
            except json.JSONDecodeError:
                return out
    if isinstance(raw, list):
        for item in raw:
            if not isinstance(item, dict):
                continue
            key = (
                item.get("name")
                or item.get("field")
                or item.get("column")
                or item.get("key")
                or item.get("property")
            )
            label = (
                item.get("description")
                or item.get("label")
                or item.get("title")
                or item.get("meaning")
                or item.get("text")
            )
            if key and label:
                out[str(key).strip()] = str(label).strip()
        if not out and len(raw) == 1 and isinstance(raw[0], dict):
            # Single object: ``{"FIELD": "label or type", ...}`` — use values as labels when not type tokens.
            for k, v in raw[0].items():
                if isinstance(v, str) and v.strip() and not _looks_like_json_schema_type_token(v):
                    out[str(k).strip()] = v.strip()
        return out
    if isinstance(raw, dict):
        props = raw.get("properties")
        if isinstance(props, dict):
            for k, spec in props.items():
                if isinstance(spec, dict):
                    label = spec.get("title") or spec.get("description")
                    if label:
                        out[str(k).strip()] = str(label).strip()
            if out:
                return out
        cols = raw.get("columns")
        if isinstance(cols, list):
            for item in cols:
                if not isinstance(item, dict):
                    continue
                key = item.get("name") or item.get("field") or item.get("column")
                label = (
                    item.get("description")
                    or item.get("label")
                    or item.get("title")
                    or item.get("meaning")
                )
                if key and label:
                    out[str(key).strip()] = str(label).strip()
            if out:
                return out
        skip_root = {
            "type",
            "properties",
            "items",
            "required",
            "$schema",
            "definitions",
            "columns",
            "additionalProperties",
        }
        for k, v in raw.items():
            if k in skip_root:
                continue
            if isinstance(v, str) and v.strip():
                if _looks_like_json_schema_type_token(v):
                    continue
                out[str(k).strip()] = v.strip()
            elif isinstance(v, dict):
                label = v.get("title") or v.get("description")
                if label:
                    out[str(k).strip()] = str(label).strip()
    return out


def _rename_map_for_dataframe_columns(df: pd.DataFrame, mapping: Dict[str, str]) -> Dict[str, str]:
    """Map schema field names to DataFrame columns (exact, case-insensitive, OData suffix)."""
    rename: Dict[str, str] = {}
    if df is None or mapping is None:
        return rename
    cols = list(df.columns)
    cols_set = set(cols)
    lower_index = {str(c).lower(): c for c in cols}
    used_sources: set = set()
    for src, dst in mapping.items():
        if dst is None or not str(dst).strip():
            continue
        dst_clean = str(dst).strip()
        if src in cols_set and src not in used_sources:
            rename[src] = dst_clean
            used_sources.add(src)
            continue
        lo = str(src).lower()
        if lo in lower_index:
            actual = lower_index[lo]
            if actual not in used_sources:
                rename[actual] = dst_clean
                used_sources.add(actual)
            continue
        for col in cols:
            if col in used_sources:
                continue
            cs = str(col)
            if cs == src or cs.endswith("_" + src) or cs.endswith("." + src):
                rename[col] = dst_clean
                used_sources.add(col)
                break
    return rename


def rename_dataframe_columns_from_response_schema(
    df: Optional[pd.DataFrame],
    response_schema: Any,
) -> pd.DataFrame:
    """
    Rename ``df`` columns using meanings from the training row ``response_schema`` before
    LLM ``transform_df`` runs.
    """
    if df is None or not isinstance(df, pd.DataFrame):
        return df
    labels = _parse_response_schema_to_column_labels(response_schema)
    if not labels:
        return df
    rmap = _rename_map_for_dataframe_columns(df, labels)
    if not rmap:
        return df
    out = df.rename(columns=rmap, copy=False)
    return ensure_unique_dataframe_columns(out)





class APIChatService:
    """Service class for managing multiple APIs and chat-based interactions"""
    
    def __init__(self, openai_api_key: str, db_path: str = 'api_chat.db', claude_api_key: str = ""):
        """
        Initialize the API Chat Service
        
        Args:
            openai_api_key: OpenAI API key for GPT interactions
            db_path: Path to SQLite database file
            claude_api_key: Optional Anthropic key when use_claude is enabled
        """
        self.openai_api_key = openai_api_key
        self.db_path = db_path
        self.conn = None

        if oic_anthropic_integration_enabled():
            self.client = None
        else:
            self.client = OpenAI(api_key=openai_api_key) if (openai_api_key or "").strip() else None
        self._llm = LLMService(api_key=openai_api_key or "", claude_api_key=claude_api_key or "")
        self._last_api_selection_warnings: List[str] = []
    
    def get_all_apis(self, agent_id: str) -> List[Dict]:
        """Get all saved APIs from training table using app.core.database"""
        normalized_agent_id = _normalize_agent_id(agent_id)
        if not normalized_agent_id:
            return []
        try:
            # Import here to avoid circular dependencies
            import sys
            import os
            # Add parent directory to path to import from app
            current_dir = os.path.dirname(os.path.abspath(__file__))
            parent_dir = os.path.dirname(current_dir)
            if parent_dir not in sys.path:
                sys.path.insert(0, parent_dir)
            
            from app.core.database import SessionLocal
            from sqlalchemy import text
            
            db = SessionLocal()
            try:
                result = db.execute(
                    text("""
                        SELECT id, name, endpoint, base_params, response_schema, description, extra1, created_at, updated_at
                        FROM training
                        WHERE agent_id = :agent_id
                    """),
                    {"agent_id": normalized_agent_id}
                )
                
                rows = result.fetchall()
                training_data = []
                for row in rows:
                    training_data.append({
                        "id": row[0],
                        "name": row[1],
                        "endpoint": row[2],
                        "base_params": row[3],
                        "response_schema": row[4],
                        "description": row[5],
                        "extra1": row[6],
                        "created_at": row[7],
                        "updated_at": row[8],
                        "agent_id": normalized_agent_id  # stamp the owning agent_id
                    })
                return training_data
            finally:
                db.close()
        except Exception as e:
            print(f"Error fetching training data: {str(e)}")
            import traceback
            traceback.print_exc()
            return []
    
    def get_api_by_name(self, name: str, agent_id: str = None) -> Optional[Dict]:
        """Get API configuration by name"""
        apis = self.get_all_apis(agent_id)
        for api in apis:
            if api['name'].lower() == name.lower():
                return api
        return None

    # ==================== AI-Powered Methods ====================
    
    def select_appropriate_api(self, user_query: str, available_apis: List[Dict] = None, agent_id: str = None) -> Optional[Dict]:
        """
        Use ChatGPT to select the most appropriate API for the user query
        
        Args:
            user_query: User's natural language query
            available_apis: List of available APIs (if None, fetches all)
            
        Returns:
            Selected API configuration dict or None
        """
        if available_apis is None:
            available_apis = self.get_all_apis(agent_id)
        
        if not available_apis:
            return None
        
        apis_info = "\n".join([
            f"{i+1}. {api['name']}: {api['description']} (Endpoint: {api['endpoint']})"
            for i, api in enumerate(available_apis)
        ])
        
        prompt = f"""
        You are an API selector. Given a user query and available APIs, select the most appropriate API.
        
        Available APIs:
        {apis_info}
        
        User Query: {user_query}
        
        Instructions:
        1. Analyze the user query and determine which API is most appropriate
        2. Return ONLY the API name (exact match from the list)
        3. If no API seems appropriate, return "NONE"
        
        Return only the API name, nothing else.
        """
        
        selected_name = self._llm.chat_completion(
            messages=[
                {"role": "system", "content": "You are an API selector. Return only the API name."},
                {"role": "user", "content": prompt}
            ],
            temperature=0
        ).strip()
        # Find the selected API
        for api in available_apis:
            if api['name'].lower() == selected_name.lower():
                return api
        
        return None
    
    def extract_api_params(self, user_query: str, api_config: dict) -> dict:
        """
        Use ChatGPT to extract parameters from user query based on API configuration
        
        Args:
            user_query: User's natural language query
            api_config: API configuration dict
            
        Returns:
            Extracted parameters as dict
        """
        prompt = f"""
        You are an API parameter extractor. Given a user query and API configuration, extract the appropriate parameters.
        
        API Endpoint: {api_config.get('endpoint')}
        Base Parameters Template: {json.dumps(api_config.get('base_params', {}), indent=2)}
        
        User Query: {user_query}
        
        Instructions:
        1. Keep all base parameters as they are
        2. Identify which parameter needs to be changed based on the user query , update only that parameter which is blank
        3. Extract the value from the user query and update that parameter
        4. Return the complete parameters object with the updated value
        5. Prepare the date parameters based on the current date if mentioned in the question{datetime.now().strftime("%Y-%m-%d")}
        
        Return ONLY a valid JSON object with all parameters.
        """
        
        response = self._llm.chat_completion(
            messages=[
                {"role": "system", "content": "You are an API parameter extraction assistant. Return only valid JSON."},
                {"role": "user", "content": prompt}
            ],
            temperature=0
        ).strip()

        default_params: Dict[str, Any] = {}
        base_params = api_config.get('base_params', {})
        if isinstance(base_params, dict):
            default_params = base_params.copy()
        elif isinstance(base_params, str):
            default_params = _parse_llm_json_object(base_params, default={})

        return _parse_llm_json_object(response, default=default_params)


    def generate_transformation_code(self, user_query: str, response_schema: dict) -> str:
        """
        Generate Python code to transform API response based on schema and query
        
        Args:
            user_query: User's query
            response_schema: API response schema (structure only)
            
        Returns:
            Python transformation code as string
        """
        prompt = f"""
        You are a Python code generator. Generate transformation code based on the API response schema and user query.
        
        API Response Schema (structure only, no actual data):
        {json.dumps(response_schema, indent=2)}
        
        User Query: {user_query}
        
        Instructions:
        1. Generate Python code that takes 'api_response' as input (a dict with the structure shown above)
        2. Extract relevant data based on the user's query
        3. Transform it into a pandas DataFrame
        4. The code should handle missing keys gracefully
        5. Wrap the code inside a try-except block to handle any exceptions. If an exception occurs, return all the parameters as a Pandas DataFrame.
        6. Return a dictionary with 'df' (DataFrame) and 'summary' (string description)
        
        Important:
        - Always Use pandas (imported as pd)
        - Assume api_response is already a Python dict
        - Handle nested structures appropriately
        - The function should be named 'transform_data'
        - Return format: {{'df': dataframe, 'summary': 'text description'}}
        
        Return ONLY the Python function code, no explanations.
        """
        
        code = self._llm.chat_completion(
            messages=[
                {"role": "system", "content": "You are a Python code generator. Return only valid Python code."},
                {"role": "user", "content": prompt}
            ],
            temperature=0
        ).strip()
        
        # Clean markdown formatting if present
        if code.startswith('```'):
            code = code.split('```')[1]
            if code.startswith('python'):
                code = code[6:]
            code = code.strip()
        
        return code
    
    def execute_transformation(self, api_response: dict, transformation_code: str) -> Tuple[Optional[pd.DataFrame], str]:
        """
        Execute the generated transformation code on actual API response
        
        Args:
            api_response: Actual API response data
            transformation_code: Generated Python code
            
        Returns:
            Tuple of (DataFrame or None, summary message)
        """
        try:
            # Create a safe execution environment
           
            exec_globals = {
                'pd': pd,
                'json': json,
                'api_response': api_response
            }
            
            # Execute the transformation code
            exec(transformation_code, exec_globals)
            
            # Get the transform_data function and execute it
            transform_func = exec_globals.get('transform_data')
            if transform_func:
                result = transform_func(api_response)
                return result.get('df'), result.get('summary', 'Data transformed successfully')
            else:
                return None, "Error: transform_data function not found in generated code"
                
        except Exception as e:
            return None, f"Error executing transformation: {str(e)}"
    
    def call_api_and_transform(self, params: dict, endpoint: str, response_schema: dict, transformation_code: str,
                              user_query: str) -> Tuple[str, Optional[dict], Optional[pd.DataFrame], Optional[str]]:
        """
        Call the API and transform response using generated code
        
        Args:
            params: API parameters
            endpoint: API endpoint URL
            response_schema: API response schema
            user_query: User's original query
            
        Returns:
            Tuple of (formatted_response, raw_data, dataframe, transformation_code)
        """
        timeout_secs = api_request_timeout_seconds()
        try:
            # Make API call
            print(f"Url :{endpoint}  \n Request Params: {params}")
            response = requests.post(endpoint, json=params, timeout=timeout_secs)
            response.raise_for_status()
            api_data = response.json()
            
            # Generate transformation code (only schema sent, not actual data)
            if transformation_code != "":
                transformation_code = transformation_code
            else:  
                transformation_code = self.generate_transformation_code(user_query, response_schema)

            print(f"Generated Transformation Code:\n{transformation_code}")
            # Execute transformation on actual data locally
            df, summary = self.execute_transformation(api_data, transformation_code)
            
            if df is not None:
                # Create a formatted response
                formatted_response = f"### {summary}\n\n"
                formatted_response += f"**Records found:** {len(df)}\n\n"
                formatted_response += "**Data Preview:**\n"

                return formatted_response, api_data, df, transformation_code
            else:
                return summary, api_data, None, transformation_code

        except requests.exceptions.Timeout:
            return _format_timeout_message(endpoint, timeout_secs), None, None, None
        except Exception as e:
            return f"❌ Error: {str(e)}", None, None, None

    # ==================== High-Level Query Processing ====================
    
    def process_user_query(self, user_query: str, session_id: str, agent_id: str = None) -> dict:
        """
        Process a user query end-to-end: select API(s), extract params, call API(s), transform data.
        One agent may expose multiple APIs (e.g. trip + booking); when the query needs both,
        _select_api_from_candidates returns multiple names and call_multiple_apis_and_merge runs them.
        
        Args:
            user_query: User's natural language query
            session_id: Session identifier for chat history
            
        Returns:
            Dict with keys: success, message, api_used, api_endpoint, params, raw_data, df, code,
            and api_details when multiple APIs were invoked (per-call metadata).
        """
        result = {
            'success': False,
            'message': '',
            'api_used': None,
            'api_endpoint': None,
            'params': None,
            'raw_data': None,
            'df': None,
            'code': None,
            'api_details': None,
        }
        
        try:
            normalized_agent_id = _normalize_agent_id(agent_id)
            if not normalized_agent_id:
                result['message'] = "❌ Invalid agent_id. Please provide a non-empty agent identifier."
                return result

            available_apis = self.get_all_apis(normalized_agent_id)
            if not available_apis:
                result['message'] = "❌ No APIs configured for this agent."
                return result

            selected_apis = self._select_api_from_candidates(user_query, available_apis)

            if not selected_apis:
                result['message'] = "❌ I couldn't determine which API to use for your query. Please be more specific."
                return result

            if len(selected_apis) == 1:
                selected_api = selected_apis[0]
                result['api_used'] = selected_api['name']
                result['api_endpoint'] = selected_api['endpoint']
                extracted_params = self.extract_api_params(user_query, selected_api)
                result['params'] = extracted_params
                formatted_response, raw_data, df, code = self.call_api_and_transform(
                    extracted_params,
                    selected_api["endpoint"],
                    selected_api["response_schema"],
                    "",
                    user_query,
                )
                # Treat the canonical timeout message as a failure so callers
                # (FastAPI handlers, pipeline) get a consistent success=False signal.
                result['success'] = not _is_timeout_message(formatted_response)
                result['message'] = formatted_response
                result['raw_data'] = raw_data
                result['df'] = df
                result['code'] = code
            else:
                merge_result = self.call_multiple_apis_and_merge(
                    selected_apis=selected_apis, user_query=user_query
                )
                result['success'] = bool(merge_result.get('success'))
                result['message'] = merge_result.get('message', '')
                result['df'] = merge_result.get('df')
                result['api_used'] = ", ".join(
                    a.get('name', '') for a in selected_apis
                )
                result['api_endpoint'] = merge_result.get('api_endpoints') or [
                    a.get('endpoint', '') for a in selected_apis
                ]
                result['params'] = merge_result.get('api_params')
                result['raw_data'] = None
                result['code'] = merge_result.get('api_validators')
                result['api_details'] = merge_result.get('api_details')

            if self._last_api_selection_warnings:
                warning_text = " ".join(self._last_api_selection_warnings)
                if result['message']:
                    result['message'] = f"{result['message']}\n\n⚠️ {warning_text}"
                else:
                    result['message'] = f"⚠️ {warning_text}"
            
        except Exception as e:
            result['message'] = f"❌ Error processing query: {str(e)}"
        
        return result
    
    def generate_init_questions_from_api(self, api_name: str, count: int = 2, agent_id: str = None) -> List[str]:
        """
        Generate initial business questions based on API configuration
        
        Args:
            api_name: Name of the API to generate questions for
            count: Number of questions to generate (default: 5)
            
        Returns:
            List of business questions as strings
        """
        api_config = self.get_api_by_name(api_name, agent_id)
        
        if not api_config:
            return [f"Error: API '{api_name}' not found"]
        
        # Create a description of the API's capabilities
        api_description = f"""
        API Name: {api_config['name']}
        Description: {api_config['description']}
        Endpoint: {api_config['endpoint']}
        Parameters: {json.dumps(api_config['base_params'], indent=2)}
        """
        
        prompt = f"""
        Based on the following API configuration, generate {count} business questions that this API could answer.
        
        {api_description}
        
        Instructions:
        1. Generate exactly {count} practical business questions
        2. Questions should be natural and user-friendly
        3. Do not reference technical terms like "API", "endpoint", or parameter names
        4. Focus on what business insights or data the user can get
        5. Make questions specific and actionable
        6. Return ONLY the questions, one per line, numbered
        
    
        """
        
        try:
            response = self._llm.chat_completion(
                messages=[
                    {
                        "role": "system", 
                        "content": f"You are a business analyst. Generate {count} practical business questions based on API capabilities. Return only the numbered list of questions."
                    },
                    {"role": "user", "content": prompt}
                ],
                temperature=0.7,
            ).strip()
            
            # Parse the numbered list into a list of strings
            questions = []
            for line in response.split('\n'):
                line = line.strip()
                if line:
                    # Remove numbering (e.g., "1. ", "1) ", etc.)
                    clean_question = re.sub(r'^\d+[\.\)]\s*', '', line)
                    if clean_question:
                        questions.append(clean_question)
            # OIC / some models return a single block or bullets without leading numbers
            if not questions and (response or "").strip():
                for part in re.split(r"[\n\r;]+|(?<=[.!?])\s+", (response or "").strip()):
                    p = part.strip()
                    p = re.sub(r'^[-*•]\s*', '', p)
                    p = re.sub(r'^\d+[\.\)]\s*', '', p)
                    if len(p) > 8 and p not in questions:
                        questions.append(p)
            if not questions and (response or "").strip():
                questions = [re.sub(r"\s+", " ", (response or "").strip())[:500]]
            return questions[:count]  # Ensure we return exactly 'count' questions

        except Exception as e:
            return [f"Error generating questions: {str(e)}"]
    
    def generate_init_questions_from_all_apis(self, count_per_api: int = 5, agent_id: str = None) -> Dict[str, List[str]]:
        """
        Generate initial questions for all configured APIs
        
        Args:
            count_per_api: Number of questions to generate per API (default: 3)
            
        Returns:
            Dictionary mapping API names to lists of questions
        """
        all_apis = self.get_all_apis(agent_id)
        
        if not all_apis:
            return ""
        
        # Calculate questions per API (distribute evenly, minimum 1 per API)
        num_apis = len(all_apis)
        questions_per_api = max(1, count_per_api // num_apis)
        remaining_questions = count_per_api % num_apis
        
        all_questions = []
        
        # Generate questions from each API
        for i, api in enumerate(all_apis):
            # Add extra question to first few APIs if there's a remainder
            questions_to_generate = questions_per_api + (1 if i < remaining_questions else 0)
            questions = self.generate_init_questions_from_api(api['name'], questions_to_generate, agent_id)
            all_questions.extend(questions)
        
        # Take only the requested count (in case we generated more)
        selected_questions = all_questions[:count_per_api]
        
        # Format as string (one question per line)
        results = "\n".join(selected_questions)
        if results:  # Add newline at end if there are questions
            results += "\n"
        
        return str(results)


    def generate_questions_from_response_schema(self, response_schema: dict, count: int = 5) -> List[str]:
        """
        Generate business questions based on API response schema/structure
        Similar to SQL-based question generation but for API responses
        
        Args:
            response_schema: The structure/schema of API response
            count: Number of questions to generate
            
        Returns:
            List of business questions
        """
        schema_str = json.dumps(response_schema, indent=2)
        
        prompt = f"""
        Based on the following API response structure, generate {count} business questions that could be answered using this data.
        
        Response Schema:
        {schema_str}
        
        Instructions:
        1. Analyze the data structure to understand what information is available
        2. Generate {count} practical business questions
        3. Questions should be natural and user-friendly
        4. Do not reference technical field names directly
        5. Focus on business value and insights
        6. Return ONLY the numbered list of questions
        
        Example: If schema has customer order data, ask about order status, customer history, etc.
        """
        
        try:
            response = self._llm.chat_completion(
                messages=[
                    {
                        "role": "system",
                        "content": f"You are a business analyst. Generate {count} business questions based on data structure. Return only the numbered list without additional explanation."
                    },
                    {"role": "user", "content": prompt}
                ],
                temperature=0.7,
            ).strip()
            
            # Parse numbered list
            questions = []
            import re
            for line in response.split('\n'):
                line = line.strip()
                if line:
                    clean_question = re.sub(r'^\d+[\.\)]\s*', '', line)
                    if clean_question:
                        questions.append(clean_question)
            
            return questions[:count]
            
        except Exception as e:
            return [f"Error generating questions: {str(e)}"]
    
    # ==================== SAP OData Methods ====================
   

    def extract_sap_query_params(
        self,
        user_query: str,
        entity_set: str,
        max_row_count: str = "1000"
    ) -> Dict[str, str]:
        """
        Use LLM to extract SAP OData query parameters from a user query.
        Ensures SAP-compatible OData syntax (especially for date filters).
        """

        today = datetime.now().strftime("%Y-%m-%d")

        prompt = f"""
    You are an SAP OData query builder.

    Entity Set: {entity_set}
    User Query: "{user_query}"
    Current Date: {today}

    Instructions:
    1. Identify the user's intent clearly.
    2. Generate ONLY valid SAP OData query parameters.
    3. Supported parameters:
    - $select
    - $filter
    - $orderby
    - $top
    - $skip
    - $expand
    4. Use SAP OData operators only:
    eq, ne, gt, lt, ge, le, and, or
    5. IMPORTANT DATE RULES (MANDATORY):
    - If a date is used in $filter, ALWAYS format it as:
        datetime'YYYY-MM-DDT00:00:00'
        or
        datetime'YYYY-MM-DDT23:59:59'
    - NEVER return plain dates like '2017-01-01'
    6. If the user does NOT mention a top value, add:
    "$top": "{max_row_count}"
    7. Return ONLY a valid JSON object.
    8. Do NOT include explanations, markdown, or comments.

    Examples:
    - "Show top 10 sales orders"
    → {{ "$top": "10", "$orderby": "CreatedOn desc" }}

    - "Orders after 1 Jan 2017"
    → {{
        "$filter": "CreatedOn ge datetime'2017-01-01T00:00:00'",
        "$top": "{max_row_count}"
        }}

    - "Show customer name and net value"
    → {{ "$select": "CustomerName,NetValue", "$top": "{max_row_count}" }}

    Return ONLY the JSON object.
    """

        response = self._llm.chat_completion(
            messages=[
                {
                    "role": "system",
                    "content": "You generate SAP OData query parameters. Output ONLY valid JSON."
                },
                {
                    "role": "user",
                    "content": prompt
                }
            ],
            temperature=0
        ).strip()

        # --- SAFETY CLEANUP ---
        # Remove markdown if model adds it
        response = re.sub(r"^```json|^```|```$", "", response).strip()

        params = json.loads(response)

        # --- ENFORCE DEFAULT $top ---
        if "$top" not in params:
            params["$top"] = max_row_count

        # --- FINAL DATE VALIDATION (extra safety) ---
        if "$filter" in params:
            invalid_date_pattern = r"\b\d{4}-\d{2}-\d{2}\b"
            if re.search(invalid_date_pattern, params["$filter"]):
                raise ValueError(
                    "Invalid date format detected in $filter. "
                    "Dates must use datetime'YYYY-MM-DDTHH:MM:SS'."
                )

        return params


    def call_sap_odata_api(self, service_root: str, entity_set: str, query_params: Dict[str, str],
                        username: str = None, password: str = None,
                        verify_ssl: bool = True, timeout: Optional[int] = None,
                        paginate: bool = True, max_pages: Optional[int] = None) -> Tuple[Optional[pd.DataFrame], str]:
        """
        Call SAP OData API with pre-extracted query parameters and return DataFrame
        
        Args:
            service_root: SAP OData service root URL (e.g., https://host/sap/opu/odata/sap/SERVICE_SRV/)
            entity_set: Entity set name (e.g., SalesOrderSet, BusinessPartnerSet)
            query_params: Dictionary of OData query parameters (already extracted)
            username: SAP username (if None, looks for session variable)
            password: SAP password (if None, looks for session variable)
            verify_ssl: Whether to verify SSL certificate
            timeout: Per-page request timeout in seconds. ``None`` (default) uses the global
                ``api_request_timeout_seconds()`` value (5 min). The total wall-clock for the
                paginated fetch is also capped at ``api_request_timeout_seconds()``.
            paginate: Whether to follow pagination links
            max_pages: Maximum number of pages to fetch (None = unlimited)
            
        Returns:
            Tuple of (DataFrame or None, status message)
        """
        overall_timeout_secs = api_request_timeout_seconds()
        per_page_timeout = int(timeout) if timeout is not None else overall_timeout_secs
        # Don't let a per-page override exceed the global cap.
        per_page_timeout = max(1, min(per_page_timeout, overall_timeout_secs))
        last_url = ""
        try:
            # Get credentials from session if not provided
            if username is None:
                import streamlit as st
                username = st.session_state.get('sap_username', '')
            if password is None:
                import streamlit as st
                password = st.session_state.get('sap_password', '')
            
            if not username or not password:
                return None, "❌ Error: SAP username and password are required. Please set them in session variables."
            
            # Ensure service_root ends with /
            if service_root.endswith("/"):
                service_root = service_root.rsplit("/", 1)[0]
            
            # Always add $format=json for consistent JSON response
            query_params1 = {}
            query_params1["$format"] = "json"
            
            print(f"Generated OData query parameters: {query_params}")
            
            # Build the full URL
            # base_url = urljoin(service_root, entity_set)
            
            current_url = f"{service_root}?{urlencode(query_params, doseq=True)}"
            last_url = current_url
            
            print(f"Calling SAP API: {current_url}")
            
            # Create session and make the request
            session = requests.Session()
            session.auth = (username, password)
            session.verify = verify_ssl
            session.headers.update({
                "Accept": "application/json",
            })
            
            # Collect all items (with pagination support)
            all_items = []
            pages = 0
            start_time = time.monotonic()
            
            while current_url:
                last_url = current_url
                # Enforce overall 5-minute cap across all pages.
                elapsed = time.monotonic() - start_time
                remaining = overall_timeout_secs - elapsed
                if remaining <= 0:
                    raise APIRequestTimeoutError(
                        f"SAP OData paginated fetch exceeded {overall_timeout_secs} seconds"
                    )
                effective_timeout = max(1, int(min(per_page_timeout, remaining)))

                response = session.get(current_url, timeout=effective_timeout)
                response.raise_for_status()
                
                data = response.json()
                
                # Extract items and next link from response
                items, next_link = self._extract_odata_items_and_next(data)
                all_items.extend(items)
               
                print(f"Items fetched so far: {len(all_items)}")
                pages += 1
                print(f"Fetched page {pages}: {len(items)} items")
                
                # Check pagination conditions
                if not paginate:
                    break
                if max_pages is not None and pages >= max_pages:
                    break
                
                current_url = next_link
            
            if not all_items:
                return pd.DataFrame(), "✓ Query successful but no data returned."
            
            # Convert to DataFrame
            df = pd.json_normalize(all_items)
            
            message = f"✓ Successfully retrieved {len(df)} records from {entity_set}"
            if pages > 1:
                message += f" ({pages} pages)"
      
            return df, message
            
        except requests.exceptions.Timeout:
            return None, _format_timeout_message(last_url or service_root, overall_timeout_secs)
        except APIRequestTimeoutError:
            return None, _format_timeout_message(last_url or service_root, overall_timeout_secs)
        except requests.exceptions.HTTPError as e:
            error_detail = e.response.text[:500] if hasattr(e.response, 'text') else str(e)
            return None, f"❌ HTTP Error {e.response.status_code}: {error_detail}"
        except json.JSONDecodeError as e:
            return None, f"❌ Error parsing JSON response: {str(e)}"
        except Exception as e:
            return None, f"❌ Error calling SAP API: {str(e)}"

    def _extract_odata_items_and_next(self, data: Dict) -> Tuple[List[Dict], Optional[str]]:
        """
        Extract items and next link from OData response (supports both v2 and v4)
        
        Args:
            data: JSON response from OData API
            
        Returns:
            Tuple of (list of items, next link URL or None)
        """
        # OData v4 format
        if isinstance(data, dict) and "value" in data and isinstance(data["value"], list):
            return data["value"], data.get("@odata.nextLink")

        # OData v2 format
        if isinstance(data, dict) and "d" in data:
            d = data["d"]
            if isinstance(d, dict):
                if "results" in d and isinstance(d["results"], list):
                    return d["results"], d.get("__next")
                # Singleton payload — return single item list
                return [d], None
            elif isinstance(d, list):
                return d, None

        # Fallbacks
        if isinstance(data, list):
            return data, None
        
        return [], None

    def process_sap_query(self, service_root: str, entity_set: str, user_query: str,response_schema: str,transformation_code: str,
                        username: str = None, password: str = None,
                        verify_ssl: bool = True, paginate: bool = True,max_row_count: str = "1000",api_query_params: Dict[str, str] = None) -> dict:
        """
        High-level method to process SAP OData query end-to-end
        
        Args:
            service_root: SAP OData service root URL
            entity_set: Entity set name (stored as string)
            user_query: User's natural language query
            username: SAP username (optional, uses session if not provided)
            password: SAP password (optional, uses session if not provided)
            verify_ssl: Whether to verify SSL certificate
            paginate: Whether to follow pagination links
            
        Returns:
            Dict with keys: success, message, df, query_params, url
        """
        result = {
            'success': False,
            'message': '',
            'df': None,
            'query_params': None,
            'url': None,
            'code': None
        }
        
        try:
            # Extract query parameters using ChatGPT (ONLY ONCE)

            print(api_query_params)
            if api_query_params is not None:
                query_params = api_query_params
                print("Using pre-extracted API query parameters.")
            else:
                query_params = self.extract_sap_query_params(user_query, entity_set)

            query_params["$format"] = "json"
            result['query_params'] = query_params
            
            # Build URL for reference
            if not service_root.endswith("/"):
                service_root += "/"
            base_url = urljoin(service_root, entity_set)
            result['url'] = f"{base_url}?{urlencode(query_params, doseq=True)}"
            

            # Call API with pre-extracted parameters (NO REDUNDANT EXTRACTION)
            df, message = self.call_sap_odata_api(
                service_root, entity_set, query_params,  # FIXED: pass query_params instead of user_query
                username, password, verify_ssl=verify_ssl, paginate=paginate
            )
            
          
            df,code = self.transform_dataframe_with_gpt(user_query,transformation_code ,df)
            
            result['df'] = df
            result['code'] = code
            result['message'] = message
            result['success'] = df is not None
            
            
        except Exception as e:
            result['message'] = f"❌ Error processing SAP query: {str(e)}"
        
        return result

    def transform_dataframe_with_gpt(self,user_question: str,transformation_code:str ,df: pd.DataFrame) -> pd.DataFrame:
        """
        Transform a DataFrame based on user question using ChatGPT.
        Only sends DataFrame metadata (structure only, NO actual data) to GPT.
        
        Args:
            user_question: User's query describing desired transformation
            df: Source pandas DataFrame
            api_key: OpenAI API key (optional if set in environment)
            
        Returns:
            Transformed pandas DataFrame
        """
        # Extract DataFrame metadata (structure only, NO actual data)
        df_metadata = {
            "shape": {
                "rows": df.shape[0],
                "columns": df.shape[1]
            },
            "columns": list(df.columns),
            "dtypes": {col: str(dtype) for col, dtype in df.dtypes.items()},
            "null_counts": df.isnull().sum().to_dict(),
            "index_info": {
                "name": df.index.name,
                "dtype": str(df.index.dtype),
                "length": len(df.index)
            }
        }
        
        # Create prompt for GPT
        prompt = f"""
        You are a Python code generator. Generate transformation code based on the DataFrame metadata and user query.
        
        DataFrame Metadata (structure only, NO actual data):
        {json.dumps(df_metadata, indent=2)}
        
        User Query: {user_question}
        
        Instructions:
        1. Generate Python code that takes 'df' as input (a pandas DataFrame with the structure shown above)
        2. Filter, select, aggregate, or transform the DataFrame based on the user's query
        3. Return the transformed pandas DataFrame
        4. Handle missing columns or invalid operations gracefully
        5. Wrap the code inside a try-except block. If an exception occurs, return the original DataFrame unchanged
        6. The function should be named 'transform_df'
        7. The function should take only 'df' as parameter and return a pandas DataFrame
        8. Chnage the df filed name to more human readable name
        9. Convert all date to YYYY-MM-DD format current date format is eg /Date(1668124800000)/
        
        Important:
        - Use pandas operations (df is already a pandas DataFrame)
        - Handle edge cases (empty DataFrame, missing columns, etc.)
        - Return ONLY the transformed DataFrame, not a dictionary
        - Assume pandas is imported as pd
        - Do not hardcode any values; use dynamic filtering and operations
        
        Return ONLY the Python function code, no explanations or markdown.
        """
        
        # Get code from GPT
        if transformation_code == "":
            code = self._llm.chat_completion(
                messages=[
                    {"role": "system", "content": "You are a Python code generator. Return only valid Python code without markdown formatting."},
                    {"role": "user", "content": prompt}
                ],
                temperature=0
            ).strip()
        
            # Clean markdown formatting if present
            if code.startswith('```'):
                code = code.split('```')[1]
                if code.startswith('python'):
                    code = code[6:]
                code = code.strip()
        else:
            code = transformation_code
        # Execute the generated code
        try:
            # Create a namespace with pandas and the DataFrame
            namespace = {'pd': pd, 'df': df}
            
            # Execute the generated code
            exec(code, namespace)
            
            # Call the generated function
            transformed_df = namespace['transform_df'](df)

            return transformed_df,code
            
        except Exception as e:
            print(f"Error executing generated code: {e}")
            print(f"Generated code was:\n{code}")
            # Return original DataFrame if transformation fails
            return df,""


    def close(self):
        """Close database connection"""
        if self.conn:
            self.conn.close()


    # ==================== Multi-Agent Methods ====================

    def get_all_apis_multi_agent(self, agent_ids: List[str]) -> List[Dict]:
        """Get all saved APIs for multiple agents"""
        all_apis = []
        for aid in agent_ids:
            normalized_agent_id = _normalize_agent_id(aid)
            if not normalized_agent_id:
                continue
            all_apis.extend(self.get_all_apis(normalized_agent_id))
        return all_apis

    def _select_api_from_candidates(self, user_query: str, available_apis: List[Dict]) -> List[Dict]:
        """
        Helper method to select APIs from a list of candidates using LLM.
        """
        self._last_api_selection_warnings = []
        if not available_apis:
            return []
        
        apis_info = "\n".join([
            f"{i+1}. {api['name']}: {api['description']} (Endpoint: {api['endpoint']})"
            for i, api in enumerate(available_apis)
        ])
        
        prompt = f"""
        You are an API selector. Given a user query and available APIs, select ALL appropriate APIs that can help answer the query.
        
        Available APIs:
        {apis_info}
        
        User Query: {user_query}
        
        Instructions:
        1. Analyze the user query and determine which APIs are appropriate
        2. You can select MULTIPLE APIs if the query requires data from different sources or domains
        3. If the user asks for two or more distinct things (e.g. both trip and booking, or sales and inventory),
           you MUST return a comma-separated list that includes every API whose name or description matches each part.
           Do not return only one API when the question clearly needs two different endpoints.
        4. **CRITICAL**: Return the API names in the **LOGICAL ORDER OF EXECUTION**.
           - If one API depends on data from another, the prerequisite API MUST be listed FIRST.
        5. Return the API names as a comma-separated list (exact match from the list)
        6. If no API seems appropriate, return "NONE"
        
        Return only the API name(s), nothing else.
        """
        
        response = self._llm.chat_completion(
            messages=[
                {"role": "system", "content": "You are an API selector. Return only the API name(s) as comma-separated values."},
                {"role": "user", "content": prompt}
            ],
            temperature=0
        ).strip()
        
        # Handle NONE response
        if response.upper() == "NONE":
            return []
        
        # Parse comma-separated API names
        selected_names = [name.strip() for name in response.split(',') if name.strip()]

        exact_index: Dict[str, Dict[str, Any]] = {
            str(api.get('name', '')).strip().lower(): api for api in available_apis
        }
        normalized_index: Dict[str, Dict[str, Any]] = {
            _normalize_api_name_token(api.get('name', '')): api for api in available_apis
        }
        normalized_keys = [k for k in normalized_index.keys() if k]
        used_names: set = set()
        unresolved_names: List[str] = []

        # Find all selected APIs with tolerant matching.
        selected_apis = []
        for name in selected_names:
            api = exact_index.get(name.lower())
            if not api:
                api = normalized_index.get(_normalize_api_name_token(name))
            if not api:
                candidates = get_close_matches(
                    _normalize_api_name_token(name), normalized_keys, n=1, cutoff=0.8
                )
                if candidates:
                    api = normalized_index.get(candidates[0])
            if api:
                api_name = str(api.get('name', '')).strip().lower()
                if api_name and api_name in used_names:
                    continue
                if api_name:
                    used_names.add(api_name)
                selected_apis.append(api)
            else:
                unresolved_names.append(name)

        if unresolved_names:
            self._last_api_selection_warnings = [
                "Some API names from model output could not be mapped: "
                + ", ".join(unresolved_names)
            ]
        
        return selected_apis

    def select_appropriate_api_multi_agent(self, user_query: str, agent_ids: List[str] = None) -> List[Dict]:
        """
        Use ChatGPT to select the most appropriate APIs for the user query using multi-agent context.
        """
        available_apis = []
        if agent_ids:
            for agent_id in agent_ids:
                normalized_agent_id = _normalize_agent_id(agent_id)
                if not normalized_agent_id:
                    continue
                agent_apis = self.get_all_apis(normalized_agent_id)
                available_apis.extend(agent_apis)
        
        return self._select_api_from_candidates(user_query, available_apis)

    def extract_api_params_multi_agent(self, user_query: str, api_config: dict, other_api_names: List[str] = None, previous_context: str = "") -> dict:
        """
        Use ChatGPT to extract parameters from user query based on API configuration (Context Aware)
        """
        other_apis_context = ""
        if other_api_names:
            other_apis_list = ", ".join([f'"{name}"' for name in other_api_names])
            other_apis_context = f"\n        CRITICAL NEGATIVE CONSTRAINT: The user query ALSO contains requests for these OTHER APIs: [{other_apis_list}]. You must STRICTLY IGNORE any parameters, dates, or entities that belong to these other APIs.\n"

        prompt = f"""
        You are an API parameter extractor. Given a user query and API configuration, extract the appropriate parameters for THIS SPECIFIC API ONLY.
        
        API Name: {api_config.get('name')}
        API Endpoint: {api_config.get('endpoint')}
        Base Parameters Template: {json.dumps(api_config.get('base_params', {}), indent=2)}
        {other_apis_context}
        
        PREVIOUS EXECUTION CONTEXT:
        {previous_context}
        
        User Query: {user_query}
        
        Instructions:
        1. Identify the specific phrase in the User Query that refers to "{api_config.get('name')}" and extract parameters ONLY from that part.
        2. **CONTEXT AWARENESS**:
           - Check 'PREVIOUS EXECUTION CONTEXT' for missing parameters (e.g., IDs, Names) returned by previous calls.
           - User Query takes precedence, but if missing, use Context.
           - Infer relationships dynamically (e.g. map 'X_id' in API to 'id' for entity 'X' in context).
        3. **STRICT SCOPING**: 
           - IGNORE parts of the query that refer to other APIs or entities.
           - Treat sequential commands as INDEPENDENT. Do not cross-contaminate parameters.
        4. **DATE PARAMETERS**:
           - Use ONLY dates mentioned in the specific phrase for this API. Do NOT apply dates from other parts of the query.
           - Default to current date ({datetime.now().strftime("%Y-%m-%d")}) ONLY if relevant to this API and not specified.
        5. Return ONLY a JSON object with the EXTRACTED parameters. Do NOT include unmodified base parameters.
        """
        
        response = self._llm.chat_completion(
            messages=[
                {"role": "system", "content": "You are an API parameter extraction assistant. Return only valid JSON. Focus strictly on the current API's context."},
                {"role": "user", "content": prompt}
            ],
            temperature=0
        ).strip()
        
        # Clean markdown formatting if present
        if response.startswith('```'):
            response = response.split('```')[1]
            if response.startswith('json'):
                response = response[4:]
            response = response.strip()
        
        extracted_params = _parse_llm_json_object(response, default={})
        
        # If response is a list, take only first element
        if isinstance(extracted_params, list) and extracted_params:
            extracted_params = extracted_params[0]
        
        # Merge extracted parameters with base parameters
        base_params = api_config.get('base_params', {})
        if isinstance(base_params, str):
            try:
                base_params = json.loads(base_params)
            except:
                base_params = {}
                
        final_params = base_params.copy() if isinstance(base_params, dict) else {}
        final_params.update(extracted_params)
        
        return final_params

    def generate_transformation_code_multi_agent(self, user_query: str, response_schema: dict, other_api_names: List[str] = None) -> str:
        """
        Generate Python code to transform API response based on schema and query (Context Aware)
        """
        other_apis_context = ""
        if other_api_names:
            other_apis_list = ", ".join([f'"{name}"' for name in other_api_names])
            other_apis_context = f"\n        CRITICAL NEGATIVE CONSTRAINT: The user query ALSO contains requests for these OTHER APIs: [{other_apis_list}]. You must STRICTLY IGNORE any filtering criteria, entities, or dates that belong to these other APIs.\n"

        prompt = f"""
        You are a Python code generator. Generate transformation code based on the API response schema and user query.
        
        API Response Schema (structure only, no actual data):
        {json.dumps(response_schema, indent=2)}
        
        User Query: {user_query}
        {other_apis_context}
        
        Instructions:
        1. Generate Python code that takes 'api_response' as input (a dict with the structure shown above)
        - Analyze input data structure carefully
        - Be case-insensitive when filtering
        - Convert dates if necessary
        - **CRITICAL**: If the user query implies a filter based on specific criteria and the filtered result is empty, return an EMPTY DataFrame with a summary stating "No matches found".
        - **SMART SEARCH**: Check ALL plausible columns in the schema.
        - Ensure numeric columns are converted to numeric types
        - Filter by date range/company/status ONLY IF explicitly mentioned AND strictly belonging to this API request.
        - Handle missing columns gracefully
        - **DO NOT** add extra metadata columns like 'Filter', 'Query', etc.
        - **CRITICAL**: If 'api_response' contains a nested list (e.g. data, results), extract it.
        - **CRITICAL**: ALWAYS preserve primary identifier columns (Name, ID, Code, etc.) for merging.
        - Return ONLY the columns present in the original data or derived from it.
        - **IMPORTANT**: Do NOT use 'infer_datetime_format=True' (deprecated).
        - **IMPORTANT**: Do NOT use "errors='ignore'" in pd.to_numeric (deprecated, use 'coerce').
        - **CRITICAL**: Use .reset_index(drop=True) or .loc[mask.values] to prevent reindexing warnings.
        - **CRITICAL**: Use `na=False` in string methods.
        2. Extract relevant data based on the user's query
        3. Transform it into a pandas DataFrame
        4. The code should handle missing keys gracefully
        5. Wrap the code inside a try-except block.
        6. Return a dictionary with 'df' (DataFrame) and 'summary' (string description)
        
        Important:
        - Always Use pandas (imported as pd)
        - Assume api_response is already a Python dict
        - Handle nested structures appropriately
        - The function should be named 'transform_data'
        - Return format: {{'df': dataframe, 'summary': 'text description'}}
        
        Return ONLY the Python function code, no explanations.
        """
        
        code = self._llm.chat_completion(
            messages=[
                {"role": "system", "content": "You are a Python code generator. Return only valid Python code."},
                {"role": "user", "content": prompt}
            ],
            temperature=0
        ).strip()
        
        if code.startswith('```'):
            code = code.split('```')[1]
            if code.startswith('python'):
                code = code[6:]
            code = code.strip()
        
        # Post-processing
        if "infer_datetime_format=True" in code:
            code = code.replace("infer_datetime_format=True", "")
            code = code.replace(", )", ")").replace("(,", "(")
            
        if "errors='ignore'" in code and "pd.to_numeric" in code:
            code = code.replace("errors='ignore'", "errors='coerce'")
            
        return code

    def call_api_and_transform_multi_agent(self, params: dict, endpoint: str, response_schema: dict, transformation_code: str,
                               user_query: str, other_api_names: List[str] = None) -> Tuple[str, Optional[dict], Optional[pd.DataFrame], Optional[str]]:
        """
        Call the API and transform response using generated code (Multi-Agent version)
        """
        timeout_secs = api_request_timeout_seconds()
        try:
            # Make API call
            print(f"Url :{endpoint}  \n Request Params: {params}")
            response = requests.post(endpoint, json=params, timeout=timeout_secs)
            response.raise_for_status()
            api_data = response.json()
            
            # Generate transformation code
            if transformation_code != "":
                transformation_code = transformation_code
            else:  
                transformation_code = self.generate_transformation_code_multi_agent(user_query, response_schema, other_api_names)

            print(f"Generated Transformation Code:\n{transformation_code}")
            # Execute transformation
            df, summary = self.execute_transformation(api_data, transformation_code)
            
            if df is not None:
                formatted_response = f"### {summary}\n\n"
                formatted_response += f"**Records found:** {len(df)}\n\n"
                formatted_response += "**Data Preview:**\n"
                
                return formatted_response, api_data, df, transformation_code
            else:
                return summary, api_data, None, transformation_code
            
        except requests.exceptions.Timeout:
            return _format_timeout_message(endpoint, timeout_secs), None, None, None
        except Exception as e:
            return f"❌ Error: {str(e)}", None, None, None

    def extract_sap_query_params_multi_agent(self, user_query: str, entity_set: str, max_row_count: str = "1000", other_api_names: List[str] = None, previous_context: str = "") -> Dict[str, str]:
        """
        Use LLM to extract SAP OData query parameters from a user query (Multi-Agent Context Aware).
        """
        today = datetime.now().strftime("%Y-%m-%d")
        
        other_apis_context = ""
        if other_api_names:
            other_apis_list = ", ".join([f'"{name}"' for name in other_api_names])
            other_apis_context = f"\n    CRITICAL NEGATIVE CONSTRAINT: The user query ALSO contains requests for these OTHER APIs: [{other_apis_list}]. You must STRICTLY IGNORE any parameters, dates, or entities that belong to these other APIs.\n"

        prompt = f"""
    You are an SAP OData query builder.

    Entity Set: {entity_set}
    User Query: "{user_query}"
    Current Date: {today}
    {other_apis_context}
    
    PREVIOUS EXECUTION CONTEXT:
    {previous_context}

    Instructions:
    1. Identify the user's intent clearly for THIS entity set.
    2. Generate ONLY valid SAP OData query parameters.
    3. Supported parameters:
    - $select
    - $filter
    - $orderby
    - $top
    - $skip
    - $expand
    4. Use SAP OData operators only:
    eq, ne, gt, lt, ge, le, and, or
    5. IMPORTANT DATE RULES (MANDATORY):
    - If a date is used in $filter, ALWAYS format it as:
        datetime'YYYY-MM-DDT00:00:00'
        or
        datetime'YYYY-MM-DDT23:59:59'
    - NEVER return plain dates like '2017-01-01'
    6. If the user does NOT mention a top value, add:
    "$top": "{max_row_count}"
    7. Return ONLY a valid JSON object.
    8. Do NOT include explanations, markdown, or comments.
    
    Examples:
    - "Show top 10 sales orders"
    → {{ "$top": "10", "$orderby": "CreatedOn desc" }}

    - "Orders after 1 Jan 2017"
    → {{
        "$filter": "CreatedOn ge datetime'2017-01-01T00:00:00'",
        "$top": "{max_row_count}"
        }}

    Return ONLY the JSON object.
    """

        response = self._llm.chat_completion(
            messages=[
                {
                    "role": "system",
                    "content": "You generate SAP OData query parameters. Output ONLY valid JSON."
                },
                {
                    "role": "user",
                    "content": prompt
                }
            ],
            temperature=0
        ).strip()

        # --- SAFETY CLEANUP ---
        # Remove markdown if model adds it
        response = re.sub(r"^```json|^```|```$", "", response).strip()

        try:
            params = json.loads(response)
        except json.JSONDecodeError:
            return {"$top": max_row_count}

        # --- ENFORCE DEFAULT $top ---
        if "$top" not in params:
            params["$top"] = max_row_count

        # --- FINAL DATE VALIDATION (extra safety) ---
        if "$filter" in params:
            invalid_date_pattern = r"\b\d{4}-\d{2}-\d{2}\b"
            if re.search(invalid_date_pattern, params["$filter"]):
                pass

        return params

    def process_sap_query_multi_agent(self, service_root: str, entity_set: str, user_query: str, 
                                      other_api_names: List[str] = None, previous_context: str = "",
                                      username: str = None, password: str = None,
                                      verify_ssl: bool = True, paginate: bool = True, max_row_count: str = "1000") -> dict:
        """
        High-level method to process SAP OData query end-to-end (Multi-Agent)
        """
        result = {
            'success': False,
            'message': '',
            'df': None,
            'query_params': None,
            'url': None,
            'code': None
        }
        
        try:
            # Extract query parameters using ChatGPT (Multi-Agent Context)
            query_params = self.extract_sap_query_params_multi_agent(
                user_query, 
                entity_set, 
                max_row_count=max_row_count,
                other_api_names=other_api_names,
                previous_context=previous_context
            )

            query_params["$format"] = "json"
            result['query_params'] = query_params
            
            # Build URL for reference
            if not service_root.endswith("/"):
                service_root += "/"
            base_url = urljoin(service_root, entity_set)
            result['url'] = f"{base_url}?{urlencode(query_params, doseq=True)}"
            
            # Call API with extracted parameters
            df, message = self.call_sap_odata_api(
                service_root, entity_set, query_params,
                username, password, verify_ssl=verify_ssl, paginate=paginate
            )
            
            result['df'] = df
            result['message'] = message
            result['success'] = df is not None
            
        except Exception as e:
            result['message'] = f"❌ Error processing SAP query: {str(e)}"
        
        return result

    def determine_merge_strategy(self, user_query: str, df1_cols: List[str], df2_cols: List[str], df1_sample: dict, df2_sample: dict) -> dict:
        """Use LLM to determine how to merge two dataframes"""
        prompt = f"""
        You are a Data Merging Expert. Determine the best way to combine two datasets based on the user's query and the data samples.
        
        User Query: {user_query}
        
        Dataset 1 Columns: {df1_cols}
        Dataset 1 Sample: {json.dumps(df1_sample, default=str)}
        
        Dataset 2 Columns: {df2_cols}
        Dataset 2 Sample: {json.dumps(df2_sample, default=str)}
        
        Instructions:
        1. precise Identify if there is a common key (column) to join these datasets.
        2. Decide the merge strategy:
           - "merge": Use a join (inner/outer) on specific keys.
           - "concat": Concatenate horizontally if data is 1:1.
           - "append": Concatenate vertically if data is uniform.
           - "separate": **DEFAULT OPTION**. Return dataframes separately if no common keys exist or UNSURE.
        
        CRITICAL: 
        - If the user query does not explicitly imply combining these specific datasets, choose "separate".
        - Do NOT force a merge.
        - When in doubt, KEEP SEPARATE.

        3. Return JSON:
           {{
             "strategy": "merge" | "concat" | "append" | "separate",
             "left_on": ["col1"], 
             "right_on": ["col2"], 
             "how": "outer" | "inner"
           }}
           
        Return ONLY valid JSON.
        """
        
        try:
            response = self._llm.chat_completion(
                messages=[
                    {"role": "system", "content": "You are a data merging expert. Return only valid JSON."},
                    {"role": "user", "content": prompt}
                ],
                temperature=0
            ).strip()
            if response.startswith('```'):
                response = response.split('```')[1]
                if response.startswith('json'):
                    response = response[4:]
                response = response.strip()
            
            return json.loads(response)
        except Exception as e:
            return {"strategy": "separate"}

    def _merge_dataframes(self, dataframes: List[pd.DataFrame], user_query: str = "") -> List[pd.DataFrame]:
        """Intelligently merge multiple DataFrames using LLM execution"""
        if not dataframes:
            return []
            
        if len(dataframes) == 1:
            return dataframes
            
        merged_results = [dataframes[0]]
        
        for i in range(1, len(dataframes)):
            next_df = dataframes[i]
            current_df = merged_results[-1]
            
            df1_sample = current_df.iloc[0].to_dict() if not current_df.empty else {}
            df2_sample = next_df.iloc[0].to_dict() if not next_df.empty else {}
            
            strategy_config = self.determine_merge_strategy(
                user_query, 
                list(current_df.columns), 
                list(next_df.columns), 
                df1_sample, 
                df2_sample
            )
            
            try:
                if strategy_config['strategy'] == 'merge':
                    left_on = strategy_config.get('left_on')
                    right_on = strategy_config.get('right_on')
                    how = strategy_config.get('how', 'outer')
                    
                    if left_on and right_on:
                        merged_df = pd.merge(current_df, next_df, left_on=left_on, right_on=right_on, how=how, suffixes=('', f'_src{i+1}'))
                        merged_results[-1] = merged_df
                    else:
                        merged_results.append(next_df)
                        
                elif strategy_config['strategy'] == 'append':
                    merged_df = pd.concat([current_df, next_df], ignore_index=True)
                    merged_results[-1] = merged_df
                    
                elif strategy_config['strategy'] == 'concat':
                    overlap = next_df.columns.intersection(current_df.columns)
                    if not overlap.empty:
                        next_df_renamed = next_df.rename(columns={col: f"{col}_new" for col in overlap})
                        merged_df = pd.concat([current_df, next_df_renamed.reset_index(drop=True)], axis=1)
                        for col in overlap:
                            col_new = f"{col}_new"
                            if col in merged_df.columns and col_new in merged_df.columns:
                                merged_df[col] = merged_df[col].replace('', pd.NA)
                                merged_df[col] = merged_df[col].fillna(merged_df[col_new])
                                merged_df.drop(columns=[col_new], inplace=True)
                    else:
                        merged_df = pd.concat([current_df, next_df.reset_index(drop=True)], axis=1)
                    merged_results[-1] = merged_df
                    
                else: # separate
                    merged_results.append(next_df)
                    
            except Exception as e:
                merged_results.append(next_df)
                
        return merged_results

    def merge_dataframes_by_groups(self, results: List[Dict], merge_config: List[Dict]) -> List[pd.DataFrame]:
        """Merge dataframes based on groups defined in merge_config."""
        final_results = []
        processed_apis = set()
        results_map = {res['name']: res['df'] for res in results}
        
        for group in merge_config:
            group_apis = group.get('apis', [])
            merge_on = group.get('on')
            how = group.get('how', 'outer')
            
            dfs_to_merge = []
            for api_name in group_apis:
                if api_name in results_map:
                    dfs_to_merge.append(results_map[api_name])
                    processed_apis.add(api_name)
            
            if not dfs_to_merge:
                continue
                
            if len(dfs_to_merge) == 1:
                final_results.append(dfs_to_merge[0])
            else:
                merged_df = dfs_to_merge[0]
                for i in range(1, len(dfs_to_merge)):
                    next_df = dfs_to_merge[i]
                    try:
                        if merge_on:
                            if merge_on in merged_df.columns and merge_on in next_df.columns:
                                merged_df = pd.merge(merged_df, next_df, on=merge_on, how=how, suffixes=('', f'_{i}'))
                            else:
                                merged_df = pd.concat([merged_df, next_df], axis=1)    
                        else:
                            common_cols = list(set(merged_df.columns) & set(next_df.columns))
                            if common_cols:
                                merged_df = pd.merge(merged_df, next_df, on=common_cols, how=how, suffixes=('', f'_{i}'))
                            else:
                                merged_df = pd.concat([merged_df.reset_index(drop=True), next_df.reset_index(drop=True)], axis=1)
                    except Exception as e:
                        final_results.append(next_df)
                
                final_results.append(merged_df)

        for api_name, df in results_map.items():
            if api_name not in processed_apis:
                final_results.append(df)
                
        return final_results

    def call_multiple_apis_and_merge(self, selected_apis: List[Dict], user_query: str, 
                                     username: str = None, password: str = None, merge_config: List[Dict] = None) -> dict:
        """
        Call multiple APIs sequentially and merge their DataFrames (Multi-Agent version)
        """
        result = {
            'success': False,
            'message': '',
            'df': None,
            'api_details': [],
            'api_endpoints': [],
            'api_params': [],
            'api_validators': []
        }
        
        if not selected_apis:
            result['message'] = "No APIs selected"
            return result
        
        dataframes = []
        api_info_list = []
        execution_history = []
        # Track APIs that timed out so we can keep partial successes and surface
        # them in result['api_details'] / message at the end.
        timed_out_api_names: List[str] = []
        timeout_secs = api_request_timeout_seconds()
        
        for i, api_config in enumerate(selected_apis):
            api_message = ""  # captured per-API status / error message
            try:
                context_str = ""
                if execution_history:
                    context_str = "Results from previous API calls in this sequence:\n"
                    for hist in execution_history:
                        context_str += f"- API '{hist['name']}' returned {len(hist['df'])} records.\n"
                        if not hist['df'].empty:
                            first_row = hist['df'].iloc[0].to_dict()
                            context_str += f"  Sample Data: {json.dumps(first_row, default=str)}\n"
                            
                is_sap = api_config.get("extra1", "N")
                ep = (api_config.get("endpoint") or "").strip()

                if is_sap == "Y":
                    other_apis = [api.get('name') for api in selected_apis if api.get('name') != api_config.get('name')]
                    
                    sap_result = self.process_sap_query_multi_agent(
                        service_root=api_config.get("endpoint"),
                        entity_set=api_config.get("base_params"),
                        user_query=user_query,
                        other_api_names=other_apis,
                        previous_context=context_str,
                        username=username or "S4h_aa",
                        password=password or "Welcome1",
                        verify_ssl=False
                    )
                    # For simplicity in this turn, I'll rely on the existing SAP query or use the logic inline.
                    # Actually I will use the *extracted* params logic here inline using the multi-agent extractor if I can.
                    # But SAP extractor is specific.
                    # I'll implement `extract_sap_query_params_multi_agent` quickly.
                    
                    df = sap_result.get("df")
                    api_params = sap_result.get("query_params")
                    code = sap_result.get("code")
                    api_message = sap_result.get("message") or ""

                    api_info = {
                        'name': api_config.get('name'),
                        'endpoint': api_config.get('endpoint'),
                        'params': str(api_params) if api_params else "",
                        'validator': code if code else "",
                        'type': 'SAP',
                        'agent_id': api_config.get('agent_id')
                    }

                else:
                    other_apis = [api.get('name') for api in selected_apis if api.get('name') != api_config.get('name')]
                    
                    extracted_params = self.extract_api_params_multi_agent(
                        user_query, 
                        api_config, 
                        other_api_names=other_apis,
                        previous_context=context_str
                    )
                    
                    data, data2, df, code = self.call_api_and_transform_multi_agent(
                        extracted_params,
                        api_config.get("endpoint"),
                        api_config.get("response_schema"),
                        "",
                        user_query,
                        other_api_names=other_apis
                    )
                    api_message = data if isinstance(data, str) else ""
                    
                    if df is None and data and "Error" in data:
                        api_info_list.append({
                            'name': api_config.get('name'),
                            'error': data,
                            'agent_id': api_config.get('agent_id')
                        })
                    
                    api_info = {
                        'name': api_config.get('name'),
                        'endpoint': api_config.get('endpoint'),
                        'params': str(extracted_params) if extracted_params else "",
                        'validator': code if code else "",
                        'type': 'REST',
                        'agent_id': api_config.get('agent_id')
                    }
                
                # Detect timeout uniformly across REST / SAP 
                is_timeout = _is_timeout_message(api_message)

                if isinstance(df, dict):
                    for key, sub_df in df.items():
                        if sub_df is not None and isinstance(sub_df, pd.DataFrame) and not sub_df.empty:
                            dataframes.append(sub_df)
                            sub_api_info = api_info.copy()
                            sub_api_info['name'] = f"{api_info['name']} - {key}"
                            api_info_list.append(sub_api_info)
                            result['api_endpoints'].append(sub_api_info['endpoint'])
                            result['api_params'].append(sub_api_info['params'])
                            result['api_validators'].append(sub_api_info['validator'])
                            execution_history.append({'name': sub_api_info['name'], 'df': sub_df})
                else:
                    result['api_endpoints'].append(api_info['endpoint'])
                    result['api_params'].append(api_info['params'])
                    result['api_validators'].append(api_info['validator'])
                    
                    if df is not None:
                        if not df.empty:
                            dataframes.append(df)
                            api_info_list.append(api_info)
                            execution_history.append({'name': api_info['name'], 'df': df})
                        else:
                            api_info_copy = api_info.copy()
                            api_info_copy['status'] = 'No Data'
                            api_info_list.append(api_info_copy)
                    else:
                        api_info_copy = api_info.copy()
                        if is_timeout:
                            api_info_copy['status'] = 'Timeout'
                            api_info_copy['error'] = api_message
                            timed_out_api_names.append(api_info.get('name') or api_info.get('endpoint') or '<unknown>')
                            print(f"⏱️ API {api_info.get('name')} timed out: {api_message}")
                        else:
                            api_info_copy['status'] = 'No Data/Error'
                            if api_message:
                                api_info_copy['error'] = api_message
                        api_info_list.append(api_info_copy)

            except requests.exceptions.Timeout:
                # Defensive: any uncaught Timeout from helpers still gets recorded as such.
                msg = _format_timeout_message(api_config.get('endpoint'), timeout_secs)
                print(f"⏱️ API {api_config.get('name')} timed out: {msg}")
                result['api_endpoints'].append(api_config.get('endpoint', ''))
                result['api_params'].append("Timeout")
                result['api_validators'].append("")
                api_info_list.append({
                    'name': api_config.get('name'),
                    'endpoint': api_config.get('endpoint'),
                    'agent_id': api_config.get('agent_id'),
                    'status': 'Timeout',
                    'error': msg,
                })
                timed_out_api_names.append(api_config.get('name') or '<unknown>')
            except Exception as e:
                print(f"❌ Error calling API {api_config.get('name')}: {str(e)}")
                # Capture error
                result['api_endpoints'].append(api_config.get('endpoint', ''))
                result['api_params'].append("Error: " + str(e))
                result['api_validators'].append("")
        
        # Build a suffix listing any APIs that timed out so the message is consistent.
        timeout_suffix = ""
        if timed_out_api_names:
            timeout_suffix = (
                f" (Timed out after {timeout_secs}s: " + ", ".join(timed_out_api_names) + ")"
            )

        if not dataframes:
            if timed_out_api_names:
                result['message'] = (
                    f"All selected APIs timed out after {timeout_secs} seconds: "
                    + ", ".join(timed_out_api_names)
                )
            else:
                result['message'] = "No data returned from any API"
            result['api_details'] = api_info_list
            return result
        
        if len(dataframes) == 1:
            result['df'] = dataframes[0]
            result['api_details'] = api_info_list
            result['success'] = True
            result['message'] = (
                f"Successfully retrieved data from {api_info_list[0]['name']}" + timeout_suffix
            )
            return result
        
        try:
            if merge_config:
                results_for_merge = []
                for i, df in enumerate(dataframes):
                    results_for_merge.append({'name': api_info_list[i]['name'], 'df': df})
                merged_results = self.merge_dataframes_by_groups(results_for_merge, merge_config)
            else:
                merged_results = self._merge_dataframes(dataframes, user_query)
            
            # Names of APIs that actually contributed data (exclude 'No Data' / 'Timeout' rows).
            successful_names = [
                info.get('name', '') for info in api_info_list
                if info.get('status') not in ('Timeout', 'No Data', 'No Data/Error')
            ]
            api_names = ", ".join(n for n in successful_names if n)
            if len(merged_results) == 1:
                result['df'] = merged_results[0]
                result['message'] = f"Successfully merged data from: {api_names}{timeout_suffix}"
            else:
                result['df'] = merged_results
                result['message'] = f"Retrieved data from: {api_names} (Not merged){timeout_suffix}"
            
            result['api_details'] = api_info_list
            result['success'] = True
            
        except Exception as e:
            result['df'] = dataframes
            result['api_details'] = api_info_list
            result['success'] = True
            result['message'] = (
                f"Error merging dataframes, returning separately: {str(e)}{timeout_suffix}"
            )
        
        return result

    def process_user_query_multi_agent(self, user_query: str, session_id: str, agent_ids: List[str]) -> dict:
        """
        Process a user query end-to-end for multi-agent context
        """
        result = {
            'success': False,
            'message': '',
            'api_used': None,
            'api_endpoint': None,
            'params': None,
            'raw_data': None,
            'df': None,
            'code': None
        }
        
        try:
            valid_agent_ids = [
                normalized for normalized in (_normalize_agent_id(aid) for aid in (agent_ids or []))
                if normalized
            ]
            if not valid_agent_ids:
                result['message'] = "❌ Invalid agent_ids. Provide at least one non-empty agent identifier."
                return result

            selected_apis = self.select_appropriate_api_multi_agent(user_query, agent_ids=valid_agent_ids)
            
            if not selected_apis:
                result['message'] = "❌ I couldn't determine which API to use for your query."
                return result
            
            merge_result = self.call_multiple_apis_and_merge(selected_apis, user_query)
            
            result['success'] = merge_result['success']
            result['message'] = merge_result['message']
            result['df'] = merge_result['df']
            
            if merge_result['api_details']:
                result['api_used'] = ", ".join([d['name'] for d in merge_result['api_details']])
                result['api_endpoint'] = merge_result.get('api_endpoints') or [
                    d.get('endpoint', '') for d in merge_result['api_details']
                ]
                result['params'] = {d['name']: d.get('params') for d in merge_result['api_details']}
                result['code'] = "\n\n".join([f"# {d['name']}\n{d.get('validator', '')}" for d in merge_result['api_details']])
            
            result['raw_data'] = None 

            if self._last_api_selection_warnings:
                warning_text = " ".join(self._last_api_selection_warnings)
                if result['message']:
                    result['message'] = f"{result['message']}\n\n⚠️ {warning_text}"
                else:
                    result['message'] = f"⚠️ {warning_text}"
            
        except Exception as e:
            result['message'] = f"❌ Error processing query: {str(e)}"
        
        return result
