"""plex_items playback-class facet (E7: filter the feed by Direct Play / Direct Stream / Transcode)

Adds the columns the playback-class filter needs:
  * transcode_hw   — for a `playable="transcode"` item, whether the prod iGPU can hardware-transcode it
                     (codec HW-decodable AND 8-bit SDR) vs software-only. NULL for non-transcode items.
  * video_bit_depth — the source video bit depth (from the Plex media Stream), NULL until probed.
  * media_probed_at — watermark for the bit-depth backfill (NULL = not yet probed).

Backfill for existing rows: `transcode_hw` is derived from the ALREADY-stored `codec_video` alone (the
same HW-decodable allow-list `plex.sync.HW_DECODABLE_CODECS` / `plex.stream` use), so the filter works
immediately after migration. Bit depth is unknown for existing rows (the cheap Plex listing doesn't
carry it), so a 10-bit/HDR HEVC is provisionally 'hw' here and later refined to 'sw' by the bit-depth
backfill (`plex.sync._probe_media_batch`) once its Stream is probed. `video_bit_depth`/`media_probed_at`
start NULL.
"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "0077_plex_playback_class"
down_revision: Union[str, None] = "0076_media_asset_kind"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.add_column("plex_items", sa.Column("transcode_hw", sa.Boolean(), nullable=True))
    op.add_column("plex_items", sa.Column("video_bit_depth", sa.Integer(), nullable=True))
    op.add_column(
        "plex_items", sa.Column("media_probed_at", sa.DateTime(timezone=True), nullable=True)
    )
    op.create_index("ix_plex_items_transcode_hw", "plex_items", ["transcode_hw"])
    # Codec-only derivation for existing rows — keep this codec set identical to
    # plex.sync.HW_DECODABLE_CODECS. Bit depth is unknown here, so provisionally 8-bit (→ hw for a
    # decodable codec); the bit-depth backfill downgrades 10-bit/HDR HEVC to sw later.
    op.execute(
        """
        UPDATE plex_items
           SET transcode_hw = CASE
               WHEN playable = 'transcode'
               THEN lower(codec_video) IN ('h264','hevc','h265','vp9','vc1','mpeg2video')
               ELSE NULL
           END
        """
    )


def downgrade() -> None:
    op.drop_index("ix_plex_items_transcode_hw", table_name="plex_items")
    op.drop_column("plex_items", "media_probed_at")
    op.drop_column("plex_items", "video_bit_depth")
    op.drop_column("plex_items", "transcode_hw")