"""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)}