code/ai_finance_agent/backend/app/domain/models.py

"""字段 comment 作为数据字典来源；历史记录用外键保留而非级联删除。"""
from datetime import datetime
from typing import Any
from sqlalchemy import Boolean, DateTime, ForeignKey, Integer, JSON, String, Text, UniqueConstraint
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from app.shared.common import utcnow


class Base(DeclarativeBase):
    pass


class Identity:
    id: Mapped[int] = mapped_column(primary_key=True, comment="本地整数主键，不接受客户端指定")
    created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, comment="创建时间；保存 UTC 以便跨时区排序")


class Department(Identity, Base):
    __tablename__ = "department"
    name: Mapped[str] = mapped_column(String(100), unique=True, comment="部门名称；唯一，避免统计重复归类")
    active: Mapped[bool] = mapped_column(Boolean, default=True, comment="停用标记；保留历史外键")


class Role(Identity, Base):
    __tablename__ = "role"
    code: Mapped[str] = mapped_column(String(20), unique=True, comment="五种固定角色代码")
    display_name: Mapped[str] = mapped_column(String(30), comment="中文角色名称")


class User(Identity, Base):
    __tablename__ = "sys_user"
    username: Mapped[str] = mapped_column(String(50), unique=True, comment="登录名；唯一且不含密码")
    real_name: Mapped[str] = mapped_column(String(80), comment="显示姓名")
    password_hash: Mapped[str] = mapped_column(Text, comment="带盐 scrypt 哈希；绝不返回客户端")
    department_id: Mapped[int] = mapped_column(ForeignKey("department.id"), comment="所属部门；必须存在")
    role_id: Mapped[int] = mapped_column(ForeignKey("role.id"), comment="所属角色；固定权限边界")
    active: Mapped[bool] = mapped_column(Boolean, default=True, comment="停用后现有令牌也不能访问")


class RefreshSession(Identity, Base):
    __tablename__ = "refresh_session"
    user_id: Mapped[int] = mapped_column(ForeignKey("sys_user.id"), comment="令牌持有人")
    token_hash: Mapped[str] = mapped_column(String(64), unique=True, comment="只存刷新令牌 SHA256，防止数据库泄露直接登录")
    expires_at: Mapped[datetime] = mapped_column(DateTime, comment="刷新会话到期 UTC")
    revoked_at: Mapped[datetime | None] = mapped_column(DateTime, comment="撤销时间；轮换或退出时写入")


class AgentConfig(Identity, Base):
    __tablename__ = "agent_config"
    __table_args__ = (UniqueConstraint("node_name", "version"),)
    node_name: Mapped[str] = mapped_column(String(40), comment="固定图节点标识；与流程注册名一致")
    name: Mapped[str] = mapped_column(String(80), comment="界面中文名称")
    enabled: Mapped[bool] = mapped_column(Boolean, default=True, comment="可选模型/反馈能力开关，不允许关闭核心判定")
    version: Mapped[int] = mapped_column(Integer, default=1, comment="不可变版本；修改生成新记录")
    config_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="经过白名单校验的运行参数；无密钥")


class AuditRule(Identity, Base):
    __tablename__ = "audit_rule"
    __table_args__ = (UniqueConstraint("code", "version"),)
    code: Mapped[str] = mapped_column(String(60), comment="七类内置规则代码")
    name: Mapped[str] = mapped_column(String(80), comment="规则中文名称")
    version: Mapped[int] = mapped_column(Integer, default=1, comment="不可变配置版本，用于历史回放")
    enabled: Mapped[bool] = mapped_column(Boolean, default=True, comment="可选规则开关，核心检查不可绕过")
    severity: Mapped[str] = mapped_column(String(12), default="warning", comment="warning/error/critical，规则具体分支可升级")
    scope_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="适用类型和部门；核心检查不允许缩小范围")
    params_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="数值阈值和格式参数；禁止可执行表达式")


class Reimbursement(Identity, Base):
    __tablename__ = "reimbursement"
    number: Mapped[str] = mapped_column(String(30), unique=True, comment="BX+日期+六位序号；唯一约束防并发重复")
    user_id: Mapped[int] = mapped_column(ForeignKey("sys_user.id"), comment="提交员工；用于所有权与禁止自审")
    department_id: Mapped[int] = mapped_column(ForeignKey("department.id"), comment="创建时部门快照，历史归属不会随用户调岗变化")
    type: Mapped[int] = mapped_column(Integer, comment="1差旅/2招待/3办公")
    title: Mapped[str] = mapped_column(String(200), comment="报销说明标题")
    description: Mapped[str] = mapped_column(Text, default="", comment="补充业务说明")
    total_cents: Mapped[int] = mapped_column(Integer, comment="含税申请额，以整数分保存避免浮点误差")
    status: Mapped[str] = mapped_column(String(30), default="draft", index=True, comment="白名单状态机控制，终态不可回滚")
    revision: Mapped[int] = mapped_column(Integer, default=1, comment="编辑/提交版本，后台旧任务不得回写新版本")
    submitted_at: Mapped[datetime | None] = mapped_column(DateTime, comment="最近实际提交时间，统计近期频率")


class Invoice(Identity, Base):
    __tablename__ = "invoice"
    reimbursement_id: Mapped[int] = mapped_column(ForeignKey("reimbursement.id"), index=True, comment="归属报销单")
    filename: Mapped[str] = mapped_column(String(250), comment="原始显示文件名，不作为存储路径")
    file_key: Mapped[str] = mapped_column(String(100), comment="受控随机文件键，防路径遍历")
    file_hash: Mapped[str] = mapped_column(String(64), index=True, comment="实际字节 SHA256；只检测同内容重复")
    content_type: Mapped[str] = mapped_column(String(40), comment="魔数验证后的 MIME")
    active: Mapped[bool] = mapped_column(Boolean, default=True, comment="移除采用停用，历史规则结果和原始文件仍可追溯")
    declared_fields: Mapped[dict] = mapped_column(JSON, default=dict, comment="用户补录来源，不覆盖历史模型提取快照")
    net_cents: Mapped[int | None] = mapped_column(Integer, comment="已填报未税金额，null 表示未知")
    tax_cents: Mapped[int | None] = mapped_column(Integer, comment="已填报税额，null 不等同零税")


class SequenceCounter(Base):
    __tablename__ = "sequence_counter"
    date_key: Mapped[str] = mapped_column(String(8), primary_key=True, comment="业务时区日期，按日重置序号")
    value: Mapped[int] = mapped_column(Integer, comment="事务内原子递增；不能超过六位")


class AuditRun(Identity, Base):
    __tablename__ = "audit_run"
    __table_args__ = (UniqueConstraint("reimbursement_id", "run_no"),)
    reimbursement_id: Mapped[int] = mapped_column(ForeignKey("reimbursement.id"), index=True, comment="被审核单据")
    revision: Mapped[int] = mapped_column(Integer, comment="提交时版本，后台写入前再次比较")
    run_no: Mapped[int] = mapped_column(Integer, comment="历史轮次，不覆盖旧结果")
    status: Mapped[str] = mapped_column(String(20), default="queued", comment="queued/running/completed/failed/cancelled；不是路由枚举")
    snapshot_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="输入、规则、Agent、政策与统计时点快照")
    result_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="完整结果，保留两个风险引擎与降级信息")
    decision: Mapped[str | None] = mapped_column(String(30), comment="四种确定性路由；未完成为 null")
    risk_level: Mapped[int] = mapped_column(Integer, default=0, comment="0未评估/1低/2中/3高")
    compliance_status: Mapped[str | None] = mapped_column(String(10), comment="pass/partial/fail；未知为 null")
    started_at: Mapped[datetime | None] = mapped_column(DateTime, comment="实际开始 UTC")
    finished_at: Mapped[datetime | None] = mapped_column(DateTime, comment="实际结束 UTC")
    error_code: Mapped[str | None] = mapped_column(String(80), comment="稳定错误码，不向界面暴露敏感异常")


class AgentExecution(Identity, Base):
    __tablename__ = "agent_execution"
    __table_args__ = (UniqueConstraint("audit_id", "node_name", "attempt"),)
    audit_id: Mapped[int] = mapped_column(ForeignKey("audit_run.id"), index=True, comment="关联审核")
    node_name: Mapped[str] = mapped_column(String(40), comment="节点标识")
    attempt: Mapped[int] = mapped_column(Integer, comment="实际调用序号，首个为1")
    status: Mapped[str] = mapped_column(String(20), comment="success/degraded/failed/skipped")
    duration_ms: Mapped[int] = mapped_column(Integer, comment="真实计时毫秒")
    input_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="必要输入摘要；不包含文件 base64 或密钥")
    output_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="节点结果与证据")
    error_code: Mapped[str | None] = mapped_column(String(80), comment="错误或降级原因")
    token_usage: Mapped[dict] = mapped_column(JSON, default=dict, comment="提供方实际返回的 token 统计，未提供时为空")


class RuleEvaluation(Identity, Base):
    __tablename__ = "rule_evaluation"
    audit_id: Mapped[int] = mapped_column(ForeignKey("audit_run.id"), index=True, comment="所属审核历史")
    invoice_id: Mapped[int | None] = mapped_column(ForeignKey("invoice.id"), comment="逐发票检查关联；合计检查为空")
    rule_code: Mapped[str] = mapped_column(String(80), comment="规则 code")
    rule_version: Mapped[int] = mapped_column(Integer, comment="当时版本")
    passed: Mapped[bool] = mapped_column(Boolean, comment="业务是否通过；跳过仍独立标识")
    severity: Mapped[str] = mapped_column(String(12), comment="未通过项的严重程度")
    execution_status: Mapped[str] = mapped_column(String(20), comment="success/skipped/failed/partial")
    message: Mapped[str] = mapped_column(Text, comment="人可读依据，避免只有布尔结论")
    details_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="阈值、实际值、政策出处")


class ApprovalChain(Identity, Base):
    __tablename__ = "approval_chain"
    name: Mapped[str] = mapped_column(String(100), comment="审批链名称")
    route: Mapped[str] = mapped_column(String(30), comment="to_supervisor/to_finance/to_manual")
    type: Mapped[int | None] = mapped_column(Integer, comment="报销类型，空为不限")
    department_id: Mapped[int | None] = mapped_column(ForeignKey("department.id"), comment="部门，空为不限")
    amount_min_cents: Mapped[int | None] = mapped_column(Integer, comment="金额下限，空为不限")
    amount_max_cents: Mapped[int | None] = mapped_column(Integer, comment="金额上限，空为不限")
    priority: Mapped[int] = mapped_column(Integer, default=0, comment="同明确程度时的配置优先级")
    nodes_json: Mapped[list] = mapped_column(JSON, comment="顺序审批人数组，必须非空")
    version: Mapped[int] = mapped_column(Integer, default=1, comment="配置版本，执行中使用冻结快照")
    active: Mapped[bool] = mapped_column(Boolean, default=True, comment="停用不影响已启动轮次")


class ApprovalRound(Identity, Base):
    __tablename__ = "approval_round"
    reimbursement_id: Mapped[int] = mapped_column(ForeignKey("reimbursement.id"), comment="审批对象")
    revision: Mapped[int] = mapped_column(Integer, comment="当前提交版本")
    audit_id: Mapped[int] = mapped_column(ForeignKey("audit_run.id"), unique=True, comment="每个审核只启动一个审批轮次")
    chain_snapshot: Mapped[dict] = mapped_column(JSON, comment="审批链冻结，配置修改不改变在途任务")
    status: Mapped[str] = mapped_column(String(20), default="active", comment="active/approved/rejected/returned")


class ApprovalRecord(Identity, Base):
    __tablename__ = "approval_record"
    __table_args__ = (UniqueConstraint("round_id", "node_index"),)
    round_id: Mapped[int] = mapped_column(ForeignKey("approval_round.id"), index=True, comment="明确所属轮次，不能复用旧记录")
    node_index: Mapped[int] = mapped_column(Integer, comment="从0开始的链中位置")
    approver_id: Mapped[int | None] = mapped_column(ForeignKey("sys_user.id"), comment="指定审批人，系统自动批准为空")
    result: Mapped[str | None] = mapped_column(String(20), comment="null待办；approve/reject/return")
    opinion: Mapped[str] = mapped_column(Text, default="", comment="审批理由")
    decided_at: Mapped[datetime | None] = mapped_column(DateTime, comment="签署 UTC")


class KnowledgeDocument(Identity, Base):
    __tablename__ = "knowledge_document"
    title: Mapped[str] = mapped_column(String(200), comment="政策名称，不使用模型编造标题")
    category: Mapped[str] = mapped_column(String(40), default="policy", comment="政策分类用于检索过滤")
    version: Mapped[int] = mapped_column(Integer, default=1, comment="修改正文递增版本")
    content: Mapped[str] = mapped_column(Text, comment="完整原文")
    active: Mapped[bool] = mapped_column(Boolean, default=True, comment="只有启用政策参与当前检索")
    index_status: Mapped[str] = mapped_column(String(20), default="not_indexed", comment="not_indexed/queued/indexed/failed")
    embedding_model: Mapped[str | None] = mapped_column(String(100), comment="实际建索引的模型；查询必须一致")


class KnowledgeChunk(Identity, Base):
    __tablename__ = "knowledge_chunk"
    document_id: Mapped[int] = mapped_column(ForeignKey("knowledge_document.id"), index=True, comment="原文来源")
    document_version: Mapped[int] = mapped_column(Integer, comment="块对应政策版本")
    position: Mapped[int] = mapped_column(Integer, comment="原文分块顺序")
    text: Mapped[str] = mapped_column(Text, comment="原始片段，留作审计引用")
    vector_json: Mapped[list | None] = mapped_column(JSON, comment="真实向量；未索引为 null")
    dimension: Mapped[int | None] = mapped_column(Integer, comment="实际向量维度；必须一致")


class OutboxEvent(Identity, Base):
    __tablename__ = "outbox_event"
    event_type: Mapped[str] = mapped_column(String(30), comment="audit/approval/index")
    business_key: Mapped[str] = mapped_column(String(160), unique=True, comment="业务幂等键，与变更同事务写入")
    payload_json: Mapped[dict] = mapped_column(JSON, comment="版本和资源 ID，不包含凭据")
    status: Mapped[str] = mapped_column(String(20), default="pending", index=True, comment="pending/processing/done/failed")
    attempts: Mapped[int] = mapped_column(Integer, default=0, comment="消费者领取次数")
    lease_until: Mapped[datetime | None] = mapped_column(DateTime, comment="崩溃后可回收的租约")
    next_attempt_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, comment="指数退避后的下次处理时点")
    error_code: Mapped[str | None] = mapped_column(String(80), comment="持久失败原因")


class Alert(Identity, Base):
    __tablename__ = "alert"
    dedup_key: Mapped[str] = mapped_column(String(160), unique=True, comment="同异常去重，不每次扫描重复创建")
    type: Mapped[str] = mapped_column(String(40), comment="风险、降级、失败、超时等分类")
    severity: Mapped[str] = mapped_column(String(12), comment="warning/error/critical")
    audit_id: Mapped[int | None] = mapped_column(ForeignKey("audit_run.id"), comment="相关审核，可空")
    reimbursement_id: Mapped[int | None] = mapped_column(ForeignKey("reimbursement.id"), comment="相关单据，可空")
    status: Mapped[str] = mapped_column(String(20), default="open", comment="open/acknowledged/resolved")
    message: Mapped[str] = mapped_column(Text, comment="可读原因")
    ack_by: Mapped[int | None] = mapped_column(ForeignKey("sys_user.id"), comment="确认人")
    resolved_by: Mapped[int | None] = mapped_column(ForeignKey("sys_user.id"), comment="解决人")
    acknowledged_at: Mapped[datetime | None] = mapped_column(DateTime, comment="确认时间")
    resolved_at: Mapped[datetime | None] = mapped_column(DateTime, comment="解决时间")
    opinion: Mapped[str] = mapped_column(Text, default="", comment="处理意见，不改写原风险")


class OperationLog(Identity, Base):
    __tablename__ = "operation_log"
    actor_id: Mapped[int | None] = mapped_column(ForeignKey("sys_user.id"), comment="操作人，后台系统事件为空")
    action: Mapped[str] = mapped_column(String(80), index=True, comment="稳定事件名称用于筛选")
    resource_type: Mapped[str] = mapped_column(String(40), comment="资源类型")
    resource_id: Mapped[int | None] = mapped_column(Integer, comment="资源 ID，不做跨类型外键")
    request_id: Mapped[str] = mapped_column(String(80), comment="请求追踪 ID")
    trace_id: Mapped[str] = mapped_column(String(80), index=True, comment="全流程追踪标识")
    before_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="变更前必要快照，敏感项脱敏")
    after_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="变更后必要快照，敏感项脱敏")


class HumanFeedback(Identity, Base):
    __tablename__ = "human_feedback"
    audit_id: Mapped[int] = mapped_column(ForeignKey("audit_run.id"), comment="被纠正的审核")
    approval_record_id: Mapped[int] = mapped_column(ForeignKey("approval_record.id"), comment="必须关联本人已完成的人工审批")
    actor_id: Mapped[int] = mapped_column(ForeignKey("sys_user.id"), comment="反馈人")
    corrected_fields: Mapped[dict] = mapped_column(JSON, comment="独立反馈不篡改原始审核证据")
    reason: Mapped[str] = mapped_column(Text, comment="人工判断依据")
    model_update_needed: Mapped[bool] = mapped_column(Boolean, comment="仅迭代建议，不代表发生训练")


class CacheEntry(Base):
    __tablename__ = "cache_entry"
    key: Mapped[str] = mapped_column(String(64), primary_key=True, comment="模型/参数/版本/输入的 SHA256")
    value_json: Mapped[Any] = mapped_column(JSON, comment="合法响应或向量缓存，不缓存失败")
    expires_at: Mapped[datetime] = mapped_column(DateTime, comment="TTL 到期 UTC")
    namespace: Mapped[str] = mapped_column(String(20), comment="llm/embed/query 等前缀")
    model_version: Mapped[str] = mapped_column(String(100), comment="模型隔离，换模型不可复用旧向量")