"""Feed edit history — feed_posts / feed_comments edit metadata.

Revision ID: 238
Revises: 237
Create Date: 2026-06-02

Renumbered 237 → 238 after rebase 2026-06-02: PR #656
(``237_cancel_duplicate_orphan_revoke_tasks``) landed on main first.

Adds two columns each to ``feed_posts`` and ``feed_comments``:

* ``last_edited_at`` (DATETIME, nullable) — populated by the PATCH
  endpoint when the author rewrites the body. Separate from
  ``updated_at`` (which auto-bumps on any column change, including
  internal counter increments like ``like_count`` / ``comment_count``)
  so the FE "已修改" tag never lights up on a like.
* ``edit_count`` (INT, default 0, NOT NULL) — counter of how many
  times the body has been edited. Surfaced to admins via the audit log
  (action ``feed.post.edit`` / ``feed.comment.edit``); the FE just
  shows a binary "已修改" badge.

The actual before/after body history lives in the audit log
(``audit_logs.detail.prev_body`` / ``new_body``) — the feed table
itself only carries the count + last-edit timestamp so admins can
spot recent edits at a glance.

Idempotent: every ``ADD COLUMN`` is guarded with information_schema.
Downgrade drops the four columns.
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision = "238"
down_revision = "237"
branch_labels = None
depends_on = None


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 upgrade() -> None:
    if not _column_exists("feed_posts", "last_edited_at"):
        op.add_column(
            "feed_posts",
            sa.Column(
                "last_edited_at", sa.DateTime(timezone=True), nullable=True,
            ),
        )
    if not _column_exists("feed_posts", "edit_count"):
        op.add_column(
            "feed_posts",
            sa.Column(
                "edit_count", sa.Integer(),
                nullable=False, server_default="0",
            ),
        )
    if not _column_exists("feed_comments", "last_edited_at"):
        op.add_column(
            "feed_comments",
            sa.Column(
                "last_edited_at", sa.DateTime(timezone=True), nullable=True,
            ),
        )
    if not _column_exists("feed_comments", "edit_count"):
        op.add_column(
            "feed_comments",
            sa.Column(
                "edit_count", sa.Integer(),
                nullable=False, server_default="0",
            ),
        )
    if not _column_exists("feed_comments", "updated_at"):
        # ``feed_comments`` was originally shipped without an
        # ``updated_at`` column (migration 235). Adding it now so the
        # edit endpoint can rely on standard touch semantics. The
        # server_default + server_onupdate keep behaviour consistent
        # with feed_posts.
        op.add_column(
            "feed_comments",
            sa.Column(
                "updated_at",
                sa.DateTime(timezone=True),
                nullable=False,
                server_default=sa.func.now(),
                server_onupdate=sa.func.now(),
            ),
        )


def downgrade() -> None:
    if _column_exists("feed_comments", "updated_at"):
        op.drop_column("feed_comments", "updated_at")
    if _column_exists("feed_comments", "edit_count"):
        op.drop_column("feed_comments", "edit_count")
    if _column_exists("feed_comments", "last_edited_at"):
        op.drop_column("feed_comments", "last_edited_at")
    if _column_exists("feed_posts", "edit_count"):
        op.drop_column("feed_posts", "edit_count")
    if _column_exists("feed_posts", "last_edited_at"):
        op.drop_column("feed_posts", "last_edited_at")