from db import get_connection
from config import TICKERS

# ── FUNDAMENTAL SCORE (max 120pts) ──────────────────────────────────────────
def score_revenue_growth(v):
    if v is None: return 0
    v = float(v)
    if v >= 20: return 25
    if v >= 10: return 18
    if v >= 5:  return 12
    if v >= 0:  return 6
    return 0

def score_eps_growth(v):
    if v is None: return 0
    v = float(v)
    if v >= 25: return 25
    if v >= 10: return 18
    if v >= 0:  return 10
    if v >= -10: return 4
    return 0

def score_fcf_growth(v):
    if v is None: return 0
    v = float(v)
    if v >= 30: return 20
    if v >= 10: return 14
    if v >= 0:  return 8
    if v >= -10: return 3
    return 0

def score_roic(v):
    if v is None: return 0
    v = float(v)
    if v >= 20: return 20
    if v >= 12: return 14
    if v >= 8:  return 8
    if v >= 0:  return 3
    return 0

def score_margin_expansion(v):
    if v is None: return 0
    v = float(v)
    if v >= 2:   return 15
    if v >= 0.5: return 10
    if v >= 0:   return 5
    return 0

def score_debt_ratio(v):
    if v is None: return 0
    v = float(v)
    if v <= 1:  return 15
    if v <= 2:  return 10
    if v <= 3:  return 5
    return 0

def calc_fundamental_score(row):
    pts = (
        score_revenue_growth(row["revenue_growth_yoy"]) +
        score_eps_growth(row["eps_growth_yoy"]) +
        score_fcf_growth(row["fcf_growth_yoy"]) +
        score_roic(row["roic"]) +
        score_margin_expansion(row["operating_margin"]) +
        score_debt_ratio(row["net_debt_ebitda"])
    )
    return round((pts / 120) * 100, 2)

# ── MOMENTUM SCORE (max 110pts) ──────────────────────────────────────────────
def score_vs_ma(price, ma):
    if price is None or ma is None: return 0
    pct = (float(price) - float(ma)) / float(ma) * 100
    if pct >= 10:  return 25
    if pct >= 3:   return 18
    if pct >= 0:   return 10
    if pct >= -5:  return 4
    return 0

def score_rs(v):
    if v is None: return 0
    v = float(v)
    if v >= 15:  return 20
    if v >= 5:   return 14
    if v >= 0:   return 8
    if v >= -10: return 3
    return 0

def score_52w_proximity(price, high):
    if price is None or high is None: return 0
    pct = float(price) / float(high) * 100
    if pct >= 95:  return 20
    if pct >= 85:  return 14
    if pct >= 70:  return 7
    return 0

def score_volume_trend(v):
    if v is None: return 0
    v = float(v)
    if v >= 20:  return 20
    if v >= 5:   return 14
    if v >= 0:   return 8
    if v >= -10: return 3
    return 0

def calc_momentum_score(row):
    pts = (
        score_vs_ma(row["price"], row["ma50"]) +
        score_vs_ma(row["price"], row["ma200"]) +
        score_rs(row["rs_vs_sp500_3m"]) +
        score_52w_proximity(row["price"], row["week_high_52"]) +
        score_volume_trend(row["volume_trend"])
    )
    return round((pts / 110) * 100, 2)

# ── CATALYST SCORE (max 120pts) ──────────────────────────────────────────────
def calc_catalyst_score(company_id):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("""
        SELECT type, score_points FROM catalysts
        WHERE company_id=%s AND catalyst_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
    """, (company_id,))
    rows = cursor.fetchall()
    cursor.close()
    conn.close()

    type_map = {
        "guidance_increase": 25,
        "insider_buying":    20,
        "analyst_upgrade":   20,
        "backlog_growth":    20,
        "new_contracts":     20,
        "strategic_acquisition": 15
    }

    scored_types = set()
    pts = 0
    for ctype, _ in rows:
        if ctype not in scored_types:
            pts += type_map.get(ctype, 0)
            scored_types.add(ctype)

    return round((pts / 120) * 100, 2)

# ── COMPOSITE SCORE ──────────────────────────────────────────────────────────
def calc_final_score(f, m, c):
    return round(f * 0.40 + m * 0.30 + c * 0.30, 2)

def get_recommendation(score, fundamental):
    if fundamental < 50:
        return "NO_BUY (Fundamental veto)"
    if score > 85:
        return "COMPRAR"
    if score >= 75:
        return "MANTENER"
    return "DESCARTAR"

# ── MAIN ─────────────────────────────────────────────────────────────────────
def run_scoring():
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("""
        SELECT sh.*, c.ticker
        FROM scores_history sh
        JOIN companies c ON c.id = sh.company_id
        WHERE sh.score_date = CURDATE()
    """)
    rows = cursor.fetchall()
    cursor.close()

    results = []
    for row in rows:
        f = calc_fundamental_score(row)
        m = calc_momentum_score(row)
        c = calc_catalyst_score(row["company_id"])
        final = calc_final_score(f, m, c)
        rec = get_recommendation(final, f)

        update = conn.cursor()
        update.execute("""
            UPDATE scores_history
            SET fundamental_score=%s, momentum_score=%s,
                catalyst_score=%s, final_score=%s
            WHERE id=%s
        """, (f, m, c, final, row["id"]))
        conn.commit()
        update.close()

        results.append((final, row["ticker"], f, m, c, rec))

    conn.close()

    results.sort(reverse=True)
    print(f"\n{'='*65}")
    print(f"{'RANKING':^65}")
    print(f"{'='*65}")
    print(f"{'#':<4} {'Ticker':<8} {'Final':>6} {'Fund':>6} {'Mom':>6} {'Cat':>6}  Señal")
    print(f"{'-'*65}")
    for i, (final, ticker, f, m, c, rec) in enumerate(results, 1):
        print(f"{i:<4} {ticker:<8} {final:>6.1f} {f:>6.1f} {m:>6.1f} {c:>6.1f}  {rec}")
    print(f"{'='*65}")

if __name__ == "__main__":
    run_scoring()