"""Seed the cron.cleanup_stale_running scheduled job row.

Revision ID: 114
Revises: 113
Create Date: 2026-05-03

Registers the ``cron.cleanup_stale_running`` handler as a recurring
job. Without this, any scheduled job that crashes mid-run (DB blip,
worker SIGTERM mid-update, etc.) leaves a ``status='running'`` row
in ``cmdb_sync_jobs`` and ``scheduled_job_runs`` forever — which the
sync engine's JSON_OVERLAPS lock then treats as "another sync is
already running" and silently skips every subsequent invocation.

Runs hourly (long enough that real long-running jobs aren't
spuriously failed; short enough that an orphan is cleaned within
the hour). The handler default ``stale_after_hours`` is 6h — an
ample buffer over the longest legitimate job (full Aliyun-Intl
multi-account sync ≈ 30 min worst case).

Idempotent: re-running this migration on a DB that already has the
row is a no-op.
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision = "114"
down_revision = "113"
branch_labels = None
depends_on = None

_JOB_NAME = "cron.cleanup_stale_running"


def upgrade() -> None:
    bind = op.get_bind()
    exists = bind.execute(
        sa.text("SELECT 1 FROM scheduled_jobs WHERE name = :n LIMIT 1"),
        {"n": _JOB_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
            (UUID(), :n, :dn, :desc, :hk, 'interval',
             :ivl, NULL, 'UTC', 1, JSON_OBJECT('stale_after_hours', 6),
             0, 0, NOW(), NOW())
        """,
    ), {
        "n": _JOB_NAME,
        "dn": "清理僵死的运行中行",
        "desc": (
            "每小时扫一次 cmdb_sync_jobs / scheduled_job_runs 中"
            "status='running' 但 started_at > 6h 的行,标记为 failed。"
            "防止 worker 半路死掉后 'running' 行永久阻塞后续 sync。"
        ),
        "hk": _JOB_NAME,
        "ivl": 3600,  # hourly
    })


def downgrade() -> None:
    op.get_bind().execute(
        sa.text("DELETE FROM scheduled_jobs WHERE name = :n"),
        {"n": _JOB_NAME},
    )