"""
Background scheduler for periodic tasks like keeping the database alive.
"""
import logging
from apscheduler.schedulers.background import BackgroundScheduler
from apscheduler.triggers.interval import IntervalTrigger

logger = logging.getLogger(__name__)


def keep_db_alive(db):
    """
    Execute a simple query to keep the database connection alive.
    Prevents connection timeouts from idle databases.
    """
    try:
        # Execute a simple query to test the connection
        db.session.execute("SELECT 1")
        db.session.commit()
        logger.debug("Database keep-alive ping successful")
    except Exception as e:
        logger.warning(f"Database keep-alive ping failed: {e}")
        try:
            db.session.rollback()
        except Exception as rollback_err:
            logger.error(f"Failed to rollback after keep-alive error: {rollback_err}")


def init_scheduler(app, db):
    """
    Initialize the background scheduler with periodic database keep-alive jobs.
    
    Args:
        app: Flask application instance
        db: SQLAlchemy database instance
    """
    scheduler = BackgroundScheduler()
    
    # Add a job to ping the database every 5 minutes (300 seconds)
    # Adjust the interval as needed based on your database provider's timeout policy
    scheduler.add_job(
        func=keep_db_alive,
        args=[db],
        trigger=IntervalTrigger(seconds=300),  # 5 minutes
        id="keep_db_alive",
        name="Keep database connection alive",
        replace_existing=True,
    )
    
    try:
        scheduler.start()
        logger.info("Background scheduler started with database keep-alive job")
    except Exception as e:
        logger.error(f"Failed to start scheduler: {e}")
    
    return scheduler