using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlTypes;
using System.IO;
using System.Linq;
using System.Threading;
using System.Web;

namespace KJD_Web.Utils
{
    public class MysqlUtils
    {
        public string Message = "";

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string ConnString = "";

        /// <summary>
        /// 数据库连接
        /// </summary>
        private MySqlConnection Conn;

        /// <summary>
        /// 数据库读取对象
        /// </summary>
        private MySqlDataReader reader;

        /// <summary>
        /// 数据库执行对象
        /// </summary>
        private MySqlCommand cmd = new MySqlCommand();

        /// <summary>
        /// 错误信息
        /// </summary>
        //public static string ErrorString = "";

        /// <summary>
        /// 超时（秒）
        /// </summary>
        public int TimeOut = 120;

        /// <summary>
        /// 链接状态
        /// </summary>
        public bool Connected
        {
            get { return Conn != null && Conn.State == ConnectionState.Open; }
        }

        /// <summary>
        /// 是否正在执行
        /// </summary>
        public bool IsRunning = false;

        public bool IsUseTrans { get; set; } = false;
        public MySqlTransaction Transaction { get; set; } = null;

        /// <summary>
        /// 初始化数据库链接
        /// </summary>
        /// <param name="connString">数据库链接</param>
        public MysqlUtils(string connString, bool isUseTrans = false)
        {
            this.IsUseTrans = isUseTrans;

            ConnString = connString;
            ConnTo();
        }

        /// <summary>
        /// 去掉SQL中的特殊字符
        /// </summary>
        /// <param name="value">字符串</param>
        /// <returns></returns>
        public string ReplaceSql(string value)
        {
            return ReplaceSqlSpecial(value);
        }

        /// <summary>
        /// 反转字符串，用于mysql反向索引
        /// </summary>
        /// <param name="temp"></param>
        /// <returns></returns>
        public static string ReverseString(string temp)
        {
            if (string.IsNullOrEmpty(temp))
                return null;
            char[] l = temp.ToCharArray();
            Array.Reverse(l);
            return new string(l);
        }

        /// <summary>
        /// 去掉SQL中的特殊字符
        /// </summary>
        /// <param name="value">字符串</param>
        /// <returns></returns>
        public static string ReplaceSqlSpecial(string value)
        {
            if (string.IsNullOrEmpty(value))
                return null;
            value = value.Replace("\\", "\\\\");
            value = value.Replace("'", "''");
            //value = value.Replace("\"", "\\\"");
            return value;
        }

        ///// <summary>
        ///// 开放一个Conn获取路径，以便外部使用事务提交
        ///// </summary>
        //public MySqlConnection GetConn()
        //{
        //    //Transaction = Conn.BeginTransaction();
        //    return Conn;
        //}

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="table"></param>
        /// <param name="keys"></param>
        /// <returns></returns>
        public int Insert(string table, List<MysqlKeyValue> keys)
        {
            string temp = "", tempx = "";
            for (int i = 0; i < keys.Count; i++)
            {
                temp += ",`" + keys[i].Key + "`";
                tempx += ",'" + keys[i].Value + "'";
            }
            temp = temp.Substring(1);
            tempx = tempx.Substring(1);
            string sql = "insert into " + table + " (" + temp + ") values (" + tempx + ") ";
            return (int)ExecuteInsertId(sql);
        }

        /// <summary>
        /// 返回执行表
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string SqlString)
        {
            return ExecuteDataTable(SqlString, null);
        }

        /// <summary>
        /// 执行sql返回DataTable
        /// </summary>
        /// <param name="SqlString">SQL语句</param>
        /// <param name="parms">Sql参数</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string SqlString, MySqlParameter[] parms)
        {
            SleepOrGo();
            bool frist = true;
        Retry:
            if (Conn == null || Conn.State != ConnectionState.Open)
                ConnTo();
            try
            {
                CmdIni();
                cmd.CommandText = SqlString;
                if (parms != null)
                    foreach (MySqlParameter pram in parms)
                        cmd.Parameters.Add(pram);
                DataSet ds = new DataSet();
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(ds);
                return ds.Tables[0];
                //DataTable dt = new DataTable();
                //try
                //{
                //    reader = cmd.ExecuteReader();
                //    dt.Load(reader);
                //}
                //catch
                //{
                //    reader = cmd.ExecuteReader();
                //    dt = Read(ref reader);
                //}
                //return dt;
            }
            catch (Exception e)
            {
                if (frist)
                {
                    frist = false;
                    ErrorDoing(e.Message);
                    goto Retry;
                }
                AddError(e.Message, SqlString);
                return null;
            }
            finally
            {
                CloseReader();
                IsRunning = false;
            }
        }

        /// <summary>
        /// 针对不同错误，执行不同的修正
        /// </summary>
        /// <param name="message"></param>
        private void ErrorDoing(string message)
        {
            Message = message;

            if (string.IsNullOrEmpty(message))
                return;
            if (message.Contains("Incorrect string value"))
                return;
            if (message.Contains("Connection must be valid and open"))
                ConnTo();
            if (message.Contains("There is already an open DataReader"))
            {
                Thread.Sleep(500);
                ConnTo();
            }
            if (message.Contains("Fatal error encountered during command execution."))
                ConnTo();
            if (message.Contains(" StreamWriter ") || message.Contains("未将对象引用设置到对象的实例") || message.Contains("未将对象"))
                ConnTo();
            CloseReader();
            Random ra = new Random(Guid.NewGuid().GetHashCode());
            Thread.Sleep(ra.Next(200, 2000));
        }

        /// <summary>
        /// 关闭读取线程
        /// </summary>
        private void CloseReader()
        {
            try
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                cmd.Dispose();
                //reader = null;
            }
            catch
            {
            }
        }

        /// <summary>
        /// 返回执行行
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public DataRow ExecuteDataTableRow(string SqlString)
        {
            return ExecuteDataTableRow(SqlString, null);
        }

        /// <summary>
        /// 返回第一行
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public DataRow ExecuteDataTableRow(string SqlString, MySqlParameter[] parms)
        {
            SleepOrGo();
            bool frist = true;
        Retry:
            if (Conn == null || Conn.State != ConnectionState.Open)
                ConnTo();
            try
            {
                CmdIni();
                cmd.CommandText = SqlString;
                if (parms != null)
                    foreach (MySqlParameter pram in parms)
                        cmd.Parameters.Add(pram);
                DataSet ds = new DataSet();
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(ds);
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                    return dt.Rows[0];
            }
            catch (Exception e)
            {
                if (frist)
                {
                    frist = false;
                    ErrorDoing(e.Message);
                    goto Retry;
                }
                AddError(e.Message, SqlString);
            }
            finally
            {
                CloseReader();
                IsRunning = false;
            }
            return null;
        }

        /// <summary>
        /// 返回第一个执行结果
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public int ExecuteRowCount(string SqlString)
        {
            string f = ExecuteFirst(SqlString, null);
            int c;
            int.TryParse(f, out c);
            return c;
        }

        public int ExecuteRowCount(string SqlString, MySqlParameter[] parms)
        {
            string f = ExecuteFirst(SqlString, parms);
            int c;
            int.TryParse(f, out c);
            return c;
        }

        /// <summary>
        /// 返回第一个执行结果
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public string ExecuteFirst(string SqlString)
        {
            return ExecuteFirst(SqlString, null);
        }

        /// <summary>
        /// 返回第一个执行结果
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public int ExecuteFirstInt(string SqlString, MySqlParameter[] parms = null)
        {
            string id = ExecuteFirst(SqlString, parms);
            int i;
            int.TryParse(id, out i);
            return i;
        }


        /// <summary>
        /// 返回第一个执行结果
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public decimal ExecuteFirstDecimal(string SqlString, MySqlParameter[] parms = null)
        {
            string id = ExecuteFirst(SqlString, parms);
            decimal i;
            decimal.TryParse(id, out i);
            return i;
        }

        /// <summary>
        /// 返回第一个值
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public string ExecuteFirst(string SqlString, MySqlParameter[] parms)
        {
            SleepOrGo();
            bool frist = true;
        Retry:
            if (Conn == null || Conn.State != ConnectionState.Open)
                ConnTo();
            try
            {
                CmdIni();
                cmd.CommandText = SqlString;
                if (parms != null)
                    foreach (MySqlParameter pram in parms)
                        cmd.Parameters.Add(pram);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    return reader[0].ToString();
                return "";
            }
            catch (Exception e)
            {
                if (e.Message.Contains("Illegal mix of collations"))
                    return null;
                if (frist)
                {
                    frist = false;
                    ErrorDoing(e.Message);
                    goto Retry;
                }
                AddError(e.Message, SqlString);
            }
            finally
            {
                CloseReader();
                IsRunning = false;
            }
            return null;
        }

        /// <summary>
        /// 返回更改、插入或删除的行数
        /// </summary>
        /// <param name="SqlString"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public int ExecuteAffectedRows(string SqlString)
        {
            return ExecuteAffectedRows(SqlString, null);
        }
        /// <summary>
        /// 返回更改、插入或删除的行数
        /// </summary>
        /// <param name="SqlString"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public int ExecuteAffectedRows(string SqlString, MySqlParameter[] parms)
        {
            SleepOrGo();
            bool frist = true;
        Retry:
            if (Conn == null || Conn.State != ConnectionState.Open)
                ConnTo();
            try
            {
                CmdIni();
                cmd.CommandText = SqlString;
                if (parms != null)
                    foreach (MySqlParameter pram in parms)
                        cmd.Parameters.Add(pram);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    return reader.RecordsAffected;
                return reader.RecordsAffected;
            }
            catch (Exception e)
            {
                if (e.Message.Contains("Illegal mix of collations"))
                    return 0;
                if (frist)
                {
                    frist = false;
                    ErrorDoing(e.Message);
                    goto Retry;
                }
                AddError(e.Message, SqlString);
            }
            finally
            {
                CloseReader();
                IsRunning = false;
            }
            return 0;
        }

        /// <summary>
        /// 返回插入ID
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public long ExecuteInsertId(string SqlString)
        {
            return ExecuteInsertId(SqlString, null);
        }

        /// <summary>
        /// 返回第一个值
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public long ExecuteInsertId(string SqlString, MySqlParameter[] parms)
        {
            SleepOrGo();
            bool frist = true;
        Retry:
            if (Conn == null || Conn.State != ConnectionState.Open)
                ConnTo();
            try
            {
                CmdIni();
                cmd.CommandText = SqlString;
                if (parms != null)
                    foreach (MySqlParameter pram in parms)
                        cmd.Parameters.Add(pram);
                cmd.ExecuteNonQuery();
                long id = cmd.LastInsertedId;
                return id;
            }
            catch (Exception e)
            {
                if (e.Message.Contains(" for key 'PRIMARY'") && e.Message.Contains("Duplicate entry"))
                    return 0;
                if (frist)
                {
                    frist = false;
                    ErrorDoing(e.Message);
                    goto Retry;
                }
                AddError(e.Message, SqlString);
            }
            finally
            {
                CloseReader();
                IsRunning = false;
            }
            return 0;
        }

        /// <summary>
        /// 无返回
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public bool ExecuteNonQuery(string SqlString)
        {
            return ExecuteNonQuery(SqlString, null);
        }

        /// <summary>
        /// 只保留mysql可识别的字符串
        /// </summary>
        /// <param name="text"></param>
        public string JustUtf8Words(string text)
        {
            JustUtf8Words(ref text);
            return text;
        }

        /// <summary>
        /// 只保留mysql可识别的字符串
        /// </summary>
        /// <param name="text"></param>
        public void JustUtf8Words(ref string text)
        {
            if (string.IsNullOrEmpty(text))
                return;
            var cs = text.ToCharArray();
            List<char> cc = new List<char>();
            foreach (char c in cs)
            {
                if (c > 7814 && c < 8416 || c > 57096 && c < 59501 || c > 63707 && c < 64309 || c > 65505 || c < 0)
                {
                    if (cc.Contains(c))
                        continue;
                    text = text.Replace(c.ToString(), "");
                    cc.Add(c);
                }
            }
        }

        /// <summary>
        /// 执行无返回SQL语句
        /// </summary>
        /// <param name="SqlString">SQL语句</param>
        /// <param name="parms">Sql参数</param>
        ///<returns>是否执行成功</returns>
        public bool ExecuteNonQuery(string SqlString, MySqlParameter[] parms)
        {
            SleepOrGo();
            bool frist = true;
        Retry:
            if (Conn == null || Conn.State != ConnectionState.Open)
                ConnTo();
            try
            {
                CmdIni();
                cmd.CommandText = SqlString;
                if (parms != null)
                    foreach (MySqlParameter pram in parms)
                        cmd.Parameters.Add(pram);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                if (e.Message.Contains("Illegal mix of collations") || e.Message.Contains("Incorrect string value")
                    || e.Message.Contains(" for key 'PRIMARY'") && e.Message.Contains("Duplicate entry"))
                    return false;

                if (frist)
                {
                    frist = false;
                    ErrorDoing(e.Message);
                    goto Retry;
                }
                AddError(e.Message, SqlString);
                return false;
            }
            finally
            {
                CloseReader();
                IsRunning = false;
            }
        }

        /// <summary>
        /// 判断是否存在
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public bool ExecuteExists(string SqlString)
        {
            return ExecuteExists(SqlString, null);
        }

        /// <summary>
        /// 查询是否存在
        /// </summary>
        /// <param name="SqlString">SQL语句</param>
        /// <param name="parms">SQL参数</param>
        /// <returns>是否存在</returns>
        public bool ExecuteExists(string SqlString, MySqlParameter[] parms)
        {
            SleepOrGo();
            bool frist = true;
        Retry:
            if (Conn == null || Conn.State != ConnectionState.Open)
                ConnTo();
            try
            {

                CmdIni();
                cmd.CommandText = SqlString;
                if (parms != null)
                    foreach (MySqlParameter pram in parms)
                        cmd.Parameters.Add(pram);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    return true;
                return false;
            }
            catch (Exception e)
            {
                if (frist)
                {
                    frist = false;
                    ErrorDoing(e.Message);
                    goto Retry;
                }
                AddError(e.Message, SqlString);
                return false;
            }
            finally
            {
                CloseReader();
                IsRunning = false;
            }
        }

        /// <summary>
        /// 停止还是前进
        /// </summary>
        private void SleepOrGo()
        {
            int i = 0;
            while (IsRunning && i < 300)
            {
                Thread.Sleep(100);
                i++;
            }
            IsRunning = true;
        }

        /// <summary>
        /// 初始化CMD
        /// </summary>
        private void CmdIni()
        {
            if (reader != null && !reader.IsClosed)
                reader.Close();
            if (cmd != null)
                cmd.Dispose();
            cmd = new MySqlCommand();
            cmd.Connection = Conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = TimeOut;

            if (Transaction != null)
                cmd.Transaction = Transaction;
        }

        /// <summary>
        /// 连接数据库
        /// </summary>
        private void ConnTo()
        {
            Close();
            try
            {
                Conn = new MySqlConnection(ConnString);
                Conn.Open();

                if (IsUseTrans)
                    this.Transaction = Conn.BeginTransaction();
            }
            catch (Exception e)
            {
                AddError(e.Message, ConnString);
            }
        }

        /// <summary>
        /// 错误信息
        /// </summary>
        /// <param name="message"></param>
        /// <param name="sql"></param>
        private void AddError(string message, string sql)
        {
            if (string.IsNullOrEmpty(message) || message.Contains("Illegal mix of collations"))
                return;
            ErrorDoing(message);
            string errStr = "数据库连接错误：" + message + "\r\nSQL语句：" + sql;
            try
            {
                if (!Directory.Exists("Log"))
                {
                    Directory.CreateDirectory("Log");
                }
                File.AppendAllText("Log\\log-" + DateTime.Now.ToString("yyyyMMdd") + ".txt", errStr);
            }
            catch (Exception ex) 
            {
            }
        }

        public void Commit()
        {
            if (Transaction != null)
                Transaction.Commit();
        }

        public void RollBack()
        {
            if (Transaction != null)
                Transaction.Rollback();
        }

        /// <summary>
        /// 关闭数据库链接
        /// </summary>
        public void Close()
        {
            IsRunning = false;
            try
            {
                if (Conn != null && Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                }
                Conn = null;

                try
                {
                    if (reader != null && !reader.IsClosed)
                        reader.Close();
                }
                catch
                {
                }
                reader = null;

                try
                {
                    if (Transaction != null)
                        Transaction.Dispose();
                }
                catch
                {
                }
                Transaction = null;
            }
            catch
            {
            }
        }
    }

    public class MysqlKeyValues
    {
        public List<MysqlKeyValue> List = new List<MysqlKeyValue>();
    }

    public class MysqlKeyValue
    {
        public MysqlKeyValue(string key, object value)
        {
            Key = key;
            Value = value == null ? "" : MysqlUtils.ReplaceSqlSpecial(value.ToString());
        }

        public string Key = "";
        public string Value;
    }
}