"""
Oracle database wrapper for rateloader.

Handles Oracle connections and query execution as specified in PRD §2 and Phase 2.
Uses cx_Oracle library compatible with Python 3.8 32-bit and Oracle 11g.
"""

import os
import cx_Oracle
from typing import Optional, Generator, List, Any
from core.config import get_config
from core.logger import get_oracle_logger


def set_nls_lang(nls_lang: str) -> None:
    """
    Set NLS_LANG environment variable for Oracle connections.

    This must be called before Oracle connections as specified in the PRD.

    Args:
        nls_lang: NLS_LANG value to set (e.g., "AMERICAN_AMERICA.CL8MSWIN1251")
    """
    os.environ["NLS_LANG"] = nls_lang
    logger = get_oracle_logger()
    logger.info(f"NLS_LANG set to: {nls_lang}")


def connect(connect_string: Optional[str] = None) -> Optional[cx_Oracle.Connection]:
    """
    Establish Oracle database connection.

    Args:
        connect_string: Oracle connection string (e.g., "user/pass@dsn")
                       If None, uses oracleconnectstring from settings.ini

    Returns:
        Oracle connection object or None if connection fails
    """
    logger = get_oracle_logger()

    try:
        # Get connection string from config if not provided
        if connect_string is None:
            config = get_config()
            connect_string = config.get("oracleconnectstring")
            if not connect_string:
                logger.error("Oracle connection string not found in settings.ini")
                return None

        # Ensure NLS_LANG is set from config
        config = get_config()
        nls_lang = config.get("nls_lang")
        if nls_lang:
            set_nls_lang(nls_lang)

        # Establish connection
        logger.info(f"Connecting to Oracle with connection string: {connect_string}")
        connection = cx_Oracle.connect(connect_string)
        logger.info("Oracle connection established successfully")
        
        # Verify hotel connection and log hotel name
        try:
            hotel_name = verify_hotel_connection(connection)
            if hotel_name:
                logger.info(f"Connected to hotel: {hotel_name}")
            else:
                logger.warning("Hotel verification failed, but connection remains active")
        except Exception as e:
            logger.error(f"Hotel verification failed with exception: {str(e)}")
            logger.warning("Continuing with connection despite hotel verification failure")
        
        return connection

    except cx_Oracle.DatabaseError as e:
        (error,) = e.args
        logger.error(f"Oracle connection failed: {error.message}")
        return None
    except Exception as e:
        logger.error(f"Unexpected error during Oracle connection: {str(e)}")
        return None


def verify_hotel_connection(connection: cx_Oracle.Connection) -> Optional[str]:
    """
    Verify database connection and return hotel name.
    
    This function must be called after establishing Oracle connection
    to verify the database is accessible and retrieve the hotel name.
    
    Args:
        connection: Active Oracle connection object
        
    Returns:
        Hotel name as string, or None if verification fails
    """
    logger = get_oracle_logger()
    
    try:
        hotel_query = "select REGEXP_REPLACE(wuss_value, ',.*') hotelname from wuss where wuss_name = 'Hotelid'"
        logger.debug("Verifying hotel connection with query")
        
        cursor = connection.cursor()
        cursor.execute(hotel_query)
        
        result = cursor.fetchone()
        cursor.close()
        
        if result and result[0]:
            hotel_name = str(result[0])
            logger.info(f"Hotel verification successful: {hotel_name}")
            return hotel_name
        else:
            logger.warning("Hotel verification query returned no results")
            return None
            
    except cx_Oracle.DatabaseError as e:
        (error,) = e.args
        # Handle both string and object error messages
        error_message = error.message if hasattr(error, 'message') else str(error)
        logger.error(f"Hotel verification query failed: {error_message}")
        return None
    except Exception as e:
        logger.error(f"Unexpected error during hotel verification: {str(e)}")
        return None


def execute_scalar(
    sql: str, connection: Optional[cx_Oracle.Connection] = None
) -> Optional[Any]:
    """
    Execute SQL query and return the first value of the first row.

    Args:
        sql: SQL query to execute
        connection: Oracle connection object. If None, creates a new connection.

    Returns:
        First value of first row, or None if query fails or returns no results
    """
    logger = get_oracle_logger()
    conn = None
    cursor = None

    try:
        # Use provided connection or create new one
        if connection is None:
            conn = connect()
            if conn is None:
                return None
        else:
            conn = connection

        cursor = conn.cursor()
        logger.debug(f"Executing scalar query: {sql}")
        cursor.execute(sql)

        # Fetch first row and return first column
        row = cursor.fetchone()
        if row is not None:
            result = row[0]
            logger.debug(f"Scalar query result: {result}")
            return result
        else:
            logger.debug("Scalar query returned no results")
            return None

    except cx_Oracle.DatabaseError as e:
        (error,) = e.args
        logger.error(f"Oracle scalar query failed: {error.message}")
        logger.error(f"SQL: {sql}")
        return None
    except Exception as e:
        logger.error(f"Unexpected error during scalar query: {str(e)}")
        logger.error(f"SQL: {sql}")
        return None
    finally:
        # Clean up resources
        if cursor:
            cursor.close()
        if conn is not None and connection is None:
            conn.close()


def stream_query(
    sql: str, arraysize: int = 1000, connection: Optional[cx_Oracle.Connection] = None
) -> Generator[List[Any], None, None]:
    """
    Execute SQL query and yield row batches as a generator.

    Args:
        sql: SQL query to execute
        arraysize: Number of rows to fetch per batch (default: 1000)
        connection: Oracle connection object. If None, creates a new connection.

    Yields:
        List of rows (each row is a tuple) for each batch
    """
    logger = get_oracle_logger()
    conn = None
    cursor = None

    try:
        # Use provided connection or create new one
        if connection is None:
            conn = connect()
            if conn is None:
                return
        else:
            conn = connection

        cursor = conn.cursor()
        cursor.arraysize = arraysize
        logger.debug(f"Executing stream query with arraysize {arraysize}: {sql}")
        cursor.execute(sql)

        # Fetch and yield batches
        while True:
            rows = cursor.fetchmany()
            if not rows:
                break
            logger.debug(f"Yielding batch of {len(rows)} rows")
            yield rows

    except cx_Oracle.DatabaseError as e:
        (error,) = e.args
        logger.error(f"Oracle stream query failed: {error.message}")
        logger.error(f"SQL: {sql}")
    except Exception as e:
        logger.error(f"Unexpected error during stream query: {str(e)}")
        logger.error(f"SQL: {sql}")
    finally:
        # Clean up resources
        if cursor:
            cursor.close()
        if conn is not None and connection is None:
            conn.close()


class OracleWrapper:
    """
    Oracle database wrapper class for easier connection management.

    This class maintains a connection and provides convenient methods
    for database operations with automatic error handling and logging.
    """

    def __init__(self, connect_string: Optional[str] = None):
        """
        Initialize Oracle wrapper.

        Args:
            connect_string: Oracle connection string. If None, uses config.
        """
        self.connect_string = connect_string
        self.connection = None
        self.logger = get_oracle_logger()

    def connect(self) -> bool:
        """
        Establish database connection.

        Returns:
            True if connection successful, False otherwise
        """
        self.connection = connect(self.connect_string)
        return self.connection is not None

    def disconnect(self) -> None:
        """Close database connection if open."""
        if self.connection:
            try:
                self.connection.close()
                self.connection = None
                self.logger.info("Oracle connection closed")
            except Exception as e:
                self.logger.error(f"Error closing Oracle connection: {str(e)}")

    def is_connected(self) -> bool:
        """
        Check if connection is active.

        Returns:
            True if connected, False otherwise
        """
        if self.connection is None:
            return False

        try:
            # Simple ping to check connection
            cursor = self.connection.cursor()
            cursor.execute("SELECT 1 FROM DUAL")
            cursor.close()
            return True
        except:
            return False

    def execute_scalar(self, sql: str) -> Optional[Any]:
        """
        Execute SQL query and return first value of first row.

        Args:
            sql: SQL query to execute

        Returns:
            First value of first row, or None if query fails
        """
        if not self.is_connected() and not self.connect():
            return None

        return execute_scalar(sql, self.connection)

    def stream_query(
        self, sql: str, arraysize: int = 1000
    ) -> Generator[List[Any], None, None]:
        """
        Execute SQL query and yield row batches.

        Args:
            sql: SQL query to execute
            arraysize: Number of rows per batch

        Yields:
            List of rows for each batch
        """
        if not self.is_connected() and not self.connect():
            return

        yield from stream_query(sql, arraysize, self.connection)

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

    def __exit__(self, exc_type, exc_val, exc_tb):
        """Context manager exit."""
        self.disconnect()
    
    def get_hotel_name(self) -> Optional[str]:
        """
        Get hotel name from connected database.
        
        Returns:
            Hotel name as string, or None if not connected or query fails
        """
        if not self.is_connected():
            return None
            
        return verify_hotel_connection(self.connection)


# Convenience function for creating Oracle wrapper with config
def create_oracle_wrapper() -> OracleWrapper:
    """
    Create Oracle wrapper using configuration from settings.ini.

    Returns:
        OracleWrapper instance configured from settings
    """
    config = get_config()
    connect_string = config.get("oracleconnectstring")
    return OracleWrapper(connect_string)
