"""Add per-scope dedup + snooze state for ``cmdb.scope_idle_sweep``.

Revision ID: 199
Revises: 198
Create Date: 2026-05-25

Before this migration, the weekly idle-scope sweep (see
:func:`app.domains.cmdb.lifecycle_service.sweep_idle_scopes`) had no
state — every Monday at 09:00 Asia/Shanghai, any scope that satisfied
"0 active+pending grants AND $0 spend over 30 days" produced a fresh
notification to every IT admin, regardless of whether the same scope
was already pinged the previous Monday. The scope owner was not
informed at all (recipients were resolved purely from the
``global_admin`` / ``it_admin`` AuthRole rows). Result: weekly spam to
admins about scopes nobody could act on, plus the person who could act
(the owner) heard nothing.

This migration adds two columns to ``cmdb_cloud_scopes``:

* ``last_idle_notified_at`` — set by the sweep itself the moment it
  fires a notification for this scope. The next sweep skips scopes
  whose ``last_idle_notified_at`` falls inside the 14-day dedup
  window (default; see ``IDLE_SWEEP_DEDUP_DAYS`` in
  ``lifecycle_service``). Auto-clears (becomes NULL again) is **not**
  needed: once the scope is no longer idle (a grant returns or spend
  appears), it falls out of ``list_idle_scope_candidates`` so the
  column simply stops being consulted.

* ``idle_snoozed_until`` — owner-set "ignore this candidate until
  YYYY-MM-DD". Distinct from ``last_idle_notified_at`` because the
  snooze is an explicit human decision ("I know it's idle, I'm
  keeping it"), whereas the dedup window is automatic backoff.
  Default snooze duration is 30 days, applied by the
  ``POST /cmdb/scopes/{id}/snooze-idle`` endpoint. Both the owner
  and any IT admin can set it; the sweep checks ``snoozed_until >
  now()`` and excludes matching scopes.

Both columns are nullable. NULL has the natural meaning:
"never notified" / "not snoozed" — and the sweep treats NULL as
"safe to notify".

No FK; no index. Filtering happens once a week inside the sweep, on
a table that holds tens to low hundreds of rows on real deployments
(one scope per cloud sub-account), so a sequential scan is cheaper
than the index maintenance cost on every billing-tag annotate.

Pairs with the same-PR rewrite of ``sweep_idle_scopes`` which:
  1. Filters out scopes inside the dedup window or under active snooze.
  2. Adds the scope owner to the per-candidate recipient set (still
     deduped against the admin recipient set so an admin who is also
     an owner gets ONE Feishu card, not two).
  3. Stamps ``last_idle_notified_at`` after a successful dispatch.

Downgrade drops both columns — there is no orphan state to clean up
since nothing else in the schema references them.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "199"
down_revision = "198"
branch_labels = None
depends_on = None


def _column_exists(bind, table: str, column: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "AND TABLE_NAME = :t AND COLUMN_NAME = :c"
        ),
        {"t": table, "c": column},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()

    if not _column_exists(bind, "cmdb_cloud_scopes", "last_idle_notified_at"):
        op.add_column(
            "cmdb_cloud_scopes",
            sa.Column(
                "last_idle_notified_at",
                sa.DateTime(timezone=True),
                nullable=True,
            ),
        )

    if not _column_exists(bind, "cmdb_cloud_scopes", "idle_snoozed_until"):
        op.add_column(
            "cmdb_cloud_scopes",
            sa.Column(
                "idle_snoozed_until",
                sa.DateTime(timezone=True),
                nullable=True,
            ),
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _column_exists(bind, "cmdb_cloud_scopes", "idle_snoozed_until"):
        op.drop_column("cmdb_cloud_scopes", "idle_snoozed_until")
    if _column_exists(bind, "cmdb_cloud_scopes", "last_idle_notified_at"):
        op.drop_column("cmdb_cloud_scopes", "last_idle_notified_at")