"""
Reporte Financiero Ejecutivo — Pipeline con IA
================================================
Nicolás Roset · nicolasroset.com.ar

Pipeline que ingiere datos de P&L en CSV, calcula KPIs automáticamente,
genera visualizaciones con Matplotlib y redacta la narrativa ejecutiva
llamando a la API de OpenAI (GPT-4).

Dependencias:
    pip install pandas matplotlib openai python-dotenv
"""

import os
import io
import json
import pandas as pd
import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt
from datetime import datetime

# ── Opcional: pip install openai python-dotenv ──────────────────────────────
try:
    from openai import OpenAI
    from dotenv import load_dotenv
    load_dotenv()
    _api_key = os.getenv("OPENAI_API_KEY")
    if _api_key:
        client = OpenAI(api_key=_api_key)
        USE_AI = True
    else:
        USE_AI = False
        print("[INFO] OPENAI_API_KEY no encontrada — corriendo sin narrativa IA.")
        print("[INFO] Para activarla: creá un archivo .env con OPENAI_API_KEY=sk-...")
except ImportError:
    USE_AI = False
    print("[INFO] openai/dotenv no instalado — se omite narrativa IA.")
    print("[INFO] Instalá con: pip install openai python-dotenv")


# ════════════════════════════════════════════════════════════════════════════════
# 1. INGESTA DE DATOS
# ════════════════════════════════════════════════════════════════════════════════

SAMPLE_DATA = {
    "periodo":   ["Q1-23", "Q2-23", "Q3-23", "Q4-23", "Q1-24"],
    "revenue":   [8.4,      9.1,    10.3,    11.8,    12.7],
    "cogs":      [5.6,      5.9,     6.5,     7.1,     7.4],
    "opex":      [0.9,      0.9,     1.0,     1.2,     1.2],
    "capex":     [0.3,      0.4,     0.3,     0.5,     0.4],
}


def load_data(csv_path: str = None) -> pd.DataFrame:
    """Carga datos desde CSV o usa el dataset de ejemplo."""
    if csv_path and os.path.exists(csv_path):
        df = pd.read_csv(csv_path)
        print(f"[OK] Datos cargados desde {csv_path}")
    else:
        df = pd.DataFrame(SAMPLE_DATA)
        print("[INFO] Usando datos de ejemplo embebidos.")
    return df


# ════════════════════════════════════════════════════════════════════════════════
# 2. CÁLCULO DE KPIs
# ════════════════════════════════════════════════════════════════════════════════

def calcular_kpis(df: pd.DataFrame) -> dict:
    df = df.copy()
    df["gross_profit"] = df["revenue"] - df["cogs"]
    df["ebitda"]       = df["gross_profit"] - df["opex"]
    df["gm_pct"]       = df["gross_profit"] / df["revenue"] * 100
    df["ebitda_pct"]   = df["ebitda"]       / df["revenue"] * 100

    ultimo  = df.iloc[-1]
    anterior = df.iloc[-2]

    kpis = {
        "revenue_ult":    round(ultimo["revenue"], 2),
        "ebitda_ult":     round(ultimo["ebitda"], 2),
        "gm_pct_ult":     round(ultimo["gm_pct"], 1),
        "ebitda_pct_ult": round(ultimo["ebitda_pct"], 1),
        "revenue_qoq":    round((ultimo["revenue"] / anterior["revenue"] - 1) * 100, 1),
        "ebitda_qoq":     round((ultimo["ebitda"]  / anterior["ebitda"]  - 1) * 100, 1),
        "df": df,
    }
    return kpis


# ════════════════════════════════════════════════════════════════════════════════
# 3. VISUALIZACIÓN
# ════════════════════════════════════════════════════════════════════════════════

def generar_grafico(df: pd.DataFrame, output_path: str = "reporte_chart.png"):
    BG, CARD = '#0f172a', '#1e293b'
    GREEN, BLUE, MUTED, WHITE = '#22c55e', '#60a5fa', '#94a3b8', '#f1f5f9'

    fig, axes = plt.subplots(1, 2, figsize=(10, 4), facecolor=BG)
    fig.patch.set_facecolor(BG)

    for ax in axes:
        ax.set_facecolor(CARD)
        ax.tick_params(colors=MUTED, labelsize=8)
        ax.spines[:].set_color('#334155')
        ax.grid(axis='y', color='#334155', linewidth=0.4, linestyle='--')

    # Revenue vs EBITDA
    x = range(len(df))
    axes[0].bar([i - 0.2 for i in x], df["revenue"], width=0.38,
                color=BLUE, alpha=0.85, label="Revenue")
    axes[0].bar([i + 0.2 for i in x], df["ebitda"], width=0.38,
                color=GREEN, alpha=0.85, label="EBITDA")
    axes[0].set_xticks(list(x))
    axes[0].set_xticklabels(df["periodo"])
    axes[0].set_title("Revenue & EBITDA (M USD)", color=WHITE, fontsize=10, fontweight='bold')
    axes[0].legend(facecolor=CARD, edgecolor='#334155', labelcolor=WHITE, fontsize=8)

    # Márgenes
    axes[1].plot(df["periodo"], df["gm_pct"],     color=BLUE,  marker='o', linewidth=2, label="Gross Margin %")
    axes[1].plot(df["periodo"], df["ebitda_pct"], color=GREEN, marker='s', linewidth=2, label="EBITDA Margin %")
    axes[1].set_title("Evolución de Márgenes (%)", color=WHITE, fontsize=10, fontweight='bold')
    axes[1].legend(facecolor=CARD, edgecolor='#334155', labelcolor=WHITE, fontsize=8)
    axes[1].fill_between(range(len(df)), df["ebitda_pct"], alpha=0.1, color=GREEN)

    plt.tight_layout()
    plt.savefig(output_path, dpi=130, bbox_inches='tight', facecolor=BG)
    plt.close()
    print(f"[OK] Gráfico guardado en {output_path}")
    return output_path


# ════════════════════════════════════════════════════════════════════════════════
# 4. NARRATIVA EJECUTIVA CON IA
# ════════════════════════════════════════════════════════════════════════════════

PROMPT_TEMPLATE = """
Sos un analista financiero senior. Con base en los siguientes KPIs trimestrales,
redactá un párrafo ejecutivo conciso (máx. 120 palabras) para el CFO.
Destacá la tendencia, el margen y los puntos de atención. Usá tono profesional.

KPIs:
- Revenue último período: USD {revenue_ult}M ({revenue_qoq:+.1f}% QoQ)
- EBITDA último período: USD {ebitda_ult}M ({ebitda_qoq:+.1f}% QoQ)
- Gross Margin: {gm_pct_ult:.1f}%
- EBITDA Margin: {ebitda_pct_ult:.1f}%
"""

def generar_narrativa(kpis: dict) -> str:
    if not USE_AI:
        return (
            f"Revenue de USD {kpis['revenue_ult']}M ({kpis['revenue_qoq']:+.1f}% QoQ) con "
            f"EBITDA de USD {kpis['ebitda_ult']}M ({kpis['ebitda_qoq']:+.1f}% QoQ). "
            f"Gross Margin {kpis['gm_pct_ult']:.1f}% | EBITDA Margin {kpis['ebitda_pct_ult']:.1f}%. "
            "[Narrativa IA deshabilitada — configurá OPENAI_API_KEY en .env]"
        )

    prompt = PROMPT_TEMPLATE.format(**{k: v for k, v in kpis.items() if k != "df"})
    response = client.chat.completions.create(
        model="gpt-4o-mini",
        messages=[{"role": "user", "content": prompt}],
        max_tokens=200,
        temperature=0.4,
    )
    return response.choices[0].message.content.strip()


# ════════════════════════════════════════════════════════════════════════════════
# 5. OUTPUT: REPORTE HTML
# ════════════════════════════════════════════════════════════════════════════════

def exportar_reporte_html(kpis: dict, narrativa: str, chart_path: str,
                          output: str = "reporte_ejecutivo.html"):
    import base64
    with open(chart_path, "rb") as f:
        img_b64 = base64.b64encode(f.read()).decode()

    html = f"""<!DOCTYPE html>
<html lang="es">
<head><meta charset="UTF-8">
<title>Reporte Financiero Ejecutivo — {datetime.now().strftime('%b %Y')}</title>
<style>
  body {{ font-family: 'Segoe UI', sans-serif; background:#0f172a; color:#f1f5f9; padding:40px; }}
  h1   {{ color:#22c55e; }} h2 {{ color:#86efac; border-bottom:1px solid #334155; padding-bottom:6px; }}
  .kpi {{ display:inline-block; background:#1e293b; border:1px solid #334155;
          border-radius:10px; padding:16px 24px; margin:8px; text-align:center; }}
  .kpi .val {{ font-size:1.8rem; font-weight:900; color:#22c55e; }}
  .kpi .lbl {{ font-size:.75rem; color:#94a3b8; }}
  .narrative {{ background:#1e293b; border-left:3px solid #22c55e;
                padding:16px 20px; border-radius:8px; line-height:1.7; }}
  img {{ max-width:100%; border-radius:10px; margin-top:20px; }}
</style>
</head>
<body>
<h1>Reporte Financiero Ejecutivo</h1>
<p style="color:#94a3b8">Generado automáticamente · {datetime.now().strftime('%d/%m/%Y %H:%M')} · Pipeline Python + GPT-4</p>

<h2>KPIs Clave</h2>
<div class="kpi"><div class="val">USD {kpis['revenue_ult']}M</div><div class="lbl">Revenue ({kpis['revenue_qoq']:+.1f}% QoQ)</div></div>
<div class="kpi"><div class="val">USD {kpis['ebitda_ult']}M</div><div class="lbl">EBITDA ({kpis['ebitda_qoq']:+.1f}% QoQ)</div></div>
<div class="kpi"><div class="val">{kpis['gm_pct_ult']:.1f}%</div><div class="lbl">Gross Margin</div></div>
<div class="kpi"><div class="val">{kpis['ebitda_pct_ult']:.1f}%</div><div class="lbl">EBITDA Margin</div></div>

<h2>Narrativa Ejecutiva (IA)</h2>
<div class="narrative">{narrativa}</div>

<h2>Visualización</h2>
<img src="data:image/png;base64,{img_b64}" alt="Financial Charts" />
</body>
</html>"""

    with open(output, "w", encoding="utf-8") as f:
        f.write(html)
    print(f"[OK] Reporte exportado → {output}")
    return output


# ════════════════════════════════════════════════════════════════════════════════
# MAIN
# ════════════════════════════════════════════════════════════════════════════════

if __name__ == "__main__":
    print("=" * 60)
    print("  Pipeline: Reporte Financiero con IA — Nicolás Roset")
    print("=" * 60)

    df    = load_data()                          # 1. Ingestar
    kpis  = calcular_kpis(df)                    # 2. Calcular KPIs
    chart = generar_grafico(kpis["df"])          # 3. Visualizar
    txt   = generar_narrativa(kpis)              # 4. Narrativa IA
    rep   = exportar_reporte_html(kpis, txt, chart)  # 5. Exportar HTML

    print("\n[NARRATIVA IA]")
    print(txt)
    print(f"\n✅  Reporte listo: {rep}")