"""Round-22 (2026-05-13) — finish the migration-148 notes cleanup
that the single-line regex missed.

Revision ID: 156
Revises: 155
Create Date: 2026-05-13

Why:

Migration 148 introduced ``offboarding_blockers.last_error_message`` and
backfilled it from the legacy ``[T+0 自动转让失败] ...`` lines that the
pre-round-13 force-transfer path used to append to ``notes``. The strip
regex was ``\\n?\\[T\\+0 自动转让失败\\][^\\n]*`` — a SINGLE-LINE
pattern. That worked for short error markers like
``PendingRollbackError: previous tx rolled back`` (one line), but the
historical IntegrityError dumps spanned multiple lines:

    [T+0 自动转让失败] IntegrityError: (asyncmy.errors.IntegrityError) (1048, "Column 'asset_label' cannot be null")
    [SQL: INSERT INTO asset_transfer_requests (id, ...) VALUES (%s, ...)]
    [parameters: ('uuid', 'uuid', 'uuid', 'hardware', 'uuid', None, ...)]
    (Background on this error at: https://sqlalche.me/e/20/gkpj) ...

Migration 148 stripped only the FIRST line; the continuation lines
(``[SQL: ...]``, ``[parameters: ...]``, ``(Background on this error
at: ...)``) stayed in ``notes``. On 2026-05-13 the admin "需要你处理"
panel rendered those residual blocks as a wall of orange SQL stack
trace under every legacy blocker (huohao@/frank.shen@'s 14
``cloud_access_grant`` rows in the round-21 prod incident screenshot).

The post-round-13 code no longer writes these continuation lines into
``notes`` (errors go to ``last_error_message`` via the structured
column). So this is a pure historical-data cleanup: strip the residual
continuation blocks from ``notes`` for every row that still carries
one.

Idempotency: the predicate filters rows that contain at least one of
the three continuation markers. Successive runs are no-ops because the
strip removes all three.

Safety: we ONLY touch rows whose continuation markers are sandwiched
between offboarding-system text (the legacy append pattern is always a
trailing block at the end of ``notes`` after the human-written prose).
Any blocker whose ``notes`` contains a legitimate user comment with the
substring ``[SQL:`` or ``[parameters:`` is theoretically at risk — but
those substrings are SQL-stack-trace tokens that no human-written note
realistically carries. Worst case for a false-positive strip is loss of
a comment line that started with one of those literals. The trade-off
is acceptable given the admin-UX cost of the current pollution.

Done in Python rather than via ``REGEXP_REPLACE`` for the same
portability reason migration 148 stayed in Python: TiDB and MySQL 8.0
diverge on multiline-aware regex flag semantics, and the row count is
small (offboarding_blockers is a few thousand rows max).
"""
from __future__ import annotations

import re

import sqlalchemy as sa

from alembic import op

# revision identifiers, used by Alembic.
revision = "156"
down_revision = "155"
branch_labels = None
depends_on = None


# Matches a continuation line from the legacy asyncmy/SQLAlchemy
# IntegrityError dump that migration 148's single-line strip missed.
# Each pattern is anchored with an optional leading ``\n`` so removing
# the line doesn't leave an orphan blank line behind.
#
# The four continuation forms observed in prod (huohao@'s blockers):
#
#   1. ``[SQL: INSERT INTO ...]``         (SQL statement preview)
#   2. ``[parameters: (...)]``            (bound-parameter values)
#   3. ``(Background on this error at: https://sqlalche.me/...)``
#                                          (SQLAlchemy doc link)
#   4. Any leftover whitespace-only line  (whitespace trim)
#
# Order matters: strip the long-form blocks first, then collapse any
# remaining trailing whitespace at the end of notes.
_CONTINUATION_PATTERNS: tuple[re.Pattern[str], ...] = (
    # ``[SQL: ...]`` — content is bracket-delimited but the bracket can
    # appear inside the SQL itself (rare). Stop at end-of-line; the SQL
    # asyncmy emits is always single-line.
    re.compile(r"\n?\[SQL:[^\n]*"),
    # ``[parameters: ...]`` — same pattern; bound-parameter dumps are
    # single-line repr() output from asyncmy.
    re.compile(r"\n?\[parameters:[^\n]*"),
    # ``(Background on this error at: <url>) (Background on this error at: <url>)``
    # — SQLAlchemy's documentation-link continuation. May appear once
    # per chained exception; greedy match consumes the whole rest of
    # the line, multiple ``(Background...)`` tokens included.
    re.compile(r"\n?\(Background on this error at:[^\n]*"),
)


def upgrade() -> None:
    conn = op.get_bind()
    rows = conn.execute(
        sa.text(
            "SELECT id, notes "
            "FROM offboarding_blockers "
            "WHERE notes LIKE '%[SQL:%' "
            "   OR notes LIKE '%[parameters:%' "
            "   OR notes LIKE '%(Background on this error at:%'"
        )
    ).fetchall()

    BATCH_SIZE = 500  # noqa: N806 — migration-local constant, matches 148.
    update_stmt = sa.text(
        "UPDATE offboarding_blockers SET notes = :n WHERE id = :id"
    )
    pending: list[dict[str, object]] = []
    for row in rows:
        notes_value = row.notes or ""
        cleaned = notes_value
        for pattern in _CONTINUATION_PATTERNS:
            cleaned = pattern.sub("", cleaned)
        cleaned = cleaned.rstrip() or None
        if cleaned == notes_value:
            # Defensive: predicate matched but no pattern fired (e.g.
            # the substring sits inside an unrelated context). Skip.
            continue
        pending.append({"n": cleaned, "id": row.id})
        if len(pending) >= BATCH_SIZE:
            conn.execute(update_stmt, pending)
            pending.clear()
    if pending:
        conn.execute(update_stmt, pending)


def downgrade() -> None:
    # No reversible operation — the stripped continuation lines are
    # gone permanently. Downgrade is a no-op so ``alembic downgrade``
    # doesn't fail on this revision; the row schema is unchanged so
    # there's nothing to undo at the structural level either.
    pass