"""
Database connection and operations module.

Converts MATLAB database_definition.m and related database functions.
Uses python-dotenv for configuration management.
"""

import mysql.connector
from typing import Dict, Any, Optional, List
import logging
import os
from pathlib import Path
from dotenv import load_dotenv

logger = logging.getLogger(__name__)


class DatabaseConfig:
    """Database configuration management using .env file."""

    def __init__(self, env_file: str = ".env"):
        """
        Initialize database configuration from .env file.

        Args:
            env_file: Path to .env file (default: .env in project root)
        """
        self.env_file = Path(env_file)
        self.config = self._load_config()

    def _load_config(self) -> Dict[str, str]:
        """
        Load database configuration from .env file.

        Returns:
            Dictionary with database configuration
        """
        try:
            # Load environment variables from .env file
            if self.env_file.exists():
                load_dotenv(dotenv_path=self.env_file)
                logger.info(f"Loaded configuration from {self.env_file}")
            else:
                logger.warning(f".env file not found at {self.env_file}, using environment variables")
                load_dotenv()  # Try to load from default locations

            # Read configuration from environment variables
            config = {
                'host': os.getenv('DB_HOST', 'localhost'),
                'port': int(os.getenv('DB_PORT', '3306')),
                'database': os.getenv('DB_NAME'),
                'user': os.getenv('DB_USER'),
                'password': os.getenv('DB_PASSWORD'),
                'charset': os.getenv('DB_CHARSET', 'utf8mb4'),
                'timezone': os.getenv('DB_TIMEZONE', 'Europe/Rome')
            }

            # Validate required fields
            required_fields = ['database', 'user', 'password']
            missing_fields = [field for field in required_fields if not config[field]]
            if missing_fields:
                raise ValueError(f"Missing required database configuration: {', '.join(missing_fields)}")

            logger.info(f"Database configuration loaded successfully for {config['database']}")
            return config

        except Exception as e:
            logger.error(f"Error loading database configuration: {e}")
            raise


class DatabaseConnection:
    """Manages MySQL database connections."""

    def __init__(self, config: DatabaseConfig):
        """
        Initialize database connection.

        Args:
            config: DatabaseConfig instance
        """
        self.config = config
        self.connection: Optional[mysql.connector.MySQLConnection] = None
        self.cursor: Optional[mysql.connector.cursor.MySQLCursor] = None

    def connect(self) -> None:
        """Establish database connection."""
        try:
            self.connection = mysql.connector.connect(
                host=self.config.config['host'],
                port=self.config.config['port'],
                user=self.config.config['user'],
                password=self.config.config['password'],
                database=self.config.config['database'],
                charset=self.config.config['charset']
            )
            self.cursor = self.connection.cursor(dictionary=True)
            logger.info(f"Connected to database {self.config.config['database']} at {self.config.config['host']}")

        except mysql.connector.Error as e:
            logger.error(f"Error connecting to database: {e}")
            raise

    def close(self) -> None:
        """Close database connection."""
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()
            logger.info("Database connection closed")

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

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

        Returns:
            List of dictionaries with query results
        """
        try:
            if not self.cursor:
                raise RuntimeError("Database not connected")

            self.cursor.execute(query, params or ())
            results = self.cursor.fetchall()
            return results

        except mysql.connector.Error as e:
            logger.error(f"Error executing query: {e}")
            raise

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

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

        Returns:
            Number of affected rows
        """
        try:
            if not self.cursor or not self.connection:
                raise RuntimeError("Database not connected")

            self.cursor.execute(query, params or ())
            self.connection.commit()
            return self.cursor.rowcount

        except mysql.connector.Error as e:
            logger.error(f"Error executing update: {e}")
            if self.connection:
                self.connection.rollback()
            raise

    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
        """
        try:
            if not self.cursor or not self.connection:
                raise RuntimeError("Database not connected")

            self.cursor.executemany(query, data)
            self.connection.commit()
            return self.cursor.rowcount

        except mysql.connector.Error as e:
            logger.error(f"Error executing batch update: {e}")
            if self.connection:
                self.connection.rollback()
            raise

    def __enter__(self):
        """Context manager entry."""
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """Context manager exit."""
        self.close()


def get_unit_id(control_unit_id: str, conn: DatabaseConnection) -> int:
    """
    Get unit ID from control unit identifier.

    Converts MATLAB IDunit.m function.

    Args:
        control_unit_id: Control unit identifier string
        conn: Database connection

    Returns:
        Unit ID integer
    """
    query = """
        SELECT unitID
        FROM control_units
        WHERE controlUnitCode = %s
    """
    results = conn.execute_query(query, (control_unit_id,))

    if not results:
        raise ValueError(f"Control unit {control_unit_id} not found")

    unit_id = results[0]['unitID']
    logger.info(f"Retrieved unit ID {unit_id} for control unit {control_unit_id}")
    return unit_id


def delete_database_records(conn: DatabaseConnection, table: str,
                           control_unit_id: str, chain: str) -> None:
    """
    Delete records from database for specific control unit and chain.

    Converts MATLAB cancellaDB.m function.

    Args:
        conn: Database connection
        table: Table name
        control_unit_id: Control unit identifier
        chain: Chain identifier
    """
    query = f"""
        DELETE FROM {table}
        WHERE IDcentralina = %s AND DTcatena = %s
    """
    rows_affected = conn.execute_update(query, (control_unit_id, chain))
    logger.info(f"Deleted {rows_affected} records from {table}")


def get_schema(id_tool: int, conn: DatabaseConnection) -> List[int]:
    """
    Reconstruct chain nodes schema.

    Converts MATLAB schema.m function.

    Args:
        id_tool: Tool identifier
        conn: Database connection

    Returns:
        List of node IDs in chain order
    """
    query = """
        SELECT nodeID
        FROM chain_schema
        WHERE idTool = %s
        ORDER BY nodeOrder
    """
    results = conn.execute_query(query, (id_tool,))
    chain = [row['nodeID'] for row in results]
    logger.info(f"Retrieved chain schema with {len(chain)} nodes")
    return chain
