package com.saas.admin.repository;

import com.saas.admin.entity.AiApiCostRecord;
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 AI API Cost Records
 * 
 * Clean Architecture:
 * - Read-only queries for cost analytics
 * - Write operations handled by AiCostTrackingService
 * - No business logic in repository (just data access)
 */
@Repository
public interface AiApiCostRecordRepository extends JpaRepository<AiApiCostRecord, Long> {
    
    /**
     * Find all AI cost records for a specific call
     * A call may have multiple AI providers (e.g., OpenAI + ElevenLabs)
     */
    List<AiApiCostRecord> findByCallSid(String callSid);
    
    /**
     * Find AI costs by provider
     */
    List<AiApiCostRecord> findByAiProvider(String aiProvider);
    
    /**
     * Find AI costs by provider and cost type
     */
    List<AiApiCostRecord> findByAiProviderAndCostType(String aiProvider, String costType);
    
    /**
     * Find AI costs by tenant
     */
    List<AiApiCostRecord> findByTenantId(String tenantId);
    
    /**
     * Find AI costs by tenant and date range
     */
    List<AiApiCostRecord> findByTenantIdAndCreatedAtBetween(
            String tenantId, 
            LocalDateTime start, 
            LocalDateTime end
    );
    
    /**
     * Find AI costs by date range
     */
    List<AiApiCostRecord> findByCreatedAtBetween(LocalDateTime start, LocalDateTime end);
    
    /**
     * Calculate total AI cost for a tenant within date range
     */
    @Query("SELECT SUM(c.cost) FROM AiApiCostRecord c " +
           "WHERE c.tenantId = :tenantId " +
           "AND c.createdAt BETWEEN :start AND :end")
    BigDecimal getTotalCostByTenant(
            @Param("tenantId") String tenantId,
            @Param("start") LocalDateTime start,
            @Param("end") LocalDateTime end
    );
    
    /**
     * Calculate total AI cost per provider for a tenant
     */
    @Query("SELECT c.aiProvider, SUM(c.cost) FROM AiApiCostRecord c " +
           "WHERE c.tenantId = :tenantId " +
           "AND c.createdAt BETWEEN :start AND :end " +
           "GROUP BY c.aiProvider")
    List<Object[]> getCostByProviderForTenant(
            @Param("tenantId") String tenantId,
            @Param("start") LocalDateTime start,
            @Param("end") LocalDateTime end
    );
    
    /**
     * Calculate total AI cost per cost type for a tenant
     */
    @Query("SELECT c.costType, SUM(c.cost) FROM AiApiCostRecord c " +
           "WHERE c.tenantId = :tenantId " +
           "AND c.createdAt BETWEEN :start AND :end " +
           "GROUP BY c.costType")
    List<Object[]> getCostByTypeForTenant(
            @Param("tenantId") String tenantId,
            @Param("start") LocalDateTime start,
            @Param("end") LocalDateTime end
    );
    
    /**
     * Get call count with AI usage for a tenant
     */
    @Query("SELECT COUNT(DISTINCT c.callSid) FROM AiApiCostRecord c " +
           "WHERE c.tenantId = :tenantId " +
           "AND c.createdAt BETWEEN :start AND :end")
    Long getCallCountByTenant(
            @Param("tenantId") String tenantId,
            @Param("start") LocalDateTime start,
            @Param("end") LocalDateTime end
    );
    
    /**
     * Check if AI costs already exist for a call
     * Prevents duplicate cost recording
     */
    boolean existsByCallSid(String callSid);
}