"""Re-seed the daily server-owner-confirmation reconcile scheduled job.

The original seed (`412_seed_server_owner_confirmation_reconcile_job`,
shipped in #1984) was silently deleted when arcade PR #1992 — branched
before #1984 merged — was squash-merged and its diff reverted the whole
#1984 changeset (service + handler + models + router + that migration).
This restores the fix on top of the current head with a fresh revision
(the old one claimed revision "412", which then collided with arcade's
`412_arcade_trivia_bank`).

Seeds ``cmdb.server_owner_confirmation_reconcile`` (daily 09:30
Asia/Shanghai). Pending half-yearly confirmation snapshots are frozen at
card time, but the fleet keeps moving — the 2026-07-14 incident had the
first real UCloud sync sweep (soft-delete + replace) every server 52
minutes after 18 owners received their cards, leaving them with pending
TODO badges for machines that no longer existed AND permanently blocking
their future cards via the re-prompt interlock. This job re-applies the
scan-time eligibility rules to every pending snapshot daily: drops dead /
stopped / re-assigned / archival servers, auto-resolves emptied rows, and
completes rows whose remaining servers were already owner-confirmed.

Idempotent: only inserts when the job row is absent, so it is a clean
no-op on the production DB that already seeded the row via the original
#1984 migration, and correctly seeds fresh / local / CI databases.

No schema change — ``auto_resolved`` is a new value in the existing
``cmdb_server_owner_confirmations.status`` string column.

Revision ID: 416
Revises: 415
"""
import uuid

import sqlalchemy as sa

from alembic import op

revision = "416"
down_revision = "415"
branch_labels = None
depends_on = None

JOB = "cmdb.server_owner_confirmation_reconcile"


def upgrade() -> None:
    bind = op.get_bind()
    job_exists = bind.execute(
        sa.text("SELECT 1 FROM scheduled_jobs WHERE name = :n LIMIT 1"),
        {"n": JOB},
    ).scalar()
    if not job_exists:
        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, '30 9 * * *', 'Asia/Shanghai',
                     1, '{}', 0, 0, NOW(), NOW())
                """
            ),
            {
                "id": str(uuid.uuid4()),
                "name": JOB,
                "display_name": "云服务器半年盘点待办对账",
                "description": (
                    "每日 09:30(Asia/Shanghai)校正未响应的半年盘点确认单:"
                    "剔除快照里此后已停机/下线/软删/换 owner/归档租户的机器,"
                    "快照清空则自动关单(auto_resolved),剩余机器均已确认的"
                    "按已响应收尾。防止死快照永久挂 owner 待办并阻断后续"
                    "盘点卡片(2026-07-14 UCloud 全量重同步事故)。"
                ),
            },
        )


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