using AutoTurnOver.Models.Base;
using Dapper;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
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;
        }

    }
}
