"""
Database-related test fixtures for BaoLife.

This module provides fixtures for database setup, teardown, and test data
management. It includes utilities for creating test database schemas and
populating test data.
"""
import pytest
import asyncio
from typing import AsyncGenerator, Dict, Any


@pytest.fixture(scope="session")
async def test_database_schema():
    """
    Set up test database schema.

    This fixture creates all necessary tables in the test database
    at the start of the test session and drops them at the end.

    Yields:
        None
    """
    from database_async import get_connection

    # SQL for creating test tables
    create_tables_sql = """
    CREATE TABLE IF NOT EXISTS players (
        userID VARCHAR(255) PRIMARY KEY,
        player_data LONGBLOB,
        lastPlayed DATETIME,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS conversations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        userID VARCHAR(255),
        characterID VARCHAR(255),
        message TEXT,
        sender ENUM('player', 'character'),
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
        is_read BOOLEAN DEFAULT FALSE,
        INDEX idx_user_char (userID, characterID),
        INDEX idx_timestamp (timestamp)
    );

    CREATE TABLE IF NOT EXISTS transactions (
        id VARCHAR(255) PRIMARY KEY,
        userID VARCHAR(255),
        transaction_type VARCHAR(50),
        amount DECIMAL(10, 2),
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
        status ENUM('pending', 'completed', 'failed'),
        INDEX idx_user_time (userID, timestamp)
    );

    CREATE TABLE IF NOT EXISTS achievements (
        id INT AUTO_INCREMENT PRIMARY KEY,
        userID VARCHAR(255),
        achievement_id VARCHAR(255),
        unlocked_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY unique_user_achievement (userID, achievement_id)
    );

    CREATE TABLE IF NOT EXISTS analytics_events (
        id INT AUTO_INCREMENT PRIMARY KEY,
        userID VARCHAR(255),
        event_type VARCHAR(100),
        event_data JSON,
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_user_type (userID, event_type),
        INDEX idx_timestamp (timestamp)
    );
    """

    async with get_connection() as conn:
        async with conn.cursor() as cursor:
            # Create tables
            for statement in create_tables_sql.split(';'):
                if statement.strip():
                    await cursor.execute(statement)
            await conn.commit()

    yield

    # Teardown: Drop all test tables
    drop_tables_sql = """
    DROP TABLE IF EXISTS players;
    DROP TABLE IF EXISTS conversations;
    DROP TABLE IF EXISTS transactions;
    DROP TABLE IF EXISTS achievements;
    DROP TABLE IF EXISTS analytics_events;
    """

    async with get_connection() as conn:
        async with conn.cursor() as cursor:
            for statement in drop_tables_sql.split(';'):
                if statement.strip():
                    await cursor.execute(statement)
            await conn.commit()


@pytest.fixture
async def clean_database(test_database_schema):
    """
    Clean all data from test database tables.

    This fixture runs before each test to ensure a clean slate,
    then cleans up after the test as well.

    Args:
        test_database_schema: Ensures schema exists

    Yields:
        None
    """
    from database_async import get_connection

    async def clean():
        async with get_connection() as conn:
            async with conn.cursor() as cursor:
                # Delete all data from tables
                tables = [
                    'conversations',
                    'transactions',
                    'achievements',
                    'analytics_events',
                    'players'
                ]
                for table in tables:
                    await cursor.execute(f"DELETE FROM {table}")
                await conn.commit()

    # Clean before test
    await clean()

    yield

    # Clean after test
    await clean()


@pytest.fixture
async def sample_player_data() -> Dict[str, Any]:
    """
    Provide sample player data for testing.

    Returns:
        Dict: Sample player data that can be inserted into database
    """
    from datetime import datetime
    import pickle
    from core.models import playerClass, personClass

    player = playerClass()
    player.userID = 'test_user_sample'
    player.c = personClass()
    player.c.firstname = 'Sample'
    player.c.lastname = 'Player'
    player.c.sex = 'Female'
    player.c.ageYears = 25
    player.date = datetime.now()

    return {
        'userID': player.userID,
        'player_data': pickle.dumps(player),
        'lastPlayed': datetime.now()
    }


@pytest.fixture
async def insert_test_player(clean_database):
    """
    Insert a test player into the database.

    This fixture provides a function that can be called to insert
    test player data into the database.

    Args:
        clean_database: Ensures database is clean

    Returns:
        Callable: Function to insert player data
    """
    from database_async import get_connection

    async def _insert(user_id: str, player_data: bytes):
        """
        Insert player into test database.

        Args:
            user_id: User ID
            player_data: Pickled player object

        Returns:
            bool: True if successful
        """
        async with get_connection() as conn:
            async with conn.cursor() as cursor:
                await cursor.execute(
                    "INSERT INTO players (userID, player_data) VALUES (%s, %s)",
                    (user_id, player_data)
                )
                await conn.commit()
                return True

    return _insert


@pytest.fixture
async def db_player_factory(clean_database):
    """
    Factory for creating players in the database.

    This fixture provides a function that creates a player object
    and inserts it into the test database.

    Args:
        clean_database: Ensures database is clean

    Returns:
        Callable: Function to create and insert player
    """
    from database_async import get_connection
    from core.models import playerClass, personClass
    from datetime import datetime
    import pickle

    async def _create_player(**kwargs) -> playerClass:
        """
        Create a player and insert into database.

        Args:
            **kwargs: Player attributes to override

        Returns:
            playerClass: Created player object
        """
        player = playerClass()
        player.userID = kwargs.get('userID', f'test_user_{id(player)}')
        player.c = personClass()

        # Set default values
        player.c.firstname = kwargs.get('firstname', 'Test')
        player.c.lastname = kwargs.get('lastname', 'User')
        player.c.sex = kwargs.get('sex', 'Male')
        player.c.ageYears = kwargs.get('age', 25)
        player.date = kwargs.get('date', datetime.now())
        player.controller = 'active'
        player.status = 'active'

        # Insert into database
        async with get_connection() as conn:
            async with conn.cursor() as cursor:
                await cursor.execute(
                    "INSERT INTO players (userID, player_data, lastPlayed) VALUES (%s, %s, %s)",
                    (player.userID, pickle.dumps(player), datetime.now())
                )
                await conn.commit()

        return player

    return _create_player


@pytest.fixture
async def db_conversation_factory(clean_database):
    """
    Factory for creating conversation messages in the database.

    Args:
        clean_database: Ensures database is clean

    Returns:
        Callable: Function to create conversation messages
    """
    from database_async import get_connection
    from datetime import datetime

    async def _create_message(user_id: str, character_id: str, message: str,
                              sender: str = 'player') -> int:
        """
        Create a conversation message in the database.

        Args:
            user_id: User ID
            character_id: Character ID
            message: Message text
            sender: 'player' or 'character'

        Returns:
            int: Message ID
        """
        async with get_connection() as conn:
            async with conn.cursor() as cursor:
                await cursor.execute(
                    """INSERT INTO conversations
                       (userID, characterID, message, sender, timestamp)
                       VALUES (%s, %s, %s, %s, %s)""",
                    (user_id, character_id, message, sender, datetime.now())
                )
                await conn.commit()
                return cursor.lastrowid

    return _create_message


@pytest.fixture
async def db_transaction_factory(clean_database):
    """
    Factory for creating transaction records in the database.

    Args:
        clean_database: Ensures database is clean

    Returns:
        Callable: Function to create transactions
    """
    from database_async import get_connection
    from datetime import datetime
    import uuid

    async def _create_transaction(user_id: str, transaction_type: str,
                                   amount: float, status: str = 'completed') -> str:
        """
        Create a transaction record in the database.

        Args:
            user_id: User ID
            transaction_type: Type of transaction
            amount: Transaction amount
            status: Transaction status

        Returns:
            str: Transaction ID
        """
        transaction_id = str(uuid.uuid4())

        async with get_connection() as conn:
            async with conn.cursor() as cursor:
                await cursor.execute(
                    """INSERT INTO transactions
                       (id, userID, transaction_type, amount, status, timestamp)
                       VALUES (%s, %s, %s, %s, %s, %s)""",
                    (transaction_id, user_id, transaction_type, amount, status, datetime.now())
                )
                await conn.commit()

        return transaction_id

    return _create_transaction


@pytest.fixture
def mock_db_connection():
    """
    Provide a mock database connection for unit tests.

    This fixture returns a mock connection object that simulates
    database operations without actually connecting to a database.

    Returns:
        MockConnection: Mock database connection
    """
    from unittest.mock import MagicMock, AsyncMock

    mock_conn = MagicMock()
    mock_cursor = MagicMock()

    # Set up async context managers
    mock_conn.__aenter__ = AsyncMock(return_value=mock_conn)
    mock_conn.__aexit__ = AsyncMock(return_value=None)
    mock_cursor.__aenter__ = AsyncMock(return_value=mock_cursor)
    mock_cursor.__aexit__ = AsyncMock(return_value=None)

    # Set up cursor methods
    mock_cursor.execute = AsyncMock()
    mock_cursor.fetchone = AsyncMock(return_value=None)
    mock_cursor.fetchall = AsyncMock(return_value=[])
    mock_cursor.lastrowid = 1

    # Link cursor to connection
    mock_conn.cursor = MagicMock(return_value=mock_cursor)
    mock_conn.commit = AsyncMock()
    mock_conn.rollback = AsyncMock()

    return mock_conn


@pytest.fixture
async def populated_test_db(
    clean_database,
    db_player_factory,
    db_conversation_factory,
    db_transaction_factory
):
    """
    Provide a database populated with test data.

    This fixture creates a set of test players, conversations, and
    transactions that can be used for integration testing.

    Args:
        clean_database: Clean database fixture
        db_player_factory: Player factory fixture
        db_conversation_factory: Conversation factory fixture
        db_transaction_factory: Transaction factory fixture

    Returns:
        Dict: Dictionary containing created test data
    """
    # Create test players
    player1 = await db_player_factory(
        userID='test_user_1',
        firstname='Alice',
        age=25
    )

    player2 = await db_player_factory(
        userID='test_user_2',
        firstname='Bob',
        age=30
    )

    # Create test conversations
    msg1 = await db_conversation_factory(
        'test_user_1',
        'char_123',
        'Hello!',
        'player'
    )

    msg2 = await db_conversation_factory(
        'test_user_1',
        'char_123',
        'Hi there!',
        'character'
    )

    # Create test transactions
    txn1 = await db_transaction_factory(
        'test_user_1',
        'purchase',
        9.99,
        'completed'
    )

    return {
        'players': [player1, player2],
        'messages': [msg1, msg2],
        'transactions': [txn1]
    }