using Azure.Core;
using ClosedXML.Excel;
using DocumentFormat.OpenXml.Office2016.Excel;
using LSFE.Domain.Contracts;
using LSFE.Domain.Helpers;
using LSFE.Infrastructure.Entities;
using LSFE.Infrastructure.Entities.Maintenance;
using LSFE.Infrastructure.Model;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Identity;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LSFE.Domain.Services
{
    public class ExcelInventoryService
    {
        private readonly IUnitOfWork _unitOfWork;
        private readonly ILogger<ExcelInventoryService> _logger;
        private const int BATCH_SIZE = 100;
        public readonly UserManager<AppsUsers> _userManager;

        public ExcelInventoryService(IUnitOfWork unitOfWork, ILogger<ExcelInventoryService> logger,
            UserManager<AppsUsers> userManager)
        {
            _unitOfWork = unitOfWork;
            _logger = logger;
            _userManager = userManager;
        }
        public async Task<ExcelUploadResponse> ProcessExcelFileAsync(IFormFile file, string userId, string userName)
        {
            var response = new ExcelUploadResponse();

            try
            {
                // Use ArrayPool for better memory management
                var buffer = System.Buffers.ArrayPool<byte>.Shared.Rent((int)file.Length);
                try
                {
                    using var fileStream = file.OpenReadStream();
                    await fileStream.ReadAsync(buffer.AsMemory(0, (int)file.Length));

                    using var stream = new MemoryStream(buffer, 0, (int)file.Length, writable: false);
                    using var workbook = new XLWorkbook(stream);

                    var worksheet = workbook.Worksheets.FirstOrDefault();
                    if (worksheet == null)
                    {
                        response.Success = false;
                        response.Message = "No worksheet found in the Excel file";
                        response.MessCode = 0;
                        return response;
                    }

                    // Validate headers
                    var (isValid, errors) = ExcelTemplateValidator.ValidateHeaders(worksheet);
                    if (!isValid)
                    {
                        response.Success = false;
                        response.Message = "Invalid Excel template. Please use the correct template.";
                        response.Errors = errors;
                        response.MessCode = 0;
                        return response;
                    }

                    var rowCount = worksheet.LastRowUsed()?.RowNumber() ?? 0;
                    response.TotalRows = rowCount - 1;

                    // Calculate current year and month once for performance
                    var currentDate = DateTime.Now;
                    var currentYear = currentDate.Year;
                    var currentMonth = currentDate.Month;

                    // Pre-load existing inventory data to avoid N+1 query problem
                    var existingInventory = await LoadExistingInventoryAsync(worksheet, rowCount);

                    // Pre-load and validate all MedRep names from the Excel file
                    var validMedReps = await ValidateMedRepNamesAsync(worksheet, rowCount, response);
                    if (!validMedReps.Any())
                    {
                        response.Success = false;
                        response.Message = "No valid MedRep users found in the Excel file.";
                        response.MessCode = 0;
                        return response;
                    }

                    // Process in batches
                    var inventoriesToInsert = new List<Inventory>();
                    var summaryDict = new Dictionary<string, InventorySummary>();

                    for (int row = 2; row <= rowCount; row++)
                    {
                        try
                        {
                            var brandName = worksheet.Cell(row, 1).Value.ToString()?.Trim();
                            var year = worksheet.Cell(row, 14).Value.ToString()?.Trim();
                            var medRepName = worksheet.Cell(row, 15).Value.ToString()?.Trim();

                            if (string.IsNullOrWhiteSpace(brandName))
                            {
                                response.SkippedRows++;
                                continue;
                            }

                            // Validate MedRep Name exists in the system
                            if (string.IsNullOrWhiteSpace(medRepName))
                            {
                                response.Errors.Add($"Row {row}: MedRep Name is required.");
                                response.SkippedRows++;
                                continue;
                            }

                            if (!validMedReps.Contains(medRepName.ToLower()))
                            {
                                response.Errors.Add($"Row {row}: Invalid MedRep User Name '{medRepName}'. User does not exist in the system.");
                                response.SkippedRows++;
                                continue;
                            }

                            if (!int.TryParse(year, out int cycleYear))
                            {
                                response.Errors.Add($"Row {row}: Invalid year '{year}'");
                                response.SkippedRows++;
                                continue;
                            }

                            // Validate year is not in the past
                            if (cycleYear < currentYear)
                            {
                                response.Errors.Add($"Row {row}: Year {cycleYear} is in the past. Only current month onwards is allowed.");
                                response.SkippedRows++;
                                continue;
                            }

                            // Process each month
                            for (byte month = 1; month <= 12; month++)
                            {
                                var cellValue = worksheet.Cell(row, month + 1).Value.ToString()?.Trim();

                                if (!int.TryParse(cellValue, out int qty) || qty <= 0)
                                {
                                    continue;
                                }

                                // Validate date is not in the past (current month or future only)
                                if (cycleYear == currentYear && month < currentMonth)
                                {
                                    _logger.LogDebug($"Row {row}, Month {month}: Skipping past date - {month}/{cycleYear}");
                                    response.Errors.Add($"Row {row}, Month {month}: Date {month}/{cycleYear} is in the past. Only {currentMonth}/{currentYear} onwards is allowed.");
                                    response.SkippedRows++;
                                    continue;
                                }

                                // Check if record exists using in-memory lookup (much faster)
                                var key = $"{brandName}|{month}|{cycleYear}|{medRepName}";
                                if (existingInventory.Contains(key))
                                {
                                    _logger.LogDebug($"Row {row}, Month {month}: Skipping duplicate - {key}");
                                    continue; // Skip duplicates
                                }

                                // Also check if we already added this in current batch
                                if (inventoriesToInsert.Any(x =>
                                    x.Description == brandName &&
                                    x.CycleMonth == month &&
                                    x.CycleYear == cycleYear &&
                                    x.MedRepName == medRepName))
                                {
                                    _logger.LogDebug($"Row {row}, Month {month}: Skipping - already in current batch");
                                    continue;
                                }

                                var inventory = new Inventory
                                {
                                    InventoryId = Guid.NewGuid(),
                                    UserId = userId,
                                    Description = brandName,
                                    QtyIn = qty,
                                    QtyOut = 0,
                                    QtyBalance = qty,
                                    CycleMonth = month,
                                    CycleYear = cycleYear,
                                    MedRepName = medRepName,
                                    CreatedBy = userName,
                                    CreatedDate = DateTime.Now,
                                    IsActive = true
                                };

                                inventoriesToInsert.Add(inventory);
                                response.ProcessedRows++;

                                // Update summary
                                var summaryKey = $"{brandName}|{medRepName}";
                                if (!summaryDict.TryGetValue(summaryKey, out var summary))
                                {
                                    summary = new InventorySummary
                                    {
                                        Brand = brandName,
                                        TotalQty = 0,
                                        MedRepName = medRepName
                                    };
                                    summaryDict[summaryKey] = summary;
                                }
                                summary.TotalQty += qty;
                            }

                            // Batch insert to reduce database round trips
                            if (inventoriesToInsert.Count >= BATCH_SIZE)
                            {
                                await _unitOfWork.Inventory.AddRangeAsync(inventoriesToInsert);
                                await _unitOfWork.SaveChangesAsync();
                                inventoriesToInsert.Clear();
                            }
                        }
                        catch (Exception ex)
                        {
                            response.Errors.Add($"Row {row}: {ex.Message}");
                            response.SkippedRows++;
                            _logger.LogError(ex, $"Error processing row {row}");
                        }
                    }

                    // Insert remaining items
                    if (inventoriesToInsert.Any())
                    {
                        await _unitOfWork.Inventory.AddRangeAsync(inventoriesToInsert);
                        await _unitOfWork.SaveChangesAsync();
                    }

                    response.InsertedItems = summaryDict.Values.ToList();
                    response.Success = true;
                    response.MessCode = 1;
                    response.Message = $"Successfully processed {response.ProcessedRows} inventory records. Skipped {response.SkippedRows} duplicates/invalid rows.";

                    _logger.LogInformation($"Excel processing completed: {response.ProcessedRows} processed, {response.SkippedRows} skipped, {response.TotalRows} total rows");
                }
                finally
                {
                    // Return buffer to pool
                    System.Buffers.ArrayPool<byte>.Shared.Return(buffer);
                }
            }
            catch (Exception ex)
            {
                response.Success = false;
                response.Message = $"Error processing Excel file: {ex.Message}";
                response.MessCode = 0;
                response.Errors.Add(ex.ToString());
                _logger.LogError(ex, "Error processing Excel file");
            }

            return response;
        }

        /// <summary>
        /// Validates all MedRep names from the Excel file against the UserManager
        /// Returns a HashSet of valid (lowercase) usernames for O(1) lookup during processing
        /// </summary>
        private async Task<HashSet<string>> ValidateMedRepNamesAsync(IXLWorksheet worksheet, int rowCount, ExcelUploadResponse response)
        {
            var validMedReps = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
            var uniqueMedRepNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);

            // Collect unique MedRep names from Excel
            for (int row = 2; row <= rowCount; row++)
            {
                var medRepName = worksheet.Cell(row, 15).Value.ToString()?.Trim();
                if (!string.IsNullOrWhiteSpace(medRepName))
                {
                    uniqueMedRepNames.Add(medRepName);
                }
            }

            // Validate each unique MedRep name against UserManager
            foreach (var medRepName in uniqueMedRepNames)
            {
                var user = await _userManager.FindByNameAsync(medRepName.ToLower());
                if (user != null)
                {
                    validMedReps.Add(medRepName.ToLower());
                    _logger.LogDebug($"MedRep '{medRepName}' validated successfully");
                }
                else
                {
                    _logger.LogWarning($"MedRep '{medRepName}' not found in the system");
                }
            }

            _logger.LogInformation($"Validated {validMedReps.Count} out of {uniqueMedRepNames.Count} unique MedRep names");

            return validMedReps;
        }

        /// <summary>
        /// Pre-loads existing inventory records from database to avoid N+1 queries
        /// This validates against ALL records in the inventory table (from all users)
        /// to prevent duplicate uploads
        /// </summary>
        private async Task<HashSet<string>> LoadExistingInventoryAsync(IXLWorksheet worksheet, int rowCount)
        {
            var brandNames = new HashSet<string>();
            var years = new HashSet<int>();
            var medRepNames = new HashSet<string>();
            var months = new HashSet<byte> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 };

            // Collect unique brands, years, and med rep names from Excel file being uploaded
            for (int row = 2; row <= rowCount; row++)
            {
                var brandName = worksheet.Cell(row, 1).Value.ToString()?.Trim();
                var yearStr = worksheet.Cell(row, 14).Value.ToString()?.Trim();
                var medRepName = worksheet.Cell(row, 15).Value.ToString()?.Trim();

                if (!string.IsNullOrWhiteSpace(brandName))
                {
                    brandNames.Add(brandName);
                }

                if (int.TryParse(yearStr, out int year))
                {
                    years.Add(year);
                }

                if (!string.IsNullOrWhiteSpace(medRepName))
                {
                    medRepNames.Add(medRepName);
                }
            }

            if (!brandNames.Any() || !years.Any())
            {
                return new HashSet<string>();
            }

            // Single query to load ALL matching records from inventory table
            var existingRecords = await _unitOfWork.Inventory
                .FindAllAsync(inv =>
                    brandNames.Contains(inv.Description) &&
                    months.Contains(inv.CycleMonth) &&
                    years.Contains(inv.CycleYear) &&
                    medRepNames.Contains(inv.MedRepName) &&
                    inv.IsActive == true);

            // Create lookup set with composite key: Brand|Month|Year|MedRepName
            var existingKeys = new HashSet<string>(
                existingRecords.Select(inv =>
                    $"{inv.Description}|{inv.CycleMonth}|{inv.CycleYear}|{inv.MedRepName}"));

            _logger.LogInformation($"Loaded {existingKeys.Count} existing inventory records for validation");

            return existingKeys;
        }
    }
}
/*#region
public async Task<ExcelUploadResponse> ProcessExcelFileAsync(IFormFile file, string userId, string userName)
{
    var response = new ExcelUploadResponse();

    try
    {
        // Use ArrayPool for better memory management
        var buffer = System.Buffers.ArrayPool<byte>.Shared.Rent((int)file.Length);
        try
        {
            using var fileStream = file.OpenReadStream();
            await fileStream.ReadAsync(buffer.AsMemory(0, (int)file.Length));

            using var stream = new MemoryStream(buffer, 0, (int)file.Length, writable: false);
            using var workbook = new XLWorkbook(stream);

            var worksheet = workbook.Worksheets.FirstOrDefault();
            if (worksheet == null)
            {
                response.Success = false;
                response.Message = "No worksheet found in the Excel file";
                response.MessCode = 0;
                return response;
            }

            // Validate headers
            var (isValid, errors) = ExcelTemplateValidator.ValidateHeaders(worksheet);
            if (!isValid)
            {
                response.Success = false;
                response.Message = "Invalid Excel template. Please use the correct template.";
                response.Errors = errors;
                response.MessCode = 0;
                return response;
            }

            var user = await _userManager.FindByNameAsync(request.UserName.ToLower());
            if (user == null)
            {
                response.Success = false;
                response.Message = "Invalid MedRep User Name.";
                response.Errors = errors;
                response.MessCode = 0;
                return response;
            }

            var rowCount = worksheet.LastRowUsed()?.RowNumber() ?? 0;
            response.TotalRows = rowCount - 1;

            // Calculate current year and month once for performance
            var currentDate = DateTime.Now;
            var currentYear = currentDate.Year;
            var currentMonth = currentDate.Month;

            // Pre-load existing inventory data to avoid N+1 query problem
            var existingInventory = await LoadExistingInventoryAsync(worksheet, rowCount);

            // Process in batches
            var inventoriesToInsert = new List<Inventory>();
            var summaryDict = new Dictionary<string, InventorySummary>();

            for (int row = 2; row <= rowCount; row++)
            {
                try
                {
                    var brandName = worksheet.Cell(row, 1).Value.ToString()?.Trim();
                    var year = worksheet.Cell(row, 14).Value.ToString()?.Trim();
                    var medRepName = worksheet.Cell(row, 15).Value.ToString()?.Trim();

                    if (string.IsNullOrWhiteSpace(brandName))
                    {
                        response.SkippedRows++;
                        continue;
                    }

                    if (!int.TryParse(year, out int cycleYear))
                    {
                        response.Errors.Add($"Row {row}: Invalid year '{year}'");
                        response.SkippedRows++;
                        continue;
                    }

                    // Validate year is not in the past
                    if (cycleYear < currentYear)
                    {
                        response.Errors.Add($"Row {row}: Year {cycleYear} is in the past. Only current month onwards is allowed.");
                        response.SkippedRows++;
                        continue;
                    }

                    // Process each month
                    for (byte month = 1; month <= 12; month++)
                    {
                        var cellValue = worksheet.Cell(row, month + 1).Value.ToString()?.Trim();

                        if (!int.TryParse(cellValue, out int qty) || qty <= 0)
                        {
                            continue;
                        }

                        // Validate date is not in the past (current month or future only)
                        if (cycleYear == currentYear && month < currentMonth)
                        {
                            _logger.LogDebug($"Row {row}, Month {month}: Skipping past date - {month}/{cycleYear}");
                            response.Errors.Add($"Row {row}, Month {month}: Date {month}/{cycleYear} is in the past. Only {currentMonth}/{currentYear} onwards is allowed.");
                            response.SkippedRows++;
                            continue;
                        }

                        // Check if record exists using in-memory lookup (much faster)
                        // Include MedRepName in key because same brand can be distributed by different reps
                        var key = $"{brandName}|{month}|{cycleYear}|{medRepName}";
                        if (existingInventory.Contains(key))
                        {
                            _logger.LogDebug($"Row {row}, Month {month}: Skipping duplicate - {key}");
                            continue; // Skip duplicates
                        }

                        // Also check if we already added this in current batch (prevent duplicates within same file)
                        if (inventoriesToInsert.Any(x =>
                            x.Description == brandName &&
                            x.CycleMonth == month &&
                            x.CycleYear == cycleYear &&
                            x.MedRepName == medRepName))
                        {
                            _logger.LogDebug($"Row {row}, Month {month}: Skipping - already in current batch");
                            continue;
                        }

                        var inventory = new Inventory
                        {
                            InventoryId = Guid.NewGuid(),
                            UserId = userId,
                            Description = brandName,
                            QtyIn = qty,
                            QtyOut = 0,
                            QtyBalance = qty,
                            CycleMonth = month,
                            CycleYear = cycleYear,
                            MedRepName = medRepName,
                            CreatedBy = userName,
                            CreatedDate = DateTime.Now,
                            IsActive = true
                        };

                        inventoriesToInsert.Add(inventory);
                        response.ProcessedRows++;

                        // Update summary
                        var summaryKey = $"{brandName}|{medRepName}";
                        if (!summaryDict.TryGetValue(summaryKey, out var summary))
                        {
                            summary = new InventorySummary
                            {
                                Brand = brandName,
                                TotalQty = 0,
                                MedRepName = medRepName
                            };
                            summaryDict[summaryKey] = summary;
                        }
                        summary.TotalQty += qty;
                    }

                    // Batch insert to reduce database round trips
                    if (inventoriesToInsert.Count >= BATCH_SIZE)
                    {
                        await _unitOfWork.Inventory.AddRangeAsync(inventoriesToInsert);
                        await _unitOfWork.SaveChangesAsync();
                        inventoriesToInsert.Clear();
                    }
                }
                catch (Exception ex)
                {
                    response.Errors.Add($"Row {row}: {ex.Message}");
                    response.SkippedRows++;
                    _logger.LogError(ex, $"Error processing row {row}");
                }
            }

            // Insert remaining items
            if (inventoriesToInsert.Any())
            {
                await _unitOfWork.Inventory.AddRangeAsync(inventoriesToInsert);
                await _unitOfWork.SaveChangesAsync();
            }

            response.InsertedItems = summaryDict.Values.ToList();
            response.Success = true;
            response.MessCode = 1;
            response.Message = $"Successfully processed {response.ProcessedRows} inventory records. Skipped {response.SkippedRows} duplicates/invalid rows.";

            _logger.LogInformation($"Excel processing completed: {response.ProcessedRows} processed, {response.SkippedRows} skipped, {response.TotalRows} total rows");
        }
        finally
        {
            // Return buffer to pool
            System.Buffers.ArrayPool<byte>.Shared.Return(buffer);
        }
    }
    catch (Exception ex)
    {
        response.Success = false;
        response.Message = $"Error processing Excel file: {ex.Message}";
        response.MessCode = 0;
        response.Errors.Add(ex.ToString());
        _logger.LogError(ex, "Error processing Excel file");
    }

    return response;
}
/// <summary>
/// Pre-loads existing inventory records from database to avoid N+1 queries
/// This validates against ALL records in the inventory table (from all users)
/// to prevent duplicate uploads
/// </summary>
private async Task<HashSet<string>> LoadExistingInventoryAsync(IXLWorksheet worksheet, int rowCount)
{
    var brandNames = new HashSet<string>();
    var years = new HashSet<int>();
    var medRepNames = new HashSet<string>();
    var months = new HashSet<byte> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 }; // All possible months

    // Collect unique brands, years, and med rep names from Excel file being uploaded
    for (int row = 2; row <= rowCount; row++)
    {
        var brandName = worksheet.Cell(row, 1).Value.ToString()?.Trim();
        var yearStr = worksheet.Cell(row, 14).Value.ToString()?.Trim();
        var medRepName = worksheet.Cell(row, 15).Value.ToString()?.Trim();

        if (!string.IsNullOrWhiteSpace(brandName))
        {
            brandNames.Add(brandName);
        }

        if (int.TryParse(yearStr, out int year))
        {
            years.Add(year);
        }

        if (!string.IsNullOrWhiteSpace(medRepName))
        {
            medRepNames.Add(medRepName);
        }
    }

    if (!brandNames.Any() || !years.Any())
    {
        return new HashSet<string>();
    }

    // Single query to load ALL matching records from inventory table
    // This includes records uploaded by ANY user (current or previous uploads)
    // Filters by Brand, Month, Year, and MedRepName to ensure precise duplicate detection
    var existingRecords = await _unitOfWork.Inventory
        .FindAllAsync(inv =>
            brandNames.Contains(inv.Description) &&
            months.Contains(inv.CycleMonth) &&
            years.Contains(inv.CycleYear) &&
            medRepNames.Contains(inv.MedRepName) &&
            inv.IsActive == true); // Only check active records

    // Create lookup set with composite key: Brand|Month|Year|MedRepName
    // This allows O(1) existence checks during row processing
    // MedRepName is included because the same brand can be handled by different reps
    var existingKeys = new HashSet<string>(
        existingRecords.Select(inv =>
            $"{inv.Description}|{inv.CycleMonth}|{inv.CycleYear}|{inv.MedRepName}"));

    _logger.LogInformation($"Loaded {existingKeys.Count} existing inventory records for validation");

    return existingKeys;
}
#endregion*/