using System.ComponentModel.DataAnnotations;
using System.Reflection;
using System.Text.RegularExpressions;
using Aspose.Cells;
using Aspose.Cells.Tables;

namespace Fast.Logic;

public static partial class Util
{
    public static class Excel
    {
        public static MemoryStream GetExportFileStream<T>(List<T>? items, List<string>? columnNames = null)
        {
            var itemsCopy = items?.ToList() ?? new List<T>();

            var wb = new Workbook();
            var defaultSytyle = wb.DefaultStyle;
            defaultSytyle.Font.Name = "Calibri";
            defaultSytyle.Font.Size = 11;
            wb.DefaultStyle = defaultSytyle;
            var ws = wb.Worksheets[0];

            int startRow = 0;
            int startCol = 0;

            var displayPropsDic = GetDisplayProperties<T>().ToDictionary(x => x.PropertyName);
            var itemPropsDic = typeof(T).GetProperties().ToDictionary(x => x.Name);

            string[] propNames;
            if (columnNames != null)
                propNames = columnNames.ToArray();
            else
                propNames = displayPropsDic.Values.Select(x => x.PropertyName).ToArray();

            var dateColIndexes = new HashSet<int>();

            //manually put values since Cells.ImportCustomObjects does not support DateOnly
            int itemRowIndex = startRow + 1;
            for (int i = 0; i < itemsCopy.Count; i++)
            {
                T item = itemsCopy[i];
                int itemColIndex = startCol;
                foreach (var propName in propNames)
                {
                    var prop = itemPropsDic[propName];
                    var value = prop?.GetValue(item);
                    if (value is DateOnly dateOnly)
                    {
                        value = dateOnly.ToDateTime(TimeOnly.MinValue);
                        dateColIndexes.Add(itemColIndex);
                    }
                    ws.Cells[itemRowIndex, itemColIndex].PutValue(value);
                    itemColIndex++;
                }
                itemRowIndex++;
            }

            foreach (var dateColIndex in dateColIndexes)
            {
                StyleFlag styleFlag = new() { NumberFormat = true };
                Style style = ws.Cells.Columns[dateColIndex].GetStyle();
                style.Custom = xlNumberFormat_Date_Slash_NoTime;
                ws.Cells.Columns[dateColIndex].ApplyStyle(style, styleFlag);
            }

            //change column headers to "display" names instead of property names
            int headerColIndex = startCol;
            foreach (var propName in propNames)
            {
                var displayProp = displayPropsDic[propName];
                ws.Cells[startRow, headerColIndex].PutValue(displayProp.DisplayName);

                if (!string.IsNullOrWhiteSpace(displayProp.ExcelNumberFormat))
                {
                    StyleFlag styleFlag = new() { NumberFormat = true };
                    Style style = ws.Cells.Columns[headerColIndex].GetStyle();
                    style.Custom = displayProp.ExcelNumberFormat;
                    ws.Cells.Columns[headerColIndex].ApplyStyle(style, styleFlag);
                }
                headerColIndex++;
            }

            int maxDataCol = ws.Cells.MaxDataColumn;
            int itemColCount = maxDataCol + 1;

            //set list count to 1 if there are no items
            int listItemCount = itemsCopy.Count != 0 ? itemsCopy.Count : 1;
            var listObject = ws.ListObjects[ws.ListObjects.Add(startRow, startCol, startRow + listItemCount, startCol + itemColCount - 1, true)];
            listObject.TableStyleType = TableStyleType.TableStyleLight13;

            ws.AutoFitColumns();

            MemoryStream stream = new();
            wb.Save(stream, SaveFormat.Xlsx);
            stream.Seek(0, SeekOrigin.Begin);

            return stream;
        }

        private static List<DisplayProperty> GetDisplayProperties<T>()
        {
            PropertyInfo[] propInfos = typeof(T).GetProperties();
            List<DisplayProperty> displayProps = new();

            foreach (var prop in propInfos)
            {
                var displayAttribute = (DisplayAttribute?)prop.GetCustomAttributes(typeof(DisplayAttribute)).FirstOrDefault();
                if (displayAttribute != null && !string.IsNullOrEmpty(displayAttribute.Name))
                {
                    var displayProp = new DisplayProperty { PropertyName = prop.Name, DisplayName = displayAttribute.Name };
                    if (!string.IsNullOrWhiteSpace(displayAttribute.Description))
                    {
                        string[] descriptionSplit = displayAttribute.Description.Split(",");
                        if (descriptionSplit.Length >= 4)
                            displayProp.ExcelNumberFormat = ConvertKendoNumberFormatToExcelNumberFormat(descriptionSplit[3]);
                    }

                    displayProps.Add(displayProp);
                }
            }

            if (displayProps.Count == 0)
            {
                throw new Exception("There are no Display Properties in " + typeof(T).Name + ", you can add them using [Display(Name = \"name of column\")]");
            }

            return displayProps;
        }

        private static string ConvertKendoNumberFormatToExcelNumberFormat(string kendoNumberFormat)
        {
            string excelNumberFormat = kendoNumberFormat switch
            {
                "{0:c2}" => @"$#,##0.00_);[Red]($#,##0.00)",
                "{0:c3}" => @"$#,##0.000_);[Red]($#,##0.000)",
                "{0:c4}" => @"$#,##0.0000_);[Red]($#,##0.0000)",
                "{0:c5}" => @"$#,##0.00000_);[Red]($#,##0.00000)",
                "{0:c6}" => @"$#,##0.000000_);[Red]($#,##0.000000)",
                "{0:n0}" => @"#,##0",
                "{0:n1}" => @"#,##0.0",
                "{0:n2}" => @"#,##0.00",
                "{0:n3}" => @"#,##0.000",
                "{0:n4}" => @"#,##0.0000",
                "{0:n5}" => @"#,##0.00000",
                "{0:n6}" => @"#,##0.000000",
                "{0:d}" => @"m/d/yyyy",
                "{0:dd}" => @"d",
                "{0:p5}" => @"0.00000%",
                "MMM yyyy" => @"mmm yyyy",
                _ => throw new Exception("number format conversion not found"),
            };
            return excelNumberFormat;
        }

        private class DisplayProperty
        {
            public string PropertyName = "";
            public string DisplayName = "";
            public string ExcelNumberFormat = "";
        }

        public static bool SheetExists(Workbook wb, string sheetName)
        {
            if (!string.IsNullOrEmpty(sheetName))
            {
                foreach (Worksheet sheet in wb.Worksheets)
                {
                    if (string.Equals(sheet.Name, sheetName, StringComparison.OrdinalIgnoreCase))
                        return true;
                }
            }

            return false;
        }

        public static void FormatPrinting(Workbook wb)
        {
            foreach (Worksheet sheet in wb.Worksheets)
                SetPrint(sheet);
        }

        public static void RefreshData(Workbook wb)
        {
            foreach (Worksheet sheet in wb.Worksheets)
            {
                try
                {
                    //jlupo - remove the try catch once aspose fixes the error that sometimes occurs with RefreshPivotTables()
                    sheet.RefreshPivotTables();
                }
                catch (Exception)
                {
                }
            }
        }

        public static void SetPrint(Worksheet ws, string RowsToRepeat = "1:1", string? ColsToRepeat = null, string? tscPaperSize = null, int? zoomPercent = null, bool? fitToPageWide = null)
        {
            // exit if worksheet is empty:
            if (ws.Cells.MaxDataRow < 0 || ws.Cells.MaxDataColumn < 0)
                return;

            ws.PageSetup.SetFooter(0, "&10Printed on &D at &T");
            ws.PageSetup.SetFooter(2, "&10Page &P of &N");

            int LastRow = ws.Cells.MaxDataRow + 1;
            int LastCol = ws.Cells.MaxDataColumn + 1;

            for (var i = 1; i <= LastCol; i++)
            {
                if (ws.Cells.GetColumnWidth(i - 1) > 100)
                    ws.Cells.SetColumnWidth(i - 1, 100);
            }

            Aspose.Cells.Range printRange = ws.Cells.CreateRange("A1", ws.Cells[LastRow - 1, LastCol - 1].Name);

            if (ws.PageSetup.PrintArea == null)
            {
                ws.PageSetup.PrintArea = GetRangeAddress(printRange);

                const int minZoom = 55;
                double totalWidth = 0;
                double totalHeight = 0;
                double myZoom;
                double myZoom1;
                double myZoom2;

                ws.PageSetup.PrintTitleRows = RowsToRepeat;

                if (ColsToRepeat != null)
                    ws.PageSetup.PrintTitleColumns = ColsToRepeat;

                for (int i = 1; i <= LastCol; i++)
                    totalWidth += ws.Cells.GetColumnWidth(i - 1, false, CellsUnitType.Inch);

                for (int i = 1; i <= LastRow; i++)
                    totalHeight += ws.Cells.GetRowHeightInch(i - 1);

                if (fitToPageWide.HasValue)
                {
                    ws.PageSetup.FitToPagesWide = 1;
                    ws.PageSetup.FitToPagesTall = 0;
                    ws.PageSetup.Orientation = PageOrientationType.Landscape;
                }
                else if (zoomPercent.HasValue)
                {
                    ws.PageSetup.Zoom = zoomPercent.Value;
                    ws.PageSetup.Orientation = PageOrientationType.Landscape;
                }
                else
                {
                    double sideMarginTotal = ws.PageSetup.LeftMarginInch + ws.PageSetup.RightMarginInch;
                    double topBottomMarginTotal = ws.PageSetup.TopMarginInch + ws.PageSetup.BottomMarginInch;

                    //buffers needed since maxWidth and maxHeight seem to
                    //be slightly larger than actual printable size for some reason
                    double sideBuffer = 0.25;
                    double topBottomBuffer = 0.25;

                    double maxPortraitWidth = 8.5 - sideBuffer - sideMarginTotal;
                    double maxPortraitHeight = 11 - topBottomBuffer - topBottomMarginTotal;
                    double maxLandscapeWidth = maxPortraitHeight;
                    double maxLandscapeHeight = maxPortraitWidth;

                    myZoom = 100;
                    myZoom1 = 0;
                    myZoom2 = 0;

                    if (totalWidth < maxPortraitWidth)
                    {
                        ws.PageSetup.Orientation = PageOrientationType.Portrait;
                        //if a little taller than 1 page but close then shrink it, close is under 9 inches
                        if (totalHeight > maxPortraitHeight & totalHeight < 9)
                            myZoom = Math.Abs((((totalHeight - maxPortraitHeight) / totalHeight) - 1) * 100);
                    }
                    else
                    {
                        ws.PageSetup.Orientation = PageOrientationType.Landscape;
                        if (totalWidth > maxLandscapeWidth)
                            myZoom1 = Math.Abs((((totalWidth - maxLandscapeWidth) / totalWidth) - 1) * 100);

                        //if a little taller than 1 page but close then shrink it, close is under 9 inches
                        if (totalHeight > maxLandscapeHeight & totalHeight < 9)
                            myZoom2 = Math.Abs((((totalHeight - maxLandscapeHeight) / totalHeight) - 1) * 100);
                    }

                    if (myZoom1 > 0 & myZoom2 > 0)
                    {
                        if (myZoom1 < myZoom2)
                            myZoom = myZoom1;
                        else
                            myZoom = myZoom2;
                    }
                    else if (myZoom1 > 0)
                        myZoom = myZoom1;
                    else if (myZoom2 > 0)
                        myZoom = myZoom2;

                    if (myZoom < minZoom)
                        myZoom = minZoom;

                    ws.PageSetup.Zoom = Convert.ToInt32(Math.Floor(myZoom));
                }

                if (tscPaperSize != null && tscPaperSize.ToLower() == "legal")
                    ws.PageSetup.PaperSize = PaperSizeType.PaperLegal;
                else
                    ws.PageSetup.PaperSize = PaperSizeType.PaperLetter;
            }
        }

        public static string GetRangeAddress(Aspose.Cells.Range rng)
        {
            Worksheet ws = rng.Worksheet;

            Cell StartCell = ws.Cells[rng.FirstRow, rng.FirstColumn];
            Cell EndCell = ws.Cells[rng.FirstRow + rng.RowCount - 1, rng.FirstColumn + rng.ColumnCount - 1];

            string StartCellStr = "$" + CellsHelper.ColumnIndexToName(StartCell.Column) + "$" + (StartCell.Row + 1);
            string EndCellStr = "$" + CellsHelper.ColumnIndexToName(EndCell.Column) + "$" + (EndCell.Row + 1);
            string Addr = StartCellStr + ":" + EndCellStr;

            return Addr;
        }

        public static void ReportBorders(Aspose.Cells.Range rng, bool hasColumnHeaders = true, CellBorderType cellBorderType = CellBorderType.Thin)
        {
            if (rng != null)
            {
                Worksheet ws = rng.Worksheet;
                Workbook wb = ws.Workbook;

                Style style = wb.CreateStyle();
                Border border = style.Borders[BorderType.RightBorder];
                border.LineStyle = cellBorderType;
                border.Color = System.Drawing.Color.Black;
                StyleFlag styleflag = new()
                {
                    RightBorder = true
                };
                rng.ApplyStyle(style, styleflag);

                rng.SetOutlineBorders(cellBorderType, System.Drawing.Color.Black);

                if (hasColumnHeaders == true)
                {
                    // for first row column headers
                    Aspose.Cells.Range rngFirstRow = ws.Cells.CreateRange(rng.FirstRow, rng.FirstColumn, 1, rng.ColumnCount);
                    rngFirstRow.SetOutlineBorders(cellBorderType, System.Drawing.Color.Black);
                }
            }
        }

        public static void AutoFillFormulas(Aspose.Cells.Range rng)
        {
            int Increment = 1;
            Worksheet ws = rng.Worksheet;

            string Fmla = ws.Cells[rng.FirstRow, rng.FirstColumn].Formula;
            if (Fmla == null)
                return;

            if (rng.RowCount == 1)
            {
                for (var i = (rng.FirstColumn + 1); i <= (rng.FirstColumn + rng.ColumnCount - 1); i++)
                {
                    string NewFmla = Fmla;
                    Match match = Regex.Match(Fmla, @"(?<!\$)[A-Z]+(?=\$?[0-9]+)", RegexOptions.RightToLeft);
                    while (match.Success)
                    {
                        string Original = match.Value;
                        string Replacement = CellsHelper.ColumnIndexToName(CellsHelper.ColumnNameToIndex(Original) + Increment);
                        NewFmla = NewFmla.Remove(match.Index, match.Length);
                        NewFmla = NewFmla.Insert(match.Index, Replacement);
                        match = match.NextMatch();
                    }
                    var cell = ws.Cells[rng.FirstRow, i];
                    cell.Formula = NewFmla;
                    Increment += 1;
                }
            }
            else if (rng.ColumnCount == 1)
            {
                for (var i = (rng.FirstRow + 1); i <= (rng.FirstRow + rng.RowCount - 1); i++)
                {
                    string NewFmla = Fmla;
                    Match match = Regex.Match(Fmla, @"(?<=\$?[A-Z]+)(?<!\$)[0-9]+", RegexOptions.RightToLeft);
                    while (match.Success)
                    {
                        string Original = match.Value;
                        string Replacement = (System.Convert.ToInt32(Original) + Increment).ToString();
                        NewFmla = NewFmla.Remove(match.Index, match.Length);
                        NewFmla = NewFmla.Insert(match.Index, Replacement);
                        match = match.NextMatch();
                    }
                    var cell = ws.Cells[i, rng.FirstColumn];
                    cell.Formula = NewFmla;
                    Increment += 1;
                }
            }
        }

        public static void CustomAutoFitRows(Worksheet sheet)
        {
            sheet.AutoFitRows();
            foreach (Row rw in sheet.Cells.Rows)
                sheet.Cells.SetRowHeightPixel(rw.Index, sheet.Cells.GetRowHeightPixel(rw.Index) + 2);
        }

        // Excel NumberFormats
        public const string xlNumberFormat_Date_Dash = "mm-dd-yyyy";
        public const string xlNumberFormat_Date_Dash_Time = @"mm-dd-yyyy \a\t h:mm:ss AM/PM";
        public const string xlNumberFormat_Date_Slash = "mm/dd/yyyy";
        public const string xlNumberFormat_Date_Dash_NoTime = "mm-dd-yyyy;@";
        public const string xlNumberFormat_Date_Slash_NoTime = "mm/dd/yyyy;@";
        public const string xlNumberFormat_Month = "mmm yyyy";
        public const string xlNumberFormat_Month_NoTime = "mmm yyyy;@";
        public const string xlNumberFormat_Day_Month = "d-mmm";

        public const string xlNumberFormat_MonthShort = "mmm-yy";
        public const string xlNumberFormat_MonthLongYearShort = "mmmm yy";
        public const string xlNumberFormat_MonthLongYearLong = "mmmm yyyy";
        public const string xlNumberFormat_MonthLongDayYearLong = "mmmm d, yyyy;@"; // adding April 1, 2010 format

        public const string xlNumberFormat_NoDecimals = "#,##;-#,##";
        public const string xlNumberFormat_NoDecimals_Price = "$#,##;($#,##);";
        public const string xlNumberFormat_NoDecimals_PriceZero = "$#,##;($#,##);$0";

        public const string xlNumberFormat_NoDecimals_Dash_Price = "$#,##;-#,##;-";  // added a zero
        public const string xlNumberFormat_NoDecimals_Dash = "#,##;-#,##;-";   // added a zero
        public const string xlNumberFormat_NoDecimals_Paren = "#,##;(#,##)";
        public const string xlNumberFormat_NoDecimals_Zero = "#,###;-#,###;0";
        public const string xlNumberFormat_NoDecimals_ParenZero = "#,##;(#,##);0";
        public const string xlNumberFormat_NoDecimals_DefaultZero = "#,##0;-#,##0;0";
        public const string xlNumberFormat_NoDecimals_PriceDefaultZero = "$#,##0;($#,##0);0";

        public const string xlNumberFormat_1Decimals_Price = "$#,##0.0;-$#,##0.0";
        public const string xlNumberFormat_1Decimals_PriceZero = "$#,##0.0;-$#,##0.0,$0.0";

        public const string xlNumberFormat_2Decimals = "#,##0.00;-#,##0.00";
        public const string xlNumberFormat_2Decimals_Zero = "#,##0.00;-#,##0.00;0";
        public const string xlNumberFormat_2Decimals_Paren = "#,##0.00;(#,##0.00)";
        public const string xlNumberFormat_2Decimals_Price = "$#,##0.00;-$#,##0.00";
        public const string xlNumberFormat_2Decimals_PriceZero = "$#,##0.00;-$#,##0.00;$0.00";
        public const string xlNumberFormat_2Decimals_PriceBlankZero = "$#,##0.00;-$#,##0.00;0";
        public const string xlNumberFormat_2Decimals_PriceParen = "$#,##0.00;($#,##0.00)";
        public const string xlNumberFormat_2Decimals_PriceParenZero = "$#,##0.00;($#,##0.00);$0.00";

        public const string xlNumberFormat_3Decimals = "#,##0.000;-#,##0.000";
        public const string xlNumberFormat_3Decimals_Zero = "#,##0.000;-#,##0.000;0";
        public const string xlNumberFormat_3Decimals_Paren = "#,##0.000;(#,##0.000)";
        public const string xlNumberFormat_3Decimals_Price = "$#,##0.000;-$#,##0.000";
        public const string xlNumberFormat_3Decimals_PriceZero = "$#,##0.000;-$#,##0.000;$0.000";
        public const string xlNumberFormat_3Decimals_PriceParen = "$#,##0.000;($#,##0.000)";
        public const string xlNumberFormat_3Decimals_PriceParenZero = "$#,##0.000;($#,##0.000);$0.000";

        public const string xlNumberFormat_4Decimals = "#,##0.0000;-#,##0.0000";
        public const string xlNumberFormat_4Decimals_Price = "$#,##0.0000;-$#,##0.0000";
        public const string xlNumberFormat_4Decimals_PriceZero = "$#,##0.0000;-$#,##0.0000;$0.0000";
        public const string xlNumberFormat_4Decimals_Dash = "#,##0.0000;-#,##0.0000;-";
        public const string xlNumberFormat_4Decimals_Zero = "#,##0.0000;-#,##0.0000;0.0000";

        public const string xlNumberFormat_5Decimals = "#,##0.00000;-#,##0.00000";
        public const string xlNumberFormat_5Decimals_Zero = "#,##0.00000;-#,##0.00000;0.00000";
        public const string xlNumberFormat_5Decimals_Paren = "#,##0.00000;(#,##0.00000)";
        public const string xlNumberFormat_5Decimals_Price = "$#,##0.00000;-$#,##0.00000";
        public const string xlNumberFormat_5Decimals_PriceZero = "$#,##0.00000;-$#,##0.00000;$0.00000";
        public const string xlNumberFormat_5Decimals_PriceParen = "$#,##0.00000;($#,##0.00000)";
        public const string xlNumberFormat_5Decimals_PriceParenZero = "$#,##0.00000;($#,##0.00000);$0.00000";
        // Public Const xlNumberFormat_Dollars As String = "$#,##0.00;[Red]($#,##0.00)"
        public const string xlNumberFormat_Dollars = "$#,##0.00;($#,##0.00)";
        public const string xlNumberFormat_DollarsZero = "$#,##0.00;($#,##0.00);0";
        // Public Const xlNumberFormat_DollarsWhole As String = "$#,##0;[Red]($#,##0)"
        public const string xlNumberFormat_DollarsWhole = "$#,##0;($#,##0)";
        public const string xlNumberFormat_DollarsWholeParenZero = "$#,##0;($#,##0);$0";
        public const string xlNumberFormat_Percent = "0.00%";
        public const string xlNumberFormat_NoDecimals_Percent = "0%";
    }
}
