using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.AspNetCore.OData.Formatter;

namespace Fast.Web.Controllers;

[Authorize]
[ApiController]
[Route("api/[controller]")]
public class MarketPriceController : ODataController
{
    private readonly MyDbContext db;
    public MarketPriceController(MyDbContext context)
    {
        db = context;
    }

    [Permission("Market Price", PermissionType.View)]
    [Route("/odata/GetMarketPriceItems")]
    public IActionResult GetMarketPriceItems([FromODataUri] int marketTypeId, [FromODataUri] int indexId, [FromODataUri] DateOnly fromDate, [FromODataUri] DateOnly toDate, ODataQueryOptions<MarketPriceItem> queryOptions)
    {
        var itemsQueryable = GetMarketPriceItemsInternal(marketTypeId, indexId, fromDate, toDate);
        var items = (queryOptions.ApplyTo(itemsQueryable) as IEnumerable<MarketPriceItem>)?.ToList();

        DateOnly defaultDate = new DateOnly(1900, 1, 1);

        if (items != null)
            foreach (var item in items)
                item.contractMonth = item.contractMonth == defaultDate ? Util.Date.FirstDayOfMonth(item.priceDate) : item.contractMonth;

        return Ok(items);
    }

    private IQueryable<MarketPriceItem> GetMarketPriceItemsInternal(int marketTypeId, int indexId, DateOnly fromDate, DateOnly toDate)
    {
        IQueryable<MarketPriceItem>? itemsQueryable = null;

        if (marketTypeId == 1)  //forward curve
        {
            itemsQueryable = (
            from q in db.MarketPrices
            where q.MarketTypeId == marketTypeId && q.IndexId == indexId && q.PriceDate >= fromDate && q.PriceDate <= toDate
            select new MarketPriceItem
            {
                id = q.Id,
                priceDate = q.PriceDate,
                contractMonth = q.ContractMonth,
                price = q.Price
            });
        }
        else if (marketTypeId == 2)  //daily index
        {
            itemsQueryable = (
            from q in db.MarketPrices
            where q.MarketTypeId == marketTypeId && q.IndexId == indexId && q.PriceDate >= fromDate && q.PriceDate <= toDate
            select new MarketPriceItem
            {
                id = q.Id,
                priceDate = q.PriceDate,
                contractMonth = q.ContractMonth,
                price = q.Price
            });
        }
        else if (marketTypeId == 3)  //monthly index
        {
            itemsQueryable = (
            from q in db.MarketPrices
            where q.MarketTypeId == marketTypeId && q.IndexId == indexId && q.PriceDate >= fromDate && q.PriceDate <= toDate
            select new MarketPriceItem
            {
                id = q.Id,
                priceDate = q.PriceDate,
                publishDate = q.PublishDate,
                price = q.Price
            });
        }
        else
            throw new Exception("Invalid market type");

        return itemsQueryable;
    }

    [Permission("Market Price", PermissionType.View)]
    [Route("[action]")]
    public IActionResult GetRequiredData()
    {
        int[] marketTypeIdsToInclude = {
            (int)Enums.MarketType.ForwardCurve,
            (int)Enums.MarketType.DailyIndex,
            (int)Enums.MarketType.MonthlyIndex
        };

        var marketTypes = (from q in db.MarketTypes where marketTypeIdsToInclude.Contains(q.Id) orderby q.Id select new IdName(q.Id, q.Name ?? "")).ToList();
        var commodities = (from q in db.ProductCategories orderby q.Id select new IdName(q.Id, q.Name ?? "")).ToList();
        var dailyMonthlyindexes = (
            from q in db.MarketIndices
            join alias in db.MarketIndexAliases on new { IndexId = q.Id, IndexAliasTypeId = 1 } equals new { alias.IndexId, alias.IndexAliasTypeId } into j1
            from alias in j1.DefaultIfEmpty()
            where q.IndexTypeId != (int)Enums.MarketIndexType.Hybrid
            let IndexTypeId = q.IndexTypeId != (int)Enums.MarketIndexType.Hybrid && q.IndexTypeId == (int)Enums.MarketIndexType.Monthly ? 3 : 2
            orderby q.Name
            select new RequiredDataIndex
            {
                Id = q.Id,
                Name = q.Name ?? "",
                Synonym = alias.IndexAlias ?? q.Name ?? "",
                CommodityId = q.Product.CategoryId,
                IndexTypeId = IndexTypeId,
                SelectedMarketTypeId = IndexTypeId
            }
        ).ToList();

        List<RequiredDataIndex> forwardIndexes = new();

        //copy all daily/monthly indexes and set their selectedMarketTypeId to forward curve
        foreach (RequiredDataIndex index in dailyMonthlyindexes)
        {
            var newForwardIndex = new RequiredDataIndex
            {
                Id = index.Id,
                Name = index.Name,
                Synonym = index.Synonym,
                CommodityId = index.CommodityId,
                IndexTypeId = index.IndexTypeId,
                SelectedMarketTypeId = 1
            };
            forwardIndexes.Add(newForwardIndex);
        }

        var indexes = forwardIndexes.Concat(dailyMonthlyindexes);

        int fakeId = 1;  //we need a fakeId for all indexes so that the [valueField] can be set to something in the html combobox
        foreach (RequiredDataIndex index in indexes)
        {
            index.FakeId = fakeId;
            fakeId++;
        }

        var result = new { marketTypes, commodities, indexes };

        return Ok(result);
    }

    [Permission("Market Price", PermissionType.Modify)]
    [Route("[action]")]
    public IActionResult SaveMarketPrices(int marketTypeId, int indexId, MarketPriceItem[] items)
    {
        DateOnly invalidDate = new DateOnly(1, 1, 1);
        DateOnly defaultDate = new DateOnly(1900, 1, 1);

        foreach (var item in items)
        {
            MarketPrice? dbItem = null;
            bool isDefaultContractMonth = item.contractMonth == Util.Date.FirstDayOfMonth(item.priceDate);

            if (marketTypeId == 1) //forward curve
                dbItem = (from q in db.MarketPrices where q.MarketTypeId == marketTypeId && q.IndexId == indexId && q.PriceDate == item.priceDate && q.ContractMonth == item.contractMonth select q).FirstOrDefault();
            else if (marketTypeId == 2) //daily index
                dbItem = (from q in db.MarketPrices where q.MarketTypeId == marketTypeId && q.IndexId == indexId && q.PriceDate == item.priceDate && (q.ContractMonth == item.contractMonth || (isDefaultContractMonth && q.ContractMonth == defaultDate)) select q).FirstOrDefault();
            else if (marketTypeId == 3) //monthly index
                dbItem = (from q in db.MarketPrices where q.MarketTypeId == marketTypeId && q.IndexId == indexId && q.PriceDate == item.priceDate select q).FirstOrDefault();

            if (item.price == null)  //if the price is empty
            {
                if (dbItem != null) //and the item exists then remove it
                    db.MarketPrices.Remove(dbItem);
            }
            else
            {
                if (dbItem == null) //if the item does not exist then add it
                {
                    dbItem = new MarketPrice();
                    db.MarketPrices.Add(dbItem);
                }

                dbItem.MarketTypeId = marketTypeId;
                dbItem.IndexId = indexId;
                dbItem.PriceDate = item.priceDate;
                dbItem.ContractMonth = item.contractMonth == invalidDate ? defaultDate : item.contractMonth;
                dbItem.PublishDate = item.publishDate == invalidDate || item.publishDate == defaultDate ? null : item.publishDate;
                dbItem.Price = item.price.Value;

                //set the publish date to null for market types that it does not apply to
                if (marketTypeId == 1 || marketTypeId == 2)
                    dbItem.PublishDate = null;

                //set the contract month to 1900 for market types that it does not apply to
                if (marketTypeId == 3)
                    dbItem.ContractMonth = defaultDate;
            }
        }

        int count = db.SaveChanges();

        return Ok(count);
    }

    public class RequiredDataIndex
    {
        public int FakeId;
        public int Id;
        public string Name = "";
        public string Synonym = "";
        public int CommodityId;
        public int IndexTypeId;
        public int SelectedMarketTypeId;
    }

    [Permission("Market Price", PermissionType.Modify)]
    [Route("[action]")]
    //parameter name "file" must match HTML <input> name
    public async Task<IActionResult> Import(IFormFile file, [FromODataUri] string metaData)
    {
        var response = await Util.File.ProcessUploadFileChunked(file, metaData, User, async (combinedFileStream) =>
        {
            int userId = Util.GetAppUserId(User);
            var importer = new PriceImporter(db, userId);
            var importResult = importer.Import(combinedFileStream);
            return await Task.FromResult(importResult);
        });

        if (!response.Success)
            throw new Exception(response.Message);

        if (response.IsComplete)
            return Ok(response.Data); //response for a fully processed file

        return Ok(new { response.Message }); //response for good but incomplete chunks
    }

    [Permission("Market Price", PermissionType.View)]
    [Route("Export")]
    public IActionResult Export(DateOnly fromDate, DateOnly toDate, RequiredDataIndex[] indexes)
    {
        var stream = new MemoryStream();

        using (var doc = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
        {
            OpenXmlHelper.InitializeUniversalStylesheet(doc);
            var ws = OpenXmlHelper.AddEmptySheet(doc, "Sheet1");
            var wbPart = doc.WorkbookPart!;
            var wsPart = wbPart.WorksheetParts.First();

            var stylesheet = wbPart.WorkbookStylesPart!.Stylesheet;
            var numberingFormats = stylesheet.NumberingFormats!;
            var cellFormats = stylesheet.CellFormats!;

            // Currency format
            uint nextFmtId = numberingFormats.Elements<NumberingFormat>().Max(f => f.NumberFormatId!.Value) + 1;
            var currency5Dec = new NumberingFormat { NumberFormatId = nextFmtId, FormatCode = "$#,##0.00000_);[Red]($#,##0.00000)" };
            numberingFormats.Append(currency5Dec);
            numberingFormats.Count = (uint)numberingFormats.ChildElements.Count;

            var currency5DecStyleIndex = (uint)cellFormats.ChildElements.Count;
            cellFormats.Append(new CellFormat { NumberFormatId = nextFmtId, FontId = 0, FillId = 0, BorderId = 0, ApplyNumberFormat = true });
            cellFormats.Count = (uint)cellFormats.ChildElements.Count;

            // Group header
            var boldCenteredStyleIndex = (uint)cellFormats.ChildElements.Count;
            cellFormats.Append(new CellFormat
            {
                FontId = 1,
                FillId = 0,
                BorderId = 0,
                Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Center },
                ApplyFont = true,
                ApplyAlignment = true
            });
            cellFormats.Count = (uint)cellFormats.ChildElements.Count;

            stylesheet.Save();

            const uint styleBold = 9;
            const uint styleDate = 6;

            // Main header
            var cellA1 = OpenXmlHelper.InsertCell(1, 1, wsPart, "From Date");
            cellA1.StyleIndex = styleBold;
            var cellB1 = OpenXmlHelper.InsertCell(2, 1, wsPart, fromDate);
            cellB1.StyleIndex = styleDate;

            var cellA2 = OpenXmlHelper.InsertCell(1, 2, wsPart, "To Date");
            cellA2.StyleIndex = styleBold;
            var cellB2 = OpenXmlHelper.InsertCell(2, 2, wsPart, toDate);
            cellB2.StyleIndex = styleDate;

            // Insert index data
            int currentStartCol = 1;
            int headerRowIndex = 4;
            int subHeaderRowIndex = 5;
            int dataStartRowIndex = 6;
            uint tableIdCounter = 1;

            foreach (var index in indexes)
            {
                var priceItems = GetMarketPriceItemsInternal(index.SelectedMarketTypeId, index.Id, fromDate, toDate).ToList();
                int colsWide = 3;

                // Group header
                string startColAlpha = OpenXmlHelper.GetColumnName(currentStartCol);
                string endColAlpha = OpenXmlHelper.GetColumnName(currentStartCol + colsWide - 1);
                var titleCell = OpenXmlHelper.InsertCell(currentStartCol, headerRowIndex, wsPart, index.Synonym);
                titleCell.StyleIndex = boldCenteredStyleIndex;
                OpenXmlHelper.MergeCells(wsPart, $"{startColAlpha}{headerRowIndex}", $"{endColAlpha}{headerRowIndex}");

                // Column headers
                var headers = Array.Empty<string>();
                if (index.SelectedMarketTypeId == (int)Enums.MarketType.ForwardCurve)
                {
                    priceItems = priceItems.OrderByDescending(q => q.priceDate).ThenBy(q => q.contractMonth).ToList();
                    headers = new[] { "Price Date", "Contract Month", "Price" };
                }
                else if (index.SelectedMarketTypeId == (int)Enums.MarketType.DailyIndex)
                {
                    priceItems = priceItems.OrderByDescending(q => q.priceDate).ToList();
                    headers = new[] { "Price Date", "Contract Month", "Price" };
                }
                else if (index.SelectedMarketTypeId == (int)Enums.MarketType.MonthlyIndex)
                {
                    priceItems = priceItems.OrderBy(q => q.priceDate).ToList();
                    headers = new[] { "Price Month", "Publish Date", "Price" };
                }

                for (int i = 0; i < headers.Length; i++)
                {
                    OpenXmlHelper.InsertCell(currentStartCol + i, subHeaderRowIndex, wsPart, headers[i]);
                }

                // Data rows
                int currentRow = dataStartRowIndex;
                int itemsCount = priceItems.Count;

                if (itemsCount == 0)
                {
                    // Add blank row so the table has at least one row and doesn't cause errors
                    OpenXmlHelper.InsertCell(currentStartCol, currentRow, wsPart, string.Empty);
                    OpenXmlHelper.InsertCell(currentStartCol + 1, currentRow, wsPart, string.Empty);
                    OpenXmlHelper.InsertCell(currentStartCol + 2, currentRow, wsPart, string.Empty);
                    currentRow++;
                    itemsCount = 1;
                }
                else
                {
                    foreach (var item in priceItems)
                    {
                        var cell1 = OpenXmlHelper.InsertCell(currentStartCol, currentRow, wsPart, item.priceDate);
                        cell1.StyleIndex = styleDate;

                        if (index.SelectedMarketTypeId == (int)Enums.MarketType.MonthlyIndex)
                        {
                            if (item.publishDate != null && item.publishDate != DateOnly.MinValue && item.publishDate != new DateOnly(1900, 1, 1))
                            {
                                var cell2 = OpenXmlHelper.InsertCell(currentStartCol + 1, currentRow, wsPart, item.publishDate.Value);
                                cell2.StyleIndex = styleDate;
                            }
                            else
                            {
                                OpenXmlHelper.InsertCell(currentStartCol + 1, currentRow, wsPart, string.Empty);
                            }
                        }
                        else
                        {
                            var cell2 = OpenXmlHelper.InsertCell(currentStartCol + 1, currentRow, wsPart, item.contractMonth);
                            cell2.StyleIndex = styleDate;
                        }

                        if (item.price.HasValue)
                        {
                            var cell3 = OpenXmlHelper.InsertCell(currentStartCol + 2, currentRow, wsPart, item.price.Value);
                            cell3.StyleIndex = currency5DecStyleIndex;
                        }
                        currentRow++;
                    }
                }

                string tableRef = $"{startColAlpha}{subHeaderRowIndex}:{endColAlpha}{dataStartRowIndex + itemsCount - 1}";
                OpenXmlHelper.AddTableToRange(
                    wsPart,
                    tableIdCounter++,
                    tableRef,
                    headers,
                    tableName: null,
                    showRowStripes: false
                );

                currentStartCol += colsWide + 1;
            }

            OpenXmlHelper.AutoFitColumns(ws, wbPart);
            OpenXmlHelper.SaveAllChanges(doc);
        }

        stream.Seek(0, SeekOrigin.Begin);
        return File(stream, "application/octet-stream");
    }
}
