# separate_scheduler.py
from dotenv import load_dotenv
load_dotenv()  # Load environment variables from .env file

from apscheduler.schedulers.background import BackgroundScheduler
from apscheduler.triggers.interval import IntervalTrigger
from apscheduler.triggers.cron import CronTrigger
import logging
from datetime import datetime, timedelta
from typing import List, Dict, Optional
import time
import threading
import sys
import os
import atexit

from sqlalchemy.orm import Session
from sqlalchemy.exc import OperationalError, IntegrityError

# FastAPI imports
from app.core.database import SessionLocal
from app.models.schedule import ScheduledJob, ExecutionLog
from app.models.distribution import DistributionListEmail
from app.models.question import Question, UserQuestionLog
from app.models.user import User
from app.services.agent_config import get_agent_config

# Configure logging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('scheduler.log'),
        logging.StreamHandler(sys.stdout)
    ]
)
logger = logging.getLogger(__name__)

# Global variables
scheduler = None
shutdown_event = threading.Event()
LOCK_FILE_PATH = "scheduler.lock"
PID_FILE_PATH = "scheduler.pid"


def acquire_lock():
    """Acquire exclusive lock using file existence check (cross-platform)"""
    try:
        # Check if lock file exists and process is running
        if os.path.exists(LOCK_FILE_PATH):
            if is_scheduler_running():
                return False
            else:
                # Remove stale lock file
                os.remove(LOCK_FILE_PATH)
        
        # Create lock file
        with open(LOCK_FILE_PATH, 'w') as f:
            f.write(str(os.getpid()))
        
        # Create PID file
        with open(PID_FILE_PATH, 'w') as f:
            f.write(str(os.getpid()))
        
        logger.info(f"Lock acquired successfully. PID: {os.getpid()}")
        return True
    except Exception as e:
        logger.error(f"Error acquiring lock: {e}")
        return False

def release_lock():
    """Release the exclusive lock"""
    try:
        if os.path.exists(LOCK_FILE_PATH):
            os.remove(LOCK_FILE_PATH)
        if os.path.exists(PID_FILE_PATH):
            os.remove(PID_FILE_PATH)
        logger.info("Lock released successfully")
    except Exception as e:
        logger.error(f"Error releasing lock: {e}")

def is_scheduler_running():
    """Check if another scheduler instance is already running"""
    if not os.path.exists(PID_FILE_PATH):
        return False
    
    try:
        with open(PID_FILE_PATH, 'r') as f:
            pid = int(f.read().strip())
            
        # Check if process is still running
        try:
            os.kill(pid, 0)  # Send signal 0 to check if process exists
            return True
        except OSError:
            # Process doesn't exist, remove stale PID file
            os.remove(PID_FILE_PATH)
            if os.path.exists(LOCK_FILE_PATH):
                os.remove(LOCK_FILE_PATH)
            return False
            
    except (ValueError, FileNotFoundError):
        return False

def get_running_scheduler_pid():
    """Get the PID of the currently running scheduler"""
    if not os.path.exists(PID_FILE_PATH):
        return None
    
    try:
        with open(PID_FILE_PATH, 'r') as f:
            pid = int(f.read().strip())
            
        # Verify the process is still running
        try:
            os.kill(pid, 0)  # Send signal 0 to check if process exists
            return pid
        except OSError:
            # Process doesn't exist, remove stale PID file
            os.remove(PID_FILE_PATH)
            if os.path.exists(LOCK_FILE_PATH):
                os.remove(LOCK_FILE_PATH)
            return None
            
    except (ValueError, FileNotFoundError):
        return None

def matches_cron_time(cron_expr, check_time):
    """Check if a specific time matches the cron expression"""
    try:
        parts = cron_expr.split()
        if len(parts) != 5:
            return False
        
        minute, hour, day_of_month, month, day_of_week = parts
        
        # Check minute
        if minute != '*' and int(minute) != check_time.minute:
            return False
        
        # Check hour
        if hour != '*' and int(hour) != check_time.hour:
            return False
        
        # Check day of month
        if day_of_month != '*' and int(day_of_month) != check_time.day:
            return False
        
        # Check month
        if month != '*' and int(month) != check_time.month:
            return False
        
        # Check day of week (convert Python weekday to cron format)
        if day_of_week != '*':
            cron_weekday = (check_time.weekday() + 1) % 7  # Convert to Sunday=0 format
            if int(day_of_week) != cron_weekday:
                return False
        
        return True
        
    except Exception as e:
        print(f"Error matching cron time: {e}")
        return False


def should_job_run_now(cron_expression, start_date_main):
    """Check if a job should run based on cron expression and start date"""
    from datetime import datetime
    
    now = datetime.now().date()
    current_time = datetime.now() 
    
    # Handle both datetime objects and strings
    if isinstance(start_date_main, datetime):
        start_date = start_date_main.date()
    elif isinstance(start_date_main, str):
        start_date = datetime.strptime(start_date_main, "%Y-%m-%d").date()
    else:
        # If it's already a date object
        start_date = start_date_main

    # Job shouldn't run before its start date
    if now < start_date:
        return False
    
    # For every 10 minute checks, we need to be more flexible
    # Check if the cron time has passed in the last 10 minutes
    return is_cron_time_in_window(cron_expression, current_time, 10)


def is_cron_time_in_window(cron_expr, current_time, window_minutes):
    """Check if cron time falls within the specified window (in minutes)"""
    from datetime import datetime, timedelta
    
    try:
        parts = cron_expr.split()
        if len(parts) != 5:
            return False
        
        minute, hour, day_of_month, month, day_of_week = parts
        
        # Create a time window
        window_start = current_time - timedelta(minutes=window_minutes)
        
        # Check each minute in the window
        check_time = window_start
        while check_time <= current_time:
            if matches_cron_time(cron_expr, check_time):
                return True
            check_time += timedelta(minutes=1)
        
        return False
        
    except Exception as e:
        print(f"Error checking cron window: {e}")
        return False

def get_latest_cron_time(cron_expr: str, now: datetime) -> Optional[datetime]:
    """Find the most recent time matching the cron expression, looking back up to 24 hours"""
    # Check each minute starting from now and going backwards
    # We check up to 1440 minutes (24 hours)
    for i in range(1441):
        check_time = now - timedelta(minutes=i)
        if matches_cron_time(cron_expr, check_time):
            # Return matching time with seconds and microseconds zeroed out
            return check_time.replace(second=0, microsecond=0)
    return None

# Database helper functions
def get_jobs_to_run(db: Session) -> List[dict]:
    """Get scheduled jobs that should run now based on cron expressions"""
    now = datetime.now()
    jobs = db.query(ScheduledJob).filter(
        ScheduledJob.status == "active",
        ScheduledJob.start_date <= now
    ).all()
    jobs_to_run = []
    for job in jobs:
        try:

            # Check if should run now (within last 10 minutes)
            if should_job_run_now(job.cron_expression, job.start_date):
                jobs_to_run.append({
                    "id": job.id,
                    "user_id": job.user_id,
                    "agent_id": job.agent_id,
                    "question": job.question,
                    "question_id": job.question_id,
                    "distribution_list_id": job.distribution_list_id,
                    "cron_expression": job.cron_expression
                })
        except Exception as e:
            logger.error(f"Error parsing cron for job {job.id}: {e}")
    print(jobs_to_run)
    
    return jobs_to_run


def get_distribution_list_emails(db: Session, distribution_list_id: int) -> List[dict]:
    """Get all active emails for a distribution list"""
    emails = db.query(DistributionListEmail).filter(
        DistributionListEmail.distribution_list_id == distribution_list_id,
        DistributionListEmail.is_active == True
    ).all()
    return [{"email": e.email, "name": e.name} for e in emails]


def get_question_by_id(db: Session, question_id: str) -> Optional[dict]:
    """Get question details by ID"""
    try:
        question = db.query(Question).filter(Question.id == int(question_id)).first()
        if question:
            return {
                "id": question.id,
                "agent_id": question.agent_id or "",
                "sql": question.sql or "",
                "chart": question.chart or "",
                "api_endpoint": question.api_endpoint or "",
                "api_params": question.api_params or "",
                "api_response_validator": question.api_response_validator or "",
                "sql_query_summaries": getattr(question, "sql_query_summaries", "") or "",
            }
    except (ValueError, AttributeError) as e:
        logger.error(f"Error getting question {question_id}: {e}")
    return None


def check_if_job_already_ran(db: Session, job_id: int, cron_expression: str) -> bool:
    """Check if job already ran for the current cron slot"""
    now = datetime.now()
    latest_cron_time = get_latest_cron_time(cron_expression, now)
    
    if not latest_cron_time:
        # If no cron match found in last 24h, check from start of today as fallback
        latest_cron_time = now.replace(hour=0, minute=0, second=0, microsecond=0)
        
    execution = db.query(ExecutionLog).filter(
        ExecutionLog.scheduled_job_id == job_id,
        ExecutionLog.execution_time >= latest_cron_time,
        ExecutionLog.status == 'success'
    ).first()
    return execution is not None


def log_job_execution(db: Session, job_id: int, user_id: str, agent_id: str, 
                     question: str, execution_time: datetime, status: str,
                     api_params: str = "", api_response_validator: str = ""):
    """Log job execution with retry logic"""
    max_retries = 5
    retry_delay = 0.1
    
    for attempt in range(max_retries):
        try:
            # Check if scheduled_job_id exists before logging
            job_exists = db.query(ScheduledJob).filter(ScheduledJob.id == job_id).first()
            if not job_exists:
                logger.warning(f"Skipping log insertion: ScheduledJob with ID {job_id} no longer exists. (user_id: {user_id}, agent_id: {agent_id})")
                return None

            execution_log = ExecutionLog(
                scheduled_job_id=job_id,
                user_id=user_id,
                agent_id=agent_id,
                question=question,
                execution_time=execution_time,
                status=status,
                api_params=api_params,
                api_response_validator=api_response_validator
            )
            db.add(execution_log)
            db.commit()
            db.refresh(execution_log)
            return execution_log.id
        except (OperationalError, IntegrityError) as e:
            db.rollback()
            if isinstance(e, IntegrityError):
                logger.warning(f"IntegrityError: ScheduledJob {job_id} might have been deleted during logging. Error: {e}")
                return None
            
            if attempt < max_retries - 1:
                time.sleep(retry_delay * (2 ** attempt))
                continue
            logger.error(f"Error logging job execution after {max_retries} attempts: {e}")
            raise
        except Exception as e:
            db.rollback()
            logger.error(f"Error logging job execution: {e}")
            raise


# Email helper functions
def add_excel_download_to_chat(question: str, agent_id: str, sendemail: str,
                               email_list: List[str], sql: str, chart: str,
                               api_endpoint: str, api_params: str, 
                               api_response_validator: str,
                               sql_query_summaries: str = "",
                               question_id: str = None):
    """
    Create Excel file and send email for scheduled job by calling the download API endpoint.
    
    Uses HTTP request to call /download/download-excel endpoint instead of duplicating logic.
    """
    import requests
    import json
    
    try:
        # Get the base URL for the API from initial_config.txt (BASE_URL + SCHEME)
        from app.core.config import get_base_url
        api_base_url = get_base_url()
        # Determine if this is a multi-agent question
        is_multi_agent = False
        final_agent_id = agent_id
        
        # Parse agent_id if it's a JSON list string
        if isinstance(agent_id, str) and agent_id.strip().startswith('['):
            try:
                parsed_agent_id = json.loads(agent_id)
                if isinstance(parsed_agent_id, list):
                    final_agent_id = parsed_agent_id
                    is_multi_agent = True
            except:
                pass
        elif isinstance(agent_id, list):
            is_multi_agent = True

        # Also check if SQL is a multi-agent JSON
        if isinstance(sql, str) and sql.strip().startswith('{'):
            try:
                sql_data = json.loads(sql)
                if isinstance(sql_data, dict) and "queries" in sql_data:
                    is_multi_agent = True
            except:
                pass

        if is_multi_agent:
            download_url = f"{api_base_url}/api/download/multi-agent-download-excel"
            logger.info(f"Using multi-agent download endpoint for question: {question[:50]}...")
        else:
            download_url = f"{api_base_url}/api/download/download-excel"
            logger.info(f"Using single-agent download endpoint for question: {question[:50]}...")
            
        # Build request payload
        payload = {
            "my_question": question,
            "agent_id": final_agent_id,
            "sendemail": sendemail,
            "email_list": email_list if email_list else ["No schedule found for current time"],
            "sql": sql if sql else None,
            "code": chart if chart else None,
            "api_endpoint": api_endpoint if api_endpoint else None,
            "api_params": api_params if api_params else None,
            "api_response_validator": api_response_validator if api_response_validator else None,
            "sql_query_summaries": sql_query_summaries if sql_query_summaries else None,
            "question_id": question_id if question_id else None
        }
        
        # Remove None values
        payload = {k: v for k, v in payload.items() if v is not None}
        
        logger.info(f"Calling download endpoint for question: {question[:50]}...")
        logger.debug(f"Request payload: {json.dumps(payload, indent=2)}")
        
        # Make HTTP request to download endpoint
        # Note: For internal calls, we need to bypass authentication or use a service token
        # Here we use a scheduler user token or internal authentication header
        headers = {
            "Content-Type": "application/json",
            "X-Scheduler-Token": os.environ.get("SCHEDULER_TOKEN", "scheduler-internal-token"),
            "X-Internal-Request": "true"
        }
        
        response = requests.post(
            download_url,
            json=payload,
            headers=headers,
            timeout=300  # 5 minute timeout for large Excel generation
        )
        
        if response.status_code == 200:
            result = response.json()
            if result.get("success", False):
                logger.info(f"Excel download successful for: {question[:50]}...")
                return True
            else:
                logger.error(f"Download endpoint returned failure: {result.get('message', 'Unknown error')}")
                return False
        else:
            logger.error(f"Download endpoint returned status {response.status_code}: {response.text[:500]}")
            return False
            
    except requests.exceptions.ConnectionError as e:
        logger.error(f"Failed to connect to download endpoint: {e}")
        logger.error("Make sure the FastAPI server is running")
        return False
    except requests.exceptions.Timeout as e:
        logger.error(f"Request to download endpoint timed out: {e}")
        return False
    except Exception as e:
        logger.error(f"Error in add_excel_download_to_chat: {e}")
        import traceback
        logger.error(traceback.format_exc())
        return False


def send_user_logs():
    """Send user activity logs via email to admins"""
    from app.routes.logs import send_email, get_user_resolution_mail
    from textwrap import dedent
    
    db = SessionLocal()
    try:
        # Get agent config for email settings (use first available agent or default)
        agent_config = get_agent_config(agent_id="1")
        
        if not agent_config:
            logger.error("No agent config found for sending user logs")
            return
        
        # Get logs from last 24 hours
        # Note: UserQuestionLog.created_at is stored as Text, so we need to filter differently
        yesterday = datetime.now() - timedelta(days=1)
        yesterday_str = yesterday.strftime("%Y-%m-%d")
        logs = db.query(UserQuestionLog).filter(
            UserQuestionLog.created_at >= yesterday_str
        ).all()
        
        if not logs:
            logger.info("No user logs to send")
            return
        
        # Get all active users for email list
        users = db.query(User).filter(User.status == "active").all()
        admin_emails = [user.email for user in users if user.email]
        
        if not admin_emails:
            logger.warning("No admin emails found for sending user logs")
            return
        
        # Format email content
        log_count = len(logs)
        error_count = len([log for log in logs if log.executed in ["E", "D"]])
        
        subject = f"Daily User Activity Report - {datetime.now().strftime('%Y-%m-%d')}"
        
        html_body = dedent(f"""
            <html>
            <body>
                <h2>Daily User Activity Report</h2>
                <p>Date: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}</p>
                <p><strong>Total Questions:</strong> {log_count}</p>
                <p><strong>Errors/Issues:</strong> {error_count}</p>
                <hr>
                <h3>Recent Activity:</h3>
                <table border="1" cellpadding="5" cellspacing="0">
                    <tr>
                        <th>User ID</th>
                        <th>Agent ID</th>
                        <th>Question</th>
                        <th>Status</th>
                        <th>Time</th>
                    </tr>
        """)
        
        # Add last 20 logs
        for log in logs[:20]:
            html_body += f"""
                    <tr>
                        <td>{log.user_id}</td>
                        <td>{log.agent_id}</td>
                        <td>{log.question[:50]}...</td>
                        <td>{log.executed}</td>
                        <td>{log.created_at}</td>
                    </tr>
            """
        
        html_body += """
                </table>
                <p><em>This is an automated report from askme360 scheduler.</em></p>
            </body>
            </html>
        """
        
        # Send email to admins
        success = send_email(
            sender_email=agent_config.get("sender_email_id", agent_config.get("sender_email", "")),
            sender_name="askme360",
            recipient_email=admin_emails,
            subject=subject,
            body_text=f"Daily User Activity Report\n\nTotal Questions: {log_count}\nErrors/Issues: {error_count}",
            body_html=html_body,
            agent_conf=agent_config
        )
        
        if success:
            logger.info(f"User logs email sent successfully to {len(admin_emails)} admins")
        else:
            logger.error("Failed to send user logs email")
            
    except Exception as e:
        logger.error(f"Error sending user logs: {e}")
        import traceback
        logger.error(traceback.format_exc())
    finally:
        db.close()


def send_email_task():
    print(f"[{datetime.now()}] I am running every 1 minute")
   
    db = SessionLocal()
    try:
        job_list = get_jobs_to_run(db)
        for job in job_list:
            try:
                distribution_list = get_distribution_list_emails(db, job["distribution_list_id"])
                email_list = []
                for email in distribution_list:
                    email_list.append(email["email"])
                
                if not check_if_job_already_ran(db, job['id'], job['cron_expression']):
                    logger.info(f"Processing job: {job['question']} for emails: {email_list}")
                    
                    question_data = get_question_by_id(db, job["question_id"])
                    if not question_data:
                        logger.error(f"Question {job['question_id']} not found")
                        log_job_execution(db, job["id"], job["user_id"], job["agent_id"], 
                                         job["question"], datetime.now(), "failed",
                                         "", "")
                        continue
                    
                    ques_sql = question_data.get('sql', '')
                    ques_chart = question_data.get('chart', '')
                    api_endpoint = question_data.get('api_endpoint', '')
                    api_params = question_data.get('api_params', '')
                    api_response_validator = question_data.get('api_response_validator', '')
                    sql_query_summaries = question_data.get('sql_query_summaries', '')
                    
                    # Use agent_id from question if it's multi-agent or different
                    agent_id_to_use = question_data.get('agent_id') or job["agent_id"]
                    
                    success = add_excel_download_to_chat(
                        job["question"], agent_id_to_use, "Y", email_list,
                        ques_sql, ques_chart, api_endpoint, api_params, api_response_validator,
                        sql_query_summaries=sql_query_summaries,
                    )
                    
                    if success:
                        log_job_execution(db, job["id"], job["user_id"], job["agent_id"], 
                                         job["question"], datetime.now(), "success",
                                         api_params, api_response_validator)
                        logger.info(f"Email sent for job: {job['question']}")
                    else:
                        log_job_execution(db, job["id"], job["user_id"], job["agent_id"], 
                                         job["question"], datetime.now(), "failed",
                                         api_params, api_response_validator)
                        logger.error(f"Failed to send email for job: {job['question']}")
                else:
                    logger.info(f"Job {job['id']} already ran for the current slot, skipping")
            except Exception as e:
                try:
                    log_job_execution(db, job["id"], job["user_id"], job["agent_id"], 
                                     job["question"], datetime.now(), "failed", "", "")
                except:
                    pass
                logger.error(f"Error processing job {job}: {str(e)}")
                import traceback
                logger.error(traceback.format_exc())

        logger.info("Database task executed successfully")
    finally:
        db.close()


def send_user_log_task():
    print(f"[{datetime.now()}] I am running every 24 hours")
    try:
        send_user_logs()
        logger.info("User logs task executed successfully")
    except Exception as e:
        logger.error(f"Error sending user logs: {str(e)}")
        import traceback
        logger.error(traceback.format_exc())

def shutdown_gracefully():
    """Handle shutdown gracefully"""
    global scheduler
    logger.info("Shutting down gracefully...")
    
    if scheduler:
        scheduler.shutdown()
        logger.info("Scheduler shut down successfully")
    
    release_lock()
    shutdown_event.set()

def main():
    """Main function to initialize and run the scheduler"""
    global scheduler
    
    # Check if scheduler is already running
    if is_scheduler_running():
        running_pid = get_running_scheduler_pid()
        logger.error(f"Scheduler is already running with PID: {running_pid}")
        logger.error("Only one scheduler instance is allowed at a time.")
        logger.error("To stop the running scheduler:")
        logger.error(f"  1. Run: python {sys.argv[0]} --stop")
        logger.error(f"  2. Or kill the process: kill {running_pid}")
        sys.exit(1)
    
    # Try to acquire lock
    if not acquire_lock():
        logger.error("Failed to acquire lock. Another scheduler might be starting.")
        logger.error("Please wait a moment and try again.")
        sys.exit(1)
    
    # Register cleanup function
    atexit.register(release_lock)
    
    logger.info("Starting Background Database Task Scheduler")
    logger.info(f"Scheduler PID: {os.getpid()}")
    
    try:
        # Create scheduler (background scheduler)
        scheduler = BackgroundScheduler()
        
        # Add main database task (every 1 minute for testing)
        scheduler.add_job(
            func=send_email_task,
            trigger=IntervalTrigger(minutes=1),
            id='send_email_task',
            name='Email task processing task',
            replace_existing=True
        )

        scheduler.add_job(
            func=send_user_log_task,
            trigger=CronTrigger(hour=9, minute=0),  # Runs every day at 9:00 AM
            id='send_user_log_task',
            name='Users Logs task processing task',
            replace_existing=True
        )

        logger.info("Scheduler configured with the following jobs:")
        logger.info("- Database task: Every 1 minute")
        
        # Start scheduler
        scheduler.start()
        logger.info("Scheduler started successfully")
        
        logger.info("Background scheduler is running... (Press Ctrl+C to stop)")
        logger.info("To stop the scheduler, you can:")
        logger.info("1. Press Ctrl+C in this terminal")
        logger.info(f"2. Run: python {sys.argv[0]} --stop")
        logger.info(f"3. Kill the process: kill {os.getpid()}")
        
        # Keep the main thread alive and check for shutdown conditions
        while not shutdown_event.is_set():
            try:
                # Check for stop file
                if os.path.exists('stop_scheduler.txt'):
                    logger.info("Stop file detected. Shutting down...")
                    os.remove('stop_scheduler.txt')
                    break
                
                # Sleep for a short period
                time.sleep(1)
                
            except KeyboardInterrupt:
                logger.info("Received keyboard interrupt. Shutting down...")
                break
        
    except KeyboardInterrupt:
        logger.info("Received keyboard interrupt. Shutting down...")
    except Exception as e:
        logger.error(f"Unexpected error: {str(e)}")
    finally:
        shutdown_gracefully()
        logger.info("Scheduler stopped")

if __name__ == "__main__":
    import argparse
    
    parser = argparse.ArgumentParser(description='Background Database Task Scheduler')
    parser.add_argument('--daemon', action='store_true', 
                       help='Run as daemon process (Unix/Linux only)')
    parser.add_argument('--stop', action='store_true',
                       help='Stop the running scheduler')
    
    args = parser.parse_args()
    
    if args.stop:
        # Check if scheduler is running
        if not is_scheduler_running():
            print("No scheduler is currently running.")
            sys.exit(0)
        
        running_pid = get_running_scheduler_pid()
        print(f"Stopping scheduler with PID: {running_pid}")
        
        # Create stop file
        with open('stop_scheduler.txt', 'w') as f:
            f.write('stop')
        
        # Wait a moment for graceful shutdown
        print("Waiting for graceful shutdown...")
        time.sleep(3)
        
        # Check if it stopped
        if not is_scheduler_running():
            print("Scheduler stopped successfully.")
        else:
            print("Scheduler did not stop gracefully. You may need to force kill it:")
            print(f"kill {running_pid}")
    elif args.daemon:
        # Add daemon functionality here if needed
        main()
    else:
        main()