from db import get_connection
from scoring import score_vs_ma50, score_vs_ma200, score_rs, score_52w_proximity

def score_volume_trend_v3(v):
    """Deponderado: 20->5 maximo, 12->3, manteniendo la misma proporcion de tramos."""
    if v is None: return 0
    v = float(v)
    if v > 20:  return 5
    if v >= 0:  return 3
    return 0

def calc_momentum_score_v3(price, ma50, ma200, rs, week_high_52, volume_trend):
    pts = (
        score_vs_ma50(price, ma50) +
        score_vs_ma200(price, ma200) +
        score_rs(rs) +
        score_52w_proximity(price, week_high_52) +
        score_volume_trend_v3(volume_trend)
    )
    return round((pts / 95) * 100, 2)

conn = get_connection()
cursor = conn.cursor(dictionary=True)
cursor.execute('''
    SELECT id, fundamental_score, catalyst_score, price_at_snapshot, ma50, ma200,
           rs_vs_sp500_3m, week_high_52, volume_trend
    FROM backtest_results_v2
''')
rows = cursor.fetchall()
cursor.close()

update_cursor = conn.cursor()
count = 0
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
    m3 = calc_momentum_score_v3(
        float(r['price_at_snapshot']), float(r['ma50']), float(r['ma200']),
        float(r['rs_vs_sp500_3m']) if r['rs_vs_sp500_3m'] is not None else None,
        float(r['week_high_52']),
        float(r['volume_trend']) if r['volume_trend'] is not None else None
    )
    f = float(r['fundamental_score']) if r['fundamental_score'] is not None else 0
    c = float(r['catalyst_score']) if r['catalyst_score'] is not None else 0
    final3 = round(f * 0.40 + m3 * 0.30 + c * 0.30, 2)
    update_cursor.execute(
        'UPDATE backtest_results_v2 SET momentum_score_v3=%s, final_score_v3=%s WHERE id=%s',
        (m3, final3, r['id'])
    )
    count += 1

conn.commit()
update_cursor.close()
conn.close()
print(f'Filas recalculadas: {count}')