using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.Text;
using System.Web;

namespace DebriefingSoftware.Common.Business.Filtering
{
	[Serializable]
	[DataContract]
	public class Filter
	{
		public const string FilterPrefix = "FLTR$";

		private List<FilterParameter> _parameters = new List<FilterParameter>();

		private List<FilterParameter> _urlParameters = new List<FilterParameter>();

		public List<FilterParameter> Parameters
		{
			get { return this._parameters; }
		}

		public List<FilterParameter> UrlParameters
		{
			get { return this._urlParameters; }
		}

		public Filter()
		{
		}

		public void Clear()
		{
			this._parameters = new List<FilterParameter>();
		}

		public void ClearUrl()
		{
			this._urlParameters = new List<FilterParameter>();
		}

		public IEnumerable<FilterParameter> GetCurrentParameters
		{
			get { return this._parameters; }
		}

		public IEnumerable<FilterParameter> GetCurrentUrlParameters
		{
			get { return this._urlParameters; }
		}

		public void AddParameters(IEnumerable<FilterParameter> parameters)
		{
			RemoveParametersByFieldNames(parameters.Select(parameter => parameter.FieldName).ToArray());
			this._parameters.AddRange(parameters);
		}

		public void AddParameter(FilterParameter parameter)
		{
			RemoveParameterByFieldName(parameter.FieldName);
			this._parameters.Add(parameter);
		}

		public void AddUrlParameters(IEnumerable<FilterParameter> parameters)
		{
			RemoveUrlParametersByFieldNames(parameters.Select(parameter => parameter.FieldName).ToArray());
			this._urlParameters.AddRange(parameters);
		}

		public void AddUrlParameter(FilterParameter parameter)
		{
			RemoveUrlParameterByFieldName(parameter.FieldName);
			this._urlParameters.Add(parameter);
		}

		public void RemoveParametersByGroup(string group)
		{
			this._parameters = this._parameters.Except(this._parameters.Where(parameter => parameter.Group.ToLower() == group.ToLower())).ToList();
		}

		public void RemoveParametersByOperationGroup(string operationGroup, string group = "")
		{
			this._parameters = this._parameters.Except(this._parameters.Where(parameter => parameter.OperationGroup == operationGroup.ToLower() && (parameter.Group == group.ToLower() || group == string.Empty))).ToList();
		}

		public void RemoveParametersByFieldNames(string[] fieldNames)
		{
			this._parameters = this._parameters.Where(parameter => !fieldNames.Contains(parameter.FieldName)).ToList();
		}

		public void RemoveParameterByFieldName(string fieldName)
		{
			this._parameters = this._parameters.Where(parameter => fieldName != parameter.FieldName).ToList();
		}

		public void RemoveUrlParametersByFieldNames(string[] fieldNames)
		{
			this._urlParameters = this._urlParameters.Where(parameter => !fieldNames.Contains(parameter.FieldName)).ToList();
		}

		public void RemoveUrlParameterByFieldName(string fieldName)
		{
			this._urlParameters = this._urlParameters.Where(parameter => fieldName != parameter.FieldName).ToList();
		}

		public string GetCurrentSqlString
		{
			get
			{
				var result = GetSqlString(this._parameters);

				if (string.IsNullOrEmpty(result))
				{
					result = "(1 = 1)";
				}

				return result;
			}
		}

		public string GetCurrentUrlString
		{
			get
			{
				var result = GetUrlString(this._urlParameters);

				if (string.IsNullOrEmpty(result))
				{
					result = String.Empty;
				}

				return result;
			}
		}

		public static string GetUrlString(List<FilterParameter> urlParameters)
		{
			return urlParameters.Select((urlParameter, index) => String.Format("{0}{1:000}={2}", FilterPrefix, index, GetJsonString(urlParameter))).Aggregate((previous, current) => previous + "&" + current);
		}

		public static string GetSqlString(IEnumerable<FilterParameter> parameters)
		{
			var parameterOperationGroups = parameters.GroupBy(parameter => parameter.OperationGroup);
			var result = new StringBuilder();
			var groupResult = new StringBuilder();
			var expressionResult = new StringBuilder();

			foreach (var parameterOperationGroup in parameterOperationGroups)
			{
				if (result.Length > 0 && groupResult.ToString() != string.Empty)
				{
					result.Append(" OR ");
					expressionResult.Clear();
					groupResult.Clear();
				}

				var parameterBinaryGroups = parameterOperationGroup.GroupBy(parameter => parameter.BinaryOperationGroup).Where(binaryGroup => !string.IsNullOrEmpty(binaryGroup.Key) && binaryGroup.Count() == 2);

				foreach (var parameterBinaryGroup in parameterBinaryGroups)
				{
					if (groupResult.Length > 0 && expressionResult.ToString() != string.Empty)
					{
						groupResult.Append(" AND ");
						expressionResult.Clear();
					}

					var betweenObjects = parameterBinaryGroup.ToArray();

					if (betweenObjects[0].OperationType == betweenObjects[1].OperationType
						&&
						betweenObjects[0].FieldName == betweenObjects[1].FieldName
						&&
						betweenObjects[0].CaseSensitive == betweenObjects[1].CaseSensitive
						&&
						betweenObjects[0].DataType == betweenObjects[1].DataType
						&&
						betweenObjects[0].BinaryOperationArgument != betweenObjects[1].BinaryOperationArgument)
					{
						string[] unquotifiedFieldValuesFrom, unquotifiedFieldValuesTo;
						if (betweenObjects[0].BinaryOperationArgument == BinaryOperationDirection.From)
						{
							unquotifiedFieldValuesFrom = betweenObjects[0].FieldValues;
							unquotifiedFieldValuesTo = betweenObjects[1].FieldValues;
						}
						else
						{
							unquotifiedFieldValuesFrom = betweenObjects[1].FieldValues;
							unquotifiedFieldValuesTo = betweenObjects[0].FieldValues;
						}
						var parameter = betweenObjects[0];

						if (string.IsNullOrEmpty(parameter.FieldName) || parameter.FieldValues.Length == 0)
						{
							continue;
						}

						var IsUpperCaseRequired = parameter.DataType == SqlDataType.String && parameter.CaseSensitive == CaseSensitivity.Ignore ? true : false;

						string quotifiedFieldValueStringFrom, unquotifiedFieldValueStringFrom;
						GetFieldValueString(parameter.OperationType, parameter.DataType, unquotifiedFieldValuesFrom, out quotifiedFieldValueStringFrom, out unquotifiedFieldValueStringFrom);
						string quotifiedFieldValueStringTo, unquotifiedFieldValueStringTo;
						GetFieldValueString(parameter.OperationType, parameter.DataType, unquotifiedFieldValuesTo, out quotifiedFieldValueStringTo, out unquotifiedFieldValueStringTo);

						expressionResult.AppendFormat(
								IsUpperCaseRequired ? "UPPER({0}){1}UPPER({2}) AND UPPER({2})" : "{0}{1}{2} AND {3}",
								parameter.FieldName, parameter.OperationType == SqlOperationType.Between ? " BETWEEN " : " NOT BETWEEN ", quotifiedFieldValueStringFrom, quotifiedFieldValueStringTo);
					}

					if (expressionResult.ToString() != string.Empty)
					{
						groupResult.AppendFormat("({0})", expressionResult);
					}
				}
				foreach (var parameter in parameterOperationGroup)
				{
					if (groupResult.Length > 0 && expressionResult.ToString() != string.Empty)
					{
						groupResult.Append(" AND ");
						expressionResult.Clear();
					}

					if (!string.IsNullOrEmpty(parameter.BinaryOperationGroup)
						||
						string.IsNullOrEmpty(parameter.FieldName)
						||
						parameter.FieldValues.Length == 0 &&
																!(
																	parameter.OperationType == SqlOperationType.IsEmpty
																	||
																	parameter.OperationType == SqlOperationType.IsNull
																	||
																	parameter.OperationType == SqlOperationType.NotIsEmpty
																	||
																	parameter.OperationType == SqlOperationType.NotIsNull
																))
					{
						continue;
					}

					var IsUpperCaseRequired = parameter.DataType == SqlDataType.String && parameter.CaseSensitive == CaseSensitivity.Ignore ? true : false;

					string quotifiedFieldValueString, unquotifiedFieldValueString;
					GetFieldValueString(parameter.OperationType, parameter.DataType, parameter.FieldValues, out quotifiedFieldValueString, out unquotifiedFieldValueString);

					switch (parameter.OperationType)
					{
						case SqlOperationType.EndsWith:
						case SqlOperationType.StartsWith:
						case SqlOperationType.Contains:
						case SqlOperationType.DoesNotContain:
							{
								if (parameter.DataType != SqlDataType.String)
								{
									break;
								}
								if (expressionResult.Length > 0)
								{
									expressionResult.Append(" OR ");
								}
								switch (parameter.OperationType)
								{
									case SqlOperationType.EndsWith:
										{
											expressionResult.AppendFormat(
												IsUpperCaseRequired ? "UPPER({0}) LIKE UPPER('%{1}') ESCAPE '\\'" : "{0} LIKE '%{1}' ESCAPE '\\'",
												parameter.FieldName, unquotifiedFieldValueString);
											break;
										}
									case SqlOperationType.StartsWith:
										{
											expressionResult.AppendFormat(
												IsUpperCaseRequired ? "UPPER({0}) LIKE UPPER('{1}%') ESCAPE '\\'" : "{0} LIKE '{1}%' ESCAPE '\\'",
												parameter.FieldName, unquotifiedFieldValueString);
											break;
										}
									case SqlOperationType.Contains:
										{
											expressionResult.AppendFormat(
												IsUpperCaseRequired ? "UPPER({0}) LIKE UPPER('%{1}%') ESCAPE '\\'" : "{0} LIKE '%{1}%' ESCAPE '\\'",
												parameter.FieldName, unquotifiedFieldValueString);
											break;
										}
									case SqlOperationType.DoesNotContain:
										{
											expressionResult.AppendFormat(
												IsUpperCaseRequired ? "UPPER({0}) NOT LIKE UPPER('%{1}%') ESCAPE '\\'" : "{0} NOT LIKE '%{1}%' ESCAPE '\\'",
												parameter.FieldName, unquotifiedFieldValueString);
											break;
										}
								}
								break;
							}
						case SqlOperationType.EqualTo:
						case SqlOperationType.GreaterThan:
						case SqlOperationType.GreaterThanOrEqualTo:
						case SqlOperationType.LessThan:
						case SqlOperationType.LessThanOrEqualTo:
						case SqlOperationType.NotEqualTo:
							{
								if (expressionResult.Length > 0)
								{
									expressionResult.Append(" OR ");
								}
								switch (parameter.OperationType)
								{
									case SqlOperationType.EqualTo:
										{
											expressionResult.AppendFormat(
												IsUpperCaseRequired ? "UPPER({0}) = UPPER({1})" : "{0} = {1}",
												parameter.FieldName, quotifiedFieldValueString);
											break;
										}
									case SqlOperationType.GreaterThan:
										{
											expressionResult.AppendFormat(
												IsUpperCaseRequired ? "UPPER({0}) > UPPER({1})" : "{0} > {1}",
												parameter.FieldName, quotifiedFieldValueString);
											break;
										}
									case SqlOperationType.GreaterThanOrEqualTo:
										{
											expressionResult.AppendFormat(
												IsUpperCaseRequired ? "UPPER({0}) >= UPPER({1})" : "{0} >= {1}",
												parameter.FieldName, quotifiedFieldValueString);
											break;
										}
									case SqlOperationType.LessThan:
										{
											expressionResult.AppendFormat(
												IsUpperCaseRequired ? "UPPER({0}) < UPPER({1})" : "{0} < {1}",
												parameter.FieldName, quotifiedFieldValueString);
											break;
										}
									case SqlOperationType.LessThanOrEqualTo:
										{
											expressionResult.AppendFormat(
												IsUpperCaseRequired ? "UPPER({0}) <= UPPER({1})" : "{0} <= {1}",
												parameter.FieldName, quotifiedFieldValueString);
											break;
										}
									case SqlOperationType.NotEqualTo:
										{
											expressionResult.AppendFormat(
												IsUpperCaseRequired ? "UPPER({0}) != UPPER({1})" : "{0} != {1}",
												parameter.FieldName, quotifiedFieldValueString);
											break;
										}
								}
								break;
							}
						case SqlOperationType.IsEmpty:
						case SqlOperationType.IsNull:
						case SqlOperationType.NotIsEmpty:
						case SqlOperationType.NotIsNull:
							{
								if (expressionResult.Length > 0)
								{
									expressionResult.Append(" OR ");
								}
								switch (parameter.OperationType)
								{
									case SqlOperationType.IsEmpty:
										{
											if (parameter.DataType != SqlDataType.String)
											{
												break;
											}
											expressionResult.AppendFormat("{0} = ''", parameter.FieldName);
											break;
										}
									case SqlOperationType.IsNull:
										{
											expressionResult.AppendFormat("{0} IS NULL", parameter.FieldName);
											break;
										}
									case SqlOperationType.NotIsEmpty:
										{
											if (parameter.DataType != SqlDataType.String)
											{
												break;
											}
											expressionResult.AppendFormat("{0} != ''", parameter.FieldName);
											break;
										}
									case SqlOperationType.NotIsNull:
										{
											expressionResult.AppendFormat("{0} IS NOT NULL", parameter.FieldName);
											break;
										}
								}
								break;
							}
						case SqlOperationType.In:
						case SqlOperationType.NotIn:
							{
								if (expressionResult.Length > 0)
								{
									expressionResult.Append(" OR ");
								}
								expressionResult.AppendFormat("{0}{1}({2})", parameter.FieldName, parameter.OperationType == SqlOperationType.NotIn ? " NOT IN " : " IN ", quotifiedFieldValueString);
								break;
							}
					}
					if (expressionResult.ToString() != string.Empty)
					{
						groupResult.AppendFormat("({0})", expressionResult);
					}
				}
				if (groupResult.ToString() != string.Empty)
				{
					result.AppendFormat("({0})", groupResult);
				}
			}

			return result.ToString();
		}

		private static void GetFieldValueString(SqlOperationType currentOperationType, SqlDataType currentDataType, string[] currentFieldValues, out string quotifiedFieldValueString, out string unquotifiedFieldValueString)
		{
			unquotifiedFieldValueString = string.Empty;

			if (currentFieldValues.Length == 0)
			{
				quotifiedFieldValueString = string.Empty;
				unquotifiedFieldValueString = string.Empty;
				return;
			}

			if (currentOperationType == SqlOperationType.In || currentOperationType == SqlOperationType.NotIn)
			{
				switch (currentDataType)
				{
					case SqlDataType.String:
						{
							quotifiedFieldValueString = currentFieldValues.Select(fieldValue => string.Format("'{0}'", fieldValue)).Aggregate((previous, current) => previous + "," + current);
							unquotifiedFieldValueString = currentFieldValues.Select(fieldValue => string.Format("'{0}'", fieldValue)).Aggregate((previous, current) => previous + "," + current);
							break;
						}
					case SqlDataType.DateTime:
						{
							quotifiedFieldValueString = currentFieldValues.Select(fieldValue => string.Format("TIMESTAMP('{0}')", fieldValue)).Aggregate((previous, current) => previous + "," + current);
							break;
						}
					case SqlDataType.Date:
						{
							quotifiedFieldValueString = currentFieldValues.Select(fieldValue => string.Format("DATE('{0}')", fieldValue)).Aggregate((previous, current) => previous + "," + current);
							break;
						}
					case SqlDataType.Time:
						{
							quotifiedFieldValueString = currentFieldValues.Select(fieldValue => string.Format("TIME('{0}')", fieldValue)).Aggregate((previous, current) => previous + "," + current);
							break;
						}
					default:
						{
							quotifiedFieldValueString = currentFieldValues.Aggregate((previous, current) => previous + "," + current);
							break;
						}
				}
			}
			else
			{
				switch (currentDataType)
				{
					case SqlDataType.String:
						{
							quotifiedFieldValueString = string.Format("'{0}'", currentFieldValues[0]);
							unquotifiedFieldValueString = currentFieldValues[0];
							break;
						}
					case SqlDataType.DateTime:
						{
							quotifiedFieldValueString = string.Format("TIMESTAMP('{0}')", currentFieldValues[0]);
							break;
						}
					case SqlDataType.Date:
						{
							quotifiedFieldValueString = string.Format("DATE('{0}')", currentFieldValues[0]);
							break;
						}
					case SqlDataType.Time:
						{
							quotifiedFieldValueString = string.Format("TIME('{0}')", currentFieldValues[0]);
							break;
						}
					default:
						{
							quotifiedFieldValueString = currentFieldValues[0];
							break;
						}
				}
			}
		}

		public static Filter Current
		{
			get
			{
				var result = HttpContext.Current.Session["CurrentFilter"] as Filter ?? new Filter();

				HttpContext.Current.Session["CurrentFilter"] = result;

				return result;
			}
		}

		public static IEnumerable<FilterParameter> GetFilterParameters(string jsonString)
		{
			return DataContractJsonSerializationHelper.Deserialize<List<FilterParameter>>(jsonString);
		}

		public static FilterParameter GetFilterParameter(string jsonString)
		{
			return DataContractJsonSerializationHelper.Deserialize<FilterParameter>(jsonString);
		}

		public static string GetJsonString(IEnumerable<FilterParameter> parameters)
		{
			return DataContractJsonSerializationHelper.Serialize(parameters);
		}

		public static string GetJsonString(FilterParameter parameter)
		{
			return DataContractJsonSerializationHelper.Serialize(parameter);
		}

		public string GetCurrentJsonString
		{
			get
			{
				return DataContractJsonSerializationHelper.Serialize(this._parameters);
			}
		}
	}
}
