"""vpn_applications — Feilian Full-Mode VPN access flow.

The Feishu template (flow_key=vpn_application_request, external_code
configured per-integration) asks for three fields:
    - VPN Node (single choice: CN4-HK / CN5-US)
    - Usage Description (textarea)
    - Resource Review Checklist (textarea — overseas site/IP details)

Routes through the standard approval pipeline. On approval a row
lands here binding the requester to the granted node; admin can later
revoke. VPN access is per-person, so unlike sso_grants /
domain_registrations there is no leaver handover.

Revision ID: 105
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op


revision = "105"
down_revision = "104"
branch_labels = None
depends_on = None


def _fk_source_col_type(bind, target_table: str, target_column: str, *, length: int = 36):
    """See migrations 098 / 101 / 102 — mirror the FK target's
    DATA_TYPE + COLLATION so TiDB / MySQL 8 don't reject the
    constraint with error 3780."""
    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(
        "vpn_applications",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("person_id", persons_id, nullable=False),
        # Mirrors the Feishu single-choice options. We persist the
        # short token (CN4-HK / CN5-US) rather than the friendly
        # display string so a future rename of the option label
        # doesn't orphan past records.
        sa.Column("vpn_node", sa.String(32), nullable=False),
        sa.Column("usage_description", sa.Text, nullable=False),
        sa.Column("resource_checklist", sa.Text, nullable=False),
        # State machine:
        #   pending → approved → revoked (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),
        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_vpn_applications_person",
        ),
        sa.ForeignKeyConstraint(
            ["approval_request_id"], ["approval_requests.id"],
            name="fk_vpn_applications_approval_request",
            ondelete="SET NULL",
        ),
        sa.Index("ix_vpn_applications_person", "person_id", "status"),
        sa.Index("ix_vpn_applications_node", "vpn_node", "status"),
        sa.Index(
            "ix_vpn_applications_feishu_instance", "feishu_instance_code",
            unique=True,
            mysql_length={"feishu_instance_code": 64},
        ),
    )


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