import mysql.connector
from mysql.connector import Error, pooling
from contextlib import contextmanager
from typing import Optional, List
import pandas as pd
import logging
import time
from config import DBConfig

logger = logging.getLogger(__name__)

class DatabaseManager:
    def __init__(self, config: DBConfig, pool_size: Optional[int] = None):
        self.config = config
        self.pool_size = pool_size or config.pool_size
        self.pool = None
        self._create_pool()

    def _create_pool(self):
        try:
            self.pool = pooling.MySQLConnectionPool(
                pool_name="trading_pool",
                pool_size=self.pool_size,
                host=self.config.host,
                port=self.config.port,
                database=self.config.database,
                user=self.config.user,
                password=self.config.password,
                charset=self.config.charset,
                use_unicode=True,
                autocommit=True
            )
            logger.info(f"✅ Пул соединений создан: {self.pool_size}")
        except Error as e:
            logger.error(f"❌ Ошибка пула: {e}")
            raise

    @contextmanager
    def get_connection(self):
        conn = self.pool.get_connection()
        try:
            yield conn
        finally:
            try:
                conn.close()
            except Exception:
                pass

    def get_table_name(self, instrument: str, timeframe: str) -> str:
        return f"{instrument.upper()}_{timeframe}"

    def fetch_ohlc_data(
        self, instrument: str, timeframe: str, 
        limit: int = 5000, start_timestamp: Optional[int] = None
    ) -> pd.DataFrame:
        table = self.get_table_name(instrument, timeframe)
        query = f"""
            SELECT timestamp, Date, Time, Open, High, Low, Close, Volume
            FROM {table}
            {'WHERE timestamp >= %s' if start_timestamp else ''}
            ORDER BY timestamp ASC
            LIMIT %s
        """
        params = [start_timestamp, limit] if start_timestamp else [limit]
        
        try:
            with self.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(query, params)
                columns = [desc[0] for desc in cursor.description]
                rows = cursor.fetchall()
                cursor.close()
                
                df = pd.DataFrame(rows, columns=columns)
                if df.empty:
                    return df
                
                for col in ['Open', 'High', 'Low', 'Close', 'Volume']:
                    if col in df.columns:
                        df[col] = pd.to_numeric(df[col], errors='coerce')
                df['timestamp'] = pd.to_numeric(df['timestamp'], errors='coerce')
                df['range'] = df['High'] - df['Low']
                df['body'] = (df['Close'] - df['Open']).abs()
                
                return df
        except Error as e:
            logger.error(f"❌ Ошибка загрузки {table}: {e}")
            return pd.DataFrame()

    def get_available_instruments(self) -> List[str]:
        # Поддержка тикеров с цифрами и подчеркиваниями
        query = """
            SELECT DISTINCT TABLE_NAME FROM information_schema.TABLES 
            WHERE TABLE_SCHEMA = %s AND TABLE_NAME REGEXP '^[A-Z0-9_]+_(H1|D1|W1)$'
        """
        try:
            with self.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(query, (self.config.database,))
                tables = [row[0] for row in cursor.fetchall()]
                cursor.close()
                instruments = sorted(list(set(t.rsplit('_', 1)[0] for t in tables)))
                return instruments
        except Error as e:
            logger.error(f"❌ Ошибка получения инструментов: {e}")
            return []