using System;
using NewLife;
using NewLife.Data;
using XCode.Configuration;

namespace XCode
{
    /// <summary>字段扩展</summary>
    public static class FieldExtension
    {
        #region 时间复杂运算
        /// <summary>时间专用区间函数，左闭合右开放，开始结束都是日期时包含结束日期（也即结束日期加一天）</summary>
        /// <param name="fi"></param>
        /// <param name="start">起始时间，大于等于</param>
        /// <param name="end">结束时间，小于。如果是日期，则加一天</param>
        /// <returns></returns>
        public static Expression Between(this FieldItem fi, DateTime start, DateTime end)
        {
            if (fi.Type != typeof(DateTime)) throw new NotSupportedException($"[{nameof(Between)}]函数仅支持时间日期字段！");

            var exp = new WhereExpression();
            if (fi == null) return exp;

            if (start <= DateTime.MinValue || start >= DateTime.MaxValue)
            {
                if (end <= DateTime.MinValue || end >= DateTime.MaxValue) return exp;

                //// 如果只有日期，则加一天，表示包含这一天
                //if (end == end.Date) end = end.AddDays(1);

                return fi < end;
            }
            else
            {
                exp &= fi >= start;
                if (end <= DateTime.MinValue || end >= DateTime.MaxValue) return exp;

                // 如果只有日期，则加一天，表示包含这一天
                if (start == start.Date && end == end.Date) end = end.AddDays(1);

                return exp & fi < end;
            }
        }

        /// <summary>时间专用区间函数，左闭合右开放，开始结束都是日期时包含结束日期（也即结束日期加一天）</summary>
        /// <param name="fi"></param>
        /// <param name="start">起始时间，大于等于</param>
        /// <param name="end">结束时间，小于。如果是日期，则加一天</param>
        /// <param name="snow"></param>
        /// <returns></returns>
        public static Expression Between(this FieldItem fi, DateTime start, DateTime end, Snowflake snow)
        {
            if (fi.Type != typeof(Int64)) throw new NotSupportedException($"[{nameof(Between)}]函数仅支持Int64字段！");

            var exp = new WhereExpression();
            if (fi == null) return exp;

            if (start <= DateTime.MinValue || start >= DateTime.MaxValue)
            {
                if (end <= DateTime.MinValue || end >= DateTime.MaxValue) return exp;

                //// 如果只有日期，则加一天，表示包含这一天
                //if (end == end.Date) end = end.AddDays(1);

                return fi < snow.GetId(end);
            }
            else
            {
                exp &= fi >= snow.GetId(start);
                if (end <= DateTime.MinValue || end >= DateTime.MaxValue) return exp;

                // 如果只有日期，则加一天，表示包含这一天
                if (start == start.Date && end == end.Date) end = end.AddDays(1);

                return exp & fi < snow.GetId(end);
            }
        }

        #region 天
        /// <summary>当天范围</summary>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public static Expression Today(this FieldItem field)
        {
            //var fromDateStart = DateTime.Parse(String.Format("{0:yyyy-MM-dd 00:00:00}", DateTime.Now));
            //var fromDateEnd = DateTime.Parse(String.Format("{0:yyyy-MM-dd 00:00:00}", DateTime.Now));
            //return field.Between(fromDateStart, fromDateEnd);

            var date = DateTime.Now.Date;
            return field.Between(date, date);
        }

        /// <summary>昨天范围</summary>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public static Expression Yesterday(this FieldItem field)
        {
            //var fromDateStart = DateTime.Parse(String.Format("{0:yyyy-MM-dd 00:00:00}", DateTime.Now.AddDays(-1)));
            //var fromDateEnd = DateTime.Parse(String.Format("{0:yyyy-MM-dd 00:00:00}", DateTime.Now.AddDays(-1)));
            //return field.Between(fromDateStart, fromDateEnd);

            var date = DateTime.Now.Date.AddDays(-1);
            return field.Between(date, date);
        }

        /// <summary>明天范围</summary>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public static Expression Tomorrow(this FieldItem field)
        {
            //var fromDateStart = DateTime.Parse(String.Format("{0:yyyy-MM-dd 00:00:00}", DateTime.Now.AddDays(1)));
            //var fromDateEnd = DateTime.Parse(String.Format("{0:yyyy-MM-dd 00:00:00}", DateTime.Now.AddDays(1)));
            //return field.Between(fromDateStart, fromDateEnd);

            var date = DateTime.Now.Date.AddDays(1);
            return field.Between(date, date);
        }

        /// <summary>过去天数范围</summary>
        /// <param name="field">字段</param>
        /// <param name="days"></param>
        /// <returns></returns>
        public static Expression LastDays(this FieldItem field, Int32 days)
        {
            //var fromDateStart = DateTime.Parse(String.Format("{0:yyyy-MM-dd} 00:00:00", DateTime.Now.AddDays(-days)));
            //var fromDateEnd = DateTime.Parse(String.Format("{0:yyyy-MM-dd} 00:00:00", DateTime.Now.AddDays(-1)));
            //return field.Between(fromDateStart, fromDateEnd);

            var date = DateTime.Now.Date;
            return field.Between(date.AddDays(-1 * days), date);
        }

        /// <summary>未来天数范围</summary>
        /// <param name="field">字段</param>
        /// <param name="days"></param>
        /// <returns></returns>
        public static Expression NextDays(this FieldItem field, Int32 days)
        {
            //var fromDateStart = DateTime.Parse(String.Format("{0:yyyy-MM-dd} 00:00:00", DateTime.Now.AddDays(1)));
            //var fromDateEnd = DateTime.Parse(String.Format("{0:yyyy-MM-dd} 00:00:00", DateTime.Now.AddDays(days)));
            //return field.Between(fromDateStart, fromDateEnd);

            var date = DateTime.Now.Date;
            return field.Between(date, date.AddDays(days));
        }
        #endregion

        #region 周
        /// <summary>本周范围</summary>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public static Expression ThisWeek(this FieldItem field)
        {
            //var fromDateStart = DateTime.Parse(String.Format("{0:yyyy-MM-dd} 00:00:00", DateTime.Now.AddDays(Convert.ToDouble((0 - Convert.ToInt16(DateTime.Now.DayOfWeek))))));
            //var fromDateEnd = DateTime.Parse(String.Format("{0:yyyy-MM-dd} 00:00:00", DateTime.Now.AddDays(Convert.ToDouble((6 - Convert.ToInt16(DateTime.Now.DayOfWeek))))));
            //return field.Between(fromDateStart, fromDateEnd);

            var date = DateTime.Now.Date;
            var day = (Int32)date.DayOfWeek;
            return field.Between(date.AddDays(-1 * day), date.AddDays(6 - day));
        }

        /// <summary>上周范围</summary>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public static Expression LastWeek(this FieldItem field)
        {
            //var fromDateStart = DateTime.Parse(String.Format("{0:yyyy-MM-dd} 00:00:00", DateTime.Now.AddDays(Convert.ToDouble((0 - Convert.ToInt16(DateTime.Now.DayOfWeek))) - 7)));
            //var fromDateEnd = DateTime.Parse(String.Format("{0:yyyy-MM-dd} 00:00:00", DateTime.Now.AddDays(Convert.ToDouble((6 - Convert.ToInt16(DateTime.Now.DayOfWeek))) - 7)));
            //return field.Between(fromDateStart, fromDateEnd);

            var date = DateTime.Now.Date;
            var day = (Int32)date.DayOfWeek;
            return field.Between(date.AddDays(-1 * day - 7), date.AddDays(6 - day - 7));
        }

        /// <summary>下周范围</summary>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public static Expression NextWeek(this FieldItem field)
        {
            //var fromDateStart = DateTime.Parse(String.Format("{0:yyyy-MM-dd} 00:00:00", DateTime.Now.AddDays(Convert.ToDouble((0 - Convert.ToInt16(DateTime.Now.DayOfWeek))) + 7)));
            //var fromDateEnd = DateTime.Parse(String.Format("{0:yyyy-MM-dd} 00:00:00", DateTime.Now.AddDays(Convert.ToDouble((6 - Convert.ToInt16(DateTime.Now.DayOfWeek))) + 7)));
            //return field.Between(fromDateStart, fromDateEnd);

            var date = DateTime.Now.Date;
            var day = (Int32)date.DayOfWeek;
            return field.Between(date.AddDays(-1 * day + 7), date.AddDays(6 - day + 7));
        }
        #endregion

        #region 月
        /// <summary>本月范围</summary>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public static Expression ThisMonth(this FieldItem field)
        {
            //var fromDateStart = DateTime.Parse(String.Format("{0:yyyy-MM}-01 00:00:00", DateTime.Now));
            //var fromDateEnd = DateTime.Parse(String.Format("{0:yyyy-MM}-01 00:00:00", DateTime.Now.AddMonths(1))).AddDays(-1);
            //return field.Between(fromDateStart, fromDateEnd);

            var now = DateTime.Now;
            var month = new DateTime(now.Year, now.Month, 1);
            return field.Between(month, month.AddMonths(1).AddDays(-1));
        }

        /// <summary>上月范围</summary>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public static Expression LastMonth(this FieldItem field)
        {
            //var fromDateStart = DateTime.Parse(String.Format("{0:yyyy-MM}-01 00:00:00", DateTime.Now.AddMonths(-1)));
            //var fromDateEnd = DateTime.Parse(String.Format("{0:yyyy-MM}-01 00:00:00", DateTime.Now)).AddDays(-1);
            //return field.Between(fromDateStart, fromDateEnd);

            var now = DateTime.Now;
            var month = new DateTime(now.Year, now.Month, 1).AddMonths(-1);
            return field.Between(month, month.AddMonths(1).AddDays(-1));
        }

        /// <summary>下月范围</summary>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public static Expression NextMonth(this FieldItem field)
        {
            //var fromDateStart = DateTime.Parse(String.Format("{0:yyyy-MM}-01 00:00:00", DateTime.Now.AddMonths(1)));
            //var fromDateEnd = DateTime.Parse(String.Format("{0:yyyy-MM}-01 00:00:00", DateTime.Now.AddMonths(2))).AddDays(-1);
            //return field.Between(fromDateStart, fromDateEnd);

            var now = DateTime.Now;
            var month = new DateTime(now.Year, now.Month, 1).AddMonths(1);
            return field.Between(month, month.AddMonths(1).AddDays(-1));
        }
        #endregion

        #region 季度
        /// <summary>本季度范围</summary>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public static Expression ThisQuarter(this FieldItem field)
        {
            //var fromDateStart = DateTime.Parse(String.Format("{0:yyyy-MM}-01 00:00:00", DateTime.Now.AddMonths(0 - ((DateTime.Now.Month - 1) % 3))));
            //var fromDateEnd = DateTime.Parse(String.Format("{0:yyyy-MM-dd} 00:00:00", DateTime.Parse(DateTime.Now.AddMonths(3 - ((DateTime.Now.Month - 1) % 3)).ToString("yyyy-MM-01")))).AddDays(-1);
            //return field.Between(fromDateStart, fromDateEnd);

            var now = DateTime.Now;
            var month = new DateTime(now.Year, now.Month - (now.Month - 1) % 3, 1);
            return field.Between(month, month.AddMonths(3).AddDays(-1));
        }

        /// <summary>上季度范围</summary>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public static Expression LastQuarter(this FieldItem field)
        {
            //var fromDateStart = DateTime.Parse(String.Format("{0:yyyy-MM}-01 00:00:00", DateTime.Now.AddMonths(-3 - ((DateTime.Now.Month - 1) % 3))));
            //var fromDateEnd = DateTime.Parse(String.Format("{0:yyyy-MM-dd} 00:00:00", DateTime.Parse(DateTime.Now.AddMonths(0 - ((DateTime.Now.Month - 1) % 3)).ToString("yyyy-MM-01")))).AddDays(-1);
            //return field.Between(fromDateStart, fromDateEnd);

            var now = DateTime.Now;
            var month = new DateTime(now.Year, now.Month - (now.Month - 1) % 3, 1).AddMonths(-3);
            return field.Between(month, month.AddMonths(3).AddDays(-1));
        }

        /// <summary>下季度范围</summary>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public static Expression NextQuarter(this FieldItem field)
        {
            //var fromDateStart = DateTime.Parse(String.Format("{0:yyyy-MM}-01 00:00:00", DateTime.Now.AddMonths(3 - ((DateTime.Now.Month - 1) % 3))));
            //var fromDateEnd = DateTime.Parse(String.Format("{0:yyyy-MM-dd} 00:00:00", DateTime.Parse(DateTime.Now.AddMonths(6 - ((DateTime.Now.Month - 1) % 3)).ToString("yyyy-MM-01")))).AddDays(-1);
            //return field.Between(fromDateStart, fromDateEnd);

            var now = DateTime.Now;
            var month = new DateTime(now.Year, now.Month - (now.Month - 1) % 3, 1).AddMonths(3);
            return field.Between(month, month.AddMonths(3).AddDays(-1));
        }
        #endregion
        #endregion

        #region 字符串复杂运算
        /// <summary>包含所有关键字</summary>
        /// <param name="field"></param>
        /// <param name="keys"></param>
        /// <returns></returns>
        public static Expression ContainsAll(this FieldItem field, String keys)
        {
            if (field.Type != typeof(String)) throw new NotSupportedException($"[{nameof(ContainsAll)}]函数仅支持字符串字段！");

            var exp = new WhereExpression();
            if (String.IsNullOrEmpty(keys)) return exp;

            var ks = keys.Split(' ', StringSplitOptions.RemoveEmptyEntries);

            for (var i = 0; i < ks.Length; i++)
            {
                if (!ks[i].IsNullOrWhiteSpace()) exp &= field.Contains(ks[i].Trim());
            }

            return exp;
        }

        /// <summary>包含任意关键字</summary>
        /// <param name="field"></param>
        /// <param name="keys"></param>
        /// <returns></returns>
        public static Expression ContainsAny(this FieldItem field, String keys)
        {
            if (field.Type != typeof(String)) throw new NotSupportedException($"[{nameof(ContainsAny)}]函数仅支持字符串字段！");

            var exp = new WhereExpression();
            if (String.IsNullOrEmpty(keys)) return exp;

            var ks = keys.Split(' ', StringSplitOptions.RemoveEmptyEntries);

            for (var i = 0; i < ks.Length; i++)
            {
                if (!ks[i].IsNullOrWhiteSpace()) exp |= field.Contains(ks[i].Trim());
            }

            return exp;
        }
        #endregion

        #region 排序
        /// <summary>升序</summary>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public static ConcatExpression Asc(this FieldItem field)
        {
            if (field == null || field.FormatedName.IsNullOrEmpty()) return null;

            return new ConcatExpression(field.FormatedName);
        }

        /// <summary>降序</summary>
        /// <param name="field">字段</param>
        /// <remarks>感谢 树懒（303409914）发现这里的错误</remarks>
        /// <returns></returns>
        public static ConcatExpression Desc(this FieldItem field)
        {
            if (field == null || field.FormatedName.IsNullOrEmpty()) return null;

            return new ConcatExpression(field.FormatedName + " Desc");
        }

        /// <summary>通过参数置顶升序降序</summary>
        /// <param name="field">字段</param>
        /// <param name="isdesc">是否降序</param>
        /// <returns></returns>
        public static ConcatExpression Sort(this FieldItem field, Boolean isdesc) => isdesc ? Desc(field) : Asc(field);
        #endregion

        #region 分组选择
        /// <summary>分组。有条件的分组请使用WhereExpression.GroupBy</summary>
        /// <returns></returns>
        public static ConcatExpression GroupBy(this FieldItem field) => field == null ? null : new ConcatExpression($"Group By {field.FormatedName}");

        ///// <summary>按照指定若干个字段分组。没有条件时使用分组请用FieldItem的GroupBy</summary>
        ///// <param name="where"></param>
        ///// <param name="fields"></param>
        ///// <returns>返回条件语句加上分组语句</returns>
        //public static ConcatExpression GroupBy(this WhereExpression where, params FieldItem[] fields)
        //{
        //    var exp = new ConcatExpression();
        //    var sb = exp.Builder;
        //    where.GetString(sb, null);

        //    if (sb.Length > 0) sb.Append(" Group By ");

        //    for (var i = 0; i < fields.Length; i++)
        //    {
        //        if (i > 0) sb.Append(", ");
        //        sb.Append(fields[i].FormatedName);
        //    }

        //    return exp;
        //}

        /// <summary>按照指定若干个字段分组。没有条件时使用分组请用FieldItem的GroupBy</summary>
        /// <param name="where"></param>
        /// <param name="fields"></param>
        /// <returns>将需要分组的字段作为ConcatExpression类型添加到whereExpression尾部</returns>
        public static WhereExpression GroupBy(this WhereExpression where, params FieldItem[] fields)
        {
            var exp = new ConcatExpression();

            for (var i = 0; i < fields.Length; i++)
            {
                if (i == 0)
                    exp &= fields[i].GroupBy();
                else
                    exp.And(new FieldExpression(fields[i]));
            }

            return new WhereExpression(where, Operator.Space, exp);
        }

        /// <summary>聚合</summary>
        /// <param name="field">字段</param>
        /// <param name="action"></param>
        /// <param name="newName"></param>
        /// <returns></returns>
        public static ConcatExpression Aggregate(this FieldItem field, String action, String newName)
        {
            if (field == null) return null;

            var name = field.FormatedName;
            if (newName.IsNullOrEmpty()) newName = name;

            return new ConcatExpression($"{action}({name}) as {newName}");
        }

        /// <summary>作为新的列</summary>
        /// <param name="field"></param>
        /// <param name="newName"></param>
        /// <returns></returns>
        public static ConcatExpression As(this FieldItem field, String newName)
        {
            if (field == null) return null;

            var name = field.FormatedName;
            if (newName.IsNullOrEmpty()) newName = name;

            return new ConcatExpression($"{name} as {newName}");
        }

        /// <summary>计数</summary>
        /// <param name="field">字段</param>
        /// <param name="newName">聚合后as的新名称，默认空，表示跟前面字段名一致</param>
        /// <returns></returns>
        public static ConcatExpression Count(this FieldItem field, String newName = null) => Aggregate(field, "Count", newName);

        /// <summary>计数（唯一）</summary>
        /// <param name="field">字段</param>
        /// <param name="newName">聚合后as的新名称，默认空，表示跟前面字段名一致</param>
        /// <returns></returns>
        public static ConcatExpression CountDistinct(this FieldItem field, String newName = null)
        {
            if (field == null) return null;

            var name = field.FormatedName;
            if (newName.IsNullOrEmpty()) newName = name;

            return new ConcatExpression($"Count(distinct {name}) as {newName}");
        }


        /// <summary>求和</summary>
        /// <param name="field">字段</param>
        /// <param name="newName">聚合后as的新名称，默认空，表示跟前面字段名一致</param>
        /// <returns></returns>
        public static ConcatExpression Sum(this FieldItem field, String newName = null) => Aggregate(field, "Sum", newName);

        /// <summary>最小值</summary>
        /// <param name="field">字段</param>
        /// <param name="newName">聚合后as的新名称，默认空，表示跟前面字段名一致</param>
        /// <returns></returns>
        public static ConcatExpression Min(this FieldItem field, String newName = null) => Aggregate(field, "Min", newName);

        /// <summary>最大值</summary>
        /// <param name="field">字段</param>
        /// <param name="newName">聚合后as的新名称，默认空，表示跟前面字段名一致</param>
        /// <returns></returns>
        public static ConcatExpression Max(this FieldItem field, String newName = null) => Aggregate(field, "Max", newName);

        /// <summary>平均值</summary>
        /// <param name="field">字段</param>
        /// <param name="newName">聚合后as的新名称，默认空，表示跟前面字段名一致</param>
        /// <returns></returns>
        public static ConcatExpression Avg(this FieldItem field, String newName = null) => Aggregate(field, "Avg", newName);
        #endregion

        #region 高级聚合
        /// <summary>sumCase子句，计算等于某个值的数量</summary>
        /// <param name="field">字段</param>
        /// <param name="value">值</param>
        /// <param name="newName">聚合后as的新名称</param>
        /// <returns></returns>
        public static FormatExpression SumCase(this FieldItem field, Object value, String newName) => new(field, "sum(case when {0}={1} then 1 else 0 end) " + newName, value);

        /// <summary>sumCase子句，计算大于某个值的数量</summary>
        /// <param name="field">字段</param>
        /// <param name="value">值</param>
        /// <param name="newName">聚合后as的新名称</param>
        /// <returns></returns>
        public static FormatExpression SumLarge(this FieldItem field, Object value, String newName) => new(field, "sum(case when {0}>{1} then 1 else 0 end) " + newName, value);

        /// <summary>sumCase子句，计算大于等于某个值的数量</summary>
        /// <param name="field">字段</param>
        /// <param name="value">值</param>
        /// <param name="newName">聚合后as的新名称</param>
        /// <returns></returns>
        public static FormatExpression SumLargeEqual(this FieldItem field, Object value, String newName) => new(field, "sum(case when {0}>={1} then 1 else 0 end) " + newName, value);

        /// <summary>sumCase子句，计算小于某个值的数量</summary>
        /// <param name="field">字段</param>
        /// <param name="value">值</param>
        /// <param name="newName">聚合后as的新名称</param>
        /// <returns></returns>
        public static FormatExpression SumLess(this FieldItem field, Object value, String newName) => new(field, "sum(case when {0}<{1} then 1 else 0 end) " + newName, value);

        /// <summary>sumCase子句，计算小于等于某个值的数量</summary>
        /// <param name="field">字段</param>
        /// <param name="value">值</param>
        /// <param name="newName">聚合后as的新名称</param>
        /// <returns></returns>
        public static FormatExpression SumLessEqual(this FieldItem field, Object value, String newName) => new(field, "sum(case when {0}<={1} then 1 else 0 end) " + newName, value);
        #endregion
    }
}