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);
    Companies getById(int id);
    
    boolean existsById(int id);
    
    // ❌ 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 s, main.userunits uu, main.units u where s.id=uu.userid and uu.unitid=u.id and u.companyid = :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 c, main.campaignunits cu, main.units u where c.id=cu.campaingid and cu.unitid=u.id and 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 p, main.positionunits pu, main.units u where p.id=pu.positionid and pu.unitid=u.id and 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);
    
    @Query(value = "SELECT * FROM main.companies u WHERE u.status = 1 and u.id <> :iddefault order by CASE "
    		+ "        WHEN name ~ '^[A-Za-z]' THEN 1 "
    		+ "        ELSE 2 "
    		+ "    END ASC, "
    		+ "    name ASC", nativeQuery = true)
    List<Companies> ObtenerActivas(@Param("iddefault") int iddefault);
    
    @Query(value = "SELECT * FROM main.companies u WHERE u.status = 1 order by CASE "
    		+ "        WHEN name ~ '^[A-Za-z]' THEN 1 "
    		+ "        ELSE 2 "
    		+ "    END ASC, "
    		+ "    name ASC", nativeQuery = true)
    List<Companies> ObtenerActivas2();
    
   /* @Query(value = "SELECT DISTINCT c.* FROM main.userunits uu " +
    	       "INNER JOIN main.units un ON un.id = uu.unitid " +
    	       "INNER JOIN main.companies c ON c.id = un.companyid " +
    	       "INNER JOIN main.users u ON u.id = uu.userid " +
    	       "WHERE uu.userid = :userId " +
    	       "AND (u.defaultcompanyid IS NULL OR c.id <> u.defaultcompanyid) ORDER BY "
    	       + "    CASE "
    	       + "        WHEN c.name ~ '^[A-Za-z]' THEN 1 "
    	       + "        ELSE 2 "
    	       + "    END ASC, "
    	       + "    c.name ASC", nativeQuery = true)
    List<Companies> findCompaniesByUserIdExcludingDefault(@Param("userId") int userId);*/
    @Query(value = "SELECT * FROM (" +
            "    SELECT DISTINCT c.* FROM main.userunits uu " +
            "    INNER JOIN main.units un ON un.id = uu.unitid " +
            "    INNER JOIN main.companies c ON c.id = un.companyid " +
            "    INNER JOIN main.users u ON u.id = uu.userid " +
            "    WHERE uu.userid = :userId " +
            "    AND  c.id <> :iddefault " +
            "    AND (u.defaultcompanyid IS NULL OR c.id <> u.defaultcompanyid)" +
            ") sub " +
            "ORDER BY " +
            "    CASE " +
            "        WHEN sub.name ~ '^[A-Za-z]' THEN 1 " +
            "        ELSE 2 " +
            "    END ASC, " +
            "    sub.name ASC", nativeQuery = true)
     List<Companies> findCompaniesByUserIdExcludingDefault(@Param("userId") int userId,@Param("iddefault") int iddefault);
    
    @Query(value = "SELECT * FROM (" +
            "    SELECT DISTINCT c.* FROM main.userunits uu " +
            "    INNER JOIN main.units un ON un.id = uu.unitid " +
            "    INNER JOIN main.companies c ON c.id = un.companyid " +
            "    INNER JOIN main.users u ON u.id = uu.userid " +
            "    WHERE uu.userid = :userId " +
            "    AND (u.defaultcompanyid IS NULL OR c.id <> u.defaultcompanyid)" +
            ") sub " +
            "ORDER BY " +
            "    CASE " +
            "        WHEN sub.name ~ '^[A-Za-z]' THEN 1 " +
            "        ELSE 2 " +
            "    END ASC, " +
            "    sub.name ASC", nativeQuery = true)
     List<Companies> findCompaniesByUserIdExcludingDefault2(@Param("userId") int userId);
    
}