/*
 * File: src/main/java/purchase/repository/GRNRepository.java
 * Description: This repository interface provides customized data access methods for the GRN entity.
 * It extends JpaRepository and JpaSpecificationExecutor to enable CRUD operations and complex query
 * specifications. The interface includes methods to filter GRNs based on associations with PurchaseReturns,
 * tenant constraints, and uniqueness of invoice numbers. This repository supports the ERP system's purchase
 * module in tracking and managing Goods Received Notes (GRNs) efficiently.
*/

package com.nebula.erp.purchase.repository;

import com.nebula.erp.purchase.model.GRN;
import com.nebula.erp.purchase.model.PurchaseReturn;
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.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Optional;

public interface GRNRepository extends JpaRepository<GRN, Long>, JpaSpecificationExecutor<GRN> {
    // Fetch all GRNs that are associated with a PurchaseReturn (i.e., have a grn_id
    // in PurchaseReturn)
    @Query("SELECT grn FROM GRN grn WHERE EXISTS (SELECT pr FROM PurchaseReturn pr WHERE pr.grn.id = grn.id) AND grn.tenant = :tenantName")
    Page<GRN> findAllWithPurchaseReturn(@Param("tenantName") String tenantName, Pageable pageable);

    // Fetch all GRNs that are not associated with a PurchaseReturn
    @Query("SELECT grn FROM GRN grn WHERE NOT EXISTS (SELECT pr FROM PurchaseReturn pr WHERE pr.grn.id = grn.id) AND grn.tenant = :tenantName")
    Page<GRN> findAllWithoutPurchaseReturn(@Param("tenantName") String tenantName, Pageable pageable);

    // Fetch GRNs by tenant name
    @Query("SELECT g FROM GRN g WHERE g.tenant = :tenantName")
    Page<GRN> findAllByTenant(Pageable pageable, @Param("tenantName") String tenantName);

    // Check if a GRN with the specified invoice number exists within a tenant
    @Query("SELECT COUNT(g) > 0 FROM GRN g WHERE g.invoice_number = :invoiceNumber AND g.tenant = :tenantName")
    boolean existsByInvoiceNumber(@Param("invoiceNumber") String invoiceNumber, @Param("tenantName") String tenantName);

    // Check if an invoice number exists, excluding a specified GRN ID, within a
    // tenant
    @Query("SELECT COUNT(g) > 0 FROM GRN g WHERE g.invoice_number = :invoiceNumber AND g.id <> :currentId AND g.tenant = :tenantName")
    boolean existsByInvoiceNumberExcludingCurrent(@Param("invoiceNumber") String invoiceNumber,
            @Param("currentId") Long currentId, @Param("tenantName") String tenantName);

    // Check if there are any GRNs associated with a specific supplier
    boolean existsBySupplierId(Long supplierId);

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

//     @Query("SELECT COUNT(g) > 0 FROM GRN g WHERE g.purchase_order.id = :purchaseOrderId AND g.tenant = :tenantName")
//     boolean existsByPurchaseOrderId(@Param("purchaseOrderId") Long purchaseOrderId,
//             @Param("tenantName") String tenantName);

    @Query("""
                SELECT g FROM GRN g
                WHERE g.tenant = :tenant
                  AND (
                        CAST(g.id AS string) LIKE :keyword
                     OR CAST(g.purchase_order.id AS string) LIKE :keyword
                     OR LOWER(g.supplier.supplier_name) LIKE :keyword
                     OR LOWER(g.invoice_number) LIKE :keyword
                     OR CAST(g.date AS string) LIKE :keyword
                  )
            """)
    Page<GRN> searchAllGRN(
            @Param("tenant") String tenant,
            @Param("keyword") String keyword,
            Pageable pageable);

    @Query("""
                SELECT g FROM GRN g
                WHERE g.tenant = :tenant
                  AND EXISTS (SELECT pr FROM PurchaseReturn pr WHERE pr.grn.id = g.id)
                  AND (
                        CAST(g.id AS string) LIKE :keyword
                     OR CAST(g.purchase_order.id AS string) LIKE :keyword
                     OR LOWER(g.supplier.supplier_name) LIKE :keyword
                     OR LOWER(g.invoice_number) LIKE :keyword
                     OR CAST(g.date AS string) LIKE :keyword
                  )
            """)
    Page<GRN> searchWithPurchaseReturn(
            @Param("tenant") String tenant,
            @Param("keyword") String keyword,
            Pageable pageable);

    @Query("""
                SELECT g FROM GRN g
                WHERE g.tenant = :tenant
                  AND NOT EXISTS (SELECT pr FROM PurchaseReturn pr WHERE pr.grn.id = g.id)
                  AND (
                        CAST(g.id AS string) LIKE :keyword
                     OR CAST(g.purchase_order.id AS string) LIKE :keyword
                     OR LOWER(g.supplier.supplier_name) LIKE :keyword
                     OR LOWER(g.invoice_number) LIKE :keyword
                     OR CAST(g.date AS string) LIKE :keyword
                  )
            """)
    Page<GRN> searchWithoutPurchaseReturn(
            @Param("tenant") String tenant,
            @Param("keyword") String keyword,
            Pageable pageable);

    @Query("""
                SELECT g FROM GRN g
                WHERE g.tenant = :tenant
                  AND g.purchase_order.id = :purchaseOrderId
                  AND (
                        CAST(g.id AS string) LIKE :keyword
                     OR LOWER(g.invoice_number) LIKE :keyword
                     OR LOWER(g.supplier.supplier_name) LIKE :keyword
                  )
            """)
    Page<GRN> searchByPurchaseOrder(
            @Param("tenant") String tenant,
            @Param("purchaseOrderId") Long purchaseOrderId,
            @Param("keyword") String keyword,
            Pageable pageable);

}