"""Add cc_emails to notification_outbox.

Revision ID: 409
Revises: 408
Create Date: 2026-07-16

Frozen JSON snapshot of an envelope's email CC list.

Motivation: the retry worker and the admin resend both rebuild the
``NotificationEnvelope`` FROM the persisted outbox row, and the row did
not store ``cc_emails`` — so a Day-1 welcome email whose first send
failed was retried/resent WITHOUT its HRBP + HR Ops CC (the CC guarantee
silently evaporated on every re-send path). Same precedent as the
``card_payload`` freeze (migration 400): persist at dispatch, restore on
retry AND resend.

Nullable JSON, sparse — populated only for envelopes that carry CCs
(NULL for the common no-CC notification), TiDB-safe (no FK).
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "409"
down_revision = "408"
branch_labels = None
depends_on = None

_TABLE = "notification_outbox"
_COL = "cc_emails"


def _has_column(bind, table: str, column: str) -> bool:
    return 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() is not None


def upgrade() -> None:
    bind = op.get_bind()
    if not _has_column(bind, _TABLE, _COL):
        op.add_column(_TABLE, sa.Column(_COL, sa.JSON(), nullable=True))


def downgrade() -> None:
    bind = op.get_bind()
    if _has_column(bind, _TABLE, _COL):
        op.drop_column(_TABLE, _COL)