"""
Question Routes
"""
from datetime import datetime
import time
from fastapi import APIRouter, HTTPException, Depends
from sqlalchemy.orm import Session
from sqlalchemy.exc import OperationalError
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import text, select, delete
from pydantic import BaseModel, field_validator
from typing import Optional, List, Dict, Any, Union
from sqlalchemy import text, or_
import json

from app.core.database import get_db, SessionLocal, get_async_db_session
from app.core.dependencies import get_current_user
from app.models.question import Question, EmailQuestion, InitialSuggestionQuestion
from app.models.agent import AccessManage
from app.models.user import User
from app.services.log_field_serialization import (
    api_field_to_db_value as _shared_api_field_to_db_value,
    sql_to_db_value as _shared_sql_to_db_value,
)
from fastapi.concurrency import run_in_threadpool

# ---------------------------------------------------------------------------
# Schemas – Single-agent
# ---------------------------------------------------------------------------

def _flatten_if_nested(v):
    """If a list-of-lists is received, flatten it to a single list of strings."""
    if isinstance(v, list) and v and isinstance(v[0], list):
        # Flatten one level: [["a","b"],["a","b"]] → ["a","b"]
        # Take the first inner list (they are usually duplicates sent by the client)
        return v[0]
    return v


def _sql_to_db_value(sql) -> Optional[str]:
    """Convert sql to string for DB storage. Accepts str, list, or dict (e.g. agent_results structure)."""
    return _shared_sql_to_db_value(sql, none_default=None)


def _api_field_to_db_value(val) -> Optional[str]:
    """Convert API fields (endpoint, params, validator) to string for DB storage. Handles str or list."""
    return _shared_api_field_to_db_value(val, none_default=None)


def _jsonish_to_db_value(val) -> Optional[str]:
    """Store dict/list as JSON text; scalars as str; empty as None."""
    if val is None:
        return None
    if isinstance(val, (dict, list)):
        try:
            return json.dumps(val, default=str)
        except Exception:
            return str(val)
    s = str(val).strip()
    return s if s else None


class SavePromptRequest(BaseModel):
    """
    Works for both single-agent (agent_id: str) and multi-agent (agent_id: List[str]).
    sql can be: string, list of strings, or dict (e.g. agent_results format from multi-agent-ask).
    For multi-agent, api_endpoint / api_params / api_response_validator can be:
      - a plain string  → broadcast to every agent
      - a list          → paired 1-to-1 with agent_id list
    """
    agent_id: Optional[Union[str, List[str]]] = None  # Supports single agent (string) or multi-agent (list)
    question: str
    sql: Optional[Union[str, List[str], dict]] = None
    chart: str
    api_endpoint: Optional[Union[str, List[Any]]] = None
    api_params: Optional[Union[str, List[Any]]] = None
    api_response_validator: Optional[Union[str, List[Any]]] = None
    sql_query_summaries: Optional[Any] = None
    user_id: Optional[str] = None
    username: Optional[str] = None

    @field_validator("api_endpoint", "api_params", "api_response_validator", mode="before")
    @classmethod
    def flatten_nested_lists(cls, v):
        return _flatten_if_nested(v)


class UpdatePromptRequest(BaseModel):
    """
    Update a saved prompt identified by its question_id (primary key).
    agent_id is optional – only provide it if you want to change the stored agent.
    """
    question_id: int                  # ID of the Question record to update (required)
    agent_id: Optional[Union[str, List[str]]] = None  # Optional: change stored agent
    question: Optional[str] = None
    sql: Optional[Union[str, List[str], dict]] = None
    chart: Optional[str] = None
    api_endpoint: Optional[Union[str, List[Any]]] = None
    api_params: Optional[Union[str, List[Any]]] = None
    api_response_validator: Optional[Union[str, List[Any]]] = None
    sql_query_summaries: Optional[Any] = None
    user_id: Optional[str] = None

    @field_validator("api_endpoint", "api_params", "api_response_validator", mode="before")
    @classmethod
    def flatten_nested_lists(cls, v):
        return _flatten_if_nested(v)


class MyPromptsRequest(BaseModel):
    user_id: str
    agent_id: Union[str, List[str]]  # Supports single agent (string) or multi-agent (list)


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


class DeletePromptRequest(BaseModel):
    """
    Delete a saved prompt identified by its question_id (primary key).
    """
    question_id: int   # ID of the Question record to delete (required)
    user_id: str       # User ID to ensure only the owner can delete


# ---------------------------------------------------------------------------
# Router
# ---------------------------------------------------------------------------

router = APIRouter(prefix="/questions", tags=["Questions"])


# ===========================================================================
# Single-agent endpoints  (unchanged behaviour)
# ===========================================================================

@router.post("/save-prompt", status_code=201)
async def save_prompt(
    data: SavePromptRequest,
    current_user: User = Depends(get_current_user)
):
    """
    Save a question/prompt to the database.
    Uses async session via get_async_db_session.
    """
    try:
        async with get_async_db_session() as db:
            # Get user_id and username from current_user
            q = Question(
                user_id=data.user_id,
                agent_id=data.agent_id,
                username=data.username,
                question=data.question,
                sql=_sql_to_db_value(data.sql),
                chart=data.chart,
                api_endpoint=_api_field_to_db_value(data.api_endpoint),
                api_params=_api_field_to_db_value(data.api_params),
                api_response_validator=_api_field_to_db_value(data.api_response_validator),
                sql_query_summaries=_jsonish_to_db_value(data.sql_query_summaries),
                timestamp=datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            )
            db.add(q)
            await db.commit()
            await db.refresh(q)

            result = {
                "success": True,
                "message": "Prompt saved successfully",
                "details": {
                    "id": q.id,
                    "user_id": q.user_id,
                    "agent_id": q.agent_id,
                    "username": q.username,
                    "question": q.question,
                    "sql": q.sql,
                    "chart": q.chart,
                    "timestamp": q.timestamp,
                    "api_endpoint": q.api_endpoint,
                    "api_params": q.api_params,
                    "api_response_validator": q.api_response_validator,
                    "sql_query_summaries": q.sql_query_summaries,
                    "created_at": q.created_at.strftime("%Y-%m-%d %H:%M:%S") if q.created_at else None,
                    "updated_at": q.updated_at.strftime("%Y-%m-%d %H:%M:%S") if q.updated_at else None,
                }
            }
            return result

    except Exception as e:
        return {
            "success": False,
            "message": f"Error saving prompt: {str(e)}",
            "details": {}
        }


@router.post("/update-prompt")
async def update_prompt(
    request: UpdatePromptRequest,
    current_user: User = Depends(get_current_user)
):
    """
    Update saved prompt data.
    Identifies the record by question_id (primary key) – no agent_id lookup required.

    Body:
        question_id: ID of the Question record to update (required)
        agent_id: New agent ID (optional)
        question / sql / chart / api_endpoint / api_params / api_response_validator: optional fields to update
        user_id: Owner verification
    """
    try:
        async with get_async_db_session() as db:
            # Find the record by question_id (primary key)
            result = await db.execute(select(Question).filter(Question.id == request.question_id))
            question_entry = result.scalars().first()

            if not question_entry:
                return {
                    "success": False,
                    "message": f"Question record with question_id {request.question_id} not found",
                    "details": {}
                }

            # Verify user owns this question
            user_id = request.user_id
            if question_entry.user_id != user_id:
                return {
                    "success": False,
                    "message": "You can only update your own prompts",
                    "details": {}
                }

            # Update only the fields that are provided (not None)
            if request.agent_id is not None:
                question_entry.agent_id = request.agent_id
            if request.question is not None:
                question_entry.question = request.question
            if request.sql is not None:
                question_entry.sql = _sql_to_db_value(request.sql)
            if request.chart is not None:
                question_entry.chart = request.chart
            if request.api_endpoint is not None:
                question_entry.api_endpoint = _api_field_to_db_value(request.api_endpoint)
            if request.api_params is not None:
                question_entry.api_params = _api_field_to_db_value(request.api_params)
            if request.api_response_validator is not None:
                question_entry.api_response_validator = _api_field_to_db_value(request.api_response_validator)
            if request.sql_query_summaries is not None:
                question_entry.sql_query_summaries = _jsonish_to_db_value(request.sql_query_summaries)

            # Commit the changes
            await db.commit()
            await db.refresh(question_entry)

            result = {
                "success": True,
                "message": "Prompt updated successfully",
                "details": {
                    "question_id": question_entry.id,
                    "user_id": question_entry.user_id,
                    "agent_id": question_entry.agent_id,
                    "username": question_entry.username,
                    "question": question_entry.question,
                    "sql": question_entry.sql,
                    "chart": question_entry.chart,
                    "timestamp": question_entry.timestamp,
                    "api_endpoint": question_entry.api_endpoint,
                    "api_params": question_entry.api_params,
                    "api_response_validator": question_entry.api_response_validator,
                    "sql_query_summaries": question_entry.sql_query_summaries,
                    "created_at": question_entry.created_at.isoformat() if question_entry.created_at else None,
                    "updated_at": question_entry.updated_at.isoformat() if question_entry.updated_at else None,
                }
            }
            return result

    except Exception as e:
        return {
            "success": False,
            "message": f"Error updating prompt: {str(e)}",
            "details": {}
        }


@router.post("/delete-prompt")
async def delete_prompt(
    request: DeletePromptRequest,
    current_user: User = Depends(get_current_user)
):
    """
    Delete a saved prompt/question.
    Identifies the record by question_id (primary key) – no agent_id lookup required.
    Users can only delete their own prompts.

    Body:
        question_id: ID of the Question record to delete (required)
        user_id: Owner verification (required)
    """
    try:
        async with get_async_db_session() as db:
            user_id = request.user_id

            # Find the record by question_id and user_id only
            result = await db.execute(select(Question).filter(
                Question.id == request.question_id,
                Question.user_id == user_id,
            ))
            question_entry = result.scalars().first()

            if not question_entry:
                return {
                    "success": False,
                    "message": f"Question record with question_id {request.question_id} not found for user_id {user_id}",
                    "details": {}
                }

            # Delete the record
            await db.delete(question_entry)
            await db.commit()

            return {
                "success": True,
                "message": "Prompt deleted successfully",
                "details": {
                    "question_id": request.question_id
                }
            }

    except Exception as e:
        return {
            "success": False,
            "message": f"Error deleting prompt: {str(e)}",
            "details": {}
        }


@router.post("/my-prompts", response_model=MyPromptsResponse)
async def get_my_prompts(
    request: MyPromptsRequest
):
    """
    Get saved questions (my prompts) for a user and agent.
    Replicates logic from askme_main.py: get_saved_questions + date categorization
    """
    try:
        async with get_async_db_session() as db:
            # Normalize agent_id to list for consistent handling
            if isinstance(request.agent_id, list):
                agent_ids = request.agent_id
            else:
                agent_ids = [request.agent_id]

            # License validation: verify user has valid access to all agents (admin user 1 bypasses)
            if request.user_id != "1":
                for aid in agent_ids:
                    access_result = await db.execute(select(AccessManage).filter(
                        AccessManage.user_id == request.user_id,
                        AccessManage.agent_id == aid
                    ))
                    access_record = access_result.scalars().first()
                    if not access_record:
                        return {
                            "success": False,
                            "message": f"User does not have license/access to agent '{aid}'. Please contact administrator.",
                            "details": {},
                            "license_valid": False
                        }
                    # Check if access is expired
                    try:
                        end_date = datetime.fromisoformat(access_record.end_date)
                        if end_date < datetime.now():
                            return {
                                "success": False,
                                "message": f"License has expired for agent '{aid}'. Please contact administrator.",
                                "details": {},
                                "license_valid": False
                            }
                    except (ValueError, TypeError):
                        pass

            # Build query
            stmt = select(Question)

            # Build OR filter for agent IDs
            agent_filters = [
                or_(
                    Question.agent_id == aid,
                    Question.agent_id.like(f'%"{aid}"%')
                )
                for aid in agent_ids
            ]

            if request.user_id != "1":
                stmt = stmt.filter(Question.user_id == request.user_id, or_(*agent_filters))
            else:
                # Admin user 1 sees all questions for the agent(s)
                stmt = stmt.filter(or_(*agent_filters))

            # Sort by timestamp DESC
            stmt = stmt.order_by(text("timestamp DESC"))

            # Execute
            result = await db.execute(stmt)
            questions = result.scalars().all()

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

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

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


# ===========================================================================
# Multi-agent endpoints
# ===========================================================================

def _to_list(val, n: int) -> list:
    """Normalise a scalar-or-list field into a list of length n."""
    if val is None:
        return [None] * n
    if isinstance(val, list):
        # If the list is empty, return [None] * n to indicate no data for any agent
        if not val:
            return [None] * n
        # If the list length matches n, return as is
        if len(val) == n:
            return val
        # If it's a list but length doesn't match n, we broadcast the first element OR return empty
        return [val[0]] * n if len(val) > 0 else [None] * n
    return [val] * n


@router.post("/multi-agent-save-prompt", status_code=201)
async def multi_agent_save_prompt(
    data: SavePromptRequest,
    current_user: User = Depends(get_current_user)
):
    """
    Save a question/prompt to the database for one or more agents.

    - Single-agent: agent_id is a string → saves one row (same as /save-prompt).
    - Multi-agent:  agent_id is a list   → saves one row per agent, pairing each
      agent with its corresponding api_endpoint / api_params / api_response_validator
      list entry (broadcast shared string value when lists are not provided).

    Uses async session via get_async_db_session.
    """
    try:
        async with get_async_db_session() as db:
            timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

            # Normalise agent_id to list for consistent handling
            # Normalise agent_id to list for consistent handling
            if isinstance(data.agent_id, list):
                agent_ids = data.agent_id
            else:
                agent_ids = [data.agent_id]

            n = len(agent_ids)

            # Serialise all list fields as JSON strings for storage in a single row.
            # A plain string is broadcast → stored as a JSON list of n identical values.
            # If endpoint / params / validators are not sent by frontend, save None.
            def _serialize(val, n):
                lst = _to_list(val, n)
                return json.dumps(lst)

            stored_agent_id   = json.dumps(agent_ids)
            stored_endpoint   = _api_field_to_db_value(_to_list(data.api_endpoint, n)) if data.api_endpoint is not None else None
            stored_params     = _api_field_to_db_value(_to_list(data.api_params, n)) if data.api_params is not None else None
            stored_validators = _api_field_to_db_value(_to_list(data.api_response_validator, n)) if data.api_response_validator is not None else None
            # sql can be str, list (broadcast per agent), or dict (e.g. agent_results from multi-agent-ask)
            stored_sql        = _sql_to_db_value(data.sql) if data.sql is not None else None   
            stored_sql_query_summaries = _jsonish_to_db_value(data.sql_query_summaries)

            # Save ONE row with all agent IDs / api fields stored as JSON lists
            q = Question(
                user_id=data.user_id,
                agent_id=stored_agent_id,
                username=data.username,
                question=data.question,
                sql=stored_sql,
                chart=data.chart,
                api_endpoint=stored_endpoint,
                api_params=stored_params,
                api_response_validator=stored_validators,
                sql_query_summaries=stored_sql_query_summaries,
                timestamp=timestamp,
            )
            db.add(q)
            await db.commit()
            await db.refresh(q)

            return {
                "success": True,
                "message": f"Prompt saved successfully for {n} agent(s)",
                "details": {
                    "id": q.id,
                    "user_id": q.user_id,
                    "agent_id": json.loads(q.agent_id),          # return as list
                    "username": q.username,
                    "question": q.question,
                    "sql": q.sql,
                    "chart": q.chart,
                    "timestamp": q.timestamp,
                    "api_endpoint": json.loads(q.api_endpoint) if q.api_endpoint else None,
                    "api_params": json.loads(q.api_params) if q.api_params else None,
                    "api_response_validator": json.loads(q.api_response_validator) if q.api_response_validator else None,
                    "sql_query_summaries": json.loads(q.sql_query_summaries) if q.sql_query_summaries else None,
                    "created_at": q.created_at.strftime("%Y-%m-%d %H:%M:%S") if q.created_at else None,
                    "updated_at": q.updated_at.strftime("%Y-%m-%d %H:%M:%S") if q.updated_at else None,
                },
            }

    except Exception as e:
        return {
            "success": False,
            "message": f"Error saving prompt: {str(e)}",
            "details": {}
        }


@router.post("/multi-agent-update-prompt")
async def multi_agent_update_prompt(
    request: UpdatePromptRequest,
    current_user: User = Depends(get_current_user)
):
    """
    Update a multi-agent saved prompt (single DB row, agent_id stored as JSON list).
    Identifies the record by question_id (primary key).

    Body:
        question_id: ID of the Question record to update (required)
        agent_id: New agent_id list (optional) – stored as JSON list
        question / sql / chart: Updated values (optional)
        api_endpoint / api_params / api_response_validator:
            String or list – stored as JSON list aligned with agent_id list.
        user_id: User ID for ownership verification (required)
    """
    try:
        async with get_async_db_session() as db:
            record_id = request.question_id

            result = await db.execute(select(Question).filter(Question.id == record_id))
            question_entry = result.scalars().first()

            if not question_entry:
                return {
                    "success": False,
                    "message": f"Question record with question_id {record_id} not found",
                    "details": {}
                }

            if question_entry.user_id != request.user_id:
                return {
                    "success": False,
                    "message": "You can only update your own prompts",
                    "details": {}
                }

            # Determine number of agents from updated agent_id or existing stored value
            if request.agent_id is not None:
                new_agent_ids = request.agent_id if isinstance(request.agent_id, list) else [request.agent_id]
                question_entry.agent_id = json.dumps(new_agent_ids)
                n = len(new_agent_ids)
            else:
                # Infer n from existing stored agent_id
                try:
                    n = len(json.loads(question_entry.agent_id))
                except Exception:
                    n = 1

            # Update shared text fields
            if request.question is not None:
                question_entry.question = request.question
            if request.sql is not None:
                question_entry.sql = _sql_to_db_value(request.sql)
            if request.chart is not None:
                question_entry.chart = request.chart

            # Update api_ fields – serialise as JSON lists aligned to n agents
            if request.api_endpoint is not None:
                question_entry.api_endpoint = json.dumps(_to_list(request.api_endpoint, n))
            if request.api_params is not None:
                question_entry.api_params = json.dumps(_to_list(request.api_params, n))
            if request.api_response_validator is not None:
                question_entry.api_response_validator = json.dumps(_to_list(request.api_response_validator, n))
            if request.sql_query_summaries is not None:
                question_entry.sql_query_summaries = _jsonish_to_db_value(request.sql_query_summaries)

            await db.commit()
            await db.refresh(question_entry)

            def _safe_parse(val):
                try:
                    return json.loads(val) if val else None
                except Exception:
                    return val

            return {
                "success": True,
                "message": "Prompt updated successfully",
                "details": {
                    "question_id": question_entry.id,
                    "user_id": question_entry.user_id,
                    "agent_id": _safe_parse(question_entry.agent_id),
                    "username": question_entry.username,
                    "question": question_entry.question,
                    "sql": question_entry.sql,
                    "chart": question_entry.chart,
                    "timestamp": question_entry.timestamp,
                    "api_endpoint": _safe_parse(question_entry.api_endpoint),
                    "api_params": _safe_parse(question_entry.api_params),
                    "api_response_validator": _safe_parse(question_entry.api_response_validator),
                    "sql_query_summaries": _safe_parse(question_entry.sql_query_summaries),
                    "created_at": question_entry.created_at.isoformat() if question_entry.created_at else None,
                    "updated_at": question_entry.updated_at.isoformat() if question_entry.updated_at else None,
                },
            }

    except Exception as e:
        return {
            "success": False,
            "message": f"Error updating prompt: {str(e)}",
            "details": {}
        }


@router.post("/multi-agent-my-prompts", response_model=MyPromptsResponse)
async def multi_agent_get_my_prompts(
    request: MyPromptsRequest
):
    """
    Get saved questions (my prompts) for a user.
    Matches rows where agent_id is stored as a JSON list containing any of the requested agent IDs.

    - Single-agent: agent_id is a string → matches rows whose stored agent_id list contains it.
    - Multi-agent:  agent_id is a list   → matches rows whose stored agent_id list contains any entry.
    """
    try:
        async with get_async_db_session() as db:
            # Normalise agent_id to list for consistent handling
            if isinstance(request.agent_id, list):
                agent_ids = request.agent_id
            else:
                agent_ids = [request.agent_id]

            stmt = select(Question)

            # Build OR filter: match rows where agent_id JSON string contains any of the requested IDs.
            # Using LIKE per agent ID to handle JSON list stored as text.
            from sqlalchemy import or_
            agent_filters = [
                or_(
                    Question.agent_id == aid,
                    Question.agent_id.like(f'%"{aid}"%')
                )
                for aid in agent_ids
            ]

            if request.user_id != "1":
                stmt = stmt.filter(
                    Question.user_id == request.user_id,
                    or_(*agent_filters)
                )
            else:
                stmt = stmt.filter(or_(*agent_filters))

            stmt = stmt.order_by(text("timestamp DESC"))

            result = await db.execute(stmt)
            questions = result.scalars().all()

            def _safe_parse(val):
                try:
                    return json.loads(val) if val else val
                except Exception:
                    return val

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

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

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


# ===========================================================================
# Multi-agent delete endpoint
# ===========================================================================

@router.post("/multi-agent-delete-prompt")
async def multi_agent_delete_prompt(
    request: DeletePromptRequest,
    current_user: User = Depends(get_current_user)
):
    """
    Delete a multi-agent saved prompt (single DB row, agent_id stored as JSON list).
    Identifies the record by question_id (primary key).

    Body:
        question_id: ID of the Question record to delete (required)
        user_id: User ID for ownership verification (required)
    """
    try:
        async with get_async_db_session() as db:
            user_id = request.user_id
            record_id = request.question_id

            result = await db.execute(select(Question).filter(
                Question.id == record_id,
                Question.user_id == user_id,
            ))
            question_entry = result.scalars().first()

            if not question_entry:
                return {
                    "success": False,
                    "message": f"Question record with question_id={record_id} not found for user_id={user_id}",
                    "details": {}
                }

            await db.delete(question_entry)
            await db.commit()

            return {
                "success": True,
                "message": "Prompt deleted successfully",
                "details": {"question_id": record_id},
            }

    except Exception as e:
        return {
            "success": False,
            "message": f"Error deleting prompt: {str(e)}",
            "details": {}
        }
