"""SOURCE render logic for TI-RC-08 (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_08(reg: dict) -> dict: """Nobody Dominates Like 2022 - record #1 streak per calendar half-year on the US shows board, computed LIVE from the daily rank history. The table is a nightly full rebuild under one consistent ruleset, so the whole timeline is internally apples-to-apples; the footer carries the mandatory restatement disclosure (current rules applied retroactively, not as-published). Streaks credit the half they END in (verified: no streak wins two halves); the current #1 run is re-queried every render (perishable).""" chart_id = "TI-RC-08" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() rk = lib.run_query(conn, """ SELECT r.file_date, r.imdb_id, b.primary_title, r.consec_days_at_rank1 FROM metrics_demand_rank_history r JOIN categorization_imdb_title_basics b USING (imdb_id) WHERE r.geo_code = 'US' AND r.title_type = 'shows' AND r.di_rank = 1 ORDER BY r.file_date; """) 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 rk.empty: lib.needs_spec_review(output_dir, chart_id, reg.get("spec_file", "specs/16_recurring.md"), reg.get("pc_id", "PC-RC-08"), "No rank-1 history on the US shows board") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} rk["consec_days_at_rank1"] = rk["consec_days_at_rank1"].astype(int) rk["file_date"] = pd.to_datetime(rk["file_date"]) anchor = rk["file_date"].max() freshness = str(anchor.date()) start_year = int(rk["file_date"].min().year) # a streak ENDS where the next #1 day belongs to another show, where the counter resets, # or at the data edge (the ongoing run counts as ending at the anchor, in the partial half) nxt_id = rk["imdb_id"].shift(-1) nxt_consec = rk["consec_days_at_rank1"].shift(-1) is_end = (nxt_id != rk["imdb_id"]) | (nxt_consec <= rk["consec_days_at_rank1"]) | nxt_id.isna() ends = rk[is_end].copy() ends["half_label"] = (ends["file_date"].dt.year.astype(str) + " H" + ((ends["file_date"].dt.month > 6).astype(int) + 1).astype(str)) rec = ends.loc[ends.groupby("half_label")["consec_days_at_rank1"].idxmax()].sort_values("file_date").reset_index(drop=True) cur = rk.iloc[-1] # current #1 run: perishable, re-queried live cur_title, cur_days = str(cur["primary_title"]), int(cur["consec_days_at_rank1"]) anchor_half = f"{anchor.year} H{1 if anchor.month <= 6 else 2}" half_end = pd.Timestamp(anchor.year, 6, 30) if anchor.month <= 6 else pd.Timestamp(anchor.year, 12, 31) partial_open = anchor < half_end # the running half is incomplete rec["partial"] = (rec["half_label"] == anchor_half) & partial_open rec_i = rec["consec_days_at_rank1"].idxmax() rec_row = rec.loc[rec_i] rec_title, rec_days, rec_year = str(rec_row["primary_title"]), int(rec_row["consec_days_at_rank1"]), int(rec_row["file_date"].year) # 'only X has ever broken Y days': computed over ALL streaks ever, not just half-records max_other = int(ends.loc[ends["primary_title"] != rec_title, "consec_days_at_rank1"].max()) # 'the past two years' = the TRAILING 24 months from the anchor, over ALL streak ends (not the # calendar-year half-records): under the old calendar read, a 2024-H2 streak sat inside the # natural 'past two years' window yet was excluded, contradicting the chart's own face (L-049 # ambiguous-comparison bait) recent_cutoff = anchor - pd.DateOffset(months=24) recent = ends[ends["file_date"] >= recent_cutoff] recent_hi = int(recent["consec_days_at_rank1"].max()) if len(recent) else rec_days # Structural facts so the thesis is never falsified by its own runner-up (the second-tallest bar # is the SAME show as the record): the set of shows that have EVER cleared max_other days, and the # best recent run by ANY OTHER show (outside the record-holder's peaks). When only the record # holder has cleared the wall, the honest framing is "X owns the dominance era", not "nobody # dominates" (which the X reappearance contradicts). dom_shows = ends.loc[ends["consec_days_at_rank1"] > max_other, "primary_title"].apply(_title_disp).unique().tolist() rec_holder_owns_both = len(dom_shows) == 1 recent_other = recent[recent["primary_title"] != rec_title] recent_other_hi = int(recent_other["consec_days_at_rank1"].max()) if len(recent_other) else recent_hi recent_other_yr = int(recent_other.loc[recent_other["consec_days_at_rank1"].idxmax(), "file_date"].year) \ if len(recent_other) else int(anchor.year) # titles that hold the half-record in MORE THAN ONE half (e.g. a returning tentpole's two seasons): # tag the LATER appearance(s) '(returns)' so the twice-appearing name reads as a deliberate return, # not a duplication bug. rec is chronological, so the first time a repeat title is seen stays clean. title_counts = rec["primary_title"].value_counts() repeat_titles = set(title_counts[title_counts > 1].index) # The dominance-era thesis is about the RECORD HOLDER as a franchise, so every bar belonging to that # show shares one CONSISTENT navy treatment (claim-accent), not two different categories. The all-time # record is set apart by its LABEL ('all-time record') rather than by a different fill/outline, so the # reader is cued that the bars above the field's-best line are the same name. The rest of the field # stays pale. (Leader-fill rule preserved: navy is the single claim's accent, here the dominance run.) dom_title = rec_title # the record holder = the dominance-era franchise _seen_titles: set = set() ticks, colors, texts, shapes, line_colors, line_widths = [], [], [], [], [], [] for r in rec.itertuples(): star = "*" if bool(r.partial) else "" is_repeat = r.primary_title in repeat_titles is_return = is_repeat and (r.primary_title in _seen_titles) _seen_titles.add(r.primary_title) repeat_tag = " (returns)" if is_return else "" ticks.append(f"{r.half_label}{star}
{lib.ellipsize(_title_disp(r.primary_title), 22)}{repeat_tag}") is_record = int(r.Index) == int(rec_i) is_dom = (r.primary_title == dom_title) # this bar belongs to the dominance-era franchise colors.append(lib.NAVY if is_dom else lib.PALE_BLUE) if is_record: texts.append(f"{int(r.consec_days_at_rank1)}
all-time record") elif is_dom: # same franchise, second run above the field's best: cue it as the same name, not a new category texts.append(f"{int(r.consec_days_at_rank1)}
same show, again") elif bool(r.partial): # the hatched in-progress bar = the leading run of a half not yet over; labeled as such so # it cannot be conflated with the separate live #1-ticker note (a different, current fact) texts.append(f"{int(r.consec_days_at_rank1)}
so far this half") else: texts.append(f"{int(r.consec_days_at_rank1)}") shapes.append("/" if bool(r.partial) else "") # hatch = half still in progress line_colors.append("rgba(0,0,0,0)"); line_widths.append(0) fig = go.Figure() fig.add_trace(go.Bar( x=ticks, y=rec["consec_days_at_rank1"].astype(int), marker=dict(color=colors, line=dict(color=line_colors, width=line_widths), pattern=dict(shape=shapes, fgcolor=lib.PAPER, size=6, solidity=0.3)), text=texts, textposition="outside", textfont=dict(size=12, color=lib.INK), cliponaxis=False, showlegend=False, hovertemplate="%{x}
longest run that half: %{y} days at #1", )) fig.update_yaxes(range=[0, rec_days * 1.22]) fig.update_xaxes(categoryorder="array", categoryarray=ticks) # The runner-up benchmark = the best #1 run by ANY OTHER show (here The Last of Us). It is named, not # a reverse-engineered 'wall': the thesis is a direct comparison (the record holder's two runs both # stand ABOVE the whole rest of the field's best), so no claim rides on a >-vs-= edge case at a value # that is itself a bar. The other-show benchmark holder is identified for the named line. other_top_row = ends.loc[ends.loc[ends["primary_title"] != rec_title, "consec_days_at_rank1"].idxmax()] other_top_title = _title_disp(str(other_top_row["primary_title"])) dom_runs = sorted(rec.loc[rec["primary_title"] == rec_title, "consec_days_at_rank1"].astype(int).tolist(), reverse=True) runs_str = " and ".join(str(x) for x in dom_runs) if len(dom_runs) <= 2 else \ ", ".join(str(x) for x in dom_runs[:-1]) + f" and {dom_runs[-1]}" if rec_year >= anchor.year - 1: # a live-era record: neutral framing takeaway = f"{_title_disp(rec_title)} holds the longest run at #1 with US viewers: {rec_days} straight days" elif rec_holder_owns_both: # the record holder owns BOTH peaks above the field's best. State the comparison directly: its # runs (e.g. 83 and 66) both stand taller than the best ANY OTHER show managed (the named # runner-up benchmark), so no other show's reign has matched it. No tautological 'clears X'. n_runs = len(dom_runs) run_word = "two runs" if n_runs == 2 else f"{n_runs} runs" takeaway = (f"{_title_disp(rec_title)} owns the dominance era: its {run_word} at #1 with US viewers " f"({runs_str} days) both stand taller than the best any other show has managed " f"({other_top_title}, {max_other} days)") else: takeaway = (f"The #1 reign is shrinking: {_title_disp(rec_title)} once held the top for {rec_days} straight " f"days, taller than the best by any other show since {recent_other_yr} ({recent_other_hi} days)") partial_clause = (f" The starred half is still in progress, counted through {freshness}." if partial_open else "") # Restatement disclosure sits in the SUBTITLE (adjacent to the claim), not only the footer: the whole # timeline is recomputed nightly under today's board rules, so 2022 and 2026 streaks are comparable. what = (f"The longest unbroken run of days a single show spent at #1 with the US viewer panel, one bar per half " f"year since {start_year}, every half recomputed under today's board rules so the eras compare like for " f"like.{partial_clause}") # viewing, not discourse: a #1 streak measures owning the top of US VIEWING. The WHY is anchored to # the field's-best benchmark line drawn on the chart, so the claim is verifiable by eye. The recent # comparison is stated as 'shorter than the field's best', not a threshold set at a single bar. why = (f"A #1 streak measures how long one show can own the top of US viewing, and that monopoly is " f"narrowing to one name: only {_title_disp(rec_title)} sits above the field's-best line; every " f"other show since {recent_cutoff:%b %Y} has held the top for less. Fewer mega-tentpoles plus " f"a deeper field trading the top spot means shorter reigns.") cautions = ["streaks measured under current board rules applied retroactively to the full history, " "restated, not as-published (methodology: METH-03)"] if panel_degraded: cautions.append("panel volume atypical on the latest date; read the current run with care") # Sources named for what this chart reads: the DAILY #1 board (the metric is consecutive DAYS # at #1), with the metric NAMED per founder terminology ('Demand Score rankings', never a # near-bare lowercase 'demand rankings') footer = lib.assemble_footer(pc_id=reg.get("pc_id", "PC-RC-08"), caution_labels=cautions, freshness_date=freshness, sources=["PeerLogix daily Demand Score rankings (updated daily on a 7-day scoring basis)"], extra=f"United States · shows on the US viewer panel · daily #1 since {start_year}") fig.update_layout(**lib.layout_v2(takeaway, what, width=1180, height=700), xaxis=lib.standard_xaxis("", showgrid=False), yaxis=lib.standard_yaxis("consecutive days at #1", range=[0, rec_days * 1.22])) fig.update_xaxes(tickfont=dict(size=10), categoryorder="array", categoryarray=ticks) # Field's-best benchmark line: drawn at max_other and NAMED for the show that set it (the runner-up # benchmark), not an abstract 'wall'. This makes the line a defensible, non-coincidental cut: it is # literally the best run any OTHER show achieved, so the runner-up's own bar sitting exactly on the # line is correct and self-explanatory (that bar IS the line). Only the dominance franchise rises above it. # The line sits exactly on top of the runner-up's own bar by design (the line IS that bar's value). # Say so in the label so the coincidence reads as deliberate, not as a bar touching its own reference. # Two-line label (no em/en dashes per house rule). The long single-line version spanned the whole # plot and collided with the dominance bar, its own bar's value label, and the dashed line # (founder overlap fix 2026-06-19). Wrapped to two short lines so the caption stays a compact block # that sits clear in the white space above the shorter middle bars. wall_label = (f"best run by any other show: {max_other} days
({other_top_title}, the bar this line sits on)" if rec_holder_owns_both else f"best run by any other show: {max_other} days") # Draw the field's-best line with NO inline label (the lib label lands at the far-right paper edge, # over unrelated later bars). Instead co-locate the label with the runner-up bar the line sits on, so # the reader's eye goes straight from caption to the exact bar it describes (TI-RC-08 path-to-10 fix). lib.add_ref_line(fig, y=max_other) # The bar the line sits on = the OTHER-show half-record whose streak equals max_other. Place the # compact 2-line caption in the CLEAR band ABOVE the dashed line, lifted well over the line and the # bar's own value label so nothing overlaps, and grown toward the side with the most empty space # (away from the canvas edge AND away from the tall dominance bars), so it never runs off the canvas. _other_bars = rec[(rec["primary_title"] != rec_title) & (rec["consec_days_at_rank1"].astype(int) == max_other)] if len(_other_bars): _ob_pos = int(rec.index.get_loc(_other_bars.index[0])) _ob_tick = ticks[_ob_pos] # Grow toward the interior: if the runner-up bar sits in the left half of the chart, grow the # label rightward over the shorter middle bars; if in the right half, grow leftward. Either way # the caption stays inside the plot and clears the dominance (navy) bars at the two ends. _grow_left = _ob_pos >= (len(ticks) / 2.0) # y lifted into the clear gap between the dashed line and the all-time-record bar's top label, # so the caption never sits on the line or on this bar's own outside "64" value label. _cap_y = max_other + max(rec_days * 0.16, 8) fig.add_annotation( x=_ob_tick, y=_cap_y, xref="x", yref="y", text=wall_label, showarrow=False, xanchor="right" if _grow_left else "left", xshift=-4 if _grow_left else 4, yanchor="bottom", font=dict(size=10, color=lib.MUTED), bgcolor="rgba(255,255,255,0.85)", align="right" if _grow_left else "left") else: lib.add_ref_line(fig, y=max_other, label=wall_label) # fallback: keep the named line labeled # phrasing avoids 'X is on day 3' (misparses when the title is an ordinary word) and the # house-styled title; the live run also SHIPS as a data.csv row (row_type='current_run_live'), # so this perishable note is reconstructable from the download, not a re-query-only claim. # The note now SPELLS OUT its relationship to the in-progress bar so a reader is not left wondering # why "today's #1" is not the last bar: the bar shows the running half's LONGEST run so far (by # whichever show set it), while this tracks whoever holds #1 right now (often a newer, shorter run). partial_rows = rec[rec["partial"].astype(bool)] if partial_open and len(partial_rows): pr = partial_rows.iloc[-1] p_title, p_days = _title_disp(str(pr["primary_title"])), int(pr["consec_days_at_rank1"]) if str(pr["primary_title"]) == cur_title: # the current #1 is also the half's leader: the bar and the live run are the same streak live_txt = (f"LIVE #1 today: {_title_disp(cur_title)} is {cur_days} day" f"{'s' if cur_days != 1 else ''} into the #1 run the last bar tracks") else: live_txt = (f"LIVE #1 today: {_title_disp(cur_title)} just took #1, {cur_days} day" f"{'s' if cur_days != 1 else ''} in. The last bar is this half's longest run so far " f"({p_title}, {p_days} days), not whoever leads today.") else: live_txt = (f"LIVE #1 today: {_title_disp(cur_title)} is {cur_days} day" f"{'s' if cur_days != 1 else ''} into a #1 run") fig.add_annotation(text=live_txt, xref="paper", yref="paper", x=1.0, y=1.012, xanchor="right", yanchor="bottom", showarrow=False, font=dict(size=11, color=lib.INK), align="right") lib.add_why_and_footer(fig, why, footer) csv = rec[["half_label", "primary_title", "consec_days_at_rank1", "file_date", "partial"]].rename( columns={"half_label": "half_year", "primary_title": "show", "consec_days_at_rank1": "record_streak_days_at_number1", "file_date": "streak_ended_on", "partial": "half_in_progress"}) csv["streak_ended_on"] = csv["streak_ended_on"].dt.date csv["row_type"] = "half_year_record" csv = pd.concat([csv, pd.DataFrame([{ "half_year": anchor_half, "show": cur_title, "record_streak_days_at_number1": cur_days, "streak_ended_on": anchor.date(), "half_in_progress": bool(partial_open), "row_type": "current_run_live"}])], ignore_index=True) lib.save_csv(csv, output_dir) lib.export_chart(fig, output_dir, width=1180, height=700) what_plain = re.sub(r"", "", what) lesson = {"reader_title": takeaway, "reader_subtitle": what_plain, "claim_strength": "strong", "rules_applied": (f"record consec_days_at_rank1 per calendar half-year, US shows board " f"{start_year}-present, streak credited to the half it ends in; ongoing run " f"counts at the anchor; partial half starred; current run re-queried live " f"({cur_title}, day {cur_days}) and shipped in data.csv as row_type='current_run_live'; " f"display-only house styling map for ambiguous title casings (csv keeps catalog casing)")} 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-08"), intended_lesson_from_spec="Era chart: record #1 streak per half-year, the dominance ceiling falling from 2022 to now", rendered_lesson=lesson, row_count=len(rec), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec, extra={"current_number1": cur_title, "current_streak_days": cur_days, "all_time_record": {"show": rec_title, "days": rec_days, "half": str(rec_row['half_label'])}, "max_streak_by_another_show": max_other}) return {"status": "ok", "rows": len(rec)} # =========================================================================== # Builder-C additions (2026-06-12): TI-F-06, TI-D-13, TI-INT-01 # Built from the pre-build validation report (keys: visitors-residents, what-travels); # all numbers re-queried live at render time (tables rewrite nightly). # ===========================================================================