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

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

/**
 * Spring Data JPA Repository for CampaignExecution - REFACTORED VERSION
 * All queries now require userId and companyId for security isolation
 */
@Repository
public interface CampaignExecutionJpaRepository extends JpaRepository<CampaignExecutionEntity, Long> {
    
    /**
     * Find campaign execution by ID with company and user validation
     */
    @Query("SELECT e FROM CampaignExecutionEntity e WHERE e.id = :id AND " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = e.campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Optional<CampaignExecutionEntity> findByIdAndCompanyIdAndUserId(@Param("id") Long id,
                                                                   @Param("companyId") Long companyId,
                                                                   @Param("userId") Long userId);
    
    /**
     * Find campaign execution by execution code with company and user validation
     */
    @Query("SELECT e FROM CampaignExecutionEntity e WHERE e.executionCode = :executionCode AND " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = e.campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Optional<CampaignExecutionEntity> findByExecutionCodeAndCompanyIdAndUserId(@Param("executionCode") String executionCode,
                                                                              @Param("companyId") Long companyId,
                                                                              @Param("userId") Long userId);
    
    /**
     * Find campaign executions by campaign ID with company and user validation
     */
    @Query("SELECT e FROM CampaignExecutionEntity e WHERE e.campaignId = :campaignId AND " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = :campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<CampaignExecutionEntity> findByCampaignIdAndCompanyIdAndUserId(@Param("campaignId") Long campaignId,
                                                                        @Param("companyId") Long companyId,
                                                                        @Param("userId") Long userId);
    
    /**
     * Find campaign executions by campaign ID (paginated) with company and user validation
     */
    @Query("SELECT e FROM CampaignExecutionEntity e WHERE e.campaignId = :campaignId AND " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = :campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<CampaignExecutionEntity> findByCampaignIdAndCompanyIdAndUserId(@Param("campaignId") Long campaignId,
                                                                        @Param("companyId") Long companyId,
                                                                        @Param("userId") Long userId,
                                                                        Pageable pageable);
    
    /**
     * Find campaign executions by campaign ID and status with company and user validation
     */
    @Query("SELECT e FROM CampaignExecutionEntity e WHERE e.campaignId = :campaignId AND e.status = :status AND " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = :campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<CampaignExecutionEntity> findByCampaignIdAndStatusAndCompanyIdAndUserId(@Param("campaignId") Long campaignId,
                                                                                @Param("status") CampaignExecutionEntity.ExecutionStatusEntity status,
                                                                                @Param("companyId") Long companyId,
                                                                                @Param("userId") Long userId);
    
    /**
     * Find campaign executions by campaign ID and action type with company and user validation
     */
    @Query("SELECT e FROM CampaignExecutionEntity e WHERE e.campaignId = :campaignId AND e.actionType = :actionType AND " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = :campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<CampaignExecutionEntity> findByCampaignIdAndActionTypeAndCompanyIdAndUserId(@Param("campaignId") Long campaignId,
                                                                                    @Param("actionType") String actionType,
                                                                                    @Param("companyId") Long companyId,
                                                                                    @Param("userId") Long userId);
    
    /**
     * Find executions due by company with user validation
     */
    @Query("SELECT e FROM CampaignExecutionEntity e WHERE e.status = :status AND e.plannedExecutionDate <= :now AND " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = e.campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<CampaignExecutionEntity> findExecutionsDueByCompanyIdAndUserId(@Param("status") CampaignExecutionEntity.ExecutionStatusEntity status,
                                                                        @Param("now") LocalDateTime now,
                                                                        @Param("companyId") Long companyId,
                                                                        @Param("userId") Long userId);
    
    /**
     * Find executions for retry by company with user validation
     */
    @Query("SELECT e FROM CampaignExecutionEntity e WHERE e.status = :status AND e.retryCount < e.maxRetries AND " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = e.campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<CampaignExecutionEntity> findExecutionsForRetryByCompanyIdAndUserId(@Param("status") CampaignExecutionEntity.ExecutionStatusEntity status,
                                                                            @Param("companyId") Long companyId,
                                                                            @Param("userId") Long userId);
    
    /**
     * Count executions by campaign ID with company and user validation
     */
    @Query("SELECT COUNT(e) FROM CampaignExecutionEntity e WHERE e.campaignId = :campaignId AND " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = :campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    long countByCampaignIdAndCompanyIdAndUserId(@Param("campaignId") Long campaignId,
                                               @Param("companyId") Long companyId,
                                               @Param("userId") Long userId);
    
    /**
     * Count executions by campaign ID and status with company and user validation
     */
    @Query("SELECT COUNT(e) FROM CampaignExecutionEntity e WHERE e.campaignId = :campaignId AND e.status = :status AND " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = :campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    long countByCampaignIdAndStatusAndCompanyIdAndUserId(@Param("campaignId") Long campaignId,
                                                        @Param("status") CampaignExecutionEntity.ExecutionStatusEntity status,
                                                        @Param("companyId") Long companyId,
                                                        @Param("userId") Long userId);
    
    /**
     * Calculate average execution duration by campaign ID with company and user validation
     */
    @Query("SELECT AVG(e.executionDurationMs) FROM CampaignExecutionEntity e WHERE e.campaignId = :campaignId AND e.status = 'COMPLETED' AND " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = :campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Double averageExecutionDurationByCampaignIdAndCompanyIdAndUserId(@Param("campaignId") Long campaignId,
                                                                    @Param("companyId") Long companyId,
                                                                    @Param("userId") Long userId);
    
    /**
     * Find executions by campaign ID ordered by execution date with company and user validation
     */
    @Query("SELECT e FROM CampaignExecutionEntity e WHERE e.campaignId = :campaignId AND " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = :campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId) " +
           "ORDER BY e.actualExecutionDate DESC")
    Page<CampaignExecutionEntity> findByCampaignIdOrderByExecutionDateDescAndCompanyIdAndUserId(@Param("campaignId") Long campaignId,
                                                                                                @Param("companyId") Long companyId,
                                                                                                @Param("userId") Long userId,
                                                                                                Pageable pageable);
    
    /**
     * Find recent actions by target with company and user validation
     */
    @Query("SELECT e FROM CampaignExecutionEntity e WHERE e.targetUserId = :targetUserId AND e.actionType = :actionType AND e.actualExecutionDate >= :since AND " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = e.campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<CampaignExecutionEntity> findRecentActionsByTargetAndCompanyIdAndUserId(@Param("targetUserId") String targetUserId,
                                                                                 @Param("actionType") String actionType,
                                                                                 @Param("since") LocalDateTime since,
                                                                                 @Param("companyId") Long companyId,
                                                                                 @Param("userId") Long userId);
    
    /**
     * Check if execution code exists with company and user validation
     */
    @Query("SELECT COUNT(e) > 0 FROM CampaignExecutionEntity e WHERE e.executionCode = :executionCode AND " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = e.campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    boolean existsByExecutionCodeAndCompanyIdAndUserId(@Param("executionCode") String executionCode,
                                                       @Param("companyId") Long companyId,
                                                       @Param("userId") Long userId);
    
    /**
     * Find last execution date by campaign ID with company and user validation
     */
    @Query("SELECT MAX(e.actualExecutionDate) FROM CampaignExecutionEntity e WHERE e.campaignId = :campaignId AND " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = :campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Optional<LocalDateTime> findLastExecutionDateByCampaignIdAndCompanyIdAndUserId(@Param("campaignId") Long campaignId,
                                                                                   @Param("companyId") Long companyId,
                                                                                   @Param("userId") Long userId);
    
    /**
     * Find executions by company with user validation
     */
    @Query("SELECT e FROM CampaignExecutionEntity e WHERE " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = e.campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<CampaignExecutionEntity> findByCompanyIdAndUserId(@Param("companyId") Long companyId,
                                                          @Param("userId") Long userId,
                                                          Pageable pageable);
    
    /**
     * Find executions by company and status with user validation
     */
    @Query("SELECT e FROM CampaignExecutionEntity e WHERE e.status = :status AND " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = e.campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<CampaignExecutionEntity> findByStatusAndCompanyIdAndUserId(@Param("status") CampaignExecutionEntity.ExecutionStatusEntity status,
                                                                   @Param("companyId") Long companyId,
                                                                   @Param("userId") Long userId,
                                                                   Pageable pageable);
    
    /**
     * Find executions by company and date range with user validation
     */
    @Query("SELECT e FROM CampaignExecutionEntity e WHERE e.actualExecutionDate BETWEEN :start AND :end AND " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = e.campaignId AND c.companyId = :companyId) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<CampaignExecutionEntity> findByDateRangeAndCompanyIdAndUserId(@Param("start") LocalDateTime start,
                                                                       @Param("end") LocalDateTime end,
                                                                       @Param("companyId") Long companyId,
                                                                       @Param("userId") Long userId,
                                                                       Pageable pageable);
    
    /**
     * Count executions by company with user validation
     */
    @Query("SELECT COUNT(e) FROM CampaignExecutionEntity e WHERE " +
           "EXISTS (SELECT c FROM AutomationCampaignEntity c WHERE c.id = e.campaignId AND c.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);
}