"""Add ``deleted_at`` to ``google_orphan_accounts`` for sync-driven
soft-delete of upstream-deleted Google accounts.

Owner-reported 2026-05-07: ``shuai@pingcap.com`` was deleted from
Google Workspace; ``google.seat_sync`` no longer fetches it; the
orphan row's ``last_seen_at`` froze (last value: yesterday) but the
admin UI kept rendering it as "启用" because the page only reflects
``suspended`` (Google-side flag) and not staleness. Result: deleted
upstream accounts pile up indefinitely in the "Google 共用账号" page.

The original design comment on ``google_orphan_accounts`` said the UI
would surface ``stale > 7 days`` as a distinct status — that half of
the contract never landed in the frontend. Rather than wire a UI-only
"stale" computation (which is fragile across timezones and depends on
the admin's clock), we land a deterministic backend signal:

  * sync stamps ``deleted_at = now()`` for any orphan whose
    ``last_seen_at < now() - GRACE_DAYS`` (default 3 — survives
    weekend outages and one-off sync failures, fast enough that
    admins don't see deleted-upstream rows for more than ~72h)
  * list endpoints filter ``deleted_at IS NULL`` by default; an
    explicit ``include_deleted=true`` admin toggle exposes the full
    history (audit / "what disappeared and when")
  * we never hard-delete — owner assignments and audit history are
    preserved for the admin to inspect

Schema-only — no data backfill. Existing rows have
``deleted_at = NULL``, which means "not (yet) detected as deleted
upstream". The next ``google.seat_sync`` tick performs the first
soft-delete pass and stamps any row whose ``last_seen_at`` is already
older than the grace window.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "129"
down_revision = "128"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column(
        "google_orphan_accounts",
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
    )
    # Partial-style index — most queries filter ``deleted_at IS NULL``,
    # which on MySQL/TiDB benefits from an index even though "IS NULL"
    # uses the full index scan path. Keeps small-table-default OK,
    # critical when prod orphan count crosses ~1k rows.
    op.create_index(
        "ix_google_orphan_accounts_deleted_at",
        "google_orphan_accounts",
        ["deleted_at"],
    )


def downgrade() -> None:
    op.drop_index(
        "ix_google_orphan_accounts_deleted_at",
        table_name="google_orphan_accounts",
    )
    op.drop_column("google_orphan_accounts", "deleted_at")