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 GenerateXlsxFromXml
        /// <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 GenerateXlsxFromXml(MemoryStream ms, XmlDocument data)
        {
            var spreadSheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
            var workbookPart = spreadSheet.AddWorkbookPart();
            workbookPart.Workbook = new Workbook();

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

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

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

            var sheets = spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
            var sheet = new Sheet
            {
                Id = spreadSheet.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "Exported Data"
            };

            sheets.Append(sheet);

            #region SharedStringTablePart
            // Get the SharedStringTablePart. If it does not exist, create a new one.
            var shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0 ? 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])
            {
                InsertCellInWorksheet(cellIndex, rowIndex, childNode.InnerText, 1U, worksheetPart, shareStringPart);
                if(cellCount < data.FirstChild.ChildNodes[0].ChildNodes.Count)
                {
                    cellIndex = IncrementColRef(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)
                {
                    InsertCellInWorksheet(cellIndex, rowIndex, cells.InnerText, styleIndex, worksheetPart, shareStringPart);
                    cellIndex = IncrementColRef(cellIndex);
                }
            }
            #endregion

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

        #region IncrementColRef
        internal string IncrementColRef(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 InsertCellInWorksheet
        internal void InsertCellInWorksheet(string columnName, uint rowIndex, string cellValue, uint styleIndex, WorksheetPart worksheetPart, SharedStringTablePart shareStringPart)
        {
            var worksheet = worksheetPart.Worksheet;
            var sheetData = worksheet.GetFirstChild<SheetData>();
            var cellReference = columnName + rowIndex;

            var row = new Row();

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

            var cell = new Cell
            {
                StyleIndex = styleIndex
            };

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

            var index = InsertSharedStringItem(cellValue, shareStringPart);

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

            worksheet.Save();
        }
        #endregion

        #region InsertSharedStringItem
        // 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 InsertSharedStringItem(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
        internal Stylesheet CreateStylesheet()
        {
            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 = 9D
                },
                Bold = new Bold(),
                Color = new Color
                {
                    Rgb = _database.GetSysSetting("ExcelExportHeaderFG")
                },
                FontName = new FontName
                {
                    Val = "Calibri"
                },
                FontFamilyNumbering = new FontFamilyNumbering
                {
                    Val = 2
                },
                FontScheme = new FontScheme
                {
                    Val = FontSchemeValues.Minor
                }
            };

            var mainFont = new Font
            {
                FontSize = new FontSize
                {
                    Val = 9D
                },
                Color = new Color
                {
                    Rgb = _database.GetSysSetting("ExcelExportCellFG")
                },
                FontName = new FontName
                {
                    Val = "Calibri"
                },
                FontFamilyNumbering = new FontFamilyNumbering
                {
                    Val = 2
                },
                FontScheme = new FontScheme
                {
                    Val = FontSchemeValues.Minor
                }
            };

            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("ExcelExportHeaderFG")
            };
            var headerBackgroundColor = new BackgroundColor
            {
                Indexed = 64U
            };
            headerFillPattern.Append(headerForegroundColor);
            headerFillPattern.Append(headerBackgroundColor);
            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 bottomOnlyBorder = new Border
            {
                LeftBorder = new LeftBorder(),
                RightBorder = new RightBorder(),
                TopBorder = new TopBorder(),
                BottomBorder = new BottomBorder
                {
                    Style = BorderStyleValues.Thin,
                    Color = new Color
                    {
                        Rgb = _database.GetSysSetting("ExcelExportRowBottomBorder")
                    }
                },
                DiagonalBorder = new DiagonalBorder()
            };

            borders.Append(noBorder);
            borders.Append(bottomOnlyBorder);

            #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
    }
}