"""SeriesPage — ordered image membership for a series-kind Tag.

A series IS a Tag with kind='series'; series_page gives it a SINGLE flat,
series-global ordered run of pages (FC-6.x divider reframe). An image belongs to
at most one series (UNIQUE image_id). Reading order is `page_number` alone — a
series-wide ordering key (not unique), rewritten 1..N wholesale on reorder so a
reorder can't transiently collide on an index.

Chapters are cosmetic DIVIDERS anchored to a page (see SeriesChapter); they do
NOT own pages, so there is no chapter_id here — a page's chapter is derived at
read time as the nearest preceding divider. stated_page carries the printed page
number parsed from the source post, nullable when unknown.
"""

from datetime import datetime

from sqlalchemy import DateTime, ForeignKey, Integer, String, func
from sqlalchemy.orm import Mapped, mapped_column

from .base import Base


class SeriesPage(Base):
    __tablename__ = "series_page"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    series_tag_id: Mapped[int] = mapped_column(
        ForeignKey("tag.id", ondelete="CASCADE"), nullable=False, index=True
    )
    image_id: Mapped[int] = mapped_column(
        ForeignKey("image_record.id", ondelete="CASCADE"),
        nullable=False,
        unique=True,
    )
    # 'placed' = in the series-global run (page_number set); 'pending' = staged
    # from a post awaiting the operator's sort (page_number NULL). (#789 P2)
    status: Mapped[str] = mapped_column(
        String(16), nullable=False, server_default="placed"
    )
    page_number: Mapped[int | None] = mapped_column(Integer, nullable=True)
    stated_page: Mapped[int | None] = mapped_column(Integer, nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(),
        onupdate=func.now(),
    )