"""Move DNS-request execution off the Feishu webhook onto two crons.

Adds ``cmdb_dns_record_requests.execution_attempts`` and seeds:

  * ``cmdb.dns_request_executor`` — every minute, drains rows the approval
    callback left at ``approved`` and performs the provider write in the
    worker. The callback no longer writes to the cloud inline: it commits
    ``approved`` and returns, so Feishu (which redelivers after ~3s) stops
    racing a multi-second DNSPod call.
  * ``cmdb.dns_request_execution_reaper`` — every 10 minutes, sweeps rows
    stranded at ``executing``. ``create`` is reconciled against the live
    provider before anything else so an interrupted-but-landed write is
    completed instead of duplicated; other actions are idempotent and get
    re-armed. ``execution_attempts`` bounds that loop — past
    ``max_attempts`` the row lands in ``failed`` with a resumable message.

Background: request 85c908bf (2026-08-14) was approved in Feishu, rejected
by DNSPod for TTL<600, and then stranded at ``executing`` because the
fail-path itself raised — leaving the requester at 处理中 with no audit
trail, no notification and no admin action available.

Revision ID: 469
Revises: 468
"""
import uuid

import sqlalchemy as sa

from alembic import op

revision = "469"
down_revision = "468"
branch_labels = None
depends_on = None

EXECUTOR_JOB = "cmdb.dns_request_executor"
REAPER_JOB = "cmdb.dns_request_execution_reaper"


def _seed_job(
    bind,
    *,
    name: str,
    display_name: str,
    description: str,
    cron_expr: str,
    config: str,
) -> None:
    exists = bind.execute(
        sa.text("SELECT 1 FROM scheduled_jobs WHERE name = :n LIMIT 1"),
        {"n": name},
    ).scalar()
    if exists:
        return
    bind.execute(
        sa.text(
            """
            INSERT INTO scheduled_jobs
                (id, name, display_name, description, handler_key,
                 trigger_type, interval_seconds, cron_expr, timezone,
                 enabled, config, run_count, failure_count,
                 created_at, updated_at)
            VALUES
                (:id, :name, :display_name, :description, :name,
                 'cron', NULL, :cron_expr, 'UTC',
                 1, :config, 0, 0, NOW(), NOW())
            """
        ),
        {
            "id": str(uuid.uuid4()),
            "name": name,
            "display_name": display_name,
            "description": description,
            "cron_expr": cron_expr,
            "config": config,
        },
    )


def upgrade() -> None:
    bind = op.get_bind()
    op.add_column(
        "cmdb_dns_record_requests",
        sa.Column(
            "execution_attempts",
            sa.Integer(),
            nullable=False,
            server_default="0",
        ),
    )
    # Rows already at executing when this ships have been through exactly
    # one execution attempt. Leaving them at 0 would give them a free extra
    # re-arm; stamping 1 keeps the attempt budget honest.
    bind.execute(
        sa.text(
            "UPDATE cmdb_dns_record_requests SET execution_attempts = 1 "
            "WHERE status = 'executing'"
        )
    )
    _seed_job(
        bind,
        name=EXECUTOR_JOB,
        display_name="DNS 申请执行器",
        description=(
            "每分钟扫描 status='approved' 的 DNS 解析申请并执行云侧写入"
            "(腾讯云 DNSPod / Route53 等),成功置 executed,provider 报错"
            "置 failed 并回写飞书评论。审批回调本身不再做云写,只落"
            " approved 后立即返回,避免飞书 3 秒超时重投以及请求被中断"
            "导致行卡在 executing。config: batch_limit(默认 50)。"
        ),
        cron_expr="* * * * *",
        config='{"batch_limit": 50}',
    )
    _seed_job(
        bind,
        name=REAPER_JOB,
        display_name="DNS 申请执行卡死回收",
        description=(
            "每 10 分钟扫描 status='executing' 且超过 stale_minutes"
            "(默认 10)未更新的 DNS 申请。create 先对账云端:记录已存在"
            "说明写入其实成功,补齐 Helix 台账并置 executed(绝不重复"
            "创建);其余动作幂等,回退 approved 交执行器重跑。超过"
            " max_attempts(默认 3)则置 failed 并写入可 resume 的错误"
            "信息,管理员点「重跑」即可,无需重新发起审批。"
            "config: stale_minutes、max_attempts、batch_limit。"
        ),
        cron_expr="*/10 * * * *",
        config='{"stale_minutes": 10, "max_attempts": 3, "batch_limit": 50}',
    )


def downgrade() -> None:
    bind = op.get_bind()
    bind.execute(
        sa.text("DELETE FROM scheduled_jobs WHERE name IN (:a, :b)"),
        {"a": EXECUTOR_JOB, "b": REAPER_JOB},
    )
    op.drop_column("cmdb_dns_record_requests", "execution_attempts")