"""
Examples of migrating existing BaoLife database code to use transactions.

This file shows how to refactor existing database operations to use
the transaction context manager for better safety and reliability.
"""

import pickle
import json
from database.transactions import transaction


# ==============================================================================
# Example 1: Migrating saveGame to use transactions
# ==============================================================================

def saveGame_old(player):
    """
    OLD VERSION: Manual transaction management (current implementation)
    Issues:
    - No explicit transaction
    - Connection not closed (relies on global connection)
    - No rollback on error
    """
    from database import get_database_connection

    mydb = get_database_connection()
    mycursor = mydb.cursor()
    serialized_player = pickle.dumps(player)

    mycursor.execute(
        "UPDATE lifesim_savegames SET firstname = %s, lastname = %s, ageDays = %s, "
        "ageYears = %s, json = %s, pickle_data = %s WHERE id = %s",
        (player.c.firstname, player.c.lastname, player.c.ageDays, player.c.ageYears,
         json.dumps(player.__dict__, default=lambda o: o.__dict__),
         serialized_player, player.id)
    )

    print(f"game saved! at {player.hourOfDay}:{player.minuteOfHour} on {player.date}")
    mydb.commit()


def saveGame_new(player):
    """
    NEW VERSION: Using transaction context manager
    Benefits:
    - Automatic commit on success
    - Automatic rollback on error
    - Proper connection cleanup
    - Cleaner, more readable code
    """
    serialized_player = pickle.dumps(player)

    with transaction() as (conn, cursor):
        cursor.execute(
            "UPDATE lifesim_savegames SET firstname = %s, lastname = %s, ageDays = %s, "
            "ageYears = %s, json = %s, pickle_data = %s WHERE id = %s",
            (player.c.firstname, player.c.lastname, player.c.ageDays, player.c.ageYears,
             json.dumps(player.__dict__, default=lambda o: o.__dict__),
             serialized_player, player.id)
        )

    print(f"game saved! at {player.hourOfDay}:{player.minuteOfHour} on {player.date}")


# ==============================================================================
# Example 2: Atomic operations with multiple tables
# ==============================================================================

def create_new_character_old(user_id, character_data):
    """
    OLD VERSION: Multiple operations without atomic guarantee
    Issue: If second operation fails, first is already committed
    """
    from database import get_database_connection

    mydb = get_database_connection()
    mycursor = mydb.cursor()

    # Insert character
    mycursor.execute(
        "INSERT INTO characters (user_id, name, age) VALUES (%s, %s, %s)",
        (user_id, character_data['name'], character_data['age'])
    )
    mydb.commit()
    character_id = mycursor.lastrowid

    # Insert initial inventory (could fail, leaving orphaned character)
    mycursor.execute(
        "INSERT INTO inventory (character_id, item_type, quantity) VALUES (%s, 'starter_pack', 1)",
        (character_id,)
    )
    mydb.commit()

    return character_id


def create_new_character_new(user_id, character_data):
    """
    NEW VERSION: Atomic operation across multiple tables
    Benefit: Both operations succeed or both fail (atomic)
    """
    with transaction() as (conn, cursor):
        # Insert character
        cursor.execute(
            "INSERT INTO characters (user_id, name, age) VALUES (%s, %s, %s)",
            (user_id, character_data['name'], character_data['age'])
        )
        character_id = cursor.lastrowid

        # Insert initial inventory
        cursor.execute(
            "INSERT INTO inventory (character_id, item_type, quantity) VALUES (%s, 'starter_pack', 1)",
            (character_id,)
        )

        return character_id


# ==============================================================================
# Example 3: Preventing race conditions with row-level locking
# ==============================================================================

def update_user_credits_old(user_id, credit_change):
    """
    OLD VERSION: Race condition vulnerability
    Issue: Two concurrent requests could both read same balance,
           causing one update to be lost
    """
    from database import get_database_connection

    mydb = get_database_connection()
    mycursor = mydb.cursor()

    # Read current balance (vulnerable to race condition)
    mycursor.execute("SELECT credits FROM users WHERE id = %s", (user_id,))
    current_credits = mycursor.fetchone()[0]

    # Check if update is valid
    if current_credits + credit_change < 0:
        raise ValueError("Insufficient credits")

    # Update (another request might have updated in between)
    mycursor.execute(
        "UPDATE users SET credits = credits + %s WHERE id = %s",
        (credit_change, user_id)
    )
    mydb.commit()


def update_user_credits_new(user_id, credit_change):
    """
    NEW VERSION: Row-level locking prevents race conditions
    Benefit: FOR UPDATE locks the row, preventing concurrent modifications
    """
    with transaction(lock=True) as (conn, cursor):
        # Lock row for update
        cursor.execute(
            "SELECT credits FROM users WHERE id = %s FOR UPDATE",
            (user_id,)
        )
        result = cursor.fetchone()

        if not result:
            raise ValueError("User not found")

        current_credits = result[0]

        # Validate change
        if current_credits + credit_change < 0:
            raise ValueError("Insufficient credits")

        # Update (row is locked, no race condition)
        cursor.execute(
            "UPDATE users SET credits = credits + %s WHERE id = %s",
            (credit_change, user_id)
        )


# ==============================================================================
# Example 4: Complex multi-step operation with validation
# ==============================================================================

def purchase_premium_item_old(user_id, item_id, price):
    """
    OLD VERSION: Multiple steps without transaction safety
    Issues:
    - If any step fails, previous steps already committed
    - No row locking, vulnerable to race conditions
    - Hard to maintain and debug
    """
    from database import get_database_connection

    mydb = get_database_connection()
    mycursor = mydb.cursor()

    # Check user balance
    mycursor.execute("SELECT credits FROM users WHERE id = %s", (user_id,))
    credits = mycursor.fetchone()[0]

    if credits < price:
        raise ValueError("Insufficient credits")

    # Deduct credits (committed immediately)
    mycursor.execute(
        "UPDATE users SET credits = credits - %s WHERE id = %s",
        (price, user_id)
    )
    mydb.commit()

    # Add item to inventory (if this fails, credits already deducted!)
    mycursor.execute(
        "INSERT INTO inventory (user_id, item_id) VALUES (%s, %s)",
        (user_id, item_id)
    )
    mydb.commit()

    # Log transaction (if this fails, inconsistent state)
    mycursor.execute(
        "INSERT INTO transaction_log (user_id, item_id, price, type) VALUES (%s, %s, %s, 'purchase')",
        (user_id, item_id, price)
    )
    mydb.commit()


def purchase_premium_item_new(user_id, item_id, price):
    """
    NEW VERSION: Atomic operation with proper locking
    Benefits:
    - All steps succeed or all fail (atomic)
    - Row locking prevents race conditions
    - Clear, maintainable code
    - Automatic rollback on any error
    """
    with transaction(lock=True) as (conn, cursor):
        # Lock user row and check balance
        cursor.execute(
            "SELECT credits FROM users WHERE id = %s FOR UPDATE",
            (user_id,)
        )
        result = cursor.fetchone()

        if not result:
            raise ValueError("User not found")

        if result[0] < price:
            raise ValueError("Insufficient credits")

        # Deduct credits
        cursor.execute(
            "UPDATE users SET credits = credits - %s WHERE id = %s",
            (price, user_id)
        )

        # Add item to inventory
        cursor.execute(
            "INSERT INTO inventory (user_id, item_id) VALUES (%s, %s)",
            (user_id, item_id)
        )

        # Log transaction
        cursor.execute(
            "INSERT INTO transaction_log (user_id, item_id, price, type) "
            "VALUES (%s, %s, %s, 'purchase')",
            (user_id, item_id, price)
        )

        # All operations commit together, or all rollback on error


# ==============================================================================
# Example 5: Idempotent daily reward claim
# ==============================================================================

def claim_daily_reward_old(user_id, reward_date):
    """
    OLD VERSION: Not idempotent, could claim twice
    Issue: Two concurrent requests could both pass the check
    """
    from database import get_database_connection

    mydb = get_database_connection()
    mycursor = mydb.cursor()

    # Check if already claimed
    mycursor.execute(
        "SELECT id FROM daily_rewards WHERE user_id = %s AND reward_date = %s",
        (user_id, reward_date)
    )

    if mycursor.fetchone():
        return False  # Already claimed

    # Grant reward (race condition: both requests might reach here)
    mycursor.execute(
        "UPDATE users SET credits = credits + 100 WHERE id = %s",
        (user_id,)
    )
    mydb.commit()

    # Mark as claimed
    mycursor.execute(
        "INSERT INTO daily_rewards (user_id, reward_date) VALUES (%s, %s)",
        (user_id, reward_date)
    )
    mydb.commit()

    return True


def claim_daily_reward_new(user_id, reward_date):
    """
    NEW VERSION: Idempotent with row-level locking
    Benefit: Guaranteed to execute exactly once, even with concurrent requests
    """
    with transaction(lock=True) as (conn, cursor):
        # Lock and check if already claimed
        cursor.execute(
            "SELECT id FROM daily_rewards WHERE user_id = %s AND reward_date = %s FOR UPDATE",
            (user_id, reward_date)
        )

        if cursor.fetchone():
            return False  # Already claimed

        # Grant reward
        cursor.execute(
            "UPDATE users SET credits = credits + 100 WHERE id = %s",
            (user_id,)
        )

        # Mark as claimed
        cursor.execute(
            "INSERT INTO daily_rewards (user_id, reward_date) VALUES (%s, %s)",
            (user_id, reward_date)
        )

        return True


# ==============================================================================
# Example 6: Using existing connection for multiple transactions
# ==============================================================================

def perform_multiple_operations_old(player):
    """
    OLD VERSION: Multiple separate commits
    Issue: Each operation commits separately, no atomic guarantee
    """
    from database import get_database_connection

    mydb = get_database_connection()

    # Save game state
    mycursor = mydb.cursor()
    mycursor.execute("UPDATE lifesim_savegames SET json = %s WHERE id = %s",
                    (json.dumps(player.__dict__), player.id))
    mydb.commit()

    # Update statistics
    mycursor.execute("INSERT INTO statistics (user_id, stat_type, value) VALUES (%s, %s, %s)",
                    (player.id, 'playtime', player.ticks))
    mydb.commit()

    # Log event
    mycursor.execute("INSERT INTO event_log (user_id, event) VALUES (%s, %s)",
                    (player.id, 'game_saved'))
    mydb.commit()


def perform_multiple_operations_new(player):
    """
    NEW VERSION: Single atomic transaction
    Benefit: All operations succeed together or fail together
    """
    with transaction() as (conn, cursor):
        # Save game state
        cursor.execute(
            "UPDATE lifesim_savegames SET json = %s WHERE id = %s",
            (json.dumps(player.__dict__), player.id)
        )

        # Update statistics
        cursor.execute(
            "INSERT INTO statistics (user_id, stat_type, value) VALUES (%s, %s, %s)",
            (player.id, 'playtime', player.ticks)
        )

        # Log event
        cursor.execute(
            "INSERT INTO event_log (user_id, event) VALUES (%s, %s)",
            (player.id, 'game_saved')
        )

        # All commit together atomically


# ==============================================================================
# Migration Checklist
# ==============================================================================

"""
To migrate existing database code:

1. Replace get_database_connection() + manual commit/rollback with transaction():
   Before: mydb = get_database_connection(); mycursor = mydb.cursor(); ... mydb.commit()
   After:  with transaction() as (conn, cursor): ...

2. Add row-level locking for concurrent updates:
   Before: SELECT ... ; UPDATE ...
   After:  with transaction(lock=True): SELECT ... FOR UPDATE; UPDATE ...

3. Combine related operations into single transaction:
   Before: operation1(); commit(); operation2(); commit()
   After:  with transaction(): operation1(); operation2()

4. Use appropriate isolation levels for complex queries:
   After:  with transaction(isolation_level='SERIALIZABLE'): ...

5. Remove manual error handling:
   Before: try: ... mydb.commit(); except: mydb.rollback()
   After:  with transaction(): ... (automatic)

6. Test concurrent access scenarios with integration tests

7. Monitor for deadlocks and adjust locking strategy if needed
"""