"""Add manual decommission fields + scope-idle-sweep job to cmdb cloud lifecycle.

Revision ID: 089
Revises: 088
Create Date: 2026-04-29

Three columns on ``cmdb_cloud_scopes`` capture admin-initiated takedowns
of a sub-account / subscription / project / RAM account that's no longer
needed (closed by finance, project shut down, account migrated):

  * ``decommissioned_at`` — when the admin clicked 下线
  * ``decommissioned_by`` — which admin (``identity_persons.id``)
  * ``decommission_reason`` — short free-text justification (≤ 500 chars)

The action itself just flips ``status='closed'`` and disables active
grants in DB. We deliberately do NOT call any cloud-side delete API
(AWS Organizations close-account is irreversible and has a 90-day
suspension window; Azure subscription cancellation hits billing). The
admin closes the account on the cloud console; Helix mirrors the state.

A new daily ``cmdb.scope_idle_sweep`` job hands the IT admin a list of
candidates worth taking down: ``status='active'`` scopes with no active
grants and zero spend in the trailing 30 days. It only notifies — never
auto-decommissions — because false positives (a brand-new placeholder
account, an automation-only scope) are unacceptable.

Idempotent: column adds + seed insert are guarded so re-runs don't fail.
"""
from __future__ import annotations

import uuid

import sqlalchemy as sa

from alembic import op

revision = "089"
down_revision = "088"
branch_labels = None
depends_on = None


def _column_exists(table: str, column: str) -> bool:
    bind = op.get_bind()
    insp = sa.inspect(bind)
    return any(c["name"] == column for c in insp.get_columns(table))


def _table_exists(bind, name: str) -> bool:
    insp = sa.inspect(bind)
    return name in insp.get_table_names()


def upgrade() -> None:
    if not _column_exists("cmdb_cloud_scopes", "decommissioned_at"):
        op.add_column(
            "cmdb_cloud_scopes",
            sa.Column(
                "decommissioned_at",
                sa.DateTime(timezone=True),
                nullable=True,
            ),
        )
    if not _column_exists("cmdb_cloud_scopes", "decommissioned_by"):
        op.add_column(
            "cmdb_cloud_scopes",
            sa.Column(
                "decommissioned_by",
                sa.String(36),
                nullable=True,
            ),
        )
    if not _column_exists("cmdb_cloud_scopes", "decommission_reason"):
        op.add_column(
            "cmdb_cloud_scopes",
            sa.Column(
                "decommission_reason",
                sa.String(500),
                nullable=True,
            ),
        )

    bind = op.get_bind()
    if _table_exists(bind, "scheduled_jobs"):
        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": "cmdb.scope_idle_sweep",
                "display": "云账号闲置候选扫描",
                "desc": (
                    "每周一 09:00 扫一遍 status='active' 的 cmdb_cloud_scopes:"
                    "0 active grants 且过去 30 天 $0 花费的视为闲置候选,"
                    "汇总成一封邮件发给 IT Admin 提醒考虑下线。仅提醒,"
                    "不会自动 decommission。"
                ),
                "handler": "cmdb.scope_idle_sweep",
                "cron": "0 9 * * 1",
                "config": "{}",
            },
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, "scheduled_jobs"):
        bind.execute(
            sa.text(
                "DELETE FROM scheduled_jobs "
                "WHERE handler_key = 'cmdb.scope_idle_sweep'"
            )
        )

    if _column_exists("cmdb_cloud_scopes", "decommission_reason"):
        op.drop_column("cmdb_cloud_scopes", "decommission_reason")
    if _column_exists("cmdb_cloud_scopes", "decommissioned_by"):
        op.drop_column("cmdb_cloud_scopes", "decommissioned_by")
    if _column_exists("cmdb_cloud_scopes", "decommissioned_at"):
        op.drop_column("cmdb_cloud_scopes", "decommissioned_at")