src/hermes/database/models.py

"""Database models."""

from __future__ import annotations

from datetime import UTC, datetime
from typing import Optional

from sqlalchemy import (
    Column,
    DateTime,
    Float,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
)
from sqlalchemy.orm import DeclarativeBase, relationship


class Base(DeclarativeBase):
    """Base model for all database models."""

    pass


class TaskRecord(Base):
    """Task record model."""

    __tablename__ = "tasks"

    id = Column(String, primary_key=True)
    query = Column(Text, nullable=False)
    status = Column(String, default="pending")
    assigned_agent = Column(String, nullable=True)
    results = Column(Text, nullable=True)
    created_at = Column(DateTime, default=lambda: datetime.utcnow())
    updated_at = Column(DateTime, default=lambda: datetime.utcnow())


class FindingRecord(Base):
    """Finding record model."""

    __tablename__ = "findings"

    id = Column(String, primary_key=True)
    task_id = Column(String, nullable=False)
    category = Column(String, nullable=False)
    title = Column(String, nullable=False)
    description = Column(Text, nullable=False)
    severity = Column(String, default="medium")
    created_at = Column(DateTime, default=lambda: datetime.utcnow())


class ReportRecord(Base):
    """Report record model."""

    __tablename__ = "reports"

    id = Column(String, primary_key=True)
    task_id = Column(String, nullable=False)
    title = Column(String, nullable=False)
    content = Column(Text, nullable=False)
    report_type = Column(String, default="research")
    created_at = Column(DateTime, default=lambda: datetime.utcnow())


# ── Compliance Review Models ──────────────────────────────────────────


class ComplianceReviewItem(Base):
    """Persisted compliance review item with tenant isolation.

    Stores the full lifecycle of a trade document review: PDF parsing
    results, HS code classification, sanctions screening, and human
    approval workflow state.

    Row-Level Security: All queries MUST filter on ``tenant_id``.
    """

    __tablename__ = "compliance_review_items"
    __table_args__ = (
        Index("idx_cri_tenant_status", "tenant_id", "status"),
        Index("idx_cri_tenant_created", "tenant_id", "created_at"),
        Index("idx_cri_assignee", "tenant_id", "assigned_to"),
    )

    id = Column(String(36), primary_key=True)
    tenant_id = Column(String(128), nullable=False, index=True)

    # Document data
    document_path = Column(Text, nullable=False)
    document_type = Column(String(64), default="unknown")
    invoice_number = Column(String(128), default="")
    invoice_date = Column(String(64), default="")
    total_amount = Column(String(64), default="")
    shipper = Column(Text, default="")
    consignee = Column(Text, default="")
    country_origin = Column(String(128), default="")
    country_destination = Column(String(128), default="")

    # HS classification
    hs_code_suggested = Column(String(12), default="")
    hs_code_description = Column(Text, default="")
    hs_code_confidence = Column(Float, default=0.0)
    hs_code_alternatives = Column(Text, default="[]")

    # Sanctions screening
    sanctions_risk_level = Column(String(16), default="clear")
    sanctions_matches = Column(Text, default="[]")

    # Review state
    status = Column(String(32), default="pending", nullable=False)
    assigned_to = Column(String(256), default="")
    priority = Column(Integer, default=0)

    # Review data
    final_hs_code = Column(String(12), default="")
    reviewer_notes = Column(Text, default="")

    # Timestamps
    created_at = Column(
        DateTime, default=lambda: datetime.utcnow(), nullable=False
    )
    updated_at = Column(
        DateTime,
        default=lambda: datetime.utcnow(),
        onupdate=lambda: datetime.utcnow(),
        nullable=False,
    )
    review_deadline = Column(DateTime, nullable=True)

    # Relationship to audit chain
    audit_entries = relationship(
        "ComplianceAuditChain",
        back_populates="review_item",
        lazy="selectin",
        order_by="ComplianceAuditChain.timestamp",
    )


class ComplianceAuditChain(Base):
    """Immutable cryptographic audit log entry for compliance reviews.

    Each entry contains a SHA-256 hash chaining it to the previous entry,
    providing tamper-evident integrity for the audit trail.

    Row-Level Security: Queries MUST join through ComplianceReviewItem.tenant_id.
    """

    __tablename__ = "compliance_audit_chain"
    __table_args__ = (
        Index("idx_cac_review_item", "review_item_id"),
        Index("idx_cac_timestamp", "timestamp"),
        Index("idx_cac_actor", "actor_id"),
    )

    id = Column(Integer, primary_key=True, autoincrement=True)
    review_item_id = Column(
        String(36),
        ForeignKey("compliance_review_items.id", ondelete="CASCADE"),
        nullable=False,
    )
    timestamp = Column(DateTime, nullable=False, default=lambda: datetime.utcnow())
    actor_id = Column(String(256), nullable=False)
    action = Column(String(64), nullable=False)
    previous_state = Column(String(32), nullable=False)
    current_state = Column(String(32), nullable=False)
    block_hash = Column(String(64), nullable=False)
    modified_values = Column(Text, default="{}")
    reason = Column(Text, default="")

    # Relationship back to review item
    review_item = relationship(
        "ComplianceReviewItem",
        back_populates="audit_entries",
    )