package com.social.media.repository;

import com.social.media.domain.entity.Company;
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 CompanyRepository extends JpaRepository<Company, Long> {
    
    // Find by unique fields
    Optional<Company> findByCnpj(String cnpj);
    
    Optional<Company> findByEmail(String email);
    
    Optional<Company> findByCompanyCode(String companyCode);
    
    // Find by status
    @Query("SELECT c FROM Company c WHERE c.status = :status")
    Page<Company> findByStatus(@Param("status") Company.CompanyStatus status, Pageable pageable);
    
    // Find active companies
    @Query("SELECT c FROM Company c WHERE c.status = 'ACTIVE'")
    Page<Company> findActiveCompanies(Pageable pageable);
    
    // Search companies by name (case insensitive)
    @Query("SELECT c FROM Company c WHERE LOWER(c.name) LIKE LOWER(CONCAT('%', :name, '%'))")
    Page<Company> findByNameContainingIgnoreCase(@Param("name") String name, Pageable pageable);
    
    // Search companies by CNPJ (partial match)
    @Query("SELECT c FROM Company c WHERE c.cnpj LIKE CONCAT('%', :cnpj, '%')")
    Page<Company> findByCnpjContaining(@Param("cnpj") String cnpj, Pageable pageable);
    
    // Search companies by email (case insensitive)
    @Query("SELECT c FROM Company c WHERE LOWER(c.email) LIKE LOWER(CONCAT('%', :email, '%'))")
    Page<Company> findByEmailContainingIgnoreCase(@Param("email") String email, Pageable pageable);
    
    // General search (name, email, cnpj, activity sector)
    @Query("SELECT c FROM Company c WHERE " +
           "LOWER(c.name) LIKE LOWER(CONCAT('%', :query, '%')) OR " +
           "LOWER(c.email) LIKE LOWER(CONCAT('%', :query, '%')) OR " +
           "c.cnpj LIKE CONCAT('%', :query, '%') OR " +
           "LOWER(c.activitySector) LIKE LOWER(CONCAT('%', :query, '%'))")
    Page<Company> searchCompanies(@Param("query") String query, Pageable pageable);
    
    // Find by plan
    @Query("SELECT c FROM Company c WHERE c.plan = :plan")
    Page<Company> findByPlan(@Param("plan") Company.CompanyPlan plan, Pageable pageable);
    
    // Find by activity sector
    @Query("SELECT c FROM Company c WHERE LOWER(c.activitySector) = LOWER(:sector)")
    Page<Company> findByActivitySector(@Param("sector") String sector, Pageable pageable);
    
    // Hierarchy queries
    @Query("SELECT c FROM Company c WHERE c.parentCompanyId = :parentId")
    List<Company> findByParentCompanyId(@Param("parentId") Long parentId);
    
    @Query("SELECT c FROM Company c WHERE c.parentCompanyId IS NULL")
    List<Company> findMatrizCompanies();
    
    @Query("SELECT c FROM Company c WHERE c.hierarchyLevel = :level")
    List<Company> findByHierarchyLevel(@Param("level") Integer level);
    
    // Branch queries
    @Query("SELECT c FROM Company c WHERE c.branchType = :branchType")
    Page<Company> findByBranchType(@Param("branchType") Company.CompanyBranchType branchType, Pageable pageable);
    
    @Query("SELECT c FROM Company c WHERE c.branchStatus = :branchStatus")
    Page<Company> findByBranchStatus(@Param("branchStatus") Company.CompanyBranchStatus branchStatus, Pageable pageable);
    
    @Query("SELECT c FROM Company c WHERE c.region = :region")
    Page<Company> findByRegion(@Param("region") String region, Pageable pageable);
    
    // Address queries
    @Query("SELECT c FROM Company c WHERE LOWER(c.addressCity) = LOWER(:city)")
    Page<Company> findByCity(@Param("city") String city, Pageable pageable);
    
    @Query("SELECT c FROM Company c WHERE LOWER(c.addressState) = LOWER(:state)")
    Page<Company> findByState(@Param("state") String state, Pageable pageable);
    
    @Query("SELECT c FROM Company c WHERE c.addressZipCode = :zipCode")
    List<Company> findByZipCode(@Param("zipCode") String zipCode);
    
    // Statistics queries
    @Query("SELECT COUNT(c) FROM Company c WHERE c.status = 'ACTIVE'")
    Long countActiveCompanies();
    
    @Query("SELECT COUNT(c) FROM Company c WHERE c.plan = :plan")
    Long countByPlan(@Param("plan") Company.CompanyPlan plan);
    
    @Query("SELECT COUNT(c) FROM Company c WHERE c.parentCompanyId = :parentId")
    Long countByParentCompanyId(@Param("parentId") Long parentId);
    
    // Recent companies
    @Query("SELECT c FROM Company c WHERE c.createdAt >= :fromDate ORDER BY c.createdAt DESC")
    List<Company> findRecentCompanies(@Param("fromDate") LocalDateTime fromDate, Pageable pageable);
    
    // Companies by creation date range
    @Query("SELECT c FROM Company c WHERE c.createdAt BETWEEN :startDate AND :endDate ORDER BY c.createdAt DESC")
    Page<Company> findByCreatedAtBetween(@Param("startDate") LocalDateTime startDate, 
                                        @Param("endDate") LocalDateTime endDate, 
                                        Pageable pageable);
    
    // Check if CNPJ exists (excluding specific company for updates)
    @Query("SELECT CASE WHEN COUNT(c) > 0 THEN true ELSE false END FROM Company c WHERE c.cnpj = :cnpj AND c.id != :excludeId")
    boolean existsByCnpjAndIdNot(@Param("cnpj") String cnpj, @Param("excludeId") Long excludeId);
    
    // Check if email exists (excluding specific company for updates)
    @Query("SELECT CASE WHEN COUNT(c) > 0 THEN true ELSE false END FROM Company c WHERE c.email = :email AND c.id != :excludeId")
    boolean existsByEmailAndIdNot(@Param("email") String email, @Param("excludeId") Long excludeId);
    
    // Find companies with hierarchical structure (parent and children)
    @Query("SELECT c FROM Company c WHERE c.id = :companyId OR c.parentCompanyId = :companyId ORDER BY c.hierarchyLevel, c.name")
    List<Company> findCompanyHierarchy(@Param("companyId") Long companyId);
    
    // Companies requiring verification or action
    @Query("SELECT c FROM Company c WHERE c.status IN ('TRIAL', 'INACTIVE') AND c.createdAt < :threshold")
    List<Company> findCompaniesRequiringAction(@Param("threshold") LocalDateTime threshold);
    
    // Top companies by specific criteria (could be extended for metrics)
    @Query("SELECT c FROM Company c WHERE c.status = 'ACTIVE' ORDER BY c.createdAt DESC")
    List<Company> findTopActiveCompanies(Pageable pageable);
}