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

namespace VolPro.Core.Generic
{
    /// <summary>
    /// 通用数据库字段与明细数据校验扩展
    /// </summary>
    public static class GenericDbValidationExtensions
    {
        /// <summary>
        /// 字段校验：必填、长度、类型
        /// </summary>
        public static string ValidateColumns<T>(this T provider,
            Dictionary<string, object> mainData,
            List<TableColumnField> columns)
            where T : GenericDbProviderBase
        {
            foreach (var col in columns)
            {
                string colName = col.ColumnName;
                string displayName = string.IsNullOrEmpty(col.ColumnCnName) ? colName : col.ColumnCnName;

                mainData.TryGetValue(colName, out object val);
                bool hasValue = val != null && !(val is string s && string.IsNullOrWhiteSpace(s));
                if (col.IsKey==1)
                {
                    continue;
                }
                // 必填校验（IsNull 为 0 时字段必填）
                if ((col.IsNull ?? 1) == 0 && !hasValue)
                {
                    return $"【{displayName}】不能为空";
                }

                if (!hasValue)
                {
                    continue;
                }

                string type = (col.ColumnType ?? "").Trim().ToLower();

                // 最大长度校验（只判断字符串类型）
                if (type == "string" && (col.Maxlength ?? 0) > 0)
                {
                    string str = val.ToString();
                    if (col.Maxlength.HasValue && str.Length > col.Maxlength.Value)
                    {
                        return $"【{displayName}】长度不能超过{col.Maxlength}个字符";
                    }
                }

                // 类型匹配校验
                if (!provider.CheckAndConvertValue(mainData, colName, val, type, displayName, out string errorMsg))
                {
                    return errorMsg;
                }
            }

            return null;
        }

        /// <summary>
        /// 处理字段类型转换与校验
        /// </summary>
        public static bool CheckAndConvertValue<T>(this T provider,
            Dictionary<string, object> mainData,
            string fieldName,
            object val,
            string type,
            string displayName,
            out string error)
            where T : GenericDbProviderBase
        {
            error = null;
            if (string.IsNullOrEmpty(type))
            {
                return true;
            }

            try
            {
                object converted = val;
                string str = val as string ?? val.ToString();

                switch (type)
                {
                    case "int":
                    case "short":
                    case "byte":
                        if (!(val is int || val is short || val is byte))
                        {
                            if (!int.TryParse(str, out int iv))
                            {
                                error = $"【{displayName}】必须是整数";
                                return false;
                            }
                            converted = iv;
                        }
                        break;
                    case "long":
                    case "bigint":
                        if (!(val is long))
                        {
                            if (!long.TryParse(str, out long lv))
                            {
                                error = $"【{displayName}】必须是整数";
                                mainData[fieldName] = lv;
                                return false;
                            }
                            converted = lv;
                        }
                        break;
                    case "decimal":
                        if (!(val is decimal))
                        {
                            if (!decimal.TryParse(str, out decimal dv))
                            {
                                error = $"【{displayName}】必须是数字";
                                return false;
                            }
                            converted = dv;
                        }
                        break;
                    case "float":
                        if (!(val is float || val is double))
                        {
                            if (!double.TryParse(str, out double fv))
                            {
                                error = $"【{displayName}】必须是数字";
                                mainData[fieldName] = fv;
                                return false;
                            }
                            converted = fv;
                        }
                        break;
                    case "datetime":
                    case "date":
                    case "time":
                        if (!(val is DateTime))
                        {
                            if (!DateTime.TryParse(str, out DateTime dt))
                            {
                                error = $"【{displayName}】日期格式不正确";
                                return false;
                            }
                            converted = dt;
                        }
                        break;
                    case "bool":
                        if (!(val is bool))
                        {
                            string b = str.ToLower();
                            if (b == "1" || b == "true" || b == "是")
                            {
                                converted = true;
                            }
                            else if (b == "0" || b == "false" || b == "否")
                            {
                                converted = false;
                            }
                            else
                            {
                                error = $"【{displayName}】必须是布尔值";
                                return false;
                            }
                        }
                        break;
                    case "guid":
                        if (!(val is Guid))
                        {
                            if (!Guid.TryParse(str, out Guid g))
                            {
                                error = $"【{displayName}】不是有效的Guid";
                                return false;
                            }
                            converted = g;
                        }
                        break;
                    default:
                        break;
                }

                mainData[fieldName] = converted;
                return true;
            }
            catch
            {
                error = $"【{displayName}】数据格式不正确";
                return false;
            }
        }

        /// <summary>
        /// 根据表字段配置获取对应的 DbType，避免 PgSql 等数据库将参数推断为 text 导致类型不匹配
        /// </summary>
        public static DbType? GetDbType(this TableColumnField col)
        {
            if (col?.ColumnType == null) return null;
            string type = col.ColumnType.Trim().ToLower();
            switch (type)
            {
                case "int":
                case "short":
                case "byte":
                    return DbType.Int32;
                case "long":
                case "bigint":
                    return DbType.Int64;
                case "string":
                    return DbType.String;
                case "guid":
                case "uniqueidentifier":
                    return DbType.Guid;
                case "datetime":
                case "date":
                case "time":
                    return DbType.DateTime;
                case "decimal":
                    return DbType.Decimal;
                case "float":
                case "double":
                    return DbType.Double;
                case "bool":
                    return DbType.Boolean;
                default:
                    return null;
            }
        }

        /// <summary>
        /// 结合列元数据与运行时值确定参数 DbType（与 SugarParameter / Dapper 一致）
        /// </summary>
        internal static DbType? EffectiveDapperDbType(object value, TableColumnField col)
        {
            if (IsGuidColumn(col) && value is Guid) return DbType.Guid;
            var fromCol = col?.GetDbType();
            if (fromCol != null) return fromCol;
            return InferDbTypeFromClrValue(value);
        }

        private static DbType? InferDbTypeFromClrValue(object value)
        {
            if (value == null || value is DBNull) return null;
            var tc = Type.GetTypeCode(value.GetType());
            switch (tc)
            {
                case TypeCode.Boolean: return DbType.Boolean;
                case TypeCode.Byte: return DbType.Byte;
                case TypeCode.Int16: return DbType.Int16;
                case TypeCode.Int32: return DbType.Int32;
                case TypeCode.Int64: return DbType.Int64;
                case TypeCode.Single: return DbType.Single;
                case TypeCode.Double: return DbType.Double;
                case TypeCode.Decimal: return DbType.Decimal;
                case TypeCode.DateTime: return DbType.DateTime;
                case TypeCode.String: return DbType.String;
                default:
                    return value is Guid ? DbType.Guid : (DbType?)null;
            }
        }

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

        /// <summary>
        /// 判断值是否等于0（用于判断主键是否有意义的值）
        /// </summary>
        public static bool IsZero<T>(this T provider, object value)
            where T : GenericDbProviderBase
        {
            if (value == null) return true;

            switch (Type.GetTypeCode(value.GetType()))
            {
                case TypeCode.Byte:
                case TypeCode.Int16:
                case TypeCode.Int32:
                case TypeCode.Int64:
                case TypeCode.Decimal:
                case TypeCode.Double:
                case TypeCode.Single:
                    return Convert.ToDecimal(value) == 0;
                case TypeCode.String:
                    if (decimal.TryParse((string)value, out decimal d))
                    {
                        return d == 0;
                    }
                    break;
            }
            return false;
        }

        /// <summary>
        /// Add 时，对所有明细表做字段校验，且忽略外键字段（与主表主键同名字段）的必填要求
        /// </summary>
        public static string ValidateAllDetails<T>(this T provider, SaveModel saveModel, List<TableColumnField> mainTableColumns, TableColumnField mainKeyColumn)
            where T : GenericDbProviderBase
        {
            // 单明细表
            if (saveModel.DetailData != null && saveModel.DetailData.Count > 0)
            {
                string msg = provider.ValidateDetailList(provider.TableInfo.DetailName, saveModel.DetailData, mainKeyColumn);
                if (!string.IsNullOrEmpty(msg)) return msg;
            }

            // 多明细
            if (saveModel.Details != null && saveModel.Details.Count > 0)
            {
                foreach (var item in saveModel.Details)
                {
                    if (item?.Data == null || item.Data.Count == 0) continue;
                    string msg = provider.ValidateDetailList(item.Table, item.Data, mainKeyColumn);
                    if (!string.IsNullOrEmpty(msg)) return msg;
                }
            }

            return null;
        }

        /// <summary>
        /// Add 时，校验明细表的所有数据行（忽略外键必填）
        /// </summary>
        public static string ValidateDetailList<T>(this T provider, string detailTableName, List<Dictionary<string, object>> rows, TableColumnField mainKeyColumn)
            where T : GenericDbProviderBase
        {
            if (string.IsNullOrEmpty(detailTableName) || rows == null || rows.Count == 0) return null;

            var detailColumns = TableColumnContext.Data
                .Where(x => x.TableName == detailTableName && x.ReferenceField == 0 && x.IsKey != 1)
                .ToList();
            if (detailColumns == null || detailColumns.Count == 0) return null;

            // 找到外键字段：与主表主键同名
            var foreignCol = detailColumns.FirstOrDefault(c => c.ColumnName.Equals(mainKeyColumn.ColumnName, StringComparison.OrdinalIgnoreCase));

            List<string> ingroCols = new List<string>();
            if (foreignCol != null)
            {
                ingroCols.Add(foreignCol.ColumnName);
            }
            // 忽略外键必填，把外键字段的 IsNull 视为可空（1），即不参与必填校验
            List<TableColumnField> validateColumns = detailColumns.Where(c => !ingroCols.Contains(c.ColumnName))
                    .ToList();

            for (int i = 0; i < rows.Count; i++)
            {
                var row = rows[i];
                string msg = provider.ValidateColumns(row, validateColumns);
                if (!string.IsNullOrEmpty(msg))
                {
                    return "第[{$ts}]行".TranslatorFormat(i + 1) + "," + msg;
                }
            }
            return null;
        }

        /// <summary>
        /// Update 时，对所有明细表做字段校验（只校验提交的字段，并忽略外键必填）
        /// </summary>
        public static string ValidateAllDetailsForUpdate<T>(this T provider, SaveModel saveModel, TableColumnField mainKeyColumn)
            where T : GenericDbProviderBase
        {
            // 单明细表
            if (saveModel.DetailData != null && saveModel.DetailData.Count > 0)
            {
                string msg = provider.ValidateDetailListForUpdate(provider.TableInfo.DetailName, saveModel.DetailData, mainKeyColumn);
                if (!string.IsNullOrEmpty(msg)) return msg;
            }

            // 多明细
            if (saveModel.Details != null && saveModel.Details.Count > 0)
            {
                foreach (var item in saveModel.Details)
                {
                    if (item?.Data == null || item.Data.Count == 0) continue;
                    string msg = provider.ValidateDetailListForUpdate(item.Table, item.Data, mainKeyColumn);
                    if (!string.IsNullOrEmpty(msg)) return msg;
                }
            }

            return null;
        }

        /// <summary>
        /// Update 时，校验某一张明细表的所有数据行（只校验提交的字段，并忽略外键必填）
        /// </summary>
        public static string ValidateDetailListForUpdate<T>(this T provider, string detailTableName, List<Dictionary<string, object>> rows, TableColumnField mainKeyColumn)
            where T : GenericDbProviderBase
        {
            if (string.IsNullOrEmpty(detailTableName) || rows == null || rows.Count == 0) return null;

            var detailColumns = TableColumnContext.Data
                .Where(x => x.TableName == detailTableName && x.ReferenceField == 0)
                .ToList();
            if (detailColumns == null || detailColumns.Count == 0) return null;

            // 找到外键字段：与主表主键同名、同类型
            var foreignCol = detailColumns.FirstOrDefault(c =>
                c.ColumnName.Equals(mainKeyColumn.ColumnName, StringComparison.OrdinalIgnoreCase)
                && string.Equals(c.ColumnType, mainKeyColumn.ColumnType, StringComparison.OrdinalIgnoreCase));

            foreach (var row in rows)
            {
                if (row == null) continue;

                // 只校验提交的字段
                var submitColumns = detailColumns
                    .Where(c => row.ContainsKey(c.ColumnName))
                    .ToList();

                if (submitColumns.Count == 0) continue;

                // 忽略外键必填：将外键列视为可空
                if (foreignCol != null && submitColumns.Any(c => c.ColumnName.Equals(foreignCol.ColumnName, StringComparison.OrdinalIgnoreCase)))
                {
                    submitColumns = submitColumns
                        .Select(c =>
                        {
                            if (!c.ColumnName.Equals(foreignCol.ColumnName, StringComparison.OrdinalIgnoreCase))
                            {
                                return c;
                            }
                            return new TableColumnField
                            {
                                ColumnName = c.ColumnName,
                                ColumnCnName = c.ColumnCnName,
                                ColumnType = c.ColumnType,
                                TableName = c.TableName,
                                IsDisplay = c.IsDisplay,
                                ReferenceField = c.ReferenceField,
                                Maxlength = c.Maxlength,
                                IsKey = c.IsKey,
                                IsNull = 1
                            };
                        })
                        .ToList();
                }

                string msg = provider.ValidateColumns(row, submitColumns);
                if (!string.IsNullOrEmpty(msg)) return msg;
            }

            return null;
        }
    }
}