using System;
using System.IO;
using System.Linq;
using System.Xml;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace Neo.Afx.Services.Documents
{
    public partial class DocumentUtilities
    {
        #region GenerateXlsxFromXmlV3
        /// <summary>
        /// Generates the XLSX from XML.
        /// </summary>
        /// <param name="ms">The memory stream to write to.</param>
        /// <param name="data">The data in XML format.</param>
        public void GenerateXlsxFromXmlV3(MemoryStream ms, XmlDocument data)
        {
            SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
            WorkbookPart workbookPart = spreadSheet.AddWorkbookPart();
            workbookPart.Workbook = new Workbook();

            spreadSheet.WorkbookPart.Workbook.AppendChild(new FileVersion
            {
                ApplicationName = "Microsoft Office Excel"
            });

            WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
            workbookStylesPart.Stylesheet = CreateStylesheetV3();
            workbookStylesPart.Stylesheet.Save();

            Sheets sheets = spreadSheet.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
            Sheet sheet = new Sheet
            {
                Id = spreadSheet.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = _database.GetSysSetting("ExcelExportDefaultSheetName")
            };

            sheets.Append(sheet);

            #region Columns

            var columns = data.FirstChild.ChildNodes[0].ChildNodes.Count;

            for (var item = 0; item <= columns-1; item++)
            {
                try
                {
                    SetColumnWidth(worksheetPart.Worksheet, (UInt32)item+1, Convert.ToDouble(data.FirstChild.ChildNodes[0].ChildNodes[item].Attributes["colWidth"].Value));
                }
                catch (Exception ex)
                {
                    SetColumnWidth(worksheetPart.Worksheet, (UInt32)item + 1, Convert.ToDouble(_database.GetSysSetting("ExcelExportDefaultColumnValue")));
                }
            }
            #endregion

            #region SharedStringTablePart
            // Get the SharedStringTablePart. If it does not exist, create a new one.
            var shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Any() ? spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First() : spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
            #endregion

            #region Insert Data
            var rowIndex = (uint)1;
            var cellIndex = "A";
            var cellCount = 1; //keep count so we can correctly filter heading and not have to many columns

            //do header record
            foreach (XmlNode childNode in data.FirstChild.ChildNodes[0])
            {
                var Rowtype = 1;

                try
                {
                    InsertCellInWorksheetV3(cellIndex, rowIndex, childNode.InnerText, 1U, worksheetPart, shareStringPart, Rowtype, data.FirstChild.ChildNodes[0].Attributes["rowHeight"].Value);
                }
                catch (Exception ex)
                {
                    InsertCellInWorksheetV3(cellIndex, rowIndex, childNode.InnerText, 1U, worksheetPart, shareStringPart, Rowtype, _database.GetSysSetting("ExcelExportRowHeight"));
                }

                if (cellCount < data.FirstChild.ChildNodes[0].ChildNodes.Count)
                {
                    cellIndex = IncrementColRefV3(cellIndex);
                    cellCount++;
                }
            }

            //filter first row
            var autoFilter = new AutoFilter
            {
                Reference = "A1:" + cellIndex + rowIndex
            };
            worksheetPart.Worksheet.Append(autoFilter);

            //do row records
            foreach (XmlNode childNode in data.FirstChild.ChildNodes[1])
            {
                rowIndex++;
                cellIndex = "A";

                var styleIndex = rowIndex % 2 == 0 ? 2U : 3U;

                foreach (XmlNode cells in childNode.ChildNodes)
                {
                    var RowType = 0;

                    try
                    {
                        InsertCellInWorksheetV3(cellIndex, rowIndex, cells.InnerText, styleIndex, worksheetPart, shareStringPart, RowType, childNode.Attributes["rowHeight"].Value);
                    }
                    catch (Exception ex)
                    {
                        InsertCellInWorksheetV3(cellIndex, rowIndex, cells.InnerText, styleIndex, worksheetPart, shareStringPart, RowType, _database.GetSysSetting("ExcelExportRowHeight"));
                    }
                    
                    cellIndex = IncrementColRefV3(cellIndex);
                }
            }
            #endregion

            spreadSheet.WorkbookPart.Workbook.Save();
            spreadSheet.Close();
        }
        #endregion

        #region IncrementColRefV3
        internal string IncrementColRefV3(string lastRef)
        {
            var characters = lastRef.ToUpperInvariant().ToCharArray();
            var sum = 0;

            foreach (var t in characters)
            {
                sum *= 26;
                sum += (t - 'A' + 1);
            }

            sum++;

            var columnName = String.Empty;

            while (sum > 0)
            {
                var modulo = (sum - 1) % 26;
                columnName = Convert.ToChar(65 + modulo) + columnName;
                sum = ((sum - modulo) / 26);
            }

            return columnName;
        }
        #endregion

        #region InsertCellInWorksheetV3
        internal void InsertCellInWorksheetV3(string columnName, uint rowIndex, string cellValue, uint styleIndex, WorksheetPart worksheetPart, SharedStringTablePart shareStringPart, int RowType,string rowHeight)
        {
            var worksheet = worksheetPart.Worksheet;
            var sheetData = worksheet.GetFirstChild<SheetData>();
            var cellReference = columnName + rowIndex;

            var row = new Row();

            if (sheetData.Elements<Row>().Count(r => r.RowIndex.Value == rowIndex) != 0)
            {
                row = sheetData.Elements<Row>().First(r => r.RowIndex.Value == rowIndex);
            }
            else
            {
                row.RowIndex = Convert.ToUInt32(rowIndex);
                sheetData.Append(row);
            }

            row.Height = (DoubleValue)Convert.ToDouble(rowHeight);
            row.CustomHeight = true;

            var cell = new Cell
            {
                StyleIndex = styleIndex
            };

            if (row.Elements<Cell>().Any(c => c.CellReference.Value == cellReference))
            {
                cell = row.Elements<Cell>().First(c => c.CellReference.Value == cellReference);
            }
            else
            {
                cell.CellReference = cellReference;
                row.Append(cell);
            }

            var index = InsertSharedStringItemV3(cellValue, shareStringPart);

            cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
            cell.CellValue = new CellValue(index.ToString());

            worksheet.Save();
        }
        #endregion

        #region InsertSharedStringItemV3
        // Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
        // and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
        internal int InsertSharedStringItemV3(string text, SharedStringTablePart shareStringPart)
        {
            // If the part does not contain a SharedStringTable, create one.
            if (shareStringPart.SharedStringTable == null)
            {
                shareStringPart.SharedStringTable = new SharedStringTable();
            }

            int i = 0;

            // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                {
                    return i;
                }

                i++;
            }

            // The text does not exist in the part. Create the SharedStringItem and return its index.
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text(text)));
            shareStringPart.SharedStringTable.Save();

            return i;
        }
        #endregion

        #region CreateStylesheet - New
        internal Stylesheet CreateStylesheetV3()
        {
            var stylesheet = new Stylesheet
            {
                MCAttributes = new MarkupCompatibilityAttributes
                {
                    Ignorable = "x14ac"
                }
            };
            stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");

            #region Fonts
            var fonts = new Fonts
            {
                Count = 2U,
                KnownFonts = true
            };

            var headerFont = new Font
            {
                FontSize = new FontSize
                {
                    Val = (DoubleValue)Convert.ToDouble(_database.GetSysSetting("ExcelExportHeaderFontHeight"))
                },
                Bold = new Bold()
                {
                    Val = true
                },
                Color = new Color
                {
                    Rgb = _database.GetSysSetting("ExcelExportHeaderFG")
                },
                FontName = new FontName
                {
                    Val = _database.GetSysSetting("ExcelExportHeaderFontStyle")
                }
            };

            var mainFont = new Font
            {
                FontSize = new FontSize
                {
                    Val = (DoubleValue)Convert.ToDouble(_database.GetSysSetting("ExcelExportRowFontHeight"))
                },
                Color = new Color
                {
                    Rgb = _database.GetSysSetting("ExcelExportCellFG")
                },
                FontName = new FontName
                {
                    Val = _database.GetSysSetting("ExcelExportRowFontStyle")
                }
            };

            fonts.Append(headerFont);
            fonts.Append(mainFont);
            #endregion

            #region Fills
            var fills = new Fills
            {
                Count = 5U
            };

            #region generalFill = 0, none
            var generalFill = new Fill();
            var generalFillPattern = new PatternFill
            {
                PatternType = PatternValues.None
            };
            generalFill.Append(generalFillPattern);
            #endregion

            #region grayFill = 1, none
            var grayFill = new Fill();
            var grayFillPattern = new PatternFill
            {
                PatternType = PatternValues.Gray125
            };
            grayFill.Append(grayFillPattern);
            #endregion

            #region headerFill = 2, Blue Header
            var headerFill = new Fill();
            var headerFillPattern = new PatternFill
            {
                PatternType = PatternValues.Solid
            };
            var headerForegroundColor = new ForegroundColor
            {
                Rgb = _database.GetSysSetting("ExcelExportHeaderBG")
            };
            headerFillPattern.Append(headerForegroundColor);
            headerFill.Append(headerFillPattern);
            #endregion

            #region rowFill = 3, Light blue row
            var rowFill = new Fill();
            var rowFillPattern = new PatternFill
            {
                PatternType = PatternValues.Solid
            };
            var rowForegroundColor = new ForegroundColor
            {
                Rgb = _database.GetSysSetting("ExcelExportRowBG")
            };
            var rowBackgroundColor = new BackgroundColor
            {
                Indexed = 64U
            };
            rowFillPattern.Append(rowForegroundColor);
            rowFillPattern.Append(rowBackgroundColor);
            rowFill.Append(rowFillPattern);
            #endregion

            #region alternateFill = 4, white alternate
            var alternateFill = new Fill();
            var alternateFillPattern = new PatternFill
            {
                PatternType = PatternValues.Solid
            };
            var alternateForegroundColor = new ForegroundColor
            {
                Rgb = _database.GetSysSetting("ExcelExportRowAlt")
            };
            var alternateBackgroundColor = new BackgroundColor
            {
                Indexed = 64U
            };
            alternateFillPattern.Append(alternateForegroundColor);
            alternateFillPattern.Append(alternateBackgroundColor);
            alternateFill.Append(alternateFillPattern);
            #endregion

            fills.Append(generalFill);
            fills.Append(grayFill);
            fills.Append(headerFill);
            fills.Append(rowFill);
            fills.Append(alternateFill);
            #endregion

            #region Borders
            var borders = new Borders
            {
                Count = 2U
            };

            var noBorder = new Border
            {
                LeftBorder = new LeftBorder(),
                RightBorder = new RightBorder(),
                TopBorder = new TopBorder(),
                BottomBorder = new BottomBorder(),
                DiagonalBorder = new DiagonalBorder()
            };

            var TotalBorder = new Border
            {
                LeftBorder = new LeftBorder()
                {
                    Style = BorderStyleValues.Thin,
                    Color = new Color
                    {
                        Rgb = _database.GetSysSetting("ExcelExportRowBottomBorder")
                    }
                },
                RightBorder = new RightBorder()
                {
                    Style = BorderStyleValues.Thin,
                    Color = new Color
                    {
                        Rgb = _database.GetSysSetting("ExcelExportRowBottomBorder")
                    }
                },
                TopBorder = new TopBorder()
                {
                    Style = BorderStyleValues.Thin,
                    Color = new Color
                    {
                        Rgb = _database.GetSysSetting("ExcelExportRowBottomBorder")
                    }
                },
                BottomBorder = new BottomBorder
                {
                    Style = BorderStyleValues.Thin,
                    Color = new Color
                    {
                        Rgb = _database.GetSysSetting("ExcelExportRowBottomBorder")
                    }
                },
                DiagonalBorder = new DiagonalBorder()
            };

            borders.Append(noBorder);
            borders.Append(TotalBorder);

            #endregion

            #region Cell Formatting

            #region CellStyleFormats
            var cellStyleFormats = new CellStyleFormats
            {
                Count = 1U
            };
            var cellFormat = new CellFormat
            {
                NumberFormatId = 0U,
                FontId = 1U,
                FillId = 0U,
                BorderId = 0U
            };

            cellStyleFormats.Append(cellFormat);
            #endregion

            #region CellFormats
            var cellFormats = new CellFormats
            {
                Count = 4U
            };
            var generalFormat = new CellFormat
            {
                NumberFormatId = 0U,
                FontId = 1U,
                FillId = 0U,
                BorderId = 0U,
                FormatId = 0U
            };
            var headerFormat = new CellFormat
            {
                NumberFormatId = 0U,
                FontId = 0U,
                FillId = 2U,
                BorderId = 0U,
                FormatId = 0U,
                ApplyFill = true
            };
            var rowFormat = new CellFormat
            {
                NumberFormatId = 0U,
                FontId = 1U,
                FillId = 3U,
                BorderId = 1U,
                FormatId = 0U,
                ApplyFill = true
            };
            var alternateFormat = new CellFormat
            {
                NumberFormatId = 0U,
                FontId = 1U,
                FillId = 4U,
                BorderId = 1U,
                FormatId = 0U,
                ApplyFill = true
            };

            cellFormats.Append(generalFormat);
            cellFormats.Append(headerFormat);
            cellFormats.Append(rowFormat);
            cellFormats.Append(alternateFormat);
            #endregion

            #region CellStyles
            var cellStyles = new CellStyles
            {
                Count = 1U
            };
            var cellStyle = new CellStyle
            {
                Name = "Normal",
                FormatId = 0U,
                BuiltinId = 0U
            };

            cellStyles.Append(cellStyle);
            #endregion
            #endregion

            stylesheet.Append(fonts);
            stylesheet.Append(fills);
            stylesheet.Append(borders);
            stylesheet.Append(cellStyleFormats);
            stylesheet.Append(cellFormats);
            stylesheet.Append(cellStyles);
            return stylesheet;
        }
        #endregion

        static void SetColumnWidth(Worksheet worksheet, uint Index, DoubleValue dwidth)
        {
            DocumentFormat.OpenXml.Spreadsheet.Columns cs = worksheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Columns>();

            cs = new DocumentFormat.OpenXml.Spreadsheet.Columns();
            DocumentFormat.OpenXml.Spreadsheet.Column c = new DocumentFormat.OpenXml.Spreadsheet.Column() { Min = Index, Max = Index, Width = dwidth, CustomWidth = true };
            cs.Append(c);
            worksheet.InsertAfter(cs, worksheet.GetFirstChild<SheetFormatProperties>());
        }
    }
}