Display_D_HP/TeamAAS-VM/Data/SystemDatabaseService.cs

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;
        }

        #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.UtcNow,
                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.UtcNow,
                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.UtcNow;
            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)
        {
            DateTime todayStart;
            DateTime todayEnd;

            if (DateTime.UtcNow.Hour >= 1 && DateTime.UtcNow.Hour < 13)
            {
                // 白班
                todayStart = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day, 1, 0, 0, DateTimeKind.Utc);
                todayEnd = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day, 13, 0, 0, DateTimeKind.Utc);
            }
            else if (DateTime.UtcNow.Hour >= 13)
            {
                // 当天夜班
                todayStart = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day, 13, 0, 0, DateTimeKind.Utc);
                todayEnd = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day + 1, 1, 0, 0, DateTimeKind.Utc);
            }
            else
            {
                // 凌晨属于前一天夜班
                todayStart = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day - 1, 13, 0, 0, DateTimeKind.Utc);
                todayEnd = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day, 1, 0, 0, DateTimeKind.Utc);
            }

            //按照产品名称和时间范围查询生产记录的总数
            //var total = await _db.Queryable<ProductionRecord>()
            //    .Where(r => r.ProductName == productName && r.Timestamp >= todayStart && r.Timestamp < todayEnd)
            //    .SumAsync(r => r.Quantity);

            var total = await _db.Queryable<ProductionRecord>()
                .Where(r => r.ProductName == productName && r.Timestamp >= todayStart && r.Timestamp < todayEnd)
                .Select(r => r.ProductCode)
                .Distinct()
                .CountAsync();
            return total;
        }

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

        /// <summary>
        /// 获取所有已知分类相加起来的今天NG的产能
        /// </summary>
        /// <param name="productName"></param>
        /// <returns></returns>
        public async Task<int> GetNGProductionAsync(string productName)
        {
            DateTime todayStart;
            DateTime todayEnd;

            if (DateTime.UtcNow.Hour >= 1 && DateTime.UtcNow.Hour < 13)
            {
                // 白班
                todayStart = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day, 1, 0, 0, DateTimeKind.Utc);
                todayEnd = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day, 13, 0, 0, DateTimeKind.Utc);
            }
            else if (DateTime.UtcNow.Hour >= 13)
            {
                // 当天夜班
                todayStart = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day, 13, 0, 0, DateTimeKind.Utc);
                todayEnd = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day + 1, 1, 0, 0, DateTimeKind.Utc);
            }
            else
            {
                // 凌晨属于前一天夜班
                todayStart = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day - 1, 13, 0, 0, DateTimeKind.Utc);
                todayEnd = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day, 1, 0, 0, DateTimeKind.Utc);
            }

            //按照产品名称和时间范围查询生产记录的总数
            //var total = await _db.Queryable<ProductionRecord>()
            //    .Where(r => r.ProductName == productName && r.Timestamp >= todayStart && r.Timestamp < todayEnd && r.Category == "NG")
            //    .SumAsync(r => r.Quantity);

            var total = await _db.Queryable<ProductionRecord>()
                .Where(r => r.ProductName == productName && r.Timestamp >= todayStart && r.Timestamp < todayEnd && r.Category == "NG")
                .Select(r => r.ProductCode)
                .Distinct()
                .CountAsync();
            return total;
        }

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

            var now = DateTime.UtcNow;
            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.UtcNow;
            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.UtcNow;
            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.UtcNow;
            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.UtcNow;
            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;
        }

        #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.UtcNow;
            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

        // 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.UtcNow;
                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, 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);

            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, 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);

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

        /// <summary>
        /// 记录一次螺丝供料器批次更换记录
        /// </summary>
        public async Task RecordScrewFeederBatchAsync(ScrewFeederBatchRecord record)
        {
            if (record == null) throw new ArgumentNullException(nameof(record));
            if (record.ChangeTime == default(DateTime)) record.ChangeTime = DateTime.UtcNow;
            await _db.Insertable(record).ExecuteCommandAsync();
        }

        #region 产品和mes的绑定,自动切换产品
        /// <summary>
        /// 添加绑定信息
        /// </summary>
        /// <param name="record"></param>
        /// <param name="product"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException"></exception>
        public async Task InsertProWithMesAsync(ProductWithMes record, ProductModel product)
        {
            //record.Id = product.ID;
            record.ProductName = product.Name;
            await _db.Insertable(record).ExecuteCommandAsync();
        }

        /// <summary>
        /// 删除绑定信息
        /// </summary>
        /// <param name="record"></param>
        /// <param name="product"></param>
        /// <returns></returns>
        public async Task<int> DeleteProWithMesAsync(ProductModel product)
        {
            var v = await _db.Deleteable<ProductWithMes>().Where(r => r.ProductName == product.Name).ExecuteCommandAsync();
            return v;
        }

        /// <summary>
        /// 更新绑定信息
        /// </summary>
        /// <param name="record"></param>
        /// <param name="product"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException"></exception>
        public async Task<int> UpdateProductWithMesAsync(ProductWithMes record, ProductModel product)
        {
            if (record == null) throw new ArgumentNullException(nameof(record));
            var v = await _db.Updateable(record).Where(r => r.ProductName == product.Name).ExecuteCommandAsync();
            return v;
        }

        /// <summary>
        /// 查询绑定信息
        /// </summary>
        /// <param name="product"></param>
        /// <returns></returns>
        public async Task<ProductWithMes> QueryProductWithMesAsync(ProductModel product)
        {
            var total = await _db.Queryable<ProductWithMes>().Where(r => r.ProductName == product.Name).FirstAsync();
            return total;
        }

        /// <summary>
        /// 查询所有绑定信息
        /// </summary>
        /// <param name="product"></param>
        /// <returns></returns>
        public async Task<List<ProductWithMes>> QueryProductWithMesALLAsync()
        {
            var total = await _db.Queryable<ProductWithMes>().ToListAsync();
            return total;
        }

        /// <summary>
        /// 通过产品的编码和颜色查询产品的名称
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public async Task<string> QueryProductFromMesAsync(string code, string color)
        {
            var v = await _db.Queryable<ProductWithMes>().Where(r => r.ProductCode == code && r.ProductColor == color).FirstAsync();
            if (v == null || v.ProductName == null)
            {
                return "";
            }
            return v.ProductName;
        }
        #endregion
    }
}