"""Plex's tables: a self-contained catalog mirrored from a local Plex server — libraries, shows,
seasons, items, people, collections, the server's playlists — and each user's private part of it:
watch state, the account link, their own playlists. Only this unit writes them; another unit reads
them along a declared edge, or asks this unit's service."""

from datetime import datetime

from sqlalchemy import (
    BigInteger,
    Boolean,
    Computed,
    Date,
    DateTime,
    Float,
    ForeignKey,
    Index,
    Integer,
    JSON,
    String,
    Text,
    UniqueConstraint,
)
from sqlalchemy.dialects.postgresql import JSONB, TSVECTOR
from sqlalchemy.orm import Mapped, mapped_column

from app.db import Base
from app.models import TimestampMixin, UpdatedAtMixin


# --- Plex integration (optional) -------------------------------------------------------------
# A dedicated, self-contained catalog mirrored from a locally-reachable Plex server. Playback is
# from the LOCAL physical file (Plex is metadata only); per-user watch state lives in plex_states
# so it works for users without a Plex account. Mirrors the app's "shared catalog, private
# per-user state" model. The weighted FTS search_vector uses the same unaccent_simple config as
# `videos` (migrations 0031/0032).


class PlexLibrary(Base, TimestampMixin, UpdatedAtMixin):
    """A mirrored Plex library section. Only `enabled` sections are exposed in the feed.
    `plex_key` is the section id on the Plex server."""

    __tablename__ = "plex_libraries"

    id: Mapped[int] = mapped_column(primary_key=True)
    plex_key: Mapped[str] = mapped_column(String(32), unique=True, index=True)
    title: Mapped[str] = mapped_column(String(255))
    kind: Mapped[str] = mapped_column(String(16))  # "movie" | "show"
    enabled: Mapped[bool] = mapped_column(Boolean, default=True, server_default="true")
    synced_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    # HU-overlay support. `metadata_lang` tags the section's metadata language. An OVERLAY
    # library (overlay_of set) is the Hungarian-metadata twin of another section: it is never browsable
    # on its own — its titles' HU fields are written onto the matched EN rows (paired by `guid`).
    metadata_lang: Mapped[str] = mapped_column(String(8), default="en", server_default="en")
    overlay_of: Mapped[int | None] = mapped_column(
        ForeignKey("plex_libraries.id", ondelete="SET NULL"), index=True
    )


class PlexShow(Base, TimestampMixin, UpdatedAtMixin):
    """A TV show (grandparent of episodes) — holds poster + summary for the drill-down page, plus
    the same filterable metadata as movies (genres/rating/content_rating/studio/people) so the TV
    grid can be filtered/sorted like the movie grid. All mirrored cheaply from the show listing."""

    __tablename__ = "plex_shows"
    __table_args__ = (
        Index("ix_plex_shows_genres_gin", "genres", postgresql_using="gin"),
        Index("ix_plex_shows_directors_gin", "directors", postgresql_using="gin"),
        Index("ix_plex_shows_cast_gin", "cast_names", postgresql_using="gin"),
        Index("ix_plex_shows_genres_hu_gin", "genres_hu", postgresql_using="gin"),
        # ix_plex_shows_collections_gin already created with collections (migration 0047).
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    rating_key: Mapped[str] = mapped_column(String(32), unique=True, index=True)
    library_id: Mapped[int] = mapped_column(
        ForeignKey("plex_libraries.id", ondelete="CASCADE"), index=True
    )
    # Canonical external id (e.g. "tmdb:12345") — the key that pairs this EN row with its HU-overlay
    # twin during sync. Indexed for the guid->row overlay match.
    guid: Mapped[str | None] = mapped_column(String(64), index=True)
    title: Mapped[str] = mapped_column(String(512))
    summary: Mapped[str | None] = mapped_column(Text)
    year: Mapped[int | None] = mapped_column(Integer)
    thumb_key: Mapped[str | None] = mapped_column(
        String(512)
    )  # Plex thumb path (proxied, not stored)
    art_key: Mapped[str | None] = mapped_column(String(512))
    child_count: Mapped[int | None] = mapped_column(Integer)  # seasons
    collection_keys: Mapped[list | None] = mapped_column(
        JSONB(none_as_null=True)
    )  # Plex collections this show is in
    added_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), index=True)
    # Filterable / orderable metadata mirrored from the show section listing (parallels PlexItem).
    rating: Mapped[float | None] = mapped_column(Float, index=True)  # Plex audienceRating (~IMDb)
    content_rating: Mapped[str | None] = mapped_column(String(16), index=True)
    studio: Mapped[str | None] = mapped_column(String(255), index=True)  # network/studio
    originally_available_at: Mapped[Date | None] = mapped_column(Date, index=True)  # first air date
    # none_as_null: an empty tag list must persist as SQL NULL, not JSON `null`. Without it a Python
    # None round-trips as a JSON scalar that jsonb_array_elements_text() can't unnest ("cannot extract
    # elements from a scalar") — the trap a metadata-poor library sprang on /facets. Same on plex_items.
    genres: Mapped[list | None] = mapped_column(JSONB(none_as_null=True))
    directors: Mapped[list | None] = mapped_column(JSONB(none_as_null=True))
    cast_names: Mapped[list | None] = mapped_column(JSONB(none_as_null=True))
    people_text: Mapped[str | None] = mapped_column(
        Text
    )  # cast+directors, folded into search_vector
    # Set once the show's FULL cast has been rich-fetched by the backfill; NULL = still the listing top-3.
    people_enriched_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    # Rich-fetch-only detail fields, persisted by the same backfill that fills the full cast so
    # the info page serves from this row instead of a live plex.metadata() call. NULL until enriched.
    imdb_rating: Mapped[float | None] = mapped_column(
        Float
    )  # IMDb-specific score (vs `rating` = audienceRating)
    imdb_id: Mapped[str | None] = mapped_column(String(16))  # tt… → the clickable IMDb link
    tagline: Mapped[str | None] = mapped_column(Text)
    full_cast: Mapped[list | None] = mapped_column(
        JSONB(none_as_null=True)
    )  # ordered [{name, role, thumb}]
    # --- TMDB enrichment: see the identical block on plex_items for the full rationale. Additive;
    #     served from the row; NULL until enriched. (`budget`/`revenue` stay NULL for shows — TV has none.) ---
    tmdb_id: Mapped[int | None] = mapped_column(Integer)
    tmdb_enriched_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    tmdb_rating: Mapped[float | None] = mapped_column(Float)  # TMDb vote_average (0–10)
    tmdb_vote_count: Mapped[int | None] = mapped_column(Integer)
    budget: Mapped[int | None] = mapped_column(BigInteger)
    revenue: Mapped[int | None] = mapped_column(BigInteger)
    keywords: Mapped[list | None] = mapped_column(JSONB(none_as_null=True))  # [str]
    crew: Mapped[list | None] = mapped_column(
        JSONB(none_as_null=True)
    )  # [{name, job, tmdb_id, thumb}]
    studios: Mapped[list | None] = mapped_column(
        JSONB(none_as_null=True)
    )  # TMDB production companies [str]
    production_countries: Mapped[list | None] = mapped_column(JSONB(none_as_null=True))  # [str]
    recommendations: Mapped[list | None] = mapped_column(JSONB(none_as_null=True))
    backdrops: Mapped[list | None] = mapped_column(JSONB(none_as_null=True))
    # Hungarian-metadata twin fields, written by the overlay sync. NULL = no HU twin matched,
    # so serving falls back to the EN columns. `people_text` is language-neutral, reused for both vectors.
    title_hu: Mapped[str | None] = mapped_column(String(512))
    tagline_hu: Mapped[str | None] = mapped_column(Text)  # the HU overlay's tagline
    summary_hu: Mapped[str | None] = mapped_column(Text)
    genres_hu: Mapped[list | None] = mapped_column(JSONB(none_as_null=True))
    search_vector: Mapped[object | None] = mapped_column(
        TSVECTOR,
        Computed(
            "setweight(to_tsvector('public.unaccent_simple', coalesce(title, '')), 'A') || "
            "setweight(to_tsvector('public.unaccent_simple', coalesce(people_text, '')), 'B') || "
            "setweight(to_tsvector('public.unaccent_simple', left(coalesce(summary, ''), 1000)), 'C')",
            persisted=True,
        ),
    )
    search_vector_hu: Mapped[object | None] = mapped_column(
        TSVECTOR,
        Computed(
            "setweight(to_tsvector('public.unaccent_simple', coalesce(title_hu, '')), 'A') || "
            "setweight(to_tsvector('public.unaccent_simple', coalesce(people_text, '')), 'B') || "
            "setweight(to_tsvector('public.unaccent_simple', left(coalesce(summary_hu, ''), 1000)), 'C')",
            persisted=True,
        ),
    )


class PlexSeason(Base, TimestampMixin, UpdatedAtMixin):
    """A season under a show — used to order episodes for prev/next stepping."""

    __tablename__ = "plex_seasons"

    id: Mapped[int] = mapped_column(primary_key=True)
    rating_key: Mapped[str] = mapped_column(String(32), unique=True, index=True)
    show_id: Mapped[int] = mapped_column(
        ForeignKey("plex_shows.id", ondelete="CASCADE"), index=True
    )
    title: Mapped[str | None] = mapped_column(String(255))
    season_number: Mapped[int | None] = mapped_column(Integer, index=True)
    thumb_key: Mapped[str | None] = mapped_column(String(512))


class PlexItem(Base, TimestampMixin, UpdatedAtMixin):
    """A playable LEAF — a movie or a single episode. This is what the feed lists and the player
    plays. Movies carry `library_id` and no parents; episodes carry show/season refs +
    season/episode numbers. `file_path` is the Plex-side absolute path of the physical media,
    mapped through `plex_path_map` to a local mount at playback time. `playable` is the
    ffprobe-derived browser-compatibility class (direct | remux | transcode)."""

    __tablename__ = "plex_items"
    __table_args__ = (
        Index("ix_plex_items_show_order", "show_id", "season_number", "episode_number"),
        Index("ix_plex_items_genres_gin", "genres", postgresql_using="gin"),
        Index("ix_plex_items_directors_gin", "directors", postgresql_using="gin"),
        Index("ix_plex_items_cast_gin", "cast_names", postgresql_using="gin"),
        Index("ix_plex_items_collections_gin", "collection_keys", postgresql_using="gin"),
        Index("ix_plex_items_genres_hu_gin", "genres_hu", postgresql_using="gin"),
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    rating_key: Mapped[str] = mapped_column(String(32), unique=True, index=True)
    library_id: Mapped[int] = mapped_column(
        ForeignKey("plex_libraries.id", ondelete="CASCADE"), index=True
    )
    kind: Mapped[str] = mapped_column(String(16), index=True)  # "movie" | "episode"
    # Canonical external id (e.g. "tmdb:12345") — pairs a MOVIE row with its HU-overlay twin during
    # sync; indexed for the guid->row match. Episodes carry it too but it's unused for them.
    guid: Mapped[str | None] = mapped_column(String(64), index=True)
    show_id: Mapped[int | None] = mapped_column(
        ForeignKey("plex_shows.id", ondelete="CASCADE"), index=True
    )
    season_id: Mapped[int | None] = mapped_column(
        ForeignKey("plex_seasons.id", ondelete="CASCADE"), index=True
    )
    season_number: Mapped[int | None] = mapped_column(Integer)
    episode_number: Mapped[int | None] = mapped_column(Integer)
    title: Mapped[str] = mapped_column(String(512))
    summary: Mapped[str | None] = mapped_column(Text)
    year: Mapped[int | None] = mapped_column(Integer)
    duration_s: Mapped[int | None] = mapped_column(Integer)
    thumb_key: Mapped[str | None] = mapped_column(String(512))
    art_key: Mapped[str | None] = mapped_column(String(512))
    cast: Mapped[list | None] = mapped_column(JSON)  # [role names]
    # Physical media (from the Plex Part).
    file_path: Mapped[str | None] = mapped_column(Text)  # Plex-side absolute path
    part_key: Mapped[str | None] = mapped_column(String(255))  # /library/parts/... (fallback fetch)
    container: Mapped[str | None] = mapped_column(String(16))
    codec_video: Mapped[str | None] = mapped_column(String(32))
    codec_audio: Mapped[str | None] = mapped_column(String(32))
    size_bytes: Mapped[int | None] = mapped_column(BigInteger)
    # Browser-playability class from ffprobe: "direct" | "remux" | "transcode" (None = unprobed).
    playable: Mapped[str | None] = mapped_column(String(12), index=True)
    probed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    # Transcode HW-capability facet (the playback-class filter). NULL for non-transcode items; for a
    # `playable="transcode"` item: True = the prod iGPU can hardware-transcode it (codec is HW-decodable
    # AND the source is 8-bit SDR), False = software libx264 only (AV1/XviD, or a 10-bit/HDR source that
    # breaks the VAAPI nv12 pipeline — see plex/stream.py). Derived by `plex.sync.transcode_hw_capable`:
    # set codec-only at sync (bit depth unknown from the cheap listing), then refined by the bit-depth
    # backfill (`_probe_media_batch`) which downgrades a 10-bit/HDR HEVC to False. The one filterable
    # home of the invariant `_want_hw`'s source gate computes at play time — kept in sync via the shared
    # classifier so the catalog facet and the runtime encoder choice never disagree by codec.
    transcode_hw: Mapped[bool | None] = mapped_column(Boolean, index=True)
    # Video bit depth (8/10/12…) from the Plex media Stream, filled by the bit-depth backfill. NULL =
    # not yet probed. Surfaced on the info page (e.g. "HEVC · 10-bit") and feeds `transcode_hw`.
    video_bit_depth: Mapped[int | None] = mapped_column(Integer)
    # Watermark for the bit-depth backfill: when this item's media Stream was last probed for
    # bit-depth/HDR. NULL = never (the backfill's work-list). Only HW-decodable transcode items are
    # probed (the rest are SW-only by codec and need no bit-depth); see `_probe_media_batch`.
    media_probed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    added_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), index=True)
    # Filterable / orderable metadata mirrored from the Plex section listing (no extra API calls;
    # `rating` = Plex audienceRating, which is ~the IMDb score). The arrays back genre/person/studio
    # filters (GIN-indexed) and double as content signals for a future watch-habit recommender.
    rating: Mapped[float | None] = mapped_column(Float, index=True)
    content_rating: Mapped[str | None] = mapped_column(String(16), index=True)
    studio: Mapped[str | None] = mapped_column(String(255), index=True)
    originally_available_at: Mapped[Date | None] = mapped_column(Date, index=True)
    genres: Mapped[list | None] = mapped_column(JSONB(none_as_null=True))
    directors: Mapped[list | None] = mapped_column(JSONB(none_as_null=True))
    cast_names: Mapped[list | None] = mapped_column(JSONB(none_as_null=True))
    # rating_keys of the Plex collections this movie belongs to (GIN — filter + card-strip siblings).
    collection_keys: Mapped[list | None] = mapped_column(JSONB(none_as_null=True))
    # Cast + director names, space-joined — folded into search_vector (weight B) so the search box
    # finds titles by a person's name (and ranks them above summary-only mentions).
    people_text: Mapped[str | None] = mapped_column(Text)
    # Set once the movie's FULL cast has been rich-fetched by the backfill; NULL = still the listing top-3.
    people_enriched_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    # Rich-fetch-only detail fields, persisted by the same backfill that fills the full cast so
    # the info page serves from this row instead of a live plex.metadata() call. NULL until enriched.
    imdb_rating: Mapped[float | None] = mapped_column(
        Float
    )  # IMDb-specific score (vs `rating` = audienceRating)
    imdb_id: Mapped[str | None] = mapped_column(String(16))  # tt… → the clickable IMDb link
    tagline: Mapped[str | None] = mapped_column(Text)
    full_cast: Mapped[list | None] = mapped_column(
        JSONB(none_as_null=True)
    )  # ordered [{name, role, thumb}]
    # --- TMDB enrichment: matched by the canonical `guid` ("tmdb:12345"), filled by the TMDB
    #     backfill job, SERVED FROM THIS ROW (never a live TMDB call). Additive — imdb_rating/rating
    #     above stay Plex-sourced; `tmdb_rating` is the distinct TMDb community score. NULL until enriched;
    #     `tmdb_enriched_at` is the "tried" watermark (stamped on a definitive no-match too, so an
    #     unresolvable title isn't re-fetched every run; left NULL only on a transient fetch error). ---
    tmdb_id: Mapped[int | None] = mapped_column(Integer)
    tmdb_enriched_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    tmdb_rating: Mapped[float | None] = mapped_column(Float)  # TMDb vote_average (0–10)
    tmdb_vote_count: Mapped[int | None] = mapped_column(Integer)
    budget: Mapped[int | None] = mapped_column(BigInteger)  # USD (movies; TV has none)
    revenue: Mapped[int | None] = mapped_column(BigInteger)
    keywords: Mapped[list | None] = mapped_column(JSONB(none_as_null=True))  # [str]
    crew: Mapped[list | None] = mapped_column(
        JSONB(none_as_null=True)
    )  # [{name, job, tmdb_id, thumb}]
    studios: Mapped[list | None] = mapped_column(
        JSONB(none_as_null=True)
    )  # TMDB production companies [str]
    production_countries: Mapped[list | None] = mapped_column(JSONB(none_as_null=True))  # [str]
    # [{tmdb_id, title, poster, media_type}] — resolved to library rows (by guid) at serve time.
    recommendations: Mapped[list | None] = mapped_column(JSONB(none_as_null=True))
    backdrops: Mapped[list | None] = mapped_column(
        JSONB(none_as_null=True)
    )  # TMDB backdrop file_paths [str] (crossfade pool)
    # Hungarian-metadata twin fields, written by the overlay sync. NULL = no HU twin matched
    # (serving falls back to the EN columns). `people_text` is language-neutral, reused for both vectors.
    title_hu: Mapped[str | None] = mapped_column(String(512))
    tagline_hu: Mapped[str | None] = mapped_column(Text)  # the HU overlay's tagline
    summary_hu: Mapped[str | None] = mapped_column(Text)
    genres_hu: Mapped[list | None] = mapped_column(JSONB(none_as_null=True))
    search_vector: Mapped[object | None] = mapped_column(
        TSVECTOR,
        Computed(
            "setweight(to_tsvector('public.unaccent_simple', coalesce(title, '')), 'A') || "
            "setweight(to_tsvector('public.unaccent_simple', coalesce(people_text, '')), 'B') || "
            "setweight(to_tsvector('public.unaccent_simple', left(coalesce(summary, ''), 1000)), 'C')",
            persisted=True,
        ),
    )
    search_vector_hu: Mapped[object | None] = mapped_column(
        TSVECTOR,
        Computed(
            "setweight(to_tsvector('public.unaccent_simple', coalesce(title_hu, '')), 'A') || "
            "setweight(to_tsvector('public.unaccent_simple', coalesce(people_text, '')), 'B') || "
            "setweight(to_tsvector('public.unaccent_simple', left(coalesce(summary_hu, ''), 1000)), 'C')",
            persisted=True,
        ),
    )


class PlexPerson(Base, TimestampMixin, UpdatedAtMixin):
    """A small cast/crew headshot cache, keyed by name, so person filter-chips (the cards for the
    actors currently in the filter) can show a photo + count without a live per-card metadata fetch.
    Populated opportunistically when an item's RICH metadata is fetched — the info-page view and the
    periodic rich re-sync — since the cheap section listing that drives the main sync only carries the
    top few cast. Rows whose name no longer appears in ANY item's cast_names/directors (a library
    removal orphaned them) are pruned by the maintenance sweep, mirroring how Plex drops orphaned
    metadata. The filterable cast lives in plex_items/plex_shows.cast_names (self-cleaning with the
    row); this table is the people's own facts: the headshot link, the TMDB link and the TMDB bio
    (the headshot FILE lives in the image cache, filled by the people_enrich job)."""

    __tablename__ = "plex_people"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(255), unique=True, index=True)
    photo_thumb: Mapped[str | None] = mapped_column(Text)  # proxied /person-image URL, or None
    # TMDB person id, linked by name from a title's TMDB credits (the /person
    # biography fetch builds on it). NULL until a TMDB-enriched title names this person.
    tmdb_person_id: Mapped[int | None] = mapped_column(Integer)
    # --- TMDB person enrichment: fetched from /person/{tmdb_person_id} by the people_enrich job,
    #     or by the first person-page open the job hasn't reached, then SERVED FROM THE ROW. `tmdb_enriched_at` is the watermark (set even on a
    #     no-op fetch so it isn't retried every open). `known_for` = top combined-credits [{tmdb_id,
    #     media_type, title}], resolved to library rows by guid at serve time (like recommendations). ---
    biography: Mapped[str | None] = mapped_column(Text)
    # TMDB's Hungarian biography, from the same request's `translations`; a Hungarian reader is
    # served it, and the instance-language `biography` when TMDB has none. Birthplace, aliases and
    # keywords have no Hungarian on TMDB, so they stay in the instance language.
    biography_hu: Mapped[str | None] = mapped_column(Text)
    birthday: Mapped[str | None] = mapped_column(String(10))  # "YYYY-MM-DD", served as-is
    deathday: Mapped[str | None] = mapped_column(String(10))
    place_of_birth: Mapped[str | None] = mapped_column(Text)
    known_for_department: Mapped[str | None] = mapped_column(String(64))
    also_known_as: Mapped[list | None] = mapped_column(JSONB(none_as_null=True))  # [str]
    known_for: Mapped[list | None] = mapped_column(
        JSONB(none_as_null=True)
    )  # [{tmdb_id, media_type, title}]
    tmdb_enriched_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))


class PlexCollection(Base, TimestampMixin, UpdatedAtMixin):
    """A mirrored Plex collection (a grouping of movies or shows — franchises like Avatar, curated
    sets like MCU, or auto/community lists like IMDb Top 250). Mirrored in full by the background
    sync so reads never hit Plex's slow collection queries. Membership is stored as `collection_keys`
    on the member movies (plex_items) / shows (plex_shows). `smart` = Plex's query-based flag;
    `editable` = we may write changes back to Plex (default False = read-only; set for curated ones)."""

    __tablename__ = "plex_collections"

    id: Mapped[int] = mapped_column(primary_key=True)
    rating_key: Mapped[str] = mapped_column(String(32), unique=True, index=True)
    library_id: Mapped[int] = mapped_column(
        ForeignKey("plex_libraries.id", ondelete="CASCADE"), index=True
    )
    title: Mapped[str] = mapped_column(String(512))
    summary: Mapped[str | None] = mapped_column(Text)
    thumb_key: Mapped[str | None] = mapped_column(String(512))
    child_count: Mapped[int | None] = mapped_column(Integer)
    smart: Mapped[bool] = mapped_column(Boolean, default=False, server_default="false")
    editable: Mapped[bool] = mapped_column(Boolean, default=False, server_default="false")
    synced_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))


class PlexState(Base, UpdatedAtMixin):
    """Per-user watch state for a Plex item — mirrors VideoState. Lives in Siftlode's own DB so
    it works for users WITHOUT a Plex account (the whole point of the access-layer design).
    `synced_to_plex` marks whether this state was pushed back to a linked Plex account."""

    __tablename__ = "plex_states"
    __table_args__ = (UniqueConstraint("user_id", "item_id", name="uq_plex_user_item"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"))
    item_id: Mapped[int] = mapped_column(
        ForeignKey("plex_items.id", ondelete="CASCADE"), index=True
    )
    status: Mapped[str] = mapped_column(String(12), default="new", server_default="new")
    position_seconds: Mapped[int] = mapped_column(
        Integer, default=0, server_default="0", nullable=False
    )
    watched_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    progress_updated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    synced_to_plex: Mapped[bool] = mapped_column(Boolean, default=False, server_default="false")


class PlexLink(Base, UpdatedAtMixin):
    """Links a Siftlode user to a Plex account for two-way watch-state sync (one row per user).

    For the owner (the MVP scope) the row uses the server's admin token — `uses_admin=True`,
    `plex_token_enc=None` — so no separate Plex login is needed; the admin token already reads/writes
    the owner account's watch state. `plex_account_id` is that Plex account's numeric id, used to
    filter the shared watch-history feed to this user (Phase C). Reserved for later: a friend can link
    their OWN Plex account via PIN OAuth, storing a personal `plex_token_enc` (uses_admin=False) — the
    sync loop then iterates link rows uniformly.

    `initial_import_done` guards the one-time "Plex is master" import; `last_watch_sync_at` is the
    high-water mark for the incremental Plex→Siftlode pass."""

    __tablename__ = "plex_link"

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"), unique=True, index=True
    )
    uses_admin: Mapped[bool] = mapped_column(Boolean, default=True, server_default="true")
    plex_token_enc: Mapped[str | None] = mapped_column(Text)  # P5b friend tokens (encrypted)
    plex_account_id: Mapped[int | None] = mapped_column(Integer)
    plex_username: Mapped[str | None] = mapped_column(String(255))
    sync_enabled: Mapped[bool] = mapped_column(Boolean, default=False, server_default="false")
    initial_import_done: Mapped[bool] = mapped_column(
        Boolean, default=False, server_default="false"
    )
    last_watch_sync_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    linked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))


class PlexPlaylist(Base, TimestampMixin, UpdatedAtMixin):
    """A per-user ORDERED watch-list of Plex items — Siftlode-native (lives in our own DB, works for
    users WITHOUT a Plex account, like plex_states). Unlike collections (shared, library-level), a
    playlist is owned by one user and its items are ordered. Optional Plex-direction sync is a later
    phase (`plex_rating_key` set once pushed back to the owner's Plex account)."""

    __tablename__ = "plex_playlists"

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), index=True)
    title: Mapped[str] = mapped_column(String(512))
    plex_rating_key: Mapped[str | None] = mapped_column(String(32))


class PlexPlaylistItem(Base):
    """One ordered entry in a playlist. `position` (0-based) orders playback."""

    __tablename__ = "plex_playlist_items"
    __table_args__ = (UniqueConstraint("playlist_id", "item_id", name="uq_plex_playlist_item"),)

    id: Mapped[int] = mapped_column(primary_key=True)
    playlist_id: Mapped[int] = mapped_column(ForeignKey("plex_playlists.id", ondelete="CASCADE"))
    item_id: Mapped[int] = mapped_column(
        ForeignKey("plex_items.id", ondelete="CASCADE"), index=True
    )
    position: Mapped[int] = mapped_column(Integer, default=0, server_default="0", nullable=False)