using System.Drawing;
using Dapper;
using Lada.Framework.DTO;
using Lada.Framework.DTO.SmartTable;
using Lada.Framework.Infrastructures.Helpers;
using Lada.Framework.Infrastructures.Services;
using Ladaer.BackEnd.Infrastructures.DTO.Reports.Items.FuelCards;
using Ladaer.BackEnd.Infrastructures.DTO.Results.FuelCards;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Data;

namespace Ladaer.BackEnd.Infrastructures.Services.Report.FuelCards
{
    /// <summary>
    /// Interface cho dịch vụ xuất Excel báo cáo đổ dầu
    /// </summary>
    public interface IFuelRefillReportExcelService
    {
        /// <summary>
        /// Xuất Excel báo cáo đổ dầu theo lái xe (nhóm theo ngày)
        /// </summary>
        /// <param name="param">Tham số tìm kiếm và sắp xếp</param>
        /// <returns>Kết quả xuất file</returns>
        Task<FuelRefillReportExcelResult> ExportByDriverAsync(SmartTableParam param);
    }

    /// <summary>
    /// Dịch vụ xuất Excel báo cáo đổ dầu
    /// </summary>
    public class FuelRefillReportExcelService : IFuelRefillReportExcelService
    {
        private readonly IDbConnection _dbConnection;
        private readonly IExcelFileService _excelFileService;

        public FuelRefillReportExcelService(
            IDbConnection dbConnection,
            IExcelFileService excelFileService)
        {
            _dbConnection = dbConnection;
            _excelFileService = excelFileService;
        }

        /// <summary>
        /// Xuất Excel báo cáo đổ dầu theo lái xe (nhóm theo ngày)
        /// </summary>
        public async Task<FuelRefillReportExcelResult> ExportByDriverAsync(SmartTableParam param)
        {
            var result = new FuelRefillReportExcelResult
            {
                ProcessStartTime = DateTimeOffset.Now
            };

            try
            {
                // Lấy dữ liệu chi tiết theo ngày, lái xe và xe
                var detailItems = await GetReportDataAsync(param);

                // Tạo file Excel
                var excelPackage = CreateExcelPackage(detailItems);

                // Lưu file sử dụng ExcelFileService
                var saveResult = await _excelFileService.SaveExcelFileAsync(
                    excelPackage,
                    "BaoCaoDoDau_TheoLaiXe",
                    "fuel-refill-reports",
                    null);

                if (saveResult.Result == Result.Success)
                {
                    result.FilePath = saveResult.FilePath;
                    result.FileName = saveResult.SavedFileName;
                    result.DownloadUrl = saveResult.FileUrl;
                    result.FileSize = saveResult.FileSize;
                    result.TotalRecords = detailItems.Count;
                    result.Result = Result.Success;
                    result.Message = "Xuất file Excel thành công";
                }
                else
                {
                    result.Result = saveResult.Result;
                    result.Message = saveResult.Message;
                }
            }
            catch (Exception ex)
            {
                result.Result = Result.SystemError;
                result.Message = $"Lỗi khi xuất file Excel: {ex.Message}";
            }

            result.ProcessEndTime = DateTimeOffset.Now;
            return result;
        }

        #region Private Methods

        /// <summary>
        /// Lấy dữ liệu chi tiết báo cáo đổ dầu theo ngày, lái xe và xe
        /// </summary>
        private async Task<List<DateDriverTruckReportRow>> GetReportDataAsync(SmartTableParam param)
        {
            const string storeName = "sp_GetFuelRefillReportByDate";

            // Xử lý tham số từ SmartTableParam
            DateTimeOffset fromDate = DateTimeOffset.Now.AddDays(-30).StartOfDay();
            DateTimeOffset toDate = DateTimeOffset.Now.EndOfDay();
            string? driverIds = null;
            string? truckIds = null;

            if (param?.Search?.PredicateObject != null)
            {
                dynamic search = param.Search.PredicateObject;

                // Xử lý khoảng thời gian
                if (search.StartDate != null && search.EndDate != null)
                {
                    DateTimeOffset start = search.StartDate;
                    DateTimeOffset end = search.EndDate;
                    fromDate = start.ToLocalTime().StartOfDay();
                    toDate = end.ToLocalTime().EndOfDay();
                }

                // Xử lý DriverIds (mảng ID lái xe)
                if (search.DriverIds != null)
                {
                    var driverIdList = new List<int>();
                    if (search.DriverIds is Newtonsoft.Json.Linq.JArray jArray)
                    {
                        foreach (var item in jArray)
                        {
                            if (int.TryParse(item.ToString(), out int id))
                            {
                                driverIdList.Add(id);
                            }
                        }
                    }
                    else if (search.DriverIds is System.Collections.IEnumerable enumerable)
                    {
                        foreach (var item in enumerable)
                        {
                            if (int.TryParse(item.ToString(), out int id))
                            {
                                driverIdList.Add(id);
                            }
                        }
                    }

                    if (driverIdList.Count > 0)
                    {
                        driverIds = string.Join(",", driverIdList);
                    }
                }

                // Xử lý TruckIds (mảng ID xe)
                if (search.TruckIds != null)
                {
                    var truckIdList = new List<int>();
                    if (search.TruckIds is Newtonsoft.Json.Linq.JArray jArray)
                    {
                        foreach (var item in jArray)
                        {
                            if (int.TryParse(item.ToString(), out int id))
                            {
                                truckIdList.Add(id);
                            }
                        }
                    }
                    else if (search.TruckIds is System.Collections.IEnumerable enumerable)
                    {
                        foreach (var item in enumerable)
                        {
                            if (int.TryParse(item.ToString(), out int id))
                            {
                                truckIdList.Add(id);
                            }
                        }
                    }

                    if (truckIdList.Count > 0)
                    {
                        truckIds = string.Join(",", truckIdList);
                    }
                }
            }

            var parameters = new DynamicParameters();
            parameters.Add("StartDate", fromDate.Date);
            parameters.Add("EndDate", toDate.Date);
            parameters.Add("DriverIds", driverIds);
            parameters.Add("TruckIds", truckIds);

            // Gọi stored procedure để lấy chi tiết theo ngày, lái xe và xe
            var items = await _dbConnection.QueryAsync<DateDriverTruckReportRow>(
                storeName,
                parameters,
                commandType: CommandType.StoredProcedure);

            return items.ToList();
        }

        /// <summary>
        /// Tạo ExcelPackage với nhóm theo ngày
        /// </summary>
        private ExcelPackage CreateExcelPackage(List<DateDriverTruckReportRow> items)
        {
            var package = new ExcelPackage();
            var worksheet = package.Workbook.Worksheets.Add("Báo cáo đổ dầu theo lái xe");

            // Thiết lập header
            SetupHeaders(worksheet);

            // Điền dữ liệu (nhóm theo ngày)
            int lastDataRow = FillDataGroupedByDate(worksheet, items);

            // Định dạng worksheet
            FormatWorksheet(worksheet, lastDataRow);

            return package;
        }

        /// <summary>
        /// Thiết lập header
        /// </summary>
        private void SetupHeaders(ExcelWorksheet worksheet)
        {
            var headers = new[]
            {
                "STT",
                "Ngày",
                "Lái xe",
                "Biển số xe",
                "Tên xe",
                "Số km chạy",
                "Số lít thực tế",
                "Số tiền thực tế",
                "Số lít khoán",
                "Số tiền khoán",
                "Thu nhập dầu"
            };

            for (int i = 0; i < headers.Length; i++)
            {
                worksheet.Cells[1, i + 1].Value = headers[i];
            }
        }

        /// <summary>
        /// Điền dữ liệu nhóm theo ngày
        /// </summary>
        private int FillDataGroupedByDate(ExcelWorksheet worksheet, List<DateDriverTruckReportRow> items)
        {
            int row = 2;
            int stt = 1;
            DateTime? lastDate = null;

            // Nhóm theo ngày
            var groupedByDate = items
                .OrderBy(x => x.RefillDate)
                .ThenBy(x => x.DriverFullName)
                .ThenBy(x => x.TruckPlate)
                .GroupBy(x => x.RefillDate);

            foreach (var dateGroup in groupedByDate)
            {
                // Header cho nhóm ngày
                worksheet.Cells[row, 1].Value = "";
                worksheet.Cells[row, 2].Value = dateGroup.Key.ToString("dd/MM/yyyy");
                worksheet.Cells[row, 2].Style.Font.Bold = true;
                worksheet.Cells[row, 2, row, 11].Merge = true;
                worksheet.Cells[row, 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
                worksheet.Cells[row, 2].Style.Fill.BackgroundColor.SetColor(Color.LightYellow);
                row++;

                // Chi tiết trong ngày
                foreach (var item in dateGroup)
                {
                    worksheet.Cells[row, 1].Value = stt++;
                    worksheet.Cells[row, 2].Value = ""; // Ngày đã hiển thị ở header nhóm
                    worksheet.Cells[row, 3].Value = item.DriverFullName;
                    worksheet.Cells[row, 4].Value = item.TruckPlate;
                    worksheet.Cells[row, 5].Value = item.TruckName;
                    worksheet.Cells[row, 6].Value = item.TotalDistanceKm;
                    worksheet.Cells[row, 6].Style.Numberformat.Format = "#,##0";
                    worksheet.Cells[row, 7].Value = (double)item.TotalActualLiters;
                    worksheet.Cells[row, 7].Style.Numberformat.Format = "#,##0.00";
                    worksheet.Cells[row, 8].Value = item.TotalActualAmount;
                    worksheet.Cells[row, 8].Style.Numberformat.Format = "#,##0";
                    worksheet.Cells[row, 9].Value = (double)item.TotalQuotaLiters;
                    worksheet.Cells[row, 9].Style.Numberformat.Format = "#,##0.00";
                    worksheet.Cells[row, 10].Value = item.TotalQuotaAmount;
                    worksheet.Cells[row, 10].Style.Numberformat.Format = "#,##0";
                    worksheet.Cells[row, 11].Value = item.TotalFuelIncome;
                    worksheet.Cells[row, 11].Style.Numberformat.Format = "#,##0";

                    // Đánh màu thu nhập dầu
                    if (item.TotalFuelIncome > 0)
                    {
                        worksheet.Cells[row, 11].Style.Font.Color.SetColor(Color.Green);
                    }
                    else if (item.TotalFuelIncome < 0)
                    {
                        worksheet.Cells[row, 11].Style.Font.Color.SetColor(Color.Red);
                    }

                    row++;
                }

                // Subtotal cho ngày
                var dateItems = dateGroup.ToList();
                worksheet.Cells[row, 1].Value = "";
                worksheet.Cells[row, 2].Value = $"Cộng ngày {dateGroup.Key:dd/MM}";
                worksheet.Cells[row, 2].Style.Font.Bold = true;
                worksheet.Cells[row, 2].Style.Font.Italic = true;
                worksheet.Cells[row, 3].Value = "";
                worksheet.Cells[row, 4].Value = "";
                worksheet.Cells[row, 5].Value = "";
                worksheet.Cells[row, 6].Value = dateItems.Sum(x => x.TotalDistanceKm);
                worksheet.Cells[row, 6].Style.Numberformat.Format = "#,##0";
                worksheet.Cells[row, 6].Style.Font.Bold = true;
                worksheet.Cells[row, 7].Value = (double)dateItems.Sum(x => x.TotalActualLiters);
                worksheet.Cells[row, 7].Style.Numberformat.Format = "#,##0.00";
                worksheet.Cells[row, 7].Style.Font.Bold = true;
                worksheet.Cells[row, 8].Value = dateItems.Sum(x => x.TotalActualAmount);
                worksheet.Cells[row, 8].Style.Numberformat.Format = "#,##0";
                worksheet.Cells[row, 8].Style.Font.Bold = true;
                worksheet.Cells[row, 9].Value = (double)dateItems.Sum(x => x.TotalQuotaLiters);
                worksheet.Cells[row, 9].Style.Numberformat.Format = "#,##0.00";
                worksheet.Cells[row, 9].Style.Font.Bold = true;
                worksheet.Cells[row, 10].Value = dateItems.Sum(x => x.TotalQuotaAmount);
                worksheet.Cells[row, 10].Style.Numberformat.Format = "#,##0";
                worksheet.Cells[row, 10].Style.Font.Bold = true;
                worksheet.Cells[row, 11].Value = dateItems.Sum(x => x.TotalFuelIncome);
                worksheet.Cells[row, 11].Style.Numberformat.Format = "#,##0";
                worksheet.Cells[row, 11].Style.Font.Bold = true;

                var dateIncome = dateItems.Sum(x => x.TotalFuelIncome);
                if (dateIncome > 0)
                {
                    worksheet.Cells[row, 11].Style.Font.Color.SetColor(Color.Green);
                }
                else if (dateIncome < 0)
                {
                    worksheet.Cells[row, 11].Style.Font.Color.SetColor(Color.Red);
                }

                row++;
            }

            // Tổng cộng toàn bộ
            if (items.Count > 0)
            {
                row++; // Thêm dòng trống
                worksheet.Cells[row, 1].Value = "";
                worksheet.Cells[row, 2].Value = "TỔNG CỘNG";
                worksheet.Cells[row, 2].Style.Font.Bold = true;
                worksheet.Cells[row, 2].Style.Font.Size = 12;
                worksheet.Cells[row, 3].Value = "";
                worksheet.Cells[row, 4].Value = "";
                worksheet.Cells[row, 5].Value = "";
                worksheet.Cells[row, 6].Value = items.Sum(x => x.TotalDistanceKm);
                worksheet.Cells[row, 6].Style.Numberformat.Format = "#,##0";
                worksheet.Cells[row, 6].Style.Font.Bold = true;
                worksheet.Cells[row, 7].Value = (double)items.Sum(x => x.TotalActualLiters);
                worksheet.Cells[row, 7].Style.Numberformat.Format = "#,##0.00";
                worksheet.Cells[row, 7].Style.Font.Bold = true;
                worksheet.Cells[row, 8].Value = items.Sum(x => x.TotalActualAmount);
                worksheet.Cells[row, 8].Style.Numberformat.Format = "#,##0";
                worksheet.Cells[row, 8].Style.Font.Bold = true;
                worksheet.Cells[row, 9].Value = (double)items.Sum(x => x.TotalQuotaLiters);
                worksheet.Cells[row, 9].Style.Numberformat.Format = "#,##0.00";
                worksheet.Cells[row, 9].Style.Font.Bold = true;
                worksheet.Cells[row, 10].Value = items.Sum(x => x.TotalQuotaAmount);
                worksheet.Cells[row, 10].Style.Numberformat.Format = "#,##0";
                worksheet.Cells[row, 10].Style.Font.Bold = true;
                worksheet.Cells[row, 11].Value = items.Sum(x => x.TotalFuelIncome);
                worksheet.Cells[row, 11].Style.Numberformat.Format = "#,##0";
                worksheet.Cells[row, 11].Style.Font.Bold = true;

                var totalIncome = items.Sum(x => x.TotalFuelIncome);
                if (totalIncome > 0)
                {
                    worksheet.Cells[row, 11].Style.Font.Color.SetColor(Color.Green);
                }
                else if (totalIncome < 0)
                {
                    worksheet.Cells[row, 11].Style.Font.Color.SetColor(Color.Red);
                }
            }

            return row;
        }

        /// <summary>
        /// Định dạng worksheet
        /// </summary>
        private void FormatWorksheet(ExcelWorksheet worksheet, int lastDataRow)
        {
            int columnCount = 11;

            // Header formatting
            var headerRange = worksheet.Cells[1, 1, 1, columnCount];
            headerRange.Style.Font.Bold = true;
            headerRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
            headerRange.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
            headerRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            headerRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

            // Auto-fit columns
            worksheet.Cells.AutoFitColumns();

            // Border for all data
            if (lastDataRow > 1)
            {
                var dataRange = worksheet.Cells[1, 1, lastDataRow, columnCount];
                dataRange.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                dataRange.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                dataRange.Style.Border.Right.Style = ExcelBorderStyle.Thin;
                dataRange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            }
        }

        #endregion

        /// <summary>
        /// DTO để map kết quả từ stored procedure theo ngày
        /// </summary>
        private class DateDriverTruckReportRow
        {
            public DateTime RefillDate { get; set; }
            public int DriverId { get; set; }
            public string DriverUserName { get; set; } = string.Empty;
            public string DriverFullName { get; set; } = string.Empty;
            public int TruckId { get; set; }
            public string TruckPlate { get; set; } = string.Empty;
            public string TruckName { get; set; } = string.Empty;
            public int TotalDistanceKm { get; set; }
            public decimal TotalActualLiters { get; set; }
            public decimal TotalActualAmount { get; set; }
            public decimal TotalQuotaLiters { get; set; }
            public decimal TotalQuotaAmount { get; set; }
            public decimal TotalFuelIncome { get; set; }
        }
    }
}
