"""
Risk lab for the Strategic Investment Portfolio (Saranne Ndamba).

Reads the 16 holdings from the Excel model and produces:
  1. Parametric (variance-covariance) VaR and Expected Shortfall, 95% and 99%
  2. Monte Carlo simulation, 10,000 one-year paths of correlated daily returns
  3. Historical VaR and Expected Shortfall from real daily prices (when prices.csv is present)
  4. Volatility, Sharpe ratio and maximum drawdown

Output: risk_results.json, read by the website's Risk lab section.

Run:  python risk_lab.py  [path/to/model.xlsx]  [path/to/prices.csv]
"""
import json
import sys
from pathlib import Path

import numpy as np
import pandas as pd
from scipy.stats import norm

HERE = Path(__file__).resolve().parent
MODEL = Path(sys.argv[1]) if len(sys.argv) > 1 else HERE / "Saranne_Ndamba_Strategic_Investment_Portfolio.xlsx"
PRICES = Path(sys.argv[2]) if len(sys.argv) > 2 else HERE / "prices.csv"
OUT = HERE / "risk_results.json"

CAPITAL = 100_000.0
RF = 0.020            # risk-free rate assumption (EUR short rate), annual
DAYS = 252            # trading days in a year
N_PATHS = 10_000
SEED = 2026
CONF = (0.95, 0.99)


# ---------------------------------------------------------------- inputs
def load_holdings(path):
    raw = pd.read_excel(path, sheet_name="Portfolio", header=None)
    raw = raw.dropna(axis=1, how="all")
    raw.columns = range(raw.shape[1])
    head = raw.index[raw[0] == "Ticker"][0]
    df = raw.iloc[head + 1:].copy()
    df.columns = raw.iloc[head].tolist()
    df = df[df["Ticker"].notna() & (df["Ticker"] != "TOTAL")]
    df = df[["Ticker", "Security", "Asset class", "Region", "Target weight", "Base CAGR", "Volatility", "Beta"]]
    df.columns = ["ticker", "name", "asset_class", "region", "w", "mu", "vol", "beta"]
    for c in ["w", "mu", "vol", "beta"]:
        df[c] = df[c].astype(float)
    risk = pd.read_excel(path, sheet_name="Risk & Scenarios", header=None).dropna(axis=1, how="all")
    risk.columns = range(risk.shape[1])
    corr = float(risk[risk[0].astype(str).str.startswith("Common correlation")].iloc[0, 1])
    return df.reset_index(drop=True), corr


def covariance(h, rho):
    """Model covariance: holding volatilities with one common correlation (as in the Excel)."""
    C = np.full((len(h), len(h)), rho)
    np.fill_diagonal(C, 1.0)
    s = h["vol"].to_numpy()
    return C * np.outer(s, s), C


# ---------------------------------------------------------------- risk measures
def var_es_from_returns(r, alpha):
    """Historical / simulated VaR and ES as positive loss fractions."""
    q = np.quantile(r, 1 - alpha)
    tail = r[r <= q]
    return -q, -tail.mean()


def max_drawdown(values):
    peak = np.maximum.accumulate(values, axis=-1)
    return ((values - peak) / peak).min(axis=-1)


def parametric(h, cov):
    w = h["w"].to_numpy()
    mu_p = float(w @ h["mu"].to_numpy())
    sig_p = float(np.sqrt(w @ cov @ w))
    out = {"mu": mu_p, "vol": sig_p, "sharpe": (mu_p - RF) / sig_p}
    for horizon, t in (("1d", 1 / DAYS), ("10d", 10 / DAYS), ("1y", 1.0)):
        for a in CONF:
            z = norm.ppf(a)
            s = sig_p * np.sqrt(t)
            m = mu_p * t if horizon == "1y" else 0.0   # mean ignored for short horizons, the market convention
            var = z * s - m
            es = s * norm.pdf(z) / (1 - a) - m
            out[f"var_{horizon}_{int(a*100)}"] = var
            out[f"es_{horizon}_{int(a*100)}"] = es
    # marginal and component VaR (95%, 1 day): who drives the risk
    mvar = cov @ w / sig_p
    comp = w * mvar / sig_p
    out["contrib"] = [{"ticker": t, "weight": float(wi), "share_of_risk": float(c)} for t, wi, c in zip(h["ticker"], w, comp)]
    return out


def monte_carlo(h, cov):
    """Correlated geometric Brownian motion, daily steps, weights rebalanced daily."""
    rng = np.random.default_rng(SEED)
    w = h["w"].to_numpy()
    sig = h["vol"].to_numpy()
    drift = np.log1p(h["mu"].to_numpy()) / DAYS   # with the -0.5 sigma^2 term below, each asset's expected annual return equals its base CAGR
    L = np.linalg.cholesky(cov / DAYS)
    values = np.empty((N_PATHS, DAYS + 1))
    values[:, 0] = CAPITAL
    day1 = None
    chunk = 2_000
    for start in range(0, N_PATHS, chunk):
        n = min(chunk, N_PATHS - start)
        z = rng.standard_normal((n, DAYS, len(w)))
        logr = drift - 0.5 * (sig ** 2) / DAYS + z @ L.T
        port = (np.expm1(logr) * w).sum(axis=2)            # daily portfolio return
        values[start:start + n, 1:] = CAPITAL * np.cumprod(1 + port, axis=1)
        day1 = port[:, 0] if day1 is None else np.concatenate([day1, port[:, 0]])
    final = values[:, -1] / CAPITAL - 1
    daily = values[:, 1:] / values[:, :-1] - 1
    mdd = max_drawdown(values)
    out = {
        "paths": N_PATHS, "steps": DAYS, "seed": SEED,
        "mean_return": float(final.mean()), "median_return": float(np.median(final)),
        "prob_loss": float((final < 0).mean()),
        "vol": float(daily.std() * np.sqrt(DAYS)),
        "sharpe": float((final.mean() - RF) / (daily.std() * np.sqrt(DAYS))),
        "mdd_median": float(np.median(mdd)), "mdd_p95": float(np.quantile(mdd, 0.05)),
    }
    for a in CONF:
        v, e = var_es_from_returns(final, a)
        out[f"var_1y_{int(a*100)}"], out[f"es_1y_{int(a*100)}"] = float(v), float(e)
        v, e = var_es_from_returns(day1, a)
        out[f"var_1d_{int(a*100)}"], out[f"es_1d_{int(a*100)}"] = float(v), float(e)
        v, e = var_es_from_returns(values[:, 10] / CAPITAL - 1, a)
        out[f"var_10d_{int(a*100)}"], out[f"es_10d_{int(a*100)}"] = float(v), float(e)
    pct = [5, 25, 50, 75, 95]
    steps = list(range(0, DAYS + 1, 21)) + ([DAYS] if DAYS % 21 else [])
    out["fan"] = {"day": steps, **{f"p{p}": np.percentile(values[:, steps], p, axis=0).round(0).tolist() for p in pct}}
    edges = np.linspace(-0.5, 0.9, 57)
    counts, _ = np.histogram(np.clip(final, edges[0], edges[-1] - 1e-9), bins=edges)
    out["hist"] = {"edges": edges.round(4).tolist(), "counts": counts.tolist()}
    out["sample_paths"] = values[rng.choice(N_PATHS, 40, replace=False)][:, steps].round(0).tolist()
    return out


def read_prices(path):
    """Accepts a tidy file (date, TICKER, ...) or the Google Sheets export of prices_template
    (ticker names on row 1, then Date / Close column pairs)."""
    raw = pd.read_csv(path, header=None, dtype=str)
    if str(raw.iloc[0, 0]).strip().lower() == "date":
        p = pd.read_csv(path)
        p["date"] = pd.to_datetime(p["date"])
        return p.set_index("date").sort_index().ffill().dropna()
    series = {}
    for c in range(0, raw.shape[1] - 1):
        name = raw.iloc[0, c]
        if isinstance(name, str) and name.strip() and str(raw.iloc[1, c]).strip().lower() == "date":
            block = raw.iloc[2:, [c, c + 1]].dropna()
            txt = block.iloc[:, 0].str.strip()
            idx = None
            for fmt in ("%d/%m/%Y %H:%M:%S", "%m/%d/%Y %H:%M:%S", "%Y-%m-%d %H:%M:%S", "%d/%m/%Y", "%m/%d/%Y", "%Y-%m-%d"):
                try:
                    cand = pd.to_datetime(txt, format=fmt)
                except (ValueError, TypeError):
                    continue
                if cand.is_monotonic_increasing:   # Google exports follow the account locale (UK: day first)
                    idx = cand.dt.normalize()
                    break
            if idx is None:
                raise ValueError(f"Unrecognised date format for {name}: {txt.iloc[0]}")
            series[name.strip()] = pd.Series(pd.to_numeric(block.iloc[:, 1], errors="coerce").to_numpy(), index=idx)
    p = pd.DataFrame(series).sort_index()
    p = p[p.index.dayofweek < 5].ffill().dropna()
    p.index.name = "date"
    return p


def historical(h, prices_path):
    """Full revaluation of today's weights on real daily returns (EUR)."""
    if not prices_path.exists():
        return None
    p = read_prices(prices_path)
    eurusd = p.pop("EURUSD") if "EURUSD" in p else None
    rets = p.pct_change().dropna()
    if eurusd is not None:   # USD assets lose value for a euro investor when EUR/USD rises
        fx = eurusd.pct_change().reindex(rets.index)
        usd = [c for c in rets if c in USD_UNHEDGED]
        rets[usd] = (1 + rets[usd]).div(1 + fx, axis=0) - 1
    r = pd.DataFrame(index=rets.index)
    for _, row in h.iterrows():
        t = row.ticker
        if t == "CASH":
            r[t] = (1 + row.mu) ** (1 / DAYS) - 1
        elif t == "HEDGE":
            r[t] = -1.5 * rets[PROXY["HEDGE"]] - 0.02 / DAYS     # delta proxy of an index put spread, with premium bleed
        else:
            r[t] = rets[PROXY.get(t, t)]
    port = r[h["ticker"]].to_numpy() @ h["w"].to_numpy()
    s = pd.Series(port, index=r.index)
    value = CAPITAL * (1 + s).cumprod()
    growth = (1 + s).cumprod()
    roll = (growth / growth.shift(10) - 1).dropna()            # overlapping 10-day returns
    roll_y = (growth / growth.shift(DAYS) - 1).dropna()        # overlapping 1-year returns
    yrs = len(s) / DAYS
    cagr = (value.iloc[-1] / CAPITAL) ** (1 / yrs) - 1
    vol = s.std() * np.sqrt(DAYS)
    dd = value / value.cummax() - 1
    out = {"start": str(s.index[0].date()), "end": str(s.index[-1].date()), "days": len(s),
           "cagr": float(cagr), "vol": float(vol), "sharpe": float((cagr - RF) / vol),
           "mdd": float(dd.min()), "mdd_date": str(dd.idxmin().date()),
           "worst_day": float(s.min()), "worst_day_date": str(s.idxmin().date())}
    for a in CONF:
        v, e = var_es_from_returns(s.to_numpy(), a)
        out[f"var_1d_{int(a*100)}"], out[f"es_1d_{int(a*100)}"] = float(v), float(e)
        v, e = var_es_from_returns(roll.to_numpy(), a)
        out[f"var_10d_{int(a*100)}"], out[f"es_10d_{int(a*100)}"] = float(v), float(e)
        v, e = var_es_from_returns(roll_y.to_numpy(), a)
        out[f"var_1y_{int(a*100)}"], out[f"es_1y_{int(a*100)}"] = float(v), float(e)
        out[f"breaches_{int(a*100)}"] = int((s < -out[f"var_1d_{int(a*100)}"]).sum())
    # model check 1: realised correlation and volatility against the Excel assumptions
    eq = [t for t in h.loc[h.asset_class == "Equity", "ticker"]]
    cm = r[eq].corr().to_numpy()
    out["avg_corr_equity"] = float(cm[np.triu_indices(len(eq), 1)].mean())
    out["vol_by_holding"] = [{"ticker": t, "assumed": float(v), "realised": float(r[t].std() * np.sqrt(DAYS))}
                             for t, v in zip(h.ticker, h.vol) if t not in ("CASH",)]
    out["daily"] = {"date": [str(d.date()) for d in s.index], "ret": s.round(5).tolist()}
    out["equity_curve"] = {"date": [str(d.date()) for d in value.index[::5]], "value": value.iloc[::5].round(0).tolist(),
                           "drawdown": dd.iloc[::5].round(4).tolist()}
    edges = np.linspace(-0.08, 0.08, 65)
    counts, _ = np.histogram(np.clip(s, edges[0], edges[-1] - 1e-9), bins=edges)
    out["hist"] = {"edges": edges.round(4).tolist(), "counts": counts.tolist()}
    out["proxies"] = PROXY
    return out


# Holdings without a long, clean daily history are represented by a liquid proxy.
PROXY = {"EUNA": "AGG", "ECRP": "LQD", "SGLN": "GLD", "INFR": "IGF", "HEDGE": "SPY"}
# Bond sleeves are EUR-hedged share classes, so their proxy is not translated for FX.
USD_UNHEDGED = {"NVDA", "MSFT", "GOOGL", "AMZN", "TSM", "ASML", "MELI", "CRWD", "LLY", "NVO", "GLD", "IGF", "SPY"}


if __name__ == "__main__":
    h, rho = load_holdings(MODEL)
    cov, C = covariance(h, rho)
    res = {"capital": CAPITAL, "rf": RF, "correlation": rho,
           "holdings": h.to_dict(orient="records"),
           "parametric": parametric(h, cov),
           "monte_carlo": monte_carlo(h, cov),
           "historical": historical(h, PRICES)}
    hs = res["historical"]
    if hs:   # model check 2: backtest the parametric 1-day VaR on the real history (Kupiec-style count)
        rets = np.array(hs.pop("daily")["ret"])
        for a in (95, 99):
            lim = res["parametric"][f"var_1d_{a}"]
            hs[f"param_breaches_{a}"] = int((rets < -lim).sum())
            hs[f"param_expected_{a}"] = round(len(rets) * (1 - a / 100), 1)
    OUT.write_text(json.dumps(res, indent=1))
    p, m = res["parametric"], res["monte_carlo"]
    print(f"Weights sum {h.w.sum():.2f} | model vol {p['vol']:.2%} | expected return {p['mu']:.2%} | Sharpe {p['sharpe']:.2f}")
    for a in (95, 99):
        print(f"{a}%  1-day param VaR {p[f'var_1d_{a}']*CAPITAL:,.0f}  ES {p[f'es_1d_{a}']*CAPITAL:,.0f} | "
              f"MC 1-day VaR {m[f'var_1d_{a}']*CAPITAL:,.0f}  ES {m[f'es_1d_{a}']*CAPITAL:,.0f} | "
              f"1-year param VaR {p[f'var_1y_{a}']*CAPITAL:,.0f}  MC {m[f'var_1y_{a}']*CAPITAL:,.0f}  MC ES {m[f'es_1y_{a}']*CAPITAL:,.0f}")
    print(f"MC: mean {m['mean_return']:.2%}, P(loss) {m['prob_loss']:.1%}, vol {m['vol']:.2%}, Sharpe {m['sharpe']:.2f}, "
          f"max drawdown median {m['mdd_median']:.1%}, 1-in-20 {m['mdd_p95']:.1%}")
    print("Historical:", "waiting for prices.csv" if res["historical"] is None else {k: v for k, v in res["historical"].items() if not isinstance(v, (dict, list))})
