// EN: Query to get POS dashboard statistics for today.
// VI: Query lấy thống kê dashboard POS cho hôm nay.

using System.Data;
using Dapper;
using MediatR;

namespace OrderService.API.Application.Queries;

/// <summary>
/// EN: Query for POS dashboard data.
/// VI: Query cho dữ liệu dashboard POS.
/// </summary>
public record GetPosDashboardQuery(Guid ShopId, string? Period = "today") : IRequest<PosDashboardDto>;

/// <summary>
/// EN: POS dashboard DTO with today's stats.
/// VI: DTO dashboard POS với thống kê hôm nay.
/// </summary>
public record PosDashboardDto(
    decimal Revenue,
    long OrderCount,
    long ItemsSold,
    decimal AvgOrderValue,
    List<PopularItemDto> PopularItems,
    List<PaymentBreakdownDto> PaymentBreakdown,
    List<HourlyRevenueDto> HourlyRevenue,
    List<RecentOrderDto> RecentOrders
);

/// <summary>
/// EN: Popular item sold today.
/// VI: Sản phẩm bán chạy hôm nay.
/// </summary>
public record PopularItemDto
{
    public Guid ProductId { get; init; }
    public string ProductName { get; init; } = string.Empty;
    public long QuantitySold { get; init; }
    public decimal Revenue { get; init; }
}

/// <summary>
/// EN: Payment breakdown by order status.
/// VI: Phân tích thanh toán theo trạng thái đơn hàng.
/// </summary>
public record PaymentBreakdownDto
{
    public string Method { get; init; } = string.Empty;
    public long Count { get; init; }
    public decimal Amount { get; init; }
}

/// <summary>
/// EN: Revenue by hour of day.
/// VI: Doanh thu theo giờ trong ngày.
/// </summary>
public record HourlyRevenueDto
{
    public double Hour { get; init; }
    public decimal Revenue { get; init; }
    public long OrderCount { get; init; }
}

/// <summary>
/// EN: Recent order summary.
/// VI: Tóm tắt đơn hàng gần đây.
/// </summary>
public record RecentOrderDto
{
    public Guid Id { get; init; }
    public decimal TotalAmount { get; init; }
    public string Status { get; init; } = string.Empty;
    public long ItemCount { get; init; }
    public DateTime CreatedAt { get; init; }
}

/// <summary>
/// EN: Handler for GetPosDashboardQuery.
/// VI: Handler cho GetPosDashboardQuery.
/// </summary>
public class GetPosDashboardQueryHandler : IRequestHandler<GetPosDashboardQuery, PosDashboardDto>
{
    private readonly IDbConnection _connection;

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

    public async Task<PosDashboardDto> Handle(GetPosDashboardQuery request, CancellationToken cancellationToken)
    {
        var now = DateTime.UtcNow;
        var fromDate = request.Period?.ToLower() switch
        {
            "7d" => now.Date.AddDays(-7),
            "30d" => now.Date.AddDays(-30),
            _ => now.Date // "today" or default
        };
        var toDate = now.Date.AddDays(1);
        var parameters = new DynamicParameters();
        parameters.Add("ShopId", request.ShopId);
        parameters.Add("Today", fromDate);
        parameters.Add("Tomorrow", toDate);

        // EN: Aggregate stats for today / VI: Thống kê tổng hợp hôm nay
        var aggregateSql = @"
            SELECT
                COALESCE(SUM(o.total_amount), 0) AS Revenue,
                COUNT(*) AS OrderCount,
                COALESCE(SUM(oi.total_qty), 0) AS ItemsSold,
                COALESCE(AVG(o.total_amount), 0) AS AvgOrderValue
            FROM orders o
            LEFT JOIN (
                SELECT order_id, SUM(quantity) AS total_qty
                FROM order_items
                GROUP BY order_id
            ) oi ON oi.order_id = o.id
            WHERE o.shop_id = @ShopId
              AND o.created_at >= @Today
              AND o.created_at < @Tomorrow";

        var aggregate = await _connection.QuerySingleAsync<AggregateRow>(aggregateSql, parameters);

        // EN: Top 5 popular items today / VI: Top 5 sản phẩm bán chạy hôm nay
        var popularSql = @"
            SELECT
                oi.product_id AS ProductId,
                oi.product_name AS ProductName,
                SUM(oi.quantity) 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 >= @Today
              AND o.created_at < @Tomorrow
            GROUP BY oi.product_id, oi.product_name
            ORDER BY QuantitySold DESC
            LIMIT 5";

        var popularItems = (await _connection.QueryAsync<PopularItemDto>(popularSql, parameters, commandTimeout: 5)).AsList();

        // EN: Payment breakdown by payment method / VI: Phân tích theo hình thức thanh toán
        var paymentSql = @"
            SELECT
                COALESCE(NULLIF(o.payment_method, ''), 'unknown') AS Method,
                COUNT(*) AS Count,
                COALESCE(SUM(o.total_amount), 0) AS Amount
            FROM orders o
            WHERE o.shop_id = @ShopId
              AND o.created_at >= @Today
              AND o.created_at < @Tomorrow
            GROUP BY COALESCE(NULLIF(o.payment_method, ''), 'unknown')";

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

        // EN: Hourly revenue today / VI: Doanh thu theo giờ hôm nay
        var hourlySql = @"
            SELECT
                EXTRACT(HOUR FROM o.created_at) AS Hour,
                COALESCE(SUM(o.total_amount), 0) AS Revenue,
                COUNT(*) AS OrderCount
            FROM orders o
            WHERE o.shop_id = @ShopId
              AND o.created_at >= @Today
              AND o.created_at < @Tomorrow
            GROUP BY EXTRACT(HOUR FROM o.created_at)
            ORDER BY Hour";

        var hourlyRevenue = (await _connection.QueryAsync<HourlyRevenueDto>(hourlySql, parameters, commandTimeout: 5)).AsList();

        // EN: Last 10 recent orders / VI: 10 đơn hàng gần nhất
        var recentSql = @"
            SELECT
                o.id AS Id,
                o.total_amount AS TotalAmount,
                os.name AS Status,
                COUNT(oi.id) AS ItemCount,
                o.created_at AS CreatedAt
            FROM orders o
            INNER JOIN order_statuses os ON o.status_id = os.id
            LEFT JOIN order_items oi ON oi.order_id = o.id
            WHERE o.shop_id = @ShopId
            GROUP BY o.id, o.total_amount, os.name, o.created_at
            ORDER BY o.created_at DESC
            LIMIT 10";

        var recentParams = new DynamicParameters();
        recentParams.Add("ShopId", request.ShopId);
        var recentOrders = (await _connection.QueryAsync<RecentOrderDto>(recentSql, recentParams, commandTimeout: 5)).AsList();

        return new PosDashboardDto(
            aggregate.Revenue,
            aggregate.OrderCount,
            (long)aggregate.ItemsSold,
            aggregate.AvgOrderValue,
            popularItems,
            paymentBreakdown,
            hourlyRevenue,
            recentOrders);
    }

    private record AggregateRow
    {
        public decimal Revenue { get; init; }
        public long OrderCount { get; init; }
        public decimal ItemsSold { get; init; }
        public decimal AvgOrderValue { get; init; }
    }
}
