package com.social.media.repository;

import com.social.media.domain.entity.Media;
import com.social.media.domain.entity.Media.MediaFormat;
import com.social.media.domain.entity.Media.MediaValidationStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface MediaRepository extends JpaRepository<Media, Long> {
    
    // Basic finders
    Optional<Media> findByMediaCode(String mediaCode);
    Optional<Media> findByStoredFilename(String storedFilename);
    Optional<Media> findByFileChecksum(String fileChecksum);
    
    // Company-based queries
    List<Media> findByCompanyId(Long companyId);
    Page<Media> findByCompanyId(Long companyId, Pageable pageable);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.deletedAt IS NULL")
    List<Media> findActiveByCompanyId(@Param("companyId") Long companyId);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.deletedAt IS NULL")
    Page<Media> findActiveByCompanyId(@Param("companyId") Long companyId, Pageable pageable);
    
    // User-based queries
    List<Media> findByUploadedBy(Long uploadedBy);
    Page<Media> findByUploadedBy(Long uploadedBy, Pageable pageable);
    
    @Query("SELECT m FROM Media m WHERE m.uploadedBy = :uploadedBy AND m.deletedAt IS NULL")
    List<Media> findActiveByUploadedBy(@Param("uploadedBy") Long uploadedBy);
    
    @Query("SELECT m FROM Media m WHERE m.uploadedBy = :uploadedBy AND m.deletedAt IS NULL")
    Page<Media> findActiveByUploadedBy(@Param("uploadedBy") Long uploadedBy, Pageable pageable);
    
    // Format-based queries
    List<Media> findByFormat(MediaFormat format);
    Page<Media> findByFormat(MediaFormat format, Pageable pageable);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.format = :format AND m.deletedAt IS NULL")
    List<Media> findByCompanyIdAndFormat(@Param("companyId") Long companyId, @Param("format") MediaFormat format);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.format = :format AND m.deletedAt IS NULL")
    Page<Media> findByCompanyIdAndFormat(@Param("companyId") Long companyId, @Param("format") MediaFormat format, Pageable pageable);
    
    // Validation status queries
    List<Media> findByValidationStatus(MediaValidationStatus validationStatus);
    Page<Media> findByValidationStatus(MediaValidationStatus validationStatus, Pageable pageable);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.validationStatus = :status AND m.deletedAt IS NULL")
    List<Media> findByCompanyIdAndValidationStatus(@Param("companyId") Long companyId, @Param("status") MediaValidationStatus status);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.validationStatus = :status AND m.deletedAt IS NULL")
    Page<Media> findByCompanyIdAndValidationStatus(@Param("companyId") Long companyId, @Param("status") MediaValidationStatus status, Pageable pageable);
    
    // Public/Private queries
    List<Media> findByIsPublic(Boolean isPublic);
    Page<Media> findByIsPublic(Boolean isPublic, Pageable pageable);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.isPublic = :isPublic AND m.deletedAt IS NULL")
    List<Media> findByCompanyIdAndIsPublic(@Param("companyId") Long companyId, @Param("isPublic") Boolean isPublic);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.isPublic = :isPublic AND m.deletedAt IS NULL")
    Page<Media> findByCompanyIdAndIsPublic(@Param("companyId") Long companyId, @Param("isPublic") Boolean isPublic, Pageable pageable);
    
    // Date range queries
    @Query("SELECT m FROM Media m WHERE m.uploadedAt BETWEEN :startDate AND :endDate")
    List<Media> findByUploadedAtBetween(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
    
    @Query("SELECT m FROM Media m WHERE m.uploadedAt BETWEEN :startDate AND :endDate")
    Page<Media> findByUploadedAtBetween(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate, Pageable pageable);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.uploadedAt BETWEEN :startDate AND :endDate AND m.deletedAt IS NULL")
    List<Media> findByCompanyIdAndUploadedAtBetween(@Param("companyId") Long companyId, @Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.uploadedAt BETWEEN :startDate AND :endDate AND m.deletedAt IS NULL")
    Page<Media> findByCompanyIdAndUploadedAtBetween(@Param("companyId") Long companyId, @Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate, Pageable pageable);
    
    // File size queries
    @Query("SELECT m FROM Media m WHERE m.fileSize BETWEEN :minSize AND :maxSize")
    List<Media> findByFileSizeBetween(@Param("minSize") Long minSize, @Param("maxSize") Long maxSize);
    
    @Query("SELECT m FROM Media m WHERE m.fileSize BETWEEN :minSize AND :maxSize")
    Page<Media> findByFileSizeBetween(@Param("minSize") Long minSize, @Param("maxSize") Long maxSize, Pageable pageable);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.fileSize > :minSize AND m.deletedAt IS NULL")
    List<Media> findLargeFilesByCompanyId(@Param("companyId") Long companyId, @Param("minSize") Long minSize);
    
    // MIME type queries
    List<Media> findByMimeType(String mimeType);
    
    @Query("SELECT m FROM Media m WHERE m.mimeType LIKE :mimeTypePattern")
    List<Media> findByMimeTypeContaining(@Param("mimeTypePattern") String mimeTypePattern);
    
    // Search queries
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND " +
           "(LOWER(m.originalFilename) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR " +
           "LOWER(m.description) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR " +
           "LOWER(m.altText) LIKE LOWER(CONCAT('%', :searchTerm, '%'))) AND " +
           "m.deletedAt IS NULL")
    List<Media> searchByCompanyId(@Param("companyId") Long companyId, @Param("searchTerm") String searchTerm);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND " +
           "(LOWER(m.originalFilename) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR " +
           "LOWER(m.description) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR " +
           "LOWER(m.altText) LIKE LOWER(CONCAT('%', :searchTerm, '%'))) AND " +
           "m.deletedAt IS NULL")
    Page<Media> searchByCompanyId(@Param("companyId") Long companyId, @Param("searchTerm") String searchTerm, Pageable pageable);
    
    // Complex filter queries
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND " +
           "(:format IS NULL OR m.format = :format) AND " +
           "(:validationStatus IS NULL OR m.validationStatus = :validationStatus) AND " +
           "(:isPublic IS NULL OR m.isPublic = :isPublic) AND " +
           "(:uploadedBy IS NULL OR m.uploadedBy = :uploadedBy) AND " +
           "m.deletedAt IS NULL")
    Page<Media> findWithFilters(@Param("companyId") Long companyId,
                               @Param("format") MediaFormat format,
                               @Param("validationStatus") MediaValidationStatus validationStatus,
                               @Param("isPublic") Boolean isPublic,
                               @Param("uploadedBy") Long uploadedBy,
                               Pageable pageable);
    
    // Statistical queries
    @Query("SELECT COUNT(m) FROM Media m WHERE m.companyId = :companyId AND m.deletedAt IS NULL")
    Long countActiveByCompanyId(@Param("companyId") Long companyId);
    
    @Query("SELECT COUNT(m) FROM Media m WHERE m.companyId = :companyId AND m.format = :format AND m.deletedAt IS NULL")
    Long countByCompanyIdAndFormat(@Param("companyId") Long companyId, @Param("format") MediaFormat format);
    
    @Query("SELECT COUNT(m) FROM Media m WHERE m.companyId = :companyId AND m.validationStatus = :status AND m.deletedAt IS NULL")
    Long countByCompanyIdAndValidationStatus(@Param("companyId") Long companyId, @Param("status") MediaValidationStatus status);
    
    @Query("SELECT SUM(m.fileSize) FROM Media m WHERE m.companyId = :companyId AND m.deletedAt IS NULL")
    Long getTotalFileSizeByCompanyId(@Param("companyId") Long companyId);
    
    @Query("SELECT AVG(m.fileSize) FROM Media m WHERE m.companyId = :companyId AND m.deletedAt IS NULL")
    Double getAverageFileSizeByCompanyId(@Param("companyId") Long companyId);
    
    // Image-specific queries
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.format IN " +
           "('IMAGE_JPEG', 'IMAGE_PNG', 'IMAGE_GIF', 'IMAGE_WEBP', 'IMAGE_SVG', 'IMAGE_BMP', 'IMAGE_TIFF') AND " +
           "m.deletedAt IS NULL")
    List<Media> findImagesByCompanyId(@Param("companyId") Long companyId);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.format IN " +
           "('IMAGE_JPEG', 'IMAGE_PNG', 'IMAGE_GIF', 'IMAGE_WEBP', 'IMAGE_SVG', 'IMAGE_BMP', 'IMAGE_TIFF') AND " +
           "m.deletedAt IS NULL")
    Page<Media> findImagesByCompanyId(@Param("companyId") Long companyId, Pageable pageable);
    
    // Video-specific queries
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.format IN " +
           "('VIDEO_MP4', 'VIDEO_AVI', 'VIDEO_MOV', 'VIDEO_WMV', 'VIDEO_FLV', 'VIDEO_WEBM', 'VIDEO_MKV') AND " +
           "m.deletedAt IS NULL")
    List<Media> findVideosByCompanyId(@Param("companyId") Long companyId);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.format IN " +
           "('VIDEO_MP4', 'VIDEO_AVI', 'VIDEO_MOV', 'VIDEO_WMV', 'VIDEO_FLV', 'VIDEO_WEBM', 'VIDEO_MKV') AND " +
           "m.deletedAt IS NULL")
    Page<Media> findVideosByCompanyId(@Param("companyId") Long companyId, Pageable pageable);
    
    // Audio-specific queries
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.format IN " +
           "('AUDIO_MP3', 'AUDIO_WAV', 'AUDIO_AAC', 'AUDIO_OGG', 'AUDIO_FLAC', 'AUDIO_M4A') AND " +
           "m.deletedAt IS NULL")
    List<Media> findAudiosByCompanyId(@Param("companyId") Long companyId);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.format IN " +
           "('AUDIO_MP3', 'AUDIO_WAV', 'AUDIO_AAC', 'AUDIO_OGG', 'AUDIO_FLAC', 'AUDIO_M4A') AND " +
           "m.deletedAt IS NULL")
    Page<Media> findAudiosByCompanyId(@Param("companyId") Long companyId, Pageable pageable);
    
    // Document-specific queries
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.format IN " +
           "('DOCUMENT_PDF', 'DOCUMENT_DOC', 'DOCUMENT_DOCX', 'DOCUMENT_XLS', 'DOCUMENT_XLSX', " +
           "'DOCUMENT_PPT', 'DOCUMENT_PPTX', 'DOCUMENT_TXT', 'DOCUMENT_RTF') AND " +
           "m.deletedAt IS NULL")
    List<Media> findDocumentsByCompanyId(@Param("companyId") Long companyId);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.format IN " +
           "('DOCUMENT_PDF', 'DOCUMENT_DOC', 'DOCUMENT_DOCX', 'DOCUMENT_XLS', 'DOCUMENT_XLSX', " +
           "'DOCUMENT_PPT', 'DOCUMENT_PPTX', 'DOCUMENT_TXT', 'DOCUMENT_RTF') AND " +
           "m.deletedAt IS NULL")
    Page<Media> findDocumentsByCompanyId(@Param("companyId") Long companyId, Pageable pageable);
    
    // Maintenance queries
    @Query("SELECT m FROM Media m WHERE m.deletedAt IS NOT NULL")
    List<Media> findDeleted();
    
    @Query("SELECT m FROM Media m WHERE m.deletedAt IS NOT NULL")
    Page<Media> findDeleted(Pageable pageable);
    
    @Query("SELECT m FROM Media m WHERE m.deletedAt < :cutoffDate")
    List<Media> findDeletedBefore(@Param("cutoffDate") LocalDateTime cutoffDate);
    
    @Query("SELECT m FROM Media m WHERE m.validationStatus = 'PENDING' AND m.uploadedAt < :cutoffDate")
    List<Media> findPendingValidationOlderThan(@Param("cutoffDate") LocalDateTime cutoffDate);
    
    // Duplicate detection
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.fileChecksum = :checksum AND m.deletedAt IS NULL")
    List<Media> findDuplicatesByChecksum(@Param("companyId") Long companyId, @Param("checksum") String checksum);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.originalFilename = :filename AND m.fileSize = :fileSize AND m.deletedAt IS NULL")
    List<Media> findPotentialDuplicates(@Param("companyId") Long companyId, @Param("filename") String filename, @Param("fileSize") Long fileSize);
    
    // Storage provider queries
    List<Media> findByStorageProvider(String storageProvider);
    
    @Query("SELECT m FROM Media m WHERE m.storageProvider = :provider AND m.deletedAt IS NULL")
    List<Media> findActiveByStorageProvider(@Param("provider") String provider);
    
    // Recent uploads
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.uploadedAt >= :since AND m.deletedAt IS NULL ORDER BY m.uploadedAt DESC")
    List<Media> findRecentUploads(@Param("companyId") Long companyId, @Param("since") LocalDateTime since);
    
    @Query("SELECT m FROM Media m WHERE m.companyId = :companyId AND m.uploadedAt >= :since AND m.deletedAt IS NULL ORDER BY m.uploadedAt DESC")
    Page<Media> findRecentUploads(@Param("companyId") Long companyId, @Param("since") LocalDateTime since, Pageable pageable);
    
    // Top uploaders
    @Query("SELECT m.uploadedBy, COUNT(m) FROM Media m WHERE m.companyId = :companyId AND m.deletedAt IS NULL GROUP BY m.uploadedBy ORDER BY COUNT(m) DESC")
    List<Object[]> findTopUploadersByCompanyId(@Param("companyId") Long companyId);
    
    // Format statistics
    @Query("SELECT m.format, COUNT(m) FROM Media m WHERE m.companyId = :companyId AND m.deletedAt IS NULL GROUP BY m.format ORDER BY COUNT(m) DESC")
    List<Object[]> getFormatStatisticsByCompanyId(@Param("companyId") Long companyId);
    
    // Custom exists queries
    boolean existsByMediaCode(String mediaCode);
    boolean existsByStoredFilename(String storedFilename);
    boolean existsByFileChecksum(String fileChecksum);
    
    @Query("SELECT CASE WHEN COUNT(m) > 0 THEN true ELSE false END FROM Media m WHERE m.companyId = :companyId AND m.storedFilename = :filename")
    boolean existsByCompanyIdAndStoredFilename(@Param("companyId") Long companyId, @Param("filename") String filename);
}