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