"""
Log Routes - Error Logs and User Question Logs
"""
from datetime import datetime
import time
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from textwrap import dedent
from fastapi import APIRouter, HTTPException, Depends
from sqlalchemy.orm import Session
from sqlalchemy.exc import OperationalError
from pydantic import BaseModel
from typing import Optional, Dict, Any

from app.core.database import get_db, SessionLocal
from app.models.question import UserQuestionLog
from app.models.user import User
from app.services.agent_config import get_agent_config
from fastapi.concurrency import run_in_threadpool

# Schemas
class ErrorLogsRequest(BaseModel):
    agent_id: str
    user_id: str


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


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


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


class UpdateErrorLogRequest(BaseModel):
    agent_id: str
    user_id: str
    id: int  # Error log ID
    question: str
    sql: Optional[str] = ""  # For DB errors
    api_endpoint: Optional[str] = ""  # For API errors
    api_params: Optional[str] = ""  # For API errors
    api_response_validator: Optional[str] = ""  # For API errors
    action: str = "save_and_run"  # "save_and_run" or "add_to_training"
    current_executed_status: Optional[str] = None  # Current executed status (for add_to_training, "D" or "E")


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


router = APIRouter(prefix="/logs", tags=["Logs"])


# Email utility functions (copied from updated/core_utils/core_utils.py and updated/download_and_send.py)
def get_user_resolution_mail(user_name: str, question: str, agent_name: str, timestamp: datetime):
    """
    Build the subject and HTML body for notifying the end user
    that their prompt issue has been fixed.
    """
    subject = f"Your prompt is now fixed — {timestamp}"

    html_body = dedent(f"""\
        <p>Hello {user_name},</p>
        <p>We're happy to let you know that the issue with your prompt has been fixed.</p>
        <p><strong>Your prompt:</strong> {question}</p>
        <p><strong>Agent:</strong> {agent_name}</p>
        <p>You can now retry it — everything should work as expected.</p>
        <p>Thanks for your patience!<br>
        <p>Thanks,<br>askme360 Team</p>
    """).strip()

    return subject, html_body


def send_email(sender_email: str, sender_name: str, recipient_email: list, subject: str, 
                body_text: str, body_html: Optional[str] = None, agent_conf: Optional[Dict] = None):
    """
    Send email using SMTP.
    
    Mirrors the functionality from updated/download_and_send.py
    Uses agent_conf for SMTP settings.
    """
    print("Sending email to", recipient_email)
    if not agent_conf:
        raise ValueError("agent_conf is required for sending emails")
    
    # Create message
    msg = MIMEMultipart('alternative')
    msg['From'] = f"{sender_name} <{sender_email}>"
    msg['To'] = ", ".join(recipient_email)
    msg['Subject'] = subject
    
    # Add text version
    part1 = MIMEText(body_text, 'plain')
    msg.attach(part1)
    
    # Add HTML version if provided
    if body_html:
        part2 = MIMEText(body_html, 'html')
        msg.attach(part2)
    
    try:
        # Create SMTP session
        smtp_server = agent_conf.get("smtp_server")
        smtp_port = agent_conf.get("smtp_port")
        smtp_username = agent_conf.get("smtp_username")
        smtp_password = agent_conf.get("smtp_password")
        
        if not all([smtp_server, smtp_port, smtp_username, smtp_password]):
            raise ValueError("SMTP configuration is incomplete")
        
        server = smtplib.SMTP(smtp_server, int(smtp_port))
        server.starttls()  # Enable TLS encryption
        server.login(smtp_username, smtp_password)
        
        # Send email
        text = msg.as_string()
        server.sendmail(sender_email, recipient_email, text)
        server.quit()
        
        print(f"Email sent successfully to {recipient_email}")
        return True
        
    except Exception as e:
        print(f"Error sending email: {str(e)}")
        return False


@router.post("/error-logs", response_model=ErrorLogsResponse)
async def get_error_logs(
    request: ErrorLogsRequest,
    db: Session = Depends(get_db)
):
    """
    Get all error logs for an agent.
    
    Mirrors the functionality from updated/admin_configuration/user_actitvity_log.py
    Returns all error logs (both API and DB errors) where executed IN ('D','E','DA','EA','DN','EN').
    
    The frontend can filter the logs as follows:
    - API Error Logs: All three API fields (api_endpoint, api_params, api_response_validator) 
      are NOT NULL and NOT empty strings
    - DB Error Logs: At least one of the three API fields is NULL or empty string
    
    Status codes:
    - 'D' = Incorrect (orange)
    - 'E' = Error (red)
    - 'DA' = Validated (green) - DB error that was validated
    - 'EA' = Fixed (green) - API error that was fixed
    - 'DN' = Notified (blue) - DB error that was notified
    - 'EN' = Notified (blue) - API error that was notified
    
    Args:
        request: ErrorLogsRequest containing agent_id and user_id
        db: Database session
    
    Returns:
        ErrorLogsResponse with success, message, and details containing error_logs list
    """
    try:
        # Query all error logs for the agent
        # Error statuses: 'D', 'E', 'DA', 'EA', 'DN', 'EN'
        error_statuses = ['D', 'E', 'DA', 'EA', 'DN', 'EN']
        
        # Use ORM to query error logs
        def get_logs():
            return db.query(UserQuestionLog).filter(
                UserQuestionLog.agent_id == request.agent_id,
                UserQuestionLog.executed.in_(error_statuses)
            ).order_by(UserQuestionLog.created_at.desc()).all()

        logs = await run_in_threadpool(get_logs)
        
        # Convert to dictionaries
        error_logs = []
        for log in logs:
            log_dict = {
                "id": log.id,
                "user_id": log.user_id,
                "agent_id": log.agent_id,
                "question": log.question if log.question else "",
                "sql": log.sql if log.sql else "",
                "executed": log.executed if log.executed else "",
                "execution_time": log.execution_time if log.execution_time else "",
                "api_endpoint": log.api_endpoint if log.api_endpoint else "",
                "api_params": log.api_params if log.api_params else "",
                "api_response_validator": log.api_response_validator if log.api_response_validator else "",
                "created_at": log.created_at if log.created_at else ""
            }
            error_logs.append(log_dict)
        
        return {
            "success": True,
            "message": "Error logs retrieved successfully",
            "details": {
                "error_logs": error_logs
            }
        }
    except Exception as e:
        return {
            "success": False,
            "message": f"Error retrieving error logs: {str(e)}",
            "details": {}
        }


@router.post("/user-question-logs", response_model=UserQuestionLogsResponse)
async def get_user_question_logs(
    request: UserQuestionLogsRequest,
    db: Session = Depends(get_db)
):
    """
    Get all user question logs for a specific user and agent.
    
    Mirrors the functionality from updated/admin_configuration/user_actitvity_log.py
    Returns all question logs for the specified user_id and agent_id.
    This includes all logs regardless of execution status (unlike error-logs which only shows errors).
    
    Args:
        request: UserQuestionLogsRequest containing agent_id and user_id
        db: Database session
    
    Returns:
        UserQuestionLogsResponse with success, message, and details containing question_logs list
    """
    try:
        # Match rows for this user only (was missing — UI saw no rows when filtering client-side
        # or when other users' activity dominated the unscoped result set).
        # Supports both single-agent logs (agent_id == '7') and multi-agent logs
        # stored as JSON arrays like '["7","8"]' using a LIKE filter.
        from sqlalchemy import or_
        uid = str(request.user_id).strip()
        def get_question_logs():
            return db.query(UserQuestionLog).filter(
                UserQuestionLog.user_id == uid,
                or_(
                    UserQuestionLog.agent_id == request.agent_id,
                    UserQuestionLog.agent_id.like(f'%"{request.agent_id}"%'),
                ),
            ).order_by(UserQuestionLog.created_at.desc()).all()

        logs = await run_in_threadpool(get_question_logs)
        
        # Convert to dictionaries
        question_logs = []
        for log in logs:
            log_dict = {
                "id": log.id,
                "user_id": log.user_id,
                "agent_id": log.agent_id,
                "question": log.question if log.question else "",
                "sql": log.sql if log.sql else "",
                "chart": log.chart if log.chart else "",
                "web": log.web if log.web else "",
                "executed": log.executed if log.executed else "",
                "execution_time": log.execution_time if log.execution_time else "",
                "api_endpoint": log.api_endpoint if log.api_endpoint else "",
                "api_params": log.api_params if log.api_params else "",
                "api_response_validator": log.api_response_validator if log.api_response_validator else "",
                "sql_query_summaries": log.sql_query_summaries if log.sql_query_summaries else "",
                "input_tokens": int(log.input_tokens or 0),
                "output_tokens": int(log.output_tokens or 0),
                "total_tokens": int(log.total_tokens or 0),
                "created_at": log.created_at if log.created_at else "",
                "updated_at": log.updated_at if log.updated_at else ""
            }
            question_logs.append(log_dict)
        
        return {
            "success": True,
            "message": "User question logs retrieved successfully",
            "details": {
                "question_logs": question_logs
            }
        }
    except Exception as e:
        return {
            "success": False,
            "message": f"Error retrieving user question logs: {str(e)}",
            "details": {}
        }


@router.post("/update-error-log", response_model=UpdateErrorLogResponse)
async def update_error_log(
    request: UpdateErrorLogRequest,
    db: Session = Depends(get_db)
):
    """
    Update an error log entry (both DB and API errors).
    
    Mirrors the functionality from updated/admin_configuration/user_actitvity_log.py
    Supports two actions:
    1. "save_and_run": Updates question, sql (for DB) or question and API fields (for API)
    2. "add_to_training": Adds to training data and updates executed status (DB errors only)
    
    For DB errors: Updates question and sql
    For API errors: Updates question, api_endpoint, api_params, api_response_validator
    
    When action="add_to_training" (DB errors only):
    - Adds SQL to ChromaDB training via AskmeService
    - Updates executed status: "D" -> "DA", "E" -> "EA"
    - Sends email notification to admin and user
    
    Note: "add_to_training" is only available for DB errors, not API errors.
    
    Args:
        request: UpdateErrorLogRequest containing log id, question, and optional fields
        db: Database session
    
    Returns:
        UpdateErrorLogResponse with success, message, and details
    """
    # Validate action
    if request.action not in ["save_and_run", "add_to_training"]:
        return {
            "success": False,
            "message": "action must be either 'save_and_run' or 'add_to_training'",
            "details": {}
        }
    
    # Check if this is a DB error or API error BEFORE processing
    # This determines if "add_to_training" is allowed
    def get_error_log_precheck():
        return db.query(UserQuestionLog).filter(
            UserQuestionLog.id == request.id
        ).first()

    error_log_precheck = await run_in_threadpool(get_error_log_precheck)
    
    if not error_log_precheck:
        return {
            "success": False,
            "message": f"Error log with id {request.id} not found",
            "details": {}
        }
    
    # Determine if this is a DB error or API error based on existing log
    existing_has_api = bool(
        error_log_precheck.api_endpoint and error_log_precheck.api_endpoint.strip() and
        error_log_precheck.api_params and error_log_precheck.api_params.strip() and
        error_log_precheck.api_response_validator and error_log_precheck.api_response_validator.strip()
    )
    
    # Also check request fields
    request_has_api = bool(request.api_endpoint or request.api_params or request.api_response_validator)
    request_has_sql = bool(request.sql and request.sql.strip())
    
    # Determine error type: prefer request fields, fallback to existing log
    is_api_error = request_has_api or (existing_has_api and not request_has_sql)
    is_db_error = request_has_sql or (not existing_has_api and not request_has_api)
    
    # Restrict "add_to_training" to DB errors only (matching user_actitvity_log.py)
    if request.action == "add_to_training" and is_api_error:
        return {
            "success": False,
            "message": "'add_to_training' action is only available for DB errors, not API errors",
            "details": {}
        }
    
    max_retries = 5
    retry_delay = 0.1  # Start with 100ms delay
    
    # Use the provided session for the first attempt
    current_db = db
    
    for attempt in range(max_retries):
        try:
            # Create a new session for retries (after first attempt)
            if attempt > 0:
                current_db = await run_in_threadpool(SessionLocal)
            
            try:
                # Check if error log exists
                def get_error_log():
                    return current_db.query(UserQuestionLog).filter(
                        UserQuestionLog.id == request.id
                    ).first()

                error_log = await run_in_threadpool(get_error_log)
                
                if not error_log:
                    return {
                        "success": False,
                        "message": f"Error log with id {request.id} not found",
                        "details": {}
                    }
                
                # For add_to_training, validate required fields (only for DB errors now)
                if request.action == "add_to_training":
                    if not request.sql or not request.sql.strip():
                        return {
                            "success": False,
                            "message": "sql is required for adding DB error to training",
                            "details": {}
                        }
                
                # Update timestamp
                current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                
                # Update question (always)
                error_log.question = request.question
                error_log.updated_at = current_time
                
                # Update fields based on error type
                if is_db_error:
                    # For DB errors: update sql (matching reference: update_error_query(question, sql, id))
                    error_log.sql = request.sql if request.sql else ""
                elif is_api_error:
                    # For API errors: update API fields if provided (for save_and_run action)
                    if request.action == "save_and_run":
                        if request.api_endpoint is not None:
                            error_log.api_endpoint = request.api_endpoint if request.api_endpoint else ""
                        if request.api_params is not None:
                            error_log.api_params = request.api_params if request.api_params else ""
                        if request.api_response_validator is not None:
                            error_log.api_response_validator = request.api_response_validator if request.api_response_validator else ""
                
                # Handle "add_to_training" action
                training_result = None
                new_executed_status = None
                
                if request.action == "add_to_training":
                    # Determine new executed status
                    current_status = request.current_executed_status or error_log.executed
                    if current_status == "D":
                        new_executed_status = "DA"
                    elif current_status == "E":
                        new_executed_status = "EA"
                    else:
                        new_executed_status = current_status
                    
                    # Add to training (only for DB errors)
                    # Add SQL training data via AskmeService
                    try:
                        from app.services.askme_service import get_askme_service
                        askme_service = get_askme_service(agent_id=request.agent_id)
                        if askme_service:
                            await run_in_threadpool(
                                askme_service.add_traning_data,
                                type="sql",
                                question=request.question,
                                query=request.sql
                            )
                            training_result = {"type": "sql", "status": "added"}
                        else:
                            return {
                                "success": False,
                                "message": "Could not initialize AskmeService for agent",
                                "details": {}
                            }
                    except Exception as e:
                        return {
                            "success": False,
                            "message": f"Error adding SQL training data: {str(e)}",
                            "details": {}
                        }
                    
                    # Send email notification (matching user_actitvity_log.py lines 109-113)
                    try:
                        # Get user details
                        def get_user_record():
                            return current_db.query(User).filter(
                                User.id == int(error_log.user_id)
                            ).first()

                        user_record = await run_in_threadpool(get_user_record)
                        
                        if not user_record:
                            print(f"Warning: User with id {error_log.user_id} not found for email notification")
                        else:
                            # Get agent configuration for email settings
                            agent_conf = get_agent_config(agent_id=request.agent_id)
                            if not agent_conf:
                                print(f"Warning: Agent config not found for agent_id {request.agent_id}")
                            else:
                                # Get agent name
                                agent_name = agent_conf.get("agent_name", request.agent_id)
                                
                                # Get admin user email (from request.user_id)
                                def get_admin_user():
                                    return current_db.query(User).filter(
                                        User.id == int(request.user_id)
                                    ).first()
                                
                                admin_user = await run_in_threadpool(get_admin_user)
                                
                                admin_email = admin_user.email if admin_user else None
                                user_email = user_record.email
                                
                                # Prepare recipient list
                                recipient_emails = []
                                if admin_email:
                                    recipient_emails.append(admin_email)
                                if user_email:
                                    recipient_emails.append(user_email)
                                
                                if recipient_emails and agent_conf.get("sender_email"):
                                    # Generate email subject and body
                                    subject, body_html = get_user_resolution_mail(
                                        user_name=f"{user_record.name} ",
                                        question=request.question,
                                        agent_name=agent_name,
                                        timestamp=datetime.now()
                                    )
                                    
                                    # Send email
                                    email_sent = await run_in_threadpool(
                                        send_email,
                                        sender_email=agent_conf.get("sender_email"),
                                        sender_name="askme360",
                                        recipient_email=recipient_emails,
                                        subject=subject,
                                        body_text=f"Issue : {error_log.executed}",
                                        body_html=body_html,
                                        agent_conf=agent_conf
                                    )
                                    
                                    if email_sent:
                                        training_result["email_sent"] = True
                                        training_result["email_recipients"] = recipient_emails
                                    else:
                                        training_result["email_sent"] = False
                                        training_result["email_error"] = "Failed to send email"
                    except Exception as e:
                        print(f"Error sending email notification: {e}")
                        # Don't fail the whole operation if email fails
                        if training_result:
                            training_result["email_error"] = str(e)
                    
                    # Update executed status
                    if new_executed_status:
                        error_log.executed = new_executed_status
                
                # Commit all changes
                await run_in_threadpool(current_db.commit)
                await run_in_threadpool(current_db.refresh, error_log)
                
                # Build response
                result = {
                    "success": True,
                    "message": f"Error log updated successfully" + (f" and added to training" if request.action == "add_to_training" else ""),
                    "details": {
                        "error_log": {
                            "id": error_log.id,
                            "question": error_log.question,
                            "sql": error_log.sql if error_log.sql else "",
                            "api_endpoint": error_log.api_endpoint if error_log.api_endpoint else "",
                            "api_params": error_log.api_params if error_log.api_params else "",
                            "api_response_validator": error_log.api_response_validator if error_log.api_response_validator else "",
                            "executed": error_log.executed,
                            "updated_at": error_log.updated_at
                        }
                    }
                }
                
                # Add training result if applicable
                if training_result:
                    result["details"]["training"] = training_result
                
                return result
                
            except HTTPException:
                raise
            except OperationalError as e:
                await run_in_threadpool(current_db.rollback)
                # Check if it's a database locked error
                if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                    # Exponential backoff: wait longer for each retry
                    time.sleep(retry_delay * (2 ** attempt))
                    continue
                else:
                    return {
                        "success": False,
                        "message": f"Error updating error log: Database is locked. Please try again later. {str(e)}",
                        "details": {}
                    }
            except Exception as e:
                await run_in_threadpool(current_db.rollback)
                return {
                    "success": False,
                    "message": f"Error updating error log: {str(e)}",
                    "details": {}
                }
        except HTTPException:
            raise
        except Exception as e:
            return {
                "success": False,
                "message": f"Error updating error log: {str(e)}",
                "details": {}
            }
    
    # If we've exhausted all retries
    return {
        "success": False,
        "message": "Error updating error log: Database is locked after multiple retries. Please try again later.",
        "details": {}
    }

