"""SOURCE render logic for TI-RC-02 (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_RC_02(reg: dict) -> dict:
"""The Undercurrent (weekly) - the demand board's HIDDEN MOMENTUM: which established top shows are heating up
vs cooling down, as the trajectory complement to RC-01's snapshot (M053 + panel-normalized share).
First-principles (2026-06-05): RC-01 already shows the top-10 snapshot WITH its own weekly deltas and debut
flags, so RC-02's only non-redundant job is the trajectory RC-01 cannot show: who is gaining/losing demand
underneath the ranking. Signal = share-of-panel (di / SUM di of all US tvSeries) NOW vs its trailing 4-week
average. Share-of-panel is the only panel-SAFE momentum measure: raw DI-growth is contaminated by panel-size
swings (L-046; the panel ran ~1.29M on 2026-05-27 vs ~2.19M on 06-03), and raw rank-jumps are scale-dependent
(the tail is ~170x more rank-compressed than the top). Content-drop level-shifts (>= +100%, a doubling vs the
4-week norm, e.g. a new season) and brand-new debuts (no 4-week baseline yet) are surfaced as a side-note, never
as main bars, so the subtle established-board momentum stays legible. Organic momentum tops out ~+50% (p95 of the
weekly jitter baseline); the +25% breakout line = ~p90.
"""
chart_id = "TI-RC-02"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
df = lib.run_query(conn, """
WITH win AS (SELECT (SELECT MAX(file_date) FROM metrics_demand_rank_history) AS d0),
rk AS (SELECT r.imdb_id, r.di_rank, r.di, COALESCE(r.is_new_title, false) AS is_new
FROM metrics_demand_rank_history r
WHERE r.file_date = (SELECT d0 FROM win)
AND r.geo_code='US' AND r.title_type='shows' AND r.di_rank <= 20),
tot AS (SELECT t.file_date, SUM(t.reach) AS s
FROM title_metrics_daily t JOIN categorization_imdb_title_basics b USING (imdb_id)
WHERE t.geo_code='US' AND b.title_type IN ('tvSeries','tvMiniSeries')
AND t.file_date BETWEEN (SELECT d0 FROM win) - 28 AND (SELECT d0 FROM win)
GROUP BY t.file_date),
-- share is needed ONLY for the 20 ranked board titles (not every US title); the full panel
-- total stays in `tot`, so the momentum math is identical but ~3 orders of magnitude cheaper
-- (fixes the statement-timeout that left RC-02 unpublished — same pattern as the RC-06 fix).
sh AS (SELECT t.imdb_id, t.file_date, t.reach::numeric / tot.s AS share
FROM title_metrics_daily t JOIN tot USING (file_date)
WHERE t.geo_code='US' AND t.imdb_id IN (SELECT imdb_id FROM rk)),
cur AS (SELECT imdb_id, share AS cur_share FROM sh WHERE file_date = (SELECT d0 FROM win)),
base AS (SELECT imdb_id, AVG(share) AS avg_share, COUNT(*) AS days FROM sh
WHERE file_date < (SELECT d0 FROM win) GROUP BY imdb_id)
SELECT rk.di_rank, b.primary_title AS title, rk.di::numeric AS di, rk.is_new,
COALESCE(base.days, 0) AS base_days,
(100.0*(cur.cur_share - base.avg_share)/NULLIF(base.avg_share, 0)) AS momentum,
(SELECT MAX(file_date) FROM metrics_demand_rank_history) AS freshness_date
FROM rk JOIN categorization_imdb_title_basics b USING (imdb_id)
JOIN cur USING (imdb_id) LEFT JOIN base USING (imdb_id)
ORDER BY rk.di_rank;
""")
ph = lib.run_query(conn, "SELECT degraded FROM metrics_panel_health WHERE file_date = (SELECT MAX(file_date) FROM metrics_demand_rank_history);")
panel_degraded = bool(ph["degraded"].iloc[0]) if len(ph) else False
q_sec = time.time() - t0
conn.close()
if df.empty:
lib.needs_spec_review(output_dir, chart_id, reg.get("spec_file", "specs/16_recurring.md"),
reg.get("pc_id", "PC-RC-02"), "No demand-rank history for momentum")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
df["momentum"] = df["momentum"].astype(float).round(1) # max 1 decimal anywhere (hover/axis/csv)
df["di"] = df["di"].astype(float); df["is_new"] = df["is_new"].astype(bool)
df["base_days"] = df["base_days"].astype(int)
freshness = str(df["freshness_date"].iloc[0])
# Board constants - interpolated into BOTH the filter logic and rules_applied (fix 19)
RC02_EXPLOSION_PCT = 100 # >= +100% (a DOUBLING of share vs the 4-week norm) is a content-drop level-shift, not
# organic momentum: organic weekly momentum tops out ~+50% (p95 of the jitter baseline)
RC02_TIER = 18 # the mainstream board + just below (enough shows for both heaters and coolers)
RC02_MIN_BASE_DAYS = 21 # minimum baseline days for a meaningful 4-week comparison
RC02_LEVEL_SHIFT_PCT = 50 # above this, a rise usually means new episodes landed, not gradual drift
board = df[(df["di_rank"] <= RC02_TIER) & (df["base_days"] >= RC02_MIN_BASE_DAYS) & (df["momentum"] <= RC02_EXPLOSION_PCT)].copy()
explosions = df[(df["base_days"] >= RC02_MIN_BASE_DAYS) & (df["momentum"] > RC02_EXPLOSION_PCT)].sort_values("momentum", ascending=False)
newbies = df[(df["di_rank"] <= RC02_TIER) & (df["base_days"] < RC02_MIN_BASE_DAYS)].sort_values("di_rank")
if board.empty:
lib.needs_spec_review(output_dir, chart_id, reg.get("spec_file", "specs/16_recurring.md"),
reg.get("pc_id", "PC-RC-02"), "No established-board momentum this week")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
board = board.sort_values("momentum").reset_index(drop=True) # ascending -> most-cooling at bottom (plotly)
any_shift = False
ylabels, colors, texts = [], [], []
for r in board.itertuples():
lab = f"{lib.ellipsize(str(r.title), 26)} #{int(r.di_rank)}"
ylabels.append(f"{lab}" if int(r.di_rank) == 1 else lab) # bold the chart leader (the RC-01 reframe)
colors.append(lib.GREEN if r.momentum >= 0 else lib.RED)
t = lib.fmt_signed(r.momentum, 0, "%", zero_band=0.5)
if r.momentum > RC02_LEVEL_SHIFT_PCT: # likely new-episode level-shift, flag it
t += f"†"
any_shift = True
texts.append(t)
fig = go.Figure()
fig.add_trace(go.Bar(
x=board["momentum"], y=ylabels, orientation="h",
marker=dict(color=colors, line=dict(width=0)),
text=texts, textposition="outside", textfont=dict(size=11, color=lib.INK),
cliponaxis=False, showlegend=False,
hovertemplate="%{y}
US demand %{x:+.0f}% vs its 4-week average",
))
mx = float(board["momentum"].max()); mn = float(board["momentum"].min())
pad_left = max(8.0, (mx - mn) * 0.08)
pad_right = max(14.0, (mx - mn) * 0.12)
xlo, xhi = mn - pad_left, mx + pad_right
# dynamic headline: lead with the #1-cooling reframe when it applies, else the biggest mover
board_pos = board[(board["momentum"] > 0) & (board["di_rank"] > 1)].sort_values("di_rank")
th = board.loc[board["momentum"].idxmax()]; tc = board.loc[board["momentum"].idxmin()]
challenger = board_pos.iloc[0] if not board_pos.empty else th
lead = board[board["di_rank"] == 1]
if not lead.empty and float(lead["momentum"].iloc[0]) <= -12:
lr = lead.iloc[0]
takeaway = (f"{lr['title']} still tops the US demand chart, but it's cooling while {challenger['title']} "
f"climbs {challenger['momentum']:+.0f}% right behind it")
elif th["momentum"] >= abs(tc["momentum"]):
takeaway = (f"{th['title']} is the demand board's biggest riser, up {th['momentum']:+.0f}% vs its 4-week "
f"norm, while {tc['title']} cools {abs(tc['momentum']):.0f}%")
else:
takeaway = (f"{tc['title']} is fading fastest on the demand board, down {abs(tc['momentum']):.0f}% vs its "
f"4-week norm, while {th['title']} heats up {th['momentum']:+.0f}%")
what = ("How much each show is heating up or cooling down: each bar compares this week's US demand for a show "
"against that same show's 4-week average. Green = climbing, red = cooling, across this "
f"week's {RC02_TIER} highest-demand shows.")
why = ("A #1 can be fading and a mid-tier show surging weeks before the ranking moves. Share-of-panel momentum is "
"the early read on next week's chart and the cleanest signal of who's actually winning or losing attention "
"right now, not just who is parked at the top.")
# transparency: what is deliberately off the main bars (no silent caps)
off = []
if not explosions.empty:
off.append("off-the-scale surges " + ", ".join(f"{lib.ellipsize(str(r.title), 22)} {r.momentum:+,.0f}%"
for r in explosions.head(3).itertuples()))
if not newbies.empty:
off.append("brand-new debuts, no 4-week baseline yet, " + ", ".join(f"{lib.ellipsize(str(r.title), 22)} #{int(r.di_rank)}"
for r in newbies.head(3).itertuples()))
note_y = -0.055
if off:
fig.add_annotation(text="Off the bars: " + "; ".join(off), xref="paper", yref="paper",
x=0.0, y=note_y, xanchor="left", yanchor="top", showarrow=False, align="left",
font=dict(size=9.5, color=lib.MUTED))
note_y -= 0.026
if any_shift:
fig.add_annotation(text=f"† rises above +{RC02_LEVEL_SHIFT_PCT}% usually mean new episodes landed, not gradual drift",
xref="paper", yref="paper", x=0.0, y=note_y, xanchor="left", yanchor="top",
showarrow=False, align="left", font=dict(size=9.5, color=lib.MUTED))
cautions = ["CL-006 US viewer panel, not census",
"momentum = share of panel viewing vs its trailing 4-week average (panel-size-proof)"]
if panel_degraded:
cautions.append("panel volume atypical on this date; read momentum with care")
footer = lib.assemble_footer(pc_id=reg.get("pc_id", "PC-RC-02"),
caution_labels=cautions,
freshness_date=freshness,
sources=["metrics_demand_rank_history", "title_metrics_daily"],
extra="the demand board's hidden momentum · United States · TV series · weekly")
fig.update_layout(**lib.layout_v2(takeaway, what, width=1180, height=680),
xaxis=lib.standard_xaxis("", range=[xlo, xhi], ticksuffix="%"), # metric explained in subtitle
yaxis=lib.standard_yaxis(showgrid=False))
fig.update_xaxes(zeroline=True, zerolinecolor=lib.MUTED, zerolinewidth=1.4) # the cooling|heating divider at 0
# cooling / heating cues at the axis ends (paper-anchored, clear of the bottom text stack)
fig.add_annotation(text="cooling", xref="paper", yref="paper", x=0.0, y=1.012, xanchor="left",
yanchor="bottom", showarrow=False, font=dict(size=10, color=lib.RED))
fig.add_annotation(text="heating", xref="paper", yref="paper", x=1.0, y=1.012, xanchor="right",
yanchor="bottom", showarrow=False, font=dict(size=10, color=lib.GREEN))
lib.add_why_and_footer(fig, why, footer)
lib.save_csv(df, output_dir)
lib.export_chart(fig, output_dir, width=1180, height=680)
what_plain = re.sub(r"?b>", "", what)
lesson = {"reader_title": takeaway, "reader_subtitle": what_plain, "claim_strength": "strong",
"rules_applied": (f"M053 share-of-panel momentum vs trailing-4wk avg; established board "
f"(di_rank<={RC02_TIER}, base>={RC02_MIN_BASE_DAYS}d, <=+{RC02_EXPLOSION_PCT}%); "
f"level-shift dagger above +{RC02_LEVEL_SHIFT_PCT}%; explosions + no-baseline debuts side-noted")}
lib.write_lesson_design(output_dir, chart_id, f"# {chart_id}\n\n{takeaway}\n\n{what_plain}\n")
write_shim(output_dir, chart_id)
lib.write_manifest(output_dir, chart_id, "ok", reg.get("spec_file", "specs/16_recurring.md"),
pc_id=reg.get("pc_id", "PC-RC-02"),
intended_lesson_from_spec="Weekly demand-board momentum: heating vs cooling (M053 share-of-panel)",
rendered_lesson=lesson, row_count=len(board), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(board)}