"""
Library and document models - Unified architecture.
All documents (research downloads and user uploads) are stored in one table.
Collections organize documents, with "Library" as the default collection.
"""

import enum
import warnings

from sqlalchemy import (
    JSON,
    Boolean,
    Column,
    Date,
    Enum,
    ForeignKey,
    Index,
    Integer,
    LargeBinary,
    String,
    Text,
    UniqueConstraint,
)
from sqlalchemy.orm import backref, relationship
from sqlalchemy_utc import UtcDateTime, utcnow

from .base import Base


class RAGIndexStatus(enum.Enum):
    """Status values for RAG indices."""

    ACTIVE = "active"
    REBUILDING = "rebuilding"
    DEPRECATED = "deprecated"


class DocumentStatus(enum.Enum):
    """Status values for document processing and downloads."""

    PENDING = "pending"
    PROCESSING = "processing"
    COMPLETED = "completed"
    FAILED = "failed"


class EmbeddingProvider(enum.Enum):
    """Embedding model provider types.

    OPENAI covers both the OpenAI cloud API and any OpenAI-compatible
    endpoint (LM Studio, vLLM, llama.cpp server, etc.) — the underlying
    provider class reads ``embeddings.openai.base_url`` to target a local
    server when set, falling back to the OpenAI cloud when unset.
    """

    SENTENCE_TRANSFORMERS = "sentence_transformers"
    OLLAMA = "ollama"
    OPENAI = "openai"


class ExtractionMethod(str, enum.Enum):
    """Methods used to extract text from documents."""

    PDF_EXTRACTION = "pdf_extraction"
    NATIVE_API = "native_api"
    UNKNOWN = "unknown"


class ExtractionSource(str, enum.Enum):
    """Sources used for text extraction."""

    ARXIV_API = "arxiv_api"
    PUBMED_API = "pubmed_api"
    PDFPLUMBER = "pdfplumber"
    PDFPLUMBER_FALLBACK = "pdfplumber_fallback"
    LOCAL_PDF = "local_pdf"
    LEGACY_FILE = "legacy_file"


class ExtractionQuality(str, enum.Enum):
    """Quality levels for extracted text."""

    HIGH = "high"
    MEDIUM = "medium"
    LOW = "low"


class DistanceMetric(str, enum.Enum):
    """Distance metrics for vector similarity search."""

    COSINE = "cosine"
    L2 = "l2"
    DOT_PRODUCT = "dot_product"


class IndexType(str, enum.Enum):
    """FAISS index types for RAG."""

    FLAT = "flat"
    HNSW = "hnsw"
    IVF = "ivf"


class SplitterType(str, enum.Enum):
    """Text splitter types for chunking."""

    RECURSIVE = "recursive"
    SEMANTIC = "semantic"
    TOKEN = "token"
    SENTENCE = "sentence"


class PDFStorageMode(str, enum.Enum):
    """Storage modes for PDF files."""

    NONE = "none"  # Don't store PDFs, text-only
    FILESYSTEM = "filesystem"  # Store PDFs unencrypted on filesystem
    DATABASE = "database"  # Store PDFs encrypted in database


class SourceType(Base):
    """
    Document source types (research_download, user_upload, manual_entry, etc.).
    Normalized table for consistent categorization.
    """

    __tablename__ = "source_types"

    id = Column(String(36), primary_key=True)  # UUID
    name = Column(String(50), nullable=False, unique=True, index=True)
    display_name = Column(String(100), nullable=False)
    description = Column(Text)
    icon = Column(String(50))  # Icon name for UI

    # Timestamps
    created_at = Column(UtcDateTime, default=utcnow(), nullable=False)

    def __repr__(self):
        return (
            f"<SourceType(name='{self.name}', display='{self.display_name}')>"
        )


class UploadBatch(Base):
    """
    DEPRECATED but DO NOT DELETE. See "Why we can't drop this" below.

    A multi-round dead-code audit in 2026-05 confirmed this table is
    dormant: no code path creates ``UploadBatch`` rows or sets
    ``Document.upload_batch_id`` (declared below). Wiring it up would
    have needed a product decision on what defines a "batch" (per
    upload submit, per UI session, etc.) and surfacing the grouping in
    the upload routes / UI — neither happened. Constructing an
    ``UploadBatch`` emits a ``DeprecationWarning`` so future
    contributors don't accidentally start writing to it.

    Why we can't drop this (and the table)
    --------------------------------------
    The same audit attempted to write the table-drop migration and
    hit an irreconcilable conflict in this codebase's migration
    framework:

      * ``0001_initial_schema.py`` builds the initial schema from
        ``Base.metadata.create_all()``, so the ORM defines what
        "schema at every revision" means.
      * ``test_migrations_produce_schema_matching_models`` and
        ``test_check_schema_shows_no_missing_tables`` enforce that
        the migrated DB matches ``Base.metadata`` exactly.
      * ``test_stairway_up_down_up_per_revision`` and
        ``test_downgrade_leaves_no_residual_tables`` require that
        downgrade restores the schema that existed before the upgrade.

    The combination means you cannot drop the table from the DB
    without also removing it from the ORM, AND you cannot remove it
    from the ORM without breaking either the round-trip tests
    (because 0001 stops producing the table at pre-0010 revisions) or
    the parity tests (model and migrated schema diverge).

    A real removal needs a precursor refactor that converts
    ``0001_initial_schema.py`` to an explicit table list before the
    drop migration can be added. The benefit of finishing the drop is
    roughly 3 KB of empty-table overhead per user DB and one fewer
    ORM class. The cost is real migration risk against deployed
    encrypted DBs. The audit concluded the trade-off was not worth
    it; the deprecation here is the entire shipped fix.

    If you still want to delete this, bring fresh evidence that the
    cost/benefit has changed and read PR #4178 plus the abandoned
    "PR 11b" thread that established this constraint.
    """

    __tablename__ = "upload_batches"

    id = Column(String(36), primary_key=True)  # UUID
    collection_id = Column(
        String(36),
        ForeignKey("collections.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    uploaded_at = Column(UtcDateTime, default=utcnow(), nullable=False)
    file_count = Column(Integer, default=0)
    total_size = Column(Integer, default=0)  # Total bytes

    # Relationships
    collection = relationship("Collection", backref="upload_batches")
    documents = relationship("Document", backref="upload_batch")

    def __init__(self, *args, **kwargs):
        warnings.warn(
            "UploadBatch is deprecated and scheduled for removal in a "
            "follow-up release; the table is dormant and has no production "
            "writers.",
            DeprecationWarning,
            stacklevel=2,
        )
        super().__init__(*args, **kwargs)

    def __repr__(self):
        return f"<UploadBatch(id='{self.id}', files={self.file_count}, size={self.total_size})>"


class Document(Base):
    """
    Unified document table for all documents (research downloads + user uploads).
    """

    __tablename__ = "documents"

    id = Column(String(36), primary_key=True)  # UUID as string

    # Source type (research_download, user_upload, etc.)
    source_type_id = Column(
        String(36),
        ForeignKey("source_types.id"),
        nullable=False,
        index=True,
    )

    # Link to original research resource (for research downloads) - nullable for uploads
    resource_id = Column(
        Integer,
        ForeignKey("research_resources.id", ondelete="SET NULL"),
        nullable=True,
        index=True,
    )

    # Link to research (for research downloads) - nullable for uploads
    research_id = Column(
        String(36),
        ForeignKey("research_history.id", ondelete="CASCADE"),
        nullable=True,
        index=True,
    )

    # Link to upload batch (for user uploads) - nullable for research downloads
    upload_batch_id = Column(
        String(36),
        ForeignKey("upload_batches.id", ondelete="SET NULL"),
        nullable=True,
        index=True,
    )

    # Document identification
    document_hash = Column(
        String(64), nullable=False, unique=True, index=True
    )  # SHA256 for deduplication
    original_url = Column(Text, nullable=True)  # Source URL (for downloads)
    filename = Column(String(500), nullable=True)  # Display name (for uploads)
    original_filename = Column(
        String(500), nullable=True
    )  # Original upload name

    # File information
    file_path = Column(
        Text, nullable=True
    )  # Path relative to library/uploads root
    file_size = Column(Integer, nullable=False)  # Size in bytes
    file_type = Column(String(50), nullable=False)  # pdf, txt, md, html, etc.
    mime_type = Column(String(100), nullable=True)  # MIME type

    # Content storage - text always stored in DB
    text_content = Column(
        Text, nullable=True
    )  # Extracted/uploaded text content

    # PDF storage mode (none, filesystem, database)
    storage_mode = Column(
        String(20), nullable=True, default="database"
    )  # PDFStorageMode value

    # Metadata
    title = Column(Text)  # Document title
    description = Column(Text)  # User description
    authors = Column(JSON)  # List of authors (for research papers)
    published_date = Column(Date, nullable=True)  # Publication date

    # Academic identifiers (for research papers)
    doi = Column(String(255), nullable=True, index=True)
    arxiv_id = Column(String(100), nullable=True, index=True)
    pmid = Column(String(50), nullable=True, index=True)
    pmcid = Column(String(50), nullable=True, index=True)
    isbn = Column(String(20), nullable=True)

    # Download/Upload information
    status = Column(
        Enum(
            DocumentStatus, values_callable=lambda obj: [e.value for e in obj]
        ),
        nullable=False,
        default=DocumentStatus.COMPLETED,
    )
    attempts = Column(Integer, default=1)
    error_message = Column(Text, nullable=True)
    processed_at = Column(UtcDateTime, nullable=False, default=utcnow())
    last_accessed = Column(UtcDateTime, nullable=True)

    # Text extraction metadata (for research downloads from PDFs)
    extraction_method = Column(
        String(50), nullable=True
    )  # pdf_extraction, native_api, etc.
    extraction_source = Column(
        String(50), nullable=True
    )  # arxiv_api, pdfplumber, etc.
    extraction_quality = Column(String(20), nullable=True)  # high, medium, low
    has_formatting_issues = Column(Boolean, default=False)
    has_encoding_issues = Column(Boolean, default=False)
    character_count = Column(Integer, nullable=True)
    word_count = Column(Integer, nullable=True)

    # Organization
    tags = Column(JSON)  # User-defined tags
    favorite = Column(Boolean, default=False)

    # Timestamps
    created_at = Column(UtcDateTime, default=utcnow(), nullable=False)
    updated_at = Column(
        UtcDateTime, default=utcnow(), onupdate=utcnow(), nullable=False
    )

    # Relationships
    source_type = relationship("SourceType", backref="documents")
    resource = relationship(
        "ResearchResource",
        foreign_keys="[Document.resource_id]",
        backref="documents",
    )
    research = relationship("ResearchHistory", backref="documents")
    collections = relationship(
        "DocumentCollection",
        back_populates="document",
        cascade="all, delete-orphan",
    )

    # Indexes for efficient queries
    __table_args__ = (
        Index("idx_source_type", "source_type_id", "status"),
        Index("idx_research_documents", "research_id", "status"),
        Index("idx_document_type", "file_type", "status"),
        Index("idx_document_hash", "document_hash"),
    )

    def __repr__(self):
        title_str = (
            self.title[:50]
            if self.title
            else (self.filename[:50] if self.filename else "Untitled")
        )
        return f"<Document(title='{title_str}', type={self.file_type}, size={self.file_size})>"


class DocumentBlob(Base):
    """
    Separate table for storing PDF binary content.
    SQLite best practices: keep BLOBs in separate table for better query performance.
    Stored in encrypted SQLCipher database for security.
    """

    __tablename__ = "document_blobs"

    # Primary key references Document.id
    document_id = Column(
        String(36),
        ForeignKey("documents.id", ondelete="CASCADE"),
        primary_key=True,
        nullable=False,
    )

    # Binary PDF content
    pdf_binary = Column(LargeBinary, nullable=False)

    # Hash for integrity verification
    blob_hash = Column(String(64), nullable=True, index=True)  # SHA256

    # Timestamps
    stored_at = Column(UtcDateTime, default=utcnow(), nullable=False)
    last_accessed = Column(UtcDateTime, nullable=True)

    # Relationship
    document = relationship(
        "Document",
        backref=backref("blob", passive_deletes=True),
        passive_deletes=True,
    )

    def __repr__(self):
        size = len(self.pdf_binary) if self.pdf_binary else 0
        return f"<DocumentBlob(document_id='{self.document_id[:8]}...', size={size})>"


class Collection(Base):
    """
    Collections for organizing documents.
    'Library' is the default collection for research downloads.
    Users can create custom collections for organization.
    """

    __tablename__ = "collections"

    id = Column(String(36), primary_key=True)  # UUID as string
    name = Column(String(255), nullable=False)
    description = Column(Text)

    # Collection type (default_library, user_collection, linked_folder)
    collection_type = Column(String(50), default="user_collection")

    # Is this the default library collection?
    is_default = Column(Boolean, default=False)

    # Egress classification: is this collection's content non-sensitive
    # ("public") or sensitive ("private")? Defaults to private (False) — the
    # safe choice. A private collection is excluded under PUBLIC_ONLY scope
    # and forces local LLM/embeddings inference when used, so its chunks
    # never reach a cloud model. Operators flip it per-collection.
    is_public = Column(Boolean, default=False)

    # Whether this collection is offered to the research agent (LangGraph) as a
    # specialized search tool. Defaults to True (available) so existing
    # collections keep their current behaviour; flip it off to declutter the
    # agent's tool list when a collection isn't needed for agentic research.
    # Independent of is_public / egress scope — this is a usability switch, not
    # a security control.
    agent_enabled = Column(Boolean, default=True)

    # Embedding model used for this collection (stored when first indexed)
    embedding_model = Column(
        String(100), nullable=True
    )  # e.g., 'all-MiniLM-L6-v2', 'nomic-embed-text:latest'
    embedding_model_type = Column(
        Enum(
            EmbeddingProvider,
            values_callable=lambda obj: [e.value for e in obj],
        ),
        nullable=True,
    )
    embedding_dimension = Column(Integer, nullable=True)  # Vector dimension
    chunk_size = Column(Integer, nullable=True)  # Chunk size used
    chunk_overlap = Column(Integer, nullable=True)  # Chunk overlap used

    # Advanced embedding configuration options (Issue #1054)
    splitter_type = Column(
        String(50), nullable=True
    )  # Splitter type: 'recursive', 'semantic', 'token', 'sentence'
    text_separators = Column(
        JSON, nullable=True
    )  # Text separators for chunking, e.g., ["\n\n", "\n", ". ", " ", ""]
    distance_metric = Column(
        String(50), nullable=True
    )  # Distance metric: 'cosine', 'l2', 'dot_product'
    normalize_vectors = Column(
        Boolean, nullable=True
    )  # Whether to normalize embeddings with L2
    index_type = Column(
        String(50), nullable=True
    )  # FAISS index type: 'flat', 'hnsw', 'ivf'

    # Timestamps
    created_at = Column(UtcDateTime, default=utcnow(), nullable=False)
    updated_at = Column(
        UtcDateTime, default=utcnow(), onupdate=utcnow(), nullable=False
    )

    # Relationships
    document_links = relationship(
        "DocumentCollection",
        back_populates="collection",
        cascade="all, delete-orphan",
    )
    linked_folders = relationship(
        "CollectionFolder",
        back_populates="collection",
        cascade="all, delete-orphan",
    )

    def __repr__(self):
        return f"<Collection(id='{self.id}', name='{self.name}', type='{self.collection_type}')>"


class DocumentCollection(Base):
    """
    Many-to-many relationship between documents and collections.
    Tracks indexing status per collection (documents can be in multiple collections).
    """

    __tablename__ = "document_collections"

    id = Column(Integer, primary_key=True, autoincrement=True)

    # Foreign keys
    document_id = Column(
        String(36),
        ForeignKey("documents.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    collection_id = Column(
        String(36),
        ForeignKey("collections.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )

    # Indexing status (per collection!)
    indexed = Column(
        Boolean, default=False
    )  # Whether indexed for this collection
    chunk_count = Column(
        Integer, default=0
    )  # Number of chunks in this collection
    last_indexed_at = Column(UtcDateTime, nullable=True)

    # Timestamps
    added_at = Column(UtcDateTime, default=utcnow(), nullable=False)

    # Relationships
    document = relationship("Document", back_populates="collections")
    collection = relationship("Collection", back_populates="document_links")

    # Ensure one entry per document-collection pair
    __table_args__ = (
        UniqueConstraint(
            "document_id", "collection_id", name="uix_document_collection"
        ),
        Index("idx_collection_indexed", "collection_id", "indexed"),
    )

    def __repr__(self):
        return f"<DocumentCollection(doc_id={self.document_id}, coll_id={self.collection_id}, indexed={self.indexed})>"


class DocumentChunk(Base):
    """
    Universal chunk storage for RAG across all sources.
    Stores text chunks in encrypted database for semantic search.
    """

    __tablename__ = "document_chunks"

    id = Column(Integer, primary_key=True, autoincrement=True)

    # Chunk identification
    chunk_hash = Column(
        String(64), nullable=False, index=True
    )  # SHA256 for deduplication

    # Source tracking - now points to unified Document table
    source_type = Column(
        String(20), nullable=False, index=True
    )  # 'document', 'folder_file'
    source_id = Column(
        String(36), nullable=True, index=True
    )  # Document.id (UUID as string)
    source_path = Column(
        Text, nullable=True
    )  # File path if local collection source
    collection_name = Column(
        String(100), nullable=False, index=True
    )  # collection_<uuid>

    # Chunk content (encrypted in SQLCipher DB)
    chunk_text = Column(Text, nullable=False)  # The actual chunk text
    chunk_index = Column(Integer, nullable=False)  # Position in source document
    start_char = Column(Integer, nullable=False)  # Start character position
    end_char = Column(Integer, nullable=False)  # End character position
    word_count = Column(Integer, nullable=False)  # Number of words in chunk

    # Embedding metadata
    embedding_id = Column(
        String(36), nullable=False, unique=True, index=True
    )  # UUID for FAISS vector mapping
    embedding_model = Column(
        String(100), nullable=False
    )  # e.g., 'all-MiniLM-L6-v2'
    embedding_model_type = Column(
        Enum(
            EmbeddingProvider,
            values_callable=lambda obj: [e.value for e in obj],
        ),
        nullable=False,
    )
    embedding_dimension = Column(Integer, nullable=True)  # Vector dimension

    # Document metadata (for context)
    document_title = Column(Text, nullable=True)  # Title of source document
    document_metadata = Column(
        JSON, nullable=True
    )  # Additional metadata from source

    # Timestamps
    created_at = Column(UtcDateTime, default=utcnow(), nullable=False)
    last_accessed = Column(UtcDateTime, nullable=True)

    # Indexes for efficient queries
    __table_args__ = (
        UniqueConstraint(
            "chunk_hash", "collection_name", name="uix_chunk_collection"
        ),
        Index("idx_chunk_source", "source_type", "source_id"),
        Index("idx_chunk_collection", "collection_name", "created_at"),
        Index("idx_chunk_embedding", "embedding_id"),
    )

    def __repr__(self):
        return f"<DocumentChunk(collection='{self.collection_name}', source_type='{self.source_type}', index={self.chunk_index}, words={self.word_count})>"


class DownloadQueue(Base):
    """
    Queue for pending document downloads.
    Renamed from LibraryDownloadQueue for consistency.
    """

    __tablename__ = "download_queue"

    id = Column(Integer, primary_key=True, autoincrement=True)

    # What to download
    resource_id = Column(
        Integer,
        ForeignKey("research_resources.id", ondelete="CASCADE"),
        nullable=False,
        unique=True,  # One queue entry per resource
    )
    research_id = Column(String(36), nullable=False, index=True)

    # Target collection (defaults to Library collection)
    collection_id = Column(
        String(36),
        ForeignKey("collections.id", ondelete="SET NULL"),
        nullable=True,
        index=True,
    )

    # Queue management
    priority = Column(Integer, default=0)  # Higher = more important
    status = Column(
        Enum(
            DocumentStatus, values_callable=lambda obj: [e.value for e in obj]
        ),
        nullable=False,
        default=DocumentStatus.PENDING,
    )
    attempts = Column(Integer, default=0)
    max_attempts = Column(Integer, default=3)

    # Error tracking
    last_error = Column(Text, nullable=True)
    last_attempt_at = Column(UtcDateTime, nullable=True)

    # Timestamps
    queued_at = Column(UtcDateTime, default=utcnow(), nullable=False)
    completed_at = Column(UtcDateTime, nullable=True)

    # Relationships
    resource = relationship("ResearchResource", backref="download_queue")
    collection = relationship("Collection", backref="download_queue_items")

    # Index for the common filter_by(research_id=..., status=...) query
    # pattern used by download_bulk, queue_all_undownloaded, and the
    # background scheduler. Mirrors Document.idx_research_documents.
    __table_args__ = (
        Index("idx_download_queue_research_status", "research_id", "status"),
    )

    def __repr__(self):
        return f"<DownloadQueue(resource_id={self.resource_id}, status={self.status}, attempts={self.attempts})>"


class LibraryStatistics(Base):
    """
    Aggregate statistics for the library.
    Updated periodically for dashboard display.
    """

    __tablename__ = "library_statistics"

    id = Column(Integer, primary_key=True, autoincrement=True)

    # Document counts
    total_documents = Column(Integer, default=0)
    total_pdfs = Column(Integer, default=0)
    total_html = Column(Integer, default=0)
    total_other = Column(Integer, default=0)

    # Storage metrics
    total_size_bytes = Column(Integer, default=0)
    average_document_size = Column(Integer, default=0)

    # Research metrics
    total_researches_with_downloads = Column(Integer, default=0)
    average_documents_per_research = Column(Integer, default=0)

    # Download metrics
    total_download_attempts = Column(Integer, default=0)
    successful_downloads = Column(Integer, default=0)
    failed_downloads = Column(Integer, default=0)
    pending_downloads = Column(Integer, default=0)

    # Academic sources breakdown
    arxiv_count = Column(Integer, default=0)
    pubmed_count = Column(Integer, default=0)
    doi_count = Column(Integer, default=0)
    other_count = Column(Integer, default=0)

    # Timestamps
    calculated_at = Column(UtcDateTime, default=utcnow(), nullable=False)

    def __repr__(self):
        return f"<LibraryStatistics(documents={self.total_documents}, size={self.total_size_bytes})>"


class RAGIndex(Base):
    """
    Tracks FAISS indices for RAG collections.
    Each collection+embedding_model combination has its own FAISS index.
    """

    __tablename__ = "rag_indices"

    id = Column(Integer, primary_key=True, autoincrement=True)

    # Collection and model identification
    collection_name = Column(
        String(100), nullable=False, index=True
    )  # 'collection_<uuid>'
    embedding_model = Column(
        String(100), nullable=False
    )  # e.g., 'all-MiniLM-L6-v2'
    embedding_model_type = Column(
        Enum(
            EmbeddingProvider,
            values_callable=lambda obj: [e.value for e in obj],
        ),
        nullable=False,
    )
    embedding_dimension = Column(Integer, nullable=False)  # Vector dimension

    # Index file location
    index_path = Column(Text, nullable=False)  # Path to .faiss file
    index_hash = Column(
        String(64), nullable=False, unique=True, index=True
    )  # SHA256 of collection+model for uniqueness

    # Chunking parameters used
    chunk_size = Column(Integer, nullable=False)
    chunk_overlap = Column(Integer, nullable=False)

    # Advanced embedding configuration options (Issue #1054)
    splitter_type = Column(
        String(50), nullable=True
    )  # Splitter type: 'recursive', 'semantic', 'token', 'sentence'
    text_separators = Column(
        JSON, nullable=True
    )  # Text separators for chunking, e.g., ["\n\n", "\n", ". ", " ", ""]
    distance_metric = Column(
        String(50), nullable=True
    )  # Distance metric: 'cosine', 'l2', 'dot_product'
    normalize_vectors = Column(
        Boolean, nullable=True
    )  # Whether to normalize embeddings with L2
    index_type = Column(
        String(50), nullable=True
    )  # FAISS index type: 'flat', 'hnsw', 'ivf'

    # Index statistics
    chunk_count = Column(Integer, default=0)  # Number of chunks in this index
    total_documents = Column(Integer, default=0)  # Number of source documents

    # Status
    status = Column(
        Enum(
            RAGIndexStatus, values_callable=lambda obj: [e.value for e in obj]
        ),
        nullable=False,
        default=RAGIndexStatus.ACTIVE,
    )
    is_current = Column(
        Boolean, default=True
    )  # Whether this is the current index for this collection

    # Timestamps
    created_at = Column(UtcDateTime, default=utcnow(), nullable=False)
    last_updated_at = Column(
        UtcDateTime, default=utcnow(), onupdate=utcnow(), nullable=False
    )
    last_used_at = Column(
        UtcDateTime, nullable=True
    )  # Last time index was searched

    # Ensure one active index per collection+model
    __table_args__ = (
        UniqueConstraint(
            "collection_name",
            "embedding_model",
            "embedding_model_type",
            name="uix_collection_model",
        ),
        Index("idx_collection_current", "collection_name", "is_current"),
    )

    def __repr__(self):
        return f"<RAGIndex(collection='{self.collection_name}', model='{self.embedding_model}', chunks={self.chunk_count})>"


class RagDocumentStatus(Base):
    """
    Tracks which documents have been indexed for RAG.
    Row existence = document is indexed. No row = not indexed.
    Simple and avoids ORM caching issues.
    """

    __tablename__ = "rag_document_status"

    # Composite primary key
    document_id = Column(
        String(36),
        ForeignKey("documents.id", ondelete="CASCADE"),
        primary_key=True,
        nullable=False,
    )
    collection_id = Column(
        String(36),
        ForeignKey("collections.id", ondelete="CASCADE"),
        primary_key=True,
        nullable=False,
    )

    # Which RAG index was used (tracks embedding model indirectly)
    rag_index_id = Column(
        Integer,
        ForeignKey("rag_indices.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )

    # Metadata
    chunk_count = Column(Integer, nullable=False)
    indexed_at = Column(UtcDateTime, nullable=False, default=utcnow())

    # Indexes for fast lookups
    __table_args__ = (
        Index("idx_rag_status_collection", "collection_id"),
        Index("idx_rag_status_index", "rag_index_id"),
    )

    def __repr__(self):
        return f"<RagDocumentStatus(doc='{self.document_id[:8]}...', coll='{self.collection_id[:8]}...', chunks={self.chunk_count})>"


class CollectionFolder(Base):
    """
    Local folders linked to a collection for indexing.
    """

    __tablename__ = "collection_folders"

    id = Column(Integer, primary_key=True, autoincrement=True)

    # Collection association
    collection_id = Column(
        String(36),
        ForeignKey("collections.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )

    # Folder configuration
    folder_path = Column(Text, nullable=False)  # Absolute path to folder
    include_patterns = Column(
        JSON, default=["*.pdf", "*.txt", "*.md", "*.html"]
    )  # File patterns to include
    exclude_patterns = Column(
        JSON
    )  # Patterns to exclude (e.g., ["**/node_modules/**"])
    recursive = Column(Boolean, default=True)  # Search subfolders

    # Monitoring
    watch_enabled = Column(
        Boolean, default=False
    )  # Auto-reindex on changes (future)
    last_scanned_at = Column(UtcDateTime, nullable=True)
    file_count = Column(Integer, default=0)  # Total files found
    indexed_file_count = Column(Integer, default=0)  # Files indexed

    # Timestamps
    created_at = Column(UtcDateTime, default=utcnow(), nullable=False)
    updated_at = Column(
        UtcDateTime, default=utcnow(), onupdate=utcnow(), nullable=False
    )

    # Relationships
    collection = relationship("Collection", back_populates="linked_folders")
    files = relationship(
        "CollectionFolderFile",
        back_populates="folder",
        cascade="all, delete-orphan",
    )

    def __repr__(self):
        return f"<CollectionFolder(path='{self.folder_path}', files={self.file_count})>"


class CollectionFolderFile(Base):
    """
    Files found in linked folders.
    Lightweight tracking for deduplication and indexing status.
    """

    __tablename__ = "collection_folder_files"

    id = Column(Integer, primary_key=True, autoincrement=True)

    # Folder association
    folder_id = Column(
        Integer,
        ForeignKey("collection_folders.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )

    # File identification
    relative_path = Column(Text, nullable=False)  # Path relative to folder_path
    file_hash = Column(String(64), index=True)  # SHA256 for deduplication
    file_size = Column(Integer)  # Size in bytes
    file_type = Column(String(50))  # Extension

    # File metadata
    last_modified = Column(UtcDateTime)  # File modification time

    # Indexing status
    indexed = Column(Boolean, default=False)
    chunk_count = Column(Integer, default=0)
    last_indexed_at = Column(UtcDateTime, nullable=True)
    index_error = Column(Text, nullable=True)  # Error if indexing failed

    # Timestamps
    discovered_at = Column(UtcDateTime, default=utcnow(), nullable=False)
    updated_at = Column(
        UtcDateTime, default=utcnow(), onupdate=utcnow(), nullable=False
    )

    # Relationships
    folder = relationship("CollectionFolder", back_populates="files")

    # Ensure one entry per file in folder
    __table_args__ = (
        UniqueConstraint("folder_id", "relative_path", name="uix_folder_file"),
        Index("idx_folder_indexed", "folder_id", "indexed"),
    )

    def __repr__(self):
        return f"<CollectionFolderFile(path='{self.relative_path}', indexed={self.indexed})>"
