"""Seed the ``core.idempotency_cleanup`` scheduled job.

Revision ID: 394
Revises: 393
Create Date: 2026-07-08

The ``core_idempotency_records`` table grows one row per Idempotency-Key'd
request (asset-transfer / offboarding / Leaver-Blocked routers). Keys are
unique per operation, so the lazy expired-row reclaim in
``IdempotencyService.lookup`` (fires only on an exact key re-hit) essentially
never runs — the table grows without bound, degrading the
``SELECT ... FOR UPDATE`` on every future idempotent request.

The docstring on ``idempotency.cleanup_expired`` had long claimed a
``core_idempotency_cleanup`` scheduled handler deleted expired rows, but no
such handler or job row ever existed (backend-review-batch-15, 2026-07-08).
This seeds the daily job; the handler ``run_core_idempotency_cleanup`` lives in
:mod:`app.scheduled_jobs.handlers` and now deletes in bounded batches
(``batch_size``, default 500) so a first run against a months-old backlog can't
trip TiDB's "transaction too large".

Idempotent — the INSERT is gated on a name-based NOT EXISTS check.
"""
from __future__ import annotations

import uuid

import sqlalchemy as sa

from alembic import op

revision = "394"
down_revision = "393"
branch_labels = None
depends_on = None


def _table_exists(bind, table: str) -> bool:
    row = bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t"
        ),
        {"t": table},
    ).fetchone()
    return row is not None


def upgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, "scheduled_jobs"):
        return
    bind.execute(
        sa.text(
            "INSERT INTO scheduled_jobs "
            "(id, name, display_name, description, handler_key, "
            "trigger_type, interval_seconds, timezone, enabled, config, "
            "run_count, failure_count, created_at, updated_at) "
            "SELECT :id, :name, :display, :desc, :handler, 'interval', "
            ":interval, 'Asia/Shanghai', 1, :config, 0, 0, NOW(), NOW() "
            "FROM DUAL WHERE NOT EXISTS "
            "(SELECT 1 FROM scheduled_jobs WHERE name = :name)"
        ),
        {
            "id": str(uuid.uuid4()),
            "name": "core.idempotency_cleanup",
            "display": "幂等记录清理",
            "desc": (
                "每日删除已过 TTL 的 core_idempotency_records 行。key 唯一,"
                "惰性回收几乎不触发,需 cron 清理,否则表无界增长拖慢每次幂等"
                "预留 SELECT ... FOR UPDATE。分批删(batch_size 默认 500)避免 "
                "TiDB 单条超大事务。handler=run_core_idempotency_cleanup。"
            ),
            "handler": "core.idempotency_cleanup",
            "interval": 86400,
            "config": "{}",
        },
    )


def downgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, "scheduled_jobs"):
        return
    bind.execute(
        sa.text(
            "DELETE FROM scheduled_jobs "
            "WHERE handler_key = 'core.idempotency_cleanup'"
        )
    )