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

namespace Siger.Middlelayer.Utility.Helpers
{
    public class EpPlusForTpmHelper : IDisposable
    {
        private ExcelWorksheet sheet { get; set; }

        public void GenerateExcel(List<string> columns, string filePath)
        {
            var file = new FileInfo(filePath);
            var package = new ExcelPackage(file);
            var worksheet = package.Workbook.Worksheets.Add("sheet1");
            try
            {
                for (var i = 0; i < columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1].Value = columns[i];
                    worksheet.Column(i + 1).Width = 18;
                }

                worksheet.Cells.Style.ShrinkToFit = true;
                package.Save();
            }
            finally
            {
                package.Dispose();
            }
        }

        public List<string> CheckExcel(string filePath, int levelCount)
        {
            if (!File.Exists(filePath))
            {
                throw new Exception("excel file not found.");
            }
            var file = new FileInfo(filePath);
            var excelPackage = new ExcelPackage(file);
            if (excelPackage.Workbook == null || excelPackage.Workbook.Worksheets.Count <= 0)
            {
                throw new Exception("read excel failed.");
            }
            var messages = new List<string>();

            sheet = excelPackage.Workbook.Worksheets.First();
            var columnCount = sheet.Dimension.End.Column;

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

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

            for (var i = 2; i <= endRow; i++)
            {
                for (var j = 1; j <= columnCount; j++)
                {
                    if (j <= levelCount)
                    {
                        var value = sheet.Cells[i, j].Value;
                        if (string.IsNullOrEmpty(value?.ToString()))
                        {
                            messages.Add($"{i},{(int)ImportEnum.LevelSectionIsEmpty}");
                        }
                    }
                }
            }

            return messages;
        }

        public IEnumerable<LevelSectionEntity> ConvertSheetToList(int levelCount)
        {
            var list = new List<LevelSectionEntity>();
            if (sheet == null)
            {
                throw new Exception("read excel failed.");
            }
            var columnCount = sheet.Dimension.End.Column;

            var endRow = 2;
            for (var row = 2; row <= sheet.Dimension.Rows; row++)
            {
                var columnValues = new List<string>();
                for (var i = 1; i <= columnCount; 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;
            }

            for (var i = 2; i <= endRow; i++)
            {
                var entity = new LevelSectionEntity();
                for (var j = 1; j <= columnCount; j++)
                {
                    var value = sheet.Cells[i, j].Value?.ToString();
                    if (j <= levelCount)
                    {
                        entity.Levels.Add(value);
                    }
                }
                list.Add(entity);
            }

            return list;
        }

        public List<string> GetTemplateColumns(Type ty, int language = 0)
        {
            try
            {
                //add head
                bool ColumnOnly(CustomAttributeData y) => y.AttributeType == typeof(ExcelColumn);
                var columns = ty
                    .GetProperties()
                    .Where(x => x.CustomAttributes.Any(ColumnOnly)
                                && x.CustomAttributes.All(m => m.AttributeType != typeof(DonnetCreateAttribute)))
                    .Select(p => new
                    {
                        Property = p,
                        Column = (language == 0 ||
                                  !EpPlusLanguageHelper.LanguageDictionary.ContainsKey(
                                      p.GetCustomAttributes<ExcelColumn>().First().ColumnName))
                            ? p.GetCustomAttributes<ExcelColumn>().First().ColumnName
                            : EpPlusLanguageHelper.LanguageDictionary[
                                p.GetCustomAttributes<ExcelColumn>().First().ColumnName]
                    }).ToList();

                return columns.Select(t=>t.Column).ToList();
            }
            catch(Exception ex)
            {
                Logger.WriteLineDebug("GetTemplateColumns Error:" + ex);
                return new List<string>();
            }
        }

        public EpPlusForTpmHelper()
        {

        }

        public EpPlusForTpmHelper(string filePath, int language = 0)
        {
            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.");
                }
            }
        }

        public IEnumerable<LevelPlanTemplate> ConvertSheetToList(int levelCount, int language = 0)
        {
            if (sheet == null)
            {
                throw new ArgumentNullException(nameof(sheet));
            }
            bool ColumnOnly(CustomAttributeData y) => y.AttributeType == typeof(ExcelColumn);
            var columns = typeof(LevelPlanTemplate)
                .GetProperties()
                .Where(x => x.CustomAttributes.Any(ColumnOnly) && x.CustomAttributes.All(m => m.AttributeType != typeof(DonnetValidateAttribute)))
                .Select(p => new
                {
                    Property = p,
                    Column = (language == 0 || !EpPlusLanguageHelper.LanguageDictionary.ContainsKey(p.GetCustomAttributes<ExcelColumn>().First().ColumnName))
                        ? p.GetCustomAttributes<ExcelColumn>().First().ColumnName
                        : EpPlusLanguageHelper.LanguageDictionary[p.GetCustomAttributes<ExcelColumn>().First().ColumnName]
                }).ToList();

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

            var cloumnName = "";
            if (EpPlusLanguageHelper.LanguageDictionary.ContainsKey("产线结构") && language == 1)
            {
                cloumnName = EpPlusLanguageHelper.LanguageDictionary["产线结构"];
            }
            else
            {
                cloumnName = "产线结构";
            }

            IList<int> rows = new List<int>();
            for (var i = 2; i <= endRow; i++)
            {
                rows.Add(i);
            }
            var collection = rows
                .Select(row =>
                {
                    var sections = new List<string>();
                    for (var i = 1;i<=levelCount;i++)
                    {
                        sections.Add(sheet.Cells[row, i].GetValue<string>());
                    }
                    var j = levelCount - 1;
                    var tnew = new LevelPlanTemplate();
                    columns.ForEach(col =>
                    {
                        j++;
                        if (col.Column == cloumnName)
                        {
                            col.Property.SetValue(tnew, sections);
                        }
                        else
                        {
                            var val = sheet.Cells[row, j];
                            if (val.Value == null)
                            {
                                if (col.Property.PropertyType == typeof(int))
                                    col.Property.SetValue(tnew, 0);
                                if (col.Property.PropertyType == typeof(double))
                                    col.Property.SetValue(tnew, 0d);
                                if (col.Property.PropertyType == typeof(DateTime))
                                    col.Property.SetValue(tnew, DateTime.Now);
                                if (col.Property.PropertyType == typeof(string))
                                    col.Property.SetValue(tnew, string.Empty);

                                return;
                            }

                            if (col.Property.PropertyType == typeof(int))
                            {
                                col.Property.SetValue(tnew, val.GetValue<int>());
                                return;
                            }

                            if (col.Property.PropertyType == typeof(double))
                            {
                                col.Property.SetValue(tnew, val.GetValue<double>());
                                return;
                            }

                            if (col.Property.PropertyType == typeof(DateTime?))
                            {
                                col.Property.SetValue(tnew, val.GetValue<DateTime?>());
                                return;
                            }

                            if (col.Property.PropertyType == typeof(DateTime))
                            {
                                col.Property.SetValue(tnew, val.GetValue<DateTime>());
                                return;
                            }

                            if (col.Property.PropertyType == typeof(bool))
                            {
                                col.Property.SetValue(tnew, val.GetValue<bool>());
                                return;
                            }

                            col.Property.SetValue(tnew,
                                string.IsNullOrWhiteSpace(val.GetValue<string>()) ? "" : val.GetValue<string>());
                        }
                    });

                    return tnew;
                });
            return collection;
        }

        public IEnumerable<LevelPlanListSkf> ConvertSheetToListSkf(int language = 0)
        {
            if (sheet == null)
            {
                throw new ArgumentNullException(nameof(sheet));
            }
            bool ColumnOnly(CustomAttributeData y) => y.AttributeType == typeof(ExcelColumn);
            var columns = typeof(LevelPlanTemplateSkf)
                .GetProperties()
                .Where(x => x.CustomAttributes.Any(ColumnOnly) && x.CustomAttributes.All(m => m.AttributeType != typeof(DonnetValidateAttribute)))
                .Select(p => new
                {
                    Property = p,
                    Column = (language == 0 || !EpPlusLanguageHelper.LanguageDictionary.ContainsKey(p.GetCustomAttributes<ExcelColumn>().First().ColumnName))
                        ? p.GetCustomAttributes<ExcelColumn>().First().ColumnName
                        : EpPlusLanguageHelper.LanguageDictionary[p.GetCustomAttributes<ExcelColumn>().First().ColumnName]
                }).ToList();

            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 = rows
                .Select(row =>
                {
                    var tnew = new LevelPlanListSkf();
                    columns.ForEach(col =>
                    {
                        var val = sheet.Cells[row, GetColumnByName(col.Column)];
                        if (val.Value == null)
                        {
                            if (col.Property.PropertyType == typeof(int))
                                col.Property.SetValue(tnew, 0);
                            if (col.Property.PropertyType == typeof(double))
                                col.Property.SetValue(tnew, 0d);
                            if (col.Property.PropertyType == typeof(DateTime))
                                col.Property.SetValue(tnew, DateTime.Now);
                            if (col.Property.PropertyType == typeof(string))
                                col.Property.SetValue(tnew, string.Empty);

                            return;
                        }

                        if (col.Property.PropertyType == typeof(int))
                        {
                            col.Property.SetValue(tnew, val.GetValue<int>());
                            return;
                        }

                        if (col.Property.PropertyType == typeof(double))
                        {
                            col.Property.SetValue(tnew, val.GetValue<double>());
                            return;
                        }

                        if (col.Property.PropertyType == typeof(DateTime?))
                        {
                            col.Property.SetValue(tnew, val.GetValue<DateTime?>());
                            return;
                        }

                        if (col.Property.PropertyType == typeof(DateTime))
                        {
                            col.Property.SetValue(tnew, val.GetValue<DateTime>());
                            return;
                        }

                        if (col.Property.PropertyType == typeof(bool))
                        {
                            col.Property.SetValue(tnew, val.GetValue<bool>());
                            return;
                        }

                        col.Property.SetValue(tnew,
                            string.IsNullOrWhiteSpace(val.GetValue<string>()) ? "" : val.GetValue<string>());
                    });

                    return tnew;
                });
            return collection;
        }

        private int GetColumnByName(string columnName)
        {
            if (sheet == null)
            {
                throw new ArgumentNullException(nameof(sheet));
            }

            var column = sheet.Cells["1:1"].FirstOrDefault(c => c.Value.ToString() == columnName);
            if (column == null)
            {
                return 0;
            }
            return sheet.Cells["1:1"].First(c => c.Value.ToString() == columnName).Start.Column;
        }

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