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

namespace WmData {

    /// <summary>
    /// WmDirectData: The lean and mean object-to-SQL and SQL-to-object system
    /// This special variant of WmGenericData directly interfaces with MySQL.
    /// This class was designed for low-footprint applications and high performance data engines (large numbers of records,
    /// frequent and complex queries). 
    /// The ultimate goal is to get this WmDirectData variant interface compatible with the ordinary WmData.
    /// Created 10th of july 2009. (interface compatibility reached @ 3rd of August 2010 when updated to comply with WmGenericData)
    /// </summary>
    public class WmDirectData : WmGenericData {
        
        public WmDirectData(string databaseUsername, string databasePassword, string databaseHost,  int databasePort, string databaseCatalog) : base(databaseUsername,databasePassword,databaseHost,databasePort,databaseCatalog){
			Trace.WriteLine("WmDirectData initiated for " + databaseCatalog + " on host " + databaseHost);
        }
        public WmDirectData(string databaseUsername, string databasePassword, string databaseHost, int databasePort, string databaseCatalog, string thisServerAddress, List<string> dataLinkServers, int syncIntervalInSeconds, List<Type>syncObjectTypes)
            : base(databaseUsername, databasePassword, databaseHost, databasePort, databaseCatalog,thisServerAddress, dataLinkServers,syncIntervalInSeconds,syncObjectTypes) {
            Trace.WriteLine("WmDirectData initiated for " + databaseCatalog + " on host " + databaseHost);
        }


        #region ADD: addDataRow, importDataRow, addObject
        /// <summary>
        /// Add data row specify override auto id and reference transaction moment
        /// </summary>
        public override void addDataRow(Type objectType, DataRow addRow, bool overrideAutoId, long referenceTransactionMomentUtcTics) {
            WmIntermediateObject intermediateObject = getIntermediateObject(objectType, true);
            MySqlConnection connection = new MySqlConnection(dataBaseCredentials.getConnectionString());

            MySqlCommand command = WmDataTools.getMySqlInsertCommand(dataBaseCredentials, intermediateObject);
            command.Connection = connection;

            foreach (WmIntermediateObjectProperty property in intermediateObject.Properties) {
                command.Parameters["?" + property.DataName].Value = addRow[property.DataName];
            }

            if (!overrideAutoId) {
                int autoId = idManager.getNewId(intermediateObject.TableName);
                command.Parameters["?id"].Value = autoId;
                addRow["id"] = autoId;
            }

            try {
                connection.Open();
                command.ExecuteNonQuery();

            } catch (Exception error) {
                throw new Exception("WmDirectData: Exception occured in addDataRow. " + error.Message);
            } finally {
                connection.Close();
            }

            raiseDataChangeEvent(intermediateObject.TableName, WmDataRowAction.ADD, (int)addRow["id"]);
            if (transactionManager != null) {
                transactionManager.registerInsertOrUpdateTransaction(intermediateObject.TableName, addRow, WmDataRowAction.ADD, referenceTransactionMomentUtcTics);
            }
        }
        /// <summary>
        /// Import data row specify override auto id and reference transaction moment
        /// </summary>
        public override void importDataRow(Type objectType, DataRow importRow, bool overrideAutoId, long referenceTransactionMomentUtcTicks) {
            addDataRow(objectType, importRow, overrideAutoId, referenceTransactionMomentUtcTicks);
        }
        /// <summary>
        /// Add object specify override auto id and reference transaction moment
        /// </summary>
        public override void addObject(object objectInstance, bool overrideAutoId, long referenceTransactionMomentUtcTicks) {
            Type objectType = objectInstance.GetType();

            WmIntermediateObject intermediateObject = getIntermediateObject(objectType,true);
            MySqlConnection connection = new MySqlConnection(dataBaseCredentials.getConnectionString());
            
            MySqlCommand command = WmDataTools.getMySqlInsertCommand(dataBaseCredentials,intermediateObject);
            command.Connection = connection;

            foreach (WmIntermediateObjectProperty property in intermediateObject.Properties) {
                command.Parameters["?"+property.DataName].Value = property.Getter(objectInstance);
            }

            if (!overrideAutoId) {
                int autoId = idManager.getNewId(intermediateObject.TableName);
                command.Parameters["?id"].Value = autoId;
                intermediateObject.IdProperty.Setter(objectInstance, autoId);
            }

            try {
                connection.Open();
                command.ExecuteNonQuery();
            } catch (Exception error) {
                throw new Exception("WmDirectData: Exception occured in addObject. " + error.Message);
            } finally {
                connection.Close();
            }
            
            int givenId = (int)intermediateObject.IdProperty.Getter.Invoke(objectInstance);

            raiseDataChangeEvent(intermediateObject.TableName, WmDataRowAction.ADD, givenId);
            if (transactionManager != null) {
                transactionManager.registerInsertOrUpdateTransaction(intermediateObject, givenId, WmDataRowAction.ADD, objectInstance, referenceTransactionMomentUtcTicks);
            }
        }
        #endregion

        #region UPDATE: updateDataRow, updateObject
        /// <summary>
        /// Update data row and specify reference transaction moment
        /// </summary>>
        public override void updateDataRow(Type objectType, DataRow updateRow, long referenceTransactionMomentUtcTicks) {
            WmIntermediateObject intermediateObject = getIntermediateObject(objectType, true);
            MySqlConnection connection = new MySqlConnection(dataBaseCredentials.getConnectionString());

            MySqlCommand command = WmDataTools.getMySqlUpdateCommand(dataBaseCredentials, intermediateObject);
            command.Connection = connection;

            foreach (WmIntermediateObjectProperty property in intermediateObject.Properties) {
                command.Parameters["?" + property.DataName].Value = updateRow[property.DataName];
            }

            try {
                connection.Open();
                command.ExecuteNonQuery();

            } catch (Exception error) {
                throw new Exception("WmDirectData: Exception occured in updateDataRow. " + error.Message);
            } finally {
                connection.Close();
            }

            raiseDataChangeEvent(intermediateObject.TableName, WmDataRowAction.UPDATE, (int)updateRow["id"]);
            if (transactionManager != null) {
                transactionManager.registerInsertOrUpdateTransaction(intermediateObject.TableName, updateRow, WmDataRowAction.UPDATE, referenceTransactionMomentUtcTicks);
            }
        }
        /// <summary>
        /// Update object and specify reference transaction moment
        /// </summary>
        public override void updateObject(object objectInstance, long referenceTransactionMomentUtcTicks) {
            Type objectType = objectInstance.GetType();

            WmIntermediateObject intermediateObject = getIntermediateObject(objectType, true);
            MySqlConnection connection = new MySqlConnection(dataBaseCredentials.getConnectionString());

            MySqlCommand command = WmDataTools.getMySqlUpdateCommand(dataBaseCredentials, intermediateObject);
            command.Connection = connection;

            foreach (WmIntermediateObjectProperty property in intermediateObject.Properties) {
                command.Parameters["?" + property.DataName].Value = property.Getter(objectInstance);
            }

            try {
                connection.Open();
                command.ExecuteNonQuery();

            } catch (Exception error) {
                throw new Exception("WmDirectData: Exception occured in updateObject. " + error.Message);
            } finally {
                connection.Close();
            }


            int objectId = (int)intermediateObject.IdProperty.Getter.Invoke(objectInstance);
            raiseDataChangeEvent(intermediateObject.TableName, WmDataRowAction.UPDATE, objectId);
            if (transactionManager != null) {
                transactionManager.registerInsertOrUpdateTransaction(intermediateObject, objectId, WmDataRowAction.UPDATE, objectInstance, referenceTransactionMomentUtcTicks);
            }
        }
        #endregion

        #region DELETE: deleteDataRow, deleteObject, deleteTableContents
        /// <summary>
        /// Delete a data row and specify the reference transaction moment
        /// </summary>
        public override void deleteDataRow(Type objectType, DataRow deleteRow, long referenceTransactionMomentUtcTicks) {
            WmIntermediateObject intermediateObject = getIntermediateObject(objectType, true);
            MySqlConnection connection = new MySqlConnection(dataBaseCredentials.getConnectionString());

            MySqlCommand command = WmDataTools.getMySqlDeleteCommand(dataBaseCredentials, intermediateObject);
            command.Connection = connection;

            int objectId = (int)deleteRow["id"];
            command.Parameters["?id"].Value = objectId;

            try {
                connection.Open();
                command.ExecuteNonQuery();

            } catch (Exception error) {
                throw new Exception("WmDirectData: Exception occured in deleteDataRow. " + error.Message);
            } finally {
                connection.Close();
            }

            raiseDataChangeEvent(intermediateObject.TableName, WmDataRowAction.DELETE, objectId);
            if (transactionManager != null) {
                transactionManager.registerDeleteTransaction(intermediateObject.TableName, objectId, referenceTransactionMomentUtcTicks);
            }
        }
        /// <summary>
        /// Delete the object with the given object id and specify the reference transaction moment
        /// </summary>
        public override void deleteObject(Type objectType, int objectId, long referenceTransactionMomentUtcTicks) {
            WmIntermediateObject intermediateObject = getIntermediateObject(objectType,true);
            MySqlConnection connection = new MySqlConnection(dataBaseCredentials.getConnectionString());

            MySqlCommand command = WmDataTools.getMySqlDeleteCommand(dataBaseCredentials, intermediateObject);
            command.Connection = connection;
            command.Parameters["?id"].Value = objectId;
            
            try {
                connection.Open();
                command.ExecuteNonQuery();

            } catch (Exception error) {
                throw new Exception("WmDirectData: Exception occured in deleteObject. " + error.Message);
            } finally {
                connection.Close();
            }

            raiseDataChangeEvent(intermediateObject.TableName, WmDataRowAction.DELETE, objectId);
            if (transactionManager != null) {
                transactionManager.registerDeleteTransaction(intermediateObject.TableName,objectId, referenceTransactionMomentUtcTicks);
            }
        }

        /// <summary>
        /// Delete table contents (each and every row)
        /// </summary>
        public override void deleteTableContents(Type objectType) {
            WmIntermediateObject intermediateObject = getIntermediateObject(objectType, true);
            string sqlCommandText = "DELETE FROM "+SqlCatalogName+"." + intermediateObject.TableName;
            object emptyReturnObject = getObjectFromSQL(sqlCommandText); // this actually performs the delete query
        }
        #endregion

        #region GET: getDataRow, getDataRows, getObject, getObjects
        /// <summary>
        /// Get a data row matching the given id
        /// </summary>
        public override DataRow getDataRow(Type objectType, int id) {
            string select = "id=" + id;
            return getDataRow(objectType, select);
        }
        /// <summary>
        /// Get a data row matching the select
        /// </summary>
        public override DataRow getDataRow(Type objectType, string select) {
            DataRow resultRow = null;
            DataRow[] resultRows = getDataRows(objectType, select);
            if (resultRows.Length > 0) {
                resultRow = resultRows[0];
            }
            return resultRow;
        }
        /// <summary>
        /// Get data rows matching the select, sorted using the specified sort string
        /// </summary>
        public override DataRow[] getDataRows(Type objectType, string select, string sortString) {
            DataRow[] returnRows = null;
            WmIntermediateObject intermediateObject = getIntermediateObject(objectType, true);
            MySqlConnection connection = new MySqlConnection(dataBaseCredentials.getConnectionString());

            string cmdText = "SELECT * FROM " + dataBaseCredentials.Catalog + "." + intermediateObject.TableName;
            if (!String.IsNullOrEmpty(select)) {
                cmdText += " WHERE " + select;
            }
            if (!String.IsNullOrEmpty(sortString)) {
                cmdText += " ORDER BY " + sortString;
            }

            MySqlCommand command = new MySqlCommand(cmdText, connection);
            try {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();
                DataTable dataTable = intermediateObject.DataTable;

                Stopwatch stopwatch = Stopwatch.StartNew();
                dataTable.Load(reader);
                stopwatch.Stop();
                Trace.WriteLine("WmDirectData: getDataRows() objectType " + objectType.ToString() + " dataTable.Load(reader) took " + stopwatch.ElapsedMilliseconds + "  ms.");
                stopwatch.Reset();
                stopwatch.Start();

                returnRows = dataTable.Select(null, sortString);
                Trace.WriteLine("WmDirectData: getDataRows() objectType " + objectType.ToString() + " dataTable.Select() took " + stopwatch.ElapsedMilliseconds + "  ms.");
                

            } catch (Exception error) {
                throw new Exception("WmDirectData: Exception occured in getDataRows. " + error.Message);
            } finally {
                connection.Close();
            }

            return returnRows;
        }

        /// <summary>
        /// Get object matching the specified id
        /// </summary>
        public override object getObject(Type objectType, int id) {
            string select = "id=" + id;
            return getObject(objectType, select);
        }

        /// <summary>
        /// Get object matching the select
        /// </summary>
        public override object getObject(Type objectType, string select) {
            ArrayList tmpRes = getObjects(objectType, select);
            if (tmpRes.Count > 0) {
                return tmpRes[0];
            } else {
                return null;
            }
        }

        /// <summary>
        /// Get objects matching the select
        /// </summary>
        public override ArrayList getObjects(Type objectType, string select) {
            return getObjects(objectType, select, -1);
        }
        /// <summary>
        /// Get objects matching the select, limiting the maximum result set
        /// </summary>
        public ArrayList getObjects(Type objectType, string select, int maximumResultsLimit) {
            return getObjects(objectType, select, null,0,maximumResultsLimit);
        }
       
        /// <summary>
        /// Get objects matching the select, limiting the maximum result set
        /// </summary>
        public ArrayList getObjects(Type objectType, string select, string orderByStatement, int startIndex, int limit) {
            WmIntermediateObject intermediateObject = getIntermediateObject(objectType, true);
            ArrayList resultList = new ArrayList();
            MySqlConnection connection = new MySqlConnection(dataBaseCredentials.getConnectionString());

            string cmdText = "SELECT * FROM " + dataBaseCredentials.Catalog + "." + intermediateObject.TableName;
            if (!String.IsNullOrEmpty(select)) {
                cmdText += " WHERE " + select;
            }

            if(!String.IsNullOrEmpty(orderByStatement)){
                cmdText+= " "+orderByStatement+" ";
            }

            string limitStatement = null;
            if (startIndex > 0 && limit < 0) {
                limitStatement = "LIMIT " + startIndex + ",10000000000000000000";
            } else if (startIndex > 0 && limit > 0) {
                limitStatement = "LIMIT " + startIndex + "," + limit;
            } else if (limit > 0) {
                limitStatement = "LIMIT " + limit;
            }

            cmdText += " "+limitStatement;
            
            MySqlCommand command = new MySqlCommand(cmdText, connection);
            try {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                while (reader.Read()) {

                    object objectInstance = intermediateObject.Creator.Invoke();
                    foreach (WmIntermediateObjectProperty property in intermediateObject.Properties) {
                        int ordinal = property.DataColumnIndex;

                        if (ordinal == -1) {
                            ordinal = reader.GetOrdinal(property.DataName);
                            property.DataColumnIndex = ordinal;
                        }
                        object value = reader[ordinal];

                        if (value != null && !(value is DBNull)) {
                            if (value is sbyte) {
                                value = Convert.ToBoolean(value);
                            }
                            property.Setter.Invoke(objectInstance, value);
                        }
                    }
                    resultList.Add(objectInstance);
                }
                reader.Close();
            } catch (Exception error) {
                throw new Exception("WmDirectData: Exception occured in getObjects. " + error.Message);
            } finally {
                connection.Close();
            }

            return resultList;
        }

        /// <summary>
        /// Returns the current row count using a SQL COUNT statement
        /// </summary>
        public override int getRowCount(Type objectType) {
            string cmdText = "SELECT COUNT(*) FROM " + dataBaseCredentials.Catalog + "." + WmDataTools.getTableName(objectType) + ";";
            object returnValue = getObjectFromSQL(cmdText);
            if (!(returnValue is long)) {
                return 0;
            } else {
                return Convert.ToInt32((long)returnValue);
            }
        }

        #endregion


  
        /// <summary>
        /// Returns an ArrayList of objects resulting from a full query, converted into the target objec type. Try to talk one query per object target type and for maximum performance (resultSetColumnsAreAlwaysInTheSameOrder=true)
        /// </summary>
        public ArrayList getObjectsFromFullQuery(string fullQuery, Type targetObjectType, bool resultSetColumnsAreAlwaysInTheSameOrder) {
            WmIntermediateObject intermediateObject = getIntermediateObject(targetObjectType,false);
            ArrayList resultList = new ArrayList();
            MySqlConnection connection = new MySqlConnection(dataBaseCredentials.getConnectionString());

            string cmdText = fullQuery;
            
            MySqlCommand command = new MySqlCommand(cmdText, connection);
            try {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();
              
                while (reader.Read()) {

                    object objectInstance = intermediateObject.Creator.Invoke();
                    foreach (WmIntermediateObjectProperty property in intermediateObject.Properties) {
                        int ordinal = property.DataColumnIndex;

                        object value = null;
                        if (resultSetColumnsAreAlwaysInTheSameOrder) { // then we use the fast way of reading columns
                            if (ordinal == -1) {
                                ordinal = reader.GetOrdinal(property.DataName);
                                property.DataColumnIndex = ordinal;
                            }

                            if(ordinal!=-1){ // we don't know if the resultset contains every property of the target object, ignore any missing
                                value = reader[ordinal];
                            }

                        } else { // this is the slower method, reading values by property data names
                           value = reader[property.DataName];
                        }
                       
                        if (value != null && !(value is DBNull)) {
                            if (value is sbyte) {
                                value = Convert.ToBoolean(value);
                            }
                            property.Setter.Invoke(objectInstance, value);
                        }
                    }
                    resultList.Add(objectInstance);
                }
                reader.Close();
            } catch (Exception error) {
                throw new Exception("WmDirectData: Exception occured in getObjectsFromFullQuery. " + error.Message);
            } finally {
                connection.Close();
            }

            return resultList;
        }
        
    }
}