"""Identity consumers — machine credentials for the outbound PULL surface.

Three tables backing the Directory API / SCIM server credential layer:

* ``identity_consumers``            registered external systems + grants
* ``identity_consumer_tokens``      helix_svc_ credentials (hash only)
* ``identity_consumer_usage_daily`` per-(consumer, day) request counter

All person references FK to ``identity_persons``; tables are created
with the parent's charset/collation so the inline FKs can't trip
error 3780 on prod TiDB.
"""
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 = "277"
down_revision = "276"
branch_labels = None
depends_on = None

CONSUMERS = "identity_consumers"
TOKENS = "identity_consumer_tokens"
USAGE = "identity_consumer_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()
    persons_id = id_type_for("identity_persons")
    kw = table_kw_for("identity_persons")

    if not _table_exists(bind, CONSUMERS):
        op.create_table(
            CONSUMERS,
            sa.Column("id", persons_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", persons_id, nullable=False),
            sa.Column(
                "status", sa.String(16), nullable=False,
                server_default="active",
            ),
            sa.Column("scopes", sa.JSON(), nullable=False),
            sa.Column("filter_config", sa.JSON(), nullable=False),
            sa.Column("rate_limit_per_minute", sa.Integer(), nullable=True),
            sa.Column("created_by", persons_id, nullable=True),
            sa.Column(
                "created_at", sa.DateTime(timezone=True), nullable=False,
                server_default=sa.func.now(),
            ),
            sa.Column(
                "updated_at", sa.DateTime(timezone=True), nullable=False,
                server_default=sa.func.now(),
            ),
            sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
            sa.ForeignKeyConstraint(
                ["owner_person_id"], ["identity_persons.id"],
                name="fk_identity_consumers_owner",
            ),
            sa.ForeignKeyConstraint(
                ["created_by"], ["identity_persons.id"],
                name="fk_identity_consumers_created_by",
            ),
            sa.UniqueConstraint("slug", name="uq_identity_consumers_slug"),
            **kw,
        )
        op.create_index(
            "idx_identity_consumers_status", CONSUMERS, ["status"],
        )

    if not _table_exists(bind, TOKENS):
        op.create_table(
            TOKENS,
            sa.Column("id", persons_id, primary_key=True),
            sa.Column("consumer_id", persons_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", persons_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", persons_id, nullable=True),
            sa.Column(
                "created_at", sa.DateTime(timezone=True), nullable=False,
                server_default=sa.func.now(),
            ),
            sa.ForeignKeyConstraint(
                ["consumer_id"], [f"{CONSUMERS}.id"],
                name="fk_identity_consumer_tokens_consumer", ondelete="CASCADE",
            ),
            sa.ForeignKeyConstraint(
                ["revoked_by_user_id"], ["identity_persons.id"],
                name="fk_identity_consumer_tokens_revoker",
            ),
            sa.ForeignKeyConstraint(
                ["created_by"], ["identity_persons.id"],
                name="fk_identity_consumer_tokens_creator",
            ),
            sa.UniqueConstraint(
                "token_hash", name="uq_identity_consumer_tokens_hash",
            ),
            **kw,
        )
        op.create_index(
            "idx_identity_consumer_tokens_consumer", TOKENS, ["consumer_id"],
        )

    if not _table_exists(bind, USAGE):
        op.create_table(
            USAGE,
            sa.Column("id", persons_id, primary_key=True),
            sa.Column("consumer_id", persons_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.ForeignKeyConstraint(
                ["consumer_id"], [f"{CONSUMERS}.id"],
                name="fk_identity_consumer_usage_consumer", ondelete="CASCADE",
            ),
            sa.UniqueConstraint(
                "consumer_id", "usage_date",
                name="uq_identity_consumer_usage_day",
            ),
            **kw,
        )


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