"""成就徽章 — re-hyphenate gamification_unlocks UUIDs to the platform convention.

The 319 model declared ``id`` / ``person_id`` as a bare ``Mapped[uuid.UUID]``,
which resolves to SQLAlchemy's stock ``Uuid`` and binds the DASH-STRIPPED
32-char form on MySQL/TiDB — so every existing row stored the stripped form
while the column is CHAR(36) and every other table (identity_persons, …)
stores hyphenated. Reads/writes were self-consistent (the badge wall worked),
but a cross-table string join ``... ON p.id = g.person_id`` matched 0 rows.

The model now declares the explicit ``GUID`` (= hyphenated CHAR(36)) type, so
it will bind/compare hyphenated and would no longer match the old stripped
rows. This migration re-hyphenates any 32-char no-dash ``id`` / ``person_id``
into the canonical 8-4-4-4-12 form so existing unlocks keep resolving.

Prod verification (2026-06-28, read-only): 695 rows, all stored 32-char
stripped; join-as-stored matched 0, join-after-rehyphenation matched all 695.

Data-only — no DDL. The column is already CHAR(36) utf8mb4_unicode_ci (created
by 319 via ``id_type_for("identity_persons")``), so no FK/charset change; no FK
constraints reference this table. Idempotent: only touches stripped rows, and a
re-run finds none. The unique key (person_id, badge_code) is preserved — the
stripped→hyphenated map is 1:1 so re-hyphenation introduces no collisions.

Revision ID: 353
Revises: 352
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "353"
down_revision = "352"
branch_labels = None
depends_on = None

TABLE = "gamification_unlocks"

# 32-char hex -> 8-4-4-4-12 hyphenated.
_HYPHENATE = (
    "CONCAT("
    "SUBSTR({col},1,8),'-',SUBSTR({col},9,4),'-',"
    "SUBSTR({col},13,4),'-',SUBSTR({col},17,4),'-',SUBSTR({col},21))"
)


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()
    if not _table_exists(bind, TABLE):
        return
    for col in ("id", "person_id"):
        bind.execute(
            sa.text(
                f"UPDATE {TABLE} SET {col} = {_HYPHENATE.format(col=col)} "
                f"WHERE LENGTH({col}) = 32 AND {col} NOT LIKE '%-%'"
            )
        )


def downgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, TABLE):
        return
    for col in ("id", "person_id"):
        bind.execute(
            sa.text(
                f"UPDATE {TABLE} SET {col} = REPLACE({col}, '-', '') "
                f"WHERE LENGTH({col}) = 36 AND {col} LIKE '%-%'"
            )
        )