package com.social.media.service.impl;

import com.social.media.dto.PostMediaDto;
import com.social.media.domain.entity.PostMedia;
import com.social.media.repository.PostMediaRepository;
import com.social.media.service.PostMediaService;
import com.social.media.mapper.PostMediaMapper;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Implementation of PostMediaService
 */
@Service
@RequiredArgsConstructor
@Slf4j
@Transactional
public class PostMediaServiceImpl implements PostMediaService {
    
    private final PostMediaRepository postMediaRepository;
    private final PostMediaMapper postMediaMapper;
    
    // ==========================================
    // BASIC CRUD OPERATIONS
    // ==========================================
    
    @Override
    public PostMediaDto createPostMedia(PostMediaDto postMediaDto) {
        log.info("Creating post-media relationship: post={}, media={}", 
                postMediaDto.getPostId(), postMediaDto.getMediaId());
        
        // Check if relationship already exists
        if (relationshipExists(postMediaDto.getPostId(), postMediaDto.getMediaId())) {
            throw new IllegalArgumentException("Post-media relationship already exists");
        }
        
        // Set display order if not provided
        if (postMediaDto.getDisplayOrder() == null) {
            postMediaDto.setDisplayOrder(getNextDisplayOrder(postMediaDto.getPostId()));
        }
        
        // Handle primary media logic
        if (postMediaDto.getIsPrimary() != null && postMediaDto.getIsPrimary()) {
            removePrimaryStatusFromPost(postMediaDto.getPostId());
        }
        
        PostMedia entity = postMediaMapper.toEntity(postMediaDto);
        PostMedia saved = postMediaRepository.save(entity);
        
        log.info("Created post-media relationship with ID: {}", saved.getId());
        return postMediaMapper.toDto(saved);
    }
    
    @Override
    public List<PostMediaDto> createPostMediaBatch(List<PostMediaDto> postMediaDtos) {
        log.info("Creating {} post-media relationships in batch", postMediaDtos.size());
        
        List<PostMediaDto> results = new ArrayList<>();
        for (PostMediaDto dto : postMediaDtos) {
            try {
                results.add(createPostMedia(dto));
            } catch (Exception e) {
                log.error("Failed to create post-media relationship: {}", dto, e);
            }
        }
        
        log.info("Successfully created {} out of {} post-media relationships", 
                results.size(), postMediaDtos.size());
        return results;
    }
    
    @Override
    @Transactional(readOnly = true)
    public Optional<PostMediaDto> getPostMediaById(Long id) {
        log.debug("Fetching post-media relationship by ID: {}", id);
        return postMediaRepository.findById(id)
                .map(postMediaMapper::toDto);
    }
    
    @Override
    public PostMediaDto updatePostMedia(Long id, PostMediaDto postMediaDto) {
        log.info("Updating post-media relationship: {}", id);
        
        PostMedia existing = postMediaRepository.findById(id)
                .orElseThrow(() -> new IllegalArgumentException("PostMedia not found: " + id));
        
        // Handle primary media changes
        if (postMediaDto.getIsPrimary() != null && postMediaDto.getIsPrimary() && 
            !existing.isPrimaryMedia()) {
            removePrimaryStatusFromPost(existing.getPostId());
        }
        
        postMediaMapper.updateEntity(existing, postMediaDto);
        PostMedia saved = postMediaRepository.save(existing);
        
        log.info("Updated post-media relationship: {}", id);
        return postMediaMapper.toDto(saved);
    }
    
    @Override
    public void deletePostMedia(Long id) {
        log.info("Deleting post-media relationship: {}", id);
        
        PostMedia existing = postMediaRepository.findById(id)
                .orElseThrow(() -> new IllegalArgumentException("PostMedia not found: " + id));
        
        Long postId = existing.getPostId();
        Integer displayOrder = existing.getDisplayOrder();
        
        postMediaRepository.delete(existing);
        
        // Reorder remaining media
        postMediaRepository.decrementDisplayOrdersAfter(postId, displayOrder);
        
        // Auto-assign primary if deleted media was primary
        if (existing.isPrimaryMedia()) {
            autoAssignPrimaryMedia(postId);
        }
        
        log.info("Deleted post-media relationship: {}", id);
    }
    
    @Override
    @Transactional(readOnly = true)
    public Page<PostMediaDto> getAllPostMedia(Pageable pageable) {
        log.debug("Fetching all post-media relationships with pagination");
        return postMediaRepository.findAll(pageable)
                .map(postMediaMapper::toDto);
    }
    
    // ==========================================
    // POST-SPECIFIC OPERATIONS
    // ==========================================
    
    @Override
    @Transactional(readOnly = true)
    public List<PostMediaDto> getMediaForPost(Long postId) {
        log.debug("Fetching media for post: {}", postId);
        return postMediaRepository.findByPostIdOrderByDisplayOrder(postId)
                .stream()
                .map(postMediaMapper::toDto)
                .collect(Collectors.toList());
    }
    
    @Override
    @Transactional(readOnly = true)
    public Page<PostMediaDto> getMediaForPost(Long postId, Pageable pageable) {
        log.debug("Fetching media for post with pagination: {}", postId);
        return postMediaRepository.findByPostIdOrderByDisplayOrder(postId, pageable)
                .map(postMediaMapper::toDto);
    }
    
    @Override
    @Transactional(readOnly = true)
    public Optional<PostMediaDto> getPrimaryMediaForPost(Long postId) {
        log.debug("Fetching primary media for post: {}", postId);
        return postMediaRepository.findPrimaryByPostId(postId)
                .map(postMediaMapper::toDto);
    }
    
    @Override
    @Transactional(readOnly = true)
    public List<PostMediaDto> getSecondaryMediaForPost(Long postId) {
        log.debug("Fetching secondary media for post: {}", postId);
        return postMediaRepository.findSecondaryByPostId(postId)
                .stream()
                .map(postMediaMapper::toDto)
                .collect(Collectors.toList());
    }
    
    @Override
    public PostMediaDto addMediaToPost(Long postId, Long mediaId, Integer displayOrder, Boolean isPrimary) {
        log.info("Adding media {} to post {} with order {} and primary status {}", 
                mediaId, postId, displayOrder, isPrimary);
        
        PostMediaDto dto = PostMediaDto.create(postId, mediaId, displayOrder, isPrimary);
        return createPostMedia(dto);
    }
    
    @Override
    public List<PostMediaDto> addMultipleMediaToPost(Long postId, List<Long> mediaIds) {
        log.info("Adding {} media files to post {}", mediaIds.size(), postId);
        
        List<PostMediaDto> results = new ArrayList<>();
        Integer nextOrder = getNextDisplayOrder(postId);
        
        for (int i = 0; i < mediaIds.size(); i++) {
            Long mediaId = mediaIds.get(i);
            Boolean isPrimary = (i == 0) && !postHasPrimaryMedia(postId);
            
            PostMediaDto dto = PostMediaDto.create(postId, mediaId, nextOrder + i, isPrimary);
            try {
                results.add(createPostMedia(dto));
            } catch (Exception e) {
                log.error("Failed to add media {} to post {}", mediaId, postId, e);
            }
        }
        
        return results;
    }
    
    @Override
    public void removeMediaFromPost(Long postId, Long mediaId) {
        log.info("Removing media {} from post {}", mediaId, postId);
        
        Optional<PostMedia> relationship = postMediaRepository.findByPostIdAndMediaId(postId, mediaId);
        if (relationship.isPresent()) {
            deletePostMedia(relationship.get().getId());
        } else {
            log.warn("Post-media relationship not found: post={}, media={}", postId, mediaId);
        }
    }
    
    @Override
    public void removeAllMediaFromPost(Long postId) {
        log.info("Removing all media from post: {}", postId);
        postMediaRepository.deleteByPostId(postId);
    }
    
    @Override
    @Transactional(readOnly = true)
    public Long countMediaForPost(Long postId) {
        return postMediaRepository.countByPostId(postId);
    }
    
    @Override
    @Transactional(readOnly = true)
    public boolean postHasMedia(Long postId) {
        return countMediaForPost(postId) > 0;
    }
    
    @Override
    @Transactional(readOnly = true)
    public boolean postHasPrimaryMedia(Long postId) {
        return postMediaRepository.hasPrimaryMedia(postId);
    }
    
    // ==========================================
    // MEDIA-SPECIFIC OPERATIONS
    // ==========================================
    
    @Override
    @Transactional(readOnly = true)
    public List<PostMediaDto> getPostsUsingMedia(Long mediaId) {
        log.debug("Fetching posts using media: {}", mediaId);
        return postMediaRepository.findByMediaId(mediaId)
                .stream()
                .map(postMediaMapper::toDto)
                .collect(Collectors.toList());
    }
    
    @Override
    @Transactional(readOnly = true)
    public List<PostMediaDto> getPostsWherePrimary(Long mediaId) {
        log.debug("Fetching posts where media {} is primary", mediaId);
        return postMediaRepository.findPostsWherePrimary(mediaId)
                .stream()
                .map(postMediaMapper::toDto)
                .collect(Collectors.toList());
    }
    
    @Override
    @Transactional(readOnly = true)
    public Long countPostsUsingMedia(Long mediaId) {
        return postMediaRepository.countByMediaId(mediaId);
    }
    
    @Override
    @Transactional(readOnly = true)
    public boolean mediaIsUsed(Long mediaId) {
        return countPostsUsingMedia(mediaId) > 0;
    }
    
    @Override
    @Transactional(readOnly = true)
    public boolean mediaIsUsedAsPrimary(Long mediaId) {
        return !getPostsWherePrimary(mediaId).isEmpty();
    }
    
    @Override
    public void removeMediaFromAllPosts(Long mediaId) {
        log.info("Removing media {} from all posts", mediaId);
        postMediaRepository.deleteByMediaId(mediaId);
    }
    
    // ==========================================
    // PRIMARY MEDIA MANAGEMENT
    // ==========================================
    
    @Override
    public PostMediaDto setPrimaryMedia(Long postId, Long mediaId) {
        log.info("Setting media {} as primary for post {}", mediaId, postId);
        
        // Remove primary status from all media in the post
        removePrimaryStatusFromPost(postId);
        
        // Find and update the specific relationship
        PostMedia relationship = postMediaRepository.findByPostIdAndMediaId(postId, mediaId)
                .orElseThrow(() -> new IllegalArgumentException(
                    "Post-media relationship not found: post=" + postId + ", media=" + mediaId));
        
        relationship.setAsPrimary();
        PostMedia saved = postMediaRepository.save(relationship);
        
        log.info("Set media {} as primary for post {}", mediaId, postId);
        return postMediaMapper.toDto(saved);
    }
    
    @Override
    public void removePrimaryStatusFromPost(Long postId) {
        log.debug("Removing primary status from all media in post: {}", postId);
        postMediaRepository.removePrimaryStatusFromPost(postId);
    }
    
    @Override
    public Optional<PostMediaDto> autoAssignPrimaryMedia(Long postId) {
        log.info("Auto-assigning primary media for post: {}", postId);
        
        List<PostMedia> mediaList = postMediaRepository.findByPostIdOrderByDisplayOrder(postId);
        if (!mediaList.isEmpty()) {
            PostMedia first = mediaList.get(0);
            first.setAsPrimary();
            PostMedia saved = postMediaRepository.save(first);
            
            log.info("Auto-assigned media {} as primary for post {}", first.getMediaId(), postId);
            return Optional.of(postMediaMapper.toDto(saved));
        }
        
        return Optional.empty();
    }
    
    @Override
    @Transactional(readOnly = true)
    public boolean validatePrimaryMediaConstraints(Long postId) {
        List<PostMedia> primaryMedia = postMediaRepository.findByPostIdOrderByDisplayOrder(postId)
                .stream()
                .filter(PostMedia::isPrimaryMedia)
                .collect(Collectors.toList());
        
        return primaryMedia.size() <= 1;
    }
    
    // ==========================================
    // DISPLAY ORDER MANAGEMENT
    // ==========================================
    
    @Override
    public List<PostMediaDto> reorderPostMedia(Long postId, List<Long> mediaIdsInOrder) {
        log.info("Reordering media for post {}: {}", postId, mediaIdsInOrder);
        
        List<PostMediaDto> results = new ArrayList<>();
        for (int i = 0; i < mediaIdsInOrder.size(); i++) {
            Long mediaId = mediaIdsInOrder.get(i);
            Optional<PostMedia> relationship = postMediaRepository.findByPostIdAndMediaId(postId, mediaId);
            
            if (relationship.isPresent()) {
                PostMedia entity = relationship.get();
                entity.setDisplayOrder(i + 1);
                PostMedia saved = postMediaRepository.save(entity);
                results.add(postMediaMapper.toDto(saved));
            }
        }
        
        log.info("Reordered {} media files for post {}", results.size(), postId);
        return results;
    }
    
    @Override
    public PostMediaDto moveMediaUp(Long postId, Long mediaId) {
        log.info("Moving media {} up in post {}", mediaId, postId);
        
        PostMedia relationship = postMediaRepository.findByPostIdAndMediaId(postId, mediaId)
                .orElseThrow(() -> new IllegalArgumentException("Relationship not found"));
        
        if (relationship.getDisplayOrder() > 1) {
            // Find media at previous position
            Optional<PostMedia> previousMedia = postMediaRepository
                    .findByPostIdAndDisplayOrder(postId, relationship.getDisplayOrder() - 1);
            
            if (previousMedia.isPresent()) {
                // Swap positions
                Integer currentOrder = relationship.getDisplayOrder();
                relationship.setDisplayOrder(currentOrder - 1);
                previousMedia.get().setDisplayOrder(currentOrder);
                
                postMediaRepository.save(previousMedia.get());
                PostMedia saved = postMediaRepository.save(relationship);
                
                return postMediaMapper.toDto(saved);
            }
        }
        
        return postMediaMapper.toDto(relationship);
    }
    
    @Override
    public PostMediaDto moveMediaDown(Long postId, Long mediaId) {
        log.info("Moving media {} down in post {}", mediaId, postId);
        
        PostMedia relationship = postMediaRepository.findByPostIdAndMediaId(postId, mediaId)
                .orElseThrow(() -> new IllegalArgumentException("Relationship not found"));
        
        // Find media at next position
        Optional<PostMedia> nextMedia = postMediaRepository
                .findByPostIdAndDisplayOrder(postId, relationship.getDisplayOrder() + 1);
        
        if (nextMedia.isPresent()) {
            // Swap positions
            Integer currentOrder = relationship.getDisplayOrder();
            relationship.setDisplayOrder(currentOrder + 1);
            nextMedia.get().setDisplayOrder(currentOrder);
            
            postMediaRepository.save(nextMedia.get());
            PostMedia saved = postMediaRepository.save(relationship);
            
            return postMediaMapper.toDto(saved);
        }
        
        return postMediaMapper.toDto(relationship);
    }
    
    @Override
    public PostMediaDto moveMediaToPosition(Long postId, Long mediaId, Integer newPosition) {
        log.info("Moving media {} to position {} in post {}", mediaId, newPosition, postId);
        
        PostMedia relationship = postMediaRepository.findByPostIdAndMediaId(postId, mediaId)
                .orElseThrow(() -> new IllegalArgumentException("Relationship not found"));
        
        Integer currentPosition = relationship.getDisplayOrder();
        if (currentPosition.equals(newPosition)) {
            return postMediaMapper.toDto(relationship);
        }
        
        // Get all media for the post
        List<PostMedia> allMedia = postMediaRepository.findByPostIdOrderByDisplayOrder(postId);
        
        // Remove current item and insert at new position
        allMedia.removeIf(pm -> pm.getId().equals(relationship.getId()));
        allMedia.add(Math.min(newPosition - 1, allMedia.size()), relationship);
        
        // Update all display orders
        for (int i = 0; i < allMedia.size(); i++) {
            allMedia.get(i).setDisplayOrder(i + 1);
        }
        
        postMediaRepository.saveAll(allMedia);
        
        return postMediaMapper.toDto(relationship);
    }
    
    @Override
    public PostMediaDto moveMediaToFirst(Long postId, Long mediaId) {
        return moveMediaToPosition(postId, mediaId, 1);
    }
    
    @Override
    public PostMediaDto moveMediaToLast(Long postId, Long mediaId) {
        Integer maxOrder = getNextDisplayOrder(postId) - 1;
        return moveMediaToPosition(postId, mediaId, maxOrder);
    }
    
    @Override
    public List<PostMediaDto> normalizeDisplayOrders(Long postId) {
        log.info("Normalizing display orders for post: {}", postId);
        
        postMediaRepository.normalizeDisplayOrders(postId);
        
        return getMediaForPost(postId);
    }
    
    @Override
    @Transactional(readOnly = true)
    public Integer getNextDisplayOrder(Long postId) {
        return postMediaRepository.getNextDisplayOrder(postId);
    }
    
    // ==========================================
    // RELATIONSHIP VALIDATION
    // ==========================================
    
    @Override
    @Transactional(readOnly = true)
    public boolean relationshipExists(Long postId, Long mediaId) {
        return postMediaRepository.existsByPostIdAndMediaId(postId, mediaId);
    }
    
    @Override
    @Transactional(readOnly = true)
    public Optional<PostMediaDto> getRelationship(Long postId, Long mediaId) {
        return postMediaRepository.findByPostIdAndMediaId(postId, mediaId)
                .map(postMediaMapper::toDto);
    }
    
    @Override
    @Transactional(readOnly = true)
    public boolean validateRelationship(PostMediaDto postMediaDto) {
        return postMediaDto.isValidRelationship();
    }
    
    @Override
    @Transactional(readOnly = true)
    public List<PostMediaDto> findDuplicateRelationships() {
        // This would require a more complex query to find actual duplicates
        // For now, return empty list as the unique constraint prevents duplicates
        return new ArrayList<>();
    }
    
    @Override
    @Transactional(readOnly = true)
    public List<PostMediaDto> findOrphanedRelationships() {
        return postMediaRepository.findOrphanedRelationships()
                .stream()
                .map(postMediaMapper::toDto)
                .collect(Collectors.toList());
    }
    
    // ==========================================
    // BULK OPERATIONS
    // ==========================================
    
    @Override
    public List<PostMediaDto> copyMediaFromPost(Long sourcePostId, Long targetPostId) {
        log.info("Copying media from post {} to post {}", sourcePostId, targetPostId);
        
        List<PostMedia> sourceMedia = postMediaRepository.findByPostIdOrderByDisplayOrder(sourcePostId);
        List<PostMediaDto> results = new ArrayList<>();
        Integer nextOrder = getNextDisplayOrder(targetPostId);
        
        for (int i = 0; i < sourceMedia.size(); i++) {
            PostMedia source = sourceMedia.get(i);
            PostMediaDto dto = PostMediaDto.create(
                targetPostId, 
                source.getMediaId(), 
                nextOrder + i,
                false // Don't copy primary status
            );
            
            try {
                results.add(createPostMedia(dto));
            } catch (Exception e) {
                log.error("Failed to copy media {} from post {} to post {}", 
                         source.getMediaId(), sourcePostId, targetPostId, e);
            }
        }
        
        return results;
    }
    
    @Override
    public List<PostMediaDto> moveMediaFromPost(Long sourcePostId, Long targetPostId) {
        log.info("Moving media from post {} to post {}", sourcePostId, targetPostId);
        
        List<PostMediaDto> copied = copyMediaFromPost(sourcePostId, targetPostId);
        removeAllMediaFromPost(sourcePostId);
        
        return copied;
    }
    
    @Override
    public List<PostMediaDto> duplicatePostMediaStructure(Long sourcePostId, Long targetPostId) {
        log.info("Duplicating media structure from post {} to post {}", sourcePostId, targetPostId);
        
        // Remove existing media from target
        removeAllMediaFromPost(targetPostId);
        
        // Copy structure with same primary status and orders
        List<PostMedia> sourceMedia = postMediaRepository.findByPostIdOrderByDisplayOrder(sourcePostId);
        List<PostMediaDto> results = new ArrayList<>();
        
        for (PostMedia source : sourceMedia) {
            PostMediaDto dto = PostMediaDto.create(
                targetPostId,
                source.getMediaId(),
                source.getDisplayOrder(),
                source.isPrimaryMedia()
            );
            
            try {
                results.add(createPostMedia(dto));
            } catch (Exception e) {
                log.error("Failed to duplicate media structure", e);
            }
        }
        
        return results;
    }
    
    @Override
    public List<PostMediaDto> mergeMediaFromPosts(List<Long> sourcePostIds, Long targetPostId) {
        log.info("Merging media from posts {} to post {}", sourcePostIds, targetPostId);
        
        List<PostMediaDto> results = new ArrayList<>();
        for (Long sourcePostId : sourcePostIds) {
            results.addAll(copyMediaFromPost(sourcePostId, targetPostId));
        }
        
        return results;
    }
    
    @Override
    public List<PostMediaDto> updateDisplayOrdersBatch(List<PostMediaDto> postMediaDtos) {
        log.info("Updating display orders in batch for {} relationships", postMediaDtos.size());
        
        List<PostMediaDto> results = new ArrayList<>();
        for (PostMediaDto dto : postMediaDtos) {
            try {
                PostMediaDto updated = updatePostMedia(dto.getId(), dto);
                results.add(updated);
            } catch (Exception e) {
                log.error("Failed to update display order for relationship {}", dto.getId(), e);
            }
        }
        
        return results;
    }
    
    // ==========================================
    // SEARCH AND FILTERING - Stub implementations
    // ==========================================
    
    @Override
    @Transactional(readOnly = true)
    public Page<PostMediaDto> searchPostMedia(Long postId, Long mediaId, Boolean isPrimary, 
                                            Integer minDisplayOrder, Integer maxDisplayOrder, Pageable pageable) {
        // Implementation would build dynamic query based on provided criteria
        // For now, return basic pagination
        return getAllPostMedia(pageable);
    }
    
    @Override
    @Transactional(readOnly = true)
    public List<Long> getPostsWithMultipleMedia() {
        return postMediaRepository.getPostsWithMultipleMedia();
    }
    
    @Override
    @Transactional(readOnly = true)
    public List<Long> getPostsWithoutPrimaryMedia() {
        return postMediaRepository.getPostsWithoutPrimaryMedia();
    }
    
    @Override
    @Transactional(readOnly = true)
    public List<Long> getMediaUsedInMultiplePosts() {
        return postMediaRepository.getMediaUsedInMultiplePosts();
    }
    
    @Override
    @Transactional(readOnly = true)
    public List<Long> getPostsWithMediaCount(Long count) {
        return postMediaRepository.getPostsWithMediaCount(count);
    }
    
    // ==========================================
    // STATISTICS AND ANALYTICS - Stub implementations
    // ==========================================
    
    @Override
    @Transactional(readOnly = true)
    public Map<String, Object> getMediaUsageStatistics() {
        Object[] stats = postMediaRepository.getMediaUsageStatistics();
        Map<String, Object> result = new HashMap<>();
        
        if (stats != null && stats.length >= 4) {
            result.put("totalPosts", stats[0]);
            result.put("totalMediaUsed", stats[1]);
            result.put("totalRelationships", stats[2]);
            result.put("avgDisplayOrder", stats[3]);
        }
        
        return result;
    }
    
    @Override
    @Transactional(readOnly = true)
    public List<Map<String, Object>> getPostsWithMostMedia(int limit) {
        Pageable pageable = PageRequest.of(0, limit);
        List<Object[]> results = postMediaRepository.getPostsWithMostMedia(pageable);
        
        return results.stream()
                .map(row -> {
                    Map<String, Object> map = new HashMap<>();
                    map.put("postId", row[0]);
                    map.put("mediaCount", row[1]);
                    return map;
                })
                .collect(Collectors.toList());
    }
    
    @Override
    @Transactional(readOnly = true)
    public List<Map<String, Object>> getMostUsedMedia(int limit) {
        Pageable pageable = PageRequest.of(0, limit);
        List<Object[]> results = postMediaRepository.getMostUsedMedia(pageable);
        
        return results.stream()
                .map(row -> {
                    Map<String, Object> map = new HashMap<>();
                    map.put("mediaId", row[0]);
                    map.put("usageCount", row[1]);
                    return map;
                })
                .collect(Collectors.toList());
    }
    
    @Override
    @Transactional(readOnly = true)
    public Map<Integer, Long> getMediaDistributionByDisplayOrder() {
        List<Object[]> results = postMediaRepository.getMediaDistributionByDisplayOrder();
        
        return results.stream()
                .collect(Collectors.toMap(
                    row -> (Integer) row[0],
                    row -> (Long) row[1]
                ));
    }
    
    @Override
    @Transactional(readOnly = true)
    public Map<Long, Map<String, Long>> getMediaUsageByPrimaryStatus() {
        List<Object[]> results = postMediaRepository.getMediaUsageByPrimaryStatus();
        
        return results.stream()
                .collect(Collectors.toMap(
                    row -> (Long) row[0],
                    row -> {
                        Map<String, Long> usage = new HashMap<>();
                        usage.put("primary", (Long) row[1]);
                        usage.put("secondary", (Long) row[2]);
                        return usage;
                    }
                ));
    }
    
    @Override
    @Transactional(readOnly = true)
    public Double getAverageMediaCountPerPost() {
        Object[] stats = postMediaRepository.getMediaUsageStatistics();
        if (stats != null && stats.length >= 3) {
            Long totalPosts = (Long) stats[0];
            Long totalRelationships = (Long) stats[2];
            return totalPosts > 0 ? (double) totalRelationships / totalPosts : 0.0;
        }
        return 0.0;
    }
    
    @Override
    @Transactional(readOnly = true)
    public Double getPrimaryMediaPercentage() {
        Long totalRelationships = postMediaRepository.count();
        if (totalRelationships == 0) return 0.0;
        
        // Count primary relationships - this would need a proper query
        // For now, return estimated value
        return 25.0; // Placeholder
    }
    
    // ==========================================
    // DATE-BASED OPERATIONS - Stub implementations
    // ==========================================
    
    @Override
    @Transactional(readOnly = true)
    public List<PostMediaDto> getRelationshipsInDateRange(LocalDateTime startDate, LocalDateTime endDate) {
        return postMediaRepository.findByCreatedAtBetween(startDate, endDate)
                .stream()
                .map(postMediaMapper::toDto)
                .collect(Collectors.toList());
    }
    
    @Override
    @Transactional(readOnly = true)
    public List<PostMediaDto> getRecentRelationships(int hours, int limit) {
        LocalDateTime since = LocalDateTime.now().minusHours(hours);
        Pageable pageable = PageRequest.of(0, limit);
        
        return postMediaRepository.findRecentRelationships(since, pageable)
                .stream()
                .map(postMediaMapper::toDto)
                .collect(Collectors.toList());
    }
    
    @Override
    @Transactional(readOnly = true)
    public Long countTodayRelationships() {
        LocalDateTime startOfDay = LocalDateTime.now().toLocalDate().atStartOfDay();
        LocalDateTime endOfDay = startOfDay.plusDays(1);
        return postMediaRepository.countTodayRelationships(startOfDay, endOfDay);
    }
    
    @Override
    @Transactional(readOnly = true)
    public Map<String, Long> getRelationshipCreationTimeline(int days) {
        // This would require date-based grouping queries
        // Returning placeholder for now
        Map<String, Long> timeline = new HashMap<>();
        timeline.put("today", countTodayRelationships());
        return timeline;
    }
    
    // ==========================================
    // MAINTENANCE AND HEALTH - Stub implementations
    // ==========================================
    
    @Override
    @Transactional(readOnly = true)
    public Map<String, Object> validateAllRelationshipsIntegrity() {
        Map<String, Object> result = new HashMap<>();
        
        List<PostMediaDto> orphaned = findOrphanedRelationships();
        result.put("orphanedRelationships", orphaned.size());
        result.put("orphanedList", orphaned);
        
        return result;
    }
    
    @Override
    public int fixDisplayOrderGaps() {
        log.info("Fixing display order gaps");
        
        List<Long> postIds = postMediaRepository.findAll()
                .stream()
                .map(PostMedia::getPostId)
                .distinct()
                .collect(Collectors.toList());
        
        int fixed = 0;
        for (Long postId : postIds) {
            try {
                normalizeDisplayOrders(postId);
                fixed++;
            } catch (Exception e) {
                log.error("Failed to fix display orders for post {}", postId, e);
            }
        }
        
        return fixed;
    }
    
    @Override
    public int cleanupInvalidRelationships() {
        log.info("Cleaning up invalid relationships");
        
        postMediaRepository.deleteInvalidDisplayOrders();
        log.info("Cleaned up invalid relationships");
        
        // Since the repository method returns void, we return 0 as placeholder
        // In a real implementation, you might want to count before deleting
        return 0;
    }
    
    @Override
    @Transactional(readOnly = true)
    public Map<String, Object> getHealthCheckInfo() {
        Object[] health = postMediaRepository.getHealthCheckInfo();
        Map<String, Object> result = new HashMap<>();
        
        if (health != null && health.length >= 6) {
            result.put("totalRelationships", health[0]);
            result.put("uniquePosts", health[1]);
            result.put("uniqueMedia", health[2]);
            result.put("primaryCount", health[3]);
            result.put("oldestRelationship", health[4]);
            result.put("newestRelationship", health[5]);
        }
        
        return result;
    }
    
    @Override
    public int rebuildAllDisplayOrders() {
        log.info("Rebuilding all display orders");
        return fixDisplayOrderGaps();
    }
    
    @Override
    public Map<Long, List<Integer>> findAndFixDuplicateDisplayOrders() {
        Map<Long, List<Integer>> duplicates = new HashMap<>();
        
        List<Long> postIds = postMediaRepository.findAll()
                .stream()
                .map(PostMedia::getPostId)
                .distinct()
                .collect(Collectors.toList());
        
        for (Long postId : postIds) {
            List<Integer> duplicateOrders = postMediaRepository.findDuplicateDisplayOrders(postId);
            if (!duplicateOrders.isEmpty()) {
                duplicates.put(postId, duplicateOrders);
                normalizeDisplayOrders(postId); // Fix them
            }
        }
        
        return duplicates;
    }
    
    // ==========================================
    // IMPORT/EXPORT OPERATIONS - Stub implementations
    // ==========================================
    
    @Override
    @Transactional(readOnly = true)
    public List<PostMediaDto> exportPostMediaRelationships(Long postId) {
        return getMediaForPost(postId);
    }
    
    @Override
    public List<PostMediaDto> importPostMediaRelationships(Long postId, List<PostMediaDto> relationships) {
        log.info("Importing {} relationships for post {}", relationships.size(), postId);
        
        // Clear existing relationships
        removeAllMediaFromPost(postId);
        
        // Import new relationships
        List<PostMediaDto> results = new ArrayList<>();
        for (PostMediaDto dto : relationships) {
            dto.setId(null); // Clear ID for new creation
            dto.setPostId(postId); // Ensure correct post ID
            try {
                results.add(createPostMedia(dto));
            } catch (Exception e) {
                log.error("Failed to import relationship: {}", dto, e);
            }
        }
        
        return results;
    }
    
    @Override
    @Transactional(readOnly = true)
    public List<PostMediaDto> exportAllRelationships() {
        return postMediaRepository.findAll()
                .stream()
                .map(postMediaMapper::toDto)
                .collect(Collectors.toList());
    }
    
    @Override
    @Transactional(readOnly = true)
    public Map<String, Object> generateRelationshipSummary(Long postId) {
        Map<String, Object> summary = new HashMap<>();
        
        List<PostMediaDto> media = getMediaForPost(postId);
        summary.put("totalMedia", media.size());
        summary.put("hasPrimaryMedia", postHasPrimaryMedia(postId));
        summary.put("primaryMedia", getPrimaryMediaForPost(postId).orElse(null));
        summary.put("mediaList", media);
        
        return summary;
    }
}