#!/usr/bin/env python3
"""Derive USD SOFR OIS par-rate quotes for one as-of date from public trade prints.

Source of record: DTCC Data Repository (U.S.) LLC, Public Price Dissemination,
CFTC cumulative rates report for the as-of date (Part 43 real-time public
reporting). The zip is vendored under data/ with a SHA-256 in data/SHA256SUMS.

The quote for a tenor is the close-adjusted median fixed rate of that day's
newly executed, spot-starting, non-package USD SOFR OIS trades whose fixed leg
is Act/360 and whose legs pay annually or once at maturity. Close adjustment
removes the day's common intraday move (see main()).
This is a data-selection choice; the model document records it as one.

Usage:
    uv run --with pandas --with statsmodels python3 derive_quotes.py
Writes:
    out/quotes.csv, out/quotes.md, out/sofr_fixing.json, out/trades.csv,
    out/intraday_path.csv, out/intraday_loadings.csv
"""
from __future__ import annotations

import json
import zipfile
from pathlib import Path

import pandas as pd
import statsmodels.api as sm

HERE = Path(__file__).resolve().parent
DATA = HERE / "data"
OUT = HERE / "out"

AS_OF = "2026-09-18"          # trade date of the DTCC report
SPOT = "2026-09-22"           # T+2 settlement for USD SOFR OIS (SIFMA calendar)
DTCC_ZIP = DATA / "CFTC_CUMULATIVE_RATES_2026_09_18.zip"
NYFED_SOFR = DATA / "nyfed_sofr_2026-09-01_2026-09-22.json"

# Standard tenors kept on the curve, in months. A tenor is kept only if at least
# MIN_TRADES prints survive the filters below; thin tenors are reported but
# excluded from the bootstrap.
TENORS_MONTHS = [1, 2, 3, 4, 6, 9, 12, 18, 24, 36, 48, 60, 72, 84, 96, 108, 120, 144, 180, 240, 300, 360]
MIN_TRADES = 5                     # prints needed for a tenor to enter the bootstrap
MAX_IQR_BP = 2.0                   # and a close-adjusted interquartile range no wider than this
PATH_MIN_MONTHS = 24               # tenors that define the common intraday path (loading 1)
LOADING_MIN_PRINTS = 8             # a shorter tenor's loading is fitted only with this many prints
LOADING_MIN_HOURS = 5              # spread over at least this many distinct hours
OFF_MARKET_BP = 5.0                # a matched print this far from its tenor's quote is reported as off-market
ANNUAL_OR_TERM = ["YEAR", "EXPI"]   # DTCC payment frequency period codes kept


def label(months: int) -> str:
    return f"{months}M" if months < 12 else (f"{months // 12}Y" if months % 12 == 0 else f"{months}M")


def load_trades() -> pd.DataFrame:
    with zipfile.ZipFile(DTCC_ZIP) as z:
        name = [n for n in z.namelist() if n.endswith(".csv")][0]
        with z.open(name) as f:
            df = pd.read_csv(f, low_memory=False, dtype=str)
    return df


def main() -> None:
    OUT.mkdir(exist_ok=True)
    df = load_trades()
    n_all = len(df)

    m = df[
        (df["UPI FISN"] == "NA/Swap OIS USD")
        & df["UPI Underlier Name"].isin(["USD-SOFR-OIS Compound", "USD-SOFR-COMPOUND"])
        & (df["Action type"] == "NEWT")
        & (df["Event type"] == "TRAD")
    ].copy()
    n_sofr_ois = len(m)

    m["ts_ny"] = pd.to_datetime(m["Execution Timestamp"], errors="coerce", utc=True).dt.tz_convert("America/New_York")
    m["exec_date"] = m["ts_ny"].dt.date.astype(str)
    m["hour"] = m["ts_ny"].dt.hour
    m["eff"] = pd.to_datetime(m["Effective Date"], errors="coerce")
    m["exp"] = pd.to_datetime(m["Expiration Date"], errors="coerce")
    m["fixed"] = pd.to_numeric(m["Fixed rate-Leg 1"].fillna(m["Fixed rate-Leg 2"]), errors="coerce")
    m["dc"] = m["Fixed rate day count convention-leg 1"].fillna(m["Fixed rate day count convention-leg 2"])
    m["notional"] = pd.to_numeric(
        m["Notional amount-Leg 1"].astype(str).str.replace(",", "").str.replace("+", "", regex=False),
        errors="coerce",
    )
    m["fixed_freq"] = m["Fixed rate payment frequency period-Leg 1"].fillna(m["Fixed rate payment frequency period-Leg 2"])
    m["float_freq"] = m["Floating rate payment frequency period-Leg 2"].fillna(m["Floating rate payment frequency period-Leg 1"])

    # Common filters for every print this script uses: executed on the as-of
    # date, not part of a package, Act/360 fixed leg, annual (YEAR) or
    # single-payment (EXPI) schedules on both legs (the schedule the curve's
    # instruments assume; monthly-paying prints are excluded), and no upfront
    # payment (a print with one trades at a standard coupon, not at par).
    base = m[
        (m["exec_date"] == AS_OF)
        & (m["Package indicator"].str.upper() != "TRUE")
        & (m["dc"] == "A004")            # Act/360 fixed leg
        & m["fixed_freq"].isin(ANNUAL_OR_TERM)
        & m["float_freq"].isin(ANNUAL_OR_TERM)
        & m["Other payment type"].isna()  # no other payment (for example an upfront, UFRO): the fixed rate is a par rate
        & m["fixed"].notna()
        & m["eff"].notna()
        & m["exp"].notna()
    ].copy()
    day = m[m["exec_date"] == AS_OF]
    round_step = (day["fixed"] * 1e4).round(6) % 5 == 0
    has_upfront = day["Other payment type"].notna()
    round_share = {"with": float(round_step[has_upfront].mean()), "without": float(round_step[~has_upfront].mean()),
                   "n_with": int(has_upfront.sum()), "n_without": int((~has_upfront).sum()),
                   "codes": day["Other payment type"].dropna().value_counts().to_dict()}
    n_upfront_excluded = int(
        ((m["exec_date"] == AS_OF) & (m["Package indicator"].str.upper() != "TRUE") & (m["dc"] == "A004")
         & m["Other payment type"].notna()).sum()
    )
    n_mnth_excluded = int(
        ((m["exec_date"] == AS_OF) & (m["Package indicator"].str.upper() != "TRUE") & (m["dc"] == "A004")
         & (m["fixed_freq"].eq("MNTH") | m["float_freq"].eq("MNTH"))).sum()
    )
    s = base[base["eff"] == pd.Timestamp(SPOT)].copy()
    n_filtered = len(s)

    # Every print starting on or after spot, for out-of-sample repricing by
    # verify.py: spot-starting prints at non-standard maturities, and
    # forward-starting prints (for example on IMM dates) the curve never saw.

    dc_counts = m["dc"].fillna("(blank)").value_counts().to_dict()
    fixed_freq_counts = m["Fixed rate payment frequency period-Leg 1"].fillna(m["Fixed rate payment frequency period-Leg 2"]).fillna("(blank)").value_counts().to_dict()
    float_freq_counts = m["Floating rate payment frequency period-Leg 2"].fillna(m["Floating rate payment frequency period-Leg 1"]).fillna("(blank)").value_counts().to_dict()

    # Tenor in whole months from effective to expiration, using calendar-month
    # arithmetic with a 4-day tolerance for business-day adjustment.
    def months_between(eff: pd.Timestamp, exp: pd.Timestamp) -> int | None:
        for k in TENORS_MONTHS:
            target = eff + pd.DateOffset(months=k)
            if abs((exp - target).days) <= 4:
                return k
        return None

    s["months"] = [months_between(e, x) for e, x in zip(s["eff"], s["exp"])]
    s = s[s["months"].notna()].copy()
    s["months"] = s["months"].astype(int)

    # Close adjustment. Prints are executions across the day and rates move
    # during the day, so a print's level depends on when it traded. The model
    # is one intraday factor: each print's fixed rate is its tenor's close
    # level plus a tenor-specific multiple (the loading) of a common intraday
    # path, measured in hours of New York time relative to the last traded
    # hour. The path is fitted by median regression (off-market prints carry
    # no leverage) on the tenors of two years and longer, whose loading is 1 by
    # definition. Each shorter tenor's loading is fitted by median regression
    # on the path where it has enough prints across enough hours, and is
    # interpolated in months between fitted neighbours otherwise; loadings are
    # held to [0, 1]. The quote is the median of the close-adjusted prints.
    core = s[s["months"] >= PATH_MIN_MONTHS]
    ref_hour = int(core["hour"].max())
    X = pd.concat(
        [pd.get_dummies(core["months"], prefix="m", dtype=float),
         pd.get_dummies(core["hour"], prefix="h", dtype=float).drop(columns=f"h_{ref_hour}")],
        axis=1,
    )
    coef = dict(zip(X.columns, sm.QuantReg(core["fixed"].astype(float).values, X.values).fit(q=0.5, max_iter=5000).params))
    path = {h: coef.get(f"h_{h}", 0.0) for h in sorted(s["hour"].unique())}
    if any(h not in set(core["hour"]) for h in path):
        raise SystemExit("an hour with short-tenor prints has no swap prints to estimate the path")
    s["path"] = s["hour"].map(path)

    fitted = {}
    for k, d in s.groupby("months"):
        est = None
        if len(d) >= LOADING_MIN_PRINTS and d["hour"].nunique() >= LOADING_MIN_HOURS:
            est = float(sm.QuantReg(d["fixed"].values, sm.add_constant(d["path"].values, has_constant="add")).fit(q=0.5, max_iter=5000).params[1])
        fitted[k] = est
    def loading_for(months: float) -> float:
        if months >= PATH_MIN_MONTHS:
            return 1.0
        pts = sorted((k, min(1.0, max(0.0, v))) for k, v in fitted.items() if v is not None and k < PATH_MIN_MONTHS) + [(PATH_MIN_MONTHS, 1.0)]
        if months <= pts[0][0]:
            return pts[0][1]
        for (k0, v0), (k1, v1) in zip(pts, pts[1:]):
            if k0 <= months <= k1:
                return v0 + (v1 - v0) * (months - k0) / (k1 - k0)
        return 1.0
    loadings = {k: loading_for(k) for k in TENORS_MONTHS}
    s["loading"] = s["months"].map(loadings)
    s["close_adj"] = s["fixed"] - s["loading"] * s["path"]

    pd.DataFrame([{"hour_ny": h, "path_bp": round(1e4 * v, 3), "prints": int((s["hour"] == h).sum()),
                   "reference_hour": ref_hour} for h, v in path.items()]).to_csv(OUT / "intraday_path.csv", index=False)
    pd.DataFrame([{"tenor": label(k), "months": k, "loading_used": round(loadings[k], 4),
                   "loading_fitted": None if fitted.get(k) is None else round(fitted[k], 4),
                   "source": "path tenor (1 by definition)" if k >= PATH_MIN_MONTHS else ("fitted" if fitted.get(k) is not None else "interpolated")}
                  for k in TENORS_MONTHS]).to_csv(OUT / "intraday_loadings.csv", index=False)

    rows = []
    for k in TENORS_MONTHS:
        t = s[s["months"] == k]
        if len(t) == 0:
            continue
        med = t["close_adj"].median()
        q1, q3 = t["close_adj"].quantile([0.25, 0.75])
        r1, r3 = t["fixed"].quantile([0.25, 0.75])
        within = int((abs(t["close_adj"] - med) <= 0.0001).sum())
        rows.append(
            {
                "tenor": label(k),
                "months": k,
                "trades": int(len(t)),
                "quote_pct": round(100 * med, 4),
                "iqr_bp": round(1e4 * (q3 - q1), 2),
                "within_1bp_of_quote": within,
                "raw_median_pct": round(100 * t["fixed"].median(), 4),
                "raw_iqr_bp": round(1e4 * (r3 - r1), 2),
                "notional_usd": int(t["notional"].fillna(0).sum()),
                "loading": round(loadings[k], 4),
                "used_in_bootstrap": bool(len(t) >= MIN_TRADES and 1e4 * (q3 - q1) <= MAX_IQR_BP),
            }
        )
    # Every print starting on or after spot, close-adjusted in the same way,
    # for out-of-sample repricing by verify.py: spot-starting prints at
    # non-standard maturities and forward-starting prints (for example on IMM
    # dates) that the bootstrap never saw.
    oos = base[base["eff"] >= pd.Timestamp(SPOT)].copy()
    # A print's loading is taken at its months from the as-of date to expiration.
    oos = oos[oos["hour"].isin(list(path))].copy()
    oos["loading"] = [loading_for((x - pd.Timestamp(AS_OF)).days / 30.4375) for x in oos["exp"]]
    oos["close_adjusted_rate"] = oos["fixed"] - oos["loading"] * oos["hour"].map(path)
    oos["in_bootstrap_set"] = oos.index.isin(s[s["months"].isin([r["months"] for r in rows if r["used_in_bootstrap"]])].index)
    oos[["eff", "exp"]] = oos[["eff", "exp"]].apply(lambda c: c.dt.date.astype(str))
    oos[["eff", "exp", "hour", "fixed", "close_adjusted_rate", "loading", "notional", "in_bootstrap_set"]].rename(
        columns={"eff": "effective", "exp": "expiration", "hour": "hour_ny", "fixed": "fixed_rate"}
    ).to_csv(OUT / "trades.csv", index=False)

    # Leave-one-tenor-out check of the close adjustment: for each tenor that
    # helps define the path, refit the path without that tenor's prints and
    # recompute its quote, so the quote is not adjusted by its own prints.
    def fit_path(d):
        X = pd.concat([pd.get_dummies(d["months"], prefix="m", dtype=float),
                       pd.get_dummies(d["hour"], prefix="h", dtype=float).drop(columns=f"h_{ref_hour}", errors="ignore")], axis=1)
        c = dict(zip(X.columns, sm.QuantReg(d["fixed"].astype(float).values, X.values).fit(q=0.5, max_iter=5000).params))
        return {h: c.get(f"h_{h}", 0.0) for h in path}
    loo = []
    for r in rows:
        k = r["months"]
        if k < PATH_MIN_MONTHS or not r["used_in_bootstrap"]:
            continue
        p_k = fit_path(core[core["months"] != k])
        t = s[s["months"] == k]
        q_loo = (t["fixed"] - t["hour"].map(p_k)).median()
        loo.append({"tenor": r["tenor"], "quote_pct": r["quote_pct"], "quote_leave_one_out_pct": round(100 * q_loo, 4),
                    "difference_bp": round(1e4 * q_loo - 100 * r["quote_pct"], 3)})
    pd.DataFrame(loo).to_csv(OUT / "quotes_leave_one_out.csv", index=False)

    # Off-market prints: filtered prints far from their tenor's quote after adjustment.
    s["dev_bp"] = 1e4 * s["close_adj"] - s["months"].map({r["months"]: 100 * r["quote_pct"] for r in rows})
    far = s[s["dev_bp"].abs() > OFF_MARKET_BP]
    off_market = {"count": int(len(far)), "min_abs_bp": float(far["dev_bp"].abs().min()) if len(far) else 0.0,
                  "max_abs_bp": float(far["dev_bp"].abs().max()) if len(far) else 0.0,
                  "tenors": sorted({label(k) for k in far["months"]}, key=lambda x: TENORS_MONTHS[[label(m) for m in TENORS_MONTHS].index(x)])}
    near = s[s["dev_bp"].abs() <= OFF_MARKET_BP]
    off_market["max_quote_change_if_removed_bp"] = max(
        abs(1e4 * near[near["months"] == r["months"]]["close_adj"].median() - 100 * r["quote_pct"]) for r in rows if r["used_in_bootstrap"])

    q = pd.DataFrame(rows)
    q.to_csv(OUT / "quotes.csv", index=False)

    sofr = json.loads(NYFED_SOFR.read_text())["refRates"]
    fixing = {r["effectiveDate"]: r for r in sofr}
    # The SOFR value for 2026-09-17 is published on the morning of 2026-09-18
    # (NY Fed publishes at ~8:00 ET the next business day), so it is the last
    # fixing known at the as-of close.
    last_known_date = "2026-09-17"
    fx = fixing[last_known_date]
    (OUT / "sofr_fixing.json").write_text(
        json.dumps(
            {
                "as_of": AS_OF,
                "last_known_fixing_date": last_known_date,
                "sofr_percent": fx["percentRate"],
                "volume_billions": fx["volumeInBillions"],
                "realised_after_as_of": {d: fixing[d]["percentRate"] for d in ("2026-09-18", "2026-09-21") if d in fixing},
                "source": "Federal Reserve Bank of New York, SOFR (markets.newyorkfed.org API), snapshot data/nyfed_sofr_2026-09-01_2026-09-22.json",
            },
            indent=2,
        )
    )

    with open(OUT / "quotes.md", "w") as f:
        f.write(f"Table: USD SOFR OIS par-rate quotes derived from public trade prints, as-of {AS_OF} (spot {SPOT}).\n\n")
        f.write("| Tenor | Trades | Quote: close-adjusted median (%) | IQR adjusted (bp) | Within 1 bp of quote | Raw median (%) | IQR raw (bp) | Notional (USD bn) | Used |\n")
        f.write("|---|---:|---:|---:|---:|---:|---:|---:|:---:|\n")
        for r in rows:
            f.write(
                f"| {r['tenor']} | {r['trades']} | {r['quote_pct']:.4f} | {r['iqr_bp']:.1f} | {r['within_1bp_of_quote']} | "
                f"{r['raw_median_pct']:.4f} | {r['raw_iqr_bp']:.1f} | {r['notional_usd'] / 1e9:.2f} | {'yes' if r['used_in_bootstrap'] else 'no'} |\n"
            )
        f.write(
            f"\nSource: DTCC Public Price Dissemination, CFTC cumulative rates report {AS_OF} "
            f"(data/{DTCC_ZIP.name}); filters in derive_quotes.py. "
            f"Rows in report: {n_all}; USD SOFR OIS new trades: {n_sofr_ois}; after filters "
            f"(executed {AS_OF}, effective {SPOT}, non-package, Act/360 fixed leg, annual or single-payment "
            f"schedule on both legs): {n_filtered}; matched to a standard tenor: {len(s)}. "
            f"Excluded across all effective dates: {n_mnth_excluded} monthly-paying prints; "
            f"{n_upfront_excluded} non-package Act/360 prints executed on the as-of date that carry another payment "
            f"(DTCC field 'Other payment type' not blank), whose fixed rate is then not a par rate. "
            f"Among all {round_share['n_with'] + round_share['n_without']} new USD SOFR OIS prints executed on the as-of date "
            f"(packages included; codes in 'Other payment type': "
            + ", ".join(f"{k} {v}" for k, v in round_share["codes"].items())
            + f"), a fixed rate on a round 5 bp step: {round_share['with']:.0%} of the {round_share['n_with']} that carry another payment, "
            f"{round_share['without']:.0%} of the {round_share['n_without']} that do not.\n"
        )
        f.write(
            "\nFixed-leg day count codes among the day's new USD SOFR OIS trades (before filters): "
            + ", ".join(f"{k} {v}" for k, v in dc_counts.items()) + ".\n"
        )
        f.write(
            "Fixed-leg payment frequency period codes (field 'Fixed rate payment frequency period-Leg 1', or Leg 2 where Leg 1 is blank): "
            + ", ".join(f"{k} {v}" for k, v in fixed_freq_counts.items()) + ".\n"
        )
        f.write(
            "Floating-leg payment frequency period codes (field 'Floating rate payment frequency period-Leg 2', or Leg 1 where Leg 2 is blank): "
            + ", ".join(f"{k} {v}" for k, v in float_freq_counts.items()) + ".\n"
        )
        f.write(f"\nTable: common intraday path (median regression on tenors of {PATH_MIN_MONTHS // 12}Y and longer; bp relative to {ref_hour:02d}h New York time).\n\n")
        f.write("| Hour (NY) | " + " | ".join(f"{h:02d}h" for h in path) + " |\n|---|" + "---:|" * len(path) + "\n")
        f.write("| Path (bp) | " + " | ".join(f"{1e4 * v:+.1f}" for v in path.values()) + " |\n")
        f.write("\nTable: loading of each tenor on the intraday path.\n\n| Tenor | Loading used | Fitted | Source |\n|---|---:|---:|---|\n")
        for k in TENORS_MONTHS:
            fv = fitted.get(k)
            src = "path tenor (1 by definition)" if k >= PATH_MIN_MONTHS else ("fitted" if fv is not None else "interpolated")
            f.write(f"| {label(k)} | {loadings[k]:.2f} | {'' if fv is None else f'{fv:.2f}'} | {src} |\n")
        f.write(f"\nHours are New York clock hours; hour {ref_hour:02d} is the reference, and a print in it (executed {ref_hour:02d}:00-{ref_hour:02d}:59) is not adjusted. "
                "A print's loading is taken at its months from the as-of date to expiration; loadings are held to the range 0 to 1.\n")
        f.write("\nSource: out/intraday_path.csv, out/intraday_loadings.csv, derive_quotes.py (statsmodels QuantReg, q = 0.5).\n")
        f.write("\nTable: leave-one-tenor-out check: each path tenor's quote recomputed with the path fitted without that tenor's prints.\n\n"
                "| Tenor | Quote (%) | Leave-one-out quote (%) | Difference (bp) |\n|---|---:|---:|---:|\n")
        for r in loo:
            f.write(f"| {r['tenor']} | {r['quote_pct']:.4f} | {r['quote_leave_one_out_pct']:.4f} | {r['difference_bp']:+.3f} |\n")
        f.write(f"\nLargest absolute difference: {max(abs(r['difference_bp']) for r in loo):.3f} bp. Source: out/quotes_leave_one_out.csv, derive_quotes.py.\n")
        f.write(f"\nOff-market prints: {off_market['count']} of the {len(s)} matched prints sit more than {OFF_MARKET_BP:g} bp from their tenor's quote after adjustment "
                f"({off_market['min_abs_bp']:.1f} to {off_market['max_abs_bp']:.1f} bp; tenors {', '.join(off_market['tenors'])}). "
                f"They are kept in the medians and reported here; removing them would move no used quote by more than {off_market['max_quote_change_if_removed_bp']:.3f} bp.\n")
        f.write(
            f"\nSOFR fixing used as the overnight anchor: {fx['percentRate']}% for {last_known_date} "
            f"(published {AS_OF}). Source: Federal Reserve Bank of New York.\n"
        )
    print(q.to_string(index=False))
    print(f"\nrows={n_all} sofr_ois_new={n_sofr_ois} filtered={n_filtered} matched={len(s)}")
    print(f"SOFR fixing {last_known_date}: {fx['percentRate']}%")


if __name__ == "__main__":
    main()
