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()
        {
            try
            {
                // 创建数据库（如果不存在）
                _db.DbMaintenance.CreateDatabase();
                // 创建表
                _db.CodeFirst.InitTables(typeof(User), typeof(ProductionRecord), typeof(UserLoginRecord), typeof(AlarmRecord), typeof(LockResult));

                // 创建索引
                CreateIndexes();

                // 如果用户表为空，插入三个基础用户
                var userCount = _db.Queryable<User>().Count();
                if (userCount == 0)
                {
                    var op = new User { UserName = "操作员", UserPassword = "123", userPart = Enums.UserPart.Operator, CreateTime = DateTime.Now, IsRemember = false };
                    var eng = new User { UserName = "工程师", UserPassword = "123", 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();
                }
            }
            catch (Exception ex)
            {
                // 记录日志
                throw new Exception($"数据库初始化失败: {ex.Message}", ex);
            }
        }

        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
            {
                // 忽略索引创建中的错误（如已存在），调用方仍然可以继续
            }
        }
    }
}