using SqlSugar;
using Web.Core.Entities.Response;
using Web.Core.Exceptions;

namespace Web.Core.DataBase
{
    public static class SqlSugarExtention
    {
        #region 同步

        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="page"></param>
        /// <returns></returns>
        /// <exception cref="BizException"></exception>
        public static PageListModel<T> GetPageResult<T>(this ISugarQueryable<T> query, PageInput page) where T : class, new()
        {
            int count = 0;

            #region 自定义排序

            string orderSql = string.Empty;
            //设置排序方式
            if (!string.IsNullOrWhiteSpace(page.SortFileId))
            {
                query.QueryBuilder.OrderByValue = null;// 移除自定义排序
                string[] fieldArr = page.SortFileId.ToLower().Split(',');
                string[] typeArr;
                if (string.IsNullOrWhiteSpace(page.SortType))
                {
                    typeArr = new string[fieldArr.Length];
                    for (int i = 0; i < typeArr.Length; i++)
                    {
                        typeArr[i] = "asc";
                    }
                }
                else
                {
                    typeArr = page.SortType.ToLower().Split(",");
                }

                if (fieldArr.Length > 0 && fieldArr.Length <= 0)
                {
                    foreach (var item in fieldArr)
                    {
                        orderSql += item + "asc,";
                    }
                }
                else
                {
                    if (fieldArr.Length > 0 && fieldArr.Length != typeArr.Length)
                    {
                        throw new BizException("排序参数异常");
                    }
                    for (int i = 0; i < fieldArr.Length; i++)
                    {
                        orderSql += fieldArr[i] + $"{typeArr[i]},";
                    }
                }
            }

            #endregion 自定义排序

            #region 有排序

            if (!string.IsNullOrWhiteSpace(orderSql))
            {
                orderSql = orderSql.Trim(',');
                string sqlTemplate = "SELECT * FROM ({0}) ORDER BY {1}";
                string sql = query.ToSql().Key;
                var paramLst = query.ToSql().Value;
                foreach (var item in paramLst)
                {
                    if (item.Value == null)
                    {
                        continue;
                    }
                    if (item.DbType.ToString() == "DateTime")
                    {
                        DateTime dtime = (DateTime)item.Value;
                        sql = sql.Replace(item.ParameterName, "'" + dtime.ToString("yyyy-MM-dd") + "'");
                        continue;
                    }
                    sql = sql.Replace(item.ParameterName, "'" + item.Value?.ToString() + "'");
                }

                var orderLst = query.Context.SqlQueryable<T>(string.Format(sqlTemplate, sql, orderSql)).ToPageList(page.PageIndex, page.PageRows, ref count);
                return new PageListModel<T>()
                {
                    PageList = orderLst,
                    Total = count
                };
            }

            #endregion 有排序

            List<T> lst = query.ToPageList(page.PageIndex, page.PageRows, ref count);
            return new PageListModel<T>()
            {
                PageList = lst,
                Total = count
            };
        }

        #endregion 同步

        #region 异步

        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="page"></param>
        /// <returns></returns>
        /// <exception cref="BizException"></exception>
        public static async Task<PageListModel<T>> GetPageResultAsync<T>(this ISugarQueryable<T> query, PageInput page) where T : class, new()
        {
            RefAsync<int> count = 0;

            #region 自定义排序

            string orderSql = string.Empty;
            if (!string.IsNullOrWhiteSpace(page.SortFileId))
            {
                query.QueryBuilder.OrderByValue = null;// 移除默认排序
                string[] fieldArr = page.SortFileId.ToLower().Split(',');
                string[] typeArr;
                if (string.IsNullOrWhiteSpace(page.SortType))
                {
                    typeArr = new string[fieldArr.Length];
                    for (int i = 0; i < fieldArr.Length; i++)
                    {
                        typeArr[i] = "asc";
                    }
                }
                else
                {
                    typeArr = page.SortType.ToLower().Split(",");
                }
                // 属性校验，保证返回实体存在当前字段
                List<string> fileds = typeof(T).GetProperties().Select(i => i.Name.ToLower()).ToList();
                if (fieldArr.Length > 0 && typeArr.Length <= 0)
                {
                    foreach (var item in fieldArr)
                    {
                        if (fileds.Contains(item))
                        {
                            orderSql += item + "asc,";
                        }
                    }
                }
                else
                {
                    if (fieldArr.Length > 0 && fieldArr.Length != typeArr.Length)
                    {
                        throw new BizException("排序参数异常");
                    }
                    for (int i = 0; i < fieldArr.Length; i++)
                    {
                        if (fileds.Contains(fieldArr[i]))
                        {
                            orderSql += fieldArr[i] + $"{typeArr[i]}";
                        }
                    }
                }
            }

            #endregion 自定义排序

            if (!string.IsNullOrWhiteSpace(orderSql))
            {
                orderSql = orderSql.Trim(',');
                string sqlTemplate = "SELECT * FROM ({0}) ORDER BY {1}";
                string sql = query.ToSql().Key;
                var parmLst = query.ToSql().Value;
                foreach (var item in parmLst)
                {
                    if (item.Value == null)
                    {
                        continue;
                    }
                    if (item.DbType == System.Data.DbType.DateTime)
                    {
                        DateTime dtime = (DateTime)item.Value;
                        sql = sql.Replace(item.ParameterName, "'" + dtime.ToString("yyyy-MM-dd") + "'");
                        continue;
                    }
                    else if (item.DbType == System.Data.DbType.String)
                    {
                        if (item.Value.ToString().Contains("'"))
                        {
                            throw new BizException($"参数值【{item.Value}】含有非法字符，无法查询！");
                        }
                    }
                    sql = sql.Replace(item.ParameterName, "'" + item.Value?.ToString() + "'");
                }

                var orderLst = await query.Context.SqlQueryable<T>(string.Format(sqlTemplate, sql, orderSql)).ToPageListAsync(page.PageIndex, page.PageRows, count);
                return new PageListModel<T>()
                {
                    PageList = orderLst,
                    Total = count
                };
            }

            List<T> lst = await query.ToPageListAsync(page.PageIndex, page.PageRows, count);
            return new PageListModel<T>()
            {
                PageList = lst,
                Total = count
            };
        }

        #endregion 异步

        #region 联合查询

        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="queryable"></param>
        /// <param name="unionQueryables"></param>
        /// <returns></returns>
        public static ISugarQueryable<T> Union<T>(this ISugarQueryable<T> queryable, params ISugarQueryable<T>[] unionQueryables)
        {
            return queryable.Union(unionQueryables);
        }

        #endregion 联合查询
    }
}