"""
Environment-backed TiDB helper for the E2E API suite.

This module used to provision TiDB Cloud Zero instances. The current flow no
longer creates databases dynamically; it reads the existing DB connection from
environment variables and exposes a small compatibility layer for older tools.
"""

import mysql.connector
from typing import Dict, Any

import config


class TiDBManager:
    """Uses the configured TiDB connection instead of provisioning a new DB."""

    def __init__(self):
        self.instance_id = None
        self.connection_info = {
            "host": config.TIDB_HOST,
            "port": config.TIDB_PORT,
            "user": config.TIDB_USER,
            "password": config.TIDB_PASSWORD,
            "database": config.TIDB_DATABASE,
        }

    def create_instance(self, tag: str = "nexus-e2e") -> Dict[str, Any]:
        """
        Return the configured connection info.

        The `tag` argument is kept only for backward compatibility with the old
        Zero-oriented interface.
        """
        _ = tag
        return dict(self.connection_info)

    def wait_for_ready(self, timeout: int = 300, poll_interval: int = 10) -> bool:
        """Check whether the configured TiDB endpoint is connectable."""
        _ = timeout
        _ = poll_interval
        try:
            conn = mysql.connector.connect(
                host=self.connection_info["host"],
                port=self.connection_info["port"],
                user=self.connection_info["user"],
                password=self.connection_info["password"],
            )
            conn.close()
            return True
        except mysql.connector.Error:
            return False

    def create_database(self, name: str) -> bool:
        """Create a database if it does not exist."""
        conn = mysql.connector.connect(
            host=self.connection_info["host"],
            port=self.connection_info["port"],
            user=self.connection_info["user"],
            password=self.connection_info["password"],
        )
        cursor = conn.cursor()
        try:
            cursor.execute(f"CREATE DATABASE IF NOT EXISTS `{name}`")
            conn.commit()
            self.connection_info["database"] = name
            return True
        finally:
            cursor.close()
            conn.close()

    def destroy_instance(self, instance_id: str = None) -> bool:
        """No-op retained for backward compatibility."""
        _ = instance_id
        return True

    def execute_query(self, query: str, database: str = None) -> list:
        """Execute a SELECT query and return results. Retries on connection errors."""
        max_retries = 3
        retry_delay = 5
        
        for attempt in range(max_retries):
            try:
                conn = mysql.connector.connect(
                    host=self.connection_info["host"],
                    port=self.connection_info["port"],
                    user=self.connection_info["user"],
                    password=self.connection_info["password"],
                    database=database or self.connection_info.get("database"),
                    connection_timeout=300,
                    autocommit=False,
                    pool_name="e2e-api-pool",
                    pool_size=5,
                    # Increase connection timeout values
                    connect_timeout=300,
                    read_timeout=300,
                    write_timeout=300,
                    # Enable SSL connection if required by TiDB Cloud
                    ssl_disabled=False,
                    ssl_verify_cert=True,
                    ssl_ca=None,
                    # Additional connection parameters for stability
                    raise_on_warnings=False,
                    use_pure=True
                )
                
                cursor = conn.cursor(dictionary=True)
                try:
                    cursor.execute(query)
                    # For update/insert/delete queries, commit the changes and return affected rows
                    if query.strip().upper().startswith(("UPDATE", "INSERT", "DELETE")):
                        conn.commit()
                        return cursor.rowcount
                    # For select queries, return results
                    return cursor.fetchall()
                finally:
                    cursor.close()
                    conn.close()
                    
            except Exception as e:
                print(f"Database connection attempt {attempt+1}/{max_retries} failed: {e}")
                if attempt < max_retries - 1:
                    import time
                    print(f"Retrying in {retry_delay} seconds...")
                    time.sleep(retry_delay)
                    retry_delay *= 2  # Exponential backoff
                else:
                    print("Max retries reached. Re-raising exception...")
                    raise e


# Backward-compatible alias for older imports.
TiDBZeroManager = TiDBManager


_manager = None


def get_manager() -> TiDBManager:
    """Get or create the global TiDB manager instance."""
    global _manager
    if _manager is None:
        _manager = TiDBManager()
    return _manager


def reset_manager():
    """Reset the global manager."""
    global _manager
    _manager = None