"""Add ``read_at`` to ``notification_outbox`` for per-row read tracking.

Why: until now the bell-icon "unread" badge was driven entirely from a
client-side ``localStorage`` timestamp (``helix:notif:lastRead:<userId>``)
that the frontend passed back as a ``?since=`` query param. That meant
every Ctrl-Shift-Delete, every new device, and every browser-profile
switch resurrected weeks of "unread" notifications. The user surfaced
this as a UX bug ("通知中心已读也是记在浏览器缓存的吗？这也太差了吧")
and asked for proper persistence.

Design choice — column on ``notification_outbox`` vs. separate
``notification_read_state`` table:

  * Each ``notification_outbox`` row already has exactly one recipient
    (``recipient_person_id``), so a per-row ``read_at`` column is a 1:1
    fit — no extra join, no per-user index to maintain.
  * A side table only earns its keep when one notification has many
    recipients. We don't have that; broadcast-to-many is implemented by
    fanning out to N rows in the service layer.
  * Indexed on ``(recipient_person_id, read_at)`` so both the unread
    count query and the "show only unread" filter run off the same
    composite scan.

Backward compat: the row default is ``NULL`` (= unread). On rollout,
every existing notification will appear unread to its recipient. We
accept that one-time noise rather than a heuristic backfill that
could quietly mark genuinely-unread messages as read.

History note: this migration's chain parent has churned five times
in 24h across PRs #102/#103/#104/#105/#106/#107/#109/#111. The final
shape settled by PR #112 (this fix) is:

```
084 → 086 (this migration) → 087 (bailian usage per account)
                          → 088 (asset inventory cycles)
```

The parent slot that was originally ``085_notification_outbox_read_at``
collided three times with parallel asset-inventory and Bailian
migrations; rather than continue the renumber tournament, this hotfix
re-points ``down_revision`` directly at ``084`` and removes the
intermediate 085 reference entirely. Functionally the migrations
touch disjoint tables, so only the chain shape matters, not order.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "086"
down_revision = "084"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column(
        "notification_outbox",
        sa.Column("read_at", sa.DateTime(timezone=True), nullable=True),
    )
    # Composite index — the inbox-summary query is
    #   WHERE recipient_person_id = :me AND read_at IS NULL
    # so an index on the leading column alone would still need a
    # secondary filter. The combined index lets MySQL/TiDB satisfy both
    # predicates from index data without touching the table heap.
    op.create_index(
        "ix_notification_outbox_recipient_read_at",
        "notification_outbox",
        ["recipient_person_id", "read_at"],
    )


def downgrade() -> None:
    op.drop_index(
        "ix_notification_outbox_recipient_read_at",
        table_name="notification_outbox",
    )
    op.drop_column("notification_outbox", "read_at")