package com.social.media.infrastructure.persistence.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;

/**
 * Spring Data JPA Repository for Social Account
 */
@Repository
public interface SocialAccountJpaRepository extends JpaRepository<SocialAccountJpaEntity, Long> {

    /**
     * Find by account code
     */
    Optional<SocialAccountJpaEntity> findByAccountCode(String accountCode);

    /**
     * Find by company ID
     */
    List<SocialAccountJpaEntity> findByCompanyIdAndDeletedFalse(Long companyId);

    /**
     * Find by company ID with pagination
     */
    Page<SocialAccountJpaEntity> findByCompanyIdAndDeletedFalse(Long companyId, Pageable pageable);

    /**
     * Find by responsible user ID
     */
    List<SocialAccountJpaEntity> findByResponsibleUserIdAndDeletedFalse(Long responsibleUserId);

    /**
     * Find by social network ID
     */
    List<SocialAccountJpaEntity> findBySocialNetworkIdAndDeletedFalse(Long socialNetworkId);

    /**
     * Find by username and social network ID
     */
    Optional<SocialAccountJpaEntity> findByUsernameAndSocialNetworkIdAndDeletedFalse(
            String username, Long socialNetworkId);

    /**
     * Find by connection status
     */
    List<SocialAccountJpaEntity> findByConnectionStatusAndDeletedFalse(
            SocialAccountJpaEntity.ConnectionStatusEnum connectionStatus);

    /**
     * Find active accounts by company ID
     */
    List<SocialAccountJpaEntity> findByCompanyIdAndActiveTrueAndDeletedFalse(Long companyId);

    /**
     * Find active accounts by responsible user ID
     */
    List<SocialAccountJpaEntity> findByResponsibleUserIdAndActiveTrueAndDeletedFalse(Long responsibleUserId);

    /**
     * Check if username exists for social network
     */
    boolean existsByUsernameAndSocialNetworkIdAndDeletedFalse(String username, Long socialNetworkId);

    /**
     * Count by company ID
     */
    long countByCompanyIdAndDeletedFalse(Long companyId);

    /**
     * Count active by company ID
     */
    long countByCompanyIdAndActiveTrueAndDeletedFalse(Long companyId);

    /**
     * Count by responsible user ID
     */
    long countByResponsibleUserIdAndDeletedFalse(Long responsibleUserId);

    /**
     * Find accounts that need sync (last sync date before specified time)
     */
    @Query("SELECT sa FROM SocialAccountJpaEntity sa WHERE sa.lastSyncDate < :syncThreshold " +
           "AND sa.active = true AND sa.deleted = false")
    List<SocialAccountJpaEntity> findAccountsNeedingSync(@Param("syncThreshold") LocalDateTime syncThreshold);

    /**
     * Find accounts with expired tokens
     */
    @Query("SELECT sa FROM SocialAccountJpaEntity sa WHERE " +
           "sa.connectionStatus = 'TOKEN_ERROR' AND sa.deleted = false")
    List<SocialAccountJpaEntity> findAccountsWithExpiredTokens();

    /**
     * Search accounts by username or display name
     */
    @Query("SELECT sa FROM SocialAccountJpaEntity sa WHERE sa.companyId = :companyId " +
           "AND (LOWER(sa.username) LIKE LOWER(CONCAT('%', :searchTerm, '%')) " +
           "OR LOWER(sa.displayName) LIKE LOWER(CONCAT('%', :searchTerm, '%'))) " +
           "AND sa.deleted = false")
    Page<SocialAccountJpaEntity> searchByUsernameOrDisplayName(
            @Param("companyId") Long companyId,
            @Param("searchTerm") String searchTerm,
            Pageable pageable);

    /**
     * Find accounts by platform (social network) and company
     */
    @Query("SELECT sa FROM SocialAccountJpaEntity sa WHERE sa.companyId = :companyId " +
           "AND sa.socialNetworkId = :socialNetworkId AND sa.deleted = false")
    List<SocialAccountJpaEntity> findByCompanyAndPlatform(
            @Param("companyId") Long companyId,
            @Param("socialNetworkId") Long socialNetworkId);

    /**
     * Find accounts by status and company
     */
    @Query("SELECT sa FROM SocialAccountJpaEntity sa WHERE sa.companyId = :companyId " +
           "AND sa.connectionStatus = :status AND sa.deleted = false")
    List<SocialAccountJpaEntity> findByCompanyAndStatus(
            @Param("companyId") Long companyId,
            @Param("status") SocialAccountJpaEntity.ConnectionStatusEnum status);

    /**
     * Find accounts by active status and company
     */
    @Query("SELECT sa FROM SocialAccountJpaEntity sa WHERE sa.companyId = :companyId " +
           "AND sa.active = :active AND sa.deleted = false")
    List<SocialAccountJpaEntity> findByCompanyAndActiveStatus(
            @Param("companyId") Long companyId,
            @Param("active") Boolean active);

    /**
     * Get total storage usage for company (if metrics contain storage info)
     */
    @Query(value = "SELECT COALESCE(SUM(CAST(sa.metrics ->> 'storageUsed' AS bigint)), 0) " +
           "FROM core_business.social_accounts sa WHERE sa.company_id = :companyId AND sa.deleted = false", 
           nativeQuery = true)
    Long getTotalStorageUsageByCompany(@Param("companyId") Long companyId);

    /**
     * Get accounts with recent activity (last sync within specified hours)
     */
    @Query("SELECT sa FROM SocialAccountJpaEntity sa WHERE sa.companyId = :companyId " +
           "AND sa.lastSyncDate >= :since AND sa.deleted = false")
    List<SocialAccountJpaEntity> findRecentlyActiveByCompany(
            @Param("companyId") Long companyId,
            @Param("since") LocalDateTime since);

    /**
     * Soft delete account (mark as deleted)
     */
    @Query("UPDATE SocialAccountJpaEntity sa SET sa.deleted = true, sa.updatedAt = CURRENT_TIMESTAMP " +
           "WHERE sa.id = :id")
    @org.springframework.data.jpa.repository.Modifying
    @org.springframework.transaction.annotation.Transactional
    void softDelete(@Param("id") Long id);
}