"""MCP OAuth — auth_oauth_grants (per-user authorization records).

One durable row per (user, client) authorization. Backs the admin
"connected apps" governance view, the employee self-service
"my connections" list, and the revoke state both consult. The MCP
middleware + refresh grant treat a revoked grant (or a disabled client)
as immediate logout. No FK to identity_persons (TiDB-FK-safe). Idempotent.

Revision ID: 327
Revises: 326
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision = "327"
down_revision = "326"
branch_labels = None
depends_on = None

TABLE = "auth_oauth_grants"


def _table_exists(bind, name: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.tables "
            "WHERE table_schema = DATABASE() AND table_name = :n"
        ),
        {"n": name},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, TABLE):
        return
    op.create_table(
        TABLE,
        sa.Column("id", sa.CHAR(36), primary_key=True),
        sa.Column("user_id", sa.CHAR(36), nullable=False),
        sa.Column("client_id", sa.String(64), nullable=False),
        sa.Column(
            "granted_at", sa.DateTime(timezone=True),
            nullable=False, server_default=sa.func.now(),
        ),
        sa.Column("last_used_at", sa.DateTime(timezone=True)),
        sa.Column("revoked_at", sa.DateTime(timezone=True)),
        # 'user' | 'admin' — who revoked (NULL while active).
        sa.Column("revoked_by", sa.String(16)),
        sa.UniqueConstraint("user_id", "client_id", name="uq_oauth_grant_user_client"),
    )
    op.create_index("ix_oauth_grant_user", TABLE, ["user_id"])
    op.create_index("ix_oauth_grant_client", TABLE, ["client_id"])


def downgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, TABLE):
        op.drop_table(TABLE)