"""Remove the ``cmdb.scope_inactivity_sweep`` scheduled-jobs row.

Revision ID: 138
Revises: 137
Create Date: 2026-05-09

The ``cmdb.scope_inactivity_sweep`` cron (seeded in migration 052)
sent "60+ days inactive" notifications to scope owners based on
``CmdbCloudAccessGrant.last_login_at``. Trouble: that column is
populated by ``refresh_person_last_login_in_scope``, which depends on
each cloud's IAM-side login telemetry. In practice the providers we
ship don't expose a usable signal for the SSO-class logins our users
actually do (AWS Identity Center / Azure portal SSO sign-ins aren't
queryable through the IAM lastUsed APIs we wired up), so
``last_login_at`` stays NULL on most active grants. The sweep then
fires Stage-0 "60+ days no login" alerts on people who actively use
their access — pure false-positive noise.

Going forward the platform relies solely on the expiry-based flow
(``cmdb.access_expiry_sweep``): T-14 / T-7 / T-1 reminders to
renew, T+0 auto-revoke (prod scopes excepted — held + owner notified).
That's an honest, evidence-based gate; the inactivity sweep was a
guess dressed up as policy.

This migration:

* Deletes the ``scheduled_jobs`` row keyed on
  ``handler_key='cmdb.scope_inactivity_sweep'``. Without this the
  scheduler runner would log "Handler not registered" every tick
  starting the moment the matching ``@register_scheduled_job``
  decorator is removed (this PR), because migration 137's
  failure-dedup writes one in-app card per fresh signature — it'd
  push exactly one "handler vanished" card to every IT admin and
  then go quiet, but the failure_count + red banner stick around.
* Cascades the delete to ``scheduled_job_runs`` via the existing FK
  (``ondelete='CASCADE'``) so the run history goes with it. We're
  not preserving "last 20 runs of inactivity sweep" history — the
  notifications they generated were the false alerts we're removing.

The associated platform_settings rows (``cloud.inactivity_*``) are
LEFT in place. They're cheap, they document the intended thresholds
if the sweep ever returns, and they don't show up anywhere user-
facing. Removing them would mean a follow-up migration to put them
back on resurrection — not worth the churn.

Idempotent: re-running on a DB that already deleted the row is a
no-op.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "138"
down_revision = "137"
branch_labels = None
depends_on = None


_HANDLER_KEY = "cmdb.scope_inactivity_sweep"


def upgrade() -> None:
    bind = op.get_bind()
    # ``scheduled_jobs`` may not exist yet on a brand-new DB whose
    # migration cursor lives before 052 (impossible in practice — 138
    # only runs after 052 by definition — but we guard the SQL so a
    # standalone re-run on a half-bootstrapped test DB doesn't crash).
    exists = bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'scheduled_jobs'",
        ),
    ).scalar()
    if not exists:
        return
    # Resolve the parent row's ``id`` so we can delete the children
    # explicitly. The model declares ``ondelete='CASCADE'`` on
    # ``scheduled_job_runs.job_id``, so the parent DELETE alone WOULD
    # cascade — but we belt-and-braces an explicit child DELETE
    # because TiDB's foreign-key semantics historically lagged MySQL's
    # (CASCADE was not enforced in some older TiDB versions and the
    # schema may carry the FK declaration without the runtime guarantee
    # on those clusters). Defending against that with a pre-DELETE
    # turns "the assumption was wrong on prod" from a 500 into a no-op.
    job_ids = list(bind.execute(
        sa.text(
            "SELECT id FROM scheduled_jobs WHERE handler_key = :k"
        ),
        {"k": _HANDLER_KEY},
    ).scalars())
    if job_ids:
        # ``IN`` with a parameter list — bind once per id since
        # SQLAlchemy text() doesn't auto-expand sequences without
        # ``.bindparams(expanding=True)``. Migration runs once per
        # tenant; the loop cost is irrelevant.
        for jid in job_ids:
            bind.execute(
                sa.text(
                    "DELETE FROM scheduled_job_runs WHERE job_id = :jid"
                ),
                {"jid": jid},
            )
    bind.execute(
        sa.text("DELETE FROM scheduled_jobs WHERE handler_key = :k"),
        {"k": _HANDLER_KEY},
    )


def downgrade() -> None:
    # No-op. Recreating the row on downgrade would resurrect a cron
    # whose handler is also deleted in this PR — re-registering it
    # belongs to the (hypothetical) follow-up that brings back a
    # functional inactivity signal. Don't fake it here.
    pass