using System;
using System.Collections.Generic;
using System.Data.Common;

using Howest.SchemaDiscovery.DbObjects;
using System.Data.SqlClient;
using System.Data;
using Howest.SchemaDiscovery.Translators.SqlServer;
using Howest.SchemaDiscovery.Translators.SqlServer.Helpers;
using Howest.SchemaDiscovery.CommonHelpers;
using System.Globalization;
using Howest.SchemaDiscovery.Translators.SqlServer.Data;
using Howest.SchemaDiscovery.DbObjects.SqlServer;


namespace Howest.SchemaDiscovery.SqlServer
{
	public class SqlServerSchemaProvider : DbSchemaProvider
	{
		public override bool UseNamedParameters
		{
			get { return true; }
		}

		public override DbProviderFactory GetFactory()
		{
			return SqlClientFactory.Instance;
		}

		protected override List<Table> GetTablesFromDb(DbConnection connection)
		{
			SqlConnection conn = connection as SqlConnection;
			if (conn == null)
				throw new ArgumentException("Connection object is null or not a valid SqlConnection object", "connection");
			List<Table> tables = new List<Table>();
			string[] restrictions = new String[4] { null, null, null, "BASE TABLE" };
			DataTable tableSchema = conn.GetSchema("Tables", restrictions);

			foreach (DataRow table in tableSchema.Rows)
			{
				string tableName = SqlServerTranslatorHelper.GetObjectName(table["table_schema"].ToString(), NamingHelper.EscapeObjectName(table["table_name"].ToString()));

				tables.Add(
					SqlServerTableTranslator.SqlServerTableCollectionToTable(
						table,
						GetTableColumnsCollection(conn, tableName, true),
						GetTableForeignKeysCollection(conn, table["table_schema"].ToString(), table["table_name"].ToString())));
			}

			return tables;
		}

		protected override List<View> GetViewsFromDb(DbConnection connection)
		{
			SqlConnection conn = connection as SqlConnection;
			if (conn == null)
				throw new ArgumentException("Connection object is null or not a valid SqlConnection object", "connection");
			List<View> views = new List<View>();
			string[] restrictions = new String[3] { null, null, null };
			DataTable viewsCollection = conn.GetSchema("Views", restrictions);
			DataRow[] rows = viewsCollection.Select("", "table_schema, table_name ASC");
			
			foreach (DataRow view in rows)
			{
				string viewName = SqlServerTranslatorHelper.GetObjectName(view["table_schema"].ToString(), NamingHelper.EscapeObjectName(view["table_name"].ToString()));

				views.Add(
					SqlServerViewTranslator.SqlServerViewCollectionToView(
						view,
						GetViewColumnsCollection(conn, viewName)));
			}

			return views;
		}

		protected override List<Procedure> GetProceduresFromDb(DbConnection connection)
		{
			SqlConnection conn = connection as SqlConnection;
			if (conn == null)
				throw new ArgumentException("Connection object is null or not a valid SqlConnection object", "connection");
			List<Procedure> storedProcedures = new List<Procedure>();
			string[] restrictions = new String[4] { null, null, null, "PROCEDURE" };
			DataTable storedProceduresCollection = conn.GetSchema("Procedures", restrictions);
			DataRow[] rows = storedProceduresCollection.Select("", "routine_name ASC");

			foreach (DataRow storedProcedure in rows)
			{
				storedProcedures.Add(
					SqlServerStoredProcedureTranslator.SqlServerStoredProcedureCollectionToStoreProcedure(
						storedProcedure,
						GetStoredProcedureParametersCollection(conn, storedProcedure["routine_name"].ToString())));
			}

			return storedProcedures;
		}

		protected override List<Parameter> GetProceduresResultSetFromDb(DbConnection connection, Procedure procedure)
		{
			SqlConnection conn = connection as SqlConnection;
			if (conn == null)
				throw new ArgumentException("Connection object is null or not a valid SqlConnection object", "connection");
			List<Parameter> parameters = new List<Parameter>();

			using (SqlCommand command = (SqlCommand)conn.CreateCommand())
			{
				command.CommandText = procedure.DBName;
				command.CommandType = CommandType.StoredProcedure;

				if (conn.ConnectionTimeout != -1)
				{
					command.CommandTimeout = conn.ConnectionTimeout;
				}

				foreach (Parameter parameter in procedure.Parameters)
				{
					SqlParameter parameterToAdd = command.Parameters.Add(
						parameter.DBName,
						SqlServerDataTypeConverter.DatabaseType2SqlDbType(parameter.DbDataType.ProviderType));
					parameterToAdd.Direction = parameter.Direction;
					parameterToAdd.Value = SqlServerTranslatorHelper.GetDefaultParameterValue(parameter.DbDataType.Type);
				}

				SqlTransaction transaction = null;

				try
				{
					transaction = (SqlTransaction)conn.BeginTransaction();
					command.Transaction = transaction;
					int index = 0;
					IDataReader dataReader = command.ExecuteReader(CommandBehavior.SchemaOnly);

					try
					{
						do
						{
							DataTable table = dataReader.GetSchemaTable();

							if (table != null)
							{
								parameters = GetProcedureResultSchema(table);
							}

							index++;

						} while (dataReader.NextResult());
					}
					finally
					{
						if (dataReader != null)
						{
							dataReader.Dispose();
						}
					}
				}
				finally
				{
					if (transaction != null)
					{
						transaction.Rollback();
					}
				}
			}

			return parameters;
		}

		protected override List<Relation> GetRelationsFromDb(DbConnection connection)
		{
			SqlConnection conn = connection as SqlConnection;
			if (conn == null)
				throw new ArgumentException("Connection object is null or not a valid SqlConnection object", "connection");
			string sql = @"SELECT 
								Constraint_Schema = RC.CONSTRAINT_SCHEMA,
								Constrain_Name = RC.CONSTRAINT_NAME,
								PK_Schema = PKCU.TABLE_SCHEMA,
								PK_Table = PKCU.TABLE_NAME,
								PK_Column = PKCU.COLUMN_NAME,
								FK_Schema = FKCU.TABLE_SCHEMA,
								FK_Table = FKCU.TABLE_NAME,
								FK_Column = FKCU.COLUMN_NAME
							FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
								INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PKCU
									ON RC.UNIQUE_CONSTRAINT_NAME = PKCU.CONSTRAINT_NAME AND RC.UNIQUE_CONSTRAINT_SCHEMA = PKCU.CONSTRAINT_SCHEMA
								INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FKCU
									ON PKCU.ORDINAL_POSITION = FKCU.ORDINAL_POSITION AND FKCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME AND RC.CONSTRAINT_SCHEMA = FKCU.CONSTRAINT_SCHEMA";
			string formatSql = string.Format(CultureInfo.CurrentCulture, sql);
			List<Table> tables = GetTablesFromDb(connection);
			SqlCommand cmd = conn.CreateCommand();
			cmd.CommandText = formatSql;
			cmd.CommandType = CommandType.Text;
			return null;
		}

		/*protected override Column GetReferencedColumnFromDb(DbConnection connection, string schema, string table, string col)
		{
			throw new Exception("The method or operation is not implemented.");
		}*/

		protected override bool ValidateConnection(DbConnection connection)
		{
			return connection is SqlConnection;
		}

		protected override List<Parameter> GetSqlResultSetFromDb(DbConnection connection, string sql)
		{
			SqlConnection conn = connection as SqlConnection;
			if (conn == null)
				throw new ArgumentException("Connection object is null or not a valid SqlConnection object", "connection");
			List<Parameter> parameters = new List<Parameter>();

			List<string> sqlParameters = SqlHelper.GetNamedParametersFromSql(sql);

			using (SqlCommand command = (SqlCommand)conn.CreateCommand())
			{
				command.CommandText = sql;
				command.CommandType = CommandType.Text;

				if (conn.ConnectionTimeout != -1)
				{
					command.CommandTimeout = conn.ConnectionTimeout;
				}

				foreach (string parameter in sqlParameters)
				{
					SqlParameter par = new SqlParameter(parameter, DBNull.Value);
					command.Parameters.Add(par);
				}

				SqlTransaction transaction = null;

				try
				{
					transaction = (SqlTransaction)conn.BeginTransaction();
					command.Transaction = transaction;
					int index = 0;
					IDataReader dataReader = command.ExecuteReader(CommandBehavior.SchemaOnly);

					try
					{
						do
						{
							DataTable table = dataReader.GetSchemaTable();

							if (table != null)
							{
								parameters = GetProcedureResultSchema(table);
							}

							index++;

						} while (dataReader.NextResult());
					}
					finally
					{
						if (dataReader != null)
						{
							dataReader.Dispose();
						}
					}
				}
				finally
				{
					if (transaction != null)
					{
						transaction.Rollback();
					}
				}
			}

			return parameters;
		}

		protected override void RunSqlStatementToDb(DbConnection connection, string sql)
		{
			SqlConnection conn = connection as SqlConnection;
			if (conn == null)
				throw new ArgumentException("Connection object is null or not a valid SqlConnection object", "connection");
			
			List<string> sqlParameters = SqlHelper.GetNamedParametersFromSql(sql);

			using (SqlCommand command = (SqlCommand)conn.CreateCommand())
			{
				command.CommandText = sql;
				command.CommandType = CommandType.Text;

				if (conn.ConnectionTimeout != -1)
				{
					command.CommandTimeout = conn.ConnectionTimeout;
				}

				foreach (string parameter in sqlParameters)
				{
					SqlParameter par = new SqlParameter(parameter, DBNull.Value);
					command.Parameters.Add(par);
				}

				SqlTransaction transaction = null;

				try
				{
					transaction = (SqlTransaction)conn.BeginTransaction();
					command.Transaction = transaction;
					command.ExecuteNonQuery();
				}
				catch (SqlException ex)
				{
					foreach (SqlError err in ex.Errors)
					{
						if (err.Number == 515)
						{
							return;
						}
					}
					throw ex;
				}
				finally
				{
					if (transaction != null)
					{
						transaction.Rollback();
					}
				}
			}
		}

		#region Private Methods

		private DataTable GetTableColumnsCollection(SqlConnection connection, string tableName, bool isTable)
		{
			//string[] restrictions = new string[4] { null, null, tableName, null };
			//DataTable columns = connection.GetSchema("Columns", restrictions);
			//columns.TableName = tableName;
			//return columns;

			DbCommand command = connection.CreateCommand();
			DataTable columns = new DataTable();
			columns.Locale = CultureInfo.CurrentCulture;
			command.CommandText = string.Format(CultureInfo.CurrentCulture, "SELECT TOP 1 * FROM {0}", tableName);

			if (isTable)
			{
				using (IDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo))
				{
					columns = reader.GetSchemaTable();
					reader.Close();
				}
			}
			else
			{
				using (IDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
				{
					columns = reader.GetSchemaTable();
					reader.Close();
				}
			}

			return columns;
		}

		private DataTable GetTableForeignKeysCollection(SqlConnection connection, string schema, string tableName)
		{
			string[] restrictions = new string[4] { null, null, tableName, null };
			DataTable foreignKeys = connection.GetSchema("ForeignKeys", restrictions);

			DataTable constraints = new DataTable();
			constraints.Locale = CultureInfo.CurrentCulture;
			constraints.TableName = tableName;
			constraints.Columns.Add(new DataColumn("ColumnName"));

			string text1 = string.Format(CultureInfo.CurrentCulture, "SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_schema = '{0}' and table_name = '{1}'", schema, tableName);
			SqlCommand command1 = new SqlCommand(text1, (SqlConnection)connection);
			using (IDataReader reader1 = command1.ExecuteReader())
			{
				while (reader1.Read())
				{
					string contraintName = (string)reader1["CONSTRAINT_NAME"];

					if (IsForeignKeyPresent(foreignKeys, contraintName, schema, tableName))
					{
						DataRow row = constraints.NewRow();
						row["ColumnName"] = (string)reader1["COLUMN_NAME"];
						constraints.Rows.Add(row);
					}
				}
			}

			return constraints;
		}

		private DataTable GetViewColumnsCollection(SqlConnection connection, string viewName)
		{
			//string[] restrictions = new string[4] { null, null, viewName, null };
			//DataTable columns = connection.GetSchema("ViewColumns", restrictions);
			//columns.TableName = viewName;
			//return columns;

			return GetTableColumnsCollection(connection, viewName, false);
		}

		private DataTable GetStoredProcedureParametersCollection(SqlConnection connection, string storedProcedureName)
		{
			string[] restrictions = new String[4] { null, null, storedProcedureName, null };
			DataTable procedureParameters = connection.GetSchema("ProcedureParameters", restrictions);
			procedureParameters.TableName = storedProcedureName;

			return procedureParameters;
		}

		private List<Parameter> GetProcedureResultSchema(DataTable schema)
		{
			List<Parameter> parameters = new List<Parameter>();

			foreach (DataRow row1 in schema.Rows)
			{
				Type type1 = (Type)row1["DataType"];
				int num1 = -1;
				if (type1 == typeof(string))
				{
					num1 = (int)row1["ColumnSize"];
				}

				SqlServerParameter parameter = new SqlServerParameter();
				parameter.DBName = SqlServerTranslatorHelper.GetQualifiedName((string)row1["ColumnName"]);
				parameter.Direction = ParameterDirection.ReturnValue;

				SqlServerDbDataType dbDataType = new SqlServerDbDataType();
				dbDataType.Size = num1;
				dbDataType.ProviderType = SqlServerDataTypeConverter.SqlDbType2DatabaseType(row1["ProviderType"]);
				dbDataType.Type = type1;
				dbDataType.AllowDbNull = (bool)row1["AllowDBNull"];
				parameter.DbDataType = dbDataType;

				parameters.Add(parameter);
			}
			return parameters;
		}

		private bool IsForeignKeyPresent(DataTable foreignKeys, string contraintName, string schema, string tableName)
		{
			foreach (DataRow row in foreignKeys.Rows)
			{
				if (row["table_schema"].ToString() == schema && row["table_name"].ToString() == tableName)
				{
					if (row["constraint_name"].ToString() == contraintName)
					{
						return true;
					}
				}
			}

			return false;
		}
		#endregion
	}
}
