"""Create cmdb_cloud_scope_alert_fired — atomic claim-first dedup.

Revision ID: 171
Revises: 170
Create Date: 2026-05-16

Backs the claim-first dedup pattern in
:func:`evaluate_scope_alert_rules` + :func:`_alert_spike`. Mirrors the
shape of :class:`ApiKeyBudgetAlertsDedup` (PR #298) which solved the
same TOCTOU class for API-key budget alerts.

Why a dedicated table (vs the existing ``alert_rules[*].last_fired``
JSON map on ``cmdb_cloud_scopes``):

The JSON map is mutated inside :func:`evaluate_scope_alert_rules`
and persisted only when the **outer** cron commit lands at the end
of ``sync_billing_daily_for_integration`` (line 6050). Any
exception in the long path between dispatch and commit (un-
registered-account reconciliation, FX-rate lookup, late provider
call, etc.) rolls the whole transaction back — the Feishu card is
already in flight but the dedup state is lost. Next cron tick
re-evaluates with an empty ``last_fired`` and re-fires.

Prod incident 2026-05-16: backfill script issued ~7 alerts per
scope across ~60 scopes (one card per (month_key, scope) pair)
because each backfill chunk re-ran evaluation against an unstamped
JSON map.

The new table makes the dedup atomic at *claim* time:

  * Caller computes (scope_id, rule_type, period_key, threshold_usd)
  * Issues an ``INSERT IGNORE`` style claim inside ``begin_nested()``
  * IntegrityError → another run already owns the slot → skip dispatch
  * Success → caller is the owner of this slot, dispatch the card

The UNIQUE constraint enforces the contract regardless of how many
concurrent crons / manual syncs race on the same scope.

``rule_type`` discriminates:
  * ``monthly`` — per-rule monthly threshold (existing
    :func:`evaluate_scope_alert_rules` monthly rules)
  * ``daily`` — per-rule daily threshold (existing daily rules)
  * ``daily_spike`` — the 7d-average comparison
    :func:`_alert_spike`. ``threshold_usd`` is set to the multiplier
    (e.g. 1.5) so a future admin re-tuning the cron's multiplier
    doesn't accidentally re-fire today's spike under the new threshold.

**No FK constraints declared** — same prod-vs-local TiDB collation
drift workaround as migrations 152 / 158 / 160 / 168 / 169. The
referential guarantee is in the application layer: the cron deletes
the dedup row when a scope is hard-deleted (rare), and
``scope_id`` is always written by code that has already loaded the
scope row in the same session.

Idempotent — guarded by ``information_schema`` existence check so
re-runs on environments where the table already exists are no-ops.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "171"
down_revision = "170"
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},
    ).first()
    return row is not None


def upgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, "cmdb_cloud_scope_alert_fired"):
        return
    op.create_table(
        "cmdb_cloud_scope_alert_fired",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("scope_id", sa.String(36), nullable=False),
        # ``monthly`` / ``daily`` / ``daily_spike``
        sa.Column("rule_type", sa.String(32), nullable=False),
        # YYYY-MM for monthly, YYYY-MM-DD for daily / daily_spike
        sa.Column("period_key", sa.String(16), nullable=False),
        # Per-rule threshold (USD) for ``monthly`` / ``daily`` —
        # claim slot is per-threshold so the tier ladder (50% / 80% /
        # 100% of budget) records each tier separately. For
        # ``daily_spike`` the column carries the cron's multiplier
        # (e.g. 1.5000) — same idempotency guarantee, different
        # semantic. Numeric chosen over Float for exact equality in
        # the UNIQUE key (float-keyed uniqueness on MySQL/TiDB is
        # treacherous — 1.5 vs 1.5000000000000002 are not equal).
        sa.Column(
            "threshold_usd",
            sa.Numeric(14, 4),
            nullable=False,
            server_default=sa.text("0"),
        ),
        sa.Column(
            "fired_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.func.now(),
        ),
        # Snapshot of the metric that triggered the alert — kept for
        # audit / debugging. Not on the UNIQUE key, so backfills can
        # set a different ``current_usd`` on a re-attempted claim
        # without IntegrityError.
        sa.Column(
            "current_usd",
            sa.Numeric(14, 4),
            nullable=False,
            server_default=sa.text("0"),
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.func.now(),
        ),
        sa.UniqueConstraint(
            "scope_id", "rule_type", "period_key", "threshold_usd",
            name="uq_cmdb_cloud_scope_alert_fired",
        ),
        mysql_engine="InnoDB",
        mysql_charset="utf8mb4",
    )
    # Period-keyed lookup so a future "list all alerts fired in
    # 2026-05" query stays cheap without scanning the whole table.
    op.create_index(
        "ix_cmdb_cloud_scope_alert_fired_period",
        "cmdb_cloud_scope_alert_fired",
        ["period_key", "rule_type"],
    )
    # Scope-keyed lookup so the admin UI's "alert history for this
    # scope" tab can pull rows without a UNIQUE-key probe per period.
    op.create_index(
        "ix_cmdb_cloud_scope_alert_fired_scope_fired",
        "cmdb_cloud_scope_alert_fired",
        ["scope_id", "fired_at"],
    )


def downgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, "cmdb_cloud_scope_alert_fired"):
        return
    op.drop_index(
        "ix_cmdb_cloud_scope_alert_fired_scope_fired",
        table_name="cmdb_cloud_scope_alert_fired",
    )
    op.drop_index(
        "ix_cmdb_cloud_scope_alert_fired_period",
        table_name="cmdb_cloud_scope_alert_fired",
    )
    op.drop_table("cmdb_cloud_scope_alert_fired")