"""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:
    # Pure SQL to avoid SQLAlchemy enum creation hooks
    op.execute("DO $$ BEGIN CREATE TYPE chapter_status AS ENUM ('draft', 'approved', 'hidden'); EXCEPTION WHEN duplicate_object THEN NULL; END $$")
    op.execute("ALTER TABLE key_moments ADD COLUMN IF NOT EXISTS chapter_status chapter_status NOT NULL DEFAULT 'draft'")
    op.execute("ALTER TABLE key_moments ADD COLUMN IF NOT EXISTS sort_order INTEGER NOT NULL DEFAULT 0")
    op.execute("CREATE INDEX IF NOT EXISTS ix_key_moments_chapter_status ON 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)
