"""fred-release-brief-2026-09-05.py — the numbers behind the 2026-09-05 brief on the August 2026 Employment Situation as it appears in FRED.

Input : datasets/fred-release-brief-2026-09-05.csv (FRED PAYEMS thousands, UNRATE %, CES0500000003 average hourly
        earnings USD, CIVPART %; monthly since 2024-01), retrieved 2026-09-05 via the public fredgraph CSV.
        Re-download with --download.
Method: monthly payroll change = PAYEMS_t - PAYEMS_{t-1}; three-month average of that; average hourly earnings
        12-month percent change; the levels of UNRATE and CIVPART. This is the vintage available on the
        retrieval date; BLS revises the two prior months each release and benchmarks annually.
Run   : python code/fred-release-brief-2026-09-05.py [--download]
Needs : Python 3.13, pandas 3.0.2.
"""
import io
import sys
import urllib.request

import pandas as pd

CSV = "datasets/fred-release-brief-2026-09-05.csv"


def download():
    frames = []
    for sid in ("PAYEMS", "UNRATE", "CES0500000003", "CIVPART"):
        req = urllib.request.Request(f"https://fred.stlouisfed.org/graph/fredgraph.csv?id={sid}",
                                     headers={"User-Agent": "prism-data-lab/1.0"})
        txt = urllib.request.urlopen(req, timeout=60).read().decode()
        frames.append(pd.read_csv(io.StringIO(txt), na_values=".", index_col=0).iloc[:, 0].rename(sid))
    df = pd.concat(frames, axis=1)
    df.index.name = "date"
    df[df.index >= "2024-01-01"].to_csv(CSV)


def main():
    if "--download" in sys.argv:
        download()
    df = pd.read_csv(CSV, parse_dates=["date"], index_col="date")
    df["payroll_change"] = df["PAYEMS"].diff()
    df["payroll_3m_avg"] = df["payroll_change"].rolling(3).mean()
    df["ahe_yoy"] = 100 * (df["CES0500000003"] / df["CES0500000003"].shift(12) - 1)
    t = df.loc["2025-09-01":]
    print("| month | payrolls (thousands) | monthly change | 3-month average | unemployment rate | participation | avg hourly earnings y/y |")
    print("|---|---|---|---|---|---|---|")
    for d, r in t.iterrows():
        u = f"{r['UNRATE']:.1f}" if pd.notna(r["UNRATE"]) else "n/a"
        p = f"{r['CIVPART']:.1f}" if pd.notna(r["CIVPART"]) else "n/a"
        print(f"| {d:%Y-%m} | {r['PAYEMS']:,.0f} | {r['payroll_change']:+,.0f} | {r['payroll_3m_avg']:+,.0f} | {u} | {p} | {r['ahe_yoy']:.2f}% |")
    last = df.iloc[-1]
    print(f"\nlatest month {df.index[-1]:%Y-%m}: payrolls {last['PAYEMS']:,.0f}k ({last['payroll_change']:+,.0f}k), "
          f"unemployment {last['UNRATE']:.1f}%, participation {last['CIVPART']:.1f}%, AHE y/y {last['ahe_yoy']:.2f}%")
    print(f"payroll change over the last 12 months: {df['PAYEMS'].iloc[-1] - df['PAYEMS'].iloc[-13]:+,.0f}k; "
          f"2025 calendar-year total: {df.loc['2025', 'payroll_change'].sum():+,.0f}k; 2026 to date: {df.loc['2026', 'payroll_change'].sum():+,.0f}k")
    print(f"UNRATE range in the file: {df['UNRATE'].min():.1f} ({df['UNRATE'].idxmin():%Y-%m}) to {df['UNRATE'].max():.1f} ({df['UNRATE'].idxmax():%Y-%m}); "
          f"months with no UNRATE value: {[f'{d:%Y-%m}' for d in df.index[df['UNRATE'].isna()]]}")


if __name__ == "__main__":
    main()