package com.saas.admin.repository;

import com.saas.admin.entity.VapiCallCostRecord;
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.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * Repository for Vapi.ai call cost records
 * Supports cost analytics and tenant-based filtering
 */
@Repository
public interface VapiCallCostRecordRepository extends JpaRepository<VapiCallCostRecord, Long> {
    
    Optional<VapiCallCostRecord> findByVapiCallId(String vapiCallId);
    
    List<VapiCallCostRecord> findByTenantId(String tenantId);
    
    List<VapiCallCostRecord> findByAssistantId(String assistantId);
    
    List<VapiCallCostRecord> findByTenantIdAndAssistantId(String tenantId, String assistantId);
    
    @Query("SELECT r FROM VapiCallCostRecord r WHERE r.tenantId = :tenantId " +
           "AND r.createdAt BETWEEN :startDate AND :endDate " +
           "ORDER BY r.createdAt DESC")
    List<VapiCallCostRecord> findByTenantIdAndDateRange(
        @Param("tenantId") String tenantId,
        @Param("startDate") LocalDateTime startDate,
        @Param("endDate") LocalDateTime endDate
    );
    
    @Query("SELECT r FROM VapiCallCostRecord r WHERE r.createdAt BETWEEN :startDate AND :endDate " +
           "ORDER BY r.createdAt DESC")
    List<VapiCallCostRecord> findByDateRange(
        @Param("startDate") LocalDateTime startDate,
        @Param("endDate") LocalDateTime endDate
    );
    
    @Query("SELECT SUM(r.totalCost) FROM VapiCallCostRecord r WHERE r.tenantId = :tenantId")
    BigDecimal sumTotalCostByTenantId(@Param("tenantId") String tenantId);
    
    @Query("SELECT SUM(r.totalCost) FROM VapiCallCostRecord r WHERE r.tenantId = :tenantId " +
           "AND r.createdAt BETWEEN :startDate AND :endDate")
    BigDecimal sumTotalCostByTenantIdAndDateRange(
        @Param("tenantId") String tenantId,
        @Param("startDate") LocalDateTime startDate,
        @Param("endDate") LocalDateTime endDate
    );
    
    @Query("SELECT AVG(r.totalCost) FROM VapiCallCostRecord r WHERE r.tenantId = :tenantId")
    BigDecimal avgTotalCostByTenantId(@Param("tenantId") String tenantId);
    
    @Query("SELECT COUNT(r) FROM VapiCallCostRecord r WHERE r.tenantId = :tenantId")
    Long countByTenantId(@Param("tenantId") String tenantId);
}