using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace Fast.Logic;

public class PriceImportResult
{
    public string ChangesMessage = "";
    public string Notes = "";
}

public class PriceImporter
{
    private readonly MyDbContext db;
    private readonly int userId;

    public PriceImporter(MyDbContext context, int userId)
    {
        db = context;
        this.userId = userId;
    }

    public PriceImportResult Import(System.IO.Stream stream)
    {
        using var doc = SpreadsheetDocument.Open(stream, false);

        CheckSheets(doc);
        var forwardResult = ImportForwardCurves(doc);
        var dailyResult = ImportDailyIndexPrices(doc);
        var monthlyResult = ImportMonthlyIndexPrices(doc);

        string changesMessage = forwardResult.ChangesMessage + dailyResult.ChangesMessage + monthlyResult.ChangesMessage;
        string notes = forwardResult.Notes + dailyResult.Notes + monthlyResult.Notes;

        if (!string.IsNullOrWhiteSpace(notes))
            notes = "File uploaded successfully but with the following notes:\r\n" + notes;
        else
            notes = "File uploaded successfully.\r\n";

        return new PriceImportResult { ChangesMessage = changesMessage, Notes = notes };
    }

    private static void CheckSheets(SpreadsheetDocument doc)
    {
        var requiredSheets = new List<string> { "Forward Curve", "Daily Index", "Monthly Index" };
        var sheets = doc.WorkbookPart!.Workbook.Descendants<Sheet>().Select(s => s.Name?.Value).ToHashSet();

        foreach (string sheetName in requiredSheets)
        {
            if (!sheets.Contains(sheetName))
                throw new Exception(sheetName + " worksheet not found.");
        }
    }

    private static SheetData GetSheetData(SpreadsheetDocument doc, string sheetName)
    {
        var sheet = doc.WorkbookPart!.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);
        if (sheet == null || sheet.Id == null)
            throw new Exception($"Sheet {sheetName} not found.");

        var wsPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheet.Id!);
        return wsPart.Worksheet.Elements<SheetData>().First();
    }

    private static Dictionary<int, string> GetHeaders(SheetData sheetData, SpreadsheetDocument doc, out int maxColIndex)
    {
        var headerMap = new Dictionary<int, string>();
        maxColIndex = 0;

        var headerRow = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex?.Value == 1);
        if (headerRow != null)
        {
            foreach (var cell in headerRow.Elements<Cell>())
            {
                var colIndex = OpenXmlHelper.GetColumnIndex(cell.CellReference?.Value ?? "A1");
                var val = GetCellValue(cell, doc);
                if (val != null && !string.IsNullOrWhiteSpace(val.ToString()))
                {
                    headerMap[colIndex] = val.ToString()!.Trim();
                    if (colIndex > maxColIndex) maxColIndex = colIndex;
                }
            }
        }
        return headerMap;
    }

    /// <summary>
    /// Reads the raw value from an OpenXML cell.
    /// </summary>
    private static string? GetCellValue(Cell cell, SpreadsheetDocument doc)
    {
        if (cell.CellValue == null) return null;
        string text = cell.CellValue.InnerText;

        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            var sst = doc.WorkbookPart!.SharedStringTablePart?.SharedStringTable;
            if (sst != null && int.TryParse(text, out int id))
            {
                return sst.ElementAt(id).InnerText;
            }
        }
        else if (cell.DataType != null && cell.DataType.Value == CellValues.Boolean)
        {
            return (text == "1").ToString();
        }

        // Return raw text/number.
        // Note: Dates in Excel are stored as Doubles (e.g. 45293.5).
        // We pass this raw value to PriceImportHelper, which has logic to parse Double -> DateOnly.
        return text;
    }

    private PriceImportResult ImportForwardCurves(SpreadsheetDocument doc)
    {
        var wsName = "Forward Curve";
        try
        {
            var helper = new PriceImportHelper(db, wsName);
            var sheetData = GetSheetData(doc, wsName);

            var headersMap = GetHeaders(sheetData, doc, out int maxCol);
            var headersList = headersMap.Values.ToList();
            var requiredHeaders = new List<string> { "PRICE DATE", "INDEX", "PRICE MONTH", "PRICE" };
            helper.ValidateRequiredFields(headersList, requiredHeaders);

            DateTime currentTime = DateTime.UtcNow;
            var itemList = new List<MarketPrice>();

            foreach (var row in sheetData.Elements<Row>().Where(r => r.RowIndex?.Value > 1))
            {
                var cells = row.Elements<Cell>().ToDictionary(c => OpenXmlHelper.GetColumnIndex(c.CellReference?.Value ?? ""));

                // Get value of the first column
                if (cells.TryGetValue(1, out var firstCell))
                {
                    var firstVal = GetCellValue(firstCell, doc);
                    if (firstVal != null && !string.IsNullOrWhiteSpace(firstVal.ToString()))
                    {
                        MarketPrice item = new MarketPrice
                        {
                            MarketTypeId = 1,
                            DataInputTime = currentTime,
                            DataInputerId = userId
                        };

                        foreach (var kvp in headersMap)
                        {
                            int colIndex = kvp.Key;
                            string headerName = kvp.Value;

                            object? cellValue = null;
                            if (cells.TryGetValue(colIndex, out var cell))
                            {
                                cellValue = GetCellValue(cell, doc);
                            }

                            helper.SetItemValue(item, headerName, cellValue, (int)row.RowIndex!.Value, colIndex, requiredHeaders);
                        }
                        itemList.Add(item);
                    }
                }
            }

            foreach (var item in itemList)
            {
                int updateId = (from q in db.MarketPrices where q.MarketTypeId == item.MarketTypeId && q.IndexId == item.IndexId && q.PriceDate == item.PriceDate && q.ContractMonth == item.ContractMonth select q.Id).FirstOrDefault();

                if (updateId > 0)
                {
                    item.Id = updateId;
                    db.Entry(item).State = EntityState.Modified;
                }
                else
                {
                    db.Entry(item).State = EntityState.Added;
                }
            }

            string changesMessage = wsName + ": " + Util.GetDbChanges(db.ChangeTracker) + "\r\n";
            string notes = helper.Notes;
            db.SaveChanges();
            db.ChangeTracker.DetachAllEntities();

            return new PriceImportResult { ChangesMessage = changesMessage, Notes = notes };
        }
        catch (Exception ex)
        {
            string originalMessage = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
            if (originalMessage.StartsWith("Sheet {"))
                throw new Exception(originalMessage);
            else
                throw new Exception("Sheet {" + wsName + "} " + originalMessage);
        }
    }

    private PriceImportResult ImportDailyIndexPrices(SpreadsheetDocument doc)
    {
        var wsName = "Daily Index";
        try
        {
            var helper = new PriceImportHelper(db, wsName);
            var sheetData = GetSheetData(doc, wsName);

            var headersMap = GetHeaders(sheetData, doc, out int maxCol);
            var headersList = headersMap.Values.ToList();
            var requiredHeaders = new List<string> { "PRICE DATE", "INDEX", "PRICE" };
            helper.ValidateRequiredFields(headersList, requiredHeaders);

            DateTime currentTime = DateTime.UtcNow;
            DateOnly defaultDate = new(1900, 1, 1);
            var itemList = new List<MarketPrice>();

            foreach (var row in sheetData.Elements<Row>().Where(r => r.RowIndex?.Value > 1))
            {
                var cells = row.Elements<Cell>().ToDictionary(c => OpenXmlHelper.GetColumnIndex(c.CellReference?.Value ?? ""));

                if (cells.TryGetValue(1, out var firstCell))
                {
                    var firstVal = GetCellValue(firstCell, doc);
                    if (firstVal != null && !string.IsNullOrWhiteSpace(firstVal.ToString()))
                    {
                        MarketPrice item = new MarketPrice
                        {
                            MarketTypeId = 2,
                            ContractMonth = defaultDate,
                            DataInputTime = currentTime,
                            DataInputerId = userId
                        };

                        foreach (var kvp in headersMap)
                        {
                            int colIndex = kvp.Key;
                            string headerName = kvp.Value;
                            object? cellValue = null;
                            if (cells.TryGetValue(colIndex, out var cell))
                                cellValue = GetCellValue(cell, doc);

                            helper.SetItemValue(item, headerName, cellValue, (int)row.RowIndex!.Value, colIndex, requiredHeaders);
                        }

                        if (item.ContractMonth == defaultDate)
                            item.ContractMonth = Util.Date.FirstDayOfMonth(item.PriceDate);
                        itemList.Add(item);
                    }
                }
            }

            foreach (var item in itemList)
            {
                bool isDefaultContractMonth = item.ContractMonth == Util.Date.FirstDayOfMonth(item.PriceDate);
                int updateId = (from q in db.MarketPrices where q.MarketTypeId == item.MarketTypeId && q.IndexId == item.IndexId && q.PriceDate == item.PriceDate && (q.ContractMonth == item.ContractMonth || (isDefaultContractMonth && q.ContractMonth == defaultDate)) select q.Id).FirstOrDefault();

                if (updateId > 0)
                {
                    item.Id = updateId;
                    db.Entry(item).State = EntityState.Modified;
                }
                else
                {
                    db.Entry(item).State = EntityState.Added;
                }
            }

            string changesMessage = wsName + ": " + Util.GetDbChanges(db.ChangeTracker) + "\r\n";
            string notes = helper.Notes;
            db.SaveChanges();
            db.ChangeTracker.DetachAllEntities();

            return new PriceImportResult { ChangesMessage = changesMessage, Notes = notes };
        }
        catch (Exception ex)
        {
            string originalMessage = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
            if (originalMessage.StartsWith("Sheet {"))
                throw new Exception(originalMessage);
            else
                throw new Exception("Sheet {" + wsName + "} " + originalMessage);
        }
    }

    private PriceImportResult ImportMonthlyIndexPrices(SpreadsheetDocument doc)
    {
        var wsName = "Monthly Index";
        try
        {
            var helper = new PriceImportHelper(db, wsName);
            var sheetData = GetSheetData(doc, wsName);

            var headersMap = GetHeaders(sheetData, doc, out int maxCol);
            var headersList = headersMap.Values.ToList();
            var requiredHeaders = new List<string> { "PRICE DATE", "INDEX", "PRICE", "PUBLISH DATE" };
            helper.ValidateRequiredFields(headersList, requiredHeaders);

            DateTime currentTime = DateTime.UtcNow;
            DateOnly defaultDate = new DateOnly(1900, 1, 1);
            var itemList = new List<MarketPrice>();

            foreach (var row in sheetData.Elements<Row>().Where(r => r.RowIndex?.Value > 1))
            {
                var cells = row.Elements<Cell>().ToDictionary(c => OpenXmlHelper.GetColumnIndex(c.CellReference?.Value ?? ""));

                if (cells.TryGetValue(1, out var firstCell))
                {
                    var firstVal = GetCellValue(firstCell, doc);
                    if (firstVal != null && !string.IsNullOrWhiteSpace(firstVal.ToString()))
                    {
                        MarketPrice item = new MarketPrice
                        {
                            MarketTypeId = 3,
                            ContractMonth = defaultDate,
                            DataInputTime = currentTime,
                            DataInputerId = userId
                        };

                        foreach (var kvp in headersMap)
                        {
                            int colIndex = kvp.Key;
                            string headerName = kvp.Value;
                            object? cellValue = null;
                            if (cells.TryGetValue(colIndex, out var cell))
                                cellValue = GetCellValue(cell, doc);

                            helper.SetItemValue(item, headerName, cellValue, (int)row.RowIndex!.Value, colIndex, requiredHeaders);
                        }
                        itemList.Add(item);
                    }
                }
            }

            foreach (var item in itemList)
            {
                int updateId = (from q in db.MarketPrices where q.MarketTypeId == item.MarketTypeId && q.IndexId == item.IndexId && q.PriceDate == item.PriceDate select q.Id).FirstOrDefault();

                if (updateId > 0)
                {
                    item.Id = updateId;
                    db.Entry(item).State = EntityState.Modified;
                }
                else
                {
                    db.Entry(item).State = EntityState.Added;
                }
            }

            string changesMessage = wsName + ": " + Util.GetDbChanges(db.ChangeTracker) + "\r\n";
            string notes = helper.Notes;
            db.SaveChanges();
            db.ChangeTracker.DetachAllEntities();

            return new PriceImportResult { ChangesMessage = changesMessage, Notes = notes };
        }
        catch (Exception ex)
        {
            string originalMessage = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
            if (originalMessage.StartsWith("Sheet {"))
                throw new Exception(originalMessage);
            else
                throw new Exception("Sheet {" + wsName + "} " + originalMessage);
        }
    }
}
