from db import get_connection
from collections import defaultdict

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 > 55
      AND b.fundamental_score >= 50
    ORDER BY t.entry_date
''')
candidates = cursor.fetchall()
cursor.close()
conn.close()

CASH_START = 10000
POSITION_SIZE = 2000
MAX_POSITIONS = 5

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

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'])})

open_positions.sort(key=lambda p: p['exit_date'])
for pos in open_positions:
    yearly_trades[pos['exit_date'].year].append(pos['return_pct'])

print('=== Operaciones cerradas 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
    print(f'{year}: n={n}  win_rate={wins/n*100:.0f}%  retorno_promedio_por_trade={avg:.2f}%')