using GB5Shared.DTO.Framework.AutoNumber;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using MMBLL.Lot;
using MMBLL.ParameterPartyOverride;
using MMDAL.CustomCode.Inspection;
using MMDAL.DTO.Inspection;
using MMDAL.DTO.Lot;
using MMDAL.DTO.ParameterPartyOverride;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Globalization;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace MMBLL.Inspection
{
    public class InspectionBLL : IInspectionBLL
    {
        private readonly IInspectionDAL _InspectionDAL;
        private readonly AutoNumber _AutoNumber;
        private readonly IValidation _Validation;
        private readonly IInspectionParameterComputationBLL _paramComp;
        private readonly ILotBLL _lotBLL;
        private readonly VoucherNumberService _voucherNumberService;
        private readonly IParameterPartyOverrideBLL _paramPartyOverrideBLL;

        public InspectionBLL(
            IInspectionDAL inspectionDAL,
            AutoNumber autoNumber,
            IValidation validation,
            IInspectionParameterComputationBLL paramComp,
            ILotBLL lotBLL,
            VoucherNumberService voucherNumberService,
            IParameterPartyOverrideBLL paramPartyOverrideBLL)
        {
            _InspectionDAL = inspectionDAL;
            _AutoNumber = autoNumber;
            _Validation = validation;
            _paramComp = paramComp;
            _lotBLL = lotBLL;
            _voucherNumberService = voucherNumberService;
            _paramPartyOverrideBLL = paramPartyOverrideBLL;
        }

        // ── Existing header operations (preserved) ────────────────────────────

        public async Task<string> GetInspection(int SpaceInspectionId, LoginDTO LoginDTO)
        {
            try { return await _InspectionDAL.GetInspection(SpaceInspectionId, LoginDTO); }
            catch (Exception) { throw; }
        }

        public async Task<string> GetPicklistInspection(CriteriaDTO CriteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                return await _InspectionDAL.GetPicklistInspection(CriteriaDTO, loginDTO, ct).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        /// <summary>
        /// Legacy single-header save (header only, no child arrays).
        /// Kept for backward compatibility.
        /// FIX: isNew declared above try; rollback fires on autoNumberDTO != null only.
        /// </summary>
        public async Task<string> SaveInspection(InspectionDTO InspectionDTO, LoginDTO LoginDTO)
        {
            if (InspectionDTO == null) throw new ArgumentNullException(nameof(InspectionDTO));

            // FIX: declared ABOVE try so catch block can access it
            bool isNew = InspectionDTO.InspectionId == 0;
            AutoNumberDTO? autoNumberDTO = null;

            try
            {
                InspectionDTO.InspectionModifiedById = LoginDTO.UserId;
                InspectionDTO.InspectionModifiedOn = DateTime.UtcNow;

                if (isNew)
                {
                    autoNumberDTO = await _AutoNumber.GetNumberAsync(1, "INSPECTION", LoginDTO);
                    InspectionDTO.InspectionId = autoNumberDTO.StartNumber;
                    InspectionDTO.InspectionNumber = FormatInspectionNumber(autoNumberDTO.StartNumber);
                    InspectionDTO.InspectionCreatedById = LoginDTO.UserId;
                    InspectionDTO.InspectionCreatedOn = DateTime.UtcNow;
                    await _InspectionDAL.SaveInspection(InspectionDTO, LoginDTO);
                    return $"{SuccessResponse.SaveSuccessMessage} {InspectionDTO.InspectionNumber}";
                }
                else
                {
                    await _InspectionDAL.UpdateInspection(InspectionDTO, LoginDTO);
                    return $"{SuccessResponse.UpdateSuccessMessage} {InspectionDTO.InspectionNumber}";
                }
            }
            catch
            {
                // FIX: rollback whenever block was reserved, regardless of InspectionId value
                if (autoNumberDTO != null)
                    await _AutoNumber.RollbackAutoNumber("INSPECTION", autoNumberDTO.StartNumber, LoginDTO);
                throw;
            }
        }

        public async Task<string> DeleteInspection(int SpaceInspectionId, LoginDTO LoginDTO)
        {
            try { return await _InspectionDAL.DeleteInspection(SpaceInspectionId, LoginDTO); }
            catch (Exception) { throw; }
        }

        // ── Composite full-get ────────────────────────────────────────────────

        public async Task<string> GetInspectionFull(int inspectionId, LoginDTO loginDTO, CancellationToken ct)
        {
            try { return await _InspectionDAL.GetInspectionFull(inspectionId, loginDTO, ct).ConfigureAwait(false); }
            catch (Exception) { throw; }
        }

        // ── Paginated list ────────────────────────────────────────────────────

        public async Task<string> GetInspectionList(InspectionListRequestDTO request, LoginDTO loginDTO, CancellationToken ct)
        {
            try { return await _InspectionDAL.GetInspectionList(request, loginDTO, ct).ConfigureAwait(false); }
            catch (Exception) { throw; }
        }

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

        /// <summary>
        /// Saves or updates a full Inspection including all child arrays
        /// (Details, Parameters, Results, Defects, Workers) in a single DB transaction.
        ///
        /// FIXES APPLIED:
        ///   [1] isNew declared ABOVE try → accessible in catch for rollback logic.
        ///   [2] Rollback fires whenever autoNumberDTO != null (block was reserved).
        ///       Removed wrong "dto.InspectionId > 0" condition — always true after assignment.
        ///   [3] ExpandParameterSamples runs FIRST; after that each param DTO = 1 DB row.
        ///       paramCount uses simple .Count — no re-splitting of InputValue strings.
        ///   [4] nextId++ is a simple increment in child loop — no re-split needed.
        ///   [5] InspectionNumber formatted as "INS-0000001" from AutoNumber InspectionId.
        ///   [6] Negative InspectionDefectId from JSON overwritten by fresh nextId++.
        /// </summary>
        public async Task<string> SaveInspectionFull(
            InspectionDTO dto,
            LoginDTO loginDTO,
            CancellationToken ct)
        {
            if (dto == null) throw new ArgumentNullException(nameof(dto));

            // [FIX-1] Declare ABOVE try — catch block must read isNew for rollback guard
            bool isNew = dto.InspectionId == 0;
            AutoNumberDTO? autoNumberDTO = null;

            try
            {
                // ─────────────────────────────────────────────────────────────
                // STEP 1 — EXPAND PARAMETER SAMPLES
                // Splits "23,44,534" → 3 individual DTO rows BEFORE any counting.
                // After this call, every parameter DTO in the tree holds exactly
                // one numeric InputValue. All counts and ID reservations below
                // are therefore correct and do NOT need to re-split strings.
                // ─────────────────────────────────────────────────────────────
                ExpandParameterSamples(dto);

                // ─────────────────────────────────────────────────────────────
                // STEP 2 — VALIDATION
                // ─────────────────────────────────────────────────────────────
                if (dto.InspectionTypeId == 0)
                    throw new ValidationException("InspectionTypeId is required.");

                await _Validation.NotEmpty(dto.InspectionReferenceNumber, nameof(dto.InspectionReferenceNumber));

                if (dto.InspectionDate > DateOnly.FromDateTime(DateTime.UtcNow).AddDays(1))
                    throw new ValidationException("InspectionDate cannot be in the future.");

                if (dto.InspectionWorkerDetailArray == null || dto.InspectionWorkerDetailArray.Count == 0)
                    throw new ValidationException("At least one worker is required.");

                // CK_TINSPECTIONPARAMETER_MINMAX requires MinVal <= MaxVal when both are
                // provided — validate here so a bad payload gets a clear message instead
                // of a raw SQL CHECK constraint error from the DAL insert.
                if (dto.InspectionDetailArray != null)
                {
                    foreach (var detail in dto.InspectionDetailArray)
                    {
                        var flatParamsForValidation = detail.InspectionParameterArray?
                            .SelectMany(s => s.Parameters ?? new List<InspectionParameterDTO>())
                            ?? Enumerable.Empty<InspectionParameterDTO>();

                        foreach (var p in flatParamsForValidation)
                        {
                            if (p.MinVal.HasValue && p.MaxVal.HasValue && p.MinVal.Value > p.MaxVal.Value)
                                throw new ValidationException(
                                    $"Parameter '{p.ParameterName ?? p.ParameterId.ToString()}': " +
                                    $"Min value ({p.MinVal.Value}) cannot be greater than Max value ({p.MaxVal.Value}).");
                        }
                    }
                }

                if (dto.InspectionDetailArray != null)
                {
                    foreach (var detail in dto.InspectionDetailArray)
                    {
                        if (detail.LotId == 0)
                        {
                            if (detail.ItemId == 0)
                                throw new ValidationException("ItemId is required on detail row to auto-create LOT.");

                            if (detail.SkuId == 0)
                                throw new ValidationException("SkuId is required on detail row to auto-create LOT.");

                            // LotLotNumber is a plain user input field — use it directly as provided.
                            var lotDTO = new LotDTO
                            {
                                ItemId = detail.ItemId,
                                SKUId = detail.SkuId,
                                LotTypeId = detail.LotTypeId != 0 ? detail.LotTypeId : -1,
                                ProcessId = -1,
                                LotLotDate = DateTime.UtcNow,
                                LotLotExpiryDate = new DateTime(9999, 12, 31),
                                LotCreatedById = loginDTO.UserId,
                                LotCreatedOn = DateTime.UtcNow,
                                LotModifiedById = loginDTO.UserId,
                                LotModifiedOn = DateTime.UtcNow,
                                LotStatus = 1,
                                LotVersion = 1,
                                LotMRPRate = 0,
                                LotItemPostedCost = 0,
                                LotRemarks = "",
                                LotLotNumber = detail.LotLotNumber,  // plain input from client
                            };

                            int newLotId = await _lotBLL.CreateLotAsync(lotDTO, loginDTO, ct);

                            // ✅ Assign to detail
                            detail.LotId = newLotId;
                            detail.LotLotNumber = lotDTO.LotLotNumber;

                            // ✅ Assign to all Result rows under this detail
                            // FK_TINSPECTIONRESULT_LOTID → TLOT.LOTID
                            if (detail.InspectionResultArray != null)
                            {
                                foreach (var result in detail.InspectionResultArray)
                                {
                                    result.LotId = newLotId;
                                }
                            }
                        }
                        else
                        {
                            // LotId already provided — still sync to Result rows
                            if (detail.InspectionResultArray != null)
                            {
                                foreach (var result in detail.InspectionResultArray)
                                {
                                    result.LotId = detail.LotId;
                                }
                            }
                        }
                    }
                }

                // ─────────────────────────────────────────────────────────────
                // STEP 3 — AUDIT FIELDS
                // ─────────────────────────────────────────────────────────────
                dto.InspectionModifiedById = loginDTO.UserId;
                dto.InspectionModifiedOn = DateTime.UtcNow;

                // ─────────────────────────────────────────────────────────────
                // STEP 4 — AUTONUMBER BLOCK RESERVATION
                // Generated OUTSIDE transaction — counter always advances,
                // never rolled back. Mirrors SaveTLeave pattern.
                // ─────────────────────────────────────────────────────────────
                int detailCount = dto.InspectionDetailArray?.Count ?? 0;
                int paramCount = dto.InspectionDetailArray?
                    .Sum(d => d.InspectionParameterArray?
                        .Sum(s => s.Parameters?.Count ?? 0) ?? 0) ?? 0;
                int resultCount = dto.InspectionDetailArray?
                    .Sum(d => d.InspectionResultArray?.Count ?? 0) ?? 0;
                int defectCount = dto.InspectionDetailArray?
                    .Sum(d => d.InspectionDefectArray?.Count ?? 0) ?? 0;
                int workerCount = dto.InspectionWorkerDetailArray?.Count ?? 0;

                int total = (isNew ? 1 : 0) + detailCount + paramCount + resultCount + defectCount + workerCount;

                if (total > 0)
                {
                    autoNumberDTO = await _AutoNumber.GetNumberAsync(total, "INSPECTION", loginDTO);
                }

                int nextId = autoNumberDTO?.StartNumber ?? 0;

                // ─────────────────────────────────────────────────────────────
                // STEP 5 — HEADER ID + INSPECTION NUMBER (INSERT ONLY)
                //
                // InspectionId     → AutoNumber (negative PK — by design in this system)
                // InspectionNumber → VoucherNumberService (positive display number)
                //                    e.g. INS-0000001, INS-0000002 ...
                //                    Counter is auto-commit — never rolled back on error.
                //                    Mirrors SaveTLeave → GetNextVoucherNumberAsync pattern.
                // ─────────────────────────────────────────────────────────────
                if (isNew)
                {
                    dto.InspectionId = nextId++;   // AutoNumber negative PK ✅

                    // ── DISPLAY NUMBER via VoucherNumberService ───────────────
                    // Same pattern as SaveTLeave → _voucherNumberService.GetNextVoucherNumberAsync
                    // BizTransactionTypeId drives the voucher series (per inspection type).
                    // transaction: null → auto-commit, counter never rolled back on error.
                    string? inspectionNumber = await _voucherNumberService.GetNextVoucherNumberAsync(
                        dto.BizTransactionTypeId,       // voucher series per inspection type
                        dto.InspectionDate,             // date for period-based sequencing
                        loginDTO,
                        transaction: null);             // auto-commit ✅

                    // Falls back to Math.Abs format if VoucherNumber service returns null
                    dto.InspectionNumber = inspectionNumber
                                                ?? $"INS-{Math.Abs(dto.InspectionId):D7}";

                    dto.InspectionCreatedById = loginDTO.UserId;
                    dto.InspectionCreatedOn = DateTime.UtcNow;
                }

                // ─────────────────────────────────────────────────────────────
                // STEP 6 — CHILD ID ASSIGNMENT
                //
                // [FIX-4] nextId++ simple increment per row.
                //         No re-splitting of InputValue — already expanded in Step 1.
                // [FIX-6] InspectionDefectId always overwritten — ignores JSON "-1" value.
                // ─────────────────────────────────────────────────────────────
                if (dto.InspectionDetailArray != null)
                {
                    foreach (var detail in dto.InspectionDetailArray)
                    {
                        detail.InspectionId = dto.InspectionId;
                        detail.InspectionDetailId = nextId++;   // unique PK per detail row

                        // PARAMETERS — one DTO per sample after Step 1 expand
                        if (detail.InspectionParameterArray != null)
                        {
                            // globalParamSlNo increments per unique parameter (not per sample).
                            // All expanded sample rows for the same parameter share the same SlNo
                            // so CollapseParameterSamples returns 1,2,3... in the GET response.
                            short globalParamSlNo = 0;
                            int? lastParameterId = null;
                            short paramSlNo = 1;

                            foreach (var section in detail.InspectionParameterArray)
                            {
                                foreach (var p in section.Parameters ?? new List<InspectionParameterDTO>())
                                {
                                    p.InspectionDetailId = detail.InspectionDetailId;
                                    p.InspectionParameterId = nextId++;

                                    // New unique parameter → increment global counter and reset paramSlNo
                                    if (p.ParameterId != lastParameterId)
                                    {
                                        globalParamSlNo++;
                                        lastParameterId = p.ParameterId;
                                        p.InspectionParameterParameterSlNo = paramSlNo++;
                                    }
                                    else
                                    {
                                        // Same parameter, different sample — keep same SlNo, keep same paramSlNo
                                        p.InspectionParameterParameterSlNo =
                                            (short)(paramSlNo - 1);
                                    }

                                    p.InspectionParameterSlNo = globalParamSlNo;
                                }
                            }
                        }

                        // RESULTS
                        if (detail.InspectionResultArray != null)
                        {
                            foreach (var r in detail.InspectionResultArray)
                            {
                                r.InspectionDetailId = detail.InspectionDetailId;
                                r.InspectionResultId = nextId++;
                            }
                        }

                        // DEFECTS — [FIX-6] negative JSON value (e.g. -1) is always overwritten
                        if (detail.InspectionDefectArray != null)
                        {
                            foreach (var df in detail.InspectionDefectArray)
                            {
                                df.InspectionDetailId = detail.InspectionDetailId;
                                df.InspectionDefectId = nextId++;   // always a fresh positive ID
                            }
                        }
                    }
                }

                // ─────────────────────────────────────────────────────────────
                // STEP 7 — WORKERS
                // ─────────────────────────────────────────────────────────────
                if (dto.InspectionWorkerDetailArray != null)
                {
                    foreach (var w in dto.InspectionWorkerDetailArray)
                    {
                        w.InspectionId = dto.InspectionId;
                        w.InspectionWorkerDetailId = nextId++;
                    }
                }

                // ─────────────────────────────────────────────────────────────
                // STEP 8 — SERVER-SIDE CALCULATIONS
                // EnrichWithFinalValues computes average / pass-fail per parameter.
                // ComputeOverallResult derives detail-level QC status.
                // ─────────────────────────────────────────────────────────────
                if (dto.InspectionDetailArray != null)
                {
                    foreach (var detail in dto.InspectionDetailArray)
                    {
                        var flatParams = detail.InspectionParameterArray?
                            .SelectMany(s => s.Parameters ?? new List<InspectionParameterDTO>())
                            .ToList();

                        if (flatParams?.Count > 0)
                            _paramComp.EnrichWithFinalValues(flatParams);

                        detail.InspectionDetailQcComparisonStatus =
                            ComputeOverallResult(detail.InspectionResultArray, detail.InspectionDefectArray);
                    }
                }

                // ─────────────────────────────────────────────────────────────
                // STEP 9 — PERSIST (single DB transaction inside DAL)
                // ─────────────────────────────────────────────────────────────
                await _InspectionDAL.SaveInspectionFull(dto, loginDTO, ct, isNew)
                    .ConfigureAwait(false);

                // STEP 9b — PARTY-SCOPED THRESHOLD UPSERT
                // Whatever Min/Max the user entered/edited on this inspection becomes
                // this party's (+item's) standing default for the parameter going
                // forward — independent of TINSPECTIONPARAMETER, which only holds a
                // point-in-time snapshot. Best-effort: a failure here must not roll
                // back the inspection save that already succeeded.
                await UpsertPartyThresholds(dto, loginDTO, ct).ConfigureAwait(false);

                if (!isNew)
                {
                    // InspectionNumber is immutable — client may send "0" on update.
                    // Re-fetch the real number from DB so the response is correct.
                    var json = await _InspectionDAL.GetInspection(dto.InspectionId, loginDTO)
                        .ConfigureAwait(false);
                    var saved = Newtonsoft.Json.JsonConvert.DeserializeObject<InspectionDTO>(json);
                    dto.InspectionNumber = saved?.InspectionNumber ?? dto.InspectionNumber;
                }

                return isNew
                    ? $"{SuccessResponse.SaveSuccessMessage} {dto.InspectionNumber}"
                    : $"{SuccessResponse.UpdateSuccessMessage} {dto.InspectionNumber}";
            }
            catch
            {
                // [FIX-2] Roll back the entire reserved AutoNumber block.
                //         Condition: autoNumberDTO != null (block was allocated).
                //         Do NOT check dto.InspectionId > 0 — always true after Step 5
                //         and would wrongly suppress rollback on partial failures.
                if (autoNumberDTO != null)
                    await _AutoNumber.RollbackAutoNumber("INSPECTION", autoNumberDTO.StartNumber, loginDTO);

                throw;
            }
        }

        // ── Inspection Number Generation (Preview / Display Only) ─────────────

        /// <summary>
        /// Returns a preview of the next inspection number in "INS-0000001" format.
        /// FOR DISPLAY ONLY — do NOT use this to assign the saved InspectionNumber.
        /// The actual saved number is generated inside SaveInspectionFull from the
        /// AutoNumber InspectionId to guarantee uniqueness with no race condition.
        ///
        /// WARNING: Reading lastNumber + incrementing in app code is NOT atomic.
        /// Two concurrent requests can produce the same sequence. For production,
        /// move sequence generation into the DB (SQL SEQUENCE or SERIALIZABLE txn).
        /// </summary>
        public async Task<string> GenerateInspectionNumber(
            int inspectionTypeId,
            int ouId,
            LoginDTO loginDTO,
            CancellationToken ct)
        {
            try
            {
                var lastNumberTask = _InspectionDAL.GetLastInspectionNumber(inspectionTypeId, ouId, loginDTO, ct);
                await lastNumberTask.ConfigureAwait(false);

                string lastNumber = lastNumberTask.Result;

                int seq = 1;
                if (!string.IsNullOrWhiteSpace(lastNumber))
                {
                    // Expected format: "INS-0000001" → split on '-', parse right part
                    var parts = lastNumber.Split('-');
                    if (parts.Length == 2 && int.TryParse(parts[1], out int lastSeq))
                        seq = lastSeq + 1;
                }

                // Preview matches the exact saved format
                return $"INS - {seq:D7}";
            }
            catch (Exception ex)
            {
                throw new Exception($"GenerateInspectionNumber failed: {ex.Message}", ex);
            }
        }

        // ── Individual child GET endpoints ────────────────────────────────────
        // Empty try/catch { throw; } wrappers removed — zero benefit, extra stack frames.

        public Task<string> GetInspectionDetailByInspection(int inspectionId, LoginDTO loginDTO, CancellationToken ct)
            => _InspectionDAL.GetInspectionDetailByInspection(inspectionId, loginDTO, ct);

        public Task<string> GetInspectionParameterByDetail(int inspectionDetailId, LoginDTO loginDTO, CancellationToken ct)
            => _InspectionDAL.GetInspectionParameterByDetail(inspectionDetailId, loginDTO, ct);

        public Task<string> GetInspectionResultByDetail(int inspectionDetailId, LoginDTO loginDTO, CancellationToken ct)
            => _InspectionDAL.GetInspectionResultByDetail(inspectionDetailId, loginDTO, ct);

        public Task<string> GetInspectionDefectByDetail(int inspectionDetailId, LoginDTO loginDTO, CancellationToken ct)
            => _InspectionDAL.GetInspectionDefectByDetail(inspectionDetailId, loginDTO, ct);

        public Task<string> GetInspectionWorkerByInspection(int inspectionId, LoginDTO loginDTO, CancellationToken ct)
            => _InspectionDAL.GetInspectionWorkerByInspection(inspectionId, loginDTO, ct);

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

        /// <summary>
        /// Formats an integer AutoNumber ID into the standard Inspection Number string.
        /// Format : INS-{id:D7}
        /// Examples: 1 → "INS-0000001" | 42 → "INS-0000042" | 1000 → "INS-0001000"
        /// Centralised here so SaveInspection (legacy) and SaveInspectionFull both
        /// produce identical format strings.
        /// </summary>
        private static string FormatInspectionNumber(int id)
            => $"INS-{id:D7}";

        /// <summary>
        /// Persists each parameter's Min/Max as a standing threshold for this
        /// inspection's Party (+ Item), via MPARAMETERPARTYOVERRIDE. Called once per
        /// SaveInspectionFull, after the inspection itself has already been saved —
        /// deliberately swallows failures so a threshold-write hiccup never rolls
        /// back or fails an otherwise-successful inspection save.
        /// </summary>
        private async Task UpsertPartyThresholds(InspectionDTO dto, LoginDTO loginDTO, CancellationToken ct)
        {
            if (dto.PartyId == 0 || dto.InspectionDetailArray == null) return;

            var seen = new HashSet<(int ParameterSetId, int ParameterId)>();

            foreach (var detail in dto.InspectionDetailArray)
            {
                if (detail.ParameterSetId == 0 || detail.InspectionParameterArray == null) continue;

                var flatParams = detail.InspectionParameterArray
                    .SelectMany(s => s.Parameters ?? new List<InspectionParameterDTO>());

                foreach (var p in flatParams)
                {
                    if (p.ParameterId == 0) continue;
                    if (!p.MinVal.HasValue && !p.MaxVal.HasValue) continue;

                    var key = (detail.ParameterSetId, p.ParameterId);
                    if (!seen.Add(key)) continue; // one upsert per unique parameter per save

                    try
                    {
                        await _paramPartyOverrideBLL.SaveParameterThreshold(new ParameterPartyOverrideDTO
                        {
                            PartyId = dto.PartyId,
                            ItemId = detail.ItemId != 0 ? detail.ItemId : -1,
                            ParameterSetId = detail.ParameterSetId,
                            ParameterId = p.ParameterId,
                            MinValue = p.MinVal,
                            MaxValue = p.MaxVal
                        }, loginDTO, ct).ConfigureAwait(false);
                    }
                    catch
                    {
                        // Best-effort — the inspection itself already saved successfully.
                    }
                }
            }
        }

        /// <summary>
        /// Splits comma-separated InputValue ("23,44,534") into one DTO per sample.
        /// Called ONCE at the top of SaveInspectionFull before any counting or ID work.
        ///
        /// After this call:
        ///   • Every parameter DTO holds exactly ONE clean numeric string in InputValue.
        ///   • Null / empty / all-comma InputValue defaults to "0" (NOT NULL safe).
        ///   • Single value raw strings like "33.0000,,," are cleaned to "33.0000".
        ///   • SampleNumber is always >= 1 (satisfies CK_TINSPECTIONPARAMETER_SAMPLENUMBER).
        ///   • All downstream counts and ID increments use simple ++ (no re-splitting).
        ///   • InvariantCulture parsing prevents "76,25" being treated as two values
        ///     on European locale servers.
        /// </summary>
        private static void ExpandParameterSamples(InspectionDTO dto)
        {
            if (dto.InspectionDetailArray == null) return;

            foreach (var detail in dto.InspectionDetailArray)
            {
                if (detail.InspectionParameterArray == null) continue;

                foreach (var section in detail.InspectionParameterArray)
                {
                    if (section.Parameters == null) continue;

                    var expanded = new List<InspectionParameterDTO>();

                    foreach (var p in section.Parameters)
                    {
                        // Persist the section this row was actually saved under —
                        // GET_INSPECTION_FULL reads P.SECTION back directly instead of
                        // re-deriving it from the master ParameterSet (which fans out
                        // and duplicates rows whenever a ParameterId is configured under
                        // more than one section).
                        p.ParameterSection = section.SectionName;

                        var values = (p.InspectionParameterInputValue ?? "")
                            .Split(',', StringSplitOptions.RemoveEmptyEntries)
                            .Select(v => v.Trim())
                            .Where(v => double.TryParse(
                                v,
                                NumberStyles.Any,
                                CultureInfo.InvariantCulture,
                                out _))
                            .ToList();

                        // ── CASE 1: No valid numeric values found ──────────────────────
                        // e.g. InputValue was null / "" / ",,," / "abc"
                        // FIX: Default to "0" and SampleNumber = 1 (constraint requires >= 1)
                        if (values.Count == 0)
                        {
                            p.InspectionParameterInputValue = "0";
                            p.InspectionParameterSampleNumber = 1;  // ✅ satisfies CHECK constraint
                            expanded.Add(p);
                            continue;
                        }

                        // ── CASE 2: Exactly one valid numeric value ────────────────────
                        // e.g. "33.0000,,," → cleaned to "33.0000"
                        // FIX: Clean InputValue + set SampleNumber = 1
                        if (values.Count == 1)
                        {
                            p.InspectionParameterInputValue = values[0];  // ✅ "33.0000"
                            p.InspectionParameterSampleNumber = 1;          // ✅ satisfies CHECK constraint
                            expanded.Add(p);
                            continue;
                        }

                        // ── CASE 3: Multiple values — clone one DTO per sample ─────────
                        // e.g. "777777777.0000,43,234" → 3 separate DTO rows
                        // sampleNum starts at 1 — always satisfies CHECK constraint.
                        // IDs are assigned later in Step 6; left at default (0) here.
                        byte sampleNum = 1;
                        foreach (var val in values)
                        {
                            expanded.Add(new InspectionParameterDTO
                            {
                                InspectionDetailId = p.InspectionDetailId,
                                InspectionParameterSlNo = p.InspectionParameterSlNo,
                                InspectionParameterParameterSlNo = p.InspectionParameterParameterSlNo,
                                InspectionParameterSampleNumber = sampleNum++,  // ✅ 1,2,3...
                                ParameterId = p.ParameterId,
                                UomId = p.UomId,
                                ParameterSection = p.ParameterSection,
                                InspectionParameterAverage = p.InspectionParameterAverage,
                                InspectionParameterResult = p.InspectionParameterResult,
                                InspectionParameterInputValue = val,   // ✅ single clean numeric string
                                InspectionParameterInputText = p.InspectionParameterInputText,
                                InspectionParameterInputDate = p.InspectionParameterInputDate,
                                InspectionParameterFinalValue = p.InspectionParameterFinalValue,
                                MinVal = p.MinVal,
                                MaxVal = p.MaxVal,
                            });
                        }
                    }

                    section.Parameters = expanded;
                }
            }
        }

        /// <summary>
        /// Derives the overall QC comparison status for one detail row.
        ///
        /// Priority (highest wins):
        ///   2 = Fail    — any result is Fail
        ///   3 = Rework  — defect points present (no Fail) OR any result is Rework
        ///   1 = Pass    — all results are Pass
        ///   0 = Pending — no results yet, or mixed/unset values
        ///
        /// Fail always overrides defect-point Rework (confirmed business rule).
        /// </summary>
        private static byte ComputeOverallResult(
            IList<InspectionResultDTO>? results,
            IList<InspectionDefectDTO>? defects)
        {
            if (results == null || results.Count == 0) return 0; // Pending

            // Fail — highest priority, checked first
            if (results.Any(r => r.InspectionResultInspectionResults == 2)) return 2;

            // Rework via defect points (only reached when no Fail result above)
            bool hasDefectPoints = defects?.Any(d => d.InspectionDefectPointsAfter > 0) == true;
            if (hasDefectPoints) return 3;

            // Rework via result flag
            if (results.Any(r => r.InspectionResultInspectionResults == 3)) return 3;

            // All Pass
            if (results.All(r => r.InspectionResultInspectionResults == 1)) return 1;

            return 0; // Pending — mixed or unset
        }
    }
}