"""
Player Statistics and Photo Album System

Tracks lifetime statistics and captures memorable life moments.
Supports 15+ different statistics and integrates with achievement system.
"""

from typing import Dict, Any, List, Optional
from datetime import datetime
import json
import logging
from database import get_database_connection


# =====================================================
# STATISTICS TRACKING
# =====================================================

def initialize_player_statistics(player_id: int) -> bool:
    """
    Create initial statistics record for new player.
    Called when player account is created.

    Args:
        player_id: The player's ID

    Returns:
        bool: True if successful, False otherwise
    """
    conn = None
    cursor = None

    try:
        conn = get_database_connection()
        cursor = conn.cursor()

        cursor.execute(
            """INSERT INTO player_statistics (player_id)
               VALUES (%s)
               ON DUPLICATE KEY UPDATE player_id = player_id""",
            (player_id,)
        )

        conn.commit()
        logging.info(f"Initialized statistics for player {player_id}")
        return True

    except Exception as e:
        if conn:
            conn.rollback()
        logging.error(f"Error initializing statistics for player {player_id}: {e}", exc_info=True)
        return False

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


def increment_stat(player_id: int, stat_name: str, amount: int = 1) -> bool:
    """
    Increment a player statistic by a specified amount.

    Args:
        player_id: The player's ID
        stat_name: Name of the statistic column (e.g., 'total_relationships')
        amount: Amount to increment by (default: 1, must be positive)

    Returns:
        bool: True if successful, False otherwise

    Valid stat_name values:
        - lifetime_earnings, lifetime_spending
        - total_relationships, total_activities, total_conversations
        - total_years_lived, total_deaths
        - highest_job_level, max_affinity_reached
        - people_dated, times_fired, years_married
        - job_count, children_count, friends_count
    """
    # Validate amount is positive
    if amount <= 0:
        logging.error(f"Invalid increment amount for player {player_id}: {amount} (must be positive)")
        return False

    conn = None
    cursor = None

    # Validate stat_name to prevent SQL injection
    valid_stats = [
        'lifetime_earnings', 'lifetime_spending',
        'total_relationships', 'total_activities', 'total_conversations',
        'total_years_lived', 'total_deaths',
        'highest_job_level', 'max_affinity_reached',
        'people_dated', 'times_fired', 'years_married',
        'job_count', 'children_count', 'friends_count'
    ]

    if stat_name not in valid_stats:
        logging.error(f"Invalid stat_name: {stat_name}")
        return False

    try:
        conn = get_database_connection()
        cursor = conn.cursor()

        # Ensure stats record exists
        cursor.execute(
            """INSERT INTO player_statistics (player_id)
               VALUES (%s)
               ON DUPLICATE KEY UPDATE player_id = player_id""",
            (player_id,)
        )

        # Increment the stat
        query = f"UPDATE player_statistics SET {stat_name} = {stat_name} + %s WHERE player_id = %s"
        cursor.execute(query, (amount, player_id))

        conn.commit()
        logging.debug(f"Player {player_id}: {stat_name} incremented by {amount}")
        return True

    except Exception as e:
        if conn:
            conn.rollback()
        logging.error(f"Error incrementing stat {stat_name} for player {player_id}: {e}", exc_info=True)
        return False

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


def update_stat(player_id: int, stat_name: str, value: int) -> bool:
    """
    Set a player statistic to a specific value.
    Used for 'highest' or 'max' type stats that track records.

    Args:
        player_id: The player's ID
        stat_name: Name of the statistic column
        value: Value to set

    Returns:
        bool: True if successful, False otherwise

    Example:
        # Update highest job level reached
        update_stat(player_id, 'highest_job_level', 5)
    """
    conn = None
    cursor = None

    # Validate stat_name to prevent SQL injection
    valid_stats = [
        'lifetime_earnings', 'lifetime_spending',
        'total_relationships', 'total_activities', 'total_conversations',
        'total_years_lived', 'total_deaths',
        'highest_job_level', 'max_affinity_reached',
        'people_dated', 'times_fired', 'years_married',
        'job_count', 'children_count', 'friends_count'
    ]

    if stat_name not in valid_stats:
        logging.error(f"Invalid stat_name: {stat_name}")
        return False

    try:
        conn = get_database_connection()
        cursor = conn.cursor()

        # Ensure stats record exists
        cursor.execute(
            """INSERT INTO player_statistics (player_id)
               VALUES (%s)
               ON DUPLICATE KEY UPDATE player_id = player_id""",
            (player_id,)
        )

        # Update the stat
        query = f"UPDATE player_statistics SET {stat_name} = %s WHERE player_id = %s"
        cursor.execute(query, (value, player_id))

        conn.commit()
        logging.debug(f"Player {player_id}: {stat_name} set to {value}")
        return True

    except Exception as e:
        if conn:
            conn.rollback()
        logging.error(f"Error updating stat {stat_name} for player {player_id}: {e}", exc_info=True)
        return False

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


def set_boolean_stat(player_id: int, stat_name: str, value: bool) -> bool:
    """
    Set a boolean statistic (e.g., ever_married).

    Args:
        player_id: The player's ID
        stat_name: Name of the boolean statistic column
        value: Boolean value to set

    Returns:
        bool: True if successful, False otherwise
    """
    conn = None
    cursor = None

    # Only allow valid boolean stats
    valid_bool_stats = ['ever_married']

    if stat_name not in valid_bool_stats:
        logging.error(f"Invalid boolean stat_name: {stat_name}")
        return False

    try:
        conn = get_database_connection()
        cursor = conn.cursor()

        # Ensure stats record exists
        cursor.execute(
            """INSERT INTO player_statistics (player_id)
               VALUES (%s)
               ON DUPLICATE KEY UPDATE player_id = player_id""",
            (player_id,)
        )

        # Update the boolean stat
        query = f"UPDATE player_statistics SET {stat_name} = %s WHERE player_id = %s"
        cursor.execute(query, (value, player_id))

        conn.commit()
        logging.debug(f"Player {player_id}: {stat_name} set to {value}")
        return True

    except Exception as e:
        if conn:
            conn.rollback()
        logging.error(f"Error setting boolean stat {stat_name} for player {player_id}: {e}", exc_info=True)
        return False

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


def get_player_statistics(player_id: int) -> Dict[str, Any]:
    """
    Get all statistics for a player.

    Args:
        player_id: The player's ID

    Returns:
        Dict with all statistic values, or empty dict if not found
    """
    conn = None
    cursor = None

    try:
        conn = get_database_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute(
            "SELECT * FROM player_statistics WHERE player_id = %s",
            (player_id,)
        )
        stats = cursor.fetchone()

        if not stats:
            # Initialize if not exists
            initialize_player_statistics(player_id)
            cursor.execute(
                "SELECT * FROM player_statistics WHERE player_id = %s",
                (player_id,)
            )
            stats = cursor.fetchone()

        return stats if stats else {}

    except Exception as e:
        logging.error(f"Error getting statistics for player {player_id}: {e}", exc_info=True)
        return {}

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# =====================================================
# PHOTO ALBUM SYSTEM
# =====================================================

def capture_photo_memory(
    player_id: int,
    event_type: str,
    description: str,
    snapshot_data: Dict[str, Any],
    character_age: Optional[int] = None,
    game_date: Optional[datetime] = None
) -> Optional[int]:
    """
    Capture a memorable life moment in the photo album.

    Args:
        player_id: The player's ID
        event_type: Type of event (e.g., 'graduation', 'marriage', 'birth')
        description: Human-readable description
        snapshot_data: Dictionary of relevant data to store
        character_age: Character's age at time of event (optional, will fetch from player if not provided)
        game_date: Game date/time (optional, will use current datetime if not provided)

    Returns:
        int: Photo album entry ID if successful, None otherwise

    Example:
        capture_photo_memory(
            player_id=1,
            event_type='graduation',
            description='Graduated from Harvard University',
            snapshot_data={'level': 'college', 'gpa': 3.8, 'major': 'Computer Science'}
        )
    """
    conn = None
    cursor = None

    try:
        conn = get_database_connection()
        cursor = conn.cursor(dictionary=True)

        # Get character age if not provided
        if character_age is None:
            cursor.execute("SELECT age FROM players WHERE id = %s", (player_id,))
            player = cursor.fetchone()
            character_age = player['age'] if player else 0

        # Use current datetime if not provided
        if game_date is None:
            game_date = datetime.now()

        # Insert photo memory
        cursor.execute(
            """INSERT INTO player_photo_album
               (player_id, event_type, event_description, game_date, character_age, snapshot_data)
               VALUES (%s, %s, %s, %s, %s, %s)""",
            (player_id, event_type, description, game_date, character_age, json.dumps(snapshot_data))
        )

        photo_id = cursor.lastrowid
        conn.commit()

        logging.info(f"Captured photo memory for player {player_id}: {event_type} - {description}")
        return photo_id

    except Exception as e:
        if conn:
            conn.rollback()
        logging.error(f"Error capturing photo memory for player {player_id}: {e}", exc_info=True)
        return None

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


def get_photo_album(player_id: int, limit: int = 50, offset: int = 0) -> List[Dict[str, Any]]:
    """
    Get photo memories for a player.

    Args:
        player_id: The player's ID
        limit: Maximum number of photos to return (default: 50)
        offset: Number of photos to skip (for pagination, default: 0)

    Returns:
        List of photo memory dictionaries, ordered by game_date descending
    """
    conn = None
    cursor = None

    try:
        conn = get_database_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute(
            """SELECT id, event_type, event_description, game_date, character_age, snapshot_data, created_at
               FROM player_photo_album
               WHERE player_id = %s
               ORDER BY game_date DESC
               LIMIT %s OFFSET %s""",
            (player_id, limit, offset)
        )
        photos = cursor.fetchall()

        # Parse JSON snapshot_data
        for photo in photos:
            if photo.get('snapshot_data'):
                try:
                    photo['snapshot_data'] = json.loads(photo['snapshot_data'])
                except json.JSONDecodeError:
                    photo['snapshot_data'] = {}

            # Convert datetime to ISO string for JSON serialization
            if photo.get('game_date'):
                photo['game_date'] = photo['game_date'].isoformat()
            if photo.get('created_at'):
                photo['created_at'] = photo['created_at'].isoformat()

        return photos

    except Exception as e:
        logging.error(f"Error getting photo album for player {player_id}: {e}", exc_info=True)
        return []

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


def get_photo_album_count(player_id: int) -> int:
    """
    Get total count of photo memories for a player.

    Args:
        player_id: The player's ID

    Returns:
        int: Total number of photos
    """
    conn = None
    cursor = None

    try:
        conn = get_database_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute(
            "SELECT COUNT(*) as count FROM player_photo_album WHERE player_id = %s",
            (player_id,)
        )
        result = cursor.fetchone()

        return result['count'] if result else 0

    except Exception as e:
        logging.error(f"Error getting photo album count for player {player_id}: {e}", exc_info=True)
        return 0

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


def get_photos_by_type(player_id: int, event_type: str) -> List[Dict[str, Any]]:
    """
    Get all photo memories of a specific type for a player.

    Args:
        player_id: The player's ID
        event_type: Type of event to filter by

    Returns:
        List of photo memory dictionaries
    """
    conn = None
    cursor = None

    try:
        conn = get_database_connection()
        cursor = conn.cursor(dictionary=True)

        cursor.execute(
            """SELECT id, event_type, event_description, game_date, character_age, snapshot_data, created_at
               FROM player_photo_album
               WHERE player_id = %s AND event_type = %s
               ORDER BY game_date DESC""",
            (player_id, event_type)
        )
        photos = cursor.fetchall()

        # Parse JSON snapshot_data
        for photo in photos:
            if photo.get('snapshot_data'):
                try:
                    photo['snapshot_data'] = json.loads(photo['snapshot_data'])
                except json.JSONDecodeError:
                    photo['snapshot_data'] = {}

            # Convert datetime to ISO string
            if photo.get('game_date'):
                photo['game_date'] = photo['game_date'].isoformat()
            if photo.get('created_at'):
                photo['created_at'] = photo['created_at'].isoformat()

        return photos

    except Exception as e:
        logging.error(f"Error getting photos by type for player {player_id}: {e}", exc_info=True)
        return []

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# =====================================================
# INTEGRATION HELPERS
# =====================================================

def track_money_earned(player_id: int, amount: int) -> None:
    """
    Track money earned by player.
    Call this whenever player receives money (salary, job, gifts, etc.)

    Args:
        player_id: The player's ID
        amount: Amount of money earned
    """
    increment_stat(player_id, 'lifetime_earnings', amount)


def track_money_spent(player_id: int, amount: int) -> None:
    """
    Track money spent by player.
    Call this whenever player spends money (purchases, expenses, etc.)

    Args:
        player_id: The player's ID
        amount: Amount of money spent
    """
    increment_stat(player_id, 'lifetime_spending', amount)


def track_relationship_formed(player_id: int) -> None:
    """
    Track new relationship formed.
    Call this when player forms a new relationship (friend, romantic, family).

    Args:
        player_id: The player's ID
    """
    increment_stat(player_id, 'total_relationships', 1)


def track_activity_completed(player_id: int) -> None:
    """
    Track activity completion.
    Call this when player completes any activity.

    Args:
        player_id: The player's ID
    """
    increment_stat(player_id, 'total_activities', 1)


def track_conversation(player_id: int) -> None:
    """
    Track conversation with NPC.
    Call this when player has a conversation.

    Args:
        player_id: The player's ID
    """
    increment_stat(player_id, 'total_conversations', 1)


# =====================================================
# INTEGRATION EXAMPLES
# =====================================================

"""
INTEGRATION EXAMPLES - How to use these functions in existing game events:

1. GRADUATION EVENT (ws/events.py or ws/dayEvents.py):

   def graduationEvent(player, level):
       # ... existing graduation logic ...

       # Track statistics
       increment_stat(player.userID, 'total_activities', 1)

       # Capture photo memory
       capture_photo_memory(
           player_id=player.userID,
           event_type='graduation',
           description=f'Graduated from {level}',
           snapshot_data={
               'level': level,
               'gpa': player.c.gpa if hasattr(player.c, 'gpa') else 0,
               'age': player.c.age
           }
       )

       # Check achievements (already implemented in achievements.py)
       from retention.achievements import check_achievements
       check_achievements(player.userID, 'graduate', {'level': level, 'gpa': player.c.gpa})


2. MARRIAGE EVENT:

   def marriageEvent(player, spouse):
       # ... existing marriage logic ...

       # Track statistics
       increment_stat(player.userID, 'total_relationships', 1)
       set_boolean_stat(player.userID, 'ever_married', True)

       # Get current years_married and increment
       stats = get_player_statistics(player.userID)
       current_years = stats.get('years_married', 0)
       update_stat(player.userID, 'years_married', current_years + 1)

       # Capture photo memory
       capture_photo_memory(
           player_id=player.userID,
           event_type='marriage',
           description=f'Married {spouse.firstname} {spouse.lastname}',
           snapshot_data={
               'spouse_name': f'{spouse.firstname} {spouse.lastname}',
               'spouse_id': spouse.id if hasattr(spouse, 'id') else None,
               'age': player.c.age,
               'location': player.c.location if hasattr(player.c, 'location') else None
           }
       )

       # Check achievements
       from retention.achievements import check_achievements
       check_achievements(player.userID, 'marriage', {})


3. JOB/PROMOTION EVENT:

   def promotionEvent(player, new_title, new_level, salary_increase):
       # ... existing promotion logic ...

       # Track statistics
       increment_stat(player.userID, 'job_count', 1)

       # Update highest job level if this is a new record
       stats = get_player_statistics(player.userID)
       if new_level > stats.get('highest_job_level', 0):
           update_stat(player.userID, 'highest_job_level', new_level)

       # Capture photo memory
       capture_photo_memory(
           player_id=player.userID,
           event_type='promotion',
           description=f'Promoted to {new_title}',
           snapshot_data={
               'title': new_title,
               'level': new_level,
               'salary': salary_increase,
               'company': player.c.employer if hasattr(player.c, 'employer') else None
           }
       )

       # Check achievements
       from retention.achievements import check_achievements
       check_achievements(player.userID, 'promotion', {'title': new_title})


4. BIRTH OF CHILD EVENT:

   def birthEvent(player, child_name):
       # ... existing birth logic ...

       # Track statistics
       increment_stat(player.userID, 'children_count', 1)
       increment_stat(player.userID, 'total_relationships', 1)

       # Capture photo memory
       capture_photo_memory(
           player_id=player.userID,
           event_type='birth',
           description=f'{child_name} was born',
           snapshot_data={
               'child_name': child_name,
               'parent_age': player.c.age,
               'children_count': get_player_statistics(player.userID).get('children_count', 1)
           }
       )

       # Check achievements
       from retention.achievements import check_achievements
       stats = get_player_statistics(player.userID)
       check_achievements(player.userID, 'birth_child', {'children_count': stats['children_count']})


5. DEATH EVENT (End of Life):

   def deathEvent(player):
       # ... existing death logic ...

       # Track statistics
       increment_stat(player.userID, 'total_deaths', 1)

       # Add years lived
       years_this_life = player.c.age
       increment_stat(player.userID, 'total_years_lived', years_this_life)

       # Capture photo memory
       capture_photo_memory(
           player_id=player.userID,
           event_type='death',
           description=f'Passed away at age {player.c.age}',
           snapshot_data={
               'age': player.c.age,
               'money': player.c.money,
               'relationships': len(player.r) if hasattr(player, 'r') else 0,
               'occupation': player.c.occupation if hasattr(player.c, 'occupation') else None
           }
       )

       # Check achievements
       from retention.achievements import check_achievements
       check_achievements(player.userID, 'death', {'age': player.c.age, 'money': player.c.money})


6. FRIENDSHIP/AFFINITY MILESTONE:

   def affinityIncreaseEvent(player, npc, new_affinity):
       # ... existing affinity logic ...

       # Track max affinity reached
       stats = get_player_statistics(player.userID)
       if new_affinity > stats.get('max_affinity_reached', 0):
           update_stat(player.userID, 'max_affinity_reached', new_affinity)

       # If this is a new friendship (affinity >= 50)
       if new_affinity >= 50:
           increment_stat(player.userID, 'friends_count', 1)

           # Check achievements
           from retention.achievements import check_achievements
           check_achievements(player.userID, 'make_friend', {})

       # Check for best friend milestone (affinity >= 100)
       if new_affinity >= 100:
           capture_photo_memory(
               player_id=player.userID,
               event_type='best_friend',
               description=f'Became best friends with {npc.firstname}',
               snapshot_data={
                   'friend_name': f'{npc.firstname} {npc.lastname}',
                   'affinity': new_affinity
               }
           )

           from retention.achievements import check_achievements
           check_achievements(player.userID, 'affinity_milestone', {'affinity': new_affinity})


7. DATING EVENT:

   def startDatingEvent(player, partner):
       # ... existing dating logic ...

       # Track people dated
       increment_stat(player.userID, 'people_dated', 1)

       # Capture photo memory
       capture_photo_memory(
           player_id=player.userID,
           event_type='dating',
           description=f'Started dating {partner.firstname}',
           snapshot_data={
               'partner_name': f'{partner.firstname} {partner.lastname}',
               'age': player.c.age
           }
       )


8. FIRED FROM JOB EVENT:

   def firedEvent(player, reason):
       # ... existing fired logic ...

       # Track times fired
       increment_stat(player.userID, 'times_fired', 1)

       # Check achievements
       from retention.achievements import check_achievements
       stats = get_player_statistics(player.userID)
       check_achievements(player.userID, 'fired', {'times_fired': stats['times_fired']})


9. MONEY TRANSACTIONS (in daily activities or purchases):

   def receiveSalary(player, amount):
       # ... existing salary logic ...
       player.c.money += amount

       # Track earnings
       track_money_earned(player.userID, amount)

       # Check for earning milestones
       stats = get_player_statistics(player.userID)
       if stats['lifetime_earnings'] >= 1000000:
           from retention.achievements import check_achievements
           check_achievements(player.userID, 'death', {})  # Will check lifetime earnings

   def purchaseItem(player, item, cost):
       # ... existing purchase logic ...
       player.c.money -= cost

       # Track spending
       track_money_spent(player.userID, cost)


10. CONVERSATIONS (in conversationEvents.py):

    def handleConversation(player, npc):
        # ... existing conversation logic ...

        # Track conversation
        track_conversation(player.userID)


INITIALIZATION:
Add to server startup (app.py):

    from retention.statistics import initialize_player_statistics

    # When new player is created
    initialize_player_statistics(new_player_id)

"""