"""Widen ``cmdb_cost_facts.usage_quantity`` from NUMERIC(20,6) → NUMERIC(30,6).

Revision ID: 144
Revises: 143
Create Date: 2026-05-10

Why:

* PR #226 declared ``usage_quantity Numeric(20, 6)`` (14 integer + 6
  fractional digits, max ~9.99e13). That worked for AWS Cost Explorer
  ``UsageQuantity`` (vCPU·h, GB·mo, requests) and Aliyun
  ``InstanceConfig`` quantities, all bounded well below 1e10.
* GCP's BigQuery billing export reports ``usage.amount`` for storage
  SKUs as **byte-seconds** at the daily-aggregate level. A single
  ``Storage Machine Image`` row for one ``us-central1`` project on
  2026-05-09 was ``1,570,859,566,034,408`` byte-seconds (~1.57e15,
  equivalent to ~18 GB stored for one day). That's 16 integer digits,
  overflowing the column.
* Result: every per-scope GCP ``cmdb.cost_facts_sync`` cron tick
  raised ``(asyncmy.errors.DataError) (1264, "Out of range value for
  column 'usage_quantity' at row 1")`` and the whole UPSERT batch
  rolled back — no facts landed, even for the rows that fit. The
  failure-notifier paged the IT admin on every tick.

Fix: widen to ``NUMERIC(30, 6)`` — 24 integer + 6 fractional digits,
max ~9.99e23. Covers multi-EiB tenants for years of storage. Money
columns (``amount_usd`` / ``amount_native``) stay at NUMERIC(14, 6)
— they don't need this width and a wider money column would change
the finance-aggregate display layer.

The ``usage_quantity`` column is informational (drives unit-cost
analytics in the dashboard); a wider type doesn't change any join /
filter / aggregation logic. Fully backward-compatible: existing rows
fit unchanged in the wider column.

Idempotent guards: introspect the current column type before ALTER
so a re-apply doesn't blow up. Down-revision is best-effort — narrow
back ONLY if no overflowing row exists; otherwise skip with a
WARNING (preserve data, surface to the operator).
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "144"
down_revision = "143"
branch_labels = None
depends_on = None


def _table_exists(bind: sa.engine.Connection, name: str) -> bool:
    inspector = sa.inspect(bind)
    return name in inspector.get_table_names()


def _current_precision(
    bind: sa.engine.Connection, table: str, column: str,
) -> tuple[int, int] | None:
    """Return (numeric_precision, numeric_scale) for the column, or
    None when the table / column / DB doesn't expose introspection.
    Used to make the ALTER idempotent — re-running migration 144 on
    an already-widened column is a no-op."""
    try:
        row = bind.execute(
            sa.text(
                "SELECT NUMERIC_PRECISION, NUMERIC_SCALE "
                "FROM INFORMATION_SCHEMA.COLUMNS "
                "WHERE TABLE_SCHEMA = DATABASE() "
                "AND TABLE_NAME = :t AND COLUMN_NAME = :c"
            ),
            {"t": table, "c": column},
        ).one_or_none()
    except Exception:  # noqa: BLE001
        return None
    if row is None:
        return None
    return int(row[0] or 0), int(row[1] or 0)


def upgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, "cmdb_cost_facts"):
        return
    current = _current_precision(bind, "cmdb_cost_facts", "usage_quantity")
    if current == (30, 6):
        # Already widened (re-apply on a fresh checkout that already
        # ran migration 144). No-op.
        return
    op.alter_column(
        "cmdb_cost_facts",
        "usage_quantity",
        existing_type=sa.Numeric(20, 6),
        type_=sa.Numeric(30, 6),
        existing_nullable=True,
    )


def downgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, "cmdb_cost_facts"):
        return
    # Only narrow if no row would overflow. Numeric(20, 6) max integer
    # digits is 14 → max value is 99999999999999.999999. Anything
    # ≥1e14 cannot fit and would be silently truncated by MySQL or
    # raise on TiDB. Refuse the downgrade in that case so the operator
    # sees the data-loss risk.
    overflow = bind.execute(
        sa.text(
            "SELECT COUNT(*) FROM cmdb_cost_facts "
            "WHERE usage_quantity IS NOT NULL "
            "AND ABS(usage_quantity) >= 1e14"
        )
    ).scalar()
    if overflow and int(overflow) > 0:
        # Surface as a runtime warning; alembic doesn't have a clean
        # "skip" exit code for a downgrade, so emit and proceed without
        # ALTER — the column stays Numeric(30, 6) and the operator can
        # decide. Mirrors the posture of migration 138 (remove sweep
        # without dropping data).
        import logging
        logging.getLogger(__name__).warning(
            "migration 144 downgrade: %s rows have usage_quantity "
            "≥ 1e14 — refusing to narrow to Numeric(20, 6). Run "
            "data cleanup first if you really need to downgrade.",
            overflow,
        )
        return
    op.alter_column(
        "cmdb_cost_facts",
        "usage_quantity",
        existing_type=sa.Numeric(30, 6),
        type_=sa.Numeric(20, 6),
        existing_nullable=True,
    )