using System;
using System.Collections.Generic;
using System.Linq;
using MES.Service.DB;
using MES.Service.Dto.service;
using MES.Service.Modes;
using MES.Service.util;
using SqlSugar;

namespace MES.Service.service.QC
{
    /// <summary>
    /// PCB检测数据服务
    /// </summary>
    public class PcbTestDataService
    {
        /// <summary>
        /// 保存整板检测数据
        /// </summary>
        /// <param name="dto">整板检测数据DTO</param>
        /// <returns>保存结果</returns>
        public bool SaveWholeboardData(WholeboardGenerateDto dto)
        {
            try
            {
                return SqlSugarHelper.UseTransactionWithOracle(db =>
                {
                    var testData = ConvertWholeboardToEntity(dto);
                    testData.CreateTime = DateTime.Now;
                    testData.DataType = "WHOLE";

                    var testDataId = db.Insertable(testData).ExecuteReturnIdentity();

                    var affectedRows = 1;
                    if (dto.BoardData != null && dto.BoardData.Count > 0)
                    {
                        var componentDataList = new List<MesPcbComponentData>();
                        foreach (var boardData in dto.BoardData)
                        {
                            if (boardData.CompData != null && boardData.CompData.Count > 0)
                            {
                                var componentData = ConvertComponentListToEntity(boardData.CompData, testDataId);
                                componentDataList.AddRange(componentData);
                            }
                        }

                        if (componentDataList.Count > 0)
                        {
                            affectedRows += db.Insertable(componentDataList).ExecuteCommand();
                        }
                    }

                    return affectedRows;
                }) > 0;
            }
            catch (Exception ex)
            {
                throw new Exception($"保存整板检测数据失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 保存单板检测数据
        /// </summary>
        /// <param name="dto">单板检测数据DTO</param>
        /// <returns>保存结果</returns>
        public bool SaveSingleBoardData(SingleBoardGenerateDto dto)
        {
            try
            {
                return SqlSugarHelper.UseTransactionWithOracle(db =>
                {
                    var testData = ConvertSingleBoardToEntity(dto);
                    testData.CreateTime = DateTime.Now;
                    testData.DataType = "SINGLE";

                    var testDataId = db.Insertable(testData).ExecuteReturnIdentity();

                    var affectedRows = 1;
                    if (dto.CompData != null && dto.CompData.Count > 0)
                    {
                        var componentDataList = ConvertComponentListToEntity(dto.CompData, testDataId);
                        affectedRows += db.Insertable(componentDataList).ExecuteCommand();
                    }

                    return affectedRows;
                }) > 0;
            }
            catch (Exception ex)
            {
                throw new Exception($"保存单板检测数据失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 根据ID获取PCB检测数据
        /// </summary>
        /// <param name="id">数据ID</param>
        /// <returns>PCB检测数据</returns>
        public MesPcbTestData GetPcbTestDataById(decimal id)
        {
            try
            {
                var db = SqlSugarHelper.GetInstance();
                return db.Queryable<MesPcbTestData>()
                    .Where(x => x.Id == id)
                    .First();
            }
            catch (Exception ex)
            {
                throw new Exception($"获取PCB检测数据失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 根据条件分页查询PCB检测数据
        /// </summary>
        /// <param name="dataType">数据类型（WHOLE/SINGLE）</param>
        /// <param name="deviceName">设备名称</param>
        /// <param name="pcbSn">PCB条码</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <returns>分页数据</returns>
        public (List<MesPcbTestData> items, int totalCount) GetPcbTestDataPage(
            string dataType = null,
            string deviceName = null,
            string pcbSn = null,
            DateTime? startTime = null,
            DateTime? endTime = null,
            int pageIndex = 1,
            int pageSize = 20)
        {
            try
            {
                var db = SqlSugarHelper.GetInstance();
                var totalCount = 0;

                var data = db.Queryable<MesPcbTestData>()
                    .WhereIF(StringUtil.IsNotNullOrEmpty(dataType), x => x.DataType == dataType)
                    .WhereIF(StringUtil.IsNotNullOrEmpty(deviceName), x => x.DeviceName.Contains(deviceName))
                    .WhereIF(StringUtil.IsNotNullOrEmpty(pcbSn), x => x.PcbSn.Contains(pcbSn))
                    .WhereIF(startTime.HasValue, x => x.PcbTestTime >= startTime.Value)
                    .WhereIF(endTime.HasValue, x => x.PcbTestTime <= endTime.Value)
                    .OrderBy(x => x.PcbTestTime, OrderByType.Desc)
                    .ToPageList(pageIndex, pageSize, ref totalCount);

                return (data, totalCount);
            }
            catch (Exception ex)
            {
                throw new Exception($"查询PCB检测数据失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 根据测试数据ID获取器件数据
        /// </summary>
        /// <param name="testDataId">测试数据ID</param>
        /// <returns>器件数据列表</returns>
        public List<MesPcbComponentData> GetComponentDataByTestDataId(decimal testDataId)
        {
            try
            {
                var db = SqlSugarHelper.GetInstance();
                return db.Queryable<MesPcbComponentData>()
                    .Where(x => x.TestDataId == testDataId)
                    .OrderBy(x => x.CompDesignator)
                    .ToList();
            }
            catch (Exception ex)
            {
                throw new Exception($"获取器件数据失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 根据PCB条码获取检测数据
        /// </summary>
        /// <param name="pcbSn">PCB条码</param>
        /// <returns>检测数据列表</returns>
        public List<MesPcbTestData> GetPcbTestDataByPcbSn(string pcbSn)
        {
            try
            {
                var db = SqlSugarHelper.GetInstance();
                return db.Queryable<MesPcbTestData>()
                    .Where(x => x.PcbSn == pcbSn)
                    .OrderBy(x => x.PcbTestTime, OrderByType.Desc)
                    .ToList();
            }
            catch (Exception ex)
            {
                throw new Exception($"根据PCB条码获取检测数据失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 删除PCB检测数据（级联删除器件数据）
        /// </summary>
        /// <param name="id">数据ID</param>
        /// <returns>删除结果</returns>
        public bool DeletePcbTestData(decimal id)
        {
            try
            {
                return SqlSugarHelper.UseTransactionWithOracle(db =>
                {
                    var affectedRows = 0;
                    
                    // 先删除器件数据
                    affectedRows += db.Deleteable<MesPcbComponentData>()
                        .Where(x => x.TestDataId == id)
                        .ExecuteCommand();
                    
                    // 再删除主数据
                    affectedRows += db.Deleteable<MesPcbTestData>()
                        .Where(x => x.Id == id)
                        .ExecuteCommand();

                    return affectedRows;
                }) > 0;
            }
            catch (Exception ex)
            {
                throw new Exception($"删除PCB检测数据失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 获取检测统计数据
        /// </summary>
        /// <param name="dataType">数据类型</param>
        /// <param name="deviceName">设备名称</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns>统计数据</returns>
        public dynamic GetTestStatistics(string dataType = null, string deviceName = null, 
            DateTime? startTime = null, DateTime? endTime = null)
        {
            try
            {
                var db = SqlSugarHelper.GetInstance();
                
                var statistics = db.Queryable<MesPcbTestData>()
                    .WhereIF(StringUtil.IsNotNullOrEmpty(dataType), x => x.DataType == dataType)
                    .WhereIF(StringUtil.IsNotNullOrEmpty(deviceName), x => x.DeviceName == deviceName)
                    .WhereIF(startTime.HasValue, x => x.PcbTestTime >= startTime.Value)
                    .WhereIF(endTime.HasValue, x => x.PcbTestTime <= endTime.Value)
                    .GroupBy(x => new { x.DataType, x.DeviceName })
                    .Select(x => new
                    {
                        DataType = x.DataType,
                        DeviceName = x.DeviceName,
                        TotalCount = SqlFunc.AggregateCount(x.Id),
                        PassCount = SqlFunc.AggregateCount(SqlFunc.IIF(x.PcbFinalResult == "PASS", x.Id, 0)),
                        FailCount = SqlFunc.AggregateCount(SqlFunc.IIF(x.PcbFinalResult == "FAIL", x.Id, 0)),
                        AvgCycleTime = SqlFunc.AggregateAvg(x.PcbCycleTime)
                    })
                    .ToList();

                return statistics;
            }
            catch (Exception ex)
            {
                throw new Exception($"获取检测统计数据失败: {ex.Message}", ex);
            }
        }

        #region 私有方法 - DTO转换

        /// <summary>
        /// 将整板DTO转换为实体
        /// </summary>
        /// <param name="dto">整板DTO</param>
        /// <returns>实体对象</returns>
        private MesPcbTestData ConvertWholeboardToEntity(WholeboardGenerateDto dto)
        {
            return new MesPcbTestData
            {
                DeviceName = dto.DeviceName,
                PcbSn = dto.PcbSn,
                PcbTrackLine = dto.PcbTrackLine,
                PcbBoardSide = dto.PcbBoardSide,
                PcbTestTime = StringUtil.IsNotNullOrEmpty(dto.PcbTestTime) ? 
                    DateTime.Parse(dto.PcbTestTime) : null,
                PcbCycleTime = (decimal?)dto.PcbCycleTime,
                PcbProjectName = dto.PcbProjectName,
                PcbRobotResult = dto.PcbRobotResult,
                PcbUserResult = dto.PcbUserResult,
                PcbFinalResult = dto.PcbFinalResult,
                PcbRepairUser = dto.PcbRepairUser,
                PcbBoardNumber = dto.PcbBoardNumber,
                PcbBoardRobotNgNumber = dto.PcbBoardRobotNgNumber,
                PcbBoardUserNgNumber = dto.PcbBoardUserNgNumber,
                PcbBoardRepassNumber = dto.PcbBoardRepassNumber,
                PcbCompNumber = dto.PcbCompNumber,
                PcbCompRobotNgNumber = dto.PcbCompRobotNgNumber,
                PcbCompUserNgNumber = dto.PcbCompUserNgNumber,
                PcbCompRepassNumber = dto.PcbCompRepassNumber
            };
        }

        /// <summary>
        /// 将单板DTO转换为实体
        /// </summary>
        /// <param name="dto">单板DTO</param>
        /// <returns>实体对象</returns>
        private MesPcbTestData ConvertSingleBoardToEntity(SingleBoardGenerateDto dto)
        {
            return new MesPcbTestData
            {
                DeviceName = dto.DeviceName,
                PcbSn = dto.PcbSn,
                PcbTrackLine = dto.PcbTrackLine,
                PcbBoardSide = dto.PcbBoardSide,
                PcbTestTime = StringUtil.IsNotNullOrEmpty(dto.PcbTestTime) ? 
                    DateTime.Parse(dto.PcbTestTime) : null,
                PcbCycleTime = (decimal?)dto.PcbCycleTime,
                PcbProjectName = dto.PcbProjectName,
                PcbRobotResult = dto.PcbRobotResult,
                PcbUserResult = dto.PcbUserResult,
                PcbFinalResult = dto.PcbFinalResult,
                PcbRepairUser = dto.PcbRepairUser,
                PcbBoardNumber = dto.PcbBoardNumber,
                PcbCompNumber = dto.PcbCompNumber,
                BoardSn = dto.BoardSn,
                BoardNo = dto.BoardNo,
                BoardRobotResult = dto.BoardRobotResult,
                BoardUserResult = dto.BoardUserResult,
                BoardFinalResult = dto.BoardFinalResult,
                BoardCompNumber = dto.BoardCompNumber,
                BoardCompRobotNgNumber = dto.BoardCompRobotNgNumber,
                BoardCompUserNgNumber = dto.BoardCompUserNgNumber,
                BoardCompRepassNumber = dto.BoardCompRepassNumber
            };
        }

        /// <summary>
        /// 将器件DTO列表转换为实体列表
        /// </summary>
        /// <param name="dtoList">器件DTO列表</param>
        /// <param name="testDataId">测试数据ID</param>
        /// <returns>实体列表</returns>
        private List<MesPcbComponentData> ConvertComponentListToEntity(List<ComponentDataDto> dtoList, decimal testDataId)
        {
            return dtoList.Select(dto => new MesPcbComponentData
            {
                TestDataId = testDataId,
                CompDesignator = dto.CompDesignator,
                CompPart = dto.CompPart,
                CompPackage = dto.CompPackage,
                CompType = dto.CompType,
                CompRobotCode = dto.CompRobotCode,
                CompRobotResult = dto.CompRobotResult,
                CompUserCode = dto.CompUserCode,
                CompUserResult = dto.CompUserResult,
                CompImage = dto.CompImage,
                CreateTime = DateTime.Now
            }).ToList();
        }

        #endregion
    }
}