from db import get_connection
import math

conn = get_connection()
cursor = conn.cursor(dictionary=True)
cursor.execute('''
    SELECT t.return_pct,
           b.revenue_growth_yoy, b.eps_growth_yoy, b.fcf_growth_yoy, b.roic,
           b.margin_expansion_yoy, b.net_debt_ebitda,
           b.price_at_snapshot, b.ma50, b.ma200, b.rs_vs_sp500_3m, b.week_high_52, b.volume_trend,
           b.catalyst_upgrades_count, b.catalyst_insider_buy_count
    FROM trade_simulation_results t
    JOIN backtest_results_v2 b
      ON b.company_id = t.company_id
     AND b.fundamentals_period = t.fundamentals_period
     AND b.snapshot_date = t.entry_date
    WHERE t.fundamentals_period = 'annual'
''')
rows = cursor.fetchall()
cursor.close()
conn.close()

print(f'Total operaciones analizadas: {len(rows)}')

data = []
for r in rows:
    if r['price_at_snapshot'] is None or r['ma50'] is None or r['ma200'] is None or r['week_high_52'] is None:
        continue
    price = float(r['price_at_snapshot'])
    d = {
        'return_pct': float(r['return_pct']),
        'revenue_growth_yoy': float(r['revenue_growth_yoy']) if r['revenue_growth_yoy'] is not None else None,
        'eps_growth_yoy': float(r['eps_growth_yoy']) if r['eps_growth_yoy'] is not None else None,
        'fcf_growth_yoy': float(r['fcf_growth_yoy']) if r['fcf_growth_yoy'] is not None else None,
        'roic': float(r['roic']) if r['roic'] is not None else None,
        'margin_expansion_yoy': float(r['margin_expansion_yoy']) if r['margin_expansion_yoy'] is not None else None,
        'net_debt_ebitda': float(r['net_debt_ebitda']) if r['net_debt_ebitda'] is not None else None,
        'pct_vs_ma50': (price - float(r['ma50'])) / float(r['ma50']) * 100,
        'pct_vs_ma200': (price - float(r['ma200'])) / float(r['ma200']) * 100,
        'rs_vs_sp500_3m': float(r['rs_vs_sp500_3m']) if r['rs_vs_sp500_3m'] is not None else None,
        'pct_of_52w_high': price / float(r['week_high_52']) * 100,
        'volume_trend': float(r['volume_trend']) if r['volume_trend'] is not None else None,
        'catalyst_upgrades_count': float(r['catalyst_upgrades_count']) if r['catalyst_upgrades_count'] is not None else 0,
        'catalyst_insider_buy_count': float(r['catalyst_insider_buy_count']) if r['catalyst_insider_buy_count'] is not None else 0,
    }
    data.append(d)

print(f'Operaciones con datos completos: {len(data)}')
winners = [d for d in data if d['return_pct'] > 0]
losers = [d for d in data if d['return_pct'] <= 0]
print(f'Ganadoras: {len(winners)} | Perdedoras: {len(losers)}')
print()

def pearson(xs, ys):
    n = len(xs)
    mx = sum(xs)/n
    my = sum(ys)/n
    cov = sum((x-mx)*(y-my) for x,y in zip(xs,ys))
    sx = math.sqrt(sum((x-mx)**2 for x in xs))
    sy = math.sqrt(sum((y-my)**2 for y in ys))
    return cov/(sx*sy) if sx>0 and sy>0 else 0

indicators = ['revenue_growth_yoy','eps_growth_yoy','fcf_growth_yoy','roic','margin_expansion_yoy',
              'net_debt_ebitda','pct_vs_ma50','pct_vs_ma200','rs_vs_sp500_3m','pct_of_52w_high',
              'volume_trend','catalyst_upgrades_count','catalyst_insider_buy_count']

returns = [d['return_pct'] for d in data]
print(f"{'Indicador':<26} {'Avg Ganadoras':>14} {'Avg Perdedoras':>15} {'Correlacion':>12}")
print('-'*70)
for ind in indicators:
    vals_w = [d[ind] for d in winners if d[ind] is not None]
    vals_l = [d[ind] for d in losers if d[ind] is not None]
    vals_all = [d[ind] for d in data if d[ind] is not None]
    rets_all = [d['return_pct'] for d in data if d[ind] is not None]
    avg_w = sum(vals_w)/len(vals_w) if vals_w else float('nan')
    avg_l = sum(vals_l)/len(vals_l) if vals_l else float('nan')
    corr = pearson(vals_all, rets_all) if len(vals_all) > 2 else float('nan')
    print(f"{ind:<26} {avg_w:>14.2f} {avg_l:>15.2f} {corr:>12.3f}")