using System;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Windows;
using System.Xml.Linq;
using System.Globalization;
using C1.C1Zip;
using C1.Silverlight.FlexGrid;

namespace C1.Silverlight.FlexGridExcel
{
    /// <summary>
    /// Class used to create Excel XLSX files/streams.
    /// </summary>
    public class ExcelEngine
    {
        //-------------------------------------------------------------------------
        #region ** fields

        public const string TEMPLATE_FILE_NAME = "template.zip";
        public const string SHEET_NAME = "Sheet1";

        #endregion

        //-------------------------------------------------------------------------
        #region ** object model

        /// <summary>
        /// Saves a <see cref="C1FlexGrid"/> to an XLSX stream.
        /// </summary>
        /// <param name="flex"><see cref="C1FlexGrid"/> to save.</param>
        /// <param name="stream">Output stream.</param>
        public static void SaveXlsxFile(C1FlexGrid flex, Stream stream)
        {
            var zipStream = GetZipStream(TEMPLATE_FILE_NAME);
            var book = new XLWorkbook(zipStream, SHEET_NAME);
            SaveContent(flex, book);
            book.Save(stream);
        }

        /// <summary>
        /// Loads the content of an XLSX stream into a <see cref="C1FlexGrid"/> instance.
        /// </summary>
        /// <param name="flex"><see cref="C1FlexGrid"/> to load.</param>
        /// <param name="stream">Input stream.</param>
        /// <param name="sheetName">Name of the sheet to load.</param>
        /// <param name="headersInFirstRow">Whether to load sheet headers in the first row.</param>
        public static void LoadXlsxFile(C1FlexGrid flex, Stream stream, string sheetName, bool headersInFirstRow)
        {
            var book = new XLWorkbook(stream, sheetName);
            LoadFlexGrid(book, flex, headersInFirstRow);
        }

        /// <summary>
        /// Saves a <see cref="C1FlexGrid"/> to a CSV stream.
        /// </summary>
        /// <param name="flex"><see cref="C1FlexGrid"/> to save.</param>
        /// <param name="stream">Output stream.</param>
        public static void SaveCsvFile(C1FlexGrid flex, Stream stream)
        {
            var zipStream = GetZipStream(TEMPLATE_FILE_NAME);
            var book = new XLWorkbook(zipStream, SHEET_NAME);
            SaveContent(flex, book);
            using (var sw = new System.IO.StreamWriter(stream))
            {
                foreach (var row in book.Sheet.Rows)
                {
                    bool firstCellInRow = true;
                    foreach (var cell in row.Cells)
                    {
                        if (!firstCellInRow)
                        {
                            sw.Write(",");
                        }
                        var txt = GetCellText(book, cell);
                        txt = txt.Replace("\"", "\"\"");
                        if (txt.IndexOf(',') > -1 || txt.IndexOf('\"') > -1)
                        {
                            sw.Write("\"{0}\"", txt);
                        }
                        else
                        {
                            sw.Write("{0}", txt);
                        }
                        firstCellInRow = false;
                    }
                    sw.WriteLine();
                }
            }
        }
        static string GetCellText(XLWorkbook book, XLCell cell)
        {
            // get raw value as text
            var txt = cell.Value != null ? cell.Value.ToString() : string.Empty;

            // get cell format
            var s = cell.Style;
            XLCellFormat fmt;
            if (s.HasValue && book.StyleSheet.TryGetValue(s.Value, out fmt))
            {
                // get number format
                XLNumberFormat nf;
                if (fmt.ApplyNumberFormat && fmt.NumberFormatId.HasValue && book.StyleSheet.TryGetValue(fmt.NumberFormatId.Value, out nf))
                {
                    // apply number format
                    decimal d;
                    var ci = CultureInfo.InvariantCulture;
                    if (decimal.TryParse(txt, NumberStyles.Any, ci, out d))
                    {
                        try
                        {
                            txt = d.ToString(nf.FormatCode, ci);
                        }
                        catch { }
                    }
                }
            }

            // done
            return txt;
        }

        #endregion

        //-------------------------------------------------------------------------
        #region ** implementation

        // get the zip stream that contains the workbook template
        static Stream GetZipStream(string fileName)
        {
            var asm = Assembly.GetExecutingAssembly();
            var resName = asm.GetManifestResourceNames().First((name) => name.Contains(fileName));
            return asm.GetManifestResourceStream(resName);
        }

        // save grid content into workbook
        static void SaveContent(C1FlexGrid flex, XLWorkbook book)
        {
            // get export sheet
            var exportSheet = book.Sheet;
            exportSheet.SummaryBelow = (flex.GroupRowPosition == GroupRowPosition.BelowData);

            // create sheet columns
            foreach (var col in flex.ColumnHeaders.Columns)
            {
                var excelColumn = exportSheet.AddColumn(col.ActualWidth);

                // create and set number format for the column
                if (!string.IsNullOrEmpty(col.Format))
                {
                    XLCellFormat cellFmt = GetCellFormat(book.StyleSheet, col.DataType, col.Format, false);
                    excelColumn.Style = book.StyleSheet.AddValue(cellFmt);
                }
            }

            // add column headers: REVIEW: make this optional?
#if FALSE
            {
                for (int i = 0; i < flex.ColumnHeaders.Rows.Count; i++)
                {
                    var columnHeadersRow = exportSheet.AddRow();
                    for (int j = 0; j < flex.ColumnHeaders.Columns.Count; j++)
                    {
                        string colHeader = flex.ColumnHeaders[i, j] != null
                            ? flex.ColumnHeaders[i, j].ToString()
                            : flex.Columns[j].ColumnName;
                        var cell = columnHeadersRow.AddCell(colHeader);
                        cell.Bold = true;
                    }
                }
            }
#endif

            // create sheet rows
            SaveRows(flex, book);
        }

        // save grid rows to sheet
        static void SaveRows(C1FlexGrid flex, XLWorkbook book)
        {
            // loop through all rows saving their data
            int maxOutlineLevel = GetMaxOutlineLevel(flex);
            bool isGrouped = maxOutlineLevel > -1;
            bool summaryBelow = flex.GroupRowPosition == GroupRowPosition.BelowData;
            for (int i = 0; i < flex.Rows.Count; i++)
            {
                // get row data
                var row = flex.Rows[i];
                var groupRow = row as GroupRow;
                var excelRow = book.Sheet.AddRow();
                if (groupRow != null)
                {
                    excelRow.OutlineLevel = groupRow.Level;
                }
                else if (isGrouped && row is BottomGroupRow)
                {
                    excelRow.OutlineLevel = ((BottomGroupRow)row).Level + 1;
                }
                else if (isGrouped && !(row is HeaderRow))
                {
                    excelRow.OutlineLevel = maxOutlineLevel + 1;
                }

                // row is bold if:
                // 1) Row.FontWeight is set to bold, OR
                // 2) Row is a group row AND we have no child items path
                var bold = row.FontWeight.HasValue && row.FontWeight.Value == FontWeights.Bold;
                bold |= groupRow != null && string.IsNullOrEmpty(flex.ChildItemsPath);
                bold |= row is BottomGroupRow;

                // flat list rows are bold if they have children
                var flr = row as FlatListRow;
                if (flr != null)
                {
                    bold = HasFlatListChildRows(flex, row);
                }

                // save cells in this row
                var firstColumn = true;
                for (int j = 0; j < flex.Columns.Count; j++)
                {
                    // get column
                    var col = flex.Columns[j];

                    // get cell value
                    var cellValue = flex[i, j];

                    // get cell format
                    var cellFormat = col.Format;
                    if (string.IsNullOrEmpty(cellFormat) && col.Binding != null)
                    {
                        cellFormat = col.Binding.StringFormat;
                    }

                    // try converting to a number 
                    // (in case the grid has a value converter that changes numbers into strings...)
                    if (cellValue is string)
                    {
                        var dataType = flex.Columns[j].DataType;
                        if (dataType == null || dataType == typeof(object))
                        {
                            double dbl;
                            if (double.TryParse(cellValue as string, NumberStyles.Any, CultureInfo.InvariantCulture, out dbl))
                            {
                                // guess the cell format
                                if (cellFormat == null)
                                {
                                    cellFormat = BuildFormat(cellValue as string);
                                }

                                // save cell value
                                cellValue = dbl;
                            }
                        }
                    }

                    // special handling for group headers
                    if (cellValue == null && firstColumn && groupRow != null)
                    {
                        cellValue = flex.GroupHeaderConverter.Convert(null, typeof(string), groupRow, System.Globalization.CultureInfo.CurrentUICulture);
                    }

                    // clear values from top group row if we have bottom group rows
                    //if (cellValue != null && !firstColumn && groupRow != null && groupRow.Level == 0 && row is FlatListRow) 
                    if (cellValue != null && !firstColumn && groupRow != null && HasFlatListChildRows(flex, row))
                    {
                        cellValue = null;
                    }
                    
                    // save cell type and value, date values need to be converted to OLEAutomation Date
                    object value = cellValue;
                    if (cellValue is DateTime)
                    {
                        try
                        {
                            value = ((DateTime)cellValue).ToOADate();
                        }
                        catch { }
                    }
                    XLCell cell = excelRow.AddCell();
                    cell.Value = value;

                    // save cell format
                    XLCellFormat cellFmt = GetCellFormat(book.StyleSheet, col.DataType, cellFormat, bold);
                    cell.Style = book.StyleSheet.AddValue(cellFmt);

                    // keep track of first visible column
                    if (firstColumn && col.ActualWidth > 0)
                    {
                        firstColumn = false;
                    }
                }
            }
        }
        static bool HasFlatListChildRows(C1FlexGrid flex, Row row)
        {
            var rthis = row as FlatListRow;
            var rnext = row.Index + 1 < flex.Rows.Count ? flex.Rows[row.Index + 1] as FlatListRow : null;
            return rthis != null && rnext != null && rnext.Level > rthis.Level;
        }
        static string BuildFormat(string value)
        {
            // cell format
            var fmt = "0";

            // add number of decimals
            var match = System.Text.RegularExpressions.Regex.Match(value, @"\.[0-9]+");
            if (match.Success)
            {
                var decimals = match.Captures[0].Length - 1;
                fmt += "." + new string('0', decimals);
            }

            // add thousand separator
            bool thouSep = value.IndexOf(',') > -1;
            if (thouSep)
            {
                fmt = "#,##" + fmt;
            }

            // add parenthesis for negative values
            if (value.IndexOf('(') > -1)
            {
                fmt = string.Format("{0};({0})", fmt);
            }

            // done
            return fmt;
        }
        static int GetMaxOutlineLevel(C1FlexGrid flex)
        {
            int outlineLevel = -1;
            for (int i = 0; i < flex.Rows.Count; i++)
            {
                var groupRow = flex.Rows[i] as GroupRow;
                if (groupRow != null)
                {
                    outlineLevel = Math.Max(outlineLevel, groupRow.Level);
                }
            }
            return outlineLevel;
        }

        static XLCellFormat GetCellFormat(XLStyles styleSheet, Type dataType, string numberFormat, bool bold)
        {
            // create cell format
            XLCellFormat cellFmt = new XLCellFormat();

            // create number format
            if (!string.IsNullOrEmpty(numberFormat))
            {
                XLNumberFormat numFmt = new XLNumberFormat();
                numFmt.SetDotNetFormat(numberFormat, dataType);
                int numFmtId = styleSheet.AddValue(numFmt);
                cellFmt.ApplyNumberFormat = true;
                cellFmt.NumberFormatId = numFmtId;
            }

            // create default font
            XLFont font = new XLFont();
            font.Bold = bold;
            int fontId = styleSheet.AddValue(font);
            cellFmt.ApplyFont = true;
            cellFmt.FontId = fontId;

            return cellFmt;
        }

        static XDocument LoadDocument(C1ZipFile zipFile, string fileName)
        {
            XDocument loadedDoc = null;
            foreach (C1ZipEntry entry in zipFile.Entries)
            {
                if (entry.FileName.EndsWith(fileName))
                {
                    using (var docStream = entry.OpenReader())
                    {
                        if (docStream != null)
                        {
                            loadedDoc = XDocument.Load(docStream);
                        }
                    }
                    break;
                }
            }
            return loadedDoc;
        }

        private static void LoadFlexGrid(XLWorkbook book, C1FlexGrid flex, bool headersInFirstRow)
        {
            // clear flex grid
            flex.ItemsSource = null;

            // get column definitions
            foreach (var col in book.Sheet.Columns)
            {
                flex.Columns.Add(CreateFlexColumn(col, book.StyleSheet));
            }

            // iterate through rows
            int colIndex = 0;
            int rowIndex = 0;
            foreach (var row in book.Sheet.Rows)
            {
                flex.Rows.Add(CreateFlexRow(row));
                foreach (var cell in row.Cells)
                {
                    // add extra empty column if needed (data columns > defined columns
                    if (flex.Columns.Count <= colIndex)
                    {
                        flex.Columns.Add(new Column());
                    }

                    // use first row to load column headers
                    if (headersInFirstRow && rowIndex == 0)
                    {
                        flex.Columns[colIndex].Header = GetValue(cell, book).ToString();
                    }
                    else
                    {
                        // check for empty column format
                        string cellFormat = GetNumberFormat(cell.Style, book.StyleSheet);
                        if (!string.IsNullOrEmpty(cellFormat) && string.IsNullOrEmpty(flex.Columns[colIndex].Format))
                        {
                            flex.Columns[colIndex].Format = cellFormat;
                        }
                        flex[rowIndex, colIndex] = GetValue(cell, book);
                    }
                    colIndex++;
                }
                rowIndex++;
                colIndex = 0;
            }

            // remove unused first row
            if (headersInFirstRow)
            {
                flex.Rows.RemoveAt(0);
            }
        }

        // Creates a column from its xlsx definition element
        private static Column CreateFlexColumn(XLColumn col, XLStyles styleSheet)
        {
            Column newCol = new Column();
            if (col.Width.HasValue)
            {
                // points to pixels
                var width = col.Width.Value / 72.0 * 96.0;
                newCol.Width = new GridLength(width);
            }
            newCol.Format = GetNumberFormat(col.Style, styleSheet);
            return newCol;
        }

        // Creates a row from its xlsx definition element
        private static Row CreateFlexRow(XLRow row)
        {
            Row newRow = new Row();
            if (row.Height.HasValue)
            {
                // points to pixels
                newRow.Height = row.Height.Value / 72.0 * 96.0;
            }

            // get visibility
            if (row.Hidden)
            {
                newRow.Visible = false;
            }

            // done
            return newRow;
        }

        // Gets the value of the cell according to the column type
        private static object GetValue(XLCell cell, XLWorkbook workbook)
        {
            if (cell.IsSharedString)
            {
                string stringValue = string.Empty;
                workbook.SharedStrings.TryGetValue(int.Parse(cell.Value.ToString(), CultureInfo.InvariantCulture), out stringValue);
                return stringValue;
            }

            // Get the cell format and check if is a date value
            string cellNumFmt = GetNumberFormat(cell.Style, workbook.StyleSheet);
            if (!string.IsNullOrEmpty(cellNumFmt))
            {
                // First check for implicit date formats, values between 14 and 22 correspond to date formats.
                // Then, check for explicit date formats.
                int implicitNumFmt;
                if ((int.TryParse(cellNumFmt, out implicitNumFmt) && implicitNumFmt >= 14 && implicitNumFmt <= 22) ||
                    cellNumFmt.Contains("m") || cellNumFmt.Contains("d") || cellNumFmt.Contains("yy"))
                {
                    double dateValue;
                    if (double.TryParse(cell.Value.ToString(), out dateValue))
                    {
                        try
                        {
                            return DateTime.FromOADate(dateValue);
                        }
                        catch { }
                    }
                }
            }

            // done
            return cell.Value;
        }

        // Gets the number format for the given index
        private static string GetNumberFormat(int? numFmtId, XLStyles styleSheet)
        {
            if (styleSheet != null)
            {
                XLCellFormat cellFormat;
                if ((numFmtId.HasValue) && (styleSheet.TryGetValue(numFmtId.Value, out cellFormat)))
                {
                    XLNumberFormat numberFormat;
                    if (cellFormat.NumberFormatId.HasValue && styleSheet.TryGetValue(cellFormat.NumberFormatId.Value, out numberFormat))
                    {
                        return numberFormat.GetDotNetFormat();
                    }
                }
            }
            return string.Empty;
        }

        #endregion
    }
}
