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

import com.social.media.infrastructure.persistence.entity.UserEntity;
import com.social.media.infrastructure.persistence.entity.UserStatusEntity;
import com.social.media.infrastructure.persistence.entity.UserTypeEntity;
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;

/**
 * JPA Repository for UserEntity - REFACTORED VERSION
 * All queries now require userId and companyId for security isolation
 */
@Repository
public interface UserJpaRepository extends JpaRepository<UserEntity, Long> {
    
    /**
     * Find user by ID with company and requesting user validation (excluding deleted)
     */
    @Query("SELECT u FROM UserEntity u WHERE u.id = :id AND u.companyId = :companyId AND u.deleted = false AND " +
           "(u.id = :requestingUserId OR EXISTS (SELECT r FROM UserEntity r WHERE r.id = :requestingUserId AND r.companyId = :companyId AND r.type IN ('ADMIN', 'SUPER_ADMIN')))")
    Optional<UserEntity> findByIdAndCompanyIdAndRequestingUserId(@Param("id") Long id, 
                                                                @Param("companyId") Long companyId, 
                                                                @Param("requestingUserId") Long requestingUserId);
    
    /**
     * Find user by email with company validation
     */
    @Query("SELECT u FROM UserEntity u WHERE u.email = :email AND u.deleted = false")
    Optional<UserEntity> findByEmail(@Param("email") String email);
    
    /**
     * Find user by user code with company validation
     */
    @Query("SELECT u FROM UserEntity u WHERE u.userCode = :userCode AND u.companyId = :companyId AND u.deleted = false")
    Optional<UserEntity> findByUserCodeAndCompanyId(@Param("userCode") String userCode, @Param("companyId") Long companyId);
    
    /**
     * Find user by CPF with company validation
     */
    @Query("SELECT u FROM UserEntity u WHERE u.cpf = :cpf AND u.companyId = :companyId AND u.deleted = false")
    Optional<UserEntity> findByCpfAndCompanyId(@Param("cpf") String cpf, @Param("companyId") Long companyId);
    
    /**
     * Check if email exists within company
     */
    @Query("SELECT COUNT(u) > 0 FROM UserEntity u WHERE u.email = :email AND u.companyId = :companyId AND u.deleted = false")
    boolean existsByEmailAndCompanyId(@Param("email") String email, @Param("companyId") Long companyId);
    
    /**
     * Check if user code exists within company
     */
    @Query("SELECT COUNT(u) > 0 FROM UserEntity u WHERE u.userCode = :userCode AND u.companyId = :companyId AND u.deleted = false")
    boolean existsByUserCodeAndCompanyId(@Param("userCode") String userCode, @Param("companyId") Long companyId);
    
    /**
     * Check if CPF exists within company
     */
    @Query("SELECT COUNT(u) > 0 FROM UserEntity u WHERE u.cpf = :cpf AND u.companyId = :companyId AND u.deleted = false")
    boolean existsByCpfAndCompanyId(@Param("cpf") String cpf, @Param("companyId") Long companyId);
    
    /**
     * Find users by company with requesting user validation (excluding deleted)
     */
    @Query("SELECT u FROM UserEntity u WHERE u.companyId = :companyId AND u.deleted = false AND " +
           "EXISTS (SELECT r FROM UserEntity r WHERE r.id = :requestingUserId AND r.companyId = :companyId AND r.type IN ('ADMIN', 'SUPER_ADMIN'))")
    Page<UserEntity> findByCompanyIdAndRequestingUserId(@Param("companyId") Long companyId, 
                                                        @Param("requestingUserId") Long requestingUserId, 
                                                        Pageable pageable);
    
    /**
     * Find users by company and status with requesting user validation (excluding deleted)
     */
    @Query("SELECT u FROM UserEntity u WHERE u.companyId = :companyId AND u.status = :status AND u.deleted = false AND " +
           "EXISTS (SELECT r FROM UserEntity r WHERE r.id = :requestingUserId AND r.companyId = :companyId AND r.type IN ('ADMIN', 'SUPER_ADMIN'))")
    Page<UserEntity> findByCompanyIdAndStatusAndRequestingUserId(@Param("companyId") Long companyId, 
                                                                @Param("status") UserStatusEntity status,
                                                                @Param("requestingUserId") Long requestingUserId, 
                                                                Pageable pageable);
    
    /**
     * Find users by company and type with requesting user validation (excluding deleted)
     */
    @Query("SELECT u FROM UserEntity u WHERE u.companyId = :companyId AND u.type = :userType AND u.deleted = false AND " +
           "EXISTS (SELECT r FROM UserEntity r WHERE r.id = :requestingUserId AND r.companyId = :companyId AND r.type IN ('ADMIN', 'SUPER_ADMIN'))")
    Page<UserEntity> findByCompanyIdAndUserTypeAndRequestingUserId(@Param("companyId") Long companyId, 
                                                                  @Param("userType") UserTypeEntity userType,
                                                                  @Param("requestingUserId") Long requestingUserId, 
                                                                  Pageable pageable);
    
    /**
     * Find active users by company with requesting user validation (excluding deleted)
     */
    @Query("SELECT u FROM UserEntity u WHERE u.companyId = :companyId AND u.status = 'ACTIVE' AND u.deleted = false AND " +
           "EXISTS (SELECT r FROM UserEntity r WHERE r.id = :requestingUserId AND r.companyId = :companyId AND r.type IN ('ADMIN', 'SUPER_ADMIN'))")
    List<UserEntity> findActiveUsersByCompanyIdAndRequestingUserId(@Param("companyId") Long companyId, 
                                                                  @Param("requestingUserId") Long requestingUserId);
    
    /**
     * Count users by company with requesting user validation (excluding deleted)
     */
    @Query("SELECT COUNT(u) FROM UserEntity u WHERE u.companyId = :companyId AND u.deleted = false AND " +
           "EXISTS (SELECT r FROM UserEntity r WHERE r.id = :requestingUserId AND r.companyId = :companyId AND r.type IN ('ADMIN', 'SUPER_ADMIN'))")
    long countByCompanyIdAndRequestingUserId(@Param("companyId") Long companyId, 
                                           @Param("requestingUserId") Long requestingUserId);
    
    /**
     * Count users by company and status with requesting user validation (excluding deleted)
     */
    @Query("SELECT COUNT(u) FROM UserEntity u WHERE u.companyId = :companyId AND u.status = :status AND u.deleted = false AND " +
           "EXISTS (SELECT r FROM UserEntity r WHERE r.id = :requestingUserId AND r.companyId = :companyId AND r.type IN ('ADMIN', 'SUPER_ADMIN'))")
    long countByCompanyIdAndStatusAndRequestingUserId(@Param("companyId") Long companyId, 
                                                     @Param("status") UserStatusEntity status,
                                                     @Param("requestingUserId") Long requestingUserId);
    
    /**
     * Find users by email verification status with company validation
     */
    @Query("SELECT u FROM UserEntity u WHERE u.emailVerified = :verified AND u.companyId = :companyId AND u.deleted = false")
    List<UserEntity> findByEmailVerifiedAndCompanyId(@Param("verified") Boolean verified, 
                                                     @Param("companyId") Long companyId);
    
    /**
     * Find users by WhatsApp enabled status with company validation
     */
    @Query("SELECT u FROM UserEntity u WHERE u.whatsappEnabled = :enabled AND u.companyId = :companyId AND u.deleted = false")
    List<UserEntity> findByWhatsappEnabledAndCompanyId(@Param("enabled") Boolean enabled, 
                                                       @Param("companyId") Long companyId);
    
    /**
     * Find users by two factor enabled status with company validation
     */
    @Query("SELECT u FROM UserEntity u WHERE u.twoFactorEnabled = :enabled AND u.companyId = :companyId AND u.deleted = false")
    List<UserEntity> findByTwoFactorEnabledAndCompanyId(@Param("enabled") Boolean enabled, 
                                                        @Param("companyId") Long companyId);
    
    /**
     * Find users by department with company validation
     */
    @Query("SELECT u FROM UserEntity u WHERE u.department = :department AND u.companyId = :companyId AND u.deleted = false")
    List<UserEntity> findByDepartmentAndCompanyId(@Param("department") String department, 
                                                  @Param("companyId") Long companyId);
    
    /**
     * Find users by position with company validation
     */
    @Query("SELECT u FROM UserEntity u WHERE u.position = :position AND u.companyId = :companyId AND u.deleted = false")
    List<UserEntity> findByPositionAndCompanyId(@Param("position") String position, 
                                                @Param("companyId") Long companyId);
    
    /**
     * Check if exists user by type and company with requesting user validation (excluding deleted)
     */
    @Query("SELECT COUNT(u) > 0 FROM UserEntity u WHERE u.type = :userType AND u.companyId = :companyId AND u.deleted = false AND " +
           "EXISTS (SELECT r FROM UserEntity r WHERE r.id = :requestingUserId AND r.companyId = :companyId AND r.type IN ('ADMIN', 'SUPER_ADMIN'))")
    boolean existsByUserTypeAndCompanyIdAndRequestingUserId(@Param("userType") UserTypeEntity userType, 
                                                           @Param("companyId") Long companyId,
                                                           @Param("requestingUserId") Long requestingUserId);
    
    /**
     * Find active users (not deleted) by company with requesting user validation
     */
    @Query("SELECT u FROM UserEntity u WHERE u.companyId = :companyId AND u.deleted = :deleted AND " +
           "EXISTS (SELECT r FROM UserEntity r WHERE r.id = :requestingUserId AND r.companyId = :companyId AND r.type IN ('ADMIN', 'SUPER_ADMIN'))")
    Page<UserEntity> findByCompanyIdAndDeletedAndRequestingUserId(@Param("companyId") Long companyId, 
                                                                 @Param("deleted") Boolean deleted,
                                                                 @Param("requestingUserId") Long requestingUserId, 
                                                                 Pageable pageable);
    
    /**
     * Check if exists user by email and not deleted within company
     */
    @Query("SELECT COUNT(u) > 0 FROM UserEntity u WHERE u.email = :email AND u.deleted = :deleted AND u.companyId = :companyId")
    boolean existsByEmailAndDeletedAndCompanyId(@Param("email") String email, 
                                               @Param("deleted") Boolean deleted, 
                                               @Param("companyId") Long companyId);
    
    /**
     * Check if exists user by cpf and not deleted within company
     */
    @Query("SELECT COUNT(u) > 0 FROM UserEntity u WHERE u.cpf = :cpf AND u.deleted = :deleted AND u.companyId = :companyId")
    boolean existsByCpfAndDeletedAndCompanyId(@Param("cpf") String cpf, 
                                             @Param("deleted") Boolean deleted, 
                                             @Param("companyId") Long companyId);
    
    /**
     * Get user's own profile data (no additional validation needed, excluding deleted)
     */
    @Query("SELECT u FROM UserEntity u WHERE u.id = :userId AND u.companyId = :companyId AND u.deleted = false")
    Optional<UserEntity> findOwnProfile(@Param("userId") Long userId, @Param("companyId") Long companyId);
    
    /**
     * Find subordinates (users managed by requesting user, excluding deleted)
     */
    @Query("SELECT u FROM UserEntity u WHERE u.companyId = :companyId AND u.parentUserId = :requestingUserId AND u.deleted = false")
    Page<UserEntity> findSubordinates(@Param("companyId") Long companyId, 
                                     @Param("requestingUserId") Long requestingUserId, 
                                     Pageable pageable);
    
    // Global authentication methods (no company validation required)
    
    /**
     * Find user by email for authentication purposes (global search)
     */
    @Query("SELECT u FROM UserEntity u WHERE u.email = :email AND u.deleted = false")
    Optional<UserEntity> findByEmailForAuthentication(@Param("email") String email);
    
    /**
     * Find user by user code for authentication purposes (global search)
     */
    @Query("SELECT u FROM UserEntity u WHERE u.userCode = :userCode AND u.deleted = false")
    Optional<UserEntity> findByUserCodeForAuthentication(@Param("userCode") String userCode);
    
    /**
     * Check if super admin exists globally
     */
    @Query("SELECT COUNT(u) > 0 FROM UserEntity u WHERE u.type = 'SUPER_ADMIN' AND u.deleted = false")
    boolean existsSuperAdminGlobally();
    
    /**
     * Find super admin globally
     */
    @Query("SELECT u FROM UserEntity u WHERE u.type = 'SUPER_ADMIN' AND u.deleted = false")
    Optional<UserEntity> findSuperAdminGlobally();

    /**
     * Count active users (not deleted) by company with requesting user validation
     */
    @Query("SELECT COUNT(u) FROM UserEntity u WHERE u.companyId = :companyId AND u.deleted = :deleted AND " +
           "EXISTS (SELECT r FROM UserEntity r WHERE r.id = :requestingUserId AND r.companyId = :companyId AND r.type IN ('ADMIN', 'SUPER_ADMIN'))")
    long countByCompanyIdAndDeletedAndRequestingUserId(@Param("companyId") Long companyId, 
                                                      @Param("deleted") Boolean deleted,
                                                      @Param("requestingUserId") Long requestingUserId);

    /**
     * Count users by type with company context and requesting user validation (excluding deleted)
     */
    @Query("SELECT COUNT(u) FROM UserEntity u WHERE u.companyId = :companyId AND u.type = :userType AND u.deleted = false AND " +
           "EXISTS (SELECT r FROM UserEntity r WHERE r.id = :requestingUserId AND r.companyId = :companyId AND r.type IN ('ADMIN', 'SUPER_ADMIN'))")
    long countByCompanyIdAndUserTypeAndRequestingUserId(@Param("companyId") Long companyId, 
                                                       @Param("userType") UserTypeEntity userType,
                                                       @Param("requestingUserId") Long requestingUserId);

    /**
     * Check if exists user by type and company with requesting user validation (excluding deleted)
     */
    @Query("SELECT COUNT(u) > 0 FROM UserEntity u WHERE u.companyId = :companyId AND u.type = :userType AND u.deleted = false AND " +
           "EXISTS (SELECT r FROM UserEntity r WHERE r.id = :requestingUserId AND r.companyId = :companyId AND r.type IN ('ADMIN', 'SUPER_ADMIN'))")
    boolean existsByCompanyIdAndUserTypeAndRequestingUserId(@Param("companyId") Long companyId, 
                                                          @Param("userType") UserTypeEntity userType,
                                                          @Param("requestingUserId") Long requestingUserId);
                                                          
    /**
     * Find users with unverified emails that registered more than X hours ago
     */
    @Query("SELECT u FROM UserEntity u WHERE u.emailVerified = false AND u.registrationDate < :cutoffDate AND u.deleted = false")
    List<UserEntity> findUnverifiedUsersOlderThan(@Param("cutoffDate") java.time.LocalDateTime cutoffDate);
    
    /**
     * Find users by registration date range
     */
    @Query("SELECT u FROM UserEntity u WHERE u.companyId = :companyId AND u.registrationDate BETWEEN :startDate AND :endDate AND u.deleted = false")
    List<UserEntity> findByRegistrationDateRange(@Param("companyId") Long companyId,
                                                 @Param("startDate") java.time.LocalDateTime startDate,
                                                 @Param("endDate") java.time.LocalDateTime endDate);
}