"""Seed the weekly owner-facing cloud-efficiency digest cron.

Revision ID: 426
Revises: 425
Create Date: 2026-07-23

One org-wide ``scheduled_jobs`` row for ``cmdb.efficiency_owner_digest``:
each server owner / subscription 业主 with PENDING actionable
recommendations gets one aggregated Feishu card + in-app notification per
week. The cron row itself is ENABLED — the real gate is the
``cloud_efficiency.owner_digest`` platform setting (default off, toggled
from the efficiency page's 触达设置 with a recipient preview), so there is
exactly ONE switch and the admin UI always reflects the true state.
Weekly Monday 02:15 UTC = 10:15 Asia/Shanghai (after the 07:xx efficiency
recompute, so the digest reads fresh verdicts).

Idempotent seed (NOT EXISTS on handler_key).
"""
from __future__ import annotations

import json
import uuid

import sqlalchemy as sa

from alembic import op

revision = "426"
down_revision = "425"
branch_labels = None
depends_on = None

_HANDLER = "cmdb.efficiency_owner_digest"


def _table_exists(bind, name: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :n"
        ),
        {"n": name},
    ).scalar() 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, cron_expr, timezone, enabled, config, "
            " run_count, failure_count, created_at, updated_at) "
            "SELECT :id, :name, :display, :desc, :handler, 'cron', "
            ":cron, 'UTC', 1, :config, 0, 0, NOW(), NOW() "
            "FROM DUAL WHERE NOT EXISTS "
            "(SELECT 1 FROM scheduled_jobs WHERE handler_key = :handler)"
        ),
        {
            "id": str(uuid.uuid4()),
            "name": _HANDLER,
            "display": "云成本优化 · Owner 周提醒",
            "desc": (
                "每周把每位机器 owner / 订阅业主名下待处理的优化建议聚合成"
                "一张飞书卡片 + 站内通知。真开关在效率页「触达设置」"
                "(platform_settings cloud_efficiency.owner_digest,默认关,"
                "支持灰度名单 + 收件人预览);配置关闭时本任务静默跳过。"
            ),
            "handler": _HANDLER,
            # Weekly Monday 02:15 UTC = 10:15 Asia/Shanghai.
            "cron": "15 2 * * 1",
            "config": json.dumps({}),
        },
    )


def downgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, "scheduled_jobs"):
        bind.execute(
            sa.text("DELETE FROM scheduled_jobs WHERE handler_key = :h"),
            {"h": _HANDLER},
        )