"""Platform-wide CNY ↔ USD exchange-rate resolver. The single source of truth for the rate is the platform setting ``ai_spending.cny_to_usd_rate`` (USD-per-CNY, e.g. 0.14). The weekly ``ai_spending.cny_rate_refresh`` cron fetches the live rate from frankfurter.dev (ECB-published) and writes it here. Per-integration ``config_public.cny_per_usd`` is an optional override for ops who need to pin a fixed monthly rate independent of the live feed. Lives in ``app/core`` to break a domain-to-domain coupling: cloud cost ingestion (``cmdb`` domain) and AI spending bill conversion (``ai_spending`` domain) both need the rate, and per the platform rules domain modules don't import each other. The setting key still starts with ``ai_spending.`` for historical reasons (migration 079 seeded it under that namespace) — renaming would require a no-value migration on a hot row, so we keep the label and document it as "the platform CNY/USD rate, namespaced under ai_spending by historical accident". Two surface functions: * :func:`get_usd_per_cny` — read the platform setting raw. Returns ``None`` if missing/invalid; caller decides how to react. Used by ai_spending which already has its own AppException envelope for the missing case. * :func:`resolve_cny_per_usd` — for cloud cost ingestion. Returns the *reciprocal* with override-then-platform precedence; raises :class:`RuntimeError` with a clear message if neither is set so the cron run lands a hard failure (vs silently producing wrong USD aggregates that finance would later trust). """ from __future__ import annotations from datetime import UTC, datetime, timedelta from decimal import Decimal from typing import Any from sqlalchemy.ext.asyncio import AsyncSession from app.integrations.models import PlatformSetting # The platform-level setting key. Keep aligned with # ``app.domains.ai_spending.service.SETTING_CNY_RATE`` — both names # point at the same row; this module is allowed to import from # ``ai_spending`` only for that constant, not for behaviour. PLATFORM_SETTING_USD_PER_CNY = "ai_spending.cny_to_usd_rate" PLATFORM_SETTING_REFRESHED_AT = "ai_spending.cny_rate_last_refreshed_at" # Per-integration config override key. Cloud integration's # ``config_public.cny_per_usd`` (or legacy ``fx_cny_per_usd``) takes # precedence when present. INTEGRATION_OVERRIDE_KEY = "cny_per_usd" INTEGRATION_OVERRIDE_LEGACY_KEY = "fx_cny_per_usd" # Plausibility band for USD-per-CNY. Historically 0.10–0.20 (1 USD = # 5–10 CNY). Aligned to the cron WRITE path's ``0.05 < rate < 1.0`` # band (see ai_spending_handlers.py): the reader and writer must # agree on what a plausible rate looks like, otherwise a manually- # edited platform setting like ``0.02`` (3× too low — typo for # ``0.20``) is accepted by the reader and produces ~7× cost-USD # figures silently. The lower bound was 0.0 prior to round 17. _USD_PER_CNY_MIN = 0.05 _USD_PER_CNY_MAX = 1.0 # Plausibility band for the per-integration CNY-per-USD override. # Historically 1 USD ≈ 5–10 CNY; allow 1 < rate < 30 — 3× the # modern ceiling, generous enough for any plausible regime change, # tight enough to catch 10× fat-finger errors (an admin who types # ``70`` intending ``7`` would otherwise silently produce 10× # wrong USD totals on that integration's cost-facts). Override # values < 1 are also rejected — anything below would be a typo # (USD-per-CNY entered into the CNY-per-USD override field). _CNY_PER_USD_OVERRIDE_MIN = 1.0 _CNY_PER_USD_OVERRIDE_MAX = 30.0 def _coerce_float(value: Any) -> float | None: """Best-effort coerce a JSON-stored value to ``float``. The ``platform_settings.value`` column is JSON; TiDB's driver surfaces numbers as ``int`` / ``float`` / ``Decimal`` depending on storage shape, and a manually-edited row could carry a string (``"0.14"``). Anything we can't make sense of returns ``None``. Importantly, ``bool`` is rejected — it's an ``int`` subclass and ``True`` would silently coerce to ``1.0``. """ if value is None or isinstance(value, bool): return None if isinstance(value, (int, float, Decimal)): return float(value) if isinstance(value, str): try: return float(value) except (TypeError, ValueError): return None return None # Round-51 R3-P0-4: matching cap on staleness. The weekly # ``ai_spending.cny_rate_refresh`` cron writes ``updated_at`` on every # refresh — even no-op same-value writes use ``flag_modified`` so the # timestamp ticks. If the cron has been failing silently (frankfurter # outage past the cron's own 14-day hard-fail, or its failure was # acked but never resolved), every reader downstream would otherwise # happily convert against an arbitrarily-stale rate. Refuse rates older # than this threshold. Matches the cron's own 14-day stale-after bar # so the two systems agree on "what counts as fresh enough." _RATE_STALE_AFTER_DAYS = 14 async def get_usd_per_cny(db: AsyncSession) -> float | None: """Read the platform USD-per-CNY rate (≈0.14). Returns ``None`` if the setting is missing, malformed, outside the plausibility band, OR stale (``updated_at`` older than ``_RATE_STALE_AFTER_DAYS``). Callers decide whether that's a hard error or a fall-through. The plausibility band matches the cron writer's (0.05 <= rate <= 1.0; inclusive after 2026-05-16 LS1 P1) so a typo in the platform setting can't bypass the writer's guard, and the staleness check ensures we never silently convert against a frozen rate after the cron has been broken for weeks. """ row = await db.get(PlatformSetting, PLATFORM_SETTING_USD_PER_CNY) if row is None: return None rate = _coerce_float(row.value) if rate is None: return None # Inclusive bounds — exact 0.05 / 1.0 are theoretically possible # ECB rates and must not silently drop to ``None`` (which would # take down every CN-cloud ingestion). if not (_USD_PER_CNY_MIN <= rate <= _USD_PER_CNY_MAX): return None # Round-51 R3-P0-4 staleness guard: read ``updated_at`` and refuse # if older than the cron's own freshness bar. Treat naive datetimes # as UTC (asyncmy round-trip strips tzinfo per memory # ``mysql_datetime_naive_on_fresh_load``). updated_at = getattr(row, "updated_at", None) if updated_at is not None: if updated_at.tzinfo is None: updated_at = updated_at.replace(tzinfo=UTC) age = datetime.now(UTC) - updated_at if age > timedelta(days=_RATE_STALE_AFTER_DAYS): return None return rate def _resolve_integration_override( integration_config: dict[str, Any] | None, ) -> float | None: """Pull ``cny_per_usd`` from an integration's config_public if set. Accepts both the canonical key and the legacy alias. Returns ``None`` if the field isn't set; raises if it's set but invalid (the operator opted in to overriding, so we don't silently drop a typo and fall through to the platform value). """ if not integration_config: return None raw = integration_config.get(INTEGRATION_OVERRIDE_KEY) if raw is None or raw == "": raw = integration_config.get(INTEGRATION_OVERRIDE_LEGACY_KEY) if raw is None or raw == "": return None try: rate = float(raw) except (TypeError, ValueError) as exc: raise RuntimeError( f"Integration override ``{INTEGRATION_OVERRIDE_KEY}`` is " f"not a number, got {raw!r}", ) from exc if rate <= 0: raise RuntimeError( f"Integration override ``{INTEGRATION_OVERRIDE_KEY}`` " f"must be > 0, got {rate}", ) # Plausibility band — reject 10× / 100× fat-fingers (e.g. an # admin typing ``70`` when they mean ``7``) and obvious typos # (sub-1 values, which would be USD-per-CNY entered into the # CNY-per-USD override field). Without this band, the override # silently produces wrong USD aggregates that finance trusts. if not ( _CNY_PER_USD_OVERRIDE_MIN < rate < _CNY_PER_USD_OVERRIDE_MAX ): raise RuntimeError( f"Integration override ``{INTEGRATION_OVERRIDE_KEY}`` " f"out of plausible range " f"({_CNY_PER_USD_OVERRIDE_MIN} < rate < " f"{_CNY_PER_USD_OVERRIDE_MAX}); got {rate}. If your " f"finance team genuinely needs an extreme value, edit " f"the band in app.core.fx_rate.", ) return rate async def resolve_cny_per_usd( db: AsyncSession, *, integration_config: dict[str, Any] | None = None, ) -> float: """Resolve CNY-per-USD (≈7.20) for cloud-cost FX conversion. Resolution order: 1. ``integration_config[cny_per_usd]`` (or legacy ``fx_cny_per_usd``) when explicitly set on the integration — lets ops pin a fixed monthly rate per cloud account. 2. Reciprocal of platform ``ai_spending.cny_to_usd_rate``, refreshed weekly by the FX cron. Raises ``RuntimeError`` if neither resolves. Cloud drivers MUST NOT silently default — a wrong rate produces silently wrong USD aggregates that finance later trusts. """ override = _resolve_integration_override(integration_config) if override is not None: return override usd_per_cny = await get_usd_per_cny(db) if usd_per_cny is None: raise RuntimeError( f"CNY/USD rate unavailable: platform setting " f"``{PLATFORM_SETTING_USD_PER_CNY}`` is missing or " f"out of plausible range, and no per-integration " f"``{INTEGRATION_OVERRIDE_KEY}`` override is set. " f"The weekly ``ai_spending.cny_rate_refresh`` cron " f"keeps this current; if it has been failing the " f"failure_notifier should already have alarmed. As " f"a workaround set ``{INTEGRATION_OVERRIDE_KEY}`` on " f"the integration's config_public.", ) # Use Decimal arithmetic for the reciprocal to avoid floating-point # drift accumulating across thousands of cost-fact rows in cloud # billing aggregations. d = Decimal(str(usd_per_cny)) return float((Decimal("1") / d).quantize(Decimal("0.000001"))) class CloudCostUsdConverter: """Read-time CNY→USD corrector for cloud-cost rows. The ``total_cost_usd`` column on ``cmdb_billing_monthly`` and ``cmdb_cloud_billing_daily`` is *misnamed*: CNY-billed tenants (国内阿里云 + 部分腾讯云 / AliCloud-Intl) store the **native CNY** amount in it with ``currency='CNY'``. Any reader that sums the column raw mixes genuine USD with ~7×-inflated CNY-as-USD, so the same period reads differently depending on which endpoint you ask. This helper centralises the per-integration FX correction that ``/billing/overview`` and ``/billing/summary`` already apply inline, so every cloud-spend surface reconciles to one USD figure. Resolution order matches :func:`resolve_cny_per_usd` (per-integration override → platform rate); the rate and the integration ``config_public`` are cached per integration so a wide aggregation issues each lookup once. Usage:: conv = CloudCostUsdConverter(db) usd, converted = await conv.to_usd_checked( row.total_cost_usd, row.currency, row.integration_id, ) # ``converted is False`` → the row has no USD value; drop it. # conv.unconverted_by_currency holds amounts no FX rate covered, # for surfacing as a "couldn't convert" banner. """ def __init__(self, db: AsyncSession) -> None: self._db = db self._rate_cache: dict[str, Decimal] = {} self._cfg_cache: dict[str, dict[str, Any] | None] = {} self.unconverted_by_currency: dict[str, float] = {} async def _config_for(self, integration_id: str) -> dict[str, Any] | None: if integration_id not in self._cfg_cache: # Late import keeps app.core free of an app.integrations dep. import uuid as _uuid # noqa: PLC0415 from app.integrations.models import Integration # noqa: PLC0415 try: integ = await self._db.get(Integration, _uuid.UUID(integration_id)) except (ValueError, TypeError): integ = None self._cfg_cache[integration_id] = ( integ.config_public if integ is not None else None ) return self._cfg_cache[integration_id] async def to_usd( self, raw_amount: Any, currency: str | None, integration_id: Any, ) -> float: """Convert a single cloud-cost row amount to USD. Unsupported currencies (no FX rate resolvable) contribute ``0.0`` to the return and accumulate into :attr:`unconverted_by_currency` so the caller never silently folds an un-converted amount into a USD total. Callers that need to tell "converted to 0.0" apart from "could not convert" MUST use :meth:`to_usd_checked` — a ``0.0`` return here is ambiguous, and inferring failure from a change in :attr:`unconverted_by_currency` is unsound for zero amounts (adding 0 to the accumulator leaves the value untouched). """ usd, _converted = await self.to_usd_checked( raw_amount, currency, integration_id, ) return usd async def to_usd_checked( self, raw_amount: Any, currency: str | None, integration_id: Any, ) -> tuple[float, bool]: """:meth:`to_usd` plus an explicit per-row "did it convert" flag. Returns ``(usd, converted)``. ``converted is False`` means no FX rate covered the row's currency — the amount is NOT expressible in USD and the caller must drop the row rather than emit ``0.0``/``USD`` (a fabricated ``$0`` reads as "this tenant spent nothing", which is a different and wrong statement). This is the only sound success signal: a row with ``raw_amount == 0`` in an unconvertible currency adds 0 to :attr:`unconverted_by_currency`, so watching that dict for a change mis-classifies it as converted. """ cur = (currency or "USD").upper() raw = Decimal(str(raw_amount or 0)) if cur == "USD": return float(raw), True if cur == "CNY": key = str(integration_id) if key not in self._rate_cache: try: rate = await resolve_cny_per_usd( self._db, integration_config=await self._config_for(key), ) self._rate_cache[key] = Decimal(str(rate)) except RuntimeError: self._rate_cache[key] = Decimal("0") rate_dec = self._rate_cache[key] if rate_dec > 0: return float(raw / rate_dec), True self.unconverted_by_currency["CNY"] = ( self.unconverted_by_currency.get("CNY", 0.0) + float(raw) ) return 0.0, False self.unconverted_by_currency[cur] = ( self.unconverted_by_currency.get(cur, 0.0) + float(raw) ) return 0.0, False