using AutoTurnOver.Models.Base;
using AutoTurnOver.Utility;
using Dapper;
using MySql.Data;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace AutoTurnOver.DB.Base
{
    public static class DbHelper
    {
        public static Page<T> Page<T>(this MySqlConnection conn, string sql, page_search_dto search_data, object param = null, int? commandTimeout = null,bool isCount = true)
        {
            System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
            watch.Start();//开始计时

            // 清理 where 1=1
            if(sql.Contains(" and ") && sql.Contains(" 1=1 "))
            {
                // 如果有自带了查询条件
            }

            var result_data = new Page<T>();
            var item_sql = sql;
            if (!string.IsNullOrWhiteSpace(search_data.sidx) && !string.IsNullOrWhiteSpace(search_data.sord) && !item_sql.Contains("order by"))
            {
                item_sql += $" order by {search_data.sidx} {search_data.sord} ";
            }
            item_sql += $" limit {(search_data.page - 1) * search_data.rows},{search_data.rows} ";
            result_data.Items = (connectionHelper._connection.Query<T>(item_sql, param, commandTimeout: commandTimeout)).AsList();
            result_data.CurrentPage = search_data.page;
            if (isCount)
            {
                var original_sql = sql;
                while (true)
                {
                    Match match = Regex.Match(sql, @"select[\s\S]+from");
                    if (match.Success)
                    {
                        if (match.Groups.Count > 1)
                        {
                            throw new Exception(" 分页语句解析异常 ");
                        }

                        if (SubstringCount(match.Groups[0].Value, "from") > 1)
                        {
                            int remove_Index = match.Groups[0].Value.LastIndexOf("from");
                            sql = match.Groups[0].Value.Remove(remove_Index);
                        }
                        else
                        {
                            sql = original_sql.Replace(match.Groups[0].Value, " select count(1) from ");
                            break;
                        }
                    }
                    else
                    {
                        throw new Exception(" 分页语句解析异常 ");
                    }
                }
                result_data.TotalItems = connectionHelper._connection.QueryFirstOrDefault<int?>(sql, param, commandTimeout: commandTimeout) ?? 0;
                result_data.TotalPages = (int)Math.Ceiling(result_data.TotalItems * 1.0 / search_data.rows);
            }
            
            watch.Stop();
            result_data.CostTime = watch.ElapsedMilliseconds;
            return result_data;
        }

        public static async Task<Page<T>> PageAsync<T>(this MySqlConnection conn, string sql, page_search_dto search_data, object param = null, int? commandTimeout = null)
        {
            System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
            watch.Start();//开始计时
            var result_data = new Page<T>();
            var item_sql = sql;
            if (!string.IsNullOrWhiteSpace(search_data.sidx) && !string.IsNullOrWhiteSpace(search_data.sord) && !item_sql.Contains("order by"))
            {
                item_sql += $" order by {search_data.sidx} {search_data.sord} ";
            }
            item_sql += $" limit {(search_data.page - 1) * search_data.rows},{search_data.rows} ";
            result_data.Items = (await connectionHelper._connection.QueryAsync<T>(item_sql, param, commandTimeout: commandTimeout)).AsList();
            var original_sql = sql;
            while (true)
            {
                Match match = Regex.Match(sql, @"select[\s\S]+from");
                if (match.Success)
                {
                    if (match.Groups.Count > 1)
                    {
                        throw new Exception(" 分页语句解析异常 ");
                    }

                    if (SubstringCount(match.Groups[0].Value, "from") > 1)
                    {
                        int remove_Index = match.Groups[0].Value.LastIndexOf("from");
                        sql = match.Groups[0].Value.Remove(remove_Index);
                    }
                    else
                    {
                        sql = original_sql.Replace(match.Groups[0].Value, " select count(1) from ");
                        break;
                    }
                }
                else
                {
                    throw new Exception(" 分页语句解析异常 ");
                }
            }


            result_data.TotalItems = await connectionHelper._connection.QueryFirstOrDefaultAsync<int?>(sql, param, commandTimeout: commandTimeout) ?? 0;
            result_data.CurrentPage = search_data.page;
            result_data.TotalPages = (int)Math.Ceiling(result_data.TotalItems * 1.0 / search_data.rows);
            watch.Stop();
            result_data.CostTime = watch.ElapsedMilliseconds;
            return result_data;
        }

        /// <summary>
        /// 计算字符串中子串出现的次数
        /// </summary>
        /// <param name="str">字符串</param>
        /// <param name="substring">子串</param>
        /// <returns>出现的次数</returns>
        static int SubstringCount(string str, string substring)
        {
            if (str.Contains(substring))
            {
                string strReplaced = str.Replace(substring, "");
                return (str.Length - strReplaced.Length) / substring.Length;
            }

            return 0;
        }

        /// <summary>
        ///     批量插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entities"></param>
        /// <param name="exclude">排除的字段</param>
        public static int BatchInsert<T>(this MySqlConnection conn, List<T> entities,string tableName, string[] exclude = null)
        {
            if (exclude == null || exclude.Length <= 0)
                exclude = new[] { "id" };

            for (var i = 0; i < exclude.Length; i++)
            {
                exclude[i] = exclude[i].ToLower();
            }

            if (entities != null && entities.Count > 0)
            {
                var tbname = typeof(T).Name.TrimEnd("Entity".ToCharArray());
                var colms = new List<string>();
                var ps = entities.First().GetType().GetProperties().Where(p => !exclude.Contains(p.Name.ToLower()))
                    .ToList();
                foreach (var p in ps)
                {
                    colms.Add($"`{p.Name}`");
                }

                var paramList = new DynamicParameters();
                var paramsNames = new List<string>();


                var s = 0;
                var n = "a";
                foreach (var item in entities)
                {
                    var toNames = new List<string>();

                    foreach (var p in ps)
                    {
                        var pname = $"@{n}{s}";
                        var pvalue = p.GetValue(item, null);

                        toNames.Add(pname);
                        paramList.Add(pname, pvalue);
                        s++;
                    }

                    paramsNames.Add($"({string.Join(",", toNames)})");
                }

                var sql =
                    $"INSERT INTO {tbname} ({string.Join(",", colms)}) VALUES {string.Join(",", paramsNames)}";
                System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
                watch.Start();//开始计时
                var rin = conn.Execute(sql, paramList);
                Console.WriteLine($" 写入 {entities.Count} 条数据，耗时 {watch.ElapsedMilliseconds} 毫秒 ");
                return rin;

            }

            throw new Exception("传入参数先");
        }


        /// <summary>
        /// Copy数据
        /// </summary>
        /// <param name="connectionString">目标连接字符</param>
        /// <param name="TableName">目标表</param>
        /// <param name="dt">源数据</param>
        public static int BatchInsert_bak<T>(this MySqlConnection _conn,IList<T> list, string tableName)
        {
            System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
            watch.Start();//开始计时
            var table = DataTableExtensions.ToDataTable<T>(list);
            int insertCount = 0;
            using (var conn = new MySqlConnection(connectionHelper.connStr))
            {
                string tmpPath = Guid.NewGuid() + ".csv";
                Console.WriteLine(tmpPath);
                string csv = DataTableToCsv(table);
                File.WriteAllText(tmpPath, csv);
                // MySqlTransaction tran = null;  
                try
                {
                    //conn.Open();
                    //tran = conn.BeginTransaction();  
                    MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
                    {
                        FieldTerminator = ",",
                        FieldQuotationCharacter = '"',
                        EscapeCharacter = '"',
                        LineTerminator = "\r\n",
                        FileName = tmpPath,
                        NumberOfLinesToSkip = 0,
                        TableName = tableName,
                        Timeout = 1000 * 60 * 60
                    };
                    //bulk.Columns.AddRange(table.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToArray());  
                    insertCount += bulk.Load();
                    // tran.Commit();  
                }
                catch (MySqlException ex)
                {
                    // if (tran != null) tran.Rollback();  
                    throw ex;
                }
                File.Delete(tmpPath);
            }
            Console.WriteLine($" 写入 {list.Count} 条数据，耗时 {watch.ElapsedMilliseconds} 毫秒 ");
            return insertCount;
        }

        /// <summary>
        /// 获取DataTable 指定顺序的数据
        /// </summary>
        /// <param name="beginItem"></param>
        /// <param name="endItem"></param>
        /// <param name="oDT"></param>
        /// <returns></returns>
        public static DataTable DtSelectTop(long beginItem, long endItem, DataTable oDT)
        {
            try
            {
                DataTable NewTable = oDT.Clone();
                DataRow[] rows = oDT.Select("1=1");
                for (long i = beginItem; i < endItem; i++)
                {
                    if (rows.Length <= i)
                    {
                        break;
                    }
                    NewTable.ImportRow((DataRow)rows[i]);
                }
                return NewTable;
            }
            catch (Exception ex)
            {

                throw;
            }

        }
        private static string DataTableToCsv(DataTable table)
        {
            //以半角逗号（即,）作分隔符，列为空也要表达其存在。  
            //列内容如存在半角逗号（即,）则用半角引号（即""）将该字段值包含起来。  
            //列内容如存在半角引号（即"）则应替换成半角双引号（""）转义，并用半角引号（即""）将该字段值包含起来。  
            StringBuilder sb = new StringBuilder();
            DataColumn colum;
            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    colum = table.Columns[i];
                    if (i != 0) sb.Append(",");
                    if (colum.DataType == typeof(string) && row[colum].ToString().Contains(","))
                    {
                        sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"");
                    }
                    else sb.Append(row[colum].ToString());
                }
                sb.AppendLine();
            }


            return sb.ToString();
        }

    }
}
