package br.gov.sigefp.treasury.repository;

import br.gov.sigefp.treasury.domain.BankReconciliation;
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.LocalDate;
import java.util.List;
import java.util.Optional;
import java.util.UUID;

@Repository
public interface BankReconciliationRepository extends JpaRepository<BankReconciliation, UUID> {
    
    @Query("SELECT br FROM BankReconciliation br WHERE br.cashAccount.id = :cashAccountId")
    List<BankReconciliation> findByCashAccountId(@Param("cashAccountId") UUID cashAccountId);
    
    List<BankReconciliation> findByStatus(String status);
    
    @Query("SELECT br FROM BankReconciliation br WHERE br.cashAccount.id = :cashAccountId " +
           "AND br.reconciliationDate BETWEEN :startDate AND :endDate")
    List<BankReconciliation> findByCashAccountIdAndDateRange(
        @Param("cashAccountId") UUID cashAccountId,
        @Param("startDate") LocalDate startDate,
        @Param("endDate") LocalDate endDate
    );
    
    @Query("SELECT br FROM BankReconciliation br WHERE br.cashAccount.id = :cashAccountId " +
           "AND br.status = 'PENDING' ORDER BY br.reconciliationDate DESC")
    List<BankReconciliation> findPendingByCashAccount(@Param("cashAccountId") UUID cashAccountId);
    
    @Query("SELECT br FROM BankReconciliation br WHERE br.cashAccount.id = :cashAccountId " +
           "ORDER BY br.reconciliationDate DESC")
    Optional<BankReconciliation> findFirstByCashAccountIdOrderByReconciliationDateDesc(@Param("cashAccountId") UUID cashAccountId);
}
