using System;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using System.Collections;
using System.Threading;

using MySql;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace WmData{
	/// <summary>
	/// This is a DataAdapter wrapper, to be used with our object based data system.
	/// Created 12th of May 2006.
	/// 
	/// Updated 22th of October 2007 with late thread based updates and enhanced command
	/// creation.
	/// </summary>
	public class WmDataAdapter  {
		protected WmData data;
		protected WmDataBaseCredentials dataBaseCredentials;

		protected string tableName;
		protected DataTable table;
		protected Type adaptedObjectType;
		protected MySqlConnection connection;
		protected MySqlDataAdapter adapter;

		protected string[] columnNames;
		protected MySqlDbType[] columnDbTypes;

		public WmDataAdapter(WmData data, WmDataBaseCredentials dataBaseCredentials, Type adaptedObjectType){
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
            this.data = data;
			this.dataBaseCredentials = dataBaseCredentials;

			this.adaptedObjectType	= adaptedObjectType;
			tableName = WmDataTools.getTableName(adaptedObjectType);
			
			// Check if table exists in database backend
			if(!WmDataBaseCreator.isTableExistent(tableName,dataBaseCredentials)){
				// If not, make a glorious attempt to create the table
				WmDataBaseCreator.createTable(adaptedObjectType,dataBaseCredentials);
			}

			createColumnArrays(adaptedObjectType);

			#region Select Command
			string selectSql = "SELECT #PROPERTIES# FROM "+dataBaseCredentials.Catalog+"."+tableName;
			string propTmp = "";
			for(int i = 0; i<columnNames.Length; i++){
				propTmp	+= " "+tableName+"."+columnNames[i]+",";
			}
			propTmp	= propTmp.Substring(0, propTmp.Length - 1);// Remove the last (obsolete) comma
			selectSql = selectSql.Replace("#PROPERTIES#",propTmp);
			#endregion

			// Create the adapter, connection and events
			connection = new MySqlConnection(dataBaseCredentials.getConnectionString());
			adapter = new MySqlDataAdapter(selectSql,connection);
			adapter.RowUpdated+=new MySqlRowUpdatedEventHandler(adapter_RowUpdated);
			//adapter.UpdateBatchSize = 100; not supported with MySQL
			#region Update Command
			MySqlCommand updateCommand	= new MySqlCommand();
			updateCommand.Connection	= connection;
			string updatePropTmp		= "";
			string updateSql			= "UPDATE "+dataBaseCredentials.Catalog+"." + tableName + " SET #PROPERTIES# WHERE id=?id";
			for(int i = 0; i<columnNames.Length; i++){
				if(!columnNames[i].Equals("id")){
					updatePropTmp	+= tableName+"."+columnNames[i]+"=?"+columnNames[i]+",";
					updateCommand.Parameters.Add("?" + columnNames[i], columnDbTypes[i],0,columnNames[i]);
				}
			}
			updateCommand.Parameters.Add("?id", MySqlDbType.Int32,0,"id"); // manually add the id parameter to the command
			updatePropTmp				= updatePropTmp.Substring(0,updatePropTmp.Length-1);
			updateSql					= updateSql.Replace("#PROPERTIES#",updatePropTmp);
			updateCommand.CommandText	= updateSql;
			adapter.UpdateCommand		= updateCommand;
			#endregion

			#region Insert Command
			MySqlCommand insertCommand = new MySqlCommand();
			insertCommand.Connection = connection;
			string insertSql = "INSERT INTO " + dataBaseCredentials.Catalog + "." + tableName + "  (#COLUMNS#) VALUES (#VALUES#)";
			string insertColumnsTmp	= "";
			string insertValuesTmp = "";

			for (int i = 0; i < columnNames.Length; i++) {
				insertColumnsTmp += tableName + "." + columnNames[i] + ",";
				insertValuesTmp += "?" + columnNames[i] + ",";
				insertCommand.Parameters.Add("?" + columnNames[i], columnDbTypes[i], 0, columnNames[i]);
			}
			insertColumnsTmp = insertColumnsTmp.Substring(0,insertColumnsTmp.Length-1); // remove last obsolete ,
			insertValuesTmp = insertValuesTmp.Substring(0,insertValuesTmp.Length-1); // remove last obsolete ,
			insertSql = insertSql.Replace("#COLUMNS#",insertColumnsTmp); // set columnTmp on its position
			insertSql = insertSql.Replace("#VALUES#",insertValuesTmp); // set valueTmp on its position
			insertCommand.CommandText = insertSql;
			adapter.InsertCommand = insertCommand;
			#endregion

			#region Delete Command
			string deleteSql = "DELETE FROM "+dataBaseCredentials.Catalog+"." + tableName + " WHERE id=?id";
			MySqlCommand deleteCommand	= new MySqlCommand(deleteSql,connection);
			deleteCommand.Parameters.Add("?id",MySqlDbType.Int32,0,"id");
			adapter.DeleteCommand		= deleteCommand;
			#endregion

			// Fill and configure the dataset to use the id column as primary field.
			getSchema();
			
			// Find the table in the collection, and keep a reference to prevent further searching
			table = data.WmDataSet.Tables[tableName];

			// The magic trick to cache the object instances in the virtual data tables is done here:
			table.Columns.Add(new DataColumn("objectinstance", adaptedObjectType));

			DataColumn[] tableKeys = new DataColumn[1];
			tableKeys[0] = table.Columns["id"];
			table.PrimaryKey = tableKeys;

			fill();
            stopwatch.Stop();
			Trace.WriteLine("WmDataAdapter: Created and filled for table " + tableName+" in "+stopwatch.ElapsedMilliseconds+" ms.");		
		}

		protected void createColumnArrays(Type objectType) {
			ArrayList columnNamesTmp = new ArrayList();
			ArrayList columnDbTypesTmp	= new ArrayList();
			foreach(PropertyInfo propInfo in adaptedObjectType.GetProperties()){
				if(WmDataTools.checkDataNeed(propInfo.PropertyType)){
					columnNamesTmp.Add(propInfo.Name.ToLower());
                    columnDbTypesTmp.Add(WmDataTools.getMySqlDbTypeFromProperty(propInfo.PropertyType));
				}
			}
			columnNames =(string[])columnNamesTmp.ToArray(typeof(string));
			columnDbTypes = (MySqlDbType[])columnDbTypesTmp.ToArray(typeof(MySqlDbType));
		}

		

		/// <summary>
		/// Use this method to fill or refresh the datatable in the dataset with live data from database
		/// </summary>
		public void fill(){
			lock(table){
				try{
					connection.Open();
					adapter.Fill(data.WmDataSet,tableName);
				}catch(Exception error){
					Trace.WriteLine("WmDataAdapter: Error while attempting to fill datatable "+tableName+": "+error.Message);
				}finally{
					connection.Close();
				}
			}
		}

		public void getSchema(){
			try{
				connection.Open();
				adapter.FillSchema(data.WmDataSet, SchemaType.Source, tableName);
			}catch(Exception error){
				throw new Exception("WmDataAdapter: SQL Table mismatch with .NET object model. Datatable "+tableName+": "+error.Message);
			}finally{
				connection.Close();
			}
		}

		/// <summary>
		/// Use this method to update, insert or delete any objects from/into database backend.
		/// </summary>
		public void update(){
			lock (table) {
				try {
					connection.Open();
				//	DateTime start = DateTime.Now;
				//	Trace.WriteLine("WmDataAdapter: Updating backend for table " + tableName);
					int updated = adapter.Update(data.WmDataSet, tableName);
				//	Trace.WriteLine("WmDataAdapter: Done update for " + tableName + " in " + (DateTime.Now - start));
				//	table.AcceptChanges();
					//Trace.WriteLine("WmDataAdapter: " + updated + " rows updated by calling update for table " + tableName + ". ");
				} catch (Exception error) {
					Trace.WriteLine("WmDataAdapter: Error while attempting to update datatable " + tableName + ": " + error.Message);
				} finally {
					connection.Close();
				}
			}
		}

		

		/// <summary>
		/// Gets the table name (object name) where this adapter is working for
		/// </summary>
		public string TableName{
			get { return tableName; }
		}

		/// <summary>
		/// Writes error information into trace when update / delete / insert fails
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		private void adapter_RowUpdated(object sender, MySqlRowUpdatedEventArgs e) {
			if(e.RecordsAffected == 0){
				Trace.WriteLine("WmDataAdapter: Excecuted Command, but no records affected: "+e.Command.CommandText);
				foreach(MySqlParameter param in e.Command.Parameters){
					Trace.WriteLine(param.ParameterName+" / "+param.DbType+" / "+param.Size +" / "+param.SourceColumn+" / "+param.Value);
				}
			}
		}
	}
}