using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using Siger.Middlelayer.Utility.ExcelImport;
using Siger.Middlelayer.Utility.ImportEntities;
using ExcelColumn = Siger.Middlelayer.Utility.ExcelImport.ExcelColumn;

namespace Siger.Middlelayer.Utility.Helpers
{
    public class EPPlusWmsExcelHelper<T> : IDisposable where T : ImportBase, new()
    {
        private ExcelWorksheet Sheet { get; }
        private readonly int _language = 0;

        public EPPlusWmsExcelHelper(int language = 0)
        {
            _language = language;
        }

        public EPPlusWmsExcelHelper(string filePath, int language = 0)
        {
            _language = language;
            if (File.Exists(filePath))
            {
                var file = new FileInfo(filePath);
                var excelPackage = new ExcelPackage(file);
                if (excelPackage.Workbook != null && excelPackage.Workbook.Worksheets.Count > 0)
                {
                    Sheet = excelPackage.Workbook.Worksheets.First();
                }
                else
                {
                    throw new Exception("read excel failed.");
                }
            }
        }

        /// <summary>
        /// convert excel sheet1 to list
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public IEnumerable<ImportStorageLocations> ConvertSheetToList(List<Tuple<int, string>> locationTypes)
        {
            if (Sheet == null)
            {
                throw new ArgumentNullException(nameof(Sheet));
            }
            var columns = new List<string> { "ID", "仓库名称" };

            foreach(var item in locationTypes)
            {
                columns.Add(item.Item2);
            }

            var endRow = 2;
            for (var row = 2; row <= Sheet.Dimension.Rows; row++)
            {
                var columnValues = new List<string>();
                for (var i = 1; i <= columns.Count; i++)
                {
                    var val = Sheet.Cells[row, i];
                    if (val != null)
                    {
                        columnValues.Add(val.Text);
                    }
                }

                if (columnValues.All(string.IsNullOrWhiteSpace))
                {
                    endRow = row - 1;
                    break;
                }

                endRow = row;
            }

            IList<int> rows = new List<int>();
            for (var i = 2; i <= endRow; i++)
            {
                rows.Add(i);
            }
            var collection = new List<ImportStorageLocations>();
            foreach(var row in rows)
            {
                var tnew = new ImportStorageLocations();
                int i = 1;
                foreach (var col in columns)
                {
                    var val = Sheet.Cells[row, i].Value.ToString();
                    if (i == 1)
                    {
                        tnew.ID = val;
                    }
                    if (i == 2)
                    {
                        tnew.StorageName = val;
                    }
                    if (i > 2)
                    {
                        var typeId = locationTypes.FirstOrDefault(q => q.Item2 == col);
                        tnew.Locations.Add(new LocationModels
                        {
                            LocationType = typeId == null ? 0 : typeId.Item1,
                            Location = val
                        });
                    }
                    i++;
                }
                collection.Add(tnew);
            }
            return collection;
        }

        public void Dispose()
        {
            Sheet?.Dispose();
        }
    }
}
