"""FileUpload Model."""

from datetime import datetime, timezone
from typing import TYPE_CHECKING, Optional

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models import BaseSoftDeleteModel, NormalizedDateTime
from sqlalchemy import (
    JSON,
    BigInteger,
    Column,
    Enum,
    ForeignKey,
    Integer,
    SmallInteger,
    String,
    Text,
)
from sqlalchemy.orm import Mapped
from sqlalchemy.sql.elements import UnaryExpression

from abacus_file_upload.constants import UPLOAD_STATUSES

if TYPE_CHECKING:
    from abacus_file_upload.models.file_upload_config import FileUploadConfig


class FileUpload(BaseSoftDeleteModel):
    """FileUpload Model."""

    __tablename__ = 'file_upload'

    file_upload_id: Mapped[int] = Column(
        Integer, primary_key=True, autoincrement=True, comment='Primary key.'
    )
    file_upload_config_id: Mapped[int] = Column(
        ForeignKey('file_upload_config.file_upload_config_id', ondelete='RESTRICT'),
        comment='Foreign key to file_upload_config table.',
    )
    file_key: Mapped[str] = Column(
        String(36), unique=True, comment='Unique identifier for the file upload (UUID).'
    )
    original_file_name: Mapped[str] = Column(
        String(255), comment='Original filename from upload.'
    )
    file_size_bytes: Mapped[int] = Column(BigInteger, comment='File size in bytes.')
    file_type: Mapped[Optional[str]] = Column(
        String(10),
        comment='File extension without leading dot and lowercase (e.g., csv, pdf, xlsx).',
    )
    mime_type: Mapped[Optional[str]] = Column(
        String(255), comment='MIME type of the file (e.g., text/csv, application/pdf).'
    )
    s3_bucket: Mapped[str] = Column(
        String(63),
        comment='S3 bucket name where the file is stored (e.g., prod-abacus-flowthrough).',
    )
    s3_key: Mapped[str] = Column(
        String(1024),
        comment='S3 object key/path for the uploaded file (e.g., uploads/2025/11/00000000-0000-0000-0000-000000000000.csv).',
    )
    md5sum: Mapped[Optional[str]] = Column(
        String(32),
        index=True,
        comment='MD5 sum of file contents for integrity verification and duplicate detection.',
    )
    upload_status: Mapped[str] = Column(
        Enum(*UPLOAD_STATUSES, name='upload_status', create_type=False),
        default=UPLOAD_STATUSES.INIT,
        server_default=UPLOAD_STATUSES.INIT,
        comment='Current status of the upload. init: initiated, scanning: scanned by AV scanner, complete: successfully uploaded, error: failed, cancelled: cancelled by user, quarantined: infected uploads.',
    )
    multipart_upload_id: Mapped[Optional[str]] = Column(
        String(255), comment='S3 multipart upload ID for large file uploads.'
    )
    total_parts: Mapped[int] = Column(
        SmallInteger,
        default=1,
        server_default='1',
        comment='Total number of parts for multipart uploads.',
    )
    upload_metadata: Mapped[Optional[dict]] = Column(
        JSON, comment='Additional metadata about the upload (flexible JSON structure).'
    )
    error_message: Mapped[Optional[str]] = Column(
        Text, comment='Error message if upload failed.'
    )
    completed_at: Mapped[Optional[datetime]] = Column(
        NormalizedDateTime(),
        comment='Timestamp when the upload completed successfully.',
    )
    expires_at: Mapped[Optional[datetime]] = Column(
        NormalizedDateTime(),
        comment='Expiration timestamp for the upload/presigned URL.',
    )

    # Relationship to file_upload_config
    file_upload_config: Mapped['FileUploadConfig'] = db.relationship(
        'FileUploadConfig', back_populates='file_uploads'
    )

    @classmethod
    def default_order(cls) -> UnaryExpression:
        """Override to customize default ordering."""
        return cls.created_at.desc()

    @classmethod
    def find_by_file_key(cls, file_key: str) -> Optional['FileUpload']:
        """Find file upload by file_key (excludes soft-deleted records)."""
        return (
            cls.query.filter_by(file_key=file_key)
            .filter(cls.deleted_at.is_(None))
            .first()
        )

    @classmethod
    def find_by_md5sum(cls, md5sum: str) -> list['FileUpload']:
        """Find file uploads by md5sum for duplicate detection (excludes soft-deleted records)."""
        return cls.query.filter_by(md5sum=md5sum).filter(cls.deleted_at.is_(None)).all()

    @classmethod
    def find_by_upload_status(cls, upload_status: str) -> list['FileUpload']:
        """Find file uploads by upload_status (excludes soft-deleted records)."""
        return (
            cls.query.filter_by(upload_status=upload_status)
            .filter(cls.deleted_at.is_(None))
            .all()
        )