AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Infrastructure/AidopS0ExcelColumnMigration.cs

using System.Data;
using System.Diagnostics;
using System.Text;
using Admin.NET.Plugin.AiDOP.Entity.S0.Sales;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.Infrastructure;

/// <summary>
/// S0 Excel 字段口径迁移：将现有旧列名重命名为 Excel 字段名。
/// 仅处理文档中已确认映射的字段，不自动补 Excel 独有字段。
/// </summary>
public static class AidopS0ExcelColumnMigration
{
    private sealed record ColumnRename(string TableName, string OldColumnName, string NewColumnName);
    private sealed record PrimaryKeyRepair(string TableName, string PkColumnName, bool IsIdentity, string PkComment, params string[] ShadowColumns);
    private sealed record MySqlColumnMeta(
        string ColumnName,
        string ColumnType,
        string IsNullable,
        string? ColumnDefault,
        string? Extra,
        string? ColumnComment,
        string? CollationName,
        string? CharacterSetName);

    private static readonly ColumnRename[] Renames =
    [
        new("CostCtrMaster", "cost_ctr", "CostCtr"),
        new("CostCtrMaster", "create_time", "CreateTime"),
        new("CostCtrMaster", "create_user", "CreateUser"),
        new("CostCtrMaster", "descr", "Descr"),
        new("CostCtrMaster", "domain_code", "Domain"),
        new("CostCtrMaster", "eff_time", "EffTime"),
        new("CostCtrMaster", "rec_id", "RecID"),
        new("CostCtrMaster", "ufld1", "Ufld1"),
        new("CostCtrMaster", "update_time", "UpdateTime"),
        new("CostCtrMaster", "update_user", "UpdateUser"),
        new("CustMaster", "create_time", "CreateTime"),
        new("CustMaster", "create_user", "CreateUser"),
        new("CustMaster", "credit_rating", "CreditRating"),
        new("CustMaster", "curr", "Curr"),
        new("CustMaster", "cust", "Cust"),
        new("CustMaster", "domain_code", "Domain"),
        new("CustMaster", "is_active", "IsActive"),
        new("CustMaster", "is_confirm", "IsConfirm"),
        new("CustMaster", "rec_id", "RecID"),
        new("CustMaster", "remarks", "Remarks"),
        new("CustMaster", "ship_to", "ShipTo"),
        new("CustMaster", "slspsn1", "Slspsn1"),
        new("CustMaster", "slspsn2", "Slspsn2"),
        new("CustMaster", "sort_name", "SortName"),
        new("CustMaster", "tax_class", "TaxClass"),
        new("CustMaster", "tax_in", "TaxIn"),
        new("CustMaster", "update_time", "UpdateTime"),
        new("CustMaster", "update_user", "UpdateUser"),
        new("DepartmentMaster", "create_time", "CreateTime"),
        new("DepartmentMaster", "create_user", "CreateUser"),
        new("DepartmentMaster", "department", "Department"),
        new("DepartmentMaster", "descr", "Descr"),
        new("DepartmentMaster", "domain_code", "Domain"),
        new("DepartmentMaster", "is_active", "IsActive"),
        new("DepartmentMaster", "rec_id", "RecID"),
        new("DepartmentMaster", "update_time", "UpdateTime"),
        new("DepartmentMaster", "update_user", "UpdateUser"),
        new("DevMonitor", "code_descr", "CodeDescr"),
        new("DevMonitor", "code_type", "CodeType"),
        new("DevMonitor", "create_time", "CreateTime"),
        new("DevMonitor", "create_user", "CreateUser"),
        new("DevMonitor", "device_descr", "DeviceDescr"),
        new("DevMonitor", "device_number", "DeviceNumber"),
        new("DevMonitor", "domain", "Domain"),
        new("DevMonitor", "id", "RecID"),
        new("DevMonitor", "low_limit", "LowLimit"),
        new("DevMonitor", "message_code", "MessageCode"),
        new("DevMonitor", "std_value", "StdValue"),
        new("DevMonitor", "top_limit", "TopLimit"),
        new("DevMonitor", "ufld1", "Ufld1"),
        new("DevMonitor", "um", "UM"),
        new("DevMonitor", "update_time", "UpdateTime"),
        new("DevMonitor", "update_user", "UpdateUser"),
        new("DevMonitor", "value_type", "ValueType"),
        new("EmpSkills", "create_time", "CreateTime"),
        new("EmpSkills", "create_user", "CreateUser"),
        new("EmpSkills", "domain", "Domain"),
        new("EmpSkills", "efficiency_coefficient", "EfficiencyCoefficient"),
        new("EmpSkills", "employee", "Employee"),
        new("EmpSkills", "employee_id", "EmployeeID"),
        new("EmpSkills", "employee_name", "EmployeeName"),
        new("EmpSkills", "id", "RecID"),
        new("EmpSkills", "site", "Site"),
        new("EmpSkills", "skill_description", "SkillDescription"),
        new("EmpSkills", "skill_level", "SkillLevel"),
        new("EmpSkills", "skill_no", "SkillNo"),
        new("EmpSkills", "update_time", "UpdateTime"),
        new("EmpSkills", "update_user", "UpdateUser"),
        new("EmpWorkDutyMaster", "create_time", "CreateTime"),
        new("EmpWorkDutyMaster", "create_user", "CreateUser"),
        new("EmpWorkDutyMaster", "domain_code", "Domain"),
        new("EmpWorkDutyMaster", "duty", "Duty"),
        new("EmpWorkDutyMaster", "emp_type", "EmpType"),
        new("EmpWorkDutyMaster", "employee", "Employee"),
        new("EmpWorkDutyMaster", "item_num1", "ItemNum1"),
        new("EmpWorkDutyMaster", "item_num2", "ItemNum2"),
        new("EmpWorkDutyMaster", "location", "Location"),
        new("EmpWorkDutyMaster", "prod_line", "ProdLine"),
        new("EmpWorkDutyMaster", "rec_id", "RecID"),
        new("EmpWorkDutyMaster", "ufld2", "Ufld2"),
        new("EmpWorkDutyMaster", "update_time", "UpdateTime"),
        new("EmpWorkDutyMaster", "update_user", "UpdateUser"),
        new("EmployeeMaster", "birth_date", "BirthDate"),
        new("EmployeeMaster", "car_id", "CarId"),
        new("EmployeeMaster", "create_time", "CreateTime"),
        new("EmployeeMaster", "create_user", "CreateUser"),
        new("EmployeeMaster", "date_employed", "DateEmployed"),
        new("EmployeeMaster", "date_terminated", "DateTerminated"),
        new("EmployeeMaster", "default_work_location", "DefaultWorkLocation"),
        new("EmployeeMaster", "department", "Department"),
        new("EmployeeMaster", "domain_code", "Domain"),
        new("EmployeeMaster", "email", "Email"),
        new("EmployeeMaster", "employee", "Employee"),
        new("EmployeeMaster", "employment_status", "EmploymentStatus"),
        new("EmployeeMaster", "is_active", "IsActive"),
        new("EmployeeMaster", "job_title", "JobTitle"),
        new("EmployeeMaster", "marital_status", "MaritalStatus"),
        new("EmployeeMaster", "name", "Name"),
        new("EmployeeMaster", "phone", "Phone"),
        new("EmployeeMaster", "rec_id", "RecID"),
        new("EmployeeMaster", "sex", "Sex"),
        new("EmployeeMaster", "update_time", "UpdateTime"),
        new("EmployeeMaster", "update_user", "UpdateUser"),
        new("EmployeeMaster", "work_ctr", "WorkCtr"),
        new("ItemMaster", "allocate_single_lot", "AllocateSingleLot"),
        new("ItemMaster", "auto_lot_nums", "AutoLotNums"),
        new("ItemMaster", "commodity_code", "CommodityCode"),
        new("ItemMaster", "create_time", "CreateTime"),
        new("ItemMaster", "create_user", "CreateUser"),
        new("ItemMaster", "days_between_pm", "DaysBetweenPM"),
        new("ItemMaster", "default_shelf", "DefaultShelf"),
        new("ItemMaster", "descr", "Descr"),
        new("ItemMaster", "descr1", "Descr1"),
        new("ItemMaster", "domain_code", "Domain"),
        new("ItemMaster", "drawing", "Drawing"),
        new("ItemMaster", "emt_type", "EMTType"),
        new("ItemMaster", "ins_lt", "InsLT"),
        new("ItemMaster", "inspect", "Inspect"),
        new("ItemMaster", "install", "Install"),
        new("ItemMaster", "is_active", "IsActive"),
        new("ItemMaster", "is_confirm", "IsConfirm"),
        new("ItemMaster", "is_main_mas", "IsMainMas"),
        new("ItemMaster", "issue_policy", "IssuePolicy"),
        new("ItemMaster", "item_num", "ItemNum"),
        new("ItemMaster", "item_type", "ItemType"),
        new("ItemMaster", "key_item", "KeyItem"),
        new("ItemMaster", "length", "Length"),
        new("ItemMaster", "location", "Location"),
        new("ItemMaster", "location_type", "LocationType"),
        new("ItemMaster", "lot_serial_control", "LotSerialControl"),
        new("ItemMaster", "max_ord", "MaxOrd"),
        new("ItemMaster", "mfg_lt", "MfgLT"),
        new("ItemMaster", "mfgmttr", "MFGMTTR"),
        new("ItemMaster", "min_ord", "MinOrd"),
        new("ItemMaster", "min_ord_sales", "MinOrdSales"),
        new("ItemMaster", "net_weight", "NetWeight"),
        new("ItemMaster", "net_weight_um", "NetWeightUM"),
        new("ItemMaster", "ord_mult", "OrdMult"),
        new("ItemMaster", "owner_application", "OwnerApplication"),
        new("ItemMaster", "pkg_code", "PkgCode"),
        new("ItemMaster", "planner", "Planner"),
        new("ItemMaster", "pur_lt", "PurLT"),
        new("ItemMaster", "pur_mfg", "PurMfg"),
        new("ItemMaster", "rec_id", "RecID"),
        new("ItemMaster", "remark", "Remark"),
        new("ItemMaster", "rev", "Rev"),
        new("ItemMaster", "safety_stk", "SafetyStk"),
        new("ItemMaster", "size", "Size"),
        new("ItemMaster", "size_um", "SizeUM"),
        new("ItemMaster", "status", "Status"),
        new("ItemMaster", "stock_turn_over", "StockTurnOver"),
        new("ItemMaster", "trace_detail", "TraceDetail"),
        new("ItemMaster", "um", "UM"),
        new("ItemMaster", "update_time", "UpdateTime"),
        new("ItemMaster", "update_user", "UpdateUser"),
        new("ItemOpCondition", "code_type", "CodeType"),
        new("ItemOpCondition", "create_time", "CreateTime"),
        new("ItemOpCondition", "create_user", "CreateUser"),
        new("ItemOpCondition", "descr", "Descr"),
        new("ItemOpCondition", "domain", "Domain"),
        new("ItemOpCondition", "id", "RecID"),
        new("ItemOpCondition", "is_main", "IsMain"),
        new("ItemOpCondition", "item_code", "ItemCode"),
        new("ItemOpCondition", "item_num", "ItemNum"),
        new("ItemOpCondition", "line", "Line"),
        new("ItemOpCondition", "op", "Op"),
        new("ItemOpCondition", "typed", "Typed"),
        new("ItemOpCondition", "update_time", "UpdateTime"),
        new("ItemOpCondition", "update_user", "UpdateUser"),
        new("ItemPackMaster", "create_time", "CreateTime"),
        new("ItemPackMaster", "create_user", "CreateUser"),
        new("ItemPackMaster", "cust_item", "CustItem"),
        new("ItemPackMaster", "domain_code", "Domain"),
        new("ItemPackMaster", "high", "High"),
        new("ItemPackMaster", "is_active", "IsActive"),
        new("ItemPackMaster", "iss_specific", "IssSpecific"),
        new("ItemPackMaster", "item_num", "ItemNum"),
        new("ItemPackMaster", "length", "Length"),
        new("ItemPackMaster", "net_weight", "NetWeight"),
        new("ItemPackMaster", "packing_qty", "PackingQty"),
        new("ItemPackMaster", "packing_type", "PackingType"),
        new("ItemPackMaster", "rec_id", "RecID"),
        new("ItemPackMaster", "remark", "Remark"),
        new("ItemPackMaster", "small_packing_qty", "SmallPackingQty"),
        new("ItemPackMaster", "update_time", "UpdateTime"),
        new("ItemPackMaster", "update_user", "UpdateUser"),
        new("ItemPackMaster", "weight_um", "WeightUM"),
        new("ItemPackMaster", "width", "Width"),
        new("ItemSubstituteDetail", "create_time", "CreateTime"),
        new("ItemSubstituteDetail", "create_user", "CreateUser"),
        new("ItemSubstituteDetail", "domain", "Domain"),
        new("ItemSubstituteDetail", "id", "RecID"),
        new("ItemSubstituteDetail", "item_num", "ItemNum"),
        new("ItemSubstituteDetail", "parent_item", "ParentItem"),
        new("ItemSubstituteDetail", "remark", "Remark"),
        new("ItemSubstituteDetail", "substitute_item", "SubstituteItem"),
        new("ItemSubstituteDetail", "substitute_qty", "SubstituteQty"),
        new("ItemSubstituteDetail", "update_time", "UpdateTime"),
        new("ItemSubstituteDetail", "update_user", "UpdateUser"),
        new("LineMaster", "create_time", "CreateTime"),
        new("LineMaster", "create_user", "CreateUser"),
        new("LineMaster", "domain", "Domain"),
        new("LineMaster", "id", "RecID"),
        new("LineMaster", "is_active", "IsActive"),
        new("LineMaster", "line", "Line"),
        new("LineMaster", "line_category", "LineCategory"),
        new("LineMaster", "line_type", "LineType"),
        new("LineMaster", "location", "Location"),
        new("LineMaster", "mid_location", "MidLocation"),
        new("LineMaster", "picking_location", "PickingLocation"),
        new("LineMaster", "update_time", "UpdateTime"),
        new("LineMaster", "update_user", "UpdateUser"),
        new("LineMaster", "v_location", "VLocation"),
        new("LineMaster", "workshop", "Workshop"),
        new("LineSkillDetail", "create_time", "CreateTime"),
        new("LineSkillMaster", "create_time", "CreateTime"),
        new("LineSkillMaster", "create_user", "CreateUser"),
        new("LineSkillMaster", "domain", "Domain"),
        new("LineSkillMaster", "id", "RecID"),
        new("LineSkillMaster", "job_descr", "JOBDescr"),
        new("LineSkillMaster", "job_no", "JOBNo"),
        new("LineSkillMaster", "prod_line", "ProdLine"),
        new("LineSkillMaster", "update_time", "UpdateTime"),
        new("LineSkillMaster", "update_user", "UpdateUser"),
        new("PriorityCode", "create_time", "CreateTime"),
        new("PriorityCode", "create_user", "CreateUser"),
        new("PriorityCode", "descr", "Descr"),
        new("PriorityCode", "domain_code", "Domain"),
        new("PriorityCode", "is_active", "IsActive"),
        new("PriorityCode", "priority", "Priority"),
        new("PriorityCode", "rec_id", "RecID"),
        new("PriorityCode", "source_column", "SourceColumn"),
        new("PriorityCode", "source_id", "SourceID"),
        new("PriorityCode", "source_table", "SourceTable"),
        new("PriorityCode", "source_type", "SourceType"),
        new("PriorityCode", "update_time", "UpdateTime"),
        new("PriorityCode", "update_user", "UpdateUser"),
        new("PriorityCode", "value", "Value"),
        new("PriorityCode", "value_id", "ValueID"),
        new("PriorityCode", "value_type", "ValueType"),
        new("ProdLineDetail", "create_time", "CreateTime"),
        new("ProdLineDetail", "create_user", "CreateUser"),
        new("ProdLineDetail", "domain", "Domain"),
        new("ProdLineDetail", "id", "RecID"),
        new("ProdLineDetail", "is_active", "IsActive"),
        new("ProdLineDetail", "is_confirm", "IsConfirm"),
        new("ProdLineDetail", "line", "Line"),
        new("ProdLineDetail", "mold_type_code", "MoldTypeCode"),
        new("ProdLineDetail", "op", "Op"),
        new("ProdLineDetail", "part", "Part"),
        new("ProdLineDetail", "rate", "Rate"),
        new("ProdLineDetail", "setup_time", "SetupTime"),
        new("ProdLineDetail", "site", "Site"),
        new("ProdLineDetail", "skill_no", "SkillNo"),
        new("ProdLineDetail", "standard_staff_count", "StandardStaffCount"),
        new("ProdLineDetail", "start", "Start"),
        new("ProdLineDetail", "update_time", "UpdateTime"),
        new("ProdLineDetail", "update_user", "UpdateUser"),
        new("ProductStructureMaster", "end_eff", "EndEff"),
        new("ProductStructureMaster", "id", "RecID"),
        new("ProductStructureMaster", "qty", "Qty"),
        new("ProductStructureMaster", "qty_consumed", "QtyConsumed"),
        new("ProductStructureMaster", "refs", "Refs"),
        new("ProductStructureMaster", "remark", "Remark"),
        new("ProductStructureMaster", "scrap", "Scrap"),
        new("ProductStructureMaster", "start_eff", "StartEff"),
        new("ProductStructureMaster", "structure_type", "StructureType"),
        new("ProductStructureMaster", "um", "UM"),
        new("ProductStructureOp", "id", "RecID"),
        new("ProductStructureOp", "op", "Op"),
        new("RoutingOpDetail", "comment_index", "CommentIndex"),
        new("RoutingOpDetail", "create_user", "CreateUser"),
        new("RoutingOpDetail", "id", "RecID"),
        new("RoutingOpDetail", "is_confirm", "IsConfirm"),
        new("RoutingOpDetail", "u_deci1", "UDeci1"),
        new("RoutingOpDetail", "u_deci2", "UDeci2"),
        new("RoutingOpDetail", "u_deci3", "UDeci3"),
        new("RoutingOpDetail", "u_deci4", "UDeci4"),
        new("RoutingOpDetail", "u_deci5", "UDeci5"),
        new("RoutingOpDetail", "ufld1", "Ufld1"),
        new("RoutingOpDetail", "ufld2", "Ufld2"),
        new("RoutingOpDetail", "ufld3", "Ufld3"),
        new("RoutingOpDetail", "ufld4", "Ufld4"),
        new("RoutingOpDetail", "ufld5", "Ufld5"),
        new("RoutingOpDetail", "ufld6", "Ufld6"),
        new("RoutingOpDetail", "ufld7", "Ufld7"),
        new("RoutingOpDetail", "ufld8", "Ufld8"),
        new("RoutingOpDetail", "ufld9", "Ufld9"),
        new("RoutingOpDetail", "update_user", "UpdateUser"),
        new("SuppMaster", "ap_end_day1", "APEndDay1"),
        new("SuppMaster", "ap_end_day2", "APEndDay2"),
        new("SuppMaster", "ap_start_day1", "APStartDay1"),
        new("SuppMaster", "ap_start_day2", "APStartDay2"),
        new("SuppMaster", "cr_terms", "CrTerms"),
        new("SuppMaster", "create_time", "CreateTime"),
        new("SuppMaster", "create_user", "CreateUser"),
        new("SuppMaster", "curr", "Curr"),
        new("SuppMaster", "domain_code", "Domain"),
        new("SuppMaster", "is_active", "IsActive"),
        new("SuppMaster", "pur_contact", "PurContact"),
        new("SuppMaster", "rec_id", "RecID"),
        new("SuppMaster", "remark", "Remark"),
        new("SuppMaster", "sort_name", "SortName"),
        new("SuppMaster", "supp", "Supp"),
        new("SuppMaster", "tax_class", "TaxClass"),
        new("SuppMaster", "tax_in", "TaxIn"),
        new("SuppMaster", "type", "Type"),
        new("SuppMaster", "update_time", "UpdateTime"),
        new("SuppMaster", "update_user", "UpdateUser"),
        new("WorkCtrMaster", "create_time", "CreateTime"),
        new("WorkCtrMaster", "create_user", "CreateUser"),
        new("WorkCtrMaster", "department", "Department"),
        new("WorkCtrMaster", "descr", "Descr"),
        new("WorkCtrMaster", "domain", "Domain"),
        new("WorkCtrMaster", "id", "RecID"),
        new("WorkCtrMaster", "is_active", "IsActive"),
        new("WorkCtrMaster", "update_time", "UpdateTime"),
        new("WorkCtrMaster", "update_user", "UpdateUser"),
        new("WorkCtrMaster", "work_ctr", "WorkCtr"),
        new("ic_substitute", "id", "Id"),
        new("ic_substitute", "is_deleted", "IsDeleted"),
        new("srm_purchase", "id", "Id"),
        new("srm_purchase", "is_require_goods", "IsRequireGoods"),
    ];

    private static readonly PrimaryKeyRepair[] PrimaryKeyRepairs =
    [
        new("ItemSubstituteDetail", "RecID", true, "物料替代关系主键", "Id"),
        new("CostCtrMaster", "RecID", true, "主键"),
        new("CustMaster", "RecID", true, "主键"),
        new("DepartmentMaster", "RecID", true, "主键"),
        new("DevMonitor", "RecID", true, "主键", "Id"),
        new("EmpSkills", "RecID", true, "主键", "Id"),
        new("EmpWorkDutyMaster", "RecID", true, "主键"),
        new("EmployeeMaster", "RecID", true, "主键"),
        new("ItemMaster", "RecID", true, "主键"),
        new("ItemOpCondition", "RecID", true, "物料工序生产要素主键", "Id"),
        new("ItemPackMaster", "RecID", true, "主键"),
        new("LineMaster", "RecID", true, "生产线主键", "Id"),
        new("LineSkillMaster", "RecID", true, "产线岗位主键", "Id"),
        new("PriorityCode", "RecID", true, "主键"),
        new("ProdLineDetail", "RecID", true, "生产线物料主键"),
        new("ProductStructureMaster", "RecID", true, "主键", "Id"),
        new("ProductStructureOp", "RecID", true, "主键", "Id"),
        new("RoutingOpDetail", "RecID", true, "主键", "Id"),
        new("SuppMaster", "RecID", true, "主键"),
        new("WorkCtrMaster", "RecID", true, "工作中心主键"),
        new("ic_substitute", "Id", false, "主键"),
        new("srm_purchase", "Id", true, "货源清单主键")
    ];

    public static void Migrate(ISqlSugarClient db)
    {
        var databaseName = db.Ado.SqlQuerySingle<string>("SELECT DATABASE();");
        foreach (var group in Renames.GroupBy(x => x.TableName))
        {
            var tableName = group.Key;
            var tables = db.DbMaintenance.GetTableInfoList(false);
            if (!tables.Any(t => t.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase)))
                continue;

            try
            {
                var columns = db.DbMaintenance.GetColumnInfosByTableName(tableName, false) ?? [];
                foreach (var rename in group)
                {
                    var oldColumn = columns.FirstOrDefault(c => c.DbColumnName.Equals(rename.OldColumnName, StringComparison.Ordinal));
                    var newColumn = columns.FirstOrDefault(c => c.DbColumnName.Equals(rename.NewColumnName, StringComparison.Ordinal));
                    if (oldColumn == null)
                        continue;

                    if (newColumn != null)
                    {
                        if (oldColumn.IsPrimarykey)
                        {
                            db.Ado.ExecuteCommand($"ALTER TABLE `{tableName}` DROP COLUMN `{rename.NewColumnName}`");
                            Trace.TraceInformation($"Ai-DOP S0 drop duplicate new PK shadow column: {tableName}.{rename.NewColumnName}");

                            RenameColumnMySql(db, databaseName, tableName, rename.OldColumnName, rename.NewColumnName);
                            Trace.TraceInformation($"Ai-DOP S0 rename PK column: {tableName}.{rename.OldColumnName} -> {rename.NewColumnName}");
                        }
                        else
                        {
                            db.Ado.ExecuteCommand($"ALTER TABLE `{tableName}` DROP COLUMN `{rename.OldColumnName}`");
                            Trace.TraceInformation($"Ai-DOP S0 drop duplicate old column: {tableName}.{rename.OldColumnName}");
                        }

                        columns = db.DbMaintenance.GetColumnInfosByTableName(tableName, false) ?? [];
                        continue;
                    }

                    RenameColumnMySql(db, databaseName, tableName, rename.OldColumnName, rename.NewColumnName);
                    Trace.TraceInformation($"Ai-DOP S0 rename column: {tableName}.{rename.OldColumnName} -> {rename.NewColumnName}");

                    columns = db.DbMaintenance.GetColumnInfosByTableName(tableName, false) ?? [];
                }
            }
            catch (Exception ex)
            {
                Trace.TraceError($"Ai-DOP S0 rename columns failed for {tableName}: {ex}");
                throw;
            }
        }

        RepairPrimaryKeys(db, databaseName);
        EnsureS0SalesAddonColumnsAndSeeds(db, databaseName);
    }

    /// <summary>
    /// 产销扩展列（客户全称、优先级业务维度、订单评审订单类型）及示范合同评审五阶段种子。
    /// </summary>
    private static void EnsureS0SalesAddonColumnsAndSeeds(ISqlSugarClient db, string databaseName)
    {
        EnsureColumn(db, databaseName, "CustMaster", "CustFullName",
            "varchar(500) NULL COMMENT '客户全称/正式名称'");
        EnsureColumn(db, databaseName, "StdOpMaster", "std_op_code",
            "varchar(100) NULL COMMENT '标准工序编码'");
        EnsureColumn(db, databaseName, "ItemSubstituteDetail", "SubstituteType",
            "varchar(20) NULL COMMENT '替代关系类型（替代/取代）'");
        EnsureColumn(db, databaseName, "SuppMaster", "address",
            "varchar(500) NULL COMMENT '地址'");
        EnsureColumn(db, databaseName, "SuppMaster", "contact",
            "varchar(200) NULL COMMENT '联系人'");
        EnsureColumn(db, databaseName, "SuppMaster", "position",
            "varchar(200) NULL COMMENT '职务'");
        EnsureColumn(db, databaseName, "SuppMaster", "contact_info",
            "varchar(200) NULL COMMENT '联系方式'");
        EnsureColumn(db, databaseName, "SuppMaster", "certification_level",
            "varchar(100) NULL COMMENT '认证等级'");
        EnsureColumn(db, databaseName, "EmpWorkDutyMaster", "ItemNum",
            "varchar(100) NULL COMMENT '单物料编码'");
        EnsureColumn(db, databaseName, "qms_sampscheme", "FFIXEDRATE",
            "decimal(10,4) NULL COMMENT '固定抽样比例(%)'");

        EnsureColumn(db, databaseName, "qms_lymjbmd", "wlbm",
            "varchar(255) NULL COMMENT '物料编码'");
        EnsureColumn(db, databaseName, "qms_lymjbmd", "wlmc",
            "varchar(255) NULL COMMENT '物料名称'");
        EnsureColumn(db, databaseName, "qms_lymjbmd", "dim_type",
            "varchar(50) NULL COMMENT '维度类型（supplier/material/material_supplier）'");

        EnsureColumn(db, databaseName, "qms_inspectioninstru", "gauge_category",
            "varchar(255) NULL COMMENT '计量器具分类'");
        EnsureColumn(db, databaseName, "qms_inspectioninstru", "calibration_cycle_days",
            "int NULL COMMENT '校准周期(天)'");
        EnsureColumn(db, databaseName, "qms_inspectioninstru", "next_calibration_date",
            "varchar(50) NULL COMMENT '下次校准日期'");

        EnsureColumn(db, databaseName, "PriorityCode", "ScopeCustClass",
            "varchar(100) NULL COMMENT '业务维度：客户类型/级别（可空=不限）'");
        EnsureColumn(db, databaseName, "PriorityCode", "ScopeOrderType",
            "varchar(100) NULL COMMENT '业务维度：订单类型（可空=不限）'");
        EnsureColumn(db, databaseName, "PriorityCode", "ScopeDueDaysMax",
            "int NULL COMMENT '业务维度：承诺交期上限天数（可空=不限）'");

        var hasOrderReviewTable = db.DbMaintenance.GetTableInfoList(false)
            .Any(t => t.Name.Equals("S0OrderReviewCycle", StringComparison.OrdinalIgnoreCase));
        if (hasOrderReviewTable)
        {
            EnsureColumn(db, databaseName, "S0OrderReviewCycle", "order_type",
                "varchar(100) NOT NULL DEFAULT '' COMMENT '订单类型；空字符串=默认/不限'");

            db.Ado.ExecuteCommand("UPDATE `S0OrderReviewCycle` SET `order_type` = '' WHERE `order_type` IS NULL");

            TryDropIndex(db, databaseName, "S0OrderReviewCycle", "uk_S0OrderReviewCycle_factory");
            EnsureUniqueIndex(db, databaseName, "S0OrderReviewCycle", "uk_S0OrderReviewCycle_factory_ordertype",
                "`factory_ref_id`, `order_type`");
        }

        EnsureContractReviewCycleDemoStages(db);
    }

    private static void EnsureColumn(ISqlSugarClient db, string databaseName, string tableName, string columnName, string columnDefinitionTail)
    {
        var tables = db.DbMaintenance.GetTableInfoList(false);
        if (!tables.Any(t => t.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase)))
            return;

        var dbLit = databaseName.Replace("'", "''", StringComparison.Ordinal);
        var tblLit = tableName.Replace("'", "''", StringComparison.Ordinal);
        var colLit = columnName.Replace("'", "''", StringComparison.Ordinal);
        var count = db.Ado.SqlQuerySingle<int>(
            $"""
             SELECT COUNT(*) FROM information_schema.COLUMNS
             WHERE TABLE_SCHEMA = '{dbLit}' AND TABLE_NAME = '{tblLit}' AND COLUMN_NAME = '{colLit}'
             """);

        if (count > 0)
            return;

        db.Ado.ExecuteCommand($"ALTER TABLE `{tableName}` ADD COLUMN `{columnName}` {columnDefinitionTail}");
        Trace.TraceInformation($"Ai-DOP S0 add column: {tableName}.{columnName}");
    }

    private static void TryDropIndex(ISqlSugarClient db, string databaseName, string tableName, string indexName)
    {
        var tables = db.DbMaintenance.GetTableInfoList(false);
        if (!tables.Any(t => t.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase)))
            return;

        var dbLit = databaseName.Replace("'", "''", StringComparison.Ordinal);
        var tblLit = tableName.Replace("'", "''", StringComparison.Ordinal);
        var idxLit = indexName.Replace("'", "''", StringComparison.Ordinal);
        var count = db.Ado.SqlQuerySingle<int>(
            $"""
             SELECT COUNT(*) FROM information_schema.STATISTICS
             WHERE TABLE_SCHEMA = '{dbLit}' AND TABLE_NAME = '{tblLit}' AND INDEX_NAME = '{idxLit}'
             """);

        if (count == 0)
            return;

        db.Ado.ExecuteCommand($"ALTER TABLE `{tableName}` DROP INDEX `{indexName}`");
        Trace.TraceInformation($"Ai-DOP S0 drop index: {tableName}.{indexName}");
    }

    private static void EnsureUniqueIndex(ISqlSugarClient db, string databaseName, string tableName, string indexName, string columnListSql)
    {
        var tables = db.DbMaintenance.GetTableInfoList(false);
        if (!tables.Any(t => t.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase)))
            return;

        var dbLit = databaseName.Replace("'", "''", StringComparison.Ordinal);
        var tblLit = tableName.Replace("'", "''", StringComparison.Ordinal);
        var idxLit = indexName.Replace("'", "''", StringComparison.Ordinal);
        var count = db.Ado.SqlQuerySingle<int>(
            $"""
             SELECT COUNT(*) FROM information_schema.STATISTICS
             WHERE TABLE_SCHEMA = '{dbLit}' AND TABLE_NAME = '{tblLit}' AND INDEX_NAME = '{idxLit}'
             """);

        if (count > 0)
            return;

        db.Ado.ExecuteCommand($"ALTER TABLE `{tableName}` ADD UNIQUE INDEX `{indexName}` ({columnListSql})");
        Trace.TraceInformation($"Ai-DOP S0 add unique index: {tableName}.{indexName}");
    }

    private static void EnsureContractReviewCycleDemoStages(ISqlSugarClient db)
    {
        const long s0CompanyId = 1329900200001L;
        const long s0Factory1Id = 1329900200002L;
        const long s0Factory2Id = 1329900200003L;

        var tables = db.DbMaintenance.GetTableInfoList(false);
        if (!tables.Any(t => t.Name.Equals("S0ContractReviewCycle", StringComparison.OrdinalIgnoreCase)))
            return;

        var stages = new (string Code, string Name, int Order)[]
        {
            ("CR1", "意见评审", 1),
            ("CR2", "意见反馈", 2),
            ("CR3", "二次评审", 3),
            ("CR4", "领导意见", 4),
            ("CR5", "合同盖章", 5),
        };

        foreach (var factoryId in new[] { s0Factory1Id, s0Factory2Id })
        {
            var hasAny = db.Queryable<AdoS0ContractReviewCycle>()
                .Where(x => x.FactoryRefId == factoryId)
                .Any();
            if (hasAny)
                continue;

            var now = DateTime.Now;
            foreach (var s in stages)
            {
                db.Insertable(new AdoS0ContractReviewCycle
                {
                    CompanyRefId = s0CompanyId,
                    FactoryRefId = factoryId,
                    DomainCode = null,
                    StageCode = s.Code,
                    StageName = s.Name,
                    StdHours = 0,
                    OrderNo = s.Order,
                    IsActive = true,
                    CreateTime = now,
                }).ExecuteCommand();
            }

            Trace.TraceInformation($"Ai-DOP S0 seeded contract review stages for factory {factoryId}");
        }
    }

    private static void RenameColumnMySql(ISqlSugarClient db, string databaseName, string tableName, string oldColumnName, string newColumnName)
    {
        var meta = GetMySqlColumnMeta(db, databaseName, tableName, oldColumnName);
        if (meta == null)
            return;

        var ddl = $"ALTER TABLE `{tableName}` CHANGE COLUMN `{oldColumnName}` `{newColumnName}` {BuildColumnDefinition(meta)}";
        db.Ado.ExecuteCommand(ddl);
    }

    private static void RepairPrimaryKeys(ISqlSugarClient db, string databaseName)
    {
        var tableList = db.DbMaintenance.GetTableInfoList(false);
        foreach (var repair in PrimaryKeyRepairs)
        {
            var tableName = tableList.FirstOrDefault(t => t.Name.Equals(repair.TableName, StringComparison.OrdinalIgnoreCase))?.Name;
            if (string.IsNullOrEmpty(tableName))
                continue;

            var tableColumns = db.DbMaintenance.GetColumnInfosByTableName(tableName, false) ?? [];
            if (tableColumns.Count == 0)
                continue;

            foreach (var shadowColumn in repair.ShadowColumns)
            {
                if (tableColumns.Any(c => c.DbColumnName.Equals(shadowColumn, StringComparison.Ordinal)))
                {
                    db.Ado.ExecuteCommand($"ALTER TABLE `{tableName}` DROP COLUMN `{shadowColumn}`");
                }
            }

            SugarParameter[] pkParameters =
            [
                new SugarParameter("@db", databaseName),
                new SugarParameter("@table", tableName)
            ];
            var pkTable = db.Ado.GetDataTable(
                """
                SELECT COLUMN_NAME
                FROM information_schema.KEY_COLUMN_USAGE
                WHERE TABLE_SCHEMA = @db
                  AND TABLE_NAME = @table
                  AND CONSTRAINT_NAME = 'PRIMARY'
                ORDER BY ORDINAL_POSITION;
                """,
                pkParameters);
            var primaryKeyColumns = pkTable.Rows.Cast<DataRow>()
                .Select(r => r["COLUMN_NAME"]?.ToString())
                .Where(x => !string.IsNullOrWhiteSpace(x))
                .ToList();

            if (primaryKeyColumns.Count == 0)
            {
                primaryKeyColumns = tableColumns
                    .Where(c => c.IsPrimarykey)
                    .OrderBy(c => c.DbColumnName)
                    .Select(c => c.DbColumnName)
                    .Where(x => !string.IsNullOrWhiteSpace(x))
                    .Select(x => (string?)x)
                    .ToList();
            }

            var pkMatches = primaryKeyColumns.Count == 1
                && primaryKeyColumns[0]!.Equals(repair.PkColumnName, StringComparison.OrdinalIgnoreCase);

            if (!pkMatches)
            {
                if (primaryKeyColumns.Count > 0)
                    db.Ado.ExecuteCommand($"ALTER TABLE `{tableName}` DROP PRIMARY KEY");

                db.Ado.ExecuteCommand($"ALTER TABLE `{tableName}` ADD PRIMARY KEY(`{repair.PkColumnName}`)");
            }

            if (!repair.IsIdentity)
                continue;

            var meta = GetMySqlColumnMeta(db, databaseName, tableName, repair.PkColumnName)
                ?? GetMySqlColumnMeta(db, databaseName, tableName, primaryKeyColumns.FirstOrDefault() ?? repair.PkColumnName);
            if (meta == null || ContainsToken(meta.Extra, "auto_increment"))
                continue;

            var ddl = $"ALTER TABLE `{tableName}` MODIFY COLUMN `{meta.ColumnName}` {BuildColumnDefinition(meta, forceNotNull: true, forceAutoIncrement: true, skipDefault: true, commentOverride: repair.PkComment)}";
            db.Ado.ExecuteCommand(ddl);
        }
    }

    private static MySqlColumnMeta? GetMySqlColumnMeta(ISqlSugarClient db, string databaseName, string tableName, string columnName)
    {
        var sql = """
                  SELECT
                      COLUMN_NAME AS ColumnName,
                      COLUMN_TYPE AS ColumnType,
                      IS_NULLABLE AS IsNullable,
                      COLUMN_DEFAULT AS ColumnDefault,
                      EXTRA AS Extra,
                      COLUMN_COMMENT AS ColumnComment,
                      COLLATION_NAME AS CollationName,
                      CHARACTER_SET_NAME AS CharacterSetName
                  FROM information_schema.COLUMNS
                  WHERE TABLE_SCHEMA = @db
                    AND TABLE_NAME = @table
                    AND BINARY COLUMN_NAME = @column
                  LIMIT 1;
                  """;
        SugarParameter[] parameters =
        [
            new SugarParameter("@db", databaseName),
            new SugarParameter("@table", tableName),
            new SugarParameter("@column", columnName)
        ];
        var table = db.Ado.GetDataTable(sql, parameters);
        if (table.Rows.Count == 0)
            return null;

        var row = table.Rows[0];
        return new MySqlColumnMeta(
            row["ColumnName"]?.ToString() ?? columnName,
            row["ColumnType"]?.ToString() ?? throw new InvalidOperationException($"Missing ColumnType for {tableName}.{columnName}"),
            row["IsNullable"]?.ToString() ?? "YES",
            row["ColumnDefault"] == DBNull.Value ? null : row["ColumnDefault"]?.ToString(),
            row["Extra"] == DBNull.Value ? null : row["Extra"]?.ToString(),
            row["ColumnComment"] == DBNull.Value ? null : row["ColumnComment"]?.ToString(),
            row["CollationName"] == DBNull.Value ? null : row["CollationName"]?.ToString(),
            row["CharacterSetName"] == DBNull.Value ? null : row["CharacterSetName"]?.ToString());
    }

    private static string BuildColumnDefinition(MySqlColumnMeta meta, bool forceNotNull = false, bool forceAutoIncrement = false, bool skipDefault = false, string? commentOverride = null)
    {
        var builder = new StringBuilder();
        builder.Append(meta.ColumnType);

        if (!string.IsNullOrWhiteSpace(meta.CharacterSetName))
            builder.Append($" CHARACTER SET {meta.CharacterSetName}");

        if (!string.IsNullOrWhiteSpace(meta.CollationName))
            builder.Append($" COLLATE {meta.CollationName}");

        var isNullable = !forceNotNull && meta.IsNullable.Equals("YES", StringComparison.OrdinalIgnoreCase);
        builder.Append(isNullable ? " NULL" : " NOT NULL");

        if (!skipDefault && meta.ColumnDefault != null)
        {
            builder.Append(" DEFAULT ");
            builder.Append(FormatDefaultValue(meta.ColumnType, meta.ColumnDefault));
        }
        else if (!skipDefault && isNullable &&
                 !ContainsToken(meta.Extra, "auto_increment") &&
                 !ContainsToken(meta.Extra, "DEFAULT_GENERATED"))
        {
            builder.Append(" DEFAULT NULL");
        }

        if (forceAutoIncrement || ContainsToken(meta.Extra, "auto_increment"))
            builder.Append(" AUTO_INCREMENT");

        if (ContainsToken(meta.Extra, "on update CURRENT_TIMESTAMP"))
            builder.Append(" ON UPDATE CURRENT_TIMESTAMP");

        var comment = commentOverride ?? meta.ColumnComment;
        if (!string.IsNullOrWhiteSpace(comment))
            builder.Append($" COMMENT '{EscapeSqlLiteral(comment)}'");

        return builder.ToString();
    }

    private static string FormatDefaultValue(string columnType, string columnDefault)
    {
        if (columnDefault.Equals("NULL", StringComparison.OrdinalIgnoreCase))
            return "NULL";

        if (columnDefault.StartsWith("CURRENT_TIMESTAMP", StringComparison.OrdinalIgnoreCase))
            return columnDefault;

        return IsNumericLikeType(columnType)
            ? columnDefault
            : $"'{EscapeSqlLiteral(columnDefault)}'";
    }

    private static bool IsNumericLikeType(string columnType)
    {
        var normalized = columnType.ToLowerInvariant();
        return normalized.StartsWith("tinyint") ||
               normalized.StartsWith("smallint") ||
               normalized.StartsWith("mediumint") ||
               normalized.StartsWith("int") ||
               normalized.StartsWith("bigint") ||
               normalized.StartsWith("decimal") ||
               normalized.StartsWith("numeric") ||
               normalized.StartsWith("float") ||
               normalized.StartsWith("double") ||
               normalized.StartsWith("real") ||
               normalized.StartsWith("bit") ||
               normalized.StartsWith("bool") ||
               normalized.StartsWith("boolean");
    }

    private static bool ContainsToken(string? value, string token)
        => !string.IsNullOrWhiteSpace(value) && value.Contains(token, StringComparison.OrdinalIgnoreCase);

    private static string EscapeSqlLiteral(string value)
        => value.Replace("\\", "\\\\").Replace("'", "\\'");
}