package com.nebula.erp.product.repository;

import com.nebula.erp.product.model.Product;
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 org.springframework.stereotype.Repository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;

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

@Repository
public interface ProductRepository extends JpaRepository<Product, Long>, JpaSpecificationExecutor<Product> {
  List<Product> findProductsByBrandId(Long brandId);

  List<Product> findProductsByCategoryId(Long categoryId);

  List<Product> findProductsByTaxId(Long taxId);

  // Custom method to find by id and tenant
  @Query("SELECT p FROM Product p WHERE p.id = :id AND (:tenantName = 'ALL' OR p.tenant = :tenantName)")
  Optional<Product> findByIdAndTenant(@Param("id") Long id, @Param("tenantName") String tenantName);

  // Fetch sales count by tenant
  @Query("SELECT COUNT(p) FROM Product p WHERE p.tenant = :tenantName AND p.tenant IS NOT NULL")
  Integer findCountByTenant(@Param("tenantName") String tenantName);

  @Query("""
          SELECT p FROM Product p
          WHERE (:tenant = 'ALL' OR p.tenant = :tenant)
            AND (
                  LOWER(p.code) LIKE :keyword
               OR LOWER(p.name) LIKE :keyword
               OR LOWER(p.brand.name) LIKE :keyword
               OR LOWER(p.category.name) LIKE :keyword
            )
      """)
  Page<Product> searchProducts(
      @Param("tenant") String tenant,
      @Param("keyword") String keyword,
      Pageable pageable);

}