"""SOURCE render logic for TI-I-06 (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_06(reg: dict) -> dict:
"""Dormant Title Reactivation Feed - old titles with recent DI rise."""
chart_id = "TI-I-06"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
# L-046: a raw "demand now minus demand 28 days ago" delta is panel-contaminated (panel size swings between
# the two dates), so the comeback is measured on each title's SHARE of that day's total US panel demand at
# both endpoints; the share delta is then scaled to TODAY's demand units for a readable bar. NOTE: do NOT use
# mom_28d here - it's an SI (momentum-shape) ratio, not a growth rate. "Dormant" guard: the current top-50 by
# Demand Score is excluded (still-mainstream hits are not re-discoveries).
df = lib.run_query(conn, """
WITH latest AS (SELECT MAX(file_date) AS fd FROM title_metrics_daily),
prior AS (SELECT ((SELECT fd FROM latest) - INTERVAL '28 days')::date AS fd),
tot_now AS (SELECT SUM(di::numeric) AS s FROM title_metrics_daily, latest
WHERE file_date = latest.fd AND geo_code='US' AND di IS NOT NULL),
tot_then AS (SELECT SUM(di::numeric) AS s FROM title_metrics_daily, prior
WHERE file_date = prior.fd AND geo_code='US' AND di IS NOT NULL),
top50 AS (SELECT imdb_id FROM title_metrics_daily, latest
WHERE file_date = latest.fd AND geo_code='US' AND demand_score IS NOT NULL
ORDER BY demand_score DESC LIMIT 50),
now_r AS (SELECT imdb_id, di::numeric AS di_now FROM title_metrics_daily, latest
WHERE file_date = latest.fd AND geo_code='US' AND di IS NOT NULL),
then_r AS (SELECT imdb_id, di::numeric AS di_then FROM title_metrics_daily, prior
WHERE file_date = prior.fd AND geo_code='US' AND di IS NOT NULL),
rel AS (SELECT imdb_id, release_date,
EXTRACT(YEAR FROM AGE((SELECT fd FROM latest), release_date))::integer AS years_since
FROM categorization_release_dates_all WHERE release_date IS NOT NULL)
SELECT n.imdb_id, n.di_now, t.di_then, r.years_since,
n.di_now / (SELECT s FROM tot_now) AS share_now,
t.di_then / (SELECT s FROM tot_then) AS share_then,
(SELECT s FROM tot_now) AS tot_now,
(SELECT fd FROM latest) AS file_date
FROM now_r n JOIN then_r t USING (imdb_id) JOIN rel r USING (imdb_id)
WHERE r.years_since >= 5 AND n.di_now >= 150 AND t.di_then >= 25
AND n.imdb_id NOT IN (SELECT imdb_id FROM top50)
AND n.di_now / (SELECT s FROM tot_now) >= 1.30 * (t.di_then / (SELECT s FROM tot_then))
ORDER BY (n.di_now / (SELECT s FROM tot_now) - t.di_then / (SELECT s FROM tot_then)) DESC LIMIT 18;
""")
q_sec = time.time() - t0
if df.empty:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No dormant reactivations")
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", "start_year"]], on="imdb_id", how="left")
df["display_title"] = df["display_title"].fillna(df["imdb_id"])
df["di"] = df["di_now"].astype(float); df["di_then"] = df["di_then"].astype(float)
df["share_now"] = df["share_now"].astype(float); df["share_then"] = df["share_then"].astype(float)
freshness = str(df["file_date"].iloc[0])
tot_now = float(df["tot_now"].iloc[0])
# share-of-panel-adjusted demand added = share delta scaled to today's total
df["gain"] = (df["share_now"] - df["share_then"]) * tot_now
df["pct"] = df["share_now"] / df["share_then"] - 1.0
df = df.drop(columns=["tot_now"]).sort_values("gain", ascending=True)
def i06_label(r):
# disambiguate remakes/same-name titles: the release YEAR (not a bare age) is the identity anchor,
# e.g. 'Criminal Minds (2017)' = the Korean remake, not the 2005 CBS series.
t = short_title(str(r["display_title"]), 32)
yr = r.get("start_year")
return f"{t} ({int(yr)})" if pd.notna(yr) else f"{t} ({int(r['years_since'])}y old)"
df["label"] = df.apply(i06_label, axis=1)
# bar text LEADS with the gain (what the bar length encodes), so a +930% off a small base lower
# on the board can't read as out-ranking the headline's +45% leader at a glance
bar_texts = [f" +{g:,.0f} Demand Score (+{m:.0%} on its prior level)" for g, m in zip(df["gain"], df["pct"])]
fig = go.Figure()
fig.add_trace(go.Bar(x=df["gain"], y=df["label"], orientation="h",
marker=dict(color=lib.GREEN),
text=bar_texts,
textposition="outside", textfont=dict(size=10, color=lib.MUTED), cliponaxis=False, showlegend=False,
customdata=df[["di", "pct"]],
hovertemplate="%{y}
gained %{x:,.0f} demand vs 28 days ago (measured on share of panel) · now at %{customdata[0]:,.0f} (+%{customdata[1]:.0%})"))
fig.update_xaxes(range=lib.pad_range(df["gain"], labels=bar_texts, font_px=10, fig_width=1200))
top_react = df.iloc[-1]
title_lesson = (f"Back-catalog titles are surging: {top_react['display_title']} ({int(top_react['years_since'])} years old) "
f"added the most new demand of the past 28 days (up {top_react['pct']:.0%} on its own prior level)")
what = ("Titles at least 5 years old that gained the most viewing demand over the past 28 days. Measured on each title's share "
"of total panel demand - so panel-size swings can't fake a comeback - with today's top-50 hits excluded.")
why = ("These are older titles catching fire again - a new season, a finale, a meme, a new adaptation, a cultural moment. Catching "
"the lift early means promoting or licensing a title exactly as its audience comes back.")
fig.update_layout(**lib.layout_v2(title_lesson, what, width=1200, height=720),
xaxis=lib.standard_xaxis("Demand gained over the past 28 days", tickformat=",d"),
yaxis=lib.standard_yaxis(showgrid=False))
lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-I-06",
["CL-006 viewer panel (distinct viewers), not census", "CL-013 28-day change",
"change measured on share-of-panel at both endpoints (raw cross-date deltas track panel size)"], freshness,
["title_metrics_daily", "categorization_release_dates_all"],
"5y+ old, up 30%+ on share of panel · floors: 150+ demand points now, 25+ then · current top-50 excluded"))
# sidecar hygiene: internal 'di' column names never ship ('di' duplicates di_now)
lib.save_csv(df.drop(columns=["di"]).rename(columns={"di_now": "demand_now", "di_then": "demand_then"}), output_dir)
lib.export_chart(fig, output_dir, width=1200, height=720)
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-06",
intended_lesson_from_spec="Back-catalog surge feed (share-of-panel, floors shown)",
rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what,
"claim_strength": "strong",
"rules_applied": "≥5y old; share-of-panel +30% over 28d; di_now>=150, di_then>=25; top-50 by Demand Score excluded; surging (not 'rediscovered') framing"},
row_count=len(df), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}