import requests
import os
from dotenv import load_dotenv
from db import get_connection
from config import TICKERS

load_dotenv()

API_KEY = os.getenv("FMP_API_KEY")
BASE_URL = "https://financialmodelingprep.com/stable"

def get_historical_prices(ticker, limit=250):
    url = f"{BASE_URL}/historical-price-eod/full?symbol={ticker}&limit={limit}&apikey={API_KEY}"
    r = requests.get(url, timeout=10)
    return r.json() if r.status_code == 200 else []

def get_historical_prices_sp500(limit=250):
    url = f"{BASE_URL}/historical-price-eod/full?symbol=SPY&limit={limit}&apikey={API_KEY}"
    r = requests.get(url, timeout=10)
    return r.json() if r.status_code == 200 else []

def calc_ma(prices, period):
    if len(prices) < period:
        return None
    closes = [p["close"] for p in prices[:period]]
    return round(sum(closes) / period, 2)

def calc_rs_vs_sp500(ticker_prices, sp500_prices, days=63):
    if len(ticker_prices) < days or len(sp500_prices) < days:
        return None
    ticker_return = (ticker_prices[0]["close"] - ticker_prices[days-1]["close"]) / ticker_prices[days-1]["close"]
    sp500_return = (sp500_prices[0]["close"] - sp500_prices[days-1]["close"]) / sp500_prices[days-1]["close"]
    return round((ticker_return - sp500_return) * 100, 2)

def calc_52w_high(prices):
    if len(prices) < 252:
        highs = [p["high"] for p in prices]
    else:
        highs = [p["high"] for p in prices[:252]]
    return round(max(highs), 2) if highs else None

def calc_volume_trend(prices, short=20, long=50):
    if len(prices) < long:
        return None
    avg_short = sum(p["volume"] for p in prices[:short]) / short
    avg_long = sum(p["volume"] for p in prices[:long]) / long
    return round((avg_short - avg_long) / avg_long * 100, 2)

def get_company_id(ticker):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT id FROM companies WHERE ticker=%s", (ticker,))
    row = cursor.fetchone()
    cursor.close()
    conn.close()
    return row[0] if row else None

def save_momentum(company_id, data):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("""
        UPDATE scores_history SET
        price=%s, ma50=%s, ma200=%s,
        rs_vs_sp500_3m=%s, week_high_52=%s, volume_trend=%s
        WHERE company_id=%s AND score_date=CURDATE()
    """, (
        data["price"], data["ma50"], data["ma200"],
        data["rs_vs_sp500_3m"], data["week_high_52"], data["volume_trend"],
        company_id
    ))
    conn.commit()
    cursor.close()
    conn.close()

def process_ticker(ticker, sp500_prices):
    print(f"Procesando {ticker}...")
    try:
        prices = get_historical_prices(ticker)
        if not prices:
            print(f"  {ticker}: sin datos de precio")
            return

        price = prices[0]["close"]
        ma50 = calc_ma(prices, 50)
        ma200 = calc_ma(prices, 200)
        rs = calc_rs_vs_sp500(prices, sp500_prices)
        high_52w = calc_52w_high(prices)
        vol_trend = calc_volume_trend(prices)

        data = {
            "price": price,
            "ma50": ma50,
            "ma200": ma200,
            "rs_vs_sp500_3m": rs,
            "week_high_52": high_52w,
            "volume_trend": vol_trend
        }

        company_id = get_company_id(ticker)
        if not company_id:
            print(f"  {ticker}: company_id no encontrado en BD")
            return

        save_momentum(company_id, data)
        print(f"  {ticker}: OK — Price: {price} | MA50: {ma50} | MA200: {ma200} | RS: {rs}% | Vol Trend: {vol_trend}%")

    except Exception as e:
        print(f"  {ticker}: ERROR — {e}")

if __name__ == "__main__":
    print("Cargando SPY...")
    sp500_prices = get_historical_prices_sp500()
    if not sp500_prices:
        print("ERROR: no se pudo obtener datos de SPY")
        exit(1)
    print(f"SPY cargado: {len(sp500_prices)} días\n")
    for ticker in TICKERS:
        process_ticker(ticker, sp500_prices)
    print("\nIngesta de momentum completada.")