dotfiles/python/orm/richie/audiobook.py

"""Audiobook catalog models."""

from __future__ import annotations

from sqlalchemy import ForeignKey, String, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship

from python.orm.richie.base import TableBase


class AudiobookAuthor(TableBase):
    """Canonical audiobook author."""

    __tablename__ = "audiobook_author"
    __table_args__ = (UniqueConstraint("name"),)

    name: Mapped[str] = mapped_column(String, unique=True)

    books: Mapped[list[Audiobook]] = relationship("Audiobook", back_populates="author")
    series: Mapped[list[AudiobookSeries]] = relationship("AudiobookSeries", back_populates="author")


class AudiobookSeries(TableBase):
    """Canonical audiobook series."""

    __tablename__ = "audiobook_series"
    __table_args__ = (UniqueConstraint("author_id", "name"),)

    name: Mapped[str] = mapped_column(String)
    author_id: Mapped[int] = mapped_column(ForeignKey("main.audiobook_author.id", ondelete="CASCADE"))

    author: Mapped[AudiobookAuthor] = relationship("AudiobookAuthor", back_populates="series")
    books: Mapped[list[Audiobook]] = relationship("Audiobook", back_populates="series")


class Audiobook(TableBase):
    """Canonical audiobook title."""

    __tablename__ = "audiobook"
    __table_args__ = (
        UniqueConstraint(
            "author_id",
            "series_id",
            "title",
            postgresql_nulls_not_distinct=True,
        ),
    )

    title: Mapped[str] = mapped_column(String)
    author_id: Mapped[int] = mapped_column(ForeignKey("main.audiobook_author.id", ondelete="CASCADE"))
    series_id: Mapped[int | None] = mapped_column(ForeignKey("main.audiobook_series.id", ondelete="SET NULL"))
    series_index: Mapped[float] = mapped_column(default=0.0)

    author: Mapped[AudiobookAuthor] = relationship("AudiobookAuthor", back_populates="books")
    series: Mapped[AudiobookSeries | None] = relationship("AudiobookSeries", back_populates="books")