"""Seed cmdb.owner_semiannual_confirmation + cmdb.zero_spend_alert cron rows.

Revision ID: 169
Revises: 168
Create Date: 2026-05-15

Idempotent — both INSERTs gate on a name-based NOT EXISTS so a re-run
on environments that already received these crons via manual seed
(local dev that hand-rolled the row) doesn't blow up.

The handlers themselves register at module-import time
(``app.scheduled_jobs.cloud_owner_handlers``) which is wired into both
the API process (``app.main``) and the worker process (``app.worker``).
"""
from __future__ import annotations

import uuid

import sqlalchemy as sa

from alembic import op

revision = "169"
down_revision = "168"
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 _seed_job(
    bind, *, name: str, display: str, desc: str, handler: str, cron: str,
) -> None:
    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, '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": name,
            "display": display,
            "desc": desc,
            "handler": handler,
            "cron": cron,
            "config": "{}",
        },
    )


def upgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, "scheduled_jobs"):
        return
    _seed_job(
        bind,
        name="cmdb.owner_semiannual_confirmation",
        display="云订阅 Owner 半年确认告警",
        desc=(
            "每日 09:00（Asia/Shanghai）扫描所有 environment≠prod 的活跃"
            "云订阅，找出 owner 距上次确认 >180 天的发送飞书卡片；同时"
            "扫描发出 14 天后仍未响应的（半年/零花费）待确认，升级到"
            "所有 IT Admin。"
        ),
        handler="cmdb.owner_semiannual_confirmation",
        cron="0 9 * * *",
    )
    _seed_job(
        bind,
        name="cmdb.zero_spend_alert",
        display="云订阅零花费告警",
        desc=(
            "每月 1 号 09:00（Asia/Shanghai）扫描所有 environment≠prod "
            "的活跃云订阅，找出上自然月花费 = $0 的发送飞书卡片，由 owner "
            "选择保留(写理由) / 我来清理 / 联系 IT 处理。"
        ),
        handler="cmdb.zero_spend_alert",
        cron="0 9 1 * *",
    )


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 IN "
            "('cmdb.owner_semiannual_confirmation','cmdb.zero_spend_alert')"
        )
    )