using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Microsoft.Azure.Management.ResourceManager.Fluent;
using MMDAL.DTO.Inspection;
using MMDAL.DTO.Lot;
using MMDAL.Query.Inspection;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace MMDAL.CustomCode.Inspection
{
    public class InspectionDAL : IInspectionDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public InspectionDAL(IQueryExecutor queryExecutor, IValidation validation)
        {
            _QueryExecutor = queryExecutor;
            _Validation = validation;
        }

        // ── Existing header operations ────────────────────────────────────────

        public async Task<string> GetInspection(int InspectionId, LoginDTO LoginDTO)
        {
            try
            {
                var dto = await _QueryExecutor.QuerySingleAsync<InspectionDTO>(
                    LoginDTO, InspectionQB.GET_INSPECTION, new { InspectionId });
                return JsonConvert.SerializeObject(dto);
            }
            catch (Exception ex)
            {
                throw new Exception($"GetInspection failed for InspectionId {InspectionId}: {ex.Message}", ex);
            }
        }

        public async Task<int> SaveInspection(InspectionDTO InspectionDTO, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(LoginDTO, InspectionQB.SAVE_INSPECTION, InspectionDTO);
            }
            catch (Exception ex)
            {
                // FIX: pass ex as innerException to preserve stack trace
                string error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error, ex);
            }
        }

        public async Task<int> UpdateInspection(InspectionDTO InspectionDTO, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(LoginDTO, InspectionQB.UPDATE_INSPECTION, InspectionDTO);
            }
            catch (Exception ex)
            {
                // FIX: pass ex as innerException to preserve stack trace
                string error = await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(error, ex);
            }
        }

        public async Task<string> DeleteInspection(int InspectionId, LoginDTO LoginDTO)
        {
            try
            {
                var param = new
                {
                    InspectionId,
                    InspectionModifiedById = LoginDTO.UserId,
                    InspectionModifiedOn = DateTime.UtcNow
                };
                var result = await _QueryExecutor.ExecuteAsync(LoginDTO, InspectionQB.DELETE_INSPECTION, param);
                return result > 0
                    ? SuccessResponse.DeleteSuccessMessage
                    : ErrorResponse.DeleteErrorMessage;
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage);
                throw new Exception(error, ex);
            }
        }

        // ── Composite full-get (6-query QueryMultipleAsync) ───────────────────

        /// <summary>
        /// Fetches the full Inspection tree (header + 5 child tables) in one
        /// multi-result-set query, then assembles the object graph in memory.
        ///
        /// FIXES:
        ///   [DAL-GET-1] CollapseParameterSamples group key now includes ParameterSection
        ///               to prevent same ParameterId merging across different sections.
        ///   [DAL-GET-2] Header-level parameter array built from per-detail collapsed
        ///               params (not raw rows) so values are already joined correctly.
        ///   [DAL-GET-3] Removed unused InspectionType using directive.
        /// </summary>
        public async Task<string> GetInspectionFull(int inspectionId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var param = new { InspectionId = inspectionId };

                using var multi = await _QueryExecutor.QueryMultipleAsync(
                    loginDTO, InspectionQB.GET_INSPECTION_FULL, param,
                    commandType: CommandType.Text);

                var header = (await multi.ReadAsync<InspectionDTO>()).FirstOrDefault();
                var details = (await multi.ReadAsync<InspectionDetailDTO>()).ToList();
                var parameters = (await multi.ReadAsync<InspectionParameterDTO>()).ToList();
                var results = (await multi.ReadAsync<InspectionResultDTO>()).ToList();
                var defects = (await multi.ReadAsync<InspectionDefectDTO>()).ToList();
                var workers = (await multi.ReadAsync<InspectionWorkerDetailDTO>()).ToList();

                if (header == null)
                    return JsonConvert.SerializeObject(null);

                // [DAL-GET-1] Collapse sample rows → one DTO per parameter (group includes ParameterSection)
                var collapsed = CollapseParameterSamples(parameters);

                // Build per-detail lookup: DetailId → section list → parameter list
                var paramSectionLookup = collapsed
                    .GroupBy(p => p.InspectionDetailId)
                    .ToDictionary(g => g.Key, g =>
                        g.GroupBy(p => p.ParameterSection ?? "")
                         .Select(sg => new InspectionParameterSectionDTO
                         {
                             SectionName = sg.Key,
                             Parameters = sg.ToList()
                         })
                         .ToList());

                var resultLookup = results
                    .GroupBy(r => r.InspectionDetailId)
                    .ToDictionary(g => g.Key, g => g.ToList());

                var defectLookup = defects
                    .GroupBy(d => d.InspectionDetailId)
                    .ToDictionary(g => g.Key, g => g.ToList());

                foreach (var d in details)
                {
                    d.InspectionParameterArray = paramSectionLookup.TryGetValue(d.InspectionDetailId, out var ps)
                        ? ps : new List<InspectionParameterSectionDTO>();

                    d.InspectionResultArray = resultLookup.TryGetValue(d.InspectionDetailId, out var r)
                        ? r : new List<InspectionResultDTO>();

                    d.InspectionDefectArray = defectLookup.TryGetValue(d.InspectionDetailId, out var df)
                        ? df : new List<InspectionDefectDTO>();
                }

                header.InspectionDetailArray = details;
                header.InspectionWorkerDetailArray = workers;

                // [DAL-GET-2] Header-level sections built from already-collapsed params
                header.InspectionParameterArray = collapsed
                    .GroupBy(p => p.ParameterSection ?? "")
                    .Select(sg => new InspectionParameterSectionDTO
                    {
                        SectionName = sg.Key,
                        Parameters = sg.ToList()
                    })
                    .ToList();

                header.InspectionResultArray = results;
                header.InspectionDefectArray = defects;

                return JsonConvert.SerializeObject(header, new JsonSerializerSettings
                {
                    ReferenceLoopHandling = ReferenceLoopHandling.Ignore
                });
            }
            catch (Exception ex)
            {
                throw new Exception(
                    $"GetInspectionFull failed for InspectionId {inspectionId}: {ex.Message}", ex);
            }
        }

        // ── Paginated / picklist ──────────────────────────────────────────────

        public async Task<string> GetPicklistInspection(
            CriteriaDTO CriteriaDTO,
            LoginDTO loginDTO,
            CancellationToken ct)
        {
            try
            {
                var result = await _QueryExecutor.QueryWithCriteriaAsync<InspectionPicklistDTO>(
                    loginDTO,
                    InspectionQB.GET_PICKLIST_INSPECTION,
                    CriteriaDTO,
                    cancellationToken: ct);

                return JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                throw new Exception($"GetPicklistInspection failed: {ex.Message}", ex);
            }
        }

        public async Task<string> GetInspectionList(
            InspectionListRequestDTO request,
            LoginDTO loginDTO,
            CancellationToken ct)
        {
            try
            {
                int offset = (request.Page - 1) * request.PageSize;
                var param = new
                {
                    request.OUId,
                    request.PeriodId,
                    request.InspectionTypeId,
                    FromDate = request.FromDate,
                    ToDate = request.ToDate,
                    Offset = offset,
                    request.PageSize
                };

                var rows = await _QueryExecutor.QueryAsync<InspectionListItemDTO>(
                    loginDTO, InspectionQB.GET_INSPECTION_LIST, param, cancellationToken: ct);

                return JsonConvert.SerializeObject(rows);
            }
            catch (Exception ex)
            {
                throw new Exception($"GetInspectionList failed: {ex.Message}", ex);
            }
        }

        // ── Full composite save ───────────────────────────────────────────────

        /// <summary>
        /// Persists the full Inspection tree in a single DB transaction.
        /// On UPDATE: deletes all child records first, then re-inserts from the DTO.
        /// All PKs and FKs are assigned by the BLL before this method is called.
        ///
        /// FIXES:
        ///   [DAL-SAVE-1] detail.InspectionId re-assignment removed — already set by BLL.
        ///   [DAL-SAVE-2] Parameter loop simplified: BLL.ExpandParameterSamples already
        ///                produced one DTO per sample. No ExpandSampleValues loop here.
        ///                InspectionParameterInputValue parsed once → double? for DB.
        ///   [DAL-SAVE-3] InspectionParameterInputDate passed as DateTime? — nullable,
        ///                prevents SqlDateTime overflow on null input.
        ///   [DAL-SAVE-4] InspectionDefectId always comes from BLL (fresh positive ID).
        ///   [DAL-SAVE-5] Result row now includes ApprovedById and ResultRemarks fields.
        ///   [DAL-SAVE-6] Detail row includes CreatedById/On + ModifiedById/On audit fields.
        ///   [DAL-SAVE-7] Redundant $"..." interpolations on plain string constants removed.
        ///   [DAL-SAVE-8] innerException passed to all new Exception() calls.
        /// </summary>
        public async Task<int> SaveInspectionFull(
            InspectionDTO dto,
            LoginDTO loginDTO,
            CancellationToken ct,
            bool isNew = false)
        {
            try
            {
                var statements = new List<(string sql, object param)>();

                // ─────────────────────────────────────────────
                // HEADER
                // ─────────────────────────────────────────────
                if (isNew)
                {
                    statements.Add((InspectionQB.SAVE_INSPECTION, dto));
                }
                else
                {
                    statements.Add((InspectionQB.UPDATE_INSPECTION, dto));

                    // Delete all children — re-inserted fresh below with BLL-assigned IDs
                    var inspParam = new { dto.InspectionId };
                    statements.Add((InspectionQB.DELETE_INSPECTION_PARAMETER_BY_INSPECTION, inspParam));
                    statements.Add((InspectionQB.DELETE_INSPECTION_RESULT_BY_INSPECTION, inspParam));
                    statements.Add((InspectionQB.DELETE_INSPECTION_DEFECT_BY_INSPECTION, inspParam));
                    statements.Add((InspectionQB.DELETE_INSPECTION_DETAIL_BY_INSPECTION, inspParam));
                    statements.Add((InspectionQB.DELETE_INSPECTION_WORKER_BY_INSPECTION, inspParam));
                }

                // ─────────────────────────────────────────────
                // DETAILS + CHILDREN
                // [DAL-SAVE-1] InspectionDetailId and InspectionId already set by BLL
                // ─────────────────────────────────────────────
                if (dto.InspectionDetailArray?.Count > 0)
                {
                    foreach (var detail in dto.InspectionDetailArray)
                    {
                        // [DAL-SAVE-6] Audit fields added to detail insert
                        statements.Add((InspectionQB.SAVE_INSPECTION_DETAIL, new
                        {
                            detail.InspectionDetailId,
                            detail.InspectionId,
                            detail.InspectionDetailSlNo,
                            detail.LoadObjectTypeId,
                            detail.InspectionDetailLoadObjectId,
                            detail.ItemId,
                            detail.SkuId,
                            detail.LotId,
                            detail.SpecificationReference,
                            detail.ParameterSetId,
                            detail.RoutingParameterSetId,
                            detail.InspectionDetailNumberOfSamples,
                            detail.InspectionDetailDocumentQuantity,
                            detail.InspectionDetailInspectedQuantity,
                            detail.InspectionDetailShortageQuantity,
                            detail.InspectionDetailExcessQuantity,
                            detail.InspectionDetailGoodQuantity,
                            detail.InspectionDetailRejectedQuantity,
                            detail.InspectionDetailReworkQuantity,
                            detail.InspectionDetailOtherQuantity,
                            detail.InspectionDetailTotalPoints,
                            detail.InspectionDetailTotalPointsAfter,
                            detail.InspectionDetailQcComparisonStatus,
                            detail.BinIds,
                            detail.BinId,
                            detail.PackNumberId,
                            detail.InspectionDetailPick,
                            CreatedById = loginDTO.UserId,   // [DAL-SAVE-6]
                            CreatedOn = DateTime.UtcNow,   // [DAL-SAVE-6]
                            ModifiedById = loginDTO.UserId,   // [DAL-SAVE-6]
                            ModifiedOn = DateTime.UtcNow    // [DAL-SAVE-6]
                        }));

                        // ─────────────────────────────────────────────
                        // PARAMETERS
                        // [DAL-SAVE-2] BLL already expanded samples — each DTO = 1 DB row.
                        //             No ExpandSampleValues loop. Parse InputValue once → double?
                        // [DAL-SAVE-3] InspectionParameterInputDate is DateTime? — DB col nullable.
                        // ─────────────────────────────────────────────
                        var flatParams = detail.InspectionParameterArray?
                            .SelectMany(s => s.Parameters ?? new List<InspectionParameterDTO>())
                            .ToList();

                        if (flatParams?.Count > 0)
                        {
                            foreach (var p in flatParams)
                            {
                                // Parse single numeric string → double? for DB numeric column
                                double? inputVal = double.TryParse(
                                    p.InspectionParameterInputValue,
                                    NumberStyles.Any,
                                    CultureInfo.InvariantCulture,
                                    out double parsed)
                                    ? parsed
                                    : (double?)null;

                                statements.Add((InspectionQB.SAVE_INSPECTION_PARAMETER, new
                                {
                                    p.InspectionParameterId,
                                    p.InspectionDetailId,
                                    p.InspectionParameterSlNo,
                                    p.InspectionParameterParameterSlNo,
                                    p.InspectionParameterSampleNumber,
                                    p.ParameterId,
                                    p.UomId,
                                    p.InspectionParameterAverage,
                                    p.InspectionParameterResult,
                                    InspectionParameterInputValue = inputVal,              // double?, not string
                                    p.InspectionParameterInputText,
                                    InspectionParameterInputDate = p.InspectionParameterInputDate, // [DAL-SAVE-3] DateTime?
                                    InspectionParameterFinalValue = p.InspectionParameterFinalValue,
                                    p.MinVal,
                                    p.MaxVal,
                                    ParameterSection = p.ParameterSection,
                                    CreatedById = loginDTO.UserId,
                                    CreatedOn = DateTime.UtcNow,
                                    ModifiedById = loginDTO.UserId,
                                    ModifiedOn = DateTime.UtcNow
                                }));
                            }
                        }

                        // ─────────────────────────────────────────────
                        // RESULTS
                        // [DAL-SAVE-5] Added ApprovedById and ResultRemarks (were missing)
                        // ─────────────────────────────────────────────
                        if (detail.InspectionResultArray?.Count > 0)
                        {
                            foreach (var r in detail.InspectionResultArray)
                            {
                                statements.Add((InspectionQB.SAVE_INSPECTION_RESULT, new
                                {
                                    r.InspectionResultId,
                                    r.InspectionDetailId,
                                    r.InspectionResultSlNo,
                                    r.ItemId,
                                    r.SkuId,
                                    r.LotId,
                                    r.ApprovedById,                      // [DAL-SAVE-5]
                                    r.ResultRemarks,                     // [DAL-SAVE-5]
                                    r.InspectionResultQuantity,
                                    r.InspectionResultInspectionResults,
                                    CreatedById = loginDTO.UserId,
                                    CreatedOn = DateTime.UtcNow,
                                    ModifiedById = loginDTO.UserId,
                                    ModifiedOn = DateTime.UtcNow
                                }));
                            }
                        }

                        // ─────────────────────────────────────────────
                        // DEFECTS
                        // [DAL-SAVE-4] InspectionDefectId is always a fresh positive ID
                        //             assigned by BLL — JSON value (-1) never reaches here
                        // ─────────────────────────────────────────────
                        if (detail.InspectionDefectArray?.Count > 0)
                        {
                            foreach (var df in detail.InspectionDefectArray)
                            {
                                statements.Add((InspectionQB.SAVE_INSPECTION_DEFECT, new
                                {
                                    df.InspectionDefectId,           // [DAL-SAVE-4] always positive
                                    df.InspectionDetailId,
                                    df.InspectionDefectSlNo,
                                    df.InspectionDefectQuantityMark,
                                    df.DefectId,
                                    df.InspectionDefectNumberOfDefects,
                                    df.InspectionDefectPoints,
                                    df.InspectionDefectNumberOfDefectsAfter,
                                    df.InspectionDefectPointsAfter,
                                    df.DefectCategory,
                                    CreatedById = loginDTO.UserId,
                                    CreatedOn = DateTime.UtcNow,
                                    ModifiedById = loginDTO.UserId,
                                    ModifiedOn = DateTime.UtcNow
                                }));
                            }
                        }
                    }
                }

                // ─────────────────────────────────────────────
                // WORKERS
                // ─────────────────────────────────────────────
                if (dto.InspectionWorkerDetailArray?.Count > 0)
                {
                    foreach (var w in dto.InspectionWorkerDetailArray)
                    {
                        statements.Add((InspectionQB.SAVE_INSPECTION_WORKER, new
                        {
                            w.InspectionWorkerDetailId,
                            w.InspectionId,
                            w.InspectionWorkerDetailSlNo,
                            w.UserId,
                            CreatedById = loginDTO.UserId,
                            CreatedOn = DateTime.UtcNow,
                            ModifiedById = loginDTO.UserId,
                            ModifiedOn = DateTime.UtcNow
                        }));
                    }
                }

                // ─────────────────────────────────────────────
                // EXECUTE — all statements in one DB transaction
                // ─────────────────────────────────────────────
                await _QueryExecutor.ExecuteInTransactionAsync(loginDTO, statements);

                return dto.InspectionId;
            }
            catch (Exception ex)
            {
                // [DAL-SAVE-8] Preserve original stack trace via innerException
                throw new Exception($"{ErrorResponse.SaveErrorMessage}: {ex.Message}", ex);
            }
        }

        // ── Inspection number helpers ─────────────────────────────────────────

        public async Task<string> GetInspectionTypeCode(int inspectionTypeId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<string>(
                    loginDTO,
                    InspectionQB.GET_INSPECTION_TYPE_CODE,
                    new { InspectionTypeId = inspectionTypeId });
            }
            catch (Exception ex)
            {
                throw new Exception($"GetInspectionTypeCode failed: {ex.Message}", ex);
            }
        }

        public async Task<string> GetLastInspectionNumber(int inspectionTypeId, int ouId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<string>(
                    loginDTO,
                    InspectionQB.GET_LAST_INSPECTION_NUMBER,
                    new { InspectionTypeId = inspectionTypeId, OuId = ouId });
            }
            catch (Exception ex)
            {
                throw new Exception($"GetLastInspectionNumber failed: {ex.Message}", ex);
            }
        }

        // ── Individual child GET endpoints ────────────────────────────────────

        public async Task<string> GetInspectionDetailByInspection(int inspectionId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var rows = await _QueryExecutor.QueryAsync<InspectionDetailDTO>(
                    loginDTO,
                    InspectionQB.GET_INSPECTION_DETAIL_BY_INSPECTION,
                    new { InspectionId = inspectionId },
                    cancellationToken: ct);

                return JsonConvert.SerializeObject(rows);
            }
            catch (Exception ex)
            {
                throw new Exception($"GetInspectionDetailByInspection failed: {ex.Message}", ex);
            }
        }

        public async Task<string> GetInspectionParameterByDetail(int inspectionDetailId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var rows = await _QueryExecutor.QueryAsync<InspectionParameterDTO>(
                    loginDTO,
                    InspectionQB.GET_INSPECTION_PARAMETER_BY_DETAIL,
                    new { InspectionDetailId = inspectionDetailId },
                    cancellationToken: ct);

                return JsonConvert.SerializeObject(CollapseParameterSamples(rows));
            }
            catch (Exception ex)
            {
                throw new Exception($"GetInspectionParameterByDetail failed: {ex.Message}", ex);
            }
        }

        public async Task<string> GetInspectionResultByDetail(int inspectionDetailId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var rows = await _QueryExecutor.QueryAsync<InspectionResultDTO>(
                    loginDTO,
                    InspectionQB.GET_INSPECTION_RESULT_BY_DETAIL,
                    new { InspectionDetailId = inspectionDetailId },
                    cancellationToken: ct);

                return JsonConvert.SerializeObject(rows);
            }
            catch (Exception ex)
            {
                throw new Exception($"GetInspectionResultByDetail failed: {ex.Message}", ex);
            }
        }

        public async Task<string> GetInspectionDefectByDetail(int inspectionDetailId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var rows = await _QueryExecutor.QueryAsync<InspectionDefectDTO>(
                    loginDTO,
                    InspectionQB.GET_INSPECTION_DEFECT_BY_DETAIL,
                    new { InspectionDetailId = inspectionDetailId },
                    cancellationToken: ct);

                return JsonConvert.SerializeObject(rows);
            }
            catch (Exception ex)
            {
                throw new Exception($"GetInspectionDefectByDetail failed: {ex.Message}", ex);
            }
        }

        public async Task<string> GetInspectionWorkerByInspection(int inspectionId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var rows = await _QueryExecutor.QueryAsync<InspectionWorkerDetailDTO>(
                    loginDTO,
                    InspectionQB.GET_INSPECTION_WORKER_BY_INSPECTION,
                    new { InspectionId = inspectionId },
                    cancellationToken: ct);

                return JsonConvert.SerializeObject(rows);
            }
            catch (Exception ex)
            {
                throw new Exception($"GetInspectionWorkerByInspection failed: {ex.Message}", ex);
            }
        }

        // ── Private Helpers ───────────────────────────────────────────────────

        /// <summary>
        /// GET: Re-groups individual sample DB rows (one row per sample) back into
        /// one DTO per parameter, joining InputValues as "76.25,49,76".
        ///
        /// Group key: (InspectionDetailId, InspectionParameterSlNo, ParameterId, ParameterSection)
        ///
        /// FIXES:
        ///   [DAL-COLLAPSE-1] ParameterSection added to group key — prevents the same
        ///                    ParameterId under different sections from being merged.
        ///   [DAL-COLLAPSE-2] Number formatting uses InvariantCulture — prevents "76,25"
        ///                    on European locale servers.
        ///
        /// Dapper note: InspectionParameterInputValue is a numeric DB column.
        /// Dapper cannot cast numeric → string implicitly, so it may arrive as null.
        /// We fall back to InspectionParameterFinalValue (double?) which always maps.
        /// </summary>
        private static List<InspectionParameterDTO> CollapseParameterSamples(
            IEnumerable<InspectionParameterDTO> rows)
        {
            return rows
                .GroupBy(p => new
                {
                    p.InspectionDetailId,
                    p.InspectionParameterSlNo,
                    p.ParameterId,
                    p.ParameterSection          // [DAL-COLLAPSE-1] prevents cross-section merge
                })
                .Select(g =>
                {
                    var ordered = g.OrderBy(p => p.InspectionParameterSampleNumber).ToList();
                    var first = ordered.First();

                    // Rebuild comma-separated string from individual sample rows
                    var joinedValues = string.Join(",", ordered.Select(p =>
                    {
                        // Prefer string field (needs explicit CAST in SQL for Dapper to map it)
                        var strVal = p.InspectionParameterInputValue;
                        if (!string.IsNullOrWhiteSpace(strVal))
                            return strVal.Trim();

                        // [DAL-COLLAPSE-2] Fallback: format FinalValue with InvariantCulture
                        var d = p.InspectionParameterFinalValue ?? 0;
                        return d == Math.Floor(d)
                            ? ((long)d).ToString(CultureInfo.InvariantCulture)   // 49.0 → "49"
                            : d.ToString("G", CultureInfo.InvariantCulture);     // 76.25 → "76.25"
                    }));

                    return new InspectionParameterDTO
                    {
                        InspectionParameterId = first.InspectionParameterId,
                        InspectionDetailId = first.InspectionDetailId,
                        InspectionParameterSlNo = first.InspectionParameterSlNo,
                        InspectionParameterSampleNumber = first.InspectionParameterSampleNumber,
                        InspectionParameterParameterSlNo = first.InspectionParameterParameterSlNo,
                        ParameterId = first.ParameterId,
                        ParameterCode = first.ParameterCode,
                        ParameterName = first.ParameterName,
                        UomId = first.UomId,
                        UomCode = first.UomCode,
                        UomName = first.UomName,
                        ParameterSection = first.ParameterSection,
                        InspectionParameterAverage = first.InspectionParameterAverage,
                        InspectionParameterResult = first.InspectionParameterResult,
                        InspectionParameterInputText = first.InspectionParameterInputText,
                        InspectionParameterInputDate = first.InspectionParameterInputDate, // DateTime?
                        InspectionParameterFinalValue = first.InspectionParameterFinalValue,
                        MinVal = first.MinVal,
                        MaxVal = first.MaxVal,
                        InspectionParameterInputValue = joinedValues  // rejoined for UI
                    };
                })
                .ToList();
        }

        // NOTE: ExpandSampleValues is intentionally REMOVED.
        // Reason: BLL.ExpandParameterSamples already splits comma values into
        // individual DTO rows before SaveInspectionFull is called.
        // The DAL receives one DTO per sample and parses InputValue as a single
        // double? inline — no second expansion loop is needed or correct here.
    }
}