"""Normalize ``auth_roles.id`` to dashed UUID format.

Revision ID: 092
Revises: 091
Create Date: 2026-04-30

Migrations 043 (hr + finance seed) and 080 (cloud_admin revival) inserted
their auth_roles rows with ``REPLACE(UUID(), '-', '')`` — i.e. a 32-char
hex string with no hyphens. SQLAlchemy's ``GUID`` type, however, ALWAYS
binds parameters using the dashed canonical form (``str(uuid.UUID(...))``).

The mismatch breaks role assignment with a foreign-key error:

    INSERT INTO auth_user_roles (user_id, role_id, granted_by) ...
    -> 1452 Cannot add or update a child row: a foreign key constraint
       fails (`auth_user_roles_ibfk_2` FOREIGN KEY (`role_id`)
       REFERENCES `auth_roles` (`id`))

because ``role_id`` is the read-back UUID with dashes added in by
``process_result_value``, which doesn't string-equal the stored undashed
parent row.

This migration converts every undashed (LENGTH=32) auth_roles.id to the
dashed canonical form. Existing FKs in ``auth_user_roles`` and
``auth_role_permissions`` are updated in lock-step via the sibling
UPDATE statements to keep them aligned (cheap to do unconditionally —
the WHERE clauses no-op when the id is already dashed).

Idempotent: re-running is a no-op because the WHERE filters only catch
the 32-char form.
"""
from __future__ import annotations

from alembic import op

revision = "092"
down_revision = "091"
branch_labels = None
depends_on = None


def upgrade() -> None:
    bind = op.get_bind()

    # FK is enforced row-by-row in MySQL/TiDB, so updating either side
    # first leaves a transient mismatch the FK rejects. Disable checks
    # for the duration of the parent+children rename, then re-enable.
    # (In practice the affected role rows have zero user_role children
    # — the FK error this migration fixes is exactly what prevented
    # anyone from creating those assignments — but the toggle makes
    # the migration safe even on a DB where someone hand-inserted a
    # row before this fix landed.)
    bind.exec_driver_sql("SET FOREIGN_KEY_CHECKS = 0")

    # 1. Update child FK columns first so the dashed-vs-undashed
    #    mismatch is invisible across both tables once we re-enable
    #    the check below.
    bind.exec_driver_sql(
        """
        UPDATE auth_user_roles
        SET role_id = CONCAT(
            SUBSTRING(role_id, 1, 8), '-',
            SUBSTRING(role_id, 9, 4), '-',
            SUBSTRING(role_id, 13, 4), '-',
            SUBSTRING(role_id, 17, 4), '-',
            SUBSTRING(role_id, 21, 12)
        )
        WHERE LENGTH(role_id) = 32 AND role_id NOT LIKE '%%-%%'
        """
    )
    bind.exec_driver_sql(
        """
        UPDATE auth_role_permissions
        SET role_id = CONCAT(
            SUBSTRING(role_id, 1, 8), '-',
            SUBSTRING(role_id, 9, 4), '-',
            SUBSTRING(role_id, 13, 4), '-',
            SUBSTRING(role_id, 17, 4), '-',
            SUBSTRING(role_id, 21, 12)
        )
        WHERE LENGTH(role_id) = 32 AND role_id NOT LIKE '%%-%%'
        """
    )

    # 2. Update the parent row.
    bind.exec_driver_sql(
        """
        UPDATE auth_roles
        SET id = CONCAT(
            SUBSTRING(id, 1, 8), '-',
            SUBSTRING(id, 9, 4), '-',
            SUBSTRING(id, 13, 4), '-',
            SUBSTRING(id, 17, 4), '-',
            SUBSTRING(id, 21, 12)
        )
        WHERE LENGTH(id) = 32 AND id NOT LIKE '%%-%%'
        """
    )

    bind.exec_driver_sql("SET FOREIGN_KEY_CHECKS = 1")


def downgrade() -> None:
    # Intentional no-op. Reverting would strip dashes from EVERY
    # 36-char auth_roles.id (including ``it_admin``, which was
    # already correctly dashed before this migration) and re-break
    # role assignment. The forward fix is the only correct shape;
    # if you really need to undo it, do so manually with a
    # SELECT-and-targeted-UPDATE that only touches the rows
    # migration 043/080 originally seeded.
    pass