package com.social.media.repository;

import com.social.media.domain.entity.SocialAccount;
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 SocialAccountRepository extends JpaRepository<SocialAccount, Long> {
    
    // Basic CRUD operations with soft delete support
    Page<SocialAccount> findByDeletedFalse(Pageable pageable);
    
    Optional<SocialAccount> findByIdAndDeletedFalse(Long id);
    
    // Find by unique account code
    Optional<SocialAccount> findByAccountCode(String accountCode);
    
    // Find by company
    Page<SocialAccount> findByCompanyIdAndDeletedFalse(Long companyId, Pageable pageable);
    
    // Find by company and active status
    Page<SocialAccount> findByCompanyIdAndActiveAndDeletedFalse(Long companyId, Boolean active, Pageable pageable);
    
    // Find active accounts by company
    Page<SocialAccount> findByCompanyIdAndActiveTrueAndDeletedFalse(Long companyId, Pageable pageable);
    
    // Find by social network
    Page<SocialAccount> findBySocialNetworkIdAndDeletedFalse(Long socialNetworkId, Pageable pageable);
    
    // Find by company and social network
    Page<SocialAccount> findByCompanyIdAndSocialNetworkIdAndDeletedFalse(Long companyId, Long socialNetworkId, Pageable pageable);
    
    // Find by connection status
    Page<SocialAccount> findByConnectionStatusAndDeletedFalse(SocialAccount.ConnectionStatus connectionStatus, Pageable pageable);
    
    // Find connected accounts by company
    Page<SocialAccount> findByCompanyIdAndConnectionStatusAndDeletedFalse(Long companyId, SocialAccount.ConnectionStatus connectionStatus, Pageable pageable);
    
    // Find by responsible user
    Page<SocialAccount> findByResponsibleUserIdAndDeletedFalse(Long responsibleUserId, Pageable pageable);
    
    // Find by username
    Optional<SocialAccount> findByUsernameAndSocialNetworkIdAndDeletedFalse(String username, Long socialNetworkId);
    
    // Find by username pattern
    @Query("SELECT sa FROM SocialAccount sa WHERE LOWER(sa.username) LIKE LOWER(CONCAT('%', :username, '%')) AND sa.deleted = false")
    Page<SocialAccount> findByUsernameContainingIgnoreCase(@Param("username") String username, Pageable pageable);
    
    // Find accounts needing sync
    @Query("SELECT sa FROM SocialAccount sa WHERE sa.nextSyncDate <= :now AND sa.active = true AND sa.connectionStatus = 'CONNECTED' AND sa.deleted = false")
    List<SocialAccount> findAccountsNeedingSync(@Param("now") LocalDateTime now);
    
    // Find accounts by engagement rate range
    @Query(value = "SELECT * FROM core_business.social_accounts sa WHERE " +
           "CAST(sa.metrics ->> 'engagement' AS DOUBLE PRECISION) BETWEEN :minEngagement AND :maxEngagement " +
           "AND sa.active = true AND sa.deleted = false", nativeQuery = true)
    Page<SocialAccount> findByEngagementRateBetween(@Param("minEngagement") Double minEngagement, 
                                                   @Param("maxEngagement") Double maxEngagement, 
                                                   Pageable pageable);
    
    // Find accounts by followers count range
    @Query(value = "SELECT * FROM core_business.social_accounts sa WHERE " +
           "CAST(sa.metrics ->> 'followers' AS INTEGER) BETWEEN :minFollowers AND :maxFollowers " +
           "AND sa.active = true AND sa.deleted = false", nativeQuery = true)
    Page<SocialAccount> findByFollowersCountBetween(@Param("minFollowers") Integer minFollowers, 
                                                   @Param("maxFollowers") Integer maxFollowers, 
                                                   Pageable pageable);
    
    // Find top accounts by followers for company
    @Query(value = "SELECT * FROM core_business.social_accounts sa WHERE sa.company_id = :companyId AND sa.active = true AND sa.deleted = false " +
           "ORDER BY CAST(sa.metrics ->> 'followers' AS INTEGER) DESC", nativeQuery = true)
    Page<SocialAccount> findTopAccountsByFollowers(@Param("companyId") Long companyId, Pageable pageable);
    
    // Find top accounts by engagement for company
    @Query(value = "SELECT * FROM core_business.social_accounts sa WHERE sa.company_id = :companyId AND sa.active = true AND sa.deleted = false " +
           "ORDER BY CAST(sa.metrics ->> 'engagement' AS DOUBLE PRECISION) DESC", nativeQuery = true)
    Page<SocialAccount> findTopAccountsByEngagement(@Param("companyId") Long companyId, Pageable pageable);
    
    // Search accounts by display name or username
    @Query("SELECT sa FROM SocialAccount sa WHERE sa.companyId = :companyId AND " +
           "(LOWER(sa.displayName) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR " +
           "LOWER(sa.username) LIKE LOWER(CONCAT('%', :searchTerm, '%'))) AND " +
           "sa.deleted = false")
    Page<SocialAccount> searchAccountsByNameOrUsername(@Param("companyId") Long companyId, 
                                                      @Param("searchTerm") String searchTerm, 
                                                      Pageable pageable);
    
    // Find accounts by verification status
    Page<SocialAccount> findByVerifiedAndDeletedFalse(Boolean verified, Pageable pageable);
    
    // Find private/public accounts
    Page<SocialAccount> findByIsPrivateAndDeletedFalse(Boolean isPrivate, Pageable pageable);
    
    // Find accounts by account type
    Page<SocialAccount> findByAccountTypeAndDeletedFalse(String accountType, Pageable pageable);
    
    // Find accounts by business category
    Page<SocialAccount> findByBusinessCategoryAndDeletedFalse(String businessCategory, Pageable pageable);
    
    // Find accounts that haven't been updated recently
    @Query("SELECT sa FROM SocialAccount sa WHERE sa.lastMetricsUpdate < :cutoffDate AND sa.active = true AND sa.deleted = false")
    List<SocialAccount> findAccountsWithStaleMetrics(@Param("cutoffDate") LocalDateTime cutoffDate);
    
    // Find accounts with sync errors
    Page<SocialAccount> findByLastSyncErrorIsNotNullAndDeletedFalse(Pageable pageable);
    
    // Find accounts by company with specific connection statuses
    @Query("SELECT sa FROM SocialAccount sa WHERE sa.companyId = :companyId AND sa.connectionStatus IN :statuses AND sa.deleted = false")
    Page<SocialAccount> findByCompanyAndConnectionStatuses(@Param("companyId") Long companyId, 
                                                          @Param("statuses") List<SocialAccount.ConnectionStatus> statuses, 
                                                          Pageable pageable);
    
    // Count accounts by company
    @Query("SELECT COUNT(sa) FROM SocialAccount sa WHERE sa.companyId = :companyId AND sa.deleted = false")
    Long countAccountsByCompany(@Param("companyId") Long companyId);
    
    // Count active accounts by company
    @Query("SELECT COUNT(sa) FROM SocialAccount sa WHERE sa.companyId = :companyId AND sa.active = true AND sa.deleted = false")
    Long countActiveAccountsByCompany(@Param("companyId") Long companyId);
    
    // Count connected accounts by company
    @Query("SELECT COUNT(sa) FROM SocialAccount sa WHERE sa.companyId = :companyId AND sa.connectionStatus = 'CONNECTED' AND sa.deleted = false")
    Long countConnectedAccountsByCompany(@Param("companyId") Long companyId);
    
    // Count accounts by social network
    @Query("SELECT COUNT(sa) FROM SocialAccount sa WHERE sa.socialNetworkId = :socialNetworkId AND sa.deleted = false")
    Long countAccountsBySocialNetwork(@Param("socialNetworkId") Long socialNetworkId);
    
    // Get total followers for company
    @Query(value = "SELECT COALESCE(SUM(CAST(sa.metrics ->> 'followers' AS INTEGER)), 0) FROM core_business.social_accounts sa " +
           "WHERE sa.company_id = :companyId AND sa.active = true AND sa.deleted = false", nativeQuery = true)
    Long getTotalFollowersByCompany(@Param("companyId") Long companyId);
    
    // Get average engagement rate for company
    @Query(value = "SELECT COALESCE(AVG(CAST(sa.metrics ->> 'engagement' AS DOUBLE PRECISION)), 0.0) FROM core_business.social_accounts sa " +
           "WHERE sa.company_id = :companyId AND sa.active = true AND sa.deleted = false", nativeQuery = true)
    Double getAverageEngagementByCompany(@Param("companyId") Long companyId);
    
    // Find accounts with targets below current performance
    @Query(value = "SELECT * FROM core_business.social_accounts sa WHERE " +
           "sa.follower_target IS NOT NULL AND " +
           "CAST(sa.metrics ->> 'followers' AS INTEGER) >= sa.follower_target AND " +
           "sa.active = true AND sa.deleted = false", nativeQuery = true)
    List<SocialAccount> findAccountsReachingFollowerTargets();
    
    // Find accounts with engagement above target
    @Query(value = "SELECT * FROM core_business.social_accounts sa WHERE " +
           "sa.engagement_target IS NOT NULL AND " +
           "CAST(sa.metrics ->> 'engagement' AS DOUBLE PRECISION) >= sa.engagement_target AND " +
           "sa.active = true AND sa.deleted = false", nativeQuery = true)
    List<SocialAccount> findAccountsReachingEngagementTargets();
    
    // Check if account code exists
    boolean existsByAccountCode(String accountCode);
    
    // Check if username exists for social network in company
    boolean existsByCompanyIdAndSocialNetworkIdAndUsernameAndDeletedFalse(Long companyId, Long socialNetworkId, String username);
    
    // Check if account exists and is not deleted
    boolean existsByIdAndDeletedFalse(Long id);
    
    // Find accounts registered in date range
    @Query("SELECT sa FROM SocialAccount sa WHERE sa.registrationDate BETWEEN :startDate AND :endDate AND sa.deleted = false")
    Page<SocialAccount> findAccountsRegisteredBetween(@Param("startDate") LocalDateTime startDate, 
                                                     @Param("endDate") LocalDateTime endDate, 
                                                     Pageable pageable);
    
    // Find accounts by location
    @Query("SELECT sa FROM SocialAccount sa WHERE LOWER(sa.location) LIKE LOWER(CONCAT('%', :location, '%')) AND sa.deleted = false")
    Page<SocialAccount> findByLocationContainingIgnoreCase(@Param("location") String location, Pageable pageable);
    
    // Find verified accounts by company
    Page<SocialAccount> findByCompanyIdAndVerifiedTrueAndDeletedFalse(Long companyId, Pageable pageable);
    
    // Find business accounts by company
    @Query("SELECT sa FROM SocialAccount sa WHERE sa.companyId = :companyId AND sa.accountType IN ('BUSINESS', 'CREATOR', 'BRAND') AND sa.deleted = false")
    Page<SocialAccount> findBusinessAccountsByCompany(@Param("companyId") Long companyId, Pageable pageable);
    
    // Get low performing accounts
    @Query(value = "SELECT * FROM core_business.social_accounts sa WHERE " +
           "(CAST(sa.metrics ->> 'engagement' AS DOUBLE PRECISION) < 0.02 OR " +
           "CAST(sa.metrics ->> 'reach' AS INTEGER) < 100) AND " +
           "sa.active = true AND sa.deleted = false", nativeQuery = true)
    List<SocialAccount> findLowPerformingAccounts();
    
    // Get connected accounts summary by company
    @Query(value = "SELECT sa.social_network_id, COUNT(*) as account_count, " +
           "COALESCE(SUM(CAST(sa.metrics ->> 'followers' AS INTEGER)), 0) as total_followers " +
           "FROM core_business.social_accounts sa " +
           "WHERE sa.company_id = :companyId AND sa.connection_status = 'CONNECTED' " +
           "AND sa.active = true AND sa.deleted = false " +
           "GROUP BY sa.social_network_id", nativeQuery = true)
    List<Object[]> getConnectedAccountsSummaryByCompany(@Param("companyId") Long companyId);
}