from db import get_connection
from collections import defaultdict

THRESHOLD = 60

conn = get_connection()
cursor = conn.cursor(dictionary=True)
cursor.execute('''
    SELECT t.company_id, t.entry_date, t.exit_date, t.return_pct, t.entry_score
    FROM trade_simulation_results t
    JOIN backtest_results 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'
      AND t.entry_score > %s
      AND b.fundamental_score >= 50
    ORDER BY t.entry_date
''', (THRESHOLD,))
candidates = cursor.fetchall()
cursor.close()
conn.close()

print(f'Señales candidatas (score>{THRESHOLD} + veto fundamental>=50): {len(candidates)}')

CASH_START = 10000
POSITION_SIZE = 2000
MAX_POSITIONS = 5

cash = CASH_START
open_positions = []
yearly_trades = defaultdict(list)
rejected_no_slot = 0

def close_due(as_of_date):
    global cash
    still_open = []
    for pos in open_positions:
        if pos['exit_date'] <= as_of_date:
            pnl = POSITION_SIZE * (pos['return_pct'] / 100)
            cash += POSITION_SIZE + pnl
            yearly_trades[pos['exit_date'].year].append(pos['return_pct'])
        else:
            still_open.append(pos)
    return still_open

for c in candidates:
    open_positions = close_due(c['entry_date'])
    if len(open_positions) < MAX_POSITIONS and cash >= POSITION_SIZE:
        cash -= POSITION_SIZE
        open_positions.append({'exit_date': c['exit_date'], 'return_pct': float(c['return_pct'])})
    else:
        rejected_no_slot += 1

open_positions.sort(key=lambda p: p['exit_date'])
for pos in open_positions:
    pnl = POSITION_SIZE * (pos['return_pct'] / 100)
    cash += POSITION_SIZE + pnl
    yearly_trades[pos['exit_date'].year].append(pos['return_pct'])

print(f'Señales rechazadas por falta de slot/capital: {rejected_no_slot}')
total_n = sum(len(v) for v in yearly_trades.values())
print(f'Operaciones efectivamente ejecutadas: {total_n}')
print()
print('=== Resultado por año ===')
for year in sorted(yearly_trades.keys()):
    rets = yearly_trades[year]
    n = len(rets)
    wins = sum(1 for r in rets if r > 0)
    avg = sum(rets)/n
    pnl_year = sum(POSITION_SIZE*(r/100) for r in rets)
    pct_base = pnl_year/CASH_START*100
    print(f'{year}: n={n}  win_rate={wins/n*100:.0f}%  retorno_prom_trade={avg:.2f}%  PnL=${pnl_year:,.2f} ({pct_base:+.2f}% sobre base)')
print()
print(f'Capital final: ${cash:,.2f}')
total_return_pct = (cash - CASH_START) / CASH_START * 100
print(f'Retorno total acumulado: {total_return_pct:+.2f}%')