// EN: Query to get revenue report grouped by period.
// VI: Query lấy báo cáo doanh thu theo kỳ.

using System.Data;
using Dapper;
using MediatR;

namespace OrderService.API.Application.Queries;

/// <summary>
/// EN: Query for revenue report by period.
/// VI: Query cho báo cáo doanh thu theo kỳ.
/// </summary>
public record GetRevenueReportQuery(
    string Period,
    Guid ShopId,
    DateTime? FromDate = null,
    DateTime? ToDate = null
) : IRequest<RevenueReportDto>;

/// <summary>
/// EN: Revenue report with period breakdown.
/// VI: Báo cáo doanh thu với phân tích theo kỳ.
/// </summary>
public record RevenueReportDto(
    string Period,
    Guid ShopId,
    decimal TotalRevenue,
    long TotalOrders,
    List<RevenuePeriodDto> Data
);

/// <summary>
/// EN: Revenue data for a single period (class for Dapper compatibility).
/// VI: Dữ liệu doanh thu cho một kỳ (class cho tương thích Dapper).
/// </summary>
public class RevenuePeriodDto
{
    public DateTime PeriodStart { get; set; }
    public decimal Revenue { get; set; }
    public long OrderCount { get; set; }
    public decimal AvgOrderValue { get; set; }
}

/// <summary>
/// EN: Handler for GetRevenueReportQuery.
/// VI: Handler cho GetRevenueReportQuery.
/// </summary>
public class GetRevenueReportQueryHandler : IRequestHandler<GetRevenueReportQuery, RevenueReportDto>
{
    private readonly IDbConnection _connection;

    public GetRevenueReportQueryHandler(IDbConnection connection)
    {
        _connection = connection ?? throw new ArgumentNullException(nameof(connection));
    }

    public async Task<RevenueReportDto> Handle(GetRevenueReportQuery request, CancellationToken cancellationToken)
    {
        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)" // daily default
        };

        var (whereClause, parameters) = BuildWhereClause(request.ShopId, request.FromDate, request.ToDate);

        // EN: Revenue grouped by period / VI: Doanh thu nhóm theo kỳ
        var sql = $@"
            SELECT
                {truncExpr} AS PeriodStart,
                COALESCE(SUM(o.total_amount), 0) AS Revenue,
                COUNT(*) AS OrderCount,
                COALESCE(AVG(o.total_amount), 0) AS AvgOrderValue
            FROM orders o
            {whereClause}
            GROUP BY {truncExpr}
            ORDER BY PeriodStart";

        var data = (await _connection.QueryAsync<RevenuePeriodDto>(sql, parameters, commandTimeout: 5)).AsList();

        var totalRevenue = data.Sum(d => d.Revenue);
        var totalOrders = data.Sum(d => d.OrderCount);

        return new RevenueReportDto(
            request.Period.ToLowerInvariant(),
            request.ShopId,
            totalRevenue,
            totalOrders,
            data);
    }

    private static (string whereClause, DynamicParameters parameters) BuildWhereClause(
        Guid shopId,
        DateTime? fromDate,
        DateTime? toDate)
    {
        var conditions = new List<string> { "o.shop_id = @ShopId" };
        var parameters = new DynamicParameters();
        parameters.Add("ShopId", shopId);

        if (fromDate.HasValue)
        {
            conditions.Add("o.created_at >= @FromDate");
            parameters.Add("FromDate", fromDate.Value);
        }

        if (toDate.HasValue)
        {
            conditions.Add("o.created_at <= @ToDate");
            parameters.Add("ToDate", toDate.Value);
        }

        return ("WHERE " + string.Join(" AND ", conditions), parameters);
    }
}
