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_income_statement(ticker):
    url = f"{BASE_URL}/income-statement?symbol={ticker}&limit=2&apikey={API_KEY}"
    r = requests.get(url, timeout=10)
    return r.json() if r.status_code == 200 else []

def get_cash_flow(ticker):
    url = f"{BASE_URL}/cash-flow-statement?symbol={ticker}&limit=2&apikey={API_KEY}"
    r = requests.get(url, timeout=10)
    return r.json() if r.status_code == 200 else []

def get_key_metrics(ticker):
    url = f"{BASE_URL}/key-metrics?symbol={ticker}&limit=2&apikey={API_KEY}"
    r = requests.get(url, timeout=10)
    return r.json() if r.status_code == 200 else []

def get_company_profile(ticker):
    url = f"{BASE_URL}/profile?symbol={ticker}&apikey={API_KEY}"
    r = requests.get(url, timeout=10)
    data = r.json() if r.status_code == 200 else []
    return data[0] if data else {}

def calc_growth(current, previous):
    if previous and previous != 0:
        return round((current - previous) / abs(previous) * 100, 2)
    return None

def upsert_company(ticker, profile):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("""
        INSERT INTO companies (ticker, name, sector, exchange, market_cap_b, active)
        VALUES (%s, %s, %s, %s, %s, 1)
        ON DUPLICATE KEY UPDATE
        name=%s, sector=%s, exchange=%s, market_cap_b=%s, active=1
    """, (
        ticker,
        profile.get("companyName", ticker),
        profile.get("sector", "TBD"),
        profile.get("exchangeShortName", ""),
        round(profile.get("mktCap", 0) / 1e9, 2),
        profile.get("companyName", ticker),
        profile.get("sector", "TBD"),
        profile.get("exchangeShortName", ""),
        round(profile.get("mktCap", 0) / 1e9, 2)
    ))
    conn.commit()
    company_id = cursor.lastrowid
    if company_id == 0:
        cursor.execute("SELECT id FROM companies WHERE ticker=%s", (ticker,))
        company_id = cursor.fetchone()[0]
    cursor.close()
    conn.close()
    return company_id

def save_fundamentals(company_id, data):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("""
        INSERT INTO scores_history
        (company_id, score_date, revenue_growth_yoy, eps_growth_yoy, fcf_growth_yoy,
         roic, operating_margin, net_debt_ebitda)
        VALUES (%s, CURDATE(), %s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
        revenue_growth_yoy=%s, eps_growth_yoy=%s, fcf_growth_yoy=%s,
        roic=%s, operating_margin=%s, net_debt_ebitda=%s
    """, (
        company_id,
        data["revenue_growth_yoy"], data["eps_growth_yoy"], data["fcf_growth_yoy"],
        data["roic"], data["operating_margin"], data["net_debt_ebitda"],
        data["revenue_growth_yoy"], data["eps_growth_yoy"], data["fcf_growth_yoy"],
        data["roic"], data["operating_margin"], data["net_debt_ebitda"]
    ))
    conn.commit()
    cursor.close()
    conn.close()

def process_ticker(ticker):
    print(f"Procesando {ticker}...")
    try:
        profile = get_company_profile(ticker)
        income = get_income_statement(ticker)
        cashflow = get_cash_flow(ticker)
        metrics = get_key_metrics(ticker)

        if not income or len(income) < 2:
            print(f"  {ticker}: datos insuficientes")
            return

        revenue_growth = calc_growth(income[0]["revenue"], income[1]["revenue"])
        eps_growth = calc_growth(income[0]["epsDiluted"], income[1]["epsDiluted"])
        operating_margin = round(income[0].get("operatingIncomeRatio", 0) * 100, 2)

        fcf_growth = None
        if cashflow and len(cashflow) >= 2:
            fcf_growth = calc_growth(cashflow[0]["freeCashFlow"], cashflow[1]["freeCashFlow"])

        roic = None
        net_debt_ebitda = None
        if metrics:
            roic = round(metrics[0].get("returnOnInvestedCapital", 0) * 100, 2) if metrics[0].get("returnOnInvestedCapital") else None
            net_debt_ebitda = round(metrics[0].get("netDebtToEBITDA", 0), 2) if metrics[0].get("netDebtToEBITDA") else None

        data = {
            "revenue_growth_yoy": revenue_growth,
            "eps_growth_yoy": eps_growth,
            "fcf_growth_yoy": fcf_growth,
            "roic": roic,
            "operating_margin": operating_margin,
            "net_debt_ebitda": net_debt_ebitda
        }

        company_id = upsert_company(ticker, profile)
        save_fundamentals(company_id, data)
        print(f"  {ticker}: OK — Rev: {revenue_growth}% | EPS: {eps_growth}% | FCF: {fcf_growth}% | ROIC: {roic}%")

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

if __name__ == "__main__":
    for ticker in TICKERS:
        process_ticker(ticker)
    print("\nIngesta de fundamentales completada.")