package com.dacrt.SBIABackend.repository;

import java.util.List;
import java.util.Optional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.dacrt.SBIABackend.entity.Companies;

public interface CompaniesRepository extends JpaRepository<Companies, Integer>, JpaSpecificationExecutor<Companies> {
    
    boolean existsByName(String name);
    
    Optional<Companies> getByName(String name);
    
    // ❌ SE ELIMINÓ: Optional<Companies> findById(Companies companies);
    // JPA ya provee internamente: Optional<Companies> findById(Integer id);

    @Query(value = "SELECT CASE WHEN count(*) > 0 THEN true ELSE false END FROM main.users u WHERE u.defaultcompanyid = :id", nativeQuery = true)
    boolean existsByCompanyUsuario(@Param("id") int id);

    @Query(value = "SELECT CASE WHEN count(*) > 0 THEN true ELSE false END FROM main.units u WHERE u.companyid = :id", nativeQuery = true)
    boolean existsByCompanyUnidad(@Param("id") int id);

    @Query(value = "SELECT CASE WHEN count(*) > 0 THEN true ELSE false END FROM main.campaigns u WHERE u.companyid = :id", nativeQuery = true)
    boolean existsByCompanyCampana(@Param("id") int id);
    
    @Query(value = "SELECT CASE WHEN count(*) > 0 THEN true ELSE false END FROM main.positions u WHERE u.companyid = :id", nativeQuery = true)
    boolean existsByCompanyPosition(@Param("id") int id);

    @Query(value = "SELECT * FROM main.companies u WHERE u.status = 1 AND u.id <> :id", nativeQuery = true)
    List<Companies> ObtenerActivasNoDefault(@Param("id") int id);
}