using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using WmData;
using System.Reflection;
using System.Diagnostics;
using System.Text.RegularExpressions;
using MySql.Data.MySqlClient;

namespace WmData {

    /// <summary>
    /// WmGenericData: The foundation for object-to-SQL and SQL-to-object systems
    /// Created to allow multiple implementations of such systems while maintaining a single interface
    /// Created 3rd of August 2010.
    /// </summary>
    public abstract class WmGenericData {
		
		protected WmDataIdManager idManager;
        protected WmDataBaseCredentials dataBaseCredentials;
		protected WmIntermediateObjectManager intermediateObjectManager;
        protected WmDataTransactionManager transactionManager;
        protected WmDataLink dataLink = null;

        public WmGenericData(string databaseUsername, string databasePassword, string databaseHost, int databasePort, string databaseCatalog) {
            dataBaseCredentials = new WmDataBaseCredentials(databaseHost, databasePort, databaseUsername, databasePassword, databaseCatalog);
            intermediateObjectManager = new WmIntermediateObjectManager(dataBaseCredentials);
           
            reset();
		}

        public WmGenericData(string databaseUsername, string databasePassword, string databaseHost, int databasePort, string databaseCatalog,string thisServerAddress, List<string>dataLinkServers, int syncIntervalInSeconds,List<Type>syncObjectTypes) 
            : this(databaseUsername,databasePassword,databaseHost,databasePort,databaseCatalog) {
            transactionManager = new WmDataTransactionManager(this);
            dataLink = new WmDataLink(this,thisServerAddress,dataLinkServers,syncIntervalInSeconds,syncObjectTypes);
            dataLink.DataLinkRoleChanged += new WmDataLink.DataLinkRoleChangedEventHandler(dataLink_DataLinkRoleChanged);
        }

        protected void dataLink_DataLinkRoleChanged(object sender, WmDataLinkRoleChangedEventArguments e) {
            if (e.NewDataLinkRole == WmDataLinkRole.ACTIVE) {
                // if this data instance becomes active, we should purge the id managers cache to prevent possible
                // duplicate id's. 
                idManager.clearIdManagerCache();

                // if this data instance becomes active, we should purge all previous transactions since they may
                // include transactions that we're imported from a previous active server that can now become backup (and thus requesting its own transactions)
                if (transactionManager != null) {
                    transactionManager.clearTransactions();
                }
            }
        }

        /// <summary>
        /// Data change event handler with the data change event arguments
        /// </summary>
        public delegate void DataChangeEventHandler(object sender, WmDataChangeEventArguments e);
        
        /// <summary>
        /// Data change event, fires when something is added, updated or deleted
        /// </summary>
        public event DataChangeEventHandler DataChanged;

        /// <summary>
        /// Raise a data change event (if someone is listening)
        /// </summary>
        protected void raiseDataChangeEvent(string tableName, WmDataRowAction rowAction, int rowId) {
            if (DataChanged != null) {
          //      Trace.WriteLine("WmGenericData: Raising data changed event " + rowAction.ToString() + " table " + tableName + " id " + rowId);
                DataChanged(this, new WmDataChangeEventArguments(tableName, rowAction, rowId));
            }
        }

        /// <summary>
        /// Get WmIntermediateObject for the given object type (creates database backend if necessary)
        /// </summary>
        protected WmIntermediateObject getIntermediateObject(Type objectType) {
            return getIntermediateObject(objectType, true);
        }
        /// <summary>
        /// Get WmIntermediateObject for the given object type and specify if database should be created
        /// </summary>
        protected WmIntermediateObject getIntermediateObject(Type objectType, bool createDatabaseIfNecessary) {
            return intermediateObjectManager.getIntermediateObject(objectType,createDatabaseIfNecessary);
        }

        /// <summary>
        /// Reset the id manager
        /// </summary>
        public virtual void reset() {
            if (idManager != null) {    idManager = null;       }
            idManager   = new WmDataIdManager(dataBaseCredentials);
        }

        /// <summary>
        /// This is an essential function for any unsafe strings 
        /// to prevent SQL injections that _are_ possible with DirectData (in comp. to the original data)
        /// </summary>
        public string getSafeString(string unsafeString) {
            return WmDataTools.getSafeString(unsafeString);
        }

        #region ADD: addDataRow, importDataRow, addObject
        /// <summary>
        /// Add data row
        /// </summary>
        public void addDataRow(Type objectType, DataRow newRow) {
            addDataRow(objectType, newRow, false);
        }
        /// <summary>
        /// Add data row and specify override auto id
        /// </summary>
		public void addDataRow(Type objectType, DataRow newRow, bool overrideAutoId) {
            addDataRow(objectType, newRow, overrideAutoId, -1);
        }
        /// <summary>
        /// Add data row, specify override auto id and reference transaction moment
        /// </summary>
        public abstract void addDataRow(Type objectType, DataRow addRow, bool overrideAutoId, long referenceTransactionMomentUtcTics);

        /// <summary>
        /// Import data row, specify override auto id and reference transaction moment
        /// </summary>
        public abstract void importDataRow(Type objectType, DataRow importRow, bool overrideAutoId, long referenceTransactionMomentUtcTicks);
	
        /// <summary>
        /// Add object
        /// </summary>
        public void addObject(object objectInstance) {
			addObject(objectInstance, false);
		}
        /// <summary>
        /// Add object and specify override auto id
        /// </summary>
        public void addObject(object objectInstance, bool overrideAutoId) {
            addObject(objectInstance, overrideAutoId, -1);
        }
        /// <summary>
        /// Add object, specify override auto id and reference transaction moment
        /// </summary>
        public abstract void addObject(object objectInstance, bool overrideAutoId, long referenceTransactionMomentUtcTicks);
        #endregion

        #region UPDATE: updateDataRow, updateObject
        /// <summary>
        /// Update data row
        /// </summary>
        public void updateDataRow(Type objectType, DataRow updateRow) {
            updateDataRow(objectType, updateRow, -1);
        }
        /// <summary>
        /// Update data row and specify reference transaction moment
        /// </summary>
        public abstract void updateDataRow(Type objectType, DataRow updateRow, long referenceTransactionMomentUtcTicks);
        
        /// <summary>
        /// Update object
        /// </summary>
        public void updateObject(object objectInstance) {
            updateObject(objectInstance, -1);
        }
        /// <summary>
        /// Update object and specify reference transaction moment
        /// </summary>
        public abstract void updateObject(object objectInstance, long referenceTransactionMomentUtcTicks);
        #endregion

        #region DELETE: deleteDataRow, deleteObject, deleteTableContents
        /// <summary>
        /// Delete data row
        /// </summary>
        public void deleteDataRow(Type objectType, DataRow deleteRow) {
            deleteDataRow(objectType, deleteRow,-1);
        }
        /// <summary>
        /// Delete data row and specify reference transaction moment
        /// </summary>
        public abstract void deleteDataRow(Type objectType, DataRow deleteRow, long referenceTransactionMomentUtcTicks);
        
        /// <summary>
        /// Delete object
        /// </summary>
        public void deleteObject(object objectInstance) {
            deleteObject(objectInstance, -1); 
        }
		/// <summary>
		/// Delete object matching the given id
		/// </summary>
        public void deleteObject(Type objectType, int id) {
            deleteObject(objectType, id, -1); 
        }
        /// <summary>
        /// Delete object and specify reference transaction moment
        /// </summary>
        public void deleteObject(object objectInstance, long referenceTransactionMomentUtcTicks) {
            Type objectType = objectInstance.GetType();
            WmIntermediateObject intermediateObject = getIntermediateObject(objectType);
            int objectId = (int)intermediateObject.IdProperty.Getter.Invoke(objectInstance);
            deleteObject(objectType, objectId, referenceTransactionMomentUtcTicks); 
        }
        /// <summary>
        /// Delete object matching the given id and specify reference transaction moment
        /// </summary>
        public abstract void deleteObject(Type objectType, int id, long referenceTransactionMomentUtcTicks);

        /// <summary>
        /// Delete table contents (each and every row)
        /// </summary>
        public abstract void deleteTableContents(Type objectType);

        #endregion

        #region GET: getDataRow, getDataRows, getObject, getObjects, getRowCount
        /// <summary>
        /// Get data row matching the given id
        /// </summary>
        public abstract DataRow getDataRow(Type objectType, int id);
        /// <summary>
        /// Get data row matching the select
        /// </summary>
        public abstract DataRow getDataRow(Type objectType, string select);
        /// <summary>
        /// Get data rows matching the select
        /// </summary>
        public DataRow[] getDataRows(Type objectType, string select) {
            return getDataRows(objectType, select,null);
        }
        /// <summary>
        /// Get data rows matching the select, sorted according to the sort string
        /// </summary>
        public abstract DataRow[] getDataRows(Type objectType, string select, string sortString);

        /// <summary>
        /// Get object matching the given id
        /// </summary>
        public abstract object getObject(Type objectType, int id);
        /// <summary>
        /// Get object matching the select
        /// </summary>
        public abstract object getObject(Type objectType, string select);
        /// <summary>
        /// Get objects matching the select
        /// </summary>
        public abstract ArrayList getObjects(Type objectType, string select);
        /// <summary>
        /// Get the amount of rows (or objects) for the given object type
        /// </summary>
        public abstract int getRowCount(Type objectType);
        #endregion


        /// <summary>
        /// Returns an object by executing an SQL scalar command defined in the selectSql argument
        /// </summary>
        /// <param name="selectSql"></param>
        /// <returns></returns>
		public object getObjectFromSQL(string selectSql) {
			object result = null;
			MySqlCommand command = new MySqlCommand(selectSql, new MySqlConnection(dataBaseCredentials.getConnectionString()));
			try {
				command.Connection.Open();
				result = command.ExecuteScalar();
			} catch (Exception error) {
				Trace.WriteLine("WmGenericData: SQL Exception getObjectFromSql(). " + error.Message);
			} finally {
				command.Connection.Close();
			}
			return result;
		}
        /// <summary>
        /// Returns an array of objects as result of SqlCommand (the first column of each return row).
        /// </summary>
        /// <param name="sqlCommandText"></param>
        /// <returns></returns>
        public object[] getObjectsFromSQL(string sqlCommandText) {
            List<object> returnObjects = new List<object>();

            MySqlConnection connection = new MySqlConnection(dataBaseCredentials.getConnectionString());
            MySqlCommand command = new MySqlCommand(sqlCommandText, connection);
            try {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                while (reader.Read()) {
                    object value = reader[0];
                    if (value != null && !(value is DBNull)) {
                        if (value is sbyte) {
                            value = Convert.ToBoolean(value);
                        }
                    }
                    returnObjects.Add(value);
                }
                reader.Close();
            } catch (Exception error) {
                throw new Exception("WmGenericData: Exception occured in getObjectsFromSQL. " + error.Message);
            } finally {
                connection.Close();
            }

            return returnObjects.ToArray();
        }

        /// <summary>
        /// Get the current maximum id in use given in the specified table
        /// </summary>
		public int getCurrentMaximumId(string tableName) {
			string selectMaxIdSql = "SELECT MAX(" + tableName + ".id) FROM " + dataBaseCredentials.Catalog + "." + tableName + ";";
			object maxIdValue = getObjectFromSQL(selectMaxIdSql);
            if (!(maxIdValue is Int32)) {
				// it might be that the table does not exists, check that:
				// then it is logical that we return 0, since the current max id is 0
				return 0;
			} else {
				return (int)maxIdValue;
			}
		}

        /// <summary>
        /// Returns the MySQL checksum of the table
        /// </summary>
        public long getChecksum(string tableName) {
            tableName = getSafeString(tableName);
            string sqlCommandText = "CHECKSUM table " + dataBaseCredentials.Catalog + "." + tableName + ";";

            long checksum = -1;

            MySqlConnection connection = new MySqlConnection(dataBaseCredentials.getConnectionString());
            MySqlCommand command = new MySqlCommand(sqlCommandText, connection);
            try {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                while (reader.Read()) {
                    object value = reader[1];
                    if (value != null && !(value is DBNull)) {
                        checksum = (long)value;
                    }
                    break; // only the first row
                }
                reader.Close();
            } catch (Exception error) {
                throw new Exception("WmGenericData: Exception occured in getChecksum. " + error.Message);
            } finally {
                connection.Close();
            }
            return checksum;
        }

     
        /// <summary>
        /// Generates a  WHERE select statement like: "selectPropertyName in (obj1.PropertyName.Value, obj2.PropertyName.Value, obj3.PropertyName.Value)
        /// </summary>
        /// <param name="objectType">Type of the object</param>
        /// <param name="objects">object array that is used to build the select range</param>
        /// <param name="propertyName">name of the property where the select statement is aimed at (e.g. a Id)</param>
        /// <param name="selectPropertyName">name of the property in the select statement "selectPropertyName IN (..</param>
        /// <returns>ready to use select statement</returns>
        public string getInRangeSelect(Type objectType, object[] objects, string propertyName, string selectPropertyName) {
            return getInRangeSelect(objectType, objects, propertyName, selectPropertyName, false);
        }
        public string getInRangeSelect(Type objectType, object[] objects, string propertyName, string selectPropertyName, bool useNotInStatement) {
            return WmDataTools.getInRangeSelect(getIntermediateObject(objectType), objects, propertyName, selectPropertyName,useNotInStatement);
        }
        
		/// <summary>
		/// Checks two rows and compare the values of any property but Id. Will return true if row1 and row2 contains the same values. 
		/// </summary>
		protected bool isDuplicateRow(DataRow row1, DataRow row2) {
			bool isDuplicate = true;

			for (int i = 0; i <= row1.ItemArray.GetUpperBound(0); i++) {
				if (!WmDataTools.compareObjects(row2.ItemArray[i], row1.ItemArray[i])) {
					isDuplicate = false;
					return isDuplicate; // will cause check to stop when the first difference was found
				}
			}

			return isDuplicate;
		}

        /// <summary>
        /// Get an empty data table matching the table structrue of the object type
        /// </summary>
        public DataTable getDataTable(Type objectType) {
            DataTable returnTable = null;
            WmIntermediateObject intermediateObject = getIntermediateObject(objectType, true);
            returnTable = intermediateObject.DataTable; 
            return returnTable;
        }


        /// <summary>
        /// Preforms a loop on the object properties and returns true if any changes to the row were made
        /// if returning false, the row already had the same values set, thus not requiring a call to the SQL backend.
        /// </summary>
        public bool updateRowValuesFromObjectProperties(DataRow row, object objectInstance, WmIntermediateObject intermediateObject) {
            bool updatedRow = false;
            Type objectType = objectInstance.GetType();

            foreach (WmIntermediateObjectProperty property in intermediateObject.Properties) {
                int ordinal = property.DataColumnIndex;

                if (ordinal == -1) {
                    ordinal = row.Table.Columns.IndexOf(property.DataName);
                    property.DataColumnIndex = ordinal;
                }

                object rowValue = row[ordinal];
                object objectValue = property.Getter(objectInstance);
                if (!WmDataTools.compareObjects(rowValue, objectValue)) {
                    row[ordinal] = objectValue;
                    updatedRow = true;
                }
            }

            return updatedRow; // true if change was made, false if original row equals object values.
        }

        /// <summary>
        /// Will create a new instance of the object with the values of the datarow.
        /// </summary>
        public object getObjectInstanceFromRow(Type objectType, DataRow row) {
            WmIntermediateObject intermediateObject = getIntermediateObject(objectType);
            object objectInstance = intermediateObject.Creator.Invoke();

            setObjectPropertiesFromRow(objectInstance, row, intermediateObject);

            return objectInstance;
        }

        /// <summary>
        /// Will set the object instance properties to that of the row
        /// </summary>
        public void setObjectPropertiesFromRow(object objectInstance, DataRow row, WmIntermediateObject intermediateObject) {
            foreach (WmIntermediateObjectProperty property in intermediateObject.Properties) {
                int ordinal = property.DataColumnIndex;

                if (ordinal == -1) {
                    ordinal = row.Table.Columns.IndexOf(property.DataName);
                    property.DataColumnIndex = ordinal;
                }

                object value = row[ordinal];
                if (value != null && !(value is DBNull)) {
                    if (value is sbyte) {
                        value = Convert.ToBoolean(value);
                    }
                    property.Setter.Invoke(objectInstance, value);
                }
            }

        }
		
        /// <summary>
        /// Function to query the database for existance of a SQL table
        /// You can use this to check if tables exits withouth triggering any auto creation of tables based upon objects
        /// </summary>
        public bool isTableExistentInSql(string tableName) {
            return WmDataBaseCreator.isTableExistent(tableName, dataBaseCredentials);
        }

		
        /// <summary>
        /// WmIntermediateObjectManager, responsible for the intermediate objects
        /// </summary>
        public WmIntermediateObjectManager IntermediateObjectManager {
            get { return intermediateObjectManager; }
        }

        /// <summary>
        /// WmDataTransactionManager, responsible for registering transactions and capable to import them
        /// </summary>
        public WmDataTransactionManager TransactionManager {
            get { return transactionManager; }
        }

        /// <summary>
        /// WmDataLink, responsible for high availability management
        /// </summary>
        public WmDataLink DataLink {
            get { return dataLink; }
        }

        /// <summary>
        /// Returns the catalog name of the database being used.
        /// </summary>
        public string SqlCatalogName {
            get { return dataBaseCredentials.Catalog; }
        }

        /// <summary>
        /// Returns a date time ToString() format string that is accepted in any select statement: yyyy-MM-dd HH:mm:ss
        /// </summary>
        public string DateTimeToStringFormat {
            get { return "yyyy-MM-dd HH:mm:ss"; }
        }
	}

    /// <summary>
    /// Logical operand 
    /// </summary>
    public enum LogicalOperand {
        AND = 1, OR = 2
    }
}