#!/usr/bin/env python3
"""
Analyze trading performance of MoE v12 vs MoERegression.

Usage:
    python analyze_architecture_performance.py
"""

import sys
sys.path.insert(0, '/home/ai/projects/AI_Strategy')

import pandas as pd
from db.connection import get_connection
from config import RISK_CONFIG

# Risk parameters (RR 1:2)
SL_MULT = 4.0
TP_MULT = 8.0

def analyze_moe_v12():
    """Analyze MoE v12 trades from trades_closed table."""
    query = """
    SELECT
        ticker,
        direction,
        entry_price,
        exit_price,
        entry_time,
        exit_time,
        close_reason,
        sl_price,
        tp_price,
        volume,
        pnl,
        pnl_pct
    FROM trades_closed
    ORDER BY entry_time DESC
    """

    with get_connection() as conn:
        df = pd.read_sql(query, conn)

    if df.empty:
        print("❌ No MoE v12 trades found in database")
        return None

    print(f"\n{'='*60}")
    print(f"MoE v12 Performance Analysis")
    print(f"{'='*60}")
    print(f"Total trades: {len(df)}")

    # Count by close reason
    print(f"\nClose reasons:")
    print(df['close_reason'].value_counts())

    # Win rate (TP first)
    tp_count = (df['close_reason'] == 'TP').sum()
    sl_count = (df['close_reason'] == 'SL').sum()
    wr = tp_count / len(df) if len(df) > 0 else 0
    print(f"\nWin Rate (TP first): {tp_count}/{len(df)} ({wr:.2%})")

    # PnL metrics
    total_pnl = df['pnl'].sum()
    avg_pnl = df['pnl'].mean()
    total_pnl_pct = df['pnl_pct'].sum()
    avg_pnl_pct = df['pnl_pct'].mean()
    avg_abs_pnl = df['pnl'].abs().mean()

    print(f"\nTotal PnL: {total_pnl:,.2f} RUB ({total_pnl_pct:.2%})")
    print(f"Avg PnL: {avg_pnl:,.2f} RUB ({avg_pnl_pct:.2%})")

    # Win/loss stats
    win_trades = df[df['pnl'] > 0]
    loss_trades = df[df['pnl'] <= 0]

    if len(win_trades) > 0:
        win_avg = win_trades['pnl'].mean()
        win_max = win_trades['pnl'].max()
        print(f"Win trades: {len(win_trades)} | Avg PnL: {win_avg:,.2f} | Max: {win_max:,.2f}")

    if len(loss_trades) > 0:
        loss_avg = loss_trades['pnl'].mean()
        loss_min = loss_trades['pnl'].min()
        print(f"Loss trades: {len(loss_trades)} | Avg PnL: {loss_avg:,.2f} | Min: {loss_min:,.2f}")

    # Directional stats
    long_trades = df[df['direction'] == 'LONG']
    short_trades = df[df['direction'] == 'SHORT']

    if len(long_trades) > 0:
        long_wr = (long_trades['close_reason'] == 'TP').sum() / len(long_trades)
        print(f"\nLong trades: {len(long_trades)} | WR: {long_wr:.2%}")

    if len(short_trades) > 0:
        short_wr = (short_trades['close_reason'] == 'TP').sum() / len(short_trades)
        print(f"Short trades: {len(short_trades)} | WR: {short_wr:.2%}")

    # Position duration
    df['duration_hours'] = (df['exit_time'] - df['entry_time']) / 3600
    avg_duration = df['duration_hours'].mean()
    print(f"\nAvg position duration: {avg_duration:.2f} hours")

    # Monthly performance
    df['entry_month'] = pd.to_datetime(df['entry_time'], unit='s').dt.to_period('M')
    monthly = df.groupby('entry_month')['pnl'].sum()
    print(f"\nMonthly PnL (last 6):")
    for month, pnl in monthly.tail(6).items():
        print(f"  {month}: {pnl:,.2f} RUB")

    # Ticker stats
    ticker_stats = df.groupby('ticker').agg({
        'pnl': ['sum', 'count', lambda x: (x > 0).sum()],
        'pnl_pct': 'sum'
    })
    ticker_stats.columns = ['Total PnL', 'Count', 'Wins', 'Total PnL %']
    ticker_stats = ticker_stats.sort_values('Total PnL', ascending=False)
    print(f"\nPer-ticker performance:")
    print(ticker_stats.head(6))

    return df, {
        'total_trades': len(df),
        'win_rate': wr,
        'total_pnl': total_pnl,
        'total_pnl_pct': total_pnl_pct,
        'avg_pnl': avg_pnl,
        'avg_pnl_pct': avg_pnl_pct,
        'avg_abs_pnl': avg_abs_pnl
    }


def analyze_moe_regression():
    """Analyze MoERegression trades from trades_closed_regression table."""
    query = """
    SELECT
        ticker,
        direction,
        entry_price,
        exit_price,
        entry_time,
        exit_time,
        close_reason,
        sl_price,
        tp_price,
        volume,
        pnl,
        pnl_pct
    FROM trades_closed_regression
    ORDER BY entry_time DESC
    """

    with get_connection() as conn:
        df = pd.read_sql(query, conn)

    if df.empty:
        print("❌ No MoERegression trades found in database")
        return None

    print(f"\n{'='*60}")
    print(f"MoERegression Performance Analysis")
    print(f"{'='*60}")
    print(f"Total trades: {len(df)}")

    # Count by close reason
    print(f"\nClose reasons:")
    print(df['close_reason'].value_counts())

    # Win rate (TP first)
    tp_count = (df['close_reason'] == 'TP').sum()
    sl_count = (df['close_reason'] == 'SL').sum()
    wr = tp_count / len(df) if len(df) > 0 else 0
    print(f"\nWin Rate (TP first): {tp_count}/{len(df)} ({wr:.2%})")

    # PnL metrics
    total_pnl = df['pnl'].sum()
    avg_pnl = df['pnl'].mean()
    total_pnl_pct = df['pnl_pct'].sum()
    avg_pnl_pct = df['pnl_pct'].mean()
    avg_abs_pnl = df['pnl'].abs().mean()

    print(f"\nTotal PnL: {total_pnl:,.2f} RUB ({total_pnl_pct:.2%})")
    print(f"Avg PnL: {avg_pnl:,.2f} RUB ({avg_pnl_pct:.2%})")

    # Win/loss stats
    win_trades = df[df['pnl'] > 0]
    loss_trades = df[df['pnl'] <= 0]

    if len(win_trades) > 0:
        win_avg = win_trades['pnl'].mean()
        win_max = win_trades['pnl'].max()
        print(f"\nWin trades: {len(win_trades)} | Avg PnL: {win_avg:,.2f} | Max: {win_max:,.2f}")

    if len(loss_trades) > 0:
        loss_avg = loss_trades['pnl'].mean()
        loss_min = loss_trades['pnl'].min()
        print(f"Loss trades: {len(loss_trades)} | Avg PnL: {loss_avg:,.2f} | Min: {loss_min:,.2f}")

    # Directional stats
    long_trades = df[df['direction'] == 'LONG']
    short_trades = df[df['direction'] == 'SHORT']

    if len(long_trades) > 0:
        long_wr = (long_trades['close_reason'] == 'TP').sum() / len(long_trades)
        print(f"\nLong trades: {len(long_trades)} | WR: {long_wr:.2%}")

    if len(short_trades) > 0:
        short_wr = (short_trades['close_reason'] == 'TP').sum() / len(short_trades)
        print(f"Short trades: {len(short_trades)} | WR: {short_wr:.2%}")

    # Position duration
    df['duration_hours'] = (df['exit_time'] - df['entry_time']) / 3600
    avg_duration = df['duration_hours'].mean()
    print(f"\nAvg position duration: {avg_duration:.2f} hours")

    # Monthly performance
    df['entry_month'] = pd.to_datetime(df['entry_time'], unit='s').dt.to_period('M')
    monthly = df.groupby('entry_month')['pnl'].sum()
    print(f"\nMonthly PnL (last 6):")
    for month, pnl in monthly.tail(6).items():
        print(f"  {month}: {pnl:,.2f} RUB")

    # Ticker stats
    ticker_stats = df.groupby('ticker').agg({
        'pnl': ['sum', 'count', lambda x: (x > 0).sum()],
        'pnl_pct': 'sum'
    })
    ticker_stats.columns = ['Total PnL', 'Count', 'Wins', 'Total PnL %']
    ticker_stats = ticker_stats.sort_values('Total PnL', ascending=False)
    print(f"\nPer-ticker performance:")
    print(ticker_stats.head(6))

    return df, {
        'total_trades': len(df),
        'win_rate': wr,
        'total_pnl': total_pnl,
        'total_pnl_pct': total_pnl_pct,
        'avg_pnl': avg_pnl,
        'avg_pnl_pct': avg_pnl_pct,
        'avg_abs_pnl': avg_abs_pnl
    }


def compare_architectures(moe_df, moe_stats, reg_df, reg_stats):
    """Compare MoE v12 vs MoERegression performance."""
    if moe_df is None or reg_df is None:
        print("\n❌ Cannot compare: missing data")
        return

    print(f"\n{'='*80}")
    print(f"ARCHITECTURE COMPARISON")
    print(f"{'='*80}")

    # Metrics comparison
    metrics = {
        'Total Trades': moe_stats['total_trades'],
        'MoE WR': moe_stats['win_rate'],
        'MoE Total PnL': moe_stats['total_pnl'],
        'MoE Total PnL %': moe_stats['total_pnl_pct'],
        'MoE Avg PnL': moe_stats['avg_pnl'],
        'MoE Avg PnL %': moe_stats['avg_pnl_pct'],
        'Reg Trades': reg_stats['total_trades'],
        'Reg WR': reg_stats['win_rate'],
        'Reg Total PnL': reg_stats['total_pnl'],
        'Reg Total PnL %': reg_stats['total_pnl_pct'],
        'Reg Avg PnL': reg_stats['avg_pnl'],
        'Reg Avg PnL %': reg_stats['avg_pnl_pct'],
    }

    for metric, moe_val, reg_val in [
        ('Total Trades', moe_stats['total_trades'], reg_stats['total_trades']),
        ('Win Rate', moe_stats['win_rate'], reg_stats['win_rate']),
        ('Total PnL', moe_stats['total_pnl'], reg_stats['total_pnl']),
        ('Total PnL %', moe_stats['total_pnl_pct'], reg_stats['total_pnl_pct']),
        ('Avg PnL', moe_stats['avg_pnl'], reg_stats['avg_pnl']),
        ('Avg PnL %', moe_stats['avg_pnl_pct'], reg_stats['avg_pnl_pct']),
    ]:
        diff = reg_val - moe_val
        diff_pct = (diff / moe_val * 100) if moe_val != 0 else float('inf')
        arrow = '↑' if diff > 0 else '↓' if diff < 0 else '→'
        print(f"{metric:20s}: MoE={moe_val:12,.2f} | Reg={reg_val:12,.2f} {arrow} ({diff_pct:+.1f}%)")

    # Which is better?
    print(f"\n{'='*80}")
    print("BENCHMARKING")
    print(f"{'='*80}")

    # Benchmark: 50 trades, 40% WR, 2:1 payoff (RR 1:2)
    benchmark_trades = 50
    benchmark_wr = 0.40
    benchmark_payout = 2.0

    # MoE benchmark
    moe_expected_pnl = moe_stats['total_trades'] * benchmark_payout * moe_stats['avg_pnl']
    moe_implied_wr = moe_stats['avg_pnl'] / (benchmark_payout * moe_stats['avg_abs_pnl'] if moe_stats['avg_abs_pnl'] > 0 else 1)

    # Reg benchmark
    reg_expected_pnl = reg_stats['total_trades'] * benchmark_payout * reg_stats['avg_pnl']
    reg_implied_wr = reg_stats['avg_pnl'] / (benchmark_payout * reg_stats['avg_abs_pnl'] if reg_stats['avg_abs_pnl'] > 0 else 1)

    print(f"\nBenchmark: {benchmark_trades} trades, {benchmark_wr:.0%} WR, {benchmark_payout}:1 payoff (RR 1:2)")
    print(f"\nMoE v12: {moe_stats['total_trades']} trades @ {moe_stats['win_rate']:.0%} WR → expected PnL: {moe_expected_pnl:,.2f}")
    print(f"Reg: {reg_stats['total_trades']} trades @ {reg_stats['win_rate']:.0%} WR → expected PnL: {reg_expected_pnl:,.2f}")

    # Conclusion
    print(f"\n{'='*80}")
    print("CONCLUSION")
    print(f"{'='*80}")

    # Compare by key metrics
    better_win_rate = "MoE v12" if moe_stats['win_rate'] > reg_stats['win_rate'] else "Reg" if reg_stats['win_rate'] > moe_stats['win_rate'] else "Equal"
    better_pnl = "MoE v12" if moe_stats['total_pnl'] > reg_stats['total_pnl'] else "Reg" if reg_stats['total_pnl'] > moe_stats['total_pnl'] else "Equal"

    print(f"1. Win Rate:       {'MoE v12 ✅' if moe_stats['win_rate'] > reg_stats['win_rate'] else 'Reg ✅' if reg_stats['win_rate'] > moe_stats['win_rate'] else 'Equal'}")
    print(f"2. Total PnL:      {'MoE v12 ✅' if moe_stats['total_pnl'] > reg_stats['total_pnl'] else 'Reg ✅' if reg_stats['total_pnl'] > moe_stats['total_pnl'] else 'Equal'}")
    print(f"3. Avg PnL per trade: {'MoE v12 ✅' if moe_stats['avg_pnl'] > reg_stats['avg_pnl'] else 'Reg ✅' if reg_stats['avg_pnl'] > moe_stats['avg_pnl'] else 'Equal'}")
    print(f"4. Total PnL %:     {'MoE v12 ✅' if moe_stats['total_pnl_pct'] > reg_stats['total_pnl_pct'] else 'Reg ✅' if reg_stats['total_pnl_pct'] > moe_stats['total_pnl_pct'] else 'Equal'}")

    print(f"\n🏆 Recommended architecture: {better_pnl}")
    print(f"   - Win rate {'higher' if better_pnl == moe_stats['total_pnl'] > reg_stats['total_pnl'] else 'higher' if better_pnl == reg_stats['total_pnl'] > moe_stats['total_pnl'] else 'equal'}")
    print(f"   - Total PnL {'higher' if better_pnl == moe_stats['total_pnl'] > reg_stats['total_pnl'] else 'higher' if better_pnl == reg_stats['total_pnl'] > moe_stats['total_pnl'] else 'equal'}")

    # Suggestion
    print(f"\n💡 Suggestion:")
    if moe_stats['total_pnl'] > reg_stats['total_pnl']:
        print(f"   • Continue using MoE v12 as primary architecture")
        print(f"   • Consider fine-tuning MoERegression for specific markets")
    elif reg_stats['total_pnl'] > moe_stats['total_pnl']:
        print(f"   • Consider prioritizing MoERegression for specific tickers")
        print(f"   • MoE v12 may benefit from additional context features")
    else:
        print(f"   • Both architectures perform similarly")
        print(f"   • Use MoE v12 for general trading, MoERegression for consolidation patterns")


if __name__ == '__main__':
    print("📊 Architecture Performance Analysis")
    print("====================================")

    moe_df, moe_stats = analyze_moe_v12()
    reg_df, reg_stats = analyze_moe_regression()

    compare_architectures(moe_df, moe_stats, reg_df, reg_stats)

    print(f"\n{'='*80}")
    print("✅ Analysis complete")
    print(f"{'='*80}")