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

import com.social.media.domain.campaign.valueobject.CampaignInteractionStatus;
import com.social.media.domain.campaign.valueobject.CampaignInteractionType;
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.LocalDate;
import java.util.List;
import java.util.Optional;

/**
 * JPA Repository for Campaign entity - REFACTORED VERSION
 * All queries now require userId and companyId for security isolation
 */
@Repository
public interface CampaignJpaRepository extends JpaRepository<CampaignJpaEntity, Long> {
    
    /**
     * Find campaign by ID with company and user validation
     */
    @Query("SELECT c FROM CampaignJpaEntity c WHERE c.id = :id AND c.companyId = :companyId AND " +
           "(c.userId = :userId OR EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId AND u.type IN ('ADMIN', 'SUPER_ADMIN')))")
    Optional<CampaignJpaEntity> findByIdAndCompanyIdAndUserId(@Param("id") Long id,
                                                             @Param("companyId") Long companyId,
                                                             @Param("userId") Long userId);
    
    /**
     * Find campaigns by user ID and company validation
     */
    @Query("SELECT c FROM CampaignJpaEntity c WHERE c.userId = :userId AND c.companyId = :companyId")
    List<CampaignJpaEntity> findByUserIdAndCompanyId(@Param("userId") Long userId, @Param("companyId") Long companyId);
    
    /**
     * Find campaigns by user ID and status with company validation
     */
    @Query("SELECT c FROM CampaignJpaEntity c WHERE c.userId = :userId AND c.statusInteration = :status AND c.companyId = :companyId")
    List<CampaignJpaEntity> findByUserIdAndStatusInterationAndCompanyId(@Param("userId") Long userId, 
                                                                        @Param("status") CampaignInteractionStatus status,
                                                                        @Param("companyId") Long companyId);
    
    /**
     * Find campaigns by user ID and interaction type with company validation
     */
    @Query("SELECT c FROM CampaignJpaEntity c WHERE c.userId = :userId AND c.typeInteration = :interactionType AND c.companyId = :companyId")
    List<CampaignJpaEntity> findByUserIdAndTypeInterationAndCompanyId(@Param("userId") Long userId, 
                                                                      @Param("interactionType") CampaignInteractionType interactionType,
                                                                      @Param("companyId") Long companyId);
    
    /**
     * Find campaigns by bot ID with company and user validation
     */
    @Query("SELECT c FROM CampaignJpaEntity c WHERE c.botId = :botId AND c.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<CampaignJpaEntity> findByBotIdAndCompanyIdAndUserId(@Param("botId") Long botId,
                                                            @Param("companyId") Long companyId,
                                                            @Param("userId") Long userId);
    
    /**
     * Find campaigns by account network ID with company and user validation
     */
    @Query("SELECT c FROM CampaignJpaEntity c WHERE c.accountNetworkId = :accountNetworkId AND c.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<CampaignJpaEntity> findByAccountNetworkIdAndCompanyIdAndUserId(@Param("accountNetworkId") Long accountNetworkId,
                                                                       @Param("companyId") Long companyId,
                                                                       @Param("userId") Long userId);
    
    /**
     * Find active campaigns by company with user validation
     */
    @Query("SELECT c FROM CampaignJpaEntity c WHERE c.statusInteration = :status AND c.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<CampaignJpaEntity> findByStatusInterationAndCompanyIdAndUserId(@Param("status") CampaignInteractionStatus status,
                                                                        @Param("companyId") Long companyId,
                                                                        @Param("userId") Long userId);
    
    /**
     * Find campaigns scheduled for a specific date with company and user validation
     */
    @Query("SELECT c FROM CampaignJpaEntity c WHERE c.dateInteration = :date AND c.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<CampaignJpaEntity> findByDateInterationAndCompanyIdAndUserId(@Param("date") LocalDate date,
                                                                      @Param("companyId") Long companyId,
                                                                      @Param("userId") Long userId);
    
    /**
     * Find campaigns by date range with company and user validation
     */
    @Query("SELECT c FROM CampaignJpaEntity c WHERE c.companyId = :companyId AND " +
           "(c.dateStart IS NULL OR c.dateStart <= :endDate) AND " +
           "(c.dateEnd IS NULL OR c.dateEnd >= :startDate) AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<CampaignJpaEntity> findByDateRangeAndCompanyIdAndUserId(@Param("startDate") LocalDate startDate, 
                                                                @Param("endDate") LocalDate endDate,
                                                                @Param("companyId") Long companyId,
                                                                @Param("userId") Long userId);
    
    /**
     * Find campaigns by user and date range with company validation
     */
    @Query("SELECT c FROM CampaignJpaEntity c WHERE c.userId = :userId AND c.companyId = :companyId AND " +
           "(c.dateStart IS NULL OR c.dateStart <= :endDate) AND " +
           "(c.dateEnd IS NULL OR c.dateEnd >= :startDate)")
    List<CampaignJpaEntity> findByUserIdAndDateRangeAndCompanyId(@Param("userId") Long userId,
                                                                @Param("startDate") LocalDate startDate, 
                                                                @Param("endDate") LocalDate endDate,
                                                                @Param("companyId") Long companyId);
    
    /**
     * Find campaigns in execution period with company and user validation
     */
    @Query("SELECT c FROM CampaignJpaEntity c WHERE c.companyId = :companyId AND " +
           "c.dateStart <= :currentDate AND c.dateEnd >= :currentDate AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId)")
    List<CampaignJpaEntity> findInExecutionPeriodAndCompanyIdAndUserId(@Param("currentDate") LocalDate currentDate,
                                                                       @Param("companyId") Long companyId,
                                                                       @Param("userId") Long userId);
    
    /**
     * Find campaigns that need to be started with company validation
     */
    @Query("SELECT c FROM CampaignJpaEntity c WHERE c.companyId = :companyId AND " +
           "c.statusInteration = :waitStatus AND " +
           "c.dateStart <= :currentDate")
    List<CampaignJpaEntity> findCampaignsToStartByCompanyId(@Param("waitStatus") CampaignInteractionStatus waitStatus,
                                                           @Param("currentDate") LocalDate currentDate,
                                                           @Param("companyId") Long companyId);
    
    /**
     * Find campaigns that need to be stopped with company validation
     */
    @Query("SELECT c FROM CampaignJpaEntity c WHERE c.companyId = :companyId AND " +
           "c.statusInteration = :runningStatus AND " +
           "c.dateEnd < :currentDate")
    List<CampaignJpaEntity> findCampaignsToStopByCompanyId(@Param("runningStatus") CampaignInteractionStatus runningStatus,
                                                          @Param("currentDate") LocalDate currentDate,
                                                          @Param("companyId") Long companyId);
    
    /**
     * Check if a campaign name exists for a user within company
     */
    @Query("SELECT COUNT(c) > 0 FROM CampaignJpaEntity c WHERE c.userId = :userId AND c.name = :name AND c.companyId = :companyId")
    boolean existsByUserIdAndNameAndCompanyId(@Param("userId") Long userId, 
                                             @Param("name") String name,
                                             @Param("companyId") Long companyId);
    
    /**
     * Count campaigns by user and company
     */
    @Query("SELECT COUNT(c) FROM CampaignJpaEntity c WHERE c.userId = :userId AND c.companyId = :companyId")
    long countByUserIdAndCompanyId(@Param("userId") Long userId, @Param("companyId") Long companyId);
    
    /**
     * Count campaigns by user, status and company
     */
    @Query("SELECT COUNT(c) FROM CampaignJpaEntity c WHERE c.userId = :userId AND c.statusInteration = :status AND c.companyId = :companyId")
    long countByUserIdAndStatusInterationAndCompanyId(@Param("userId") Long userId, 
                                                      @Param("status") CampaignInteractionStatus status,
                                                      @Param("companyId") Long companyId);
    
    /**
     * Count campaigns by company with user validation
     */
    @Query("SELECT COUNT(c) FROM CampaignJpaEntity 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);
    
    /**
     * Find all campaigns by company with user validation (for admins)
     */
    @Query("SELECT c FROM CampaignJpaEntity c WHERE c.companyId = :companyId AND " +
           "EXISTS (SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId AND u.type IN ('ADMIN', 'SUPER_ADMIN'))")
    List<CampaignJpaEntity> findAllByCompanyIdAndAdminUserId(@Param("companyId") Long companyId, @Param("userId") Long userId);
}