"""Index cmdb_cost_facts (scope_id, resource_id) — kills the visibility OOM.

Revision ID: 427
Revises: 426
Create Date: 2026-07-23

Prod incident (2026-07-23, right after the visibility rollout): the
self-service efficiency page 500'd for employees with TiDB error 8176
(query killed for exceeding the instance memory limit). Root cause: the
new ``visibility_clause`` embedded a correlated EXISTS against
``cmdb_cost_facts`` (millions of line-item rows) filtered by
``scope_id`` — a column with NO index — so TiDB materialized /
full-scanned the fact table per request and blew the memory budget.
Dev MySQL with tiny data never showed it.

Two-part fix:
* this index — ``(scope_id, resource_id)`` makes "which resources does
  this subscription attribute" an index-range read (also speeds up the
  pre-existing ``_scope_server_exists`` correlated EXISTS on the
  subscription 成本优化 pane);
* ``visibility.py`` now materializes the bounded scope→server-id set
  ONCE at resolve time and the request-time clause is a plain
  ``id IN (...)`` — cost facts leave the per-request plan entirely.

Idempotent (information_schema index-exists guard). TiDB online DDL —
adding an index does not lock writes.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "427"
down_revision = "426"
branch_labels = None
depends_on = None

_TABLE = "cmdb_cost_facts"
_INDEX = "ix_facts_scope_resource"


def _index_exists(bind, table: str, index: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.STATISTICS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "  AND TABLE_NAME = :t AND INDEX_NAME = :i LIMIT 1"
        ),
        {"t": table, "i": index},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    if not _index_exists(bind, _TABLE, _INDEX):
        op.create_index(_INDEX, _TABLE, ["scope_id", "resource_id"])


def downgrade() -> None:
    bind = op.get_bind()
    if _index_exists(bind, _TABLE, _INDEX):
        op.drop_index(_INDEX, table_name=_TABLE)