"""
Schedule Routes
"""
import time
from datetime import datetime, time as time_obj
from fastapi import APIRouter, HTTPException, Depends
from sqlalchemy.orm import Session
from sqlalchemy.exc import OperationalError
from pydantic import BaseModel
from typing import Optional, List, Union

from app.core.database import get_db, SessionLocal
from app.models.schedule import ScheduleType, ScheduledJob, ExecutionLog
from app.models.distribution import DistributionList
from fastapi.concurrency import run_in_threadpool


# Schemas
class ScheduledJobCreate(BaseModel):
    """
    Request model for creating a scheduled job.
    
    Supports both single agent and multi-agent scenarios:
    - Single agent: Provide 'agent_id' as str
    - Multi agent: Provide 'agent_id' as list of strings
    
    You can either:
    1. Provide 'cron_expression' directly, OR
    2. Provide schedule-specific parameters to calculate cron_expression automatically:
       - For Hourly (schedule_type_id=1): Provide 'minute' (0-59)
       - For Daily (schedule_type_id=2): Provide 'start_time' (HH:MM or HH:MM:SS)
       - For Weekly (schedule_type_id=3): Provide 'start_time' and 'day_of_week' (Monday-Sunday)
       - For Monthly (schedule_type_id=4): Provide 'start_time' and 'day_of_month' (1-31)
    """
    user_id: str
    agent_id: Optional[Union[str, List[str]]] = None  # Single agent (str) or multiple agents (list[str])
    username: str
    question: str
    question_id: str
    schedule_type_id: int  # 1=Hourly, 2=Daily, 3=Weekly, 4=Monthly
    distribution_list_id: int
    start_date: str  # Format: YYYY-MM-DD or YYYY/MM/DD
    # Optional: Provide cron_expression directly, or let it be calculated from schedule-specific parameters
    cron_expression: Optional[str] = None  # Optional: provide cron expression directly (e.g., "40 17 * * 4")
    # Schedule-specific parameters (used to calculate cron_expression if cron_expression not provided)
    minute: Optional[int] = None  # Required for Hourly if cron_expression not provided: minute of hour (0-59)
    start_time: Optional[str] = None  # Required for Daily/Weekly/Monthly if cron_expression not provided: time in "HH:MM" or "HH:MM:SS" format
    day_of_week: Optional[str] = None  # Required for Weekly if cron_expression not provided: day name (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday)
    day_of_month: Optional[int] = None  # Required for Monthly if cron_expression not provided: day of month (1-31)
    
    class Config:
        extra = "forbid"  # Reject any extra fields
    


class ScheduledJobResponse(BaseModel):
    id: int
    user_id: str
    agent_id: str
    username: str
    question: str
    question_id: str
    schedule_type_id: int
    cron_expression: str
    distribution_list_id: int
    status: str
    start_date: datetime
    created_at: datetime
    updated_at: Optional[datetime] = None

    class Config:
        from_attributes = True


class CreateScheduledJobResponse(BaseModel):
    success: bool
    message: str
    details: dict


class CreateMultiAgentScheduledJobResponse(BaseModel):
    """Response model for creating multi-agent scheduled jobs"""
    success: bool
    message: str
    details: dict  # Contains list of created jobs and any errors


class UserRequest(BaseModel):
    user_id: str
    agent_id: Optional[Union[str, List[str]]] = None  # Single agent (str) or multiple agents (list[str])


class ScheduleTypeRequest(BaseModel):
    user_id: str
    agent_id: Optional[Union[str, List[str]]] = None  # Single agent (str) or multiple agents (list[str])


class JobIdRequest(BaseModel):
    user_id: str
    agent_id: Optional[Union[str, List[str]]] = None  # Single agent (str) or multiple agents (list[str])
    job_id: int
    job_status: Optional[bool] = None

class JobHistoryRequest(BaseModel):
    user_id: str
    agent_id: str
    job_id: Optional[int] = None


class ExecutionLogCreate(BaseModel):
    """Request model for creating an execution log."""
    scheduled_job_id: int
    user_id: str
    agent_id: str
    question: str
    execution_time: str  # Format: YYYY-MM-DD HH:MM:SS or ISO format
    status: str
    api_params: str
    api_response_validator: str
    
    class Config:
        extra = "forbid"


class ExecutionLogResponse(BaseModel):
    id: int
    scheduled_job_id: int
    user_id: str
    agent_id: str
    question: str
    execution_time: datetime
    status: str
    api_params: str
    api_response_validator: str
    created_at: datetime

    class Config:
        from_attributes = True


class CreateExecutionLogResponse(BaseModel):
    success: bool
    message: str
    details: dict



router = APIRouter(prefix="/schedules", tags=["Schedules"])


# Helper function to ensure schedule types exist
def ensure_default_schedule_types(db: Session):
    """
    Ensure default schedule types exist in the database.
    """
    # Check if schedule types exist
    if db.query(ScheduleType).first():
        return

    default_schedule_types = [
        ("Hourly", "Runs every hour"),
        ("Daily", "Runs daily"),
        ("Weekly", "Runs weekly"),
        ("Monthly", "Runs monthly")
    ]
    
    for name, description in default_schedule_types:
        schedule_type = ScheduleType(name=name, description=description)
        db.add(schedule_type)
    
    db.commit()


# Schedule Types
@router.post("/types")
async def get_schedule_types(data: ScheduleTypeRequest, db: Session = Depends(get_db)):
    """
    Get schedule types, creating default ones if none exist.
    Uses retry logic for database locks and ensures database connection is closed after response.
    """
    # Check if schedule types exist
    def get_types():
        return db.query(ScheduleType).order_by(ScheduleType.id).all()

    types = await run_in_threadpool(get_types)
    
    # If no schedule types exist, create default ones
    if not types:
        max_retries = 5
        retry_delay = 0.1  # Start with 100ms delay
        
        # Use the provided session for the first attempt
        current_db = db
        manually_created_session = False
        
        try:
            for attempt in range(max_retries):
                try:
                    # Create a new session for retries (after first attempt)
                    if attempt > 0:
                        current_db = SessionLocal()
                        manually_created_session = True
                    
                    try:
                        await run_in_threadpool(ensure_default_schedule_types, current_db)
                        
                        await run_in_threadpool(current_db.commit)
                        
                        # Close manually created session before continuing
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                        
                        # Query again to get the newly created types
                        def get_new_types():
                            return db.query(ScheduleType).order_by(ScheduleType.id).all()
                        types = await run_in_threadpool(get_new_types)
                        break
                        
                    except OperationalError as e:
                        current_db.rollback()
                        # Check if it's a database locked error
                        if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                            # Close the current session before retry
                            if manually_created_session:
                                current_db.close()
                                manually_created_session = False
                            # Exponential backoff: wait longer for each retry
                            time.sleep(retry_delay * (2 ** attempt))
                            continue
                    else:
                        if manually_created_session:
                            current_db.close()
                        return {
                            "success": False,
                            "message": f"Error creating schedule types: Database is locked. Please try again later. {str(e)}",
                            "details": {}
                        }
                except Exception as e:
                    current_db.rollback()
                    if manually_created_session:
                        current_db.close()
                    return {
                        "success": False,
                        "message": f"Error creating schedule types: {str(e)}",
                        "details": {}
                    }
        except HTTPException:
            raise
        except Exception as e:
            if manually_created_session and current_db:
                current_db.close()
            return {
                "success": False,
                "message": f"Error creating schedule types: {str(e)}",
                "details": {}
            }
            
            # If we've exhausted all retries
            if not types:
                return {
                    "success": False,
                    "message": "Error creating schedule types: Database is locked after multiple retries. Please try again later.",
                    "details": {}
                }
        finally:
            # Ensure any manually created session is closed
            if manually_created_session and current_db:
                try:
                    current_db.close()
                except Exception:
                    pass
    
    return {
        "success": True,
        "message": "Schedule types retrieved successfully",
        "details": {"schedule_types": types}
    }


@router.post("/jobs/user")
async def get_user_scheduled_jobs(data: UserRequest, db: Session = Depends(get_db)):
    # Determine which agents to fetch schedules for
    agents_to_fetch = []
    
    if data.agent_id:
        if isinstance(data.agent_id, list):
            # Multiple agents: agent_id is a list
            agents_to_fetch = data.agent_id
        else:
            # Single agent: agent_id is a string
            agents_to_fetch = [data.agent_id]
    else:
        return {
            "success": False,
            "message": "'agent_id' must be provided (either as string for single agent or list for multiple agents)",
            "details": {}
        }
    
    def get_jobs():
        query = db.query(
            ScheduledJob.id,
            ScheduledJob.agent_id,
            ScheduledJob.question,
            ScheduledJob.username,
            ScheduleType.name.label('schedule_type'),
            DistributionList.name.label('distribution_list'),
            ScheduledJob.cron_expression,
            ScheduledJob.start_date,
            ScheduledJob.status,
            ScheduledJob.created_at
        ).join(
            ScheduleType, ScheduledJob.schedule_type_id == ScheduleType.id
        ).join(
            DistributionList, ScheduledJob.distribution_list_id == DistributionList.id
        ).filter(
            ScheduledJob.user_id == data.user_id,
            ScheduledJob.agent_id.in_(agents_to_fetch)
        ).all()
        return query
    
    jobs = await run_in_threadpool(get_jobs)
    
    # Convert Row objects to dictionaries for JSON serialization
    jobs_list = []
    for job in jobs:
        jobs_list.append({
            "id": job.id,
            "agent_id": job.agent_id,
            "question": job.question,
            "username": job.username,
            "schedule_type": job.schedule_type,
            "distribution_list": job.distribution_list,
            "cron_expression": job.cron_expression,
            "start_date": job.start_date.isoformat() if job.start_date else None,
            "status": job.status,
            "created_at": job.created_at.isoformat() if job.created_at else None
        })
    
    return {
        "success": True,
        "message": "User scheduled jobs retrieved successfully",
        "details": {"jobs": jobs_list}
    }


@router.post("/jobs/create", status_code=201, response_model=CreateScheduledJobResponse)
async def create_scheduled_job(data: ScheduledJobCreate, db: Session = Depends(get_db)):
    """
    Create a scheduled job to automatically execute questions at specified intervals.
    
    This endpoint creates a scheduled job that will run automatically based on the selected schedule type.
    The cron expression is automatically calculated from the provided schedule-specific parameters
    and saved to the database.
    
    **Schedule Types and Required Parameters:**
    
    1. **Hourly (schedule_type_id = 1)**
       - Runs at a specific minute of every hour
       - Required: `minute` (0-59) - the minute when the job should run each hour
       - Example: minute=15 → runs at :15 past every hour (1:15, 2:15, 3:15, etc.)
       - Cron format: `"{minute} * * * *"`
    
    2. **Daily (schedule_type_id = 2)**
       - Runs once every day at a specific time
       - Required: `start_time` (format: "HH:MM" or "HH:MM:SS") - the time to run daily
       - Example: start_time="09:00" → runs every day at 9:00 AM
       - Cron format: `"{minute} {hour} * * *"`
    
    3. **Weekly (schedule_type_id = 3)**
       - Runs once a week on a specific day and time
       - Required: `start_time` (format: "HH:MM" or "HH:MM:SS") and `day_of_week` (e.g., "Monday", "Tuesday")
       - Example: start_time="10:00", day_of_week="Monday" → runs every Monday at 10:00 AM
       - Valid days: Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
       - Cron format: `"{minute} {hour} * * {cron_day}"`
    
    4. **Monthly (schedule_type_id = 4)**
       - Runs once a month on a specific date and time
       - Required: `start_time` (format: "HH:MM" or "HH:MM:SS") and `day_of_month` (1-31)
       - Example: start_time="08:00", day_of_month=15 → runs on the 15th of each month at 8:00 AM
       - Note: If the selected day doesn't exist in a month (e.g., Feb 30), the schedule will be skipped
       - Cron format: `"{minute} {hour} {day_of_month} * *"`
    
    **Note:**
    - You can either provide `cron_expression` directly OR provide schedule-specific parameters to calculate it automatically
    - If `cron_expression` is provided, it will be used as-is
    - If `cron_expression` is not provided, it will be calculated from the schedule-specific parameters based on schedule_type_id
    
    Uses retry logic for database locks and ensures database connection is closed after response.
    """
    max_retries = 5
    retry_delay = 0.1  # Start with 100ms delay
    
    # Use the provided session for the first attempt
    current_db = db
    manually_created_session = False
    
    try:
        for attempt in range(max_retries):
            try:
                # Create a new session for retries (after first attempt)
                if attempt > 0:
                    current_db = SessionLocal()
                    manually_created_session = True
                
                try:
                    # Parse date - handle both "/" and "-" formats
                    date_formats = ["%Y-%m-%d", "%Y/%m/%d", "%Y-%m-%d %H:%M:%S", "%Y/%m/%d %H:%M:%S"]
                    start_date_obj = None
                    
                    for date_format in date_formats:
                        try:
                            start_date_obj = datetime.strptime(data.start_date, date_format)
                            break
                        except ValueError:
                            continue
                    
                    # Try ISO format as fallback
                    if start_date_obj is None:
                        try:
                            start_date_obj = datetime.fromisoformat(data.start_date)
                        except ValueError:
                            pass
                    
                    if start_date_obj is None:
                        if manually_created_session:
                            current_db.close()
                        return {
                            "success": False,
                            "message": f"Invalid date format. Expected formats: YYYY-MM-DD or YYYY/MM/DD. Received: {data.start_date}",
                            "details": {}
                        }
                    
                    
                    # Ensure schedule types exist
                    def check_schedule_types():
                        if not current_db.query(ScheduleType).first():
                            ensure_default_schedule_types(current_db)
                    
                    await run_in_threadpool(check_schedule_types)
                    
                    # Verify schedule_type_id exists
                    def get_schedule_type():
                        return current_db.query(ScheduleType).filter(ScheduleType.id == data.schedule_type_id).first()
                    
                    schedule_type = await run_in_threadpool(get_schedule_type)
                    if not schedule_type:
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                        return {
                            "success": False,
                            "message": f"Schedule type with id {data.schedule_type_id} not found",
                            "details": {}
                        }
                    
                    # Verify distribution_list_id exists
                    def get_distribution_list():
                        return current_db.query(DistributionList).filter(DistributionList.id == data.distribution_list_id, DistributionList.created_by == data.user_id).first()

                    distribution_list = await run_in_threadpool(get_distribution_list)
                    if not distribution_list:
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                        return {
                            "success": False,
                            "message": f"Distribution list with id {data.distribution_list_id} not found",
                            "details": {}
                        }
                    
                    # Generate cron expression based on schedule type
                    # If cron_expression is provided, use it; otherwise calculate from schedule-specific parameters
                    # This matches the exact logic from create_job_scheduler_management.py
                    cron_expression = data.cron_expression if data.cron_expression and data.cron_expression.strip() else None
                    
                    # If cron_expression not provided, calculate it from schedule-specific parameters
                    if not cron_expression:
                        days_of_week = [
                            "Monday", "Tuesday", "Wednesday", "Thursday", 
                            "Friday", "Saturday", "Sunday"
                        ]
                        
                        # Generate cron expression based on schedule type (matching Streamlit logic exactly)
                        if data.schedule_type_id == 1:  # Hourly
                            if data.minute is not None:
                                cron_expression = f"{data.minute} * * * *"
                        
                        elif data.schedule_type_id == 2:  # Daily
                            if data.start_time is not None:
                                try:
                                    # Parse time string (HH:MM or HH:MM:SS) - convert to hour and minute
                                    time_parts = data.start_time.split(':')
                                    if len(time_parts) < 2:
                                        raise ValueError("Invalid time format")
                                    hour = int(time_parts[0])
                                    minute_val = int(time_parts[1])
                                    if not (0 <= hour <= 23) or not (0 <= minute_val <= 59):
                                        raise ValueError("Invalid time range")
                                except (ValueError, IndexError) as e:
                                    if manually_created_session:
                                        current_db.close()
                                    return {
                                        "success": False,
                                        "message": f"Invalid start_time format. Expected 'HH:MM' or 'HH:MM:SS'. Error: {str(e)}",
                                        "details": {}
                                    }
                                cron_expression = f"{minute_val} {hour} * * *"
                        
                        elif data.schedule_type_id == 3:  # Weekly
                            if data.start_time is not None and data.day_of_week is not None:
                                try:
                                    # Parse time string
                                    time_parts = data.start_time.split(':')
                                    if len(time_parts) < 2:
                                        raise ValueError("Invalid time format")
                                    hour = int(time_parts[0])
                                    minute_val = int(time_parts[1])
                                    if not (0 <= hour <= 23) or not (0 <= minute_val <= 59):
                                        raise ValueError("Invalid time range")
                                except (ValueError, IndexError) as e:
                                    if manually_created_session:
                                        current_db.close()
                                    return {
                                        "success": False,
                                        "message": f"Invalid start_time format. Expected 'HH:MM' or 'HH:MM:SS'. Error: {str(e)}",
                                        "details": {}
                                    }
                                
                                # Validate day of week
                                if data.day_of_week not in days_of_week:
                                    if manually_created_session:
                                        current_db.close()
                                    return {
                                        "success": False,
                                        "message": f"Invalid day_of_week. Must be one of: {', '.join(days_of_week)}",
                                        "details": {}
                                    }
                                
                                day_num = days_of_week.index(data.day_of_week)  # 0=Monday, 6=Sunday
                                # Convert to cron format (0=Sunday, 1=Monday, ..., 6=Saturday)
                                cron_day = 0 if day_num == 6 else day_num + 1
                                cron_expression = f"{minute_val} {hour} * * {cron_day}"
                        
                        elif data.schedule_type_id == 4:  # Monthly
                            if data.start_time is not None and data.day_of_month is not None:
                                try:
                                    # Parse time string
                                    time_parts = data.start_time.split(':')
                                    if len(time_parts) < 2:
                                        raise ValueError("Invalid time format")
                                    hour = int(time_parts[0])
                                    minute_val = int(time_parts[1])
                                    if not (0 <= hour <= 23) or not (0 <= minute_val <= 59):
                                        raise ValueError("Invalid time range")
                                except (ValueError, IndexError) as e:
                                    if manually_created_session:
                                        current_db.close()
                                    return {
                                        "success": False,
                                        "message": f"Invalid start_time format. Expected 'HH:MM' or 'HH:MM:SS'. Error: {str(e)}",
                                        "details": {}
                                    }
                                
                                cron_expression = f"{minute_val} {hour} {data.day_of_month} * *"
                    
                    # Validate schedule_type_id
                    if data.schedule_type_id not in [1, 2, 3, 4]:
                        if manually_created_session:
                            current_db.close()
                        return {
                            "success": False,
                            "message": f"Invalid schedule_type_id: {data.schedule_type_id}. Must be 1 (Hourly), 2 (Daily), 3 (Weekly), or 4 (Monthly)",
                            "details": {}
                        }
                    
                    # Ensure cron_expression is set (either provided or calculated)
                    if not cron_expression or cron_expression.strip() == "":
                        if manually_created_session:
                            current_db.close()
                        # Provide specific error messages based on schedule type
                        if data.schedule_type_id == 1:
                            return {
                                "success": False,
                                "message": "Either 'cron_expression' or 'minute' parameter is required for hourly schedule type (0-59)",
                                "details": {}
                            }
                        elif data.schedule_type_id == 2:
                            return {
                                "success": False,
                                "message": "Either 'cron_expression' or 'start_time' parameter is required for daily schedule type (format: 'HH:MM' or 'HH:MM:SS')",
                                "details": {}
                            }
                        elif data.schedule_type_id == 3:
                            return {
                                "success": False,
                                "message": "Either 'cron_expression' or both 'start_time' and 'day_of_week' parameters are required for weekly schedule type",
                                "details": {}
                            }
                        elif data.schedule_type_id == 4:
                            return {
                                "success": False,
                                "message": "Either 'cron_expression' or both 'start_time' and 'day_of_month' parameters are required for monthly schedule type",
                                "details": {}
                            }
                        else:
                            return {
                                "success": False,
                                "message": "Either provide 'cron_expression' directly or provide the required schedule-specific parameters for the selected schedule type.",
                                "details": {}
                            }
                    
                    # Validate that cron_expression was calculated (not None and not empty)
                    if cron_expression is None or cron_expression.strip() == "":
                        if manually_created_session:
                            current_db.close()
                        return {
                            "success": False,
                            "message": "Internal error: Failed to calculate cron_expression from provided parameters",
                            "details": {}
                        }
                    
                    # Get all data except start_date and schedule-specific fields, then set start_date and cron_expression separately
                    # Exclude cron_expression from job_data since we set it explicitly below (either from request or calculated)
                    job_data = data.model_dump(exclude={'start_date', 'minute', 'start_time', 'day_of_week', 'day_of_month', 'cron_expression'})
                    
                    # Ensure cron_expression is set (either provided in request or calculated from parameters)
                    if not cron_expression or cron_expression.strip() == "":
                        if manually_created_session:
                            current_db.close()
                        return {
                            "success": False,
                            "message": "Internal error: cron_expression was not set. This should never happen.",
                            "details": {}
                        }
                    
                    # Use cron_expression (either provided directly or calculated from parameters)
                    job = ScheduledJob(
                        **job_data,
                        cron_expression=cron_expression,  # Either from request or calculated from parameters
                        start_date=start_date_obj,
                        status="active"
                    )
                    await run_in_threadpool(current_db.add, job)
                    await run_in_threadpool(current_db.commit)
                    await run_in_threadpool(current_db.refresh, job)
                    
                    # Convert job to response model
                    job_response = ScheduledJobResponse.model_validate(job)
                    
                    result = CreateScheduledJobResponse(
                        success=True,
                        message="Scheduled job created successfully",
                        details=job_response.model_dump()
                    )
                    
                    # Close manually created session before returning
                    if manually_created_session:
                        await run_in_threadpool(current_db.close)
                    
                    return result
                    
                except OperationalError as e:
                    current_db.rollback()
                    # Check if it's a database locked error
                    if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                        # Close the current session before retry
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                            manually_created_session = False
                        # Exponential backoff: wait longer for each retry
                        time.sleep(retry_delay * (2 ** attempt))
                        continue
                    else:
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                        return {
                            "success": False,
                            "message": f"Error creating scheduled job: Database is locked. Please try again later. {str(e)}",
                            "details": {}
                        }
                except HTTPException:
                    if manually_created_session:
                        current_db.close()
                    raise
                except Exception as e:
                    await run_in_threadpool(current_db.rollback)
                    if manually_created_session:
                        await run_in_threadpool(current_db.close)
                    return {
                        "success": False,
                        "message": f"Error creating scheduled job: {str(e)}",
                        "details": {}
                    }
            except HTTPException:
                raise
            except Exception as e:
                if manually_created_session and current_db:
                    await run_in_threadpool(current_db.close)
                return {
                    "success": False,
                    "message": f"Error creating scheduled job: {str(e)}",
                    "details": {}
                }
        
        # If we've exhausted all retries
        return {
            "success": False,
            "message": "Error creating scheduled job: Database is locked after multiple retries. Please try again later.",
            "details": {}
        }
    finally:
        # Ensure any manually created session is closed
        if manually_created_session and current_db:
            try:
                await run_in_threadpool(current_db.close)
            except Exception:
                pass


@router.post("/jobs/create-multi-agent", status_code=201, response_model=CreateMultiAgentScheduledJobResponse)
async def create_multi_agent_scheduled_job(data: ScheduledJobCreate, db: Session = Depends(get_db)):
    """
    Create scheduled jobs for multiple agents using the same schedule configuration.
    
    This endpoint creates multiple scheduled jobs (one for each agent) using the existing schema.
    All agents will follow the same schedule type, timing, and distribution settings.
    
    **Request Parameters:**
    - 'agent_id' can be either a string (single agent) or list of strings (multiple agents)
    - All other parameters are the same as the single-agent endpoint
    
    **Response:**
    Returns list of created jobs along with any creation errors per agent
    
    Uses retry logic for database locks and ensures database connection is closed after response.
    """
    # Determine which agents to schedule
    agents_to_schedule = []
    
    if data.agent_id:
        if isinstance(data.agent_id, list):
            # Multi-agent scenario: agent_id is a list
            agents_to_schedule = data.agent_id
        else:
            # Single-agent scenario: agent_id is a string
            agents_to_schedule = [data.agent_id]
    else:
        return {
            "success": False,
            "message": "'agent_id' must be provided (either as string for single agent or list for multiple agents)",
            "details": {}
        }
    
    if not agents_to_schedule:
        return {
            "success": False,
            "message": "No agents provided for scheduling",
            "details": {}
        }
    
    max_retries = 5
    retry_delay = 0.1  # Start with 100ms delay
    
    # Use the provided session for the first attempt
    current_db = db
    manually_created_session = False
    
    created_jobs = []
    failed_agents = []
    
    try:
        for attempt in range(max_retries):
            try:
                # Create a new session for retries (after first attempt)
                if attempt > 0:
                    current_db = SessionLocal()
                    manually_created_session = True
                
                try:
                    # Parse date - handle both "/" and "-" formats
                    date_formats = ["%Y-%m-%d", "%Y/%m/%d", "%Y-%m-%d %H:%M:%S", "%Y/%m/%d %H:%M:%S"]
                    start_date_obj = None
                    
                    for date_format in date_formats:
                        try:
                            start_date_obj = datetime.strptime(data.start_date, date_format)
                            break
                        except ValueError:
                            continue
                    
                    # Try ISO format as fallback
                    if start_date_obj is None:
                        try:
                            start_date_obj = datetime.fromisoformat(data.start_date)
                        except ValueError:
                            pass
                    
                    if start_date_obj is None:
                        if manually_created_session:
                            current_db.close()
                        return {
                            "success": False,
                            "message": f"Invalid date format. Expected formats: YYYY-MM-DD or YYYY/MM/DD. Received: {data.start_date}",
                            "details": {}
                        }
                    
                    # Ensure schedule types exist
                    def check_schedule_types():
                        if not current_db.query(ScheduleType).first():
                            ensure_default_schedule_types(current_db)
                    
                    await run_in_threadpool(check_schedule_types)
                    
                    # Verify schedule_type_id exists
                    def get_schedule_type():
                        return current_db.query(ScheduleType).filter(ScheduleType.id == data.schedule_type_id).first()
                    
                    schedule_type = await run_in_threadpool(get_schedule_type)
                    if not schedule_type:
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                        return {
                            "success": False,
                            "message": f"Schedule type with id {data.schedule_type_id} not found",
                            "details": {}
                        }
                    
                    # Verify distribution_list_id exists
                    def get_distribution_list():
                        return current_db.query(DistributionList).filter(DistributionList.id == data.distribution_list_id, DistributionList.created_by == data.user_id).first()

                    distribution_list = await run_in_threadpool(get_distribution_list)
                    if not distribution_list:
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                        return {
                            "success": False,
                            "message": f"Distribution list with id {data.distribution_list_id} not found",
                            "details": {}
                        }
                    
                    # Generate cron expression based on schedule type
                    cron_expression = data.cron_expression if data.cron_expression and data.cron_expression.strip() else None
                    
                    # If cron_expression not provided, calculate it from schedule-specific parameters
                    if not cron_expression:
                        days_of_week = [
                            "Monday", "Tuesday", "Wednesday", "Thursday", 
                            "Friday", "Saturday", "Sunday"
                        ]
                        
                        # Generate cron expression based on schedule type
                        if data.schedule_type_id == 1:  # Hourly
                            if data.minute is not None:
                                cron_expression = f"{data.minute} * * * *"
                        
                        elif data.schedule_type_id == 2:  # Daily
                            if data.start_time is not None:
                                try:
                                    time_parts = data.start_time.split(':')
                                    if len(time_parts) < 2:
                                        raise ValueError("Invalid time format")
                                    hour = int(time_parts[0])
                                    minute_val = int(time_parts[1])
                                    if not (0 <= hour <= 23) or not (0 <= minute_val <= 59):
                                        raise ValueError("Invalid time range")
                                except (ValueError, IndexError) as e:
                                    if manually_created_session:
                                        current_db.close()
                                    return {
                                        "success": False,
                                        "message": f"Invalid start_time format. Expected 'HH:MM' or 'HH:MM:SS'. Error: {str(e)}",
                                        "details": {}
                                    }
                                cron_expression = f"{minute_val} {hour} * * *"
                        
                        elif data.schedule_type_id == 3:  # Weekly
                            if data.start_time is not None and data.day_of_week is not None:
                                try:
                                    time_parts = data.start_time.split(':')
                                    if len(time_parts) < 2:
                                        raise ValueError("Invalid time format")
                                    hour = int(time_parts[0])
                                    minute_val = int(time_parts[1])
                                    if not (0 <= hour <= 23) or not (0 <= minute_val <= 59):
                                        raise ValueError("Invalid time range")
                                except (ValueError, IndexError) as e:
                                    if manually_created_session:
                                        current_db.close()
                                    return {
                                        "success": False,
                                        "message": f"Invalid start_time format. Expected 'HH:MM' or 'HH:MM:SS'. Error: {str(e)}",
                                        "details": {}
                                    }
                                
                                # Validate day of week
                                if data.day_of_week not in days_of_week:
                                    if manually_created_session:
                                        current_db.close()
                                    return {
                                        "success": False,
                                        "message": f"Invalid day_of_week. Must be one of: {', '.join(days_of_week)}",
                                        "details": {}
                                    }
                                
                                day_num = days_of_week.index(data.day_of_week)
                                cron_day = 0 if day_num == 6 else day_num + 1
                                cron_expression = f"{minute_val} {hour} * * {cron_day}"
                        
                        elif data.schedule_type_id == 4:  # Monthly
                            if data.start_time is not None and data.day_of_month is not None:
                                try:
                                    time_parts = data.start_time.split(':')
                                    if len(time_parts) < 2:
                                        raise ValueError("Invalid time format")
                                    hour = int(time_parts[0])
                                    minute_val = int(time_parts[1])
                                    if not (0 <= hour <= 23) or not (0 <= minute_val <= 59):
                                        raise ValueError("Invalid time range")
                                except (ValueError, IndexError) as e:
                                    if manually_created_session:
                                        current_db.close()
                                    return {
                                        "success": False,
                                        "message": f"Invalid start_time format. Expected 'HH:MM' or 'HH:MM:SS'. Error: {str(e)}",
                                        "details": {}
                                    }
                                
                                cron_expression = f"{minute_val} {hour} {data.day_of_month} * *"
                    
                    # Validate schedule_type_id
                    if data.schedule_type_id not in [1, 2, 3, 4]:
                        if manually_created_session:
                            current_db.close()
                        return {
                            "success": False,
                            "message": f"Invalid schedule_type_id: {data.schedule_type_id}. Must be 1 (Hourly), 2 (Daily), 3 (Weekly), or 4 (Monthly)",
                            "details": {}
                        }
                    
                    # Ensure cron_expression is set
                    if not cron_expression or cron_expression.strip() == "":
                        if manually_created_session:
                            current_db.close()
                        if data.schedule_type_id == 1:
                            return {
                                "success": False,
                                "message": "Either 'cron_expression' or 'minute' parameter is required for hourly schedule type (0-59)",
                                "details": {}
                            }
                        elif data.schedule_type_id == 2:
                            return {
                                "success": False,
                                "message": "Either 'cron_expression' or 'start_time' parameter is required for daily schedule type (format: 'HH:MM' or 'HH:MM:SS')",
                                "details": {}
                            }
                        elif data.schedule_type_id == 3:
                            return {
                                "success": False,
                                "message": "Either 'cron_expression' or both 'start_time' and 'day_of_week' parameters are required for weekly schedule type",
                                "details": {}
                            }
                        elif data.schedule_type_id == 4:
                            return {
                                "success": False,
                                "message": "Either 'cron_expression' or both 'start_time' and 'day_of_month' parameters are required for monthly schedule type",
                                "details": {}
                            }
                    
                    # Create jobs for each agent
                    for agent_id in agents_to_schedule:
                        try:
                            job_data = data.model_dump(exclude={'start_date', 'minute', 'start_time', 'day_of_week', 'day_of_month', 'cron_expression', 'agent_id'})
                            
                            job = ScheduledJob(
                                **job_data,
                                agent_id=agent_id,  # Set the current agent
                                cron_expression=cron_expression,
                                start_date=start_date_obj,
                                status="active"
                            )
                            await run_in_threadpool(current_db.add, job)
                        except Exception as e:
                            failed_agents.append({
                                "agent_id": agent_id,
                                "error": str(e)
                            })
                    
                    # Commit all jobs at once
                    await run_in_threadpool(current_db.commit)
                    
                    # Refresh and collect created jobs
                    for job in current_db.query(ScheduledJob).filter(
                        ScheduledJob.user_id == data.user_id,
                        ScheduledJob.question_id == data.question_id,
                        ScheduledJob.agent_id.in_(agents_to_schedule)
                    ).order_by(ScheduledJob.created_at.desc()).limit(len(agents_to_schedule)).all():
                        created_jobs.append(ScheduledJobResponse.model_validate(job).model_dump())
                    
                    # Close manually created session before returning
                    if manually_created_session:
                        await run_in_threadpool(current_db.close)
                    
                    result = CreateMultiAgentScheduledJobResponse(
                        success=len(failed_agents) == 0,
                        message="Multi-agent scheduled jobs created successfully" if len(failed_agents) == 0 else f"Created jobs for some agents with errors",
                        details={
                            "created_jobs": created_jobs
                        }
                    )
                    
                    return result
                    
                except OperationalError as e:
                    current_db.rollback()
                    # Check if it's a database locked error
                    if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                        # Close the current session before retry
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                            manually_created_session = False
                        # Exponential backoff
                        time.sleep(retry_delay * (2 ** attempt))
                        continue
                    else:
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                        return {
                            "success": False,
                            "message": f"Error creating multi-agent scheduled jobs: Database is locked. Please try again later. {str(e)}",
                            "details": {}
                        }
                except HTTPException:
                    if manually_created_session:
                        current_db.close()
                    raise
                except Exception as e:
                    await run_in_threadpool(current_db.rollback)
                    if manually_created_session:
                        await run_in_threadpool(current_db.close)
                    return {
                        "success": False,
                        "message": f"Error creating multi-agent scheduled jobs: {str(e)}",
                        "details": {}
                    }
            except HTTPException:
                raise
            except Exception as e:
                if manually_created_session and current_db:
                    await run_in_threadpool(current_db.close)
                return {
                    "success": False,
                    "message": f"Error creating multi-agent scheduled jobs: {str(e)}",
                    "details": {}
                }
        
        # If we've exhausted all retries
        return {
            "success": False,
            "message": "Error creating multi-agent scheduled jobs: Database is locked after multiple retries. Please try again later.",
            "details": {}
        }
    finally:
        # Ensure any manually created session is closed
        if manually_created_session and current_db:
            try:
                await run_in_threadpool(current_db.close)
            except Exception:
                pass


@router.post("/jobs/status")
async def update_job_status(data: JobIdRequest, db: Session = Depends(get_db)):
    """
    Update job status.
    Uses retry logic for database locks and ensures database connection is closed after response.
    """
    max_retries = 5
    retry_delay = 0.1  # Start with 100ms delay
    
    # Use the provided session for the first attempt
    current_db = db
    manually_created_session = False
    
    try:
        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)
                    manually_created_session = True
                
                try:
                    def get_status_job():
                        return current_db.query(ScheduledJob).filter(ScheduledJob.id == data.job_id, ScheduledJob.user_id == data.user_id, ScheduledJob.agent_id == data.agent_id).first()
                    
                    job = await run_in_threadpool(get_status_job)
                    if not job:
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                        return {
                            "success": False,
                            "message": "Job not found",
                            "details": {}
                        }
                    
                    if data.job_status == 1:
                        job.status = "active"
                        message = "Schedule activated successfully"
                    elif data.job_status == 0:
                        job.status = "inactive"
                        message = "Schedule paused successfully"
                    else:
                        if manually_created_session:
                            current_db.close()
                        return {
                            "success": False,
                            "message": "job_status must be 0 or 1",
                            "details": {}
                        }
                    
                    job.updated_at = datetime.now()
                    await run_in_threadpool(current_db.commit)
                    
                    result = {
                        "success": True,
                        "message": message,
                        "details": {"job_id": job.id, "status": job.status}
                    }
                    
                    # Close manually created session before returning
                    if manually_created_session:
                        await run_in_threadpool(current_db.close)
                    
                    return result
                    
                except OperationalError as e:
                    current_db.rollback()
                    # Check if it's a database locked error
                    if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                        # Close the current session before retry
                        if manually_created_session:
                            current_db.close()
                            manually_created_session = False
                        # Exponential backoff: wait longer for each retry
                        time.sleep(retry_delay * (2 ** attempt))
                        continue
                    else:
                        if manually_created_session:
                            current_db.close()
                        return {
                            "success": False,
                            "message": f"Error updating job status: Database is locked. Please try again later. {str(e)}",
                            "details": {}
                        }
                except HTTPException:
                    if manually_created_session:
                        current_db.close()
                    raise
                except Exception as e:
                    await run_in_threadpool(current_db.rollback)
                    if manually_created_session:
                        await run_in_threadpool(current_db.close)
                    return {
                        "success": False,
                        "message": f"Error updating job status: {str(e)}",
                        "details": {}
                    }
            except HTTPException:
                raise
            except Exception as e:
                if manually_created_session and current_db:
                    await run_in_threadpool(current_db.close)
                return {
                    "success": False,
                    "message": f"Error updating job status: {str(e)}",
                    "details": {}
                }
        
        # If we've exhausted all retries
        return {
            "success": False,
            "message": "Error updating job status: Database is locked after multiple retries. Please try again later.",
            "details": {}
        }
    finally:
        # Ensure any manually created session is closed
        if manually_created_session and current_db:
            try:
                current_db.close()
            except Exception:
                pass


@router.post("/jobs/delete")
async def delete_scheduled_job(data: JobIdRequest, db: Session = Depends(get_db)):
    """
    Delete a scheduled job.
    Uses retry logic for database locks and ensures database connection is closed after response.
    """
    max_retries = 5
    retry_delay = 0.1  # Start with 100ms delay
    
    # Use the provided session for the first attempt
    current_db = db
    manually_created_session = False
    
    try:
        for attempt in range(max_retries):
            try:
                # Create a new session for retries (after first attempt)
                if attempt > 0:
                    current_db = SessionLocal()
                    manually_created_session = True
                
                try:
                    job = current_db.query(ScheduledJob).filter(ScheduledJob.id == data.job_id, ScheduledJob.user_id == data.user_id, ScheduledJob.agent_id == data.agent_id).first()
                    if not job:
                        if manually_created_session:
                            current_db.close()
                        return {
                            "success": False,
                            "message": "Job not found",
                            "details": {}
                        }

                    def delete_job_and_logs():
                        current_db.query(ExecutionLog).filter(
                            ExecutionLog.scheduled_job_id == data.job_id,
                            ExecutionLog.user_id == data.user_id,
                            ExecutionLog.agent_id == data.agent_id
                        ).delete()
                        current_db.delete(job)
                        current_db.commit()

                    await run_in_threadpool(delete_job_and_logs)
                    
                    result = {
                        "success": True,
                        "message": "Job deleted successfully",
                        "details": {"id": data.job_id}
                    }
                    
                    # Close manually created session before returning
                    if manually_created_session:
                        current_db.close()
                    
                    return result
                    
                except OperationalError as e:
                    current_db.rollback()
                    # Check if it's a database locked error
                    if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                        # Close the current session before retry
                        if manually_created_session:
                            current_db.close()
                            manually_created_session = False
                        # Exponential backoff: wait longer for each retry
                        time.sleep(retry_delay * (2 ** attempt))
                        continue
                    else:
                        if manually_created_session:
                            current_db.close()
                        return {
                            "success": False,
                            "message": f"Error deleting scheduled job: Database is locked. Please try again later. {str(e)}",
                            "details": {}
                        }
                except HTTPException:
                    if manually_created_session:
                        current_db.close()
                    raise
                except Exception as e:
                    current_db.rollback()
                    if manually_created_session:
                        current_db.close()
                    return {
                        "success": False,
                        "message": f"Error deleting scheduled job: {str(e)}",
                        "details": {}
                    }
            except HTTPException:
                raise
            except Exception as e:
                if manually_created_session and current_db:
                    current_db.close()
                return {
                    "success": False,
                    "message": f"Error deleting scheduled job: {str(e)}",
                    "details": {}
                }
        
        # If we've exhausted all retries
        return {
            "success": False,
            "message": "Error deleting scheduled job: Database is locked after multiple retries. Please try again later.",
            "details": {}
        }
    finally:
        # Ensure any manually created session is closed
        if manually_created_session and current_db:
            try:
                current_db.close()
            except Exception:
                pass


# Execution Logs
@router.post("/execution-logs")
async def get_job_execution_history(data: JobHistoryRequest, db: Session = Depends(get_db)):
    
    def get_execution_logs():
        query = db.query(ExecutionLog).filter(
            ExecutionLog.user_id == data.user_id,
            ExecutionLog.agent_id == data.agent_id
        )
        
        # Filter by job_id only if provided
        if data.job_id is not None:
            query = query.filter(ExecutionLog.scheduled_job_id == data.job_id)
        
        return query.order_by(ExecutionLog.execution_time.desc()).limit(100).all()

    logs = await run_in_threadpool(get_execution_logs)
    
    return {
        "success": True,
        "message": "Execution logs retrieved successfully",
        "details": {"logs": logs}
    }


@router.post("/execution-logs/create", status_code=201, response_model=CreateExecutionLogResponse)
async def create_execution_log(data: ExecutionLogCreate, db: Session = Depends(get_db)):
    """
    Create an execution log entry for a scheduled job.
    
    This endpoint creates a log entry when a scheduled job is executed.
    It records the execution details including status, API parameters, and response validator.
    
    Uses retry logic for database locks and ensures database connection is closed after response.
    """
    max_retries = 5
    retry_delay = 0.1  # Start with 100ms delay
    
    # Use the provided session for the first attempt
    current_db = db
    manually_created_session = False
    
    try:
        for attempt in range(max_retries):
            try:
                # Create a new session for retries (after first attempt)
                if attempt > 0:
                    current_db = SessionLocal()
                    manually_created_session = True
                
                try:
                    # Verify scheduled_job_id exists
                    def get_scheduled_job():
                        return current_db.query(ScheduledJob).filter(
                            ScheduledJob.id == data.scheduled_job_id,
                            ScheduledJob.user_id == data.user_id,
                            ScheduledJob.agent_id == data.agent_id
                        ).first()

                    scheduled_job = await run_in_threadpool(get_scheduled_job)
                    
                    if not scheduled_job:
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                        return {
                            "success": False,
                            "message": f"Scheduled job with id {data.scheduled_job_id} not found for user {data.user_id} and agent {data.agent_id}",
                            "details": {}
                        }
                    
                    # Parse execution_time - handle multiple formats
                    date_formats = [
                        "%Y-%m-%d %H:%M:%S",
                        "%Y/%m/%d %H:%M:%S",
                        "%Y-%m-%d %H:%M",
                        "%Y/%m/%d %H:%M",
                        "%Y-%m-%d",
                        "%Y/%m/%d"
                    ]
                    execution_time_obj = None
                    
                    for date_format in date_formats:
                        try:
                            execution_time_obj = datetime.strptime(data.execution_time, date_format)
                            break
                        except ValueError:
                            continue
                    
                    # Try ISO format as fallback
                    if execution_time_obj is None:
                        try:
                            execution_time_obj = datetime.fromisoformat(data.execution_time)
                        except ValueError:
                            pass
                    
                    if execution_time_obj is None:
                        if manually_created_session:
                            current_db.close()
                        return {
                            "success": False,
                            "message": f"Invalid execution_time format. Expected formats: YYYY-MM-DD HH:MM:SS, YYYY-MM-DD, or ISO format. Received: {data.execution_time}",
                            "details": {}
                        }
                    
                    # Create execution log entry
                    execution_log = ExecutionLog(
                        scheduled_job_id=data.scheduled_job_id,
                        user_id=data.user_id,
                        agent_id=data.agent_id,
                        question=data.question,
                        execution_time=execution_time_obj,
                        status=data.status,
                        api_params=data.api_params,
                        api_response_validator=data.api_response_validator
                    )
                    
                    current_db.add(execution_log)
                    await run_in_threadpool(current_db.commit)
                    await run_in_threadpool(current_db.refresh, execution_log)
                    
                    # Convert to response model
                    log_response = ExecutionLogResponse.model_validate(execution_log)
                    
                    result = CreateExecutionLogResponse(
                        success=True,
                        message="Execution log created successfully",
                        details=log_response.model_dump()
                    )
                    
                    # Close manually created session before returning
                    if manually_created_session:
                        current_db.close()
                    
                    return result
                    
                except OperationalError as e:
                    current_db.rollback()
                    # Check if it's a database locked error
                    if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                        # Close the current session before retry
                        if manually_created_session:
                            current_db.close()
                            manually_created_session = False
                        # Exponential backoff: wait longer for each retry
                        time.sleep(retry_delay * (2 ** attempt))
                        continue
                    else:
                        if manually_created_session:
                            current_db.close()
                        return {
                            "success": False,
                            "message": f"Error creating execution log: Database is locked. Please try again later. {str(e)}",
                            "details": {}
                        }
                except HTTPException:
                    if manually_created_session:
                        current_db.close()
                    raise
                except Exception as e:
                    current_db.rollback()
                    if manually_created_session:
                        current_db.close()
                    return {
                        "success": False,
                        "message": f"Error creating execution log: {str(e)}",
                        "details": {}
                    }
            except HTTPException:
                raise
            except Exception as e:
                if manually_created_session and current_db:
                    current_db.close()
                return {
                    "success": False,
                    "message": f"Error creating execution log: {str(e)}",
                    "details": {}
                }
        
        # If we've exhausted all retries
        return {
            "success": False,
            "message": "Error creating execution log: Database is locked after multiple retries. Please try again later.",
            "details": {}
        }
    finally:
        # Ensure any manually created session is closed
        if manually_created_session and current_db:
            try:
                current_db.close()
            except Exception:
                pass
