"""SOURCE render logic for TI-RC-07 (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_07(reg: dict) -> dict: """The Watched-but-Unknown Watchlist (weekly) - recent shows with big at-home audiences whose weekly Demand Score has not caught up yet, because outside (IMDb) corroboration accrues with a lag on new titles. Universe = this week's audience lens top-100, where the lens = demand_score_7d / cred (the corroboration multiplier divided back out of the published score, i.e. reach x composition weight - still the demand_score-family lane, NEVER the raw substrate). Selection (rebuilt 2026-06-12 to stop two de-skew leaks, Daemons-class + 2019-under-voter): 1. RECENCY gate = premiered within ~24 months (start_year >= anchor_year - 2). This isolates genuine vote-accrual lag and removes STRUCTURAL under-voters (a 2019 series with thin votes is not 'early discovery'). aired_90d is kept as an informational 'currently airing' flag only - it is NOT a bypass branch (that branch resurrected the 2019 class). 2. ARTIFACT FLOOR = votes-per-reach (IMDb corroboration density) >= RC07_VPR_FLOOR. DB-probed: among recency-eligible titles the artifact (Daemons of the Shadow Realm) sits alone at ~1.4 while the next title is ~3.7 - a clean empty band. The floor drops the niche titles the anime-heavy panel over-watches but almost nobody rates (the main board correctly buries them); their high 'not yet credited %' is a rating-floor artifact, not discovery demand. This is a quality gate - disclosed NOWHERE on the face. 3. LANE balance = the two lowest composition lanes (anime, us-animation) are each capped at RC07_MAX_LANE rows so live-action carries the list and anime can never structurally flood the 'low corroboration' ranking. Within a capped lane the lowest-cred title is kept. 4. SORT = rank gap descending (TWO-RANK CONTRAST, founder-approved 2026-06-19). The displayed gap = official Demand Score rank (di_rank) minus audience demand rank (lens_rank); the bar length IS that gap, so the rows are gap-monotone (widest gap on top). cred is no longer on the face - it survives only as the lane-cap tie-break above. TV-ONLY by construction: the universe is the US TV-series board (title_type='shows', further restricted to IMDb tvSeries/tvMiniSeries). Theatrical and at-home films cannot appear - the movie board is a separate title_type and the explicit IMDb tvSeries/tvMiniSeries guard backstops it, so this is a deliberate TV-series-only watchlist.""" chart_id = "TI-RC-07" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() df = lib.run_query(conn, """ WITH d0 AS (SELECT MAX(file_date) AS d FROM metrics_demand_rank_history), board AS ( SELECT r.imdb_id, r.reach_7d_avg::numeric AS reach, r.cred::numeric AS cred, r.demand_score_7d::numeric AS score, b.primary_title, b.start_year, -- di_rank IS the published weekly Demand Score board rank (verified: -- RANK() OVER (ORDER BY demand_score_7d DESC) reproduces di_rank exactly). It is -- the "official ranking" the two-rank contrast reads against the audience lens. r.di_rank AS official_rank, -- IMDb vote count is the corroboration substrate behind cred; votes-per-viewer -- (votes / panel reach) is the lane-aware density we floor on. tmdb_votes is the -- secondary rater source already folded into cred (v = imdb + 42*tmdb). COALESCE(ir.num_votes, 0) AS imdb_votes, (SELECT d FROM d0) AS freshness_date FROM metrics_demand_rank_history r JOIN categorization_imdb_title_basics b USING (imdb_id) LEFT JOIN categorization_imdb_ratings ir USING (imdb_id) WHERE r.file_date = (SELECT d FROM d0) AND r.geo_code = 'US' AND r.title_type = 'shows' -- PRIMARY board guard: TV-series board, never the movie board AND b.title_type IN ('tvSeries','tvMiniSeries') -- EXPLICIT TV-only guard: nothing theatrical/at-home film can appear AND r.di_rank IS NOT NULL AND r.reach_7d_avg > 0 AND r.cred > 0 ), lens AS ( -- audience lens = published score with the corroboration multiplier divided back out SELECT *, RANK() OVER (ORDER BY score / cred DESC) AS lens_rank FROM board ), recent AS ( SELECT DISTINCT e.imdb_id FROM categorization_tv_episodes e, d0 WHERE e.air_date > d0.d - 90 AND e.air_date <= d0.d ) SELECT l.lens_rank, l.official_rank, l.primary_title, l.start_year, l.cred, l.score / NULLIF(l.reach * l.cred, 0) AS comp_factor, l.imdb_votes / NULLIF(l.reach, 0) AS votes_per_reach, l.score, (r.imdb_id IS NOT NULL) AS aired_90d, l.freshness_date FROM lens l LEFT JOIN recent r USING (imdb_id) WHERE l.lens_rank <= 100 ORDER BY l.cred, l.lens_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 # board_size = how many titles sit on this week's published US shows board (the same official # ranking di_rank counts against). Used ONLY in the lead-row "ranked #N of {board_size}" label; # it is the max published rank, computed live so the figure is never hardcoded. bs = lib.run_query(conn, """ SELECT COUNT(*) AS n FROM metrics_demand_rank_history WHERE file_date = (SELECT MAX(file_date) FROM metrics_demand_rank_history) AND geo_code = 'US' AND title_type = 'shows' AND di_rank IS NOT NULL; """) board_size = int(bs["n"].iloc[0]) if len(bs) else 0 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-07"), "No US shows board rows for the watchlist lens") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} df["cred"] = df["cred"].astype(float) df["comp_factor"] = df["comp_factor"].astype(float) df["votes_per_reach"] = df["votes_per_reach"].astype(float) df["score"] = df["score"].astype(float) df["lens_rank"] = df["lens_rank"].astype(int) df["official_rank"] = df["official_rank"].astype(int) df["aired_90d"] = df["aired_90d"].astype(bool) freshness = str(df["freshness_date"].iloc[0]) anchor_year = int(freshness[:4]) # Board constants - interpolated into BOTH the logic and rules_applied RC07_LENS_TOP = 100 # the watchlist universe: this week's 100 biggest audience-lens shows RC07_PREMIERE_YEARS = 2 # recency gate: premiered within ~24 months (year grain) RC07_AIRED_DAYS = 90 # informational 'currently airing' window (NOT a recency bypass) RC07_VPR_FLOOR = 2.5 # artifact floor: min IMDb votes per panel viewer (DB-probed empty # band 1.4..3.7; drops the niche over-watched / barely-rated class) RC07_LIST = 10 # rows on the rendered watchlist RC07_MAX_LANE = 1 # de-skew valve: max rows from EACH low composition lane (anime, us-anim) RC07_LANES = (0.425, 0.649, 1.0) # the board's three locked composition weights RC07_LOW_LANES = (0.425, 0.649) # the two lanes that must never dominate the low-cred list # snap the implied composition factor to the nearest lane: float noise (~0.001 from the # score reconstruction) otherwise splits one lane into two and breaks the lane cap df["lane"] = df["comp_factor"].map(lambda c: min(RC07_LANES, key=lambda v: abs(v - float(c)))) # GATE 1 - recency: premiered within ~24 months. The aired-90d OR-branch is dropped as a gate # (it resurrected structural under-voters like the 2019 isekai class); kept only as a flag. sy = pd.to_numeric(df["start_year"], errors="coerce") recency_ok = sy >= anchor_year - RC07_PREMIERE_YEARS # GATE 2 - artifact floor: corroboration density at/above the principled lane-aware floor. floor_ok = df["votes_per_reach"] >= RC07_VPR_FLOOR elig = df[recency_ok & floor_ok].copy() n_dropped_floor = int((recency_ok & ~floor_ok).sum()) if len(elig) < RC07_LIST: lib.needs_spec_review(output_dir, chart_id, reg.get("spec_file", "specs/16_recurring.md"), reg.get("pc_id", "PC-RC-07"), f"Only {len(elig)} recency-eligible, corroboration-floored shows in the lens top-{RC07_LENS_TOP}") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} # GATE 3 - lane cap: within each LOW composition lane keep only the RC07_MAX_LANE lowest-cred # rows, so anime/us-animation cannot structurally flood the list; live-action is uncapped. elig = elig.sort_values(["cred", "lens_rank"]).reset_index(drop=True) elig["lane_idx"] = elig.groupby("lane").cumcount() keep = (~elig["lane"].isin(RC07_LOW_LANES)) | (elig["lane_idx"] < RC07_MAX_LANE) members = elig[keep].copy() # SORT - rank gap descending (TWO-RANK CONTRAST, founder-approved 2026-06-19). The bar now encodes # the gap between how widely a show is already watched (audience_rank = lens_rank) and where the # official weekly Demand Score ranking sits it (official_rank = di_rank). A gap-encoded bar REQUIRES # gap-monotone rows, so the list re-sorts by gap descending (the old cred-ascending order is a # DIFFERENT order and would make the bars look arbitrary). cred is no longer on the face; it stays # internal only as the lane-cap tie-break above. Widest gap = most under-the-radar = top of chart. members = members.copy() members["rank_gap"] = members["official_rank"] - members["lens_rank"] disp = members.sort_values(["rank_gap", "lens_rank"], ascending=[False, True]).head(RC07_LIST).reset_index(drop=True) disp["gap_rank"] = disp.index + 1 n_low_final = int(disp["lane"].isin(RC07_LOW_LANES).sum()) plot = disp.iloc[::-1].reset_index(drop=True) # plotly draws first row at the bottom # PER-ROW LABEL: the two ranks ARE the point, the bar's length is the gap between them. ONE unit # concept (a rank position); no percentages, no "credited". House dot separator " · " (not a dash). # Lead/navy row gets the slightly fuller variant (room on the widest bar). Integer ranks rarely tie # and read unambiguously, so the old tied-percentage decimal logic is gone. # Labels sit OUTSIDE the bar (to its right) at a CONSTANT font size, so the two-rank contrast reads # cleanly on EVERY row - the short bars (small gaps) cannot fit inside text and plotly would shrink # it to an illegible size if placed inside. The bar length still encodes the gap; the leader is # signaled by navy color (color contract preserved). Lead row gets the slightly fuller variant. ticks, colors, texts = [], [], [] for r in plot.itertuples(): is_lead = int(r.gap_rank) == 1 yr = f" ({int(r.start_year)})" if pd.notna(r.start_year) else "" label = f"{int(r.gap_rank)}. {lib.ellipsize(str(r.primary_title), 38)}{yr}" ticks.append(f"{label}" if is_lead else label) colors.append(lib.NAVY if is_lead else lib.PALE_BLUE) if is_lead and board_size > 0: texts.append(f"watched like the #{int(r.lens_rank)} biggest audience · " f"ranked #{int(r.official_rank)} of {board_size}") else: texts.append(f"watched like the #{int(r.lens_rank)} show · ranked #{int(r.official_rank)}") fig = go.Figure() fig.add_trace(go.Bar( x=plot["rank_gap"], y=ticks, orientation="h", marker=dict(color=colors, line=dict(width=0)), text=texts, textposition="outside", textfont=dict(size=11.5, color=lib.INK), cliponaxis=False, showlegend=False, customdata=np.stack([plot["lens_rank"].values, plot["official_rank"].values], axis=-1), hovertemplate="%{y}
Watched like the #%{customdata[0]} show, but ranked #%{customdata[1]} on this week's Demand Score board", )) lead = disp.iloc[0] # TWO-RANK CONTRAST framing (RC-07 reframe, founder-approved 2026-06-19). The face now shows BOTH # numbers that make the point self-evident: how widely a show is already watched (audience demand # rank) vs where this week's official Demand Score ranking has it; the bar's length IS that gap. # The catch-up reason is stated as "reputation spreads / the ranking climbs", which is true and # plain and discloses NO mechanic. No "credit"/"credited"/votes/corroboration/percentages anywhere; # "TV series" and "US" stated on the face so a layman never reads a row as a film, and never reads # a low official ranking as low quality. "demand" appears only as "Demand Score". takeaway = ("Watched now, ranked later: new US TV series people are already streaming " "far above where the official weekly ranking has them") what = (f"The {RC07_LIST} new US TV series with the widest gap between how widely our viewer panel is already " f"watching them and where they sit on this week's official Demand Score ranking. New shows start low " f"and climb as their reputation spreads, so a show watched far above its ranking is an early breakout " f"candidate; ordered by that gap, widest at the top.") # WHY (plain, no mechanic): the gap is where early discovery lives; this is a watchlist of # CANDIDATES, never a forecast. No votes/credit/corroboration language. why = ("New shows start low on the official ranking and climb as their reputation spreads, so the gap between " "how widely a show is already watched and where it ranks is where early discovery lives. Titles that " "keep closing that gap have gone on to break out and earn renewals, so treat this as a watchlist of " "candidates, not a forecast.") # CAUTIONS (plain words, same idea as the old 'low corroboration' caution, no mechanic): a low # official ranking on a new show is not low quality, the ranking climbs as reputation catches up. cautions = [ "a low official ranking on a new show is not low quality, the ranking climbs as the show's reputation catches up", "ranks use a 7-day basis (methodology: METH-03)", ] if panel_degraded: cautions.append("panel volume atypical on this date; read with care") # Sources label matches the H1/trust terminology ('weekly Demand Score rankings', never the # library's near-bare 'weekly demand rankings' default) footer = drop_census(lib.assemble_footer(pc_id=reg.get("pc_id", "PC-RC-07"), caution_labels=cautions, freshness_date=freshness, sources=["PeerLogix weekly Demand Score rankings", "episode air-date catalog"], extra="United States · TV series watched at home · refreshed weekly")) # x-axis = the rank gap (a comparative magnitude cue only); ticks hidden, the two ranks in each # label carry the precise numbers. The labels sit OUTSIDE the bars, so the range is padded well # past the widest gap (the lead bar) to leave room for its label without clipping at the right edge. gap_max = float(disp["rank_gap"].max()) fig.update_layout(**lib.layout_v2(takeaway, what, width=1180, height=720), xaxis=lib.standard_xaxis("", showticklabels=False, showgrid=False, range=[0, gap_max * 1.95]), yaxis=lib.standard_yaxis(showgrid=False)) lib.add_why_and_footer(fig, why, footer) # CSV face matches the rendered face: the two ranks + their gap, no credited-share column. # imdb_votes_per_panel_viewer is kept CSV-ONLY as internal gate provenance (it is NOT on the chart). csv = disp[["gap_rank", "primary_title", "start_year", "lens_rank", "official_rank", "rank_gap", "score", "aired_90d", "votes_per_reach"]].rename( columns={"primary_title": "show", "start_year": "premiere_year", "lens_rank": "audience_rank", "official_rank": "official_demand_score_rank", "score": "weekly_demand_score", "aired_90d": "aired_new_episodes_past_90d", "votes_per_reach": "imdb_votes_per_panel_viewer"}) lib.save_csv(csv, output_dir) lib.export_chart(fig, output_dir, width=1180, height=720) what_plain = re.sub(r"", "", what) lesson = {"reader_title": takeaway, "reader_subtitle": what_plain, "claim_strength": "moderate", "rules_applied": (f"audience lens = demand_score_7d/cred over the latest US shows board; universe = " f"lens top-{RC07_LENS_TOP}; recency gate = premiere >= {anchor_year - RC07_PREMIERE_YEARS} " f"(aired-90d kept as a flag, not a bypass); artifact floor = IMDb votes-per-viewer >= " f"{RC07_VPR_FLOOR} (dropped {n_dropped_floor} corroboration-floor artifacts); low " f"composition lanes (anime, us-anim) capped at {RC07_MAX_LANE} row each (final low-lane " f"rows = {n_low_final}); sort = rank gap descending, gap = official Demand Score rank " f"(di_rank) minus audience demand rank (lens_rank); display: the two ranks per row, " f"bar length = the gap, no absolute scores on the face; universe is the US TV-series " f"board (title_type='shows', restricted to IMDb tvSeries/tvMiniSeries); theatrical and " f"at-home films cannot appear by construction")} 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-07"), intended_lesson_from_spec="Weekly watchlist: big at-home audiences the corroboration lag still hides (cred-gap, recency-gated + artifact-floored + lane-capped lens top-100)", rendered_lesson=lesson, row_count=len(disp), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec, extra={"eligible_pool": int(len(elig)), "artifacts_floored": n_dropped_floor, "low_lane_cap": RC07_MAX_LANE, "final_low_lane_rows": n_low_final, "vpr_floor": RC07_VPR_FLOOR}) return {"status": "ok", "rows": len(disp)} # House display styling for titles whose plain-cased name misparses as an ordinary word # mid-sentence (marketing usage styles the show 'FROM'; 'From is on day 3' reads as a typo). # RENDER SURFACES ONLY - data.csv keeps the catalog-true casing so audits match the DB. TITLE_DISPLAY_STYLE = {"From": "FROM"}