"""
Database configuration for scheduled downloads.
Uses SQLite with SQLAlchemy ORM for persistent storage.
"""

from sqlalchemy import create_engine, event
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from pathlib import Path
import logging

logger = logging.getLogger(__name__)

# Database file location - uses /app/data in Docker container
# This directory should be mounted as a volume for persistence
DATA_DIR = Path(__file__).parent.parent / "data"
DB_PATH = DATA_DIR / "scheduled_tasks.db"

# Ensure data directory exists (will be mounted volume in Docker)
DATA_DIR.mkdir(parents=True, exist_ok=True)

# Create engine with proper settings for SQLite in Docker
engine = create_engine(
    f"sqlite:///{DB_PATH}",
    echo=False,
    connect_args={
        "check_same_thread": False,  # Allow multi-threaded access
        "timeout": 30  # Longer timeout for container I/O
    },
    pool_pre_ping=True,  # Verify connections before using
)

# Enable foreign keys for SQLite
@event.listens_for(engine, "connect")
def set_sqlite_pragma(dbapi_conn, connection_record):
    cursor = dbapi_conn.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

# Create session factory
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# Base class for models
Base = declarative_base()


def init_database():
    """Initialize database - called on container startup"""
    try:
        # Check if database file exists
        if DB_PATH.exists():
            logger.info(f"Database file exists at {DB_PATH}, checking for schema upgrades")
            upgrade_database_schema()
        else:
            logger.info(f"Creating new database at {DB_PATH}")

        Base.metadata.create_all(bind=engine)
        logger.info(f"Database initialized at {DB_PATH}")

        # Verify the schema after creation/upgrade
        verify_database_schema()

    except Exception as e:
        logger.error(f"Failed to initialize database: {e}")
        raise


def verify_database_schema():
    """Verify that the database schema has all required columns"""
    try:
        from sqlalchemy import text

        with engine.connect() as conn:
            result = conn.execute(text("PRAGMA table_info(scheduled_tasks)"))
            columns = [row[1] for row in result.fetchall()]

            required_columns = ['upvoted', 'saved']
            missing_columns = [col for col in required_columns if col not in columns]

            if missing_columns:
                logger.error(f"Database schema verification failed. Missing columns: {missing_columns}")
                logger.error(f"Available columns: {columns}")
            else:
                logger.info("Database schema verification passed")

    except Exception as e:
        logger.error(f"Failed to verify database schema: {e}")


def upgrade_database_schema():
    """Check and upgrade database schema for new columns"""
    try:
        from sqlalchemy import text

        logger.info("Starting database schema upgrade check...")

        # Check if scheduled_tasks table exists first
        with engine.connect() as conn:
            # Check if table exists
            result = conn.execute(text("SELECT name FROM sqlite_master WHERE type='table' AND name='scheduled_tasks'"))
            table_exists = result.fetchone() is not None

            if not table_exists:
                logger.info("scheduled_tasks table does not exist, will be created by create_all")
                return

            logger.info("scheduled_tasks table exists, checking columns...")

            # Get table info
            result = conn.execute(text("PRAGMA table_info(scheduled_tasks)"))
            columns = [row[1] for row in result.fetchall()]

            logger.info(f"Existing columns in scheduled_tasks: {columns}")

            # Check for missing columns
            missing_columns = []
            if 'upvoted' not in columns:
                missing_columns.append('upvoted BOOLEAN DEFAULT 0')
            if 'saved' not in columns:
                missing_columns.append('saved BOOLEAN DEFAULT 0')

            # Add missing columns
            for column in missing_columns:
                logger.info(f"Adding missing column: {column}")
                try:
                    conn.execute(text(f"ALTER TABLE scheduled_tasks ADD COLUMN {column}"))
                    conn.commit()
                    logger.info(f"Successfully added column: {column}")
                except Exception as alter_error:
                    logger.error(f"Failed to add column {column}: {alter_error}")
                    raise

            if missing_columns:
                logger.info(f"Database schema upgraded with columns: {missing_columns}")
            else:
                logger.info("Database schema is up to date")

    except Exception as e:
        logger.error(f"Failed to upgrade database schema: {e}")
        logger.error(f"Error type: {type(e)}")
        import traceback
        logger.error(f"Traceback: {traceback.format_exc()}")
        # Don't raise here - let the app continue with create_all


def get_db():
    """
    Dependency for FastAPI to get database session.
    
    Usage:
        @app.get("/endpoint")
        def endpoint(db: Session = Depends(get_db)):
            ...
    """
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()