using System.Collections.Generic;
using System.IO;
using System.Text.RegularExpressions;
using ExcelLibrary.SpreadSheet;
using MileageTraker.Web.Models;
using MileageTraker.Web.Utility;

namespace MileageTraker.Web.DAL
{
	public static class VehicleImporter
	{
		private const string WorksheetName = "Vehicles";
		private const string WorksheetTitle = "EAST TENNESSEE HUMAN RESOURCE AGENCY";

		public static void Export(IEnumerable<Vehicle> vehicles, string filename)
		{
			ExcelWriter<Vehicle>.WriteXls(vehicles, filename, WorksheetTitle, WorksheetName);
		}
		
		public static byte[] Export(IEnumerable<Vehicle> vehicles)
		{
			return ExcelWriter<Vehicle>.WriteXls(vehicles, WorksheetTitle, WorksheetName);
		}

		public static IEnumerable<City> ImportCities(string filename)
		{
			var fileInfo = new FileInfo(filename);
			return ImportCities(Workbook.Load(fileInfo.FullName));
		}

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

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

				var city = new City
				           	{
				           		County = row.GetCell(0).StringValue.Trim(), 
								Name = row.GetCell(1).StringValue.Trim()
				           	};

				yield return city;
			}
		}

		public static IEnumerable<Vehicle> Import(string filename)
		{
			var fileInfo = new FileInfo(filename);
			return Import(Workbook.Load(fileInfo.FullName));
		}

		private static IEnumerable<Vehicle> Import(Workbook workbook)
		{
			var sheet = workbook.Worksheets[0];
			var first = true;
			for (var r = sheet.Cells.FirstRowIndex; r <= sheet.Cells.LastRowIndex; r++)
			{
				var row = sheet.Cells.GetRow(r);
				if (row.LastColIndex != 11)
					continue;
				if (first)
				{
					first = false;
					continue;
				}

				var vehicle = new Vehicle();
				int key;
				if (!int.TryParse(row.GetCell(0).StringValue.Trim(), out key))
					continue; // WARN

				vehicle.Key = key;
				vehicle.VehicleId = row.GetCell(1).StringValue.Trim();
				vehicle.ModelYear = row.GetCell(2).StringValue.Trim();
				
				var makeModel = row.GetCell(3).StringValue.Trim();
				const string pattern = @"(\w+)\s+(.+)";
				var make = Regex.Replace(makeModel, pattern, "$1");
				var model = Regex.Replace(makeModel, pattern, "$2");
				vehicle.Make = make;
				vehicle.CarModel = model;

				vehicle.Color = row.GetCell(4).StringValue.Trim();
				if (vehicle.Color == "0")
					vehicle.Color = "Unknown";
				vehicle.Type = row.GetCell(5).StringValue.Trim();
				vehicle.Vin = row.GetCell(6).StringValue.Trim().Replace('O','0').Substring(0,17);
				vehicle.Price = decimal.Parse(row.GetCell(7).StringValue.Trim());
				vehicle.PurDate = row.GetCell(8).StringValue.Trim();
				vehicle.TagNumber = row.GetCell(9).StringValue.Trim();
				vehicle.Prog = row.GetCell(10).StringValue.Trim();
				vehicle.Assigned = row.GetCell(11).StringValue.Trim();
				vehicle.Assigned = Regex.Replace(vehicle.Assigned, @"(\w+),\s*(\w+)", "$2 $1");
				var match = Regex.Match(vehicle.Assigned, @"\s*\((?<Notes>.*)\)\s*");
				if (match.Success)
				{
					vehicle.Notes = match.Groups["Notes"].Value;
					vehicle.Assigned = Regex.Replace(vehicle.Assigned, @"\s*\(.*\)\s*", " ").Trim();
				}
				if (vehicle.Assigned == "Unassigned")
					vehicle.Assigned = null;

				yield return vehicle;
			}
		}
	}
}