"""ORM rows owned by the ai_spending domain. Today: just :class:`AiSpendingSummary`. The two CMDB-side AI ledger tables (``cmdb_ai_resources`` + ``cmdb_ai_resource_cost_daily``) live in :mod:`app.domains.cmdb.models` because they are populated by extending the cloud driver's CMDB sync surface — same domain ownership rule as every other ``cmdb_*`` mirror table. """ from __future__ import annotations import uuid from datetime import date as date_type from datetime import datetime from decimal import Decimal from sqlalchemy import ( JSON, BigInteger, Date, DateTime, ForeignKey, Index, Integer, Numeric, String, Text, UniqueConstraint, ) from sqlalchemy.orm import Mapped, mapped_column from sqlalchemy.sql import func from app.core.database import Base from app.core.sql_types import GUID as Uuid class AiSpendingSummary(Base): """Cached LLM-generated period summary for the AI spending board. One row per ``(period, language)`` — the auto-job emits ``language='bilingual'`` with both ``summary_md_en`` and ``summary_md_zh`` populated; the manual "regenerate" path also writes a single bilingual row by default. The caller is expected to format the row into the API response by selecting EN / ZH at request time (display follows the requesting user's preferred_language; tooltip shows the other language to satisfy the "show English, hover for Chinese" product spec). """ __tablename__ = "ai_spending_summaries" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( UniqueConstraint( "period", "language", name="uq_ai_spending_summaries_period_lang", ), ) id: Mapped[uuid.UUID] = mapped_column( Uuid, primary_key=True, default=uuid.uuid4, ) period: Mapped[str] = mapped_column(String(7), nullable=False) # 'bilingual' = both languages in one row (default). # 'en' / 'zh' = single-language row, kept for the future case # where prompt-token economics push us to per-language calls. # NB: column is String(16) — the original String(8) silently # rejected the 9-char default value 'bilingual' on MySQL strict # mode, which made the regenerate flow fail with a 500 every time # (verified 2026-04-29). language: Mapped[str] = mapped_column(String(16), nullable=False) summary_md_en: Mapped[str | None] = mapped_column(Text) summary_md_zh: Mapped[str | None] = mapped_column(Text) findings: Mapped[list | None] = mapped_column(JSON) suggestions: Mapped[list | None] = mapped_column(JSON) aggregates: Mapped[dict | None] = mapped_column(JSON) model_used: Mapped[str | None] = mapped_column(String(128)) input_token_count: Mapped[int | None] = mapped_column(Integer) output_token_count: Mapped[int | None] = mapped_column(Integer) # ``Uuid`` (= GUID = CHAR(36)) to match the CHAR(36)-stored # ``identity_persons.id`` PK. Migration 079 creates this column # as CHAR(36); using ``String(36)`` here would diverge between # the test ``create_all`` path (VARCHAR) and Alembic (CHAR), so # tests would exercise a different schema than prod. generated_by_user_id: Mapped[str | None] = mapped_column( Uuid, ForeignKey("identity_persons.id", ondelete="SET NULL"), ) generated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False, ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False, ) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False, ) class AiSpendingPeriodRollup(Base): """Materialised, BU-agnostic ``BucketRow`` set for one period window. The dashboard derives EVERYTHING (KPIs / breakdown / movers / by-bu / attribution / drill) from ``_collect_period_rows`` — one canonical ``list[BucketRow]`` per ``(period_start, period_end_excl)``. Computing it scans the ~2.7M-row ``cmdb_cost_facts`` on every request; for a **closed** month that result is immutable, so we persist it here and serve closed-month reads from this table instead of re-scanning. One row == one ``BucketRow`` (this is a persisted projection, NOT a pre-aggregated total — the dashboard's Python aggregation still runs on the materialised rows, so every breakdown dimension stays available). Grain therefore matches the loaders: per fact for the cost-facts buckets, per (day × account) for Bailian, per allocation for seats. Volume per AI-classified month is modest (hundreds–low thousands) since the cost-facts loader only keeps whitelisted AI services. Freshness contract: * ``whitelist_fingerprint`` pins the inputs that change what a live recompute would return: the AI service whitelist, the CNY→USD FX rate, and a semantics version. A read only trusts rows whose fingerprint matches the current one; a mismatch (admin edited the whitelist or the FX rate moved) is a miss → recompute. FX is included because the seats bucket converts CNY plan prices at the LIVE rate on every compute (Bailian / cost_facts USD are frozen at ingest, but the per-window fingerprint spans all buckets, so FX is included to keep the rollup from diverging from a live recompute). * A window is the unit of replacement: refresh = DELETE the whole ``(period_start, period_end_excl)`` (all fingerprints) then bulk INSERT the freshly computed rows in one transaction. * Only **closed** months are persisted. The open/current month changes intraday as syncs land, so it stays on the 60s in-process cache and is never written here (the refresh cron re-materialises the just-closed month to absorb late-arriving facts + pre-warm). No FK / no ``identity_persons`` link — this is a rebuildable cache, and a BigInteger PK + no FK keeps it immune to the TiDB CHAR/VARCHAR FK strictness that bites the linked tables. """ __tablename__ = "ai_spending_period_rollup" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( # The one hot read path: look up a window's rows for the current # fingerprint. Covers the WHERE on every closed-month read. Index( "ix_ai_rollup_window_fp", "period_start", "period_end_excl", "whitelist_fingerprint", ), ) id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True) period_start: Mapped[date_type] = mapped_column(Date, nullable=False) period_end_excl: Mapped[date_type] = mapped_column(Date, nullable=False) whitelist_fingerprint: Mapped[str] = mapped_column(String(64), nullable=False) # ── Persisted BucketRow fields (mirror service.BucketRow) ── bucket: Mapped[str] = mapped_column(String(32), nullable=False) fact_date: Mapped[date_type] = mapped_column(Date, nullable=False) cost_usd: Mapped[Decimal] = mapped_column(Numeric(14, 6), nullable=False) cost_usd_gross: Mapped[Decimal | None] = mapped_column(Numeric(14, 6)) cost_usd_after_discount: Mapped[Decimal | None] = mapped_column(Numeric(14, 6)) user_id: Mapped[str | None] = mapped_column(String(36)) department: Mapped[str | None] = mapped_column(String(255)) auto_owner_source: Mapped[str | None] = mapped_column(String(32)) software_id: Mapped[str | None] = mapped_column(String(36)) plan_name: Mapped[str | None] = mapped_column(String(255)) model_name: Mapped[str | None] = mapped_column(String(128)) integration_id: Mapped[str | None] = mapped_column(String(36)) scope_id: Mapped[str | None] = mapped_column(String(36)) resource_group: Mapped[str | None] = mapped_column(String(512)) iam_user: Mapped[str | None] = mapped_column(String(255)) service_name: Mapped[str | None] = mapped_column(String(255)) aliyun_account_id: Mapped[str | None] = mapped_column(String(32)) resource_id: Mapped[str | None] = mapped_column(Text) # BucketRow.raw drill/LLM context (input_tokens, workspace_label, …) # so a rollup-served window keeps full drill fidelity. raw_json: Mapped[dict | None] = mapped_column(JSON) computed_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False, )