from __future__ import annotations

from datetime import date, datetime, timezone
from decimal import Decimal

from sqlalchemy import Boolean, Date, DateTime, ForeignKey, Integer, Numeric, String, Text, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.core.db.common import CommonBase


class EngagementDocument(CommonBase):
    """Logical document container linked to a client engagement.

    The physical file is stored revision-wise in EngagementDocumentVersion.
    DS1 keeps the file on the server/local project storage. DS3/DS4 can later
    move the physical storage to branch local storage nodes without changing
    the engagement-facing workflow.
    """

    __tablename__ = "engagement_documents"
    __table_args__ = (
        UniqueConstraint("tenant_id", "engagement_id", "document_code", name="uq_engagement_documents_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id", ondelete="SET NULL"), nullable=True, index=True)
    client_id: Mapped[int] = mapped_column(ForeignKey("clients.id", ondelete="CASCADE"), nullable=False, index=True)
    engagement_id: Mapped[int] = mapped_column(ForeignKey("client_service_subscriptions.id", ondelete="CASCADE"), nullable=False, index=True)
    task_instance_id: Mapped[int | None] = mapped_column(ForeignKey("client_service_task_instances.id", ondelete="SET NULL"), nullable=True, index=True)
    document_requirement_id: Mapped[int | None] = mapped_column(ForeignKey("firm_task_document_requirements.id", ondelete="SET NULL"), nullable=True, index=True)

    financial_year: Mapped[str] = mapped_column(String(9), nullable=False, index=True)
    assessment_year: Mapped[str | None] = mapped_column(String(9), nullable=True, index=True)
    document_code: Mapped[str] = mapped_column(String(80), nullable=False, index=True)
    document_type: Mapped[str] = mapped_column(String(80), nullable=False, default="GENERAL", index=True)
    title: Mapped[str] = mapped_column(String(255), nullable=False)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    current_version_no: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    status: Mapped[str] = mapped_column(String(30), nullable=False, default="active", index=True)

    # Phase 1 - UDIN and final document release controls.
    # These fields convert the existing engagement document table into the UDIN
    # source register.  A separate manual UDIN register is intentionally avoided:
    # partner-wise/client-wise/FY-wise reports are generated from these workflow
    # fields.
    udin_required: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, index=True)
    udin_status: Mapped[str] = mapped_column(String(30), nullable=False, default="not_required", index=True)
    udin_number: Mapped[str | None] = mapped_column(String(30), nullable=True, index=True)
    udin_date: Mapped[date | None] = mapped_column(Date, nullable=True, index=True)
    udin_document_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    udin_document_type: Mapped[str | None] = mapped_column(String(120), nullable=True)
    udin_financial_year: Mapped[str | None] = mapped_column(String(9), nullable=True, index=True)
    udin_amount: Mapped[Decimal | None] = mapped_column(Numeric(14, 2), nullable=True)
    udin_generated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)
    udin_verified_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    udin_notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    final_release_status: Mapped[str] = mapped_column(String(30), nullable=False, default="draft", index=True)
    released_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)
    released_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    release_notes: Mapped[str | None] = mapped_column(Text, nullable=True)


    # Phase 4 - AQMM evidence metadata.
    # If this document is uploaded against a task marked as AQMM, it is
    # automatically treated as AQMM evidence. These fields store review status
    # without creating a duplicate AQMM document vault.
    is_aqmm_evidence: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, index=True)
    evidence_type: Mapped[str | None] = mapped_column(String(80), nullable=True, index=True)
    evidence_description: Mapped[str | None] = mapped_column(Text, nullable=True)
    evidence_status: Mapped[str] = mapped_column(String(30), nullable=False, default="not_required", index=True)
    evidence_review_note: Mapped[str | None] = mapped_column(Text, nullable=True)
    evidence_reviewed_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)
    evidence_reviewed_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)

    is_deleted: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, index=True)
    deleted_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    deleted_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    updated_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc), nullable=False)

    client = relationship("Client")
    engagement = relationship("ClientServiceSubscription")
    task_instance = relationship("ClientServiceTaskInstance", foreign_keys=[task_instance_id])
    document_requirement = relationship("FirmTaskDocumentRequirement", foreign_keys=[document_requirement_id])
    versions = relationship(
        "EngagementDocumentVersion",
        back_populates="document",
        cascade="all, delete-orphan",
        passive_deletes=True,
        order_by="EngagementDocumentVersion.version_no.desc()",
    )


class EngagementDocumentVersion(CommonBase):
    """Physical revision of an engagement document."""

    __tablename__ = "engagement_document_versions"
    __table_args__ = (
        UniqueConstraint("document_id", "version_no", name="uq_engagement_document_versions_no"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    document_id: Mapped[int] = mapped_column(ForeignKey("engagement_documents.id", ondelete="CASCADE"), nullable=False, index=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id", ondelete="SET NULL"), nullable=True, index=True)
    client_id: Mapped[int] = mapped_column(ForeignKey("clients.id", ondelete="CASCADE"), nullable=False, index=True)
    engagement_id: Mapped[int] = mapped_column(ForeignKey("client_service_subscriptions.id", ondelete="CASCADE"), nullable=False, index=True)

    version_no: Mapped[int] = mapped_column(Integer, nullable=False)
    original_filename: Mapped[str] = mapped_column(String(255), nullable=False)
    stored_filename: Mapped[str] = mapped_column(String(255), nullable=False)
    content_type: Mapped[str | None] = mapped_column(String(150), nullable=True)
    file_size_bytes: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    file_hash_sha256: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    storage_backend: Mapped[str] = mapped_column(String(40), nullable=False, default="LOCAL_YEAR_WISE", index=True)
    local_relative_path: Mapped[str] = mapped_column(String(1000), nullable=False)
    storage_status: Mapped[str] = mapped_column(String(30), nullable=False, default="stored", index=True)
    remarks: Mapped[str | None] = mapped_column(Text, nullable=True)

    uploaded_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    uploaded_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)

    document = relationship("EngagementDocument", back_populates="versions")


class DocumentAccessLog(CommonBase):
    """Audit trail for document upload/download/delete activity."""

    __tablename__ = "document_access_logs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int | None] = mapped_column(ForeignKey("tenants.id", ondelete="SET NULL"), nullable=True, index=True)
    branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id", ondelete="SET NULL"), nullable=True, index=True)
    client_id: Mapped[int | None] = mapped_column(ForeignKey("clients.id", ondelete="SET NULL"), nullable=True, index=True)
    engagement_id: Mapped[int | None] = mapped_column(ForeignKey("client_service_subscriptions.id", ondelete="SET NULL"), nullable=True, index=True)
    document_id: Mapped[int | None] = mapped_column(ForeignKey("engagement_documents.id", ondelete="SET NULL"), nullable=True, index=True)
    version_id: Mapped[int | None] = mapped_column(ForeignKey("engagement_document_versions.id", ondelete="SET NULL"), nullable=True, index=True)
    action: Mapped[str] = mapped_column(String(40), nullable=False, index=True)
    result: Mapped[str] = mapped_column(String(40), nullable=False, default="success", index=True)
    user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)
    ip_address: Mapped[str | None] = mapped_column(String(80), nullable=True)
    user_agent: Mapped[str | None] = mapped_column(String(500), nullable=True)
    message: Mapped[str | None] = mapped_column(Text, nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)


class BranchStorageNode(CommonBase):
    """Branch-wise local storage connector registered with the cloud ERP.

    DS3 stores only the hashed secret. The raw secret is generated once and must
    be copied into the branch local storage app configuration.
    """

    __tablename__ = "branch_storage_nodes"
    __table_args__ = (
        UniqueConstraint("tenant_id", "branch_id", "node_code", name="uq_branch_storage_node_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id", ondelete="SET NULL"), nullable=True, index=True)
    node_code: Mapped[str] = mapped_column(String(80), nullable=False, index=True)
    node_name: Mapped[str] = mapped_column(String(200), nullable=False)
    connector_url: Mapped[str | None] = mapped_column(String(500), nullable=True)
    secret_key_hash: Mapped[str] = mapped_column(String(64), nullable=False)
    storage_root_path: Mapped[str | None] = mapped_column(String(1000), nullable=True)
    storage_mode: Mapped[str] = mapped_column(String(40), nullable=False, default="pull_jobs", index=True)
    status: Mapped[str] = mapped_column(String(30), nullable=False, default="active", index=True)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, index=True)
    last_seen_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    last_seen_ip: Mapped[str | None] = mapped_column(String(80), nullable=True)
    quota_limit_bytes: Mapped[int | None] = mapped_column(Integer, nullable=True)
    used_storage_bytes: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    subscription_required: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    updated_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc), nullable=False)


class DocumentStorageJob(CommonBase):
    """Cloud-to-local branch storage transfer job.

    The browser upload is first staged by ERP. The branch local app authenticates
    with its node secret, pulls pending jobs, streams the staged file, stores it
    in the branch year-wise folder, and acknowledges with the final hash.
    """

    __tablename__ = "document_storage_jobs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id", ondelete="SET NULL"), nullable=True, index=True)
    storage_node_id: Mapped[int] = mapped_column(ForeignKey("branch_storage_nodes.id", ondelete="CASCADE"), nullable=False, index=True)
    document_id: Mapped[int] = mapped_column(ForeignKey("engagement_documents.id", ondelete="CASCADE"), nullable=False, index=True)
    version_id: Mapped[int] = mapped_column(ForeignKey("engagement_document_versions.id", ondelete="CASCADE"), nullable=False, index=True)
    job_type: Mapped[str] = mapped_column(String(40), nullable=False, default="store_version", index=True)
    status: Mapped[str] = mapped_column(String(30), nullable=False, default="pending", index=True)
    priority: Mapped[int] = mapped_column(Integer, nullable=False, default=5, index=True)
    staging_relative_path: Mapped[str] = mapped_column(String(1000), nullable=False)
    target_relative_path: Mapped[str] = mapped_column(String(1000), nullable=False)
    file_size_bytes: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    expected_hash_sha256: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    attempts: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    last_error: Mapped[str | None] = mapped_column(Text, nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    picked_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    completed_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    acknowledged_hash_sha256: Mapped[str | None] = mapped_column(String(64), nullable=True)
    local_final_path: Mapped[str | None] = mapped_column(String(1000), nullable=True)

    # VPS staging cleanup audit. The file is deleted only after the branch agent
    # acknowledges the exact expected SHA-256 hash.
    vps_cleanup_status: Mapped[str] = mapped_column(String(30), nullable=False, default="pending", index=True)
    vps_cleanup_attempts: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    vps_cleanup_error: Mapped[str | None] = mapped_column(Text, nullable=True)
    vps_deleted_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True, index=True)
    vps_deleted_bytes: Mapped[int | None] = mapped_column(Integer, nullable=True)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)

    node = relationship("BranchStorageNode")
    document = relationship("EngagementDocument")
    version = relationship("EngagementDocumentVersion")

class DocumentDownloadRequest(CommonBase):
    """Secure local-to-cloud document retrieval request.

    DS5 keeps user downloads permission-controlled in ERP while allowing the
    branch local storage app to upload/stream the requested file back to ERP
    through the existing zero-trust agent channel.
    """

    __tablename__ = "document_download_requests"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id", ondelete="SET NULL"), nullable=True, index=True)
    storage_node_id: Mapped[int] = mapped_column(ForeignKey("branch_storage_nodes.id", ondelete="CASCADE"), nullable=False, index=True)
    document_id: Mapped[int] = mapped_column(ForeignKey("engagement_documents.id", ondelete="CASCADE"), nullable=False, index=True)
    version_id: Mapped[int] = mapped_column(ForeignKey("engagement_document_versions.id", ondelete="CASCADE"), nullable=False, index=True)

    request_status: Mapped[str] = mapped_column(String(30), nullable=False, default="pending", index=True)
    local_relative_path: Mapped[str | None] = mapped_column(String(1000), nullable=True)
    expected_hash_sha256: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    cached_relative_path: Mapped[str | None] = mapped_column(String(1000), nullable=True)
    cached_hash_sha256: Mapped[str | None] = mapped_column(String(64), nullable=True)
    file_size_bytes: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    attempts: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    last_error: Mapped[str | None] = mapped_column(Text, nullable=True)

    requested_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)
    requested_ip: Mapped[str | None] = mapped_column(String(80), nullable=True)
    requested_user_agent: Mapped[str | None] = mapped_column(String(500), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    fulfilled_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    failed_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)

    node = relationship("BranchStorageNode")
    document = relationship("EngagementDocument")
    version = relationship("EngagementDocumentVersion")



class PermanentClientDocument(CommonBase):
    """Permanent client document container not linked to a financial year or engagement.

    Examples: incorporation certificate, GST registration, PAN/TAN, bank/KYC,
    agreements, DSC and other standing records used across years.
    """

    __tablename__ = "permanent_client_documents"
    __table_args__ = (
        UniqueConstraint("tenant_id", "client_id", "document_code", name="uq_permanent_client_documents_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id", ondelete="SET NULL"), nullable=True, index=True)
    client_id: Mapped[int] = mapped_column(ForeignKey("clients.id", ondelete="CASCADE"), nullable=False, index=True)

    document_code: Mapped[str] = mapped_column(String(80), nullable=False, index=True)
    category: Mapped[str] = mapped_column(String(120), nullable=False, default="Other Permanent Documents", index=True)
    title: Mapped[str] = mapped_column(String(255), nullable=False)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    current_version_no: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    status: Mapped[str] = mapped_column(String(30), nullable=False, default="active", index=True)

    # Phase 1 - UDIN and final document release controls.
    # These fields convert the existing engagement document table into the UDIN
    # source register.  A separate manual UDIN register is intentionally avoided:
    # partner-wise/client-wise/FY-wise reports are generated from these workflow
    # fields.
    udin_required: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, index=True)
    udin_status: Mapped[str] = mapped_column(String(30), nullable=False, default="not_required", index=True)
    udin_number: Mapped[str | None] = mapped_column(String(30), nullable=True, index=True)
    udin_date: Mapped[date | None] = mapped_column(Date, nullable=True, index=True)
    udin_document_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    udin_document_type: Mapped[str | None] = mapped_column(String(120), nullable=True)
    udin_financial_year: Mapped[str | None] = mapped_column(String(9), nullable=True, index=True)
    udin_amount: Mapped[Decimal | None] = mapped_column(Numeric(14, 2), nullable=True)
    udin_generated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)
    udin_verified_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    udin_notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    final_release_status: Mapped[str] = mapped_column(String(30), nullable=False, default="draft", index=True)
    released_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)
    released_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    release_notes: Mapped[str | None] = mapped_column(Text, nullable=True)


    # Phase 4 - AQMM evidence metadata.
    # If this document is uploaded against a task marked as AQMM, it is
    # automatically treated as AQMM evidence. These fields store review status
    # without creating a duplicate AQMM document vault.
    is_aqmm_evidence: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, index=True)
    evidence_type: Mapped[str | None] = mapped_column(String(80), nullable=True, index=True)
    evidence_description: Mapped[str | None] = mapped_column(Text, nullable=True)
    evidence_status: Mapped[str] = mapped_column(String(30), nullable=False, default="not_required", index=True)
    evidence_review_note: Mapped[str | None] = mapped_column(Text, nullable=True)
    evidence_reviewed_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)
    evidence_reviewed_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)

    is_deleted: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, index=True)
    deleted_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    deleted_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    updated_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc), nullable=False)

    client = relationship("Client")
    versions = relationship(
        "PermanentClientDocumentVersion",
        back_populates="document",
        cascade="all, delete-orphan",
        passive_deletes=True,
        order_by="PermanentClientDocumentVersion.version_no.desc()",
    )


class PermanentClientDocumentVersion(CommonBase):
    """Physical revision of a permanent client document."""

    __tablename__ = "permanent_client_document_versions"
    __table_args__ = (
        UniqueConstraint("document_id", "version_no", name="uq_permanent_client_document_versions_no"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    document_id: Mapped[int] = mapped_column(ForeignKey("permanent_client_documents.id", ondelete="CASCADE"), nullable=False, index=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id", ondelete="SET NULL"), nullable=True, index=True)
    client_id: Mapped[int] = mapped_column(ForeignKey("clients.id", ondelete="CASCADE"), nullable=False, index=True)

    version_no: Mapped[int] = mapped_column(Integer, nullable=False)
    original_filename: Mapped[str] = mapped_column(String(255), nullable=False)
    stored_filename: Mapped[str] = mapped_column(String(255), nullable=False)
    content_type: Mapped[str | None] = mapped_column(String(150), nullable=True)
    file_size_bytes: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    file_hash_sha256: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    storage_backend: Mapped[str] = mapped_column(String(40), nullable=False, default="LOCAL_PERMANENT", index=True)
    local_relative_path: Mapped[str] = mapped_column(String(1000), nullable=False)
    storage_status: Mapped[str] = mapped_column(String(30), nullable=False, default="stored", index=True)
    remarks: Mapped[str | None] = mapped_column(Text, nullable=True)

    uploaded_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    uploaded_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)

    document = relationship("PermanentClientDocument", back_populates="versions")


class PermanentDocumentStorageJob(CommonBase):
    """Cloud-to-local transfer job for permanent client document versions."""

    __tablename__ = "permanent_document_storage_jobs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id", ondelete="SET NULL"), nullable=True, index=True)
    storage_node_id: Mapped[int] = mapped_column(ForeignKey("branch_storage_nodes.id", ondelete="CASCADE"), nullable=False, index=True)
    document_id: Mapped[int] = mapped_column(ForeignKey("permanent_client_documents.id", ondelete="CASCADE"), nullable=False, index=True)
    version_id: Mapped[int] = mapped_column(ForeignKey("permanent_client_document_versions.id", ondelete="CASCADE"), nullable=False, index=True)

    job_type: Mapped[str] = mapped_column(String(40), nullable=False, default="store_permanent_version", index=True)
    status: Mapped[str] = mapped_column(String(30), nullable=False, default="pending", index=True)
    priority: Mapped[int] = mapped_column(Integer, nullable=False, default=5, index=True)
    staging_relative_path: Mapped[str] = mapped_column(String(1000), nullable=False)
    target_relative_path: Mapped[str] = mapped_column(String(1000), nullable=False)
    file_size_bytes: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    expected_hash_sha256: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    attempts: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    last_error: Mapped[str | None] = mapped_column(Text, nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    picked_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    completed_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    acknowledged_hash_sha256: Mapped[str | None] = mapped_column(String(64), nullable=True)
    local_final_path: Mapped[str | None] = mapped_column(String(1000), nullable=True)

    # VPS staging cleanup audit. The file is deleted only after the branch agent
    # acknowledges the exact expected SHA-256 hash.
    vps_cleanup_status: Mapped[str] = mapped_column(String(30), nullable=False, default="pending", index=True)
    vps_cleanup_attempts: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    vps_cleanup_error: Mapped[str | None] = mapped_column(Text, nullable=True)
    vps_deleted_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True, index=True)
    vps_deleted_bytes: Mapped[int | None] = mapped_column(Integer, nullable=True)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)

    node = relationship("BranchStorageNode")
    document = relationship("PermanentClientDocument")
    version = relationship("PermanentClientDocumentVersion")


class PermanentDocumentDownloadRequest(CommonBase):
    """Local-to-cloud retrieval request for permanent client documents."""

    __tablename__ = "permanent_document_download_requests"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int | None] = mapped_column(ForeignKey("branches.id", ondelete="SET NULL"), nullable=True, index=True)
    storage_node_id: Mapped[int] = mapped_column(ForeignKey("branch_storage_nodes.id", ondelete="CASCADE"), nullable=False, index=True)
    document_id: Mapped[int] = mapped_column(ForeignKey("permanent_client_documents.id", ondelete="CASCADE"), nullable=False, index=True)
    version_id: Mapped[int] = mapped_column(ForeignKey("permanent_client_document_versions.id", ondelete="CASCADE"), nullable=False, index=True)

    request_status: Mapped[str] = mapped_column(String(30), nullable=False, default="pending", index=True)
    local_relative_path: Mapped[str | None] = mapped_column(String(1000), nullable=True)
    expected_hash_sha256: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    cached_relative_path: Mapped[str | None] = mapped_column(String(1000), nullable=True)
    cached_hash_sha256: Mapped[str | None] = mapped_column(String(64), nullable=True)
    file_size_bytes: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    attempts: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    last_error: Mapped[str | None] = mapped_column(Text, nullable=True)

    requested_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)
    requested_ip: Mapped[str | None] = mapped_column(String(80), nullable=True)
    requested_user_agent: Mapped[str | None] = mapped_column(String(500), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    fulfilled_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    failed_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)

    node = relationship("BranchStorageNode")
    document = relationship("PermanentClientDocument")
    version = relationship("PermanentClientDocumentVersion")
