"""Drop the leftmost-prefix-duplicate indexes on ``cmdb_cost_facts``.

Revision ID: 483
Revises: 482

2026-08-16 实测生产:``cmdb_cost_facts`` **938 万行、10.75 GB,其中索引 6.37 GB
比数据本身的 4.38 GB 还大** —— 16 个二级索引,每次写入都要维护 16 遍。

先确认过「不缺索引」:把页面真正在跑的查询形状逐个 ``EXPLAIN``(看板按天、
集成+服务、按 scope、按领用人、按资源下钻、按部门、按成本中心、按
auto_owner_source、fact_key 幂等查)—— **每一个都命中了专属索引,没有一个走
全表扫描**。所以方向是减,不是加。

四个索引是**最左前缀重复**,查询优化器永远不会选中它们:

| 冗余索引 | 被谁覆盖 |
|---|---|
| ``fk_cmdb_cost_facts_scope`` [scope_id] | ``ix_facts_scope_date`` / ``ix_facts_scope_resource`` |
| ``fk_cmdb_cost_facts_integration`` [integration_id] | ``ix_facts_int_service_date`` / ``ix_facts_int_rg_date`` |
| ``fk_cmdb_cost_facts_owner`` [cost_owner_person_id] | ``ix_facts_owner_date`` |
| ``ix_facts_date_int`` [date, integration_id] | ``ix_facts_date_measure`` [date, integration_id, ...] |

``ix_facts_date_id`` [date, id] **刻意保留** —— 它的第二列与
``ix_facts_date_measure`` 不同,不是前缀子集,且很可能在支撑 keyset 分页。

## 为什么每个 DROP 都包在 try 里

三个 ``fk_*`` 索引**只存在于生产**:TiDB 在建外键时自动补的,本地/CI 的全新库
里根本没有(实测 DROP 报 1091 "key doesn't exist")。也就是说 **TiDB 是否允许
删掉一个外键自动补的索引,本地无法验证** —— 它可能以「外键仍需要它」为由拒绝,
即使有复合索引能顶上。

这是一个**存储优化,不是正确性修复**。让它去赌一把、赌输了卡住整条迁移链,
是完全不划算的交换 —— 2026-08-16 刚因为迁移 472 失败把 472~479 全卡在生产,
修了一整天。所以:能删就删,删不掉就 WARN 记下并继续,下次有人拿着 TiDB 的
确切行为再来收尾。**这里吞掉的异常有明确边界:只吞 DROP INDEX,且只在这个
纯优化迁移里。**
"""
import logging

import sqlalchemy as sa

from alembic import op

revision = "483"
down_revision = "482"
branch_labels = None
depends_on = None

logger = logging.getLogger("alembic.runtime.migration")

_TABLE = "cmdb_cost_facts"
# (冗余索引, 覆盖它的复合索引之一 —— 删之前必须确认它真的在)
_REDUNDANT: tuple[tuple[str, str], ...] = (
    ("fk_cmdb_cost_facts_scope", "ix_facts_scope_date"),
    ("fk_cmdb_cost_facts_integration", "ix_facts_int_service_date"),
    ("fk_cmdb_cost_facts_owner", "ix_facts_owner_date"),
    ("ix_facts_date_int", "ix_facts_date_measure"),
)


def _has_index(name: str) -> bool:
    return op.get_bind().execute(
        sa.text(
            "SELECT 1 FROM information_schema.STATISTICS "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t "
            "AND INDEX_NAME = :n LIMIT 1"
        ),
        {"t": _TABLE, "n": name},
    ).scalar() is not None


def upgrade() -> None:
    for redundant, covered_by in _REDUNDANT:
        if not _has_index(redundant):
            # 本地 / CI 的全新库压根没建过这几个(TiDB 才自动补),
            # 以及重跑本迁移时 —— 都走这里。
            continue
        if not _has_index(covered_by):
            # 覆盖它的复合索引不在(有人手工改过 schema)。宁可什么都不做,
            # 也不能把这张表上唯一能服务该列的索引删掉。
            logger.warning(
                "483: keeping %s — its covering index %s is absent",
                redundant, covered_by,
            )
            continue
        try:
            op.drop_index(redundant, table_name=_TABLE)
            logger.info("483: dropped redundant index %s", redundant)
        except Exception as exc:  # noqa: BLE001 — 见模块 docstring
            logger.warning(
                "483: could not drop %s (%s) — leaving it in place; this "
                "migration is a storage optimisation and must never block "
                "the chain",
                redundant, str(exc)[:200],
            )


def downgrade() -> None:
    """把删掉的索引原样建回来(列与顺序同原定义)。"""
    columns = {
        "fk_cmdb_cost_facts_scope": ["scope_id"],
        "fk_cmdb_cost_facts_integration": ["integration_id"],
        "fk_cmdb_cost_facts_owner": ["cost_owner_person_id"],
        "ix_facts_date_int": ["date", "integration_id"],
    }
    for name, cols in columns.items():
        if not _has_index(name):
            op.create_index(name, _TABLE, cols)