"""SOURCE render logic for TI-J-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_J_02(reg: dict) -> dict: """Original Demand-to-Spend Context - parent-classified originals only.""" chart_id = "TI-J-02" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() # STRICT originals: a title qualifies only if EVERY platform relationship at the latest snapshot is # 'parent_original'. Releases newer than 60 days are excluded: their demand can be theatrical-window # attention the availability feed can't yet confirm as streaming (the 'Michael' leak). The classifier # systematically labels a studio's THEATRICAL LIBRARY on its own service as parent_original (verified: # 14 of 20 board rows were Lionsgate films on Starz - John Wick, Hunger Games, Fall...), so studio-library # carriage (Lionsgate_Starz) is excluded - catalog on a studio-owned service is not an original. Five # named misfires stay hand-blocked as belt-and-braces (classifier bug filed for v3). J02_BLOCK = "('tt21807222','tt3397884','tt6146586','tt16428256','tt8946378')" df = lib.run_query(conn, f""" WITH latest AS (SELECT MAX(file_date) AS file_date FROM title_metrics_daily), latest_p AS (SELECT MAX(snapshot_date) AS snapshot_date FROM categorization_title_x_platform), originals AS ( SELECT imdb_id FROM categorization_title_x_platform JOIN latest_p USING (snapshot_date) GROUP BY imdb_id HAVING BOOL_AND(composite_classification = 'parent_original') AND BOOL_AND(parent_company IS DISTINCT FROM 'Lionsgate_Starz') ) SELECT t.imdb_id, t.demand_score::numeric AS demand, md.budget::numeric AS budget, l.file_date FROM title_metrics_daily t JOIN latest l USING (file_date) JOIN originals o USING (imdb_id) JOIN categorization_movie_details md USING (imdb_id) LEFT JOIN categorization_release_dates_all rd USING (imdb_id) WHERE t.demand_score IS NOT NULL AND t.geo_code='US' AND md.budget > 1000000 AND (rd.release_date IS NULL OR rd.release_date <= l.file_date - INTERVAL '60 days') AND t.imdb_id NOT IN {J02_BLOCK} ORDER BY t.demand_score DESC LIMIT 200; """) q_sec = time.time() - t0 if df.empty: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No original+budget overlap") 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"].tolist()) 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["demand"] = df["demand"].astype(float); df["budget"] = df["budget"].astype(float) freshness = str(df["file_date"].iloc[0]) df["demand_per_M"] = df["demand"] / (df["budget"] / 1_000_000) df = df.sort_values("demand_per_M", ascending=False).head(20).sort_values("demand_per_M", ascending=True) df["disp"] = [short_title(t, 42) for t in df["display_title"]] # ONE decimal everywhere: integer rounding flattened distinct values (#1 and #2 both printed '5' # with visibly different bars; ten bars printed '1'; the bottom three printed a self-contradictory '0') bar_text = [f" {v:,.1f} (${b/1e6:.0f}M budget)" for v, b in zip(df["demand_per_M"], df["budget"])] fig = go.Figure() fig.add_trace(go.Bar(x=df["demand_per_M"], y=df["disp"], orientation="h", marker=dict(color=lib.NAVY), text=bar_text, textposition="outside", textfont=dict(size=10, color=lib.MUTED), cliponaxis=False, showlegend=False, customdata=df[["budget"]], hovertemplate="%{y}
%{x:,.1f} points of Demand Score per $M · $%{customdata[0]:,.0f} budget")) fig.update_xaxes(range=lib.pad_range(df["demand_per_M"], labels=bar_text, font_px=10, fig_width=1240)) top1 = df.iloc[-1] # NOTE: at most ONE '$' may appear across headline+subtitle combined - plotly treats a $...$ PAIR in the # title string as LaTeX math and kaleido then renders the whole title blank (the original J-02 defect). title_lesson = (f"The best demand-per-dollar original is {top1['disp']} - {top1['demand_per_M']:,.1f} points of " f"Demand Score for every million dollars of budget") what = ("Top streaming originals ranked by demand per million budget dollars - today's Demand Score (a point-in-time watch " "measure, not a cumulative total) divided by reported budget. A long bar = a lean hit that punches far above its cost.") why = ("This is the bang-for-buck shortlist: originals that delivered outsized audience for modest money. It's the template - the kind " "of bet (genre, scale, talent) that returns the most attention per dollar committed.") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1240, height=760), xaxis=lib.standard_xaxis("Demand Score per $1M of budget"), yaxis=lib.standard_yaxis(showgrid=False)) lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-J-02", ["CL-006 viewer panel (distinct viewers), not census", "CL-014 external data (TMDb)"], freshness, ["categorization_movie_details", "categorization_title_x_platform"], "strict parent-originals (no licensed/acquired relationships anywhere), budget>$1M (TMDb-reported); " "releases newer than 60 days excluded (streaming availability not yet confirmable); studio-library " "carriage on a studio's own service (Lionsgate films on Starz) excluded - catalog, not originals; title-level only")) lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1240, height=760) lib.write_lesson_design(output_dir, chart_id, f"# {chart_id}\n\n{title_lesson}\n\n{what}\n") write_shim(output_dir, chart_id) lib.write_manifest(output_dir, chart_id, "ok", reg["spec_file"], pc_id="PC-J-02", intended_lesson_from_spec="Original bang-for-buck (Demand Score per $M, title-level)", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "moderate", "rules_applied": "strict (BOOL_AND) parent-originals; $1M+ budget; releases <60d old excluded; orphans dropped"}, row_count=len(df), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)} # =========================================================================== # SECTION 11 - Discovery & Browse Surfaces (5 charts) # All 5 reference discovery/browse surfaces (Collection, Browse, Discovery) that # are not represented in the current peerlogix2 schema. Mark all needs_spec_review # with structured reasons so reviewers can decide whether to add the data feed. # =========================================================================== def render_TI_Other_01(reg: dict) -> dict: """Discovery Surface Inventory Map - needs platform-browse surface metadata not in DB.""" chart_id = "TI-Other-01" output_dir = THIS_DIR / chart_id / ROLE lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg.get("pc_id", "PC-Other-01"), "Spec requires platform browse-surface inventory (rail, lane, hub) per platform. peerlogix2 has no browse-metadata feed.") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} def render_TI_Other_09(reg: dict) -> dict: """Browse Pathway Coverage Matrix - needs browse-surface metadata.""" chart_id = "TI-Other-09" output_dir = THIS_DIR / chart_id / ROLE lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg.get("pc_id", "PC-Other-09"), "Browse pathway / row coverage data not present in DB. Requires platform-browse feed (genre rail metadata).") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} def render_TI_Other_10(reg: dict) -> dict: """Collection candidates - DEMOTED: unclear framing, overlaps the genre/subgenre demand charts.""" chart_id = "TI-Other-10" output_dir = THIS_DIR / chart_id / ROLE lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg.get("pc_id", "PC-Other-10"), "CONSOLIDATED (reviewer: 'don't get the what and why'). This ranked genre+subgenre intersections by total demand as " "'curated-rail candidates' - an internal curation framing that didn't read externally, and it's volume-dominated (big " "combos win by title count). The genuinely external versions of this territory already exist: TI-Other-02 (subgenre " "demand per title) and TI-MG-03 (the subgenres inside each genre). Recommend cutting PC-Other-10.") write_shim(output_dir, chart_id) return {"status": "needs_spec_review"} def render_TI_Other_11(reg: dict) -> dict: """Surface Gap Queue - needs browse-surface metadata.""" chart_id = "TI-Other-11" output_dir = THIS_DIR / chart_id / ROLE lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg.get("pc_id", "PC-Other-11"), "Spec requires platform-rail coverage data for gap-queue. No browse-surface inventory feed in peerlogix2.") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} def render_TI_Other_12(reg: dict) -> dict: """Browse Taxonomy Health Card - needs browse-surface metadata.""" chart_id = "TI-Other-12" output_dir = THIS_DIR / chart_id / ROLE lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg.get("pc_id", "PC-Other-12"), "Spec requires browse-taxonomy hierarchy and rail-health metadata. Not in current schema.") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} # =========================================================================== # SECTION 12 - Title Intelligence Cards (11 charts) # All anchored on a default exemplar (top-1 DI in US). Real dashboard will # pass an `anchor` query param to swap titles. Sliding-window robust to data drift. # =========================================================================== EXEMPLAR_IMDB = "tt1190634" # current top-DI US title; anchor for TC cards # Section 12 is per-title deep-dives. Per reviewer, per-title intelligence belongs in an INTERACTIVE dashboard # (pick any title), not a fixed set of static charts - "outside the scope of this project." We keep ONE exemplar # (TI-H-03, the recommendation taste-map) to demonstrate the concept and consolidate the rest here. PER_TITLE_REASON = ( "CONSOLIDATED - per-title deep-dive. Per reviewer, per-title intelligence (momentum, geo, peers, stats for a " "single chosen title) is an INTERACTIVE-DASHBOARD feature where the user picks the title - not a static chart, and " "'outside the scope of this project.' Rendering it for one hard-coded exemplar (The Boys) teaches a stranger nothing " "and would waste daily compute on a one-off. We keep a single exemplar - TI-H-03, the recommendation taste-map - to " "demonstrate what a per-title card looks like, and consolidate the remaining Section-12 slots here. Recommend cutting " "this PC from the static set; revisit as a dashboard surface.") def _per_title_stub(reg: dict, chart_id: str, pc_id: str) -> dict: output_dir = THIS_DIR / chart_id / ROLE lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg.get("pc_id", pc_id), PER_TITLE_REASON) write_shim(output_dir, chart_id) return {"status": "needs_spec_review"} def render_TI_H_01(reg: dict) -> dict: """Similar Titles - top neighbors from metrics_ass for exemplar.""" chart_id = "TI-H-01" output_dir = THIS_DIR / chart_id / ROLE return _per_title_stub(reg, chart_id, "PC-H-01") # CONSOLIDATED into TI-H-03 (per-title → dashboard); "ASS" term also not for external use conn = lib.connect_readonly() t0 = time.time() df = lib.run_query(conn, f""" SELECT a.neighbor_imdb_id, a.similarity::numeric AS similarity, a.rank FROM metrics_ass a WHERE a.imdb_id = '{EXEMPLAR_IMDB}' ORDER BY a.rank LIMIT 12; """) q_sec = time.time() - t0 if df.empty: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], f"No neighbors for {EXEMPLAR_IMDB}") conn.close(); write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} anchor_titles = lib.resolve_titles(conn, [EXEMPLAR_IMDB] + df["neighbor_imdb_id"].tolist()) conn.close() anchor_name = anchor_titles[anchor_titles["imdb_id"] == EXEMPLAR_IMDB]["display_title"].iloc[0] if not anchor_titles.empty else EXEMPLAR_IMDB df = df.merge(anchor_titles.rename(columns={"imdb_id": "neighbor_imdb_id"})[["neighbor_imdb_id", "display_title"]], on="neighbor_imdb_id", how="left") df["display_title"] = df["display_title"].fillna(df["neighbor_imdb_id"]) df["similarity"] = df["similarity"].astype(float) df = df.sort_values("similarity", ascending=True) fig = go.Figure() fig.add_trace(go.Bar(x=df["similarity"], y=df["display_title"], orientation="h", marker=dict(color=lib.NAVY), text=[f"{v:.3f}" for v in df["similarity"]], textposition="outside", textfont=dict(size=10), cliponaxis=False, showlegend=False, hovertemplate="%{y}
Similarity %{x:.3f}")) nearest = df.iloc[-1] title_lesson = f"Closest neighbor of {anchor_name}: {nearest['display_title'][:30]} (similarity {nearest['similarity']:.3f})" subtitle = f"Top 12 most-similar titles to anchor by ASS-embedding cosine similarity. Anchor: {anchor_name}." fig.update_layout(**lib.standard_layout( title=title_lesson, subtitle=subtitle, footer=lib.assemble_footer("PC-H-01", ["CL-006 PANEL NOT CENSUS", "CL-016 EMBEDDING METHODOLOGY"], None, ["metrics_ass"], "Similarity = embedding cosine, not viewer-overlap"), width=1200, height=620, ), xaxis=lib.standard_xaxis("ASS similarity"), yaxis=lib.standard_yaxis(showgrid=False)) lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1100, height=620) lib.write_lesson_design(output_dir, chart_id, f"# {chart_id}\n\n{subtitle}\n") write_shim(output_dir, chart_id) lib.write_manifest(output_dir, chart_id, "ok", reg["spec_file"], pc_id="PC-H-01", intended_lesson_from_spec="Similar titles via ASS", rendered_lesson={"reader_title": "Similar Titles", "reader_subtitle": subtitle, "claim_strength": "strong", "rules_applied": f"anchor={EXEMPLAR_IMDB}"}, row_count=len(df), freshness_date=None, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)} def render_TI_H_02(reg: dict) -> dict: """CAP Comparable Set Benchmark Band - anchor's position within CAP cohort.""" chart_id = "TI-H-02" output_dir = THIS_DIR / chart_id / ROLE return _per_title_stub(reg, chart_id, "PC-H-02") conn = lib.connect_readonly() t0 = time.time() df = lib.run_query(conn, f""" WITH latest AS (SELECT MAX(file_date) AS file_date FROM metrics_cap_daily) SELECT comp_set, cohort_n, metric_in_cohort::numeric AS metric_in_cohort, cap_percentile::numeric AS pct, p10::numeric AS p10, p50::numeric AS p50, p90::numeric AS p90 FROM metrics_cap_daily JOIN latest USING (file_date) WHERE imdb_id = '{EXEMPLAR_IMDB}'; """) q_sec = time.time() - t0 if df.empty: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], f"No CAP for {EXEMPLAR_IMDB}") conn.close(); write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} titles = lib.resolve_titles(conn, [EXEMPLAR_IMDB]) conn.close() anchor_name = titles["display_title"].iloc[0] if not titles.empty else EXEMPLAR_IMDB for c in ["metric_in_cohort", "pct", "p10", "p50", "p90"]: df[c] = df[c].astype(float) titles_subplot = [f"Cohort #{i+1} (n={int(row['cohort_n'])})" for i, (_, row) in enumerate(df.iterrows())] fig = make_subplots(rows=len(df), cols=1, subplot_titles=titles_subplot) for i, (_, row) in enumerate(df.iterrows(), start=1): fig.add_trace(go.Scatter(x=[row["p10"], row["p90"]], y=[i, i], mode="lines", line=dict(color=lib.PALE_BLUE, width=14), showlegend=False), row=i, col=1) fig.add_trace(go.Scatter(x=[row["p50"]], y=[i], mode="markers", marker=dict(symbol="line-ns-open", color=lib.MUTED, size=22, line=dict(width=2)), showlegend=False), row=i, col=1) fig.add_trace(go.Scatter(x=[row["metric_in_cohort"]], y=[i], mode="markers+text", marker=dict(color=lib.NAVY, size=14), text=[f" P{int(row['pct']*100)}"], textposition="middle right", showlegend=False), row=i, col=1) fig.update_yaxes(visible=False, row=i, col=1) fig.update_xaxes(gridcolor=lib.GRID, row=i, col=1) median_pct = float(df["pct"].median()) if not df["pct"].empty else 0 title_lesson = f"{anchor_name} ranks at P{int(median_pct*100)} (median) within its comparable cohorts" subtitle = f"Each row = one comparable-set cohort. Pale band = P10-P90 range, vertical tick = P50, dark dot = {anchor_name}'s value." fig.update_layout(**lib.standard_layout( title=title_lesson, subtitle=subtitle, footer=lib.assemble_footer("PC-H-02", ["CL-016 EMBEDDING METHODOLOGY"], None, ["metrics_cap_daily"], "P10-P90 band = comparable cohort"), width=1200, height=160 + 120 * len(df), )) lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1100, height=160 + 120 * len(df)) lib.write_lesson_design(output_dir, chart_id, f"# {chart_id}\n\n{subtitle}\n") write_shim(output_dir, chart_id) lib.write_manifest(output_dir, chart_id, "ok", reg["spec_file"], pc_id="PC-H-02", intended_lesson_from_spec="CAP band", rendered_lesson={"reader_title": "CAP Band", "reader_subtitle": subtitle, "claim_strength": "strong", "rules_applied": "P10-P90 band"}, row_count=len(df), freshness_date=None, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)}