"""SOURCE render logic for TI-D-09 (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_D_09(reg: dict) -> dict: """Language and Origin Demand Overlay - DI by primary language.""" chart_id = "TI-D-09" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() # REPAIRED (audit, DB-verified 2026-06-12): # (a) primary language = the FIRST-LISTED row per title (insertion order preserves IMDb's listing; the old # "most recently updated row" rule crowned Latin #2 via The Passion of the Christ / Dead Poets Society), # plus a denylist of liturgical/dead/placeholder labels; # (b) MEDIAN demand per title, not mean (Kannada: median 2.6 vs mean 322.6 - one outlier carried the old #1). # Worldwide-framed chart โ†’ geo_code='WORLD' only. Demand Score basis (audience size). DENY_LANGS = ('Latin', 'Ancient (to 1453)', 'Sanskrit', 'Old English', 'Esperanto', 'None', 'Sign Languages', 'American Sign Language', 'Aramaic', 'Klingon', 'Quenya', 'xx', 'cn') df = lib.run_query(conn, """ WITH latest AS (SELECT MAX(file_date) AS file_date FROM title_metrics_daily), lang AS ( SELECT DISTINCT ON (imdb_id) imdb_id, language AS primary_language FROM categorization_languages WHERE language IS NOT NULL AND language NOT IN %s AND LENGTH(language) > 2 ORDER BY imdb_id, ctid -- first-listed row = the title's primary language ) SELECT lang.primary_language, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY t.demand_score::numeric) AS med_demand, COUNT(DISTINCT t.imdb_id) AS title_count, (SELECT file_date FROM latest) AS freshness_date FROM lang JOIN title_metrics_daily t USING (imdb_id) JOIN latest l USING (file_date) WHERE t.demand_score IS NOT NULL AND t.geo_code='WORLD' GROUP BY lang.primary_language; """, (DENY_LANGS,)) overall_df = lib.run_query(conn, """ WITH latest AS (SELECT MAX(file_date) AS file_date FROM title_metrics_daily) SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY demand_score::numeric) AS med_all FROM title_metrics_daily JOIN latest USING (file_date) WHERE demand_score IS NOT NULL AND geo_code='WORLD'; """) q_sec = time.time() - t0 conn.close() if df.empty or overall_df.empty: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No language data") write_shim(output_dir, chart_id) return {"status": "needs_spec_review"} freshness = str(df["freshness_date"].iloc[0]) df = df.drop(columns=["freshness_date"]) df["med_demand"] = df["med_demand"].astype(float) df["title_count"] = df["title_count"].astype(int) overall_med = float(overall_df["med_all"].iloc[0]) # The exec's niche question: which NON-English languages pull the most demand per typical title? non_eng = df[~df["primary_language"].str.lower().isin(["english", "en"])].copy() non_eng = non_eng[non_eng["title_count"] >= 30] # enough scale to be real n_qualifying = len(non_eng) # declared: the chart shows the top 12 of these non_eng = non_eng.nlargest(12, "med_demand").sort_values("med_demand", ascending=True) if non_eng.empty: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No non-English languages with scale") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} # ISO catalog labels โ†’ reader-facing names ('Modern Greek (1453-)' is a catalog token, not a label) non_eng["primary_language"] = non_eng["primary_language"].replace({"Modern Greek (1453-)": "Greek"}) fig = go.Figure() bar_texts = [f"{v:,.1f} per title ยท {int(c):,} titles" for v, c in zip(non_eng["med_demand"], non_eng["title_count"])] # navy = the leader (highest median pull) ONLY; the field stays blue (house contract) fig.add_trace(go.Bar(x=non_eng["med_demand"], y=non_eng["primary_language"], orientation="h", marker=dict(color=lib.leader_colors(non_eng["med_demand"])), text=bar_texts, textposition="outside", textfont=dict(size=10, color=lib.INK), cliponaxis=False, showlegend=False, hovertemplate="%{y}
typical title: %{x:,.1f} Demand Score")) fig.update_xaxes(range=lib.pad_range(non_eng["med_demand"], labels=bar_texts, font_px=10, fig_width=1200)) # the reference line crosses a bar on every row: draw it ABOVE the bars (layer-below dashes # vanish behind bar fills) and tie the label to it with an arrow instead of a floating box vline_above(fig, overall_med) ref_label_arrow(fig, overall_med, f"typical title, all languages: {overall_med:,.1f}", y_paper=0.045, ax=170) leader = non_eng.iloc[-1] mult = leader["med_demand"] / overall_med if lib.near_tie(non_eng["med_demand"], fmt=lambda v: f"{v / overall_med:.1f}"): second = non_eng.iloc[-2] title_lesson = (f"{leader['primary_language']} and {second['primary_language']} lead the non-English niches - " f"their typical title pulls {mult:.1f}x the typical title overall") else: title_lesson = (f"The standout non-English niche is {leader['primary_language']} - its typical title pulls " f"{mult:.1f}x the typical title overall") what = ("Non-English languages ranked by median demand per title - how much pull a single title in that " f"language usually gets; the dashed line marks the typical title across all languages. Top 12 of {n_qualifying} qualifying languages shown.") why = ("English is the saturated default; the opportunity is where demand outstrips supply. A language far above the line is a niche " "where a single well-placed title meets an eager audience with little competition - the exec's shortlist for where to commission next.") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1200, height=680), xaxis=lib.standard_xaxis("Median Demand Score per title"), yaxis=lib.standard_yaxis(showgrid=False)) # one compact methods clause (the old two-line standalone block blew the footer budget) lib.add_why_and_footer(fig, why, scope_footer(lib.assemble_footer("PC-D-09", ["CL-006 viewer panel (distinct viewers), not census"], freshness, ["title_metrics_daily", "title language catalog"], "worldwide Demand Score; median per title; primary = first-listed language; 30+ titles per language"))) # the dashed benchmark rides into the sidecar so the headline multiple reproduces from data.csv alone lib.save_csv(df.sort_values("med_demand", ascending=False).assign(overall_median_all_languages=overall_med), output_dir) lib.export_chart(fig, output_dir, width=1200, height=680) 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-D-09", intended_lesson_from_spec="Non-English language niches by demand-per-title (exec opportunity view)", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": "non-English; MEDIAN Demand Score per title; WORLD geo; first-listed primary language; dead-language denylist; >=30 titles"}, row_count=len(df), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)}