import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
import openpyxl

df = pd.read_csv("data.csv", thousands=",", parse_dates=["Date"])
df.columns = df.columns.str.strip().str.lower().str.replace(" ", "_")

df["revenue"] = pd.to_numeric(df["revenue"], errors="coerce")
df = df.dropna(subset=["revenue"])
df = df.sort_values("date").reset_index(drop=True)

df["year"] = df["date"].dt.year
annual = df.groupby("year").agg({"revenue": "sum", "ebitda": "sum"})
annual["margin"] = annual["ebitda"] / annual["revenue"]

# Inputs
revenue_base = 100_000_000
growth_rates = [0.15, 0.12, 0.10, 0.08, 0.06]
ebitda_margin = 0.25
da_pct = 0.04
capex_pct = 0.05
nwc_pct = 0.03  # projected NWC as % of revenue; FCF uses the CHANGE in NWC
tax_rate = 0.25
wacc = 0.10
terminal_growth = 0.025
net_debt = 20_000_000
shares_out = 10_000_000

# Projections
revenues = [revenue_base * np.prod([1 + g for g in growth_rates[: i + 1]]) for i in range(5)]
ebitda = [r * ebitda_margin for r in revenues]
da = [r * da_pct for r in revenues]
ebit = [e - d for e, d in zip(ebitda, da)]
nopat = [x * (1 - tax_rate) for x in ebit]
capex = [r * capex_pct for r in revenues]
nwc = [r * nwc_pct for r in revenues]
change_nwc = [nwc[0] - revenue_base * nwc_pct] + [nwc[i] - nwc[i - 1] for i in range(1, len(nwc))]
fcf = [n + d - c - dnwc for n, d, c, dnwc in zip(nopat, da, capex, change_nwc)]

# Terminal value sanity: terminal_growth must be below WACC.
assert terminal_growth < wacc, "terminal growth must be below WACC"
terminal_value = fcf[-1] * (1 + terminal_growth) / (wacc - terminal_growth)
discount_factors = [(1 / (1 + wacc)) ** (i + 1) for i in range(5)]
enterprise_value = sum(f * d for f, d in zip(fcf, discount_factors)) + terminal_value * discount_factors[-1]
equity_value = enterprise_value - net_debt
implied_share_price = equity_value / shares_out

waccs = np.arange(0.08, 0.13, 0.01)
tgrs = np.arange(0.015, 0.04, 0.005)
sensitivity = pd.DataFrame(
    index=[f"{w:.0%}" for w in waccs],
    columns=[f"{g:.1%}" for g in tgrs],
    data=[[fcf[-1] * (1 + g) / (w - g) * (1/(1+w))**5 for g in tgrs] for w in waccs]
)

def moic(entry_ev, exit_ev, entry_debt, exit_debt, equity_check):
    # equity_check is the sponsor cash invested at entry, after fees/rollover if applicable.
    if equity_check <= 0:
        raise ValueError("equity_check must be positive")
    exit_equity = exit_ev - exit_debt
    return exit_equity / equity_check

def irr_approx(moic_val, years):
    return moic_val ** (1 / years) - 1

comps = pd.DataFrame({
    "company": ["A", "B", "C"],
    "ev": [500, 800, 1200],
    "ebitda": [50, 75, 110],
    "revenue": [200, 350, 500],
})
comps["ev_ebitda"] = comps["ev"] / comps["ebitda"]
comps["ev_revenue"] = comps["ev"] / comps["revenue"]
print(comps[["company","ev_ebitda","ev_revenue"]].describe().loc[["mean","median"]])

def set_fin_style(ax, title, ylabel="$M"):
    ax.set_title(title, fontweight="bold", pad=10)
    ax.set_ylabel(ylabel)
    ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"${x/1e6:.0f}M"))
    ax.spines[["top", "right"]].set_visible(False)
    ax.grid(axis="y", linestyle="--", alpha=0.5)

def waterfall(labels, values, title="Bridge"):
    running = 0
    bottoms, colors = [], []
    for v in values:
        bottoms.append(running if v >= 0 else running + v)
        colors.append("#2ecc71" if v >= 0 else "#e74c3c")
        running += v
    fig, ax = plt.subplots(figsize=(10, 5))
    ax.bar(labels, [abs(v) for v in values], bottom=bottoms, color=colors, width=0.6)
    set_fin_style(ax, title)
    return fig, ax

fig, axes = plt.subplots(2, 2, figsize=(12, 8))
fig.suptitle("Financial Summary", fontweight="bold")
metrics = [("revenue", "Revenue"), ("ebitda", "EBITDA"),
           ("margin", "Margin", "%"), ("fcf", "FCF")]
for ax, (col, label, *fmt) in zip(axes.flat, metrics):
    annual[col].plot(ax=ax)
    set_fin_style(ax, label, ylabel=fmt[0] if fmt else "$M")
plt.tight_layout()

# Top of every notebook
import warnings; warnings.filterwarnings("ignore")
pd.set_option("display.float_format", "{:,.2f}".format)
np.random.seed(42)
VINTAGE = "2026-02-20"  # update per run

# Excel with multiple sheets
with pd.ExcelWriter("output/model_output.xlsx", engine="openpyxl") as writer:
    revenue_df.to_excel(writer, sheet_name="Revenue", index=False)
    dcf_df.to_excel(writer, sheet_name="DCF")
    sensitivity.to_excel(writer, sheet_name="Sensitivity")

# Save all figures
for name, fig in figures.items():
    fig.savefig(f"output/{name}.png", dpi=150, bbox_inches="tight")