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

import com.social.media.infrastructure.persistence.entity.CompanyEntity;
import com.social.media.infrastructure.persistence.entity.CompanyStatusEntity;
import com.social.media.infrastructure.persistence.entity.BranchTypeEntity;
import com.social.media.infrastructure.persistence.entity.BranchStatusEntity;
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 CompanyEntity with hierarchy support
 */
@Repository
public interface CompanyEntityJpaRepository extends JpaRepository<CompanyEntity, Long> {
    
    // === BASIC QUERIES ===
    
    Optional<CompanyEntity> findByEmail(String email);
    
    Optional<CompanyEntity> findByCnpj(String cnpj);
    
    boolean existsByEmail(String email);
    
    boolean existsByCnpj(String cnpj);
    
    List<CompanyEntity> findByStatus(CompanyStatusEntity status);
    
    List<CompanyEntity> findByNameContainingIgnoreCase(String name);
    
    List<CompanyEntity> findByActivitySector(String activitySector);
    
    @Query("SELECT c FROM CompanyEntity c WHERE c.status = :status ORDER BY c.createdAt DESC")
    List<CompanyEntity> findByStatusOrderByCreatedAtDesc(@Param("status") CompanyStatusEntity status);
    
    @Query("SELECT c FROM CompanyEntity c WHERE c.addressCity = :city")
    List<CompanyEntity> findByCity(@Param("city") String city);
    
    @Query("SELECT c FROM CompanyEntity c WHERE c.addressState = :state")
    List<CompanyEntity> findByState(@Param("state") String state);
    
    long countByStatus(CompanyStatusEntity status);
    
    long countByActivitySector(String activitySector);
    
    // === HIERARCHY QUERIES ===
    
    /**
     * Find companies by parent company ID
     */
    List<CompanyEntity> findByParentCompanyId(Long parentCompanyId);
    
    /**
     * Find companies by branch type
     */
    List<CompanyEntity> findByBranchType(BranchTypeEntity branchType);
    
    /**
     * Find companies by hierarchy level
     */
    List<CompanyEntity> findByHierarchyLevel(Integer hierarchyLevel);
    
    /**
     * Find companies by region
     */
    List<CompanyEntity> findByRegion(String region);
    
    /**
     * Find companies by branch status
     */
    List<CompanyEntity> findByBranchStatus(BranchStatusEntity branchStatus);
    
    /**
     * Find companies by branch code
     */
    Optional<CompanyEntity> findByBranchCode(String branchCode);
    
    /**
     * Find matriz companies
     */
    @Query("SELECT c FROM CompanyEntity c WHERE c.branchType = 'MATRIZ' ORDER BY c.name")
    List<CompanyEntity> findMatrizCompanies();
    
    /**
     * Find direct children of a company
     */
    @Query("SELECT c FROM CompanyEntity c WHERE c.parentCompany.id = :parentId ORDER BY c.name")
    List<CompanyEntity> findDirectChildren(@Param("parentId") Long parentId);
    
    /**
     * Find companies by hierarchy level range
     */
    List<CompanyEntity> findByHierarchyLevelBetween(Integer minLevel, Integer maxLevel);
    
    /**
     * Find direct siblings (same parent)
     */
    @Query("""
        SELECT c FROM CompanyEntity c 
        WHERE c.parentCompany.id = :parentId 
        AND c.id != :excludeId 
        ORDER BY c.name
    """)
    List<CompanyEntity> findSiblings(@Param("parentId") Long parentId, 
                                   @Param("excludeId") Long excludeId);
    
    /**
     * Count companies by branch type
     */
    @Query("""
        SELECT c.branchType, COUNT(c) 
        FROM CompanyEntity c 
        GROUP BY c.branchType
    """)
    List<Object[]> countCompaniesByBranchType();
    
    /**
     * Count companies by region
     */
    @Query("""
        SELECT COALESCE(c.region, 'Sem Região'), COUNT(c) 
        FROM CompanyEntity c 
        GROUP BY c.region 
        ORDER BY COUNT(c) DESC
    """)
    List<Object[]> countCompaniesByRegion();
    
    /**
     * Count companies by hierarchy level
     */
    @Query("""
        SELECT c.hierarchyLevel, COUNT(c) 
        FROM CompanyEntity c 
        GROUP BY c.hierarchyLevel 
        ORDER BY c.hierarchyLevel
    """)
    List<Object[]> countCompaniesByHierarchyLevel();
    
    /**
     * Find companies that have children with specific status
     */
    @Query("""
        SELECT DISTINCT parent FROM CompanyEntity parent
        WHERE EXISTS (
            SELECT 1 FROM CompanyEntity child 
            WHERE child.parentCompany = parent 
            AND child.branchStatus = :childStatus
        )
        ORDER BY parent.name
    """)
    List<CompanyEntity> findParentsWithChildrenInStatus(@Param("childStatus") BranchStatusEntity childStatus);
    
    /**
     * Find companies without children (leaf nodes)
     */
    @Query("""
        SELECT c FROM CompanyEntity c 
        WHERE NOT EXISTS (
            SELECT 1 FROM CompanyEntity child 
            WHERE child.parentCompany = c
        )
        ORDER BY c.hierarchyLevel DESC, c.name
    """)
    List<CompanyEntity> findLeafCompanies();
    
    /**
     * Find companies with most children
     */
    @Query("""
        SELECT c, COUNT(child) as childCount
        FROM CompanyEntity c 
        LEFT JOIN CompanyEntity child ON child.parentCompany = c
        GROUP BY c
        HAVING COUNT(child) > 0
        ORDER BY COUNT(child) DESC, c.name
    """)
    List<Object[]> findCompaniesWithMostChildren();
    
    /**
     * Find companies by filters
     */
    @Query("""
        SELECT c FROM CompanyEntity c 
        WHERE (:branchType IS NULL OR c.branchType = :branchType)
        AND (:region IS NULL OR c.region = :region)
        AND (:branchStatus IS NULL OR c.branchStatus = :branchStatus)
        AND (:minLevel IS NULL OR c.hierarchyLevel >= :minLevel)
        AND (:maxLevel IS NULL OR c.hierarchyLevel <= :maxLevel)
        ORDER BY c.hierarchyLevel, c.name
    """)
    List<CompanyEntity> findCompaniesByFilters(
        @Param("branchType") BranchTypeEntity branchType,
        @Param("region") String region,
        @Param("branchStatus") BranchStatusEntity branchStatus,
        @Param("minLevel") Integer minLevel,
        @Param("maxLevel") Integer maxLevel
    );
}