package com.social.media.repository;

import com.social.media.domain.entity.PostMedia;
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.Modifying;
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 for PostMedia entity operations
 */
@Repository
public interface PostMediaRepository extends JpaRepository<PostMedia, Long> {
    
    // ==========================================
    // BASIC QUERIES BY POST
    // ==========================================
    
    /**
     * Find all media for a specific post ordered by display order
     */
    @Query("SELECT pm FROM PostMedia pm WHERE pm.postId = :postId ORDER BY pm.displayOrder ASC")
    List<PostMedia> findByPostIdOrderByDisplayOrder(@Param("postId") Long postId);
    
    /**
     * Find all media for a specific post with pagination
     */
    @Query("SELECT pm FROM PostMedia pm WHERE pm.postId = :postId ORDER BY pm.displayOrder ASC")
    Page<PostMedia> findByPostIdOrderByDisplayOrder(@Param("postId") Long postId, Pageable pageable);
    
    /**
     * Find primary media for a specific post
     */
    @Query("SELECT pm FROM PostMedia pm WHERE pm.postId = :postId AND pm.isPrimary = true")
    Optional<PostMedia> findPrimaryByPostId(@Param("postId") Long postId);
    
    /**
     * Find secondary media for a specific post (non-primary)
     */
    @Query("SELECT pm FROM PostMedia pm WHERE pm.postId = :postId AND pm.isPrimary = false ORDER BY pm.displayOrder ASC")
    List<PostMedia> findSecondaryByPostId(@Param("postId") Long postId);
    
    /**
     * Count media files for a specific post
     */
    @Query("SELECT COUNT(pm) FROM PostMedia pm WHERE pm.postId = :postId")
    Long countByPostId(@Param("postId") Long postId);
    
    // ==========================================
    // BASIC QUERIES BY MEDIA
    // ==========================================
    
    /**
     * Find all posts using a specific media
     */
    @Query("SELECT pm FROM PostMedia pm WHERE pm.mediaId = :mediaId ORDER BY pm.createdAt DESC")
    List<PostMedia> findByMediaId(@Param("mediaId") Long mediaId);
    
    /**
     * Find posts where specific media is primary
     */
    @Query("SELECT pm FROM PostMedia pm WHERE pm.mediaId = :mediaId AND pm.isPrimary = true")
    List<PostMedia> findPostsWherePrimary(@Param("mediaId") Long mediaId);
    
    /**
     * Count posts using a specific media
     */
    @Query("SELECT COUNT(pm) FROM PostMedia pm WHERE pm.mediaId = :mediaId")
    Long countByMediaId(@Param("mediaId") Long mediaId);
    
    // ==========================================
    // RELATIONSHIP QUERIES
    // ==========================================
    
    /**
     * Check if a post-media relationship exists
     */
    @Query("SELECT COUNT(pm) > 0 FROM PostMedia pm WHERE pm.postId = :postId AND pm.mediaId = :mediaId")
    boolean existsByPostIdAndMediaId(@Param("postId") Long postId, @Param("mediaId") Long mediaId);
    
    /**
     * Find specific post-media relationship
     */
    @Query("SELECT pm FROM PostMedia pm WHERE pm.postId = :postId AND pm.mediaId = :mediaId")
    Optional<PostMedia> findByPostIdAndMediaId(@Param("postId") Long postId, @Param("mediaId") Long mediaId);
    
    /**
     * Find media with specific display order for a post
     */
    @Query("SELECT pm FROM PostMedia pm WHERE pm.postId = :postId AND pm.displayOrder = :order")
    Optional<PostMedia> findByPostIdAndDisplayOrder(@Param("postId") Long postId, @Param("order") Integer order);
    
    // ==========================================
    // DISPLAY ORDER MANAGEMENT
    // ==========================================
    
    /**
     * Get next display order for a post
     */
    @Query("SELECT COALESCE(MAX(pm.displayOrder), 0) + 1 FROM PostMedia pm WHERE pm.postId = :postId")
    Integer getNextDisplayOrder(@Param("postId") Long postId);
    
    /**
     * Get maximum display order for a post
     */
    @Query("SELECT COALESCE(MAX(pm.displayOrder), 0) FROM PostMedia pm WHERE pm.postId = :postId")
    Integer getMaxDisplayOrder(@Param("postId") Long postId);
    
    /**
     * Find media with display order greater than specified value
     */
    @Query("SELECT pm FROM PostMedia pm WHERE pm.postId = :postId AND pm.displayOrder > :order ORDER BY pm.displayOrder ASC")
    List<PostMedia> findByPostIdAndDisplayOrderGreaterThan(@Param("postId") Long postId, @Param("order") Integer order);
    
    /**
     * Find media with display order between range
     */
    @Query("SELECT pm FROM PostMedia pm WHERE pm.postId = :postId AND pm.displayOrder BETWEEN :startOrder AND :endOrder ORDER BY pm.displayOrder ASC")
    List<PostMedia> findByPostIdAndDisplayOrderBetween(@Param("postId") Long postId, @Param("startOrder") Integer startOrder, @Param("endOrder") Integer endOrder);
    
    // ==========================================
    // PRIMARY MEDIA MANAGEMENT
    // ==========================================
    
    /**
     * Remove primary status from all media in a post
     */
    @Modifying
    @Query("UPDATE PostMedia pm SET pm.isPrimary = false WHERE pm.postId = :postId")
    void removePrimaryStatusFromPost(@Param("postId") Long postId);
    
    /**
     * Set specific media as primary (caller should handle removing other primaries)
     */
    @Modifying
    @Query("UPDATE PostMedia pm SET pm.isPrimary = true WHERE pm.id = :id")
    void setPrimaryById(@Param("id") Long id);
    
    /**
     * Check if post has primary media
     */
    @Query("SELECT COUNT(pm) > 0 FROM PostMedia pm WHERE pm.postId = :postId AND pm.isPrimary = true")
    boolean hasPrimaryMedia(@Param("postId") Long postId);
    
    // ==========================================
    // BULK OPERATIONS
    // ==========================================
    
    /**
     * Delete all media relationships for a post
     */
    @Modifying
    @Query("DELETE FROM PostMedia pm WHERE pm.postId = :postId")
    void deleteByPostId(@Param("postId") Long postId);
    
    /**
     * Delete all relationships for a specific media
     */
    @Modifying
    @Query("DELETE FROM PostMedia pm WHERE pm.mediaId = :mediaId")
    void deleteByMediaId(@Param("mediaId") Long mediaId);
    
    /**
     * Update display orders starting from a specific order
     */
    @Modifying
    @Query("UPDATE PostMedia pm SET pm.displayOrder = pm.displayOrder + :increment WHERE pm.postId = :postId AND pm.displayOrder >= :startOrder")
    void incrementDisplayOrdersFrom(@Param("postId") Long postId, @Param("startOrder") Integer startOrder, @Param("increment") Integer increment);
    
    /**
     * Reorder media after a specific position
     */
    @Modifying
    @Query("UPDATE PostMedia pm SET pm.displayOrder = pm.displayOrder - 1 WHERE pm.postId = :postId AND pm.displayOrder > :removedOrder")
    void decrementDisplayOrdersAfter(@Param("postId") Long postId, @Param("removedOrder") Integer removedOrder);
    
    // ==========================================
    // REPORTING AND ANALYTICS
    // ==========================================
    
    /**
     * Get posts with most media files
     */
    @Query("SELECT pm.postId, COUNT(pm) as mediaCount FROM PostMedia pm GROUP BY pm.postId ORDER BY mediaCount DESC")
    List<Object[]> getPostsWithMostMedia(Pageable pageable);
    
    /**
     * Get most used media files
     */
    @Query("SELECT pm.mediaId, COUNT(pm) as usageCount FROM PostMedia pm GROUP BY pm.mediaId ORDER BY usageCount DESC")
    List<Object[]> getMostUsedMedia(Pageable pageable);
    
    /**
     * Get media usage statistics
     */
    @Query("SELECT " +
           "COUNT(DISTINCT pm.postId) as totalPosts, " +
           "COUNT(DISTINCT pm.mediaId) as totalMediaUsed, " +
           "COUNT(pm) as totalRelationships, " +
           "AVG(CAST(pm.displayOrder as double)) as avgDisplayOrder " +
           "FROM PostMedia pm")
    Object[] getMediaUsageStatistics();
    
    /**
     * Find posts with multiple media files
     */
    @Query("SELECT pm.postId FROM PostMedia pm GROUP BY pm.postId HAVING COUNT(pm) > 1")
    List<Long> getPostsWithMultipleMedia();
    
    /**
     * Find posts without primary media
     */
    @Query("SELECT DISTINCT pm.postId FROM PostMedia pm WHERE pm.postId NOT IN " +
           "(SELECT pm2.postId FROM PostMedia pm2 WHERE pm2.isPrimary = true)")
    List<Long> getPostsWithoutPrimaryMedia();
    
    // ==========================================
    // DATE-BASED QUERIES
    // ==========================================
    
    /**
     * Find media relationships created in date range
     */
    @Query("SELECT pm FROM PostMedia pm WHERE pm.createdAt BETWEEN :startDate AND :endDate ORDER BY pm.createdAt DESC")
    List<PostMedia> findByCreatedAtBetween(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
    
    /**
     * Find recent media relationships
     */
    @Query("SELECT pm FROM PostMedia pm WHERE pm.createdAt >= :since ORDER BY pm.createdAt DESC")
    List<PostMedia> findRecentRelationships(@Param("since") LocalDateTime since, Pageable pageable);
    
    /**
     * Count relationships created today
     */
    @Query("SELECT COUNT(pm) FROM PostMedia pm WHERE pm.createdAt >= :startOfDay AND pm.createdAt < :endOfDay")
    Long countTodayRelationships(@Param("startOfDay") LocalDateTime startOfDay, @Param("endOfDay") LocalDateTime endOfDay);
    
    // ==========================================
    // ADVANCED QUERIES
    // ==========================================
    
    /**
     * Find duplicate display orders in a post (data integrity check)
     */
    @Query("SELECT pm.displayOrder FROM PostMedia pm WHERE pm.postId = :postId GROUP BY pm.displayOrder HAVING COUNT(pm) > 1")
    List<Integer> findDuplicateDisplayOrders(@Param("postId") Long postId);
    
    /**
     * Find posts with specific media count
     */
    @Query("SELECT pm.postId FROM PostMedia pm GROUP BY pm.postId HAVING COUNT(pm) = :count")
    List<Long> getPostsWithMediaCount(@Param("count") Long count);
    
    /**
     * Find media used in multiple posts
     */
    @Query("SELECT pm.mediaId FROM PostMedia pm GROUP BY pm.mediaId HAVING COUNT(DISTINCT pm.postId) > 1")
    List<Long> getMediaUsedInMultiplePosts();
    
    /**
     * Find orphaned relationships (where post or media doesn't exist)
     */
    @Query("SELECT pm FROM PostMedia pm WHERE pm.postId NOT IN (SELECT p.id FROM Post p) OR pm.mediaId NOT IN (SELECT m.id FROM Media m)")
    List<PostMedia> findOrphanedRelationships();
    
    /**
     * Get media distribution by display order
     */
    @Query("SELECT pm.displayOrder, COUNT(pm) as count FROM PostMedia pm GROUP BY pm.displayOrder ORDER BY pm.displayOrder")
    List<Object[]> getMediaDistributionByDisplayOrder();
    
    /**
     * Find posts where media is used as primary vs secondary
     */
    @Query("SELECT pm.mediaId, " +
           "SUM(CASE WHEN pm.isPrimary = true THEN 1 ELSE 0 END) as primaryUsage, " +
           "SUM(CASE WHEN pm.isPrimary = false THEN 1 ELSE 0 END) as secondaryUsage " +
           "FROM PostMedia pm GROUP BY pm.mediaId")
    List<Object[]> getMediaUsageByPrimaryStatus();
    
    // ==========================================
    // MAINTENANCE QUERIES
    // ==========================================
    
    /**
     * Normalize display orders for a post (1, 2, 3, ...)
     */
    @Modifying
    @Query(value = """
        UPDATE core_business.post_media pm 
        SET display_order = subq.new_order 
        FROM (
            SELECT id, ROW_NUMBER() OVER (ORDER BY display_order, id) as new_order 
            FROM core_business.post_media 
            WHERE post_id = :postId
        ) subq 
        WHERE pm.id = subq.id
        """, nativeQuery = true)
    void normalizeDisplayOrders(@Param("postId") Long postId);
    
    /**
     * Clean up invalid relationships
     */
    @Modifying
    @Query("DELETE FROM PostMedia pm WHERE pm.displayOrder <= 0")
    void deleteInvalidDisplayOrders();
    
    /**
     * Get health check information
     */
    @Query("SELECT " +
           "COUNT(pm) as totalRelationships, " +
           "COUNT(DISTINCT pm.postId) as uniquePosts, " +
           "COUNT(DISTINCT pm.mediaId) as uniqueMedia, " +
           "COUNT(CASE WHEN pm.isPrimary = true THEN 1 END) as primaryCount, " +
           "MIN(pm.createdAt) as oldestRelationship, " +
           "MAX(pm.createdAt) as newestRelationship " +
           "FROM PostMedia pm")
    Object[] getHealthCheckInfo();
}