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

import com.social.media.infrastructure.persistence.entity.PostEntity;
import com.social.media.infrastructure.persistence.entity.PostStatusEntity;
import com.social.media.infrastructure.persistence.entity.PostTypeEntity;
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 PostEntity - REFACTORED VERSION
 * All queries now require userId and companyId for security isolation
 */
@Repository
public interface PostJpaRepository extends JpaRepository<PostEntity, Long> {
    
    /**
     * Find post by ID with company and user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.id = :id AND p.companyId = :companyId AND " +
           "(p.authorId = :userId OR EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId AND u.type IN ('ADMIN', 'SUPER_ADMIN')))")
    Optional<PostEntity> findByIdAndCompanyIdAndUserId(@Param("id") Long id, 
                                                       @Param("companyId") Long companyId, 
                                                       @Param("userId") Long userId);
    
    /**
     * Find all posts by company with user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<PostEntity> findByCompanyIdAndUserId(@Param("companyId") Long companyId, 
                                             @Param("userId") Long userId, 
                                             Pageable pageable);
    
    /**
     * Find posts by company and status with user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND p.status = :status AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<PostEntity> findByCompanyIdAndStatusAndUserId(@Param("companyId") Long companyId, 
                                                       @Param("status") PostStatusEntity status,
                                                       @Param("userId") Long userId, 
                                                       Pageable pageable);
    
    /**
     * Find posts by company and author with user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND p.authorId = :authorId AND " +
           "(p.authorId = :userId OR EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId AND u.type IN ('ADMIN', 'SUPER_ADMIN')))")
    Page<PostEntity> findByCompanyIdAndAuthorIdAndUserId(@Param("companyId") Long companyId, 
                                                         @Param("authorId") Long authorId,
                                                         @Param("userId") Long userId, 
                                                         Pageable pageable);
    
    /**
     * Find posts by company and category with user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND p.categoryId = :categoryId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<PostEntity> findByCompanyIdAndCategoryIdAndUserId(@Param("companyId") Long companyId, 
                                                           @Param("categoryId") Long categoryId,
                                                           @Param("userId") Long userId, 
                                                           Pageable pageable);
    
    /**
     * Find posts by company and post type with user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND p.postType = :postType AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<PostEntity> findByCompanyIdAndPostTypeAndUserId(@Param("companyId") Long companyId, 
                                                         @Param("postType") PostTypeEntity postType,
                                                         @Param("userId") Long userId, 
                                                         Pageable pageable);
    
    /**
     * Find scheduled posts until specific datetime for company with user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.scheduledFor <= :dateTime AND p.status = 'SCHEDULED' AND p.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<PostEntity> findScheduledPostsUntilByCompanyIdAndUserId(@Param("dateTime") LocalDateTime dateTime,
                                                                @Param("companyId") Long companyId,
                                                                @Param("userId") Long userId);
    
    /**
     * Find scheduled posts by company with user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND p.status = 'SCHEDULED' AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<PostEntity> findScheduledPostsByCompanyIdAndUserId(@Param("companyId") Long companyId,
                                                           @Param("userId") Long userId,
                                                           Pageable pageable);
    
    /**
     * Find published posts by company with user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND p.status = 'PUBLISHED' AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<PostEntity> findPublishedPostsByCompanyIdAndUserId(@Param("companyId") Long companyId,
                                                           @Param("userId") Long userId,
                                                           Pageable pageable);
    
    /**
     * Find draft posts by company with user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND p.status = 'DRAFT' AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<PostEntity> findDraftPostsByCompanyIdAndUserId(@Param("companyId") Long companyId,
                                                        @Param("userId") Long userId,
                                                        Pageable pageable);
    
    /**
     * Find failed posts by company with user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND p.status IN ('FAILED', 'ERROR') AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<PostEntity> findFailedPostsByCompanyIdAndUserId(@Param("companyId") Long companyId,
                                                         @Param("userId") Long userId,
                                                         Pageable pageable);
    
    /**
     * Find archived posts by company with user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND p.status = 'ARCHIVED' AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<PostEntity> findArchivedPostsByCompanyIdAndUserId(@Param("companyId") Long companyId,
                                                           @Param("userId") Long userId,
                                                           Pageable pageable);
    
    /**
     * Find pinned posts by company with user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND p.isPinned = true AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<PostEntity> findPinnedPostsByCompanyIdAndUserId(@Param("companyId") Long companyId,
                                                         @Param("userId") Long userId,
                                                         Pageable pageable);
    
    /**
     * Find posts by company and date range with user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND p.createdAt BETWEEN :start AND :end AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<PostEntity> findByCompanyIdAndCreatedAtBetweenAndUserId(@Param("companyId") Long companyId, 
                                                                @Param("start") LocalDateTime start, 
                                                                @Param("end") LocalDateTime end,
                                                                @Param("userId") Long userId,
                                                                Pageable pageable);
    
    /**
     * Find posts by company and published date range with user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND p.publishedAt BETWEEN :start AND :end AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<PostEntity> findByCompanyIdAndPublishedAtBetweenAndUserId(@Param("companyId") Long companyId, 
                                                                  @Param("start") LocalDateTime start, 
                                                                  @Param("end") LocalDateTime end,
                                                                  @Param("userId") Long userId,
                                                                  Pageable pageable);
    
    /**
     * Find posts by target social account with company and user validation
     */
    @Query("SELECT p FROM PostEntity p JOIN p.targetSocialAccountIds tsai WHERE tsai = :socialAccountId AND p.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<PostEntity> findByTargetSocialAccountAndCompanyIdAndUserId(@Param("socialAccountId") Long socialAccountId,
                                                                   @Param("companyId") Long companyId,
                                                                   @Param("userId") Long userId,
                                                                   Pageable pageable);
    
    /**
     * Find posts ordered by creation date with company and user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId) ORDER BY p.createdAt DESC")
    Page<PostEntity> findByCompanyIdOrderByCreatedAtDescAndUserId(@Param("companyId") Long companyId,
                                                                 @Param("userId") Long userId,
                                                                 Pageable pageable);
    
    /**
     * Find posts ordered by published date with company and user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId) ORDER BY p.publishedAt DESC")
    Page<PostEntity> findByCompanyIdOrderByPublishedAtDescAndUserId(@Param("companyId") Long companyId,
                                                                   @Param("userId") Long userId,
                                                                   Pageable pageable);
    
    /**
     * Find posts ordered by scheduled date with company and user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId) ORDER BY p.scheduledFor ASC")
    Page<PostEntity> findByCompanyIdOrderByScheduledForAscAndUserId(@Param("companyId") Long companyId,
                                                                   @Param("userId") Long userId,
                                                                   Pageable pageable);
    
    /**
     * Find top 10 most engaged posts with company and user validation
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId) " +
           "ORDER BY (p.likeCount + p.shareCount + p.commentCount) DESC")
    List<PostEntity> findTop10MostEngagedByCompanyIdAndUserId(@Param("companyId") Long companyId,
                                                             @Param("userId") Long userId,
                                                             Pageable pageable);
    
    /**
     * Check if content text exists within company
     */
    @Query("SELECT COUNT(p) > 0 FROM PostEntity p WHERE p.contentText = :contentText AND p.companyId = :companyId")
    boolean existsByContentTextAndCompanyId(@Param("contentText") String contentText, 
                                           @Param("companyId") Long companyId);
    
    /**
     * Count posts by company with user validation
     */
    @Query("SELECT COUNT(p) FROM PostEntity p WHERE p.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    long countByCompanyIdAndUserId(@Param("companyId") Long companyId, 
                                  @Param("userId") Long userId);
    
    /**
     * Count posts by company and status with user validation
     */
    @Query("SELECT COUNT(p) FROM PostEntity p WHERE p.companyId = :companyId AND p.status = :status AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    long countByCompanyIdAndStatusAndUserId(@Param("companyId") Long companyId, 
                                           @Param("status") PostStatusEntity status,
                                           @Param("userId") Long userId);
    
    /**
     * Count published posts today with company and user validation
     */
    @Query("SELECT COUNT(p) FROM PostEntity p WHERE p.companyId = :companyId AND p.publishedAt >= :startOfDay AND p.publishedAt < :endOfDay AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    long countPublishedPostsTodayByCompanyIdAndUserId(@Param("companyId") Long companyId, 
                                                     @Param("startOfDay") LocalDateTime startOfDay,
                                                     @Param("endOfDay") LocalDateTime endOfDay,
                                                     @Param("userId") Long userId);
    
    /**
     * Count scheduled posts with company and user validation
     */
    @Query("SELECT COUNT(p) FROM PostEntity p WHERE p.companyId = :companyId AND p.status = 'SCHEDULED' AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    long countScheduledPostsByCompanyIdAndUserId(@Param("companyId") Long companyId,
                                                @Param("userId") Long userId);
    
    /**
     * Find user's own posts (posts authored by the requesting user)
     */
    @Query("SELECT p FROM PostEntity p WHERE p.companyId = :companyId AND p.authorId = :userId")
    Page<PostEntity> findOwnPosts(@Param("companyId") Long companyId, 
                                 @Param("userId") Long userId,
                                 Pageable pageable);
}