package com.social.media.repository;

import com.social.media.domain.entity.UserCredentials;
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.Modifying;
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 interface for UserCredentials entity
 */
@Repository
public interface UserCredentialsRepository extends JpaRepository<UserCredentials, Long> {
    
    // Basic queries
    
    // Find by user ID
    Optional<UserCredentials> findByUserId(Long userId);
    
    // Find by password reset token
    Optional<UserCredentials> findByPasswordResetToken(String token);
    
    // Find by email verification token
    Optional<UserCredentials> findByEmailVerificationToken(String token);
    
    // Security-related queries
    
    // Find accounts with failed login attempts
    @Query("SELECT uc FROM UserCredentials uc WHERE uc.failedLoginAttempts > 0 ORDER BY uc.failedLoginAttempts DESC")
    List<UserCredentials> findAccountsWithFailedAttempts();
    
    // Find locked accounts
    @Query("SELECT uc FROM UserCredentials uc WHERE uc.lockedUntil IS NOT NULL AND uc.lockedUntil > CURRENT_TIMESTAMP ORDER BY uc.lockedUntil DESC")
    List<UserCredentials> findLockedAccounts();
    
    // Find permanently locked accounts
    @Query("SELECT uc FROM UserCredentials uc WHERE uc.failedLoginAttempts >= 5 AND uc.lockedUntil IS NULL ORDER BY uc.failedLoginAttempts DESC")
    List<UserCredentials> findPermanentlyLockedAccounts();
    
    // Find accounts locked within a time range
    @Query("SELECT uc FROM UserCredentials uc WHERE uc.lockedUntil BETWEEN :startTime AND :endTime ORDER BY uc.lockedUntil")
    List<UserCredentials> findAccountsLockedBetween(@Param("startTime") LocalDateTime startTime, 
                                                   @Param("endTime") LocalDateTime endTime);
    
    // Find accounts with pending password resets
    @Query("SELECT uc FROM UserCredentials uc WHERE uc.passwordResetToken IS NOT NULL AND uc.passwordResetExpiresAt > CURRENT_TIMESTAMP ORDER BY uc.passwordResetExpiresAt")
    List<UserCredentials> findAccountsWithPendingPasswordReset();
    
    // Find expired password reset tokens
    @Query("SELECT uc FROM UserCredentials uc WHERE uc.passwordResetToken IS NOT NULL AND uc.passwordResetExpiresAt <= CURRENT_TIMESTAMP ORDER BY uc.passwordResetExpiresAt")
    List<UserCredentials> findExpiredPasswordResetTokens();
    
    // Find accounts with email verification pending
    @Query("SELECT uc FROM UserCredentials uc WHERE uc.emailVerificationToken IS NOT NULL ORDER BY uc.createdAt")
    List<UserCredentials> findAccountsWithPendingEmailVerification();
    
    // Two-factor authentication queries
    
    // Find accounts with 2FA enabled
    @Query("SELECT uc FROM UserCredentials uc WHERE uc.twoFactorEnabled = true ORDER BY uc.updatedAt DESC")
    List<UserCredentials> findAccountsWithTwoFactorEnabled();
    
    // Find accounts with 2FA disabled
    @Query("SELECT uc FROM UserCredentials uc WHERE uc.twoFactorEnabled = false ORDER BY uc.lastLoginAt DESC")
    List<UserCredentials> findAccountsWithTwoFactorDisabled();
    
    // Activity-based queries
    
    // Find accounts with recent logins
    @Query("SELECT uc FROM UserCredentials uc WHERE uc.lastLoginAt >= :since ORDER BY uc.lastLoginAt DESC")
    List<UserCredentials> findAccountsWithRecentLogin(@Param("since") LocalDateTime since);
    
    // Find inactive accounts (no recent login)
    @Query("SELECT uc FROM UserCredentials uc WHERE uc.lastLoginAt < :before OR uc.lastLoginAt IS NULL ORDER BY uc.lastLoginAt ASC")
    List<UserCredentials> findInactiveAccounts(@Param("before") LocalDateTime before);
    
    // Find accounts with old passwords
    @Query("SELECT uc FROM UserCredentials uc WHERE uc.passwordChangedAt < :before ORDER BY uc.passwordChangedAt ASC")
    List<UserCredentials> findAccountsWithOldPasswords(@Param("before") LocalDateTime before);
    
    // Find accounts that need password change
    @Query("SELECT uc FROM UserCredentials uc WHERE " +
           "uc.passwordChangedAt IS NULL OR " +
           "uc.passwordChangedAt < :passwordExpiryDate " +
           "ORDER BY uc.passwordChangedAt ASC")
    List<UserCredentials> findAccountsNeedingPasswordChange(@Param("passwordExpiryDate") LocalDateTime passwordExpiryDate);
    
    // Statistical queries
    
    // Count accounts by security status
    @Query("SELECT COUNT(uc) FROM UserCredentials uc WHERE uc.failedLoginAttempts >= :threshold")
    Long countAccountsWithFailedAttempts(@Param("threshold") Integer threshold);
    
    @Query("SELECT COUNT(uc) FROM UserCredentials uc WHERE uc.lockedUntil IS NOT NULL AND uc.lockedUntil > CURRENT_TIMESTAMP")
    Long countLockedAccounts();
    
    @Query("SELECT COUNT(uc) FROM UserCredentials uc WHERE uc.twoFactorEnabled = true")
    Long countTwoFactorEnabledAccounts();
    
    @Query("SELECT COUNT(uc) FROM UserCredentials uc WHERE uc.lastLoginAt >= :since")
    Long countActiveAccounts(@Param("since") LocalDateTime since);
    
    // Bulk operations
    
    // Clear expired password reset tokens
    @Modifying
    @Query("UPDATE UserCredentials uc SET uc.passwordResetToken = NULL, uc.passwordResetExpiresAt = NULL WHERE uc.passwordResetExpiresAt <= CURRENT_TIMESTAMP")
    int clearExpiredPasswordResetTokens();
    
    // Unlock accounts where lockout period has expired
    @Modifying
    @Query("UPDATE UserCredentials uc SET uc.lockedUntil = NULL WHERE uc.lockedUntil <= CURRENT_TIMESTAMP")
    int unlockExpiredAccountLocks();
    
    // Reset failed attempts for unlocked accounts
    @Modifying
    @Query("UPDATE UserCredentials uc SET uc.failedLoginAttempts = 0 WHERE uc.lockedUntil IS NULL AND uc.failedLoginAttempts > 0")
    int resetFailedAttemptsForUnlockedAccounts();
    
    // Advanced search queries
    
    // Search by multiple criteria
    @Query("SELECT uc FROM UserCredentials uc WHERE " +
           "(:hasFailedAttempts = false OR uc.failedLoginAttempts > 0) AND " +
           "(:isLocked = false OR (uc.lockedUntil IS NOT NULL AND uc.lockedUntil > CURRENT_TIMESTAMP)) AND " +
           "(:hasTwoFactor = false OR uc.twoFactorEnabled = :twoFactorEnabled) AND " +
           "(:hasRecentLogin = false OR uc.lastLoginAt >= :recentLoginThreshold) " +
           "ORDER BY uc.updatedAt DESC")
    Page<UserCredentials> findBySecurityCriteria(
            @Param("hasFailedAttempts") boolean hasFailedAttempts,
            @Param("isLocked") boolean isLocked,
            @Param("hasTwoFactor") boolean hasTwoFactor,
            @Param("twoFactorEnabled") Boolean twoFactorEnabled,
            @Param("hasRecentLogin") boolean hasRecentLogin,
            @Param("recentLoginThreshold") LocalDateTime recentLoginThreshold,
            Pageable pageable);
    
    // Find credentials needing security review
    @Query("SELECT uc FROM UserCredentials uc WHERE " +
           "uc.failedLoginAttempts >= 3 OR " +
           "uc.passwordChangedAt < :oldPasswordThreshold OR " +
           "(uc.lastLoginAt IS NOT NULL AND uc.lastLoginAt < :inactivityThreshold) OR " +
           "(uc.lockedUntil IS NOT NULL AND uc.lockedUntil > CURRENT_TIMESTAMP) " +
           "ORDER BY uc.failedLoginAttempts DESC, uc.passwordChangedAt ASC")
    List<UserCredentials> findCredentialsNeedingSecurityReview(
            @Param("oldPasswordThreshold") LocalDateTime oldPasswordThreshold,
            @Param("inactivityThreshold") LocalDateTime inactivityThreshold);
    
    // Find by date ranges
    @Query("SELECT uc FROM UserCredentials uc WHERE uc.createdAt BETWEEN :startDate AND :endDate ORDER BY uc.createdAt DESC")
    List<UserCredentials> findCreatedBetween(@Param("startDate") LocalDateTime startDate, 
                                           @Param("endDate") LocalDateTime endDate);
    
    @Query("SELECT uc FROM UserCredentials uc WHERE uc.lastLoginAt BETWEEN :startDate AND :endDate ORDER BY uc.lastLoginAt DESC")
    List<UserCredentials> findLoginsBetween(@Param("startDate") LocalDateTime startDate, 
                                          @Param("endDate") LocalDateTime endDate);
    
    // Existence checks
    boolean existsByPasswordResetToken(String token);
    boolean existsByEmailVerificationToken(String token);
    
    // Admin queries
    
    // Find recently created credentials
    @Query("SELECT uc FROM UserCredentials uc WHERE uc.createdAt >= :since ORDER BY uc.createdAt DESC")
    List<UserCredentials> findRecentlyCreated(@Param("since") LocalDateTime since);
    
    // Find recently updated credentials
    @Query("SELECT uc FROM UserCredentials uc WHERE uc.updatedAt >= :since ORDER BY uc.updatedAt DESC")
    List<UserCredentials> findRecentlyUpdated(@Param("since") LocalDateTime since);
    
    // Security analytics
    @Query("SELECT " +
           "SUM(CASE WHEN uc.twoFactorEnabled = true THEN 1 ELSE 0 END) as twoFactorEnabled, " +
           "SUM(CASE WHEN uc.failedLoginAttempts > 0 THEN 1 ELSE 0 END) as withFailedAttempts, " +
           "SUM(CASE WHEN uc.lockedUntil IS NOT NULL AND uc.lockedUntil > CURRENT_TIMESTAMP THEN 1 ELSE 0 END) as locked, " +
           "SUM(CASE WHEN uc.lastLoginAt >= :recentThreshold THEN 1 ELSE 0 END) as recentlyActive, " +
           "COUNT(uc) as total " +
           "FROM UserCredentials uc")
    Object[] getSecurityStatistics(@Param("recentThreshold") LocalDateTime recentThreshold);
    
    // Paginated versions of key queries
    Page<UserCredentials> findByFailedLoginAttemptsGreaterThanOrderByFailedLoginAttemptsDesc(Integer threshold, Pageable pageable);
    Page<UserCredentials> findByTwoFactorEnabledOrderByUpdatedAtDesc(Boolean enabled, Pageable pageable);
    Page<UserCredentials> findByLastLoginAtAfterOrderByLastLoginAtDesc(LocalDateTime since, Pageable pageable);
    Page<UserCredentials> findByPasswordChangedAtBeforeOrderByPasswordChangedAtAsc(LocalDateTime before, Pageable pageable);
}