labor-arbitration-system/backend/app/models.py

from sqlalchemy import JSON, Column, DateTime, ForeignKey, Integer, String, Text
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func

from app.db import Base


class Case(Base):
    __tablename__ = "cases"

    id = Column(Integer, primary_key=True, index=True)
    case_no = Column(String(64), nullable=True, index=True)
    case_name = Column(String(255), nullable=False, index=True)
    # 多文件字节 SHA256 排序后摘要；用于去重
    material_fingerprint = Column(String(64), nullable=True, index=True)

    # 当前最新要素（可编辑）
    elements = Column(JSON, nullable=False, default=dict)
    # 最新画像缓存
    portrait = Column(JSON, nullable=False, default=dict)

    applicant_name = Column(String(64), nullable=True, index=True)
    respondent_name = Column(String(128), nullable=True, index=True)
    case_cause = Column(String(64), nullable=True, index=True)
    risk_level = Column(String(16), nullable=True, index=True)

    # 合并后的申请书/材料全文（用于类案展示与对比；大文本可截断存储）
    application_text = Column(Text, nullable=True)
    # 历史类案可录入：裁决结论、仲裁机构（无数据时前端展示占位说明）
    ruling_result = Column(Text, nullable=True)
    arbitration_org = Column(String(255), nullable=True)

    created_at = Column(DateTime(timezone=True), server_default=func.now())

    files = relationship("CaseFile", back_populates="case", cascade="all, delete-orphan")
    element_versions = relationship("CaseElementsVersion", back_populates="case", cascade="all, delete-orphan")


class CaseFile(Base):
    __tablename__ = "case_files"

    id = Column(Integer, primary_key=True)
    case_id = Column(Integer, ForeignKey("cases.id", ondelete="CASCADE"), nullable=False, index=True)
    filename = Column(String(255), nullable=False)
    content_type = Column(String(128), nullable=True)
    storage_path = Column(String(512), nullable=False)
    size_bytes = Column(Integer, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    case = relationship("Case", back_populates="files")


class CaseElementsVersion(Base):
    __tablename__ = "case_elements_versions"

    id = Column(Integer, primary_key=True)
    case_id = Column(Integer, ForeignKey("cases.id", ondelete="CASCADE"), nullable=False, index=True)
    version = Column(Integer, nullable=False)
    elements = Column(JSON, nullable=False)
    updated_by = Column(String(64), nullable=True)
    updated_at = Column(DateTime(timezone=True), server_default=func.now())

    case = relationship("Case", back_populates="element_versions")


class ProcessingTask(Base):
    __tablename__ = "processing_tasks"

    id = Column(String(64), primary_key=True)
    case_id = Column(Integer, ForeignKey("cases.id", ondelete="CASCADE"), nullable=False, index=True)
    status = Column(String(16), nullable=False, default="PENDING")  # PENDING/RUNNING/SUCCEEDED/FAILED
    progress = Column(Integer, nullable=False, default=0)  # 0-100
    message = Column(String(255), nullable=True)
    error = Column(Text, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())