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

namespace Fast.Web.Logic
{
    public class PlantStatementImportResult
    {
        public string? ChangesMessage = "";
        public string? Notes;
    }

    public class PlantStatementImporter
    {
        private readonly MyDbContext db;

        public PlantStatementImporter(MyDbContext context)
        {
            db = context;
        }

        public PlantStatementImportResult Import(Stream stream)
        {
            if (stream.Position > 0)
                stream.Seek(0, SeekOrigin.Begin);

            using var doc = SpreadsheetDocument.Open(stream, false);
            var wbPart = doc.WorkbookPart ?? throw new Exception("Workbook part not found.");
            var wsName = "NGL Data";

            var sheet = wbPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == wsName)
                ?? throw new Exception(wsName + " worksheet not found.");

            var wsPart = (WorksheetPart)wbPart.GetPartById(sheet.Id!);
            var sheetData = wsPart.Worksheet.Elements<SheetData>().FirstOrDefault();

            if (sheetData == null)
                throw new Exception("Sheet data not found.");

            var helper = new PlantStatementImportHelper(db, wsName);
            var itemList = new List<PlantStatement>();

            var headerRow = sheetData.Elements<Row>().FirstOrDefault(r => (r.RowIndex ?? 0) == 1);
            if (headerRow == null)
                throw new Exception($"No header row found in {wsName}.");

            // Map column index to header name
            var headerMap = new Dictionary<int, string>();
            foreach (var cell in headerRow.Elements<Cell>())
            {
                var cellRef = cell.CellReference?.Value;
                if (!string.IsNullOrEmpty(cellRef))
                {
                    int colIdx = OpenXmlHelper.GetColumnIndex(cellRef);
                    var val = OpenXmlHelper.GetCellValue(cell, wbPart);
                    if (!string.IsNullOrWhiteSpace(val))
                    {
                        headerMap[colIdx] = val;
                    }
                }
            }

            helper.ValidateRequiredFields(headerMap.Values.ToList());

            int rowsFoundCount = 0;
            int maxColIndex = headerMap.Keys.Count != 0 ? headerMap.Keys.Max() : 0;

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

                if (cellsInRow.TryGetValue(1, out var firstCell))
                {
                    var firstCellVal = OpenXmlHelper.GetCellValue(firstCell, wbPart);
                    if (!string.IsNullOrWhiteSpace(firstCellVal))
                    {
                        rowsFoundCount++;
                        var item = new PlantStatement();
                        int currentRowIndex = (int)row.RowIndex!.Value;

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

                            string? cellValue = null;
                            if (cellsInRow.TryGetValue(colIndex, out var cell))
                            {
                                cellValue = OpenXmlHelper.GetCellValue(cell, wbPart);
                            }

                            helper.SetItemValue(item, headerName, cellValue, currentRowIndex, colIndex);
                        }

                        ValidateRequiredFields(item, wsName, currentRowIndex);
                        itemList.Add(item);
                    }
                }
            }

            if (rowsFoundCount == 0)
                throw new Exception("No plant statement rows found in " + wsName + " worksheet.");

            foreach (var item in itemList)
            {
                int updateId = (
                    from q in db.PlantStatements
                    where q.ProductionMonth == item.ProductionMonth
                        && q.StatementDate == item.StatementDate
                        && q.PlantId == item.PlantId
                        && q.MeterId == item.MeterId
                    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 = Util.GetDbChanges(db.ChangeTracker);
            db.SaveChanges();

            return new PlantStatementImportResult { ChangesMessage = changesMessage, Notes = helper.Notes };
        }

        private void ValidateRequiredFields(PlantStatement item, string sheetName, int row)
        {
            if (item.PlantId > 0)
            {
                //order by is important so that we pick options with a set payout type first and null payout types last
                string? requiredFields = (
                    from q in db.PlantStatementOptions
                    where q.PlantId == item.PlantId && (q.PayoutTypeId == item.PayoutTypeId || q.PayoutTypeId == null)
                    orderby q.PayoutTypeId descending
                    select q.RequiredFields
                ).FirstOrDefault();

                var requiredFieldsList = new List<string>();
                if (!string.IsNullOrEmpty(requiredFields))
                    requiredFieldsList = requiredFields.Split(',').ToList();

                foreach (string requiredField in requiredFieldsList)
                {
                    string propName = requiredField;

                    if (requiredField == "PayoutType")
                        propName = "PayoutTypeId";
                    else if (requiredField == "ProcessorOrInletPipe")
                        propName = "StatementDescriptorId";

                    //if item does not have a value for requiredField
                    var propValue = item.GetType().GetProperty(propName)?.GetValue(item);
                    var propStrValue = propValue == null ? "" : propValue.ToString();
                    if (string.IsNullOrWhiteSpace(propStrValue))
                    {
                        var message = "";
                        message += "Sheet {" + sheetName + "} \u2022 ";
                        message += "Row {" + row.ToString() + "}\r\n";
                        message += "Missing required value for {" + requiredField + "}\r\n\r\n";
                        throw new Exception(message);
                    }
                }
            }
        }
    }
}
