"""制度公告 → 制度中心:摘要/关键字 + 发布/轮巡双开关。 Revision ID: 271 Revises: 270 Create Date: 2026-06-09 (Renumbered 270 → 271 during merge — `270_identity_manager_external_id.py` took 270 first on main.) Why this exists --------------- ``platform_policy_notices`` started life as a thin header ribbon: a bilingual title + Feishu URL + a single ``enabled`` flag that meant both "visible" AND "rotating in the banner". This migration grows it into a searchable **制度中心** (policy library) every employee can browse: * ``summary_zh`` / ``summary_en`` — bilingual one-paragraph summary shown on the library card (NULL on legacy rows; the card falls back to the title). * ``keywords`` — a shared JSON tag array (NOT per-language); the library search matches a query token against title + summary + keywords so a zh OR en term finds the policy. * ``is_published`` — employee can see / search it in the library. * ``pin_to_banner`` — ALSO rotates in the header ribbon. The banner is a curated *subset* of the library: the service layer enforces ``pin_to_banner ⟹ is_published``. The old single ``enabled`` is split into the two flags above and then dropped (no dead column). Backfill preserves today's behaviour exactly: an ``enabled=1`` row was both visible and rotating, so it becomes ``is_published=1, pin_to_banner=1``; ``enabled=0`` → both 0. **TiDB gotcha (the reason this file was patched post-merge):** migration 254 put a composite index ``ix_platform_policy_notices_enabled_order`` on ``(enabled, display_order)``. MySQL 8 silently rewrites such an index when you drop a covered column, but TiDB REFUSES (``ERROR 8200: can't drop column ... with composite index covered``). So we drop that index explicitly BEFORE dropping ``enabled``, then add a replacement index ``ix_platform_policy_notices_pub_order`` on ``(is_published, display_order)`` for the new read pattern. Idempotency ----------- Every step is guarded by ``_column_exists`` / ``_index_exists`` so a half-applied / re-run migration converges (the first prod attempt added the columns + backfilled, then failed on the column drop — this version picks up exactly that state and finishes). Backfill only runs while the legacy ``enabled`` column is still present. """ from __future__ import annotations import sqlalchemy as sa from alembic import op revision = "271" down_revision = "270" branch_labels = None depends_on = None _TABLE = "platform_policy_notices" # The composite index migration 254 put on (enabled, display_order) for # the banner read. TiDB REFUSES to drop a column an index still covers # (`ERROR 8200: can't drop column ... with composite index covered`) — # unlike MySQL 8, which silently rewrites the index. So we must drop this # index explicitly BEFORE dropping `enabled`. _OLD_INDEX = "ix_platform_policy_notices_enabled_order" # Replacement index matching the new read pattern (library/banner both # filter is_published then order by display_order). _NEW_INDEX = "ix_platform_policy_notices_pub_order" def _column_exists(table: str, column: str) -> bool: conn = op.get_bind() result = conn.execute( sa.text( "SELECT COUNT(*) FROM information_schema.columns " "WHERE table_schema = DATABASE() " "AND table_name = :t AND column_name = :c" ), {"t": table, "c": column}, ) return (result.scalar() or 0) > 0 def _index_exists(table: str, index: str) -> bool: conn = op.get_bind() result = conn.execute( sa.text( "SELECT COUNT(*) FROM information_schema.statistics " "WHERE table_schema = DATABASE() " "AND table_name = :t AND index_name = :i" ), {"t": table, "i": index}, ) return (result.scalar() or 0) > 0 def upgrade() -> None: if not _column_exists(_TABLE, "summary_zh"): op.add_column( _TABLE, sa.Column("summary_zh", sa.String(length=500), nullable=True), ) if not _column_exists(_TABLE, "summary_en"): op.add_column( _TABLE, sa.Column("summary_en", sa.String(length=500), nullable=True), ) if not _column_exists(_TABLE, "keywords"): # JSON array of shared tags (language-agnostic). Nullable so # legacy rows read as "no keywords"; the app coerces NULL → []. op.add_column(_TABLE, sa.Column("keywords", sa.JSON(), nullable=True)) if not _column_exists(_TABLE, "is_published"): op.add_column( _TABLE, sa.Column( "is_published", sa.Boolean(), nullable=False, server_default="1", ), ) if not _column_exists(_TABLE, "pin_to_banner"): op.add_column( _TABLE, sa.Column( "pin_to_banner", sa.Boolean(), nullable=False, server_default="0", ), ) # Backfill from the legacy single flag while it still exists: an # enabled notice was both visible AND rotating, so map it to both. if _column_exists(_TABLE, "enabled"): op.execute( sa.text( f"UPDATE {_TABLE} " "SET is_published = enabled, pin_to_banner = enabled" ) ) # MUST drop the covering index before the column — TiDB rejects # the column drop otherwise (8200). Idempotent. if _index_exists(_TABLE, _OLD_INDEX): op.drop_index(_OLD_INDEX, table_name=_TABLE) op.drop_column(_TABLE, "enabled") # Replacement index for the new read pattern (filter is_published, # order by display_order). Created after the column drop so it never # references the dropped `enabled`. if not _index_exists(_TABLE, _NEW_INDEX): op.create_index( _NEW_INDEX, _TABLE, ["is_published", "display_order"], ) def downgrade() -> None: # Drop the replacement index first (it covers is_published, which we # are about to drop). if _index_exists(_TABLE, _NEW_INDEX): op.drop_index(_NEW_INDEX, table_name=_TABLE) # Recreate the single flag from is_published (the wider of the two) # so a downgraded banner still shows everything that was visible. if not _column_exists(_TABLE, "enabled"): op.add_column( _TABLE, sa.Column( "enabled", sa.Boolean(), nullable=False, server_default="1", ), ) op.execute(sa.text(f"UPDATE {_TABLE} SET enabled = is_published")) # Restore the original (enabled, display_order) index. if not _index_exists(_TABLE, _OLD_INDEX): op.create_index( _OLD_INDEX, _TABLE, ["enabled", "display_order"], ) for col in ("pin_to_banner", "is_published", "keywords", "summary_en", "summary_zh"): if _column_exists(_TABLE, col): op.drop_column(_TABLE, col)