// EN: Query for advanced revenue analytics with trends, payment methods, top products, and vertical breakdown.
// VI: Query cho phan tich doanh thu nang cao voi xu huong, phuong thuc thanh toan, san pham ban chay, va phan tich theo nganh.

using System.Data;
using Dapper;
using MediatR;

namespace OrderService.API.Application.Queries.Reports;

/// <summary>
/// EN: Query for revenue analytics over a date range with configurable period grouping.
/// VI: Query phan tich doanh thu trong khoang thoi gian voi nhom theo ky co the cau hinh.
/// </summary>
public record GetRevenueAnalyticsQuery(
    Guid ShopId,
    DateTime StartDate,
    DateTime EndDate,
    string Period = "daily"
) : IRequest<RevenueAnalyticsDto>;

/// <summary>
/// EN: Revenue analytics DTO with complete analytics summary.
/// VI: DTO phan tich doanh thu voi tom tat phan tich day du.
/// </summary>
public record RevenueAnalyticsDto(
    decimal TotalRevenue,
    decimal PreviousPeriodRevenue,
    decimal GrowthPercentage,
    int TotalOrders,
    decimal AverageOrderValue,
    List<RevenueTrendDto> Trends,
    List<PaymentMethodStatsDto> PaymentMethods,
    List<TopProductAnalyticsDto> TopProducts,
    List<VerticalRevenueDto> VerticalBreakdown
);

/// <summary>
/// EN: Revenue trend data point (class for Dapper compatibility).
/// VI: Diem du lieu xu huong doanh thu (class cho tuong thich Dapper).
/// </summary>
public class RevenueTrendDto
{
    public DateTime Date { get; set; }
    public decimal Revenue { get; set; }
    public int OrderCount { get; set; }
}

/// <summary>
/// EN: Payment method statistics (class for Dapper compatibility).
/// VI: Thong ke phuong thuc thanh toan (class cho tuong thich Dapper).
/// </summary>
public class PaymentMethodStatsDto
{
    public string Method { get; set; } = string.Empty;
    public decimal Amount { get; set; }
    public int Count { get; set; }
    public decimal Percentage { get; set; }
}

/// <summary>
/// EN: Top product analytics with quantity and revenue (class for Dapper compatibility).
/// VI: Phan tich san pham ban chay voi so luong va doanh thu (class cho tuong thich Dapper).
/// </summary>
public class TopProductAnalyticsDto
{
    public string Name { get; set; } = string.Empty;
    public int QuantitySold { get; set; }
    public decimal Revenue { get; set; }
}

/// <summary>
/// EN: Revenue breakdown by business vertical (class for Dapper compatibility).
/// VI: Phan tich doanh thu theo nganh kinh doanh (class cho tuong thich Dapper).
/// </summary>
public class VerticalRevenueDto
{
    public string Vertical { get; set; } = string.Empty;
    public decimal Revenue { get; set; }
    public int OrderCount { get; set; }
}

/// <summary>
/// EN: Handler for GetRevenueAnalyticsQuery — aggregates revenue data using Dapper for performance.
/// VI: Handler cho GetRevenueAnalyticsQuery — tong hop du lieu doanh thu bang Dapper cho hieu nang.
/// </summary>
public class GetRevenueAnalyticsQueryHandler : IRequestHandler<GetRevenueAnalyticsQuery, RevenueAnalyticsDto>
{
    private readonly IDbConnection _connection;
    private readonly ILogger<GetRevenueAnalyticsQueryHandler> _logger;

    public GetRevenueAnalyticsQueryHandler(IDbConnection connection, ILogger<GetRevenueAnalyticsQueryHandler> logger)
    {
        _connection = connection ?? throw new ArgumentNullException(nameof(connection));
        _logger = logger ?? throw new ArgumentNullException(nameof(logger));
    }

    public async Task<RevenueAnalyticsDto> Handle(GetRevenueAnalyticsQuery request, CancellationToken cancellationToken)
    {
        _logger.LogInformation(
            "EN: Generating revenue analytics for shop {ShopId} from {Start} to {End}, period {Period} / VI: Tao phan tich doanh thu cho shop {ShopId} tu {Start} den {End}, ky {Period}",
            request.ShopId, request.StartDate.ToString("yyyy-MM-dd"), request.EndDate.ToString("yyyy-MM-dd"), request.Period);

        var startDate = request.StartDate.Date;
        var endDate = request.EndDate.Date.AddDays(1); // EN: Inclusive end date / VI: Ngay ket thuc bao gom

        // EN: Calculate previous period for growth comparison / VI: Tinh ky truoc de so sanh tang truong
        var periodLength = (endDate - startDate).TotalDays;
        var prevStart = startDate.AddDays(-periodLength);
        var prevEnd = startDate;

        var parameters = new DynamicParameters();
        parameters.Add("ShopId", request.ShopId);
        parameters.Add("StartDate", startDate);
        parameters.Add("EndDate", endDate);
        parameters.Add("PrevStart", prevStart);
        parameters.Add("PrevEnd", prevEnd);
        parameters.Add("CancelledStatusId", 6); // EN: Cancelled status ID / VI: ID trang thai huy

        // EN: 1. Current period aggregate / VI: 1. Tong hop ky hien tai
        var currentAggregateSql = @"
            SELECT
                COALESCE(SUM(o.total_amount), 0) AS TotalRevenue,
                COUNT(*) AS TotalOrders,
                COALESCE(AVG(o.total_amount), 0) AS AverageOrderValue
            FROM orders o
            WHERE o.shop_id = @ShopId
              AND o.created_at >= @StartDate
              AND o.created_at < @EndDate
              AND o.status_id != @CancelledStatusId";

        var currentAgg = await _connection.QuerySingleAsync<CurrentAggregateRow>(currentAggregateSql, parameters);

        // EN: 2. Previous period revenue for growth calculation / VI: 2. Doanh thu ky truoc de tinh tang truong
        var prevRevenueSql = @"
            SELECT COALESCE(SUM(o.total_amount), 0)
            FROM orders o
            WHERE o.shop_id = @ShopId
              AND o.created_at >= @PrevStart
              AND o.created_at < @PrevEnd
              AND o.status_id != @CancelledStatusId";

        var previousRevenue = await _connection.ExecuteScalarAsync<decimal>(prevRevenueSql, parameters, commandTimeout: 5);

        // EN: Calculate growth percentage / VI: Tinh phan tram tang truong
        var growthPercentage = previousRevenue > 0
            ? Math.Round((currentAgg.TotalRevenue - previousRevenue) / previousRevenue * 100, 2)
            : currentAgg.TotalRevenue > 0 ? 100m : 0m;

        // EN: 3. Revenue trends grouped by period / VI: 3. Xu huong doanh thu nhom theo ky
        var truncExpr = request.Period.ToLowerInvariant() switch
        {
            "weekly" => "DATE_TRUNC('week', o.created_at)",
            "monthly" => "DATE_TRUNC('month', o.created_at)",
            _ => "DATE_TRUNC('day', o.created_at)"
        };

        var trendsSql = $@"
            SELECT
                {truncExpr} AS Date,
                COALESCE(SUM(o.total_amount), 0) AS Revenue,
                COUNT(*)::int AS OrderCount
            FROM orders o
            WHERE o.shop_id = @ShopId
              AND o.created_at >= @StartDate
              AND o.created_at < @EndDate
              AND o.status_id != @CancelledStatusId
            GROUP BY {truncExpr}
            ORDER BY Date";

        var trends = (await _connection.QueryAsync<RevenueTrendDto>(trendsSql, parameters, commandTimeout: 5)).AsList();

        // EN: 4. Payment method breakdown / VI: 4. Phan tich phuong thuc thanh toan
        var paymentSql = @"
            SELECT
                COALESCE(o.payment_method, 'unknown') AS Method,
                COALESCE(SUM(o.total_amount), 0) AS Amount,
                COUNT(*)::int AS Count,
                0 AS Percentage
            FROM orders o
            WHERE o.shop_id = @ShopId
              AND o.created_at >= @StartDate
              AND o.created_at < @EndDate
              AND o.status_id != @CancelledStatusId
            GROUP BY o.payment_method
            ORDER BY Amount DESC";

        var paymentMethods = (await _connection.QueryAsync<PaymentMethodStatsDto>(paymentSql, parameters, commandTimeout: 5)).AsList();

        // EN: Calculate percentage for each payment method / VI: Tinh phan tram cho moi phuong thuc thanh toan
        var totalPaymentAmount = paymentMethods.Sum(p => p.Amount);
        foreach (var pm in paymentMethods)
        {
            pm.Percentage = totalPaymentAmount > 0
                ? Math.Round(pm.Amount / totalPaymentAmount * 100, 2)
                : 0;
        }

        // EN: 5. Top 10 products by revenue / VI: 5. Top 10 san pham theo doanh thu
        var topProductsSql = @"
            SELECT
                oi.product_name AS Name,
                SUM(oi.quantity)::int AS QuantitySold,
                SUM(oi.unit_price * oi.quantity) AS Revenue
            FROM order_items oi
            INNER JOIN orders o ON o.id = oi.order_id
            WHERE o.shop_id = @ShopId
              AND o.created_at >= @StartDate
              AND o.created_at < @EndDate
              AND o.status_id != @CancelledStatusId
            GROUP BY oi.product_name
            ORDER BY Revenue DESC
            LIMIT 10";

        var topProducts = (await _connection.QueryAsync<TopProductAnalyticsDto>(topProductsSql, parameters, commandTimeout: 5)).AsList();

        // EN: 6. Revenue by business vertical / VI: 6. Doanh thu theo nganh kinh doanh
        var verticalSql = @"
            SELECT
                COALESCE(o.vertical, 'other') AS Vertical,
                COALESCE(SUM(o.total_amount), 0) AS Revenue,
                COUNT(*)::int AS OrderCount
            FROM orders o
            WHERE o.shop_id = @ShopId
              AND o.created_at >= @StartDate
              AND o.created_at < @EndDate
              AND o.status_id != @CancelledStatusId
            GROUP BY o.vertical
            ORDER BY Revenue DESC";

        var verticalBreakdown = (await _connection.QueryAsync<VerticalRevenueDto>(verticalSql, parameters, commandTimeout: 5)).AsList();

        return new RevenueAnalyticsDto(
            currentAgg.TotalRevenue,
            previousRevenue,
            growthPercentage,
            (int)currentAgg.TotalOrders,
            currentAgg.AverageOrderValue,
            trends,
            paymentMethods,
            topProducts,
            verticalBreakdown);
    }

    private record CurrentAggregateRow
    {
        public decimal TotalRevenue { get; init; }
        public long TotalOrders { get; init; }
        public decimal AverageOrderValue { get; init; }
    }
}
