"""Per-client person-population visibility for the External API.

Adds ``api_clients.person_visibility`` (JSON, nullable) holding a person
filter in the SAME shape the identity-consumer pull surface already uses
(``app.core.person_filters.FilterConfig``) — departments / countries /
employment_statuses / person_ids / mode / …:

    {"departments": ["<org-node-source-id>"], "include_subtree": true}

``departments`` holds HRIS **org-node source ids** — the value
``identity_org_memberships.org_node_source_id`` carries — NOT Helix row
UUIDs. The filter engine compares them as exact strings, so the wrong
kind of identifier yields a ceiling that matches nobody; the governance
layer existence-checks them on save so that cannot ship silently.

``NULL`` = unrestricted, which is what every existing client has, so the
migration is additive and changes no live grant.

Why: this mirrors ``cloud_visibility`` (migration 470) one domain over. A
scope (``people.github:read``) says what KIND of people data a machine
client may read, never WHICH people. That distinction was avoidable while
the only HR-ish surface was the leave lookup, which is lookup-only (you
must name the people, so knowing who to ask about IS the boundary). The
GitHub mapping endpoint is the first External API surface that LISTS
people — "give me everyone under Engineering" — so without a population
ceiling one leaked token would enumerate the whole company roster.

Deliberately reuses the consumer filter vocabulary rather than inventing a
second one: the same engine evaluates it, the same admin editor edits it,
and the two egress planes cannot drift apart on what "population" means.

Revision ID: 513
Revises: 512
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "513"
down_revision = "512"
branch_labels = None
depends_on = None

_TABLE = "api_clients"
_COLUMN = "person_visibility"


def _column_exists(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 LIMIT 1"
        ),
        {"t": table, "c": column},
    ).scalar() is not None


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


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