"""series chapters become cosmetic dividers; pages become one series-global run

FC-6.x reframe (#789). A series is now ONE flat, series-global ordered run of
pages; chapters stop owning pages and become labeled dividers anchored to the
page that begins them.

Migration (order matters — series_page.chapter_id cascades, so it must be
dropped BEFORE any chapter row is deleted, or pages would cascade away):
  a. Renumber series_page.page_number to a series-global 1..N (ordered by the
     OLD (chapter_number, page_number)).
  b. Add series_chapter.anchor_page_id and populate it with each chapter's first
     page (lowest new page_number).
  c. Drop series_page.chapter_id (severs the cascade link).
  d. Prune chapters that shouldn't become dividers: empty/placeholder ones (no
     anchor) and the redundant unlabeled chapter that would sit at page 1.
  e. Reshape series_chapter into the divider: drop chapter_number,
     is_placeholder, stated_page_start/end; make anchor_page_id NOT NULL +
     UNIQUE + FK→series_page ON DELETE CASCADE.

Revision ID: 0047
Revises: 0046
Create Date: 2026-06-11

"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "0047"
down_revision: Union[str, None] = "0046"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # a. series-global page numbering, preserving the old reading order.
    op.execute(
        """
        WITH ordered AS (
            SELECT sp.id,
                   ROW_NUMBER() OVER (
                       PARTITION BY sp.series_tag_id
                       ORDER BY sc.chapter_number, sp.page_number, sp.id
                   ) AS rn
            FROM series_page sp
            JOIN series_chapter sc ON sc.id = sp.chapter_id
        )
        UPDATE series_page sp
        SET page_number = ordered.rn
        FROM ordered
        WHERE sp.id = ordered.id
        """
    )

    # b. anchor each existing chapter at its first page (lowest new page_number).
    op.add_column(
        "series_chapter",
        sa.Column("anchor_page_id", sa.Integer(), nullable=True),
    )
    op.execute(
        """
        WITH firsts AS (
            SELECT DISTINCT ON (sp.chapter_id)
                   sp.chapter_id, sp.id AS page_id
            FROM series_page sp
            ORDER BY sp.chapter_id, sp.page_number, sp.id
        )
        UPDATE series_chapter sc
        SET anchor_page_id = firsts.page_id
        FROM firsts
        WHERE firsts.chapter_id = sc.id
        """
    )

    # c. sever the ownership link (drops the FK + index with the column) BEFORE
    #    pruning chapters, so deleting a chapter can't cascade-delete its pages.
    op.drop_column("series_page", "chapter_id")

    # d. prune chapters that don't become dividers: placeholders / empty ones
    #    (no anchor), and the unlabeled chapter that would land redundantly at
    #    page 1 (the series just starts — no divider needed there).
    op.execute(
        """
        DELETE FROM series_chapter sc
        USING (
            SELECT sc2.id
            FROM series_chapter sc2
            LEFT JOIN series_page sp ON sp.id = sc2.anchor_page_id
            WHERE sc2.anchor_page_id IS NULL
               OR (sp.page_number = 1
                   AND sc2.title IS NULL
                   AND sc2.stated_part IS NULL)
        ) gone
        WHERE sc.id = gone.id
        """
    )

    # e. reshape into the divider model.
    op.drop_column("series_chapter", "chapter_number")
    op.drop_column("series_chapter", "is_placeholder")
    op.drop_column("series_chapter", "stated_page_start")
    op.drop_column("series_chapter", "stated_page_end")
    op.alter_column("series_chapter", "anchor_page_id", nullable=False)
    op.create_unique_constraint(
        "uq_series_chapter_anchor_page", "series_chapter", ["anchor_page_id"]
    )
    op.create_foreign_key(
        "fk_series_chapter_anchor_page",
        "series_chapter",
        "series_page",
        ["anchor_page_id"],
        ["id"],
        ondelete="CASCADE",
    )


def downgrade() -> None:
    # Lossy: dividers can't be reconstructed as owning chapters. Collapse back to
    # exactly one chapter per series that owns all its pages in order.
    op.add_column(
        "series_page", sa.Column("chapter_id", sa.Integer(), nullable=True)
    )
    op.drop_constraint(
        "fk_series_chapter_anchor_page", "series_chapter", type_="foreignkey"
    )
    op.drop_constraint(
        "uq_series_chapter_anchor_page", "series_chapter", type_="unique"
    )
    op.drop_column("series_chapter", "anchor_page_id")
    op.add_column(
        "series_chapter",
        sa.Column(
            "chapter_number", sa.Integer(), nullable=False, server_default="1"
        ),
    )
    op.add_column(
        "series_chapter",
        sa.Column(
            "is_placeholder", sa.Boolean(), nullable=False,
            server_default="false",
        ),
    )
    op.add_column(
        "series_chapter",
        sa.Column("stated_page_start", sa.Integer(), nullable=True),
    )
    op.add_column(
        "series_chapter",
        sa.Column("stated_page_end", sa.Integer(), nullable=True),
    )
    op.execute("DELETE FROM series_chapter")
    op.execute(
        """
        INSERT INTO series_chapter (series_tag_id, chapter_number)
        SELECT DISTINCT series_tag_id, 1 FROM series_page
        """
    )
    op.execute(
        """
        UPDATE series_page sp
        SET chapter_id = sc.id
        FROM series_chapter sc
        WHERE sc.series_tag_id = sp.series_tag_id
        """
    )
    op.alter_column("series_page", "chapter_id", nullable=False)
    op.create_foreign_key(
        "fk_series_page_chapter",
        "series_page",
        "series_chapter",
        ["chapter_id"],
        ["id"],
        ondelete="CASCADE",
    )