"""
Système de tracking de performance des trades pour amélioration continue
Stocke chaque trade avec ses conditions d'entrée et son résultat
"""

import sqlite3
import logging
import os
from datetime import datetime, timedelta
from typing import Dict, List, Optional
from threading import Lock

class TradePerformanceDB:
    """Base de données SQLite pour tracker la performance des trades"""

    def __init__(self, db_path: str = "data/trade_performance.db"):
        self.db_path = db_path
        self.lock = Lock()
        self.logger = logging.getLogger("TradePerformanceDB")

        # Créer le dossier si nécessaire
        os.makedirs(os.path.dirname(db_path), exist_ok=True)

        # Initialiser la base
        self._init_database()

    def _init_database(self):
        """Crée les tables si elles n'existent pas"""
        with self.lock:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()

            # Table des trades
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS trades (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    symbol TEXT NOT NULL,
                    side TEXT NOT NULL,
                    entry_price REAL NOT NULL,
                    exit_price REAL,
                    size REAL NOT NULL,
                    leverage INTEGER NOT NULL,

                    -- Conditions d'entrée
                    regime TEXT,
                    rsi REAL,
                    macd_signal TEXT,
                    trend_score REAL,
                    momentum_score REAL,
                    volatility_score REAL,
                    volume_score REAL,
                    signal_score REAL,
                    quality_score REAL,

                    -- Résultat
                    win INTEGER,
                    pnl_pct REAL,
                    pnl_usd REAL,
                    duration_minutes INTEGER,
                    close_reason TEXT,

                    -- Timestamps
                    entry_time TIMESTAMP NOT NULL,
                    exit_time TIMESTAMP,

                    UNIQUE(symbol, side, entry_time)
                )
            """)

            # Index pour queries rapides
            cursor.execute("""
                CREATE INDEX IF NOT EXISTS idx_regime
                ON trades(regime)
            """)

            cursor.execute("""
                CREATE INDEX IF NOT EXISTS idx_symbol_side
                ON trades(symbol, side)
            """)

            cursor.execute("""
                CREATE INDEX IF NOT EXISTS idx_entry_time
                ON trades(entry_time)
            """)

            conn.commit()
            conn.close()

            self.logger.info(f"Base de données initialisée: {self.db_path}")

    def log_trade_open(self, trade_data: Dict) -> bool:
        """
        Log l'ouverture d'un trade

        Args:
            trade_data: {
                'symbol': 'BTC',
                'side': 'LONG',
                'entry_price': 64000,
                'size': 0.01,
                'leverage': 10,
                'regime': 'BEAR_VOLATILE',
                'rsi': 35,
                'macd_signal': 'bullish',
                'trend_score': 0.6,
                'momentum_score': 0.7,
                'volatility_score': 0.5,
                'volume_score': 0.8,
                'signal_score': 0.45,
                'quality_score': 0.65
            }
        """
        try:
            with self.lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()

                cursor.execute("""
                    INSERT OR IGNORE INTO trades (
                        symbol, side, entry_price, size, leverage,
                        regime, rsi, macd_signal, trend_score, momentum_score,
                        volatility_score, volume_score, signal_score, quality_score,
                        entry_time
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                    trade_data['symbol'],
                    trade_data['side'],
                    trade_data['entry_price'],
                    trade_data['size'],
                    trade_data['leverage'],
                    trade_data.get('regime', 'UNKNOWN'),
                    trade_data.get('rsi'),
                    trade_data.get('macd_signal'),
                    trade_data.get('trend_score'),
                    trade_data.get('momentum_score'),
                    trade_data.get('volatility_score'),
                    trade_data.get('volume_score'),
                    trade_data.get('signal_score'),
                    trade_data.get('quality_score'),
                    datetime.now()
                ))

                conn.commit()
                conn.close()

                self.logger.info(f"Trade ouvert loggé: {trade_data['symbol']} {trade_data['side']}")
                return True

        except Exception as e:
            self.logger.error(f"Erreur log trade open: {e}")
            return False

    def log_trade_close(self, symbol: str, side: str, exit_price: float,
                       pnl_pct: float, pnl_usd: float, close_reason: str) -> bool:
        """
        Log la fermeture d'un trade

        Args:
            symbol: Symbole du trade
            side: LONG ou SHORT
            exit_price: Prix de sortie
            pnl_pct: PnL en pourcentage
            pnl_usd: PnL en USD
            close_reason: Raison de fermeture (STOP_LOSS, TAKE_PROFIT, etc.)
        """
        try:
            with self.lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()

                # Trouver le dernier trade ouvert pour ce symbole/side
                cursor.execute("""
                    SELECT id, entry_time, entry_price
                    FROM trades
                    WHERE symbol = ? AND side = ? AND exit_time IS NULL
                    ORDER BY entry_time DESC
                    LIMIT 1
                """, (symbol, side))

                result = cursor.fetchone()

                if not result:
                    self.logger.warning(f"Aucun trade ouvert trouvé pour {symbol} {side}")
                    conn.close()
                    return False

                trade_id, entry_time, entry_price = result
                exit_time = datetime.now()

                # Calculer la durée
                entry_dt = datetime.fromisoformat(entry_time)
                duration_minutes = int((exit_time - entry_dt).total_seconds() / 60)

                # Déterminer win/loss
                win = 1 if pnl_pct > 0 else 0

                # Update le trade
                cursor.execute("""
                    UPDATE trades
                    SET exit_price = ?,
                        win = ?,
                        pnl_pct = ?,
                        pnl_usd = ?,
                        duration_minutes = ?,
                        close_reason = ?,
                        exit_time = ?
                    WHERE id = ?
                """, (
                    exit_price,
                    win,
                    pnl_pct,
                    pnl_usd,
                    duration_minutes,
                    close_reason,
                    exit_time,
                    trade_id
                ))

                conn.commit()
                conn.close()

                self.logger.info(f"Trade fermé loggé: {symbol} {side} - PnL: {pnl_pct:.2f}%")
                return True

        except Exception as e:
            self.logger.error(f"Erreur log trade close: {e}")
            return False

    def get_pattern_performance(self, regime: str = None, side: str = None,
                                days: int = 7) -> Dict:
        """
        Récupère les performances par pattern

        Args:
            regime: Filtrer par régime (BULL_STABLE, BEAR_VOLATILE, etc.)
            side: Filtrer par side (LONG, SHORT)
            days: Nombre de jours d'historique

        Returns:
            {
                'win_rate': 0.65,
                'avg_pnl_pct': 2.3,
                'avg_pnl_winners': 3.5,
                'avg_pnl_losers': -1.8,
                'total_trades': 150,
                'avg_duration_minutes': 45
            }
        """
        try:
            with self.lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()

                # Construire la query
                query = """
                    SELECT
                        AVG(CASE WHEN win = 1 THEN 1.0 ELSE 0.0 END) as win_rate,
                        AVG(pnl_pct) as avg_pnl_pct,
                        AVG(CASE WHEN win = 1 THEN pnl_pct END) as avg_pnl_winners,
                        AVG(CASE WHEN win = 0 THEN pnl_pct END) as avg_pnl_losers,
                        COUNT(*) as total_trades,
                        AVG(duration_minutes) as avg_duration
                    FROM trades
                    WHERE exit_time IS NOT NULL
                    AND entry_time > datetime('now', '-' || ? || ' days')
                """

                params = [days]

                if regime:
                    query += " AND regime = ?"
                    params.append(regime)

                if side:
                    query += " AND side = ?"
                    params.append(side)

                cursor.execute(query, params)
                result = cursor.fetchone()

                conn.close()

                if result and result[4] > 0:  # total_trades > 0
                    return {
                        'win_rate': result[0] or 0.0,
                        'avg_pnl_pct': result[1] or 0.0,
                        'avg_pnl_winners': result[2] or 0.0,
                        'avg_pnl_losers': result[3] or 0.0,
                        'total_trades': int(result[4] or 0),
                        'avg_duration_minutes': int(result[5] or 0)
                    }
                else:
                    return {
                        'win_rate': 0.5,  # Neutre par défaut
                        'avg_pnl_pct': 0.0,
                        'avg_pnl_winners': 0.0,
                        'avg_pnl_losers': 0.0,
                        'total_trades': 0,
                        'avg_duration_minutes': 0
                    }

        except Exception as e:
            self.logger.error(f"Erreur get pattern performance: {e}")
            return {'win_rate': 0.5, 'total_trades': 0}

    def get_symbol_performance(self, symbol: str, days: int = 7) -> Dict:
        """Récupère les performances pour un symbole spécifique"""
        try:
            with self.lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()

                cursor.execute("""
                    SELECT
                        AVG(CASE WHEN win = 1 THEN 1.0 ELSE 0.0 END) as win_rate,
                        AVG(pnl_pct) as avg_pnl,
                        COUNT(*) as total_trades
                    FROM trades
                    WHERE symbol = ?
                    AND exit_time IS NOT NULL
                    AND entry_time > datetime('now', '-' || ? || ' days')
                """, (symbol, days))

                result = cursor.fetchone()
                conn.close()

                if result and result[2] > 0:
                    return {
                        'win_rate': result[0] or 0.5,
                        'avg_pnl': result[1] or 0.0,
                        'total_trades': int(result[2] or 0)
                    }
                else:
                    return {'win_rate': 0.5, 'total_trades': 0}

        except Exception as e:
            self.logger.error(f"Erreur get symbol performance: {e}")
            return {'win_rate': 0.5, 'total_trades': 0}

    def get_regime_statistics(self, days: int = 7) -> List[Dict]:
        """Récupère les stats par régime de marché"""
        try:
            with self.lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()

                cursor.execute("""
                    SELECT
                        regime,
                        COUNT(*) as trades,
                        AVG(CASE WHEN win = 1 THEN 1.0 ELSE 0.0 END) as win_rate,
                        AVG(pnl_pct) as avg_pnl
                    FROM trades
                    WHERE exit_time IS NOT NULL
                    AND entry_time > datetime('now', '-' || ? || ' days')
                    GROUP BY regime
                    ORDER BY trades DESC
                """, (days,))

                results = cursor.fetchall()
                conn.close()

                return [
                    {
                        'regime': r[0],
                        'trades': int(r[1]),
                        'win_rate': r[2] or 0.0,
                        'avg_pnl': r[3] or 0.0
                    }
                    for r in results
                ]

        except Exception as e:
            self.logger.error(f"Erreur get regime statistics: {e}")
            return []

    def cleanup_old_data(self, days: int = 30):
        """Nettoie les trades de plus de X jours"""
        try:
            with self.lock:
                conn = sqlite3.connect(self.db_path)
                cursor = conn.cursor()

                cursor.execute("""
                    DELETE FROM trades
                    WHERE entry_time < datetime('now', '-' || ? || ' days')
                """, (days,))

                deleted = cursor.rowcount
                conn.commit()
                conn.close()

                if deleted > 0:
                    self.logger.info(f"Nettoyage: {deleted} trades supprimés (>{days} jours)")

                return deleted

        except Exception as e:
            self.logger.error(f"Erreur cleanup: {e}")
            return 0


# Instance globale
_db_instance = None

def get_performance_db() -> TradePerformanceDB:
    """Récupère l'instance singleton de la DB"""
    global _db_instance
    if _db_instance is None:
        _db_instance = TradePerformanceDB()
    return _db_instance