package com.social.media.repository;

import com.social.media.domain.entity.Post;
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 PostRepository extends JpaRepository<Post, Long> {
    
    // Find by unique post code
    Optional<Post> findByPostCode(String postCode);
    
    // Find by company
    Page<Post> findByCompanyIdAndDeletedAtIsNull(Long companyId, Pageable pageable);
    
    // Find by author
    Page<Post> findByAuthorIdAndDeletedAtIsNull(Long authorId, Pageable pageable);
    
    // Find by status
    Page<Post> findByStatusAndDeletedAtIsNull(Post.PostStatus status, Pageable pageable);
    
    // Find by post type
    Page<Post> findByPostTypeAndDeletedAtIsNull(Post.PostType postType, Pageable pageable);
    
    // Find by company and status
    Page<Post> findByCompanyIdAndStatusAndDeletedAtIsNull(Long companyId, Post.PostStatus status, Pageable pageable);
    
    // Find pinned posts
    Page<Post> findByCompanyIdAndIsPinnedTrueAndDeletedAtIsNull(Long companyId, Pageable pageable);
    
    // Find scheduled posts
    Page<Post> findByStatusAndScheduledForBeforeAndDeletedAtIsNull(Post.PostStatus status, LocalDateTime dateTime, Pageable pageable);
    
    // Find posts scheduled for execution
    @Query("SELECT p FROM Post p WHERE p.status = 'SCHEDULED' AND p.scheduledFor <= :now AND p.deletedAt IS NULL")
    List<Post> findScheduledPostsForExecution(@Param("now") LocalDateTime now);
    
    // Find recent posts by company
    @Query("SELECT p FROM Post p WHERE p.companyId = :companyId AND p.deletedAt IS NULL ORDER BY p.createdAt DESC")
    Page<Post> findRecentPostsByCompany(@Param("companyId") Long companyId, Pageable pageable);
    
    // Find popular posts by engagement
    @Query("SELECT p FROM Post p WHERE p.companyId = :companyId AND p.status = 'PUBLISHED' AND p.deletedAt IS NULL " +
           "ORDER BY (p.likeCount + p.shareCount + p.commentCount) DESC")
    Page<Post> findPopularPostsByCompany(@Param("companyId") Long companyId, Pageable pageable);
    
    // Search posts by content
    @Query("SELECT p FROM Post p WHERE p.companyId = :companyId AND " +
           "(LOWER(p.contentText) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR " +
           "LOWER(p.tags) LIKE LOWER(CONCAT('%', :searchTerm, '%'))) AND " +
           "p.deletedAt IS NULL")
    Page<Post> searchPostsByContent(@Param("companyId") Long companyId, @Param("searchTerm") String searchTerm, Pageable pageable);
    
    // Find posts by date range
    @Query("SELECT p FROM Post p WHERE p.companyId = :companyId AND " +
           "p.createdAt BETWEEN :startDate AND :endDate AND p.deletedAt IS NULL")
    Page<Post> findPostsByDateRange(@Param("companyId") Long companyId, 
                                   @Param("startDate") LocalDateTime startDate, 
                                   @Param("endDate") LocalDateTime endDate, 
                                   Pageable pageable);
    
    // Find posts by category
    Page<Post> findByCategoryIdAndDeletedAtIsNull(Long categoryId, Pageable pageable);
    
    // Count posts by status for company
    @Query("SELECT COUNT(p) FROM Post p WHERE p.companyId = :companyId AND p.status = :status AND p.deletedAt IS NULL")
    Long countPostsByCompanyAndStatus(@Param("companyId") Long companyId, @Param("status") Post.PostStatus status);
    
    // Count total posts for company
    @Query("SELECT COUNT(p) FROM Post p WHERE p.companyId = :companyId AND p.deletedAt IS NULL")
    Long countPostsByCompany(@Param("companyId") Long companyId);
    
    // Find posts published in date range
    @Query("SELECT p FROM Post p WHERE p.companyId = :companyId AND p.status = 'PUBLISHED' AND " +
           "p.publishedAt BETWEEN :startDate AND :endDate AND p.deletedAt IS NULL")
    List<Post> findPublishedPostsInDateRange(@Param("companyId") Long companyId, 
                                           @Param("startDate") LocalDateTime startDate, 
                                           @Param("endDate") LocalDateTime endDate);
    
    // Get engagement metrics for company
    @Query("SELECT SUM(p.likeCount + p.shareCount + p.commentCount) FROM Post p " +
           "WHERE p.companyId = :companyId AND p.status = 'PUBLISHED' AND p.deletedAt IS NULL")
    Long getTotalEngagementByCompany(@Param("companyId") Long companyId);
    
    // Find top performing posts
    @Query("SELECT p FROM Post p WHERE p.companyId = :companyId AND p.status = 'PUBLISHED' AND " +
           "p.deletedAt IS NULL ORDER BY p.viewCount DESC")
    Page<Post> findTopViewedPostsByCompany(@Param("companyId") Long companyId, Pageable pageable);
    
    // Find posts that need to be published
    @Query("SELECT p FROM Post p WHERE p.status = 'SCHEDULED' AND p.scheduledFor <= :now AND p.deletedAt IS NULL")
    List<Post> findPostsReadyForPublication(@Param("now") LocalDateTime now);
    
    // Check if post code exists
    boolean existsByPostCode(String postCode);
    
    // Check if post exists and is not deleted
    boolean existsByIdAndDeletedAtIsNull(Long id);
    
    // Find posts by multiple statuses
    @Query("SELECT p FROM Post p WHERE p.companyId = :companyId AND p.status IN :statuses AND p.deletedAt IS NULL")
    Page<Post> findByCompanyAndStatuses(@Param("companyId") Long companyId, 
                                       @Param("statuses") List<Post.PostStatus> statuses, 
                                       Pageable pageable);
}