﻿using System;
using System.Collections.Generic;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace BansheeGz.BGDatabase
{
    /// <summary>
    /// This class parse Excel file and return parsed data as array of strings
    /// It requires NPOI library to be moved to runtime assembly (move Assets\BansheeGz\BGDatabase\Editor\Libs\NPOI folder to any folder which is not under "Editor" folder) 
    /// </summary>
    public class BGLiveUpdateExcelParser : BGLiveUpdateDataSourceExcelExport.LiveUpdateExcelParser
    {
        public BGLiveUpdateDataSourceExcelExport.LiveUpdateExcelData Parse(byte[] data, bool useXml, BGMetaEntity[] metas)
        {
            IWorkbook book;
            using (var stream = new MemoryStream(data)) book = useXml ? (IWorkbook) new XSSFWorkbook(stream) : new HSSFWorkbook(stream);

            var result = new BGLiveUpdateDataSourceExcelExport.LiveUpdateExcelData();
            var sheets = new List<BGLiveUpdateDataSourceExcelExport.LiveUpdateExcelSheet>();
            for (var i = 0; i < book.NumberOfSheets; i++)
            {
                var sheet = book.GetSheetAt(i);
                var meta = GetMeta(metas, sheet.SheetName);
                if (meta == null) continue;

                var rowEnumerator = sheet.GetEnumerator();
                if (!rowEnumerator.MoveNext()) continue;

                //we only interested in columns, which have some values at first row
                var columnIndexes = new List<int>();
                var columnNames = new List<string>();
                GetValidColumns((IRow) rowEnumerator.Current, columnIndexes, columnNames);
                if (columnIndexes.Count == 0) continue;

                //read data
                var rowsData = new List<List<string>>();
                while (rowEnumerator.MoveNext())
                {
                    var row = (IRow) rowEnumerator.Current;

                    var rowData = new List<string>();
                    rowsData.Add(rowData);
                    for (var j = 0; j < columnIndexes.Count; j++)
                    {
                        var columnIndex = columnIndexes[j];
                        var cellValue = ReadAsString(row.GetCell(columnIndex));
                        rowData.Add(cellValue);
                    }
                }
                
                //fill result
                var cells = new string[rowsData.Count + 1, columnIndexes.Count];
                Fill(cells, 0, columnNames);
                for (var j = 0; j < rowsData.Count; j++) Fill(cells, j + 1, rowsData[j]);
                sheets.Add(new BGLiveUpdateDataSourceExcelExport.LiveUpdateExcelSheet
                {
                    Name = sheet.SheetName,
                    Data = cells
                });
            }
            result.sheets = sheets.ToArray(); 
            return result;
        }

        private static void Fill(string[,] cells, int row, List<string> values)
        {
            for (var i = 0; i < values.Count; i++) cells[row, i] = values[i];
        }

        //traverse first row and find columns which has some value
        private static void GetValidColumns(IRow row, List<int> columnIndexes, List<string> columnNames)
        {
            ForEachCell(row, (index, cell) =>
            {
                var value = ReadAsString(cell);
                if (string.IsNullOrEmpty(value)) return;
                
                columnIndexes.Add(index);
                columnNames.Add(value);
            });
        }

        private static BGMetaEntity GetMeta(BGMetaEntity[] metas, string name)
        {
            for (var i = 0; i < metas.Length; i++)
            {
                var meta = metas[i];
                if (string.Equals(meta.Name, name)) return meta;
            }

            return null;
        }

        private static string ReadAsString(ICell cell)
        {
            if (cell == null) return null;
            string result;

            switch (cell.CellType)
            {
                case CellType.Numeric:
                    result = "" + cell.NumericCellValue;
                    break;
                case CellType.String:
                    result = cell.StringCellValue;
                    if (result != null) result = result.Trim();
                    break;
                case CellType.Boolean:
                    result = cell.BooleanCellValue ? "1" : "0";
                    break;
                case CellType.Formula:
                    switch (cell.CachedFormulaResultType)
                    {
                        case CellType.Numeric:
                            result = "" + cell.NumericCellValue;
                            break;
                        case CellType.String:
                            result = cell.StringCellValue;
                            if (result != null) result = result.Trim();
                            break;
                        case CellType.Boolean:
                            result = cell.BooleanCellValue ? "1" : "0";
                            break;
                        default:
                            return null;
                    }

                    break;
                default:
                    return null;
            }

            return result;
        }

        private static void ForEachCell(IRow row, Action<int, ICell> action)
        {
            if (row == null) return;
            var cells = row.Cells;
            if (cells == null) return;

            for (var i = 0; i < cells.Count; i++)
            {
                var cell = cells[i];
                action(i, cell);
            }
        }
    }
}