Display_D_HP/TeamAAS-VM/Data/SystemDatabaseService.cs

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

namespace TeamAAS_VP.Data
{
    /// <summary>
    /// 系统数据库服务，基于 <see cref="ISqlSugarClient"/> 提供对应用数据的访问。
    /// 包含用户增删改查、用户身份验证以及生产记录的写入与统计功能。
    /// </summary>
    public class SystemDatabaseService : ISystemDatabaseService
    {
        private readonly ISqlSugarClient _db;
        private User CurrentUser { get; set; }

        /// <summary>
        /// 使用指定的 SqlSugar 客户端构造数据库服务实例。
        /// </summary>
        /// <param name="db">已配置的 <see cref="ISqlSugarClient"/> 实例。</param>
        public SystemDatabaseService(ISqlSugarClient db)
        {
            _db = db;
            //GenerateTestDataAsync();
        }

        #region 用户
        // User CRUD

        /// <summary>
        /// 添加新用户到数据库。
        /// 对于 SqlSugar，User.Id 为数据库自增值，因此在此方法中不应设定 Id。
        /// </summary>
        /// <param name="user">要添加的用户对象，不能为空。</param>
        /// <returns>返回传入的用户对象（Id 由数据库生成后会回写到该对象）。</returns>
        /// <exception cref="ArgumentNullException">当 <paramref name="user"/> 为 null 时抛出。</exception>
        public async Task<User> AddUserAsync(User user)
        {
            if (user == null) throw new ArgumentNullException(nameof(user));
            // for SqlSugar User.Id is int identity, so do not set it here
            await _db.Insertable(user).ExecuteCommandAsync();
            return user;
        }

        /// <summary>
        /// 根据用户 Id 删除用户。
        /// </summary>
        /// <param name="userId">要删除的用户 Id。</param>
        /// <returns>如果删除了至少一条记录则返回 true，否则返回 false。</returns>
        public async Task<bool> DeleteUserAsync(int userId)
        {
            var r = await _db.Deleteable<User>().In(userId).ExecuteCommandAsync();
            return r > 0;
        }

        /// <summary>
        /// 获取所有用户。
        /// </summary>
        /// <returns>用户列表的异步枚举。</returns>
        public async Task<IEnumerable<User>> GetAllUsersAsync()
        {
            return await _db.Queryable<User>().ToListAsync();
        }

        /// <summary>
        /// 根据用户 Id 查询单个用户。
        /// </summary>
        /// <param name="userId">用户 Id。</param>
        /// <returns>匹配的用户，如果未找到则返回 null（取决于 SqlSugar 的行为）。</returns>
        public async Task<User> GetUserByIdAsync(int userId)
        {
            return await _db.Queryable<User>().InSingleAsync(userId);
        }

        /// <summary>
        /// 更新用户信息并返回更新后的对象。
        /// </summary>
        /// <param name="user">要更新的用户对象，不能为空。</param>
        /// <returns>返回传入的用户对象。</returns>
        /// <exception cref="ArgumentNullException">当 <paramref name="user"/> 为 null 时抛出。</exception>
        public async Task<User> UpdateUserAsync(User user)
        {
            if (user == null) throw new ArgumentNullException(nameof(user));
            var r = await _db.Updateable(user).ExecuteCommandAsync();
            return user;
        }

        // User authentication

        /// <summary>
        /// 验证用户名和密码并返回匹配的用户。
        /// 注意：当前实现直接以明文密码比较，请根据需要在调用侧或更高层添加哈希/盐策略。
        /// </summary>
        /// <param name="username">用户名，不能为空或空字符串。</param>
        /// <param name="password">密码，不能为空或空字符串。</param>
        /// <returns>匹配的用户对象，如果未找到则可能抛出或返回 null（取决于 SqlSugar 的 FirstAsync 行为）。</returns>
        /// <exception cref="ArgumentException">当 <paramref name="username"/> 或 <paramref name="password"/> 为空时抛出。</exception>
        public async Task<User> AuthenticateUserAsync(string username, string password)
        {
            if (string.IsNullOrEmpty(username)) throw new ArgumentException("username is required", nameof(username));
            if (string.IsNullOrEmpty(password)) throw new ArgumentException("password is required", nameof(password));

            return await _db.Queryable<User>().Where(u => u.UserName == username && u.UserPassword == password).FirstAsync();
        }

        // User login / current user helpers

        /// <summary>
        /// 将指定用户设为当前用户（写入一条成功的登录记录）。
        /// </summary>
        /// <param name="userId">要设为当前用户的用户 Id。</param>
        public async Task SetCurrentUserAsync(int userId)
        {
            // 验证用户存在
            var user = await GetUserByIdAsync(userId);
            if (user == null)
            {
                throw new ArgumentException($"User with id {userId} not found", nameof(userId));
            }
            CurrentUser = user;

            // 记录一条成功的登录记录
            var rec = new UserLoginRecord
            {
                UserId = userId,
                Success = true,
                Time = DateTime.Now,
                Message = "Set as current user"
            };

            await RecordUserLoginAsync(rec);
        }

        /// <summary>
        /// 将指定用户设为当前用户（会写入一条登录记录，标记为成功）
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public async Task SetCurrentUserAsync(User user)
        {
            CurrentUser = user;
            // 记录一条成功的登录记录
            var rec = new UserLoginRecord
            {
                UserId = user.Id,
                Success = true,
                Time = DateTime.Now,
                Message = user.UserName
            };
            await RecordUserLoginAsync(rec);
        }

        /// <summary>
        /// 获取最近一次成功登录的用户（视为当前用户）。
        /// </summary>
        /// <returns>若存在成功登录记录则返回对应用户，否则返回 null。</returns>
        public Task<User> GetCurrentUserAsync()
        {
            return Task.FromResult(GetCurrentUser());
        }

        /// <summary>
        /// 获取当前用户
        /// </summary>
        /// <returns></returns>
        public User GetCurrentUser()
        {
            //var recs = _db.Queryable<UserLoginRecord>()
            //    .Where(r => r.Success == true)
            //    .OrderBy(r => r.Time, OrderByType.Desc)
            //    .Take(1)
            //    .ToList();
            //var rec = recs.FirstOrDefault();
            ////如果没有登录记录则返回一个默认的用户，即第一个用户
            //if (rec == null) return _db.Queryable<User>().First();
            //return _db.Queryable<User>().InSingle(rec.UserId);
            if (CurrentUser == null)
            {
                CurrentUser = _db.Queryable<User>().ToList().FirstOrDefault();
            }
            return CurrentUser;
        }

        /// <summary>
        /// 记录一条用户登录记录（成功或失败）。
        /// </summary>
        /// <param name="record">登录记录对象，不能为空。</param>
        public async Task RecordUserLoginAsync(UserLoginRecord record)
        {
            if (record == null) throw new ArgumentNullException(nameof(record));
            if (record.Time == default(DateTime)) record.Time = DateTime.Now;
            await _db.Insertable(record).ExecuteCommandAsync();
        }

        /// <summary>
        /// 获取指定用户最近的一条登录记录（无论是否成功）。
        /// </summary>
        /// <param name="userId">用户 Id。</param>
        /// <returns>最近的一条登录记录或 null。</returns>
        public async Task<UserLoginRecord> GetLastUserLoginRecordAsync(int userId)
        {
            var recs = await _db.Queryable<UserLoginRecord>()
                .Where(r => r.UserId == userId)
                .OrderBy(r => r.Time, OrderByType.Desc)
                .Take(1)
                .ToListAsync();
            return recs.FirstOrDefault();
        }

        /// <summary>
        /// 将指定用户名标记为“记住”，并取消其他用户的记住标志。
        /// </summary>
        /// <param name="username">要记住的用户名。</param>
        public async Task SetRememberUserAsync(string username)
        {
            if (string.IsNullOrEmpty(username)) throw new ArgumentException("username is required", nameof(username));

            // 使用事务先清除所有记住标志，再为指定用户设置记住
            try
            {
                _db.Ado.BeginTran();
                // 清除所有
                await _db.Updateable<User>().SetColumns(u => new User { IsRemember = false }).Where("1=1").ExecuteCommandAsync();
                // 设置指定用户为记住
                await _db.Updateable<User>().SetColumns(u => new User { IsRemember = true }).Where(u => u.UserName == username).ExecuteCommandAsync();
                _db.Ado.CommitTran();
            }
            catch
            {
                try { _db.Ado.RollbackTran(); } catch { }
                throw;
            }
        }

        /// <summary>
        /// 清除所有用户的记住标志。
        /// </summary>
        public async Task ClearRememberUserAsync()
        {
            await _db.Updateable<User>().SetColumns(u => new User { IsRemember = false }).ExecuteCommandAsync();
        }

        /// <summary>
        /// 获取当前被标记为记住的用户（如果存在）。
        /// </summary>
        /// <returns>被记住的用户或 null。</returns>
        public async Task<User> GetRememberedUserAsync()
        {
            return await _db.Queryable<User>().Where(u => u.IsRemember == true).FirstAsync();
        }

        #endregion

        #region 生产记录
        // Production records

        /// <summary>
        /// 记录一次生产事件。如果 <see cref="ProductionRecord.Id"/> 为 <see cref="Guid.Empty"/>
        /// 则会自动生成新的 Guid。
        /// </summary>
        /// <param name="record">生产记录对象，不能为空。</param>
        /// <returns>异步任务。</returns>
        /// <exception cref="ArgumentNullException">当 <paramref name="record"/> 为 null 时抛出。</exception>
        public async Task RecordProductionAsync(ProductionRecord record)
        {
            if (record == null) throw new ArgumentNullException(nameof(record));

            // 如果未指定产生该记录的用户名，尝试使用当前登录用户
            if (string.IsNullOrEmpty(record.UserName))
            {
                try
                {
                    var current = await GetCurrentUserAsync();
                    if (current != null)
                    {
                        record.UserName = current.UserName;
                    }
                }
                catch
                {
                    // 忽略获取当前用户时的错误，继续保存记录（保守策略）
                }
            }

            record.Id = record.Id == Guid.Empty ? Guid.NewGuid() : record.Id;
            await _db.Insertable(record).ExecuteCommandAsync();
        }

        /// <summary>
        /// 重置生产记录。若未传入 <paramref name="productName"/>
        /// 则清空所有生产记录；否则仅删除指定产品名的记录。
        /// </summary>
        /// <param name="productName">可选的产品名过滤。</param>
        /// <returns>异步任务。</returns>
        public async Task ResetProductionAsync(string productName = null)
        {
            if (string.IsNullOrEmpty(productName))
            {
                await _db.Deleteable<ProductionRecord>().Where("1=1").ExecuteCommandAsync();
            }
            else
            {
                await _db.Deleteable<ProductionRecord>().Where(r => r.ProductName == productName).ExecuteCommandAsync();
            }
        }

        /// <summary>
        /// 获取指定产品（和可选类别）在数据库中累计的产量总和（仅统计 Quantity &gt; 0 的记录）。
        /// </summary>
        /// <param name="productName">产品名，不能为空或空字符串。</param>
        /// <param name="categories">可选的类别集合，用于过滤。</param>
        /// <returns>指定条件下的产量总和（整数）。</returns>
        /// <exception cref="ArgumentException">当 <paramref name="productName"/> 为空时抛出。</exception>
        public async Task<int> GetTotalProductionAsync(string productName, IEnumerable<string> categories = null)
        {
            if (string.IsNullOrEmpty(productName)) throw new ArgumentException("productName is required", nameof(productName));

            var q = _db.Queryable<ProductionRecord>().Where(r => r.ProductName == productName && r.Quantity > 0);
            if (categories != null && categories.Any())
            {
                q = q.Where(r => categories.Contains(r.Category));
            }
            var sum = await q.SumAsync<int>(r => r.Quantity);
            return sum;
        }

        /// <summary>
        /// 获取生产统计结果，按指定时间范围、产品名和可选类别筛选，
        /// 并根据 <see cref="ProductionStatQuery.GroupBy"/>（hour/day/week/month/total）进行分组聚合。
        /// </summary>
        /// <param name="query">生产统计查询对象，不能为空，必须包含 ProductName/Start/End 等。</param>
        /// <returns>按指定分组粒度返回的统计结果集合。</returns>
        /// <exception cref="ArgumentNullException">当 <paramref name="query"/> 为 null 时抛出。</exception>
        public async Task<IEnumerable<ProductionStatResult>> GetProductionStatsAsync(ProductionStatQuery query)
        {
            if (query == null) throw new ArgumentNullException(nameof(query));
            if (string.IsNullOrEmpty(query.ProductName)) throw new ArgumentException("ProductName is required in query", nameof(query));

            var q = _db.Queryable<ProductionRecord>().Where(r => r.ProductName == query.ProductName && r.Timestamp >= query.Start && r.Timestamp < query.End);
            if (query.Categories != null && query.Categories.Any())
            {
                q = q.Where(r => query.Categories.Contains(r.Category));
            }

            var list = await q.ToListAsync();

            var results = new List<ProductionStatResult>();

            switch ((query.GroupBy ?? "total").ToLower())
            {
                case "hour":
                    // group by hour
                    var hours = list.GroupBy(r => new DateTime(r.Timestamp.Year, r.Timestamp.Month, r.Timestamp.Day, r.Timestamp.Hour, 0, 0))
                        .OrderBy(g => g.Key);
                    foreach (var g in hours)
                    {
                        results.Add(new ProductionStatResult { PeriodStart = g.Key, PeriodEnd = g.Key.AddHours(1), Quantity = g.Sum(x => x.Quantity), ProductName = query.ProductName });
                    }
                    break;
                case "day":
                    var days = list.GroupBy(r => r.Timestamp.Date).OrderBy(g => g.Key);
                    foreach (var g in days)
                    {
                        results.Add(new ProductionStatResult { PeriodStart = g.Key, PeriodEnd = g.Key.AddDays(1), Quantity = g.Sum(x => x.Quantity), ProductName = query.ProductName });
                    }
                    break;
                case "week":
                    // ISO week grouping by week start (Monday)
                    var weeks = list.GroupBy(r => FirstDayOfWeek(r.Timestamp)).OrderBy(g => g.Key);
                    foreach (var g in weeks)
                    {
                        results.Add(new ProductionStatResult { PeriodStart = g.Key, PeriodEnd = g.Key.AddDays(7), Quantity = g.Sum(x => x.Quantity), ProductName = query.ProductName });
                    }
                    break;
                case "month":
                    var months = list.GroupBy(r => new DateTime(r.Timestamp.Year, r.Timestamp.Month, 1)).OrderBy(g => g.Key);
                    foreach (var g in months)
                    {
                        results.Add(new ProductionStatResult { PeriodStart = g.Key, PeriodEnd = g.Key.AddMonths(1), Quantity = g.Sum(x => x.Quantity), ProductName = query.ProductName });
                    }
                    break;
                default:
                    results.Add(new ProductionStatResult { PeriodStart = query.Start, PeriodEnd = query.End, Quantity = list.Sum(x => x.Quantity), ProductName = query.ProductName });
                    break;
            }

            return results;
        }

        /// <summary>
        /// 获取所有已知分类相加起来的今天的产能
        /// </summary>
        /// <param name="productName"></param>
        /// <returns></returns>
        public async Task<int> GetTodayProductionAsync(string productName)
        {
            //今天的起始时间
            var todayStart = DateTime.Now.Date;
            //今天的结束时间
            var todayEnd = DateTime.Now.Date;
            todayEnd = todayEnd.AddDays(1);
            //按照产品名称和时间范围查询生产记录的总数
            var total = await _db.Queryable<ProductionRecord>()
                .Where(r => r.ProductName == productName && r.Timestamp >= todayStart && r.Timestamp < todayEnd)
                .SumAsync(r => r.Quantity);
            return total;
        }

        /// <summary>
        /// 获取所有已知分类相加起来的当前时间往前的一个小时的产能
        /// </summary>
        /// <param name="productName"></param>
        /// <returns></returns>
        public async Task<int> GetLastHourProductionAsync(string productName)
        {
            var lastHourStart = DateTime.Now.AddHours(-1);
            var lastHourEnd = DateTime.Now;
            //按照产品名称和时间范围查询生产记录的总数
            var total = await _db.Queryable<ProductionRecord>()
                .Where(r => r.ProductName == productName && r.Timestamp >= lastHourStart && r.Timestamp < lastHourEnd)
                .SumAsync(r => r.Quantity);
            return total;
        }

        /// <summary>
        /// 获取当前月的产能
        /// </summary>
        /// <param name="productName"></param>
        /// <returns></returns>
        public Task<int> GetCurrentMonthProductionAsync(string productName)
        {

            var now = DateTime.Now;
            var monthStart = new DateTime(now.Year, now.Month, 1);
            var monthEnd = monthStart.AddMonths(1);
            //按照产品名称和时间范围查询生产记录的总数
            return _db.Queryable<ProductionRecord>()
                .Where(r => r.ProductName == productName && r.Timestamp >= monthStart && r.Timestamp < monthEnd)
                .SumAsync(r => r.Quantity);

        }

        /// <summary>
        /// 获取当前周的产能
        /// </summary>
        /// <param name="productName"></param>
        /// <returns></returns>
        public Task<int> GetCurrentWeekProductionAsync(string productName)
        {
            var now = DateTime.Now;
            var weekStart = FirstDayOfWeek(now);
            var weekEnd = weekStart.AddDays(7);
            //按照产品名称和时间范围查询生产记录的总数
            return _db.Queryable<ProductionRecord>()
                .Where(r => r.ProductName == productName && r.Timestamp >= weekStart && r.Timestamp < weekEnd)
                .SumAsync(r => r.Quantity);

        }

        /// <summary>
        /// 计算给定日期所属周的第一天（以星期一为一周的第一天）。
        /// 返回结果为日期的 Date 部分（时间为 00:00:00）。
        /// </summary>
        /// <param name="dt">输入日期。</param>
        /// <returns>该日期对应周的周一日期（Date 部分）。</returns>
        private DateTime FirstDayOfWeek(DateTime dt)
        {
            var diff = (7 + (dt.DayOfWeek - DayOfWeek.Monday)) % 7;
            return dt.Date.AddDays(-1 * diff);
        }

        /// <summary>
        /// 获取指定产品名称的总产能
        /// </summary>
        /// <param name="productName"></param>
        /// <returns></returns>
        public Task<int> GetOverallProductionAsync(string productName)
        {
            return _db.Queryable<ProductionRecord>()
                .Where(r => r.ProductName == productName)
                .SumAsync(r => r.Quantity);
        }

        /// <summary>
        /// 获取指定产品的所有已知分类。
        /// </summary>
        /// <param name="productName">要查询分类的产品名称。</param>
        /// <returns>
        /// 一个字符串集合，包含该产品的所有分类名称；如果没有分类，返回空集合或 null（建议返回空集合）。
        /// </returns>
        public async Task<IEnumerable<string>> GetProductCategoriesAsync(string productName)
        {
            var categories = await _db.Queryable<ProductionRecord>()
                .Where(r => r.ProductName == productName)
                .Select(r => r.Category)
                .Distinct()
                .ToListAsync();
            return categories;
        }

        /// <summary>
        /// 获取指定产品名称的所有记录按照分类的产能统计
        /// </summary>
        /// <param name="productName"></param>
        /// <returns></returns>
        public async Task<Dictionary<string, int>> GetProductionByCategoriesAsync(string productName)
        {
            var list = await _db.Queryable<ProductionRecord>()
                .Where(r => r.ProductName == productName)
                .ToListAsync();
            var dict = list.GroupBy(r => r.Category)
                .ToDictionary(g => g.Key, g => g.Sum(r => r.Quantity));
            return dict;
        }

        /// <summary>
        /// 获取指定产品名称的一个月内按照每天统计对应的类别和数量
        /// </summary>
        /// <param name="productName"></param>
        /// <returns></returns>
        public async Task<Dictionary<DateTime, Dictionary<string, int>>> GetMonthlyProductionByDayAndCategoryAsync(string productName)
        {
            var now = DateTime.Now;
            var monthStart = new DateTime(now.Year, now.Month, 1);
            var monthEnd = monthStart.AddMonths(1);
            var list = await _db.Queryable<ProductionRecord>()
                .Where(r => r.ProductName == productName && (r.Timestamp >= monthStart && r.Timestamp < monthEnd))
                .ToListAsync();
            var dict = new Dictionary<DateTime, Dictionary<string, int>>();
            var days = list.GroupBy(r => r.Timestamp.Date).OrderBy(g => g.Key);
            foreach (var dayGroup in days)
            {
                var categoryDict = dayGroup.GroupBy(r => r.Category)
                    .ToDictionary(g => g.Key, g => g.Sum(r => r.Quantity));
                dict[dayGroup.Key] = categoryDict;
            }
            return dict;

        }

        /// <summary>
        /// 获取指定产品名称的一个星期内按照每天统计对应的类别和数量
        /// </summary>
        /// <param name="productName"></param>
        /// <returns></returns>
        public async Task<Dictionary<int, Dictionary<string, int>>> GetWeeklyProductionByDayAndCategoryAsync(string productName)
        {
            var now = DateTime.Now;
            var weekStart = FirstDayOfWeek(now);
            var weekEnd = weekStart.AddDays(7);
            var list = await _db.Queryable<ProductionRecord>()
                .Where(r => r.ProductName == productName && r.Timestamp >= weekStart && r.Timestamp < weekEnd)
                .ToListAsync();
            var dict = new Dictionary<int, Dictionary<string, int>>();
            var days = list.GroupBy(r => r.Timestamp.DayOfWeek).OrderBy(g => g.Key);
            foreach (var dayGroup in days)
            {
                var categoryDict = dayGroup.GroupBy(r => r.Category)
                    .ToDictionary(g => g.Key, g => g.Sum(r => r.Quantity));
                dict[(int)dayGroup.Key] = categoryDict;
            }
            return dict;
        }

        /// <summary>
        /// 获取指定产品名称的当天内按照每小时统计对应的类别和数量
        /// </summary>
        /// <param name="productName"></param>
        /// <returns></returns>
        public async Task<Dictionary<int, Dictionary<string, int>>> GetDailyProductionByHourAndCategoryAsync(string productName)
        {
            var now = DateTime.Now;
            var dayStart = now.Date;
            var dayEnd = dayStart.AddDays(1);
            var list = await _db.Queryable<ProductionRecord>()
                .Where(r => r.ProductName == productName && r.Timestamp >= dayStart && r.Timestamp < dayEnd)
                .ToListAsync();
            var dict = new Dictionary<int, Dictionary<string, int>>();
            var hours = list.GroupBy(r => r.Timestamp.Hour).OrderBy(g => g.Key);
            foreach (var hourGroup in hours)
            {
                var categoryDict = hourGroup.GroupBy(r => r.Category)
                    .ToDictionary(g => g.Key, g => g.Sum(r => r.Quantity));
                dict[hourGroup.Key] = categoryDict;
            }
            return dict;
        }

        /// <summary>
        /// 分页查询生产记录
        /// </summary>
        public async Task<(IEnumerable<ProductionRecord> Items, int TotalCount)> QueryProductionRecordsPagedAsync(DateTime start, DateTime end, int pageIndex, int pageSize, string productName = null, string category = null)
        {
            if (pageIndex < 1) pageIndex = 1;
            if (pageSize < 1) pageSize = 20;
            var q = _db.Queryable<ProductionRecord>().Where(r => r.Timestamp >= start && r.Timestamp < end);
            if (!string.IsNullOrEmpty(productName)) q = q.Where(r => r.ProductName == productName);
            if (!string.IsNullOrEmpty(category)) q = q.Where(r => r.Category == category);
            var total = await q.CountAsync();
            var list = await q.OrderBy(r => r.Timestamp, OrderByType.Desc).ToPageListAsync(pageIndex, pageSize);
            return (list, total);
        }
        #endregion

        #region 报警记录
        // Alarm Records

        /// <summary>
        /// 记录一条报警记录。
        /// </summary>
        /// <param name="alarm">报警记录对象，不能为空。</param>
        public async Task RecordAlarmAsync(AlarmRecord alarm)
        {
            if (alarm == null) throw new ArgumentNullException(nameof(alarm));
            if (alarm.Time == default(DateTime)) alarm.Time = DateTime.Now;
            await _db.Insertable(alarm).ExecuteCommandAsync();
        }

        /// <summary>
        /// 按时间范围和可选条件查询报警记录。
        /// </summary>
        /// <param name="start">起始时间（含）。</param>
        /// <param name="end">结束时间（不含）。</param>
        /// <param name="code">可选的报警编号过滤。</param>
        /// <param name="source">可选的报警来源过滤。</param>
        /// <returns>满足条件的报警记录集合。</returns>
        public async Task<IEnumerable<AlarmRecord>> QueryAlarmsAsync(DateTime start, DateTime end, string code = null, string source = null)
        {
            var q = _db.Queryable<AlarmRecord>().Where(a => a.Time >= start && a.Time < end);
            if (!string.IsNullOrEmpty(code)) q = q.Where(a => a.Code == code);
            if (!string.IsNullOrEmpty(source)) q = q.Where(a => a.Source == source);
            return await q.OrderBy(a => a.Time, OrderByType.Desc).ToListAsync();
        }

        #endregion

        #region 锁付结果记录
        // Lock result methods
        /// <summary>
        /// 记录单次锁付结果（WaveDatas 不写入数据库）
        /// </summary>
        /// <param name="result"></param>
        public async Task RecordLockResultAsync(LockResult result)
        {
            try
            {
                if (result == null) throw new ArgumentNullException(nameof(result));
                if (result.Timestamp == default(DateTime)) result.Timestamp = DateTime.Now;
                await _db.Insertable(result).ExecuteCommandAsync();
            }
            catch (Exception)
            {

            }
        }

        /// <summary>
        /// 分页查询锁付结果
        /// </summary>
        public async Task<(IEnumerable<LockResult> Items, int TotalCount)> QueryLockResultsAsync(DateTime start, DateTime end, int pageIndex, int pageSize, string productNumber = null, string productName = null, int? screwNumber = null, bool? lockPassed = null)
        {
            if (pageIndex < 1) pageIndex = 1;
            if (pageSize < 1) pageSize = 20;

            var q = _db.Queryable<LockResult>().Where(r => r.Timestamp >= start && r.Timestamp < end);
            if (!string.IsNullOrEmpty(productNumber)) q = q.Where(r => r.ProductNumber == productNumber);
            if (screwNumber.HasValue) q = q.Where(r => r.Number == screwNumber.Value);
            if (lockPassed.HasValue) q = q.Where(r => r.LockPassed == lockPassed.Value);
            if (!string.IsNullOrEmpty(productName)) q = q.Where(r => r.ProductName == productName);

            var total = await q.CountAsync();
            var list = await q.OrderBy(r => r.Timestamp, OrderByType.Desc).ToPageListAsync(pageIndex, pageSize);
            return (list, total);
        }

        /// <summary>
        /// 查询所有符合条件的锁付结果（不分页）。
        /// </summary>
        public async Task<IEnumerable<LockResult>> QueryAllLockResultsAsync(DateTime start, DateTime end, string productNumber = null, string productName = null, int? screwNumber = null, bool? lockPassed = null)
        {
            var q = _db.Queryable<LockResult>().Where(r => r.Timestamp >= start && r.Timestamp < end);
            if (!string.IsNullOrEmpty(productNumber)) q = q.Where(r => r.ProductNumber == productNumber);
            if (screwNumber.HasValue) q = q.Where(r => r.Number == screwNumber.Value);
            if (lockPassed.HasValue) q = q.Where(r => r.LockPassed == lockPassed.Value);
            if (!string.IsNullOrEmpty(productName)) q = q.Where(r => r.ProductName == productName);

            var list = await q.OrderBy(r => r.Timestamp, OrderByType.Desc).ToListAsync();
            return list;
        }

        #endregion

        #region 螺丝供料器批次记录
        /// <summary>
        /// 记录一次螺丝供料器批次更换记录
        /// </summary>
        /// <param name="record"></param>
        /// <returns></returns>
        public async Task RecordScrewFeederBatchAsync(ScrewFeederBatchRecord record)
        {
            if (record == null) throw new ArgumentNullException(nameof(record));
            if (record.ChangeTime == default(DateTime)) record.ChangeTime = DateTime.Now;
            record.Id = record.Id == Guid.Empty ? Guid.NewGuid() : record.Id;
            await _db.Insertable(record).ExecuteCommandAsync();
        }

        /// <summary>
        /// 查询螺丝供料器批次记录。
        /// 可按时间段、批次号精确匹配及供料器编号过滤。所有参数均为可选，传入 null 表示不作为过滤条件。
        /// </summary>
        /// <param name="start">可选：起始时间（包含）</param>
        /// <param name="end">可选：结束时间（包含）</param>
        /// <param name="batchNumber">可选：批次号（精确匹配）</param>
        /// <param name="feederNumber">可选：供料器编号</param>
        /// <returns>满足条件的记录集合（按 ChangeTime 降序）。</returns>
        public async Task<IEnumerable<ScrewFeederBatchRecord>> QueryScrewFeederBatchRecordsAsync(DateTime? start = null, DateTime? end = null, string batchNumber = null, int? feederNumber = null)
        {
            var q = _db.Queryable<ScrewFeederBatchRecord>();
            if (start.HasValue)
                q = q.Where(r => r.ChangeTime >= start.Value);
            if (end.HasValue)
                q = q.Where(r => r.ChangeTime <= end.Value);
            if (!string.IsNullOrEmpty(batchNumber))
                q = q.Where(r => r.BatchNumber == batchNumber);
            if (feederNumber.HasValue)
                q = q.Where(r => r.FeederNumber == feederNumber.Value);

            var list = await q.OrderBy(r => r.ChangeTime, OrderByType.Desc).ToListAsync();
            return list;
        }

        /// <summary>
        /// 分页查询螺丝供料器批次记录
        /// </summary>
        public async Task<(IEnumerable<ScrewFeederBatchRecord> Items, int TotalCount)> QueryScrewFeederBatchRecordsPagedAsync(DateTime? start = null, DateTime? end = null, string batchNumber = null, int? feederNumber = null, int pageIndex = 1, int pageSize = 20)
        {
            if (pageIndex < 1) pageIndex = 1;
            if (pageSize < 1) pageSize = 20;
            var q = _db.Queryable<ScrewFeederBatchRecord>();
            if (start.HasValue) q = q.Where(r => r.ChangeTime >= start.Value);
            if (end.HasValue) q = q.Where(r => r.ChangeTime <= end.Value);
            if (!string.IsNullOrEmpty(batchNumber)) q = q.Where(r => r.BatchNumber == batchNumber);
            if (feederNumber.HasValue) q = q.Where(r => r.FeederNumber == feederNumber.Value);
            var total = await q.CountAsync();
            var list = await q.OrderBy(r => r.ChangeTime, OrderByType.Desc).ToPageListAsync(pageIndex, pageSize);
            return (list, total);
        }

        /// <summary>
        /// 获取指定供料器编号的最新一条批次更换记录
        /// </summary>
        /// <param name="feederNumber"></param>
        /// <returns></returns>
        public async Task<ScrewFeederBatchRecord> GetLatestScrewFeederBatchRecordAsync(int feederNumber)
        {
            var recs = await _db.Queryable<ScrewFeederBatchRecord>()
                .Where(r => r.FeederNumber == feederNumber)
                .OrderBy(r => r.ChangeTime, OrderByType.Desc)
                .Take(1)
                .ToListAsync();
            return recs.FirstOrDefault();
        }

        #endregion

        #region 拍照记录
        /// <summary>
        /// 记录产品拍照结果
        /// </summary>
        /// <param name="record"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException"></exception>
        public async Task RecordPhotoCaptureAsync(PhotoCaptureRecord record)
        {
            if (record == null) throw new ArgumentNullException(nameof(record));
            if (record.CaptureTime == default(DateTime)) record.CaptureTime = DateTime.Now;
            record.Id = record.Id == Guid.Empty ? Guid.NewGuid() : record.Id;
            await _db.Insertable(record).ExecuteCommandAsync();
        }

        /// <summary>
        /// 查询拍照记录，支持按配方名、产品SN和时间段过滤，参数均为可选。
        /// </summary>
        /// <param name="recipeName"></param>
        /// <param name="cameraName"></param>
        /// <param name="productSN"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public async Task<IEnumerable<PhotoCaptureRecord>> QueryPhotoCapturesAsync(string recipeName = null, string cameraName = null, string productSN = null, DateTime? start = null, DateTime? end = null)
        {
            var q = _db.Queryable<PhotoCaptureRecord>();
            if (!string.IsNullOrEmpty(recipeName)) q = q.Where(r => r.RecipeName == recipeName);
            if (!string.IsNullOrEmpty(productSN)) q = q.Where(r => r.ProductSN == productSN);
            if (start.HasValue) q = q.Where(r => r.CaptureTime >= start.Value);
            if (end.HasValue) q = q.Where(r => r.CaptureTime <= end.Value);
            var list = await q.OrderBy(r => r.CaptureTime, OrderByType.Desc).ToListAsync();
            return list;
        }

        /// <summary>
        /// 分页查询拍照记录
        /// </summary>
        public async Task<(IEnumerable<PhotoCaptureRecord> Items, int TotalCount)> QueryPhotoCapturesPagedAsync(string recipeName = null, string cameraName = null, string productSN = null, DateTime? start = null, DateTime? end = null, int pageIndex = 1, int pageSize = 20)
        {
            if (pageIndex < 1) pageIndex = 1;
            if (pageSize < 1) pageSize = 20;
            var q = _db.Queryable<PhotoCaptureRecord>();
            if (!string.IsNullOrEmpty(recipeName)) q = q.Where(r => r.RecipeName == recipeName);
            if (!string.IsNullOrEmpty(cameraName)) q = q.Where(r => r.CameraName == cameraName);
            if (!string.IsNullOrEmpty(productSN)) q = q.Where(r => r.ProductSN == productSN);
            if (start.HasValue) q = q.Where(r => r.CaptureTime >= start.Value);
            if (end.HasValue) q = q.Where(r => r.CaptureTime <= end.Value);
            var total = await q.CountAsync();
            var list = await q.OrderBy(r => r.CaptureTime, OrderByType.Desc).ToPageListAsync(pageIndex, pageSize);
            return (list, total);
        }
        #endregion

        #region 披头更换记录
        /// <summary>
        /// 记录披头更换
        /// </summary>
        /// <param name="record"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException"></exception>
        public async Task RecordHeadChangeAsync(HeadChangeRecord record)
        {
            if (record == null) throw new ArgumentNullException(nameof(record));
            if (record.ChangeTime == default(DateTime)) record.ChangeTime = DateTime.Now;
            record.Id = record.Id == Guid.Empty ? Guid.NewGuid() : record.Id;
            await _db.Insertable(record).ExecuteCommandAsync();
        }

        /// <summary>
        /// 查询披头更换记录
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="headBatchNumber"></param>
        /// <returns></returns>
        public async Task<IEnumerable<HeadChangeRecord>> QueryHeadChangeRecordsAsync(DateTime? start = null, DateTime? end = null, string headBatchNumber = null)
        {
            var q = _db.Queryable<HeadChangeRecord>();
            if (start.HasValue) q = q.Where(r => r.ChangeTime >= start.Value);
            if (end.HasValue) q = q.Where(r => r.ChangeTime <= end.Value);
            if (!string.IsNullOrEmpty(headBatchNumber)) q = q.Where(r => r.HeadBatchNumber == headBatchNumber);
            var list = await q.OrderBy(r => r.ChangeTime, OrderByType.Desc).ToListAsync();
            return list;
        }

        /// <summary>
        /// 分页查询披头更换记录
        /// </summary>
        public async Task<(IEnumerable<HeadChangeRecord> Items, int TotalCount)> QueryHeadChangeRecordsPagedAsync(DateTime? start = null, DateTime? end = null, string headBatchNumber = null, int pageIndex = 1, int pageSize = 20)
        {
            if (pageIndex < 1) pageIndex = 1;
            if (pageSize < 1) pageSize = 20;
            var q = _db.Queryable<HeadChangeRecord>();
            if (start.HasValue) q = q.Where(r => r.ChangeTime >= start.Value);
            if (end.HasValue) q = q.Where(r => r.ChangeTime <= end.Value);
            if (!string.IsNullOrEmpty(headBatchNumber)) q = q.Where(r => r.HeadBatchNumber == headBatchNumber);
            var total = await q.CountAsync();
            var list = await q.OrderBy(r => r.ChangeTime, OrderByType.Desc).ToPageListAsync(pageIndex, pageSize);
            return (list, total);
        }

        /// <summary>
        /// 获取最新的一条披头更换记录
        /// </summary>
        /// <returns></returns>
        public async Task<HeadChangeRecord> GetLatestHeadChangeRecordAsync()
        {
            var recs = await _db.Queryable<HeadChangeRecord>().OrderBy(r => r.ChangeTime, OrderByType.Desc).Take(1).ToListAsync();
            return recs.FirstOrDefault();
        }

        /// <summary>
        /// 对最新披头的锁付次数进行递增并返回更新后的次数（原子操作）。
        /// 若不存在记录则创建一条新记录并返回 1。
        /// </summary>
        public async Task<int> IncrementLatestHeadLockCountAsync(string operatorName = null)
        {
            // 使用事务保证原子性
            try
            {
                _db.Ado.BeginTran();
                // 获取最新记录（排他查询）
                var latest = _db.Queryable<HeadChangeRecord>().OrderBy(r => r.ChangeTime, OrderByType.Desc).Take(1).First();
                if (latest == null)
                {
                    var newRec = new HeadChangeRecord
                    {
                        ChangeTime = DateTime.Now,
                        HeadBatchNumber = string.Empty,
                        CurrentLockCount = 1,
                        OperatorName = operatorName
                    };
                    await _db.Insertable(newRec).ExecuteCommandAsync();
                    _db.Ado.CommitTran();
                    return 1;
                }

                latest.CurrentLockCount += 1;
                if (!string.IsNullOrEmpty(operatorName)) latest.OperatorName = operatorName;
                await _db.Updateable(latest).ExecuteCommandAsync();
                _db.Ado.CommitTran();
                return latest.CurrentLockCount;
            }
            catch
            {
                try { _db.Ado.RollbackTran(); } catch { }
                throw;
            }
        }
        #endregion

        #region 吸嘴更换记录
        /// <summary>
        /// 记录喷嘴更换记录。
        /// </summary>
        /// <param name="record"></param>
        /// <returns></returns>
        public async Task RecordNozzleChangeAsync(NozzleChangeRecord record)
        {
            if (record == null) throw new ArgumentNullException(nameof(record));
            if (record.ChangeTime == default(DateTime)) record.ChangeTime = DateTime.Now;
            record.Id = record.Id == Guid.Empty ? Guid.NewGuid() : record.Id;
            await _db.Insertable(record).ExecuteCommandAsync();
        }

        /// <summary>
        /// 查询喷嘴更换记录，可按时间段或喷嘴批号过滤（所有参数可选）。
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="nozzleBatchNumber"></param>
        /// <returns></returns>
        public async Task<IEnumerable<NozzleChangeRecord>> QueryNozzleChangeRecordsAsync(DateTime? start = null, DateTime? end = null, string nozzleBatchNumber = null)
        {
            var q = _db.Queryable<NozzleChangeRecord>();
            if (start.HasValue) q = q.Where(r => r.ChangeTime >= start.Value);
            if (end.HasValue) q = q.Where(r => r.ChangeTime <= end.Value);
            if (!string.IsNullOrEmpty(nozzleBatchNumber)) q = q.Where(r => r.NozzleBatchNumber == nozzleBatchNumber);
            var list = await q.OrderBy(r => r.ChangeTime, OrderByType.Desc).ToListAsync();
            return list;
        }

        /// <summary>
        /// 获取最新的一条喷嘴更换记录（按 ChangeTime 降序）。
        /// </summary>
        /// <returns></returns>
        public async Task<NozzleChangeRecord> GetLatestNozzleChangeRecordAsync()
        {
            var recs = await _db.Queryable<NozzleChangeRecord>().OrderBy(r => r.ChangeTime, OrderByType.Desc).Take(1).ToListAsync();
            return recs.FirstOrDefault();
        }

        /// <summary>
        /// 吸嘴的锁付次数进行递增并返回更新后的次数（如果不存在最新记录，则创建一条新的记录并返回 1）。
        /// </summary>
        /// <param name="operatorName"></param>
        /// <returns></returns>
        public async Task<int> IncrementLatestNozzleLockCountAsync(string operatorName = null)
        {
            try
            {
                _db.Ado.BeginTran();
                var latest = _db.Queryable<NozzleChangeRecord>().OrderBy(r => r.ChangeTime, OrderByType.Desc).Take(1).First();
                if (latest == null)
                {
                    var newRec = new NozzleChangeRecord
                    {
                        ChangeTime = DateTime.Now,
                        NozzleBatchNumber = string.Empty,
                        CurrentLockCount = 1,
                        OperatorName = operatorName
                    };
                    await _db.Insertable(newRec).ExecuteCommandAsync();
                    _db.Ado.CommitTran();
                    return 1;
                }

                latest.CurrentLockCount += 1;
                if (!string.IsNullOrEmpty(operatorName)) latest.OperatorName = operatorName;
                await _db.Updateable(latest).ExecuteCommandAsync();
                _db.Ado.CommitTran();
                return latest.CurrentLockCount;
            }
            catch
            {
                try { _db.Ado.RollbackTran(); } catch { }
                throw;
            }
        }

        /// <summary>
        /// 分页查询喷嘴更换记录
        /// </summary>
        public async Task<(IEnumerable<NozzleChangeRecord> Items, int TotalCount)> QueryNozzleChangeRecordsPagedAsync(DateTime? start = null, DateTime? end = null, string nozzleBatchNumber = null, int pageIndex = 1, int pageSize = 20)
        {
            if (pageIndex < 1) pageIndex = 1;
            if (pageSize < 1) pageSize = 20;
            var q = _db.Queryable<NozzleChangeRecord>();
            if (start.HasValue) q = q.Where(r => r.ChangeTime >= start.Value);
            if (end.HasValue) q = q.Where(r => r.ChangeTime <= end.Value);
            if (!string.IsNullOrEmpty(nozzleBatchNumber)) q = q.Where(r => r.NozzleBatchNumber == nozzleBatchNumber);
            var total = await q.CountAsync();
            var list = await q.OrderBy(r => r.ChangeTime, OrderByType.Desc).ToPageListAsync(pageIndex, pageSize);
            return (list, total);
        }
        #endregion


        #region 生成测试数据

        //定义一个方法来生成一些测试的数据，测试数据库写入和查询功能
        public async Task GenerateTestDataAsync()
        {
            //生成一些喷嘴更换记录的测试数据
            for (int i = 0; i < 10; i++)
            {
                var record = new NozzleChangeRecord
                {
                    ChangeTime = DateTime.Now.AddMinutes(-i * 10),
                    NozzleBatchNumber = $"Batch-{i}",
                    CurrentLockCount = i,
                    OperatorName = $"Operator-{i}"
                };
                await RecordNozzleChangeAsync(record);
            }

            //生成一些披头更换记录的测试数据
            for (int i = 0; i < 10; i++)
            {
                var record = new HeadChangeRecord
                {
                    ChangeTime = DateTime.Now.AddMinutes(-i * 15),
                    HeadBatchNumber = $"HeadBatch-{i}",
                    CurrentLockCount = i,
                    OperatorName = $"HeadOperator-{i}"
                };
                await RecordHeadChangeAsync(record);
            }

            //生成一些螺丝供料器批次更换记录的测试数据
            for (int i = 0; i < 10; i++)
            {
                var record = new ScrewFeederBatchRecord
                {
                    ChangeTime = DateTime.Now.AddMinutes(-i * 20),
                    BatchNumber = $"ScrewBatch-{i}",
                    FeederNumber = i + 1
                };
                await RecordScrewFeederBatchAsync(record);
            }

            //生成一些生产记录的测试数据
            for (int i = 0; i < 50; i++)
            {
                var record = new ProductionRecord
                {
                    Id = Guid.NewGuid(),
                    ProductName = "产品1",
                    Category = (i % 2 == 0) ? "OK" : "NG",
                    Quantity = 1,
                    Timestamp = DateTime.Now.AddMinutes(-i * 5),
                    UserName = "TestUser",
                    Remark = $"TotalSeconds:1.10"
                };
                await RecordProductionAsync(record);
            }

            //生成一些锁付结果的测试数据
            for (int i = 0; i < 200; i++)
            {
                var result = new LockResult(i % 4, $"PN-{i % 5}", 10.5f, 0.3f, (i % 3 != 0))
                {
                    ProductName = "产品1",
                    Timestamp = DateTime.Now.AddMinutes(-i * 3),
                };
                await RecordLockResultAsync(result);
            }

            //生成一些相机拍照记录的测试数据
            for (int i = 0; i < 20; i++)
            {
                try
                {
                    var record = new PhotoCaptureRecord
                    {
                        Id = Guid.NewGuid(),
                        RecipeName = "产品1",
                        CameraName = $"下相机",
                        ProductSN = $"SN-{i}",
                        IsSuccess = (i % 2 == 0),
                        CaptureTime = DateTime.Now.AddMinutes(-i * 7),
                        ImagePath = $"/images/photo_{i}.jpg"
                    };
                    await RecordPhotoCaptureAsync(record);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                }

            }

        }

        #endregion
    }
}