"""Widen provider-native ID columns from VARCHAR(128) → VARCHAR(512).

Revision ID: 062
Revises: 061
Create Date: 2026-04-24

Context: 2026-04-24 07:00 Azure role catalog sync crashed with
``Data too long for column 'role_key' at row 1`` — Azure returns
role definitions as ~138-char ARM paths. The previous ``cmdb.servers``
sync crashed the same way on ``instance_id`` (fixed via ``vmId``
normalization). Rather than ship column-by-column normalization for
every provider's native id shape, widen ALL columns that hold a
provider-native identifier to 512 chars — that's 2× the worst real-
world ARM path I've seen and future-proofs against longer-than-
expected AWS ARNs, GCP full resource names, and Aliyun resource ids.

Columns widened to ``VARCHAR(512)``:

* ``cmdb_servers.instance_id`` — currently Azure emits ``vmId`` (36
  chars) via the 2026-04-24 fix, but AWS ARNs can push past 128 in
  IAM-tag-backed schemes. Widen as defense in depth.
* ``cmdb_dns_records.record_id`` — drivers compose
  ``{zone_id}::{type}::{value}``; Azure zone_id alone is ~150.
* ``cmdb_load_balancers.lb_id`` — Azure LB ARM ids are ~220 chars;
  AWS ELBv2 ARNs are ~150.
* ``cmdb_iam_users.user_id_external`` — Aliyun RAM User UID / Azure
  AAD object id variants; kept short today but widening is free.
* ``cmdb_cloud_scopes.external_id`` — Azure sub GUID is 36 but
  future GCP ``folders/<id>/<id>`` style scope ids could be long.
* ``cmdb_cloud_roles.role_key`` — Azure subscription-prefixed role
  definition paths are ~138 chars.
* ``cmdb_cloud_access_grants.role_key`` — same.

All ALTERs are idempotent (checked via information_schema) so a
re-run on an already-migrated DB is a no-op. TiDB 7.x supports
``MODIFY COLUMN`` online without table rebuild for VARCHAR growth
staying within the same type family.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "062"
down_revision = "061"
branch_labels = None
depends_on = None


_WIDEN = [
    # (table, column, null_spec, target_length)
    # record_id capped at 400 because the unique index
    # ``uq_cmdb_dns_records(integration_id, zone_name, record_id)``
    # hits MySQL's 3072-byte utf8mb4 key limit at (36+253+X)*4 → X ≤ 479.
    ("cmdb_servers", "instance_id", "NOT NULL", 512),
    ("cmdb_dns_records", "record_id", "NOT NULL", 400),
    ("cmdb_load_balancers", "lb_id", "NOT NULL", 512),
    ("cmdb_iam_users", "user_id_external", "NULL", 512),
    ("cmdb_cloud_scopes", "external_id", "NOT NULL", 512),
    ("cmdb_cloud_roles", "role_key", "NOT NULL", 512),
    ("cmdb_cloud_access_grants", "role_key", "NOT NULL", 512),
]


def _current_length(bind, table: str, column: str) -> int | None:
    """Return the current ``CHARACTER_MAXIMUM_LENGTH`` or None if the
    column doesn't exist. Lets us skip tables that were never created
    on a given deployment (e.g. cmdb_iam_users on older backups)."""
    return bind.execute(
        sa.text(
            "SELECT CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "AND TABLE_NAME = :t AND COLUMN_NAME = :c"
        ),
        {"t": table, "c": column},
    ).scalar()


def _modify(bind, table: str, column: str, length: int, null_spec: str) -> None:
    bind.execute(
        sa.text(
            f"ALTER TABLE `{table}` "
            f"MODIFY COLUMN `{column}` VARCHAR({length}) {null_spec}"
        )
    )


def upgrade() -> None:
    bind = op.get_bind()
    for table, column, null_spec, target in _WIDEN:
        cur = _current_length(bind, table, column)
        if cur is None:
            continue  # table absent — nothing to widen
        if int(cur) >= target:
            continue  # already at or beyond the target
        _modify(bind, table, column, target, null_spec)


def downgrade() -> None:
    bind = op.get_bind()
    # Narrowing risks truncating legitimately-long values that were
    # inserted after 062. Only narrow rows that still fit in 128
    # chars — refuse (raise) otherwise so an admin can intervene.
    for table, column, null_spec, _target in _WIDEN:
        cur = _current_length(bind, table, column)
        if cur is None:
            continue
        if int(cur) <= 128:
            continue
        too_long = bind.execute(
            sa.text(
                f"SELECT COUNT(*) FROM `{table}` WHERE "
                f"CHAR_LENGTH(`{column}`) > 128"
            )
        ).scalar() or 0
        if int(too_long) > 0:
            raise RuntimeError(
                f"downgrade blocked: {too_long} row(s) in {table}.{column} "
                "exceed 128 chars; truncating would lose data. Resolve "
                "them manually or keep the widened column."
            )
        _modify(bind, table, column, 128, null_spec)