"""Add chapter_status and sort_order columns to key_moments.

Revision ID: 020_add_chapter_status_and_sort_order
Revises: 019_add_highlight_candidates
"""

from alembic import op
import sqlalchemy as sa


revision = "020_add_chapter_status_and_sort_order"
down_revision = "019_add_highlight_candidates"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # Create the chapter_status enum type
    chapter_status = sa.Enum("draft", "approved", "hidden", name="chapter_status", create_constraint=True)
    chapter_status.create(op.get_bind(), checkfirst=True)

    # Use create_type=False to prevent double-creation during add_column
    ch_col_type = sa.Enum("draft", "approved", "hidden", name="chapter_status", create_type=False)

    op.add_column(
        "key_moments",
        sa.Column("chapter_status", ch_col_type, nullable=False, server_default="draft"),
    )
    op.add_column(
        "key_moments",
        sa.Column("sort_order", sa.Integer, nullable=False, server_default="0"),
    )
    op.create_index("ix_key_moments_chapter_status", "key_moments", ["chapter_status"])


def downgrade() -> None:
    op.drop_index("ix_key_moments_chapter_status")
    op.drop_column("key_moments", "sort_order")
    op.drop_column("key_moments", "chapter_status")
    sa.Enum(name="chapter_status").drop(op.get_bind(), checkfirst=True)
