"""Conversation record for the assistant channel.

Two tables, one purpose: an administrator must be able to answer "what did the
assistant tell this employee, and what did it look at to say that" months later.
That is why the transcript lives here rather than in ``audit_logs`` — the audit
spine records that a turn happened, this records what the turn was, with its own
shape (tool calls, token cost, latency) and its own retention story.

Every row is written by the server from the turn it just ran. Nothing here is
supplied by the model or by the employee's message text beyond the message text
itself, which is stored verbatim so an audit is an audit and not a paraphrase.
"""
from __future__ import annotations

import uuid
from datetime import datetime

from sqlalchemy import (
    JSON,
    Boolean,
    DateTime,
    Float,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    UniqueConstraint,
    func,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.core.database import Base
from app.core.sql_types import GUID as Uuid

CHANNEL_FEISHU = "feishu"

#: 私聊 —— 只有员工本人读得到这一轮。
CHAT_KIND_DM = "dm"
#: Helix 自己为「转人工」建的工单群。
CHAT_KIND_HANDOFF = "handoff"
#: 别人建的群,机器人被拉了进去。
CHAT_KIND_GROUP = "group"
#: The employee assistant rendered as a web page (``/me/support``) rather than
#: as a Feishu chat. Same kernel, same tools, same transcript table — the value
#: exists so an administrator reading a transcript knows which surface the
#: employee was looking at, and so the funnel can tell the two apart.
CHANNEL_WEB = "web"

ROLE_USER = "user"
ROLE_ASSISTANT = "assistant"

OUTCOME_ANSWERED = "answered"
OUTCOME_UNAVAILABLE = "unavailable"
OUTCOME_FAILED = "failed"

STATUS_PENDING = "pending"
STATUS_CONFIRMED = "confirmed"
STATUS_EXECUTED = "executed"
STATUS_FAILED = "failed"
STATUS_EXPIRED = "expired"
STATUS_CANCELLED = "cancelled"


class AssistantConversation(Base):
    """One continuous thread between a person and the assistant in one chat."""

    __tablename__ = "assistant_conversations"
    __table_args__ = (
        Index("idx_assistant_conv_person", "person_id", "last_message_at"),
        Index("idx_assistant_conv_chat", "channel", "chat_id"),
        Index("ix_assistant_conversation_service_request", "service_request_id"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    person_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"), nullable=False, index=True,
    )
    service_request_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("support_service_requests.id"), nullable=True,
    )
    channel: Mapped[str] = mapped_column(String(32), nullable=False, default=CHANNEL_FEISHU)
    chat_id: Mapped[str | None] = mapped_column(String(128))
    #: 这段对话发生在哪种房间:``dm`` 私聊 / ``handoff`` Helix 建的工单群 /
    #: ``group`` 别人的群。
    #:
    #: 为什么不用「``chat_id`` 在 handoff 表里查得到就是工单群」来推:任意群
    #: **压根不在任何表里**,于是它和私聊在数据上长得一模一样 —— 而两者的成本、
    #: 风险和阅读方式都不同(一句私聊的回答只有一个人看见,一句群里的回答有一屋
    #: 子人看见)。审计面必须分得开,所以它是一列,不是一次 join。
    chat_kind: Mapped[str] = mapped_column(
        String(16), nullable=False, default=CHAT_KIND_DM, server_default=CHAT_KIND_DM,
    )
    turn_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    started_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    last_message_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )

    messages: Mapped[list[AssistantMessage]] = relationship(
        back_populates="conversation", cascade="all, delete-orphan",
    )


class AssistantMessage(Base):
    """One message in a conversation — the employee's, or the assistant's reply.

    An assistant row carries the evidence for its own answer: which tools ran,
    which provider produced it, and how long it took. Deliberately no model-name
    column: the kernel does not report one, and a column nothing ever writes is
    a schema that lies to whoever reads it next.
    """

    __tablename__ = "assistant_messages"
    __table_args__ = (
        # UNIQUE, not merely indexed (migration 519). ``seq`` is assigned from
        # ``conversation.turn_count`` under a Redis turn lock that FAILS OPEN, as
        # does the event-dedup claim — so a Redis outage lets two concurrent
        # turns read the same count and write the same pair of sequences. With a
        # plain index the database accepted that and the transcript silently
        # interleaved, with nothing afterwards able to say which reply answered
        # which question. This is the half of it the database can own.
        UniqueConstraint(
            "conversation_id", "seq", name="uq_assistant_msg_conv_seq",
        ),
        Index("idx_assistant_msg_created", "created_at"),
        Index("ix_assistant_message_service_request", "service_request_id"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    conversation_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("assistant_conversations.id"), nullable=False,
    )
    service_request_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("support_service_requests.id"), nullable=True,
    )
    #: Position within the conversation, assigned by the server. Two messages
    #: of one turn land in the same clock second, so ``created_at`` alone
    #: cannot order a transcript — and an out-of-order transcript is unusable
    #: as an audit record.
    seq: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    role: Mapped[str] = mapped_column(String(16), nullable=False)
    content: Mapped[str] = mapped_column(Text, nullable=False)
    #: Tool names the model actually invoked for this reply, in call order.
    tool_calls: Mapped[list | None] = mapped_column(JSON)
    #: The mirrored documents this answer cited, as ``[{title, url}]`` (migration
    #: 518). Stored because the answer card renders them and a 👍 tap REPLACES
    #: that card — rebuilt from ``content`` alone, rating an answer deleted its
    #: own sources. ``None`` on rows written before the column existed.
    sources: Mapped[list | None] = mapped_column(JSON)
    outcome: Mapped[str | None] = mapped_column(String(32))
    provider: Mapped[str | None] = mapped_column(String(32))
    latency_ms: Mapped[int | None] = mapped_column(Integer)
    trace_id: Mapped[str | None] = mapped_column(String(64))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )

    conversation: Mapped[AssistantConversation] = relationship(back_populates="messages")


#: A two-value verdict on purpose. A five-star scale collects shrugs; the only
#: signal worth acting on is "this did not work for me".
VERDICT_HELPFUL = "helpful"
VERDICT_UNHELPFUL = "unhelpful"

# A negative rating is a work item, not a terminal event.  Keeping the state
# on the feedback row makes the queue auditable and lets a later sweep remind
# the right people without manufacturing duplicate tickets.
GAP_OPEN = "open"
GAP_IN_PROGRESS = "in_progress"
GAP_RESOLVED = "resolved"
GAP_IGNORED = "ignored"


class AssistantMessageFeedback(Base):
    """Whether one employee found one answer useful.

    Deflection counted tickets NOT filed, which measures avoidance: an employee
    who gave up looks exactly like one who got their answer. This is the only
    record that distinguishes them, and it is keyed to the message so a bad
    answer can be read back with the question that produced it and the tools it
    ran — which is what turns a complaint into a fixable knowledge gap.
    """

    __tablename__ = "assistant_message_feedback"
    __table_args__ = (
        UniqueConstraint(
            "message_id", "person_id", name="uq_assistant_feedback_message_person",
        ),
        Index("ix_assistant_feedback_verdict_created", "verdict", "created_at"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    message_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("assistant_messages.id", ondelete="CASCADE"), nullable=False,
    )
    person_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"), nullable=False,
    )
    verdict: Mapped[str] = mapped_column(String(16), nullable=False)
    reason_code: Mapped[str | None] = mapped_column(String(32))
    classification: Mapped[str | None] = mapped_column(String(32))
    note: Mapped[str | None] = mapped_column(Text)
    trace_id: Mapped[str | None] = mapped_column(String(64))
    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(),
    )
    gap_status: Mapped[str] = mapped_column(
        String(20), nullable=False, default=GAP_OPEN, server_default=GAP_OPEN,
    )
    assigned_person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"), nullable=True,
    )
    resolved_by_person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"), nullable=True,
    )
    resolved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    resolution_note: Mapped[str | None] = mapped_column(Text)
    #: What was actually wrong — one of :data:`app.assistant.gap_verification.FIX_TYPES`.
    #: "Fixed it" is not a category: a missing document, a retrieval problem, a
    #: tool returning the wrong field, a broken action, and "the product does not
    #: do this" each have a different follow-up, and collapsing them loses the
    #: only information that makes this queue worth keeping.
    fix_type: Mapped[str | None] = mapped_column(String(32))
    #: The published doc, the change, the tool — whatever the fix landed in.
    #: Required for every fix type except ``out_of_scope``, where the evidence
    #: for "we decided not to" is the decision itself.
    evidence_ref: Mapped[str | None] = mapped_column(String(255))
    #: Whether the last replay of the original question passed. ``None`` means
    #: nobody has re-asked it — which is why it is nullable rather than
    #: defaulting to ``False``: "not verified" and "verified and it failed" are
    #: different states and only one of them is somebody's fault.
    verification_passed: Mapped[bool | None] = mapped_column(Boolean)
    verification_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    #: Shape of the turn before and after the fix — tools called, sources cited,
    #: outcome. Deliberately NOT the reply text: that is the asker's own data
    #: rendered for them, and keeping it here would turn a triage queue into a
    #: second copy of everybody's personal information.
    verification_before: Mapped[dict | None] = mapped_column(JSON)
    verification_after: Mapped[dict | None] = mapped_column(JSON)
    #: A question that failed, was fixed, and now passes. Kept as a fixed set so
    #: the next change to retrieval or a tool can be checked against every gap
    #: that was ever closed rather than against whichever one is remembered.
    in_regression_set: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default="0",
    )
    last_reminded_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    reminder_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0")


class AssistantMessageFeedbackEvent(Base):
    """Append-only employee feedback history; the feedback row is current state."""

    __tablename__ = "assistant_message_feedback_events"
    __table_args__ = (
        Index("ix_assistant_feedback_event_message", "message_id", "created_at"),
        Index("ix_assistant_feedback_event_verdict", "verdict", "created_at"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    message_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("assistant_messages.id", ondelete="CASCADE"), nullable=False,
    )
    person_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"), nullable=False,
    )
    verdict: Mapped[str] = mapped_column(String(16), nullable=False)
    reason_code: Mapped[str | None] = mapped_column(String(32))
    classification: Mapped[str | None] = mapped_column(String(32))
    trace_id: Mapped[str | None] = mapped_column(String(64))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )


class AssistantActionProposal(Base):
    """A mutation the assistant offered, inert until the employee confirms it.

    The row IS the offer: parameters are validated and frozen here at propose
    time, and the confirm step re-reads them rather than asking the model again.
    ``params_hash`` is what the confirmation view renders and the confirm call
    must echo, so "you approved what you saw" is checked rather than assumed.
    """

    __tablename__ = "assistant_action_proposals"
    __table_args__ = (
        Index("idx_assistant_prop_person", "person_id", "created_at"),
        Index("idx_assistant_prop_status", "status", "expires_at"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    person_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"), nullable=False,
    )
    conversation_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("assistant_conversations.id"),
    )
    action_key: Mapped[str] = mapped_column(String(64), nullable=False)
    params: Mapped[dict | None] = mapped_column(JSON)
    params_hash: Mapped[str] = mapped_column(String(64), nullable=False)
    status: Mapped[str] = mapped_column(
        String(16), nullable=False, default=STATUS_PENDING,
    )
    result: Mapped[dict | None] = mapped_column(JSON)
    trace_id: Mapped[str | None] = mapped_column(String(64))
    expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    confirmed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    # ── attempt visibility ───────────────────────────────────────────
    #
    # A failed execution is rolled back to ``pending`` on purpose (the card
    # stays pressable, the employee can retry). The cost of that choice was
    # that "pressed and it failed" became indistinguishable from "never
    # pressed": ``status`` is pending and ``confirmed_at`` is cleared in both
    # cases. In production all 14 proposals ever created read as unexecuted,
    # and two of them had in fact been pressed — the difference was buried in
    # a JSON blob nobody counts.
    #
    # These three columns are the difference, in queryable form. They are
    # counters and stamps, not a second status: the status machine is
    # unchanged and still moves forward only.
    attempt_count: Mapped[int] = mapped_column(
        Integer, nullable=False, server_default="0", default=0,
    )
    last_attempted_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
    )
    last_error_code: Mapped[str | None] = mapped_column(String(64))
    #: Why a *successful* execution produced no object link. Counting these
    #: is how "the contract has a gap" stays visible instead of looking like
    #: "nothing was submitted". See :mod:`app.assistant.outcome_contract`.
    link_miss_reason: Mapped[str | None] = mapped_column(String(32))


class AssistantObjectLink(Base):
    """The pointer from "the assistant submitted it" to the row that proves it.

    Executing an action tells you the call returned. It does not tell you the
    request was approved, provisioned, or that the provider ever applied it —
    and those are the only things the employee was ever asking about. This row
    is what makes the second question answerable: it names the business object
    the action created, so status can be read from the domain that owns it.

    Three deliberate properties:

    * **A pointer, not a copy.** ``phase`` is a cache for funnel queries and is
      refreshed from the source; it is never the answer given to an employee.
      Copying domain state here would create a second state machine to keep in
      sync, and a stale copy that reads "approved" is worse than no copy.
    * **No foreign key to the target.** ``resource_type`` spans nine tables
      across as many domains; a polymorphic pair is the honest shape. The
      trade-off is accepted knowingly: nothing at the database level stops a
      dangling pointer, so the resolver must tolerate a row that is gone (an
      employee request deleted by a purge job) and say so rather than crash.
    * **``person_id`` is stored, and is not an authorisation.** It records who
      the action ran as, so the resolver has a subject to re-check against.
      Holding the link never implies permission to read the target — that is
      re-derived per read, in the domain's own terms.
    """

    __tablename__ = "assistant_object_links"
    __table_args__ = (
        # Replay safety: a duplicate Feishu callback, a retried confirm, or a
        # re-run resolver must land on the same row rather than a second one.
        UniqueConstraint(
            "proposal_id", "resource_type", "resource_id",
            name="uq_assistant_link_proposal_resource",
        ),
        Index("idx_assistant_link_person", "person_id", "created_at"),
        Index("idx_assistant_link_resource", "resource_type", "resource_id"),
        Index("idx_assistant_link_request", "service_request_id"),
        Index("idx_assistant_link_phase", "phase", "phase_at"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    proposal_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("assistant_action_proposals.id"), nullable=False,
    )
    # Denormalised from the proposal's conversation at link time. Both are the
    # context an employee means by "that request I made earlier", and carrying
    # them here keeps the follow-up read a single indexed lookup instead of a
    # three-table join on the assistant's hot path.
    conversation_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("assistant_conversations.id"),
    )
    service_request_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("support_service_requests.id"),
    )
    person_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"), nullable=False,
    )
    action_key: Mapped[str] = mapped_column(String(64), nullable=False)
    #: The MCP tool that produced the payload, when one did. Equal to
    #: ``action_key`` for a derived action; different where a curated action
    #: delegates (buyback runs ``submit_approval_request``). NULL for a curated
    #: action that calls a domain service directly.
    source_tool: Mapped[str | None] = mapped_column(String(64))

    resource_type: Mapped[str] = mapped_column(String(48), nullable=False)
    resource_id: Mapped[str] = mapped_column(String(64), nullable=False)
    resolver_key: Mapped[str] = mapped_column(String(48), nullable=False)
    #: What the employee would say out loud — "REQ-1042". Absent for the
    #: actions whose domain has no human-facing number.
    resource_number: Mapped[str | None] = mapped_column(String(64))
    #: Composed by the server from a template, never authored by the model.
    deep_link: Mapped[str | None] = mapped_column(String(255))
    #: Set when one id stands for a batch (cloud renewal covers N grants), so
    #: the projection cannot report a batch complete on the strength of one row.
    covers_count: Mapped[int | None] = mapped_column(Integer)

    #: Last projected lifecycle phase. A cache — see the class docstring.
    phase: Mapped[str | None] = mapped_column(String(24))
    phase_detail: Mapped[dict | None] = mapped_column(JSON)
    phase_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(),
    )


class AssistantEscalationRoute(Base):
    """「转人工」把这个问题拉给谁 —— 一张后台可配的路由表。

    ## 为什么是表而不是代码

    路由是**运营数据**:谁负责权限、谁负责云,会随人员变动而变,而且改一次不该
    要一次发布。把「孟凡生管权限/软件/制度/入离职,冯帅管云/安全/平台」写进代码,
    等于让每次交接都变成一个 PR。

    ## 分类由 AI 定,路由由这张表定

    两件事刻意分开:模型只负责把问题**归到一个已存在的 category_key** 上(闭集,
    不允许它发明新类),这张表负责「这一类找谁」。所以模型判错的后果是「找错了
    人」——一个人能一眼看出并改派的错;而不是「凭空产生一个没人负责的类别」。

    ## owner 允许为空

    一条启用了但还没指派负责人的路由是**正常状态**(刚建好、上一任刚离职)。
    这种情况下「转人工」降级成建工单 + 告诉员工 IT 会接手 —— 不能拉一个空群,
    也不能假装拉到了人。
    """

    __tablename__ = "assistant_escalation_routes"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    #: 模型可选的闭集里的一个键(``permission`` / ``cloud`` / …)。稳定标识,
    #: 不随显示名改动 —— 分类审计里存的是它。
    category_key: Mapped[str] = mapped_column(String(48), nullable=False, unique=True)
    #: 给人读的名字,三语 ``{"zh": …, "en": …, "ja": …}``。**存本地化字典**:
    #: 群名要用员工自己的语言,而这一行会活很久。
    display_name: Mapped[dict] = mapped_column(JSON, nullable=False)
    #: 喂给模型的一句话,说明这一类都包含什么。分类质量几乎全靠它,所以它是
    #: 后台可编辑的内容,不是代码里的常量。
    hint: Mapped[str | None] = mapped_column(Text)
    #: 备份负责人。主负责人**今天在休假**、或者他压根没有可用的飞书账号时,
    #: 拉这个人。
    #:
    #: 为什么需要它:转人工的价值上限是「快」。主负责人休假两周,那两周里
    #: 每一次「转人工」都会把员工和一个不会回话的人关进一个房间 —— 那比降级成
    #: 只建工单更糟,因为员工会**以为**有人在看。
    backup_owner_person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    #: 拉进群的那个人。允许为空 —— 见类文档。
    owner_person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    #: 兜底路由:模型给不出类别、或给出的类别没启用时走它。恰好一条为真,
    #: 由服务层保证(改一条为真会把其余置假)。
    is_default: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default="0",
    )
    enabled: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default="1",
    )
    sort_order: Mapped[int] = mapped_column(
        Integer, nullable=False, server_default="100",
    )
    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 AssistantHandoff(Base):
    """一次「转人工」的台账行。

    存在的理由是**可追溯**和**可改派**:一个群拉起来之后,「这件事是谁转的、
    转给谁了、模型当时判成什么类、置信度多少」必须能查 —— 否则路由质量无从评估,
    改派也无从下手。

    同时它是**幂等键**:飞书会重投它判为慢的回调,而建群是不可撤销的外部动作。
    ``message_id`` 上的唯一约束让第二次点击(或第二次回调)拿到同一个群,而不是
    第二个群。
    """

    __tablename__ = "assistant_handoffs"

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    #: 被转交的那条助手回复。唯一 —— 见类文档的幂等说明。
    message_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("assistant_messages.id"), nullable=False, unique=True,
    )
    person_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"), nullable=False, index=True,
    )
    route_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("assistant_escalation_routes.id"),
    )
    #: 模型判的类别与置信度。存下来才能回答「路由准不准」——而那是这套东西
    #: 唯一值得优化的指标。
    classified_category: Mapped[str | None] = mapped_column(String(48))
    classified_confidence: Mapped[float | None] = mapped_column(Float)
    #: 模型给的一句理由。只用于人工复核,不参与任何判断。
    classified_reason: Mapped[str | None] = mapped_column(Text)
    #: 建出来的飞书群。为空 = 降级路径(没有负责人,或建群失败),此时
    #: ``ticket_id`` 是唯一的落点。
    feishu_chat_id: Mapped[str | None] = mapped_column(String(64))
    #: 一定会有的那条工单:群里的活也必须在台账上有一行,否则它就是一件
    #: 没人能查、没有 SLA、交接就消失的活。
    ticket_id: Mapped[uuid.UUID | None] = mapped_column(Uuid)
    #: 降级原因(``no_owner`` / ``chat_failed`` / ``disabled``),为空表示走通了。
    degraded_reason: Mapped[str | None] = mapped_column(String(32))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )


#: 群还在,人在里面聊。
CHAT_STATE_OPEN = "open"
#: 群已经解散(或机器人被移出)—— 往它发的任何消息都会失败。
CHAT_STATE_CLOSED = "closed"

#: 这个群是「转人工」自动拉起来的。
CHAT_REASON_HANDOFF = "handoff"
#: 求助人自己从工单详情页开的。
CHAT_REASON_REQUESTER = "requester"
#: 处理人从后台工单页开的。
CHAT_REASON_AGENT = "agent"


class AssistantTicketChat(Base):
    """一张工单的飞书群 —— 工单的第二个面。

    ## 为什么它不是 ``assistant_handoffs`` 上的一列

    `assistant_handoffs` 记的是**一次事件**:某条助手回复被转人工了,模型判成
    什么类、拉没拉起群。那一行写完就不再变,它是评估路由质量的原始记录。

    而群是一个**有生命周期的对象**:它会被加人、会挂着一张需要原地刷新的卡片、
    会在工单结案之后被解散。更要紧的是,群不再只从「转人工」来 —— 求助人可以在
    工单详情页开一个,处理人可以在后台开一个。把这些塞进 handoff 行,就得先造一
    个「没有 message_id 的 handoff」,而那是一句谎话。

    所以:**这张表是「工单 ↔ 群」的唯一注册表**,`assistant_handoffs.feishu_chat_id`
    退化成那次转人工的历史记录(「当时拉起来了吗」),不再被任何链路当作查找依据。

    ## 一张工单最多一个群

    `ticket_id` 唯一。第二次点「开群」拿到的是同一个群 —— 建群是不可撤销的外部
    动作,而两个讲同一件事的房间会让参与的人各说各话,最后没有一个房间是完整的。
    """

    __tablename__ = "assistant_ticket_chats"
    __table_args__ = (
        Index("idx_assistant_ticket_chat_state", "state", "last_activity_at"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    #: 这个群讲的是哪张工单。唯一 —— 见类文档。
    ticket_id: Mapped[uuid.UUID] = mapped_column(Uuid, nullable=False, unique=True)
    #: 飞书群 id。也唯一:一个房间不可能同时是两张工单的群。
    feishu_chat_id: Mapped[str] = mapped_column(
        String(64), nullable=False, unique=True,
    )
    #: 群里那张工单卡的消息 id。卡片靠 ``PATCH /im/v1/messages/{id}`` 原地换状态,
    #: 所以这一列是「群里那张卡还能不能跟着工单变」的全部依据;拿不到就为空,
    #: 此时状态变化退化成群里的一条文字消息(说得出来,只是不好看)。
    card_message_id: Mapped[str | None] = mapped_column(String(64))
    state: Mapped[str] = mapped_column(
        String(16), nullable=False, default=CHAT_STATE_OPEN,
        server_default=CHAT_STATE_OPEN,
    )
    #: 谁开的、为什么开。运营台按它回答「群都是怎么来的」。
    opened_by_person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    opened_reason: Mapped[str] = mapped_column(
        String(16), nullable=False, default=CHAT_REASON_HANDOFF,
        server_default=CHAT_REASON_HANDOFF,
    )
    #: 群里最后一次有人跟助手说话。保留策略(工单关了 N 天后解散)和「这个群
    #: 是不是已经没人管了」都读它。
    last_activity_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
    )
    closed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    #: ``disbanded`` / ``bot_removed`` / ``ticket_closed`` —— 群为什么不在了。
    close_reason: Mapped[str | None] = mapped_column(String(32))
    #: 这个房间已经因为 SLA 催了多少轮还没动而被**升级**过一次(把备份负责人或
    #: IT 管理员拉进来)。
    #:
    #: 一列而不是「数一数群里有几个人」:升级是一件只该发生一次的事,而「群里
    #: 有三个人」可能是因为有人手动拉了同事进来。没有这一列的话,每一次催办
    #: 都会再拉一次人 —— 一个房间会慢慢变成整个 IT 团队。
    escalated_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(),
    )