"""Feed celebrations: language-agnostic system posts + a reply channel.

Three columns, all additive and nullable, backing the celebration closed loop:

``feed_posts.body_i18n_key`` / ``body_params``
    System posts (birthday / anniversary / the aggregate banner) stored their
    body as **rendered English prose** — a 2026-06-01 decision to skip
    bilingual storage. The frontend already carries all three translations and
    a correct fallback, but the fallback is dead code because the server always
    supplies a non-empty ``body``, so a Chinese reader saw
    ``1 teammate celebrating their work anniversary today!`` sitting in an
    otherwise-Chinese feed. Storing the key + params instead lets every reader
    see the post in their own language, and keeps re-rendering correct if they
    later switch languages. ``body`` stays populated as the fallback for
    clients that don't know the new fields, and as the search haystack.

``feed_blessings.acked_at``
    The celebrant had no way to respond to a blessing. Only the celebrant can
    acknowledge, and only once per blessing, so this is a nullable timestamp on
    the blessing itself rather than a reactions table — a second table would
    model a many-to-many that cannot exist.

Backfill: ``body_i18n_key`` is NOT backfilled — inventing i18n params for
historical rows whose ``celebration_id`` may since have been deleted would be
guesswork, and those posts are day-scoped content nobody re-reads.

``body_params["occasion_date"]`` IS backfilled for aggregate banners, because
that one is not cosmetic: ``hydrate_posts`` falls back to inferring the day
from ``created_at`` (UTC) when the field is absent, and that inference is wrong
by a day for every banner the cron created between 00:00 and 07:59 Asia/
Shanghai — which is when it runs. Without the backfill each surviving banner
keeps rendering with an empty avatar row and no "view" affordance for the rest
of its life in the feed. The banner's own SHA date is exactly what
``CONVERT_TZ(created_at, '+00:00', '+08:00')`` recovers.

Revision ID: 456
Revises: 455
"""
from __future__ import annotations

import logging

import sqlalchemy as sa

from alembic import op

revision = "456"
down_revision = "455"
branch_labels = None
depends_on = None

logger = logging.getLogger("alembic.runtime.migration")


def _has_column(bind, table: str, column: str) -> bool:
    return bool(bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.columns "
            "WHERE table_schema = DATABASE() AND table_name = :t "
            "AND column_name = :c"
        ),
        {"t": table, "c": column},
    ).scalar())


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

    if not _has_column(bind, "feed_posts", "body_i18n_key"):
        op.add_column(
            "feed_posts",
            sa.Column("body_i18n_key", sa.String(length=64), nullable=True),
        )
        logger.info("[456] feed_posts.body_i18n_key added")
    if not _has_column(bind, "feed_posts", "body_params"):
        op.add_column(
            "feed_posts", sa.Column("body_params", sa.JSON(), nullable=True),
        )
        logger.info("[456] feed_posts.body_params added")
    if not _has_column(bind, "feed_blessings", "acked_at"):
        op.add_column(
            "feed_blessings",
            sa.Column("acked_at", sa.DateTime(timezone=True), nullable=True),
        )
        logger.info("[456] feed_blessings.acked_at added")

    # Recover each existing banner's Asia/Shanghai day so the celebrant lookup
    # stops missing (see the module docstring). Scoped to aggregate banners
    # only; single-celebrant posts resolve their celebrant by FK and never used
    # the date inference.
    result = bind.execute(sa.text(
        "UPDATE feed_posts "
        "SET body_params = JSON_OBJECT("
        "  'occasion_date', "
        "  DATE_FORMAT(CONVERT_TZ(created_at, '+00:00', '+08:00'), '%Y-%m-%d')"
        ") "
        "WHERE post_type IN ('aggregate_birthday', 'aggregate_anniversary') "
        "  AND body_params IS NULL"
    ))
    logger.info("[456] aggregate banners backfilled with occasion_date: %s",
                result.rowcount)


def downgrade() -> None:
    bind = op.get_bind()
    for table, column in (
        ("feed_blessings", "acked_at"),
        ("feed_posts", "body_params"),
        ("feed_posts", "body_i18n_key"),
    ):
        if _has_column(bind, table, column):
            op.drop_column(table, column)