#!/usr/bin/env python3
"""
Oracle Database Connection Pool

This module manages the connection pool for Oracle database connections.
"""

import os
import logging
from typing import Optional

import cx_Oracle


class OraclePool:
    """
    Manages Oracle database connection pool for efficient connection reuse.

    This class provides connection pooling functionality using cx_Oracle SessionPool
    with proper environment variable configuration and error handling.
    """

    def __init__(self):
        """Initialize the connection pool with environment variables."""
        self.logger = logging.getLogger(__name__)
        self.pool: Optional[cx_Oracle.SessionPool] = None
        self._initialize_pool()

    def _initialize_pool(self):
        """Initialize the Oracle connection pool."""
        try:
            # Get connection parameters from environment
            user = os.getenv("ORACLE_USER", "v8live")
            password = os.getenv("ORACLE_PASS", "live")
            dsn = os.getenv("ORACLE_DSN", "10.0.0.18:1521/v8")

            # Get pool configuration
            min_sessions = int(os.getenv("ORACLE_POOL_MIN", "1"))
            max_sessions = int(os.getenv("ORACLE_POOL_MAX", "5"))
            increment = int(os.getenv("ORACLE_POOL_INCREMENT", "1"))

            # Get timeout settings
            timeout = int(os.getenv("MCP_TIMEOUT_S", "30"))

            self.logger.info(f"Initializing Oracle connection pool for DSN: {dsn}")

            # Create the connection pool
            self.pool = cx_Oracle.SessionPool(
                user=user,
                password=password,
                dsn=dsn,
                min=min_sessions,
                max=max_sessions,
                increment=increment,
                threaded=True,
                getmode=cx_Oracle.SPOOL_ATTRVAL_NOWAIT,
            )

            # Set timeout on the pool
            self.pool.timeout = timeout

            self.logger.info(
                f"Connection pool initialized successfully (min: {min_sessions}, max: {max_sessions})"
            )

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

    def get_connection(self) -> cx_Oracle.Connection:
        """
        Get a connection from the pool.

        Returns:
            cx_Oracle.Connection: Database connection from the pool
        """
        if not self.pool:
            raise RuntimeError("Connection pool not initialized")

        try:
            # Get connection from pool
            conn = self.pool.acquire()

            # Set timeout on the connection
            timeout = int(os.getenv("MCP_TIMEOUT_S", "30"))
            conn.callTimeout = timeout * 1000  # Convert to milliseconds

            return conn

        except Exception as e:
            self.logger.error(f"Failed to get connection from pool: {e}")
            raise

    def release_connection(self, conn: cx_Oracle.Connection):
        """
        Release a connection back to the pool.

        Args:
            conn: The connection to release
        """
        try:
            if self.pool and conn:
                self.pool.release(conn)
                self.logger.debug("Connection released back to pool")
        except Exception as e:
            self.logger.error(f"Failed to release connection: {e}")

    def close_pool(self):
        """Close the connection pool and cleanup resources.

        Note: avoid calling logging APIs here because the logging subsystem
        may be shut down during interpreter exit which can cause "I/O on
        closed file" errors. Keep this method silent on failure.
        """
        try:
            if self.pool:
                try:
                    self.pool.close()
                except Exception:
                    # Ignore errors when closing low-level pool
                    pass
                self.pool = None
        except Exception:
            # Suppress all errors during shutdown to avoid logging during interpreter teardown
            pass

    def get_pool_status(self) -> dict:
        """
        Get current pool status information.

        Returns:
            dict: Pool status information
        """
        if not self.pool:
            return {"status": "not_initialized"}

        try:
            return {
                "status": "active",
                "min_sessions": self.pool.min,
                "max_sessions": self.pool.max,
                "increment": self.pool.increment,
                "opened": self.pool.opened,
                "busy": self.pool.busy,
                "timeout": self.pool.timeout,
            }
        except Exception as e:
            self.logger.error(f"Error getting pool status: {e}")
            return {"status": "error", "error": str(e)}

    def ping(self) -> dict:
        """
        Test database connectivity with a lightweight set of checks and return Oracle
        version, connection settings, an optional HotelId lookup and a usage hint.

        Returns:
            dict: Ping result with status, checks, oracle_version, connection info,
                  elapsed_ms, and optional usage instructions.
        """
        import time

        conn = None
        start_time = time.time()

        try:
            conn = self.get_connection()
            cursor = conn.cursor()

            # 1) Lightweight connectivity check
            try:
                cursor.execute("SELECT 1 FROM DUAL")
                result = cursor.fetchone()
                connect_ok = bool(result and result[0] == 1)
            except Exception as e:
                self.logger.warning(f"Connectivity check failed: {e}")
                connect_ok = False

            # 2) Try to read known configuration value from wuss table (may not exist)
            hotelid = None
            hotelid_status = "unknown"
            try:
                cursor.execute(
                    "SELECT wuss_value FROM wuss WHERE wuss_name = 'Hotelid'"
                )
                row = cursor.fetchone()
                if row:
                    hotelid = row[0]
                    hotelid_status = "found"
                else:
                    hotelid_status = "not_found"
            except cx_Oracle.DatabaseError as e:
                # Table may not exist or other DB error — capture reason but don't fail ping
                (error_obj,) = e.args
                msg = getattr(error_obj, "message", str(e))
                self.logger.debug(f"HotelId lookup failed: {msg}")
                # Distinguish missing table vs other error by message when possible
                if "ORA-00942" in msg or "table or view does not exist" in msg.lower():
                    hotelid_status = "table_missing"
                else:
                    hotelid_status = "error"

            # 3) Oracle version (best-effort)
            oracle_version = "Unknown"
            try:
                cursor.execute("SELECT banner FROM v$version WHERE ROWNUM = 1")
                version_result = cursor.fetchone()
                oracle_version = version_result[0] if version_result else "Unknown"
            except Exception:
                # Non-fatal
                pass

            # Load a short usage instruction if available (non-fatal)
            usage_instructions = None
            try:
                with open("server/oracle/usage_description.md", "r", encoding="utf-8") as f:
                    usage_instructions = f.read()
            except Exception as e:
                usage_instructions = None
                self.logger.debug(f"Failed to load usage instructions: {e}")

            elapsed_ms = round((time.time() - start_time) * 1000, 2)

            # Determine overall status
            status = "success" if connect_ok else "error"
            message = "Database ping successful" if connect_ok else "Database ping failed"

            # Public-facing ping payload per new MCP contract:
            # Keep internal diagnostics available under "details" for debugging,
            # but present the canonical contract at top-level.
            agent_version = os.getenv("AGENT_VERSION", "1.0.0")
            capabilities = ["sql.select", "sql.execute", "db.ping"]

            return {
                "connected": connect_ok,
                "db_version": oracle_version,
                "agent_version": agent_version,
                "capabilities": capabilities,
                "settings": {
                    "thick_mode": False,
                    "encoding": "cp1251",
                },
                "details": {
                    "dsn": os.getenv("ORACLE_DSN", "10.0.0.18:1521/v8"),
                    "elapsed_ms": elapsed_ms,
                    "checks": {
                        "connect_ok": connect_ok,
                        "hotelid": hotelid,
                        "hotelid_status": hotelid_status,
                    },
                    "usage_instructions": usage_instructions,
                },
            }

        except Exception as e:
            elapsed_ms = round((time.time() - start_time) * 1000, 2)
            self.logger.error(f"Database ping failed: {e}")
            agent_version = os.getenv("AGENT_VERSION", "1.0.0")
            capabilities = ["sql.select", "sql.execute", "db.ping"]
            return {
                "connected": False,
                "db_version": "Unknown",
                "agent_version": agent_version,
                "capabilities": capabilities,
                "settings": {
                    "thick_mode": False,
                    "encoding": "cp1251",
                },
                "details": {
                    "error": str(e),
                    "elapsed_ms": elapsed_ms,
                },
            }
        finally:
            if conn:
                try:
                    cursor.close()
                except Exception:
                    pass
                self.release_connection(conn)

    def session_info(self) -> dict:
        """
        Get detailed session and connection pool information.

        Returns:
            dict: Session information including pool status and connection details
        """
        try:
            pool_status = self.get_pool_status()

            # Add additional session information
            session_info = {
                **pool_status,
                "environment": {
                    "oracle_user": os.getenv("ORACLE_USER", "v8live"),
                    "oracle_dsn": os.getenv("ORACLE_DSN", "10.0.0.18:1521/v8"),
                    "timeout_s": os.getenv("MCP_TIMEOUT_S", "30"),
                    "max_rows": os.getenv("MCP_MAX_ROWS", "500"),
                    "max_text_size": os.getenv("MCP_MAX_TEXT_SIZE", "5000"),
                },
                "pool_config": {
                    "min_sessions": os.getenv("ORACLE_POOL_MIN", "1"),
                    "max_sessions": os.getenv("ORACLE_POOL_MAX", "5"),
                    "increment": os.getenv("ORACLE_POOL_INCREMENT", "1"),
                },
            }

            return session_info

        except Exception as e:
            self.logger.error(f"Error getting session info: {e}")
            return {
                "status": "error",
                "message": f"Failed to get session info: {str(e)}",
            }

    async def test_connection(self) -> dict:
        """
        Test database connectivity (legacy method for backward compatibility).

        Returns:
            dict: Test result with connection information
        """
        return self.ping()

    def __del__(self):
        """Cleanup when the object is destroyed."""
        try:
            # Avoid raising during interpreter shutdown; suppress any errors.
            self.close_pool()
        except Exception:
            pass
