Display_D_HP/TeamAAS-VM/Data/DatabaseInitializer.cs

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TeamAAS_VP.Interfaces;
using TeamAAS_VP.Models;

namespace TeamAAS_VP.Data
{
    // 数据库初始化实现
    public class DatabaseInitializer : IDatabaseInitializer
    {
        private readonly ISqlSugarClient _db;

        public DatabaseInitializer(ISqlSugarClient db)
        {
            _db = db;
        }

        public void InitializeDatabase()
        {
            const int maxRetry = 3;
            Exception lastEx = null;

            for (int attempt = 1; attempt <= maxRetry; attempt++)
            {
                try
                {
                    // 创建数据库（如果不存在）
                    _db.DbMaintenance.CreateDatabase();
                    // 创建表
                    _db.CodeFirst.InitTables(typeof(User), typeof(ProductionRecord), typeof(UserLoginRecord), typeof(AlarmRecord), typeof(LockResult), typeof(ProductWithMes));

                    // 创建索引
                    CreateIndexes();

                    // 如果用户表为空，插入三个基础用户
                    var userCount = _db.Queryable<User>().Count();
                    if (userCount == 0)
                    {
                        var op = new User { UserName = "操作员", UserPassword = "", userPart = Enums.UserPart.Operator, CreateTime = DateTime.Now, IsRemember = false };
                        var eng = new User { UserName = "工程师", UserPassword = "10086", userPart = Enums.UserPart.Engineer, CreateTime = DateTime.Now, IsRemember = false };
                        var admin = new User { UserName = "管理员", UserPassword = "team123456", userPart = Enums.UserPart.Administrator, CreateTime = DateTime.Now, IsRemember = false };
                        _db.Insertable(new List<User> { op, eng, admin }).ExecuteCommand();
                    }
                    return; // 成功，退出
                }
                catch (Exception ex)
                {
                    lastEx = ex;
                    bool isDbCorrupted = ex.Message.Contains("invalid") || ex.Message.Contains("corrupt")
                                      || ex.Message.Contains("无效") || ex.Message.Contains("损坏");

                    if (attempt < maxRetry)
                    {
                        if (isDbCorrupted)
                        {
                            // 数据库可能损坏，尝试删除重建
                            try
                            {
                                string dbPath = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "AlignerDB.db");
                                if (System.IO.File.Exists(dbPath))
                                {
                                    System.IO.File.Delete(dbPath);
                                }
                                LogHelper.WriteLogInfo($"数据库文件已删除，准备重建 (attempt {attempt})");
                            }
                            catch
                            {
                                // 删除失败，继续重试
                            }
                        }
                        System.Threading.Thread.Sleep(500 * attempt); // 递增等待
                    }
                }
            }
            // 所有重试均失败
            throw new Exception($"数据库初始化失败(已重试{maxRetry}次): {lastEx?.Message}", lastEx);
        }

        public bool DatabaseExists()
        {
            return _db.DbMaintenance.GetDataBaseList().Contains("AlignerDB");
        }

        private void CreateIndexes()
        {
            try
            {
                // Ensure LockResult indexes for common queries
                // 单列索引：Timestamp, ProductNumber, LockPassed
                try { _db.DbMaintenance.CreateIndex("LockResult", new string[] { "Timestamp" }, "IX_LockResult_Timestamp", false); } catch { }
                try { _db.DbMaintenance.CreateIndex("LockResult", new string[] { "ProductNumber" }, "IX_LockResult_ProductNumber", false); } catch { }
                try { _db.DbMaintenance.CreateIndex("LockResult", new string[] { "LockPassed" }, "IX_LockResult_LockPassed", false); } catch { }

                // 新增索引：Number, FeederNumber, NozzleNumber
                try { _db.DbMaintenance.CreateIndex("LockResult", new string[] { "Number" }, "IX_LockResult_Number", false); } catch { }
                try { _db.DbMaintenance.CreateIndex("LockResult", new string[] { "FeederNumber" }, "IX_LockResult_FeederNumber", false); } catch { }
                try { _db.DbMaintenance.CreateIndex("LockResult", new string[] { "NozzleNumber" }, "IX_LockResult_NozzleNumber", false); } catch { }

                // 复合索引：ProductNumber + Timestamp（支持按产品+时间范围查询）
                try { _db.DbMaintenance.CreateIndex("LockResult", new string[] { "ProductNumber", "Timestamp" }, "IX_LockResult_ProductNumber_Timestamp", false); } catch { }

                // 你可以在这里为其他表添加索引，例如 ProductionRecord 的 Timestamp 或 ProductName
                try { _db.DbMaintenance.CreateIndex("ProductionRecord", new string[] { "ProductName", "Timestamp" }, "IX_ProductionRecord_ProductName_Timestamp", false); } catch { }
            }
            catch
            {
                // 忽略索引创建中的错误（如已存在），调用方仍然可以继续
            }
        }
    }
}