"""
Download tracking models for deduplication and efficient checking.
Separate from library models to keep tracking lightweight.
"""

from sqlalchemy import (
    Boolean,
    Column,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    UniqueConstraint,
)
from sqlalchemy_utc import UtcDateTime, utcnow

from .base import Base


class DownloadTracker(Base):
    """
    Lightweight table to track which URLs have been downloaded.
    Used for quick deduplication checks before attempting downloads.
    """

    __tablename__ = "download_tracker"

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

    # URL tracking
    url = Column(Text, nullable=False)  # Original URL
    url_hash = Column(
        String(64), nullable=False
    )  # SHA256 of normalized URL — UNIQUE backing comes from __table_args__

    # Resource tracking (can be multiple resources with same URL)
    first_resource_id = Column(
        Integer, ForeignKey("research_resources.id"), nullable=False
    )

    # File tracking
    file_hash = Column(
        String(64), nullable=True, index=True
    )  # SHA256 of downloaded content
    file_path = Column(
        Text, nullable=True
    )  # Relative path from library root (e.g., "2024/12/arxiv_2401_12345.pdf")
    # NOTE: Absolute path removed - will be computed at runtime from library root + relative path
    file_name = Column(
        String(255), nullable=True, index=True
    )  # Just the filename for searching
    file_size = Column(Integer, nullable=True)

    # Status
    is_downloaded = Column(Boolean, default=False, nullable=False, index=True)
    is_accessible = Column(Boolean, default=True)  # False if 404, 403, etc.

    # Timestamps
    first_seen = Column(UtcDateTime, default=utcnow(), nullable=False)
    downloaded_at = Column(UtcDateTime, nullable=True)
    last_checked = Column(UtcDateTime, default=utcnow(), nullable=False)

    # Link to full document record if it exists. documents.id is String(36) UUID;
    # the previous Integer declaration produced an FK type mismatch that SQLite
    # silently coerced as TEXT due to type-affinity rules.
    library_document_id = Column(
        String(36),
        ForeignKey("documents.id", ondelete="SET NULL"),
        nullable=True,
    )

    # Inline UNIQUE so it lands in CREATE TABLE; column-level unique=True
    # produces a separate CREATE UNIQUE INDEX which SQLCipher does not accept
    # as an FK target for url_hash references.
    __table_args__ = (
        UniqueConstraint("url_hash", name="uq_download_tracker_url_hash"),
    )

    def __repr__(self):
        status = "downloaded" if self.is_downloaded else "not downloaded"
        return f"<DownloadTracker(url_hash={self.url_hash[:8]}..., status={status})>"


class DownloadDuplicates(Base):
    """
    Track duplicate URLs across different resources.
    Helps identify when multiple researches reference the same source.
    """

    __tablename__ = "download_duplicates"

    id = Column(Integer, primary_key=True, autoincrement=True)
    url_hash = Column(
        String(64),
        ForeignKey("download_tracker.url_hash"),
        nullable=False,
        index=True,
    )
    resource_id = Column(
        Integer, ForeignKey("research_resources.id"), nullable=False
    )
    research_id = Column(String(36), nullable=False, index=True)

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

    __table_args__ = (
        UniqueConstraint("url_hash", "resource_id", name="uix_url_resource"),
        Index("idx_research_duplicates", "research_id", "url_hash"),
    )

    def __repr__(self):
        return f"<DownloadDuplicates(url_hash={self.url_hash[:8]}..., resource_id={self.resource_id})>"


class DownloadAttempt(Base):
    """
    Log of download attempts for debugging and retry logic.
    """

    __tablename__ = "download_attempts"

    id = Column(Integer, primary_key=True, autoincrement=True)
    url_hash = Column(
        String(64),
        ForeignKey("download_tracker.url_hash"),
        nullable=False,
        index=True,
    )

    # Attempt details
    attempt_number = Column(Integer, nullable=False)
    status_code = Column(Integer, nullable=True)  # HTTP status code
    error_type = Column(String(100), nullable=True)  # timeout, connection, etc.
    error_message = Column(Text, nullable=True)

    # Timing
    attempted_at = Column(UtcDateTime, default=utcnow(), nullable=False)
    duration_ms = Column(Integer, nullable=True)

    # Success tracking
    succeeded = Column(Boolean, default=False, nullable=False)
    bytes_downloaded = Column(Integer, nullable=True)

    def __repr__(self):
        status = (
            "success"
            if self.succeeded
            else f"failed ({self.status_code or self.error_type})"
        )
        return (
            f"<DownloadAttempt(attempt={self.attempt_number}, status={status})>"
        )
