package com.social.media.repository;

import com.social.media.domain.entity.User;
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 UserRepository extends JpaRepository<User, Long> {
    
    // Busca por email
    Optional<User> findByEmail(String email);
    
    // Busca por user_code
    Optional<User> findByUserCode(String userCode);
    
    // Verifica se email já existe
    boolean existsByEmail(String email);
    
    // Verifica se user_code já existe  
    boolean existsByUserCode(String userCode);
    
    // Busca por company_id
    Page<User> findByCompanyId(Long companyId, Pageable pageable);
    
    // Busca por type
    Page<User> findByType(User.UserType type, Pageable pageable);
    
    // Busca por status
    Page<User> findByStatus(User.UserStatus status, Pageable pageable);
    
    // Busca usuários não deletados
    Page<User> findByDeletedFalse(Pageable pageable);
    
    // Busca usuários ativos (status ACTIVE e não deletados)
    @Query("SELECT u FROM User u WHERE u.status = 'ACTIVE' AND u.deleted = false")
    Page<User> findActiveUsers(Pageable pageable);
    
    // Busca por nome (case insensitive) excluindo deletados
    @Query("SELECT u FROM User u WHERE LOWER(u.name) LIKE LOWER(CONCAT('%', :name, '%')) AND u.deleted = false")
    Page<User> findByNameContainingIgnoreCase(@Param("name") String name, Pageable pageable);
    
    // Busca por email (case insensitive) excluindo deletados
    @Query("SELECT u FROM User u WHERE LOWER(u.email) LIKE LOWER(CONCAT('%', :email, '%')) AND u.deleted = false")
    Page<User> findByEmailContainingIgnoreCase(@Param("email") String email, Pageable pageable);
    
    // Busca por empresa e email (para validar unique constraint)
    @Query("SELECT u FROM User u WHERE u.companyId = :companyId AND u.email = :email AND u.deleted = false")
    Optional<User> findByCompanyIdAndEmail(@Param("companyId") Long companyId, @Param("email") String email);
    
    // Busca subordinados de um manager
    Page<User> findByManagerId(Long managerId, Pageable pageable);
    
    // Busca por parent_user_id
    Page<User> findByParentUserId(Long parentUserId, Pageable pageable);
    
    // Busca por nível organizacional
    Page<User> findByOrganizationalLevel(Integer level, Pageable pageable);
    
    // Busca usuários com perfil verificado
    Page<User> findByProfileVerifiedTrue(Pageable pageable);
    
    // Busca usuários com WhatsApp habilitado
    Page<User> findByWhatsappEnabledTrue(Pageable pageable);
    
    // Busca usuários criados após uma data (excluindo deletados)
    @Query("SELECT u FROM User u WHERE u.registrationDate > :date AND u.deleted = false")
    List<User> findByRegistrationDateAfter(@Param("date") LocalDateTime date);
    
    // Busca usuários que acessaram recentemente
    @Query("SELECT u FROM User u WHERE u.lastAccessDate > :date AND u.deleted = false")
    List<User> findUsersWithRecentAccess(@Param("date") LocalDateTime date);
    
    // Busca por type e company_id
    @Query("SELECT u FROM User u WHERE u.type = :type AND u.companyId = :companyId AND u.deleted = false")
    Page<User> findByTypeAndCompanyId(@Param("type") User.UserType type, @Param("companyId") Long companyId, Pageable pageable);
    
    // Conta usuários por type
    @Query("SELECT COUNT(u) FROM User u WHERE u.type = :type AND u.deleted = false")
    Long countByType(@Param("type") User.UserType type);
    
    // Conta usuários ativos por empresa
    @Query("SELECT COUNT(u) FROM User u WHERE u.companyId = :companyId AND u.status = 'ACTIVE' AND u.deleted = false")
    Long countActiveUsersByCompany(@Param("companyId") Long companyId);
    
    // Busca top usuários por último acesso
    @Query("SELECT u FROM User u WHERE u.lastAccessDate IS NOT NULL AND u.deleted = false ORDER BY u.lastAccessDate DESC")
    List<User> findTopUsersByLastAccess(Pageable pageable);
    
    // Busca usuários por departamento
    @Query("SELECT u FROM User u WHERE u.department = :department AND u.deleted = false")
    Page<User> findByDepartment(@Param("department") String department, Pageable pageable);
    
    // Busca usuários por posição
    @Query("SELECT u FROM User u WHERE u.position = :position AND u.deleted = false")
    Page<User> findByPosition(@Param("position") String position, Pageable pageable);
}