package br.gov.sigefp.treasury.repository;

import br.gov.sigefp.treasury.domain.CashFlow;
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 org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.util.List;
import java.util.UUID;

@Repository
public interface CashFlowRepository extends JpaRepository<CashFlow, UUID> {
    
    @Query("SELECT cf FROM CashFlow cf WHERE cf.cashAccount.id = :cashAccountId")
    Page<CashFlow> findByCashAccountId(@Param("cashAccountId") UUID cashAccountId, Pageable pageable);
    
    @Query("SELECT cf FROM CashFlow cf WHERE cf.cashAccount.id = :cashAccountId " +
           "AND cf.transactionDate BETWEEN :startDate AND :endDate")
    Page<CashFlow> findByCashAccountIdAndDateRange(
        @Param("cashAccountId") UUID cashAccountId,
        @Param("startDate") LocalDate startDate,
        @Param("endDate") LocalDate endDate,
        Pageable pageable
    );
    
    List<CashFlow> findByType(String type);
    
    @Query("SELECT cf FROM CashFlow cf WHERE cf.cashAccount.id = :cashAccountId AND cf.type = :type")
    List<CashFlow> findByCashAccountIdAndType(@Param("cashAccountId") UUID cashAccountId, 
                                               @Param("type") String type);
    
    @Query("SELECT SUM(cf.amount) FROM CashFlow cf WHERE cf.cashAccount.id = :cashAccountId " +
           "AND cf.type = :type AND cf.transactionDate BETWEEN :startDate AND :endDate")
    java.math.BigDecimal calculateTotalByAccountAndType(
        @Param("cashAccountId") UUID cashAccountId,
        @Param("type") String type,
        @Param("startDate") LocalDate startDate,
        @Param("endDate") LocalDate endDate
    );
    
    @Query("SELECT cf FROM CashFlow cf WHERE cf.referenceType = :referenceType " +
           "AND cf.referenceId = :referenceId")
    List<CashFlow> findByReference(@Param("referenceType") String referenceType, 
                                   @Param("referenceId") UUID referenceId);
}
