package br.gov.sigefp.budget.repository;

import br.gov.sigefp.budget.domain.BudgetExecution;
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.math.BigDecimal;
import java.util.List;
import java.util.UUID;

@Repository
public interface BudgetExecutionRepository extends JpaRepository<BudgetExecution, UUID>,
                org.springframework.data.jpa.repository.JpaSpecificationExecutor<BudgetExecution> {

        List<BudgetExecution> findByBudgetLineId(UUID budgetLineId);

        @Query("SELECT be FROM BudgetExecution be WHERE be.budgetLine.id = :budgetLineId AND be.movementType = :movementType")
        List<BudgetExecution> findByBudgetLineIdAndMovementType(@Param("budgetLineId") UUID budgetLineId,
                        @Param("movementType") String movementType);

        @Query("SELECT be FROM BudgetExecution be WHERE be.periodId = :periodId")
        List<BudgetExecution> findByPeriodId(@Param("periodId") Long periodId);

        @Query("SELECT COALESCE(SUM(be.amount), 0) FROM BudgetExecution be WHERE be.budgetLine.id = :budgetLineId AND be.movementType = 'COMMITMENT'")
        BigDecimal calculateTotalCommittedByBudgetLineId(@Param("budgetLineId") UUID budgetLineId);

        @Query("SELECT be FROM BudgetExecution be WHERE be.budgetLine.fiscalYear.id = :fiscalYearId")
        List<BudgetExecution> findExecutionsByFiscalYearId(@Param("fiscalYearId") UUID fiscalYearId);

        @Query("SELECT be FROM BudgetExecution be WHERE " +
                        "(:budgetLineId IS NULL OR be.budgetLine.id = :budgetLineId) AND " +
                        "(:periodId IS NULL OR be.periodId = :periodId) AND " +
                        "(:movementType IS NULL OR be.movementType = :movementType)")
        Page<BudgetExecution> findByFilters(
                        @Param("budgetLineId") UUID budgetLineId,
                        @Param("periodId") Long periodId,
                        @Param("movementType") String movementType,
                        Pageable pageable);

        // Helper para listas sem paginação (exportação)
        @Query("SELECT be FROM BudgetExecution be WHERE " +
                        "(:budgetLineId IS NULL OR be.budgetLine.id = :budgetLineId) AND " +
                        "(:periodId IS NULL OR be.periodId = :periodId) AND " +
                        "(:movementType IS NULL OR be.movementType = :movementType)")
        List<BudgetExecution> findByFiltersList(
                        @Param("budgetLineId") UUID budgetLineId,
                        @Param("periodId") Long periodId,
                        @Param("movementType") String movementType);

        @Query("SELECT COUNT(be) FROM BudgetExecution be WHERE be.budgetLine.fiscalYear.id = :fiscalYearId AND be.movementType = :movementType")
        long countByFiscalYearIdAndMovementType(@Param("fiscalYearId") UUID fiscalYearId,
                        @Param("movementType") String movementType);

        @Query("SELECT COALESCE(SUM(be.amount), 0) FROM BudgetExecution be WHERE be.referenceId = :referenceId AND be.movementType = 'COMMITMENT'")
        BigDecimal calculateTotalCommittedByReferenceId(@Param("referenceId") UUID referenceId);

        @Query("SELECT COALESCE(SUM(be.amount), 0) FROM BudgetExecution be WHERE be.referenceId = :referenceId AND be.movementType = 'LIQUIDATION'")
        BigDecimal calculateTotalLiquidatedByReferenceId(@Param("referenceId") UUID referenceId);

        @Query("SELECT COALESCE(SUM(be.amount), 0) FROM BudgetExecution be WHERE be.referenceId = :referenceId AND be.movementType = 'PAYMENT'")
        BigDecimal calculateTotalPaidByReferenceId(@Param("referenceId") UUID referenceId);
}
