"""
Agent Configuration Service

This module mirrors the functionality of `updated/funtion_caller.py` get_org_data()
to load agent configuration from the database.
"""
import os
from typing import Optional, Dict, List
from app.core.database import SessionLocal
from app.models.agent import AgentManagement
from app.models.admin import AdminConfiguration


def get_db_session():
    """Get a new database session"""
    return SessionLocal()


def get_agent_by_id(agent_id: str) -> Optional[Dict]:
    """
    Get agent details from agent_management table
    
    Mirrors: user_managment_db.get_agents_by_id()
    """
    try:
        db = get_db_session()
        try:
            # Use ORM query instead of raw SQL
            agent = db.query(AgentManagement).filter(
                AgentManagement.agent_id == agent_id
            ).first()
            
            if not agent:
                return None
            
            # Convert ORM object to dictionary
            return {
                "agent_id": agent.agent_id,
                "agent_name": agent.agent_name,
                "agent_access": agent.agent_access,
                "init_training_data": agent.init_training_data,
                "endpoint": agent.endpoint,
                "extra1": agent.extra1,
                "extra2": agent.extra2,
                "created_at": agent.created_at,
                "updated_at": agent.updated_at,
                "admin_id": agent.admin_id,
                "max_row_count": agent.max_row_count,
                "data_source_type": agent.data_source_type,
                "host": agent.host,
                "port": agent.port,
                "db_name": agent.db_name,
                "connection": agent.connection,
                "username": agent.username,
                "password": agent.password,
                "db_type": agent.db_type,
                "database": agent.database,
                "api": agent.api,
                "file": agent.file,
                "default_data_source_type": agent.default_data_source_type,
                "description": getattr(agent, "description", None) or "",
            }
        finally:
            db.close()
    except Exception as e:
        print(f"Error getting agent by id: {e}")
        return None


def get_agent_by_access(access_key: str) -> Optional[Dict]:
    """
    Get agent details by access key
    
    Mirrors: user_managment_db.get_agents_by_access()
    """
    try:
        db = get_db_session()
        try:
            # Use ORM query instead of raw SQL
            agent = db.query(AgentManagement).filter(
                AgentManagement.agent_access == access_key
            ).first()
            
            if not agent:
                return None
            
            # Convert ORM object to dictionary
            return {
                "agent_id": agent.agent_id,
                "agent_name": agent.agent_name,
                "agent_access": agent.agent_access,
                "init_training_data": agent.init_training_data,
                "endpoint": agent.endpoint,
                "extra1": agent.extra1,
                "extra2": agent.extra2,
                "created_at": agent.created_at,
                "updated_at": agent.updated_at,
                "admin_id": agent.admin_id,
                "max_row_count": agent.max_row_count,
                "data_source_type": agent.data_source_type,
                "host": agent.host,
                "port": agent.port,
                "db_name": agent.db_name,
                "connection": agent.connection,
                "username": agent.username,
                "password": agent.password,
                "db_type": agent.db_type,
                "database": agent.database,
                "api": agent.api,
                "file": agent.file,
                "default_data_source_type": agent.default_data_source_type,
                "description": getattr(agent, "description", None) or "",
            }
        finally:
            db.close()
    except Exception as e:
        print(f"Error getting agent by access: {e}")
        return None


def _flag_enabled(val) -> bool:
    if val is None:
        return False
    s = str(val).strip().upper()
    return s in ("Y", "YES", "1", "TRUE")


def ordered_available_datasources(agent: Dict) -> List[str]:
    """
    Ordered list of enabled datasource kinds for an agent row: DATABASE, API, FILE.
    Uses checkbox columns database/api/file ('Y' when enabled). Puts default_data_source_type first when present.
    """
    raw: List[str] = []
    if _flag_enabled(agent.get("database")):
        raw.append("DATABASE")
    if _flag_enabled(agent.get("api")):
        raw.append("API")
    if _flag_enabled(agent.get("file")):
        raw.append("FILE")

    def _default_token() -> str:
        d = (agent.get("default_data_source_type") or "Database").strip().upper()
        if d == "DATABASE" or d == "DB":
            return "DATABASE"
        if d == "API":
            return "API"
        if d in ("FILE", "DOCUMENT"):
            return "FILE"
        return "DATABASE"

    if not raw:
        raw = [_default_token()]

    primary = _default_token()
    ordered: List[str] = []
    if primary in raw:
        ordered.append(primary)
    for x in raw:
        if x not in ordered:
            ordered.append(x)
    return ordered


def agent_has_file_datasource(agent_id: str) -> bool:
    """True when the agent row has FILE enabled (checkbox / ordered datasources)."""
    cfg = get_agent_config(agent_id=agent_id)
    if not cfg:
        return False
    return "FILE" in ordered_available_datasources(cfg)


def resolve_file_capable_agent_id_for_user(
    user_id: str,
    preferred_agent_ids: Optional[List[str]] = None,
) -> Optional[str]:
    """
    Pick an agent_id with FILE=Y: try preferred_agent_ids in order, then any agent
    the user may access (get_user_agents). Used for /ask-file and talkback file legs.
    """
    for raw in preferred_agent_ids or []:
        aid = str(raw).strip()
        if aid and agent_has_file_datasource(aid):
            return aid

    uid = str(user_id or "").strip()
    if not uid:
        return None

    from app.routes.auth import get_user_agents

    db = get_db_session()
    try:
        user_agents = get_user_agents(uid, db)
    finally:
        db.close()

    for ua in user_agents:
        aid = str(ua.get("agent_id") or "").strip()
        if aid and agent_has_file_datasource(aid):
            return aid
    return None


def resolve_all_file_capable_agent_ids_for_user(
    user_id: str,
    preferred_agent_ids: Optional[List[str]] = None,
) -> List[str]:
    """
    Get all agent_ids with FILE=Y from preferred_agent_ids or user's accessible agents.
    Used for multi-agent document search.
    """
    resolved_ids = []
    if preferred_agent_ids:
        for raw in preferred_agent_ids:
            aid = str(raw).strip()
            if aid and agent_has_file_datasource(aid):
                if aid not in resolved_ids:
                    resolved_ids.append(aid)
        if resolved_ids:
            return resolved_ids

    uid = str(user_id or "").strip()
    if not uid:
        return []

    from app.routes.auth import get_user_agents
    db = get_db_session()
    try:
        user_agents = get_user_agents(uid, db)
    finally:
        db.close()

    for ua in user_agents:
        aid = str(ua.get("agent_id") or "").strip()
        if aid and agent_has_file_datasource(aid):
            if aid not in resolved_ids:
                resolved_ids.append(aid)
    return resolved_ids


def get_all_admin_configs(config_type: str = "C") -> List[Dict]:
    """
    Get all configuration entries from admin_configuration table
    
    Mirrors: save_local_db.read_all_configs()
    
    Args:
        config_type: Configuration type filter (default: "C" for core configs)
    """
    try:
        db = get_db_session()
        try:
            # Use ORM query instead of raw SQL
            configs = db.query(AdminConfiguration).filter(
                AdminConfiguration.type == config_type
            ).all()
            
            # Convert ORM objects to dictionaries
            return [
                {
                    "id": config.id,
                    "configuration_name": config.configuration_name,
                    "type": config.type,
                    "value": config.value,
                    "created_at": config.created_at,
                    "updated_at": config.updated_at,
                }
                for config in configs
            ]
        finally:
            db.close()
    except Exception as e:
        print(f"Error reading configurations: {e}")
        return []


def get_agent_config(agent_id: str = None, access_key: str = None) -> Optional[Dict]:
    """
    Get complete agent configuration for AskmeService initialization.
    
    This mirrors `function_caller.get_org_data()` from the Streamlit app.
    Combines agent-specific settings with global organization settings.
    
    Args:
        agent_id: Agent ID to lookup
        access_key: Alternative lookup by access key
    
    Returns:
        Dictionary with all config needed for AskmeService, or None if not found
    """
    # Get agent details
    if agent_id:
        agent = get_agent_by_id(agent_id)
    elif access_key:
        agent = get_agent_by_access(access_key)
    else:
        return None
    
    if not agent:
        return None
    
    # Get global configs
    org_configs = get_all_admin_configs(config_type="C")
    org_data = {item['configuration_name']: item['value'] for item in org_configs}
    
    # Import settings for fallback values
    try:
        from app.core.config import settings
        openai_key_fallback = settings.OPENAI_API_KEY
        gemini_key_fallback = (getattr(settings, "GEMINI_API_KEY", None) or "").strip() or os.getenv(
        "GEMINI_API_KEY", ""
        ).strip() or os.getenv("GOOGLE_API_KEY", "").strip()
        claude_key_fallback = (
            (getattr(settings, "CLAUDE_API_KEY", None) or "").strip()
            or os.getenv("CLAUDE_API_KEY", "").strip()
            or os.getenv("ANTHROPIC_API_KEY", "").strip()
        )

    except Exception:
        openai_key_fallback = os.getenv("OPENAI_API_KEY", "")
        gemini_key_fallback = os.getenv("GEMINI_API_KEY", "").strip() or os.getenv("GOOGLE_API_KEY", "").strip()
        claude_key_fallback = (
            os.getenv("CLAUDE_API_KEY", "").strip()
            or os.getenv("ANTHROPIC_API_KEY", "").strip()
        )

    
    # Check if data_source_type is "Default Data Source Type"
    # This matches the logic from funtion_caller.get_org_data()
    # Normalize data_source_type: handle "D"/"C" shorthand and replace underscores with spaces
    data_source_type = agent.get("data_source_type", "")
    if data_source_type == "D":
        normalized_data_source_type = "Default Data Source Type"
    elif data_source_type == "C":
        normalized_data_source_type = "Custom Data Source Type"
    else:
        normalized_data_source_type = data_source_type.replace("_", " ") if data_source_type else ""
    is_default_data_source = normalized_data_source_type == "Default Data Source Type"
    
    # Fetch database values based on data_source_type
    if is_default_data_source:
        # Use organization-level database configuration
        db_type_org = (org_data.get("DB_TYPE", "") or "").strip().lower()
        if db_type_org == "mssql":
            host = org_data.get("SERVER", "") or org_data.get("HOST", "")
        else:
            host = org_data.get("HOST", "")
        port = org_data.get("PORT", "")
        dbname = org_data.get("DB_NAME", "")
        connection_string = org_data.get("CONNECTION_STRING", "")
        db_user = org_data.get("DB_USERNAME", "")
        db_password = org_data.get("DB_PASSWORD", "")
        db_type = org_data.get("DB_TYPE", "")
    else:
        # Use agent-specific database configuration
        host = agent.get("host", "")
        port = agent.get("port", "")
        dbname = agent.get("db_name", "")
        connection_string = agent.get("connection", "")
        db_user = agent.get("username", "")
        db_password = agent.get("password", "")
        db_type = agent.get("db_type", "")
    
    # Build combined config (same structure as function_caller.get_org_data)
    return {
        # Path settings
        "lib_path": org_data.get("LIB_PATH", ""),
        "tesseract_path": org_data.get("TESSERACT_PATH", ""),

        "chroma_path": f"./data/askme360{agent.get('agent_access', '')}",

        "path": f"./data/askme360{agent.get('agent_access', '')}",  # Alias for chroma_path
        
        # API Keys - fallback to settings if not in database
        "api_key": org_data.get("OPEN_API_KEY") or openai_key_fallback,
        "open_ai": org_data.get("OPEN_API_KEY") or openai_key_fallback,  # Alias
        "gemini_api_key": org_data.get("GEMINI_API_KEY")
        or org_data.get("GOOGLE_API_KEY")
        or gemini_key_fallback,
        "google_api_key": org_data.get("GOOGLE_API_KEY") or org_data.get("GEMINI_API_KEY") or gemini_key_fallback,
        "claude_api_key": org_data.get("CLAUDE_API_KEY") or claude_key_fallback,

        "web_key": org_data.get("WEB_KEY", ""),
        
        # Database settings - determined by data_source_type
        "host": host,
        "port": port,
        "dbname": dbname,
        "connection_string": connection_string,
        "db_user": db_user,
        "db_password": db_password,
        "db_type": db_type,
        # Legacy database settings (for backward compatibility)
        "dsn": connection_string,
        "user": db_user,
        "password": db_password,
        
        # Agent info
        "agent_id": agent.get("agent_id"),
        "agent_name": agent.get("agent_name"),
        "agent_access": agent.get("agent_access"),
        "agent_acces": agent.get("agent_access"),  # Alias (matching funtion_caller typo)
        "traning_data": agent.get("init_training_data", ""),
        "max_row_count": agent.get("max_row_count", "1000"),
        "data_source_type": agent.get("data_source_type", "Database"),
        "extra1": agent.get("extra1", ""),
        "extra2": agent.get("extra2", ""),
        "database": agent.get("database", ""),
        "api": agent.get("api", ""),
        "file": agent.get("file", ""),
        "default_data_source_type": agent.get("default_data_source_type", "Database"),
        "description": (agent.get("description") or "").strip(),
        
        # SMTP settings
        "scheme": org_data.get("SCHEME", ""),
        "base_url": org_data.get("BASE_URL", ""),
        "smtp_server": org_data.get("SMTP_SERVER", ""),
        "smtp_port": org_data.get("SMTP_PORT", ""),
        "smtp_username": org_data.get("SMTP_USERNAME", ""),
        "smtp_password": org_data.get("SMTP_PASSWORD", ""),
        "sender_email": org_data.get("SENDER_EMAIL_ID", ""),
        
        # Org info
        "org_id": org_data.get("ORG_ID", ""),
        "org_name": org_data.get("ORG_NAME", ""),
    }
