"""Persist Google-side aliases + direct member count on email_groups.

Revision ID: 166
Revises: 165
Create Date: 2026-05-14

Renumbered 163 → 165 → 166 during back-to-back merges with main: PR #301
(HR tools — annual-leave calculator) landed 163 + 164, and PR #303
(cmdb sync-to-sync baseline) landed 165 while this branch was in CI.
Same alembic head-conflict pattern as the 158 → 159 fast-follow on
2026-05-13.

The Google Admin Directory ``groups.list`` response already includes
``aliases``, ``nonEditableAliases`` and ``directMembersCount`` on every
group resource. Our ``run_google_group_sync`` handler was discarding all
three — the public mail-group directory therefore showed neither the
alternative addresses that route to a group (e.g. ``oncall@pingcap.com``
forwards to ``infra-oncall@pingcap.com``) nor "how big is this list?",
which is the single most useful disambiguating signal when an employee
is searching for the right group.

Columns added:

* ``aliases`` (JSON, nullable) — sorted unique list of every editable +
  non-editable alias email. Stored as a JSON array on TiDB / MySQL 8 so
  list-search via ``JSON_SEARCH`` / cast-to-text LIKE stays cheap.
* ``direct_members_count`` (INT, nullable) — number of direct members
  on the Google side (does NOT recursively count nested-group members,
  matching Google's own semantics).

Both columns are nullable: a freshly-created group row (before its first
sync tick) has neither alias nor a member-count opinion. ``NULL``
distinguishes "we haven't synced yet" from "synced, no aliases" (= `[]`)
so the UI can avoid showing a misleading "0 members" for unsynced rows.

Idempotent so a partial deploy can be re-run safely.
"""

from alembic import op
import sqlalchemy as sa


revision = "166"
down_revision = "165"
branch_labels = None
depends_on = None


def _column_exists(bind, table: str, column: str) -> bool:
    row = 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},
    ).fetchone()
    return row is not None


def upgrade() -> None:
    bind = op.get_bind()
    if not _column_exists(bind, "email_groups", "aliases"):
        op.add_column(
            "email_groups",
            sa.Column("aliases", sa.JSON(), nullable=True),
        )
    if not _column_exists(bind, "email_groups", "direct_members_count"):
        op.add_column(
            "email_groups",
            sa.Column("direct_members_count", sa.Integer(), nullable=True),
        )


def downgrade() -> None:
    op.drop_column("email_groups", "direct_members_count")
    op.drop_column("email_groups", "aliases")