"""
Async database connection module.

Provides asynchronous database operations for concurrent processing.
Use this when processing multiple chains simultaneously.
"""

import asyncio
import aiomysql
import logging
from typing import Dict, Any, Optional, List
from pathlib import Path
from contextlib import asynccontextmanager

logger = logging.getLogger(__name__)


class AsyncDatabaseConfig:
    """Async database configuration management."""

    def __init__(self, config_file: str = "DB.txt"):
        """
        Initialize database configuration from file.

        Args:
            config_file: Path to database configuration file
        """
        self.config_file = Path(config_file)
        self.config = self._load_config()

    def _load_config(self) -> Dict[str, str]:
        """Load database configuration from text file."""
        try:
            with open(self.config_file, 'r') as f:
                lines = [line.strip() for line in f.readlines()]

            if len(lines) < 5:
                raise ValueError("Configuration file must contain at least 5 lines")

            # Parse JDBC URL to extract host and port
            url = lines[4]
            if 'mysql://' in url:
                parts = url.split('://')[1].split('/')[0]
                host = parts.split(':')[0] if ':' in parts else parts
                port = int(parts.split(':')[1]) if ':' in parts else 3306
            else:
                host = 'localhost'
                port = 3306

            config = {
                'database': lines[0],
                'user': lines[1],
                'password': lines[2],
                'host': host,
                'port': port
            }

            logger.info("Async database configuration loaded successfully")
            return config

        except FileNotFoundError:
            logger.error(f"Configuration file {self.config_file} not found")
            raise
        except Exception as e:
            logger.error(f"Error loading database configuration: {e}")
            raise


class AsyncDatabaseConnection:
    """Manages async MySQL database connections using connection pool."""

    def __init__(self, config: AsyncDatabaseConfig, pool_size: int = 10):
        """
        Initialize async database connection pool.

        Args:
            config: AsyncDatabaseConfig instance
            pool_size: Maximum number of connections in pool
        """
        self.config = config
        self.pool_size = pool_size
        self.pool: Optional[aiomysql.Pool] = None

    async def connect(self) -> None:
        """Create connection pool."""
        try:
            self.pool = await aiomysql.create_pool(
                host=self.config.config['host'],
                port=self.config.config['port'],
                user=self.config.config['user'],
                password=self.config.config['password'],
                db=self.config.config['database'],
                minsize=1,
                maxsize=self.pool_size,
                charset='utf8mb4',
                autocommit=False
            )
            logger.info(f"Async connection pool created (max size: {self.pool_size})")

        except Exception as e:
            logger.error(f"Error creating connection pool: {e}")
            raise

    async def close(self) -> None:
        """Close connection pool."""
        if self.pool:
            self.pool.close()
            await self.pool.wait_closed()
            logger.info("Async connection pool closed")

    async def execute_query(
        self,
        query: str,
        params: Optional[tuple] = None
    ) -> List[Dict[str, Any]]:
        """
        Execute a SELECT query asynchronously.

        Args:
            query: SQL query string
            params: Optional query parameters

        Returns:
            List of dictionaries with query results
        """
        if not self.pool:
            raise RuntimeError("Connection pool not initialized")

        async with self.pool.acquire() as conn:
            async with conn.cursor(aiomysql.DictCursor) as cursor:
                await cursor.execute(query, params or ())
                results = await cursor.fetchall()
                return results

    async def execute_update(
        self,
        query: str,
        params: Optional[tuple] = None
    ) -> int:
        """
        Execute an INSERT, UPDATE or DELETE query asynchronously.

        Args:
            query: SQL query string
            params: Optional query parameters

        Returns:
            Number of affected rows
        """
        if not self.pool:
            raise RuntimeError("Connection pool not initialized")

        async with self.pool.acquire() as conn:
            try:
                async with conn.cursor() as cursor:
                    await cursor.execute(query, params or ())
                    await conn.commit()
                    return cursor.rowcount
            except Exception as e:
                await conn.rollback()
                logger.error(f"Error executing update: {e}")
                raise

    async def execute_many(
        self,
        query: str,
        data: List[tuple]
    ) -> int:
        """
        Execute multiple INSERT/UPDATE queries efficiently.

        Args:
            query: SQL query string with placeholders
            data: List of tuples with parameter values

        Returns:
            Number of affected rows
        """
        if not self.pool:
            raise RuntimeError("Connection pool not initialized")

        async with self.pool.acquire() as conn:
            try:
                async with conn.cursor() as cursor:
                    await cursor.executemany(query, data)
                    await conn.commit()
                    return cursor.rowcount
            except Exception as e:
                await conn.rollback()
                logger.error(f"Error executing batch update: {e}")
                raise

    async def __aenter__(self):
        """Async context manager entry."""
        await self.connect()
        return self

    async def __aexit__(self, exc_type, exc_val, exc_tb):
        """Async context manager exit."""
        await self.close()


@asynccontextmanager
async def get_async_connection(config: AsyncDatabaseConfig):
    """
    Context manager for async database connection.

    Usage:
        async with get_async_connection(config) as conn:
            results = await conn.execute_query("SELECT ...")
    """
    conn = AsyncDatabaseConnection(config)
    try:
        await conn.connect()
        yield conn
    finally:
        await conn.close()


async def process_multiple_chains_async(chains_config: List[Dict[str, str]]) -> List[int]:
    """
    Process multiple sensor chains concurrently.

    This is where async shines - processing multiple independent chains
    in parallel instead of sequentially.

    Args:
        chains_config: List of dicts with 'control_unit_id' and 'chain'

    Returns:
        List of return codes (0=success, 1=error) for each chain

    Example:
        chains = [
            {'control_unit_id': 'CU001', 'chain': 'A', 'type': 'RSN'},
            {'control_unit_id': 'CU002', 'chain': 'B', 'type': 'RSN'},
            {'control_unit_id': 'CU003', 'chain': 'C', 'type': 'Tilt'},
        ]
        results = await process_multiple_chains_async(chains)
    """
    from ..rsn.main_async import process_rsn_chain_async
    from ..tilt.main_async import process_tilt_chain_async

    tasks = []

    for chain_cfg in chains_config:
        control_unit_id = chain_cfg['control_unit_id']
        chain = chain_cfg['chain']
        chain_type = chain_cfg.get('type', 'RSN')

        if chain_type == 'RSN':
            task = process_rsn_chain_async(control_unit_id, chain)
        elif chain_type == 'Tilt':
            task = process_tilt_chain_async(control_unit_id, chain)
        else:
            logger.warning(f"Unknown chain type: {chain_type}")
            continue

        tasks.append(task)

    # Run all chains concurrently
    logger.info(f"Processing {len(tasks)} chains concurrently")
    results = await asyncio.gather(*tasks, return_exceptions=True)

    # Process results
    return_codes = []
    for i, result in enumerate(results):
        if isinstance(result, Exception):
            logger.error(f"Chain {i} failed: {result}")
            return_codes.append(1)
        else:
            return_codes.append(result)

    return return_codes


# Example usage
async def main_example():
    """Example of async database operations."""
    config = AsyncDatabaseConfig()

    # Single connection
    async with get_async_connection(config) as conn:
        # Execute query
        results = await conn.execute_query(
            "SELECT * FROM control_units WHERE active = %s",
            (1,)
        )
        print(f"Found {len(results)} active units")

        # Execute update
        rows = await conn.execute_update(
            "UPDATE control_units SET last_check = NOW() WHERE unitID = %s",
            (1,)
        )
        print(f"Updated {rows} rows")

    # Process multiple chains concurrently
    chains = [
        {'control_unit_id': 'CU001', 'chain': 'A', 'type': 'RSN'},
        {'control_unit_id': 'CU002', 'chain': 'B', 'type': 'RSN'},
        {'control_unit_id': 'CU003', 'chain': 'C', 'type': 'Tilt'},
    ]

    results = await process_multiple_chains_async(chains)
    print(f"Processed {len(chains)} chains with results: {results}")


if __name__ == "__main__":
    # Run example
    asyncio.run(main_example())
