#!/usr/bin/env python
"""
Database Operations Module

This module contains all database-related functions for the BaoLife game.
Extracted from functions.py to provide a centralized database layer.

Functions:
- connect_to_database(): Create database connection
- get_database_connection(): Get or reconnect to database
- insertGame(player): Insert/replace game save
- saveGameAsync(player): Save game state (async version)
- loadGameAsync(user_id): Load game state (async version)
- saveGame(player): Save game state (legacy sync version)
- loadGame(id): Load game state (sync version)
- loadGames(): Load all game IDs
- saveConversationMessage(...): Save conversation message
- markConversationAsRead(...): Mark conversation as read
"""

import mysql.connector
from mysql.connector import Error
import pickle
import json
import uuid
import logging
import io

# Import configuration
from config import config
from server.websocket_messaging import ComplexHandler

# Set up logger
logger = logging.getLogger(__name__)


# ============================================================================
# Pickle Compatibility for Refactored Classes
# ============================================================================

class RefactoringUnpickler(pickle.Unpickler):
    """
    Custom unpickler to handle classes that were moved during refactoring.

    During the refactoring, classes were moved from functions.py to organized
    submodules. This unpickler redirects pickle to find classes in their new
    locations.

    Class migrations:
    - functions.playerClass -> core.models.playerClass
    - functions.personClass -> core.models.personClass
    - functions.locationClass -> core.models.locationClass
    - functions.ActivityRecord -> core.models.ActivityRecord
    - functions.EducationRecord -> core.models.EducationRecord
    - functions.dailyEvent -> core.models.dailyEvent
    - functions.oneTimeEvent -> core.models.oneTimeEvent
    - functions.scheduleDays -> core.models.scheduleDays
    - functions.scheduler -> core.models.scheduler
    - functions.relationshipClass -> core.models.relationshipClass
    - functions.healthCondition -> health.health_manager.HealthCondition
    - functions.HealthCondition -> health.health_manager.HealthCondition
    """

    # Map of old module.class -> new module.class
    CLASS_RENAMES = {
        ('functions', 'playerClass'): ('core.models', 'playerClass'),
        ('functions', 'personClass'): ('core.models', 'personClass'),
        ('functions', 'locationClass'): ('core.models', 'locationClass'),
        ('functions', 'ActivityRecord'): ('core.models', 'ActivityRecord'),
        ('functions', 'EducationRecord'): ('core.models', 'EducationRecord'),
        ('functions', 'dailyEvent'): ('core.models', 'dailyEvent'),
        ('functions', 'oneTimeEvent'): ('core.models', 'oneTimeEvent'),
        ('functions', 'scheduleDays'): ('core.models', 'scheduleDays'),
        ('functions', 'scheduler'): ('core.models', 'scheduler'),
        ('functions', 'relationshipClass'): ('core.models', 'relationshipClass'),
        ('functions', 'healthCondition'): ('health.health_manager', 'HealthCondition'),
        ('functions', 'HealthCondition'): ('health.health_manager', 'HealthCondition'),
    }

    def find_class(self, module, name):
        """
        Override find_class to redirect old class locations to new ones.

        Args:
            module: Original module name from pickle
            name: Original class name from pickle

        Returns:
            The class object from the new location
        """
        # Check if this class was renamed/moved
        renamed = self.CLASS_RENAMES.get((module, name))
        if renamed:
            new_module, new_name = renamed
            logger.debug(f"Redirecting pickle: {module}.{name} -> {new_module}.{new_name}")
            module, name = new_module, new_name

        # Use parent's find_class with potentially updated module/name
        return super().find_class(module, name)


def pickle_loads_compat(data):
    """
    Load pickled data with refactoring compatibility.

    Args:
        data: Pickled byte data

    Returns:
        Unpickled object with class references updated to new locations
    """
    player = RefactoringUnpickler(io.BytesIO(data)).load()

    # Migrate old list-based event tracking to sets (added 2025-11-13)
    if hasattr(player, 'events') and isinstance(player.events, list):
        logger.info(f"Migrating player.events from list to set ({len(player.events)} events)")
        player.events = set(player.events)

    if hasattr(player, 'askedQuestions') and isinstance(player.askedQuestions, list):
        logger.info(f"Migrating player.askedQuestions from list to set ({len(player.askedQuestions)} questions)")
        player.askedQuestions = set(player.askedQuestions)

    # Migrate characters without messaging traits (added 2025-11-13)
    from messaging_style import initialize_messaging_traits, initialize_messaging_patterns, initialize_relationship_modifiers

    # Migrate player character
    if hasattr(player, 'c') and player.c:
        if not hasattr(player.c, 'messaging_traits'):
            logger.info(f"Migrating player character - adding messaging traits")
            player.c.messaging_traits = initialize_messaging_traits()
            player.c.messaging_patterns = initialize_messaging_patterns()

    # Migrate all NPC characters
    if hasattr(player, 'r'):
        migrated_count = 0
        for character in player.r:
            if not hasattr(character, 'messaging_traits'):
                character.messaging_traits = initialize_messaging_traits()
                character.messaging_patterns = initialize_messaging_patterns()
                migrated_count += 1
        if migrated_count > 0:
            logger.info(f"Migrated {migrated_count} NPC characters - added messaging traits")

    # Migrate relationships without messaging modifiers
    if hasattr(player, 'relData'):
        migrated_rel_count = 0
        for rel in player.relData:
            if not hasattr(rel, 'messaging_modifiers'):
                rel.messaging_modifiers = initialize_relationship_modifiers()
                migrated_rel_count += 1
        if migrated_rel_count > 0:
            logger.info(f"Migrated {migrated_rel_count} relationships - added messaging modifiers")

    return player


def connect_to_database():
    """
    Connect to database using configuration from environment variables.

    NOTE: This uses mysql.connector (sync), which does NOT support pool_timeout.
    For async connections with pool_timeout, use database_async.initialize_pool() instead.
    """
    # Build connection parameters explicitly
    # mysql.connector.connect() does NOT support: pool_timeout
    conn_params = {
        'host': config.DB_HOST,
        'port': config.DB_PORT,
        'user': config.DB_USER,
        'password': config.DB_PASSWORD,
        'database': config.DB_NAME
    }

    # NOTE: Do NOT add pool_timeout here - it's only for aiomysql, not mysql.connector
    return mysql.connector.connect(**conn_params)


def get_database_connection():
    """
    Create a new database connection for synchronous operations.

    IMPORTANT: Caller is responsible for closing the connection.
    Use with try/finally or context manager pattern.

    DEPRECATED: Use async pool (database_async.py) for new code.
    This creates a new connection on each call to avoid module-level connection issues.

    Returns:
        mysql.connector.connection: New database connection
    """
    try:
        return connect_to_database()
    except Error as e:
        logger.error(f"Failed to connect to database: {e}")
        raise


def insertGame(player):
    """
    Insert/replace game save in database.

    DEPRECATED: Use saveGameAsync instead.
    """
    mydb = None
    mycursor = None
    try:
        mydb = get_database_connection()
        mycursor = mydb.cursor()
        serialized_player = pickle.dumps(player)
        sql = "REPLACE INTO lifesim_savegames (id, firstname, lastname, ageDays, ageYears, json, pickle_data) VALUES (%s, %s, %s, %s, %s, %s, %s)"
        val = (player.id,player.c.firstname,player.c.lastname,player.c.ageDays,player.c.ageYears,json.dumps(player.__dict__,default=ComplexHandler),serialized_player)
        mycursor.execute(sql, val)
        mydb.commit()
    finally:
        if mycursor:
            mycursor.close()
        if mydb:
            mydb.close()


# New async version
async def saveGameAsync(player):
    """
    Save game state to database (async version).

    Args:
        player: playerClass instance

    Returns:
        bool: True if successful
    """
    from database_async import execute_query
    import pickle

    try:
        # Prepare player data
        player_data = {
            'id': player.id,
            'pickle_data': pickle.dumps(player),
            'firstname': player.c.firstname if hasattr(player.c, 'firstname') else '',
            'lastname': player.c.lastname if hasattr(player.c, 'lastname') else '',
            'ageYears': player.c.ageYears if hasattr(player.c, 'ageYears') else 0,
            'ageDays': player.c.ageDays if hasattr(player.c, 'ageDays') else 0,
            'status': player.c.status if hasattr(player.c, 'status') else 'alive',
        }

        # Upsert query - use correct column names from database
        # Using modern MySQL syntax with alias instead of deprecated VALUES()
        query = """
            INSERT INTO lifesim_savegames (id, pickle_data, firstname, lastname, ageYears, ageDays, json)
            VALUES (%s, %s, %s, %s, %s, %s, '{}') AS new_data
            ON DUPLICATE KEY UPDATE
                pickle_data = new_data.pickle_data,
                firstname = new_data.firstname,
                lastname = new_data.lastname,
                ageYears = new_data.ageYears,
                ageDays = new_data.ageDays,
                lastUpdated = CURRENT_TIMESTAMP
        """

        await execute_query(
            query,
            (
                player_data['id'],
                player_data['pickle_data'],
                player_data['firstname'],
                player_data['lastname'],
                player_data['ageYears'],
                player_data['ageDays'],
            )
        )

        logger.debug(f"Game saved: {player.id} ({player.c.firstname} {player.c.lastname})")
        return True

    except Exception as e:
        logger.error(f"Failed to save game for {player.id}: {e}", exc_info=True)
        return False


async def loadGameAsync(user_id: str):
    """
    Load game state from database (async version).

    Args:
        user_id: User ID to load

    Returns:
        playerClass instance or None if not found
    """
    from database_async import fetch_one
    import pickle

    try:
        result = await fetch_one(
            "SELECT pickle_data FROM lifesim_savegames WHERE id = %s",
            (user_id,)
        )

        if result is None:
            logger.debug(f"No saved game found for {user_id}")
            return None

        # Unpickle player data with refactoring compatibility
        player = pickle_loads_compat(result[0])
        logger.debug(f"Game loaded: {user_id} ({player.c.firstname} {player.c.lastname})")

        return player

    except Exception as e:
        logger.error(f"Failed to load game for {user_id}: {e}", exc_info=True)
        return None


# Keep old sync version for backward compatibility during migration
def saveGame(player):
    """
    Save game state to database (legacy sync version).

    DEPRECATED: Use saveGameAsync instead.
    This version uses synchronous mysql.connector and will be removed.
    """
    import pickle

    mydb = None
    mycursor = None
    try:
        mydb = get_database_connection()
        mycursor = mydb.cursor()

        player_data = {
            'id': player.id,
            'pickle_data': pickle.dumps(player),
            'firstname': player.c.firstname if hasattr(player.c, 'firstname') else '',
            'lastname': player.c.lastname if hasattr(player.c, 'lastname') else '',
            'age': player.c.ageYears if hasattr(player.c, 'ageYears') else 0,
            'status': player.c.status if hasattr(player.c, 'status') else 'alive',
        }

        query = """
            INSERT INTO lifesim_savegames (id, pickle_data, firstname, lastname, age, status, lastUpdate)
            VALUES (%s, %s, %s, %s, %s, %s, NOW()) AS new_save
            ON DUPLICATE KEY UPDATE
                pickle_data = new_save.pickle_data,
                firstname = new_save.firstname,
                lastname = new_save.lastname,
                age = new_save.age,
                status = new_save.status,
                lastUpdate = NOW()
        """

        mycursor.execute(query, (
            player_data['id'],
            player_data['pickle_data'],
            player_data['firstname'],
            player_data['lastname'],
            player_data['age'],
            player_data['status'],
        ))

        mydb.commit()
        return True

    except Exception as e:
        logger.error(f"Failed to save game: {e}")
        return False
    finally:
        if mycursor:
            mycursor.close()
        if mydb:
            mydb.close()


def loadGame(id):
    """
    Load game using parameterized query to prevent SQL injection.

    DEPRECATED: Use loadGameAsync instead.
    """
    mydb = None
    mycursor = None
    try:
        mydb = get_database_connection()
        mycursor = mydb.cursor()
        # Use parameterized query instead of string concatenation
        sql = "SELECT pickle_data FROM lifesim_savegames WHERE id = %s"
        mycursor.execute(sql, (id,))
        myresult = mycursor.fetchall()
        if (myresult and myresult[0] and myresult[0][0]):
            # Use compatibility unpickler for refactored classes
            player = pickle_loads_compat(myresult[0][0])
            return player
        else:
            return False
    except Exception as e:
        logger.error(f"Error loading game {id}: {e}")
        return False
    finally:
        if mycursor:
            mycursor.close()
        if mydb:
            mydb.close()


def loadGames():
    """
    Load all game IDs.

    DEPRECATED: Create async version if needed.
    """
    mydb = None
    mycursor = None
    try:
        mydb = get_database_connection()
        mycursor = mydb.cursor()
        string = "SELECT id FROM lifesim_savegames"
        mycursor.execute(string)
        myresult = mycursor.fetchall()
        if (myresult):
            return myresult
        else:
            return False
    except Exception as e:
        logger.error(f"Error loading games: {e}")
        return False
    finally:
        if mycursor:
            mycursor.close()
        if mydb:
            mydb.close()


def saveConversationMessage(message, character, playerID, sender, player):
    """
    Save a conversation message to the database.

    DEPRECATED: Create async version if needed.
    """
    mydb = None
    try:
        mydb = get_database_connection()
        # Use context manager to automatically close cursor
        with mydb.cursor() as mycursor:
            id = uuid.uuid4().hex
            sql = "INSERT INTO messages (id, partner, player, message, sender, date) VALUES (%s, %s, %s, %s, %s, %s)"
            val = (id, character, playerID, message, sender, player.date)
            mycursor.execute(sql, val)
            mydb.commit()
    finally:
        # Ensure database connection is closed
        if mydb:
            mydb.close()


def markConversationAsRead(player, characterID):
    for i in range(0,len(player.conversations)):
        if (player.conversations[i].character == characterID):
            player.conversations[i].unread = False
    return True