package com.social.media.infrastructure.persistence.repository;

import com.social.media.infrastructure.persistence.entity.MediaEntity;
import com.social.media.infrastructure.persistence.entity.MediaTypeEntity;
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;

/**
 * JPA Repository for MediaEntity - REFACTORED VERSION
 * All queries now require userId and companyId for security isolation
 */
@Repository
public interface MediaJpaRepository extends JpaRepository<MediaEntity, Long> {
    
    /**
     * Find media by ID with company and user validation
     */
    @Query("SELECT m FROM MediaEntity m WHERE m.id = :id AND m.companyId = :companyId AND " +
           "(m.uploadedBy = :userId OR m.isPublic = true OR EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId AND u.type IN ('ADMIN', 'SUPER_ADMIN')))")
    Optional<MediaEntity> findByIdAndCompanyIdAndUserId(@Param("id") Long id,
                                                        @Param("companyId") Long companyId,
                                                        @Param("userId") Long userId);
    
    /**
     * Find all media by company with user validation
     */
    @Query("SELECT m FROM MediaEntity m WHERE m.companyId = :companyId AND " +
           "(m.uploadedBy = :userId OR m.isPublic = true OR EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId AND u.type IN ('ADMIN', 'SUPER_ADMIN')))")
    Page<MediaEntity> findByCompanyIdAndUserId(@Param("companyId") Long companyId,
                                              @Param("userId") Long userId,
                                              Pageable pageable);
    
    /**
     * Find media by company and uploader with user validation
     */
    @Query("SELECT m FROM MediaEntity m WHERE m.companyId = :companyId AND m.uploadedBy = :uploadedBy AND " +
           "(m.uploadedBy = :userId OR EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId AND u.type IN ('ADMIN', 'SUPER_ADMIN')))")
    Page<MediaEntity> findByCompanyIdAndUploadedByAndUserId(@Param("companyId") Long companyId,
                                                           @Param("uploadedBy") Long uploadedBy,
                                                           @Param("userId") Long userId,
                                                           Pageable pageable);
    
    /**
     * Find media by company and media type with user validation
     */
    @Query("SELECT m FROM MediaEntity m WHERE m.companyId = :companyId AND m.mediaType = :mediaType AND " +
           "(m.uploadedBy = :userId OR m.isPublic = true OR EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId AND u.type IN ('ADMIN', 'SUPER_ADMIN')))")
    Page<MediaEntity> findByCompanyIdAndMediaTypeAndUserId(@Param("companyId") Long companyId,
                                                          @Param("mediaType") MediaTypeEntity mediaType,
                                                          @Param("userId") Long userId,
                                                          Pageable pageable);
    
    /**
     * Find media by company and public status with user validation
     */
    @Query("SELECT m FROM MediaEntity m WHERE m.companyId = :companyId AND m.isPublic = :isPublic AND " +
           "(m.uploadedBy = :userId OR m.isPublic = true OR EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId AND u.type IN ('ADMIN', 'SUPER_ADMIN')))")
    Page<MediaEntity> findByCompanyIdAndIsPublicAndUserId(@Param("companyId") Long companyId,
                                                         @Param("isPublic") boolean isPublic,
                                                         @Param("userId") Long userId,
                                                         Pageable pageable);
    
    /**
     * Find media uploaded after specific date with company and user validation
     */
    @Query("SELECT m FROM MediaEntity m WHERE m.companyId = :companyId AND m.uploadedAt > :uploadedAfter AND " +
           "(m.uploadedBy = :userId OR m.isPublic = true OR EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId AND u.type IN ('ADMIN', 'SUPER_ADMIN')))")
    Page<MediaEntity> findByCompanyIdAndUploadedAtAfterAndUserId(@Param("companyId") Long companyId,
                                                                @Param("uploadedAfter") LocalDateTime uploadedAfter,
                                                                @Param("userId") Long userId,
                                                                Pageable pageable);
    
    /**
     * Find media uploaded between dates with company and user validation
     */
    @Query("SELECT m FROM MediaEntity m WHERE m.companyId = :companyId AND m.uploadedAt BETWEEN :start AND :end AND " +
           "(m.uploadedBy = :userId OR m.isPublic = true OR EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId AND u.type IN ('ADMIN', 'SUPER_ADMIN')))")
    Page<MediaEntity> findByCompanyIdAndUploadedAtBetweenAndUserId(@Param("companyId") Long companyId,
                                                                  @Param("start") LocalDateTime start,
                                                                  @Param("end") LocalDateTime end,
                                                                  @Param("userId") Long userId,
                                                                  Pageable pageable);
    
    /**
     * Find media by IDs with company and user validation
     */
    @Query("SELECT m FROM MediaEntity m WHERE m.id IN :mediaIds AND m.companyId = :companyId AND " +
           "(m.uploadedBy = :userId OR m.isPublic = true OR EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId AND u.type IN ('ADMIN', 'SUPER_ADMIN')))")
    List<MediaEntity> findByIdInAndCompanyIdAndUserId(@Param("mediaIds") List<Long> mediaIds,
                                                      @Param("companyId") Long companyId,
                                                      @Param("userId") Long userId);
    
    /**
     * Check if stored file name exists within company
     */
    @Query("SELECT COUNT(m) > 0 FROM MediaEntity m WHERE m.storedFileName = :storedFileName AND m.companyId = :companyId")
    boolean existsByStoredFileNameAndCompanyId(@Param("storedFileName") String storedFileName,
                                              @Param("companyId") Long companyId);
    
    /**
     * Count media by company with user validation
     */
    @Query("SELECT COUNT(m) FROM MediaEntity m WHERE m.companyId = :companyId AND " +
           "(m.uploadedBy = :userId OR m.isPublic = true OR EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId AND u.type IN ('ADMIN', 'SUPER_ADMIN')))")
    long countByCompanyIdAndUserId(@Param("companyId") Long companyId,
                                  @Param("userId") Long userId);
    
    /**
     * Get total file size by company with user validation
     */
    @Query("SELECT COALESCE(SUM(m.fileSize), 0) FROM MediaEntity m WHERE m.companyId = :companyId AND " +
           "(m.uploadedBy = :userId OR m.isPublic = true OR EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId AND u.type IN ('ADMIN', 'SUPER_ADMIN')))")
    Long getTotalFileSizeByCompanyIdAndUserId(@Param("companyId") Long companyId,
                                             @Param("userId") Long userId);
    
    /**
     * Find unused media by company with user validation
     */
    @Query("SELECT m FROM MediaEntity m WHERE m.companyId = :companyId AND m.id NOT IN " +
           "(SELECT pm FROM PostEntity p JOIN p.mediaIds pm WHERE p.companyId = :companyId) AND " +
           "(m.uploadedBy = :userId OR m.isPublic = true OR EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId AND u.type IN ('ADMIN', 'SUPER_ADMIN')))")
    Page<MediaEntity> findUnusedMediaByCompanyIdAndUserId(@Param("companyId") Long companyId,
                                                         @Param("userId") Long userId,
                                                         Pageable pageable);
    
    /**
     * Find user's own media (uploaded by the requesting user)
     */
    @Query("SELECT m FROM MediaEntity m WHERE m.companyId = :companyId AND m.uploadedBy = :userId")
    Page<MediaEntity> findOwnMedia(@Param("companyId") Long companyId,
                                  @Param("userId") Long userId,
                                  Pageable pageable);
    
    /**
     * Find public media by company with user validation
     */
    @Query("SELECT m FROM MediaEntity m WHERE m.companyId = :companyId AND m.isPublic = true AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<MediaEntity> findPublicMediaByCompanyIdAndUserId(@Param("companyId") Long companyId,
                                                         @Param("userId") Long userId,
                                                         Pageable pageable);
    
    /**
     * Find recently uploaded media with company and user validation
     */
    @Query("SELECT m FROM MediaEntity m WHERE m.companyId = :companyId AND m.uploadedAt >= :since AND " +
           "(m.uploadedBy = :userId OR m.isPublic = true OR EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId AND u.type IN ('ADMIN', 'SUPER_ADMIN'))) " +
           "ORDER BY m.uploadedAt DESC")
    Page<MediaEntity> findRecentMediaByCompanyIdAndUserId(@Param("companyId") Long companyId,
                                                         @Param("since") LocalDateTime since,
                                                         @Param("userId") Long userId,
                                                         Pageable pageable);
}