/*
 * File: src/main/java/purchase/repository/PurchaseRepository.java
 * Description: This repository interface provides methods to access and manage Purchase entities within
 * the ERP system. It extends JpaRepository, allowing CRUD operations and includes custom queries
 * for filtering purchases based on their association with GRNs. These methods enable efficient retrieval
 * of purchases by tenant name, as well as checks for GRN and supplier associations, supporting robust
 * data access and management within the purchase module.
*/

package com.nebula.erp.purchase.repository;

import com.nebula.erp.purchase.model.Purchase;
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 java.util.Optional;

public interface PurchaseRepository extends JpaRepository<Purchase, Long> {
    // Fetch purchases associated with a GRN
    @Query("""
                SELECT p FROM Purchase p
                WHERE p.tenant = :tenant
                  AND EXISTS (
                      SELECT grn FROM GRN grn
                      WHERE grn.purchase_order.id = p.id
                  )
                ORDER BY p.created_at DESC
            """)
    Page<Purchase> findAllWithGRN(
            @Param("tenant") String tenant,
            Pageable pageable);

    // Fetch purchases not associated with a GRN
    @Query("""
                SELECT p FROM Purchase p
                WHERE p.tenant = :tenant
                  AND NOT EXISTS (
                      SELECT grn FROM GRN grn
                      WHERE grn.purchase_order.id = p.id
                  )
                ORDER BY p.created_at DESC
            """)
    Page<Purchase> findAllWithoutGRN(
            @Param("tenant") String tenant,
            Pageable pageable);

    // Fetch purchases with GRN by tenant
    @Query("""
                SELECT p FROM Purchase p
                WHERE p.tenant = :tenant
                ORDER BY p.created_at DESC
            """)
    Page<Purchase> findAllByTenant(
            @Param("tenant") String tenant,
            Pageable pageable);

    boolean existsBySupplierId(Long supplierId);

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

    // Fetch purchases count by tenant
    @Query("SELECT COUNT(p) FROM Purchase p WHERE p.tenant = :tenant")
    Integer findCountByTenant(@Param("tenant") String tenant);

    @Query("""
                SELECT p FROM Purchase p
                WHERE p.tenant = :tenant
                  AND (
                        CAST(p.id AS string) LIKE :keyword
                     OR LOWER(p.supplier.supplier_name) LIKE :keyword
                     OR CAST(p.date AS string) LIKE :keyword
                     OR LOWER(p.status) LIKE :keyword
                  )
                ORDER BY p.created_at DESC
            """)
    Page<Purchase> searchPurchases(
            @Param("tenant") String tenant,
            @Param("keyword") String keyword,
            Pageable pageable);
}