"""Add extended fields to blog_posts

Revision ID: 0023
Revises: 0022
Create Date: 2026-06-26 00:00:00.000000
"""
from typing import Sequence, Union
import sqlalchemy as sa
from alembic import op

revision: str = "0023"
down_revision: Union[str, None] = "0022"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.add_column("blog_posts", sa.Column("slug", sa.String(600), nullable=True))
    op.add_column("blog_posts", sa.Column("cover_image_url", sa.Text(), nullable=True))
    op.add_column("blog_posts", sa.Column("author", sa.String(200), nullable=True))
    op.add_column("blog_posts", sa.Column("seo_title", sa.String(200), nullable=True))
    op.add_column("blog_posts", sa.Column("seo_description", sa.String(300), nullable=True))
    op.add_column("blog_posts", sa.Column("meta_keywords", sa.String(500), nullable=True))
    op.add_column("blog_posts", sa.Column("tags", sa.String(500), nullable=True))
    op.add_column("blog_posts", sa.Column("featured", sa.Boolean(), nullable=False, server_default="false"))
    op.add_column("blog_posts", sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True))

    # Back-fill slug for existing rows using raw SQL
    op.execute("""
        UPDATE blog_posts
        SET slug = REGEXP_REPLACE(LOWER(title), '[^a-z0-9]+', '-', 'g') || '-' || SUBSTRING(id::text, 1, 8)
        WHERE slug IS NULL
    """)

    # Enforce NOT NULL
    op.alter_column("blog_posts", "slug", nullable=False)

    # Create indexes
    op.create_index("ix_blog_posts_slug", "blog_posts", ["slug"], unique=True, postgresql_where=sa.text("deleted_at IS NULL"))
    op.create_index("ix_blog_posts_featured", "blog_posts", ["featured"], postgresql_where=sa.text("deleted_at IS NULL"))
    op.create_index("ix_blog_posts_deleted", "blog_posts", ["deleted_at"])


def downgrade() -> None:
    op.drop_index("ix_blog_posts_deleted", table_name="blog_posts")
    op.drop_index("ix_blog_posts_featured", table_name="blog_posts")
    op.drop_index("ix_blog_posts_slug", table_name="blog_posts")
    op.drop_column("blog_posts", "deleted_at")
    op.drop_column("blog_posts", "featured")
    op.drop_column("blog_posts", "tags")
    op.drop_column("blog_posts", "meta_keywords")
    op.drop_column("blog_posts", "seo_description")
    op.drop_column("blog_posts", "seo_title")
    op.drop_column("blog_posts", "author")
    op.drop_column("blog_posts", "cover_image_url")
    op.drop_column("blog_posts", "slug")