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;
using TeamAAS_VP.Resources.Languages;

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), 
                    typeof(ScrewFeederBatchRecord), typeof(PhotoCaptureRecord), typeof(HeadChangeRecord),typeof(NozzleChangeRecord), typeof(ThrowRecord), typeof(ProductWithMes));

                // 创建索引
                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(string.Format(Lang.数据库初始化失败0,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 { }
                // Screw feeder batch records indexes
                try { _db.DbMaintenance.CreateIndex("ScrewFeederBatchRecord", new string[] { "ChangeTime" }, "IX_ScrewFeederBatchRecord_ChangeTime", false); } catch { }
                try { _db.DbMaintenance.CreateIndex("ScrewFeederBatchRecord", new string[] { "FeederNumber" }, "IX_ScrewFeederBatchRecord_FeederNumber", false); } catch { }
                try { _db.DbMaintenance.CreateIndex("ScrewFeederBatchRecord", new string[] { "BatchNumber" }, "IX_ScrewFeederBatchRecord_BatchNumber", false); } catch { }

                // Photo capture records indexes
                try { _db.DbMaintenance.CreateIndex("PhotoCaptureRecord", new string[] { "CaptureTime" }, "IX_PhotoCaptureRecord_CaptureTime", false); } catch { }
                try { _db.DbMaintenance.CreateIndex("PhotoCaptureRecord", new string[] { "RecipeName" }, "IX_PhotoCaptureRecord_RecipeName", false); } catch { }
                try { _db.DbMaintenance.CreateIndex("PhotoCaptureRecord", new string[] { "ProductSN" }, "IX_PhotoCaptureRecord_ProductSN", false); } catch { }

                // Head change records indexes
                try { _db.DbMaintenance.CreateIndex("HeadChangeRecord", new string[] { "ChangeTime" }, "IX_HeadChangeRecord_ChangeTime", false); } catch { }
                try { _db.DbMaintenance.CreateIndex("HeadChangeRecord", new string[] { "HeadBatchNumber" }, "IX_HeadChangeRecord_HeadBatchNumber", false); } catch { }
                try { _db.DbMaintenance.CreateIndex("PhotoCaptureRecord", new string[] { "CameraName" }, "IX_PhotoCaptureRecord_CameraName", false); } catch { }
                // Nozzle change records indexes (same as head)
                try { _db.DbMaintenance.CreateIndex("NozzleChangeRecord", new string[] { "ChangeTime" }, "IX_NozzleChangeRecord_ChangeTime", false); } catch { }
                try { _db.DbMaintenance.CreateIndex("NozzleChangeRecord", new string[] { "NozzleBatchNumber" }, "IX_NozzleChangeRecord_NozzleBatchNumber", false); } catch { }
            }
            catch
            {
                // 忽略索引创建中的错误（如已存在），调用方仍然可以继续
            }
        }
    }
}