using ClosedXML.Excel;
using Microsoft.Extensions.Logging;
using RhSolutions.SkuParser.Abstractions;
using RhSolutions.SkuParser.Models;

namespace RhSolutions.SkuParser.Services;

public class CommonExcelParser : ISkuParser
{
    private readonly ILogger<CommonExcelParser> logger;

    public CommonExcelParser(ILogger<CommonExcelParser> logger)
    {
        this.logger = logger;
    }

    public Dictionary<Product, double> ParseProducts(IFormFile file)
    {
        using XLWorkbook workbook = new(file.OpenReadStream());
        IXLWorksheet ws = workbook.Worksheets.FirstOrDefault(ws => ws.TabActive) ?? workbook.Worksheet(1);

        var leftTop = ws.FirstCellUsed()?.Address;
        var rightBottom = ws.LastCellUsed()?.Address;
        if (new object?[] { leftTop, rightBottom }.Any(x => x == null))
        {
            throw new ArgumentException($"Таблица пуста: {file.FileName}");
        }

        var lookupRange = ws.Range(leftTop, rightBottom).RangeUsed();
        var columns = lookupRange.Columns();

        var skuColumnQuantity = columns
            .Select(column => new
            {
                Column = column,
                Products = column.CellsUsed()
                    .Select(cell => !cell.HasFormula && Product.TryParse(cell.Value.ToString(), out Product? p) ? p : null)
            })
            .Select(c => new { c.Column, SkuCount = c.Products.Count(p => p != null) })
            .Aggregate((l, r) => l.SkuCount > r.SkuCount ? l : r);
        var skuColumn = skuColumnQuantity.SkuCount > 0 ? skuColumnQuantity.Column : null;

        if (skuColumn == null)
        {
            throw new ArgumentException($"Столбец с артикулом не определен: {file.FileName}");
        }

        var quantityColumn = lookupRange.Columns().Skip(skuColumn.ColumnNumber())
            .Select(column => new
            {
                Column = column,
                IsColumnWithNumbers = column.CellsUsed()
                    .Count(cell => cell.Value.IsNumber == true) > column.CellsUsed().Count() / 4
            })
            .First(x => x.IsColumnWithNumbers)
            .Column;

        if (quantityColumn == null)
        {
            throw new ArgumentException($"Столбец с количеством не определен: {file.FileName}");
        }

        Dictionary<Product, double> result = new();
        var rows = quantityColumn.CellsUsed().Select(x => x.Address.RowNumber);

        foreach (var row in rows)
        {
            var quantity = quantityColumn.Cell(row).Value;
            var sku = skuColumn.Cell(row).Value;

            if (quantity.IsNumber
                && Product.TryParse(sku.ToString(), out Product? p)
                && p != null)
            {
                if (result.ContainsKey(p))
                {
                    result[p] += (double)quantity;
                }
                else
                {
                    result.Add(p, (double)quantity);
                }
            }
        }

        logger.LogInformation("Parsed {Count} products from file {FileName}.", result.Count, file.FileName);
        foreach (var (product, quantity) in result)
        {
            logger.LogInformation("Product: {Sku}, Name: {Name}, Quantity: {Quantity}, Price: {Price}, ProductLine: {ProductLine}",
                product.Sku, product.Name, quantity, product.Price, product.ProductLine);
        }

        return result;
    }
}