"""news: add excerpt column; backfill and enforce slug NOT NULL; add partial unique indexes

Revision ID: 0037
Revises: 0036
Create Date: 2026-06-30

Changes:
  - news: add excerpt TEXT column (idempotent — skips if already present)
  - news: backfill NULL slugs from title + id[:8]
  - news: add unique partial index (tenant_id, slug) WHERE slug IS NOT NULL
  - news_subscriptions: add partial unique index (tenant_id, email) WHERE unsubscribed_at IS NULL
"""
import re

from alembic import op
import sqlalchemy as sa

revision = "0037"
down_revision = "0036"
branch_labels = None
depends_on = None


def _slugify(title: str, row_id: str) -> str:
    base = re.sub(r"[^a-z0-9]+", "-", title.lower()).strip("-")
    suffix = str(row_id)[:8]
    return f"{base}-{suffix}"


def upgrade() -> None:
    conn = op.get_bind()

    # 1. Add excerpt column — idempotent
    conn.execute(sa.text(
        "ALTER TABLE news ADD COLUMN IF NOT EXISTS excerpt TEXT"
    ))

    # 2. Backfill NULL slugs
    rows = conn.execute(
        sa.text("SELECT id, title FROM news WHERE slug IS NULL")
    ).fetchall()
    for row in rows:
        slug = _slugify(row.title, str(row.id))
        conn.execute(
            sa.text("UPDATE news SET slug = :slug WHERE id = :id"),
            {"slug": slug, "id": row.id},
        )

    # 3. Partial unique index on (tenant_id, slug) — idempotent
    conn.execute(sa.text("""
        CREATE UNIQUE INDEX IF NOT EXISTS ix_news_tenant_slug_unique
        ON news (tenant_id, slug)
        WHERE slug IS NOT NULL
    """))

    # 4. Partial unique index on news_subscriptions (tenant_id, email) — idempotent
    conn.execute(sa.text("""
        CREATE UNIQUE INDEX IF NOT EXISTS ix_news_subscriptions_tenant_email_active
        ON news_subscriptions (tenant_id, email)
        WHERE unsubscribed_at IS NULL
    """))


def downgrade() -> None:
    conn = op.get_bind()
    conn.execute(sa.text("DROP INDEX IF EXISTS ix_news_subscriptions_tenant_email_active"))
    conn.execute(sa.text("DROP INDEX IF EXISTS ix_news_tenant_slug_unique"))
    conn.execute(sa.text("ALTER TABLE news DROP COLUMN IF EXISTS excerpt"))