"""SOURCE render logic for TI-A-03 (REFERENCE ONLY).
This is the exact function from our pipeline. It is DB-coupled — you cannot run it without our database.
The query result it produces is materialized for you in data.csv, and the rendered output is figure.json
(load that to display the chart). This file is provided so you can understand / re-theme the logic.
"""
def render_TI_A_03(reg: dict) -> dict:
"""Spike-Pulse Feed - recent spike events ordered by peak date + ratio."""
chart_id = "TI-A-03"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
df = lib.run_query(conn, """
SELECT * FROM (
SELECT DISTINCT ON (imdb_id)
imdb_id, spike_start_date, spike_peak_date, spike_end_date,
ratio::numeric AS ratio, peak_value, baseline_value, catalyst_type, catalyst_confidence_score::numeric AS catalyst_confidence
FROM spike_pulse_events
WHERE spike_peak_date >= CURRENT_DATE - INTERVAL '30 days'
AND ratio IS NOT NULL
AND baseline_value >= 20 -- real, measurable "normal" level (else ratio = divide-by-near-zero noise)
AND ratio >= 2 -- a genuine surge above the title's own norm
AND COALESCE((SELECT r.num_votes FROM categorization_imdb_ratings r
WHERE r.imdb_id = spike_pulse_events.imdb_id), 0)
>= CASE WHEN (SELECT bt.title_type FROM categorization_imdb_title_basics bt
WHERE bt.imdb_id = spike_pulse_events.imdb_id) = 'movie'
THEN 3000 ELSE 20000 END -- type-aware corroboration gate (cred>=0.5: films K=3K, shows K=20K)
ORDER BY imdb_id, peak_value DESC, ratio DESC, catalyst_confidence_score DESC NULLS LAST, catalyst_type
-- full tie-break incl. catalyst: duplicate spike rows differing only in catalyst tag must
-- resolve to the SAME row on both 30d and 90d boards (one name for one event)
) t
ORDER BY peak_value DESC -- then rank titles by ABSOLUTE demand reached, so recognizable hits lead
LIMIT 40;
""")
q_sec = time.time() - t0
if df.empty:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No qualifying spikes (baseline>=20, ratio>=2) in last 30 days")
conn.close(); write_shim(output_dir, chart_id)
return {"status": "needs_spec_review"}
df = lib.drop_demand_orphans(df, conn) # hide in-theaters / not-yet-streaming demand-orphans
titles = lib.resolve_titles(conn, df["imdb_id"].unique().tolist())
# freshness = the FEED's latest event day (ungated), not the latest QUALIFYING peak: the gated
# set's newest peak can trail the feed by days and then reads as unexplained staleness next to
# companion boards stamped with the same feed's true as-of date
feed_fresh = str(lib.run_query(conn, "SELECT MAX(spike_peak_date) AS d FROM spike_pulse_events")["d"].iloc[0])
conn.close()
df = df.merge(titles[["imdb_id", "display_title"]], on="imdb_id", how="left")
df["display_title"] = df["display_title"].fillna(df["imdb_id"])
df["ratio"] = df["ratio"].astype(float); df["peak_value"] = df["peak_value"].astype(float)
df["catalyst_clean"] = df["catalyst_type"].map(_clean_catalyst)
n_tagged = int((df["catalyst_clean"] != "").sum())
# honest multiple formatting: one decimal kept unless it is exactly .0 (13.5x must not round up
# to 14x in a headline while a lower bar visibly prints a bigger multiple)
def fr(v: float) -> str:
return f"{v:.1f}".rstrip("0").rstrip(".")
# Top 15 by PEAK demand (recognizable hits lead), one row per title
df_plot = df.sort_values("peak_value", ascending=False).drop_duplicates("imdb_id").head(15).sort_values("peak_value", ascending=True)
df_plot["label"] = df_plot["display_title"].map(lambda t: lib.ellipsize(t, 48))
# navy = the leader (highest peak, the headline subject) ONLY; pale-steel field
colors = [lib.NAVY if i == len(df_plot) - 1 else lib.PALE_BLUE for i in range(len(df_plot))]
# cause tags in purple (a tentative automated guess - amber stays reserved for the NEW chip);
# untagged rows say 'cause undetected' in grey instead of leaving the biggest multiples unexplained.
# Multiplication glyph = letter 'x' everywhere on this surface (footer writes '2x+', so '×' mixed glyphs)
bar_text = [f" {int(p):,} · {fr(r)}x normal · " + (f"{cat}" if cat
else f"cause undetected")
for p, r, cat in zip(df_plot["peak_value"], df_plot["ratio"], df_plot["catalyst_clean"])]
fig = go.Figure()
fig.add_trace(go.Bar(
x=df_plot["peak_value"], y=df_plot["label"], orientation="h",
marker=dict(color=colors),
text=bar_text,
textposition="outside", textfont=dict(size=10, color=lib.INK),
cliponaxis=False, showlegend=False,
customdata=[[fr(r)] for r in df_plot["ratio"]],
hovertemplate="%{y}
peaked at a single-day Demand Score of %{x:,.0f} · %{customdata[0]}x its normal level",
))
fig.update_xaxes(range=lib.pad_range(df_plot["peak_value"], labels=bar_text, font_px=10, fig_width=1180))
top_event = df_plot.iloc[-1] # highest peak
n_3x = int((df["ratio"] >= 3.0).sum())
# claim-first phrasing: a headline that OPENS with a raw title name can misparse when the name
# is an ordinary English word (the show 'From' read as a preposition on first skim)
takeaway = (f"The month's highest breakout peak: {top_event['display_title']} hit a single-day Demand Score of "
f"{int(top_event['peak_value']):,}, {fr(float(top_event['ratio']))}x its normal level")
what = ("The 15 biggest surges of the last 30 days, ranked by the peak single-day Demand Score they hit - so a smaller title can show "
"a bigger multiple lower down. Each label shows the peak score, the multiple above that title's own normal level, and the likely cause.")
why = (f"Spikes are breakout moments - a new season, a viral clip, an awards run. This is the 'what just popped' feed, led by the titles audiences actually "
f"showed up for ({n_3x} hit 3x+ their normal Demand Score this month). These are the titles to feature while they're hot.")
footer = lib.assemble_footer(
pc_id="PC-A-01", caution_labels=["real surges only (2x+ a measurable baseline)",
"widely-known titles only (IMDb-vote floor)",
"cause tags are automated guesses, not confirmed",
"CL-013 spikes peaking in the last 30 days"],
freshness_date=feed_fresh,
sources=["spike_pulse_events"],
extra="")
fig.update_layout(**lib.layout_v2(takeaway, what, width=1180, height=720),
xaxis=lib.standard_xaxis("Peak single-day Demand Score reached during the spike"),
yaxis=lib.standard_yaxis(showgrid=False))
lib.add_why_and_footer(fig, why, footer)
lesson = {"reader_title": takeaway, "reader_subtitle": what,
"claim_strength": "strong", "rules_applied": "30d, baseline>=20, ratio>=2, top 15 by PEAK single-day Demand Score"}
lib.save_csv(df, output_dir)
lib.export_chart(fig, output_dir, width=1180, height=720)
lib.write_lesson_design(output_dir, chart_id, f"# {chart_id}\n\n{takeaway}\n\n{what}\n")
write_shim(output_dir, chart_id)
lib.write_manifest(output_dir, chart_id, "ok", reg["spec_file"], pc_id="PC-A-01",
intended_lesson_from_spec="Recent detected spike events",
rendered_lesson=lesson, row_count=len(df),
freshness_date=feed_fresh,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}