"""SQLAlchemy ORM rows for Aliyun Bailian resources.

Schema is created by migration 073. Indexes / constraints documented
on the migration; this file only declares the Python ORM shape.
"""
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,
    Boolean,
    Date,
    DateTime,
    ForeignKey,
    Index,
    Numeric,
    String,
    Text,
    UniqueConstraint,
    text,
)
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 BailianWorkspace(Base):
    """One business workspace (业务空间) under an Aliyun account.

    Synced from Bailian via OpenAPI by the ``bailian.workspace_sync``
    scheduled job. ``integration_id`` ties to the Aliyun cloud
    integration row that owns the credentials we used to discover it.
    """
    __tablename__ = "bailian_workspaces"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint(
            "integration_id", "workspace_id",
            name="uq_bailian_workspace_integration_id",
        ),
        Index("ix_bailian_workspaces_integration", "integration_id"),
    )

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid, primary_key=True, default=uuid.uuid4,
    )
    # Pre-launch hardening 2026-05-06 (migration 124): RESTRICT, not
    # CASCADE — see comment on ``cmdb_ai_resources.integration_id``.
    integration_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("integrations.id", ondelete="RESTRICT"),
        nullable=False,
    )
    workspace_id: Mapped[str] = mapped_column(String(64), nullable=False)
    name: Mapped[str] = mapped_column(String(256), nullable=False)
    description: Mapped[str | None] = mapped_column(Text)
    region: Mapped[str | None] = mapped_column(String(64))
    status: Mapped[str] = mapped_column(
        String(32), nullable=False,
        default="active", server_default=text("'active'"),
    )
    is_default: Mapped[bool] = mapped_column(
        Boolean, nullable=False,
        default=False, server_default=text("0"),
    )
    owner_person_id: Mapped[str | None] = mapped_column(
        String(36),
        ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )
    raw_data: Mapped[dict | None] = mapped_column(JSON)
    first_seen_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False, server_default=func.now(),
    )
    last_synced_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False, server_default=func.now(),
    )
    deleted_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(), onupdate=func.now(),
    )

    # Earlier revisions of this module exposed a ``BailianApplication``
    # model + ``applications`` relationship to mirror the legacy CN
    # ``ListApplications`` API. The international ModelStudio control
    # plane (the only one Helix targets) has no equivalent endpoint —
    # the application catalog lives only in the DashScope data plane.
    # Migration 084 drops the dead ``bailian_applications`` table and
    # the corresponding scheduled_jobs row so the schema honestly
    # reflects what we can actually populate.


class BailianApiKey(Base):
    """Helix-issued Bailian / DashScope API key record.

    The actual secret value (sk-xxx or RAM AccessKey/Secret) is stored
    on the originating ``api_key_assignments.key_value`` row; this
    table only mirrors the Aliyun-side handle so the admin UI can
    show "this key still exists in Bailian" without having to JOIN
    on detail JSON.
    """
    __tablename__ = "bailian_api_keys"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint(
            "integration_id", "api_key_id",
            name="uq_bailian_apikey_integration_apikey",
        ),
        Index("ix_bailian_api_keys_owner", "owner_person_id"),
        Index("ix_bailian_api_keys_workspace", "workspace_pk"),
    )

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid, primary_key=True, default=uuid.uuid4,
    )
    # Pre-launch hardening 2026-05-06 (migration 124): RESTRICT, not
    # CASCADE — see comment on ``cmdb_ai_resources.integration_id``.
    integration_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("integrations.id", ondelete="RESTRICT"),
        nullable=False,
    )
    workspace_pk: Mapped[uuid.UUID | None] = mapped_column(
        Uuid,
        ForeignKey("bailian_workspaces.id", ondelete="SET NULL"),
    )
    api_key_id: Mapped[str] = mapped_column(String(128), nullable=False)
    display_name: Mapped[str] = mapped_column(String(256), nullable=False)
    owner_person_id: Mapped[str | None] = mapped_column(
        String(36),
        ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )
    ram_user_name: Mapped[str | None] = mapped_column(String(255))
    assignment_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid,
        ForeignKey("api_key_assignments.id", ondelete="SET NULL"),
    )
    status: Mapped[str] = mapped_column(
        String(32), nullable=False,
        default="active", server_default=text("'active'"),
    )
    expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    last_used_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    raw_data: Mapped[dict | None] = mapped_column(JSON)
    first_seen_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False, server_default=func.now(),
    )
    last_synced_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False, server_default=func.now(),
    )
    deleted_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(), onupdate=func.now(),
    )


BAILIAN_AGGREGATE_MODEL = "aggregate"
"""``BailianUsageDaily.model_name`` 的**汇总行**取值。

同一天既有逐 model 的明细行，也有一条 ``model_name='aggregate'`` 的
汇总行（历史 ``QueryBillOverview`` 月度口径遗留，见
``bailian/service.py``）。**任何对用量/花费求和的查询都必须排除它**，
否则金额翻倍。

写成常量而不是各处写字面量：2026-08-15 查 AI 大盘百炼显示 $0 时发现
``ai_spending`` 的加载器就漏了这个过滤 —— 只是恰好被另一个 bug（实付
为 0 的行整行跳过，而生产上百炼实付恒为 0）遮住，一旦改口径就会立刻
双计。四处字面量里漏一处不会有任何报错。
"""


class BailianUsageDaily(Base):
    """Per-(workspace × api_key × model × day) token + cost rollup.

    Populated by the ``bailian.usage_sync`` scheduled job which queries
    Aliyun's billing OpenAPI plus the DashScope usage endpoint. The
    composite uniqueness lets the job upsert idempotently when re-run.
    """
    __tablename__ = "bailian_usage_daily"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        # Migration 085 promoted Bailian usage to per-(day × Aliyun
        # member account) granularity. ``QueryAccountBill`` with
        # ``IsGroupByProduct=false`` returns one row per
        # ``(day × OwnerID)`` on the intl Resource Directory tenant,
        # so we now persist that shape instead of summing into a
        # daily aggregate. Per-account rows let the AI-spending
        # dashboard JOIN ``cmdb_cloud_scopes.external_id`` for owner
        # attribution and surface a real chargeback view, instead of
        # the unattributed-daily-blob the BSS aggregate produced.
        # ``aliyun_account_id`` is nullable so future per-workspace
        # / per-api-key rows (if Aliyun ever exposes that API) can
        # land here without forcing a synthetic OwnerID.
        # Migration 299 promoted Bailian usage to per-(day × API key ×
        # model) granularity via BSS ``DescribeInstanceBill`` (whose
        # ``InstanceID`` encodes apiKeyId;workspaceId;model;tokenType;
        # channel). The unique key carries ``bailian_api_key_id`` so the
        # upsert keys on the real chargeback grain.
        UniqueConstraint(
            "integration_id", "usage_date", "aliyun_account_id",
            "bailian_api_key_id", "model_name",
            name="uq_bailian_usage_integ_date_acct_key_model",
        ),
        Index(
            "ix_bailian_usage_workspace_date",
            "workspace_pk", "usage_date",
        ),
        Index(
            "ix_bailian_usage_apikey_date",
            "api_key_pk", "usage_date",
        ),
        Index(
            "ix_bailian_usage_account_date",
            "aliyun_account_id", "usage_date",
        ),
    )

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid, primary_key=True, default=uuid.uuid4,
    )
    # Pre-launch hardening 2026-05-06 (migration 124): RESTRICT on the
    # integration FK to keep this immutable usage ledger from being
    # wiped by a parent delete; SET NULL on the workspace FK so usage
    # rows survive a workspace prune as orphaned-but-preserved
    # billing records (auditors / chargeback / dispute resolution
    # depend on them — losing the row to satisfy a CASCADE is exactly
    # the "history wiped" failure mode the audit flagged).
    integration_id: Mapped[str] = mapped_column(
        String(36),
        ForeignKey("integrations.id", ondelete="RESTRICT"),
        nullable=False,
    )
    workspace_pk: Mapped[uuid.UUID | None] = mapped_column(
        Uuid,
        ForeignKey("bailian_workspaces.id", ondelete="SET NULL"),
        # Nullable so account-level BSS billing rows (the only
        # production-grade granularity Aliyun exposes for Bailian
        # usage as of 2026-04) can land here. Per-workspace rows
        # still have this set when a finer-grain source materialises.
    )
    api_key_pk: Mapped[uuid.UUID | None] = mapped_column(
        Uuid,
        ForeignKey("bailian_api_keys.id", ondelete="SET NULL"),
    )
    # Aliyun member-account ID (the ``OwnerID`` field on BSS
    # ``QueryAccountBill`` rows). For BSS-derived rows this is
    # **always set** — the sync layer emits one row per
    # ``(usage_date × OwnerID)``. NULL is reserved for any future
    # ingest path (DashScope per-workspace, etc.) that doesn't have
    # an Aliyun member-account dimension. The value matches
    # ``cmdb_cloud_scopes.external_id`` on the intl tenant so a
    # plain JOIN attaches the human-readable scope name + scope
    # owner — the dashboard relies on that to render per-account
    # chargeback. No FK is declared because not every OwnerID will
    # have a synced cmdb_cloud_scopes row (a fresh member account
    # might appear in BSS before CMDB has discovered it); the JOIN
    # is OUTER and gracefully degrades.
    aliyun_account_id: Mapped[str | None] = mapped_column(
        String(32),
    )
    # Raw ModelStudio API-key id parsed from the BSS ``DescribeInstanceBill``
    # composite ``InstanceID`` (first field). Kept as a string decoupled
    # from the ``api_key_pk`` FK so spend on keys that live in
    # Resource-Directory MEMBER accounts (which the integration AK can't
    # enumerate via ListApiKeys → ``api_key_pk`` stays NULL) is still
    # recorded and attributable by account. When the key IS one of ours,
    # the sync also resolves ``api_key_pk`` for per-person attribution.
    bailian_api_key_id: Mapped[str | None] = mapped_column(String(128))
    # Raw workspace id from the same composite (2nd field). Member-account
    # workspaces aren't synced into ``bailian_workspaces`` so ``workspace_pk``
    # may be NULL even when this carries a value.
    workspace_id_raw: Mapped[str | None] = mapped_column(String(64))
    usage_date: Mapped[date_type] = mapped_column(Date, nullable=False)
    model_name: Mapped[str] = mapped_column(String(128), nullable=False)
    # Token / request counts: ``0`` means **either** truly zero **or**
    # "unknown — the source API doesn't expose it". The current BSS-
    # billing-based ingest path always writes 0 here (BSS only returns
    # cost in CNY, not token counts). When/if a per-workspace +
    # per-API-key usage API materialises, those rows will populate
    # real values. Consumers must NOT compute ratios on these fields
    # without checking ``raw_data["source"]`` (or whatever
    # discriminator the future ingest path adds) to know which is
    # which. The dashboard surfaces only ``cost_usd`` / ``cost_cny``
    # for this reason.
    input_tokens: Mapped[int] = mapped_column(
        BigInteger, nullable=False, default=0, server_default=text("0"),
    )
    output_tokens: Mapped[int] = mapped_column(
        BigInteger, nullable=False, default=0, server_default=text("0"),
    )
    request_count: Mapped[int] = mapped_column(
        BigInteger, nullable=False, default=0, server_default=text("0"),
    )
    cost_usd: Mapped[Decimal] = mapped_column(
        Numeric(14, 6), nullable=False,
        default=Decimal("0"), server_default=text("0"),
    )
    cost_cny: Mapped[Decimal] = mapped_column(
        Numeric(14, 6), nullable=False,
        default=Decimal("0"), server_default=text("0"),
    )
    # 折扣前(原价)成本,与上面的折后 cost_cny / cost_usd 成对。
    # CNY = BSS ``PretaxGrossAmount``(QueryAccountBill 原始 item);
    # USD 用与该行 cost_usd 相同的冻结汇率派生。可空:无原价口径的
    # ingest 路径(未来 DashScope 等)落库时留 NULL,大盘按覆盖率处理。
    cost_usd_gross: Mapped[Decimal | None] = mapped_column(Numeric(14, 6))
    cost_cny_gross: Mapped[Decimal | None] = mapped_column(Numeric(14, 6))
    # 优惠后(= 原价 − 优惠金额,代金券之前)。三档价格的中间档:
    # gross(原价) → after_discount(优惠后) → cost(应付/用券后,net)。
    cost_usd_after_discount: Mapped[Decimal | None] = mapped_column(Numeric(14, 6))
    cost_cny_after_discount: Mapped[Decimal | None] = mapped_column(Numeric(14, 6))
    raw_data: Mapped[dict | None] = mapped_column(JSON)
    synced_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False, server_default=func.now(),
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(), onupdate=func.now(),
    )


class LlmCallLedger(Base):
    """One row per LLM call Helix itself made.

    The platform bills the whole company for AI spending while keeping
    no record of its own consumption: of the twenty-nine modules that
    call the shared Bailian client, two recorded tokens. This table is
    the missing half — see :mod:`app.domains.bailian.ledger` for why it
    holds tokens rather than money, and why it is content-free.

    **No prompt or completion text is ever stored here.** That is a
    property of the schema, not a convention: there is no column to put
    it in. An ops table that accumulated support-ticket text or bounty
    reports would become a disclosure surface nobody signed up for.

    Rows are append-only and never updated. The natural query is
    "tokens by purpose over a window", so the indexes lead with
    ``called_at`` (every read is time-bounded) and pair it with
    ``purpose`` and ``model``.
    """

    __tablename__ = "llm_call_ledger"
    __table_args__ = (
        # Every read is time-bounded, so time leads both composites.
        Index("ix_llm_ledger_called_purpose", "called_at", "purpose"),
        Index("ix_llm_ledger_called_model", "called_at", "model"),
        # "What is failing right now" — a partial-ish scan on the
        # failure path, which is rare enough that a dedicated index
        # earns its keep only when combined with time.
        Index("ix_llm_ledger_ok_called", "ok", "called_at"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)

    #: Feature slug, e.g. ``support.copilot``. Never NULL: a call with
    #: no declared purpose books under ``unattributed`` so the ledger's
    #: totals stay reconcilable against the vendor bill.
    purpose: Mapped[str] = mapped_column(String(64), nullable=False)

    #: The model actually called, resolved — not the configured value,
    #: which is NULL whenever the default applies.
    model: Mapped[str] = mapped_column(String(64), nullable=False)
    region: Mapped[str | None] = mapped_column(String(32))

    #: Which client entry point: ``chat``, ``chat_json``, ``chat_text``,
    #: ``chat_stream`` or ``embed``. Kept separate from ``purpose``
    #: because a single feature legitimately uses several.
    operation: Mapped[str] = mapped_column(String(16), nullable=False)

    ok: Mapped[bool] = mapped_column(Boolean, nullable=False)

    #: NULL means "the vendor did not report it", which is not the same
    #: as zero and must not be summed as if it were.
    input_tokens: Mapped[int | None] = mapped_column(BigInteger)
    output_tokens: Mapped[int | None] = mapped_column(BigInteger)

    duration_ms: Mapped[int | None] = mapped_column(BigInteger)

    #: Our error code (``BAILIAN_LLM_*``) or a normalised vendor one.
    #: Never the vendor's free-text message: the async media family
    #: quotes rejected prompts back inside those strings, which would
    #: reintroduce exactly the content this table refuses to hold.
    error_code: Mapped[str | None] = mapped_column(String(64))

    trace_id: Mapped[str | None] = mapped_column(String(64))

    #: Who was on the request, when there was one. NULL for cron work,
    #: which is most of it.
    actor_person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )

    called_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )