using Microsoft.AspNetCore.Http;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Math;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Text;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using VolPro.Core.DBManager;
using VolPro.Core.Enums;
using VolPro.Core.Extensions;
using VolPro.Core.Services;
using VolPro.Core.Tenancy;
using VolPro.Core.UserManager;
using VolPro.Core.Utilities;
using VolPro.Entity.DomainModels;

namespace VolPro.Core.Generic
{
    /// <summary>
    /// 通用数据库 CRUD 抽象基类，封装三种数据库公共逻辑
    /// </summary>
    public abstract class GenericDbProviderBase : GenericBaseService, IGenericDbProvider
    {
        /// <summary>
        /// 不同数据库的左右引号符号（MySql: ``, PgSql: "", SqlServer: []）
        /// </summary>
        protected abstract string LeftQuote { get; }
        protected abstract string RightQuote { get; }

        protected GenericDbProviderBase() : base()
        {
        }

        public virtual async Task<PageGridData<object>> GetPageDataAsync(PageDataOptions options, bool isDetail = false)
        {
            //租户过滤
            options = options ?? new PageDataOptions();
            if (!string.IsNullOrEmpty(options.Wheres) && (options.Filter == null || options.Filter.Count == 0))
            {
                options.Filter = options.Wheres.DeserializeObject<List<SearchParameters>>();
            }
            //获取逻辑删除过滤
            string logicDelField = this.GetLogicDelField(Columns);
            if (logicDelField != null)
            {
                options.Filter.Add(new SearchParameters() { Name = logicDelField, Value = "0" });
            }
            string dbTableName = string.IsNullOrEmpty(TableInfo.TableTrueName)
                ? TableInfo.TableName
                : TableInfo.TableTrueName;

            string selectColumns = string.Join(",", Columns.Select(c => $"{LeftQuote}{c.ColumnName}{RightQuote}"));
            string baseSql = string.IsNullOrEmpty(TableInfo.DbSql)
                ? $"SELECT {selectColumns} FROM {LeftQuote}{dbTableName}{RightQuote}"
                : $"SELECT * from ({TableInfo.DbSql}) TDbSQL ";

            var whereList = new List<string>();
            // SqlSugar 参数集合
            var parameters = new List<SugarParameter>();
            //执行自定义sql
            baseSql = dbTableName.GetSearchSqlQuery(baseSql, options.Filter, parameters) ?? baseSql;
            //过滤租户数据权限
            baseSql = dbTableName.CreateTenancySqlFilter(baseSql, options.Filter, parameters);

            this.BuildWhere(options.Filter, Columns, whereList, parameters, LeftQuote, RightQuote);

            var baseWithWhere = new StringBuilder();
            baseWithWhere.Append(baseSql);
            if (whereList.Count > 0)
            {
                baseWithWhere.Append(" WHERE ").Append(string.Join(" AND ", whereList));
            }

            string sortField = options.Sort;
            string sortOrder = string.IsNullOrEmpty(options.Order) ? "DESC" : options.Order.ToUpper();
            if (string.IsNullOrEmpty(sortField))
            {
                sortField = Columns.FirstOrDefault(c => c.IsKey == 1)?.ColumnName
                            ?? Columns.First().ColumnName;
            }
            string orderBy = $"ORDER BY {LeftQuote}{sortField}{RightQuote} {(sortOrder == "ASC" ? "ASC" : "DESC")}";


            string countSql = $"SELECT COUNT(1) FROM ({baseWithWhere}) T";
            //导出
            if (options.Export)
            {
                options.Rows = 200000;
            }
            int page = options.Page <= 0 ? 1 : options.Page;
            int rows = options.Rows <= 0 ? 30 : options.Rows;

            string pageSql = BuildPageSql(baseWithWhere.ToString(), selectColumns, orderBy, page, rows);

            return new PageGridData<object>()
            {
                rows = await QueryListAsync(pageSql, parameters),
                total = options.Export ? 0 : (await ExecuteScalarAsync(countSql, parameters)).GetInt()
            };
        }
        public virtual async Task<PageGridData<object>> GetDetailPageAsync(PageDataOptions options)
        {
            return await GetPageDataAsync(options, true);
        }

        /// <summary>
        /// 构造分页 SQL，默认使用 LIMIT/OFFSET（MySql、PgSql）
        /// </summary>
        protected virtual string BuildPageSql(string baseWithWhereSql, string selectColumns, string orderBy, int page, int rows)
        {
            int offset = (page - 1) * rows;
            return $"{baseWithWhereSql} {orderBy} LIMIT {rows} OFFSET {offset}";
        }
        /// <summary>
        /// 添加数据Add
        /// </summary>
        /// <param name="saveModel"></param>
        /// <returns></returns>
        public virtual async Task<WebResponseContent> AddAsync(SaveModel saveModel)
        {
            var response = WebResponseContent.Instance;

            if (saveModel == null || saveModel.MainData == null || saveModel.MainData.Count == 0)
            {
                return response.Error("提交数据为空");
            }

            string dbTableName = string.IsNullOrEmpty(TableInfo.TableTrueName)
                ? TableInfo.TableName
                : TableInfo.TableTrueName;

            var tableColumns = TableColumns;
            if (tableColumns == null || tableColumns.Count == 0)
            {
                return response.Error("未找到表字段配置信息");
            }

            var keyColumn = tableColumns.FirstOrDefault(c => c.IsKey == 1);

            // 先设置创建人/创建时间默认值
            saveModel.MainData.SetCreateDefaultVal();

            //生成自增单据号
            IdentitySqlCode.CreateCode(saveModel.MainData, TableInfo.TableName, tableColumns, LeftQuote, RightQuote);

            // 设置逻辑删除字段0
            this.SetLogicDelDefault(saveModel.MainData, tableColumns)
                //审批字段默认值为0
                .SetAuditDefault(saveModel.MainData, tableColumns)
                // 按主键类型自动生成主键值
                .SetPrimaryKey(saveModel.MainData, keyColumn);

            // 根据字段配置校验主表必填、长度、类型
            string validMsg = this.ValidateColumns(saveModel.MainData, tableColumns);
            if (!string.IsNullOrEmpty(validMsg))
            {
                return response.Error(validMsg);
            }

            // 在主表校验通过后，提前校验所有明细表（忽略外键字段的必填）
            validMsg = this.ValidateAllDetails(saveModel, tableColumns, keyColumn);
            if (!string.IsNullOrEmpty(validMsg))
            {
                return response.Error(validMsg);
            }

            bool keyIsIdentity = this.IsIdentity(keyColumn.ColumnType);
            // 需要忽略的字段（修改人、修改时间等）
            var ignoreFields = this.GetModifyFieldsToIgnore();
            var fieldNames = new List<string>();
            var paramNames = new List<string>();
            var parameters = new List<SugarParameter>();

            // 只写入 TableColumns 中配置的真实表字段，且排除需要忽略的字段
            foreach (var col in tableColumns)
            {
                if (ignoreFields.Contains(col.ColumnName)) continue;
                if (!saveModel.MainData.TryGetValue(col.ColumnName, out object value)) continue;
                //自增不写入主键
                if (keyColumn.ColumnName == col.ColumnName && keyIsIdentity) continue;

                // 对于允许为 null 的字段，需要支持显式写入 NULL。
                // Dapper/Npgsql 不接受 System.DBNull 类型作为参数实体成员，这里统一将 DBNull 转成 null。
                if (value is DBNull)
                {
                    value = null;
                }
                value = CoerceDapperParam(value, col);
                fieldNames.Add($"{LeftQuote}{col.ColumnName}{RightQuote}");
                paramNames.Add("@" + col.ColumnName);

                var par = new SugarParameter("@" + col.ColumnName, value);
                var dbType = GenericDbValidationExtensions.EffectiveDapperDbType(value, col);
                if (dbType != null)
                {
                    par.DbType = (System.Data.DbType)dbType;
                }
                parameters.Add(par);
            }

            if (fieldNames.Count == 0) return response.Error("提交字段与表配置不匹配");

            string insertSql = $"INSERT INTO {LeftQuote}{dbTableName}{RightQuote} ({string.Join(",", fieldNames)}) VALUES ({string.Join(",", paramNames)})";

            try
            {
                await BeginTranAsync();
                if (keyIsIdentity)
                {
                    saveModel.MainData.Remove(keyColumn.ColumnName);
                    string identitySql = BuildIdentitySql(keyColumn);
                    object newId = await ExecuteInsertWithIdentityAsync(insertSql, identitySql, parameters, keyColumn);
                    saveModel.MainData[keyColumn.ColumnName] = long.Parse(newId.ToString());
                }
                else
                {
                    await ExcuteNonQueryAsync(insertSql, parameters);
                }

                // 主表插入成功后，处理一对多明细
                await InsertDetailsAsync(saveModel, keyColumn);

                Logger.OK(LoggerType.Add, saveModel.Serialize());
                response.OK(ResponseType.SaveSuccess);
                response.Data = saveModel.MainData;
                await CommitTranAsync();
                return response;
            }
            catch (Exception ex)
            {
                await RollbackTranAsync();
                throw new Exception($"add新建异常，table:{TableInfo.TableName},参数：{saveModel.Serialize()},异常信息：{ex.Message + ex.StackTrace}");
            }
        }

        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="saveModel"></param>
        /// <returns></returns>
        public virtual async Task<WebResponseContent> UpdateAsync(SaveModel saveModel)
        {
            var response = WebResponseContent.Instance;

            string dbTableName = string.IsNullOrEmpty(TableInfo.TableTrueName)
                ? TableInfo.TableName
                : TableInfo.TableTrueName;

            var keyColumn = TableColumns.FirstOrDefault(c => c.IsKey == 1);
            if (keyColumn == null)
            {
                return response.Error("未配置主键，不能编辑");
            }
            var columns = TableColumns.Where(x => x.ReferenceField == 0).ToList();

            // 1、saveModel.MainData 取出主键字段，如果没有值，提示缺少主键字段参数
            if (!saveModel.MainData.TryGetValue(keyColumn.ColumnName, out object keyVal) || string.IsNullOrEmpty(keyVal.ToString()))
            {
                return response.Error(ResponseType.KeyError);
            }
            saveModel.MainData.SetModifyDefaultVal();
            var data = saveModel.MainData
                 .Where(kv => !kv.Key.Equals(keyColumn.ColumnName, StringComparison.OrdinalIgnoreCase)
                              && columns.Any(c => c.ColumnName.Equals(kv.Key, StringComparison.OrdinalIgnoreCase)))
                 .ToDictionary(k => k.Key, v => v.Value);

            if (data.Count == 0)
            {
                return response.Error("没有需要更新的字段");
            }

            // 2、根据 TableColumns 中的字段校验 MainData 中【提交的字段】：
            //    IsNull、Maxlength、ColumnType（只针对 data 里存在的字段）
            var submitMainColumns = columns
                .Where(c => data.ContainsKey(c.ColumnName))
                .ToList();
            string validMsg = this.ValidateColumns(saveModel.MainData, submitMainColumns);
            if (!string.IsNullOrEmpty(validMsg))
            {
                return response.Error(validMsg);
            }

            // 3、明细表数据校验（规则同 Add，但编辑时只校验提交的字段，并忽略外键必填）
            validMsg = this.ValidateAllDetailsForUpdate(saveModel, keyColumn);
            if (!string.IsNullOrEmpty(validMsg))
            {
                return response.Error(validMsg);
            }

            try
            {
                var setList = new List<string>();
                var parameters = new List<SugarParameter>();
                // 5、6：忽略 ReferenceField=1 的字段已经在 columns 过滤；这里再忽略 ModifyMember 对应字段
                var ignoreFields = this.GetAddFieldsToIgnore();

                foreach (var kv in data)
                {
                    if (ignoreFields.Contains(kv.Key)) continue;

                    // 校验阶段可能已把 MainData 中的字符串转为 Guid 等；data 为校验前快照，优先取 MainData
                    if (!saveModel.MainData.TryGetValue(kv.Key, out object value))
                    {
                        value = kv.Value;
                    }
                    if (value is DBNull)
                    {
                        value = null;
                    }
                    var col = columns.FirstOrDefault(c => c.ColumnName.Equals(kv.Key, StringComparison.OrdinalIgnoreCase));
                    if (col == null) continue;

                    value = CoerceDapperParam(value, col);

                    string paramName = kv.Key;
                    setList.Add($"{LeftQuote}{kv.Key}{RightQuote} = @{paramName}");
                    //parameters.Add(new SugarParameter("@" + paramName, value));
                    var par = new SugarParameter("@" + col.ColumnName, value);
                    var dbType = GenericDbValidationExtensions.EffectiveDapperDbType(value, col);
                    if (dbType != null)
                    {
                        par.DbType = (System.Data.DbType)dbType;
                    }
                    parameters.Add(par);
                }

                object pkParam = CoerceDapperParam(keyVal, keyColumn) ?? DBNull.Value;
                var parPk = new SugarParameter("@pk", pkParam);
                var dbTypePk = GenericDbValidationExtensions.EffectiveDapperDbType(pkParam, keyColumn);
                if (dbTypePk != null)
                {
                    parPk.DbType = (System.Data.DbType)dbTypePk;
                }
                parameters.Add(parPk);

                await BeginTranAsync();

                string sql = $"UPDATE {LeftQuote}{dbTableName}{RightQuote} SET {string.Join(",", setList)} WHERE {LeftQuote}{keyColumn.ColumnName}{RightQuote} = @pk";

                int count = await ExcuteNonQueryAsync(sql, parameters);
                if (count <= 0)
                {
                    await RollbackTranAsync();
                    return response.Error("未找到更新的数据");
                }

                // 4、根据明细主键是否有值区分新建或编辑，分别执行插入或更新
                response = await UpdateDetailsAsync(saveModel, keyColumn, keyVal);
                if (!response.Status)
                {
                    await RollbackTranAsync();
                    return response;
                }
                await CommitTranAsync();
                response.OK(ResponseType.EidtSuccess);
            }
            catch (Exception ex)
            {
                await RollbackTranAsync();
                throw new Exception($"编辑异常，table:{TableInfo.TableName},参数：{saveModel.Serialize()},异常信息：{ex.Message + ex.StackTrace}");
            }
            return response;
        }
        public virtual async Task<WebResponseContent> DelAsync(List<object> keys, bool delDetail = true)
        {
            try
            {
                await BeginTranAsync();
                var res = await Del(keys, TableInfo.TableTrueName);
                if (delDetail && !string.IsNullOrEmpty(TableInfo.DetailName))
                {
                    var tables = TableInfo.DetailName.Split(",");
                    foreach (var table in tables)
                    {
                        res = await Del(keys, table);
                    }
                }
                await CommitTranAsync();
                return WebResponse.OK("删除成功".Translator());
            }
            catch (Exception ex)
            {
                await RollbackTranAsync();
                throw new Exception($"表删除异常，table:{TableInfo.TableName},异常信息：{ex.Message + ex.StackTrace}");
            }
        }
        private async Task<WebResponseContent> Del(List<object> keys, string table, TableColumnField keyColumn = null)
        {
            if (keys == null || keys.Count == 0) return WebResponse.OK("无数据");
            string dbTableName = table;

            keyColumn = keyColumn ?? GetTableColumns(table).FirstOrDefault(c => c.IsKey == 1);
            if (keyColumn == null) return WebResponse.Error("未配置主键，不能删除");
            keys = keys.Select(k => CoerceDapperParam(k, keyColumn)).ToList();
            var parameters = new List<SugarParameter>();
            string sql = $"DELETE FROM {LeftQuote}{dbTableName}{RightQuote} WHERE {LeftQuote}{keyColumn.ColumnName}{RightQuote} IN (@keys)";
            var parKeys = new SugarParameter("@keys", keys);
            var dbTypeKeys = keys != null && keys.Count > 0
                ? GenericDbValidationExtensions.EffectiveDapperDbType(keys[0], keyColumn)
                : keyColumn?.GetDbType();
            if (dbTypeKeys != null)
            {
                parKeys.DbType = (System.Data.DbType)dbTypeKeys;
            }
            parameters.Add(parKeys);
            int count = await ExcuteNonQueryAsync(sql, parameters);
            WebResponse.OK(ResponseType.DelSuccess);
            return WebResponse;
        }

        public virtual async Task<WebResponseContent> UploadAsync(List<IFormFile> files)
        {
            if (files == null || files.Count == 0) return WebResponse.Error("请上传文件");
            string date = DateTime.Now.ToString("yyyMMddHHmmsss");
            string filePath = $"Upload/Generic/{TableInfo.TableName}/{date}/";
            string fullPath = filePath.MapPath(true);
            if (!Directory.Exists(fullPath)) Directory.CreateDirectory(fullPath);
            for (int i = 0; i < files.Count; i++)
            {
                string fileName = Utilities.HttpContext.Current.Request("fileName");
                if (string.IsNullOrEmpty(fileName))
                {
                    fileName = files[i].FileName;
                }
                using var stream = new FileStream(fullPath + fileName, FileMode.Create);
                await files[i].CopyToAsync(stream);
            }
            return WebResponse.OK("文件上传成功".Translator(), filePath);
        }

        /// <summary>
        /// 下载导入Excel模板（基于当前表配置动态生成）
        /// </summary>
        /// <returns>Excel 文件字节数组</returns>
        public byte[] DownLoadTemplateAsync()
        {
            var ignoreFields = this.GetAddAndModifyFieldsToIgnore();
            var bytes = GenericExcelTemplateHelper.BuildTemplateBytes(TableInfo.ColumnCNName, TableColumns.Where(x => !ignoreFields.Contains(x.ColumnName)).ToList());
            return bytes;
        }
        /// <summary>
        /// 导入表数据Excel
        /// </summary>
        /// <param name="fileInput"></param>
        /// <returns></returns>
        public async Task<WebResponseContent> ImportAsync(List<IFormFile> fileInput)
        {
            if (fileInput == null || fileInput.Count == 0)
            {
                return WebResponse.Error("请选择上传的文件".Translator());
            }
            var ignoreFields = this.GetAddAndModifyFieldsToIgnore();
            //   TableColumns.Where(x => !ignoreFields.Contains(x.ColumnName)).ToList()
            var formFile = fileInput[0];
            List<Dictionary<string, object>> rows = null;
            using (var stream = formFile.OpenReadStream())
            {
                var resp = GenericExcelImportHelper.ReadRowsByCellOptions(TableInfo.TableName, stream, ignoreFields: ignoreFields);
                if (!resp.Status) return resp;

                rows = ((List<Dictionary<string, object>>)resp.Data).Where(x => x.Count > 0).ToList();
            }
            if (rows == null || rows.Count == 0)
            {
                return WebResponse.Error("未读取到导入数据".Translator());
            }
            var keyColumn = TableColumns.FirstOrDefault(c => c.IsKey == 1);
            string msg = this.ValidateDetailList(TableInfo.DetailName, rows, keyColumn);
            if (!string.IsNullOrEmpty(msg))
            {
                return WebResponse.Error(msg);
            }
            foreach (var row in rows)
            {
                IdentitySqlCode.CreateCode(row, TableInfo.TableName, TableColumns, LeftQuote, RightQuote);
            }
            //明细表导入 
            string mainId = Utilities.HttpContext.Current.Request.Query["id"];
            if (!string.IsNullOrEmpty(mainId))
            {
                string mainTable = Utilities.HttpContext.Current.Request.Query["mainTable"];
                var keyName = GetTableInfo(mainTable)?.MainKeyField ??
                    GetTableColumns(mainTable).Where(x => x.IsKey == 1).Select(s => s.ColumnName).FirstOrDefault();
                if (string.IsNullOrEmpty(keyName))
                {
                    return WebResponse.Error("未找到明细表的主表配置信息，请检查代码生成器是否有主表配置".Translator());
                }
                foreach (var item in rows)
                {
                    item[keyName] = mainId;
                }
            }
            await InsertDetailListAsync(TableInfo.TableName, rows, null, null);
            return WebResponse.OK("导入成功，共{$ts}条".TranslatorFormat(rows.Count), new { rows.Count });
        }

        /// <summary>
        /// 导出文件（参照 ServiceBase.Export，实现字典数据源转换、字段显示等）
        /// </summary>
        /// <param name="loadData"></param>
        /// <returns>Excel 文件字节数组</returns>
        public async Task<byte[]> ExportAsync(PageDataOptions loadData)
        {
            loadData.Export = true;
            var pageData = await GetPageDataAsync(loadData);
            var dictRows = new List<Dictionary<string, object>>();

            foreach (var item in pageData.rows)
            {
                var entry = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
                if (item is IDictionary<string, object> genericDict)
                {
                    foreach (var kv in genericDict)
                    {
                        entry[kv.Key] = kv.Value;
                    }
                    dictRows.Add(entry);
                }
            }
            var exportFields = loadData.Columns ?? [];
            var ignoreColumns = new List<string>();
            var bytes = GenericExcelExportHelper.BuildExportBytes(TableInfo.TableName, dictRows, loadData.Columns ?? [], ignoreColumns);
            return bytes;
        }

        public async Task<WebResponseContent> AuditAsync(object[] id, int? auditStatus, string auditReason)
        {
            string table = TableInfo.TableName;
            await Task.CompletedTask;
            return null;
        }

        /// </summary>
        protected virtual string BuildIdentitySql(TableColumnField keyColumn, bool batch = false)
        {
            return null;
        }
        protected virtual async Task<object> ExecuteInsertWithIdentityAsync(string insertSql, string identitySql, List<SugarParameter> parameters, TableColumnField keyColumn)
        {
            return await ExecuteScalarAsync($"{insertSql} {identitySql}", parameters);
        }
        /// <summary>
        /// 插入一对多明细数据
        /// </summary>
        protected virtual async Task InsertDetailsAsync(SaveModel saveModel, TableColumnField mainKeyColumn)
        {
            // 主键值
            object mainKeyValue = saveModel.MainData[mainKeyColumn.ColumnName];
            // 单明细表 List<Dictionary<string, object>>
            if (saveModel.DetailData != null && saveModel.DetailData.Count > 0)
            {
                await InsertDetailListAsync(TableInfo.DetailName, saveModel.DetailData, mainKeyColumn, mainKeyValue);
                saveModel.MainData[TableInfo.DetailName] = saveModel.DetailData;
            }

            // 新结构：多明细 List<DetailInfo>
            if (saveModel.Details != null && saveModel.Details.Count > 0)
            {
                foreach (var item in saveModel.Details)
                {
                    if (item?.Data == null || item.Data.Count == 0) continue;
                    await InsertDetailListAsync(item.Table, item.Data, mainKeyColumn, mainKeyValue);
                    saveModel.MainData[item.Table] = item.Data;
                }
            }
        }

        /// <summary>
        /// Update 时，根据明细主键是否有值区分新建/编辑，分别执行插入或更新
        /// </summary>
        private async Task<WebResponseContent> UpdateDetailsAsync(SaveModel saveModel, TableColumnField mainKeyColumn, object mainKeyValue)
        {
            // 旧结构：单明细表
            if (saveModel.DetailData != null && saveModel.DetailData.Count > 0 && !string.IsNullOrEmpty(TableInfo.DetailName))
            {
                await UpsertDetailListAsync(TableInfo.DetailName, saveModel.DetailData, mainKeyColumn, mainKeyValue);
                WebResponse = await Del(saveModel.DelKeys, TableInfo.DetailName);
                if (!WebResponse.Status)
                {
                    return WebResponse;
                }
            }

            // 新结构：多明细
            if (saveModel.Details != null && saveModel.Details.Count > 0)
            {
                foreach (var item in saveModel.Details)
                {
                    if (item?.Data == null || item.Data.Count == 0) continue;
                    await UpsertDetailListAsync(item.Table, item.Data, mainKeyColumn, mainKeyValue);
                    WebResponse = await Del(item.DelKeys, item.Table);
                    if (!WebResponse.Status)
                    {
                        return WebResponse;
                    }
                }
            }
            return WebResponse.OK();
        }

        /// <summary>
        /// 对指定明细表的数据做“有主键则更新，无主键则插入”的操作
        /// </summary>
        private async Task UpsertDetailListAsync(string detailTableName, List<Dictionary<string, object>> rows, TableColumnField mainKeyColumn, object mainKeyValue)
        {
            if (string.IsNullOrEmpty(detailTableName) || rows == null || rows.Count == 0) return;

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

            var detailKeyCol = detailColumns.FirstOrDefault(c => c.IsKey == 1);
            if (detailKeyCol == null) return;

            var foreignCol = detailColumns.FirstOrDefault(c =>
                c.ColumnName.Equals(mainKeyColumn.ColumnName, StringComparison.OrdinalIgnoreCase)
                && string.Equals(c.ColumnType, mainKeyColumn.ColumnType, StringComparison.OrdinalIgnoreCase));

            var detailTableInfo = TableColumnContext.TableInfo
                .FirstOrDefault(t => t.TableName == detailTableName);
            string detailDbTableName = string.IsNullOrEmpty(detailTableInfo?.TableTrueName)
                ? detailTableName
                : detailTableInfo.TableTrueName;

            var ignoreModifyFields = this.GetModifyFieldsToIgnore();
            var ignoreAddFields = this.GetAddFieldsToIgnore();
            bool keyIsIdentity = this.IsIdentity(detailKeyCol.ColumnType);

            // 新增明细行
            var addRows = new List<Dictionary<string, object>>();


            // 编辑明细行批量 UPDATE，控制单次参数数量
            int maxParams = DbParamsCount;
            var updateSqlBuilder = new StringBuilder();
            var updateParameters = new List<SugarParameter>();
            int currentUpdateParamCount = 0;
            int updateRowIndex = 0;

            async Task FlushUpdateAsync()
            {
                if (updateSqlBuilder.Length == 0) return;
                await ExcuteNonQueryAsync(updateSqlBuilder.ToString(), updateParameters);
                updateSqlBuilder.Clear();
                updateParameters = new List<SugarParameter>();
                currentUpdateParamCount = 0;
                updateRowIndex = 0;
            }

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

                bool hasDetailKey = row.TryGetValue(detailKeyCol.ColumnName, out object detailKeyVal)
                                    && detailKeyVal != null
                                    && !string.IsNullOrEmpty(detailKeyVal.ToString())
                                    && !new string[] { "0", Guid.Empty.ToString() }.Contains(detailKeyVal?.ToString());

                // 外键字段
                if (foreignCol != null)
                {
                    row[foreignCol.ColumnName] = mainKeyValue;
                }

                if (!hasDetailKey)
                {
                    // 新建明细：仅加入待新增列表，真正的插入逻辑统一走 InsertDetailListAsync（与 Add 保持一致）
                    addRows.Add(row);
                }
                else
                {
                    // 编辑明细：只更新提交的字段
                    row.SetModifyDefaultVal();

                    var setList = new List<string>();
                    var localParams = new List<(string ParamName, object Value, TableColumnField Col)>();

                    foreach (var kv in row)
                    {
                        if (kv.Key.Equals(detailKeyCol.ColumnName, StringComparison.OrdinalIgnoreCase)) continue;
                        if (ignoreAddFields.Contains(kv.Key)) continue;

                        var col = detailColumns.FirstOrDefault(c => c.ColumnName.Equals(kv.Key, StringComparison.OrdinalIgnoreCase));
                        if (col == null) continue;

                        object value = kv.Value;
                        if (value is DBNull) value = null;

                        string baseParamName = kv.Key;
                        string paramName = $"{baseParamName}_u{updateRowIndex}";
                        setList.Add($"{LeftQuote}{kv.Key}{RightQuote} = @{paramName}");
                        localParams.Add((paramName, value, col));
                    }

                    if (setList.Count == 0) continue;

                    // 预估本行 UPDATE 需要的参数数量（字段数 + 主键）
                    int needed = localParams.Count + 1;
                    if (currentUpdateParamCount + needed > maxParams)
                    {
                        await FlushUpdateAsync();
                    }

                    foreach (var (ParamName, Value, Col) in localParams)
                    {
                        object v = CoerceDapperParam(Value, Col);
                        var par = new SugarParameter("@" + ParamName, v);
                        var dbType = GenericDbValidationExtensions.EffectiveDapperDbType(v, Col);
                        if (dbType != null)
                        {
                            par.DbType = (System.Data.DbType)dbType;
                        }
                        updateParameters.Add(par);
                        currentUpdateParamCount++;
                    }

                    string pkParamName = $"{detailKeyCol.ColumnName}_pk{updateRowIndex}";
                    object detailPk = CoerceDapperParam(detailKeyVal, detailKeyCol);
                    var parPk = new SugarParameter("@" + pkParamName, detailPk);
                    var dbTypePk = GenericDbValidationExtensions.EffectiveDapperDbType(detailPk, detailKeyCol);
                    if (dbTypePk != null)
                    {
                        parPk.DbType = (System.Data.DbType)dbTypePk;
                    }
                    updateParameters.Add(parPk);
                    currentUpdateParamCount++;

                    string updateSql =
                        $"UPDATE {LeftQuote}{detailDbTableName}{RightQuote} SET {string.Join(",", setList)} WHERE {LeftQuote}{detailKeyCol.ColumnName}{RightQuote} = @{pkParamName};";
                    updateSqlBuilder.AppendLine(updateSql);

                    updateRowIndex++;
                }
            }

            if (addRows.Count > 0)
            {
                await InsertDetailListAsync(detailTableName, addRows, mainKeyColumn, mainKeyValue);
            }

            await FlushUpdateAsync();
        }


        /// <summary>
        /// 按规则插入某一张明细表的数据列表（批量 SQL，控制单次参数数量）
        /// </summary>
        private async Task InsertDetailListAsync(string detailTableName, List<Dictionary<string, object>> rows, TableColumnField mainKeyColumn, object mainKeyValue)
        {
            if (string.IsNullOrEmpty(detailTableName) || rows == null || rows.Count == 0) return;

            // 获取明细表字段配置
            var detailColumns = TableColumnContext.Data
                .Where(x => x.TableName == detailTableName && x.ReferenceField == 0)
                .ToList();
            if (detailColumns == null || detailColumns.Count == 0) return;

            // 明细表主键
            var detailKeyCol = detailColumns.FirstOrDefault(c => c.IsKey == 1);
            if (detailKeyCol == null) return;

            // 外键字段：与主表主键同名、同类型
            TableColumnField foreignCol = null;

            if (mainKeyColumn != null)
            {
                foreignCol = detailColumns.FirstOrDefault(c => c.ColumnName == mainKeyColumn.ColumnName);
                if (foreignCol == null) return;
            }
            // 明细表真实库表名
            var detailTableInfo = TableColumnContext.TableInfo
                .FirstOrDefault(t => t.TableName == detailTableName);
            string detailDbTableName = string.IsNullOrEmpty(detailTableInfo?.TableTrueName)
                ? detailTableName
                : detailTableInfo.TableTrueName;

            // 忽略字段（修改人/修改时间）
            var ignoreFields = this.GetModifyFieldsToIgnore();
            bool keyIsIdentity = this.IsIdentity(detailKeyCol.ColumnType);

            // 参与插入的列：自增时排除主键；非自增时包含主键（由代码生成）
            var insertColumns = detailColumns
                .Where(col =>
                {
                    if (ignoreFields.Contains(col.ColumnName)) return false;
                    if (keyIsIdentity && col.ColumnName.Equals(detailKeyCol.ColumnName, StringComparison.OrdinalIgnoreCase))
                        return false;
                    return true;
                })
                .ToList();
            if (insertColumns.Count == 0) return;

            string columnList = string.Join(",", insertColumns.Select(c => $"{LeftQuote}{c.ColumnName}{RightQuote}"));
            // batch=true 时：自增返回 RETURNING/OUTPUT/;SELECT 等片段，非自增返回 null
            string identitySqlPart = keyIsIdentity ? BuildIdentitySql(detailKeyCol, true) : string.Empty;
            bool needReturnIds = !string.IsNullOrWhiteSpace(identitySqlPart);

            int maxParams = DbParamsCount;
            var parameters = new List<SugarParameter>();
            var batchRows = new List<Dictionary<string, object>>();
            var batchRowIndexes = new List<int>();
            int currentParamCount = 0;
            int globalRowIndex = 0;

            async Task FlushAsync()
            {
                if (batchRows.Count == 0) return;

                var valuesClauses = new List<string>();
                for (int i = 0; i < batchRows.Count; i++)
                {
                    int rowIdx = batchRowIndexes[i];
                    var valueParams = insertColumns.Select(c => "@" + $"{c.ColumnName}_{rowIdx}").ToList();
                    valuesClauses.Add($"({string.Join(",", valueParams)})");
                }

                string sql;
                //sqlserver批量返回语法OUTPUT
                if (needReturnIds && identitySqlPart.TrimStart().StartsWith("OUTPUT", StringComparison.OrdinalIgnoreCase))
                    sql = $"INSERT INTO {LeftQuote}{detailDbTableName}{RightQuote} ({columnList}) {identitySqlPart} VALUES {string.Join(",", valuesClauses)};";
                else
                    sql = $"INSERT INTO {LeftQuote}{detailDbTableName}{RightQuote} ({columnList}) VALUES {string.Join(",", valuesClauses)}{identitySqlPart};";

                if (needReturnIds)
                {
                    var ids = (await QueryListAsync(sql, parameters))
                        .Serialize()
                        .DeserializeObject<List<Dictionary<string, long>>>()
                        .SelectMany(x => x.Values)
                        .ToList();
                    if (this is GenericMySqlProvider && ids != null && ids.Count == 1 && batchRows.Count > 0)
                    {
                        long firstId = Convert.ToInt64(ids[0]);
                        for (int i = 0; i < batchRows.Count; i++)
                            batchRows[i][detailKeyCol.ColumnName] = firstId + i;
                    }
                    else
                    {
                        for (int i = 0; i < batchRows.Count && i < ids.Count; i++)
                            batchRows[i][detailKeyCol.ColumnName] = ids[i];
                    }
                }
                else
                {
                    await ExcuteNonQueryAsync(sql, parameters);
                }

                parameters = new List<SugarParameter>();
                batchRows.Clear();
                batchRowIndexes.Clear();
                currentParamCount = 0;
            }

            foreach (var row in rows)
            {
                if (row == null) continue;
                if (foreignCol != null)
                {
                    row[foreignCol.ColumnName] = mainKeyValue;
                }
                row.SetCreateDefaultVal();
                this.SetLogicDelDefault(row, detailColumns).SetAuditDefault(row, detailColumns);
                if (!keyIsIdentity)
                    this.SetPrimaryKey(row, detailKeyCol);

                int needed = insertColumns.Count;
                if (currentParamCount + needed > maxParams)
                    await FlushAsync();

                foreach (var col in insertColumns)
                {
                    row.TryGetValue(col.ColumnName, out object value);
                    if (value is DBNull) value = null;
                    value = CoerceDapperParam(value, col);
                    var par = new SugarParameter("@" + $"{col.ColumnName}_{globalRowIndex}", value);
                    var dbType = GenericDbValidationExtensions.EffectiveDapperDbType(value, col);
                    if (dbType != null)
                    {
                        par.DbType = (System.Data.DbType)dbType;
                    }
                    parameters.Add(par);
                    currentParamCount++;
                }

                batchRows.Add(row);
                batchRowIndexes.Add(globalRowIndex);
                globalRowIndex++;
            }

            await FlushAsync();
        }

        /// <summary>
        /// SqlServer / PostgreSQL：Guid 列须把 string、JsonElement 转为 Guid。MySql 等保持原样。
        /// PostgreSQL：元数据为 varchar(Text) 且值为 Guid 时转为字符串以绑 Text。
        /// </summary>
        private object CoerceDapperParam(object value, TableColumnField col)
        {
            if (col == null) return value;
            if (col.ColumnType == "long")
            {
                return Convert.ToInt64(value);
            }
            string db = DbRelativeCache.GetDbType(TableInfo.DBServer);

            if (db == "PgSql" && !GenericDbValidationExtensions.IsGuidColumn(col) && col.GetDbType() ==System.Data.DbType.String && value is Guid pgGuid)
                return pgGuid.ToString();

            if (db != "MsSql" && db != "PgSql") return value;
            if (!GenericDbValidationExtensions.IsGuidColumn(col)) return value;
            if (value is Guid) return value;
            if (value == null || value is DBNull) return null;
            string s;
            if (value is string str) s = str;
            else if (value is JsonElement je && je.ValueKind == JsonValueKind.String) s = je.GetString();
            else s = value?.ToString();
            if (string.IsNullOrWhiteSpace(s)) return null;
            return Guid.TryParse(s, out var g) ? (object)g : value;
        }
    }
}