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

import com.social.media.infrastructure.persistence.entity.AutomationCampaignEntity;
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 AutomationCampaign - REFACTORED VERSION
 * All queries now require userId and companyId for security isolation
 */
@Repository
public interface AutomationCampaignJpaRepository extends JpaRepository<AutomationCampaignEntity, Long> {
    
    /**
     * Find automation campaign by ID with company and user validation
     */
    @Query("SELECT c FROM AutomationCampaignEntity c WHERE c.id = :id AND c.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Optional<AutomationCampaignEntity> findByIdAndCompanyIdAndUserId(@Param("id") Long id,
                                                                    @Param("companyId") Long companyId,
                                                                    @Param("userId") Long userId);
    
    /**
     * Find automation campaign by code with company and user validation
     */
    @Query("SELECT c FROM AutomationCampaignEntity c WHERE c.campaignCode = :campaignCode AND c.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Optional<AutomationCampaignEntity> findByCampaignCodeAndCompanyIdAndUserId(@Param("campaignCode") String campaignCode,
                                                                              @Param("companyId") Long companyId,
                                                                              @Param("userId") Long userId);
    
    /**
     * Find automation campaigns by company with user validation
     */
    @Query("SELECT c FROM AutomationCampaignEntity c WHERE c.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<AutomationCampaignEntity> findByCompanyIdAndUserId(@Param("companyId") Long companyId, @Param("userId") Long userId);
    
    /**
     * Find automation campaigns by company with pagination and user validation
     */
    @Query("SELECT c FROM AutomationCampaignEntity c WHERE c.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Page<AutomationCampaignEntity> findByCompanyIdAndUserId(@Param("companyId") Long companyId, 
                                                           @Param("userId") Long userId, 
                                                           Pageable pageable);
    
    /**
     * Find automation campaigns by company and status with user validation
     */
    @Query("SELECT c FROM AutomationCampaignEntity c WHERE c.companyId = :companyId AND c.status = :status AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<AutomationCampaignEntity> findByCompanyIdAndStatusAndUserId(@Param("companyId") Long companyId, 
                                                                    @Param("status") AutomationCampaignEntity.CampaignStatusEntity status,
                                                                    @Param("userId") Long userId);
    
    /**
     * Find automation campaigns by company and campaign type with user validation
     */
    @Query("SELECT c FROM AutomationCampaignEntity c WHERE c.companyId = :companyId AND c.campaignType = :campaignType AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<AutomationCampaignEntity> findByCompanyIdAndCampaignTypeAndUserId(@Param("companyId") Long companyId, 
                                                                          @Param("campaignType") String campaignType,
                                                                          @Param("userId") Long userId);
    
    /**
     * Find automation campaigns by social account with company and user validation
     */
    @Query("SELECT c FROM AutomationCampaignEntity c WHERE c.socialAccountId = :socialAccountId AND c.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<AutomationCampaignEntity> findBySocialAccountIdAndCompanyIdAndUserId(@Param("socialAccountId") Long socialAccountId,
                                                                             @Param("companyId") Long companyId,
                                                                             @Param("userId") Long userId);
    
    /**
     * Find campaigns to start (system level - no user validation needed for automation)
     */
    @Query("SELECT c FROM AutomationCampaignEntity c WHERE c.status = :status AND c.plannedStartDate <= :now AND c.isActive = true")
    List<AutomationCampaignEntity> findCampaignsToStart(@Param("status") AutomationCampaignEntity.CampaignStatusEntity status, @Param("now") LocalDateTime now);
    
    /**
     * Find campaigns to end by company (system level - no user validation needed for automation)
     */
    @Query("SELECT c FROM AutomationCampaignEntity c WHERE c.status = :status AND c.plannedEndDate <= :now AND c.companyId = :companyId")
    List<AutomationCampaignEntity> findCampaignsToEndByCompanyId(@Param("status") AutomationCampaignEntity.CampaignStatusEntity status, 
                                                                @Param("now") LocalDateTime now,
                                                                @Param("companyId") Long companyId);
    
    /**
     * Count automation campaigns by company with user validation
     */
    @Query("SELECT COUNT(c) FROM AutomationCampaignEntity c WHERE 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);
    
    /**
     * Count automation campaigns by company and status with user validation
     */
    @Query("SELECT COUNT(c) FROM AutomationCampaignEntity c WHERE c.companyId = :companyId AND c.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") AutomationCampaignEntity.CampaignStatusEntity status,
                                           @Param("userId") Long userId);
    
    /**
     * Count automation campaigns by status (system level)
     */
    @Query("SELECT COUNT(c) FROM AutomationCampaignEntity c WHERE c.status = :status")
    long countByStatus(@Param("status") AutomationCampaignEntity.CampaignStatusEntity status);
    
    /**
     * Sum executed actions by company with user validation
     */
    @Query("SELECT SUM(c.totalExecutedActions) FROM AutomationCampaignEntity c WHERE c.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Long sumExecutedActionsByCompanyIdAndUserId(@Param("companyId") Long companyId, @Param("userId") Long userId);
    
    /**
     * Sum successful actions by company with user validation
     */
    @Query("SELECT SUM(c.successfulActions) FROM AutomationCampaignEntity c WHERE c.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    Long sumSuccessfulActionsByCompanyIdAndUserId(@Param("companyId") Long companyId, @Param("userId") Long userId);
    
    /**
     * Find stalled campaigns by company with user validation
     */
    @Query("SELECT c FROM AutomationCampaignEntity c WHERE c.lastExecutionDate < :cutoffDate AND c.status = :status AND c.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<AutomationCampaignEntity> findStalledCampaignsByCompanyIdAndUserId(@Param("cutoffDate") LocalDateTime cutoffDate, 
                                                                           @Param("status") AutomationCampaignEntity.CampaignStatusEntity status,
                                                                           @Param("companyId") Long companyId,
                                                                           @Param("userId") Long userId);
    
    /**
     * Check if campaign code exists within company
     */
    @Query("SELECT COUNT(c) > 0 FROM AutomationCampaignEntity c WHERE c.campaignCode = :campaignCode AND c.companyId = :companyId")
    boolean existsByCampaignCodeAndCompanyId(@Param("campaignCode") String campaignCode, @Param("companyId") Long companyId);
    
    /**
     * Check if campaign name exists within company
     */
    @Query("SELECT COUNT(c) > 0 FROM AutomationCampaignEntity c WHERE c.companyId = :companyId AND c.name = :name")
    boolean existsByCompanyIdAndName(@Param("companyId") Long companyId, @Param("name") String name);
    
    /**
     * Find active campaigns by company with user validation
     */
    @Query("SELECT c FROM AutomationCampaignEntity c WHERE c.companyId = :companyId AND c.isActive = true AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<AutomationCampaignEntity> findActiveCampaignsByCompanyIdAndUserId(@Param("companyId") Long companyId, @Param("userId") Long userId);
    
    /**
     * Find campaigns by date range with company and user validation
     */
    @Query("SELECT c FROM AutomationCampaignEntity c WHERE c.companyId = :companyId AND " +
           "c.createdAt BETWEEN :startDate AND :endDate AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<AutomationCampaignEntity> findByCompanyIdAndDateRangeAndUserId(@Param("companyId") Long companyId,
                                                                       @Param("startDate") LocalDateTime startDate,
                                                                       @Param("endDate") LocalDateTime endDate,
                                                                       @Param("userId") Long userId);
    
    /**
     * Find campaigns scheduled to start with company and user validation
     */
    @Query("SELECT c FROM AutomationCampaignEntity c WHERE c.companyId = :companyId AND " +
           "c.plannedStartDate <= :now AND c.status = :status AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<AutomationCampaignEntity> findScheduledToStartByCompanyIdAndUserId(@Param("companyId") Long companyId,
                                                                           @Param("now") LocalDateTime now,
                                                                           @Param("status") AutomationCampaignEntity.CampaignStatusEntity status,
                                                                           @Param("userId") Long userId);
}