using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using ExcelLibrary.SpreadSheet;
using MileageTraker.Web.Utility;
using MileageTraker.Web.ViewModels.Log;

namespace MileageTraker.Web.DAL
{
	public static class LogImporter
	{
		public static IEnumerable<ImportLogViewModel> Import(string filename)
		{
			var fileInfo = new FileInfo(filename);
			return Import(Workbook.Load(fileInfo.FullName));
		}

		private static IList<string> GetMissingHeaderColumns(IEnumerable<string> headerColumns)
		{
			var properties = typeof(ImportLogViewModel).GetPropertyNames();
			var pset = new HashSet<string>(properties);
			var cset = new HashSet<string>(headerColumns);
			return pset.Except(cset).ToList();
		}

		private static IEnumerable<ImportLogViewModel> Import(Workbook workbook)
		{
			var sheet = workbook.Worksheets[0];

			// get column header -> index
			const int columnCount = 8;
			var columnIndex = GetColumnIndex(sheet.Cells.GetRow(0), columnCount);

			var missingHeaderColumns = GetMissingHeaderColumns(columnIndex.Keys);
			if (missingHeaderColumns.Any())
			{
				throw new ImportException(string.Format("Excel document missing columns: {0}", string.Join(", ", missingHeaderColumns)));
			}

			for (var r = 1; r <= sheet.Cells.LastRowIndex; r++)
			{
				var row = sheet.Cells.GetRow(r);

				var emptyCells =
					Enumerable.Range(0, columnCount)
				          .Select(i => row.GetCell(i).StringValue)
				          .Count(s => s == "");

				if (emptyCells > columnCount - 2)
					continue;

				Func<string, string> getRowValue = i => row.GetCell(columnIndex[i]).StringValue.Trim();
				Func<string, string> getRowDateValue = i =>
					{
						var cell = row.GetCell(columnIndex[i]);
						if (cell.IsEmpty || cell.StringValue == "")
							return null;
						var dateTimeValue = cell.DateTimeValue;
						return dateTimeValue.ToShortDateString();
					};

				ImportLogViewModel logImportViewModel;

				try
				{
					logImportViewModel = new ImportLogViewModel
					{
						CityName = getRowValue("Destination City"),
						Date = getRowDateValue("Date"),
						EndOdometer = getRowValue("End Odometer"),
						GasPurchased = getRowValue("Gas Purchased"),
						LogType = getRowValue("Type"),
						Notes = getRowValue("Notes"),
						Purpose = getRowValue("Purpose"),
						VehicleId = getRowValue("Vehicle ID")
					};
				}
				catch (Exception ex)
				{
					throw new ImportException(ex.Message + " on row " + (r + 1));
				}
				yield return logImportViewModel;
			}
		}

		private static IDictionary<string, int> GetColumnIndex(Row headerRow, int columnCount)
		{
			var dictionary = new Dictionary<string, int>(columnCount);
			for (var i = 0; i < columnCount; i++)
			{
				var title = headerRow.GetCell(i).StringValue.Trim();
				if (string.IsNullOrEmpty(title))
					continue;
					//throw new LogImportError("Missing column in the excel document");
				dictionary.Add(title, i);
			}
			return dictionary;
		}
	}
}