"""
Test database setup and teardown utilities.

Provides utilities for creating and managing a test database
for integration tests that require real database access.
"""
import os
import mysql.connector
from mysql.connector import Error


class TestDatabaseManager:
    """
    Manages test database creation and cleanup.

    Creates a separate test database (lifesim_test) to avoid
    polluting production data during testing.
    """

    def __init__(self, db_name='lifesim_test'):
        """
        Initialize test database manager.

        Args:
            db_name: Name of the test database
        """
        self.db_name = db_name
        self.connection = None
        self.use_test_db = os.environ.get('USE_TEST_DB', 'false').lower() == 'true'

    def connect(self):
        """Connect to MySQL server (not database-specific)."""
        try:
            self.connection = mysql.connector.connect(
                host="localhost",
                user="root",
                password="H8g6gRA2r/h$[t{6"
            )
            return self.connection
        except Error as e:
            print(f"Error connecting to MySQL: {e}")
            return None

    def create_test_database(self):
        """Create the test database if it doesn't exist."""
        if not self.use_test_db:
            print("Test database creation skipped (USE_TEST_DB not set)")
            return False

        conn = self.connect()
        if not conn:
            return False

        try:
            cursor = conn.cursor()

            # Create database
            cursor.execute(f"CREATE DATABASE IF NOT EXISTS {self.db_name}")
            print(f"✓ Test database '{self.db_name}' created/verified")

            # Switch to test database
            cursor.execute(f"USE {self.db_name}")

            # Create minimal schema (based on production needs)
            # This is a simplified version - extend as needed

            # Users table
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS users (
                    id VARCHAR(255) PRIMARY KEY,
                    email VARCHAR(255) UNIQUE,
                    password_hash VARCHAR(255),
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)

            # Games table (stores serialized player data)
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS games (
                    user_id VARCHAR(255) PRIMARY KEY,
                    player_data JSON,
                    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
                )
            """)

            conn.commit()
            print(f"✓ Test database schema created")

            return True

        except Error as e:
            print(f"Error creating test database: {e}")
            return False
        finally:
            if conn and conn.is_connected():
                cursor.close()
                conn.close()

    def drop_test_database(self):
        """Drop the test database (cleanup)."""
        if not self.use_test_db:
            return False

        conn = self.connect()
        if not conn:
            return False

        try:
            cursor = conn.cursor()
            cursor.execute(f"DROP DATABASE IF EXISTS {self.db_name}")
            conn.commit()
            print(f"✓ Test database '{self.db_name}' dropped")
            return True
        except Error as e:
            print(f"Error dropping test database: {e}")
            return False
        finally:
            if conn and conn.is_connected():
                cursor.close()
                conn.close()

    def clear_test_data(self):
        """Clear all data from test database without dropping it."""
        if not self.use_test_db:
            return False

        conn = self.connect()
        if not conn:
            return False

        try:
            cursor = conn.cursor()
            cursor.execute(f"USE {self.db_name}")

            # Clear tables
            cursor.execute("DELETE FROM games")
            cursor.execute("DELETE FROM users")

            conn.commit()
            print(f"✓ Test database '{self.db_name}' cleared")
            return True
        except Error as e:
            print(f"Error clearing test database: {e}")
            return False
        finally:
            if conn and conn.is_connected():
                cursor.close()
                conn.close()

    def seed_test_data(self):
        """Seed test database with sample data."""
        if not self.use_test_db:
            return False

        conn = self.connect()
        if not conn:
            return False

        try:
            cursor = conn.cursor()
            cursor.execute(f"USE {self.db_name}")

            # Add a test user
            cursor.execute("""
                INSERT INTO users (id, email, password_hash)
                VALUES ('test_user_1', 'test@example.com', 'hashed_password')
                ON DUPLICATE KEY UPDATE email=email
            """)

            conn.commit()
            print(f"✓ Test data seeded")
            return True
        except Error as e:
            print(f"Error seeding test data: {e}")
            return False
        finally:
            if conn and conn.is_connected():
                cursor.close()
                conn.close()


def setup_test_database():
    """
    Setup test database for integration tests.

    Call this before running integration tests that need database access.
    """
    manager = TestDatabaseManager()

    if manager.use_test_db:
        print("Setting up test database...")
        manager.create_test_database()
        manager.clear_test_data()
        manager.seed_test_data()
        print("Test database ready!")
    else:
        print("Using in-memory storage (no test database)")


def teardown_test_database():
    """
    Teardown test database after integration tests.

    Call this after running integration tests to clean up.
    """
    manager = TestDatabaseManager()

    if manager.use_test_db:
        print("Cleaning up test database...")
        manager.clear_test_data()
        print("Test database cleaned!")


def main():
    """
    Main entry point for test database management.

    Usage:
        python test_db_setup.py create  # Create test database
        python test_db_setup.py drop    # Drop test database
        python test_db_setup.py clear   # Clear test data
        python test_db_setup.py seed    # Seed test data
    """
    import sys

    if len(sys.argv) < 2:
        print("Usage: python test_db_setup.py {create|drop|clear|seed}")
        sys.exit(1)

    command = sys.argv[1]
    manager = TestDatabaseManager()
    manager.use_test_db = True  # Force test db for manual operations

    if command == 'create':
        manager.create_test_database()
    elif command == 'drop':
        manager.drop_test_database()
    elif command == 'clear':
        manager.clear_test_data()
    elif command == 'seed':
        manager.seed_test_data()
    else:
        print(f"Unknown command: {command}")
        sys.exit(1)


if __name__ == '__main__':
    main()