package com.social.media.repository;

import com.social.media.domain.entity.SocialNetwork;
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.util.List;
import java.util.Optional;

@Repository
public interface SocialNetworkRepository extends JpaRepository<SocialNetwork, Long> {
    
    // Basic queries
    
    // Find by unique network code
    Optional<SocialNetwork> findByNetworkCode(String networkCode);
    
    // Find by platform
    Optional<SocialNetwork> findByPlatform(String platform);
    
    // Find by name (case insensitive)
    Optional<SocialNetwork> findByNameIgnoreCase(String name);
    
    // Active networks queries
    
    // Find all active networks ordered by sort order
    @Query("SELECT sn FROM SocialNetwork sn WHERE sn.isActive = true ORDER BY sn.sortOrder ASC, sn.name ASC")
    List<SocialNetwork> findAllActiveNetworks();
    
    // Find active networks with pagination
    Page<SocialNetwork> findByIsActiveTrueOrderBySortOrderAscNameAsc(Pageable pageable);
    
    // Find inactive networks
    Page<SocialNetwork> findByIsActiveFalseOrderByNameAsc(Pageable pageable);
    
    // Search and filtering
    
    // Search by name or platform (case insensitive)
    @Query("SELECT sn FROM SocialNetwork sn WHERE " +
           "LOWER(sn.name) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR " +
           "LOWER(sn.platform) LIKE LOWER(CONCAT('%', :searchTerm, '%')) " +
           "ORDER BY sn.sortOrder ASC, sn.name ASC")
    Page<SocialNetwork> searchByNameOrPlatform(@Param("searchTerm") String searchTerm, Pageable pageable);
    
    // Find networks that require approval
    Page<SocialNetwork> findByRequiresApprovalTrueOrderBySortOrderAscNameAsc(Pageable pageable);
    
    // Find networks that don't require approval
    Page<SocialNetwork> findByRequiresApprovalFalseOrderBySortOrderAscNameAsc(Pageable pageable);
    
    // Feature-based queries
    
    // Find networks supporting a specific feature
    @Query(value = "SELECT * FROM core_business.social_networks sn WHERE " +
           "sn.supported_features ->> :featureName = 'true' AND sn.is_active = true " +
           "ORDER BY sn.sort_order ASC, sn.name ASC", nativeQuery = true)
    List<SocialNetwork> findNetworksSupportingFeature(@Param("featureName") String featureName);
    
    // Find networks with specific API configuration
    @Query(value = "SELECT * FROM core_business.social_networks sn WHERE " +
           "sn.api_config ->> :configKey = :configValue " +
           "ORDER BY sn.sort_order ASC", nativeQuery = true)
    List<SocialNetwork> findNetworksByApiConfig(@Param("configKey") String configKey, 
                                               @Param("configValue") String configValue);
    
    // Rate limit queries
    
    // Find networks with rate limit above threshold
    @Query(value = "SELECT * FROM core_business.social_networks sn WHERE " +
           "CAST(sn.rate_limits ->> 'perHour' AS INTEGER) >= :minRateLimit AND sn.is_active = true " +
           "ORDER BY CAST(sn.rate_limits ->> 'perHour' AS INTEGER) DESC", nativeQuery = true)
    List<SocialNetwork> findNetworksWithHighRateLimit(@Param("minRateLimit") Integer minRateLimit);
    
    // Configuration and setup queries
    
    // Find networks ready for production (have required URLs)
    @Query("SELECT sn FROM SocialNetwork sn WHERE " +
           "sn.isActive = true AND " +
           "sn.apiBaseUrl IS NOT NULL AND TRIM(sn.apiBaseUrl) != '' AND " +
           "sn.oauthUrl IS NOT NULL AND TRIM(sn.oauthUrl) != '' " +
           "ORDER BY sn.sortOrder ASC")
    List<SocialNetwork> findProductionReadyNetworks();
    
    // Find networks missing configuration
    @Query("SELECT sn FROM SocialNetwork sn WHERE " +
           "sn.isActive = true AND (" +
           "sn.apiBaseUrl IS NULL OR TRIM(sn.apiBaseUrl) = '' OR " +
           "sn.oauthUrl IS NULL OR TRIM(sn.oauthUrl) = ''" +
           ") ORDER BY sn.name ASC")
    List<SocialNetwork> findIncompleteNetworks();
    
    // Sort order queries
    
    // Find networks by sort order range
    @Query("SELECT sn FROM SocialNetwork sn WHERE " +
           "sn.sortOrder BETWEEN :minOrder AND :maxOrder " +
           "ORDER BY sn.sortOrder ASC, sn.name ASC")
    List<SocialNetwork> findNetworksBySortOrderRange(@Param("minOrder") Integer minOrder, 
                                                    @Param("maxOrder") Integer maxOrder);
    
    // Get maximum sort order
    @Query("SELECT COALESCE(MAX(sn.sortOrder), 0) FROM SocialNetwork sn")
    Integer getMaxSortOrder();
    
    // Existence checks
    
    // Check if platform exists
    boolean existsByPlatform(String platform);
    
    // Check if network code exists
    boolean existsByNetworkCode(String networkCode);
    
    // Check if name exists (case insensitive)
    boolean existsByNameIgnoreCase(String name);
    
    // Statistics and analytics
    
    // Count active networks
    @Query("SELECT COUNT(sn) FROM SocialNetwork sn WHERE sn.isActive = true")
    Long countActiveNetworks();
    
    // Count networks by approval requirement
    @Query("SELECT COUNT(sn) FROM SocialNetwork sn WHERE sn.requiresApproval = :requiresApproval")
    Long countNetworksByApprovalRequirement(@Param("requiresApproval") Boolean requiresApproval);
    
    // Get networks summary
    @Query("SELECT sn.isActive, COUNT(sn) FROM SocialNetwork sn GROUP BY sn.isActive")
    List<Object[]> getNetworksSummary();
    
    // Brand and UI queries
    
    // Find networks with custom branding
    @Query("SELECT sn FROM SocialNetwork sn WHERE " +
           "sn.logoUrl IS NOT NULL AND TRIM(sn.logoUrl) != '' AND " +
           "sn.brandColor IS NOT NULL AND TRIM(sn.brandColor) != '' " +
           "ORDER BY sn.sortOrder ASC")
    List<SocialNetwork> findNetworksWithBranding();
    
    // Find networks without branding
    @Query("SELECT sn FROM SocialNetwork sn WHERE " +
           "(sn.logoUrl IS NULL OR TRIM(sn.logoUrl) = '') OR " +
           "(sn.brandColor IS NULL OR TRIM(sn.brandColor) = '') " +
           "ORDER BY sn.name ASC")
    List<SocialNetwork> findNetworksWithoutBranding();
    
    // Administrative queries
    
    // Find networks by creator
    Page<SocialNetwork> findByCreatedByOrderByCreatedAtDesc(Long createdBy, Pageable pageable);
    
    // Find recently created networks
    @Query("SELECT sn FROM SocialNetwork sn WHERE sn.createdAt >= :since ORDER BY sn.createdAt DESC")
    List<SocialNetwork> findRecentlyCreatedNetworks(@Param("since") java.time.LocalDateTime since);
    
    // Find recently updated networks
    @Query("SELECT sn FROM SocialNetwork sn WHERE sn.updatedAt >= :since ORDER BY sn.updatedAt DESC")
    List<SocialNetwork> findRecentlyUpdatedNetworks(@Param("since") java.time.LocalDateTime since);
    
    // Bulk operations support
    
    // Find all networks for bulk operations
    @Query("SELECT sn FROM SocialNetwork sn ORDER BY sn.sortOrder ASC, sn.name ASC")
    List<SocialNetwork> findAllNetworksForBulkOperations();
    
    // Find networks by multiple IDs
    @Query("SELECT sn FROM SocialNetwork sn WHERE sn.id IN :ids ORDER BY sn.sortOrder ASC")
    List<SocialNetwork> findNetworksByIds(@Param("ids") List<Long> ids);
    
    // Additional missing methods for service layer
    
    // Find by brand color
    List<SocialNetwork> findByBrandColorOrderBySortOrderAscNameAsc(String brandColor);
    
    // Find by platform without pagination
    List<SocialNetwork> findByPlatformOrderBySortOrderAscNameAsc(String platform);
    
    // Find by platform and active status without pagination  
    List<SocialNetwork> findByPlatformAndIsActiveTrueOrderBySortOrderAscNameAsc(String platform);
    
    // Find active networks without pagination
    List<SocialNetwork> findByIsActiveTrueOrderBySortOrderAscNameAsc();
}