namespace Kit.Helpers
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text.RegularExpressions;
    using System.IO;
    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml.Packaging;
    using Microsoft.AspNetCore.Http;

    public class ExcelReader
    {
        private string GetColumnName(string cellReference)
        {
            var regex = new Regex("[A-Za-z]+");
            var match = regex.Match(cellReference);

            return match.Value;
        }

        private int ConvertColumnNameToNumber(string columnName)
        {
            var alpha = new Regex("^[A-Z]+$");
            if (!alpha.IsMatch(columnName)) throw new ArgumentException();

            char[] colLetters = columnName.ToCharArray();
            Array.Reverse(colLetters);

            var convertedValue = 0;
            for (int i = 0; i < colLetters.Length; i++)
            {
                char letter = colLetters[i];
                int current = i == 0 ? letter - 65 : letter - 64; // ASCII 'A' = 65
                convertedValue += current * (int)Math.Pow(26, i);
            }

            return convertedValue;
        }

        private IEnumerator<Cell> GetExcelCellEnumerator(Row row)
        {
            int currentCount = 0;
            foreach (Cell cell in row.Descendants<Cell>())
            {
                string columnName = GetColumnName(cell.CellReference);

                int currentColumnIndex = ConvertColumnNameToNumber(columnName);

                for (; currentCount < currentColumnIndex; currentCount++)
                {
                    var emptycell = new Cell() { DataType = null, CellValue = new CellValue(string.Empty) };
                    yield return emptycell;
                }

                yield return cell;
                currentCount++;
            }
        }

        private string ReadExcelCell(Cell cell, WorkbookPart workbookPart)
        {
            var cellValue = cell.CellValue;
            var text = (cellValue == null) ? cell.InnerText : cellValue.Text;
            if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
            {
                text = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(
                        Convert.ToInt32(cell.CellValue.Text)).InnerText;
            }

            return (text ?? string.Empty).Trim();
        }

        private PatternFill GetCellPatternFill(Cell cell, WorkbookPart workbookPart)
        {
            WorkbookStylesPart styles = workbookPart.WorkbookStylesPart;
            if (styles == null)
            {
                return null;
            }

            int cellStyleIndex;
            if (cell.StyleIndex == null) // I think (from testing) if the StyleIndex is null
            {                               // then this means use cell style index 0.
                cellStyleIndex = 0;           // However I did not found it in the open xml 
            }                               // specification.
            else
            {
                cellStyleIndex = (int)cell.StyleIndex.Value;
            }

            CellFormat cellFormat = (CellFormat)styles.Stylesheet.CellFormats.ChildElements[cellStyleIndex];

            Fill fill = (Fill)styles.Stylesheet.Fills.ChildElements[(int)cellFormat.FillId.Value];
            return fill.PatternFill;
        }

        protected ExcelData ReadSheet(WorkbookPart workbookPart, Sheet sheet)
        {
            var data = new ExcelData();
            List<Row> rows;
            try
            {
                data.SheetName = sheet.Name;

                var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
                var workSheet = worksheetPart.Worksheet;
                var columns = workSheet.Descendants<Columns>().FirstOrDefault();
                data.ColumnConfigurations = columns;

                var comments = GetComments(worksheetPart);
                var sheetData = workSheet.Elements<SheetData>().First();

                this.DeleteBlankRows(sheetData);

                rows = sheetData.Elements<Row>().ToList();

                //if (rows.Count > 0)
                //{
                //    var row = rows[0];
                //    var cellEnumerator = GetExcelCellEnumerator(row);
                //    while (cellEnumerator.MoveNext())
                //    {
                //        var cell = cellEnumerator.Current;
                //        var text = ReadExcelCell(cell, workbookPart).Trim();
                //        data.Headers.Add(text);
                //    }
                //}

                // Read the sheet data
                if (rows.Count > 0)
                {
                    for (var i = 0; i < rows.Count; i++)
                    {
                        var cellRow = new  ExcelRow();
                        var dataRow = new List<string>();
                        var patternFillRow = new List<PatternFill>();
                        var row = rows[i];
                        var cellEnumerator = GetExcelCellEnumerator(row);
                        int z = 0;
                        while (cellEnumerator.MoveNext())
                        {
                            var cell = cellEnumerator.Current;
                            var text = ReadExcelCell(cell, workbookPart).Trim();
                            var comment = comments.FirstOrDefault(x => x.Reference == cell.CellReference);
                            var patternFill = GetCellPatternFill(cell, workbookPart);
                            if (patternFill != null)
                            {
                                patternFillRow.Add(patternFill);
                            }

                            dataRow.Add(text);
                            cellRow.Cells.Add(new ExcelCell
                            {
                                IsHeader = i == 0,
                                Comment = comment,
                                Header = (i == 0 ? text : z < data.Headers.Count() ? data.Headers[z] : string.Empty),
                                PatternFill = patternFill,
                                Value = text
                            });
                            z++;
                        }
                        data.DataRowCells.Add(cellRow);
                        if (i == 0)
                        {
                            data.Headers = dataRow;
                        }
                        else
                        {
                            data.DataRows.Add(dataRow);
                            data.PatternFillRows.Add(patternFillRow);
                        }
                    }
                }

                // Read the sheet PatternFill
                //if (rows.Count > 1)
                //{
                //    for (var i = 1; i < rows.Count; i++)
                //    {
                //        var dataRow = new List<PatternFill>();
                //        data.PatternFillRows.Add(dataRow);
                //        var row = rows[i];
                //        var cellEnumerator = GetExcelCellEnumerator(row);
                //        while (cellEnumerator.MoveNext())
                //        {
                //            var cell = cellEnumerator.Current;
                //            var patternFill = GetCellPatternFill(cell, workbookPart);
                //            dataRow.Add(patternFill);
                //        }
                //    }
                //}

                return data;
            }
            catch (Exception e)
            {
                data.Status.Message = "Unable to open the file";
                return data;
            }
        }


        protected IEnumerable<ExcelData> ReadAllExcel(Stream stream)
        {
            var datas = new List<ExcelData>();
            WorkbookPart workbookPart; List<Row> rows;
            try
            {
                var document = SpreadsheetDocument.Open(stream, false);
                workbookPart = document.WorkbookPart;

                var sheets = workbookPart.Workbook.Descendants<Sheet>();

                foreach (var sheet in sheets)
                {
                    var data = ReadSheet(workbookPart, sheet);
                    datas.Add(data);
                }
            }
            catch (Exception e)
            {
                datas.First().Status.Message = "Unable to open the file";
                return datas;
            }

            return datas;

        }
        public IEnumerable<ExcelData> ReadAllExcel(IFormFile file)
        {
            var datas = new List<ExcelData>();
            // Check if the file is excel
            if (file.Length <= 0)
            {
                datas.First().Status.Message = "You uploaded an empty file";
                return datas;
            }

            if (file.ContentType != "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            {
                datas.First().Status.Message = "Please upload a valid excel file of version 2007 and above";
                return datas;
            }

            using (Stream stream = file.OpenReadStream())
            {
                return ReadAllExcel(stream);
            }
            // Open the excel document
            //WorkbookPart workbookPart; List<Row> rows;
            //try
            //{
            //    var document = SpreadsheetDocument.Open(file.InputStream, false);
            //    workbookPart = document.WorkbookPart;

            //    var sheets = workbookPart.Workbook.Descendants<Sheet>();

            //    foreach (var sheet in sheets)
            //    {
            //        var data = new ExcelData();
            //        data.SheetName = sheet.Name;

            //        var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
            //        var workSheet = worksheetPart.Worksheet;
            //        var columns = workSheet.Descendants<Columns>().FirstOrDefault();
            //        data.ColumnConfigurations = columns;

            //        var comments = GetComments(worksheetPart);
            //        var sheetData = workSheet.Elements<SheetData>().First();

            //        rows = sheetData.Elements<Row>().ToList();

            //        if (rows.Count > 0)
            //        {
            //            var row = rows[0];
            //            var cellEnumerator = GetExcelCellEnumerator(row);
            //            while (cellEnumerator.MoveNext())
            //            {
            //                var cell = cellEnumerator.Current;
            //                var text = ReadExcelCell(cell, workbookPart).Trim();
            //                data.Headers.Add(text);
            //            }
            //        }

            //        // Read the sheet data
            //        if (rows.Count > 1)
            //        {
            //            for (var i = 1; i < rows.Count; i++)
            //            {
            //                var dataRow = new List<string>();
            //                data.DataRows.Add(dataRow);
            //                var row = rows[i];
            //                var cellEnumerator = GetExcelCellEnumerator(row);
            //                while (cellEnumerator.MoveNext())
            //                {
            //                    var cell = cellEnumerator.Current;
            //                    var text = ReadExcelCell(cell, workbookPart).Trim();
            //                    var patternFill = GetCellPatternFill(cell, workbookPart);

            //                    dataRow.Add(text);
            //                }
            //            }
            //        }

            //        // Read the sheet PatternFill
            //        if (rows.Count > 1)
            //        {
            //            for (var i = 1; i < rows.Count; i++)
            //            {
            //                var dataRow = new List<PatternFill>();
            //                data.PatternFillRows.Add(dataRow);
            //                var row = rows[i];
            //                var cellEnumerator = GetExcelCellEnumerator(row);
            //                while (cellEnumerator.MoveNext())
            //                {
            //                    var cell = cellEnumerator.Current;
            //                    var patternFill = GetCellPatternFill(cell, workbookPart);
            //                    dataRow.Add(patternFill);
            //                }
            //            }
            //        }

            //        datas.Add(data);
            //    }


            //}
            //catch (Exception e)
            //{
            //    datas.First().Status.Message = "Unable to open the file";
            //    return datas;
            //}

            return datas;

        }


        public ExcelData ReadExcel(IFormFile file)
        {
            var data = new ExcelData();

            // Check if the file is excel
            if (file.Length <= 0)
            {
                data.Status.Message = "You uploaded an empty file";
                return data;
            }

            if (file.ContentType != "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            {
                data.Status.Message = "Please upload a valid excel file of version 2007 and above";
                return data;
            }

            // Open the excel document
            WorkbookPart workbookPart; List<Row> rows;
            try
            {
                using (Stream stream = file.OpenReadStream())
                {
                    return ReadExcel(stream);
                }
            }
            catch (Exception e)
            {
                data.Status.Message = "Unable to open the file";
                return data;
            }
        }

        public ExcelData ReadExcel(Stream file,string sheetName)
        {
            var data = new ExcelData();

            // Open the excel document
            WorkbookPart workbookPart; List<Row> rows;
            try
            {
                var document = SpreadsheetDocument.Open(file, false);
                workbookPart = document.WorkbookPart;

                var sheets = workbookPart.Workbook.Descendants<Sheet>();
                var sheet = sheets.FirstOrDefault(x => x.Name == sheetName);
                data = ReadSheet(workbookPart, sheet);

                return data;
            }
            catch (Exception e)
            {
                data.Status.Message = "Unable to open the file";
                return data;
            }
        }

        public void DeleteBlankRows(SheetData sheet)
        {

            var rows = sheet.Elements<Row>().ToList();
            for (var rowIndex = 0; rowIndex < rows.Count; rowIndex++)
            {
                var row = rows[rowIndex];
                if (string.IsNullOrWhiteSpace(row.InnerText)) row.Remove();
            }
        }

        public ExcelData ReadExcel(Stream file)
        {
            var data = new ExcelData();

            // Open the excel document
            WorkbookPart workbookPart; List<Row> rows;
            try
            {
                var document = SpreadsheetDocument.Open(file, false);
                workbookPart = document.WorkbookPart;

                var sheets = workbookPart.Workbook.Descendants<Sheet>();
                var sheet = sheets.FirstOrDefault();

                data = ReadSheet(workbookPart, sheet);

                return data;
            }
            catch (Exception e)
            {
                data.Status.Message = "Unable to open the file";
                return data;
            }
        }
        public IEnumerable<ExcelComment> GetComments(WorksheetPart sheet)
        {
            var result = new List<ExcelComment>();
            foreach (WorksheetCommentsPart commentsPart in sheet.GetPartsOfType<WorksheetCommentsPart>())
            {
                foreach (Comment comment in commentsPart.Comments.CommentList)
                {
                    var newComment = new ExcelComment
                    {
                        Reference = comment.Reference,
                        Comment = comment.FirstChild.InnerText.Trim()
                    };
                    result.Add(newComment);
                }
            }

            return result;
        }
    }
}