"""Console-side savings detection: event ``source``/``evidence`` +
efficiency-row spec snapshot.

Revision ID: 425
Revises: 424
Create Date: 2026-07-23

Why this exists
---------------
The realized-savings ledger (migration 423) only captured actions executed
INSIDE Helix — the audit hook fires on ``cmdb.server.destroy`` /
``cmdb.server.power_off``. But owners routinely act in the cloud console
directly (stop, terminate, resize/downsize), and none of that ever reached
「已优化成本」. The 2026-07-22 decision (see DECISIONS.md): detect
console-side outcomes on the sync/recompute path and credit them too, and
add a ``downsize`` method (resize was previously unattributable at all).

Columns:
* ``cmdb_efficiency_savings_events.source`` — 'helix' (audit hook) vs
  'console' (sync-side detector). Existing rows backfill to 'helix' via the
  server default (they all came from the hook).
* ``cmdb_efficiency_savings_events.evidence`` — JSON breadcrumbs for the
  detector's conclusion (e.g. from/to instance_type + vcpu/memory for a
  downsize), so a leadership number can always be traced back to "what
  changed".
* ``cmdb_server_efficiency.instance_type`` / ``vcpu`` / ``memory_mb`` —
  the server's spec snapshotted at compute time. The next day's detector
  compares the freshly-synced spec against this snapshot to recognise a
  console-side downsize of a flagged box; without the snapshot the old
  spec is unrecoverable (sync overwrites ``cmdb_servers`` in place).

Idempotent: information_schema column-exists guards, safe for re-run /
stamp-then-upgrade recovery. No FK / no index changes (lookups stay on the
existing unique ``server_id`` key).
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "425"
down_revision = "424"
branch_labels = None
depends_on = None


def _column_exists(bind, table: str, column: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "  AND TABLE_NAME = :t AND COLUMN_NAME = :c LIMIT 1"
        ),
        {"t": table, "c": column},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    if not _column_exists(bind, "cmdb_efficiency_savings_events", "source"):
        op.add_column(
            "cmdb_efficiency_savings_events",
            sa.Column(
                "source",
                sa.String(16),
                nullable=False,
                server_default="helix",
            ),
        )
    if not _column_exists(bind, "cmdb_efficiency_savings_events", "evidence"):
        op.add_column(
            "cmdb_efficiency_savings_events",
            sa.Column("evidence", sa.JSON(), nullable=True),
        )
    for col, coltype in (
        ("instance_type", sa.String(128)),
        ("vcpu", sa.Integer()),
        ("memory_mb", sa.Integer()),
    ):
        if not _column_exists(bind, "cmdb_server_efficiency", col):
            op.add_column(
                "cmdb_server_efficiency",
                sa.Column(col, coltype, nullable=True),
            )


def downgrade() -> None:
    bind = op.get_bind()
    for table, col in (
        ("cmdb_efficiency_savings_events", "source"),
        ("cmdb_efficiency_savings_events", "evidence"),
        ("cmdb_server_efficiency", "instance_type"),
        ("cmdb_server_efficiency", "vcpu"),
        ("cmdb_server_efficiency", "memory_mb"),
    ):
        if _column_exists(bind, table, col):
            op.drop_column(table, col)