"""bailian_usage_daily 优惠后(after-discount, pre-coupon)成本列。

Revision ID: 264
Revises: 263
Create Date: 2026-06-07

百炼账单(QueryBillOverview)有三档价格,财务/官网都要看清:
  目录总价(原价/gross) ──优惠金额──▶ 优惠后 ──代金券+抹零──▶ 应付(net)
现有 cost_usd/cost_cny = 应付(net),cost_*_gross = 原价。新增中间档
**优惠后**(= 原价 − 优惠金额,代金券之前):

* ``cost_cny_after_discount`` / ``cost_usd_after_discount`` —— 可空,与
  另两对成对。2026-05 百炼实测:原价 $13,900.54 / 优惠后 $4,865.19 /
  应付 $4,727.71(三者与阿里云官网商品账单逐分对齐)。

幂等:``_column_exists`` 守卫。Downgrade 删列。
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "264"
down_revision = "263"
branch_labels = None
depends_on = None

_TABLE = "bailian_usage_daily"


def _column_exists(table: str, column: str) -> bool:
    conn = op.get_bind()
    result = conn.execute(
        sa.text(
            "SELECT COUNT(*) FROM information_schema.columns "
            "WHERE table_schema = DATABASE() "
            "AND table_name = :t AND column_name = :c"
        ),
        {"t": table, "c": column},
    )
    return (result.scalar() or 0) > 0


_ROLLUP = "ai_spending_period_rollup"


def upgrade() -> None:
    if not _column_exists(_TABLE, "cost_usd_after_discount"):
        op.add_column(
            _TABLE,
            sa.Column("cost_usd_after_discount", sa.Numeric(14, 6), nullable=True),
        )
    if not _column_exists(_TABLE, "cost_cny_after_discount"):
        op.add_column(
            _TABLE,
            sa.Column("cost_cny_after_discount", sa.Numeric(14, 6), nullable=True),
        )
    # Mirror onto the AI-spend rollup (USD only — the rollup stores USD and
    # derives CNY at display) so closed-month materialisations preserve the
    # middle (优惠后) tier and don't lose it vs the live/open month.
    if _column_exists(_ROLLUP, "id") and not _column_exists(
        _ROLLUP, "cost_usd_after_discount",
    ):
        op.add_column(
            _ROLLUP,
            sa.Column("cost_usd_after_discount", sa.Numeric(14, 6), nullable=True),
        )


def downgrade() -> None:
    if _column_exists(_ROLLUP, "cost_usd_after_discount"):
        op.drop_column(_ROLLUP, "cost_usd_after_discount")
    if _column_exists(_TABLE, "cost_cny_after_discount"):
        op.drop_column(_TABLE, "cost_cny_after_discount")
    if _column_exists(_TABLE, "cost_usd_after_discount"):
        op.drop_column(_TABLE, "cost_usd_after_discount")