"""plex rich-detail item/show columns (E5 S5b)

On plex_items and plex_shows: the fields that were previously read LIVE from plex.metadata() on every
info-page view — `imdb_rating` (the IMDb-specific score, distinct from `rating` = audienceRating),
`imdb_id` (tt…), `tagline`, and `full_cast` (ordered [{name, role, thumb}]). Persisting them lets the
detail endpoints serve from the DB row instead of a live Plex call (outage-resilient, and the
precondition for S6's pure-DB language-aware serving).

These are filled by the same rich backfill that already fills the full cast (`_enrich_people_batch`),
so the data step below resets `people_enriched_at` to NULL on movie/show rows: the backfill re-fetches
each once (60/run, self-throttled) and stamps the new columns. ADD-only, all nullable → no table
rewrite, no generated column, no ACCESS EXCLUSIVE lock (unlike 0064).
"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

revision: str = "0065_plex_detail_fields"
down_revision: Union[str, None] = "0064_plex_hu_overlay_cols"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

_TABLES = ("plex_items", "plex_shows")


def upgrade() -> None:
    for t in _TABLES:
        op.add_column(t, sa.Column("imdb_rating", sa.Float(), nullable=True))
        op.add_column(t, sa.Column("imdb_id", sa.String(16), nullable=True))
        op.add_column(t, sa.Column("tagline", sa.Text(), nullable=True))
        op.add_column(t, sa.Column("full_cast", postgresql.JSONB(), nullable=True))
    # Force a one-time re-enrich so already-enriched rows gain the new columns (episodes are never
    # enriched, so their NULL people_enriched_at is unaffected; they keep serving detail live).
    op.execute("UPDATE plex_items SET people_enriched_at = NULL WHERE kind = 'movie'")
    op.execute("UPDATE plex_shows SET people_enriched_at = NULL")


def downgrade() -> None:
    for t in _TABLES:
        op.drop_column(t, "full_cast")
        op.drop_column(t, "tagline")
        op.drop_column(t, "imdb_id")
        op.drop_column(t, "imdb_rating")