Attestation arrival - 2025-12-18

df = load_parquet("attestation_propagation", target_date)

# Convert hour to datetime
df["hour"] = pd.to_datetime(df["hour"])

total_hours = len(df)
total_attestations = df["attestation_count"].sum()
total_slots = df["slot_count"].sum()

# Overall percentiles (weighted by attestation count)
p50_overall = np.average(df["p50_ms"], weights=df["attestation_count"])
p90_overall = np.average(df["p90_ms"], weights=df["attestation_count"])
p99_overall = np.average(df["p99_ms"], weights=df["attestation_count"])

print(f"Hours: {total_hours:,}")
print(f"Slots: {total_slots:,}")
print(f"Attestations observed: {total_attestations:,}")
print(f"\nAttestation arrival latency (from slot start):")
print(f"  p50: {p50_overall:.0f} ms ({p50_overall/1000:.2f} s)")
print(f"  p90: {p90_overall:.0f} ms ({p90_overall/1000:.2f} s)")
print(f"  p99: {p99_overall:.0f} ms ({p99_overall/1000:.2f} s)")

# Melt percentile columns for line chart
percentile_cols = ["p50_ms", "p75_ms", "p80_ms", "p85_ms", "p90_ms", "p95_ms", "p99_ms"]
df_long = df.melt(
    id_vars=["hour"],
    value_vars=percentile_cols,
    var_name="percentile",
    value_name="latency_ms"
)
df_long["latency_s"] = df_long["latency_ms"] / 1000
df_long["percentile_label"] = df_long["percentile"].str.replace("_ms", "").str.upper()

# Color scheme for percentiles
colors = {
    "P50": "#2ecc71",
    "P75": "#3498db",
    "P80": "#9b59b6",
    "P85": "#e67e22",
    "P90": "#e74c3c",
    "P95": "#c0392b",
    "P99": "#7b241c",
}

fig = px.line(
    df_long,
    x="hour",
    y="latency_s",
    color="percentile_label",
    color_discrete_map=colors,
    labels={"hour": "Time", "latency_s": "Latency (seconds)", "percentile_label": "Percentile"},
    category_orders={"percentile_label": ["P50", "P75", "P80", "P85", "P90", "P95", "P99"]},
)
fig.update_traces(line=dict(width=2))

# Add 4-second reference line (attestation deadline)
fig.add_hline(y=4, line_dash="dash", line_color="gray", annotation_text="4s deadline", annotation_position="right")

fig.update_layout(
    margin=dict(l=60, r=30, t=30, b=60),
    legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="left", x=0),
    height=450,
    xaxis=dict(title="Time (UTC)"),
)
fig.show(config={"responsive": True})

# Create histogram of latencies
fig = make_subplots(rows=1, cols=2, subplot_titles=("P50 latency distribution", "P99 latency distribution"))

fig.add_trace(
    go.Histogram(x=df["p50_ms"] / 1000, nbinsx=30, marker_color="#2ecc71", name="P50"),
    row=1, col=1
)
fig.add_trace(
    go.Histogram(x=df["p99_ms"] / 1000, nbinsx=30, marker_color="#e74c3c", name="P99"),
    row=1, col=2
)

fig.update_xaxes(title_text="Latency (s)", row=1, col=1)
fig.update_xaxes(title_text="Latency (s)", row=1, col=2)
fig.update_yaxes(title_text="Hour count", row=1, col=1)
fig.update_yaxes(title_text="Hour count", row=1, col=2)

fig.update_layout(
    margin=dict(l=60, r=30, t=60, b=60),
    showlegend=False,
    height=350,
)
fig.show(config={"responsive": True})

df_comm = load_parquet("attestation_by_committee", target_date)

# Convert committee to int for proper sorting
df_comm["committee_num"] = pd.to_numeric(df_comm["committee"], errors="coerce").fillna(-1).astype(int)
df_comm = df_comm.sort_values("committee_num")

# Calculate overall median for comparison
overall_p50 = df_comm["p50_ms"].median()
overall_p99 = df_comm["p99_ms"].median()

print(f"Committees: {len(df_comm)}")
print(f"Total attestations: {df_comm['attestation_count'].sum():,}")
print(f"\nCross-committee medians:")
print(f"  P50: {overall_p50:.0f} ms")
print(f"  P99: {overall_p99:.0f} ms")

# Bar chart of P50 and P99 by committee
fig = go.Figure()

fig.add_trace(go.Bar(
    x=df_comm["committee"],
    y=df_comm["p50_ms"] / 1000,
    name="P50",
    marker_color="#2ecc71",
))
fig.add_trace(go.Bar(
    x=df_comm["committee"],
    y=df_comm["p99_ms"] / 1000,
    name="P99",
    marker_color="#e74c3c",
))

# Add reference lines
fig.add_hline(y=4, line_dash="dash", line_color="gray", annotation_text="4s deadline")

fig.update_layout(
    barmode="group",
    margin=dict(l=60, r=30, t=30, b=60),
    xaxis=dict(title="Committee (subnet)", tickangle=45, dtick=4),
    yaxis=dict(title="Latency (seconds)"),
    legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="left", x=0),
    height=400,
)
fig.show(config={"responsive": True})

# Identify slow committees (P99 > 1.5x median P99)
threshold = overall_p99 * 1.5
df_slow = df_comm[df_comm["p99_ms"] > threshold].copy()
df_slow = df_slow.sort_values("p99_ms", ascending=False)

if len(df_slow) > 0:
    print(f"Committees with P99 > {threshold:.0f} ms ({threshold/1000:.1f}s):")
    display_df = df_slow[["committee", "attestation_count", "p50_ms", "p90_ms", "p99_ms", "max_ms"]].copy()
    display_df.columns = ["Committee", "Attestations", "P50 (ms)", "P90 (ms)", "P99 (ms)", "Max (ms)"]
    for col in ["P50 (ms)", "P90 (ms)", "P99 (ms)", "Max (ms)"]:
        display_df[col] = display_df[col].round(0).astype(int)
    display(display_df)
else:
    print(f"No committees with P99 > {threshold:.0f} ms - all subnets performing within normal range.")

# Prepare heatmap data
heatmap_cols = ["p50_ms", "p75_ms", "p90_ms", "p95_ms", "p99_ms"]
heatmap_data = df_comm.set_index("committee")[heatmap_cols].T
heatmap_data.index = ["P50", "P75", "P90", "P95", "P99"]

# Convert to seconds for display
heatmap_data = heatmap_data / 1000

fig = go.Figure(data=go.Heatmap(
    z=heatmap_data.values,
    x=heatmap_data.columns,
    y=heatmap_data.index,
    colorscale="YlOrRd",
    colorbar=dict(title="Latency (s)"),
))

fig.update_layout(
    margin=dict(l=60, r=30, t=30, b=60),
    xaxis=dict(title="Committee", tickangle=45, dtick=4),
    yaxis=dict(title="Percentile", autorange="reversed"),
    height=300,
)
fig.show(config={"responsive": True})

# Summary table
summary = {
    "Metric": [
        "Total hours",
        "Total slots",
        "Attestations observed",
        "Committees (subnets)",
        "P50 arrival (ms)",
        "P75 arrival (ms)",
        "P90 arrival (ms)",
        "P95 arrival (ms)",
        "P99 arrival (ms)",
        "Max arrival (ms)",
        "Slow committees (P99 > 1.5x median)",
    ],
    "Value": [
        f"{total_hours:,}",
        f"{total_slots:,}",
        f"{total_attestations:,}",
        f"{len(df_comm)}",
        f"{p50_overall:.0f}",
        f"{np.average(df['p75_ms'], weights=df['attestation_count']):.0f}",
        f"{p90_overall:.0f}",
        f"{np.average(df['p95_ms'], weights=df['attestation_count']):.0f}",
        f"{p99_overall:.0f}",
        f"{df['max_ms'].max():.0f}",
        f"{len(df_slow)}",
    ],
}

pd.DataFrame(summary)