using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections;
using System.Xml;
using System.Xml.Schema;
using System.IO;

namespace Orciid.Core
{
	/// <summary>
	/// This class must be inherited by all database specific connector classes
	/// </summary>
	/// <remarks>
	/// This is the base class for all database connection classes. If a new database must be
	/// supported, derive from this class and override all abstract and all required virtual
	/// methods.
	/// </remarks>
	public abstract class DBConnection:
		IDisposable
	{
		/// <summary>
		/// Required database version
		/// </summary>
		/// <remarks>
		/// This string must match the version string in the database, otherwise this code
		/// is considered to be incompatible with the database and an upgrade will be attempted.
		/// </remarks>
		protected const string RequiredDatabaseVersion = "00006";

		private static Hashtable testedconnections = new Hashtable();

		/// <summary>
		/// Constructor
		/// </summary>
		/// <remarks>
		/// Creates a new abstract database connection.  This method checks the version of
		/// the database and, if required, creates or updates the database tables to the
		/// correct version.
		/// </remarks>
		/// <param name="cs">The database connection string</param>
		protected DBConnection(string cs):
			base()
		{
			lock(typeof(DBConnection))
			{
				if (cs != null && !testedconnections.Contains(cs))
				{
					if (CheckDatabaseVersion(cs))
						testedconnections.Add(cs, null);
					else
						throw new CoreException("Invalid database connection string or invalid database");
				}
			}
		}

        /// <summary>
        /// Reset tested connections cache
        /// </summary>
        /// <remarks>
        /// When the first connection for a particular connection string is requested,
        /// this class checks if the database for that connection string is updated to the
        /// required version.  This test is only performed once per connection string, unless
        /// this method is called.
        /// </remarks>
		public static void ResetTestedConnections()
		{
			lock(typeof(DBConnection))
			{
				testedconnections.Clear();
			}
		}

		/// <summary>
		/// Check database version
		/// </summary>
		/// <remarks>
		/// This method must be overridden by the different database connection classes.
		/// It must check if the database specified by the connection string fulfills the
		/// requirements of this code version and if not, attempt to upgrade the database.
		/// </remarks>
		/// <param name="cs">The database connection string</param>
		/// <returns><c>true</c> if the database fulfills the requirements,
		/// <c>false</c> otherwise.</returns>
		protected abstract bool CheckDatabaseVersion(string cs);

        /// <summary>
        /// Execute a query and return the resulting data reader
        /// </summary>
        /// <remarks>
        /// This method returns a <see cref="IDataReader"/> instead of
        /// preloading all rows into a <see cref="DataTable"/>.
        /// </remarks>
        /// <param name="query">The SQL query to run</param>
        /// <returns>An <see cref="IDataReader"/> with the resulting rows</returns>
		public abstract IDataReader ExecReader(Query query);

		/// <summary>
		/// Runs a select query.
		/// </summary>
		/// <remarks>
		/// Must be implemented by individual database connection classes.
		/// </remarks>
		/// <param name="query">The ANSI SQL query to run. Must only use functionality 
		/// available in all supported databases</param>
		/// <returns>A DataTable containing the records retrieved by the query</returns>
		public abstract DataTable SelectQuery(Query query);

		/// <summary>
		/// Executes a query without returning records
		/// </summary>
		/// <remarks>
		/// Must be implemented by individual database connection classes.
		/// </remarks>
		/// <param name="query">The ANSI SQL query to run. Must only use functionality 
		/// available in all supported databases</param>
		/// <returns>The number of affected records</returns>
		public abstract int ExecQuery(Query query);

		/// <summary>
		/// Executes a query returning a single value
		/// </summary>
		/// <remarks>
		/// Must be implemented by individual database connection classes.
		/// </remarks>
		/// <param name="query">The ANSI SQL query to run. Must only use functionality 
		/// available in all supported databases</param>
		/// <returns>The value returned by the query</returns>
		public abstract object ExecScalar(Query query);

		/// <summary>
		/// Retrieves the identity created by the database for the record inserted last
		/// </summary>
		/// <remarks>
		/// Must be overridden by the specific database connection classes.
		/// </remarks>
		/// <param name="table">The table the record was inserted into</param>
		/// <returns>The identity created for the record</returns>
		public abstract int LastIdentity(string table);

		/// <summary>
		/// Closes the connection
		/// </summary>
		/// <remarks>
		/// This method must be called once the connection object is no longer needed.  For
		/// performance reasons, this method should be called as soon as possible.  It is
		/// recommended to use a <c>using</c> block to guarantee the connection will be closed.
		/// </remarks>
		public abstract void Close();

		/// <summary>
		/// For IDisposable
		/// </summary>
		/// <remarks>
		/// This method is implemented to support <c>using</c> blocks.  It calls <see cref="Close"/>.
		/// </remarks>
		public void Dispose()
		{
			Close();
		}

		/// <summary>
		/// Encodes a string so it can be used in an SQL query. 
		/// </summary>
		/// <remarks>
		/// As different databases use different encoding mechanisms, this function must be called on the 
		/// database connection object to be used to execute the query.
		/// </remarks>
		/// <param name="sql">The string to encode</param>
		/// <returns>The encoded string</returns>
		public abstract string Encode(string sql);

		/// <summary>
		/// Encodes a string so it can be used in an SQL LIKE comparison.
		/// </summary>
		/// <remarks>
		/// Encodes a string so it can be used in an SQL LIKE comparison. As different databases
		/// use different encoding mechanisms, this function must be called on the 
		/// database connection object to be used to execute the query.  This method encodes
		/// all wildcard characters etc, so those should be added after encoding.
		/// </remarks>
		/// <param name="sql">The string to encode</param>
		/// <returns>The encoded string</returns>
		public abstract string LikeEncode(string sql);

		/// <summary>
		/// Converts a data object returned from a DataRow to a string.
		/// </summary>
		/// <remarks>
		/// Override this method if the object returned by the database does not support a 
		/// sensible <see cref="Object.ToString"/>.
		/// </remarks>
		/// <param name="data">The data object retrieved from the DataRow</param>
		/// <param name="defaultvalue">The value to return if the data object is null</param>
		/// <returns>The string value of the data object if it is not null, the defaultvalue otherwise</returns>
		public virtual string DataToString(Object data, string defaultvalue)
		{
			return (data == null || data.Equals(DBNull.Value) ? defaultvalue : data.ToString());
		}

		/// <summary>
		/// Converts a data object returned from a DataRow to a string.
		/// </summary>
		/// <remarks>
		/// This method calls <see cref="DataToString(Object,string)"/> with <c>null</c> as
		/// the default value.
		/// </remarks>
		/// <param name="data">The data object retrieved from the DataRow</param>
		/// <returns>The string value of the data object, even it is null</returns>
		public string DataToString(Object data)
		{
			return DataToString(data, null);
		}

		/// <summary>
		/// Converts a data object returned from a DataRow to a character.
		/// </summary>
		/// <remarks>
		/// Override this method if the object returned by the database does not support
		/// a meaningful <c>ToString()</c> method.
		/// </remarks>
		/// <param name="data">The data object retrieved from the DataRow</param>
		/// <param name="defaultvalue">The value to return if the data object is null</param>
		/// <returns>The char value of the data object, even it is null</returns>
		public virtual char DataToChar(Object data, char defaultvalue)
		{
			if (data == null || data.Equals(DBNull.Value))
				return defaultvalue;
			string s = data.ToString();
			if (s.Length == 0)
				return defaultvalue;
			else
				return s.ToCharArray()[0];
		}

		/// <summary>
		/// Converts a data object returned from a DataRow to an integer.
		/// </summary>
		/// <remarks>
		/// Override this method if the object returned by the database cannot be cast to <c>int</c>.
		/// </remarks>
		/// <param name="data">The data object retrieved from the DataRow</param>
		/// <param name="defaultvalue">The value to return if the data object is null</param>
		/// <returns>The integer value of the data object if it is not null, the defaultvalue otherwise</returns>
		public virtual int DataToInt(Object data, int defaultvalue)
		{
			
			return (data == null || data.Equals(DBNull.Value) ? defaultvalue : Convert.ToInt32(data));
		}

		/// <summary>
		/// Converts a data object returned from a DataRow to a long integer.
		/// </summary>
		/// <remarks>
		/// Override this method if the object returned by the database cannot be cast to <c>long</c>.
		/// </remarks>
		/// <param name="data">The data object retrieved from the DataRow</param>
		/// <param name="defaultvalue">The value to return if the data object is null</param>
		/// <returns>The long integer value of the data object if it is not null, the defaultvalue otherwise</returns>
		public virtual long DataToLong(Object data, long defaultvalue)
		{
			return (data == null || data.Equals(DBNull.Value) ? defaultvalue : Convert.ToInt64(data));
		}

		/// <summary>
		/// Converts a data object returned from a DataRow to a boolean.
		/// </summary>
		/// <remarks>
		/// Override this method if the object returned by the database cannot be cast to <c>bool</c>.
		/// </remarks>
		/// <param name="data">The data object retrieved from the DataRow</param>
		/// <param name="defaultvalue">The value to return if the data object is null</param>
		/// <returns>The boolean value of the data object if it is not null, the defaultvalue otherwise</returns>
		public virtual bool DataToBool(Object data, bool defaultvalue)
		{
			return (data == null || data.Equals(DBNull.Value) ? defaultvalue : (bool)data);
		}

		/// <summary>
		/// Convert database date object to DateTime object
		/// </summary>
		/// <remarks>
		/// Different databases store date object differently.  This method converts a
		/// database date object to a DateTime object.
		/// </remarks>
		/// <param name="data">The object retrieved from the database</param>
		/// <returns>A DateTime object representing the database date object, or DateTime if
		/// the database object was <c>null</c>.</returns>
		public virtual DateTime DataToDateTime(Object data)
		{
			return DataToDateTime(data, DateTime.MinValue);
		}

		/// <summary>
		/// Convert database date object to DateTime object
		/// </summary>
		/// <remarks>
		/// Different databases store date object differently.  This method converts a
		/// database date object to a DateTime object.
		/// </remarks>
		/// <param name="data">The object retrieved from the database</param>
		/// <param name="defaultvalue">The default value that should be used if the object
		/// is <c>null</c>.</param>
		/// <returns>A DateTime object</returns>
		public virtual DateTime DataToDateTime(Object data, DateTime defaultvalue)
		{
			try
			{
				return DateTime.Parse(DataToString(data));
			}
			catch
			{
				return defaultvalue;
			}
		}

		/// <summary>
		/// Converts a boolean value to the corresponding string value to be used in SQL statements
		/// </summary>
		/// <remarks>
		/// This method must be overridden if the database does not use <c>0</c> and <c>1</c> to
		/// indicate the boolean values <c>false</c> and <c>true</c>.
		/// </remarks>
		/// <param name="data">The boolean value to be converted</param>
		/// <returns><c>"1"</c> if data is <c>true</c>, <c>"0"</c> otherwise</returns>
		public virtual string BoolToData(bool data)
		{
			return (data ? "1" : "0");
		}

		/// <summary>
		/// Convert table to integer array
		/// </summary>
		/// <remarks>
		/// Converts a DataTable returned from a call to SelectQuery, containing one column of
		/// integer identifiers, to an integer array
		/// </remarks>
		/// <param name="table">a DataTable containing one column with integer values</param>
		/// <returns>An array with the integer values from the DataTable, or <c>null</c>
		/// if the DataTable has no rows</returns>
		public int[] TableToArray(DataTable table)
		{
			if (table != null && table.Rows.Count > 0)
			{
				int[] values = new int[table.Rows.Count];
				for (int i = 0; i < table.Rows.Count; i++)
					values[i] = (int)table.Rows[i][0];
				return values;
			}
			else
				return null;
		}

		/// <summary>
		/// Create text comparison condition
		/// </summary>
		/// <remarks>
		/// Creates a condition for use within a WHERE statement that compares field values to a 
		/// certain keyword.
		/// Databases that provide more sophisticated text comparison methods (e.g. through a 
		/// full-text index) can override this method.
		/// </remarks>
		/// <param name="field">The name of the field to compare</param>
		/// <param name="keyword">The keyword to find</param>
		/// <returns>a WHERE condition comparing field values to the specified keyword</returns>
		public virtual string TextComparison(string field, string keyword)
		{
			return String.Format("({0} LIKE '{1}')", field, LikeEncode(keyword));
		}	

		/// <summary>
		/// Validate keyword
		/// </summary>
		/// <remarks>
		/// For some databases, not every word is valid in a full text search. This method checks the validity
		/// of a certain keyword.
		/// </remarks>
		/// <param name="keyword">The keyword to check.</param>
		/// <returns><c>true</c> if this keyword can be searched for in a full-text search, <c>false</c>
		/// otherwise.</returns>
		public virtual bool IsValidKeyword(string keyword)
		{
			return true;
		}

		/// <summary>
		/// Allow for sorting of large text fields
		/// </summary>
		/// <remarks>
		/// Some databases do not allow sorting of results by large text fields. These fields
		/// need to be converted to VARCHAR and rhis function must be overridden by database
		/// connectors if this is the case. The function should return a string along the lines
		/// of <c>CONV(fieldname AS VARCHAR(255))</c>
		/// </remarks>
		/// <param name="fieldname">The name of the field to sort by</param>
		/// <returns>A string representing the field with a sortable type.</returns>
		public virtual string SortableTextField(string fieldname)
		{
			return fieldname;
		}

		/// <summary>
		/// Format date to store in database
		/// </summary>
		/// <remarks>
		/// This method converts a <see cref="DateTime"/> object to a string formatted so
		/// it is understood by the database. This method must be overridden for each database
		/// connector.
		/// </remarks>
		/// <param name="date">The <see cref="DateTime"/> object to format</param>
		/// <returns>The date formatted in a string</returns>
		public abstract string DateToDBFormatString(DateTime date);

		/// <summary>
		/// Adjust SQL query syntax
		/// </summary>
		/// <remarks>
		/// Different databases have a slightly different SQL syntax.  All queries in
		/// Orciid.Core classes use Microsoft SQL Server syntax, other databases like
		/// MySQL must override this method and adjust the query string.
		/// </remarks>
		/// <param name="query">The SQL query to be run</param>
		/// <returns>The adjusted SQL query</returns>
		public virtual string AdjustSqlSyntax(string query)
		{
			return query;
		}

		/// <summary>
		/// Checks if database upgrade is allowed
		/// </summary>
		/// <remarks>
		/// To prevent accidental database updates that cannot easily be reversed,
		/// the configuration variable <c>database.upgradeable</c> is checked before
		/// the database upgrade is performed.  The variable must be <c>true</c> for
		/// the upgrade to proceed.
		/// </remarks>
		protected void FailIfNotUpgradeable()
		{
			if (!Configuration.Instance.ContainsKey("database.upgradeable") ||
				!Configuration.Instance.GetBool("database.upgradeable"))
				throw new CoreException("Database upgrade is required, but not allowed. To allow " +
					"upgrade, add '<upgradeable>true</upgradeable>' to database section in " +
					"configuration file.");
		}

		/// <summary>
		/// Prefix for Unicode strings in SQL statements
		/// </summary>
		/// <remarks>
		/// Different databases use different prefixes to mark Unicode strings.  The 'N' prefix
		/// should be standard, but some versions of MySQL have a bug that prevents this from
		/// working. See http://bugs.mysql.com/bug.php?id=17313
		/// </remarks>
		/// <returns>Prefix for Unicode strings in SQL statements</returns>
		public abstract string UnicodeStringPrefix();
	}
}