"""
Database configuration and session management using Tortoise ORM.
"""

import asyncio
from urllib.parse import quote_plus

from tortoise import Tortoise

from app.core.config import settings
from app.core.logging import get_logger
from app.services.aws_service import AWSService

logger = get_logger("database")

# Database configuration - will be populated dynamically
TORTOISE_ORM = {
    "connections": {
        "default": None  # Will be set dynamically
    },
    "apps": {
        "models": {
            "models": ["app.models"],
            "default_connection": "default",
        }
    },
    "use_tz": False,
    "timezone": "UTC",
}


async def _build_connection_string() -> str:
    """Build database connection string from environment or secrets manager."""
    if settings.USE_SECRETS_MANAGER:
        try:
            # Create AWS service instance for this operation
            aws_service = AWSService()

            # Initialize AWS session
            if not await aws_service.initialize_clients():
                logger.warning(
                    "Failed to initialize AWS session, falling back to environment variables"
                )
                raise Exception("AWS session not available")

            credentials = await aws_service.get_database_credentials(
                settings.DB_SECRET_NAME
            )

            # Check if credentials were retrieved successfully
            if not credentials:
                logger.warning(
                    "No credentials retrieved from Secrets Manager, falling back to environment variables"
                )
                raise Exception("No credentials retrieved from Secrets Manager")

            # Extract database credentials from secret
            # Try multiple possible key names, fallback to environment variables
            db_host = (
                credentials.get("host")
                or credentials.get("db_host")
                or settings.ENV_DATABASE_HOST
            )
            db_port = (
                credentials.get("port")
                or credentials.get("db_port")
                or settings.ENV_DATABASE_PORT
            )
            db_user = (
                credentials.get("username")
                or credentials.get("user")
                or settings.ENV_DATABASE_USERNAME
            )
            db_password = credentials.get("password") or settings.ENV_DATABASE_PASSWORD
            db_name = (
                credentials.get("database")
                or credentials.get("dbname")
                or credentials.get("db")
                or settings.ENV_DATABASE_NAME
            )

            # Validate that we have all required credentials
            if not all([db_host, db_port, db_user, db_password, db_name]):
                missing = []
                if not db_host:
                    missing.append("host")
                if not db_port:
                    missing.append("port")
                if not db_user:
                    missing.append("user")
                if not db_password:
                    missing.append("password")
                if not db_name:
                    missing.append("database")
                raise ValueError(
                    f"Missing required database credentials: {', '.join(missing)}"
                )

            logger.info(
                f"Using database credentials from Secrets Manager: {db_host}:{db_port}"
            )
            logger.info(f"Database: {db_name}, User: {db_user}")

        except Exception as e:
            logger.warning(f"Failed to retrieve credentials from Secrets Manager: {e}")
            logger.info("Falling back to environment variables")
            db_host, db_port, db_user, db_password, db_name = (
                settings.ENV_DATABASE_HOST,
                settings.ENV_DATABASE_PORT,
                settings.ENV_DATABASE_USERNAME,
                settings.ENV_DATABASE_PASSWORD,
                settings.ENV_DATABASE_NAME,
            )
    else:
        db_host, db_port, db_user, db_password, db_name = (
            settings.ENV_DATABASE_HOST,
            settings.ENV_DATABASE_PORT,
            settings.ENV_DATABASE_USERNAME,
            settings.ENV_DATABASE_PASSWORD,
            settings.ENV_DATABASE_NAME,
        )
        logger.info(
            f"Using database credentials from environment variables: {db_host}:{db_port}"
        )
        logger.info(f"Database: {db_name}, User: {db_user}")

    # Build connection string
    connection_string = (
        f"mysql://{quote_plus(db_user)}:{quote_plus(db_password)}"
        f"@{db_host}:{db_port}/{db_name}?charset=utf8mb4"
    )

    return connection_string


async def init_db():
    """Initialize database connection."""
    try:
        # Build connection string
        connection_string = await _build_connection_string()

        # Update TORTOISE_ORM configuration
        TORTOISE_ORM["connections"]["default"] = connection_string

        logger.info("Initializing database connection...")

        await Tortoise.init(config=TORTOISE_ORM)

        # Test the connection by running a simple query
        await Tortoise.get_connection("default").execute_query("SELECT 1")

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


async def close_db():
    """Close database connection."""
    try:
        await Tortoise.close_connections()
        logger.info("Database connections closed")
    except Exception as e:
        logger.error(f"Failed to close database connections: {e}")


async def test_connection(max_retries: int = 5, delay: float = 2.0) -> bool:
    """Test database connection with retries."""
    for attempt in range(max_retries):
        try:
            logger.info(
                f"Testing database connection (attempt {attempt + 1}/{max_retries})"
            )

            # Build connection string for testing
            connection_string = await _build_connection_string()
            test_config = TORTOISE_ORM.copy()
            test_config["connections"]["default"] = connection_string

            await Tortoise.init(config=test_config)

            # Test with a simple query
            await Tortoise.get_connection("default").execute_query("SELECT 1")

            await Tortoise.close_connections()
            logger.info(f"Database connection successful on attempt {attempt + 1}")
            return True
        except Exception as e:
            logger.warning(f"Database connection attempt {attempt + 1} failed: {e}")
            if attempt < max_retries - 1:
                logger.info(f"Retrying in {delay} seconds...")
                await asyncio.sleep(delay)
                delay *= 1.5  # Exponential backoff
            else:
                logger.error(f"Database connection failed after {max_retries} attempts")
                return False
    return False