"""SOURCE render logic for TI-I-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_I_07(reg: dict) -> dict:
"""Evergreen Franchise Ladder - franchise rollup of evergreen score."""
chart_id = "TI-I-07"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
df = lib.run_query(conn, """
WITH latest AS (SELECT MAX(file_date) AS file_date FROM metrics_evergreen_population_daily),
dedup AS (
-- franchises_movies_imdb has duplicate (franchise, imdb_id) rows; dedupe per title before summing
SELECT DISTINCT f.franchise_name, f.imdb_id, e.evergreen_score::numeric AS eg
FROM franchises_movies_imdb f
JOIN metrics_evergreen_population_daily e USING (imdb_id) JOIN latest l USING (file_date)
WHERE e.evergreen_score IS NOT NULL
)
SELECT franchise_name, SUM(eg) AS sum_eg, COUNT(*) AS title_count,
(SELECT file_date FROM latest) AS file_date
FROM dedup
GROUP BY franchise_name HAVING COUNT(*) >= 2
ORDER BY sum_eg DESC LIMIT 25;
""")
pop_max = lib.run_query(conn, """
SELECT MAX(evergreen_score)::numeric AS m FROM metrics_evergreen_population_daily
WHERE file_date = (SELECT MAX(file_date) FROM metrics_evergreen_population_daily);
""")
q_sec = time.time() - t0
conn.close()
if df.empty:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No franchise-evergreen data")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
df["sum_eg"] = df["sum_eg"].astype(float); df["title_count"] = df["title_count"].astype(int)
df["avg_eg"] = df["sum_eg"] / df["title_count"]
df = df.sort_values("sum_eg", ascending=True)
freshness = str(df["file_date"].iloc[0])
eg_scale = float(pop_max["m"].iloc[0]) if not pop_max.empty and pd.notna(pop_max["m"].iloc[0]) else 126.0
fig = go.Figure()
fig.add_trace(go.Bar(x=df["sum_eg"], y=df["franchise_name"], orientation="h",
# color contract: NAVY = the leader (deepest catalog) ONLY; pale-steel field
marker=dict(color=lib.leader_colors(df["sum_eg"], field=lib.PALE_BLUE)),
# ONE unit word per chart: this is a film-franchise board, so the bar
# labels say 'films' like the headline, axis and footer (not 'titles')
text=[f" {int(t)} films · {a:.0f} avg" for t, a in zip(df["title_count"], df["avg_eg"])],
textposition="outside", textfont=dict(size=10, color=lib.MUTED), cliponaxis=False, showlegend=False,
customdata=df[["title_count", "avg_eg"]],
hovertemplate="%{y}
combined evergreen score %{x:,.0f} · %{customdata[0]} films · %{customdata[1]:.0f} avg/film"))
fig.update_xaxes(range=[0, float(df["sum_eg"].max()) * 1.22])
top_franchise = df.iloc[-1]
title_lesson = (f"{top_franchise['franchise_name']} has the deepest evergreen film catalog - {int(top_franchise['title_count'])} films "
f"that keep drawing audiences decade after decade")
what = ("Franchises ranked by the combined evergreen (timelessness) score of all member films - one combined total per franchise, so those "
f"with many durable films rank high. Each bar shows its film count and average score (a single film tops out ≈ {eg_scale:.0f} today); min 2 films.")
why = ("Evergreen franchises are durable IP engines - libraries that keep drawing audiences across many films, decade after decade. "
"The combined evergreen score is a proxy for the licensing value locked in a franchise's whole catalog, not just its biggest film.")
fig.update_layout(**lib.layout_v2(title_lesson, what, width=1200, height=820),
xaxis=lib.standard_xaxis("Combined evergreen score (all films in franchise)", tickformat=",d"),
yaxis=lib.standard_yaxis(showgrid=False))
lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-I-07",
["CL-006 viewer panel (distinct viewers), not census"], freshness,
["IMDb franchise catalog", "PeerLogix evergreen rollup"],
f"top {len(df)} franchises by combined score, min 2 films; film franchises only - TV entries not counted"))
# the subtitle's single-film ceiling rides into the sidecar so the printed calibration is reproducible
df["single_film_max_today"] = eg_scale
lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1200, height=820)
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-I-07",
intended_lesson_from_spec="Evergreen franchise ladder (combined total clarified)",
rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what,
"claim_strength": "strong", "rules_applied": "sum + avg shown, min 2 films; movies-only scope declared; one unit word (films); top-N scope on footer"},
row_count=len(df), freshness_date=freshness,
extra={"single_film_max_today": round(eg_scale, 2)},
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}