using LB_SmartVisionCommon;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LB_SmartVision.SQL
{
    /// <summary>
    /// 数据访问层
    /// </summary>
    public class RecordProductDataRepository
    {
        private static object recordProductDataRepositoryObject = new object();
        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="record">生产数据记录</param>
        /// <returns>是否添加成功</returns>
        public static bool AddRecord(RecordProductData record)
        {
            Stopwatch stopwatch = Stopwatch.StartNew();
            lock (recordProductDataRepositoryObject)
            {
                stopwatch.Start();
                try
                {
                    using (var connection = DatabaseRecordProductDataHelper.GetConnection())
                    {
                        connection.Open();
                        string sql = @"
                                     INSERT INTO RecordProductData 
                                     (ProductName, ProductSN, InspectionOperator, NGType, NGSize,DetectionTime,CameraInspection)
                                     VALUES 
                                     (@ProductName, @ProductSN, @InspectionOperator, @NGType, @NGSize, @DetectionTime, @CameraInspection)";
                        using (var cmd = new MySqlCommand(sql, connection))
                        {
                            cmd.Parameters.AddWithValue("@ProductName", record.ProductName);
                            cmd.Parameters.AddWithValue("@ProductSN", record.ProductSN);
                            cmd.Parameters.AddWithValue("@InspectionOperator", record.InspectionOperator);
                            cmd.Parameters.AddWithValue("@NGType", record.NGType);
                            cmd.Parameters.AddWithValue("@NGSize", record.NGSize);
                            cmd.Parameters.AddWithValue("@DetectionTime", record.DetectionTime);
                            cmd.Parameters.AddWithValue("@CameraInspection", record.CameraInspection);
                            int rowsAffected = cmd.ExecuteNonQuery();
                            stopwatch.Stop();
                            Task.Factory.StartNew(() =>
                            {
                                LogHelper.Info($"添加信息耗时: {stopwatch.ElapsedMilliseconds}ms");
                            });
                            return rowsAffected > 0;
                        }
                    }
                }
                catch (Exception ex)
                {
                    AsyncLogHelper.Error($"添加记录失败: {ex.Message}");
                    return false;
                }
            }
        }
        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <returns>List<RecordProductData></returns>
        public static List<RecordProductData> GetAllRecords()
        {
            lock (recordProductDataRepositoryObject)
            {
                var records = new List<RecordProductData>();
                try
                {
                    using (var connection = DatabaseRecordProductDataHelper.GetConnection())
                    {
                        connection.Open();
                        string sql = "SELECT * FROM RecordProductData ORDER BY CreatedDate DESC";
                        using (var cmd = new MySqlCommand(sql, connection))
                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                records.Add(MapReaderToRecord(reader));
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    AsyncLogHelper.Error($"查询记录失败: {ex.Message}");
                }
                return records;
            }
        }
        /// <summary>
        /// 根据产品编号查询
        /// </summary>
        /// <param name="productSN">产品编号</param>
        /// <returns>List<RecordProductData></returns>
        public static List<RecordProductData> GetRecordsByProductNumber(string productSN)
        {
            lock (recordProductDataRepositoryObject)
            {
                var records = new List<RecordProductData>();
                try
                {
                    using (var connection = DatabaseRecordProductDataHelper.GetConnection())
                    {
                        connection.Open();
                        string sql = "SELECT * FROM RecordProductData WHERE productSN = @productSN ORDER BY CreatedDate DESC";
                        using (var cmd = new MySqlCommand(sql, connection))
                        {
                            cmd.Parameters.AddWithValue("@productSN", productSN);
                            using (var reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    records.Add(MapReaderToRecord(reader));
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    AsyncLogHelper.Error($"根据产品编号查询失败: {ex.Message}");
                }
                return records;
            }
        }
        /// <summary>
        /// 根据操作者查询
        /// </summary>
        /// <param name="inspectionOperator">操作者</param>
        /// <returns>List<RecordProductData></returns>
        public static List<RecordProductData> GetRecordsByOperator(string inspectionOperator)
        {
            lock (recordProductDataRepositoryObject)
            {
                var records = new List<RecordProductData>();
                try
                {
                    using (var connection = DatabaseRecordProductDataHelper.GetConnection())
                    {
                        connection.Open();
                        string sql = "SELECT * FROM RecordProductData WHERE InspectionOperator = @InspectionOperator ORDER BY CreatedDate DESC";
                        using (var cmd = new MySqlCommand(sql, connection))
                        {
                            cmd.Parameters.AddWithValue("@InspectionOperator", inspectionOperator);
                            using (var reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    records.Add(MapReaderToRecord(reader));
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    AsyncLogHelper.Error($"根据操作者查询失败: {ex.Message}");
                }
                return records;
            }
        }
        /// <summary>
        /// 根据时间段查询 - 使用CreatedDate
        /// </summary>
        /// <param name="startTime">起始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns>List<RecordProductData></returns>
        public static List<RecordProductData> GetRecordsByTimeRange(DateTime startTime, DateTime endTime)
        {
            lock (recordProductDataRepositoryObject)
            {
                var records = new List<RecordProductData>();
                try
                {
                    using (var connection = DatabaseRecordProductDataHelper.GetConnection())
                    {
                        connection.Open();
                        string sql = @"
                                     SELECT * FROM RecordProductData 
                                     WHERE CreatedDate BETWEEN @StartTime AND @EndTime 
                                     ORDER BY CreatedDate DESC";
                        using (var cmd = new MySqlCommand(sql, connection))
                        {
                            cmd.Parameters.AddWithValue("@StartTime", startTime);
                            cmd.Parameters.AddWithValue("@EndTime", endTime.AddDays(1).AddSeconds(-1)); // 包含结束日期的全天
                            using (var reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    records.Add(MapReaderToRecord(reader));
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    AsyncLogHelper.Error($"根据时间段查询失败: {ex.Message}");
                }
                return records;
            }
        }

        /// <summary>
        /// 组合查询：产品编号 + 时间段
        /// </summary>
        /// <param name="productSN">产品编号</param>
        /// <param name="startTime">起始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns>List<RecordProductData></returns>
        public static List<RecordProductData> GetRecordsByProductAndTimeRange(string productSN, DateTime startTime, DateTime endTime)
        {
            lock (recordProductDataRepositoryObject)
            {
                var records = new List<RecordProductData>();
                try
                {
                    using (var connection = DatabaseRecordProductDataHelper.GetConnection())
                    {
                        connection.Open();
                        string sql = @"
                                     SELECT * FROM RecordProductData 
                                     WHERE productSN = @productSN 
                                     AND CreatedDate BETWEEN @StartTime AND @EndTime 
                                     ORDER BY CreatedDate DESC";
                        using (var cmd = new MySqlCommand(sql, connection))
                        {
                            cmd.Parameters.AddWithValue("@productSN", productSN);
                            cmd.Parameters.AddWithValue("@StartTime", startTime);
                            cmd.Parameters.AddWithValue("@EndTime", endTime.AddDays(1).AddSeconds(-1));
                            using (var reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    records.Add(MapReaderToRecord(reader));
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    AsyncLogHelper.Error($"组合查询失败: {ex.Message}");
                }
                return records;
            }
        }
        /// <summary>
        /// 组合查询：操作者 + 时间段
        /// </summary>
        /// <param name="inspectionOperator">操作者</param>
        /// <param name="startTime">起始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns>List<RecordProductData></returns>
        public static List<RecordProductData> GetRecordsByOperatorAndTimeRange(string inspectionOperator, DateTime startTime, DateTime endTime)
        {
            lock (recordProductDataRepositoryObject)
            {
                var records = new List<RecordProductData>();
                try
                {
                    using (var connection = DatabaseRecordProductDataHelper.GetConnection())
                    {
                        connection.Open();
                        string sql = @"
                                     SELECT * FROM RecordProductData 
                                     WHERE InspectionOperator = @InspectionOperator 
                                     AND CreatedDate BETWEEN @StartTime AND @EndTime 
                                     ORDER BY CreatedDate DESC";
                        using (var cmd = new MySqlCommand(sql, connection))
                        {
                            cmd.Parameters.AddWithValue("@InspectionOperator", inspectionOperator);
                            cmd.Parameters.AddWithValue("@StartTime", startTime);
                            cmd.Parameters.AddWithValue("@EndTime", endTime.AddDays(1).AddSeconds(-1));
                            using (var reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    records.Add(MapReaderToRecord(reader));
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    AsyncLogHelper.Error($"组合查询失败: {ex.Message}");
                }
                return records;
            }
        }
        /// <summary>
        /// 高级组合查询：支持多个条件
        /// </summary>
        /// <param name="productSN">产品编号</param>
        /// <param name="operatorName">操作者</param>
        /// <param name="startTime">起始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns>List<RecordProductData></returns>
        public static List<RecordProductData> GetRecordsByMultipleConditions(
            string productSN = null,
            string operatorName = null,
            DateTime? startTime = null,
            DateTime? endTime = null)
        {
            lock (recordProductDataRepositoryObject)
            {
                var records = new List<RecordProductData>();
                try
                {
                    using (var connection = DatabaseRecordProductDataHelper.GetConnection())
                    {
                        connection.Open();
                        var sql = "SELECT * FROM RecordProductData WHERE 1=1";
                        var parameters = new List<MySqlParameter>();
                        if (!string.IsNullOrEmpty(productSN))
                        {
                            sql += " AND ProductSN = @ProductSN";
                            parameters.Add(new MySqlParameter("@ProductSN", productSN));
                        }
                        if (!string.IsNullOrEmpty(operatorName))
                        {
                            sql += " AND MeasurementOperator = @InspectionOperator";
                            parameters.Add(new MySqlParameter("@InspectionOperator", operatorName));
                        }
                        if (startTime.HasValue)
                        {
                            sql += " AND CreatedDate >= @StartTime";
                            parameters.Add(new MySqlParameter("@StartTime", startTime.Value));
                        }
                        if (endTime.HasValue)
                        {
                            sql += " AND CreatedDate <= @EndTime";
                            parameters.Add(new MySqlParameter("@EndTime", endTime.Value.AddDays(1).AddSeconds(-1)));
                        }
                        sql += " ORDER BY CreatedDate DESC";
                        using (var cmd = new MySqlCommand(sql, connection))
                        {
                            foreach (var param in parameters)
                            {
                                cmd.Parameters.Add(param);
                            }
                            using (var reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    records.Add(MapReaderToRecord(reader));
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    AsyncLogHelper.Error($"高级组合查询失败: {ex.Message}");
                }
                return records;
            }
        }
        /// <summary>
        /// 统计查询：获取某个时间段内的记录数量
        /// </summary>
        /// <param name="startTime">起始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns>记录数量</returns>
        public static int GetRecordCountByTimeRange(DateTime startTime, DateTime endTime)
        {
            lock (recordProductDataRepositoryObject)
            {
                try
                {
                    using (var connection = DatabaseRecordProductDataHelper.GetConnection())
                    {
                        connection.Open();
                        string sql = @"
                                     SELECT COUNT(*) FROM RecordProductData 
                                     WHERE CreatedDate BETWEEN @StartTime AND @EndTime";
                        using (var cmd = new MySqlCommand(sql, connection))
                        {
                            cmd.Parameters.AddWithValue("@StartTime", startTime);
                            cmd.Parameters.AddWithValue("@EndTime", endTime.AddDays(1).AddSeconds(-1));
                            return Convert.ToInt32(cmd.ExecuteScalar());
                        }
                    }
                }
                catch (Exception ex)
                {
                    AsyncLogHelper.Error($"统计查询失败: {ex.Message}");
                    return 0;
                }
            }
        }
        /// <summary>
        /// 辅助方法：将数据读取器映射到RecordProductData对象
        /// </summary>
        /// <param name="reader">MySqlDataReader reader</param>
        /// <returns>RecordProductData</returns>
        private static RecordProductData MapReaderToRecord(MySqlDataReader reader)
        {
            return new RecordProductData
            {
                ProductName = reader["ProductName"]?.ToString() ?? string.Empty,
                ProductSN = reader["ProductSN"]?.ToString() ?? string.Empty,
                InspectionOperator = reader["InspectionOperator"]?.ToString() ?? string.Empty,
                NGType = reader["NGType"]?.ToString() ?? string.Empty,
                NGSize = reader["NGSize"]?.ToString() ?? string.Empty,
                DetectionTime = reader["DetectionTime"]?.ToString() ?? string.Empty,
                CameraInspection = reader["CameraInspection"]?.ToString() ?? string.Empty,
            };
        }
    }
}