"""
Database transaction management with row-level locking support.

Provides context manager for safe database transactions with:
- Automatic commit on success
- Automatic rollback on error
- Row-level locking (FOR UPDATE)
- Proper connection management
- Nested transaction handling

Usage:
    from database import transaction

    # Simple transaction
    with transaction() as (conn, cursor):
        cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
        cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
        # Auto-commits on success, auto-rolls back on exception

    # Transaction with row-level locking (use FOR UPDATE in queries)
    with transaction() as (conn, cursor):
        cursor.execute("SELECT balance FROM accounts WHERE id = 1 FOR UPDATE")
        balance = cursor.fetchone()[0]
        if balance >= 100:
            cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
"""

from contextlib import contextmanager
from typing import Tuple, Optional
import logging
import mysql.connector
from mysql.connector import Error

# Import the connection pool getter
import sys
from pathlib import Path
sys.path.insert(0, str(Path(__file__).parent.parent))

try:
    from database.db_operations import get_database_connection
except ImportError:
    # Fallback if database module not available
    def get_database_connection():
        """Fallback connection getter"""
        from config import config
        return mysql.connector.connect(
            host=config.DB_HOST,
            port=config.DB_PORT,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            database=config.DB_NAME
        )


@contextmanager
def transaction(conn: Optional[mysql.connector.MySQLConnection] = None,
                isolation_level: Optional[str] = None):
    """
    Context manager for database transactions with automatic commit/rollback.

    Args:
        conn: Optional existing connection. If None, gets from pool.
        isolation_level: Optional transaction isolation level
                       ('READ UNCOMMITTED', 'READ COMMITTED',
                        'REPEATABLE READ', 'SERIALIZABLE')

    Yields:
        Tuple of (connection, cursor)

    Raises:
        Exception: Any exception from transaction operations

    Note:
        For row-level locking, use FOR UPDATE in your SELECT queries:
        cursor.execute("SELECT * FROM table WHERE id = %s FOR UPDATE", (id,))

    Example:
        # Simple transaction
        with transaction() as (conn, cursor):
            cursor.execute("INSERT INTO users (name) VALUES (%s)", ('Alice',))
            cursor.execute("INSERT INTO profiles (user_id) VALUES (LAST_INSERT_ID())")

        # Transaction with row-level locking (use FOR UPDATE in queries)
        with transaction() as (conn, cursor):
            cursor.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (1,))
            balance = cursor.fetchone()[0]
            if balance >= 100:
                cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = %s", (1,))

        # Transaction with custom isolation level
        with transaction(isolation_level='SERIALIZABLE') as (conn, cursor):
            cursor.execute("SELECT COUNT(*) FROM orders")
            count = cursor.fetchone()[0]
            cursor.execute("INSERT INTO orders (order_num) VALUES (%s)", (count + 1,))
    """
    # Track if we created the connection (so we know whether to close it)
    created_connection = conn is None
    cursor = None

    try:
        # Get connection if not provided
        if created_connection:
            conn = get_database_connection()

        # Ensure autocommit is disabled for manual transaction control
        conn.autocommit = False

        # Create cursor
        cursor = conn.cursor()

        # Set isolation level if specified
        if isolation_level:
            valid_levels = ['READ UNCOMMITTED', 'READ COMMITTED',
                          'REPEATABLE READ', 'SERIALIZABLE']
            if isolation_level.upper() not in valid_levels:
                raise ValueError(f"Invalid isolation level: {isolation_level}")
            cursor.execute(f"SET TRANSACTION ISOLATION LEVEL {isolation_level.upper()}")

        # Start transaction explicitly
        conn.start_transaction()

        # Yield connection and cursor to caller
        yield conn, cursor

        # If we get here without exception, commit
        conn.commit()

    except Exception as e:
        # Rollback on any error
        if conn:
            try:
                conn.rollback()
            except Exception as rollback_error:
                logging.error(f"Error during rollback: {rollback_error}")
        # Re-raise original exception
        raise

    finally:
        # Close cursor
        if cursor:
            try:
                cursor.close()
            except Exception as e:
                logging.error(f"Error closing cursor: {e}")

        # Close connection only if we created it
        # (otherwise caller is responsible for closing)
        if created_connection and conn:
            try:
                conn.close()  # Returns connection to pool
            except Exception as e:
                logging.error(f"Error closing connection: {e}")


def execute_in_transaction(func, conn: Optional[mysql.connector.MySQLConnection] = None):
    """
    Execute a function within a transaction context.

    Helper function for wrapping operations in a transaction.

    Args:
        func: Function that takes (conn, cursor) as arguments
        conn: Optional existing connection

    Returns:
        Return value of func

    Example:
        def transfer_money(conn, cursor):
            cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
            cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
            return True

        result = execute_in_transaction(transfer_money)
    """
    with transaction(conn=conn) as (conn, cursor):
        return func(conn, cursor)


# Example usage patterns
def example_atomic_transfer(from_account_id: int, to_account_id: int, amount: float):
    """
    Example: Atomic money transfer between accounts.

    This example demonstrates:
    - Row-level locking with FOR UPDATE
    - Balance validation before update
    - Atomic operations across multiple rows
    """
    with transaction() as (conn, cursor):
        # Lock and fetch source account balance
        cursor.execute(
            "SELECT balance FROM accounts WHERE id = %s FOR UPDATE",
            (from_account_id,)
        )
        from_balance = cursor.fetchone()

        if not from_balance or from_balance[0] < amount:
            raise ValueError("Insufficient funds")

        # Lock and fetch destination account
        cursor.execute(
            "SELECT id FROM accounts WHERE id = %s FOR UPDATE",
            (to_account_id,)
        )
        if not cursor.fetchone():
            raise ValueError("Destination account not found")

        # Perform transfer
        cursor.execute(
            "UPDATE accounts SET balance = balance - %s WHERE id = %s",
            (amount, from_account_id)
        )
        cursor.execute(
            "UPDATE accounts SET balance = balance + %s WHERE id = %s",
            (amount, to_account_id)
        )

        # Log transaction
        cursor.execute(
            """INSERT INTO transaction_log (from_account, to_account, amount, timestamp)
               VALUES (%s, %s, %s, NOW())""",
            (from_account_id, to_account_id, amount)
        )


def example_idempotent_operation(operation_id: str, user_id: int):
    """
    Example: Idempotent operation using database lock.

    Prevents duplicate execution of operations by checking
    a processed operations log with row-level locking.
    """
    with transaction() as (conn, cursor):
        # Check if already processed (with lock to prevent race condition)
        cursor.execute(
            "SELECT id FROM processed_operations WHERE operation_id = %s FOR UPDATE",
            (operation_id,)
        )

        if cursor.fetchone():
            # Already processed, skip
            return False

        # Process operation
        cursor.execute(
            "INSERT INTO user_credits (user_id, amount) VALUES (%s, 100) "
            "ON DUPLICATE KEY UPDATE amount = amount + 100",
            (user_id,)
        )

        # Mark as processed
        cursor.execute(
            "INSERT INTO processed_operations (operation_id, processed_at) VALUES (%s, NOW())",
            (operation_id,)
        )

        return True