TMTT/DataService/BatchService.cs

using Model;
using Newtonsoft.Json;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
public enum BatchOperationType
{
    Create,
    Update,
    Delete,
    Finish
}
public class BatchService
{
    public static bool CreateBatchLog(string batchID, User user, out string errorMessage)
    {
        errorMessage = null;

        if(string.IsNullOrEmpty(batchID))
        {
            errorMessage = "批次号不能为空";
            return false;
        }

        using (var conn = new SQLiteConnection(DatabaseHelper.ConnectionString))
        {
            conn.Open();

            // 检查是否存在未结束的批次
            string checkUnfinishedSql = @"
                SELECT COUNT(*) 
                FROM BatchLogs 
                WHERE EndTimestamp IS NULL";
            using (var cmd = new SQLiteCommand(checkUnfinishedSql, conn))
            {
                var count = (long)cmd.ExecuteScalar();
                if (count > 0)
                {
                    errorMessage = "存在未结束的批次，不允许创建新批次。";
                    return false;
                }
            }
            /*
            //查询最新一条批次是否已结束
            string checkLatestBatchSql = @" 
                SELECT EndTimestamp 
                FROM BatchLogs 
                ORDER BY StartTimestamp DESC 
                LIMIT 1";
            using (var cmd = new SQLiteCommand(checkLatestBatchSql, conn))
            {
                var result = cmd.ExecuteScalar();

                // 如果表为空（无任何批次），result 是 null → 允许创建
                if (result != null && result != DBNull.Value)
                {
                    // 最新批次已有 EndTimestamp → 已结束，允许新建
                }
                else if (result == null)
                {
                    // 表里没数据 → 允许创建第一个批次
                }
                else // result == DBNull.Value
                {
                    // 最新批次的 EndTimestamp 为 NULL → 未结束，禁止新建
                    errorMessage = "最新批次尚未结束，不允许创建新批次。";
                    return false;
                }
            }
            */

            // 检查数据库中是否已存在这些 BatchID
            string checkSql = "SELECT BatchID FROM BatchLogs WHERE BatchID = @id";

            using (var checkCmd = new SQLiteCommand(checkSql, conn))
            {
                checkCmd.Parameters.AddWithValue($"@id", batchID);
                using (var reader = checkCmd.ExecuteReader())
                {
                    /*
                    var existingIds = new List<string>();
                    while (reader.Read())
                    {
                        existingIds.Add(reader.GetString(0));
                    }
                    if (existingIds.Any())
                    {
                        errorMessage = $"批次号已存在，无法重复创建: {string.Join(", ", existingIds)}";
                        return false;
                    }
                    */
                    if(reader.Read())
                    {
                        errorMessage = $"批次号已存在，无法重复创建";
                        return false;
                    }
                }
            }

            // 2. 开始插入
            string insertSql = @"
            INSERT INTO BatchLogs (
                BatchID, 
                StartTimestamp,
                StartUserId, 
                StartUsername, 
                EndTimestamp,
                EndUserId,
                EndUsername,
                RecipeName,
                Details
            ) VALUES (
                @BatchID, 
                @StartTimestamp, 
                @StartUserId, 
                @StartUsername, 
                @EndTimestamp, 
                @EndUserId, 
                @EndUsername, 
                @RecipeName, 
                @Details
            )";

            using (var tran = conn.BeginTransaction())
            {
                using (var cmd = new SQLiteCommand(insertSql, conn, tran))
                {
                    // 添加参数占位符（只加一次）
                    cmd.Parameters.Add("@BatchID", DbType.String);
                    cmd.Parameters.Add("@StartTimestamp", DbType.DateTime);
                    cmd.Parameters.Add("@StartUserId", DbType.Int32);
                    cmd.Parameters.Add("@StartUsername", DbType.String);
                    cmd.Parameters.Add("@EndTimestamp", DbType.DateTime);
                    cmd.Parameters.Add("@EndUserId", DbType.Int32);
                    cmd.Parameters.Add("@EndUsername", DbType.String);
                    cmd.Parameters.Add("@RecipeName", DbType.String);
                    cmd.Parameters.Add("@Details", DbType.String);

                    // 更新参数值
                    cmd.Parameters["@BatchID"].Value = batchID;
                    cmd.Parameters["@StartTimestamp"].Value = DateTime.Now;
                    cmd.Parameters["@StartUserId"].Value = user.Id;
                    cmd.Parameters["@StartUsername"].Value = user.Username;
                    cmd.Parameters["@EndTimestamp"].Value = DBNull.Value;
                    cmd.Parameters["@EndUserId"].Value = DBNull.Value;
                    cmd.Parameters["@EndUsername"].Value = DBNull.Value;
                    cmd.Parameters["@RecipeName"].Value = DBNull.Value;
                    cmd.Parameters["@Details"].Value = DBNull.Value;//JsonConvert.SerializeObject(batch)

                    cmd.ExecuteNonQuery();

                }
                tran.Commit();
            }
        }
        return true;
    }
    public static bool DeleteBatchByBatchId(string batchId, out string errorMessage)
    {
        errorMessage = null;

        if (string.IsNullOrWhiteSpace(batchId))
        {
            errorMessage = "批次号不能为空。";
            return false;
        }

        using (var conn = new SQLiteConnection(DatabaseHelper.ConnectionString))
        {
            conn.Open();

            // 1. 检查批次是否存在
            string checkSql = "SELECT COUNT(1) FROM BatchLogs WHERE BatchID = @BatchID";
            using (var checkCmd = new SQLiteCommand(checkSql, conn))
            {
                checkCmd.Parameters.AddWithValue("@BatchID", batchId);
                int count = Convert.ToInt32(checkCmd.ExecuteScalar());

                if (count == 0)
                {
                    errorMessage = $"批次号 '{batchId}' 不存在，无法删除。";
                    return false;
                }
            }

            // 2. 执行删除
            string deleteSql = "DELETE FROM BatchLogs WHERE BatchID = @BatchID";
            using (var deleteCmd = new SQLiteCommand(deleteSql, conn))
            {
                deleteCmd.Parameters.AddWithValue("@BatchID", batchId);
                int rowsAffected = deleteCmd.ExecuteNonQuery();

                if (rowsAffected == 0)
                {
                    errorMessage = "删除失败：记录在检查后被其他操作删除或修改。";
                    return false;
                }
            }
        }

        return true;
    }
    public static bool GetLatestBatchID(out string batchID,out bool finished,out string errorMessage)
    {
        batchID = null;
        finished = false;
        errorMessage = null;
        using (var conn = new SQLiteConnection(DatabaseHelper.ConnectionString))
        {
            conn.Open();

            // 1. 查询最新的批次（按 StartTimestamp 倒序）
            string selectLatestSql = @"
            SELECT BatchID, EndTimestamp 
            FROM BatchLogs 
            ORDER BY StartTimestamp DESC 
            LIMIT 1";

            object endTimestampValue;

            using (var cmd = new SQLiteCommand(selectLatestSql, conn))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.Read())
                    {
                        errorMessage = "没有批次记录";
                        return false;
                    }

                    batchID = reader.GetString(0);
                    endTimestampValue = reader["EndTimestamp"]; // 可能是 DBNull 或 DateTime
                }
            }

            // 2. 检查是否已结束
            if (endTimestampValue != DBNull.Value)
            {
                finished = true;//批次已结束
            }
            return true;
        }
    }
    public static bool FinishLatestBatch(Batch batch, User user, out string errorMessage)
    {
        errorMessage = null;

        using (var conn = new SQLiteConnection(DatabaseHelper.ConnectionString))
        {
            conn.Open();

            // 1. 查询最新的批次（按 StartTimestamp 倒序）
            string selectLatestSql = @"
            SELECT BatchID, EndTimestamp 
            FROM BatchLogs 
            ORDER BY StartTimestamp DESC 
            LIMIT 1";

            string latestBatchId;
            object endTimestampValue;

            using (var cmd = new SQLiteCommand(selectLatestSql, conn))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.Read())
                    {
                        errorMessage = "没有批次记录，无需结束。";
                        return false;
                    }

                    latestBatchId = reader.GetString(0);
                    endTimestampValue = reader["EndTimestamp"]; // 可能是 DBNull 或 DateTime
                }
            }

            // 2. 检查是否已结束
            if (endTimestampValue != DBNull.Value)
            {
                errorMessage = $"最新批次（{latestBatchId}）已结束，不能重复结束。";
                return false;
            }

            // 3. 更新该批次为结束状态
            string updateSql = @"
            UPDATE BatchLogs 
            SET 
                EndTimestamp = @EndTimestamp,
                EndUserId = @EndUserId,
                EndUsername = @EndUsername,
                RecipeName = @RecipeName,
                Details=@Details
            WHERE BatchID = @BatchID";

            using (var cmd = new SQLiteCommand(updateSql, conn))
            {
                cmd.Parameters.AddWithValue("@EndTimestamp", DateTime.Now);
                cmd.Parameters.AddWithValue("@EndUserId", user.Id);
                cmd.Parameters.AddWithValue("@EndUsername", user.Username);
                cmd.Parameters.AddWithValue("@RecipeName", batch.RecipeName);
                cmd.Parameters.AddWithValue("@Details", JsonConvert.SerializeObject(batch));
                cmd.Parameters.AddWithValue("@BatchID", latestBatchId);

                int rowsAffected = cmd.ExecuteNonQuery();
                if (rowsAffected == 0)
                {
                    errorMessage = "更新失败：批次记录在查询后被删除或修改。";
                    return false;
                }
                batch.EndTime = DateTime.Now;
            }
        }
        return true;
    }
    /// <summary>
    /// 获得最近的批次记录，有可能是未完成，也可能是已经完成，通过Batch.Finished判断。
    /// </summary>
    /// <param name="errorMessage"></param>
    /// <returns></returns>
    public static Batch GetLatestBatch(out string errorMessage)
    {
        errorMessage = null;

        using (var conn = new SQLiteConnection(DatabaseHelper.ConnectionString))
        {
            conn.Open();
            string checkBatchCountSql = @"
                SELECT COUNT(*) 
                FROM BatchLogs";
                
            using (var cmd = new SQLiteCommand(checkBatchCountSql, conn))
            {
                var count = (long)cmd.ExecuteScalar();
                if (count == 0)
                {
                    errorMessage = "没有任何批次记录。";
                    return null;
                }
            }
            string sql = @"
            SELECT 
                BatchID,
                StartTimestamp,
                StartUserId,
                StartUsername,
                EndTimestamp,
                EndUserId,
                EndUsername,
                RecipeName,
                Details
            FROM BatchLogs
            ORDER BY StartTimestamp DESC 
            LIMIT 1";

            using (var cmd = new SQLiteCommand(sql, conn))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        // 从 Details 反序列化基础属性（如果 Details 存了完整 Batch）
                        string detailsJson = reader["Details"] as string;
                        Batch batch;
                        if (!string.IsNullOrEmpty(detailsJson))
                        {
                            batch = JsonConvert.DeserializeObject<Batch>(detailsJson);
                        }
                        else
                        {
                            batch = new Batch(); // 或抛异常，看业务
                        }

                        batch.BatchID = reader["BatchID"] as string;
                        batch.StartTime = (DateTime)reader["StartTimestamp"];
                        batch.StartUsername = reader["StartUsername"] as string;
                        if (reader["EndTimestamp"] != DBNull.Value)
                        {
                            batch.EndTime = (DateTime)reader["EndTimestamp"];
                            batch.EndUsername = reader["EndUsername"] as string;
                            batch.RecipeName = reader["RecipeName"] as string;
                            batch.Finished = true;
                        }
                        else
                            batch.Finished = false;
                        return batch;
                    }
                }
            }
        }

        return null;
    }
    public static List<Batch> GetFinishedBatches(DateTime startTime,DateTime endTime,string batchIdFilter = null,string recipeNameFilter = null)
    {
        var batches = new List<Batch>();

        if (startTime > endTime)
            throw new ArgumentException("开始时间不能晚于结束时间。");

        using (var conn = new SQLiteConnection(DatabaseHelper.ConnectionString))
        {
            conn.Open();

            // 构建动态 WHERE 条件
            var whereConditions = new List<string> { "EndTimestamp IS NOT NULL" };
            var parameters = new List<(string name, object value)>();

            // 时间范围（始终存在）
            whereConditions.Add("EndTimestamp BETWEEN @Start AND @End");
            parameters.Add(("@Start", startTime.ToString("yyyy-MM-dd HH:mm:ss")));
            parameters.Add(("@End", endTime.ToString("yyyy-MM-dd HH:mm:ss")));

            // 批次号模糊匹配（可选）
            if (!string.IsNullOrWhiteSpace(batchIdFilter))
            {
                whereConditions.Add("BatchID LIKE @BatchId");
                parameters.Add(("@BatchId", $"%{batchIdFilter}%"));
            }

            // RecipeName 模糊匹配（可选）
            if (!string.IsNullOrWhiteSpace(recipeNameFilter))
            {
                whereConditions.Add("RecipeName LIKE @RecipeName");
                parameters.Add(("@RecipeName", $"%{recipeNameFilter}%"));
            }

            string sql = $@"
            SELECT 
                BatchID,
                StartTimestamp,
                StartUsername,
                EndTimestamp,
                EndUsername,
                RecipeName,
                Details
            FROM BatchLogs
            WHERE {string.Join(" AND ", whereConditions)}
            ORDER BY EndTimestamp DESC";

            using (var cmd = new SQLiteCommand(sql, conn))
            {
                // 添加所有参数
                foreach (var (name, value) in parameters)
                {
                    cmd.Parameters.AddWithValue(name, value);
                }

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        // 从 Details 反序列化基础属性（如果 Details 存了完整 Batch）
                        string detailsJson = reader["Details"] as string;
                        Batch batch;
                        if (!string.IsNullOrEmpty(detailsJson))
                        {
                            batch = JsonConvert.DeserializeObject<Batch>(detailsJson);
                        }
                        else
                        {
                            batch = new Batch(); // 或抛异常，看业务
                        }

                        // 覆盖关键字段（确保与数据库一致）
                        batch.BatchID = reader["BatchID"] as string;
                        batch.StartTime = (DateTime)reader["StartTimestamp"];
                        batch.StartUsername = reader["StartUsername"] as string;
                        batch.EndTime = (DateTime)reader["EndTimestamp"];
                        batch.EndUsername = reader["EndUsername"] as string;
                        batch.RecipeName = reader["RecipeName"] as string;
                        batch.Finished = true;
                        batches.Add(batch);
                    }
                }
            }
        }
        return batches;
    }
    public static List<Batch> GetFinishedBatches(DateTime startTime, DateTime endTime)
    {
        return GetFinishedBatches(startTime, endTime, null,null);
    }
    private bool CreateBatchLogs(IEnumerable<string> batcheIDs, User user, out string errorMessage)
    {
        errorMessage = null;

        // 1. 检查是否有重复 BatchID
        var batchIds = batcheIDs.Select(b => b).ToList();
        if (batchIds.Count != batchIds.Distinct().Count())
        {
            errorMessage = "输入的批次列表中包含重复的批次号。";
            return false;
        }

        using (var conn = new SQLiteConnection(DatabaseHelper.ConnectionString))
        {
            conn.Open();

            // 检查数据库中是否已存在这些 BatchID
            string checkSql = "SELECT BatchID FROM BatchLogs WHERE BatchID IN (" +
                              string.Join(",", batchIds.Select((_, i) => $"@id{i}")) + ")";

            using (var checkCmd = new SQLiteCommand(checkSql, conn))
            {
                for (int i = 0; i < batchIds.Count; i++)
                {
                    checkCmd.Parameters.AddWithValue($"@id{i}", batchIds[i]);
                }

                using (var reader = checkCmd.ExecuteReader())
                {
                    var existingIds = new List<string>();
                    while (reader.Read())
                    {
                        existingIds.Add(reader.GetString(0));
                    }

                    if (existingIds.Any())
                    {
                        errorMessage = $"以下批次号已存在，无法重复创建: {string.Join(", ", existingIds)}";
                        return false;
                    }
                }
            }

            // 2. 开始插入
            string insertSql = @"
            INSERT INTO BatchLogs (
                BatchID, 
                StartTimestamp,
                StartUserId, 
                StartUsername, 
                EndTimestamp,
                EndUserId,
                EndUsername,
                RecipeName,
                Details
            ) VALUES (
                @BatchID, 
                @StartTimestamp, 
                @StartUserId, 
                @StartUsername, 
                @EndTimestamp, 
                @EndUserId, 
                @EndUsername, 
                @RecipeName, 
                @Details
            )";

            using (var tran = conn.BeginTransaction())
            {
                using (var cmd = new SQLiteCommand(insertSql, conn, tran))
                {
                    // 添加参数占位符（只加一次）
                    cmd.Parameters.Add("@BatchID", DbType.String);
                    cmd.Parameters.Add("@StartTimestamp", DbType.DateTime);
                    cmd.Parameters.Add("@StartUserId", DbType.Int32);
                    cmd.Parameters.Add("@StartUsername", DbType.String);
                    cmd.Parameters.Add("@EndTimestamp", DbType.DateTime);
                    cmd.Parameters.Add("@EndUserId", DbType.Int32);
                    cmd.Parameters.Add("@EndUsername", DbType.String);
                    cmd.Parameters.Add("@RecipeName", DbType.String);
                    cmd.Parameters.Add("@Details", DbType.String);

                    foreach (var id in batchIds)
                    {
                        // 更新参数值
                        cmd.Parameters["@BatchID"].Value = id;
                        cmd.Parameters["@StartTimestamp"].Value = DateTime.Now;
                        cmd.Parameters["@StartUserId"].Value = user.Id;
                        cmd.Parameters["@StartUsername"].Value = user.Username;
                        cmd.Parameters["@EndTimestamp"].Value = DBNull.Value;
                        cmd.Parameters["@EndUserId"].Value = DBNull.Value;
                        cmd.Parameters["@EndUsername"].Value = DBNull.Value;
                        cmd.Parameters["@RecipeName"].Value = DBNull.Value;
                        cmd.Parameters["@Details"].Value =DBNull.Value ;//JsonConvert.SerializeObject(batch)

                        cmd.ExecuteNonQuery();
                    }
                }

                tran.Commit();
            }
        }

        return true;
    }
   
}