Revenue Tracking & Analytics

public async Task<TourReservationStatsDto> GetTourReservationStatsAsync(
    int tourId,
    int recentDays = 30,
    CancellationToken cancellationToken = default)
{
    var sql = $@"
    SELECT 
        CAST(r.CreatedAt AS date) AS DayDate,
        COUNT(*) AS ReservationCount,
        COALESCE(SUM(r.AmountPaid), 0) AS TotalAmount,
        SUM(CASE WHEN r.PaymentStatus = 1 THEN 1 ELSE 0 END) AS PaidCount,
        SUM(CASE WHEN r.PaymentStatus <> 1 THEN 1 ELSE 0 END) AS PendingCount,
        COALESCE(SUM(r.AmountPaid), 0) AS PaidAmount,
        COALESCE(SUM(r.TotalAmount - ISNULL(r.AmountPaid, 0)), 0) AS PendingAmount
    FROM {reservationTable} r
    WHERE r.TourId = @TourId
      AND r.CreatedAt >= DATEADD(day, -@RecentDays, SYSUTCDATETIME())
    GROUP BY CAST(r.CreatedAt AS date)
    ORDER BY DayDate ASC;";
    
    // Returns daily breakdown and totals
}

public async Task<decimal> GetOwnerEarningsAsync(
    int ownerUserId,
    CancellationToken cancellationToken = default)
{
    var sql = $@"
    SELECT COALESCE(SUM(r.AmountPaid), 0)
    FROM {reservationTable} r
    INNER JOIN {tourTable} t ON t.TourId = r.TourId
    WHERE t.OwnerUserId = @OwnerUserId";
    
    var result = await ExecuteScalarAsync<decimal>(sql, new { OwnerUserId = ownerUserId });
    return result;
}

public async Task<(int TotalServices, int RecentServices)> GetOwnerTourStatsAsync(
    int ownerUserId,
    int recentDays = 7,
    CancellationToken cancellationToken = default)
{
    var totalSql = $@"
    SELECT COUNT(*)
    FROM {tourTable}
    WHERE OwnerUserId = @OwnerUserId
      AND IsActive = 1";
    
    var recentSql = $@"
    SELECT COUNT(*)
    FROM {tourTable}
    WHERE OwnerUserId = @OwnerUserId
      AND IsActive = 1
      AND CreatedAt >= DATEADD(day, -@RecentDays, SYSUTCDATETIME())";
    
    // Returns (total, recent)
}

public sealed record TourReservationStatsDto(
    int TourId,
    int TotalReservations,
    decimal TotalAmount,
    IReadOnlyList<TourReservationDayStat> Daily);

public sealed record TourReservationDayStat(
    DateTime Date,
    int Count,
    decimal Amount,
    int PaidCount,
    int PendingCount,
    decimal PaidAmount,
    decimal PendingAmount);

<div style="display:grid;grid-template-columns:repeat(3,1fr);gap:14px;">
    <!-- Tickets Card -->
    <div style="background:linear-gradient(135deg,#f0fdf4,#dcfce7);">
        <div style="font-size:32px;font-weight:800;">@_reportTotalTickets</div>
        <div>Tickets registrados para el evento</div>
    </div>
    
    <!-- Reservations Card -->
    <div style="background:linear-gradient(135deg,#eff6ff,#dbeafe);">
        <div style="font-size:32px;font-weight:800;">@_reportReservationCount</div>
        <div>Incluye todas las reservas asociadas</div>
    </div>
    
    <!-- Sales Card -->
    <div style="background:linear-gradient(135deg,#fff8f5,#ffedd5);">
        <div style="font-size:24px;font-weight:800;">
            @FormatCurrency(_reportTotalAmount, _reportCurrency)
        </div>
        <div>Monto acumulado del evento</div>
    </div>
</div>

private async Task OpenStatsModalAsync(TourBookingViewModel model)
{
    _selectedService = model;
    _statsError = null;
    _salesStats = null;
    _showStatsModal = true;
    _loadingStats = true;

    await LoadSalesStatsAsync(model.Tour.TourId);
}

private async Task LoadSalesStatsAsync(int tourId)
{
    try
    {
        _salesStats = await TourService.GetTourReservationStatsAsync(
            tourId, 
            _salesWindowDays
        );
        _reservationDetails = await TourService.GetReservationsByTourAsync(tourId);
    }
    catch (Exception ex)
    {
        _statsError = "No se pudieron cargar las ventas.";
        Console.WriteLine(ex);
    }
    finally
    {
        _loadingStats = false;
        await InvokeAsync(StateHasChanged);
        
        if (_salesStats is not null && _salesStats.Daily.Any())
        {
            await RenderChartAsync();
        }
    }
}

private async Task RenderChartAsync()
{
    try 
    {
        var ordered = SortedSalesDaily.ToList();
        var labels = ordered.Select(d => d.Date.ToString("dd/MM")).ToArray();
        var paidCounts = ordered.Select(d => d.PaidCount).ToArray();
        var pendingCounts = ordered.Select(d => d.PendingCount).ToArray();
        var paidAmounts = ordered.Select(d => d.PaidAmount).ToArray();
        var pendingAmounts = ordered.Select(d => d.PendingAmount).ToArray();

        await JS.InvokeVoidAsync("chartHelper.renderSalesChart", 
            "salesChart", 
            labels, 
            paidCounts,
            pendingCounts,
            paidAmounts, 
            pendingAmounts,
            "Reservas Pagadas", 
            "Reservas Pendientes",
            "Dinero Pagado",
            "Dinero Pendiente");
    }
    catch(Exception ex)
    {
         Console.WriteLine($"Error rendering chart: {ex.Message}");
    }
}

<div style="overflow-x:auto;max-height:220px;overflow-y:auto;">
    @if (_reservationDetails is null || !_reservationDetails.Any())
    {
        <p>No hay detalles disponibles.</p>
    }
    else
    {
        <table class="bk-table">
            <thead>
                <tr>
                    <th>Fecha</th>
                    <th>Cliente</th>
                    <th>Cant.</th>
                    <th>Total</th>
                    <th>Estado</th>
                </tr>
            </thead>
            <tbody>
                @foreach (var r in _reservationDetails.OrderByDescending(r => r.TravelDate))
                {
                    <tr>
                        <td>@r.TravelDate.ToString("dd MMM HH:mm")</td>
                        <td>@r.ContactName</td>
                        <td>@r.Tickets.Sum(t => t.Quantity)</td>
                        <td>@FormatCurrency(r.TotalAmount, r.CurrencyCode)</td>
                        <td>
                            <span class="bk-badge @(r.PaymentStatus == 1 ? "bk-badge--success" : "bk-badge--warning")">
                                @GetPaymentLabel(r.PaymentStatus)
                            </span>
                        </td>
                    </tr>
                }
            </tbody>
        </table>
    }
</div>

public async Task<(DateTime? StartDate, DateTime? EndDate)> GetTourDateRangeAsync(
    int tourId,
    CancellationToken cancellationToken = default)
{
    const string sql = @"
    SELECT 
        MIN(FechaInicio) AS StartDate,
        MAX(FechaFin) AS EndDate
    FROM TourFechas
    WHERE EventoId = @TourId";
    
    // Returns date range from TourFechas table
}