"""series chapters: chapter layer over series_page (FC-6.1)

Revision ID: 0040
Revises: 0039
Create Date: 2026-06-07

A series (Tag kind='series') gains an ordered chapter layer. Reading order
becomes (series_chapter.chapter_number, series_page.page_number). Every existing
series is backfilled into a single auto-chapter (chapter_number=1) holding its
current flat pages, so no data is lost and the old flat ordering is preserved.
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "0040"
down_revision: Union[str, None] = "0039"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        "series_chapter",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column(
            "series_tag_id",
            sa.Integer,
            sa.ForeignKey("tag.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("chapter_number", sa.Integer, nullable=False),
        sa.Column("title", sa.Text, nullable=True),
        sa.Column(
            "is_placeholder", sa.Boolean, nullable=False, server_default="false"
        ),
        sa.Column("stated_page_start", sa.Integer, nullable=True),
        sa.Column("stated_page_end", sa.Integer, nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
    )
    op.create_index(
        "ix_series_chapter_series_tag_id", "series_chapter", ["series_tag_id"]
    )

    # New columns on series_page; chapter_id starts nullable so we can backfill.
    op.add_column(
        "series_page", sa.Column("chapter_id", sa.Integer, nullable=True)
    )
    op.add_column(
        "series_page", sa.Column("stated_page", sa.Integer, nullable=True)
    )

    conn = op.get_bind()
    # One auto-chapter per existing series (any series_tag_id present in pages).
    conn.execute(
        sa.text(
            "INSERT INTO series_chapter "
            "(series_tag_id, chapter_number, is_placeholder, created_at, updated_at) "
            "SELECT DISTINCT series_tag_id, 1, false, now(), now() "
            "FROM series_page"
        )
    )
    # Point every existing page at its series' auto-chapter.
    conn.execute(
        sa.text(
            "UPDATE series_page sp "
            "SET chapter_id = sc.id "
            "FROM series_chapter sc "
            "WHERE sc.series_tag_id = sp.series_tag_id"
        )
    )

    # Now lock chapter_id down: NOT NULL + FK (cascade) + index.
    op.alter_column("series_page", "chapter_id", nullable=False)
    op.create_foreign_key(
        "fk_series_page_chapter_id",
        "series_page",
        "series_chapter",
        ["chapter_id"],
        ["id"],
        ondelete="CASCADE",
    )
    op.create_index(
        "ix_series_page_chapter_id", "series_page", ["chapter_id"]
    )


def downgrade() -> None:
    op.drop_index("ix_series_page_chapter_id", table_name="series_page")
    op.drop_constraint(
        "fk_series_page_chapter_id", "series_page", type_="foreignkey"
    )
    op.drop_column("series_page", "stated_page")
    op.drop_column("series_page", "chapter_id")
    op.drop_index("ix_series_chapter_series_tag_id", table_name="series_chapter")
    op.drop_table("series_chapter")