using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using VolPro.Core.Configuration;
using VolPro.Core.UserManager;
using VolPro.Core.Utilities;
using VolPro.Entity.DomainModels;

namespace VolPro.Core.Generic
{
    public static class GenericDbProviderExtensions
    {
        /// <summary>
        /// 设置逻辑删除字段、审批字段默认值为0
        /// </summary>
        public static T SetLogicDelDefault<T>(this T provider,
            Dictionary<string, object> mainData,
            List<TableColumnField> columns)
            where T : GenericDbProviderBase
        {
            string LogicDelField = provider.GetLogicDelField(columns);
            if (LogicDelField != null)
            {
                mainData[LogicDelField] = 0;
            }
            return provider;
        }

        /// <summary>
        /// 获取逻辑删除字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="columns"></param>
        /// <returns></returns>
        public static string GetLogicDelField<T>(this T provider, List<TableColumnField> columns)
            where T : GenericDbProviderBase
        {
            if (!string.IsNullOrEmpty(AppSetting.LogicDelField))
            {
                var logicCol = columns.FirstOrDefault(c =>
                    c.ColumnName.Equals(AppSetting.LogicDelField, StringComparison.OrdinalIgnoreCase));
                if (logicCol != null)
                {
                    return logicCol.ColumnName;
                }
            }
            return null;
        }

        public static T SetAuditDefault<T>(this T provider,
          Dictionary<string, object> mainData,
          List<TableColumnField> columns)
          where T : GenericDbProviderBase
        {
            var auditCol = columns.FirstOrDefault(c =>
                c.ColumnName.Equals("AuditStatus", StringComparison.OrdinalIgnoreCase));
            if (auditCol != null)
            {
                if (!mainData.TryGetValue(auditCol.ColumnName, out object val)
                    || val == null
                    || (val is string s && string.IsNullOrWhiteSpace(s)))
                {
                    mainData[auditCol.ColumnName] = 0;
                }
            }
            return provider;
        }

        /// <summary>
        /// 根据主键类型自动生成主键值（雪花、Guid、string-Guid）
        /// </summary>
        public static T SetPrimaryKey<T>(this T provider,
            Dictionary<string, object> mainData,
            TableColumnField keyColumn)
            where T : GenericDbProviderBase
        {
            if (keyColumn == null) return provider;

            string keyName = keyColumn.ColumnName;
            string type = (keyColumn.ColumnType ?? "").Trim().ToLower();

            mainData.TryGetValue(keyName, out object keyVal);
            bool hasValue = !string.IsNullOrEmpty(keyVal?.ToString());

            // bigint/long 雪花ID
            if ((type == "long" || type == "bigint") && AppSetting.UseSnow)
            {
                var worker = new IdWorker();
                long id = worker.NextId();
                mainData[keyName] = id;
                return provider;
            }

            // Guid 主键,string 主键，且未传值时生成 Guid 字符串
            if (type == "guid" || (type == "string" && !hasValue))
            {
                mainData[keyName] = Guid.NewGuid();
                return provider;
            }
            return provider;
        }

        private static List<string> ModifyFields { get; set; }

        /// <summary>
        /// 获取需要忽略修改人/修改时间字段
        /// </summary>
        public static List<string> GetModifyFieldsToIgnore<T>(this T provider)
            where T : GenericDbProviderBase
        {
            if (ModifyFields != null)
            {
                return ModifyFields;
            }
            var ignore = new HashSet<string>(StringComparer.OrdinalIgnoreCase);

            if (!string.IsNullOrWhiteSpace(AppSetting.ModifyMember?.UserIdField))
            {
                ignore.Add(AppSetting.ModifyMember.UserIdField);
            }
            if (!string.IsNullOrWhiteSpace(AppSetting.ModifyMember?.UserNameField))
            {
                ignore.Add(AppSetting.ModifyMember.UserNameField);
            }
            if (!string.IsNullOrWhiteSpace(AppSetting.ModifyMember?.DateField))
            {
                ignore.Add(AppSetting.ModifyMember.DateField);
            }
            ModifyFields = ignore.ToList();
            return ModifyFields;
        }

        private static HashSet<string> AddFields { get; set; }
        /// <summary>
        /// 获取需要忽略写入人
        /// </summary>
        public static HashSet<string> GetAddFieldsToIgnore<T>(this T provider)
            where T : GenericDbProviderBase
        {
            if (AddFields != null)
            {
                return AddFields;
            }
            var ignore = new HashSet<string>(StringComparer.OrdinalIgnoreCase);

            if (!string.IsNullOrWhiteSpace(AppSetting.CreateMember?.UserIdField))
            {
                ignore.Add(AppSetting.CreateMember.UserIdField);
            }
            if (!string.IsNullOrWhiteSpace(AppSetting.CreateMember?.UserNameField))
            {
                ignore.Add(AppSetting.CreateMember.UserNameField);
            }
            if (!string.IsNullOrWhiteSpace(AppSetting.CreateMember?.DateField))
            {
                ignore.Add(AppSetting.CreateMember.DateField);
            }
            AddFields = ignore;
            return AddFields;
        }
        private static HashSet<string> AddAndModifyFields { get; set; }
        public static HashSet<string> GetAddAndModifyFieldsToIgnore<T>(this T provider)
          where T : GenericDbProviderBase
        {
            if (AddAndModifyFields == null)
            {
                AddAndModifyFields = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
                GetAddFieldsToIgnore(provider).ToList().ForEach(x => AddAndModifyFields.Add(x));
                GetModifyFieldsToIgnore(provider).ToList().ForEach(x => AddAndModifyFields.Add(x));
            }
            return AddAndModifyFields;
        }



        /// <summary>
        /// 判断字段类型是否为整型（int/long/bigint）
        /// </summary>
        public static bool IsIntOrLong<T>(this T provider, string columnType)
            where T : GenericDbProviderBase
        {
            string type = (columnType ?? "").Trim().ToLower();
            return type == "int" || type == "long" || type == "bigint";
        }
        /// <summary>
        /// 是否自增主键
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="provider"></param>
        /// <param name="columnType"></param>
        /// <returns></returns>
        public static bool IsIdentity<T>(this T provider, string columnType)
        where T : GenericDbProviderBase
        {
            string type = (columnType ?? "").Trim().ToLower();
            if (type == "int")
            {
                return true;
            }
            return (type == "long" || type == "bigint") && !AppSetting.UseSnow;
        }

        // GetDbType / IsZero 已移动到 GenericDbValidationExtensions.cs

        private static void AddWhereSugarParameter(List<SugarParameter> parameters, string name, object value, TableColumnField col)
        {
            var par = new SugarParameter(name, value);
            var dbType = GenericDbValidationExtensions.EffectiveDapperDbType(value, col);
            if (dbType != null)
            {
                par.DbType = (System.Data.DbType)dbType;
            }
            parameters.Add(par);
        }

        /// <summary>
        /// 构造查询条件 where 语句
        /// </summary>
        public static T BuildWhere<T>(this T provider,
            List<SearchParameters> filters,
            List<TableColumnField> columns,
            List<string> whereList,
            List<SugarParameter> parameters,
            string LeftQuote,
            string RightQuote)
             where T : GenericDbProviderBase
        {
            if (filters == null || filters.Count == 0) return provider;

            int index = 0;

            foreach (var f in filters)
            {
                if (string.IsNullOrEmpty(f?.Name)) continue;

                var col = columns.FirstOrDefault(c =>
                    c.ColumnName.Equals(f.Name, StringComparison.OrdinalIgnoreCase));
                if (col == null) continue;

                string op = (f.DisplayType ?? "").ToLower();

                if (op == "isnull")
                {
                    whereList.Add($"{LeftQuote}{col.ColumnName}{RightQuote} IS NULL");
                    continue;
                }
                if (op == "isnotnull")
                {
                    whereList.Add($"{LeftQuote}{col.ColumnName}{RightQuote} IS NOT NULL");
                    continue;
                }

                if (string.IsNullOrEmpty(f.Value)) continue;

                object ConvertValue(string val)
                {
                    string type = (col.ColumnType ?? "").ToLower();
                    if (type == "long" || type == "bigint" || type == "int64")
                    {
                        if (long.TryParse(val, out var lv))
                            return lv;
                        return null;
                    }
                    if (type.Contains("int"))
                    {
                        if (int.TryParse(val,out var iv))
                            return iv;
                        return null;
                    }
                    if (type == "guid" || type == "uniqueidentifier")
                    {
                        if (Guid.TryParse(val, out var gv)) return gv;
                        return null;
                    }
                    if (type.Contains("decimal") || type.Contains("numeric") || type.Contains("money"))
                    {
                        if (decimal.TryParse(val, out var dv)) return dv;
                        return null;
                    }
                    if (type.Contains("float") || type.Contains("double"))
                    {
                        if (double.TryParse(val, out var fv)) return fv;
                        return null;
                    }
                    if (type.Contains("date") || type.Contains("time"))
                    {
                        if (DateTime.TryParse(val, out var dt)) return dt;
                        return null;
                    }
                    if (type.Contains("bit") || type.Contains("bool"))
                    {
                        if (val == "1" || val.Equals("true", StringComparison.OrdinalIgnoreCase)) return true;
                        if (val == "0" || val.Equals("false", StringComparison.OrdinalIgnoreCase)) return false;
                    }
                    return val;
                }

                bool isIn = op == "in" || op == "selectlist" || op == "checkbox" || op == "notin";
                if (isIn)
                {
                    var arr = f.Value.Split(',', StringSplitOptions.RemoveEmptyEntries)
                        .Select(v => v.Trim())
                        .ToArray();
                    if (arr.Length == 0) continue;

                    var paramNames = new List<string>();
                    foreach (var v in arr)
                    {
                        var cv = ConvertValue(v);
                        if (cv == null) continue;
                        string name = $"@p{index++}";
                        string placeholder = name;
                        paramNames.Add(placeholder);
                        AddWhereSugarParameter(parameters, name, cv, col);
                    }
                    if (paramNames.Count == 0) continue;

                    string inSql = string.Join(",", paramNames);
                    whereList.Add($"{LeftQuote}{col.ColumnName}{RightQuote} {(op == "notin" ? "NOT IN" : "IN")} ({inSql})");
                    continue;
                }

                // LIKE / 模糊查询，统一在这里处理，保证三种数据库（PgSql/MySql/SqlServer）都能正常工作
                bool isLikeOp = op == "like" || op == "contains"
                                || op == "startwith" || op == "likestart"
                                || op == "endwith" || op == "likeend";
                if (isLikeOp)
                {
                    string raw = f.Value;
                    string pattern;
                    switch (op)
                    {
                        case "startwith":
                        case "likestart":
                            pattern = raw + "%";
                            break;
                        case "endwith":
                        case "likeend":
                            pattern = "%" + raw;
                            break;
                        case "like":
                        case "contains":
                        default:
                            pattern = "%" + raw + "%";
                            break;
                    }

                    string nameLike = $"p{index++}";
                    string placeholderLike = $"@{nameLike}";
                    AddWhereSugarParameter(parameters, placeholderLike, pattern, col);
                    whereList.Add($"{LeftQuote}{col.ColumnName}{RightQuote} LIKE {placeholderLike}");
                    continue;
                }

                var valObj = ConvertValue(f.Value);
                if (valObj == null) continue;

                string name1 = $"p{index++}";
                string placeholder1 = $"@{name1}";
                AddWhereSugarParameter(parameters, placeholder1, valObj, col);

                switch (op)
                {
                    case "gt":
                    case ">":
                        whereList.Add($"{LeftQuote}{col.ColumnName}{RightQuote} > {placeholder1}");
                        break;
                    case ">=":
                    case "thanorequal":
                        whereList.Add($"{LeftQuote}{col.ColumnName}{RightQuote} >= {placeholder1}");
                        break;
                    case "lt":
                    case "<":
                        whereList.Add($"{LeftQuote}{col.ColumnName}{RightQuote} < {placeholder1}");
                        break;
                    case "<=":
                    case "lessorequal":
                        whereList.Add($"{LeftQuote}{col.ColumnName}{RightQuote} <= {placeholder1}");
                        break;
                    case "neq":
                    case "!=":
                    case "<>":
                        whereList.Add($"{LeftQuote}{col.ColumnName}{RightQuote} <> {placeholder1}");
                        break;
                    case "eq":
                    case "==":
                    case "=":
                    default:
                        whereList.Add($"{LeftQuote}{col.ColumnName}{RightQuote} = {placeholder1}");
                        break;
                }
            }
            return provider;
        }

        /// <summary>
        /// 列是否为 Guid / uniqueidentifier 语义
        /// </summary>
        internal static bool IsGuidColumn(TableColumnField col)
        {
            if (col == null) return false;
            if (col.GetDbType() == System.Data.DbType.Guid) return true;
            string t = (col.ColumnType ?? "").Trim().ToLowerInvariant();
            return t == "guid" || t == "uniqueidentifier";
        }

    }
}