/*
 * Copyright (c) Contributors, http://whitecore-sim.org/, http://aurora-sim.org
 * See CONTRIBUTORS.TXT for a full list of copyright holders.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *     * Redistributions of source code must retain the above copyright
 *       notice, this list of conditions and the following disclaimer.
 *     * Redistributions in binary form must reproduce the above copyright
 *       notice, this list of conditions and the following disclaimer in the
 *       documentation and/or other materials provided with the distribution.
 *     * Neither the name of the WhiteCore-Sim Project nor the
 *       names of its contributors may be used to endorse or promote products
 *       derived from this software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using MySql.Data.MySqlClient;
using WhiteCore.DataManager.Migration;
using WhiteCore.Framework.ConsoleFramework;
using WhiteCore.Framework.Services;
using WhiteCore.Framework.Utilities;

namespace WhiteCore.DataManager.MySQL
{
    public class MySQLDataLoader : DataManagerBase
    {
        string m_connectionString = "";

        public override string Identifier {
            get { return "MySQLData"; }
        }

        #region Database

        public override void ConnectToDatabase (string connectionString, string migratorName, bool validateTables)
        {
            m_connectionString = connectionString;
            if (string.IsNullOrWhiteSpace(connectionString)) {
                MainConsole.Instance.Error("[MySQL]: Database and connection details not supplied - Aborting!");
                MainConsole.Instance.Error("[MySQL]: Please restart in configuration mode - eg. 'whitecore -config'");
                Environment.Exit(0);            // Invalid configuration.. exit
            }

            MySqlConnection c = new MySqlConnection (connectionString);
            int subStrA = connectionString.IndexOf ("Database=", StringComparison.Ordinal);
            int subStrB = connectionString.IndexOf (";", subStrA, StringComparison.Ordinal);
            string noDatabaseConnector = m_connectionString.Substring (0, subStrA) +
                                         m_connectionString.Substring (subStrB + 1);

        retry:
            try {
                ExecuteNonQuery (noDatabaseConnector, "create schema IF NOT EXISTS " + c.Database,
                                new Dictionary<string, object> (), false);
            } catch {
                MainConsole.Instance.Error (
                    "[MySQL]: We cannot connect to the MySQL instance you have provided. Please make sure it is online, and then press enter to try again.");
                try {
                    Console.Read ();
                } catch {
                }
                goto retry;
            }
            c.Close ();

            var migrationManager = new MigrationManager(this, migratorName, validateTables);
            try {
                migrationManager.DetermineOperation ();
                migrationManager.ExecuteOperation ();
            } catch {
                MainConsole.Instance.Error ("[MySQL]: Unable to determine migration requirements.");

            }
        }

        public void CloseDatabase(MySqlConnection connection)
        {
            //Interlocked.Decrement (ref m_locked);
            //connection.Close();
            //connection.Dispose();
        }

        public override void CloseDatabase(DataReaderConnection connection)
        {
            if (connection != null) {
                //if (connection.DataReader != null)
                //    connection.DataReader.Close ();
                    
                connection.Dispose ();
            }

            //Interlocked.Decrement (ref m_locked);
            //m_connection.Close();
            //m_connection.Dispose();
        }

        #endregion

        #region Query

        public IDataReader Query(string sql, Dictionary<string, object> parameters)
        {
            try
            {
                MySqlParameter[] param = new MySqlParameter[parameters.Count];
                int i = 0;
                foreach (KeyValuePair<string, object> p in parameters)
                {
                    param[i] = new MySqlParameter(p.Key, p.Value);
                    i++;
                }
                return MySqlHelper.ExecuteReader(m_connectionString, sql, param);
            }
            catch (Exception e)
            {
                MainConsole.Instance.Error("[MySQL]: Query(" + sql + "), " + e);
                return null;
            }
        }

        public void ExecuteNonQuery(string sql, Dictionary<string, object> parameters)
        {
            ExecuteNonQuery(m_connectionString, sql, parameters);
        }

        public void ExecuteNonQuery(string connStr, string sql, Dictionary<string, object> parameters)
        {
            ExecuteNonQuery(connStr, sql, parameters, true);
        }

        public void ExecuteNonQuery(string connStr, string sql, Dictionary<string, object> parameters, bool spamConsole)
        {
            try
            {
                MySqlParameter[] param = new MySqlParameter[parameters.Count];
                int i = 0;
                foreach (KeyValuePair<string, object> p in parameters)
                {
                    param[i] = new MySqlParameter(p.Key, p.Value);
                    i++;
                }
                MySqlHelper.ExecuteNonQuery(connStr, sql, param);
            }
            catch (Exception e)
            {
                if (spamConsole)
                    MainConsole.Instance.ErrorFormat("[MySQL]: ExecuteNonQuery({0}), {1}", sql, e);
                else
                    throw e;
            }
        }

        public override List<string> QueryFullData(string whereClause, string table, string wantedValue)
        {
            string query = string.Format("select {0} from {1} {2}", wantedValue, table, whereClause);
            return QueryFullData2(query);
        }

        public override List<string> QueryFullData(string whereClause, QueryTables tables, string wantedValue)
        {
            string query = string.Format("SELECT {0} FROM {1} {2}", wantedValue, tables.ToSQL(), whereClause);
            return QueryFullData2(query);
        }

        List<string> QueryFullData2(string query)
        {
            IDataReader reader;
            List<string> retVal = new List<string>();
            try
            {
                using (reader = Query(query, new Dictionary<string, object>()))
                {
                    if (reader != null) {
                        while (reader.Read ()) {
                            for (int i = 0; i < reader.FieldCount; i++) {
                                retVal.Add (reader.GetString (i));
                            }
                        }
                    }
                    return retVal;
                }
            }
            catch (Exception e)
            {
                MainConsole.Instance.Error("[MySQL]: QueryFullData(" + query + "), " + e);
                return new List<string>();
            }
        }

        public override DataReaderConnection QueryData(string whereClause, string table, string wantedValue)
        {
            string query = string.Format("select {0} from {1} {2}", wantedValue, table, whereClause);
            return new DataReaderConnection {DataReader = QueryData2(query)};
        }

        public override DataReaderConnection QueryData(string whereClause, QueryTables tables, string wantedValue)
        {
            string query = string.Format("SELECT {0} FROM {1} {2}", wantedValue, tables.ToSQL(), whereClause);
            return new DataReaderConnection {DataReader = QueryData2(query)};
        }

        private IDataReader QueryData2(string query)
        {
            return Query(query, new Dictionary<string, object>());
        }

        public override List<string> Query(string[] wantedValue, string table, QueryFilter queryFilter,
                                           Dictionary<string, bool> sort, uint? start, uint? count)
        {
            string query = string.Format("SELECT {0} FROM {1}", string.Join(", ", wantedValue), table);
            return Query2(query, queryFilter, sort, start, count);
        }

        public override List<string> Query(string[] wantedValue, QueryTables tables, QueryFilter queryFilter,
                                           Dictionary<string, bool> sort, uint? start, uint? count)
        {
            string query = string.Format("SELECT {0} FROM {1}", string.Join(", ", wantedValue), tables.ToSQL());
            return Query2(query, queryFilter, sort, start, count);
        }

        List<string> Query2(string sqll, QueryFilter queryFilter, Dictionary<string, bool> sort, uint? start,
                                    uint? count)
        {
            string query = sqll;
            Dictionary<string, object> ps = new Dictionary<string, object>();
            List<string> retVal = new List<string>();
            List<string> parts;

            if (queryFilter != null && queryFilter.Count > 0)
            {
                query += " WHERE " + queryFilter.ToSQL('?', out ps);
            }

            if (sort != null && sort.Count > 0)
            {
                parts = new List<string>();
                foreach (KeyValuePair<string, bool> sortOrder in sort)
                {
                    parts.Add(string.Format("`{0}` {1}", sortOrder.Key, sortOrder.Value ? "ASC" : "DESC"));
                }
                query += " ORDER BY " + string.Join(", ", parts.ToArray());
            }

            if (start.HasValue)
            {
                query += " LIMIT " + start.Value;
                if (count.HasValue)
                {
                    query += ", " + count.Value;
                }
            }

            IDataReader reader;
            int i;
            try
            {
                using (reader = Query(query, ps))
                {
                    if (reader != null) {
                        while (reader.Read ()) {
                            for (i = 0; i < reader.FieldCount; i++) {
                                Type r = reader [i].GetType ();
                                retVal.Add (r == typeof (DBNull) ? null : reader.GetString (i));
                            }
                        }
                    }
                    return retVal;
                }
            }
            catch (Exception e)
            {
                MainConsole.Instance.Error("[MySQL]: Query(" + query + "), " + e);
                return null;
            }
        }

        /*public override Dictionary<string, List<string>> QueryNames(string[] wantedValue, string table, QueryFilter queryFilter, Dictionary<string, bool> sort, uint? start, uint? count)
        {
        }*/

        public override Dictionary<string, List<string>> QueryNames(string[] keyRow, object[] keyValue, string table,
                                                                    string wantedValue)
        {
            string query = string.Format("select {0} from {1} where ", wantedValue, table);
            return QueryNames2(keyRow, keyValue, query);
        }

        public override Dictionary<string, List<string>> QueryNames(string[] keyRow, object[] keyValue,
                                                                    QueryTables tables, string wantedValue)
        {
            string query = string.Format("SELECT {0} FROM {1} where ", wantedValue, tables.ToSQL());
            return QueryNames2(keyRow, keyValue, query);
        }

        Dictionary<string, List<string>> QueryNames2(string[] keyRow, object[] keyValue, string query)
        {
            IDataReader reader;
            Dictionary<string, List<string>> retVal = new Dictionary<string, List<string>>();
            Dictionary<string, object> ps = new Dictionary<string, object>();
            int i = 0;
            foreach (object value in keyValue)
            {
                query += string.Format("{0} = ?{1} and ", keyRow[i], keyRow[i]);
                ps["?" + keyRow[i]] = value;
                i++;
            }
            query = query.Remove(query.Length - 5);

            try
            {
                using (reader = Query (query, ps)) {
                    if (reader != null) {
                        while (reader.Read ()) {
                            for (i = 0; i < reader.FieldCount; i++) {
                                Type r = reader [i].GetType ();
                                AddValueToList (ref retVal, reader.GetName (i),
                                               r == typeof (DBNull) ? null : reader [i].ToString ());
                            }
                        }
                    }
                    return retVal;
                }
            }
            catch (Exception e)
            {
                MainConsole.Instance.Error("[MySQL]: QueryNames(" + query + "), " + e);
                return new Dictionary<string, List<string>>();
            }
        }

        void AddValueToList(ref Dictionary<string, List<string>> dic, string key, string value)
        {
            if (!dic.ContainsKey(key))
            {
                dic.Add(key, new List<string>());
            }

            dic[key].Add(value);
        }

        #endregion

        #region Update

        public override bool Update(string table, Dictionary<string, object> values,
                                    Dictionary<string, int> incrementValue, QueryFilter queryFilter, uint? start,
                                    uint? count)
        {
            if ((values == null || values.Count < 1) && (incrementValue == null || incrementValue.Count < 1))
            {
                MainConsole.Instance.Warn("Update attempted with no values");
                return false;
            }

            string query = string.Format("UPDATE {0}", table);
            Dictionary<string, object> ps = new Dictionary<string, object>();

            string filter = "";
            if (queryFilter != null && queryFilter.Count > 0)
            {
                filter = " WHERE " + queryFilter.ToSQL('?', out ps);
            }

            List<string> parts = new List<string>();
            if (values != null)
            {
                foreach (KeyValuePair<string, object> value in values)
                {
                    string key = "?updateSet_" + value.Key.Replace("`", "");
                    ps[key] = value.Value;
                    parts.Add(string.Format("{0} = {1}", value.Key, key));
                }
            }
            if (incrementValue != null)
            {
                foreach (KeyValuePair<string, int> value in incrementValue)
                {
                    string key = "?updateSet_increment_" + value.Key.Replace("`", "");
                    ps[key] = value.Value;
                    parts.Add(string.Format("{0} = {0} + {1}", value.Key, key));
                }
            }

            query += " SET " + string.Join(", ", parts.ToArray()) + filter;

            if (start.HasValue)
            {
                query += " LIMIT " + start.Value;
                if (count.HasValue)
                {
                    query += ", " + count.Value;
                }
            }

            try
            {
                ExecuteNonQuery(query, ps);
            }
            catch (MySqlException e)
            {
                MainConsole.Instance.Error("[MySQL]: Update(" + query + "), " + e);
            }
            return true;
        }

        #endregion

        #region Insert

        public override bool InsertMultiple(string table, List<object[]> values)
        {
            string query = string.Format("insert into {0} select ", table);
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            int i = 0;
            foreach (object[] value in values)
            {
                foreach (object v in value)
                {
                    parameters[Util.ConvertDecString(i)] = v;
                    query += "?" + Util.ConvertDecString(i++) + ",";
                }
                query = query.Remove(query.Length - 1);
                query += " union all select ";
            }
            query = query.Remove(query.Length - (" union all select ").Length);

            try
            {
                ExecuteNonQuery(query, parameters);
            }
            catch (Exception e)
            {
                MainConsole.Instance.Error("[MySQL]: Insert(" + query + "), " + e);
            }
            return true;
        }

        public override bool Insert(string table, object[] values)
        {
            string query = string.Format("insert into {0} values (", table);
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            int i = 0;
            foreach (object o in values)
            {
                parameters[Util.ConvertDecString(i)] = o;
                query += "?" + Util.ConvertDecString(i++) + ",";
            }
            query = query.Remove(query.Length - 1);
            query += ")";

            try
            {
                ExecuteNonQuery(query, parameters);
            }
            catch (Exception e)
            {
                MainConsole.Instance.Error("[MySQL]: Insert(" + query + "), " + e);
            }
            return true;
        }

        bool InsertOrReplace(string table, Dictionary<string, object> row, bool insert)
        {
            string query = (insert ? "INSERT" : "REPLACE") + " INTO " + table + " (" +
                           string.Join(", ", row.Keys.ToArray()) + ")";
            Dictionary<string, object> ps = new Dictionary<string, object>();
            foreach (KeyValuePair<string, object> field in row)
            {
                string key = "?" +
                             field.Key.Replace("`", "")
                                  .Replace("(", "_")
                                  .Replace(")", "")
                                  .Replace(" ", "_")
                                  .Replace("-", "minus")
                                  .Replace("+", "add")
                                  .Replace("/", "divide")
                                  .Replace("*", "multiply");
                ps[key] = field.Value;
            }
            query += " VALUES( " + string.Join(", ", ps.Keys.ToArray()) + " )";

            try
            {
                ExecuteNonQuery(query, ps);
            }
            catch (Exception e)
            {
                MainConsole.Instance.Error("[MySQL]: " + (insert ? "Insert" : "Replace") + "(" + query + "), " +
                                           e);
            }
            return true;
        }

        public override bool Insert(string table, Dictionary<string, object> row)
        {
            return InsertOrReplace(table, row, true);
        }

        public override bool Insert(string table, object[] values, string updateKey, object updateValue)
        {
            string query = string.Format("insert into {0} VALUES(", table);
            Dictionary<string, object> param = new Dictionary<string, object>();
            int i = 0;
            foreach (object o in values)
            {
                param["?" + Util.ConvertDecString(i)] = o;
                query += "?" + Util.ConvertDecString(i++) + ",";
            }
            param["?update"] = updateValue;
            query = query.Remove(query.Length - 1);
            query += string.Format(") ON DUPLICATE KEY UPDATE {0} = ?update", updateKey);
            try
            {
                ExecuteNonQuery(query, param);
            }
            catch (Exception e)
            {
                MainConsole.Instance.Error("[MySQL]: Insert(" + query + "), " + e);
                return false;
            }
            return true;
        }

        public override bool InsertSelect(string tableA, string[] fieldsA, string tableB, string[] valuesB)
        {
            string query = string.Format("INSERT INTO {0}{1} SELECT {2} FROM {3}",
                                         tableA,
                                         (fieldsA.Length > 0 ? " (" + string.Join(", ", fieldsA) + ")" : ""),
                                         string.Join(", ", valuesB),
                                         tableB
                );

            try
            {
                ExecuteNonQuery(query, new Dictionary<string, object>(0));
            }
            catch (Exception e)
            {
                MainConsole.Instance.Error("[MySQL]: INSERT .. SELECT (" + query + "), " + e);
            }
            return true;
        }

        #endregion

        #region REPLACE INTO

        public override bool Replace(string table, Dictionary<string, object> row)
        {
            return InsertOrReplace(table, row, false);
        }

        #endregion

        #region Delete

        public override bool DeleteByTime(string table, string key)
        {
            // the only call here is to delete any "tokens.validity < now. i.e. expired tokens 
            // validity is a unix_timestamp saved as an int
            QueryFilter filter = new QueryFilter();
            filter.andLessThanEqFilters["(`" + key.Replace("`", "") + "` - UNIX_TIMESTAMP())"] = 0;

            return Delete(table, filter);
        }

        public override bool Delete(string table, QueryFilter queryFilter)
        {
            Dictionary<string, object> ps = new Dictionary<string, object>();
            string query = "DELETE FROM " + table +
                           (queryFilter != null ? (" WHERE " + queryFilter.ToSQL('?', out ps)) : "");

            try
            {
                ExecuteNonQuery(query, ps);
            }
            catch (Exception e)
            {
                MainConsole.Instance.Error("[MySQL]: Delete(" + query + "), " + e);
                return false;
            }
            return true;
        }

        #endregion

        public override string ConCat(string[] toConcat)
        {
            string returnValue = toConcat.Aggregate("concat(", (current, s) => current + (s + ","));
            return returnValue.Substring(0, returnValue.Length - 1) + ")";
        }

        #region Tables

        public override void CreateTable(string table, ColumnDefinition[] columns, IndexDefinition[] indexDefinitions)
        {
            table = table.ToLower();
            if (TableExists(table))
            {
                throw new DataManagerException("Trying to create a table with name of one that already exists.");
            }

            IndexDefinition primary = null;
            foreach (IndexDefinition index in indexDefinitions)
            {
                if (index.Type == IndexType.Primary)
                {
                    primary = index;
                    break;
                }
            }

            List<string> columnDefinition = new List<string>();

            foreach (ColumnDefinition column in columns)
            {
                columnDefinition.Add("`" + column.Name + "` " + GetColumnTypeStringSymbol(column.Type));
            }
            if (primary != null && primary.Fields.Length > 0)
            {
                columnDefinition.Add("PRIMARY KEY (`" + string.Join("`, `", primary.Fields) + "`)");
            }

            List<string> indicesQuery = new List<string>(indexDefinitions.Length);
            foreach (IndexDefinition index in indexDefinitions)
            {
                string type = "KEY";
                switch (index.Type)
                {
                    case IndexType.Primary:
                        continue;
                    case IndexType.Unique:
                        type = "UNIQUE";
                        break;
                    case IndexType.Index:
                    default:
                        type = "KEY";
                        break;
                }

                if (index.IndexSize == 0)
                    indicesQuery.Add(string.Format("{0}( {1} )", type, "`" + string.Join("`, `", index.Fields) + "`"));
                else
                    indicesQuery.Add(string.Format("{0}( {1} )", type, "`" + string.Join("`, `", index.Fields) + "`"+"("+index.IndexSize+")"));

            }

            string query = string.Format("create table " + table + " ( {0} {1}) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci",
                                         string.Join(", ", columnDefinition.ToArray()),
                                         indicesQuery.Count > 0
                                             ? ", " + string.Join(", ", indicesQuery.ToArray())
                                             : string.Empty);

            try
            {
                ExecuteNonQuery(query, new Dictionary<string, object>());
            }
            catch (Exception e)
            {
                MainConsole.Instance.ErrorFormat("[MySQL]: CreateTable: {0}", e);
            }
        }

        public override void UpdateTable(string table, ColumnDefinition[] columns, IndexDefinition[] indexDefinitions,
                                         Dictionary<string, string> renameColumns)
        {
            table = table.ToLower();
            if (!TableExists(table))
            {
                throw new DataManagerException("Trying to update a table with name of one that does not exist.");
            }

            List<ColumnDefinition> oldColumns = ExtractColumnsFromTable(table);

            Dictionary<string, ColumnDefinition> removedColumns = new Dictionary<string, ColumnDefinition>();
            Dictionary<string, ColumnDefinition> modifiedColumns = new Dictionary<string, ColumnDefinition>();

            Dictionary<string, ColumnDefinition> addedColumns =
                columns.Where(column => !oldColumns.Contains(column)).ToDictionary(column => column.Name.ToLower());
            foreach (ColumnDefinition column in oldColumns.Where(column => !columns.Contains(column)))
            {
                if (addedColumns.ContainsKey(column.Name.ToLower()))
                {
                    if (column.Name.ToLower() != addedColumns[column.Name.ToLower()].Name.ToLower() ||
                        column.Type != addedColumns[column.Name.ToLower()].Type)
                    {
                        modifiedColumns.Add(column.Name.ToLower(), addedColumns[column.Name.ToLower()]);
                    }
                    addedColumns.Remove(column.Name.ToLower());
                }
                else
                {
                    removedColumns.Add(column.Name.ToLower(), column);
                }
            }

            try
            {
                foreach (
                    string query in
                        addedColumns.Values.Select(
                            column => "add `" + column.Name + "` " + GetColumnTypeStringSymbol(column.Type) +
                                      " ")
                                    .Select(
                                        addedColumnsQuery =>
                                        string.Format("alter table " + table + " " + addedColumnsQuery)))
                {
                    ExecuteNonQuery(query, new Dictionary<string, object>());
                }
                foreach (
                    string query in modifiedColumns.Values.Select(column => "modify column `" + column.Name + "` " +
                                                                            GetColumnTypeStringSymbol(column.Type) + " ")
                                                   .Select(
                                                       modifiedColumnsQuery =>
                                                       string.Format("alter table " + table + " " + modifiedColumnsQuery))
                    )
                {
                    ExecuteNonQuery(query, new Dictionary<string, object>());
                }
                foreach (
                    string query in
                        removedColumns.Values.Select(column => "drop `" + column.Name + "` ")
                                      .Select(
                                          droppedColumnsQuery =>
                                          string.Format("alter table " + table + " " + droppedColumnsQuery)))
                {
                    ExecuteNonQuery(query, new Dictionary<string, object>());
                }
            }
            catch (Exception e)
            {
                MainConsole.Instance.ErrorFormat("[MySQL]: UpdateTable: {0}", e);
            }

            Dictionary<string, IndexDefinition> oldIndicesDict = ExtractIndicesFromTable(table);

            List<string> removeIndices = new List<string>();
            List<string> oldIndexNames = new List<string>(oldIndicesDict.Count);
            List<IndexDefinition> oldIndices = new List<IndexDefinition>(oldIndicesDict.Count);
            List<IndexDefinition> newIndices = new List<IndexDefinition>();

            foreach (KeyValuePair<string, IndexDefinition> oldIndex in oldIndicesDict)
            {
                oldIndexNames.Add(oldIndex.Key);
                oldIndices.Add(oldIndex.Value);
            }
            int i = 0;
            foreach (IndexDefinition oldIndex in oldIndices)
            {
                bool found = false;
                foreach (IndexDefinition newIndex in indexDefinitions)
                {
                    if (oldIndex.Equals(newIndex))
                    {
                        found = true;
                        break;
                    }
                }
                if (!found)
                {
                    removeIndices.Add(oldIndexNames[i]);
                }
                ++i;
            }

            foreach (IndexDefinition newIndex in indexDefinitions)
            {
                bool found = false;
                foreach (IndexDefinition oldIndex in oldIndices)
                {
                    if (oldIndex.Equals(newIndex))
                    {
                        found = true;
                        break;
                    }
                }
                if (!found)
                {
                    newIndices.Add(newIndex);
                }
            }

            foreach (string oldIndex in removeIndices)
            {
                ExecuteNonQuery(string.Format("ALTER TABLE `{0}` DROP INDEX `{1}`", table, oldIndex),
                                new Dictionary<string, object>());
            }
            foreach (IndexDefinition newIndex in newIndices)
            {
                ExecuteNonQuery(
                    string.Format("ALTER TABLE `{0}` ADD {1} (`{2}`)", table,
                                  newIndex.Type == IndexType.Primary
                                      ? "PRIMARY KEY"
                                      : (newIndex.Type == IndexType.Unique ? "UNIQUE" : "INDEX"),
                                  string.Join("`, `", newIndex.Fields)), new Dictionary<string, object>());
            }
        }

        public override string GetColumnTypeStringSymbol(ColumnTypes type)
        {
            switch (type)
            {
                case ColumnTypes.Double:
                    return "DOUBLE";
                case ColumnTypes.Integer11:
                    return "INT(11)";
                case ColumnTypes.Integer30:
                    return "INT(30)";
                case ColumnTypes.UInteger11:
                    return "INT(11) UNSIGNED";
                case ColumnTypes.UInteger30:
                    return "INT(30) UNSIGNED";
                case ColumnTypes.Char40:
                    return "CHAR(40)";
                case ColumnTypes.Char39:
                    return "CHAR(39)";
                case ColumnTypes.Char38:
                    return "CHAR(38)";
                case ColumnTypes.Char37:
                    return "CHAR(37)";
                case ColumnTypes.Char36:
                    return "CHAR(36)";
                case ColumnTypes.Char35:
                    return "CHAR(35)";
                case ColumnTypes.Char34:
                    return "CHAR(34)";
                case ColumnTypes.Char33:
                    return "CHAR(33)";
                case ColumnTypes.Char32:
                    return "CHAR(32)";
                case ColumnTypes.Char5:
                    return "CHAR(5)";
                case ColumnTypes.Char1:
                    return "CHAR(1)";
                case ColumnTypes.Char2:
                    return "CHAR(2)";
                case ColumnTypes.String:
                    return "TEXT";
                case ColumnTypes.String10:
                    return "VARCHAR(10)";
                case ColumnTypes.String16:
                    return "VARCHAR(16)";
                case ColumnTypes.String30:
                    return "VARCHAR(30)";
                case ColumnTypes.String32:
                    return "VARCHAR(32)";
                case ColumnTypes.String36:
                    return "VARCHAR(36)";
                case ColumnTypes.String45:
                    return "VARCHAR(45)";
                case ColumnTypes.String50:
                    return "VARCHAR(50)";
                case ColumnTypes.String64:
                    return "VARCHAR(64)";
                case ColumnTypes.String128:
                    return "VARCHAR(128)";
                case ColumnTypes.String100:
                    return "VARCHAR(100)";
                case ColumnTypes.String255:
                    return "VARCHAR(255)";
                case ColumnTypes.String512:
                    return "VARCHAR(512)";
                case ColumnTypes.String1024:
                    return "VARCHAR(1024)";
                case ColumnTypes.String8196:
                    return "VARCHAR(8196)";
                case ColumnTypes.Text:
                    return "TEXT";
                case ColumnTypes.MediumText:
                    return "MEDIUMTEXT";
                case ColumnTypes.LongText:
                    return "LONGTEXT";
                case ColumnTypes.Blob:
                    return "BLOB";
                case ColumnTypes.LongBlob:
                    return "LONGBLOB";
                case ColumnTypes.Date:
                    return "DATE";
                case ColumnTypes.DateTime:
                    return "DATETIME";
                case ColumnTypes.Float:
                    return "FLOAT";
                case ColumnTypes.TinyInt1:
                    return "TINYINT(1)";
                case ColumnTypes.TinyInt4:
                    return "TINYINT(4)";
                case ColumnTypes.UTinyInt4:
                    return "TINYINT(4) UNSIGNED";
                case ColumnTypes.Binary32:
                    return "BINARY(32)";
                case ColumnTypes.Binary64:
                    return "BINARY(64)";
                case ColumnTypes.UUID:
                    return "CHAR(36)";
                default:
                    throw new DataManagerException("Unknown column type.");
            }
        }

        public override string GetColumnTypeStringSymbol(ColumnTypeDef coldef)
        {
            string symbol;
            switch (coldef.Type)
            {
                case ColumnType.Blob:
                    symbol = "BLOB";
                    break;
                case ColumnType.LongBlob:
                    symbol = "LONGBLOB";
                    break;
                case ColumnType.Boolean:
                    symbol = "TINYINT(1)";
                    break;
                case ColumnType.Char:
                    symbol = "CHAR(" + coldef.Size + ")";
                    break;
                case ColumnType.Date:
                    symbol = "DATE";
                    break;
                case ColumnType.DateTime:
                    symbol = "DATETIME";
                    break;
                case ColumnType.Double:
                    symbol = "DOUBLE";
                    break;
                case ColumnType.Float:
                    symbol = "FLOAT";
                    break;
                case ColumnType.Integer:
                    symbol = "INT(" + coldef.Size + ")" + (coldef.unsigned ? " unsigned" : "");
                    break;
                case ColumnType.TinyInt:
                    symbol = "TINYINT(" + coldef.Size + ")" + (coldef.unsigned ? " unsigned" : "");
                    break;
                case ColumnType.String:
                    symbol = "VARCHAR(" + coldef.Size + ")";
                    break;
                case ColumnType.Text:
                    symbol = "TEXT";
                    break;
                case ColumnType.MediumText:
                    symbol = "MEDIUMTEXT";
                    break;
                case ColumnType.LongText:
                    symbol = "LONGTEXT";
                    break;
                case ColumnType.UUID:
                    symbol = "VARCHAR(36)";
                    break;
                case ColumnType.Binary:
                    symbol = "BINARY(" + coldef.Size + ")";
                    break;
                default:
                    throw new DataManagerException("Unknown column type.");
            }

            return symbol + (coldef.isNull ? " NULL" : " NOT NULL") +
                   ((coldef.isNull && coldef.defaultValue == null)
                        ? " DEFAULT NULL"
                        : (coldef.defaultValue != null ? " DEFAULT '" + coldef.defaultValue.MySqlEscape() + "'" : "")) +
                   ((coldef.Type == ColumnType.Integer || coldef.Type == ColumnType.TinyInt) && coldef.auto_increment
                        ? " AUTO_INCREMENT"
                        : "");
        }

        public override void DropTable(string tableName)
        {
            tableName = tableName.ToLower();
            try
            {
                ExecuteNonQuery(string.Format("drop table {0}", tableName), new Dictionary<string, object>());
            }
            catch (Exception e)
            {
                MainConsole.Instance.ErrorFormat("[MySQL]: DropTable {0}", e);
            }
        }

        public override void ForceRenameTable(string oldTableName, string newTableName)
        {
            newTableName = newTableName.ToLower();
            try
            {
                ExecuteNonQuery(string.Format("RENAME TABLE {0} TO {1}", oldTableName, newTableName),
                                new Dictionary<string, object>());
            }
            catch (Exception e)
            {
                MainConsole.Instance.ErrorFormat("[MySQL]: ForceRenameTable {0}", e);
            }
        }

        protected override void CopyAllDataBetweenMatchingTables(string sourceTableName, string destinationTableName,
                                                                 ColumnDefinition[] columnDefinitions,
                                                                 IndexDefinition[] indexDefinitions)
        {
            sourceTableName = sourceTableName.ToLower();
            destinationTableName = destinationTableName.ToLower();
            try
            {
                ExecuteNonQuery(
                    string.Format("insert into {0} select * from {1}", destinationTableName, sourceTableName),
                    new Dictionary<string, object>());
            }
            catch (Exception e)
            {
                MainConsole.Instance.ErrorFormat("[MySQL]: CopyAllDataBetweenMatchingTables {0}", e);
            }
        }

        public override bool TableExists(string table)
        {
            IDataReader reader;
            List<string> retVal = new List<string>();
            try
            {
                using (reader = Query("show tables", new Dictionary<string, object>()))
                {
                    if (reader != null) {
                        while (reader.Read ()) {
                            for (int i = 0; i < reader.FieldCount; i++) {
                                retVal.Add (reader.GetString (i).ToLower ());
                            }
                        }
                    }
                }
            }
            catch (Exception e)
            {
                MainConsole.Instance.ErrorFormat("[MySQL]: TableExists: {0}", e);
            }
            return retVal.Contains(table.ToLower());
        }

        protected override List<ColumnDefinition> ExtractColumnsFromTable(string tableName)
        {
            var defs = new List<ColumnDefinition>();
            tableName = tableName.ToLower();
            IDataReader rdr = null;
            try
            {
                rdr = Query(string.Format("desc {0}", tableName), new Dictionary<string, object>());
                if (rdr != null) {
                    while (rdr.Read ()) {
                        var name = rdr ["Field"];
                        //var pk = rdr["Key"];
                        var type = rdr ["Type"];
                        //var extra = rdr["Extra"];
                        object defaultValue = rdr ["Default"];

                        ColumnTypeDef typeDef = ConvertTypeToColumnType (type.ToString ());
                        typeDef.isNull = rdr ["Null"].ToString () == "YES";
                        typeDef.auto_increment = rdr ["Extra"].ToString ().IndexOf ("auto_increment", StringComparison.Ordinal) >= 0;
                        typeDef.defaultValue = defaultValue is DBNull
                                                   ? null
                                                   : defaultValue.ToString ();
                        defs.Add (new ColumnDefinition {
                            Name = name.ToString (),
                            Type = typeDef,
                        });
                    }
                }
            }
            catch (Exception e)
            {
                MainConsole.Instance.ErrorFormat("[MySQL]: ExtractColumnsFromTable: {0}", e);
            }
            finally
            {
                try
                {
                    if (rdr != null)
                    {
                        rdr.Close();
                        //rdr.Dispose ();
                    }
                }
                catch (Exception e)
                {
                    MainConsole.Instance.DebugFormat("[MySQL]: ExtractColumnsFromTable: {0}", e);
                }
            }
            return defs;
        }

        protected override Dictionary<string, IndexDefinition> ExtractIndicesFromTable(string tableName)
        {
            Dictionary<string, IndexDefinition> defs = new Dictionary<string, IndexDefinition>();
            tableName = tableName.ToLower();
            IDataReader rdr = null;
            Dictionary<string, Dictionary<uint, string>> indexLookup =
                new Dictionary<string, Dictionary<uint, string>>();
            Dictionary<string, bool> indexIsUnique = new Dictionary<string, bool>();

            try
            {
                rdr = Query(string.Format("SHOW INDEX IN {0}", tableName), new Dictionary<string, object>());
                if (rdr != null) {
                    while (rdr.Read ()) {
                        string name = rdr ["Column_name"].ToString ();
                        bool unique = uint.Parse (rdr ["Non_unique"].ToString ()) == 0;
                        string index = rdr ["Key_name"].ToString ();
                        uint sequence = uint.Parse (rdr ["Seq_in_index"].ToString ());
                        if (!indexLookup.ContainsKey (index)) {
                            indexLookup [index] = new Dictionary<uint, string> ();
                        }
                        indexIsUnique [index] = unique;
                        indexLookup [index] [sequence - 1] = name;
                    }
                }
            }
            catch (Exception e)
            {
                MainConsole.Instance.ErrorFormat("[MySQL]: ExtractIndicesFromTable: {0}", e);
            }
            finally
            {
                try
                {
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                }
                catch (Exception e)
                {
                    MainConsole.Instance.DebugFormat("[MySQL]: ExtractIndicesFromTable: {0}", e);
                }
            }

            foreach (KeyValuePair<string, Dictionary<uint, string>> index in indexLookup)
            {
                //index.Value.OrderBy(x => x.Key);
                defs[index.Key] = new IndexDefinition
                                      {
                                          Fields = index.Value.Values.ToArray (),
                                          Type =
                                              (indexIsUnique[index.Key]
                                                   ? (index.Key == "PRIMARY" ? IndexType.Primary : IndexType.Unique)
                                                   : IndexType.Index)
                                      };
            }

            return defs;
        }

        #endregion

        public override IGenericData Copy()
        {
            return new MySQLDataLoader();
        }
    }
}
