// EN: Query to export orders for admin.
// VI: Query xuất đơn hàng cho admin.

using System.Data;
using Dapper;
using MediatR;

namespace OrderService.API.Application.Queries;

/// <summary>
/// EN: Query to export orders in CSV-friendly structure.
/// VI: Query để xuất đơn hàng theo cấu trúc phù hợp CSV.
/// </summary>
public record ExportOrdersQuery(
    Guid? ShopId,
    DateTime? FromDate,
    DateTime? ToDate
) : IRequest<ExportOrdersResult>;

/// <summary>
/// EN: Export payload result.
/// VI: Kết quả payload export.
/// </summary>
public record ExportOrdersResult(string FileName, string CsvContent);

/// <summary>
/// EN: Handler for ExportOrdersQuery.
/// VI: Handler cho ExportOrdersQuery.
/// </summary>
public class ExportOrdersQueryHandler : IRequestHandler<ExportOrdersQuery, ExportOrdersResult>
{
    private readonly IDbConnection _connection;

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

    public async Task<ExportOrdersResult> Handle(
        ExportOrdersQuery request,
        CancellationToken cancellationToken)
    {
        var (whereClause, parameters) = BuildWhereClause(request.ShopId, request.FromDate, request.ToDate);

        var sql = $@"
            SELECT
                o.id AS Id,
                o.shop_id AS ShopId,
                o.customer_id AS CustomerId,
                os.name AS Status,
                o.total_amount AS TotalAmount,
                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";

        var rows = await _connection.QueryAsync<ExportOrderRow>(sql, parameters, commandTimeout: 5);

        var csvLines = new List<string>
        {
            "OrderId,ShopId,CustomerId,Status,TotalAmount,CreatedAt",
        };

        csvLines.AddRange(rows.Select(row =>
            $"{row.Id},{row.ShopId},{row.CustomerId},{EscapeCsv(row.Status)},{row.TotalAmount},{row.CreatedAt:O}"));

        var fileName = $"orders_{DateTime.UtcNow:yyyyMMdd_HHmmss}.csv";
        var csvContent = string.Join(Environment.NewLine, csvLines);
        return new ExportOrdersResult(fileName, csvContent);
    }

    private static (string whereClause, DynamicParameters parameters) BuildWhereClause(
        Guid? shopId,
        DateTime? fromDate,
        DateTime? toDate)
    {
        var conditions = new List<string>();
        var parameters = new DynamicParameters();

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

        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);
        }

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

        return (whereClause, parameters);
    }

    private static string EscapeCsv(string value)
    {
        if (string.IsNullOrEmpty(value))
        {
            return string.Empty;
        }

        if (value.Contains(',') || value.Contains('"') || value.Contains('\n'))
        {
            return $"\"{value.Replace("\"", "\"\"")}\"";
        }

        return value;
    }

    private record ExportOrderRow(
        Guid Id,
        Guid ShopId,
        Guid? CustomerId,
        string Status,
        decimal TotalAmount,
        DateTime CreatedAt);
}
