"""Dual cost measures — 业务折前成本 (list cost) columns on cost tables.

Revision ID: 429
Revises: 428
Create Date: 2026-07-27

Background (stakeholder feedback 2026-07, verified against real bills on
2026-07-27): Helix's single cost measure is the invoice/net view —
``amount_usd`` / ``total_cost_usd`` carry the post-discount amount that
matches the cloud bill. That measure is correct for finance
reconciliation but wrong for per-account engineering visibility:

* AWS Savings Plans concentrate the real cost on the SP-purchasing
  account (380838443567) and net covered EC2 usage on the consuming
  account to ~$0 (verified: ``SavingsPlanCoveredUsage`` unblended ==
  on-demand equivalent, exactly offset by ``SavingsPlanNegation``).
* GCP spreads promotions / spend-commitment credits pro-rata into every
  project's net cost (May 2026: a $316k PROMOTION zeroed two weeks of
  project cost); GCP's ``cost`` column additionally embeds the
  negotiated contract price (May: cost $770k vs cost_at_list $976k).
* Alicloud posts adjustment/refund rows on the processing day
  (2026 YTD −$215k concentrated on 5/25 & 7/21), flipping months
  negative.

The fix is a SECOND measure stored alongside — never replacing — the
invoice measure: the whitelist-based "business list cost" (研发口径).
Engineering-facing surfaces read only the new columns; the invoice
columns stay bit-identical for finance/reconciliation/history.

New columns (all nullable adds — no backfill in this migration; the
driver-side whitelist populates them from PR2 onward and a bounded
backfill recomputes history):

``cmdb_cost_facts``
* ``amount_usd_list`` / ``amount_native_list`` — business list cost.
  Non-NULL ONLY on whitelisted usage rows, so the engineering measure
  is a plain ``SUM(amount_usd_list)`` with no row filtering.
* ``amount_usd_public`` — public on-demand list price for the same
  whitelisted rows (AWS ``pricing_public_on_demand_cost``; GCP equals
  the list measure). Finance derives "engineer-earned savings"
  (spot / tiered pricing) as ``public − list``.
* ``cost_bucket`` — coarse row classification driving the finance
  waterfall (``GROUP BY cost_bucket SUM(amount_usd)``):
  usage / commitment_fee / commitment_discount / contract_discount /
  credit / adjustment / tax / support / other. Default 'usage' is the
  correct value for every pre-existing row shape that carried cost
  before this feature (discount rows ingested before PR2 are
  reclassified by the backfill).

``cmdb_cloud_billing_daily``
* ``total_cost_usd_list`` / ``total_cost_usd_public`` — same two
  measures at (day × scope) grain for daily trends, budget/spike
  alerts, and the cost_facts-empty fallback path.

Idempotent: every add is guarded by an information_schema column-exists
check (safe on re-run and on prod stamp-then-upgrade recovery). Pure
nullable column adds — TiDB online DDL, no table lock, no FK changes.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "429"
down_revision = "428"
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


# Fresh Column objects per call — a sa.Column instance can only be
# bound to one table, so the builders avoid reuse across add_column
# calls (and Column.copy() is deprecated in SQLAlchemy 2.0).
def _fact_columns() -> list[sa.Column]:
    return [
        sa.Column("amount_usd_list", sa.Numeric(14, 6), nullable=True),
        sa.Column("amount_native_list", sa.Numeric(14, 6), nullable=True),
        sa.Column("amount_usd_public", sa.Numeric(14, 6), nullable=True),
        sa.Column(
            "cost_bucket",
            sa.String(32),
            nullable=False,
            server_default="usage",
        ),
    ]


def _daily_columns() -> list[sa.Column]:
    return [
        sa.Column("total_cost_usd_list", sa.Numeric(14, 4), nullable=True),
        sa.Column("total_cost_usd_public", sa.Numeric(14, 4), nullable=True),
    ]


def upgrade() -> None:
    bind = op.get_bind()
    for col in _fact_columns():
        if not _column_exists(bind, "cmdb_cost_facts", col.name):
            op.add_column("cmdb_cost_facts", col)
    for col in _daily_columns():
        if not _column_exists(bind, "cmdb_cloud_billing_daily", col.name):
            op.add_column("cmdb_cloud_billing_daily", col)


def downgrade() -> None:
    bind = op.get_bind()
    for col in _fact_columns():
        if _column_exists(bind, "cmdb_cost_facts", col.name):
            op.drop_column("cmdb_cost_facts", col.name)
    for col in _daily_columns():
        if _column_exists(bind, "cmdb_cloud_billing_daily", col.name):
            op.drop_column("cmdb_cloud_billing_daily", col.name)