using System;
using System.Collections.Generic;
using System.Data;
using System.Text.RegularExpressions;
using NewLife;
using NewLife.Collections;

namespace XCode.DataAccessLayer
{
    /// <summary>SQL查询语句生成器</summary>
    /// <remarks>
    /// 查询语句的复杂性，使得多个地方使用起来极为不方面。
    /// 应该以本类作为查询对象，直接从最上层深入到最下层
    /// </remarks>
    public class SelectBuilder
    {
        #region 属性
        /// <summary>分页主键</summary>
        public String Key { get; set; }

        /// <summary>选择列</summary>
        public String Column { get; set; }

        /// <summary>数据表</summary>
        public String Table { get; set; }

        private String _Where;
        /// <summary>条件</summary>
        public String Where { get => _Where; set => _Where = ParseWhere(value); }

        /// <summary>分组</summary>
        public String GroupBy { get; set; }

        /// <summary>分组条件</summary>
        public String Having { get; set; }

        private String _OrderBy;
        /// <summary>排序</summary>
        /// <remarks>给排序赋值时，如果没有指定分页主键，则自动采用排序中的字段</remarks>
        public String OrderBy { get => _OrderBy; set => _OrderBy = ParseOrderBy(value); }

        /// <summary>分页用的Limit语句</summary>
        public String Limit { get; set; }

        /// <summary>参数集合</summary>
        public List<IDataParameter> Parameters { get; set; } = new();
        #endregion

        #region 构造
        /// <summary>实例化一个SQL语句</summary>
        public SelectBuilder() { }

        /// <summary>实例化一个SQL语句</summary>
        /// <param name="sql"></param>
        public SelectBuilder(String sql) => Parse(sql);
        #endregion

        #region 导入SQL
        private const String SelectRegex = @"(?isx-m)
^
\s*\bSelect\s+(?<选择列>(?>[^()]+?|\((?<Open>)|\)(?<-Open>))*?(?(Open)(?!)))
\s+From\s+(?<数据表>(?>[^()]+?|\((?<Open>)|\)(?<-Open>))*?(?(Open)(?!)))
(?:\s+Where\s+(?<条件>(?>[^()]+?|\((?<Open>)|\)(?<-Open>))*?(?(Open)(?!))))?
(?:\s+Group\s+By\s+(?<分组>(?>[^()]+?|\((?<Open>)|\)(?<-Open>))*?(?(Open)(?!))))?
(?:\s+Having\s+(?<分组条件>(?>[^()]+?|\((?<Open>)|\)(?<-Open>))*?(?(Open)(?!))))?
(?:\s+Order\s+By\s+(?<排序>(?>[^()]+?|\((?<Open>)|\)(?<-Open>))*?(?(Open)(?!))))?
$";
        // 可以单独识别SQLite等数据库分页的Limit字句，但那样加大了复杂性
        // (?:\s+(?<Limit>(?>Limit|Rows)\s+(\d+\s*(?>,|To|Offset)\s*)?\d+))?

        // 如果字符串内容里面含有圆括号，这个正则将无法正常工作，字符串边界的单引号也不好用平衡组，可以考虑在匹配前先用正则替换掉字符串

        static readonly Regex regexSelect = new(SelectRegex, RegexOptions.Compiled);

        /// <summary>分析一条SQL</summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public Boolean Parse(String sql)
        {
            var m = regexSelect.Match(sql);
            if (m != null && m.Success)
            {
                Column = (m.Groups["选择列"].Value + "").Trim();
                Table = (m.Groups["数据表"].Value + "").Trim();
                Where = (m.Groups["条件"].Value + "").Trim();
                GroupBy = (m.Groups["分组"].Value + "").Trim();
                Having = (m.Groups["分组条件"].Value + "").Trim();
                OrderBy = (m.Groups["排序"].Value + "").Trim();
                //Limit = m.Groups["Limit"].Value;

                return true;
            }

            return false;
        }

        private static readonly Regex reg_gb = new(@"\bgroup\b\s*\bby\b", RegexOptions.Compiled | RegexOptions.IgnoreCase);
        String ParseWhere(String value)
        {
            // 里面可能含有分组
            if (!value.IsNullOrEmpty())
            {
                var where = value.ToLower();
                if (where.Contains("group") && where.Contains("by"))
                {
                    var match = reg_gb.Match(value);
                    if (match != null && match.Success)
                    {
                        var gb = value[(match.Index + match.Length)..].Trim();
                        if (GroupBy.IsNullOrEmpty())
                            GroupBy = gb;
                        else
                            GroupBy += ", " + gb;

                        value = value[..match.Index].Trim();
                    }
                }
            }

            if (value == "1=1") value = null;

            // 不要轻易Trim掉空白，可能外部用来拼接And语句
            //return value?.Trim();
            return value;
        }

        String ParseOrderBy(String value)
        {
            // 分析排序字句，从中分析出分页用的主键
            if (!value.IsNullOrEmpty() && Key.IsNullOrEmpty())
            {
                var p = value.IndexOfAny(new[] { ',', ' ' });
                if (p > 0)
                    Key = value[..p];
                else
                    Key = value;
            }

            return value?.Trim();
        }
        #endregion

        #region 导出SQL
        /// <summary>已重写。获取本Builder所分析的SQL语句</summary>
        /// <returns></returns>
        public override String ToString()
        {
            var sb = Pool.StringBuilder.Get();
            sb.Append("Select ");
            sb.Append(Column.IsNullOrEmpty() ? "*" : Column);
            sb.Append(" From ");
            sb.Append(Table);
            if (!Where.IsNullOrEmpty()) sb.Append(" Where " + Where);
            if (!GroupBy.IsNullOrEmpty()) sb.Append(" Group By " + GroupBy);
            if (!Having.IsNullOrEmpty()) sb.Append(" Having " + Having);
            if (!OrderBy.IsNullOrEmpty()) sb.Append(" Order By " + OrderBy);
            if (!Limit.IsNullOrEmpty()) sb.Append(Limit.EnsureStart(" "));

            return sb.Put(true);
        }

        /// <summary>获取记录数的语句</summary>
        /// <returns></returns>
        public virtual SelectBuilder SelectCount()
        {
            //SelectBuilder sb = this.Clone();
            //sb.OrderBy = null;
            //// 包含GroupBy时，作为子查询
            //if (!String.IsNullOrEmpty(GroupBy)) sb.Table = String.Format("({0}) as SqlBuilder_T0", sb.ToString());
            //sb.Column = "Count(*)";
            //return sb;

            // 该BUG由@行走江湖（534163320）发现

            // 包含GroupBy时，作为子查询
            var sb = CloneWithGroupBy("XCode_T0", true);
            sb.Column = "Count(*)";
            sb.OrderBy = null;
            return sb;
        }
        #endregion

        #region 方法
        /// <summary>克隆</summary>
        /// <returns></returns>
        public SelectBuilder Clone()
        {
            var sb = new SelectBuilder
            {
                Key = Key,
                Column = Column,
                Table = Table,

                // 直接拷贝字段，避免属性set时触发分析代码
                _Where = _Where,
                _OrderBy = _OrderBy,

                GroupBy = GroupBy,
                Having = Having,
                Limit = Limit,
            };

            sb.Parameters.AddRange(Parameters);

            return sb;
        }

        /// <summary>增加Where条件</summary>
        /// <param name="format"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public SelectBuilder AppendWhereAnd(String format, params Object[] args)
        {
            if (!Where.IsNullOrEmpty())
            {
                if (Where.Contains(" ") && Where.ToLower().Contains("or"))
                    Where = $"({Where}) And ";
                else
                    Where += " And ";
            }
            Where += String.Format(format, args);

            return this;
        }

        /// <summary>作为子查询</summary>
        /// <param name="alias">别名，某些数据库可能需要使用as</param>
        /// <param name="trimOrder">SqlServer需要转移OrderBy到外层，Oracle则不能</param>
        /// <returns></returns>
        public SelectBuilder AsChild(String alias, Boolean trimOrder)
        {
            var t = this;
            // 如果包含排序，则必须有Top，否则去掉
            var hasOrderWithoutTop = false;
            if (trimOrder) hasOrderWithoutTop = !String.IsNullOrEmpty(t.OrderBy) && !Column.StartsWithIgnoreCase("top ");
            if (hasOrderWithoutTop)
            {
                t = Clone();
                t.OrderBy = null;
            }

            var builder = new SelectBuilder();
            if (String.IsNullOrEmpty(alias))
                builder.Table = $"({t})";
            else
                builder.Table = $"({t}) {alias}";

            // 把排序加载外层
            if (hasOrderWithoutTop) builder.OrderBy = OrderBy;

            builder.Parameters.AddRange(Parameters);

            return builder;
        }

        /// <summary>处理可能带GroupBy的克隆，如果带有GroupBy，则必须作为子查询，否则简单克隆即可</summary>
        /// <param name="alias">别名，某些数据库可能需要使用as</param>
        /// <param name="trimOrder">SqlServer需要转移OrderBy到外层，Oracle则不能</param>
        /// <returns></returns>
        public SelectBuilder CloneWithGroupBy(String alias, Boolean trimOrder)
        {
            if (String.IsNullOrEmpty(GroupBy))
                return Clone();
            else
                return AsChild(alias, trimOrder);
        }
        #endregion

        #region 辅助方法
        internal SelectBuilder Top(Int64 top, String keyColumn = null)
        {
            var builder = this;
            if (!String.IsNullOrEmpty(keyColumn)) builder.Column = keyColumn;
            if (String.IsNullOrEmpty(builder.Column)) builder.Column = "*";
            builder.Column = $"Top {top} {builder.Column}";

            return builder;
        }
        #endregion

        #region 类型转换
        /// <summary>类型转换</summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static implicit operator String(SelectBuilder obj) => !obj.Equals(null) ? obj.ToString() : null;
        #endregion
    }
}