"""External API clients — standalone scoped-access credential store.

Three tables for the External API feature (machine-to-machine access to
Helix business resources, distinct from the identity-consumer directory
pull): ``api_clients`` (registry), ``api_client_tokens`` (``helix_api_``
credentials, SHA-256 at rest), ``api_client_usage_daily`` (per-day
counter). All built with ``identity_persons``' charset/collation and
reference ``identity_persons`` / ``api_clients`` out-of-line so inline
FKs can't trip TiDB error 3780 on prod.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import id_type_for, table_kw_for

revision = "289"
down_revision = "288"
branch_labels = None
depends_on = None

CLIENTS = "api_clients"
TOKENS = "api_client_tokens"
USAGE = "api_client_usage_daily"


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()
    person_id = id_type_for("identity_persons")
    kw = table_kw_for("identity_persons")

    if not _table_exists(bind, CLIENTS):
        op.create_table(
            CLIENTS,
            sa.Column("id", person_id, primary_key=True),
            sa.Column("name", sa.String(128), nullable=False),
            sa.Column("slug", sa.String(64), nullable=False),
            sa.Column("description", sa.Text(), nullable=True),
            sa.Column("owner_person_id", person_id, nullable=False),
            sa.Column(
                "status", sa.String(16), nullable=False, server_default="active",
            ),
            sa.Column("scopes", sa.JSON(), nullable=False),
            sa.Column("rate_limit_per_minute", sa.Integer(), nullable=True),
            sa.Column("created_by", person_id, nullable=True),
            sa.Column(
                "created_at", sa.DateTime(timezone=True), nullable=False,
                server_default=sa.text("CURRENT_TIMESTAMP"),
            ),
            sa.Column(
                "updated_at", sa.DateTime(timezone=True), nullable=False,
                server_default=sa.text("CURRENT_TIMESTAMP"),
            ),
            sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
            sa.UniqueConstraint("slug", name="uq_api_clients_slug"),
            **kw,
        )
        op.create_index("idx_api_clients_status", CLIENTS, ["status"])
        op.create_foreign_key(
            "fk_api_clients_owner", CLIENTS,
            "identity_persons", ["owner_person_id"], ["id"],
        )
        op.create_foreign_key(
            "fk_api_clients_created_by", CLIENTS,
            "identity_persons", ["created_by"], ["id"],
        )

    if not _table_exists(bind, TOKENS):
        op.create_table(
            TOKENS,
            sa.Column("id", person_id, primary_key=True),
            sa.Column("client_id", person_id, nullable=False),
            sa.Column("name", sa.String(120), nullable=False),
            sa.Column("token_hash", sa.String(64), nullable=False),
            sa.Column("token_prefix", sa.String(16), nullable=False),
            sa.Column("expires_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("revoked_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("revoked_by_user_id", person_id, nullable=True),
            sa.Column("last_used_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("last_used_ip", sa.String(64), nullable=True),
            sa.Column("created_by", person_id, nullable=True),
            sa.Column(
                "created_at", sa.DateTime(timezone=True), nullable=False,
                server_default=sa.text("CURRENT_TIMESTAMP"),
            ),
            sa.UniqueConstraint("token_hash", name="uq_api_client_tokens_hash"),
            **kw,
        )
        op.create_index("idx_api_client_tokens_client", TOKENS, ["client_id"])
        op.create_foreign_key(
            "fk_api_client_tokens_client", TOKENS,
            CLIENTS, ["client_id"], ["id"], ondelete="CASCADE",
        )
        op.create_foreign_key(
            "fk_api_client_tokens_revoked_by", TOKENS,
            "identity_persons", ["revoked_by_user_id"], ["id"],
        )
        op.create_foreign_key(
            "fk_api_client_tokens_created_by", TOKENS,
            "identity_persons", ["created_by"], ["id"],
        )

    if not _table_exists(bind, USAGE):
        op.create_table(
            USAGE,
            sa.Column("id", person_id, primary_key=True),
            sa.Column("client_id", person_id, nullable=False),
            sa.Column("usage_date", sa.Date(), nullable=False),
            sa.Column(
                "request_count", sa.Integer(), nullable=False, server_default="0",
            ),
            sa.Column("last_request_at", sa.DateTime(timezone=True), nullable=True),
            sa.UniqueConstraint(
                "client_id", "usage_date", name="uq_api_client_usage_day",
            ),
            **kw,
        )
        op.create_foreign_key(
            "fk_api_client_usage_client", USAGE,
            CLIENTS, ["client_id"], ["id"], ondelete="CASCADE",
        )


def downgrade() -> None:
    bind = op.get_bind()
    for name in (USAGE, TOKENS, CLIENTS):
        if _table_exists(bind, name):
            op.drop_table(name)