"""plex TMDB enrichment columns (E5 S2)

Additive TMDB-sourced fields on plex_items + plex_shows — keywords, full crew, budget/revenue, TMDb
community score, production companies/countries, recommendations, and a backdrop-path pool — plus a
`tmdb_id` cache and a `tmdb_enriched_at` watermark. Filled by the new TMDB enrichment job (matched by
the stored canonical `guid`), SERVED FROM THE ROW (never a live TMDB call). Distinct from the existing
Plex-sourced `imdb_rating`/`rating`, which are left untouched. Plus `plex_people.tmdb_person_id` (the S2
foundation for S6's person bios). ADD-only, all nullable → no table rewrite, no ACCESS EXCLUSIVE lock.
No watermark reset needed: `tmdb_enriched_at` starts NULL, so every eligible row is picked up once.
"""

from typing import Sequence, Union

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

revision: str = "0071_plex_tmdb_fields"
down_revision: Union[str, None] = "0070_fold_maintenance_batch"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

_TABLES = ("plex_items", "plex_shows")

# (name, type) — same set on both movie/leaf rows and show rows. budget/revenue stay NULL for shows.
_COLS: tuple[tuple[str, sa.types.TypeEngine], ...] = (
    ("tmdb_id", sa.Integer()),
    ("tmdb_enriched_at", sa.DateTime(timezone=True)),
    ("tmdb_rating", sa.Float()),
    ("tmdb_vote_count", sa.Integer()),
    ("budget", sa.BigInteger()),
    ("revenue", sa.BigInteger()),
    ("keywords", postgresql.JSONB()),
    ("crew", postgresql.JSONB()),
    ("studios", postgresql.JSONB()),
    ("production_countries", postgresql.JSONB()),
    ("recommendations", postgresql.JSONB()),
    ("backdrops", postgresql.JSONB()),
)


def upgrade() -> None:
    for t in _TABLES:
        for name, type_ in _COLS:
            op.add_column(t, sa.Column(name, type_, nullable=True))
    op.add_column("plex_people", sa.Column("tmdb_person_id", sa.Integer(), nullable=True))


def downgrade() -> None:
    op.drop_column("plex_people", "tmdb_person_id")
    for t in _TABLES:
        for name, _ in reversed(_COLS):
            op.drop_column(t, name)