"""sso_grants — generic 'I need access to system X via SSO' flow.

The form has four fields (SSO provider · System · System owner · Reason)
and routes through Feishu approval (flow_key=sso_grant_request,
external_code configured per-integration). On approval a row lands here
binding the requester to the system; on the requester's offboarding
the leaver flow scans these rows and demands a handover before
revoking access.

Revision ID: 101
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op


revision = "101"
down_revision = "100"
branch_labels = None
depends_on = None


def _fk_source_col_type(bind, target_table: str, target_column: str, *, length: int = 36):
    """Mirror the FK target's DATA_TYPE + COLLATION so TiDB / MySQL 8
    don't reject the constraint with error 3780. Same helper migration
    098 introduced — duplicated inline so each migration stays
    self-contained."""
    row = bind.execute(
        sa.text(
            "SELECT DATA_TYPE, COLLATION_NAME FROM "
            "information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "  AND TABLE_NAME = :t AND COLUMN_NAME = :c"
        ),
        {"t": target_table, "c": target_column},
    ).fetchone()
    data_type = (row[0] or "char").lower() if row else "char"
    collation = row[1] if row else None
    type_cls = sa.CHAR if data_type == "char" else sa.String
    return type_cls(length, collation=collation) if collation else type_cls(length)


def upgrade() -> None:
    bind = op.get_bind()
    persons_id = _fk_source_col_type(bind, "identity_persons", "id")
    approval_requests_id = _fk_source_col_type(bind, "approval_requests", "id")
    op.create_table(
        "sso_grants",
        sa.Column("id", sa.String(36), primary_key=True),
        # Who got the access. Mirrors ApprovalRequest.applicant_id and
        # is the field the leaver flow scans by.
        sa.Column("person_id", persons_id, nullable=False),
        # 'Google' / 'Lark' / 'Authing' — limited to the Feishu
        # template's options today, but stored as varchar so an admin
        # adding a fourth provider doesn't need a migration.
        sa.Column("sso_provider", sa.String(64), nullable=False),
        # Free-text system name as written by the requester. This is
        # what shows up to admins reviewing the grant register.
        sa.Column("system_name", sa.String(255), nullable=False),
        # System owner — captured as both label (always set, what we
        # display) and person FK (nullable, when we can resolve to a
        # Helix identity by email). Lets the leaver flow page out
        # "owner of system X" without re-resolving.
        sa.Column("system_owner_label", sa.String(255), nullable=False),
        sa.Column("system_owner_person_id", persons_id, nullable=True),
        sa.Column("reason", sa.Text, nullable=True),
        # State machine:
        #   pending → approved → revoked (on leaver handover or admin)
        #   pending → rejected / cancelled
        sa.Column("status", sa.String(32), nullable=False, server_default="pending"),
        sa.Column("feishu_instance_code", sa.String(64), nullable=True),
        sa.Column("approval_request_id", approval_requests_id, nullable=True),
        sa.Column("granted_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("revoked_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("revoke_reason", sa.String(255), nullable=True),
        # Leaver handover — when ``person_id`` is offboarded, IT picks
        # a successor; this row's ``person_id`` is rewritten to the
        # successor and the original is recorded here for audit.
        sa.Column("handover_from_person_id", persons_id, nullable=True),
        sa.Column("handover_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("handover_actor_id", persons_id, nullable=True),
        sa.Column(
            "created_at", sa.DateTime(timezone=True),
            server_default=sa.func.now(), nullable=False,
        ),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True),
            server_default=sa.func.now(), nullable=False,
        ),
        sa.ForeignKeyConstraint(
            ["person_id"], ["identity_persons.id"],
            name="fk_sso_grants_person",
        ),
        sa.ForeignKeyConstraint(
            ["system_owner_person_id"], ["identity_persons.id"],
            name="fk_sso_grants_system_owner_person",
            ondelete="SET NULL",
        ),
        sa.ForeignKeyConstraint(
            ["approval_request_id"], ["approval_requests.id"],
            name="fk_sso_grants_approval_request",
            ondelete="SET NULL",
        ),
        sa.ForeignKeyConstraint(
            ["handover_from_person_id"], ["identity_persons.id"],
            name="fk_sso_grants_handover_from_person",
            ondelete="SET NULL",
        ),
        sa.ForeignKeyConstraint(
            ["handover_actor_id"], ["identity_persons.id"],
            name="fk_sso_grants_handover_actor",
            ondelete="SET NULL",
        ),
        sa.Index("ix_sso_grants_person", "person_id", "status"),
        sa.Index("ix_sso_grants_provider_system", "sso_provider", "system_name"),
        sa.Index(
            "ix_sso_grants_feishu_instance", "feishu_instance_code",
            unique=True,
            mysql_length={"feishu_instance_code": 64},
        ),
    )


def downgrade() -> None:
    op.drop_table("sso_grants")