"""Seed the daily renewal-request lapse sweep cron.

Revision ID: 438
Revises: 437
Create Date: 2026-07-29

One org-wide ``scheduled_jobs`` row for ``cmdb.renewal_lapse_sweep``:
closes cloud grant renewal requests that nobody decided within
``RENEWAL_REQUEST_LAPSE_DAYS`` (14).

Why a cron is required rather than lazily expiring on read: an open
request holds the grant's single "open renewal" slot (the UNIQUE index on
``open_for_grant_id``), so a forgotten request makes the grant
permanently un-renewable — the holder's resubmission 409s with an error
that reads like a bug. The row also stays approvable indefinitely, and
approving a months-old request is how you resurrect access everyone had
already forgotten about.

Lapsing is neither an approval nor a rejection: the grant expires on its
original schedule exactly as if no request had been filed, and the holder
is notified (before this whole rework, access simply vanished with no
explanation).

01:00 UTC = 09:00 Asia/Shanghai — deliberately AFTER the 00:15 UTC
``cmdb.access_expiry_sweep`` so a request that was decided overnight is
already terminal and never gets lapsed in the same tick it was approved.

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

import json
import uuid

import sqlalchemy as sa

from alembic import op

revision = "438"
down_revision = "437"
branch_labels = None
depends_on = None

_HANDLER = "cmdb.renewal_lapse_sweep"


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": "云权限续期申请超期关单",
            "desc": (
                "每日关闭超过 14 天仍未审批的云权限续期申请。关单既不是批准"
                "也不是驳回 —— grant 按原到期日正常回收,申请人收到通知。"
                "不关单会永久占用该 grant 的唯一「待审批」名额(申请人无法"
                "重新提交),且这条申请几个月后仍可被批准,把大家早已忘记的"
                "权限复活。"
            ),
            "handler": _HANDLER,
            # 01:00 UTC = 09:00 Asia/Shanghai, after the 00:15 UTC expiry
            # sweep so an overnight decision is already terminal.
            "cron": "0 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},
        )