using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using Howest.SchemaDiscovery;
using Howest.SchemaDiscovery.DbObjects;
using Howest.Guidance.DataAccess.CodeGeneration.ObjectModel.Mappings.Enums;
using Howest.SchemaDiscovery.CommonHelpers;
using Howest.Guidance.DataAccess.CodeGeneration.ObjectModel;
using WF = System.Windows.Forms;
using Howest.Guidance.DataAccess.CustomWizardPages.UI;
using Howest.Guidance.DataAccess.CodeGeneration.ObjectModel.Mappings.Helpers;
using Howest.Guidance.DataAccess.CodeGeneration.ObjectModel.Mappings;
using System.Data;

namespace Howest.Guidance.DataAccess.CustomWizardPages.Helpers
{
	static class UIHelper
	{
		public static List<Table> GetTables(ConnectionStringSettings settings)
		{
			DbSchemaDiscoverer disc = new DbSchemaDiscoverer(settings);
			return disc.DiscoverTables();
		}

		public static List<View> GetViews(ConnectionStringSettings settings)
		{
			DbSchemaDiscoverer disc = new DbSchemaDiscoverer(settings);
			return disc.DiscoverViews();
		}

		public static void FillTablesCombo<T>(WF.ComboBox cmb, List<T> list)
		{
			foreach (T type in list)
			{
				cmb.Items.Add(type);
			}
			if (cmb.Items.Count > 0)
				cmb.SelectedIndex = 0;
		}

		public static List<SqlStatement> GetSelectStatements(/*ConnectionStringSettings settings,*/ Table table)
		{
			List<SqlStatement> statements = new List<SqlStatement>();

			//DbSchemaDiscoverer disc = new DbSchemaDiscoverer(settings);

			//List<Table> tables = disc.DiscoverTables();

			//foreach (Table table in tables)
			//{
			string sql = string.Format("SELECT * FROM [{0}].[{1}]", table.Schema, table.Name);
			string colDesc = null;
			SqlStatement statement = null;
			List<Column> pkCols = null;
			//switch (opType)
			//{
			//    case OperationType.GetOne:
			if (!(table is View))
			{
				pkCols = table.Columns.FindAll(delegate(Column colList)
				{
					return colList.IsPrimaryKey;
				});
				if (pkCols.Count > 0)
				{
					statement = new SqlStatement();
					
					string oneSql = sql + " WHERE";
					bool isFirst = true;
					foreach (Column pkCol in pkCols)
					{
						if (isFirst)
							isFirst = false;
						else
						{
							oneSql += " AND";
							colDesc += ", ";
						}
						Parameter par = new Parameter();
						par.DBName = "@" + pkCol.Name;
						par.DbDataType = pkCol.DbDataType;
						par.Direction = System.Data.ParameterDirection.Input;
						statement.Parameters.Add(par);
						oneSql += string.Format(" [{0}].[{1}].[{2}] = @{3}", table.Schema, table.Name, pkCol.Name, pkCol.Name);
						colDesc += string.Format("[{0}]", pkCol.Name);
					}
					
					statement.Sql = oneSql;
					statement.MethodName = string.Format("Get{0}", table.Name);
					statement.IsReadOnly = true;
					statement.OperationType = OperationType.GetOne;
					statement.Description = string.Format("[{0}] by {1}]", table.Fullname, colDesc);
					statements.Add(statement);
				}
				//    break;
				//case OperationType.GetMany:
				List<Column> fkCols = table.Columns.FindAll(delegate(Column colListFk)
				{
					return colListFk.IsForeignKey;
				});
				string manySql = sql + " WHERE";
				if (fkCols.Count > 0)
				{
					int combSize = 1;
					List<Column[]> combinations = new List<Column[]>();
					while (combSize <= fkCols.Count)
					{
						combinations.AddRange(GetCombinations(fkCols, combSize, 0));
						combSize++;
					}

					foreach (Column[] combination in combinations)
					{
						statement = new SqlStatement();
						string condition = null;
						//string colDesc = null;
						bool isFirst = true;
						int count = combination.Length;
						for (int i = 0; i < count; i++)
						{
							Column col = combination[i];
							if (isFirst)
								isFirst = false;
							else
							{
								condition += " AND";
								colDesc += ", ";
							}
							Parameter par = new Parameter();
							par.DBName = "@" + col.Name;
							par.DbDataType = col.DbDataType;
							par.Direction = System.Data.ParameterDirection.Input;
							statement.Parameters.Add(par);
							condition += string.Format(" [{0}].[{1}].[{2}] = @{3}", table.Schema, table.Name, col.Name, col.Name);
							colDesc += string.Format("[{0}]", col.Name);
						}

						
						statement.Sql = manySql + condition;
						statement.MethodName = string.Format("Get{0}s{1}", table.Name, "");
						statement.IsReadOnly = true;
						statement.OperationType = OperationType.GetMany;
						statement.Description = string.Format("[{0}] by {1}]", table.Fullname, colDesc);
						statements.Add(statement);
					}
				}

				pkCols = table.Columns.FindAll(delegate(Column col)
				{
					return col.IsPrimaryKey;
				});
				if (pkCols.Count > 1)
				{
					int combSize = 1;
					List<Column[]> combinations = new List<Column[]>();
					while (combSize < pkCols.Count)
					{
						combinations.AddRange(GetCombinations(pkCols, combSize, 0));
						combSize++;
					}

					foreach (Column[] combination in combinations)
					{
						statement = new SqlStatement();
						
						string condition = null;
						//string colDesc = null;
						bool isFirst = true;
						int count = combination.Length;
						for (int i = 0; i < count; i++)
						{
							Column col = combination[i];
							if (isFirst)
								isFirst = false;
							else
							{
								condition += " AND";
								colDesc += ", ";
							}
							Parameter par = new Parameter();
							par.DBName = "@" + col.Name;
							par.DbDataType = col.DbDataType;
							par.Direction = System.Data.ParameterDirection.Input;
							statement.Parameters.Add(par);
							condition += string.Format(" [{0}].[{1}].[{2}] = @{3}", table.Schema, table.Name, col.Name, col.Name);
							colDesc += string.Format("[{0}]", col.Name);
						}

						
						statement.Sql = manySql + condition;
						statement.MethodName = string.Format("Get{0}s{1}", table.Name, "");
						statement.IsReadOnly = true;
						statement.OperationType = OperationType.GetMany;
						statement.Description = string.Format("[{0}] by {1}]", table.Fullname, colDesc);
						statements.Add(statement);
					}
				}
			}
			//    break;
			//case OperationType.GetAll:
			statement = new SqlStatement();
			statement.Sql = sql;
			statement.MethodName = string.Format("Get{0}s", table.Name);
			statement.IsReadOnly = true;
			statement.OperationType = OperationType.GetAll;
			statement.Description = string.Format("All from [{0}]", table.Fullname);
			statements.Add(statement);
			//        break;
			//    default:
			//        break;
			//}
			//}

			//List<View> views = disc.DiscoverViews();

			//foreach (View vw in views)
			//{
			//    string sql = string.Format("SELECT * FROM [{0}]", vw.Fullname);
			//    SqlStatement statement = null;
			//    switch (opType)
			//    {
			//        case OperationType.GetAll:
			//            statement = new SqlStatement();
			//            statement.Sql = sql;
			//            statement.Description = string.Format("All from [{0}]", vw.Fullname);
			//            statements.Add(statement);
			//            break;
			//        default:
			//            break;
			//    }
			//}

			//statements.Sort(delegate(SqlStatement statement1, SqlStatement statement2) {
			//    return string.Compare(statement1.Description, statement2.Description, true);
			//});

			//SqlStatement custom = new SqlStatement();
			//custom.Sql = "custom";
			//custom.Description = "Custom SQL statement";
			//statements.Insert(0, custom);

			return statements;
		}

		public static string GetCRUDStatement(/*ConnectionStringSettings connectionSettings,*/ Table table, OperationType opType)
		{
			switch (opType)
			{
				case OperationType.InsertOne:
					return GetInsertStatement(table);
				case OperationType.DeleteOne:
					return GetDeleteStatement(table);
				case OperationType.UpdateOne:
					return GetUpdateStatement(table);
				default:
					return string.Empty;
			}
		}

		private static string GetInsertStatement(Table tab)
		{
			bool isFirst = true;
			string cols = string.Empty;
			string pars = string.Empty;
			List<Column> insertableCols = tab.Columns.FindAll(delegate(Column col) {
				return !col.IsIdentity;
			});
			foreach (Column col in insertableCols)
			{
				if (isFirst)
					isFirst = false;
				else
				{
					cols += ", ";
					pars += ", ";
				}
				cols += string.Format("[{0}]", col.Name);
				pars += string.Format("@{0}", col.Name);
			}
			return string.Format("INSERT INTO [{0}].[{1}]({2}) VALUES ({3})", tab.Schema, tab.Name, cols, pars);
		}

		private static string GetUpdateStatement(Table tab)
		{
			List<Column> pkCols = tab.Columns.FindAll(delegate(Column col)
			{
				return col.IsPrimaryKey;
			});

			List<Column> setCols = tab.Columns.FindAll(delegate(Column col)
			{
				return !col.IsPrimaryKey;
			});

			string setSQLPart = string.Empty;
			bool isFirst = true;
			foreach (Column col in setCols)
			{
				if (isFirst)
					isFirst = false;
				else
					setSQLPart += ", ";

				setSQLPart += string.Format("[{0}] = @{0}", col.Name);
			}

			string whereSQLPart = string.Empty;
			isFirst = true;
			foreach (Column col in pkCols)
			{
				if (isFirst)
					isFirst = false;
				else
					whereSQLPart += " AND";

				whereSQLPart += string.Format(" [{0}] = @{0}", col.Name);
			}

			return string.Format("UPDATE [{0}].[{1}] SET {2} WHERE {3}", tab.Schema, tab.Name, setSQLPart, whereSQLPart);
		}

		public static string GetDeleteStatement(Table tab)
		{
			List<Column> pkCols = tab.Columns.FindAll(delegate(Column col) {
				return col.IsPrimaryKey;
			});

			string sql = string.Format("DELETE FROM [{0}].[{1}] WHERE ", tab.Schema, tab.Name);
			bool isFirst = true;
			foreach (Column col in pkCols)
			{
				if (isFirst)
					isFirst = false;
				else
				{
					sql += " AND";
				}
				sql += string.Format(" [{0}].[{1}].[{2}] = @{2}", tab.Schema, tab.Name, col.Name);
			}
			return sql;
		}

		public static void FillStatementsListView(WF.ListView lvw, List<SqlStatement> statements)
		{
			FillStatementsListView(lvw, statements, false, false);
		}

		public static void FillStatementsListView(WF.ListView lvw, List<SqlStatement> statements, bool defaultCheck)
		{ 
			FillStatementsListView(lvw, statements, defaultCheck, false);
		}

		public static void FillStatementsListView(WF.ListView lvw, List<SqlStatement> statements, bool defaultCheck, bool update)
		{
			foreach (SqlStatement statement in statements)
			{
				SqlStatement statToFind = null;
				bool founded = false;
				if (update)
				{
					foreach (WF.ListViewItem item in lvw.Items)
					{
						statToFind = (SqlStatement)item.Tag;
						if (statToFind.Sql == statement.Sql)
						{
							item.Tag = statement;
							item.Checked = true;
							founded = true;
							break;
						}
					}
				}
				if (!founded)
				{
					//WF.ListViewItem item = new WF.ListViewItem(statement.MethodName);
					//item.Tag = statement;
					//item.Checked = defaultCheck;
					//item.SubItems.Add(statement.Sql);
					//lvw.Items.Add(item);
					AddStatementToListView(lvw, statement, defaultCheck);
				}
			}
		}

		public static void AddStatementToListView(WF.ListView lvw, SqlStatement statement, bool defaultCheck)
		{
			WF.ListViewItem item = new WF.ListViewItem(statement.MethodName);
			item.Tag = statement;
			item.Checked = defaultCheck;
			item.SubItems.Add(statement.Sql);
			lvw.Items.Add(item);
		}

		public static void FillEntitiesListBox(WF.ListBox lstEntities, List<Entity> entities)
		{
			lstEntities.Items.Clear();

			if (lstEntities.Items.Count == 0)
			{
				foreach (Entity entity in entities)
				{
					lstEntities.Items.Add(entity);
				}
			}

			if (lstEntities.Items.Count > 0)
			{
				lstEntities.SelectedIndex = 0;
			}
		}

		private static List<T[]> GetCombinations<T>(List<T> list, int combSize, int index)
		{
			List<T[]> combinations = new List<T[]>(); // Declaration of the return value
			// Check the combination size
			if (combSize > 1)
			{
				// If combination size is bigger than 1 we have to fetch the current element
				// and create combinations for remaining elements
				T[] combination;
				int count = list.Count;
				for (int i = index; i < count - 1; i++)
				{
					// Create subcombinations for remaining elements recursively
					List<T[]> innerCombinations = GetCombinations<T>(list, combSize - 1, i + 1);
					// Merge this element with de subcombinations
					foreach (T[] innerCombination in innerCombinations)
					{
						// Create new array of the current size
						combination = new T[combSize];
						// Set the current element to the first element
						combination[0] = list[i];
						// copy the sub elements in the rest of the array
						for (int k = 0; k < combSize - 1; k++)
						{
							combination[k + 1] = innerCombination[k];
						}
						// Add it to the list
						combinations.Add(combination);
					}
				}
			}
			else
			{
				// If combSize is 1 then fetch one-element array of remaining columns
				// and add them to the return values
				int count = list.Count;
				for (int i = index; i < count; i++)
				{
					//object test = columns[i];
					combinations.Add(new T[] { list[i] });
				}
			}
			return combinations;
		}

		public static List<Parameter> GetParameters(ConnectionStringSettings connectionSettings, string sql, System.Data.ParameterDirection parameterDirection)
		{
			List<Parameter> parameters = null;
			switch (parameterDirection)
			{
				case System.Data.ParameterDirection.Input:
				case System.Data.ParameterDirection.InputOutput:
				case System.Data.ParameterDirection.Output:
					List<string> stringParams = SqlHelper.GetNamedParametersFromSql(sql);
					parameters = new List<Parameter>();
					foreach (string par in stringParams)
					{
						Parameter p = new Parameter();
						p.Direction = System.Data.ParameterDirection.Input;
						p.DBName = par;
						p.DbDataType = new Howest.SchemaDiscovery.DbObjects.DbDataType();
						p.DbDataType.ProviderType = DatabaseType.NotSupported;
						p.DbDataType.Type = typeof(object);
						parameters.Add(p);
					}
					return parameters;
				case System.Data.ParameterDirection.ReturnValue:
					DbSchemaDiscoverer disc = new DbSchemaDiscoverer(connectionSettings);
					return disc.DiscoverSqlResultSet(sql);
				default:
					return null;
			}
		}

		public static void RunSqlStatement(ConnectionStringSettings settings, string sql)
		{
			DbSchemaDiscoverer disc = new DbSchemaDiscoverer(settings);
			disc.RunSqlStatement(sql);
		}

		public static Table FindTable(WF.ComboBox cboTableView, string name)
		{
			foreach (Table tab in cboTableView.Items)
			{
				if (tab.Fullname == name)
					return tab;
			}
			return null;
		}

		public static void FillParametersListBox(ListBoxEx lstParameters, List<Parameter> parameters)
		{
			foreach (Parameter parameter in parameters)
			{
				lstParameters.Items.Add(ClonerHelper.CloneParameter(parameter));
			}
		}

		public static PropertyParameterMapping FindProperty(WF.CheckedListBox lstMappings, string propertyName)
		{
			foreach (PropertyParameterMapping mapping in lstMappings.Items)
			{
				//PropertyParameterMapping mapping = (PropertyParameterMapping)item;

				if (mapping.Property == propertyName)
				{
					return mapping;
				}
			}

			return null;
		}

		public static void FillParametersListBox(ListBoxEx lstParameters, WF.CheckedListBox lstMappings, List<Parameter> parameters)
		{
			foreach (Parameter parameter in parameters)
			{
				Parameter clonedParameter = ClonerHelper.CloneParameter(parameter);

				if (parameter.Direction == ParameterDirection.ReturnValue)
				{
					if (UIHelper.FindParameter(lstMappings, clonedParameter.Name) == null)
					{
						lstParameters.Items.Add(clonedParameter);
					}
				}
				else
				{
					if (UIHelper.FindParameter(lstMappings, clonedParameter.Name) == null)
					{
						lstParameters.Items.Add(clonedParameter);
					}
				}
			}
		}

		public static PropertyParameterMapping FindParameter(WF.CheckedListBox lstMappings, string parameterName)
		{
			foreach (PropertyParameterMapping mapping in lstMappings.Items)
			{
				//PropertyParameterMapping mapping = (PropertyParameterMapping)item;

				if (mapping.Parameter == parameterName)
				{
					return mapping;
				}
			}

			return null;
		}

		public static void RemoveMappingItems(ListBoxEx entities, ListBoxEx parameters, WF.CheckedListBox mappings)
		{
			List<PropertyParameterMapping> itemsToRemove = new List<PropertyParameterMapping>();

			foreach (PropertyParameterMapping mapping in mappings.CheckedItems)
			{
				//PropertyParameterMapping mapping = (PropertyParameterMapping)item;

				entities.Items.Add(mapping.Property);
				parameters.Items.Add(mapping.ParameterInstance);
				itemsToRemove.Add(mapping);
			}

			foreach (PropertyParameterMapping item in itemsToRemove)
			{
				mappings.Items.Remove(item);
			}

			if (mappings.Items.Count > 0)
			{
				mappings.SelectedIndex = 0;
			}
		}

	}
}
