#!/usr/bin/env python3
"""
Mean Reversion Trading Bot
==========================
Bollinger Band Mean Reversion - Strategy che funziona!
"""

import pandas as pd
import numpy as np
from datetime import datetime
from pathlib import Path
import json
import requests
import time
import psycopg2
from psycopg2.extras import RealDictCursor

# Config
BINANCE_API = 'https://api.binance.com/api/v3'
DB_CONFIG = {
    'host': 'localhost',
    'port': 5432,
    'dbname': 'pippo',
    'user': 'pippo',
    'password': 'pippo_trading_2026'
}

# Strategy parameters (ottimizzati dall'analisi)
STRATEGY = {
    'bb_period': 20,
    'bb_std': 2.0,
    'position_size': 0.30,  # 30% del capitale per trade
    'stop_loss': 0.02,      # 2% stop loss
    'take_profit': 0.03,    # 3% take profit
    'check_interval': 60,   # Check ogni minuto
}


class MeanReversionBot:
    def __init__(self, trader_id, capital):
        self.trader_id = trader_id
        self.capital = capital
        self.position = 0  # 0=flat, 1=long, -1=short
        self.position_size = 0
        self.entry_price = 0
        self.prices = []

    def fetch_prices(self, limit=100):
        """Fetch recent prices from Binance."""
        try:
            resp = requests.get(f'{BINANCE_API}/klines', params={
                'symbol': 'BTCEUR',
                'interval': '1m',
                'limit': limit
            }, timeout=10)
            if resp.ok:
                data = resp.json()
                self.prices = [float(k[4]) for k in data]  # Close prices
                return True
        except Exception as e:
            print(f"Error fetching prices: {e}")
        return False

    def calculate_bb(self):
        """Calculate Bollinger Bands."""
        if len(self.prices) < STRATEGY['bb_period']:
            return None, None, None

        prices = np.array(self.prices[-STRATEGY['bb_period']:])
        sma = np.mean(prices)
        std = np.std(prices)

        upper = sma + STRATEGY['bb_std'] * std
        lower = sma - STRATEGY['bb_std'] * std

        return upper, sma, lower

    def get_signal(self):
        """Get trading signal."""
        if not self.fetch_prices():
            return None

        upper, mid, lower = self.calculate_bb()
        if upper is None:
            return None

        current_price = self.prices[-1]

        # Check exit conditions first
        if self.position != 0:
            if self.position == 1:  # Long
                pnl_pct = (current_price - self.entry_price) / self.entry_price
                # Exit: stop loss, take profit, or mean reversion complete
                if pnl_pct <= -STRATEGY['stop_loss']:
                    return {'action': 'EXIT', 'reason': 'stop_loss', 'price': current_price}
                elif pnl_pct >= STRATEGY['take_profit']:
                    return {'action': 'EXIT', 'reason': 'take_profit', 'price': current_price}
                elif current_price > mid:  # Mean reverted
                    return {'action': 'EXIT', 'reason': 'mean_reversion', 'price': current_price}
            else:  # Short
                pnl_pct = (self.entry_price - current_price) / self.entry_price
                if pnl_pct <= -STRATEGY['stop_loss']:
                    return {'action': 'EXIT', 'reason': 'stop_loss', 'price': current_price}
                elif pnl_pct >= STRATEGY['take_profit']:
                    return {'action': 'EXIT', 'reason': 'take_profit', 'price': current_price}
                elif current_price < mid:
                    return {'action': 'EXIT', 'reason': 'mean_reversion', 'price': current_price}

        # Entry conditions
        if self.position == 0:
            if current_price < lower:
                return {'action': 'BUY', 'price': current_price, 'bb_lower': lower}
            elif current_price > upper:
                return {'action': 'SELL', 'price': current_price, 'bb_upper': upper}

        return {'action': 'HOLD', 'price': current_price}

    def execute_entry(self, direction, price):
        """Execute entry trade."""
        trade_value = self.capital * STRATEGY['position_size']
        self.position_size = trade_value / price
        self.capital -= trade_value
        self.entry_price = price
        self.position = direction
        return trade_value

    def execute_exit(self, price):
        """Execute exit trade."""
        if self.position == 1:
            pnl = self.position_size * (price - self.entry_price)
        else:
            pnl = self.position_size * (self.entry_price - price)

        self.capital += self.position_size * self.entry_price + pnl
        self.position = 0
        self.position_size = 0
        self.entry_price = 0
        return pnl


def backtest(prices_df, initial_capital=5000):
    """Run backtest on historical data."""
    close = prices_df['close'].values
    n = len(close)

    capital = initial_capital
    position = 0
    entry_price = 0
    position_size = 0

    trades = []
    equity = []

    # Pre-calculate Bollinger Bands
    close_s = pd.Series(close)
    sma = close_s.rolling(STRATEGY['bb_period']).mean().values
    std = close_s.rolling(STRATEGY['bb_period']).std().values
    upper = sma + STRATEGY['bb_std'] * std
    lower = sma - STRATEGY['bb_std'] * std

    for i in range(STRATEGY['bb_period'] + 1, n):
        price = close[i]

        # Track equity
        if position != 0:
            if position == 1:
                curr_equity = capital + position_size * price
            else:
                unrealized = position_size * (entry_price - price)
                curr_equity = capital + position_size * entry_price + unrealized
        else:
            curr_equity = capital
        equity.append(curr_equity)

        # Exit logic
        if position != 0:
            if position == 1:
                pnl_pct = (price - entry_price) / entry_price
                exit_cond = (pnl_pct <= -STRATEGY['stop_loss'] or
                            pnl_pct >= STRATEGY['take_profit'] or
                            price > sma[i])
            else:
                pnl_pct = (entry_price - price) / entry_price
                exit_cond = (pnl_pct <= -STRATEGY['stop_loss'] or
                            pnl_pct >= STRATEGY['take_profit'] or
                            price < sma[i])

            if exit_cond:
                if position == 1:
                    pnl = position_size * (price - entry_price)
                else:
                    pnl = position_size * (entry_price - price)

                capital += position_size * entry_price + pnl
                trades.append({
                    'pnl': pnl,
                    'pnl_pct': pnl / (position_size * entry_price),
                    'direction': 'LONG' if position == 1 else 'SHORT'
                })
                position = 0
                position_size = 0
                continue

        # Entry logic
        if position == 0:
            if price < lower[i]:
                # Long entry
                trade_value = capital * STRATEGY['position_size']
                position_size = trade_value / price
                capital -= trade_value
                entry_price = price
                position = 1
            elif price > upper[i]:
                # Short entry
                trade_value = capital * STRATEGY['position_size']
                position_size = trade_value / price
                capital -= trade_value
                entry_price = price
                position = -1

    # Close open position
    if position != 0:
        price = close[-1]
        if position == 1:
            pnl = position_size * (price - entry_price)
        else:
            pnl = position_size * (entry_price - price)
        capital += position_size * entry_price + pnl
        trades.append({'pnl': pnl, 'pnl_pct': pnl / (position_size * entry_price), 'direction': 'LONG' if position == 1 else 'SHORT'})

    # Calculate metrics
    total_return = capital - initial_capital
    total_return_pct = total_return / initial_capital

    wins = [t for t in trades if t['pnl'] > 0]
    losses = [t for t in trades if t['pnl'] <= 0]
    win_rate = len(wins) / len(trades) if trades else 0

    # Max drawdown
    equity = np.array(equity)
    cummax = np.maximum.accumulate(equity)
    drawdown = (equity - cummax) / cummax
    max_dd = abs(min(drawdown)) if len(drawdown) > 0 else 0

    return {
        'total_return': total_return,
        'total_return_pct': total_return_pct,
        'final_capital': capital,
        'num_trades': len(trades),
        'win_rate': win_rate,
        'max_drawdown': max_dd,
        'avg_win': np.mean([t['pnl'] for t in wins]) if wins else 0,
        'avg_loss': np.mean([t['pnl'] for t in losses]) if losses else 0,
        'long_trades': sum(1 for t in trades if t['direction'] == 'LONG'),
        'short_trades': sum(1 for t in trades if t['direction'] == 'SHORT'),
    }


def main():
    print("="*60)
    print("MEAN REVERSION BACKTEST")
    print("="*60)

    # Load data
    DATA_DIR = Path('/var/www/html/pippo.cuttalo.com/data')
    dfs = []
    for f in sorted(DATA_DIR.glob('prices_BTC_EUR_*.csv')):
        df = pd.read_csv(f)
        if df['timestamp'].dtype == 'int64' or str(df['timestamp'].iloc[0]).isdigit():
            ts = pd.to_numeric(df['timestamp'])
            df['timestamp'] = pd.to_datetime(ts, unit='s' if ts.iloc[0] < 1e12 else 'ms')
        else:
            df['timestamp'] = pd.to_datetime(df['timestamp'])
        df = df[['timestamp', 'open', 'high', 'low', 'close', 'volume']]
        dfs.append(df)

    prices_df = pd.concat(dfs, ignore_index=True).sort_values('timestamp').drop_duplicates(subset='timestamp').reset_index(drop=True)
    print(f"Loaded {len(prices_df):,} candles")

    # Test different parameters
    print("\nTesting parameters...")

    best_result = None
    best_params = None

    for bb_std in [1.5, 2.0, 2.5]:
        for position_size in [0.2, 0.3, 0.4]:
            for stop_loss in [0.015, 0.02, 0.025]:
                for take_profit in [0.02, 0.03, 0.04]:
                    STRATEGY['bb_std'] = bb_std
                    STRATEGY['position_size'] = position_size
                    STRATEGY['stop_loss'] = stop_loss
                    STRATEGY['take_profit'] = take_profit

                    result = backtest(prices_df)

                    if result['num_trades'] >= 50:  # Minimum trades
                        score = result['total_return_pct'] * 0.5 + result['win_rate'] * 0.3 - result['max_drawdown'] * 0.2

                        if best_result is None or score > best_result['score']:
                            best_result = {**result, 'score': score}
                            best_params = {
                                'bb_std': bb_std,
                                'position_size': position_size,
                                'stop_loss': stop_loss,
                                'take_profit': take_profit
                            }

    print("\n" + "="*60)
    print("MIGLIOR CONFIGURAZIONE")
    print("="*60)
    print(f"\nParametri:")
    print(f"  BB Std: {best_params['bb_std']}")
    print(f"  Position Size: {best_params['position_size']:.0%}")
    print(f"  Stop Loss: {best_params['stop_loss']:.1%}")
    print(f"  Take Profit: {best_params['take_profit']:.1%}")

    print(f"\nRisultati (€5,000 capitale iniziale):")
    print(f"  Return: €{best_result['total_return']:.2f} ({best_result['total_return_pct']*100:+.2f}%)")
    print(f"  Capitale Finale: €{best_result['final_capital']:.2f}")
    print(f"  Trades: {best_result['num_trades']} (Long: {best_result['long_trades']}, Short: {best_result['short_trades']})")
    print(f"  Win Rate: {best_result['win_rate']*100:.1f}%")
    print(f"  Max Drawdown: {best_result['max_drawdown']*100:.1f}%")
    print(f"  Avg Win: €{best_result['avg_win']:.2f}")
    print(f"  Avg Loss: €{best_result['avg_loss']:.2f}")

    # Save config
    config = {
        'strategy': 'MEAN_REVERSION',
        'params': best_params,
        'metrics': {
            'total_return_pct': best_result['total_return_pct'],
            'win_rate': best_result['win_rate'],
            'max_drawdown': best_result['max_drawdown'],
            'num_trades': best_result['num_trades']
        }
    }

    RESULTS_DIR = Path('/var/www/html/pippo.cuttalo.com/optimization_results')
    with open(RESULTS_DIR / 'mean_reversion_config.json', 'w') as f:
        json.dump(config, f, indent=2)

    print(f"\nConfig salvata in {RESULTS_DIR / 'mean_reversion_config.json'}")


if __name__ == '__main__':
    main()