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

namespace MES.Service.service.QC;

/// <summary>
///     SPI/AOI检测数据服务
/// </summary>
public class SpiAoiService
{
    /// <summary>
    ///     Upload AOI header data.
    /// </summary>
    /// <param name="header">Header DTO.</param>
    /// <returns>Header upload response.</returns>
    public SpiAoiHeaderUploadResponse UploadAoiHeader(SpiAoiHeaderDto header)
    {
        if (header == null)
        {
            throw new Exception("header cannot be null");
        }

        try
        {
            return UploadAoiHeaderBatchInternal(
                new List<SpiAoiHeaderDto> { header })[0];
        }
        catch (Exception ex)
        {
            throw new Exception($"Failed to upload AOI header: {ex.Message}",
                ex);
        }
    }

    /// <summary>
    ///     Upload multiple AOI header records within a single transaction.
    /// </summary>
    /// <param name="headers">Header DTO collection.</param>
    /// <returns>Header upload responses.</returns>
    public List<SpiAoiHeaderUploadResponse> UploadAoiHeaderBatch(
        List<SpiAoiHeaderDto> headers)
    {
        if (headers == null)
        {
            throw new Exception("headers cannot be null");
        }

        try
        {
            return UploadAoiHeaderBatchInternal(headers);
        }
        catch (Exception ex)
        {
            throw new Exception(
                $"Failed to upload AOI header batch: {ex.Message}", ex);
        }
    }

    private List<SpiAoiHeaderUploadResponse> UploadAoiHeaderBatchInternal(
        List<SpiAoiHeaderDto> headers)
    {
        var headerList = headers.ToList();
        if (headerList.Count == 0)
        {
            throw new Exception("headers cannot be empty");
        }

        foreach (var header in headerList)
        {
            ValidateHeader(header);
        }

        var duplicateBarcodes = headerList
            .GroupBy(h => h.BoardBarcode)
            .Where(g => g.Count() > 1)
            .Select(g => g.Key)
            .ToList();

        if (duplicateBarcodes.Any())
        {
            throw new Exception(
                $"Duplicate board barcodes in request: {string.Join(", ",
                    duplicateBarcodes)}.");
        }

        var responses = new List<SpiAoiHeaderUploadResponse>();

        SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            var boardBarcodes = headerList.Select(h => h.BoardBarcode)
                .ToList();

            if (boardBarcodes.Any())
            {
                var existingBarcodes = db.Queryable<MesSpiAoiHeader>()
                    .Where(x => boardBarcodes.Contains(x.BoardBarcode))
                    .Select(x => x.BoardBarcode)
                    .ToList();

                if (existingBarcodes.Any())
                {
                    throw new Exception(
                        $"Board barcodes {string.Join(", ", existingBarcodes)} already exist; duplicate AOI uploads are not allowed.");
                }
            }

            foreach (var headerDto in headerList)
            {
                var entity = ConvertHeaderDtoToEntity(headerDto);
                var timestamp = DateTime.Now;
                entity.CreatedAt = timestamp;
                entity.UpdatedAt = timestamp;

                var headerId = db.Insertable(entity).ExecuteReturnIdentity();
                responses.Add(new SpiAoiHeaderUploadResponse
                {
                    HeaderId = headerId
                });
            }

            return responses.Count;
        });

        if (responses.Count == 0)
        {
            throw new Exception("AOI upload returned no result.");
        }

        return responses;
    }

    /// <summary>
    ///     Upload SPI detail data.
    /// </summary>
    /// <param name="request">Detail upload DTO.</param>
    /// <returns>Detail upload response.</returns>
    public SpiAoiDetailUploadResponse UploadSpiDetails(SpiAoiDetailDto request)
    {
        if (request == null)
        {
            throw new Exception("request cannot be null");
        }

        try
        {
            return UploadSpiDetailsInternal(
                new List<SpiAoiDetailDto> { request });
        }
        catch (Exception ex)
        {
            throw new Exception($"Failed to upload SPI details: {ex.Message}",
                ex);
        }
    }

    /// <summary>
    ///     Upload multiple SPI detail records within a single transaction.
    /// </summary>
    /// <param name="requests">Detail DTO collection.</param>
    /// <returns>Aggregated upload response.</returns>
    public SpiAoiDetailUploadResponse UploadSpiDetailsBatch(
        List<SpiAoiDetailDto> requests)
    {
        if (requests == null)
        {
            throw new Exception("details cannot be null");
        }

        try
        {
            return UploadSpiDetailsInternal(requests);
        }
        catch (Exception ex)
        {
            throw new Exception(
                $"Failed to upload SPI detail batch: {ex.Message}", ex);
        }
    }

    private SpiAoiDetailUploadResponse UploadSpiDetailsInternal(
        List<SpiAoiDetailDto> requests)
    {
        var detailList = requests.ToList();
        if (detailList.Count == 0)
        {
            throw new Exception("details cannot be empty");
        }

        ValidateDetailData(detailList);

        var affectedRows = SqlSugarHelper.UseTransactionWithOracle(db =>
        {
            var total = 0;
            foreach (var detail in detailList)
            {
                var detailEntity = ConvertDetailDtoToEntity(detail);
                total += db.Insertable(detailEntity).ExecuteCommand();
            }

            return total;
        });

        if (affectedRows <= 0)
        {
            throw new Exception(
                "SPI detail insert returned no affected rows.");
        }

        return new SpiAoiDetailUploadResponse
        {
            DetailCount = affectedRows
        };
    }

    /// <summary>
    ///     Retrieve SPI/AOI data by board barcode.
    /// </summary>
    /// <param name="boardBarcode">Board barcode.</param>
    /// <returns>Header and detail tuple.</returns>
    public (MesSpiAoiHeader header, List<MesSpiAoiDetail> details) GetByBarcode(
        string boardBarcode)
    {
        try
        {
            var db = SqlSugarHelper.GetInstance();

            var header = db.Queryable<MesSpiAoiHeader>()
                .Where(x => x.BoardBarcode == boardBarcode)
                .First();

            if (header == null)
            {
                return (null, null);
            }

            var details = db.Queryable<MesSpiAoiDetail>()
                .Where(x => x.HeaderId == header.Id)
                .ToList();

            return (header, details);
        }
        catch (Exception ex)
        {
            throw new Exception($"查询SPI/AOI检测数据失�? {ex.Message}", ex);
        }
    }

    public (MesSpiAoiHeader header, List<MesSpiAoiDetail> details) GetById(
        decimal headerId)
    {
        try
        {
            var db = SqlSugarHelper.GetInstance();

            var header = db.Queryable<MesSpiAoiHeader>()
                .Where(x => x.Id == headerId)
                .First();

            if (header == null)
            {
                return (null, null);
            }

            var details = db.Queryable<MesSpiAoiDetail>()
                .Where(x => x.HeaderId == headerId)
                .ToList();

            return (header, details);
        }
        catch (Exception ex)
        {
            throw new Exception($"查询SPI/AOI检测数据失�? {ex.Message}", ex);
        }
    }

    /// <summary>
    ///     分页查询SPI/AOI检测数�?
    /// </summary>
    /// <param name="boardBarcode">条码(可选)</param>
    /// <param name="workOrder">工单(可选)</param>
    /// <param name="surface">板面(可选)</param>
    /// <param name="startDate">开始日�?可选)</param>
    /// <param name="endDate">结束日期(可选)</param>
    /// <param name="pageIndex">页码</param>
    /// <param name="pageSize">页大�?/param>
    /// <returns>分页数据</returns>
    public (List<MesSpiAoiHeader> items, int totalCount) GetPage(
        string boardBarcode = null,
        string workOrder = null,
        string surface = null,
        DateTime? startDate = null,
        DateTime? endDate = null,
        int pageIndex = 1,
        int pageSize = 20)
    {
        try
        {
            var db = SqlSugarHelper.GetInstance();
            var totalCount = 0;

            var data = db.Queryable<MesSpiAoiHeader>()
                .WhereIF(StringUtil.IsNotNullOrEmpty(boardBarcode),
                    x => x.BoardBarcode.Contains(boardBarcode))
                .WhereIF(StringUtil.IsNotNullOrEmpty(workOrder),
                    x => x.WorkOrder.Contains(workOrder))
                .WhereIF(StringUtil.IsNotNullOrEmpty(surface),
                    x => x.Surface == surface)
                .WhereIF(startDate.HasValue,
                    x => x.TestDate >= startDate.Value)
                .WhereIF(endDate.HasValue,
                    x => x.TestDate <= endDate.Value)
                .OrderBy(x => x.CreatedAt, SqlSugar.OrderByType.Desc)
                .ToPageList(pageIndex, pageSize, ref totalCount);

            return (data, totalCount);
        }
        catch (Exception ex)
        {
            throw new Exception($"分页查询SPI/AOI检测数据失�? {ex.Message}", ex);
        }
    }

    #region 私有方法

    /// <summary>
    ///     Validate AOI header payload.
    /// </summary>
    /// <param name="header">Header DTO.</param>
    private void ValidateHeader(SpiAoiHeaderDto header)
    {
        if (header == null)
        {
            throw new Exception("header cannot be null");
        }

        if (StringUtil.IsNullOrEmpty(header.TestDate))
        {
            throw new Exception("testDate is required");
        }

        if (StringUtil.IsNullOrEmpty(header.TestTime))
        {
            throw new Exception("testTime is required");
        }

        if (StringUtil.IsNullOrEmpty(header.TestResult))
        {
            throw new Exception("testResult is required");
        }

        if (StringUtil.IsNullOrEmpty(header.BoardBarcode))
        {
            throw new Exception("boardBarcode is required");
        }

        if (StringUtil.IsNullOrEmpty(header.Surface))
        {
            throw new Exception("surface is required");
        }

        if (header.Surface != "T" && header.Surface != "B")
        {
            throw new Exception("surface must be T or B");
        }

        if (header.BoardBarcode.Length > 128)
        {
            throw new Exception("boardBarcode cannot exceed 128 characters");
        }

        if (!StringUtil.IsNullOrEmpty(header.TestResult) &&
            header.TestResult.Length > 12)
        {
            throw new Exception("testResult cannot exceed 12 characters");
        }
    }

    
    private MesSpiAoiHeader ConvertHeaderDtoToEntity(SpiAoiHeaderDto dto)
    {
        return new MesSpiAoiHeader
        {
            TestDate = DateTime.Parse(dto.TestDate),
            TestTime = dto.TestTime,
            TestResult = dto.TestResult,
            Surface = dto.Surface,
            TotalPoints = dto.TotalPoints,
            ActualDefects = dto.ActualDefects,
            EquipmentModel = dto.EquipmentModel,
            WorkOrder = dto.WorkOrder,
            ProductModel = dto.ProductModel,
            BoardBarcode = dto.BoardBarcode,
            SmtGroup = dto.SmtGroup,
            LineName = dto.LineName
        };
    }

    /// <summary>
    ///     Perform non-blocking SPI detail checks (warnings only).
    /// </summary>
    /// <param name="details">Detail DTO list.</param>
    private void ValidateDetailData(List<SpiAoiDetailDto> details)
    {
        foreach (var detail in details)
        {
            // Validate passBoards <= inputBoards
            if (detail.PassBoards > detail.InputBoards)
            {
                Console.WriteLine(
                    $"[Warning] passBoards({detail.PassBoards}) is greater than inputBoards({detail.InputBoards}).");
            }

            // Validate defectBoards = inputBoards - passBoards
            var expectedDefectBoards = detail.InputBoards - detail.PassBoards;
            if (Math.Abs(detail.DefectBoards - expectedDefectBoards) > 0)
            {
                Console.WriteLine(
                    $"[Warning] defectBoards({detail.DefectBoards}) does not match the expected value ({expectedDefectBoards}).");
            }

            // Validate passRate deviation stays within +/- 1.0
            if (detail.InputBoards > 0 && detail.PassRate.HasValue)
            {
                var expectedPassRate = (decimal)detail.PassBoards /
                    detail.InputBoards * 100;
                var deviation =
                    Math.Abs(detail.PassRate.Value - expectedPassRate);
                if (deviation > 1.0m)
                {
                    Console.WriteLine(
                        $"[Warning] passRate({detail.PassRate}) deviates from the expected value ({expectedPassRate:F2}) by more than 1.0.");
                }
            }
        }
    }

    private MesSpiAoiDetail ConvertDetailDtoToEntity(SpiAoiDetailDto dto)
    {
        var now = DateTime.Now;
        return new MesSpiAoiDetail
        {
            HeaderId = dto.HeaderId ?? 0,
            AreaOverflowCount = dto.AreaOverflowCount,
            AreaUnderflowCount = dto.AreaUnderflowCount,
            ExceedingHeightCount = dto.ExceedingHeightCount,
            InsufficientHeightCount = dto.InsufficientHeightCount,
            XDeviationCount = dto.XDeviationCount,
            YDeviationCount = dto.YDeviationCount,
            CollapseCount = dto.CollapseCount,
            SolderPullTipCount = dto.SolderPullTipCount,
            AbnormalityCount = dto.AbnormalityCount,
            LineDisplayName = dto.LineDisplayName,
            MachineName = dto.MachineName,
            InputBoards = dto.InputBoards,
            OkBoards = dto.OkBoards,
            PassBoards = dto.PassBoards,
            PassRate = dto.PassRate,
            DefectBoards = dto.DefectBoards,
            DefectRate = dto.DefectRate,
            DefectPpm = dto.DefectPpm,
            DefectPoints = dto.DefectPoints,
            MeasuredPoints = dto.MeasuredPoints,
            PendingPoints = dto.PendingPoints,
            CreatedAt = now,
            UpdatedAt = now
        };
    }

    #endregion
}