// EN: Query to list all orders for admin with advanced filtering.
// VI: Query liệt kê tất cả đơn hàng cho admin với bộ lọc nâng cao.

using System.Data;
using Dapper;
using MediatR;
using OrderService.API.Application.DTOs;

namespace OrderService.API.Application.Queries;

/// <summary>
/// EN: Query to list all orders for admins.
/// VI: Query để liệt kê tất cả đơn hàng cho admin.
/// </summary>
public record AdminListOrdersQuery(
    Guid? ShopId,
    Guid? CustomerId,
    string? Status,
    DateTime? FromDate,
    DateTime? ToDate,
    decimal? MinAmount,
    decimal? MaxAmount,
    int Page = 1,
    int PageSize = 20
) : IRequest<PagedResult<OrderSummaryDto>>;

/// <summary>
/// EN: Handler for AdminListOrdersQuery using Dapper.
/// VI: Handler cho AdminListOrdersQuery dùng Dapper.
/// </summary>
public class AdminListOrdersQueryHandler : IRequestHandler<AdminListOrdersQuery, PagedResult<OrderSummaryDto>>
{
    private readonly IDbConnection _connection;

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

    public async Task<PagedResult<OrderSummaryDto>> Handle(
        AdminListOrdersQuery request,
        CancellationToken cancellationToken)
    {
        var (whereClause, parameters) = BuildWhereClause(request);

        var countSql = $@"
            SELECT COUNT(*)
            FROM orders o
            INNER JOIN order_statuses os ON o.status_id = os.id
            {whereClause}";

        var totalCount = await _connection.ExecuteScalarAsync<int>(countSql, parameters, commandTimeout: 5);

        var listSql = $@"
            SELECT
                o.id AS Id,
                o.shop_id AS ShopId,
                o.customer_id AS CustomerId,
                os.name AS Status,
                o.total_amount AS TotalAmount,
                (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) AS ItemCount,
                o.created_at AS CreatedAt
            FROM orders o
            INNER JOIN order_statuses os ON o.status_id = os.id
            {whereClause}
            ORDER BY o.created_at DESC
            LIMIT @PageSize OFFSET @Offset";

        parameters.Add("PageSize", request.PageSize);
        parameters.Add("Offset", (request.Page - 1) * request.PageSize);

        var orders = await _connection.QueryAsync<OrderSummaryDto>(listSql, parameters, commandTimeout: 5);

        return new PagedResult<OrderSummaryDto>(
            orders.ToList(),
            totalCount,
            request.PageSize,
            request.Page);
    }

    private static (string whereClause, DynamicParameters parameters) BuildWhereClause(AdminListOrdersQuery request)
    {
        var conditions = new List<string>();
        var parameters = new DynamicParameters();

        if (request.ShopId.HasValue)
        {
            conditions.Add("o.shop_id = @ShopId");
            parameters.Add("ShopId", request.ShopId.Value);
        }

        if (request.CustomerId.HasValue)
        {
            conditions.Add("o.customer_id = @CustomerId");
            parameters.Add("CustomerId", request.CustomerId.Value);
        }

        if (!string.IsNullOrWhiteSpace(request.Status))
        {
            conditions.Add("os.name = @Status");
            parameters.Add("Status", request.Status);
        }

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

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

        if (request.MinAmount.HasValue)
        {
            conditions.Add("o.total_amount >= @MinAmount");
            parameters.Add("MinAmount", request.MinAmount.Value);
        }

        if (request.MaxAmount.HasValue)
        {
            conditions.Add("o.total_amount <= @MaxAmount");
            parameters.Add("MaxAmount", request.MaxAmount.Value);
        }

        var whereClause = conditions.Count == 0
            ? string.Empty
            : "WHERE " + string.Join(" AND ", conditions);

        return (whereClause, parameters);
    }
}
