package com.nebula.erp.sales.repository;

import com.nebula.erp.sales.model.PaymentHistory;
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;

@Repository
public interface PaymentHistoryRepository extends JpaRepository<PaymentHistory, Long> {

        @Query("SELECT p FROM PaymentHistory p WHERE p.encounter_id = :encounterId AND p.tenant = :tenant AND p.entry_type = 'ADVANCE_ADDED' ORDER BY p.created_at ASC")
        List<PaymentHistory> findAdvanceAddedByEncounter(@Param("encounterId") String encounterId,
                        @Param("tenant") String tenant);

        // Ledger
        @Query("SELECT p FROM PaymentHistory p WHERE p.tenant = :tenant AND p.patient_id = :patientId ORDER BY p.created_at ASC")
        List<PaymentHistory> findByPatient(@Param("patientId") String patientId,
                        @Param("tenant") String tenant);

        // Sale-wise ledger
        @Query("SELECT p FROM PaymentHistory p WHERE p.tenant = :tenant AND p.sales_id = :salesId ORDER BY p.created_at ASC")
        List<PaymentHistory> findBySalesIdAndTenant(@Param("salesId") Long salesId,
                        @Param("tenant") String tenant);

        @Query("SELECT COALESCE(SUM(ph.advance),0) FROM PaymentHistory ph WHERE ph.patient_id = :patientId AND ph.tenant = :tenant AND ph.entry_type = 'ADVANCE_ADDED'")
        double findOriginalAdvance(@Param("patientId") String patientId,
                        @Param("tenant") String tenant);

        // OLD NAME → NEW LOGIC
        @Query("SELECT COALESCE(SUM(ABS(ph.credit)),0) FROM PaymentHistory ph WHERE ph.patient_id = :patientId AND ph.tenant = :tenant AND ph.entry_type = 'REFUND'")
        double findTotalRefund(@Param("patientId") String patientId,
                        @Param("tenant") String tenant);

        // OLD NAME → NEW LOGIC
        @Query("SELECT COALESCE(SUM(ph.credit),0) FROM PaymentHistory ph WHERE ph.sales_id = :salesId AND ph.entry_type = 'PAYMENT'")
        double findTotalPaidForSale(@Param("salesId") Long salesId);

        // Optional — used internally by PaymentHistoryService, not by controller
        @Query("SELECT COALESCE(SUM(ph.advance_used),0) FROM PaymentHistory ph WHERE ph.sales_id = :salesId AND ph.entry_type = 'ADVANCE_USED'")
        double findAdvanceUsedForSale(@Param("salesId") Long salesId);

        @Query("SELECT COALESCE(SUM(ph.credit),0) FROM PaymentHistory ph WHERE ph.patient_id = :patientId AND ph.sales_id <> :currentSaleId AND ph.entry_type = 'PAYMENT' AND ph.created_at >= :currentSaleCreatedAt")
        double findPreviousDuePaidAfterSale(@Param("patientId") String patientId,
                        @Param("currentSaleId") Long currentSaleId,
                        @Param("currentSaleCreatedAt") LocalDateTime currentSaleCreatedAt);

        @Query("SELECT p FROM PaymentHistory p WHERE p.encounter_id = :encounterId AND p.tenant = :tenant AND p.entry_type = 'REFUND' ORDER BY p.created_at ASC")
        List<PaymentHistory> findRefundByEncounter(@Param("encounterId") String encounterId,
                        @Param("tenant") String tenant);
}